Функции в excel минимальное, максимальное и среднее значение. как найти минимальное значение в excel как вывести минимальное значение в excel

Задачи

Предположим, что в диапазоне A 5:D11 имеется таблица продаж ( База_данных ), содержащая Поля (столбцы) Товар , Продавец, Продажи и Дата (см. файл примера и рисунок выше).

Сформулируем задачи в виде вопросов.

Вопрос 1 (Определить максимальные продажи Белова ). Т.е. найдем строки, в которой в столбце Продавец содержится значение Белов . Если такие строки есть в таблице, то для них выведем максимальное значение из столбца Продажи .

Алгоритм следующий:

• Создадим в диапазоне G5:G6 табличку критериев (желательно над исходной таблицей или сбоку, чтобы она не мешала добавлению новых данных в таблицу), состоящую из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
• Условия отбора должны быть записаны в специальном формате: =”=Белов” (будет производиться поиск в столбце Продавец только значений точно совпадающих со словом Белов (или белов , БЕЛОВ , т.е. без учета регистра ). Если написать условие не в виде =”=Белов”, а просто ввести в ячейку значение Белов , то условию будут удовлетворять текстовые строки, которые содержат слово Белов , например, « Белов Иван», «Иван Белов» и пр.)

Предполагаем, что База_данных (исходная таблица) находится в A 5:D11 . С5 – это ссылка на заголовок столбца, в котором ищется максимальное значение, если выполняется условие. G5:G6 – ссылка на табличку критериев (см. рисунок выше).

Итоговая формула выглядит так =ДМАКС(B5:C11;C5;G5:G6)

Как видно из исходной таблицы, условиям удовлетворяет 3 строки, поэтому результатом будет максимальное значение из массива (450; 6544; 1245), т.е. 6544.

Альтернативное решение – формула =НАИБОЛЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;””);1) , которая вводится как формула массива ( подробнее см. здесь ). В случае, когда ни одна строка не удовлетворяет условиям, то формула вернет ошибку #ЧИСЛО! в отличие от ДМАКС() , которая вернет 0 (это может ввести в заблуждение, т.к. не понятно есть ли в исходной таблице строки, удоблетворяющие критерию или нет. См. раздел Недостаток функции ).

Вопрос 2 (Последняя продажа Белова). В какой день Белов продал последнюю партию товара?

=ДМАКС(B5:D11;D5;G5:G6)

Решение аналогично предыдущей задаче, только максимальное значение ищется не в столбце С ( Продажи ), а в столбце D ( Дата ).

Вопрос 3 (Максимальные продажи Мяса продавцом Белов)

=ДМАКС(A5:C11;C5;F5:G6)

В данном случае таблица критериев представляет собой 2 столбца Товар и Продавец .

Вопрос 4 (Максимальные продажи среди продаж Белова или Батурина)

=ДМАКС(B5:C11;C5;G5:G7)

В данном случае таблица критериев представляет собой 1 столбец Продавец с 2-мя строками (с фамилиями продацов Белов и Батурин ).

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

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

Впишите диапазон и нажмите Enter

Найти и вычислить поможет Excel-инструмент «Автофункции».

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

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

Определение ранга числа

ранг(адрес
ячейки; диапазон)
—
группа статистических функций.

В
текущую ячейку возвращается величина,
соответствующая положению (рангу) числа,
заданного адресом ячейки, в указанном
диапазоне.

Пример
8.
В ячейки
D1,D2,D3,D4,D5
скопируйте информацию из соответствующих
ячеек столбца А. Для каждой ячейки из
диапазона D1:D5
определить ранг числа.

Результат
должен быть получен в ячейках E1:E5.
Функция ранга вводится сначала в ячейку
E1,
затем копируется для всех ячеек до E5.

Пошаговыми
действиями Мастера
функций
в
ячейку E1
вводим формулу:

=ранг(D
1;
$
D
$1:$
D
$5)
—
знак $
устанавливает абсолютные
адреса,
чтобы
диапазон ячеек не менялся при копировании.

После
копирования формулы вниз для всех ячеек
до E5
получим ранги для каждого значения
диапазона. Ранг числа с максимальным
значением в диапазоне D1:D5
будет равен 1, а с минимальным –5.

Функция СУММЕСЛИМН для суммирования значений по нескольким условиям

Теперь давайте приведем для наглядности еще один пример. При этом таблицу оставляем ту же, что и в предыдущем случае. 

