Как сделать выпадающий список в гугл таблицах

Создание выпадающего списка в Google Таблице

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

Способ 1: Настройка проверки данных

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

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

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

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

Способ 2: Скрытие нескольких строк

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

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

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

Вариант 2: Мобильное приложение

Равно как и на веб-сайте, через официальный мобильный клиент Google Таблиц можно создавать выпадающие списки двумя способами в зависимости от требований к результату. При этом доступна данная возможность в настоящее время только в приложении для Android, тогда как версия для iOS чуть более ограничена в плане возможностей, и потому на iPhone придется использовать рассмотренный ранее сервис.

Способ 1: Настройка проверки данных

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

1. Разобравшись с созданием или открытием документа в приложении сервиса, выберите одну или несколько ячеек для создания списка и в правом верхнем углу разверните главное меню с помощью кнопки с тремя вертикально расположенными точками. Здесь в свою очередь следует выбрать категорию «Проверка данных» и первым делом отредактировать содержимое в рамках блока «Диапазон ячеек».
2. Указанные по умолчанию значения ссылаются на номер листа в рамках документа и ячейки, имеющие числовую и буквенную координаты. Чтобы с этим не было проблем, проще заранее коснуться ячейки, как было упомянуто ранее, и тогда нужные значения будут установлены автоматически.
3. Сами значения можно отредактировать в разделе «Критерии», выбрав «Значение из списка» или «Значение из диапазона». В зависимости от варианта сильно отличается процесс добавления пунктов.

Если вами было установлено «Значение из списка», для добавления новых элементов в расположенном ниже поле нажмите кнопку «+», введите нужный набор символов и воспользуйтесь иконкой галочки для сохранения. Аналогичное нужно сделать для всех остальных пунктов и по завершении на верхней панели нажать кнопку «Сохранить».

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

Способ 2: Скрытие нескольких строк

Чтобы создать список путем группировки и скрытия строк, использовать, опять же, придется официальное приложение сервиса для Android, тогда как на iOS нужные параметры отсутствуют.

1. В левой колонке выберите первую из нужных строк, растяните рамку над несколькими строками с помощью синего прямоугольника и зажмите одну из цифр до появления всплывающего меню.
2. В рамках отмеченного блока нажмите кнопку с тремя вертикально расположенными точками и в представленном меню выберите «Группа» с указанием точного диапазона строк. Если все сделано правильно, в левой колонке появится новая кнопка для работы с группой.
3. Спрятать пункты списка можно с помощью кнопки «-», тогда как повторное отображение осуществляется нажатием «+». Кроме этого, можете зажать на несколько секунд кнопку и по необходимости переместить значок в верхнюю или нижнюю часть.

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

Как создать выпадающий список в Google таблицах: полное руководство

Добавление выпадающего списка в Google таблицы может значительно упростить работу с данными. Это особенно полезно, когда вы хотите ограничить доступные варианты выбора в определенной ячейке. Шаг 1. Выберите ячейку, в которую хотите добавить выпадающий список.Шаг 2. Нажмите на кнопку ‘Данные’ в верхнем меню и выберите ‘Проверенные данные’.Шаг 3. В открывшемся окне выберите вкладку ‘Список из диапазона’.Шаг 4. Укажите диапазон ячеек, содержащих список вариантов выбора. Например, если вам нужно выбрать между ‘Красным’, ‘Зеленым’ и ‘Синим’, укажите диапазон ячеек, содержащих эти значения.Шаг 5. Нажмите на кнопку ‘Сохранить’ и закройте окно.Теперь, когда вы выбираете ячейку, в которую добавлен выпадающий список, вы увидите стрелку, при нажатии на которую появятся доступные варианты выбора. Добавление выпадающего списка в Google таблицы может значительно упростить обработку данных и избежать ошибок ввода. Следуйте этому простому руководству и добавляйте выпадающие списки в свои таблицы с легкостью.

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

Хотите сделать свой список более удобным для использования и избежать ошибок при заполнении таблицы? Тогда создайте выпадающий список! Это очень просто в Google таблицах.

