Блог

Динамические диапазоны в Excel: когда использовать OFFSET или таблицу

Обновляйте суммы и раскрывающиеся списки при добавлении строк. Разберитесь в OFFSET и COUNTA, узнайте, что меняет пустой ID и как использовать таблицу Excel.

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

В список Excel добавили товар, но сумма и варианты в раскрывающемся списке остались прежними. Если ссылка по-прежнему указывает на A2:A10, одиннадцатая строка вне диапазона. Динамическая ссылка позволяет Excel определить границу списка без ручного обновления формул.

Вычислить высоту и ширину с OFFSET

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

Ячейка Столбец A Столбец B Столбец C
Строка 1 商品ID (ID товара) 商品名 (название товара) 数量 (количество)
Строка 2 A01 Ручка 4
Строка 3 A02 Тетрадь 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Начните с A1 без смещения по строкам и столбцам. Высота равна числу заполненных ячеек в A, ширина — числу заголовков в первой строке. Получаем A1:C3 из трёх строк и трёх столбцов с суммой 10. Добавьте A03, линейку и 2 в строку 4: диапазон станет A1:C4, сумма — 12. ID хранятся как текст, а области подсчёта не содержат посторонних данных. Справка Microsoft по OFFSET

Теперь сотрите только ID из A3. В столбце A будут учтены три ячейки: заголовок A1 и ID в A2 и A4. Поэтому OFFSET вернёт первые три строки A1:C3. Количество 6 из строки 3 останется в сумме, а 2 из строки 4 выпадет. Сумма снова равна 10.

Ячейка A3 пуста. Диапазон OFFSET — A1:C3, сумма — 10. В неё входит 6 из строки 3, но не 2 из строки 4.
Зелёная рамка показывает диапазон A1:C3, который возвращает OFFSET в Excel для Windows.

Из-за пустого заголовка внутри списка последние столбцы тоже могут выпасть из диапазона. COUNTA учитывает формулы, возвращающие пустую строку (""). В имеющейся книге заполняйте заголовки и ID без пропусков и проверяйте пределы подсчёта и пустые ячейки. Отсутствие количества не меняет высоту, если ID есть. Справка Microsoft по COUNTA

Если заголовки начинаются во второй строке

Если в первой строке название отчёта, начните список с A2 и считайте высоту и ширину со второй строки, чтобы исключить название. Если сдвинуть пример вниз на строку, заголовки и два товара займут A2:C4. Сумма останется 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Использовать таблицу, которая расширяется при добавлении строк

Для нового списка выделите диапазон с заголовками и выберите «Вставка» → «Таблица». Назовите таблицу Items. Следующая формула суммирует количества из ячейки за пределами таблицы. При добавлении строк диапазон расширяется. Руководство Microsoft по структурированным ссылкам

=SUM(Items[数量])

Суммировать только строки с ID товара

После преобразования A1:C4 в таблицу Items формула выше вернёт 12 (4 + 6 + 2), даже если A3 пуста. Для суммирования только строк с ID используйте SUMIF с условием непустого ID. В примере сумма строк 2 и 4 равна 6 (4 + 2). Справка Microsoft по SUMIF

=SUMIF(Items[商品ID],"<>",Items[数量])

Связать варианты раскрывающегося списка с таблицей

Чтобы выбирать названия товаров на другом листе, откройте «Формулы» → «Диспетчер имён» в настольном Excel. Создайте имя уровня книги ItemChoices со ссылкой =Items[商品名]. В проверке данных входной ячейки выберите «Список» и введите =ItemChoices в поле «Источник». Заголовок исключается, а новые названия из добавленных в конце строк появляются в списке. Чтобы не было пустых вариантов, заполняйте название при добавлении строки. Руководство Microsoft по раскрывающимся спискам