Автозаполнение ячеек в excel при выборе из выпадающего списка - Учим Эксель

Как в Excel создать выпадающий перечень

Одна из самых нужных функций в Excel при работе с информацией – создание выпадающего списка. С его помощью стремительно и верно заполняются ячейки, значения не вписываются вручную, а просто выбираются из за ранее обозначенного списка. Также есть возможность настроить контроль, чтоб остальные юзеры не могли заносить конфигурации и добавлять новейшие значения, не предусмотренные вначале. Умение категоризировать данные полезно в почти всех вариантах: от мониторинга расходов и до управления проектами.

Одна из самых нужных функций в Excel при работе с информацией – создание выпадающего списка. С его помощью стремительно и верно заполняются ячейки, значения не вписываются вручную, а просто выбираются из за ранее обозначенного списка. Также есть возможность настроить контроль, чтоб остальные юзеры не могли заносить конфигурации и добавлять новейшие значения, не предусмотренные вначале. Умение категоризировать данные полезно в почти всех вариантах: от мониторинга расходов и до управления проектами.

Обычной выпадающий перечень

    Выделить столбец с определенными наименованиями, кликнуть по нему правой клавишей мышки. Из выпадающего меню избрать вариант «Присвоить имя».

    Повторить деяния из п.3. В открывшемся окне перейти на вкладку «Сообщение для ввода».

Связанный выпадающий перечень

    Приготовить таблицу. В первой строке всякого столбца обязана быть категория меню (для удобства).

При выборе другого элемента в H2 автоматом меняется и ссылка-источник для H3. Другими словами источник для связанного выпадающего списка в H3 изменяется с учетом данных, которые были выбраны в H2.

Пробелы в заглавии при разработке связанного выпадающего списка

Именуя спектр значений, принципиально применять заместо пробела нижнее подчеркивание «_». К примеру, «Мясные_блюда». Но есть возможность упустить данное правило в одном из заглавий, и в итоге будет ошибка при выполнении операции. Можно в самих заглавиях применять обыденный пробел, а в момент подстановки в перечень, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула смотрится так:

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

Стоит направить внимание на отсутствие пробелов в заглавии в начале и в конце, чтоб избежать неправильного вывода заголовков. Заавтоматизировать данный процесс при построении имени также можно средством функции:

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

Перечень с автозаполнением

Метод 1

Сейчас при добавлении новейших частей они также покажутся в готовом выпадающем списке.

Метод 2

    Выделить имеющийся список наименований и присвоить ему имя в левой строке формул.

Метод 3

    Выделить содержание, во вкладке «Основная» избрать «Форматировать как таблицу». В показавшемся окне непременно поставить галочку рядом с пт «Таблица с заголовками».

Если в нижние строчки вписать новейшие значения, они здесь же отобразятся в готовом списке.

Как в Excel создать выпадающий перечень в ячейке с выбором нескольких значений?

До этого всего, необходимо заполнить таблицу. Выделить ее содержимое, и при помощи «Проверки данных» выполнить вышеперечисленные деяния.

Чтоб добавить макрос, кликнуть правой клавишей мыши по ярлычку листа с выпадающим списком и избрать вариант «Начальный текст». Раскроется Visual Basic, в окне редактора следует применять код для горизонтального отображения:

В итоге обязано получиться последующее:

Для вертикального отображения можно пользоваться кодом:

В итоге получится так:

Для того чтоб элементы скапливались в той же самой ячейке, подойдет код:

Выпадающий перечень в MS Excel. Описание и примеры.

В данной статье, на обычных примерах показано, что такое выпадающий перечень в MS Excel, как им воспользоваться, какие варианты использования данной функции есть. На что нужно направить внимание.

Обычной выпадающий перечень в ячейки.

Представь, нам необходимо создать ячейку, в которой будет выпадающий перечень из нескольких вариантов значений на выбор. К примеру: город, село городского типа, село. Поместим в эту ячейку выпадающий перечень с необходимым нам списком значений. В нашем примере данная ячейка будет размещаться перед ячейкой с заглавием населенного пт.

