Создание выпадающего списка в таблице Google

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

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

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

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

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

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

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

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

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

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

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

  Откроется вот такое контекстное меню:  

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

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

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

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

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

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

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

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

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

 

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

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

Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!

 

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

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

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

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

Связанные выпадающие списки в табличках Google

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

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

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

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

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

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

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

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

# ? =

01	?	=	Сразу возникает вопрос: а каков максимальный объем данных можно загрузить на лист с данными? Есть ли предел, выше которого скрипт работать уже перестанет? Ответ, разумеется, предел есть! Но он зависит не от скрипта, а от скорости работы табличек в целом. Я рекомендую использовать скрипт с данными:  не более 5 000 строк для максимально комфортного использования. Так ты не почувствуешь, что скрипт заметно тормозит.  Можно и больше. Но всё зависит от мощности твоего компьютера. К тому же, верю, в будущем сервера и технологии табличек будут становиться всё быстрее. Я тестировал скрипт с табличкой в 200 000 строк. И он по-прежнему работал, хотя и медленно. Жду теперь твои результаты тестов скорости.
02	?	=	Доступ на редактирование файла с готовым скриптом я не даю, т.к. скрипт нужен всем. Ты откроешь мой пример и обнаружишь, что менять в нем ничего нельзя.Но можно зайти в меню «файл → Создать копию» и тогда уже это будет твоя копия. Делай с ней, что хочешь, используй, дорабатывай под свои нужды, пиши мне свои отзывы и пожелания, наиболее частые из которых я обязательно учту при дальнейшей разработке проекта.
03	?	=	Настройка скрипта вообще не составит тебе труда.Для начала зайди в место, где этот скрипт находится. Для этого можно нажать меню «Инструменты → редактор скриптов». А еще, если ты пользуешься браузером Chrome, ты можешь воспользоваться сочетанием клавиш: [Alt + T + E], которое можно запомнить по первым буквам названия меню на английском Tools → Editor. Совет: используй разные сочетания клавиш в табличках Google (и не только). Это значительно ускорит тебе работу. Но продолжим. Когда ты зайдешь в редактор скриптов, ты там увидишь текст моего скрипта. Он длинный: Но тебе потребуется только поменять настройки, которые я специально разместил в самом начале скрипта. Тут потребуется хорошо потрудится: изменить пару цифр и букв, думаю, справишься ;)Для справки. К каждой таблице Goolge может быть привязан свой набор скриптов. Для использования того же скрипта в другом файле, необходимо скопировать весь текст кода в редактор скриптов другого файла.
04	?	=	Итак, ты уже открыл редактор скриптов и хочешь поменять настройки «под себя». Там так и будет написано: «Изменить настройки». Давай разберёмся по пунктам.var TargetSheet = 'Основной' // имя листа, на котором настроены выпадающие списки↑ Вместо слова «Основной» вставь имя рабочего листа, на котором ты будешь делать связанные выпадающие списки.var LogSheet = 'Данные' // имя листа с данными для списка↑ Создай лист с исходными данными для списка, а его название впиши в скрипт. var NumOfLevels = 4 // количество уровней выпадающего списка↑ В нашем примере 4 связанных выпадающих списка. Если нужно, замени их количество на свое, впиши вместо 4 свое число. var lcol = 2; // номер колонки слева, с которой начинается первый список; // A = 1, B = 2, etc.↑ Впиши номер колонки, с которой начинается твоя таблица. var lrow = 2; // номер строки, начиная с которой срабатывает список↑ Шапка таблицы не должна затрагиваться скриптом, поэтому впиши сюда номер строки, с которой начинаются твои данные.
05	?	=	Данные формируются на отдельном листе. В моем примере имеется 4 уровня вложенных списков: Планета → Страна → Континент → Город.  При желании можно добавить еще парочку в конце: или в начале: Как тебе сделать свой список? Просто сделай его по примеру моего: Пропиши имена категорий-уровней таблицы в первой строчке, начиная с клетки A1. Начни заполнять с последнего уровня: выпиши все возможные варианты. Двигаясь к первому уровню, вводи все значения, не оставляя пустых ячеек. Некоторые названия придётся повторить, это необходимо для правильной работы скрипта. Когда данные будут готовы, можно двигаться дальше. В будущем, их можно пополнять новыми данными. Отлично! Данные готовы, осталось совсем немного настроить таблицу. Не спеши сразу заполнять все данные, для тестирования достаточно нескольких строк. Помни, что данные должны быть едиными: под ними ничего не записывай,  ячейки в них не объединяй.
06	?	=	Первый список нужно будет настроить вручную. Полученные данные нельзя сразу использовать для создания первого выпадающего списка. В моем примере первый уровень данных содержит названия планет, и они повторяются т.к. одна планета содержит несколько континентов, стран и так далее. А нам нужен список уникальных значений. Для получения списка уникальных значений, используй формулу:= UNIQUE(A2:A)Она очень простая и выдает список уникальных значений. Эту формулу рекомендую написать на 4 колонки правее основных данных: Либо вообще размести ее на новом листе:= UNIQUE(Данные!A2:A)После того, как ты получишь список, переходи на свой рабочий лист. Выдели диапазон ячеек, в котором будет находиться будущий выпадающий список. Иди в меню Данные → Проверка данных… и выбери там свой список.Совет. При выборе данных для выпадающего списка ты можешь учесть необходимость в добавлении новых значений в дальнейшем. Если данные содержатся в диапазоне A1:A20, то ты можешь выбрать бо́льший диапазон для проверки данных: A1:A100. Пустые ячейки будут игнорироваться. После этой настройки связанные выпадающие списки уже можно использовать! Теперь поговорим о приятных деталях.
07	?	=	Об этом я уже упомянул ранее. Для этого зайди в тело скрипта (Инструменты → редактор скриптов) и поменяй там одно число:var NumOfLevels = 4 // количество уровней выпадающего списка↑ В нашем примере 4 связанных выпадающих списка. Вместо 4 поставь свое число. И, конечно, не забудь под это изменить данные для выбора значений выпадающих списков. Как видишь, это сделать очень легко.
08	?	=	Если по категории, выбранный тобой элемент из выпадающего списка имеет единственные значения в подчиненных списках, то нет смысла давать их на выбор. Например, мы выбрали часть света = «Азия». В справке «Азии» соответствует только одна страна = «Китай «, а для «Китая» найден лишь один город = «Пекин». Вот, что произойдет при выборе «Азии»: скрипт автоматически вставит «Китай» и «Пекин» в нужные ячейки. Правда, удобно?
09	?	=	Часто бывает необходимость создания списка позиций с типовыми параметрами: цвет, размер, модель, ценовая категория и прочее. Эти значения могут повторяться от одной группы к другой. Получаются дубли.Хорошая новость! В текущей версии скрипта списки могут содержать дубли, они будут нормально считываться.
10	?	=	Нет. Твои исходные данные могут быть сортированы в произвольном порядке. Это может быть удобно. Добавляя новые данные тебе не нужно беспокоиться о порядке строк. К тому же легче автоматизировать процесс.Совет. Новые данные можно заполнять вручную, а можно автоматизировать: например, при помощи другого скрипта, или с помощью форм Google. Работа с другими типами данных Пока реализована работа со следующими типами данных: [Мама мыла раму] = простой текст [100500] = числа [3,14159265356] = в том числе дробные,  [100/500;100;Текст] = а так же группы данных, состоящих как из текста, так и из чисел [01.08.2050] = дата и время — пока в разработке.

