Apps Script и выпадающее меню

Я работаю в отделе физической безопасности одной технической компании. Для улучшения рабочих процессов я пользуюсь JavaScript в G Suite. Сам я не люблю монотонную работу, так что для меня писать скрипты, чтобы избежать повторяющихся муторных задач, — как спасательная капсула. А теперь покажу вам примеры.

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

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

Чтобы решить, как быть в этой ситуации, я взял второй инструмент из своего любимого набора — Apps Script.

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

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

А теперь подробно разберем весь процесс.

Первое и самое важное, про что я хочу вам рассказать, — мой любимейший инструмент, который управляет буквально всем, — поиск Google! Я открыл новое окошко и запросил “Обновить выпадающие списки при помощи Google Apps Script”. Ну или что-то подобное. Конечно же я нашел не один ресурс с подсказками.

Почитал не один урок и кое-какие посты со Stack Overflow. Я узнал про новое окно для скриптов приложений в главной таблице. Чтобы в него попасть, нужно пройти по пути Tools -> Script Editor (Инструменты->Редактор скриптов)

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

Переименовал скрипт для обновления списков и добавил функцию onEdit(e). Об этом чуть позже.

Затем я собрал уникальные ID из формы, которую я собирался обновлять. Сделал это так: прошел по ссылке в окошке редактирования форм и скопировал ID.

Я сохранил номера ID в переменную в новой функции под названием updateLists().

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

CONCATENATE. И вот в третьем столбце я собираю все необходимые имена.

Главная страница со списком Страница с именами для выпадающих меню

Я сохранил имена из столбца C страницы DropDownNames в массив. Сделал это за несколько шагов: сначала метод getActiveSpreadsheet() класса SpreadsheetApp, чтобы извлечь таблицу, которую я показывал выше, а потом — метод getSheetByName("DropDownNames"), чтобы пройтись по конкретной странице и собрать данные.

Затем с помощью функции getRange("C1:C") я собрал список имен из столбца C. Я собрал записи столбцов A и B в ‘C1:C’. И наконец я вызвал функцию getValues(), которая сохранит значения в ячейки диапазона во вложенном массиве. Вот как это выглядит:

Перед тем как двинуться дальше, нужно убедиться, что данные чистые. В них не должно быть пробелов в конце и в начале, а также не должно быть пустых имен. Как проверить? Запустить список имен в цикл при помощи метода trim(). Теперь передаем эти значения в пустой массив cleanEmployeeList. Вот как это выглядит:

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

Помните функцию onEdit(e), которую мы оставили ранее пустой? Она особенная. Такие функции называют триггерами и они запускаются каждый раз, когда редактируется таблица. Весь код, который мы собрали для обновления списка, не будет работать, пока мы явно не укажем ему, что делать. Мы можем сделать это, нажав кнопку воспроизведения в редакторе скрипта приложений или прикрепив функцию к другому триггеру. Список обновляется с

onEdit(e) каждый раз. То же самое происходит и с любой частью таблицы, какой бы код в функции onEditне выполнялся.

Последний шаг — добавляем метод updateList() в функцию onEdit(e).

Чтобы сделать это, я потратил два часа. Сначала возникли ошибки, а потом все начало работать корректно. Я реализовал этот код около восьми месяцев назад и до сих пор он работает идеально. Лучшая часть во всем этом — люди пользуются формами, и когда им нужно обновить данные, они могут запросто это сделать сами. А это сильный мотиватор для моей работы над подобными проектами. Я люблю помогать людям, упрощая их жизнь на работе при помощи Apps Script. Так что два часа моей работы сэкономили десятки часов, которые больше не нужно тратить каждый месяц на очистку данных или обновление форм. Это очень ценно.

Читайте также:

Читайте нас в Telegram, VK и Яндекс.Дзен

---

Перевод статьи Christopher Holmes: Making work easier with Apps Script: Drop Down menus in Google Forms

Excel раскрывающийся список с плюсом

Пару дней назад один знакомый прислал мне Excel документ и попросил его посмотреть. Когда я открыл документ, то ужаснулся: оказывается, я настолько привык к работе в гугл таблицах, что Excel стал для меня крайне не удобным. Это, конечно же, моё субъективное мнение, но если Вы познали Google Apps Script и не ведете объемные бухгалтерские документы — Google Sheets могут полностью заменить Вам стандартные приложения, типа Excel или LibreOffice Calc.

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

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

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

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

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

