Как вы делаете динамические / зависимые выпадающие списки в Google Таблицах?

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

Введение

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

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

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

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

Задача:

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

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

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

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

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

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

Шаг 1. Скачай себе файл с примером

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

  1. Базовая версия (описана в видео Youtube-icon_small.png )
  2. Новая версия — доработанная и улучшенная.

Совет. Эти файлы открыты для просмотра, но не для редактирования. Поэтому сразу создай свою копию: меню Файл → Создать копию.  Далее об этом я расскажу подробнее.

Сравни 2 версии скрипта:

Базовая версия

Новая версия
средняя оценка пользователей ★★★☆☆ – 3,4 ★★★★★ – 4,7
оценить оценить
Среднее время выполнения в секундах 0,3 0,4
бесплатно и не для продажи
Легко увеличить к-во связанных выпадающих списков
автоматически заполняет единственное значение
Работает с дублями
Не нужно сортировать исходную таблицу
Работает с числами и с текстом
Работает с дробными десятичными числами
функция “умного” удаления
Работает с датами

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

# ? =

Вопросы + Ответы

01 ? =

Быстродействие

timer.png

Сразу возникает вопрос: а каков максимальный объем данных можно загрузить на лист с данными? Есть ли предел, выше которого скрипт работать уже перестанет?

Ответ, разумеется, предел есть! Но он зависит не от скрипта, а от скорости работы табличек в целом. Я рекомендую использовать скрипт с данными:

  •  не более 5 000 строк для максимально комфортного использования.

    Так ты не почувствуешь, что скрипт заметно тормозит. 

Можно и больше. Но всё зависит от мощности твоего компьютера. К тому же, верю, в будущем сервера и технологии табличек будут становиться всё быстрее.Я тестировал скрипт с табличкой в 200 000 строк. И он по-прежнему работал, хотя и медленно. Жду теперь твои результаты тестов скорости.

02 ? =

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

canstock4678825.jpg

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

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

03 ? =

Где находится скрипт

matt-icons_text-x-generic-script_small.png

Настройка скрипта вообще не составит тебе труда.

Для начала зайди в место, где этот скрипт находится. Для этого можно нажать меню «Инструменты → редактор скриптов». А еще, если ты пользуешься браузером Chrome, ты можешь воспользоваться сочетанием клавиш:

  • [Alt + T + E]которое можно запомнить по первым буквам названия меню на английском Tools → Editor.

Совет: используй разные сочетания клавиш в табличках Google
(и не только). Это значительно ускорит тебе работу.

Но продолжим. Когда ты зайдешь в редактор скриптов, ты там увидишь текст моего скрипта. Он длинный:

script.png

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

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

04 ? =

Что конкретно менять в скрипте

R.O.B.%253B_Robot%2528Clear%2529.png

Итак, ты уже открыл редактор скриптов и хочешь поменять настройки «под себя». Там так и будет написано: «Изменить настройки». Давай разберёмся по пунктам.

var TargetSheet = ‘Основной’ // имя листа, на котором настроены выпадающие списки↑ Вместо слова «Основной» вставь имя рабочего листа, на котором ты будешь делать связанные выпадающие списки.

var LogSheet = ‘Данные’ // имя листа с данными для списка↑ Создай лист с исходными данными для списка, а его название впиши в скрипт.

var NumOfLevels = 4 // количество уровней выпадающего списка↑ В нашем примере 4 связанных выпадающих списка. Если нужно, замени их количество на свое, впиши вместо 4 свое число.

var lcol = 2; // номер колонки слева, с которой начинается первый список; // A = 1, B = 2, etc.↑ Впиши номер колонки, с которой начинается твоя таблица.

var lrow = 2; // номер строки, начиная с которой срабатывает список↑ Шапка таблицы не должна затрагиваться скриптом, поэтому впиши сюда номер строки, с которой начинаются твои данные.

05 ? =

Как приготовить данные

