Изменяемые диапазоны и функция промежуточные итоги в Excel

Динамически изменяемы диапазоны очень удобно использовать вместе с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). Рассмотрим конкретный пример:

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

Данная таблица изменяема и у нее неопределенно количество строк. Одни строки могут добавляться другие удаляться в процессе работы.

В нашей базе можно отфильтровать клиентов, которые живут в городе заданном критерием фильтра. В ячейке F1 мы будем отображать, какой процент наших клиентов живет в том или ином городе.



Так как размер нашей клиентской базы неопределенный мы будем использовать динамические имена с изменяемыми диапазонами:

1. Выберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя».
2. В диалоговом окне «Создание имени» в первое поле вводим слово «клиенты», а для поля «Диапазон:» вводим функцию со следующими параметрами: =СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)

3. В ячейку F1 введите формулу: =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; клиенты)/СЧЁТЗ(клиенты)
4. Перейдите на любую ячейку нашей базы в диапазоне A1:C11 и выберите инструмент: «Данные»-«Сортировка и фильтр»-«Фильтр».
5. Отфильтруйте данные по городу «Москва» (столбец C), а в ячейке F1 сразу же отобразиться процент наших клиентов приживающих в данном городе.

При создании динамического имени, мы использовали функцию СЧЁТЗ($A:$A)-1. Данная функция считает количество заполненных ячеек в столбце A. Но нам нужно было исключить заголовок столбца «Имя» поэтому сразу после функции стоит -1.

В ячейке F1 мы использовали функцию =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3; клиенты). Первым параметром мы указываем, какую функцию использовать для нашего динамического диапазона «клиенты». Код параметр 3 (или 103) – это использование той же функции СЧЁТЗ() только уже для отфильтрованного результата. То есть считаются только те непустые ячейки, которые остались после использования фильтра.

Формула в ячейке F1 показывает точные данные, если база клиентов заполонена правильно. Например, нельзя, чтобы клиент был внесен в базу без имени, а только фамилия и город. Иначе значение в ячейке F1 не соответствует действительности. Также нельзя указывать только имя и фамилию без города.