1. Выберите ячейку, где вы хотите создать список.
2. Перейдите к меню ‘Данные’ и выберите ‘Проверка данных’.
3. В панели справа выберите ‘Список из диапазона’.
4. Укажите диапазон ячеек, где находятся элементы списка.
5. Нажмите ‘Сохранить’.

Готово! Теперь при нажатии на ячейку с выпадающим списком будет появляться список элементов, которые вы заранее определили.

Как настроить параметры выпадающего списка в Google таблицах

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

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

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

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

Как использовать выпадающий список в Google таблицах для удобства работы с данными

Выпадающий список в Google таблицах – это удобный инструмент, который позволяет быстро выбирать нужное значение из предложенных вариантов. Этот инструмент особенно полезен для тех, кто работает с большим количеством данных и хочет упростить процесс ввода информации.

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

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

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

Использование выпадающего списка в Google таблицах – это простой и эффективный способ упростить работу с данными и избежать ошибок при вводе.

Применение выпадающего списка в Google таблицах для фильтрации данных

Выпадающие списки – это удобный инструмент для фильтрации и сортировки данных в Google таблицах. Они позволяют быстро выбрать нужный элемент из списка и исключить ненужные данные.

Для создания выпадающего списка в Google таблицах, необходимо выбрать ячейку или диапазон ячеек, в которых будет находиться список. Затем нужно перейти на вкладку ‘Данные’ и выбрать ‘Проверка данных’.

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

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

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

Как использовать выпадающий список в Google таблицах для валидации данных

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

Как создать выпадающий список в Google таблицах? Сначала, выберите ячейку или диапазон ячеек, в которые вы хотите вставить список. Затем, перейдите на панель инструментов и выберите «Данные» -> «Допустимые значения».

В окне «Допустимые значения» выберите опцию «Список из диапазона». Затем выберите диапазон ячеек, из которых вы хотите создать список.

После того, как вы выбрали диапазон, нажмите на кнопку «Сохранить». Теперь в выбранных ячейках появится выпадающий список с выбранными вами значениями.

Вы также можете добавить новые значения в список, просто нажав на ячейку, в которой находится список и вводя новое значение.

Использование выпадающего списка в Google таблицах поможет вам сохранить данные более точными и предотвратить ошибки ввода.

Создание выпадающего списка в Google таблицах может упростить работу и сделать ее более организованной. Но как это сделать? Существует несколько хитростей, которые помогут вам создать и настроить свой список.

1. Используйте список значений из другой ячейки. Если у вас уже есть ячейка с нужными значениями, вы можете использовать ее для создания выпадающего списка. Для этого вам нужно выбрать ячейку, куда вы хотите поместить список, затем выбрать ‘Данные’ в меню и нажать на ‘Список из диапазона’. Введите диапазон ячеек с вашими значениями и нажмите ‘Готово’.

2. Используйте дополнительные параметры. В Google таблицах вы можете настроить дополнительные параметры для своего списка, такие как установка ограничений или добавление дополнительных значений. Для этого выберите ячейку с вашим списком, затем нажмите на ‘Данные’ в меню и выберите ‘Дополнительные параметры’.

3. Используйте формулы. Если у вас есть сложные условия, которые должны быть учтены в списке, вы можете использовать формулы. Например, вы можете использовать формулу IF для того, чтобы отображать разные значения в списке в зависимости от выбора пользователя.

Следуя этим советам и хитростям, вы сможете создать и настроить свой список в Google таблицах быстро и легко.

Как быстро изменить содержание выпадающего списка в Google таблицах

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

1. Выберите ячейку с выпадающим списком.

2. Нажмите на значок ‘Изменить данные проверки’.

3. В открывшемся окне вы можете добавить, удалить или изменить элементы списка.

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

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

Как добавить новый элемент в выпадающий список в Google таблицах

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

Чтобы добавить новый элемент в выпадающий список в Google таблицах, выполните следующие шаги:

1. Выделите ячейку или диапазон ячеек, в которые вы хотите добавить новый элемент.

2. Нажмите на кнопку ‘Данные’ в верхней панели инструментов.

3. Выберите пункт ‘Проверка данных’ в выпадающем меню.

4. В окне ‘Проверка данных’ выберите вкладку ‘Список из диапазона’.