Шаг 1. Создаем список необходимых выражений. Я это делаю следующим образом: пролистываю лист вправо и в колонку Z (или в любую другую, до которой таблица точно «не дорастет», или вовсе на другом листе) вставляю список, например:

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

Шаг 3. В открывшемся диалогом окне, в пункте «Правила», необходимо ввести диапазон ячеек с данными. В нашем случае, это диапазон Z1:Z3. Также, можно просто выделить диапазон мышью, для чего необходимо кликнуть по изображению таблицы:

Шаг 4. Если по каким-то причинам не стоит галочка возле пункта «Показывать раскрывающийся список в ячейке» — ставим. После этого жмем кнопку «Сохранить» и вуаля: в ячейке появится раскрывающийся список.

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

Выпадающий список в Google таблице

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

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

Шаг 1.Для создания раскрывающегося (выпадающего) списка: один лист используем для хранения, здесь же добавляются новые наименования ( у нас это лист “Аналитика”), на другом, рабочие таблицы или любая другая рабочая информация (это второй  лист с рабочей таблицей для учета денежных средств “Учет ДС”)

Шаг 2.   Сначала заполнить “Аналитику” — создать перечень наименований, который необходим (будет выпадающим) для работы с таблицами учета денег. Нам для работы необходима информация по статьям движения денег и наименования наших контрагентов.

Рис.1. Создание «Аналитики» — перечня наименований для выпадающего списка

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

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

Рис.2. Проверка данных

Шаг 4. Открыть “Проверка данных”, в  блоке «Диапазон ячеек» указаны ячейки столбца “В”, которые были выделены в Шаге 3. 

Диалоговое окно «Правила:» выбрать «Значения из диапазона», «Выбрать диапазон данных». 

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

Рис.3. Значение из диапазона

Шаг 5. Перейти на лист с перечнем наименований для раскрывающегося (выпадающего) списка (лист «Аналитика») и выделить диапазон значений для отображения в выпадающем списке.. Нажать «ОК». Важно выделять не только заполненные, но и все пустые строки ниже по столбцу. Их можно будет заполнять новой информацией (они автоматически добавляются при открытии выпадающего списка). 

Рис.4. Выбор диапазона данных

Шаг 6. Открыть “Проверка данных” и проверить наличие галочки для «Показывать раскрывающийся список в ячейке». 

Блок «Для неверных данных». Можно включить «показывать предупреждение», 

Рис.5. Проверка данных

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

Рис.6. Сообщение об ошибке

Когда отмечен пункт «запрещать ввод данных» —

Рис.7. Использование пункта «запрещать ввод данных»

если вводится  наименование, которого нет в аналитике появляется сообщение об ошибке “Произошла ошибка”, и введенная информация не сохраняется.

Рис.8. Сообщение об ошибке при использовании пункта «запрещать ввод данных»

Когда в  «Оформление» отметить галочкой «Показывать текст справки для проверки данных» и сам текст сообщения написать в поле, расположенном под строкой “Оформление: 🇻 Показывать текст справки для проверки данных”

Рис.9. Использование пункта » Показывать текст справки для проверки данных”

Если выбрать «запрещать ввод данных» и в ячейку ввести наименование, которого нет в аналитике, появится окно с сообщением, с вашим текстом  для уведомления (“Вы ввели данные не из списка!”).

 После выполнения всех настроек —  «Сохранить».

Рис.10. Сохранение настроек

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

Рис.11. Работа с выпадающим списком

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

Остались вопросы по управленческому учету? 

Записывайтесь на консультацию

Как включить перевод в Google Sheets и еще 6 лайфхаков в Google Docs

06 апреля, 2019, 14:00

10659

Сертифицированный тренер Google for Education Джейк Миллер ведет блог, где описывает неочевидные приемы, которые упрощают и автоматизируют работу с документами Google Docs: как настроить шорткаты, как выкачивать картинки, как работать с таблицами и т. д. Редакция AIN.UA выбрала из его блога 7 советов, которые могут помочь пользователям Google Docs. 

1. Как массово переводить слова с одного языка на другой в Google Sheets

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

