BattleCaseкомпьютерная помощь

Формула минимального значения в эксель

В Excel имеется функция определения минимального значения. Однако, найти минимальное значение по условию проблематично. Справиться с этой задачей может функция из надстройки =МИНСЕСЛИ

Формула минимального значения в эксель
Содержание
  1. Пример 1
  2. Максимальное и минимальное
  3. Среднее
  4. Другие способы
  5. Как сделать выборку в Excel по условию
  6. Как выбрать значение с наибольшим числом в Excel
  7. Множественные условия
  8. Определение максимального значения
  9. Определение минимального значения
  10. Определение ранга числа
  11. Функции прогнозирования
  12. Функции для работы с матрицами

(аналогична стандартной функции Excel СУММЕСЛИ).

В Excel версии 2016

И выше появилась встроенная функция МИНЕСЛИ

, можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel

У функции следующие аргументы =МИНЕСЛИ( ДИАПАЗОН;КРИТЕРИЙ;[ДИАПАЗОН_ПОИСКА

  • КРИТЕРИЙ - Условие в формате числа, выражения или текста, определяющее проверку минимального значения.
  • [ ДИАПАЗОН_ПОИСКА ] - Фактический диапазон для определения минимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН .

Пример 1

В качестве критерия можно указывать значения и логические выражения:

  1. Рассмотрим следующий пример в котором определяется минимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение "Литература", а в параметр ДИАПАЗОН - список предметов.
  2. Если в качестве критерия указать логическое выражение "<>Русский" , то определится минимальная оценка по всем предметам за исключением русского языка.

Пример 2

В следующем примере параметр ДИАПАЗОН_ПОИСКА

Не задан, поэтому минимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН

Этот табличный процессор справится практически со всеми расчётами. Он идеально подходит для бухгалтерского учёта. Для вычислений существуют специальные инструменты - формулы. Их можно применять к диапазону или к отдельным ячейкам. Чтобы узнать минимальную или максимальную цифру в группе клеток, необязательно искать их самостоятельно. Лучше воспользоваться предназначенными для этого опциями. Также полезно будет разобраться, как посчитать среднее значение в 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. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

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

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

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

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

Как сделать выборку в Excel по условию

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

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

ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);"")))

В месте "диапазон_данных_для_выборки" следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент "диапазон" означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе "заголовок_столбца" для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

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