Избираем ячейку, в которой будет размещаться наш выпадающий перечень. Это ячейка С4. Дальше, в закладке Данные, находим клавишу Проверка данных.

Простой выпадающий список в ячейки

Жмем на клавишу Проверка данных, возникает диалоговое окно Проверка вводимых значений. Вводим нужные данные в поля этого диалогового окна.

Тип данных. Избираем вариант: Перечень

Источник. Тут просто прописываем нужные нам значения по порядку, через точку с запятой «;».

В нашем примере это смотрится вот так: Город; Село городского тип; Село

Другие пункты диалогового окна Проверка вводимых значений не трогаем.

Простой выпадающий список в ячейки

Жмем ОК. В ячейке С4 возникает выпадающий перечень с подходящими нам значениями.

Простой выпадающий список в ячейки

Дальше копируем либо протягиваем данный выпадающий перечень в ячейки, исходя из задачки, которая перед нами стоит.

Выпадающий перечень в Excel на базе начальных данных.

У нас есть Таблица №1, в которой есть два столбца, один с заглавием конфет, иной с ценой на эти конфеты. Создадим выпадающий перечень на базе столбца с заглавие конфет. Создадим выпадающий перечень на том же листе, что и Таблица №1.

Избираем ячейку, в которой будет размещаться наш выпадающий перечень. В нашем примере это ячейка F4. Дальше, в закладке Данные, находим клавишу Проверка данных.

Выпадающий список в MS Exce

Жмем на клавишу Проверка данных, возникает диалоговое окно Проверка вводимых значений. Вводим нужные данные в поля этого диалогового окна.

Тип данных. Избираем вариант: Перечень;

Источник. Избираем спектр ячеек, с теми данными, которые должны отражаться в нашем выпадающим перечне. В нашем примере, это ячейки с заглавием конфет в Таблице №1 (C4:C13). Избрать спектр можно просто поставив курсор в поле Источник. Опосля что показаться возможность избрать спектр. Опосля выбора спектра, ссылки на него автоматом стают абсолютными (=$C$4:$C$13).

Интересно почитать:  Как в ячейке эксель сделать абзац

Другие пункты диалогового окна Проверка вводимых значений не трогаем.

Выпадающий список в MS Excel

Жмем ОК. Опосля этого показаться выпадающий перечень в обозначенной нами ячейки. В нашем примере, в выпадающем перечне будет список наименования конфет из Таблицы №1.

Выпадающий список в MS Excel

Заместо использования спектра, в поле Источник, можно применять Данное имя. Тогда в поле Источник, заместо спектра =$C$4:$C$13, необходимо будет прописать Данное имя с знаком равно перед ним. К примеру: = Конфеты.

Выпадающий список в MS Excel

Можно расположить выпадающий перечень на отдельном листе. К примеру, наша Таблица №1 будет на Листе 1, а выпадающий перечень, на базе данной таблицы, будет на Листе 2. Метод буквально таковой же, просто в поле Источник, избираем подходящий нам спектр из Таблицы 1. При всем этом, в поле Источник, будет прописан не только лишь спектр ячеек, да и заглавие Листа, на котором он находиться: =Лист1!$E$6:$E$15. В случае, если заместо спектра употребляется Данное имя, к примеру Конфеты, то различий совершенно нет. В поле Источник, будет прописано Данное имя, перед которым будет символ равно: =Конфеты.

Выпадающий перечень в Excel и ВПР.

Сейчас перед нами стоит задачка, что бы при выборе определенных конфет из выпадающего списка, в примыкающий ячейки появлялась соответственная сиим конфетам стоимость. Для этого мы будем применять функцию ВПР.

Итак, у нас есть Таблица №1 с списком конфет и ценами на их. И есть выпадающий перечень (Заглавие конфет), в котором мы можем избрать нужные нам конфеты по наименованию. Рядом с выпадающим перечнем выберем ячейку (G4), назовем ее Стоимость за кг., и создадим ее желтоватого цвета .