Для того, чтобы ее задать в таблице, нужно выбрать языковую пару, затем в одну ячейку вписать слово на одном языке, после чего ввести формулу =GoogleTranslate(text, source_language, target_language) во второй. Так можно переводить не только отдельные слова, но и наборы слов. Правда, нужно знать 2-буквенные коды для языка, их можно посмотреть в этом списке. 

2. Как загружать картинки из документа Google Docs

С картинками, встроенными или экспортированными в Google Docs, можно много чего делать, редактировать, менять, но только не сохранять по правому клику мышью: функции Save As здесь просто нет.

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

1. Открыть нужный документ. 
2. Выбрать в меню File -> Download As ->Web Page (.html, zipped). 
3. Загрузить файл на компьютер и затем распаковать его с помощью любой программы-архиватора. Внутри полученной папки будут все картинки из документа.

Если нужно вытянуть одну или две картинки, можно просто открыть их в Google Keep, тогда у изображений появится функция Save As. 

3. Как поменять шрифт по умолчанию в Google Docs

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

1. Набрать произвольный текст в документе Google Docs. 
2. Поменять его шрифт на тот, который пользователь хотел бы видеть шрифтом по умолчанию. 
3. Затем во вкладке Normal text выбрать опцию Update Normal text to match. Точно так же можно настраивать дефолтные стили и для других элементов текста (к примеру, заголовков).
4. После этого еще раз зайти в то же меню и выбрать Options -> Save as my default styles.

4.  Как работать с Google Calendar с помощью хоткеев

Сначала нужно включить шорткаты в настройках Google Calendar. Вот несколько примеров: 

• / позволит быстро искать по календарю. 
• Shift + / выведет список действующих шорткатов. 
• n и p помогут листать события по периодам (дням, неделям, месяцам).
• Ctrl + S поможет сохранить ивент (Command + S для macOS). Для создания нового ивента достаточно нажать с. 
• Если набрать g (как в go to date) и затем ввести дату, можно сразу найти ее в календаре. Чтобы вернуться к текущей дате, нужно набрать t (today). 
• Если при просмотре недели нужно включить детальное расписание дня, достаточно нажать d (для дня). Точно так же работает переход m для месяца, w для недели и y для года.  

5. Как создать выпадающий список для ячейки Google Sheets

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

После этого в Criteria выбрать List of ITEMs  и сформировать список через запятую, и сохранить, что получилось. 

6. Как дать разным людям доступ к редактированию разных вкладок в таблицах

Если нужно одновременно дать права редактора многим пользователям, но при этом нельзя открывать для всех целый документ, можно обойтись отдельными вкладками (Tabs) в одной таблице. Для этого нужно кликнуть правой кнопкой по вкладке, выбрать опцию Protect sheet. А затем в меню справа выбрать права доступа для конкретной вкладки так же, как обычно они устанавливаются для целого документа. 

7. Как добавить математические символы в Google Docs

Если в документе нужен текст с математическими символами, многие из них станут доступны, если выбрать Insert (как обычно вставляются рисунки или фото), а затем Equation.  

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

Для этого нужно:

