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

Обновлено: 14.05.2024

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

Что такое Google Таблицы

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

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

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

Как начать работу Google Таблицами

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

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

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

Теперь можно начинать работу с таблицей — далее расскажем про рабочую область и основные инструменты документа.

Рабочая область в Google Таблицы

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

При наведении курсора на каждый элемент система подскажет его функцию

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

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

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

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

Как работать с ячейками, столбцами и строками

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

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

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

Перечислим наиболее распространённые:

Сtrl + пробел — поможет выделить конкретный столбец;
Shift + прoбел — это сочетание позволит вам выделить определённую строку;
Сtrl + Enter — так вы сможете заполнить диапазон;
Сtrl + К — удобный способ, чтобы вставить ссылку;

Эти горячие клавиши подходят только для компьютеров с системой Windows, для MacOS и Linux будут другие сочетания.

Топовую подборку профессиональных курсов по Google-таблицам с нуля

Форматы и форматирование в Google Таблицах

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

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

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

Как сортировать данные в Google Таблице

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

В сервисе настроить фильтрацию данных двумя способами:

Фильтрация и сортировка помогают работать с большим объёмом данных

Научиться работать с гугл-таблицами помогут курсы из нашей подборки
Топ-4 курсов по обучению работе в Google-таблицах

Сводные таблицы

Эта часть статьи будет актуальна тем, кто работает с аналитикой. Главная задача сводных таблиц — структурирование информации, чтобы можно было впоследствии сделать корректный вывод. В сводных таблицах данные из разных ячеек автоматически выводятся в новы — с помощью заданных заранее формул. Если нужно сделать из 2000 строк отчёт на 20 позиций, то сводные таблицы вам помогут.

Визуализация данных в Google Таблицах

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

Функции в Google Таблицах

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

Бонусные функции Google Таблиц

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

Google Формы

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

Google Analytics

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

Коротко о главном

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

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

Зависимый выпадающий список в Excel и Google таблицах

20 июля 2021 г. 4801

Введение

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

Рассмотрим особенности создания выпадающих списков на примере:

Исходные данные:

Задача:

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

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

Рабочие файлы по ссылке ниже

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

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

Выпадающий список готов!

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

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

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

Зависимый выпадающий список адресов готов.

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ , обработаем значения функцией ПОДСТАВИТЬ .

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"-";"_"))

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

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

Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?

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

Как сделать выпадающий список уникальных значений в Excel?

В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .

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

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

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ , которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ( $B$2:$B$18 )

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ , которая подсчитывает количество встретившихся в диапазоне ( $B$2:$B$18 ) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

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

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

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

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

Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"_";"-"))

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

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

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A . Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

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

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

В ячейке G6 укажем:

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

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

Заключение

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

Изучить работу в программе Excel Вы можете на наших курсах: Онлайн-курсы по Excel

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

Автоматическое разделение таблицы Excel на книги

30 марта 2021 г. 1295 MS Excel Code

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

Как сделать анализ состава тела дома без умных весов

20 мая 2021 г. 1318 Info Health

В этой статье мы расскажем вам, как сделать анализ состава тела за 5 минут, не выходя из дома. ИМТ, процент жира, масса мышц и многое другое в одном отчете!

Автоматизация печати документов. Печать графика работы в Excel

14 сентября 2021 г. 425 MS Excel Code Video File

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

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

2 ноября 2021 г. 385 MS Excel Google Sheets Video File

Как привести телефонные номера к единому формату? Как сократить длинные ссылки? Как извлечь домены? Ответы на эти и другие похожие вопросы Вы найдете в статье.

Добавление ячеек в Google Таблицах осуществляется следующим образом:

1. Кликнуть правой кнопкой мыши по ячейке, рядом с которой необходимо вставить еще одну ячейку;

3. Выбрать один из вариантов:

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

Удаление ячеек

Удаление ячеек в Google Документах осуществляется следующим способов:

1. Кликнуть правой кнопкой мыши по ячейке, которую необходимо удалить;