ysicon_small.png

Данные формируются на отдельном листе. В моем примере имеется 4 уровня вложенных списков:

  • Планета → Страна → Континент → Город. 

При желании можно добавить еще парочку в конце:

  • Район → Улица, 

или в начале:

  • Вселенная → Галактика.

Как тебе сделать свой список? Просто сделай его по примеру моего:

myData.png
  1. Пропиши имена категорий-уровней таблицы в первой строчке, начиная с клетки A1.
  2. Начни заполнять с последнего уровня: выпиши все возможные варианты.
  3. Двигаясь к первому уровню, вводи все значения, не оставляя пустых ячеек. Некоторые названия придётся повторить, это необходимо для правильной работы скрипта.
  4. Когда данные будут готовы, можно двигаться дальше. В будущем, их можно пополнять новыми данными.

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

  • под ними ничего не записывай, 
  • ячейки в них не объединяй.

06 ? =

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

check_list_icon_by_jhewitt86-d3dybrl.png

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

Для получения списка уникальных значений, используй формулу:
= UNIQUE(A2:A)

Она очень простая и выдает список уникальных значений. Эту формулу рекомендую написать на 4 колонки правее основных данных:

myData1.png

Либо вообще размести ее на новом листе:
= UNIQUE(Данные!A2:A)

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

Совет. При выборе данных для выпадающего списка ты можешь учесть необходимость в добавлении новых значений в дальнейшем. Если данные содержатся в диапазоне A1:A20, то ты можешь выбрать бо́льший диапазон для проверки данных: A1:A100. Пустые ячейки будут игнорироваться.

После этой настройки связанные выпадающие списки уже можно использовать! Теперь поговорим о приятных деталях.

07 ? =

Как увеличить количество связанных списков

untitled2_11.jpg

Об этом я уже упомянул ранее. Для этого зайди в тело скрипта (Инструменты → редактор скриптов) и поменяй там одно число:

var NumOfLevels = 4 // количество уровней выпадающего списка↑ В нашем примере 4 связанных выпадающих списка. Вместо 4 поставь свое число.

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

Как видишь, это сделать очень легко.

08 ? =

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

science-chemistry-icon.png

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

Например, мы выбрали часть света = “Азия”. В справке “Азии” соответствует только одна страна = “Китай “, а для “Китая” найден лишь один город = “Пекин”. Вот, что произойдет при выборе “Азии”: скрипт автоматически вставит “Китай” и “Пекин” в нужные ячейки. Правда, удобно?

%25D0%2593%25D0%25B8%25D1%25842.gif

09 ? =

Как скрипт работает с дублями

Ball-2-icon.png

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

Хорошая новость! В текущей версии скрипта списки могут содержать дубли, они будут нормально считываться.

10 ? =

Нужно ли сортировать исходную таблицу

freshiconpacks7.jpg

Нет. Твои исходные данные могут быть сортированы в произвольном порядке. Это может быть удобно. Добавляя новые данные тебе не нужно беспокоиться о порядке строк. К тому же легче автоматизировать процесс.

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

Работа с другими типами данных

Alienicon42.png

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

Если у тебя возникает проблема при работе с числами, обязательно напиши мне об этом в комментариях!

11 ? =

Про функцию «умного» удаления

wmicon.png

Мне надоели остатки старых кусков проверки данных, которые появляются, когда я хочу удалить что-то в своих списках. Поэтому я настроил это:

%25D0%2593%25D0%25B8%25D1%25843.gif

Во-первых, как видишь, можно автоматически копировать часть списков, а остальные подтянутся. Ну и, выбрав первый уровень списков и нажав [Delete], ты можешь

  • удалить все следы списков, которые были правее.

12 ? =

Лучше один раз увидеть

popcorn-icon_small.jpg

Это видео немного устарело, но описание в нем вполне подойдет и для последней версии скрипта:

13 ? =

Обратная связь

old-fashioned-phone2.jpg

Жду комментариев.

Наиболее частым вопросом пользователей является:

  • Как сделать несколько связанных списков одновременно?

Сделал файл для таких случаев:

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

5 ответов

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

вы можете настроить выпадающий первый столбец через обычные данные > подсказки меню проверки.

Главная Страница

Main Page with the drop down for the first column already populated.

Выпадающая Исходная Страница

Source page for all of the sub-categories needed

после этого вам нужно настроить скрипт на имя onEdit. (Если вы не используете это имя, getActiveRange () не будет делать ничего, кроме возврата ячейки A1)

и используйте код, указанный здесь:

function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = SpreadsheetApp.getActiveSheet(); var myRange = SpreadsheetApp.getActiveRange(); var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Categories”); var option = new Array(); var startCol = 0; if(sheet.getName() == “Front Page” && myRange.getColumn() == 1 && myRange.getRow() > 1){ if(myRange.getValue() == “Category 1”){ startCol = 1; } else if(myRange.getValue() == “Category 2”){ startCol = 2; } else if(myRange.getValue() == “Category 3”){ startCol = 3; } else if(myRange.getValue() == “Category 4”){ startCol = 4; } else { startCol = 10 } if(startCol > 0 && startCol < 10){ option = dvSheet.getSheetValues(3,startCol,10,1); var dv = SpreadsheetApp.newDataValidation(); dv.setAllowInvalid(false); //dv.setHelpText(“Some help text here”); dv.requireValueInList(option, true); sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build()); } if(startCol == 10){ sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations(); } }}

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

Trigger set up

вы должны быть хорошо идти после этого!

Примечание

скрипты имеют ограничение: он обрабатывает до 500 значений в одном раскрывающемся списке.

сценарий был выпущен в январе 2018. Пожалуйста, смотрите:

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

улучшения:

  1. скорость
  2. обрабатывает несколько правил в 1 листе
  3. связать другие листы в качестве исходных данных.
  4. пользовательский порядок столбцов раскрывающихся списков

версии скрипта

  1. В. 1.
  2. В. 2. 2016-03. Улучшено: работает с дубликатами в любом категория. Например, если у нас есть list1 с моделями автомобилей и list2 с цветами. Цвет можно повторить в любой модели.
  3. В3. 2017-01. Улучшено: нет ошибки при вводе единственного значения.
  4. новая версия: 2018-02. См.вот эту статью.

это решение не идеально, но оно дает некоторые преимущества:

  1. позвольте вам сделать несколько выпадающих списки
  2. дает больше контроля
  3. исходные данные размещаются на единственном листе, поэтому их легко редактировать

прежде всего, вот пример работающего, так что вы можете проверить его, прежде чем идти дальше.

When you choose one option, script makes new validation rule

мой план:

  1. Подготовка Данных
  2. сделать первый список как обычно: Data > Validation
  3. добавить скрипт, установить некоторые переменные
  4. готово!

Подготовка Данных

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

Sourse Data

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

первая простая проверка данных (DV)

подготовьте список уникальных значений. В нашем примере это список планеты. Найдите свободное место на листе с данными и вставьте формулу:=unique(A:A)На главном листе выберите первый столбец, с которого начнется DV. Перейдите в раздел Данные > проверка и выберите диапазон с уникальным списком.

4 columns right from data

скрипт

вставить этот код в Редакторе скриптов:

function SmartDataValidation(event) { //————————————————————————————– // The event handler, adds data validation for the input parameters //————————————————————————————– // Declare some variables: //————————————————————————————– var TargetSheet = ‘Main’ // name of the sheet where you want to verify the data var LogSheet = ‘Data’ // name of the sheet with information var NumOfLevels = 4 // number of associated drop-down list levels var lcol = 2; // number of the leftmost column, in which the changes are checked; A = 1, B = 2, etc. var lrow = 2; // line number from which the rule will be valid //————————————————————————————– // =================================== key variables ================================= // // ss sheet we change (TargetSheet) // br range to change // scol number of column to edit // srow number of row to edit // CurrentLevel level of drop-down, which we change // HeadLevel main level // r current cell, which was changed by user // X number of levels could be checked on the right // // ls Data sheet (LogSheet) // // ====================================================================================== // [ 01 ].Track sheet on which an event occurs var ts = event.source.getActiveSheet(); var sname = ts.getName(); if (sname == TargetSheet) { // ss — is the current book var ss = SpreadsheetApp.getActiveSpreadsheet(); // [ 02 ]. If the sheet name is the same, you do business… var ls = ss.getSheetByName(LogSheet); // data sheet // [ 03 ]. Determine the level //————– The changing sheet ——————————– var br = event.source.getActiveRange(); var scol = br.getColumn(); // the column number in which the change is made var srow = br.getRow() // line number in which the change is made // Test if column fits if (scol >= lcol) { // Test if row fits if (srow >= lrow) { var CurrentLevel = scol-lcol+2; // adjust the level to size of // range that was changed var ColNum = br.getLastColumn() – scol + 1; CurrentLevel = CurrentLevel + ColNum – 1; // also need to adjust the range ‘br’ if (ColNum > 1) { br = br.offset(0,ColNum-1); } // wide range var HeadLevel = CurrentLevel – 1; // main level // split rows var RowNum = br.getLastRow() – srow + 1; var X = NumOfLevels – CurrentLevel + 1; // the current level should not exceed the number of levels, or // we go beyond the desired range if (CurrentLevel <= NumOfLevels ) { // determine columns on the sheet “Data” var KudaCol = NumOfLevels + 2 var KudaNado = ls.getRange(1, KudaCol); var lastRow = ls.getLastRow(); // get the address of the last cell var ChtoNado = ls.getRange(1, KudaCol, lastRow, KudaCol); // ============================================================================= > loop > for (var j = 1; j <= RowNum; j++) { for (var k = 1; k <= X; k++) { HeadLevel = HeadLevel + k – 1; // adjust parent level CurrentLevel = CurrentLevel + k – 1; // adjust current level var r = br.getCell(j,1).offset(0,k-1,1); var SearchText = r.getValue(); // searched text // if anything is choosen! if (SearchText != ”) { //——————————————————————- // [ 04 ]. define variables to costumize data // for future data validation //————— Sheet with data ————————– // combine formula // repetitive parts var IndCodePart = ‘INDIRECT(“R1C’ + HeadLevel + ‘:R’ + lastRow + ‘C’; IndCodePart = IndCodePart + HeadLevel + ‘”,0)’; // the formula var code = ‘=UNIQUE(INDIRECT(“R” & MATCH(“‘; code = code + SearchText + ‘”,’; code = code + IndCodePart; code = code + ‘,0) & “C” & “‘ + CurrentLevel code = code + ‘” & “:” & “R” & COUNTIF(‘; code = code + IndCodePart; code = code + ‘,”‘ + SearchText + ‘”) + MATCH(“‘; code = code + SearchText + ‘”;’; code = code + IndCodePart; code = code + ‘,0) – 1’; code = code + ‘& “C” & “‘ ; code = code + CurrentLevel + ‘”,0))’; // Got it! Now we have to paste formula KudaNado.setFormulaR1C1(code); // get required array var values = []; for (var i = 1; i <= lastRow; i++) { var currentValue = ChtoNado.getCell(i,1).getValue(); if (currentValue != ”) { values.push(currentValue); } else { var Variants = i-1; // number of possible values i = lastRow; // exit loop } } //——————————————————————- // [ 05 ]. Build daya validation rule var cell = r.offset(0,1); var rule = SpreadsheetApp .newDataValidation() .requireValueInList(values, true) .setAllowInvalid(false) .build(); cell.setDataValidation(rule); if (Variants == 1) { cell.setValue(KudaNado.getValue()); } // the only value else { k = X+1; } // stop the loop through columns } // not blanc cell else { // kill extra data validation if there were // columns on the right if (CurrentLevel <= NumOfLevels ) { for (var i = 1; i <= NumOfLevels; i++) { var cell = r.offset(0,i); // clean cell.clear({contentsOnly: true}); // get rid of validation cell.clear({validationsOnly: true}); } } // correct level } // empty row } // loop by cols } // loop by rows // ============================================================================= < loop < } // wrong level } // rows } // columns… } // main sheet}function onEdit(event) { SmartDataValidation(event); }

вот набор переменных, которые должны быть изменены, вы найдете их в скрипт:

var TargetSheet = ‘Main’ // name of the sheet where you want to verify the data var LogSheet = ‘Data’ // name of the sheet with information var NumOfLevels = 4 // number of associated drop-down list levels var lcol = 2; // leftmost column, in which the changes are checked; A = 1, B = 2, etc. var lrow = 2; // line number from which the rule will be valid

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

Edit: ответ ниже может быть удовлетворительным, но у него есть некоторые недостатки:

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

  2. он работает, создавая новый диапазон каждый раз, когда вы редактируете заданную строку. Это дает “недопустимое содержимое” для предыдущих записей иногда

Я надеюсь, что другие могут это убирать отчасти.

вот еще один способ сделать это, что экономит вам кучу имен диапазон:

три листа на листе: назовите их Main, List и DRange (для динамического диапазона.)На главном листе столбец 1 содержит временную метку. Этот штамп времени изменен onEdit.

в списке ваши категории и подкатегории расположены как простой список. Я использую это для инвентаризации растений на моей ферме деревьев, поэтому мой список выглядит так:

Group | Genus | Bot_NameConifer | Abies | Abies balsameaConifer | Abies | Abies concolorConifer | Abies | Abies lasiocarpa var bifoliaConifer | Pinus | Pinus ponderosaConifer | Pinus | Pinus sylvestrisConifer | Pinus | Pinus banksianaConifer | Pinus | Pinus cembraConifer | Picea | Picea pungensConifer | Picea | Picea glaucaDeciduous | Acer | Acer ginnalaDeciduous | Acer | Acer negundoDeciduous | Salix | Salix discolorDeciduous | Salix | Salix fragilis…

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

DRrange A1 имеет формулу

=Max(Main!A2:A1000)

Это возвращает самую последнюю метку времени.

А2 до А4 есть варианты:

=vlookup($A,Inventory!$A:$E00,2,False)

С увеличением 2 для каждой ячейки справа.

при запуске A2-A4 будет иметь выбранную в настоящее время группу, род и вид.

под каждым из них находится фильтр команда что-то вроде этого:

=unique (filter (Bot_Name, REGEXMATCH (Bot_Name, C1)))

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

фильтры могут быть изменены в соответствии с вашими потребностями и форматом вашего списка.

Back to Main: проверка данных в Main выполняется с использованием диапазонов от DRange.

скрипт, который я использую:

function onEdit(event) { //SETTINGS var dynamicSheet=’DRange’; //sheet where the dynamic range lives var tsheet = ‘Main’; //the sheet you are monitoring for edits var lcol = 2; //left-most column number you are monitoring; A=1, B=2 etc var rcol = 5; //right-most column number you are monitoring var tcol = 1; //column number in which you wish to populate the timestamp // var s = event.source.getActiveSheet(); var sname = s.getName(); if (sname == tsheet) { var r = event.source.getActiveRange(); var scol = r.getColumn(); //scol is the column number of the edited cell if (scol >= lcol && scol <= rcol) { s.getRange(r.getRow(), tcol).setValue(new Date()); for(var looper=scol+1; looper<=rcol; looper++) { s.getRange(r.getRow(),looper).setValue(“”); //After edit clear the entries to the right } } }}

оригинальная презентация Youtube, которая дал мне большую часть компонента onedit timestamp:https://www.youtube.com/watch?v=RDK8rjdE85Y

2

автор: Sherwood Botsford

здесь у вас есть другое решение, основанное на том, которое предоставлено @tarheel

function onEdit() { var sheetWithNestedSelectsName = “Sitemap”; var columnWithNestedSelectsRoot = 1; var sheetWithOptionPossibleValuesSuffix = “TabSections”; var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var activeSheet = SpreadsheetApp.getActiveSheet(); // If we’re not in the sheet with nested selects, exit! if ( activeSheet.getName() != sheetWithNestedSelectsName ) { return; } var activeCell = SpreadsheetApp.getActiveRange(); // If we’re not in the root column or a content row, exit! if ( activeCell.getColumn() != columnWithNestedSelectsRoot || activeCell.getRow() < 2 ) { return; } var sheetWithActiveOptionPossibleValues = activeSpreadsheet.getSheetByName( activeCell.getValue() + sheetWithOptionPossibleValuesSuffix ); // Get all possible values var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues.getSheetValues( 1, 1, -1, 1 ); var possibleValuesValidation = SpreadsheetApp.newDataValidation(); possibleValuesValidation.setAllowInvalid( false ); possibleValuesValidation.requireValueInList( activeOptionPossibleValues, true ); activeSheet.getRange( activeCell.getRow(), activeCell.getColumn() + 1 ).setDataValidation( possibleValuesValidation.build() );}

Он имеет некоторые преимущества перед другим подходом:

  • вам не нужно редактировать скрипт каждый раз, когда вы добавить “корень”вариант. Вам нужно только создать новый лист с вложенными параметрами этого корневого параметра.
  • я переработал скрипт, предоставляющий больше семантических имен для переменных и так далее. Кроме того, я извлек некоторые параметры переменные чтобы было легче адаптироваться к вашему конкретному случаю. Вам нужно только установить первые 3 значения.
  • нет предела вложенных значений опций (я использовал метод getSheetValues со значением -1).

Итак, как использовать:

  1. создайте лист, где у вас будут вложенные селекторы
  2. перейдите в “инструменты” > “редактор сценариев…” и выберите опцию “пустой проект”
  3. вставьте код, прикрепленный к этому ответ
  4. измените первые 3 переменные скрипта, настроив свои значения и сохраните его
  5. создать лист в этом же документе для каждого возможного значения “корень селектор”. Они должны быть названы как значение + указанный суффикс.

наслаждайтесь!

2

автор: Javier Ferrer González

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

/** * “on edit” event handler * * Based on JavierCane’s answer in * * http://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets * * Each set of options has it own sheet named after the option. The * values in this sheet are used to populate the drop-down. * * The top row is assumed to be a header. * * The sub-category column is assumed to be the next column to the right. * * If there are no sub-categories the next column along is cleared in * case the previous selection did have options. */function onEdit() { var NESTED_SELECTS_SHEET_NAME = “Sitemap” var NESTED_SELECTS_ROOT_COLUMN = 1 var SUB_CATEGORY_COLUMN = NESTED_SELECTS_ROOT_COLUMN + 1 var NUMBER_OF_ROOT_OPTION_CELLS = 3 var OPTION_POSSIBLE_VALUES_SHEET_SUFFIX = “” var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet() var activeSheet = SpreadsheetApp.getActiveSheet() if (activeSheet.getName() !== NESTED_SELECTS_SHEET_NAME) { // Not in the sheet with nested selects, exit! return } var activeCell = SpreadsheetApp.getActiveRange() // Top row is the header if (activeCell.getColumn() > SUB_CATEGORY_COLUMN || activeCell.getRow() === 1 || activeCell.getRow() > NUMBER_OF_ROOT_OPTION_CELLS + 1) { // Out of selection range, exit! return } var sheetWithActiveOptionPossibleValues = activeSpreadsheet .getSheetByName(activeCell.getValue() + OPTION_POSSIBLE_VALUES_SHEET_SUFFIX) if (sheetWithActiveOptionPossibleValues === null) { // There are no further options for this value, so clear out any old // values activeSheet .getRange(activeCell.getRow(), activeCell.getColumn() + 1) .clearDataValidations() .clearContent() return } // Get all possible values var activeOptionPossibleValues = sheetWithActiveOptionPossibleValues .getSheetValues(1, 1, -1, 1) var possibleValuesValidation = SpreadsheetApp.newDataValidation() possibleValuesValidation.setAllowInvalid(false) possibleValuesValidation.requireValueInList(activeOptionPossibleValues, true) activeSheet .getRange(activeCell.getRow(), activeCell.getColumn() + 1) .setDataValidation(possibleValuesValidation.build()) } // onEdit()

Как говорит Хавьер:

  • создайте лист, где у вас будут вложенные селекторы
  • перейдите в “инструменты” > “редактор сценариев…” и выберите ” пустой проект”вариант
  • вставьте код, прикрепленный к этому ответу
  • измените константы в верхней части скрипта, настраивая свои значенияи сохранить его
  • создать один лист в этом же документе для каждого возможного значения”корневой селектор”. Они должны быть названы значения указанногосуффикс.

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

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

Запуск Google Таблицы

Откройте новую электронную таблицу или откройте существующий файл электронной таблицы.

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

Перейдите к Таблицы меню и нажмите на опцию Данные.

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

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

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

Второе поле в окне проверки данных — это Критерии который имеет список различных опций в собственном раскрывающемся меню. Критерии содержат такие параметры, как Список из диапазона, Список элементов, Число, Текст, и Дата.

  • Список из диапазона: этот параметр позволяет создать список значений, полученных из разных листов, или список значений из разных ячеек на одном листе.
  • Список элементов: это позволяет нам создать список текстовых значений. Они вводятся в поле редактирования через запятую.
  • Число: этот параметр не создает раскрывающийся список, а гарантирует, что запись в раскрывающемся меню попадает в определенный числовой диапазон.
  • Текст: этот параметр не создает раскрывающийся список, а обеспечивает правильный текстовый формат записи.
  • Дата: этот параметр не создает раскрывающийся список, а вместо этого проверяет, действительна ли введенная дата или находится в определенном диапазоне.
  • Настраиваемая формула: этот параметр не создает раскрывающийся список, а вместо этого проверяет, что в выбранной ячейке используется указанная пользователем формула.

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

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

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

Нажмите Сохранять чтобы применить изменения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для чего же нужны выпадающие списки в Гугл таблицах?

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

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

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

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

Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.

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

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

Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:
Как сделать выпадающий список в google таблицах

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

В котором мы видим следующие пункты:

  • Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
  • Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
    Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными.
  • Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
  • Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
    А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки».
  • Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»

Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:
Как сделать выпадающий список в google таблицах

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

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

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

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

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

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

Затем пройдем путь в меню: Формат -> Условное форматирование или кликнем правой кнопкой мыши и в открывшемся контекстном меню выберем Условное форматирование.

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

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

  • Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!
  • Теперь повторим эти действия с другими данными, нажав на кнопку Добавить правило справа, только теперь вводим в поле не Фрукты, а Ягоды и на последнем этапе Овощи, и наблюдаем вот такую картину:
    Как сделать выпадающий список в google таблицах
  • Теперь когда мы выберем нужные данные в выпадающем списке, ячейка эта будет окрашиваться в соответствующий цвет.

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

Возможно вам будет интересно:

  • Полный гайд по основным возможностям Гугл таблиц
  • Формулы в Гугл таблицах

Источник: http://google-suite.info/google-tablicy-vypadayushhij-spisok/

Как сделать выпадающий список уникальных значений в 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 таблицах?

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

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