1. Открыть документ. 
2. Выбрать Tools > Script Editor.
3. Нажать на поле для названия проекта Untitled Project и выбрать название. 
4. Поменять myFunction на onOpen. 
5. После { набрать DocumentApp. (включая точку).
6. Из выпадающего меню выбрать getUi : Ui. 
7. После {DocumentApp.getUi() поставить точку. 
8. Из выпадающего меню выбрать alert(String prompt) : Button. Вместо prompt набрать сообщение. 
9. Заключить его в кавычки (но так, чтобы оставаться в пределах скобок). 
10. Нажать Save. 

Как работать Google Sheets после его создания

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

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

По удобству Google Sheets стоит на первом месте также, как это было Excel таблицы в Microsoft Word, когда еще не было популярности Гугл. Здесь не будет информация, как создавать Google таблицы с нуля, так как это сделать проще простого, если у вас есть аккаунт в Google. Информация будет подана в виде списка функций, смотрите список в содержании.  

Содержание

Создать google таблицу удобным для себя

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

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

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

Добавление цветных ячеек в Google Sheets

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

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

Выделение жирным и курсивом, изменение размера текста в Google Sheets

Чтобы выделить жирным в шапке таблицы имеется функция сделать жирным «B», рядом функция » I «  чтобы положить текст в наклонном, опять же это делается для удобства и отличия текстов или цифр. Если планируется выделить жирным и сделать курсивом всю строку или столбец, то выделяется вся строка нажатием на начало самой крайней ячейки столбца или строки. 

По изменению шрифта и размера текста, здесь как в любом World документе на компьютере, в шапке Google Sheets находиться функция “Размер шрифта” из выпадающего списка цифр нужно выбрать подходящую. Изменение шрифта тоже, выбирается вариант, когда появляется выпадающий список при нажатии на “Шрифт”.

Закрепление строк и столбца в Google Sheets

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

Перенос текстов или спрятать под ячейку в Google Sheets

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

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

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

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

Добавлять комментарии в Google Sheets, если пользователь работает не один

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

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

Добавлять формулы для подсчета данных в Google Sheets

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

org/ImageObject»>

Чтобы найти функцию сложения всех чисел в графе или в строке, в зависимости от задачи, можно находить итоговую сумму добавив формулу SUM. Это функция также выделяется в нужной ячейке, например как здесь в ИТОГО, в конце столбца “С“ всех цифр нужен общий ответ. Для этого в шапке имеется функция “Вставка”, далее ниже со знаком Суммы вкладка “Функция”, которая ведет к различным формулам.

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

Если требуется сложить цифры отдельных ячеек, тогда так и пишется, C2+C5+C7 то есть складываются значения этих ячеек. Также можно эту формулу усложнить различным способом, добавляя умножения, скобки, нахождения процентов, один из них указано в примере.

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

Добавить фото изображение в Google Sheets

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

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

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

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

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

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

• В пункте “Правила” остается без изменений “Значение из диапазона”, рядом в соседней ячейке добавляются ячейки тех данных из которых должен состоять список выпадающего списка. Как на изображении достаточно добавить название ячейки А2:А7, эта формула выделить все ячейки подряд, от А2 до А7. Можно по отдельности каждую ячейку писать через запятую, тогда отражаются только они.
• “Для неверных данных”, будет всплывать окно, то есть ошибка в базе данных, но она не мешает проглядывать сам выпадающий список. Здесь все можно оставить без изменения и в конце сохранить таблицу. 

Добавить новую таблицу в другом листе

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

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

Пригласить других пользователей в Google Sheets

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

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

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

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

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

Как сортировать от большого к малому или по алфавиту данные в Гугл таблице

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

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

Итоги

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

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

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

Как сделать так, что бы данные из нескольких Google-таблиц собирались в один отчет

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

{«id»:53839,»url»:»https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet»,»title»:»\u041a\u0430\u043a \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0442\u0430\u043a, \u0447\u0442\u043e \u0431\u044b \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 Google-\u0442\u0430\u0431\u043b\u0438\u0446 \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u0432 \u043e\u0434\u0438\u043d \u043e\u0442\u0447\u0435\u0442″,»services»:{«facebook»:{«url»:»https:\/\/www.facebook.com\/sharer\/sharer.php?u=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet»,»short_name»:»FB»,»title»:»Facebook»,»width»:600,»height»:450},»vkontakte»:{«url»:»https:\/\/vk.com\/share.php?url=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet&title=\u041a\u0430\u043a \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0442\u0430\u043a, \u0447\u0442\u043e \u0431\u044b \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 Google-\u0442\u0430\u0431\u043b\u0438\u0446 \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u0432 \u043e\u0434\u0438\u043d \u043e\u0442\u0447\u0435\u0442″,»short_name»:»VK»,»title»:»\u0412\u041a\u043e\u043d\u0442\u0430\u043a\u0442\u0435″,»width»:600,»height»:450},»twitter»:{«url»:»https:\/\/twitter.com\/intent\/tweet?url=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet&text=\u041a\u0430\u043a \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0442\u0430\u043a, \u0447\u0442\u043e \u0431\u044b \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 Google-\u0442\u0430\u0431\u043b\u0438\u0446 \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u0432 \u043e\u0434\u0438\u043d \u043e\u0442\u0447\u0435\u0442″,»short_name»:»TW»,»title»:»Twitter»,»width»:600,»height»:450},»telegram»:{«url»:»tg:\/\/msg_url?url=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet&text=\u041a\u0430\u043a \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0442\u0430\u043a, \u0447\u0442\u043e \u0431\u044b \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 Google-\u0442\u0430\u0431\u043b\u0438\u0446 \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u0432 \u043e\u0434\u0438\u043d \u043e\u0442\u0447\u0435\u0442″,»short_name»:»TG»,»title»:»Telegram»,»width»:600,»height»:450},»odnoklassniki»:{«url»:»http:\/\/connect.ok.ru\/dk?st.cmd=WidgetSharePreview&service=odnoklassniki&st.shareUrl=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet»,»short_name»:»OK»,»title»:»\u041e\u0434\u043d\u043e\u043a\u043b\u0430\u0441\u0441\u043d\u0438\u043a\u0438″,»width»:600,»height»:450},»email»:{«url»:»mailto:?subject=\u041a\u0430\u043a \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0442\u0430\u043a, \u0447\u0442\u043e \u0431\u044b \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 Google-\u0442\u0430\u0431\u043b\u0438\u0446 \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u0432 \u043e\u0434\u0438\u043d \u043e\u0442\u0447\u0435\u0442&body=https:\/\/vc.ru\/finance\/53839-kak-sdelat-tak-chto-by-dannye-iz-neskolkih-google-tablic-sobiralis-v-odin-otchet»,»short_name»:»Email»,»title»:»\u041e\u0442\u043f\u0440\u0430\u0432\u0438\u0442\u044c \u043d\u0430 \u043f\u043e\u0447\u0442\u0443″,»width»:600,»height»:450}},»isFavorited»:false}

57 032 просмотров

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

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

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Обзор кейса

Вот ссылка на папку с таблицами, которые будет описаны далее.

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

Вид таблицы сотрудника

И есть таблица руководителя, содержащая форму отчета.

Вид таблицы руководителя

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

Настройка представления отчета

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

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

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

IMPORTRANGE

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

Подробнее о формуле:

=IMPORTRANGE(«Ссылка на таблицу»; «Диапазон, который нужно отобразить из этой таблицы»)

Обязательно заключайте аргументы в кавычки!

Пример функции:

=importrange(«https://docs.google.com/spreadsheets/d/14GIMUMieJBNiUGFEcukx0q0VwhdjzzQ_6R_JSWXbzNs/edit #gid=0″;»Данные!b:g»)

Для самопроверки воспользуйтесь готовой формулой

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

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

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

= СУММЕСЛИМН1+СУММЕСЛИМН2+СУММЕСЛИМН3…СУММЕСЛИМНN

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

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

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

Рассмотрим как создать такой список в таблицах Google на примере документа «График дежурства по школе»

1. Создаем на первом листе таблицу   «График дежурства по школе», на втором — список в столбик фамилий сотрудников

2. Выделяем диапазон строк, используемых для заполнения, или ячейку в которой хотите чтобы выбиралось значение из списка (а потом можно скопировать эту ячейку). 
3. Щелкаем правой кнопкой мыши и в контекстном меню выбираем опцию «Проверить данные».

4. В окне «Проверка данных» выполняем настройки для создания списка.

5. В окне «Критерии» выбираем «Элементы из списка»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В Google Таблицах есть несколько полезных функций, которые помогут вам повысить эффективность ввода данных.

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

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

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

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

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

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

Создание раскрывающегося списка с использованием диапазона ячеек

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

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

Ниже приведены шаги для создания раскрывающегося списка в Google Таблицах:

1. Выберите ячейку (или диапазон ячеек), в которой требуется раскрывающийся список
2. Щелкните опцию «Данные» в меню
3. Щелкните параметр «Проверка данных».
4. В диалоговом окне «Проверка данных» убедитесь, что выбрана правильная ячейка / диапазон. При желании вы можете изменить ячейку / диапазон в диалоговом окне «Проверка данных».
5. В параметре «Критерии» выберите вариант — «Список из диапазона».
6. В поле рядом с ним введите адрес ячейки / диапазона, в котором вы хотите открыть раскрывающееся меню.Лучше всего щелкнуть значок «Выбрать диапазон данных», а затем вручную выбрать ячейку / диапазон на листе.
7. Убедитесь, что выбран параметр «Показать раскрывающийся список в ячейке». Если этот флажок не установлен, вы не увидите значок раскрывающегося списка в ячейке / диапазоне, где они были добавлены.
8. Нажмите «Сохранить».

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

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

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

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

Ниже приведены шаги по созданию раскрывающегося списка путем ручного указания параметров:

1. Выберите ячейку (или диапазон ячеек), в которой вы хотите раскрыть список
2. Щелкните параметр «Данные» в меню
3. Щелкните параметр «Проверка данных».
4. В диалоговом окне «Проверка данных» убедитесь, что выбрана правильная ячейка / диапазон. При желании вы можете изменить ячейку / диапазон в диалоговом окне «Проверка данных».
5. В параметре «Критерии» выберите вариант — «Список элементов».
6. В поле рядом с ним вручную введите элементы (через запятую), которые должны отображаться в раскрывающемся списке.В этом примере, поскольку я хочу показать Да / Нет, я ввел бы — Да / Нет
7. Убедитесь, что выбран параметр «Показать раскрывающийся список в ячейке». Если этот флажок не установлен, вы не увидите значок раскрывающегося списка в ячейке / диапазоне, где они были добавлены.
8. Нажмите «Сохранить».

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

Копирование раскрывающегося списка

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

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

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

Если вы хотите скопировать только раскрывающийся список из ячейки, а не форматирование (например, цвет, границу или числовой формат), выполните следующие шаги:

1. Выберите ячейку, из которой вы хотите скопировать раскрывающийся список
2. Скопируйте ячейку (Ctrl + C)
3. Щелкните правой кнопкой мыши ячейку, в которую вы хотите скопировать раскрывающийся список
4. Наведите курсор на параметр «Специальная вставка».
5. Нажмите на опцию «Вставить только проверку данных».

Как изменить или удалить раскрывающийся список

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

Ниже приведены шаги по удалению раскрывающегося списка из ячейки или диапазона ячеек:

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

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

Вам также могут понравиться следующие руководства по Google Таблицам:

1. Как сделать множественный выбор в раскрывающихся списках в Google Таблицах
2. Как вставить флажок (галочку) в Google Таблицах
3. Как вставить ПУНКТЫ ПУНКТОВ в Google Таблицах
4. Как вставить символ градуса (°) в Google Таблицы
5. Как вставить изображение в ячейку в Google Таблицах

Как создать раскрывающийся список в Документах Google?

Google Документы — создание раскрывающегося списка

http: // www.youtube.com/watch?v=3WLqZxwjfh5

1) Запустите Google Документы.

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

3) Щелкните ячейку, в которой вы хотите разместить раскрывающееся меню.

4) Нажмите «Инструменты» и выберите «Проверка данных».

