Среди прочих компаний и организаций моими клиентами является наш калининградский Фонд Капитального Ремонта. Сотрудники этого предприятия хорошо учатся, а, главное, заинтересованы в хорошем качестве своей работы и поэтому постоянно задают мне вопросы о том, как сделать свои таблички ещё удобнее и информативнее.
Вот один из таких вопросов.
Перед нами список объектов, который находится на их попечении.
(Всё просто, если не надо скрывать строки)
Скачайте файлик, если хотите поупражняться.
В этом файле есть столбец «№ п/п», который нумерует строки. Причём, если объект повторяется, т.е., на одном доме выполняется сразу несколько работ, то номер должен сохраняться и переключаться при переходе к следующему объекту.
Эту задачку они решили сами. Пишем формулу:
=ЕСЛИ(C2=C1;A1;Ч(A1)+1)
Но как быть, если хочется фильтровать этот список, сохраняя при этом тот же порядок. Например, выбрать объекты, ремонтируемые одним исполнителем и снова видеть нумерацию вида 1, 2, 2, 2, 3, 3, 4.
Если применяешь фильтрацию, строчки скрываются, а их номера не пересчитываются, и тогда в подобной нумерации никакого смысла нет.
(Бессмысленная нумерация — это грустно)
Я с готовностью вызвался помочь и, зная, что если хочется не использовать значения скрытых или отфильтрованных строк, то надо использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. В ней реализованы все знакомые функции с простыми итогами — СРЗНАЧ, СУММ, СЧЁТ, МАКС, МИН и т.д.
При этом есть возможность использовать в расчётах только видимые ячейки. Для этого надо указывать в качестве первого параметра этой функции переключатель, больший 100. 101 — для СРЗНАЧ, 109 — для СУММ, 104 —для МАКС.
(Уместное применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ)
В «умных» таблицах именно через эту функцию реализована итоговая строка.
Я смело ринулся в бой и набросал функцию
=ЕСЛИ(B2=B1;A1;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(104;A$1:A1)+1)
Ну типа если повторяется объект, то повторяй и его номер, а если объект сменился, то бери максимальное из верхних ячеек, прибавляй к нему 1 и радуйся. Смысл формулы такой же, как и в начале, только скрытые отфильтрованные строки не участвуют.
Так мне казалось. Не тут-то было.
Чтобы я ни делал, результат у меня получался в виде повторяющихся единиц.
(Ошибочное применение функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ)
Я уже было подумал, что у функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ глюк с вычислением максимумов. Трудно себе представить такое в программе с 35-летним стажем, но и на старуху бывает проруха. Мы и не такое видели. Так я рассуждал, сочиняя ответ-оправдание ученице.
Но меня взяла злость на себя, и я вспомнил собственный принцип, что если тебе кажется, что Excel что-то не умеет, то внимательно почитай справку и поищи в интернете. :-)
Так оно и оказалось. Оказалось, что ПРОМЕЖУТОЧНЫЕ.ИТОГИ ни в чём не виноваты. Они просто не приспособлены для такого применения, которое я им придумал в этой задаче.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать только качестве итогов, расположенных под числами. А вот функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ от результатов, полученных при помощи функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ не работает. Значения, полученные сестричками, она просто игнорирует. Вот и получается, что максимум у меня везде стоит 0, а прибавив к нему 1, я получаю —, правильно, 1.
В результате поисков в гугло-яндексах я натолкнулся на другую функцию — АГРЕГАТ, использование которой и помогло решить эту задачу.
Дело в том, что в этой функции есть ещё один параметр, который позволяет указать, что можно использовать для подсчётов. Мне подошёл переключатель 5.
Ну и вот окончательное решение.
(Применение функции АГРЕГАТ)
Скачайте файл с результатом
Формула у меня получилась такая:
=ЕСЛИ(B2=B1;A1;АГРЕГАТ(4;5;A$1:A1)+1)
А вот ссылка на статью в справочной системе про функцию АГРЕГАТ.
В заключение.
Отвечаю на возможные вопросы типа «27 лет с Excel, а до сих пор всех функций не знаешь».
Да, и не собираюсь учить все 400 с хвостиком функций. Мне достаточно своих пары сотен для того, чтобы решать свои задачи. А как только вдруг мне или моим клиентам не хватает этого набора, вот тут я и занимаюсь поисками. Не знать не стыдно. Стыдно не учиться.
Засим прощаюсь, ждите продолжения моих изысканий.
Возврат к списку