Формулу используем следующую (но записываем ее, как формулу массива, то есть вводим ее через комбинацию клавиш CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

После того, как функция СУММЕСЛИМН просуммирует массив значений, исходя из критериев, заданных в формуле (то бишь, стран Китай и Грузия), полученный массив суммируется обычной функцией СУММ, которая записана, как формула массива.

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

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

6

Как видим, у нас все получилось. Обязательно получится и у вас. Больших успехов на этом поприще. Это очень простая функция, которую понять может человек, который только ступил на путь изучения Excel. И мы уже знаем, что функция СУММЕСЛИМН позволяет быть эффективным в любой сфере деятельности, начиная бухгалтерией и заканчивая даже образованием. Даже если вы строите карьеру в любой другой сфере, которая не была описана выше, все равно эта функция поможет заработать деньги. Этим она и ценна.

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

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

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

​ функции МАКС и​СОВЕТ:​Решением является формула массива.​ клавишу ​​Нахождение максимального/ минимального значения​​ . ​​ необязательно создавать 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 или​ значения (см. Файл​ работать со сводной​ окне в строке​ Подробнее об этом​

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

Запись МИН в других функциях

Некоторые пользователи, только начинающие свой путь в Microsoft Excel, не знают, что в рамках одной функции может находиться и другая. Конечно, непосредственно в МИН нельзя засунуть еще какое-то выражение, поскольку функция применяет только аргумент, а вот саму МИН можно поместить в другую функцию. Например, объявите логическую операцию =ЕСЛИ(МИН(A1:A10)). Вы видите, что первое выражение для ЕСЛИ – это возвращение минимального значения. 

Теперь нужно решить, какой знак поставить, а затем записать значения лжи и истины. Полностью формула выглядит следующим образом: =ЕСЛИ(МИН(A1:A10)>X;”+”;”-”). То есть если МИН будет больше указанного числа, значение ЕСЛИ станет «+», в противном случае «-». Вместо X можно подставить любую другую функцию, если в этом есть надобность.

Если описание выше вам показалось сложным, объявите ЕСЛИ самостоятельно и откройте окно «Аргументы функции». В нем вы увидите разделение на поля и поймете, что в логическом выражении нужно написать саму функцию МИН и число либо другую функцию после знака «больше», «меньше» или «равно». Дополнительно сами разработчики представляют подсказки, поэтому понять принцип действий будет нетрудно.

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

Вычисление минимального, максимального и среднего значения в Microsoft Excel

В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

В этой статье мы расскажем, как вычислить различные значения в Эксель

Максимальное и минимальное

Вот как найти максимальное значение в Excel:

1. Поставьте курсор-ячейку в любое место.
2. Перейдите в меню «Формулы».
3. Нажмите «Вставить функцию».
4. В списке выберите «МАКС». Или напишите это слово в поле «Поиск» и нажмите «Найти».
5. В окне «Аргументы» введите адреса диапазона, максимальное значение которого вам нужно узнать. В Excel имена клеток состоят из буквы и цифры («B1», «F15», «W34»). А название диапазона — это первая и последняя ячейки, которые в него входят.
6. Вместо адреса можно написать несколько чисел. Тогда система покажет самое большее из них.
7. Нажмите «OK». В клетке, в которой стоял курсор, появится результат.

Следующий шаг — укажите диапазон значений

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

Среднее

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

Вот как найти среднее значение в Excel:

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

Нажмите ОК для подсчета

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

Другие способы

Максимальное, минимальное и среднее можно узнать и другими способами.

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

Впишите диапазон и нажмите Enter

Найти и вычислить поможет Excel-инструмент «Автофункции».

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

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

Как найти второе минимальное значение excel

Как быстро найти или вернуть второе по величине или наименьшее значение в Excel?

Найти наибольшее или наименьшее значение в диапазоне может быть просто для большинства пользователей Excel, но как насчет поиска или возврата второго или n-го наибольшего или наименьшего значения из диапазона? В этом руководстве вы узнаете, как быстро вернуть второе по величине или наименьшее значение в Excel.

Найдите или верните второе наибольшее или наименьшее значение с помощью формул

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

Выберите пустую ячейку, например F1, введите эту формулу = НАИБОЛЬШИЙ (A1: D8,2) , и нажмите Enter ключ, чтобы получить второе по величине значение диапазона. Смотрите скриншот:

Если вы хотите найти второе наименьшее значение, вы можете использовать эту формулу = МАЛЕНЬКИЙ (A1: D8,2) , см. снимок экрана:

Наконечник: В приведенных выше формулах A1: D8 — это диапазон ячеек, из которого вы хотите найти значение, 2 — второе по величине или наименьшее значение, которое вы хотите найти, и вы можете изменить их по своему усмотрению.

Найдите и выберите наибольшее или наименьшее значение с помощью Kutools for Excel

Найти и вернуть наивысшее или наименьшее значение из указанного диапазона легко, но в некоторых случаях вы не только хотите знать наибольшее или наименьшее значение, но также хотите определить его положение. В этом случае вы можете подать заявку Kutools for ExcelАвтора Выберите ячейки с максимальным и минимальным значением утилита, чтобы быстро справиться с этим без формул.

После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)

1. Выберите диапазон ячеек, который вы хотите найти, найдите максимальное или минимальное значение и нажмите Kutools > Выберите > Выберите ячейки с максимальным и минимальным значением. Смотрите скриншот:

2. В появившемся диалоговом окне выполните следующие действия:

1) Укажите тип ячеек, из которого вы хотите найти максимальное / минимальное значение, вы можете искать в ячейках формулы, ячейках значений или и в ячейках формулы, и в ячейках значений;