5) Выберите «Элементы из списка» в раскрывающемся меню «Диапазон ячеек».

6) Установите флажок «Создать список из диапазона», затем щелкните значок таблицы справа.

7) В открывшемся окне «Какие данные» щелкните и перетащите по ячейкам, которые вы хотите включить в свой раскрывающийся список.Затем нажмите кнопку «ОК» в поле «Какие данные?» всплывающее окно.

8) Нажмите кнопку «Сохранить».

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

Ранее мы рассматривали функцию проверки данных в Excel 2010, и сегодня я собираюсь рассмотреть ту же функцию в Гугл документы.26 августа 2010 г. электронные таблицы Google Docs (также известные как Google Sheets) добавили раскрывающийся список в ячейках и проверку в электронных таблицах, что на один шаг приблизило его к функциональному равенству с Excel.

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

Например, если у вас есть столбец «Статус» в электронной таблице, отслеживающий ваши различные проекты для нескольких клиентов, вы можете ограничить допустимые записи для этого столбца «Завершено», «Выполняется», «Отменено» и т.п.и т. д. Таким образом вы сделаете свою электронную таблицу Google Docs более удобной для пользователя (на меньше набора текста — вы можете просто выбрать запись из раскрывающегося меню ) и помочь предотвратить опечатки и ошибки. Есть несколько разных способов настроить это и несколько разных вариантов на выбор.

