Минимальное значение в excel время. Вычисление минимального, максимального и среднего значения в Microsoft Excel. Определение максимального значения
В Excel имеется функция определения минимального значения. Однако, найти минимальное значение по условию проблематично. Справиться с этой задачей может функция из надстройки =МИНСЕСЛИ
(аналогична стандартной функции Excel СУММЕСЛИ).
В Excel версии 2016 и выше появилась встроенная функция МИНЕСЛИ , можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel .
У функции следующие аргументы =МИНЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ДИАПАЗОН_ПОИСКА ])
- КРИТЕРИЙ - Условие в формате числа, выражения или текста, определяющее проверку минимального значения.
- [ ДИАПАЗОН_ПОИСКА ] - Фактический диапазон для определения минимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН .
ДИАПАЗОН - Диапазон проверяемых ячеек.
Пример 1
В качестве критерия можно указывать значения и логические выражения:
- Рассмотрим следующий пример в котором определяется минимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение «Литература», а в параметр ДИАПАЗОН - список предметов.
- Если в качестве критерия указать логическое выражение «<>Русский» , то определится минимальная оценка по всем предметам за исключением русского языка.
Пример 2
В следующем примере параметр ДИАПАЗОН_ПОИСКА не задан, поэтому минимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН .
В Excel имеется функция определения минимального значения. Однако, найти минимальное значение по условию проблематично. Справиться с этой задачей может функция из надстройки =МИНСЕСЛИ (аналогична стандартной функции Excel СУММЕСЛИ).
В Excel версии 2016 и выше появилась встроенная функция МИНЕСЛИ , можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel .
У функции следующие аргументы =МИНЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ДИАПАЗОН_ПОИСКА ])
- КРИТЕРИЙ - Условие в формате числа, выражения или текста, определяющее проверку минимального значения.
- [ ДИАПАЗОН_ПОИСКА ] - Фактический диапазон для определения минимального значения. Если данный параметр не задан, будет использоваться ячейки, задаваемые параметром ДИАПАЗОН .
ДИАПАЗОН - Диапазон проверяемых ячеек.
Пример 1
В качестве критерия можно указывать значения и логические выражения:
- Рассмотрим следующий пример в котором определяется минимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение "Литература", а в параметр ДИАПАЗОН - список предметов.
- Если в качестве критерия указать логическое выражение "<>Русский" , то определится минимальная оценка по всем предметам за исключением русского языка.
Пример 2
В следующем примере параметр ДИАПАЗОН_ПОИСКА не задан, поэтому минимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН .
Если приходиться работать с большими таблицами определенно найдете в них дублирующийся суммы разбросаны вдоль целого столбца. В тоже время у вас может возникнуть необходимость выбрать данные из таблицы с первым наименьшим числовым значением, которое имеет свои дубликаты. Нужна автоматическая выборка данных по условию. В 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:
Как в Excel выбрать первое минимальное значение кроме нуля:
Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.
Теперь Вас ни что не ограничивает. Один раз разобравшись с принципами действия формул в массиве Вы сможете легко модифицировать их под множество условий и быстро решать много вычислительных задач.
Исходные данные
Исходными данными может быть любой числовой набор данных, таких как горизонтальный или вертикальный диапазон, двумерный массив или даже не смежные диапазоны или отдельно заполненные ячейки. Для удобства я взял одномерный вертикальный массив А1:А5
Для решения этой задачи можно использовать как дополнительный столбец с промежуточной формулой, так и одну формулу массива
1. Решение с дополнительным столбцом
В В1 напишите формулу
ЕСЛИ(A1<>0;A1;"")
и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет "убирать" из исходных данных нули, заменяя их на пустую строку ""
В С1 напишите формулу
Она и вернёт искомый результат - число 3
ЕСЛИ(A1<>0;A1;"")
надо использовать формулу
ЕСЛИ(A1>0;A1;"")
2. Решение формулой массива
Для решения этой задачи используйте такую формулу массива:
МИН(ЕСЛИ(A1:A5<>0;A1:A5))
Как это работает:
Вот эта часть формулы A1:A5<>0
формирует массив {ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА}
, после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: {5:7:ЛОЖЬ:ЛОЖЬ:3}
. Так как функция МИН()
игнорирует текстовые и логические значения, то в итоге получаем искомое - число 3
Если в диапазоне есть отрицательные числа, то вместо формулы
МИН(ЕСЛИ(A1:A5<>0;A1:A5))
надо использовать формулу
МИН(ЕСЛИ(A1:A5>0;A1:A5))
3. Решение простой формулой
Используйте такую формулу:
НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1)
Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ
Если в диапазоне есть отрицательные числа, то вместо формулы
срзнач(диапазон1; диапазон2;...)
В текущую ячейку возвращается среднее значение для чисел указанного диапазона.
Пример 5. В диапазоне ячеек A1:A5 из примера 1 определить среднее значение.
Результат должен быть получен в ячейке А7. Пошаговыми действиями Мастера функций в ячейку А7 следует ввести формулу:=срзнач(А1:А5).
Определение максимального значения
макс(диапазон1; диапазон2;...) - группа статистических функций.
В текущую ячейку возвращается максимальное число из данного диапазона.
Пример 6. В диапазоне ячеек A1:A5 из примера 1 определить максимальное значение.
Результат должен быть получен в ячейке А8.
Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу:
=макс(А1:А5).
В ячейке А8 получится число 2000.
Определение минимального значения
мин(диапазон1; диапазон2;...) - группа статистических функций.
В текущую ячейку возвращается минимальное число из данного диапазона.
Пример 7. В диапазоне ячеек В1:В5 из примера 2 определить минимальное значение.
Результат должен быть получен в ячейке В8.
Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу:
=мин(В1:В5).
В ячейке В8 получится число 800.
Определение ранга числа
ранг(адрес ячейки; диапазон) - группа статистических функций.
В текущую ячейку возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне.
Пример 8. В ячейки D1,D2,D3,D4,D5 скопируйте информацию из соответствующих ячеек столбца А. Для каждой ячейки из диапазона D1:D5 определить ранг числа.
Результат должен быть получен в ячейках E1:E5. Функция ранга вводится сначала в ячейку E1, затем копируется для всех ячеек до E5.
Пошаговыми действиями Мастера функций в ячейку E1 вводим формулу:
=ранг(D 1; $ D $1:$ D $5) - знак $ устанавливает абсолютные адреса, чтобы диапазон ячеек не менялся при копировании.
После копирования формулы вниз для всех ячеек до E5 получим ранги для каждого значения диапазона. Ранг числа с максимальным значением в диапазоне D1:D5 будет равен 1, а с минимальным –5.
Функции прогнозирования
тенденция(известные значения_х; известные значения_у; новое значение_у) - группа статистических функций
В текущую ячейку возвращается новое значение_Х, рассчитанное на основании известных значений. Выполняется линейная аппроксимация.
рост(известные значения_х; известные значения_у; новое значение_у) - группа статистических функций.
В текущую ячейку возвращается новое значение_Х, рассчитанное на основании известных значений. Выполняется экспоненциальная аппроксимация Действие функции аналогично функции тенденция , только расчет выполняется в соответствии с экспоненциальным трендом.
Функции для работы с матрицами
мобр(массив) - группа математических функций.
Возвращает в выделенный диапазон обратную матрицу для матрицы, хранящейся в массиве.
Массив - это числовой массив с равным количеством строк и столбцов. Массив может быть задан как диапазон ячеек, например A1:C3 или как имя диапазона или массива. Если какая-либо из ячеек в массиве пуста или содержит текст, а также если массив имеет неравное число строк и столбцов, то функция МОБР возвращает значение ошибки #ЗНАЧ!.
мопр(массив) - группа математических функций.
Возвращает определитель матрицы (матрица хранится в массиве).
Определитель матрицы - это число, вычисляемое на основе значений элементов массива. Массив - это числовой массив с равным количеством строк и столбцов. Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!. МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.
1)10 недавно использовавшихся,2)Полный алф.перечень,3)Финансовые,4)Дата и время,5)Математические,6)Статист,7)Логические,8)Текстовые,9)работа с базой данных,10)проверка значений