Полезные функции Google Таблиц, которых нет в Excel

Cтатья написана в соавторстве с Ренатом Шагабутдиновым.

В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).

— Если результат формулы несколько ячеек

— Объединение нескольких диапазонов данных для использования в формулах
— SORT
— Как в SORT добавить заголовки таблицы?
— FILTER
— FILTER, два условия и работа с датой
— Интерактивный график при помощи FILTER и SPARKLINE
— IMPORTRANGE
— Импорт форматирования из исходной таблицы
— IMPORTRANGE как аргумент другой функции
— IMAGE: добавляем изображения в ячейки
— GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках
Если результат формулы занимает больше одной ячейки Сначала про важную особенность отображения результатов формул в Google Таблицах. Если ваша формула возвращает более одной ячейки, то весь этот массив отобразится сразу и займет столько ячеек и столбцов, сколько для него потребуется (в Excel для этого нужно было бы во все эти ячейки ввести формулу массива). На следующем примере посмотрим, как это работает.

SORT

Поможет отсортировать диапазон данных по одному или нескольким столбцам и сразу вывести результат. Синтаксис функции: =SORT(сортируемые данные; столбец_для_сортировки; по_возрастанию; [столбец_для_сортировки_2, по_возрастанию_2; …

]) Пример на скриншоте ниже, мы ввели формулу только в ячейку D2 и сортируем данные по первому столбцу (вместо ИСТИНА/ЛОЖЬ можно вводить TRUE/FALSE).(здесь и далее — примеры для российских региональных настроек таблицы, рег.

настройки меняются в меню файл → настройки таблицы)

