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

Сергей Капличный

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

Как работать с фильтрами

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

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

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

После этого внешний вид вашей таблицы поменяется:

• В левом верхнем углу будет стоять имя нового фильтра (по умолчанию — Фильтр 1 (2, 3…)). Его можно изменить и в дальнейшем вызывать в том же диалоговом окне:
• 
• Справа от имени указан диапазон, который вы фильтруете, его можно изменять:

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

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

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

Как писать условия в формулах

Небольшое пояснение, как писать условия в формулах, чтобы все работало.

— Числовые и текстовые условия, знаки >,

Источник: https://biz.mann-ivanov-ferber.ru/2018/05/01/5-effektivnyx-lajfxakov-pri-rabote-s-google-tablicami/

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

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

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

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

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

после этого вам нужно настроить скрипт на имя 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(); } } }

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

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

Примечание

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

Новый Сценарий. 201801

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

улучшения:

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

Старый Сценарий.

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

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

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

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

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

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

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

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

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

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

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

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

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 loop >
for (var j = 1; j

Источник: https://askdev.ru/q/kak-vy-delaete-dinamicheskie-zavisimye-vypadayuschie-spiski-v-google-tablicah-89924/

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

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

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

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

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

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

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

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

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

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

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

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

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

Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:

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

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

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

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

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

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

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

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

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

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

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

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

Выпадающий список с мультивыбором

8934 30.06.2013 Скачать пример

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

Давайте рассмотрим несколько типовых вариантов реализации такого списка с мультивыбором.

Вариант 1. Горизонтальный

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

Выпадающие списки в ячейках С2:С5 в данном примере создаются стандартным образом, т.е.

1. выделить ячейки С2:С5
2. на вкладке или в меню Данные (Data) выбрать команду Проверка данных (Data Validation)
3. в открывшемся окне выбрать вариант Список (List) и указать в качестве диапазона Источник (Source) ячейки с исходными данными для списка A1:A8

Затем в модуль листа нужно добавить макрос, который и будет делать всю основную работу, т.е. добавлять выбранные значения справа от зеленых ячеек. Для этого щелкните правой кнопкой мыши по ярлычку листа с выпадающими списками и выберите команду Исходный текст (Source code). В открывшееся окно редактора Visual Basic нужно вставить следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 Then
Target.Offset(0, 1) = Target
Else
Target.End(xlToRight).Offset(0, 1) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

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

Вариант 2. Вертикальный

То же самое, что и в предыдущем варианте, но новые выбранные значения добавляются не справа, а снизу:

Делается совершенно аналогично, но немного меняется код макроса обработчика:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:F2»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(1, 0)) = 0 Then
Target.Offset(1, 0) = Target
Else
Target.End(xlDown).Offset(1, 0) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

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

Вариант 3. С накоплением в той же ячейке

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:C5»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) 0 And oldval newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

При желании, можно заменить символ-разделитель (запятую) в 9-й строке кода на свой (например, пробел или точку с запятой).

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/1/181/