Личный кабинет Версия для слабовидящих

Нумерация после фильтрации или АГРЕГАТ vs ПРОМЕЖУТОЧНЫЕ.ИТОГИ



Агрегат.jpg
(Первая картинка, найденная Яндекс по запросу "Агрегат")

Среди прочих компаний и организаций моими клиентами является наш калининградский Фонд Капитального Ремонта. Сотрудники этого предприятия хорошо учатся, а, главное, заинтересованы в хорошем качестве своей работы и поэтому постоянно задают мне вопросы о том, как сделать свои таблички ещё удобнее и информативнее.

Вот один из таких вопросов.

Перед нами список объектов, который находится на их попечении.

Рисунок1.png

(Всё просто, если не надо скрывать строки)

Скачайте файлик, если хотите поупражняться.

В этом файле есть столбец «№ п/п», который нумерует строки. Причём, если объект повторяется, т.е., на одном доме выполняется сразу несколько работ, то номер должен сохраняться и переключаться при переходе к следующему объекту.

Эту задачку они решили сами. Пишем формулу:

=ЕСЛИ(C2=C1;A1;Ч(A1)+1)

Но как быть, если хочется фильтровать этот список, сохраняя при этом тот же порядок. Например, выбрать объекты, ремонтируемые одним исполнителем и снова видеть нумерацию вида 1, 2, 2, 2, 3, 3, 4.

Если применяешь фильтрацию, строчки скрываются, а их номера не пересчитываются, и тогда в подобной нумерации никакого смысла нет.

Рисунок2.png

(Бессмысленная нумерация — это грустно)

Я с готовностью вызвался помочь и, зная, что если хочется не использовать значения скрытых или отфильтрованных строк, то надо использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В ней реализованы все знакомые функции с простыми итогами — СРЗНАЧ, СУММ, СЧЁТ, МАКС, МИН и т.д.

При этом есть возможность использовать в расчётах только видимые ячейки. Для этого надо указывать в качестве первого параметра этой функции переключатель, больший 100. 101 — для СРЗНАЧ, 109 — для СУММ, 104 —для МАКС.

Рисунок3.png

(Уместное применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ)

В «умных» таблицах именно через эту функцию реализована итоговая строка.

Я смело ринулся в бой и набросал функцию

=ЕСЛИ(B2=B1;A1;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(104;A$1:A1)+1)

Ну типа если повторяется объект, то повторяй и его номер, а если объект сменился, то бери максимальное из верхних ячеек, прибавляй к нему 1 и радуйся. Смысл формулы такой же, как и в начале, только скрытые отфильтрованные строки не участвуют.

Так мне казалось. Не тут-то было.

Чтобы я ни делал, результат у меня получался в виде повторяющихся единиц.

Рисунок4.png

(Ошибочное применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ)

Я уже было подумал, что у функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ глюк с вычислением максимумов. Трудно себе представить такое в программе с 35-летним стажем, но и на старуху бывает проруха. Мы и не такое видели. Так я рассуждал, сочиняя ответ-оправдание ученице.

Но меня взяла злость на себя, и я вспомнил собственный принцип, что если тебе кажется, что Excel что-то не умеет, то внимательно почитай справку и поищи в интернете. :-)

Так оно и оказалось. Оказалось, что ПРОМЕЖУТОЧНЫЕ.ИТОГИ ни в чём не виноваты. Они просто не приспособлены для такого применения, которое я им придумал в этой задаче.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать только качестве итогов, расположенных под числами. А вот функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ от результатов, полученных при помощи функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ не работает. Значения, полученные сестричками, она просто игнорирует. Вот и получается, что максимум у меня везде стоит 0, а прибавив к нему 1, я получаю —, правильно, 1.

В результате поисков в гугло-яндексах я натолкнулся на другую функцию — АГРЕГАТ, использование которой и помогло решить эту задачу.

Дело в том, что в этой функции есть ещё один параметр, который позволяет указать, что можно использовать для подсчётов. Мне подошёл переключатель 5.

Ну и вот окончательное решение.

Рисунок5.png

(Применение функции АГРЕГАТ)

Скачайте файл с результатом

Формула у меня получилась такая:

=ЕСЛИ(B2=B1;A1;АГРЕГАТ(4;5;A$1:A1)+1)

А вот ссылка на статью в справочной системе про функцию АГРЕГАТ.

В заключение.

Отвечаю на возможные вопросы типа «27 лет с Excel, а до сих пор всех функций не знаешь».

Да, и не собираюсь учить все 400 с хвостиком функций. Мне достаточно своих пары сотен для того, чтобы решать свои задачи. А как только вдруг мне или моим клиентам не хватает этого набора, вот тут я и занимаюсь поисками. Не знать не стыдно. Стыдно не учиться.

Засим прощаюсь, ждите продолжения моих изысканий.

Возврат к списку