Как в SORT добавить заголовки таблицы? С помощью фигурных скобок {} создаем массив из двух элементов, шапки таблицы A1:B1 и функции SORT, элементы отделяем друг-от-друга с помощью точки с запятой.

Как объединить несколько диапазонов данных и отсортировать (и не только)? Давайте рассмотрим, как можно объединять диапазоны для использования в функциях. Это касается не только SORT, этим приемом можно пользоваться в любых функциях, где это возможно, например в ВПР или ПОИСКПОЗ.

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

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

(точка с запятой и обратный слэш — это разделители элементов массива в российских региональных настройках, если у вас не работают примеры, то через файл — настройки таблицы, убедитесь, что у вас стоят именно они)
Ну а теперь вернемся к горизонтальному массиву и вставим его в функцию SORT. Будем сортировать данные по первому столбцу, по убыванию. Объединение можно использовать в любых функциях, главное — соблюдать одинаковое количество столбцов для вертикального или строк для горизонтального объединения. Все разобранные примеры можно рассмотреть поближе в

Google Документе.

FILTER

С помощью FILTER мы можем отфильтровать данные по одному или нескольким условиям и вывести результат на рабочий лист или использовать результат в другой функции, как диапазон данных. Синтаксис функции:FILTER(диапазон; условие_1; [условие_2; …])
Одно условие Пример, у нас есть таблица с продажами наших сотрудников, выведем из нее данные по одному работнику.

Введем в ячейку E3 вот такую формулу: =FILTER(A3:C7;B3:B7=«Наталья Чистякова») Обратите внимание, синтаксис немного отличается от привычных формул, вроде СУММЕСЛИН, там диапазон условия и само условие отделялось бы при помощи точки с запятой.

Введенная в одну ячейку формула возвращает нам массив из 9-ти ячеек с данными, но после примеров с функцией SORT мы этому уже не удивляемся.

Помимо знака равенства (=) в условиях можно использовать еще >, >=, (не равно), =J6)
Интерактивный график при помощи FILTER и SPARKLINE А знаете, как еще можно использовать функцию FILTER? Мы можем не выводить результат функции на рабочий лист, а использовать его как данные для другой функции, например, спарклайна.

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

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

• выделяем ячейку Е2;
• меню Данные → Проверка данных;
• правила: Значение из диапазона и в диапазоне выбираем столбец с сотрудниками из исходных данных, не переживайте, что фамилии повторяются, в выпадающем списке останутся лишь уникальные значения;

Нажимаем «Сохранить» и получаем выпадающий список в выбранной ячейке: Ячейка с выпадающим список станет условием для формулы FILTER, напишем ее.=FILTER(C3:C7;B3:B7=E2) И вставим эту формулу в функцию SPARKLINE, которая на основе полученных данных будет рисовать в ячейке график. =sparkline(FILTER(C3:C7;B3:B7=E2))
Так это выглядит в динамике: А вот как нарядно может выглядеть SPARKLINE с дополнительным настройками, в реальной работе, диаграмма выводит результаты деятельности за один день, зеленые столбцы — положительные значения, розовые — отрицательные.

IMPORTRANGE

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

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

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

Синтаксис формулы следующий:IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; диапазон) spreadsheet_key (ключ) — последовательность символов атрибута «key=» (ключа) в ссылке на таблицу (после «spreadsheets/…/»). Пример формулы с ключом:=IMPORTRANGE(«abcd123abcd123»; «sheet1!A1:C10») Вместо ключа таблицы вы можете использовать полную ссылку на документ:=IMPORTRANGE(«docs.google.

com/a/company_site.ru/spreadsheet/ccc?key=0A601pBdE1zIzHRxcGZFVT3hyVyWc»; «Лист1!A1:CM500») В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке. Если в исходном файле может меняться количество столбцов или строк, вводите во втором аргументе функции открытый диапазон (см.

также подраздел «Диапазоны вида A2:A»), например:Лист1!A1:CM (если будут добавляться строки)
Лист1!A1:1000 (если будут добавляться столбцы)
! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные).

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

