что такое именованный диапазон в гугл таблице
Cоветы и руководства по Google Таблицам
Мастер Google Таблиц с полезными советами и руководствами
Как создавать именованные диапазоны в Google Таблицах (статические и динамические)
Именованные диапазоны в Google Таблицах можно использовать для определения ячейки или диапазона ячеек и присвоения им имени. Теперь вместо использования ссылок на ячейки вы можете использовать в расчетах имя именованного диапазона.
Например, предположим, что у меня есть данные о транзакциях продаж для магазина, и мне нужно рассчитать такие показатели, как сумма всех продаж, количество продаж товара A, средняя стоимость продажи, максимальная / минимальная стоимость продажи.
Вместо использования ссылок на ячейки диапазона, в котором хранятся данные о продажах, я могу создать именованный диапазон (с описательным именем, например «SalesData») и использовать вместо него это имя.
Использование именованных диапазонов дает следующие преимущества:
Создание именованного диапазона в Google Таблицах
Предположим, у меня есть данные транзакции продаж, как показано ниже, и я хочу создать именованный диапазон для данных продаж:
Вот шаги, чтобы создать именованный диапазон в Google Таблицах:
Это создаст именованный диапазон — SalesData.
После создания вы можете использовать именованный диапазон в формулах. Вот несколько примеров использования именованного диапазона SalesData:
Правила именования при создании именованных диапазонов в Google Таблицах
При создании именованных диапазонов в Google Таблицах необходимо соблюдать некоторые правила.
Редактирование именованного диапазона
После создания именованного диапазона вы можете изменить или обновить его.
Например, если у вас есть именованный диапазон, который ссылается на данные о продажах, и у вас есть три новые транзакции, которые необходимо добавить к данным, вам придется обновить именованный диапазон, чтобы включить эти три дополнительных точки данных.
Вот шаги, чтобы обновить / отредактировать именованный диапазон в Google Таблицах:
Создание динамического именованного диапазона в Google Таблицах
Именованные диапазоны великолепны, поскольку они позволяют вам обновить именованный диапазон один раз, и все формулы, использующие этот именованный диапазон, обновляются автоматически.
Однако выполнение этого вручную может показаться утомительным, если вам нужно часто обновлять данные или если у вас слишком много именованных диапазонов.
В Excel можно создать именованный диапазон с помощью формулы OFFSET или INDIRECT, но в Google Таблицах это не работает.
Однако вы можете сделать это, используя изящный трюк с функцией INDIRECT.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите создать именованный диапазон для данных о продажах таким образом, чтобы при добавлении новых данных именованный диапазон автоматически обновлялся.
Вот шаги для создания динамического именованного диапазона в Google Таблицах :
Теперь вы можете использовать следующую формулу для ссылки на динамический именованный диапазон: = INDIRECT (SalesData).
Функция INDIRECT будет использовать именованный диапазон и ссылаться на ячейку F2, которая, в свою очередь, содержит ссылку на данные о продажах. Поскольку мы сделали диапазон в F2 динамическим (используя = «Sheet1! C2: C» & E2), именованный диапазон также становится динамическим.
Например, если теперь вы хотите рассчитать сумму продаж, вы можете использовать формулу =SUM(INDIRECT(SalesData)). Если добавлены другие записи транзакций, формула автоматически обновится и выдаст новую сумму продаж.
Google Spreadsheets
История #1: Динамические диапазоны
Предисловие
Каждый геймдизайнер так или иначе сталкивается в работе с использованием таблиц, а если не сталкивается — пусть отложит свой блокнот, в котором считает баланс и пишет диздоки и столкнется уже, наконец, с таблицами.
Я начну писать о том, как можно быстро и удобно пользоваться гугл таблицами для всего, что можно себе только представить в геймдизайне. Вряд ли это будет разбор того, как использовать те или иные функции в подсчёте баланса, но разбор самых удобных и тех, которые лично я использую чаще всего (с примерами) — точно.
Первый мой пост не самый легкий, но с чего решил, с того решил.
PS: Писать все примеры я буду на русском языке, так как подавляющее большинство всё-таки сидит на русских таблицах от Google.
Что такое диапазоны данных?
Диапазоны данных — это выпадающее меню в выбранной ячейке таблиц со списком заданных параметров.
Диапазоны данных очень удобная вещь. Нужны они, минимум, для того, чтобы не вводить данные вручную, и чтобы все данные были в одном виде. А дальше уж, как их использовать, ограничивается только фантазией.
Кто не знает, то диапазоны данных можно задавать следующим образом:
Если у кого-то всё на английском — обратитесь к справке Google 🙂
Именованные диапазоны
Также в Google таблицах есть возможность именовать диапазоны. То есть, если вы раньше вводили в проверку данных диапазон вида A:A, то теперь, можно просто ввести «Диапазон1», и он будет знать, что это такое.
Именованные диапазоны можно также использовать в различных формулах, что тоже очень удобно.
Чтобы создать именованный диапазон:
Динамические диапазоны данных
Подошли к самому интересному
Теперь, благодаря диапазонам, мы можем сделать целую выборку данных, которые будут зависеть друг от друга.
К примеру: Выбрать «Правая рука», чтобы в следующей строке появились предметы, доступные только под правую руку. Выбрать «Голова», чтобы в следующей строке появились предметы, доступные на голову и так далее.
Подготовка материала
Создаем вот такой список диапазонов:
Далее, создаем именованные диапазоны для наших списков.
Далее — создаем ячейки с именованным диапазоном главной группы, то есть «ОСНОВНАЯ»
Вторую колонку оставляете пустой.
Переходим к написанию логики. Для этого нам потребуется Google script. Доступен он во вкладке «Инструменты» — «Редактор скриптов»
Называть его можно как угодно. Вставляем туда следующий код:
function depDrop_(range, sourceRange) <
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
range.setDataValidation(rule);
>
function onEdit () <
var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
var aColumn = aCell.getColumn();
if (aColumn == 1 && SpreadsheetApp.getActiveSheet()) <
var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
>
>
Должен получиться следующий вид:
Сохраняем, жмем на «Выполнить» (кнопка, похожая на кнопку Play кассетных плееров) Код попросит разрешение, выдаем его и идём в наш документ с диапазонами.
Теперь, при выборе в основной группе, справа в колонке +1 будет появляться подгруппа с диапазоном данных (видео — для понимания)
Все, конечно, хорошо, удобно и работает, но! При удалении данных из столбца А, данные остаются в В. Что не круто.
Для того, чтобы данные из В очищались, нужно добавить в код следующий кусок скрипта:
Получится общий вид вот такой:
Делайте копию — код и всё остальное будет работать.
Послесловие
В принципе, после таких манипуляций можно сделать очень гибкую систему для постройки различных калькуляторов.
Можно пойти дальше и добавить ещё несколько колонок с данными. Формулы, автозаполнение и так далее.
Google Docs и без GS очень гибкий и мощный инструментарий, а с GS так вообще можно неимоверные вещи творить.
Google Sheets формулы
В данной статье мы поговорим о том, что такое формула в Гугл таблицах и как ими пользоваться — как вставлять и удалять, переносить и копировать, рассмотрим формулы массивов в Гугл таблицах, научимся считать используя формулы.
Какие бывают типы формул в Гугл таблицах
Формулы в Гугл таблицах бывают двух видов: простые и комплексные. Простые как правило содержат константы, ссылки на ячейки внутри рабочего листа и операторы. Комплексные формулы могут содержать гораздо больше данных – ссылки на ячейки, одновременно несколько функций, константы, операторы, имена диапазонов.
Элементы формул в Гугл таблицах
Давайте поближе познакомимся с элементами формул в гугл таблицах.
Константы – это какие-то фиксированные данные, чаще всего цифры.
Операторы – это символы вычитания, сложения, деления и умножения, символом деления служит так называемый слеш /.
Ссылки на ячейки – очень часто формулы содержат ссылки на ячейки внутри рабочего листа или ссылки на ячейки на других листах рабочей книги. Ссылки бывают трех типов – относительные, абсолютные и смешанные.
В данном случае мы вычитаем содержание ячейки A4 из ячейки A2. При копировании данной простой формулы в другую ячейку, поменяются адреса ячеек и в формуле. Например, если скопировать эту ячейку и вставить в ячейку C1, то мы увидим, что адреса в ссылках внутри формулы также сменились: 
Абсолютные ссылки выглядят таким образом: =$A$2-$A$4 
Теперь куда бы мы не скопировали нашу простую формулу, то ссылки по прежнему будут указывать на ячейки A2 и A4: =$A$2-$A$4 
Смешанные ссылки имеют такой вид: A$2-A$4 
Скопировав данную формулу в нашу новую ячейку C1, мы получим следующий результат: =С$2-С$4 
Как видим, столбец сменился на C, потому что ссылка столбца относительная, а строки остались по прежнему 2 и 4, поскольку на строки мы указали абсолютные ссылки.
Имена диапазонов – как и в excel, в гугл таблицах диапазонам и отдельным ячейкам можно давать имена. Делается это для того, чтобы было легче проводить вычисления в больших таблицах.
Кроме того, если именованные диапазоны даже перенести в другое место, то результаты вычислений останутся не тронутыми в отличие от диапазонов с содержанием относительных ссылок. Более подробно именованные диапазоны мы рассмотрим чуть ниже.
Функции – так же как и в excel, гугл таблицы имеют функции. Например, СУММ (SUM), СЧЕТ(COUNT) и т.д. Все это элементы формул, которые могут творить чудеса в умелых руках, ниже мы рассмотрим несколько примеров простых и комплексных формул.
Как в Гугл таблицах вставить формулу, отредактировать ее и скопировать
Или нажмите F2 на вашей клавиатуре.
Для редактирования формулы необходимо так же выделить ячейку с формулой, которую вы хотите отредактировать и нажмите F2 на клавиатуре или же поставьте курсор в строку формул.
Чтобы удалить формулу просто выделите ячейку с формулой и нажмите Delete на вашей клавиатуре.
Скопировать формулу можно двумя способами:
Но что, если нам формула в новой ячейке не нужна, а только значение? Для того, чтобы вставить в новую ячейку только значение ячейки где есть формула, нам надо сделать пару действий.
После того как мы вставили данные в новую ячейку появится вот такая подсказка: 
По нажатию на нее откроется небольшое контекстное меню: 
В нем нам необходимо выбрать пункт Вставить только значения.
Как создать формулу в Гугл таблицах с данными расположенными на разных листах
Бывают случаи, когда необходимо посчитать сумму значений диапазонов, которые расположены на разных листах вашей Гугл таблице.
Для того, чтобы посчитать в Гугл таблицах значения, которые находятся на другом листе, необходимо использовать формулу, в состав которой входит ссылка на тот самый лист.
Например, у нас есть диапазон данных B4:B8 на первом листе: 
Обратите внимание на то, что ссылку на Лист1 необходимо взять в одинарные кавычки, иначе формула не будет работать. После ссылки на необходимый лист ставим символ восклицательного знака и диапазон ячеек значения которых мы хотим посчитать.
Весь диапазон мы закрываем в скобки и перед ними подставляем функцию СУММ, которая будет суммировать все значения в данном диапазоне.
Но что, если нам необходимо посчитать значения с нескольких листов в нужных нам диапазонах?
Например, посчитать данные из вышеуказанного диапазона B4:B8 на первом листе и данные диапазона B4:B6 на втором листе. И все это суммировать в одной ячейке.
Формулы в Гугл таблицах – примеры
В предыдущих разделах статьи мы уже рассмотрели несколько примеров формул содержащих ссылки, в том числе и ссылки на другие листы.
В данном же разделе статьи мы рассмотрим самые простейшие формулы, формулы содержащие именованные диапазоны и формулы массива в Google таблицах.
Простая формула в Гугл таблицах
Простая формула, как правило это формула содержащая пару констант и операторы:
Данная формула выполнит умножение содержимого в ячейках A2 и B4 в нашей Гугл таблице.
А вот пример формулы в которой есть константа и ссылка на ячейку:
Такая формула сложит цифру 25 и содержимое ячейки C3.
Формулы с именованными диапазонами в Гугл таблицах
Именованные диапазоны упрощают жизнь человеку работающему с большим массивом данных в таблицах, в том числе и Гугл таблицах.
Для начала, нам необходимо дать диапазонам имена, а потом уже мы их будем использовать в формуле.
Итак, для того, чтобы диапазону в Гугл таблицах дать имя, необходимо выделить диапазон, которому мы хотим дать имя, потом кликнуть правой кнопкой мыши и в контекстном меню выбрать пункт Определить именованный диапазон. 
После этого откроется сайдбар справа, где нам будет предложено ввести имя диапазона. По умолчанию в поле для имени будет написано ИменованныйДиапазон1 (если, конечно, это первый диапазон, которому мы даем имя, в рамках данной таблицы): 
Давайте дадим ему название «Январь». Предположим, что это расчет доходов за январь.
Ниже указан будет наш выделенный диапазон на данном листе. Его можно поменять и в том числе сам Лист. Жмем Готово ниже.
Теперь этот диапазон можно использовать при расчетах в формулах. Например, мы хотим суммировать весь доход за Январь. Для этого добавим ячейку Январь и рядом с ней в ячейку введем следующую формулу: =СУММ(Январь) — по умолчанию Гугл таблицы все формулы переводят на английский вариант, в данном случае СУММ превратилось в SUM: 
Теперь куда бы мы не переносили наш диапазон Январь, он всегда будет содержать именно те данные, которые в нем указаны и мы можем делать расчеты не задумываясь о том, в каких ячейках и листах теперь он находится. Удобно, не так ли?
Давайте теперь попробуем произвести расчет формулы с нашим диапазоном, но перенесем расчет на другой лист:
Как видите, все тот же диапазон, то же имя, но другой лист. И никаких затруднений с ячейками и номерами листов! Точно так же мы можем переместить (не скопировать!) наш диапазон в другое место или даже лист. Для этого просто нажимаем сочетание клавиш Ctrl + X для того, чтобы вырезать диапазон и Ctrl + V для того, чтобы вставить в другом месте.
Формулы массива в Гугл таблицах
Массивы как правило включают в себя несколько функций и используются массивы для более сложных вычислений.
Например, у нас есть таблица с данными по продажам нескольких менеджеров и мы хотим узнать максимальные продажи конкретного менеджера, конкретного продукта: 
Мы получили значение максимальной продажи Груш менеджером по продажам Максимом.
Вот и подошла к концу данная статья, надеюсь она была полезна. Если я что-то пропустил или у вас есть дополнительные вопросы, то задавайте, я постараюсь ответить на них.
Google Таблицы. Это просто. Функции и приемы
Google Таблицы – прекрасный редактор таблиц, позволяющий работать в режиме онлайн в любом браузере. Эта книга покажет вам, какие возможности есть у Таблиц и насколько редактор удобен для совместной работы и аналитики. Авторы собрали самые полезные функции, рассказали о том, как с ними работать, и поделились своим опытом и кейсами. Как рассчитать зарплату за неполный месяц и увидеть, кто опоздал на работу? Как построить топ продаж и вычислить план на месяц? Вы найдете не только готовые решения задач и примеры автоматизации, но и источник для вдохновения. Если вы хотите иметь под рукой справочное руководство и сэкономить время на работе с данными – эта книга для вас.
Оглавление
Приведённый ознакомительный фрагмент книги Google Таблицы. Это просто. Функции и приемы предоставлен нашим книжным партнёром — компанией ЛитРес.
Работа с формулами и диапазонами
• Любая формула, как и в Excel, вводится со знака «равно».
• Текст указывается в кавычках, после названий листов ставится восклицательный знак, названия листов берутся в апострофы, если в них есть пробелы (‘Название листа’!A1).
• Аргументы функций разделяются символом (каким именно — зависит от региональных настроек), для России это точка с запятой.
• Если у вас в настройках выбран английский язык, то вы все равно можете вводить привычные русские названия, но всплывающих подсказок в этом случае не будет. После ввода русские названия будут автоматически заменены на английские.
Эта глава будет полезна тем, у кого совсем мало опыта в написании формул. В ней я по шагам расскажу, как начать формулу; как выбрать диапазон; что делать, если он на другом листе; что нажать, чтобы перейти от выбора диапазона к условию; как сделать ссылки абсолютными и не потратить на все это слишком много нервов.
Возьмем простую формулу СУММЕСЛИ (SUMIF).
По ссылке https://goo.gl/1dIZMI вы найдете Google Документ с примером, на котором можно потренироваться. Для редактирования выберите:
Чтобы немного усложнить себе задачу, вводить формулу мы будем на одном листе, а диапазон суммирования и диапазон условия — на другом (оба листа должны находиться в одном документе — в отличие от Excel, где можно ссылаться и на другие книги).
Формулы всегда начинаются со знака «равно».
Итак, выделяем ячейку В2 и начинаем вводить формулу. Уже после нескольких символов =СУ появляются варианты формул с этим слогом в названии, выбираем мышкой СУММЕСЛИ и кликаем на нее:
Видим вот такое окно (формулу можно писать как в самой ячейке, так и в строке формул — это не принципиально):
Под формулой видим окно справки. В нем цветом подсвечивается тот элемент, который нужно ввести сейчас. У нас это диапазон условия (если справка не открылась, нажмите на? под формулой).
Выбираем лист «Диапазоны» и выделяем диапазон условия — для этого кликаем на его первой ячейке и «протягиваем» до последней (в данном примере это С1:C7). Выделять ячейки можно и в обратном порядке: начать с С7 и протянуть до С1; или можно кликнуть на названии столбца С, и он выберется целиком.
Если вам мешает справка формулы, то закройте ее, нажав на крестик. Если все равно что-то мешает и никак не получается выбрать нужный диапазон, как B1:B7 на скриншоте ниже, — его можно ввести с помощью клавиатуры, прямо в строке формул (не забывайте, что буквы в ссылках Таблиц латинские).
(Если нажать F4 еще раз, то зафиксируются только строки, при повторном нажатии — только столбцы.)
Мы выбрали диапазон условия. Вводим точку с запятой (;), этот символ отделяет аргументы формулы друг от друга. Теперь нужно выбрать ячейку с условием.
Кликнем на вкладку Сводный отчет и на ячейку А2. Не будем делать условие абсолютным, так как планируем скопировать формулу на ячейку ниже и нам нужно, чтобы условие с А2 поменялось на А3.
Вводим точку с запятой, возвращаемся на вкладку Диапазоны и выбираем последний диапазон суммирования. Его тоже сделаем абсолютным.
Важно: переходите на другую вкладку только после точки с запятой, иначе при переходе у вас собьется предыдущий аргумент.
Формула готова, теперь скопируем ее из ячейки B2 в ячейку В3 (можно через пункты меню Копировать и Вставить, можно кликнуть на В2 и протянуть на ячейку ниже, можно использовать сочетания клавиш Ctrl + C, Ctrl + V):
Ссылки в Таблицах, как и в Excel, могут быть абсолютными и относительными.
Ровно так же они изменяются с помощью клавиши F4 или путем ввода знака доллара перед номером строки и/или перед номером столбца.
Читать этот подраздел дальше имеет смысл, если вы не работали с абсолютными и относительными ссылками в Excel или хотите повторить этот материал.
Относительные ссылки — это обычные ссылки вида A1, D10, AX127, которые по умолчанию появляются при щелчке на ячейку во время ввода формул. Такие ссылки смещаются вместе с формулой. Они являются относительными, так как ссылаются не на конкретную ячейку, а на ячейку, отстоящую на N строк и M столбцов от той, в которую введены. Так, если вы ввели в ячейку B1 формулу
то вы ссылаетесь не на A1, а на ячейку слева от ячейки с формулой, или отстоящую от нее на — 1 (минус один) столбец и 0 (ноль) строк.
Поэтому при копировании этой формулы в ячейку C4 она будет выглядеть следующим образом:
то есть по-прежнему ссылаться на ячейку слева от себя.
Это не всегда подходит для ваших задач.
Так, если вам нужно перемножить числа из многих строк на одну ячейку со ставкой налога, то сразу протянуть формулу не получится:
Уже во второй строке формула ссылается не на нужную ячейку, а на пустую ячейку под ней.
Ссылку можно сделать абсолютной, нажав F4 при вводе формулы (или щелкнув курсором на ссылку в строке формул). Последовательно нажимая F4, вы будете перебирать все 4 возможные комбинации:
На скриншоте ниже видно, как меняются адреса разных типов, если мы вводим их в столбец А и копируем в столбцы С, D и Е:
В большинстве случаев такая ситуация приемлема.
Если вы хотите создать по-настоящему абсолютную ссылку, всегда указывающую на A1 даже при изменении строк, воспользуйтесь функцией INDIRECT (ДВССЫЛ). Ее единственный аргумент — адрес ячейки:
Теперь при вставке строки результат вычисления обнулился, так как формула не стала ссылаться на E2, а осталась на E1:
Стиль ссылок А1. Является классической системой адресации в электронных таблицах; как в MS Excel, так и в Google Таблицах сначала идет имя столбца, потом — номер ячейки.
Стиль ссылок R1C1. В этой системе строки (ROW) и столбцы (COLUMN) обозначаются цифрами. Например, R3C2: 3-я строка и 2-й столбец — ячейка B3.
В Google Таблицах этот стиль используется редко, например в формуле ДВССЫЛ (INDIRECT). С помощью этой формулы мы приводим текстовое наименование адреса ячейки, записанное в стиле A1 или R1C1, в вид настоящей ссылки на ячейку, и формула возвращает содержимое этой ячейки.
Синтаксис ДВССЫЛ (INDIRECT) состоит из двух аргументов: адреса ячейки и стиля адреса ячейки. Если вы хотите использовать в этой формуле стиль R1C1, то поставьте 0 во втором аргументе.
На скриншоте ниже подробнее:
Скорее всего, со временем ваши таблицы будут пополняться и обновляться путем добавления новых строк.
Если при этом на листе с таблицей нет никаких других данных, кроме заголовков полей (столбцов) и данных под ними (то есть нет нескольких таблиц, расположенных одна под другой), есть смысл указывать в аргументах открытые диапазоны вида A2:A, а не A2:A100. Тогда вам не придется каждый раз менять формулы.
Можно указывать столбцы/строки целиком, если для вас приемлемо включить заголовки в расчет:
A: A — весь столбец A
Итак, вы указали обычный закрытый диапазон — до 12-й строки:
И после добавления данных в 13-ю результат расчета формулы не изменится:
С открытым диапазоном таких проблем не будет:
Почему в примере (да и вообще) лучше использовать C2:C, чем весь столбец C: C? Чтобы не учитывать заголовок.
Он может означать год, и тогда (типичная ошибка) к общей сумме добавится, например, 2016:
Как и в Excel, диапазонам в Таблицах можно присваивать имена. Именованные диапазоны делают формулы наглядными, ведь вместо
Чтобы задать диапазону имя, выделите его и нажмите на кнопку Именованные диапазоны в разделе меню Данные (или сначала вызовите окно Именованные диапазоны, а потом выделите то, чему будете присваивать имя):
После чего справа в появившемся окне введите название диапазона и нажмите Готово:
После присвоения диапазонам имен вы можете использовать их в формулах:
Небольшое пояснение, как писать условия в формулах, чтобы все работало.
• Числовые и текстовые условия, знаки >, обозначает неравенство):
— например, так нашим условием будет Вася
— а так все, кроме Васи
— Если вам нужно объединить несколько элементов в условии формулы, скажем, адрес ячейки и знак >= или формулу и текст, используйте амперсанд (&)