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

ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

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

Создание управленческой таблицы

ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

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

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

Инструментом ведения учета в рамках примера будут Гугл Таблицы.

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

Инструментом ведения учета в рамках примера будут Гугл Таблицы.

1. Создаем таблицу, в которую будут записываться основные данные:

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

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

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

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

В поле выбора правил проверки выбираем значение «Дата» из выпадающего списка:

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

Нажимаем кнопку «Сохранить» . Теперь двойным мыши нажатием в столбце ввода даты появляется календарь:

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

Организуем выбор объекта из выпадающего списка. Для этого есть два варианта:

Вариант 1: Выделяем столбец «Объект», выбираем пункт меню «Данные», «Проверка данных».
В поле выбора правил выбираем «Значение из списка» и вписываем через запятую наименования объектов

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

Получаем выпадающий список с введенными значениями:

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

Вариант 2: Список объектов хранится в таблице на любом из листов. В примере это будет лист «Справочник»

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

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

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

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

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

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

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

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

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

Далее задаем внешний вид:

Ввод данных и разграничение прав пользователей

Ввод данных и разграничение прав пользователей

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

Пользователь вводит логин аккаунта Gmail и определяет права:

В расширенных настройках можно определить полномочия делегируемых прав

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

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

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

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

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

Перед пользователем открывается таблица, в которой цветом выделены изменяемые области, а в окне справа отображены время изменения и пользователь, который внес редакцию

Анализ данных и отчетность

Анализ данных и отчетность

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

Верхняя таблица содержит суммарные результаты всех нижних таблиц, в которых должна отображаться деятельность каждого из объектов. Для каждого объекта будем подтягивать данные из листа «Данные» формулой «СУММЕСЛИМН».

Функция СУММЕСЛИМН суммирует ячейки, удовлетворяющие заданному набору условий.
Вид функции: =СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1; Диапазон условия 2; Условие 2;…)

Поехали. Сперва добавим на лист «Данные» столбец с названием «Месяц» и выведем формулой порядковый номер месяца из значения даты. Встаем курсором в ячейку H2 и вводим простую формулу: =МЕСЯЦ(A2). Протягиваем ее до конца таблицы:

Далее приступаем к самому главному. Перемещаемся на лист отчета. Встаем в ячейку D10 и вводим в строке формул «=СУММЕСЛИМН(…» Выделяем на листе «Данные» столбец F с суммами и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F;

Далее выделяем столбец с критериями для суммирования Е – статьи бюджета и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F; 'Данные ' ! $E : $E;

Выделяем условие отбора критериев – ячейка С10 и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F
; 'Данные ' ! $E : $E
;$C10;

Далее указываем второй столбец с критериями – столбец В с наименованиями объектов и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;

И указываем наименование объекта в ячейке В10 в качестве критерия и фиксируем перемещение по столбцам и строкам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B; $B$10;

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

=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;$B$10;

' Данные ' ! $H : $H;D$2) Протягиваем формулу на все статьи бюджета и каждый месяц в таблице объекта 1

=СРЗНАЧ(D10:O10) Аналогичным образом считаем итоги, используя функцию «СУММ». Аргументом формулы является набор или массив данных. Получаем заполненную расчетами таблицу:

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

Нажимаем два раза на кнопку «Уменьшить число знаков после запятой»

Получаем отчет по объекту 1:

Производим аналогичные вычисления для других объектов. Получаем отчет следующего вида:

Считаем сумму, складывая данные бюджета каждого объекта:

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

В данном кейсе рассмотрена укрупненная схема организации управленческого учета компании. Информация на листе «Данные» может представлять собой данные в системе учета или данные которые формируются вручную из разных источников. Лист отчет представляет собой отчет о прибылях и убытках. Отчет можно отнести к данным для первичного анализа. По разбросу его значений можно выявить положительные или отрицательные факторы и при необходимости искать причины в других источниках информации. В отчет можно добавить сравнение с аналогичными данными прошедших периодов, сравнение с плановыми значениями, а так же расчеты таких показателей как рентабельность, валовая прибыль и т. д.

Источник: https://helpexcel.pro/org__uchet

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

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

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

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

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

  • вы можете начать с листа 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(); } } }

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

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

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

Примечание

скрипты имеют ограничение: он обрабатывает до 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. Как в таблице google создать выпадающий список
  3. мой план:
  1. Подготовка Данных
  2. сделать первый список как обычно: Data > Validation
  3. добавить скрипт, установить некоторые переменные
  4. готово!

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

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

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

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

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

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

  • Как в таблице google создать выпадающий список
  • скрипт
  • вставить этот код в Редакторе скриптов:

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/

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

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

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

И если вводить текст приходится по несколько часов подряд, то очень велика вероятность ошибиться.

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

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

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