Так, если в ячейку A1 вы введете ссылку на документ (без кавычек), из которого нужно загрузить данные, а в ячейку B1 — ссылку на лист и диапазон (тоже без кавычек), то импортировать данные можно будет с помощью следующей формулы: =IMPORTRANGE(A1;B1)
Вариант со ссылками на ячейки предпочтительнее в том смысле, что вы всегда можете легко перейти к исходному файлу (щелкнув по ссылке в ячейке) и/или увидеть, какой диапазон и из какой вкладки импортируется.

Импорт форматирования из исходной таблицы

Как мы уже заметили, IMPORTRANGE загружает только данные, но не форматирование исходной таблицы. Как с этим быть? Заранее «подготовить почву», скопировав форматирование из исходного листа.

Для этого зайдите на исходный лист и скопируйте его в вашу книгу:

После нажатия кнопки Копировать в… выберите книгу, в которую будете импортировать данные.

Обычно нужная таблица есть на вкладке Недавние (если вы действительно недавно работали с ней).

После копирования листа выделите все данные (нажав на левый верхний угол):

И нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:

IMPORTRANGE как аргумент другой функции IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль. Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе. Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз). Сначала импортируем этот диапазон:IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D») А потом используем это как аргумент функции СРЗНАЧ (AVERAGE):=СРЗНАЧ(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))
=AVERAGE(IMPORTRANGE(«docs.google.com/spreadsheets/d/16aKQAIGtLKwQFjWyUGraKAVPQe6cJucYAHoIc-AEEc4»;«Книги!D2:D»))
Получаем результат, который будет обновляться при добавлении новых строк в исходном файле в столбце D.

IMAGE: добавляем изображения в ячейки

Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения. У функции следующий синтаксис:IMAGE(URL, [mode], [height], [width]) URL – единственный обязательный аргумент. Это ссылка на изображение. Ссылку можно указать напрямую в формуле, взяв в кавычки:=IMAGE(“http://shagabutdinov.

ru/wp-content/uploads/2015/12/Run-or-Die.jpg”)
Или же поставить ссылку на ячейку, в которой ссылка хранится:= IMAGE(B1)
Последний вариант удобнее в большинстве случаев.

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

Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):

1. изображение растягивается до размеров ячейки с сохранением соотношения сторон;
2. изображение растягивается без сохранения соотношения сторон, целиком заполняя
3. изображение вставляется с оригинальным размером;
4. вы указываете размеры изображения в третьем и четвертом аргументам функции [height] и [width]. [height], [width], соответственно, нужны только при значении аргумента mode = 4. Они задаются в пикселях.

Посмотрим, как на практике выглядят изображения с четыремя разными значениями аргумента mode: Четвертый режим может быть удобен, если вам нужно подбирать точный размер изображения в пикселях, меняя параметры height (высота) и width (ширина). Картинка будет сразу обновляться. Обратите внимание, что при всех режимах, кроме второго, могут оставаться незаполненные области в ячейке, и их можно залить цветом:

GOOGLETRANSLATE и DETECTLANGUAGE: переводим текст в ячейках

В Google Таблицах есть занятная функция GOOGLETRANSLATE, позволяющая переводить текст прямо в ячейках: Синтаксис функции следующий:GOOGLETRANSLATE (text,[source_language], [target_language])
text – это текст, который нужно переводить. Можно взять текст в кавычки и записать прямо в формулу, но удобнее сослаться на ячейку, в которой текст записан. [source_language] – язык, с которого мы переводим;

[target_language] – язык, на который мы переводим.

Второй и третий аргументы задаются двухзначным кодом: es, fr, en, ru. Их тоже можно указать в самой функции, но можно брать из ячейки, а язык исходного текста и вовсе можно автоматически определять. А как быть, если мы хотим переводить на разные языки? И при этом не хотим каждый раз указывать язык исходника вручную? Тут пригодится функция DETECTLANGUAGE. У нее единственный аргумент – текст, язык которого нужно определить: Как и с любой другой функцией, прелесть здесь в автоматизации. Можно быстро поменять текст или язык; быстро перевести одну фразу на 10 языков и так далее. Конечно, мы понимаем, что это текст онлайн-переводчика – качество будет соответствующим.

