Как создавать именованные диапазоны в Google Таблицах (статические и динамические)

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

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

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

Использование именованных диапазонов дает следующие преимущества:

• Именованные диапазоны проще использовать в формулах. Вы можете дать описательное имя именованным диапазонам, и вам не нужно беспокоиться о знании ссылки на диапазон. Например, = AVERAGE (SalesData) проще, чем = AVERAGE (B2: B21).
• Вы можете обновить именованный диапазон, и все формулы, в которых он используется, теперь будут относиться к новому диапазону. Это большая экономия времени.

Создание именованного диапазона в Google Таблицах

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

• Выберите данные, для которых вы хотите создать именованный диапазон.
• Перейдите на вкладку «Данные».
• В раскрывающемся списке выберите Именованные диапазоны.
• На панели Именованные диапазоны введите имя в первое поле. В этом примере мы используем имя SalesData .
• Убедитесь, что во втором поле указан правильный диапазон данных. Если неверно, измените его.
• Щелкните Готово.

Это создаст именованный диапазон — SalesData.

После создания вы можете использовать именованный диапазон в формулах. Вот несколько примеров использования именованного диапазона SalesData:

• Чтобы получить сумму всех продаж: = SUM (SalesData)
• Чтобы найти среднюю стоимость продаж: = СРЕДНЕЕ (SalesData)
• Чтобы найти максимальное значение продаж: = MAX (SalesData)

Правила именования при создании именованных диапазонов в Google Таблицах

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

Именованный диапазон:

• Не может содержать пробелов и знаков препинания. Например, у вас не может быть «Данные о продажах». Однако вы можете использовать подчеркивание между словами. Следовательно, «Sales_Data» в порядке.
• Может содержать только буквы, цифры и символы подчеркивания.
• Нельзя начинать с числа или слов «правда» или «ложь».
• Длина не должна превышать 250 символов.
• Не может быть чем-то, что относится к диапазону. Например, вы не можете назвать его A1, так как он уже относится к ячейке. Точно нельзя назвать его A1: A100.

Редактирование именованного диапазона

После создания именованного диапазона вы можете изменить или обновить его.

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

Вот шаги, чтобы обновить / отредактировать именованный диапазон в Google Таблицах:

• Перейдите на вкладку «Данные».
• Щелкните Именованные диапазоны.
• Наведите указатель мыши на именованный диапазон, который вы хотите отредактировать, и щелкните значок «Изменить».
• Обновите имя или диапазон.
• Щелкните Готово.

Создание динамического именованного диапазона в Google Таблицах

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

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

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

Однако вы можете сделать это, используя изящный трюк с функцией INDIRECT.

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

Вот шаги для создания динамического именованного диапазона в Google Таблицах :

• В ячейке (E2 в этом примере) введите формулу = COUNT (C2: C100) +1. Это даст нам количество ячеек, в которых есть число. 1 добавляется в формулу, поскольку наши данные о продажах начинаются с строки № 2. Также обратите внимание, что мы использовали C2: C100, так что, если данные будут добавлены в будущем, они будут автоматически подсчитаны. Кроме того, мы использовали функцию COUNT, поскольку все данные числовые. Исходя из ваших данных, вы также можете использовать COUNTIF (СЧЁТЕСЛИ).
• В другой ячейке (F2 в этом примере) введите формулу = «Sheet1! C2: C» & E2. Это дало бы нам ссылку, которая охватила бы весь столбец данных о продажах. Например, если имеется 10 транзакций продажи, это даст Sheet1! C2: C11. Если есть 15 транзакций, он выдаст Sheet1! C2: C16
• Перейдите на вкладку «Данные» и выберите «Именованные диапазоны».
• Создайте именованный диапазон с именем SalesData и используйте Sheet1! F2 в качестве диапазона.

Теперь вы можете использовать следующую формулу для ссылки на динамический именованный диапазон: = INDIRECT (SalesData).

Функция INDIRECT будет использовать именованный диапазон и ссылаться на ячейку F2, которая, в свою очередь, содержит ссылку на данные о продажах. Поскольку мы сделали диапазон в F2 динамическим (используя = «Sheet1! C2: C» & E2), именованный диапазон также становится динамическим.

Например, если теперь вы хотите рассчитать сумму продаж, вы можете использовать формулу =SUM(INDIRECT(SalesData)). Если добавлены другие записи транзакций, формула автоматически обновится и выдаст новую сумму продаж.