3. Выбрать один из вариантов:

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

Выделение диапазонов данных

Существует несколько способов выделения различных диапазонов данных в таблицах. Мы подготовили видео-урок на эту тему:

Размеры ячеек

Ширина столбца

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

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

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

Ширина столбца

Высота строки

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

1. Потянуть за нижний край той строки, которую вы хотите увеличить или уменьшить.

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

Высота строки

Объединение ячеек

Объединены могут быть как ячейки в одной строке/столбце, так и сразу в нескольких.

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

Объединить ячейки

Мощнейшая функция СМЕЩ (OFFSET) позволяет решать много задач в Excel и Google Таблицах. В Excel, например, можно создавать динамические диаграммы, в которых меняется масштаб и точка отсчета. А в Google Таблицах - получать из диапазона только определенную часть данных в зависимости от выполнения определенного условия. Например, получать из списка всех книг - как вышедших в продажу, так и готовящихся, только те, что еще не вышли. Причем тот список, что вы получите, будет обновляться автоматически при обновлении исходных данных. Пример - в этой короткой статье. Конечно, его можно применить в любой отрасли, а не только с книгами 🙂

Допустим, у вас есть список книг, часть из которых уже вышла (данные о продажах в примере вымышленные), часть - в редакции:

Задача - на другом листе (вкладке) или просто в другом месте сформировать автоматически меняющийся список только тех книг, которые еще не вышли (со статусом "В редакции"). Для этого и пригодится функция OFFSET. Ее синтаксис:

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

Число_строк - отступ от ячейки вниз (или вверх при отрицательном аргументе), заданной в предыдущем аргументе;

Число_столбцов - отступ от ячейки вправо (или влево при отрицательном аргументе), заданной в первом аргументе;

Высота - высота итогового диапазона;

Ширина - его ширина.

Формула кажется сложной, но несколько примеров помогут ее понять.

Итак, следующая формула задает диапазон A1:A9.

=OFFSET(A1;0;0;9;1 ) - диапазон, начинающийся в A1, без отступов от нее, с высотой 9 и шириной 1.

А если мы добавим отступ по столбцам и строкам (на 1 строку и на 1 столбец) и увеличим ширину до трех, получим диапазон

=OFFSET(A1;1;1;9;3 ) - диапазон, начинающийся в B2 (на строку ниже A1 и на столбец правее) с высотой 9 и шириной 3.

А как же сделать диапазон динамическим? Как получить только строки по книгам, находящимся в редакции?

Идем по порядку: очевидно, точкой отсчета может быть ячейка A1 (от нее будем уходить вниз до первой книги, которая еще не в работе), а ширина диапазона - 4 (столько у нас столбцов с данными). Отступать по столбцам вправо/влево смысла нет - ширина диапазона фиксированная, отступ равен нулю. Осталось два неизвестных значения 🙂

Раз мы хотим получить данные по книгам, которые находятся в редакции - то количество строк должно совпадать с количеством таких книг, верно? Его можно вычислить с помощью функции COUNTIF (СЧЕТЕСЛИ в Excel):

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

А какой должен быть отступ по строкам? Мы должны найти, где начинаются книги "В редакции". Это можно сделать с помощью функции MATCH (ПОИСКПОЗ в Excel):

Эта формула определяет, в какой строке впервые в столбце D встречается фраза "В редакции". В нашем примере это 14. Но так как функция OFFSET начинает отсчет с ячейки A1, нам нужно из 14 вычесть единицу: отступив на 13 строк от A1, мы и попадем в четырнадцатую строку - ту, где начинается список книг в редакции.

Вот так это выглядит вместе:

=OFFSET(A1;MATCH("В редакции";D:D;0)-1;0;COUNTIF(D:D;"В редакции");4)

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

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

Например, можно суммировать данные за N месяцев, меняя N в какой-то ячейке.
= SUM(OFFSET(A1;0;0;N;1))
Такая формула будет суммировать N ячеек, начиная с A1. N нужно брать какой-нибудь ячейки.

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