Динамические диапазоны в 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.
Из-за пустого заголовка внутри списка последние столбцы тоже могут выпасть из диапазона. 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 по раскрывающимся спискам