СМЕЩ + СЧЁТЗ — динамический диапазон

поиск

Что делает формула

СМЕЩ создаёт ссылку на диапазон с заданным смещением и размером. СЧЁТЗ считает количество непустых ячеек — это и будет высота диапазона. Вместе они создают диапазон, который растёт при добавлении строк. Используется в именованных диапазонах для выпадающих списков и диаграмм.

Формула

=СМЕЩ(A1;0;0;СЧЁТЗ(A:A);1)

Пример

Выпадающий список, который растёт автоматически:

Товары
Телефон
Ноутбук
Планшет
(новый товар добавится сюда)

Диапазон автоматически включит новый товар без изменения формулы

Частые ошибки

1СМЕЩ — летучая функция (volatile), она пересчитывается при каждом изменении — может замедлить большие файлы
2СЧЁТЗ считает ВСЕ непустые ячейки, включая заголовок — учитывайте это при расчёте размера
3В Excel 365 лучше использовать ФИЛЬТР или динамические массивы вместо СМЕЩ

Нужна другая формула?

AI сгенерирует за 3 секунды — опишите задачу на русском языке

Попробовать генератор