Евгений Намоконов и Ренат Шагабутдинов, а еще мы ведем канал в телеграмме, где разбираем разные кейсы с Google Таблицами, если вам интересно — заглядывайте в гости, ссылку можно найти в моем профиле.

• Google Docs
• MS Excel
• формулы
• google sheets

Создание выпадающего списка в таблице Google

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

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

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

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

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

Основы создания списка

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

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

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

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

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

Алгоритм создания

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

Ввод элементов списка в таблице Google

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

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

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

Проверка данных в таблице Google

Вот теперь откроется окно, в котором можно вносить ваши «пожелания», а точнее, ставить перед таблицей Google определённые задачи.

Контекстное меню проверки данных в таблице Google

В этом окне вы столкнётесь с такими параметрами, как:

• диапазон ячеек;
• правила;
• действия для неверных данных;
• оформление.

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

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

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

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

Ниже возле параметра «Для неверных данных» разработчики расположили два варианта действий:

Использование выпадающего списка в таблице Google

Остаётся последнее предложение разработчиков, активирующееся при условии постановки галочки в чекбоксе возле параметра «Оформление». Сразу же после этого ниже возникнет строка, в которой вам будет предложено ввести интересующее вас предупредительное текстовое сообщение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Создаём простой список в таблице

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

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

Создадим лист 2, где будет расположена наша справочная информация.

Скопируйте эти символы ☐,☑ в соседние ячейки столбца, А листа 2.

Можно поступить по-другому. Установите курсор в ячейку А2, введите формулу

=символ (9744) или

=char (9744)

и нажмите Enter. В результате вы получите пустой чекбокс ☐.

Переместитесь в ячейку А3 и вставьте похожую формулу

=символ (9745) или

=char (9745)

В результате этой формулы вы получите активный (отмеченный) чекбокс ☑.

В соседнем столбце В того же листа 2 укажем фамилии наших сотрудников.

Теперь на листе 1 в столбцах H и G добавим информацию об исполнителе заказа на продажу шоколада и о том, выполнен ли этот заказ.

Добавляем заголовки столбцов H и G.

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

Сначала заполним информацию о выполнении заказа. Для этого выделим диапазон ячеек, куда нужно вставить чекбокс — G2: G21.

Переходим в Меню -> Данные -> Проверка данных.

Далее можно действовать двумя способами.

Рассмотрим сначала более простой вариант.

В Правилах выбираем Значение из списка.

Вставляем в поле справа скопированные заранее символы пустого и активного чекбокса через запятую.

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

Жмем «Сохранить».

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

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

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

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

Выделяем диапазон H2: H21, в который вы будем вставлять фамилии менеджеров, которые занимались выполнением заказов.

Переходим в Меню -> Данные -> Проверка данных.

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

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

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

Ведь наша таблица с заказами вполне может быть очень большой со множеством колонок и строк. И совсем не обязательно портить внешний вид рабочей таблицы лишними данным.

Кроме того, они могут вам потом просто мешать рядом с основными данными и создавать этим путаницу.

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

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

Совершенно аналогичным образом можно создать и список с чекбоксами. В качестве диапазона значений выберите на листе 2 ячейки A2: A3. Далее действуйте согласно приведённых выше рекомендаций.

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

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

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

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

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

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

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

Создаем автоматически пополняемый список в Google таблице

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

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

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

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

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

Теперь изменим настройки выпадающего списка в ячейках H2: H21. Выделим эти ячейки, затем, как обычно, переходим в Меню -> Данные -> Проверка данных.

В пункте «Правила» меняем ссылку — теперь это будет ссылка на весь столбец D на листе 2.

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

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

Все значения из столбца D на листе 2 автоматически становятся значениями списка. Согласитесь, это очень удобно.

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

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

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

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

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

Простой способ удаления всех списков из Google таблицы

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

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

Для того, чтобы быстрее выделить все необходимые ячейки, можно воспользоваться комбинацией клавиш Ctrl+A.

Установите курсор на любую ячейку этой таблицы, нажмите Ctrl+A, и она вся целиком будет выделена. Либо нажмите серую кнопку в левом верхнем углу таблицы.

После этого действуем так, как уже ранее было описано:

Переходим в Меню -> Данные -> Проверка данных.

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

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

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

Действуйте так, как мы с вами только что сделали, и у вас всё получится.