2) Укажите, чтобы выбрать максимальное значение или максимальное значение;

3) Укажите, чтобы выбрать максимальное или минимальное значение из всего выбора или каждой строки / каждого столбца в выборе;

4) Укажите, чтобы выбрать все совпадающие ячейки или только первую.

3. Нажмите Ok, и было выбрано наибольшее или наименьшее значение.

Вычисление минимального, максимального и среднего значения в Microsoft Excel

В Microsoft Office Excel можно работать с цифрами и узнавать любое числовое значение. Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты — формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в Excel.

Это особенно актуально в таблицах с большим объёмом данных. Если в столбце, например, указаны цены на продукцию торгового центра. И вам надо узнать, какой товар самый дешёвый. Если искать его «вручную», уйдёт очень много времени. Но в Экселе это можно сделать буквально за несколько кликов. Утилита также высчитывает среднее арифметическое. Ведь это две простые операции: сложение и деление.

В этой статье мы расскажем, как вычислить различные значения в Эксель

Максимальное значение с условиями.

Довольно часто случается, что из большого объёма данных нужно выбрать максимальное значение из какой-то его части. К примеру, найти самую большую партию какого-то определённого товара в огромном списке продаж. Или же найти наивысшую оценку по какому-то предмету среди большого набора данных оценок, набранных учащимися определенного класса по нескольким предметам в школе.

Если вы хотите получить максимальное значение с учётом каких-то условий, вы можете выбрать из нескольких предложенных ниже формул. Чтобы убедиться, что все они возвращают одинаковый результат, мы протестируем их на одном и том же наборе данных.

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

МАКС + ЕСЛИ

Если вы ищете решение, которое работает во всех версиях Excel, используйте функцию ЕСЛИ для проверки условия, а затем передайте полученный массив в функцию:

Чтобы это работало, необходимо нажать Ctrl + Shift + Enter одновременно, чтобы ввести как формулу массива. Если все сделано правильно, Excel заключит ваше выражение в {фигурные скобки}, что является визуальным указанием на формулу массива.

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

МАКС + СУММПРОИЗВ

Если вам не нравится использовать формулы массива в ваших листах, объедините МАКС с функцией СУММПРОИЗВ, которая умеет работать с массивами:

МАКСЕСЛИ

В Excel 2019 и Office 365 есть специальная функция МАКСЕСЛИ, которая предназначена для поиска наибольшего значения по 126 критериям.

В нашем случае используется только одно условие, поэтому формула очень проста:

На приведенном ниже скриншоте показаны все 3 формулы в действии:

Получить максимальное значение без учета нулей

Фактически, это разновидность условного МАКС, рассмотренного в предыдущем примере. Чтобы исключить нули, используйте логический оператор «не равно» и поместите выражение «<> 0» либо в критерий функции МАКСЕСЛИ, либо в конструкцию МАКС + ЕСЛИ.

Как вы понимаете, проверка этого условия имеет смысл только в случае наличия отрицательных чисел. Для положительных чисел эта проверка излишняя, потому что любое положительное число больше нуля.

Чтобы попробовать, давайте найдем самую маленькую скидку в диапазоне C2:C7. Поскольку все скидки представлены отрицательными числами, наименьшая скидка фактически является наибольшим значением.

Обязательно нажмите , чтобы правильно заполнить эту формулу массива:

А это обычная формула –

И вот что у нас получилось:

Найдите максимальное значение, игнорируя ошибки

Когда вы работаете с большим объемом данных, управляемых различными формулами, есть вероятность, что некоторые из них приведут к ошибкам. Соответственно, функция МАКС тоже вернет ошибку.

В качестве обходного пути можно использовать МАКС вместе с ЕОШИБКА. Учитывая, что вы ищете в диапазоне A1: B5, формула принимает следующую форму:

