SMALL() и LARGE() в условном форматировании. Обход колонками

Статья рассказывает о том, как организовать данные по ПРФ и упростить поиск максимумов в массиве данных по колонкам.

Пример Таблицы SMALL() и LARGE() в условном форматировании. Обход колонками

Дана выгрузка из базы данных, получаемая на лист "Данные". Требуется составить отчет, в котором периоды, доходы, количество заявок и CV отображается в колонках по отделам. Такая форма представления часто используется как область данных. Это очень неудобно. Действительно, такое представление удобно просматривать, ни никак не использовать для заполнения. Поэтому, разделив данные и представление по ПРФ, получаем удобный файл для добавления данных и для их обзора. Для представления используется лист "Сводная" с функционалом сводной таблицы.

Далее требуется выделить максимумы и минимумы данных в каждой колонке листа "Сводная". Используем функционал формул SMALL (НАИМЕНЬШИЙ) и LARGE (НАИБОЛЬШИЙ)

Поиск второго по счету наибольшего из набора данных (поиск наименьших происходит точно также):

=LARGE(B4:B15;2)

Используя свойство условного форматирования и обхода по массивам, зададим фиксированную высоту колонок (поиск наименьших происходит точно также):

=B$4:B$15>=LARGE(B$4:B$15;2)

Смысл формулы выше: условие истинно, если значение из текущей колонки совпадает или больше вычисления LARGE в этой колонке.

Следующую формулу условного форматирования необходимо поднять выше остальных, чтобы условие успело исполниться. Оно заключается в том, что если имя колонки равно "Эффективность", то применить туже самую формулу, но использовать другой формат. Обратите внимание, что обход массива будет произведен только по колонкам заданного форматированием диапазона.

=AND(B$3="Эффективность";B$4:B$15>=LARGE(B$4:B$15;2))