Дальше перейдите на лист, где нужно сделать раскрывающийся список.

Выделите ячейку, где он будет. Кликните по вкладке «Данные» и выберите «Проверка данных».

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

Здесь так же можно выбрать «Значение из списка». Потом в соседнем поле, через запятую, введите варианты, которые должны отображаться в выпадающем блоке. Например, «Катя, Вася, Максим, Оля, Ира».

Затем перейдите на вкладку с данными (у меня это «Сотрудники») и выделите диапазон, напечатанное в котором должно будет отображаться в выпадающем списке. Нажимайте «ОК».

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

  • Возвращаемся к нашему окошку и проверяем, чтобы стояла галочка в поле «Показывать раскрывающийся список в ячейке».
  • Дальше идет блок «Для неверных данных». Если поставить марке напротив «показывать предупреждение»…
  • То после того, как пользователей введет в ячейку со списком вариант, которого в нем нет, в верхнем правом углу появится оранжевый треугольник, а после наведения на нее курсора отобразится и сообщение об ошибке.
  • Если отметить маркером «запрещать ввод данных»…
  • То при введении в ячейку не добавленного варианта появится окошко с ошибкой, и введенный текст не сохранится.
  • В блоке «Оформление» можно отметить галочкой «Показывать текст справки…» и написать его в блоке, размещенном чуть ниже.
  • Теперь, если выбрано «запрещать ввод данных» и в ячейку введен текст не из списка, появится окно с сообщением, текст в котором будет тот, что вы написали.
  • Если же выберите «показывать предупреждение» и напишите что-то в поле для справки, то данный блок будет появляться при выделении ячейки, в которой не выбран ни один вариант из выпадающего списка (она либо пустая, либо заполнена другим текстом).
  • После того, как сделаете все настройки, жмите «Сохранить».
  • Выглядит оно так: пустая выделенная ячейка с отображающимся рядом окном проверки, в которой напечатан ваш текст.

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

  1. Если нужно сделать такой список не для одной ячейки, а для определенного диапазона, тогда выделите его (в примере это В2:В11 на листе «Список»), откройте вкладочку «Данные» и выберите знакомый пункт.
  2. Теперь заполните все, как описано выше, и сохраняйте изменения.
  3. В примере в столбце «Имя» для всех выбранных ячеек был создан выпадающий список.

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

Например, я дописала на листе с сотрудниками несколько фамилий.

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

Но диапазон у меня был выделен А2: А11, а фамилий я дописала больше (до ячейки А13). Понятно, что две последние в списке не отобразятся. Поэтому давайте расскажу, как решить такую ситуацию.

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

В блоке «Правила» нужно изменить диапазон ячеек. Можете его заново выделить, нажав на кнопку с девятью квадратиками, а можно просто вручную изменить число. Например, я А11 сменила на А13. Не забудьте сохранить изменения.

Как видите, в списке отображаются все фамилии, которые напечатаны на листе «Сотрудники».

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

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

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

Например, у меня вошли еще и названия столбцов. Поскольку они не нужны, можно их (Фамилия, Имя, Отчество) написать на листе с исходными данными («Сотрудники») рядом с текстом (в столбце А название «Фамилия», в В указать их, в С – «Имя», в D написать имена).

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

Источник: https://inetfishki.ru/servisy-google/vypadayushhij-spisok-v-google-tablicax.html

google spreadsheet ячейкой Как вы делаете динамические/зависимые выпадающие списки в 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();
}
}
}

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

Вы должны быть хороши, чтобы пойти после этого!

Здесь у вас есть другое решение, основанное на том, которое предоставляется @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. Создайте один лист в этом же документе для каждого возможного значения «корневого селектора». Они должны быть названы как значение + указанный суффикс.

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

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

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

  • Создайте лист, в котором вы будете иметь вложенные селектора
  • Перейдите в «Инструменты»> «Редактор сценариев …» и выберите вариант «Пустой проект»
  • Вставьте код, прикрепленный к этому ответу
  • Измените константы в верхней части скрипта, настройте свои значения и сохраните их
  • Создайте один лист в этом же документе для каждого возможного значения «корневого селектора». Они должны быть названы как значение + указанный суффикс.

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

Заметка

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

Сценарий был выпущен в январе 2018 года. См.

Улучшения:

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

Версии сценария

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

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

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

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

Мой план:

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

Подготовить данные

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

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

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

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

  1. скрипт
  2. Вставьте этот код в редактор сценариев:
  3. Вот набор переменных, которые нужно изменить, вы найдете их в скрипте:

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

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

Источник: https://code.i-harness.com/ru/q/14bcba3

Ссылка на основную публикацию
Adblock
detector