5. В поле ‘Диапазон’ укажите диапазон ячеек, в которых содержится список.

6. Введите новый элемент в поле ‘Элементы списка’.

7. Нажмите на кнопку ‘Сохранить’.

Теперь новый элемент будет добавлен в выпадающий список в Google таблицах. Вы можете повторить эти шаги, чтобы добавить еще элементы в список.

Выпадающий список в Гугл Таблицах

Инструмент «Выпадающий список» незаменим в Google Таблицах, если вам нужно собирать и обрабатывать данные. Это значительно упрощает и ускоряет весь процесс, избавляя от необходимости вручную вводить повторяющиеся данные. Составить такой список очень просто.

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

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

Создание выпадающего списка

Самый простой вариант. Подходит для быстрого «сбора», например, простой формы опроса. Ну или любым другим способом, где обработка больших объемов данных не требуется. Сначала вам нужно выяснить, как создать таблицу Google, а затем как сделать выпадающий список в одной или нескольких ячейках в таблице Google:

• В окне «Проверка данных» настройте значения в соответствии со следующей таблицей
• Используя левую кнопку мыши (ЛКМ), щелкните нужную ячейку или выберите несколько вниз за раз.
• Щелкните правой кнопкой мыши выбранную область (ПКМ) и выберите в меню «Проверка данных”.

• Нажмите «Сохранить.

Готовый. Теперь вы знаете, как создать раскрывающийся список в Google Таблицах.

• Показать раскрывающийся список в ячейке. Если оставить этот флажок установленным, значок будет прикреплен к ячейке. Если удалить — и список отобразится двойным щелчком ЛКМ.
• Значения вне допустимого диапазона. Перейдите на «Значение списка» и введите требуемые значения.

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

Читайте также: как исправить верхнюю строку в Google Таблицах

Еще о работе с выпадающим списком

Мы узнали, как сделать выпадающий список в Google Таблицах. Остается упомянуть еще несколько вариантов конфигурации, доступных для использования. В окне «Проверка данных» в строке «Правила» можно выбрать следующие настройки:

• Дата — допустимая дата (такая же, до, после, указанная или ранее и т.д.) для обозначения даты.
• Число в диапазоне (Не в диапазоне, Больше чем, Больше или равно, Меньше, Меньше или равно и т.д.) дведите числа.
• Текст содержит (не содержит, равно, является действительным URL / адресом электронной почты) введите желаемый текст.