Выборка соответственного значения с первым наименьшим числом:

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

Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание - это выбирать из исходной таблицы (указывается в первом аргументе - A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

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

Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы.

Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

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

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

=70;

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

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

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

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

Пусть имеется таблица с двумя столбцами: текстовым и числовым.

Для удобства понимания формул создадим два для каждого из столбцов: Текст

( A

A

) и Числа

( B6:B30

). (см. Файл примера

Рассмотрим несколько задач:

А.

Найдем максимальное значение среди тех чисел, которые соответствуют значению Текст1

(критерий введем в ячейку E6

Т.е. Будем искать максимальное значение не среди всех значений столбца Числовые значения

, а только среди тех, у которых в той же строке в столбце А

Текстовое значение равно Текст1

. Напишем (не забудьте при вводе формулы нажать CTRL+SHIFT+ENTER

=НАИБОЛЬШИЙ(ЕСЛИ(A6:A30=E6;B6:B30;"");1)

Или с Именованными диапазонами

= НАИБОЛЬШИЙ(ЕСЛИ(Текст=E6;Числа;"");1)

Часть формулы Текст=E6

, вернет {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ: ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} (для просмотра результата выделите эту часть формулы в и нажмите клавишу ). ИСТИНА соответствует строкам, у которых в столбце Текстовые значения

Содержится значение Текст1

Часть формулы ЕСЛИ(Текст=E6;Числа;"")

, вернет {10:"":"":"":-66:"":"":"": -37:"":"":"":-5:"": "":"":4:"":"":"":8:"":"":"":""}, где вместо ИСТИНА подставлено значение из числового столбца, а вместо ЛОЖЬ - значение . Вместо "" можно было бы использовать любой текстовый символ (букву) или вообще опустить (в этом случае массив будет выглядеть так {10:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-66: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:-37:ЛОЖЬ: ЛОЖЬ: ЛОЖЬ:-5:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:4: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:8: ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}).

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

Со вторым параметром =1 использована вместо функции МАКС()

, т.к. В случае, если критерию не удовлетворяет ни одна строка, то формула = МАКС({"":"":"":"":"":"":"": "":"":"":"": "":"":"":"":"":"":"": "":"":"":"":"":"":""})

Вернет 0!, что может ввести в заблуждение. Функция НАИБОЛЬШИЙ()

В этом случае вернет ошибку #ЧИСЛО!

Б.

Найдем максимальное значение только среди чисел принадлежащих определенному интервалу значений, например от 5 до 50. Границы можно ввести в ячейки I

И J14

. Решением является :

= НАИБОЛЬШИЙ(ЕСЛИ((Числа>=I14)*(Числа<=J14);Числа);1)

В.

Найдем с помощью формулы массива

Минимальное значение среди тех, которые соответствуют значению Текст3

= МИН(ЕСЛИ((Текст=E7);Числа;"");1)

Т.е. Если в столбце А

Значение = Текст3

, то учитывается значение в столбце B

, если значение <> Текст3

, то учитывается максимальное значение+1, т.е. Заведомо НЕ минимальное. Далее функция МИН()

Возвращает минимальное значение из полученного массива, причем понятно, что ни одно из значений, где <> Текст3,

Не исказит результат (см. Задачу А).

Другое решение с помощью формулы ДМИН()

, которая не является формулой массива

=ДМИН(A5:B30;B5;I8:I9)

Г.

Найдем минимальное значение, среди тех, которые больше :

=ДМИН(A5:B30;B5;I10:I11)

Где в диапазоне I10:I11

Содержится критерий =B6>СРЗНАЧ(Числа)

Д.

Найдем максимальное значение по модулю. Из рисунка выше видно, что это -99. Для этого используйте формулу массива

ЕСЛИ(МАКС(ABS(Числа))=МАКС(Числа);МАКС(Числа);-МАКС(ABS(Числа)))

Е.

Найдем минимальное положительное число:

= НАИМЕНЬШИЙ(Числа;СЧЁТЕСЛИ(Числа;"<=0")+1)

- обычная формула!

= НАИМЕНЬШИЙ(ЕСЛИ(Числа>0;Числа);1)

- формула массива

СОВЕТ:

Все вышеуказанные задачи можно решить без использования и функции ДМИН()

. Для этого потребуется создать дополнительный столбец, в котором будут выведены только те значения, которые удовлетворяют критериям. Затем, среди отобранных значений с помощью функций МАКС()

Или МИН()

, определить соответственно максимальное или минимальное значение (см. Файл примера

Лист без формул массива).

Формула минимального значения в эксель

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

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

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

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

В файле примера

Для наглядности настроено . Кроме того, для выбора критериев использован (см. Желтые ячейки).

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

Срзнач(диапазон1;

Диапазон2;...)

В

Текущую ячейку возвращается среднее

Значение для чисел указанного диапазона.

Пример

В диапазоне

Ячеек A1:A5 из примера

Определить

Среднее значение.

Результат

Должен быть получен в ячейке А7. Пошаговыми

Действиями Мастера

Функций

В

Ячейку А7 следует ввести формулу: =срзнач(А1:А5).

Определение максимального значения

Макс(диапазон1;

Диапазон2;...)

Группа статистических функций.

В

Текущую ячейку возвращается максимальное

Число из данного диапазона.

Пример

В диапазоне

Ячеек A1:A5 из примера

Определить

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

Результат

Должен быть получен в ячейке А8.

Пошаговыми

Действиями Мастера

Функций

В

Ячейку А8 следует ввести формулу:

=макс(А1:А5).

В

Ячейке А8 получится число 2000.

Определение минимального значения

Мин(диапазон1;

Диапазон2;...)

Группа статистических функций.

В

Текущую ячейку возвращается минимальное

Число из данного диапазона.

Пример

В диапазоне

Ячеек В1:В5 из примера

Определить

Минимальное значение.

Результат

Должен быть получен в ячейке В8.

Пошаговыми

Действиями Мастера

Функций

В

Ячейку В8 следует ввести формулу:

=мин(В1:В5).

В

Ячейке В8 получится число 800.

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

Ранг(адрес

Ячейки; диапазон)

Группа статистических функций.

В

Текущую ячейку возвращается величина,

Соответствующая положению (рангу) числа,

Заданного адресом ячейки, в указанном

Диапазоне.

Пример

В ячейки

D1,D2,D3,D4,D5

Скопируйте информацию из соответствующих

Ячеек столбца А. Для каждой ячейки из

Диапазона D1:D5

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

Результат

Должен быть получен в ячейках E1:E5.

Функция ранга вводится сначала в ячейку

E1,

Затем копируется для всех ячеек до E5.

Пошаговыми

Действиями Мастера

Функций

В

Ячейку E1

Вводим формулу:

=ранг( D

$

D

$1:$

D

$5)

Знак $

Устанавливает абсолютные

Адреса,

Чтобы

Диапазон ячеек не менялся при копировании.

После

Копирования формулы вниз для всех ячеек

До E5

Получим ранги для каждого значения

Диапазона. Ранг числа с максимальным

Значением в диапазоне D1:D5

Будет равен 1, а с минимальным -5.

Функции прогнозирования

Тенденция(известные

Значения_х; известные значения_у; новое

Значение_у) -

Группа

Статистических функций

В

Текущую ячейку возвращается новое

Значение_Х, рассчитанное на основании

Известных значений. Выполняется линейная

Аппроксимация.

Рост(известные

Значения_х; известные значения_у; новое

Значение_у) -

Группа

Статистических функций.

В

Текущую ячейку возвращается новое

Значение_Х, рассчитанное на основании

Известных значений. Выполняется

Экспоненциальная аппроксимация Действие

Функции аналогично функции

Тенденция

Только расчет выполняется в соответствии

С экспоненциальным трендом.

Функции для работы с матрицами

Мобр(массив)

- группа

Математических функций.

Возвращает

В выделенный диапазон обратную матрицу

Для матрицы, хранящейся в массиве.

Массив

- это числовой массив с равным количеством

Строк и столбцов. Массив может быть

Задан как диапазон ячеек, например A1:C3

Или как имя диапазона или массива. Если

Какая-либо из ячеек в массиве пуста или

Содержит текст, а также если массив

Имеет неравное число строк и столбцов,

То функция МОБР возвращает значение

Ошибки #ЗНАЧ!.

Мопр(массив)

Группа

Математических функций.

Возвращает

Определитель матрицы (матрица хранится

В массиве).

Определитель

Матрицы - это число, вычисляемое на

Основе значений элементов массива.

Массив - это числовой массив с равным

Количеством строк и столбцов. Если

Какая-либо ячейка в массиве пуста или

Содержит текст, то функция МОПРЕД

Возвращает значение ошибки #ЗНАЧ!. МОПРЕД

Также возвращает значение ошибки #ЗНАЧ!,

Если массив имеет неравное количество

Строк и столбцов.

Недавно использовавшихся,2)Полный

Алф.перечень,3)Финансовые,4)Дата и

Время,5)Математические,6)Статист,7)Логические,8)Текстовые,9)работа

С базой данных,10)проверка значений

Рубрика: Офисные программы

Похожие статьи