Как сделать выпадающий список в Google таблицах
Есть там одна очень классная штука – возможность сделать выпадающий список в ячейке. То есть, вам нужно в ячейку добавлять заранее определенные значения из какого-нибудь справочника. Можно это делать руками – но, тогда у вас могут появляться опечатки, что может привести к дальнейшим неточностям анализа данных. А можно сделать выпадающий список в таблице и брать значения из ранее подготовленного перечня.
Вот это я иногда делаю, но перерыв между действиями большой, поэтому забываю, как это делается. В google-справке ответ есть, но лучше его продублирую подробнее, чтобы можно было к нему быстро обратиться.
Что хочу получить?
В такой таблице, в столбец «Значения из справочника» хочу выбирать значения из спец.справочника, который сделаю отдельно.
Как сделать выпадающий список в Google таблицах
Создаем справочник с необходимыми значениями
Тут всё просто – создаем новый лист (я назвал его справочники, так как справочников может быть много), и добавил туда перечень значений, которые должны будут использоваться в выпадающем списке:
Выделяем ячейки, в которых должен появиться выпадающий список
Переходим в пункт меню Google таблиц «Данные -> Настроить проверку данных»
В диапазон ячеек уже записаны выделенные ранее ячейки.
Добавляем правило
В поле «Правило» добавляем значение «Значения из диапазона» (устанавливается по умолчанию), и указываем диапазон – нажимаем на кнопку с решеткой и указываем диапазон значений из нашего справочника:
В появившееся поле «Выберите диапазон данных» добавляем нужный диапазон. Можно всё заполнить мышкой – переключаетесь на нужный лист в Google таблицах, выделяете ячейки – перечень автоматически добавится в поле ввода. Нажимаете кнопку «ОК»:
В этом окне можно поиграться с настройками, например, запретить ввод неправильных данных. Нажимаете кнопку «Сохранить».
Работаем с выпадающим списком
Теперь наш основной лист будет выглядеть следующим образом:
Появился характерный треугольник выпадающего списка. Нажимаем на него мышкой и выбираем то значение, которое нам нужно (либо начинаем вводить текст, Google таблицы автоматом найдут подходящий элемент выпадающего списка):
Но! Мы же можем руками случайно ввести не то значение. Если вы не запретили ввод неправильных значений, тогда появиться следующее предупреждение:
Красный треугольник предупреждает, что данные введены не из справочника. Наведя мышку на этот треугольник, поучиться сообщение чуть детальней.
Если вы на четвертом шаге указали запрет ввода неправильных значений, тогда можно будет в ячейки добавить только значение из справочника. Если попробовать ввести неправильное значение – появится сообщение:
Еще можно подсказку добавить, или убрать выпадающий список (вводящий сам должен будет значение корректно заполнить). И есть много других параметров проверки данных, которые уже не относятся к создания выпадающего списка.
Вот теперь хорошо запомнил эту информацию и далее буду уже на автомате её использовать. И другим, надеюсь, это тоже поможет.
Источник
Полезные функции Google Таблиц, которых нет в Excel
Cтатья написана в соавторстве с Ренатом Шагабутдиновым.
В этой статье речь пойдет о нескольких очень полезных функциях Google Таблиц, которых нет в Excel (SORT, объединение массивов, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)
Очень много букв, но есть разборы интересных кейсов, все примеры, кстати, можно рассмотреть поближе в Google Документе goo.gl/cOQAd9 (файл-> создать копию, чтобы скопировать файл себе на Google Диск и иметь возможность редактирования).
Оглавление:
Если результат формулы занимает больше одной ячейки
Сначала про важную особенность отображения результатов формул в Google Таблицах. Если ваша формула возвращает более одной ячейки, то весь этот массив отобразится сразу и займет столько ячеек и столбцов, сколько для него потребуется (в Excel для этого нужно было бы во все эти ячейки ввести формулу массива). На следующем примере посмотрим, как это работает.
Поможет отсортировать диапазон данных по одному или нескольким столбцам и сразу вывести результат.
=SORT(сортируемые данные; столбец_для_сортировки; по_возрастанию; [столбец_для_сортировки_2, по_возрастанию_2; . ])
(здесь и далее — примеры для российских региональных настроек таблицы, рег. настройки меняются в меню файл → настройки таблицы)
Как в SORT добавить заголовки таблицы?
С помощью фигурных скобок <> создаем массив из двух элементов, шапки таблицы A1:B1 и функции SORT, элементы отделяем друг-от-друга с помощью точки с запятой.
Как объединить несколько диапазонов данных и отсортировать (и не только)?
Давайте рассмотрим, как можно объединять диапазоны для использования в функциях. Это касается не только SORT, этим приемом можно пользоваться в любых функциях, где это возможно, например в ВПР или ПОИСКПОЗ.
Кто читал предыдущий пример уже догадался, что делать: открываем фигурную скобку и собираем массивы для объединения, отделяя их друг-от-друга точкой с запятой и закрываем фигурную скобку.
Можно объединить массивы и не использовать их в формуле, а просто вывести на лист, скажем, собрав данные с нескольких листов вашей книги. Для вертикального объединения необходимо соблюсти только одинаковое кол-во столбцов во всех фрагментах (у нас везде по два столбца).
А на скриншоте ниже — пример горизонтального объединения, в нем вместо точки с запятой используется обратный слэш и нужно, чтобы кол-во строк во фрагментах совпадало, иначе вместо объединенного диапазона формула вернет ошибку.
(точка с запятой и обратный слэш — это разделители элементов массива в российских региональных настройках, если у вас не работают примеры, то через файл — настройки таблицы, убедитесь, что у вас стоят именно они)
Ну а теперь вернемся к горизонтальному массиву и вставим его в функцию SORT. Будем сортировать данные по первому столбцу, по убыванию.
Объединение можно использовать в любых функциях, главное — соблюдать одинаковое количество столбцов для вертикального или строк для горизонтального объединения.
Все разобранные примеры можно рассмотреть поближе в
Google Документе.
FILTER
С помощью FILTER мы можем отфильтровать данные по одному или нескольким условиям и вывести результат на рабочий лист или использовать результат в другой функции, как диапазон данных.
Пример, у нас есть таблица с продажами наших сотрудников, выведем из нее данные по одному работнику.
Введем в ячейку E3 вот такую формулу:
Обратите внимание, синтаксис немного отличается от привычных формул, вроде СУММЕСЛИН, там диапазон условия и само условие отделялось бы при помощи точки с запятой.
Введенная в одну ячейку формула возвращает нам массив из 9-ти ячеек с данными, но после примеров с функцией SORT мы этому уже не удивляемся.
Помимо знака равенства (=) в условиях можно использовать еще >, >=, <> (не равно), , а для чисел или дат можно использовать все эти знаки.
Два условия и работа с датой
Давайте усложним формулу и добавим в нее еще одно условие, по дате продаж, оставим все продажи начиная с 01.02.17
Так будет выглядеть формула, если вводить аргументы условия сразу в нее, обратите внимание на конвертацию текстовой записи даты при помощи ДАТАЗНАЧ:
Интерактивный график при помощи FILTER и SPARKLINE
А знаете, как еще можно использовать функцию FILTER? Мы можем не выводить результат функции на рабочий лист, а использовать его как данные для другой функции, например, спарклайна. Спарклайн — это функция, которая строит график в ячейке на основе наших данных, у спарклайна существует много настроек, таких, как вид графика, цвет элементов, но сейчас мы не будем на них останавливаться и воспользуемся функцией без дополнительных настроек. Перейдем к примеру.
Выпадающий список. Наш график будет меняться в зависимости от выбранного сотрудника в выпадающем списке, список делаем так:
- выделяем ячейку Е2;
- меню Данные → Проверка данных;
- правила: Значение из диапазона и в диапазоне выбираем столбец с сотрудниками из исходных данных, не переживайте, что фамилии повторяются, в выпадающем списке останутся лишь уникальные значения;
Нажимаем «Сохранить» и получаем выпадающий список в выбранной ячейке:
Ячейка с выпадающим список станет условием для формулы FILTER, напишем ее.
Так это выглядит в динамике:
А вот как нарядно может выглядеть SPARKLINE с дополнительным настройками, в реальной работе, диаграмма выводит результаты деятельности за один день, зеленые столбцы — положительные значения, розовые — отрицательные.
IMPORTRANGE
Для переноса данных из одного файла в другой в Google Таблицах используется функция IMPORTRANGE.
В каких случаях она может пригодиться?
- Вам нужны актуальные данные из файла ваших коллег.
- Вы хотите обрабатывать данные из файла, к которому у вас есть доступ «Только для просмотра».
- Вы хотите собрать в одном месте таблицы из нескольких документов, чтобы обрабатывать или просматривать их.
Эта формула позволяет получить копию диапазона из другой Google Таблицы. Форматирование при этом не переносится — только данные (как быть с форматированием — мы расскажем чуть ниже).
Синтаксис формулы следующий:
IMPORTRANGE(spreadsheet key; range string)
IMPORTRANGE(ключ; диапазон)
spreadsheet_key (ключ) — последовательность символов атрибута «key=» (ключа) в ссылке на таблицу (после «spreadsheets/…/»).
Пример формулы с ключом:
В вашем файле будет отображаться диапазон A1:CM500 с Листа1 из файла, который находится по соответствующей ссылке.
Если в исходном файле может меняться количество столбцов или строк, вводите во втором аргументе функции открытый диапазон (см. также подраздел «Диапазоны вида A2:A»), например:
Лист1!A1:CM (если будут добавляться строки)
Лист1!A1:1000 (если будут добавляться столбцы)
! Имейте в виду, что если вы загружаете открытый диапазон (например, A1:D), то вы не сможете вставить никакие данные вручную в столбцы A:D в файле, где находится формула IMPORTRANGE (то есть в конечном, куда загружаются данные). Они как бы “зарезервируются” под весь открытый диапазон — ведь его размерность неизвестна заранее.
Ссылку на файл и ссылку на диапазон можно вводить не в формулу, а в ячейки вашего документа и ссылаться на них.
Так, если в ячейку A1 вы введете ссылку на документ (без кавычек), из которого нужно загрузить данные, а в ячейку B1 — ссылку на лист и диапазон (тоже без кавычек), то импортировать данные можно будет с помощью следующей формулы:
Вариант со ссылками на ячейки предпочтительнее в том смысле, что вы всегда можете легко перейти к исходному файлу (щелкнув по ссылке в ячейке) и/или увидеть, какой диапазон и из какой вкладки импортируется.
Импорт форматирования из исходной таблицы
Как мы уже заметили, IMPORTRANGE загружает только данные, но не форматирование исходной таблицы. Как с этим быть? Заранее «подготовить почву», скопировав форматирование из исходного листа. Для этого зайдите на исходный лист и скопируйте его в вашу книгу:
После нажатия кнопки Копировать в… выберите книгу, в которую будете импортировать данные. Обычно нужная таблица есть на вкладке Недавние (если вы действительно недавно работали с ней).
После копирования листа выделите все данные (нажав на левый верхний угол):
И нажмите Delete. Все данные исчезнут, а форматирование останется. Теперь можно ввести функцию IMPORTRANGE и получить полное соответствие исходного листа — как в части данных, так и в части формата:
IMPORTRANGE как аргумент другой функции
IMPORTRANGE может быть аргументом другой функции, если диапазон, который вы импортируете, подходит на эту роль.
Рассмотрим простой пример — среднее значение по продажам из диапазона, находящегося в другом документе.
Это исходный документ. Пусть данные будут добавляться и нам нужно среднее по продажам 2016 (то есть от ячейки D2 и до упора вниз).
Сначала импортируем этот диапазон:
IMAGE: добавляем изображения в ячейки
Функция IMAGE позволяет добавлять в ячейки Google Таблиц изображения.
У функции следующий синтаксис:
Или же поставить ссылку на ячейку, в которой ссылка хранится:
Последний вариант удобнее в большинстве случаев. Так, если у вас есть список книг и ссылки на обложки, достаточно одной формулы, чтобы отобразить их все:
На практике бывает, что ссылки на изображения хранятся на отдельном листе, и вы достаете их с помощью функции ВПР (VLOOKUP) или как-то иначе.
Аргумент mode может принимать четыре значения (если его пропустить, по умолчанию будет первое):
- изображение растягивается до размеров ячейки с сохранением соотношения сторон;
- изображение растягивается без сохранения соотношения сторон, целиком заполняя
- изображение вставляется с оригинальным размером;
- вы указываете размеры изображения в третьем и четвертом аргументам функции [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 Таблицами, если вам интересно — заглядывайте в гости, ссылку можно найти в моем профиле.
Источник