Обратите внимание: ячейки могут быть выделены разными цветами (и в зависимости от содержимого, в т.ч для этого выделите одну или несколько ячеек правой кнопкой мыши, выберите «Условное форматирование» и в форме справа назначьте цвет выделение правил.

Связанные выпадающие списки

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

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

// Имя рабочего листа var tsheet = ‘Результат’; // заменяем на имя листа, на котором должна выполняться проверка данных // проверяем диапазон: var rownum = 100; // строка, на которой закончится проверка var vcol = 2; // номер столбца (не буква), который отмечен // диапазон с условиями var ccols = 3; // номер столбца (не буква), с которого начинается список условий var clen = 10; // сколько условий учитывается, максимум //…………………………………………………………………….. for (var i = 2; i // Устанавливаем проверку данных var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName (таблица); var cell = sh.getRange (i, vcol, 1, 1); var range = sh.getRange (i, ccols, 1, clen); var rule = SpreadsheetApp.newDataValidation () requireValueInRange (диапазон) .build(); cell.setDataValidation (правило); >

Обратите внимание: между знаками «//» есть пояснительная информация; можно оставить в покое, на работоспособность скрипта это не влияет. Просто измените числа рядом с аннотациями, чтобы создать связанное раскрывающееся меню в электронной таблице Google.

Подготовка таблицы

Во-первых, вам нужно открыть новый документ в Google Таблицах и создать в нем еще один лист («+» в нижнем левом углу окна). Переименуйте лист 1 в «Результат», а лист 2 в «Данные”.

Первый этап — работа с таблицей «Данные». Как сделать выпадающий список в ячейке в Google Таблицах:

1. В ячейке A1 введите название / характеристику первого уровня списка. Начиная с A2, введите соответствующие значения. Каждого должно быть столько повторений, сколько существует для него значений второго уровня.
2. В ячейке D1 указываем название / характеристику первого уровня списка. Начиная с D2, введите соответствующие значения (по одному).

Все выглядит так:

/>Второй этап, работа с листом «Результат”:

• В окне «Проверка данных» первую строку не меняем. Ставим курсор в поле справа от «Диапазон значений», переходим на лист «Данные», выбираем значения столбца D, начиная с D2 + на столько пустых ячеек вниз, сколько нужно. Хорошо Сохранить.
• Перейдите на лист «Результат» и выберите интересующий диапазон ячеек. В данном случае от А2 до А13 включительно.
• Щелкните правой кнопкой мыши выделенное поле и выберите в меню «Проверка данных”.

Что мы видим перед нажатием кнопки «Сохранить”:

Подготовка окончена. Осталось прикрепить скрипт к таблице.

Прикрепление скрипта

Здесь все просто:

• Ждем несколько секунд, чтобы скрипт поработал над таблицей первого листа (у нас есть этот «Результат», данные будут извлечены и появятся выпадающие списки.
• Нажмите кнопку «Инструменты» и выберите в меню «Редактор сценариев”.
• Откроется окно редактирования. Вставляем в него приведенный выше скрипт и редактируем на свое усмотрение.
• На верхней панели нажмите «Выполнить». После этого система выдаст серию предупреждений и разрешительных запросов: мы выдаем разрешения.

Далее смотрим на результат: если что-то нас не устраивает, мы возвращаемся в редактор скриптов и исправляем в нем параметры проверки данных.

Выпадающий список уникальных значений. Автоматическое обновление выпадающего списка

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

• Список адресов в разных городах

Задача:

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

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

Скачать файлы из этой статьи

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

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

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

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

Выпадающий список готов!

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

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

Теперь выбираем ячейку для второго выпадающего списка, того, который будет зависимым. Переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем функцию: =ДВССЫЛ(D2) , где D2 – это адрес ячейки с первым выпадающим списком городов.

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

Зависимый выпадающий список адресов готов.

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ , обработаем значения функцией ПОДСТАВИТЬ .

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»-«;»_»))

То есть, мы проводим предварительную обработку значений, чтобы они соответствовали правилам написания имён. Если в названии города имеются дефисы, они будут заменены на нижнее подчеркивание.

Теперь зависимый выпадающий список работает и для города, содержащего в названии дефисы – Ростов-на-Дону. Вернемся к выпадающему списку городов.

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

Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T .

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

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

Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».

И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17

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

В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .

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

Из списка уникальных городов создадим именованный диапазон (мы назвали его — «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.

«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18 , задаем имя – =Уникальные

Как видим, список уникальных значений мы получили, но в придачу у нас остались совершенно ненужные пустые строки из таблицы.

Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;»-«;»_»)) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ , которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ( $B$2:$B$18 )

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ , которая подсчитывает количество встретившихся в диапазоне ( $B$2:$B$18 ) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

С выпадающими списками в Google таблицах все немного иначе.

Выпадающий список в Google таблицах

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

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

«Данные» – «Настроить проверку данных» – «Значение из диапазона»

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

Зависимый выпадающий список в Google таблицах

Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

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

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»_»;»-«))

Последний штрих в создании зависимого выпадающего списка, в разделе «Настроить проверку данных», в качестве диапазона указываем список из столбца F:F.

При дальнейшей работе вспомогательный столбец F можно скрыть. Минус такого метода – отсутствие динамичности. Если мы добавим новый город и адрес, то они не появятся в созданных выпадающих списках. Но это решаемо!

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

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A . Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

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

Для того, чтобы зависимый выпадающий список автоматически обновлялся с добавлением новых данных, воспользуемся функцией СМЕЩ .

В ячейке G6 укажем:

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

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

Заключение

Теперь Вам известны несколько способов, как создать выпадающие списки в Excel и Google таблицах. Смотрите примеры и создавайте нужные Вам выпадающие списки.

Изучить работу в программе Excel Вы можете на наших курсах: бесплатные онлайн-курсы по Excel

Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: пройти бесплатный тест