Обновление: Этот пост был первоначально опубликован в 2010 году. Некоторые названия настроек и меню были изменены. Я обновил скриншоты и некоторые из приведенных ниже шагов соответственно. Наслаждаться!

Как добавить Документы Google в раскрывающемся списке {Screencast}

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

Как добавить Документы Google в раскрывающееся меню в ячейке {шаг за шагом}

Шаг 1

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

Шаг 2

Щелкните Data и выберите Data validation… .Или вы можете щелкнуть правой кнопкой мыши выбранные ячейки и выбрать Проверка данных…

Шаг 3

В раскрывающемся меню Критерии выберите Список из диапазона.

Шаг 4

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

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

Шаг 5

Заполните дополнительные параметры.

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

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

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

Нажмите Сохраните , когда закончите.

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

Текст справки появляется в виде всплывающей подсказки при выборе ячейки.

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

Понял? Хорошо.Идите и подтвердите!

Используете ли вы проверку ячеек? Расскажите нам, как это сделать, в комментариях ниже!

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

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

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

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

• Создать новую таблицу в Google Таблицах
• Выберите ячейки, в которых должен отображаться раскрывающийся список.Вы можете выбрать целые строки или столбцы, если хотите.
• Щелкните Data > Data Validation .
• Первое поле Диапазон ячеек уже будет заполнено выбранным диапазоном ячеек. Вы можете внести в него изменения вручную, щелкнув значок таблицы в поле «Диапазон ячеек».
• В поле Criteria есть два варианта определения того, что будет включено в ваш раскрывающийся список.
  • Список из диапазона : Вы можете выбрать диапазон ячеек в своей электронной таблице, чтобы ввести элементы, которые будут включены в раскрывающийся список.
    • Создайте второй лист, щелкнув значок плюса в нижнем левом углу (необязательно).
    • Поместив курсор в поле Criteria , вы можете выбрать ячейки, в которых должно появиться раскрывающееся меню.Щелкните и перетащите, чтобы выбрать несколько ячеек или выбрать целые строки или столбцы. Появится окно, в котором вы также можете вручную ввести диапазон ячеек.
    • Затем вы можете ввести свой список элементов, которые будут включены в раскрывающееся меню, используя этот диапазон ячеек. Вы можете легко добавлять и удалять элементы, вернувшись к этому листу.
  • Список элементов: Если вы не ожидаете, что ваши критерии будут меняться слишком часто, вы можете просто вручную ввести список элементов, используя эту функцию.
    • Введите список элементов через запятую.
