Как сделать ниспадающий список в гугл таблицах
Перейти к содержимому

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

  • автор:

Настройка выпадающего списка в Google Docs

Разбираемся, для чего нужны списки в Гугл Таблицах. А также наглядно покажем, как их делать по пунктам.

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

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

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

Как сделать

Чтобы составить выпадающий список, нужно выполнить ряд определенных условий.

1. Создаем ячейки с нужными названиями, выбираем поле, в котором будем создавать перечисление. Затем кликаем на кнопку «Данные».

Шаг 1

2. Выбираем пункт «Проверка данных…».

Шаг 2

3. Далее всплывает новое окно с настройками. В поле «Правила» вставляем «Значение из диапазона».

Шаг 3 Шаг 4

4. Рядом выбираем диапазон для подстановки значений.

Здесь же у нас есть возможность настроить контроль проверки заполнения полей, отображение и содержимое справки.

5. Выделяем нужное и жмем на кнопку «Сохранить».

Шаг 5 Шаг 6

Готово. Вы справились.

Итог

Условное форматирование

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

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

1. Выделяем весь диапазон прямоугольников.

Форматирование 1

2. Кликаем на кнопку «Формат» => «Условное форматирование». Справа всплывет окно, в котором мы видим ниже «Форматирование ячеек». Выбираем «Текст содержит» и еще ниже в другом поле вводим один из наших вариантов. Например, овощи. Так, все прямоугольники с названием «Овощи» окрасились в зеленый цвет. Мы можем выбирать различные цветовые решения, кликнув на иконку с ведерком.

3. Жмем «Готово» и наслаждаемся получившейся радугой прямо на экране.

Форматирование 2

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

Удачной работы в Google Таблицах.

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

Как в Гугл Таблице сделать выпадающие списки?

Максим Волошин

Вы можете добавить выпадающий список в любую ячейку Google Sheets. Вот как это сделать.

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

  1. Откройте файл в Google Таблицах.
  2. Выделите нужные ячейки.
  3. Нажмите Данные и затем Настроить проверку данных.
  4. Выберите вариант в пункте Правила:
    Значения из диапазона. Укажите ячейки, которые нужно включить в список.
    Значение из списка. Введите пункты списка через запятую, не разделяя их пробелами.
  5. В ячейках появится значок в виде направленной вниз стрелки. Если вы хотите его убрать, снимите флажок Показывать раскрывающийся список в ячейке.
  6. Если в ячейку с раскрывающимся списком ввести значение, которого нет в списке, появится предупреждение. Вы можете запретить вводить значения не из списка, выбрав вариант Запрещать ввод данных в разделе Для неверных данных.
  7. Нажмите Сохранить. В ячейках появится раскрывающийся список. Если вы хотите, чтобы цвет ячеек менялся в зависимости от значений в них, используйте условное форматирование.

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

YouTube

Свяжите сервисы между собой без программистов за 5 минут!

Как настроить выгрузку данных из U-ON.Travel в виде уведомлений в Телеграм?

Как настроить выгрузку данных из U-ON.Travel в виде уведомлений в Телеграм?

Подключение JotForm

Подключение JotForm

Не забудьте поделиться этой публикацией!

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

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

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

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

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

Задача:

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

Визуализация задачи

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

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

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

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

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

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

Указываем диапазон с данными для выпадающего списка

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

Простой выпадающий список готов

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

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

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

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

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

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

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

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

Пример создания именованного диапазона

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

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

Ошибка в создании именованного диапазона

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

Корректное имя для названия с дефисами

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

Именованные диапазоны созданы

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

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

Проверка данных. Функция ДВССЫЛ

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

Зависимый выпадающий список функцией ДВССЫЛ

Зависимый выпадающий список функцией ДВССЫЛ (2)

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

Для города, в названии которого содержатся дефисы, выпадающие списки пока не отражаются

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

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

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

Выпадающий список для города, в названии которого содержатся дефисы, после обработки функцией ПОДСТАВИТЬ

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

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

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

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

Создаем умную таблицу Excel

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

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

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

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

Создаем вспомогательный столбец

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

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

Вспомогательный столбец включен в диапазон умной таблицы 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 таблицах есть аналогичный инструмент для создания выпадающих списков – «Проверка данных».

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

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

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

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

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

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

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

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

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

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

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

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

Ошибка при введении некорректного имени

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

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

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

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

Функция ДВССЫЛ в действии

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

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

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

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

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

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

Автоматическое обновление выпадающего списка

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

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

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

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

Функция СМЕЩ для зависимого выпадающего списка

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

Задаем диапазон для зависимого выпадающего списка

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

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

Скрыли вспомогательные столбцы

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

Заключение

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

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

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

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

  • 20 июля 2021
  • 38925
  • MS Excel Video File Google Sheets

Google Таблицы: 5 лайфхаков для эффективной работы

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

Формулы Google Таблиц не уступают этому инструменту в Excel. Некоторые могут работать с интернетом, поскольку это изначально веб-инструмент.

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

Горячие клавиши в Google Таблицах

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

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

Можно найти каталог горячих клавиш в меню самих таблиц. Нажмите в верхнем меню «Справку» и выберите «Быстрые клавиши».

google sheets hot keys

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

google sheets hot keys

Здесь приведем несколько примеров:

  • F4 — повтор последнего действия;
  • Ctrl + X — вырезать;
  • Ctrl + C — копировать;
  • Ctrl + V — вставить;
  • Ctrl + Home — возвращение в начало листа;
  • Ctrl + Backspace — переход к активной ячейке;
  • Ctrl + K — вставка ссылки.

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

Как сделать выпадающий список

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

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

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

google sheets

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

Условное форматирование Google Таблицы

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

Чтобы задать формат в Google Таблице перейдите в верхнем меню в «Формат», затем выберите «Условное форматирование». Затем работайте в правой панели инструментов, чтобы задать нужные правила форматирования.

google sheets

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

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

Чекбоксы

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

Включенный чекбокс в ячейке означает «ИСТИНА», выключенный — «ЛОЖЬ». В зависимости от состояния чекбокса можно задавать условное форматирование ячеек. Например, при включенном флажке строка окрасится в определенный цвет или текст в ней перечеркнется.

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

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

Вставить чекбоксы в ячейки Google Таблицы можно, нажав в верхнем меню «Вставка», а затем выбрать «Флажок».

google sheets

Чекбоксы можно в дальнейшем форматировать, как и любые другие ячейки.

Работа с изображениями

В Google Таблицах можно работать не только с цифрами. Вы можете сделать каталог визуальных материалов или использовать логотипы в списке компаний или продуктов. Для этого есть опция загрузки изображений прямо в ячейки. Требования к таким изображениям: размер до 50 Мб, формат JPEG, PNG или GIF.

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

google sheets

Какие есть опции в работе с изображениями в Google Таблицах :

  • можно вставить изображение внутрь ячейки и форматировать его там, а можно использовать вставку поверх ячейки — тогда вы сможете свободно его передвигать, сжимать и растягивать поверх сетки;
  • картинки можно загружать с компьютера, с сайта в интернете;
  • можно «подгонять» картинку под ячейку: масштабировать и растягивать, задавать произвольный размер. Для этого используйте функцию Image.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *