Функция ADDRESS (АДРЕС) в Google Таблицы

Функция ADDRESS (АДРЕС) в Google Таблицы используется для создания текстового представления адреса ячейки в виде строки. Применение функции ADDRESS (АДРЕС) упрощает работу при создании динамических формул, когда адрес ячейки может изменяться в зависимости от условий или изменения структуры таблицы.

Синтаксис

=ADDRESS(строка; столбец; [тип_адреса]; [стиль_a1]; [лист])

=ADDRESS(row; column; [absolute_relative_mode]; [use_a1_notation]; [sheet])

=АДРЕС(строка; столбец; [тип_адреса]; [стиль_a1]; [лист])

Список аргументов

Строка (row) - номер строки ячейки.

Столбец (column) - номер столбца ячейки.

[Тип_адреса] ([absolute_relative_mode]) - необязательный аргумент, определяет тип ссылки (относительная или абсолютная):

1 - Абсолютная ссылка на строку и столбец (используется по умолчанию).
2 - Абсолютная ссылка только на строку.
3 - Абсолютная ссылка только на столбец.
4 - Относительная ссылка (без символов "$").

[Стиль_a1] ([use_a1_notation]) - необязательный аргумент, логическое значение, которое определяет стиль ссылки:

1/ИСТИНА/TRUE - стиль A1 (по умолчанию).
0/ЛОЖЬ/FALSE - стиль R1C1.

[Лист] ([sheet]) - необязательный аргумент, текст представляющий имя листа, на котором находится ячейка. Если этот аргумент не указан, то просто вернет адрес ячейки без указания листа.

Аргументы функцию можно перефразировать следующим образом:

=ADDRESS(номер строки; номер столбца; [в каком виде получить адрес ячейки]; [в каком стиле отобразить]; [на каком листе находится ячейка])

Возвращаемое значение

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

Пример

Функции ADDRESS (АДРЕС) упрощает работу при создании динамических формул, когда адрес ячейки может изменяться в зависимости от условий. Например, при создании диапазонов для функций SUM (СУММ), адрес может быть создан динамически, что делает формулы более гибкими.

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

Первый вариант получения информации по ассортименту в Google Таблицы

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

Форма для контроля тотальной суммы товаров и остатков в Google Таблицы

Обратите внимание, что мы подготовили форму с двумя способами решения данной задачи. Первый способ с использованием функции SUM (СУММ), где нам постоянно надо будет выделять диапазоны суммирования.

Пример использование функции SUM (СУММ) в Google Таблицы

И второй вариант у нас будет динамический реализованный с использование функции ADDRESS (АДРЕС), мы один раз пропишем формулу и данные будут считаться автоматически, не зависимо какой вариант нам прислали.

Пример использование функции ADDRESS (АДРЕС) в Google Таблицы

У нас получилась вот такая, на первый взгляд, страшная формула:

=SUM(INDIRECT(CONCATENATE(ADDRESS(1;MATCH($H2;$1:$1;0);4;1);":";ADDRESS(ArrayFormula(MATCH(TRUE();($A:$A="");FALSE()))-1;MATCH($H2;$1:$1;0);4;1))))

Подробно описывать все используемые в функции мы не будем, акцент сделаем на функции ADDRESS (АДРЕС).

Чтобы проще было всё вопринимать, давайте всё разобьём на шаги:

Получить адрес ячейки начала диапазона, который мы будем суммировать. Для этого вставляем в нашу функцию следующие аргементы: =ADDRESS(1;3;4;1) - Результат: C1.
Получить адрес ячейки конца диапазона, который мы будем суммировать. Для этого вставляем в нашу функцию следующие аргементы: =ADDRESS(15;3;4;1) - Результат: C15.
Сцепляем наши полученные результаты с помощью функции сцепления строк CONCATENATE (СЦЕПИТЬ), чтобы у нас получился диапозон: =CONCATENATE(ADDRESS(1;3;4;1);":";ADDRESS(15;3;4;1)) - Результат: C1:C15.
С помощью функции INDIRECT (ДВССЫЛ) в Google Таблицы, превращаем полученный текст похожий на диапозон в полноценный диапозон: =INDIRECT(CONCATENATE(ADDRESS(1;3;4;1);":";ADDRESS(15;3;4;1))) - Результат: диапозон значений, которые необходимо суммировать.
Суммируем значения используя функции SUM (СУММ): =SUM(INDIRECT(CONCATENATE(ADDRESS(1;3;4;1);":";ADDRESS(15;3;4;1)))) - Результат: 118154

Проверяем сходятся ли данные с данными в соседней ячейки, которые мы получили использую обычную функцию SUM (СУММ) - всё сходится. Но если мы изменим шаблон предоставления данных, то у нас выскочит ошибка: #N/A. Это происходит из-за того что в функциях ADDRESS (АДРЕС) мы указали абсолютные значения. Давайте скорректируем нашу формулу, чтобы она была динамическая, для этого нам понадобятся ещё две дополнительные функции:

ArrayFormula - функция для работы с массивами
MATCH (ПОИСКПОЗ) - функция поиска значений по заданным аргументам

Изменяем аргументы в функцию ADDRESS (АДРЕС), которую мы написали на первом шаге:

было: =ADDRESS(1;3;4;1)
стало: =ADDRESS(1;MATCH($H2;$1:$1;0);4;1)

Изменяем аргументы в функцию ADDRESS (АДРЕС), которую мы написали на втором шаге:

было: =ADDRESS(15;3;4;1)
стало: =ADDRESS(ArrayFormula(MATCH(TRUE();($A:$A="");FALSE()))-1;MATCH($H2;$1:$1;0);4;1)

Итоговая формула: =SUM(INDIRECT(CONCATENATE(ADDRESS(1;MATCH($H2;$1:$1;0);4;1);":";ADDRESS(ArrayFormula(MATCH(TRUE();($A:$A="");FALSE()))-1;MATCH($H2;$1:$1;0);4;1))))

Меняем шаблоны, протягиваем формулу вниз - все работает всё считается!

Многие могут сказать, зачем так всё усложнять, если есть функция SUM (СУММ) в которой им не сложно каждый день менять диапозон суммирования, если это необходимо - написанием данной формулы мы избавили себя от ежедневного изменения диапозона суммирования функции SUM (СУММ) и тем самым оптимизировали свой рабочий процесс.

Функция ADDRESS (АДРЕС) часто используется в сочетании с другими функциями для создания динамических формул или в случаях, когда необходимо динамически указывать ячейки в формулах на основе условий или внешних параметров. В большинстве случаях использование адресов в формулах делает их сложными для понимания и поддержки.