• Убедитесь, что установлен флажок Показать раскрывающийся список в ячейке .
• У вас есть два варианта добавления неверной информации: отображение сообщения об ошибке или просто отклонение ввода.
• Нажмите Сохранить .

Если вы хотите полностью удалить раскрывающийся список, выберите ячейки, которые вы хотите изменить, и щелкните Data > Data Validation > Remove Data Validation .

Чтобы добавить или удалить элементы, вам нужно выбрать ячейки, на которые вы хотите повлиять, и перейти к Data > Data Validation и внести изменения в запись.

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

Как запустить скрипт Python

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

Читать далее

Об авторе Нэнси Месси (Опубликовано 898 статей)

Нэнси — писательница и редактор, живущая в Вашингтоне. Ранее она работала редактором по Ближнему Востоку в The Next Web, а в настоящее время работает в аналитическом центре по коммуникациям и социальным сетям в Вашингтоне.

Более От Нэнси Месси

Подпишитесь на нашу рассылку новостей

Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!

Еще один шаг…!

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

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

Google Таблицы — это, по сути, бесплатная версия Microsoft Excel для совместной работы.

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

Вот как это сделать.

Как добавить раскрывающийся список в Google Таблицы

1. Откройте свой проект Google Таблиц или создайте новый, перейдя в sheet.new.

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

3. Щелкните правой кнопкой мыши одну из этих ячеек.

4. Выберите «Проверка данных».

Щелкните Проверка данных.Девон Дельфино / Business Insider

5. Щелкните раздел «Критерии» и выберите «Список из диапазона» или «Список элементов» и добавьте элементы, если необходимо.

Отформатируйте раскрывающийся список и нажмите «Сохранить».Девон Дельфино / Business Insider

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

Установите флажок и нажмите Сохранить.Девон Дельфино / Business Insider

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

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

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

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

Создать зависимый раскрывающийся список в Google Таблицах проще, чем в Microsoft Excel. Здесь я использую мощную функцию QUERY () для создания зависимого списка.

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

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

Для этого сначала нужно создать выпадающий список с помощью проверки данных. Затем вам нужно создать список кандидатов для второго раскрывающегося списка. Это можно сделать с помощью функции Google Sheet QUERY . Затем вы можете использовать его для создания зависимого раскрывающегося списка.

Создание основного раскрывающегося списка

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

