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

Как сделать справочник в гугл таблицах

  • автор:

Выпадающий список в Гугл Таблицах — 2 лучших способа создания!

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

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

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

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

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

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

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

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

  • Значения из диапазона. Меняем на “Значение из списка” → вводим необходимые значения.
  • Показывать раскрывающийся список в ячейке. Если оставить флажок — на ячейке закрепится значок . Если убрать — и список будет отображаться по двойному щелчку ЛКМ.

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

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

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

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

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

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

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

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

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

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

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

Сначала нужно открыть новый документ в Гугл Таблицы и создать в нем еще один лист (“+” в левом нижнем углу окна). Переименовать Лист 1 в “Результат”, а Лист 2 в “Данные”.

Первый этап — работа с листом “Данные”. Как в Гугл Таблицах сделать выпадающий список в ячейке:

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

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

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

  • Переходим к листу “Результат” и выделяем интересующий диапазон ячеек. В данном случае — от А2 и до А13 включительно.
  • Кликаем ПКМ по выделенному полю и в меню выбираем “Проверка данных ”.
  • В окне “Проверка данных” первую строку не изменяем. Ставим курсор в поле правее “Значения из диапазона” → переключаемся на лист “Данные” → выделяем значения из столбца D, начиная с D2 + столько пустых ячеек вниз, сколько нужно → Ок → Сохранить.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

google spreadsheetsЯ довольно активно использую различные элементы Google Docs, в частности google spreadsheet – гугл.таблицы (аналог Excel). Обо всех возможностях Google таблицах я рассказывать не буду, так как их очень много. Мне там почти всё нравится.

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

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

Что хочу получить?

Шаг 0

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

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

Создаем справочник с необходимыми значениями

Тут всё просто – создаем новый лист (я назвал его справочники, так как справочников может быть много), и добавил туда перечень значений, которые должны будут использоваться в выпадающем списке:

Шаг 1

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

Шаг 2

Переходим в пункт меню Google таблиц «Данные -> Настроить проверку данных»

Шаг 3

В диапазон ячеек уже записаны выделенные ранее ячейки.

Добавляем правило

В поле «Правило» добавляем значение «Значения из диапазона» (устанавливается по умолчанию), и указываем диапазон – нажимаем на кнопку с решеткой и указываем диапазон значений из нашего справочника:

Шаг 4

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

Шаг 4_2

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

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

Теперь наш основной лист будет выглядеть следующим образом:

Шаг 5

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

Шаг 5_2

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

Шаг 5_3

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

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

Шаг 5_4

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

Вот теперь хорошо запомнил эту информацию и далее буду уже на автомате её использовать. И другим, надеюсь, это тоже поможет.

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

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

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

Способ 1: Выбор диапазона данных

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

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

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

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

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

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

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

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

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

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

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

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

Способ 2: Создание списка значений

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

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

Раскройте список правил и кликните по «Значение из списка».Выбор правила для создания раскрывающегося списка в Google Таблицах

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

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

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

Полезные настройки

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

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

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

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

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

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

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

Ваш адрес email не будет опубликован.