Чтобы упростить формулу, используйте функцию ЕСЛИОШИБКА вместо комбинации ЕСЛИ + ЕОШИБКА. Это также сделает логику более очевидной — если есть ошибка в A1:B6, замените ее пустой строкой (»), а затем вычислите самое большое значение в диапазоне:

Неудобство состоит в том, что нужно не забывать нажимать , потому что это работает только как формула массива.

В Excel 2019 и Office 365 функция МАКСЕСЛИ может быть хорошим решением при условии, что ваш набор данных содержит хотя бы одно положительное число или нулевое значение:

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

Также идеально подходит функция АГРЕГАТ, которая может выполнять ряд операций и игнорировать значения ошибок:

Число 4 в первом аргументе предписывает выполнить поиск максимального числа, шестёрка во втором аргументе – это параметр «игнорировать ошибки», а A1: B6 — ваш целевой диапазон.

Все формулы возвращают одинаковый результат:

Как найти среднее арифметическое число в Excel

Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.

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

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

Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» – кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».

Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

Теперь посмотрим, что еще умеет функция СРЗНАЧ.

Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:



Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

Найти среднее арифметическое чисел, которые больше или равны 10.

Функция: =СРЗНАЧЕСЛИ(A1:A8;”>=10″)

Результат использования функции СРЗНАЧЕСЛИ по условию “>=10”:

Третий аргумент – «Диапазон усреднения» – опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

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

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12)

Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово “столы”). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

Функция будет выглядеть так: =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон – столбец с наименованиями товаров. Критерий поиска – ссылка на ячейку со словом «столы» (можно вместо ссылки A7 вставить само слово “столы”). Диапазон усреднения – те ячейки, из которых будут браться данные для расчета среднего значения.

В результате вычисления функции получаем следующее значение:

Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.

Как посчитать средневзвешенную цену в Excel?

Как посчитать средний процент в Excel? Для этой цели подойдут функции СУММПРОИЗВ и СУММ. Таблица для примера:

Как мы узнали средневзвешенную цену?

Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ – сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

Среднее квадратическое отклонение: формула в Excel

Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

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

среднеквадратическое отклонение / среднее арифметическое значение

Формула в Excel выглядит следующим образом:

СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

Особенности использования функций МАКС и МАКСА в Excel

=МАКС( число1 ;;. )

• число1 – обязательный аргумент, характеризующий первое значение диапазона числовых данных (массив, вектор), среди которых требуется найти максимальное значение.
• … — второй и последующие необязательные аргументы, характеризующие второе и последующие числовые значения исследуемого диапазона.

Функция МАКСА имеет следующую синтаксическую запись:

=МАКСА( значение1 ;;. )

• значение1 – обязательный аргумент, характеризующий первое вхождение диапазона исследуемых данных.
• … — второй и последующие необязательные аргументы, характеризующие второй и последующие вхождения.

Примечания:

1. Обе рассматриваемые функции принимают в качестве аргументов имена, данные числового, логического, ссылочного и текстового типов данных.
2. Функция МАКС учитывает логические значения только в том случае, если они явно переданы в качестве аргумента (например, (ИСТИНА;-5;ЛОЖЬ) вернет значение 1, однако аргументы (A1;A2;A3) вернут значение -5, если A1=ИСТИНА, A2=-5, A3=ЛОЖЬ). Функция МАКСА при расчетах учитывает даже ссылки на ячейки, содержащие данные логического типа.
3. При использовании функций для данных формата Дата будет возвращено максимальное значение в коде времени Excel.
4. Если в переданные в качестве аргументов массиве или диапазоне данных в виде ссылки содержатся только текстовые данные, результатом выполнения функций МАКС и МАКСА будет значение 0. Если массив или диапазон данных содержат кроме числовых значений также текстовые значения и пустые ячейки, функции МАКС и МАКСА будут их игнорировать в расчетах.
5. Функция МАКСА удобна для использования в случаях, если необходимо передать ссылку на диапазон данных, содержащий текстовые представления чисел и логические значения, которые должны быть учтены в расчетах. В противном случае следует использовать функцию МАКС.
6. Поскольку рассматриваемые функции не имеют аналогов с логической проверкой (например, функция СУММ имеет аналог с проверкой СУММЕСЛИ), для проверки условий необходимо использовать следующий вид записи =МАКС(ЕСЛИ(проверяемое_выражение;массив1;массив2)), где массив1 и массив2 – варианты аргументов функции МАКС в зависимости от результата выполнения проверяемого выражения).

Функции МАКС и МАКСА могут быть использованы как формулы массива, что удобно при совместном использовании с логическими функциями.