1. Выберите ячейку , для которой нужно создать раскрывающийся список.
2. Перейдите в Данные> Проверка данных .
3. Во всплывающем окне для первого поля поля Criteria выберите «Список из диапазона » (да, это вариант по умолчанию).
4. Для диапазона данных мы используем наш список региона . Здесь я назвал диапазон « регионов ». Поэтому я ввожу диапазон как Regions в поле Data Range.
5. При необходимости добавьте проверочный текст и нажмите Сохранить .

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

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

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

Создание списка кандидатов

Для этой цели я собираюсь использовать функцию Google Sheets QUERY () . Функция выглядит следующим образом:

  = query (CountryByRegion, "выберите B, где A = '" & RegionDL & "'")  

В приведенной выше функции я использовал « Named Ranges » чтобы сделать функцию более читаемой. « A » и « B » — названия столбцов.

Аргумент CountryByRegion — это таблица, которая включает весь список регионов и стран (или вы можете использовать для этого нотацию A1; e.грамм. A2: B213). А область относится к ячейке, которая содержит основной раскрывающийся список. Вышеупомянутая функция фильтрует страны выбранного региона и создает отфильтрованный список, как показано в следующем видео.

Установить проверку данных

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

Вы можете сделать копию выше Google Sheet из по этой ссылке .

С помощью этого метода можно применить зависимый раскрывающийся список только к одной ячейке. Если вы хотите применить зависимый раскрывающийся список в несколько строк, вы можете прочитать; «Многострочный зависимый выпадающий список в Google Таблицах».

Заключение

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

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

Связанные

Выпадающий список из правил проверки данных рабочего листа

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

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

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

Определение правила проверки данных в Google Таблицах

Вы можете определить правило проверки данных в Google Таблицах двумя способами.

• Вы можете вручную ввести разрешенные значения в список проверки.

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

Если у вас много допустимых значений, мы рекомендуем второй подход. Первый подход, когда вручную вводятся допустимые значения, накладывает ограничение в 256 символов для всего списка.

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

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

2. Откройте «EnumSheet» и выделите столбец для вашего первого набора допустимых значений.

3. Введите значение заголовка столбца. В моем случае я ввел «Фрукты» в ячейку А1.

4. Введите все допустимые значения в этот столбец.В моем случае я ввел «Яблоко» в ячейку А2, «Абрикос» в ячейку А3, «Авакадо» в ячейку А4 и так далее.

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

6. Перейдите в меню «Данные» и выберите «Проверка».

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

8. «Какие данные?» появится диалоговое окно.

9. С помощью «Какие данные?» диалоговое окно все еще открыто, щелкните вкладку «EnumSheet», чтобы открыть «EnumSheet».

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

11. «Какие данные?» диалоговое окно должно теперь содержать что-то вроде «EnumSheet! A2: A15». Имя «EnumSheet» отображается слева от «!» и справа появится диапазон ячеек с выбранными допустимыми значениями.

12. Нажмите «ОК» в «Какие данные?» диалог.

13. Окно «Проверка данных» теперь будет отображать имя «EnumSheet» и диапазон ячеек, которые вы только что выбрали выше. Это допустимые значения, которые будут использоваться для проверки.

14. Нажмите «Сохранить» в диалоговом окне «Проверка данных».

15. Если у вас есть несколько наборов разрешенных значений, вы можете добавить столбец в «EnumSheet» для каждого набора разрешенных значений. Например, вы можете добавить столбец для допустимых значений «Овощи», другой столбец для допустимых значений «Животных» и т. Д.Повторите шаги 2–14 для каждого из допустимых значений.

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

17. Создайте приложение AppSheet из книги (используйте надстройку). AppSheet прочитает «Таблицу данных», чтобы получить значения ваших данных. Он прочитает «Enum Sheet», чтобы получить допустимые значения.

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

Определение правила проверки данных Microsoft Excel

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

1. Введите значения перечисления в специальный столбец на отдельном листе Excel, как описано в шагах с 1 по 4 выше.

2. На вкладке Excel «Формулы» нажмите «Определить имя», чтобы определить новое имя, которое относится к рабочему листу и ячейкам, созданным на шаге 1. При отображении диалогового окна «Новое имя» введите такое значение, как «FruitEnum» в атрибуте «Имя». Щелкните значок справа от атрибута «Относится к». Выберите рабочий лист, содержащий значения перечисления, и выберите диапазон ячеек, содержащий значения перечисления. Щелкните «ОК».

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