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

Формула ранжирования в Excel для выборки наибольших значений

В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:

Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.

В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.

Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:

Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:

Дополнительная информация

• Пустые ячейки, логические выражения, текст или ссылки игнорируются;
• Если в аргументах указаны ячейки с ошибкой или текст, который не возможно перевести в число – функция выдаст ошибку;
• Аргументами функции также могут служить имена, массивы или ссылки которые содержат в себе числа;
• Учитываются логические значения и текстовые значения чисел, которые вы вводите непосредственно в список аргументов;
• Если аргумент является массивом или ссылкой, то функция учитывает только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются;
• Если в аргументах функции не указано ни одного числового значения, функция выдаст значение “0” (ноль).

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки – столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в “умную таблицу”. Для этого выделите его и выберите на вкладке Главная – Форматировать как таблицу (Home – Format as Table) или нажмите Ctrl+T. Наша “поумневшая” таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1 или Таблица1. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей “умной” таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу – это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

• Диапазон_чисел – диапазон с числами, из которых выбирается минимальное или максимальное
• Диапазон_проверки – диапазон, который проверяется на выполнение условия
• Условие – критерий отбора

Например, в нашем случае:

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

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN (IF (Table1=F4;Table1))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

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

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

• База_данных – вся наша таблица вместе с заголовками.
• Поле – название столбца из шапки таблицы, из которого выбирается минимальное значение.
• Критерий – таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

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

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка – Сводная таблица (Insert – Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по – Минимум:

5. СЧЕТ: Подсчет количества значений

Функция СЧЕТ подсчитывает количество ячеек в указанном диапазоне, которые содержат числа. Например, у вас есть 9 позиций товаров, а надо узнать, сколько из них было куплено. Выделяем необходимую область со значениями и нажимаем «ОК» — функция выдаст результат. В нашем случае все можно было посчитать и вручную, а что делать, если таких значений сотни? Верно — использовать операцию СЧЕТ.

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

Статья продолжится после рекламы

Примеры использования функций ДМИН и ДМАКС в Excel по условию

Пример 1. В таблице содержатся данные о сотрудниках фирмы (оформлена с учетом требований к БД). Необходимо определить:

1. Минимальный и максимальный возраст мужчины.
2. Минимальный и максимальны возраст женщины.
3. Максимальный возраст члена коллектива с фамилией, начинающейся на «Б».

Вид таблицы данных:

На этом же листе создадим таблицу условий и таблицу для вывода найденных значений:

В ячейку C2 поместим список с двумя возможными значениями условий выборки из базы данных («М» и «Ж»).

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

• A10:D18 – диапазон ячеек, в которых находится БД;
• D1 – ячейка с названием поля данных, в котором хранятся сведения о возрасте;
• A1:D2 – диапазон ячеек, в которых находится таблица условий.

В таблице критериев установим условие значением пола «Ж» и аналогичным способом найдем значения минимального/максимального возраста женщины:

Удалим содержимое из ячейки C2, в ячейке B2 введем строку =”=Б*”. Для поиска максимального возраста с установленным критерием используем функцию:

Данная запись является избыточной, поскольку после удаления значения из C2 будет произведен автоматический расчет с учетом нового критерия в ячейке D5. Однако, данная функция приведена для наглядности, чтобы продемонстрировать возможность указания второго аргумента в виде числового значения (в данном случае 4 – четвертый столбец, соответствующий полю «Возраст»). Полученный результат:

В результате мы получили все данные после выборки с условием отбора возраста из общей БД в Excel.

Функции МАКС и МИН в Excel по условию

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

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

СРЗНАЧ()

Статистическая функция СРЗНАЧ возвращает среднее арифметическое своих аргументов.

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

Если в рассчитываемом диапазоне встречаются пустые или содержащие текст ячейки, то они игнорируются. В примере ниже среднее ищется по четырем ячейкам, т.е. (4+15+11+22)/4 = 13

Если необходимо вычислить среднее, учитывая все ячейки диапазона, то можно воспользоваться статистической функцией СРЗНАЧА. В следующем примере среднее ищется уже по 6 ячейкам, т.е. (4+15+11+22)/6 = 8,6(6).

Статистическая функция СРЗНАЧ может использовать в качестве своих аргументов математические операторы и различные функции Excel:

Добавление математических операций

Функция МИН в Excel работает стабильно, даже если вы взаимодействуете с математическими операциями. Чтобы показать, как всё работает, ниже представлена информация об объединении нескольких массивов. Всё выглядит крайне просто. Нужно лишь следовать инструкции. Тогда вы добьётесь желаемого результата:

• Задайте первую функцию МИН, чтобы реализовать выбор массива данных. Выше уже рассматривался алгоритм, вам нужно лишь повторить.
• Теперь установите любой математический знак. Например, это будет плюс.
• Впишите вторую функцию МИН.
• Кликните на клавиатуре на Enter. Вы сможете увидеть, как математическая операция реализуется. В случае со сложением были суммированы два минимальных значения.

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

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

В этом случае придется записать более сложную формулу массива :

= НАИМЕНЬШИЙ(ЕСЛИ(($A$6:$A$16=E6)*($B$6:$B$16=F6);$C$6:$C$16;””);1)

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

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

Как задать диапазон чисел в Excel?

Как задать диапазон значений в Excel?

Динамический диапазон в MS EXCEL

1. на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя;
2. в поле Имя введите: Месяц;
3. в поле Область выберите лист Книга;
4. в поле Диапазон введите формулу =СМЕЩ(лист1!$ B$5;;;1;СЧЁТЗ(лист1!$ B$5:$I$5))
5. нажмите ОК.

Как сделать интервал чисел в Excel?

Увеличение междустрочных интервалов в ячейке

1. Щелкните правой кнопкой мыши необходимую ячейку и выберите пункт Формат ячеек в контекстном меню.
2. На вкладке Выравнивание в раскрывающемся списке по вертикали выберите пункт Распределенное.
3. Нажмите кнопку ОК. Текст будет равномерно распределен по ячейке.

Как задать диапазон ячеек в Excel?

Диапазон представляет собой набор из двух и более ячеек.

1. Для выбора диапазона B2:C4 кликните по нижнему правому углу ячейки В2 и протяните указатель мыши до ячейки C4.
2. Чтобы выделить диапазон, состоящий из отдельных (несмежных) ячеек, зажмите клавишу Ctrl и кликните по каждой ячейке, которую хотите включить в диапазон.

Как указать числовой диапазон в Excel?

Чтобы заполнить диапазон, следуйте инструкции ниже:

1. Введите значение 2 в ячейку B2.
2. Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8. …
3. Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.
4. Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.

Как присвоить одной ячейке диапазон значений?

Как вставить одинаковые данные (формулы) во все выделенные ячейки одновременно

1. Выделяем все ячейки, в которые нужно вставить одинаковые данные Выделяем целый столбец Выделяем целую строку …
2. Самый быстрый способ вставить формулу во все ячейки столбца
3. Вставляем одинаковые данные в несколько ячеек при помощи Ctrl+Enter.

Как указать диапазон?

В формулах диапазон записывается адресами двух ячеек, которые разделенные двоеточием. Верхняя левая и правая нижняя ячейка, которая входит в состав диапазона, например A1:B3

Обратите внимание! Ячейка, от которой начинается выделение диапазона, остается активной

Как обозначить диапазон чисел?

Диапазон значений «от … до» передается с помощью тире. Если значение указано в цифрах — тире без пробелов, прописью — тире отбивается пробелами с обеих сторон. длиной пять — десять метров. Если имеется в виду приблизительное значение «или, или», то между числами в словесной форме ставится дефис, в цифровой — тире.

Как найти количество определенных чисел в Excel?

Функция СЧЁТ подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция СЧЁТ используется для определения количества числовых ячеек в диапазонах и массивах чисел. Например, для вычисления количества чисел в диапазоне A1:A20 можно ввести следующую формулу: =СЧЁТ(A1:A20).

Как выделить весь диапазон ячеек в Excel?

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

Как обозначается диапазон ячеек в Excel?

Диапазон ячеек — это одна или несколько прямоугольных областей ячеек: смежный диапазон (состоящий из одной области) обозначается указанием адресов противоположных угловых ячеек через двоеточие: Выделяется смежный диапазон протягиванием мыши от начальной ячейки к конечной.

Как выделить диапазон ячеек в Excel?

Чтобы выбрать диапазон, выберем ячейку, а затем, нажимая левую кнопку мыши, перетащите указатель мыши над другими ячейками. Для этого также можно использовать SHIFT+клавиши со стрелками. Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.

Как обозначается диапазон ячеек Приведите пример?

Привести примеры. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например: А1:С15. 5.

Что такое диапазон и как его выделить?

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

Что называется активной ячейкой?

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