Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Учитывая, что функция – зависимость одного параметра от другого, зададим значения для оси абсцисс с шагом 0,5. Строить график будем на отрезке [-3;3]. Называем столбец «х» , пишем первое значение «-3» , второе – «-2,5» . Выделяем их и тянем вниз за черный крестик в правом нижнем углу ячейки.

Как построить график в Excel по данным таблицы: пошаговая инструкция |

Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

Среднее

Среднее арифметическое вычисляется так: сложить все цифры из множества и поделить на их количество. В Экселе можно посчитать суммы, узнать, сколько ячеек в строке и так далее. Но это слишком сложно и долго. Придётся использовать много разных функций. Держать в голове информацию. Или даже что-то записывать на листочек. Но можно упростить алгоритм.

1. Поставьте ячейку курсор в любое свободное место таблицы.
2. Перейдите на вкладку «Формулы».
3. Нажмите на «Вставить функцию».
4. Выберите «СРЗНАЧ».
5. Если этого пункта нет в списке, откройте его с помощью опции «Найти».
6. В области «Число1» введите адрес диапазона. Или напишите несколько цифр в разных полях «Число2», «Число3».
7. Нажмите «OK». В ячейке появится нужное значение.

Так можно проводить расчёты не только с позициями в таблице, но и с произвольными множествами. Excel, по сути, играет роль продвинутого калькулятора.

Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.

Выделение столбцов или строк на диаграмме в Excel.

1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
2. Поставьте курсор в любую ячейку.
3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Проще всего разобраться, как построить график в Эксель по данным таблицы, состоящей всего из двух колонок. Для наглядного примера можно взять названия месяцев и объем полученной за это время прибыли. Ряд будет только один, а осей на графике — две. Строить диаграмму можно по следующей пошаговой инструкции:

Выделение строк на диаграмме

И естественно, данный пример также подойдет и для выделения строки на диаграмме, единственное отличие в построении будет в выборе не гистограммы с группировкой, а линейчатой диаграммы с группировкой:

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

Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно пишите в комментариях.

В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.

Как определить функцию по графику в excel

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