Выпадающий ⚠️ список в Гугл таблицах: как сделать с возможностью выбора

Google Sheets – это бесплатная онлайн-версия Excel, которая пригодится для обмена, редактирования на ходу и работы с облегченными электронными таблицами.

Введение

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

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

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

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

Задача:

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

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

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

Рабочие файлы по ссылке ниже

Ссылка на файлы

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

Разрешение множественного выбора в раскрывающемся списке (с повторением)

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

271790eec074ad9f0665d30c283c3dc4.gifЧтобы создать раскрывающийся список, допускающий множественный выбор, вам нужно сделать две вещи:

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

Давайте подробно рассмотрим каждый из этих шагов.

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

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

ffd7ed9cb684920b2edade01660c0b81.gifНиже приведены шаги для этого:

Теперь выпадающий список появится в выделенной ячейке (в данном примере C1). Когда вы нажмете на стрелку, вы увидите свой список опций.

Обратите внимание, что вам разрешено выбирать только один вариант за раз.

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

Добавление скрипта Google Apps для включения множественного выбора

Ниже приведен код сценария, который вам придется скопировать и вставить в редактор сценариев (шаги, указанные ниже в разделе после кода):

function onEdit(e) {var oldValue;var newValue;var ss=SpreadsheetApp.getActiveSpreadsheet();var activeCell = ss.getActiveCell();if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()==”Sheet1″) {newValue=e.value;oldValue=e.oldValue;if(!e.value) {activeCell.setValue(“”);}else {if (!e.oldValue) {activeCell.setValue(newValue);}else {activeCell.setValue(oldValue+’, ‘+newValue);}}}}

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

Теперь вернитесь к рабочему листу и попробуйте выбрать несколько вариантов в раскрывающемся списке. Например, сначала выберите Apple, а затем выберите Banana.

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

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

b1f5c1c80f4519a7b85dfcaeb005331a.gifТакже обратите внимание, что с помощью этого кода он позволит вам выбрать один и тот же элемент дважды. Например, если вы выберете Apple, а затем снова выберете Apple, он дважды отобразит это в ячейке.

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

Как работает код?

Попробуем разобраться в этом коде по частям.

Код начинается со строки

function onEdit(e)

onEdit () — это специальная функция в Google Таблицах. Она также известна как обработчик событий. Эта функция запускается каждый раз при изменении вашей электронной таблицы.

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

Теперь AppScript передает эту функцию как объект события в качестве аргумента. Обычно объект события называется e. Этот объект события содержит информацию о инициированном событии.

Если вы знакомы с основами AppScript, вы обнаружите, что первые четыре строки довольно легко понять:

var oldValue; var newValue; var ss = SpreadsheetApp.getActiveSpreadsheet (); var activeCell = ss.getActiveCell ();

Я объявил две переменные — одну (oldValue), которая будет содержать старое значение ячейки, и другую (newValue), которая будет содержать новое значение ячейки.

Переменная activeCell будет содержать текущую активную ячейку, которая была отредактирована.

Теперь мы не хотим, чтобы код запускался каждый раз при редактировании какой-либо ячейки. Мы хотим, чтобы он запускался только при редактировании ячейки CA1 Sheet1. Поэтому мы убеждаемся в этом, используя оператор if:

if (activeCell.getColumn () == 3 && activeCell.getRow () == 1 && ss.getActiveSheet (). getName () == “Sheet1”)

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

Код в операторе IF выполняется только при соблюдении всех этих трех условий.

Ниже приведен код, который выполняется, когда мы находимся в правой ячейке (C1 в нашем примере).

newValue = e.value; oldValue = e.oldValue;

e.oldValue также является свойством объекта события, e. Это содержит предыдущее значение активной ячейки. В нашем случае это будет значение до того, как мы сделаем выпадающий выбор.

Мы хотим присвоить это переменной oldValue.

e.value — это свойство объекта события, e. В нем хранится текущее значение активной ячейки. Мы хотим присвоить это переменной newValue.

Во-первых, давайте посмотрим, что произойдет, если не выбран ни один из вариантов. В этом случае e.value будет неопределенным. Когда это происходит, мы не хотим, чтобы в ячейке A1 отображалось что-либо. Поэтому мы помещаем в ячейку пустое значение.

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

if(! e.value) { activeCell.setValue (“”); }

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

Это означает, что e.oldValue не определено. Когда это происходит, мы хотим, чтобы в ячейке A1 отображался только выбранный параметр (newValue).

if (! e.oldValue) { activeCell.setValue (newValue);

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

else { activeCell.setValue (oldValue + ‘,’ + newValue); }

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

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

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

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

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

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

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

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

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

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

  • Жмем на сохранить.

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

Настраиваем окно “Проверка данных” Что делать
Диапазон ячеек Это поле не трогаем
Правила
  • Значения из диапазона. Меняем на “Значение из списка” → вводим необходимые значения.
  • Показывать раскрывающийся список в ячейке. Если оставить флажок — на ячейке закрепится значок . Если убрать — и список будет отображаться по двойному щелчку ЛКМ.
Для неверных данных Оставить “Показывать предупреждение” или изменить на “Запрещать ввод данных”,* чтобы создать выпадающий список в Гугл Таблице.
Оформление Поставить флажок на “Показывать текст справки”. Откроется поле с предупреждением по умолчанию. Вы можете заменить его на свой текст.

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

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

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

С тем, как сделать в Гугл Таблицах выпадающий список, мы разобрались. Осталось упомянуть еще несколько вариантов настроек, доступных для использования. В окне “Проверка данных”, в строке “Правила”, вы можете выбрать следующие настройки:

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

Обратите внимание: ячейки можно подсвечивать разными цветами (и в зависимости от содержимого в том числе. Для этого выделите ПКМ одну или несколько ячеек, выберите “Условное форматирование” и в форме справа назначьте правила выделения цветом.

Что такое и зачем нужен раскрывающийся список в Google таблице?

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

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

Кроме того, все время вручную вводить повторяющиеся данные – это просто потеря времени.

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

Список – это перечень определённых значений, из которых вам при заполнении ячейки необходимо выбрать только одно.

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

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

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

Самым простым вариантом здесь является выбор из двух значений – «да» и «нет».

Для этого используют чекбокс.

Сейчас мы с вами рассмотрим, как это правильно сделать.

Встроенная опция

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

Как же сделать выпадающий список в Гугл Таблицах, какими инструментами нужно овладеть? Без лишних предисловий приступаем! Работать будем в Документах, переходите сразу на страницу сервиса. А еще можете открыть свой Диск, нажать там на кнопку «Создать» и подготовить нужный файл – или найти его в хранилище данных.

Являетесь активным пользователем почты Gmail, но столкнулись с трудностями? Тогда эта статья по ссылке для вас!

  • Найдите в документе нужные ячейки и выделите их курсором;

  • Теперь на верхней панели ищем вкладку «Данные»;

  • Нажмите, чтобы открыть дополнительное меню;
  • Кликайте по строчке «Настроить проверку данных».

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

  • Значение из диапазона (просто впишите диапазон в окно рядом)
  • Значение из списка (введите в появившееся поле значения через запятую)

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

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

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

Найдите строку «Для неверных данных». Здесь вам опять доступно два варианта действий – выберите, что будет происходить при вводе неправильной информации в ячейку (просто отметьте нужный вариант в чекбоксе).

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

Чуть ниже – поле «Оформление». В выпадающем списке в Гугл Таблице оно используется для указания справочной информации.

  • Сначала включите опцию «Показывать текст справки для проверки данных»;

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

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

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

Уйти на фриланс легко! Все биржи для новичков в источнике!

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

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

Бонус! Вы уже знаете, как создать выпадающий список в Гугл Таблицах – интересно ли вам научиться подсвечивать разные ячейки разными же цветами, в зависимости от типа вводимых в них данных?

Этот инструмент упростит жизнь и работу еще сильнее! Давайте выделим нужный диапазон курсором, а потом:

  • Найдите на верхней панели вкладку «Формат»;

  • Пролистайте открывшийся список до вкладки «Условное форматирование».

Теперь назначаем правило условного форматирования.

  • Сначала уточняем нужный диапазон ячеек;

  • Затем выбираем правило из списка (нажмите на это поле, чтобы посмотреть все доступные варианты);

  • Выберите цвет и шрифт – можно воспользоваться вариантом по умолчанию или поменять настройки на пользовательские;

  • Не забудьте сохранить внесенные изменения.

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

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

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

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

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

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

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

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

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

Как сделать

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

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

Шаг 1

Источник: avatars.mds.yandex.net

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

Шаг 2

Источник: avatars.mds.yandex.netа

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

Шаг 3

Источник: avatars.mds.yandex.net

Шаг 4

Источник: avatars.mds.yandex.net

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

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

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

Шаг 5

 

Шаг 6

Источник: avatars.mds.yandex.net

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

Итог

Источник: avatars.mds.yandex.net

Как скопировать список из одной части таблицы в другую

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

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

Что делать? Повторять снова процедуру, которую мы описывали выше? Не нужно – всё гораздо проще.

Можно скопировать ячейки со списком туда, где они необходимы, при помощи известной нам комбинации клавиш Ctrl + C (копировать) и Ctrl + V (вставить).

Установите курсор на ячейку, которую нужно копировать, и нажмите Ctrl + C.  Далее поставьте курсор в нужную ячейку и нажмите Ctrl + V.
И так можно повторять необходимое количество раз.

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

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

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

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

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

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

Вспомогательный столбец включен в диапазон умной таблицы Excel

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

=ЕСЛИОШИБКА(ИНДЕКС([Город];ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$1:A1; [Город]);0));””)

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

Формула массива для подсчета уникальных значений

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

Автоматическое добавление новых уникальных значений

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

Именованный диапазон для уникальных значений

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

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

Лишние пустые строки в выпадающем списке

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

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

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

Убираем лишние пустые строки в выпадающем списке функцией СМЕЩ

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

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

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

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

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

  1. выделите ячейки, где необходимо удалить чекбоксы (не обязательно это будут все ячейки, содержащие списки!)
  2. Переходим по уже знакомому нам пути в Меню -> Данные -> Проверка данных
  3. Нажимаем кнопку “Удалить проверку” в появившемся окне.

удаляем список из google таблицы

Все готово! Все выделенные вами списки удалены полностью.

При этом остальные ячейки со списками значений остались в целости и сохранности.

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

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...