ВПР

Выберем «желтоватую» ячейку (G4) и вызовем функцию ВПР. В показавшемся диалоговом окне Аргументы функции, введем в поля последующие значения.

Искомое_значение. Указываем ячейку с выпадающем перечнем (F4).

Таблица. Спектр ячеек в Таблице №1, в который заходит заглавие конфет и цены за кг., Ссылки на ячейки в избранном спектре делаем абсолютными: $C$4:$D$13.

Номер_столбца. Тут ставим число два «2»

Интервальный_просмотр. Тут ставим число ноль «0».

ВПР

Жмем ОК. Сейчас при выборе определенных конфет в выпадающим перечне, в ячейки рядом (G4), возникает стоимость на эти конфеты, которая соответствует ценам обозначенным в Таблице №1.

ВПР

Можно создать выпадающей перечень с ценой, на различных с таблицей листах. Таблица №1 на Листе 1, а выпадающий перечень с ценой на Листе 2. Разница для функции ВПР будет в том, что в поле Таблица, в диалоговом окне Аргументы функции, будет указан спектр с ссылкой на лист и ячейки: Лист1!$C$4:$D$13.

Автозаполнение ячеек в Excel

Автозаполнение ячеек Excel – это автоматический ввод серии данных в некий спектр. Введем в ячейку «Пн», потом удерживая левой клавишей мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (либо в другую сторону). Результатом будет перечень из дней недельки. Можно применять короткую форму типа Пн, Вт, Ср и т.д. Эксель усвоит. Аналогичным образом создается перечень из заглавий месяцев.

Автозаполнение дней недели в Excel

Автоматическое наполнение ячеек также употребляют для продления последовательности чисел c данным шагом (арифметическая прогрессия). Чтоб создать перечень нечетных чисел, необходимо в 2-ух ячейках указать 1 и 3, потом выделить обе ячейки и протянуть вниз.

Автозаполнение последовательности чисел в Excel

Эксель также умеет распознать числа посреди текста. Так, просто сделать список кварталов. Введем в ячейку «1 квартал» и протянем вниз.

На этом зания о автозаполнении у большинства юзеров Эксель завершаются. Но это далековато не все, и дальше будут рассмотрены остальные действенные и достойные внимания приемы.

Автозаполнение в Excel из списка данных

Ясно, что не считая дней недельки и месяцев могут пригодиться остальные списки. Допустим, нередко приходится вводить список городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов на дону-на-Дону, Смоленск, Белгород. Сначала необходимо сделать и сохранить (в подходящем порядке) полный перечень заглавий. Заходим в Файл – Характеристики – Добавочно – Общие – Поменять списки.

Изменить списки для автозаполнения в Excel

В последующем открывшемся окне видны те списки, которые есть по дефлоту.

Диалоговое окно для изменения списков в Excel

Как видно, их мало. Но просто добавить собственный свой. Можно пользоваться окном справа, где или через запятую, или столбцом перечислить подходящую последовательность. Но резвее будет импортировать, в особенности, если данных много. Для этого за ранее где-нибудь на листе Excel создаем список заглавий, потом делаем на него ссылку и жмем Импорт.

Добавление нового списка

Нажимаем ОК. Перечень сотворен, можно изпользовать для автозаполнения.

Кроме текстовых списков почаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть наиболее достойные внимания приемы. Сначала необходимо выделить одно либо несколько первых значений серии, также спектр (на право либо вниз), куда будет продлена последовательность значений. Дальше вызываем диалоговое окно прогрессии: Основная – Заполнить – Прогрессия.

Команда Прогрессия в Excel

Настройки диалогового окна Прогрессия

В левой части окна при помощи переключателя задается направление построения последовательности: вниз (по строчкам) либо на право (по столбцам).

В центре выбирается подходящий тип:

  • арифметическая прогрессия – каждое следующее значение меняется на число, обозначенное в поле Шаг
  • геометрическая прогрессия – каждое следующее значение множится на число, обозначенное в поле Шаг
  • даты – делает последовательность дат. При выборе этого типа активизируются переключатели правее, где можно избрать тип единицы измерения. Есть 4 варианта:
      • денек – список календарных дат (с обозначенным ниже шагом)
      • рабочий денек – последовательность рабочих дней (пропускаются выходные)
      • месяц – изменяются лишь месяцы (число фиксируется, как в первой ячейке)
      • год – изменяются лишь годы
      • автозаполнение – эта команда равносильная протягиванию при помощи левой клавиши мыши. Другими словами эксель сам описывает: или ему продолжить последовательность чисел, или продлить перечень. Если за ранее заполнить две ячейки значениями 2 и 4, то в остальных выделенных ячейках покажется 6, 8 и т.д. Если за ранее заполнить больше ячеек, то Excel высчитает приближение способом линейной регрессии, т.е. прогноз по прямой полосы тренда (наинтереснейшая функция – подробнее см. ниже).

      Нижняя часть окна Прогрессия служит для того, чтоб сделать последовательность хоть какой длины на основании конечного значения и шага. К примеру, необходимо заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не комфортно. Потому за ранее необходимо выделить лишь ячейку с одним первым значением. Дальше в окне Прогрессия указываем Размещение, Шаг и Предельное значение.

      Предельное значение в прогрессии

      Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно создать последовательность рабочих дней на год вперед (предельным значением необходимо указать последнюю дату, к примеру 31.12.2016). Возможность заполнять столбец (либо строчку) с указанием крайнего значения весьма нужная штука, т.к. устраняет от кучи излишних действий во время протягивания. На этом опции автозаполнения завершаются. Идем дальше.

      Автозаполнение чисел при помощи мыши

      Автозаполнение в Excel удобнее созодать мышкой, у которой есть правая и левая клавиша. Пригодятся обе.

      Допустим, необходимо создать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а дальше левой клавишей мыши протягивают арифметическую прогрессию. Можно создать по-другому. Заполняем лишь одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Дальше открываем квадратик, который возникает сходу опосля протягивания в правом нижнем углу и избираем Заполнить.

      Если избрать Заполнить лишь форматы, будут продлены лишь форматы ячеек.
      Создать последовательность чисел можно еще резвее. Во время протягивания ячейки, удерживаем клавишу Ctrl.

      Этот трюк работает лишь с последовательностью чисел. В остальных ситуациях удерживание Ctrl приводит к копированию данных заместо автозаполнения.

      Если при протягивании применять правую клавишу мыши, то контекстное меню раскрывается сходу опосля отпускания клавиши.

      Автозаполнение с помощью правой кнопки мыши

      При всем этом добавляются несколько установок. Прогрессия дозволяет применять доп операции автозаполнения (опции см. выше). Правда, спектр выходит выделенным и длина последовательности будет ограничена крайней ячейкой.

      Чтоб произвести автозаполнение до нужного предельного значения (числа либо даты), можно сделать последующий трюк. Берем правой клавишей мыши за маркер чуток оттягиваем вниз, сходу возвращаем вспять и отпускаем клавишу – раскрывается контекстное меню автозаполнения. Избираем прогрессию. Сейчас выделена лишь одна ячейка, потому указываем направление, шаг, предельное значение и создаем подходящую последовательность.

      Весьма увлекательными являются пункты меню Линейное и Экспоненциальное приближение. Это экстраполяция, т.е. прогнозирование, данных по обозначенной модели (линейной либо экспоненциальной). Обычно для прогноза употребляют особые функции Excel либо за ранее рассчитывают уравнение тренда (регрессии), в которое подставляют значения независящей переменной для будущих периодов и таковым образом рассчитывают прогнозное значение. Делается приблизительно так. Допустим, есть динамика показателя с равномерным ростом.

      Для прогнозирования подойдет линейный тренд. Расчет характеристик уравнения можно выполнить при помощи функций Excel, но нередко для наглядности употребляют диаграмму с опциями отображения полосы тренда, уравнения и прогнозных значений.

      Прогноз с помощью линейного тренда на диаграмме

      Чтоб получить прогноз в числовом выражении, необходимо произвести расчет на базе приобретенного уравнения регрессии (или впрямую обратиться к формулам Excel). Таковым образом, выходит достаточно много действий, требующих при этом неплохого осознания.

      Итак вот прогноз по способу линейной регрессии можно создать совершенно без формул и без графиков, используя лишь автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой клавишей мыши на необходимое количество ячеек, соответственное длине прогноза, и избираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.

      Если данные имеют ускоряющийся рост (как счет на депозите), то можно применять экспоненциальную модель. Вновь, чтоб не страдать с вычислениями, можно пользоваться автозаполнением, выбрав Экспоненциальное приближение.

      Прогноз по методу экспоненциального приближения

      Наиболее резвого метода прогнозирования, пожалуй, не придумаешь.

      Автозаполнение дат при помощи мыши

      Достаточно нередко требуется продлить перечень дат. Берем дату и тащим левой клавишей мыши. Открываем квадратик и избираем метод наполнения.

      Автозаполнение дат в Excel с помощью мыши

      По рабочим денькам – хороший вариант для бухгалтеров, HR и остальных профессионалов, кто имеет дело с составлением разных планов. А вот иной пример. Допустим, платежи по графику наступают 15-го числа и в крайний денек всякого месяца. Укажем 1-ые две даты, протянем вниз и заполним по месяцам (хоть какой клавишей мыши).

      Автозаполнение по месяцам

      Направьте внимание, что 15-е число фиксируется, а крайний денек месяца изменяется, чтоб постоянно оставаться крайним.

      Используя правую клавишу мыши, можно пользоваться опциями прогрессии. К примеру, создать перечень рабочих дней до конца года. В списке установок через правую клавишу еще есть Секундное наполнение. Эта функция возникла в Excel 2013. Употребляется для наполнения ячеек по эталону. Но о этом уже была статья, рекомендую ознакомиться. Также поможет сберечь не один час работы.

      На этом, пожалуй, все. В видеоуроке показано, как создать автозаполнение ячеек в Excel.

      Выпадающий перечень в Excel

      vpgsch-spsk(41)

      Элемент списка знаком нам по формам на веб-сайтах. Комфортно выбирать уже готовые значения. К примеру, никто не вводит месяц вручную, его берут из такового списка. Заполнить выпадающий перечень в Excel можно с внедрением разных инструментов. В статье разглядим любой из их.

      Как создать выпадающий перечень в Excel

      Как создать выпадающий перечень в Excel 2010 либо 2016 при помощи одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите клавишу «Проверка данных». Нажмите на нее и изберите 1-ый пункт.

      Раскроется окно. Во вкладке «Характеристики» в выпадающем разделе «Тип данных» изберите «Перечень».

      Снизу покажется строчка для указания источников.

      Указывать информацию можно по-разному.

      • Ручной ввод
        Введите список через точку с запятой.
      • Выбор спектра значений с листа Excel
        Для этого начните выделять ячейки мышью.

        Как отпустите – окно опять станет обычным, а в строке покажутся адреса.
      • Создание выпадающего списка в Excel с подстановкой данных

      Поначалу назначим имя. Для этого сделайте на любом листе такую таблицу.

      Выделите ее и нажмите правую клавишу мыши. Щелкните по команде «Присвоить имя».

      Введите имя в строчку сверху.

      Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним символ «=».

      В любом из 3-х случаев Вы увидите подходящий элемент. Выбор значения из выпадающего списка Excel происходит при помощи мыши. Нажмите на него и покажется список обозначенных данных.

      Вы узнали, как сделать выпадающий перечень в ячейке Excel. Но можно создать и больше.

      Подстановка динамических данных Excel

      Если Вы добавите некое значение в спектр данных, которые подставляются в список, то в нем конфигурации не произойдет, пока вручную не будут указаны новейшие адреса. Чтоб связать спектр и активный элемент, нужно оформить 1-ый как таблицу. Сделайте вот таковой массив.

      Выделите его и на вкладке «Основная» изберите хоть какой стиль таблицы.

      Непременно поставьте галочку понизу.

      Вы получите такое оформление.

      Сделайте активный элемент, как было описано выше. В качестве источника введите формулу

      Чтоб выяснить имя таблицы, перейдите на вкладку «Конструктор» и поглядите его. Сможете поменять имя на хоть какое другое.

      Функция ДВССЫЛ делает ссылку на ячейку либо спектр. Сейчас ваш элемент в ячейке привязан к массиву данных.

      Попробуем прирастить количество городов.

      Оборотная процедура — подстановка данных из выпадающего списка в таблицу Excel, работает весьма просто. В ячейку, куда нужно вставить выбранное значение из таблицы, введите формулу:

      К примеру, если список данных находится в ячейке D1, то в ячейке, куда будут выведены избранные результаты введите формулу

      Как убрать (удалить) выпадающий перечень в Excel

      Откройте окно опции выпадающего списка и изберите «Хоть какое значение» в разделе «Тип данных».

      Ненадобный элемент пропадет.

      Зависимые элементы

      Время от времени в Excel встречается необходимость сотворения нескольких перечней, когда один зависит от другого. К примеру, любой город имеет несколько адресов. При выборе в первом мы должны получить лишь адреса избранного населенного пт.

      В этом случае дайте имя любому столбцу. Выделите без первой ячейки (наименования) и нажмите правую клавишу мыши. Изберите «Присвоить имя».

      Это будет заглавие городка.

      При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, потому что имя не может содержать пробелов, знаков подчеркивания, особых знаков и т.д.

      Потому переименуем эти городка, поставив нижнее подчеркивание.

      1-ый элемент в ячейке A9 создаем обыденным образом.

      А во 2-м пропишем формулу:


      Поначалу Вы увидите сообщение о ошибке. Соглашайтесь.
      Неувязка в отсутствии избранного значения. Как в первом списке будет избран город, 2-ой заработает.

      Как настроить зависимые выпадающие списки в Excel с поиском

      Можно применять динамический спектр данных для второго элемента. Это удобнее, если количество адресов будет расти.
      Сделаем выпадающий список городов. Оранжевым выделен именованный спектр.

      Для второго списка необходимо ввести формулу:

      Функция СМЕЩ возвращает ссылку на спектр, который сдвинут относительно первой ячейки на определенное число строк и столбцов:=СМЕЩ(начало; вниз; на право; размер_в_строках; размер_в_столбцах)

      ПОИСКПОЗ возвращает номер ячейки с избранным в первом перечне (E6) городом в обозначенной области SA:$A.
      СЧЕТЕСЛИ считает количество совпадений в спектре со значением в обозначенной ячейке (E6).


      Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском спектра для него.

      Мультивыбор

      Нередко нам нужно получить несколько значений из набора данных. Можно вывести их в различные ячейки, а можно соединить в одну. В любом случае нужен макрос.
      Нажмите на ярлычке листа понизу правую клавишу мыши и изберите команду «Просмотреть код».

      Раскроется окно разраба. В него нужно вставить последующий метод.


      Направьте внимание, что в строке

      Следует проставить адресок ячейки со перечнем. У нас это будет E7.

      Вернитесь на лист Excel и сделайте в ячейке E7 перечень.

      При выборе значения будут появляться под ним.

      Последующий код дозволит копить значения в ячейке.

      Как Вы переведете указатель на другую ячейку, Вы увидите список избранных городов. Для сотворения объединенных ячеек в Excel прочитайте эту статью.

      Мы поведали, как добавить и поменять выпадающий перечень в ячейку Excel. Возлагаем надежды, эта информация поможет для вас.

Ссылка на основную публикацию
Adblock
detector