Поиск максимального значения в столбце в excel

Примеры работы в Excel с функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

Пример 1. В конструкторском отделе предприятия работают 8 инженеров. Необходимо определить четвертую наибольшую и наименьшую зарплаты соответственно.

Внесем данные в таблицу:

Для определения наименьшей 4-й зарплаты в отделе введем следующую формулу в ячейку C2:

Аргументами данной функции являются:

1. B3:B10 – массив значений заработной платы для всех сотрудников;
2. 4 – порядок искомого наименьшего значения в массиве.

Чтобы определить наибольшую 4-ю зарплату вводим формулу в ячейке D2:

Аргументы этой функции соответствуют тем, которые принимала функция НАИМЕНЬШИЙ в рамках данного примера.

Получаем следующие результаты:

То есть, наименьшая и наибольшая четвертые зарплаты в отделе равны 3200 и 4000 денежных единиц соответственно.

Минимальное положительное значение в диапазоне MS EXCEL

​МИНчисло1​ j заносим результат​ прием можно применять​ в итоге получаем​

Один столбец

​8​Бизнес​​a​​ нового листа Excel.​

​ Office 365, убедитесь, что​ форматирование для выделения​Результат​

​Аргументы, содержащие значение ИСТИНА​

​Например, найдем минимальное значение​ оно учтено не​;число2; . )​​ операции if Cells(i,​

​ для получения среднего​ искомое — число​​=ЕСЛИ(A10;A1;»»)​​Бизнес​

Два столбца

​ Чтобы отобразить результаты​ у вас установлена​ строк, удовлетворяющим критериям.​=МИНА(A2:A6)​ интерпретируются как 1,​

​ только среди положительных​

​ будет (см. столбец​Число1, число2. ​ j).Value Находим минимальное​ значения без учёта​3​и скопируйте её​400​b​a​1​a​ формул, выделите их​

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

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

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

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

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

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

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

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

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

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

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

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

Заливка ячеек в зависимости от значения в Microsoft Excel

При работе с таблицами первоочередное значение имеют выводимые в ней значения

Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания

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

Сравнение максимального и минимального значения в Базе Данных Excel

Пример 2. В таблице содержатся данные о сотрудниках, их должности (руководящее звено или штатный сотрудник), зарплатах и дате прима на работу. Найти разницу между минимальной зарплатой руководящего сотрудника и максимальной зарплатой штатного сотрудника, принятых на работу не позже 1.01.2015 года.

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

Создадим две таблицы условий с разными условиями поиска. С помощью функции ДАТА запишем дату 1.01.2015 и установим формат «Общий» для ячейки с результатом, чтобы получить числовое представление даты – 42005. Получим следующее:

Для нахождения разницы зарплат используем следующую формулу:

Полученный результат:

ЕСЛИ

Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.

— Логическое выражение — выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.- Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА- Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.

В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле  ЕСЛИ(B2>500;»Да»;»Нет») первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр («Да») — функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.

Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции

Функция ДМИН Excel

Функция Excel DMIN возвращает наименьшее число в поле записей в списке или базе данных, которое соответствует определенным критериям.

аргументы

• база данных (обязательный): Диапазон ячеек, составляющих базу данных. Первая строка базы данных должна быть заголовками столбцов.
• поле (обязательный): Столбец, из которого вы получите наименьшее число. Его можно ввести как метку столбца или порядковый номер:
  • Метка столбца: Метка столбца (заголовок столбца) должна быть заключена в двойные кавычки, например «количество».
  • Индекс: число, представляющее позицию столбца в базе данных, из которого вы вернете наименьшее число. Например, 1 указывает на первый столбец, 2 указывает на второй столбец, И так далее.
• Критерии (обязательный): Диапазон ячеек, содержащий критерии. Первая строка этого диапазона критериев должна быть заголовками столбцов.

Примечания к функциям

• Вы можете заменить фрагмент «Всего» в формуле с относительным порядковым номером 5 С Всего столбец 5й столбец в базе данных: =ДМИН(B3:F14,5,H3:J5).
• Вы можете использовать любой диапазон для Критерии аргумент, но он должен включать по крайней мере один заголовок столбца и по крайней мере одно значение ячейки под заголовком столбца для указания условия.
• Если в будущем вам потребуется добавить новую информацию в базу данных, не размещайте диапазон критериев под ней. Поскольку Excel не может добавить новую информацию, если строки под базой данных не пусты.
• Заголовки базы данных и диапазоны критериев должны совпадать друг с другом.
• Функция не чувствительна к регистру. Чтобы сделать формулу чувствительной к регистру, вы можете добавить функцию EXACT с помощью ПОИСКПОЗ: =ДМИН(B3:F14,ПОИСКПОЗ(ИСТИНА,ТОЧНОЕ(«Всего»,B3:F3),0),H3:J5).
• Результатом формулы является числовое значение. Чтобы добавить к нему символ валюты, .

Пример

Как показано на рисунке ниже, чтобы получить самую низкую общую цену, соответствующую двум наборам критериев: Пункт = Шайба, Магазин=Б; Товар=ТВ, Магазин=С, Количество>25, скопируйте или введите приведенную ниже формулу в пустую ячейку и нажмите Enter чтобы получить результат:

=ДМИН(B3: F14,»Всего»,H3: J5)

Связанные функции

Функция Excel DEEVARAGE возвращает среднее значение в поле записей в списке или базе данных, которые соответствуют определенным критериям.

Функция Excel DGET возвращает одно значение в поле записей в списке или базе данных, соответствующее определенным критериям.

Функция Excel DMAX возвращает наибольшее число записей в поле списка или базы данных, соответствующих определенным критериям.

Функция Excel ДПРОИЗВ умножает значения в поле записей в списке или базе данных, которые соответствуют определенным критериям.

Функция Excel DSUM возвращает сумму значений в поле записей в списке или базе данных, которые соответствуют определенным критериям.

МИНА (функция МИНА)

​ так и одну​1​10​​Бизнес​​a​

Синтаксис

​ Эта функция недоступна в​

​ минимальное положительное значение​0,5​

​ только значения массива​​ помощью формул массива​ это значение (в​ числовые значения в​ ячеек, пишем в​ содержащих отрицательные числа​. Так как функция​

Замечания

​ формулу массива​a​b​8​Техника​Оценка​Скопируйте данные примеров из​

​ Excel 2016 при отсутствии​ можно найти с​0,4​ или ссылки. Пустые​ можно получить формулу​ нашем случае она​ текстовом формате, значения​

​ переменную mx​Область применения: Любая​МИН()​1. Решение с​Техника​1​

​1​100​10​ приведенных ниже таблиц​ подписки на Office 365.​

​ помощью формулы массива:​0,8​ ячейки и текст​ для нахождения минимального​

​ вернет 1). Если​ ошибки, пустые ячейки.​Создаём массив, куда​ версия Excel​игнорирует текстовые и​ дополнительным столбцом​

Пример

​100​a​a​100​b​ и вставьте их​ Если у вас​=МИН(ЕСЛИ((A6:A15=$D$6)*(B6:B15>0);B6:B15))​Формула​ в массиве или​ с учетом условия​ логическое значение введено​Синтаксис​

​ в цикле по​

​Комментарий: Этот же​

​ логические значения, то​

​ через ссылку, то​

Недостаток функции

В случае, когда ни одна строка не удовлетворяет условиям, то функция ДМИН() вернет 0. Это может ввести в заблуждение, т.к. не понятно: есть ли на самом деле строки, удовлетворяющие критерию и минимальное значение =0 или в исходной таблице отстутствуют строки, удоблетворяющие критерию.

Как показано на рисунке выше, Параметр3 вообще отстутствует в исходной таблице, но «минимальным» значением является 0!

Лучше использовать альтернативную формулу =НАИМЕНЬШИЙ(ЕСЛИ(A6:A15=D6;B6:B15;»»);1) , которая возвращает ошибку #ЧИСЛО! в случае, если ни одна строка не удовлетворяет критерию.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задачи

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В файле примера приведено решение и других задач.

Синтаксис

Аргументы функции ДМИН описаны ниже.

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

Поле — обязательный аргумент. Столбец, используемый функцией. Введите текст с заголовком столбца в двойных кавычках, например «Возраст» или «Урожай», или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д.

Условия — обязательный аргумент. Диапазон ячеек, который содержит задаваемые условия. В качестве аргумента «условия» можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.

СУММЕСЛИМН

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

— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные- Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию- Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно. Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва

Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город

Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.

Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».

Формула функций НАИБОЛЬШИЙ с массивом и СУММ

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

Внесем данные о зарплате сотрудников в таблицу:

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

Аргументами функции СУММ являются значения, которые вернет функция НАИБОЛЬШИЙ. Последняя принимает следующие аргументы:

1. B3:B10 – массив, хранящий данные о зарплатах всех работников фирмы;
2. – интервал, соответствующий первому и второму искомым величинам.

Примечание: – вариант записи массивов в Excel. С помощью этой записи было указано о необходимости вернуть первые два наибольших значения из массива B3:B10. Полученные значения будут просуммированы функцией СУММ.

В результате получим сумму зарплат директора и заместителя:

Теперь определим общую сумму зарплат оставшихся работников используя функцию СУММ в ячейке D2.

Визуально видно, что сотрудник оказался прав. Однако используем функционал Excel для отображения результата решения задачи в ячейке D6:

=ЕСЛИ(C3>D3;»Сотрудник оказался прав»;»Сотрудник неправ»)

Функция ЕСЛИ принимает следующие аргументы:

1. C3>D3 – логическое выражение, в котором C3 – суммарная з/п руководителей, D3 – суммарная з/п остальных сотрудников;
2. «Сотрудник оказался прав» – текст, который будет отображен в случае, если C3>D3 – истина;
3. «Сотрудник неправ» – текст, который отобразится в случае, если C3>D3 – ложь.

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

Сравнение максимального и минимального значения в Базе Данных Excel

Пример 2. В таблице содержатся данные о сотрудниках, их должности (руководящее звено или штатный сотрудник), зарплатах и дате прима на работу. Найти разницу между минимальной зарплатой руководящего сотрудника и максимальной зарплатой штатного сотрудника, принятых на работу не позже 1.01.2015 года.

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

Создадим две таблицы условий с разными условиями поиска. С помощью функции ДАТА запишем дату 1.01.2015 и установим формат «Общий» для ячейки с результатом, чтобы получить числовое представление даты – 42005. Получим следующее:

Для нахождения разницы зарплат используем следующую формулу: