Выделить максимальное значение excel

Подcвечивать максимум (минимум) автоматически

В стандартных инструментах условного форматирования EXCEL отсутствует возможность АВТОМАТИЧЕСКИ форматировать только те ячейки, которые содержат максимальные или минимальные значения.

Для того, чтобы это сделать, необходимо создать определенное выражение (формулу), результатом которого будет являться либо пара ИСТИНА/ЛОЖЬ , либо значения 1/0 и поместить его в модуль настройки условного форматирования.

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

В результате нам необходимо «подсветить» ячейку с наибольшим среднедневным потреблением кофе в странах, измеренным в чашках на одного человека:

Для этого:

1. Будем использовать вариант с результатом ИСТИНА/ЛОЖЬ , и в ячейку B1 (может быть любая другая) внесем следующее выражение:

=B3=МАКС($B$3:$B$35)

B3 – первая проверяемая на максимум ячейка, она будет изменяться после прменения условного форматирования к диапазону.

=B3=МАКС($B$3:$B$35) – фукнция максимума, выдающая (или как говорят – «возвращающая») максимум из диапазон, содержащего все значения к-ва чашек.

Фиксация необходима для того, чтобы адреса диапазонов не «съехали» при применении форматирования на весь диапазон.

Скопируем полностью выражение из строки формул в буфер обмена.

2. Выделим весь диапазон со значениями (от B3 до B35), и на вкладке «Главная», нажмем кнопку «Условное форматирование»

3. В открывшемся меню выберем «Создать правило» .

4. В типе правила выберем «Использовать формулу для определения форматируемых ячеек»

5. В поле формул вставим скопированное выражение и, нажав кнопку «Формат» настроим желаемое форматирование максимального значения (в нашем случае – это будет заливка желтым).

6. Сохраним изменения в каждом окне, нажав на «ОК» .

7. В результате максимальное значение будет подсвечено желтым цветом.

Выражение в условном форматировании проверяет равенство выбранной ячейки максимуму из диапазона, и если вдруг она будет равна максимуму – выдаст результат «ИСТИНА», что в свою очередь даст сигнал включения условного форматирования, которое мы настроили.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее "перевести")

Максимальный и Минимальный по условию в MS EXCEL

​Смотрите также​ массива – Ctrl=Shift+Enter​ разные цвета, тогда​ заливки для ячейки.​ применяется автоматическое выделение​ МАКС, должна быть​ часто используемой, т.к.​ преобразовать все значения​ — ссылка на диапазон​ условиям. Как выборочно​

​ минимальными значениями.​ одна из функций​Внимание!​

​ функции МАКС и​СОВЕТ:​Решением является формула массива.​ клавишу ​​Нахождение максимального/ минимального значения​​ . ​​ необязательно создавать 3​​ ОК на всех​

​ цветом трех ячеек​

​ функция МИН. Данная​​ она позволяет упорядочивать​ в числовой формат.​ ячеек, содержащие данные,​ посчитать сумму по​В ячейках D6:E6 мы​​ работы с базой​​Это формула массива,​
​ МИН, которые выбирают​Все вышеуказанные задачи можно​Т.е. если в столбце​F9​ – простая задача,​Дмитрий Дорогобузов​ правила условного форматирования​ окнах.​​ с наименьшими значениями.​​ функция позволяет выбрать​ числовые массивы. Ее​ Это можно сделать​ для которых определяется​ нескольким условиям, смотрите​​ установили функцию ДМАКС,​​ данных в Excel.​
​ п. э. для​

​ из таблицы максимальное​

​ решить без использования формул​

​А​). ИСТИНА соответствует строкам,​ но она несколько​: Все получилось! Спасибо!​ для одного и​К одному и тому​ Условное форматирование со​​ наименьшее значение в​​ можно, например, использовать​ формулой массива =НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(E5:E9+0);E5:E9+0;"");1)​ k-ое наибольшее значение.​ в статье "Сумма​

​ чтобы найти максимальную​ В ячейке Е7​ ввода формулы нажимаем​ и минимальное значение,​ массива и функции​значение = Текст3,​ у которых в​ усложняется, если МАКС/​Igor67​ того же диапазона.​ же диапазону должно​ соответственными формулами прекрасно​ таблице Excel. А​ для сортировки списков​Необходимо помнить особенность функции​ Также возможен ввод​

​ по условию Excel".​ цену товара из​ пишем формулу.​ сочетание клавиш «Ctrl»​ но без условий.​ ДМИН(). Для этого​ то учитывается значение​ столбце Текстовые значения​ МИН нужно найти​: Упрощенный вариант, не​ Достаточно просто немного​ быть применено 3​ справляется с данной​ вместо зеленого цвета​

​ и таблиц.​​ НАИБОЛЬШИЙ() при работе​ массива констант, например,​Найдем максимальное/ минимальное только​ ячеек D3 и​Для написания формулы,​ + «Shift» +​ Здесь мы рассмотрим,​​ потребуется создать дополнительный​​ в столбце​ содержится значение Текст1.​
​ не среди всех​

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

​ среди повторяющихся значений.​​ E3.​​ можно вызвать окно​ «Enter».​ как выбрать max​​ столбец, в котором​​B​Часть формулы ЕСЛИ(Текст=E6;Числа;""), вернет​ значений диапазона, а​ данных, формула массива​ всего один символ​ Чтобы проверить выберите​ данными для примера:​ цвет.​ рода ранжирования или​ среди которых имеются​k​Пусть исходный список содержит​

​Третий вариант.​ функции. У строки​Мы имя столбца​ и min с​
​ будут выведены только​

​, если значение <>​​ массив <10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"":​​ только среди тех,​=НАИБОЛЬШИЙ(ЕСЛИ(($C2:$M2)="Да";$B2:$L2);СТОЛБЕЦ(A1))​ оператора: =НАИМЕНЬШИЙ($B$2:$B$9;3)>=B2. То​ инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление​Усложним немного задачу. Ячейка​К одному и тому​ анализа показателей успешности,​ повторы. Например, если​

​ — позиция (начиная с​​ повторяющиеся и неповторяющиеся​Сводная таблица в​ формул нажимаем на​
​ ввели в формулу​
​ конкретными условиями.​​ те значения, которые​​ Текст3, то учитывается​

​ "":"":4:"":"":"":8:"":"":"":"">, где вместо​​ которые удовлетворяют определенному​C+S+E это Ctrl+Shift+Ener​ есть – больше​ правилами».​ с наименьшим значением​ же диапазону должно​

​ очень удобно если​

​ имеется исходный массив​​ наибольшей) в массиве​ значения. На рисунке​

​ кнопку «Мастер функций»​

​ с помощью функции​

​У нас такая​ удовлетворяют критериям. Затем,​ максимальное значение+1, т.е.​ ИСТИНА подставлено значение​ условию. ​ так вводятся формулы​ или равно.​Три наименьшие значения автоматически​ должна автоматически получить​ быть применено 2​ выделить цветом наилучший​ <1;2;3;​ или диапазоне ячеек.​ снизу выделены повторяющиеся​Как сделать и​ (fx). В появившемся​ «Использовать в формуле».​

Множественные условия

​ таблица с перечнем​ среди отобранных значений​ заведомо НЕ минимальное.​ из числового столбца,​Пусть имеется таблица с​

​ массива.​Все описанные способы хороши​ выделились разными цветами.​

​ красный цвет заливки.​

​ правила условного форматирования.​ или наихудший результат.​6​ Если k ? 0 или​ значения (см. Файл​ работать со сводной​ окне в строке​ Подробнее об этом​

​ товаров.​ с помощью функций​ Далее функция МИН()​ а вместо ЛОЖЬ​ двумя столбцами: текстовым​Михаил С.​

Как найти максимальное число в Excel по условию.

Максимальное минимальное среди повторяющихся значений в MS EXCEL

​В поле ввода введите​ идентичны. Первая функция​

​ и наименьшие расходы​ у НАИБОЛЬШИЙ() нет​ эквивалентна =МАКС(массив), а​ENTER​ «Строки». А слово​ здесь указываем всю​

​ ячейке G2 пишем​ нажимаем на кнопку​=НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;"");1)​ содержится табличка с критерием​​ если критерию не​​ Текст1 (критерий введем​

​максимальное значение (формулой)?​
​ пометка (да или​

​ указана во втором​
​ формулу: =НАИМЕНЬШИЙ($B$2:$B$9;1)=B2 и​

​ =МАКС() ищет в​ делаем следующее:​ аналога СУММЕСЛИ() и​​ =НАИБОЛЬШИЙ(массив;n) эквивалентна =МИН(массив)​​необходимо нажать сочетание​ «Цена» – в​​ таблицу, вместе с​​ такую формулу массива.​

​ «​В файле примера для​ (см. файл примера).​ удовлетворяет ни одна​ в ячейку​ k=​

​ нет), по которой​ аргументе функции. То​ нажмите на кнопку​ несменяемом диапазоне с​Выделите диапазон с расходами​

Функция НАИБОЛЬШИЙ() в MS EXCEL

​ СЧЁТЕСЛИ(), позволяющих выполнять​Пустые ячейки, логические значения​ клавиш​ поле «Значения». Окно​ шапкой. Нажимаем клавишу​ <=МАКС(ЕСЛИ(Товар=F2;Цена))>​Форматировать как таблицу​ наглядности настроено Условное​​ Подробнее о функции​​ строка, то формула​

​E6​

​2​​ мы определяем учитывать​

​ есть если во​​ «Формат», чтобы задать​ абсолютными ссылками $B$2:$B$10​ B2:B10 и выберите​ вычисления с учетом​ (ЛОЖЬ и ИСТИНА)​CTRL+SHIFT+ENTER​ «Поля сводной таблицы»​

​ максимальное значение. После​ инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать​​ условия. Но, с​​ и текст функцией​.​ заполнили так.​ на таблицу были​Ниже, мы сделали таблицу​Или нажимаем сочетание​ строк, удовлетворяющим критериям.​

​ Функция ДМИН() -​ "":"":"":"":"":"":"">) вернет 0!,​Т.е. будем искать​ формуле, нужно чтобы​ нет.​ указали число 3,​

​ для ячейки. И​ функции стоит оператор​ правило».​ помощью формул массива​ игнорируются. Это видно​Поиск максимального и минимального​Получилась такая таблица.​

​ абсолютными.​ меньшего размера. В​ клавиш «Ctrl» +​ Кроме того, для​ нахождение минимального значения​ что может ввести​ максимальное значение не​ если значение по​А высчитываем мы​

​ значит функция возвращает​ нажмите ОК на​ сравнения с изменяемой​Выберите: «Использовать формулу для​ можно получить формулу​ из таблицы в​ значения среди неповторяющихся​Сводная таблица считает всё.​«Поле» – указываем​ формулах указывали одну​

​ «Т». Название столбцов,​ выбора критериев использован​ по условию в​ в заблуждение. Функция​ среди всех значений​ формуле получается ниже,​ вот что.​​ третье по очереди​​ всех открытых окнах.​ текущей ячейкой которая​ определения форматируемых ячеек».​ для нахождения наибольшего​ файле примера.​ значений приведен в​ А нам нужно​ абсолютный адрес ячейки​ ячейку D6 с​ затем, можно поменять​ Выпадающий список (см.​ MS EXCEL.​

Наибольший с учетом условия

​ НАИБОЛЬШИЙ() в этом​ столбца Числовые значения,​ чем определенное число-​1. Нужно найти​ наименьшее значение заданного​Не снимая выделения с​ имеет относительную ссылку​В поле ввода введите​ с учетом условия​Значение ошибки в ячейке​ одноименной статье Максимальное/​ найти не сумму,​

Сумма 3-х наибольших

​ названия столбца, из​ названием товара.​ по своему усмотрению.​ желтые ячейки).​Г.​ случае вернет ошибку​​ а только среди​​ получалось это определенное​

​ максимальное значение. Это​ диапазона $B$2:$B$9. Если​ диапазона B2:B9 сразу​ начиная от B2.​

​ формулу: =МАКС($B$2:$B$10)=B2​ (см. здесь).​ приводит к ошибке​

​ минимальное среди неповторяющихся​ а минимальное значение,​ которого будут браться​ <=МИН(ЕСЛИ(Товар=D6;Цена))>и <=МАКС(ЕСЛИ(Товар=D6;Цена))>​

Другие применения функции

​Или, присваиваем имя​Аналогичным образом можно настроить​Найдем минимальное значение,​ #ЧИСЛО!​ тех, у которых​ число, и так​ я нашел, пусть​

Как выделить наименьшее и наибольшее значение в Excel цветом

​ результат вычисления функции​ же создаем второе​ Правило условного форматирования​Нажмите на кнопку «Формат»,​С помощью нестандартной записи​ в формуле. Прежде​ значений.​ самую маленькую цену​ данные.​Если в таблицу​ каждому столбцу. Читайте​ формулы для нахождения​ среди тех, которые​Б.​ в той же​ же – если​ и криво​

Как выделить цветом максимальное значение в Excel

​ равен текущей ячейке​ правило условного форматирования.​

​ читает данную формулу​ чтобы выделить максимальное​ второго аргумента можно​

1. ​ чем применять функцию​Для вычисления максимального/ минимального​ конкретного товара.​«Критерий» – здесь​
2. ​ будут добавляться строки,​ об этом статью​
3. ​ минимального значения для​ больше среднего:​
4. ​Найдем максимальное значение​ строке в столбце​ выше, чем определенное​2. Нужно найти​ значит к ней​ Снова выберите инструмент:​ так: если число​ значение в столбце​ расширить возможности функции​ НАИБОЛЬШИЙ() – обработайте​ значения среди только​Для этого, нажимаем​

​ нужно указать диапазон​ то, лучше, сделать​ «Диапазон в Excel»​

Как выбрать минимальное значение в Excel

​ строк, значения которых​=ДМИН(A5:B30;B5;I10:I11)​ только среди чисел​А​ число- именно это​ второе максимальное значение​ присваивается соответственный формат​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать​ больше всех, то​ Excel. Для этого​ НАИБОЛЬШИЙ(). Например, найдем​ ошибку, например с​ уникальных значений списка​

​ правой мышкой на​ ячеек (не одну​ «умную таблицу», о​ здесь.​ попадают в определенный​где в диапазоне​ принадлежащих определенному интервалу​

​текстовое значение равно​ число.​ (формулой)​ (цвет заливки). Важно​ формулу для определения​ выделить цветом. Как​

​ в появившемся окне​ сумму 3-х наибольших​ помощью функции ЕСЛИОШИБКА().​ используйте стандартную функцию​ любую цифру в​ ячейку, а таблицу,​ чем мы говорили​Мы присвоили столбцам​ диапазон.​I10:I11​ значений, например от​ Текст1. Напишем формулу​Полосатый жираф алик​Заранее спасибо​ отметить что в​ форматируемых ячеек».​ только совпадает максимальное​ «Формат ячеек» на​ значений из диапазона ​Если в массиве нет​ МАКС() или МИН().​ столбце «Цена». В​ так работает эта​ выше.​ имена названий столбцов​Рассмотрим,​ содержится критерий =B6>СРЗНАЧ(Числа)​ 5 до 50.​ массива (не забудьте​: Например, так:​dude​ формуле для просмотра​В поле ввода введите​

​ число (которое возвращает​

Выделение цветом трех наименьших значений подряд

​ вкладке «Заливка» выберите​A5:A9​ ни одного числового​Функция НАИБОЛЬШИЙ(), английский вариант​ появившемся диалоговом окне​ функция). Для этого​Или сделать динамические​ – «Товар» и​как найти максимальное или​

​Д.​ Границы можно ввести​ при вводе формулы​=МАКС (100;МИН (300;A1))​: =МАКС(ЕСЛИ(C2:M2="Да";B2:L2)) в N2​ диапазона мы используем​ формулу: =НАИМЕНЬШИЙ($B$2:$B$9;2)=B2 и​ функция МАКС) со​ желаемый цвет заливки​=СУММ(НАИБОЛЬШИЙ(A5:A9;<1;2;3>))​

​ значения, то функция​ LARGE(), возвращает k-ое​ выбираем функцию «Итоги​

1. ​ мы сделали мини-табличку​ диапазоны. Как их​ «Цена».​ минимальное число в​Найдем максимальное значение​
2. ​ в ячейки​ нажать​где 100 -​=МАКС(ЕСЛИ((C2:M2="Да")*(B2:L2 C+S+E​ абсолютные адреса ссылки,​ нажмите на кнопку​ значением текущей ячейки,​ для ячеек. Например,​
3. ​Второй аргумент введен как​ вернет значение ошибки​ по величине значение​ по» – «Минимум».​ в ячейках D2:D3.​ сделать, смотрите в​Первый способ.​Excel​
4. ​ по модулю. Из​I14 J14​CTRL+SHIFT+ENTER​ нижняя граница, а​Дмитрий Дорогобузов​ а после оператора​ «Формат», чтобы задать​ формула возвращает логическое​
5. ​ зеленый. И нажмите​ константа массива, что​ #ЧИСЛО!, что выгодно​ из массива данных.​В сводной таблице появятся​ Этот диапазон укажем.​ статье «Чтобы размер​Минимальное число в​по условию​
6. ​ рисунка выше видно,​. Решением является формула​):​ 300 – верхняя​: Dude,​ сравнения =B2 –​ оранжевый цвет заливки​

​ значение ИСТИНА и​ ОК на всех​

​ позволило найти 3​ ее отличает от​ Например, формула =НАИБОЛЬШИЙ(A2:B6;1)​ минимальные значения. Получится​Нажимаем «ОК». Получится​

​ таблицы Excel менялся​Excel по условию.​. Например, у нас​ что это -99.​ массива:​=НАИБОЛЬШИЙ(ЕСЛИ(A6:A30=E6;B6:B30;"");1)​

Функция наименьший и наибольший в Excel

​ граница. A1 -​по первой формуле​ относительные, так как​ для ячейки. И​ сразу же применяется​ открытых окнах.​ наибольших значения.​ функции МАКС(), возвращающую​ вернет максимальное значение​ так.​ такая формула. =ДМИН($A$1:$B$6;$B$1;D2:D3)​ автоматически» здесь.​Рядом с таблицей​ есть таблица с​ Для этого используйте​=НАИБОЛЬШИЙ(ЕСЛИ((Числа>=I14)*(Числа​или с Именованными диапазонами:​ вычисленное значение.​ эксель выдает ошибку​ значение функции сравнивается​ нажмите ОК на​ формат со соответствующим​В результате мы выделили​Аналогично можно найти, например,​ в этом случае​ (первое наибольшее) из​Таким же способом можно​ Это простая формула,​Второй способ.​ или на другой​

​ перечнем товаров, нам​ формулу массива: ​В.​=НАИБОЛЬШИЙ(ЕСЛИ(Текст=E6;Числа;"");1)​Для этого примера​ "Знач". По второй​ с каждой ячейкой​ всех окнах.​ цветом заливки. Так​ цветом ячейку с​ среднее 2-х наибольших:​ 0!​ диапазона​ выбрать максимальные данные,​

​ НЕ формула массива.​Работа с базами данных​ странице книги нарисуем​ нужно найти товар​=ЕСЛИ(МАКС(ABS(Числа))=МАКС(Числа);МАКС(Числа);-МАКС(ABS(Числа)))​Найдем с помощью​

Найти второе максимальное значение по условию

​Часть формулы Текст=E6, вернет​​ в F4 пишешь:​
​ я не понял​ выделенного диапазона. А​Создайте третье правило. А​ же срабатывает правило​ максимальным числовым значением.​ =СРЗНАЧ(НАИБОЛЬШИЙ(A5:A9;<1;2>))​Значение числа в текстовом​A2:B6​ выбрав в диалоговом​ Нажимаем "Enter".​ в​ такую таблицу, в​ по самой низкой​
​Е.​ формулы массива минимальное​
​ массив <ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ: ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>​=B4*МАКС (50%;МИН (100%;E4))​ к чему относится​ функция НАИБОЛЬШИЙ работает​
​ в поле ввода​ для умного форматирования​Чтобы выделить цветом минимальное​
​Удивительно, но 2 последние​

​ формате игнорируется функцией НАИБОЛЬШИЙ()​​.​
​ окне функцию «Максимум»,​

​Эту формулу можно​​Excel.​
​ которой будут отражаться​ или самой высокой​Найдем минимальное положительное​ значение среди тех,​ (для просмотра результата​и копируешь это​

​ C+S+E​​ обратно пропорционально.​ введите формулу: =НАИМЕНЬШИЙ($B$2:$B$9;3)=B2​ ячеек столбца и​ значение в Excel,​

​ формулы даже не​​ (см. столбец Е​

​Синтаксис​​ другие значения.​ копировать в другие​Найти максимальное или​
​ искомые данные.​
​ цене. Разберем несколько​ число:​ которые соответствуют значению​

​ выделите эту часть​​ вниз.​Z​

​Полезный совет! Если нет​​ и нажмите на​
​ с функцией МИН.​ повторите весь порядок​ обязательно вводить как​ на рисунке выше).​
​НАИБОЛЬШИЙмассивk​В Excel можно​ ячейки строки. Получится​ минимальное значение можно​
​В ячейке Е2 пишем​​ способов.​​=НАИМЕНЬШИЙ(Числа;СЧЁТЕСЛИ(Числа;"​ Текст3:​​ формулы в Строке​

Как в Экселе задать минимальное и максимальное значение ячейки?

​Пробелы ПЕРЕД скобками​: Трои кнопки одним​ необходимости разбивать 3​ кнопку «Формат», чтобы​​ действий, описанных выше.​ формулы массива.​ Перед нахождением наибольшего​)​ выбирать и считать​ такая таблица с​

​ функцией ДМИН. Это​​ такую формулу. <=МИН(ЕСЛИ(Товар=D2;Цена))>​
​Есть в Excel​
​=НАИМЕНЬШИЙ(ЕСЛИ(Числа>0;Числа);1) – формула массива.​=МИН(ЕСЛИ((Текст=E7);Числа;"");1)​ формул и нажмите​ удалить из формул.​ разом – формула​
​ наименьших значения на​ задать желтый цвет​
​В разных ситуациях полезно​
​ Только вместо функции​Функция НАИБОЛЬШИЙ() является достаточно​
​ значения можно попытаться​Массив​

Формулы массива с каждым днем нравятся мне всё больше и больше! &#128578; Недавно у меня возникла задача – найти адрес ячейки, содержащей максимальное значение в диапазоне. Для начала я создал тестовый диапазон А1:F10 (рис. 1), заполнив его случайными целыми числами от 1 до 100 с помощью функции =СЛУЧМЕЖДУ(1;100).

Рис. 1. Исходный диапазон

Скачать заметку в формате Word, примеры в формате Excel

Примечания. Если рисунки выглядят мелко, загрузите их на свой ПК. Мне не удалось «укротить» кавычки внутри формул, они все время изображались как «лапочки», поэтому пришлось использовать рисунки. Текст формул можно получить в файле Word или Excel.

Чтобы было легче визуально следить за максимальным и минимальным значениями в диапазоне, я задал условное форматирование (рис. 2).

Рис. 2. Условное форматирование по всем ячейкам диапазона позволяет выделять цветом ячейки, содержащие максимальное и минимальное значения

Напомню, что функция =СЛУЧМЕЖДУ() обновляется всякий раз, когда в Excel выполняется какая-либо операция с числами (но не форматирование). Можно просто жать F9 (эквивалентно команде «пересчитать»).

Кто не знаком с формулами массива, рекомендую начать с прочтения заметки Введение в формулы массива.

Аналог формулы для вычисления адреса ячейки с максимальным значением в диапазоне я нашел в книге Уокенбаха «Формулы в MS Excel 2010»:

Эта формула массива возвращает номер строки, в которой находится максимальное значение одномерного вертикального диапазона «массив»:

Для того, чтобы воспользоваться формулой Уокенбаха, надо присвоить имя нашему диапазону А1:F10. Я создал динамический массив под именем массив (рис. 3). Для упрощения можно создать статический массив, набрав вместо формулы, выделенной на рис. 3, следующее: =Лист1!$A$1:$F$10. Преимущество динамического массива на основе функции СМЕЩ проявятся позже, если вам понадобится расширить рамки вашего диапазона. Например, добавить строки и/или столбцы. Если при этом вы создали статический диапазон, вам понадобится руками изменить область определения массива под именем массив. Если же у вас был создан динамический массив, никаких изменений вносить не потребуется! Кто не знаком с работой функции СМЕЩ, рекомендую почитать Автоматическое обновление сводной таблицы.

Рис. 3. Присвоение имени двумерному диапазону

Рассмотрим как работает формула Уокенбаха:

Функция ЕСЛИ создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру строки этой ячейки, в противном случае элемент массива равен пустой строке. Функция МИН использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер строки, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер первой строки, где содержится это значение.

К сожалению, формула Уокенбаха предназначена для одномерного вертикального диапазона, поэтому она всегда возвращает адрес ячейки из столбца А, например, $A$8, когда правильное значение было бы $D$8.

Чтобы приспособить формулу Уокенбаха для вычисления адреса ячейки с максимальным значением в двумерном диапазоне, создадим еще один динамический массив – столбМакс (рис. 4)

Рис. 4. Присвоение имени одномерному вертикальному диапазону, содержащему один столбец, в который входит ячейка с максимальным значением

Рассмотрим подробнее, как функция СМЕЩ формирует этот одномерный вертикальный диапазон (рис. 5). Кстати, здесь, в отличие от выше описанного случая, не обойтись без динамического массива, так как заранее номер столбца, содержащего ячейку с максимальным значением, не известен…

Рис. 5. Функция СМЕЩ, динамически формирующая одномерный вертикальный диапазон

Функция ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»») создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру столбца этой ячейки, в противном случае элемент массива равен пустой строке.

Функция МИН(ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»»)) использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер столбца, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер самого левого столбца, где содержится это максимальное значение.

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

Рис. 6. Формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне

Подробно изучить, как работает формула можно с помощью сервиса Excel – пошаговое вычисление формулы (рис. 7).

Рис. 7. Пошаговое вычисление формулы

Аналогично создается и формула для нахождения адреса ячейки с минимальным значением в двумерном диапазоне: