Как найти наибольшую сумму баллов в excel - Исправление недочетов и поиск решений вместе с Examum.ru

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Если ячейки находятся в соединимой строке или столбце

Вы можете выбрать ячейку снизу или справа от чисел, для которых нужно найти наименьшее число.
На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой , выберите min (вычисляет наименьшее) или Max (вычисляет наибольшее) и нажмите клавишу ВВОД.

Если ячейки не находятся в подрядной строке или столбце

Для этого используйте функции МИН, МАКС, МАЛЫЙ или БОЛЬШОЙ.

Пример

Скопируйте следующие данные на пустой лист.


1
2
3
4
5
6
7

A
Данные
10
7
9
27
0
4
Формула	Описание (результат)
=МИН(A2:A7)	Наименьшее число в диапазоне (0)
=МАКС(A2:A7)	Наибольшее число в диапазоне (27)
=МАЛЫЙ(A2:A7; 2)	Второе наименьшее число в диапазоне (4)
=БОЛЬШОЙ(A2:A7;3)	Третье по величине число в диапазоне (9)

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

НАИБОЛЬШИЙ

MAX

MIN

НАИМЕНЬШИЙ

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

Автор: Лапина Надежда Александровна

Организация: МАОУ «СОШ № 1»

Населенный пункт: Свердловская область, г. Артёмовский

В электронную таблицу занесли результаты тестирования учащихся по математике и информатике. Вот первые строки получившейся таблицы:

В столбце А указан номер ученика; в столбце В – номер школы учащегося; в столбцах С, D – баллы, полученные, соответственно, по математике и информатике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 500 учащимся. Порядок записей в таблице произвольный.

Откройте файл с данной электронной таблицей (Результаты тестирования по математике и информатике). На основании данных, содержащихся в этой таблице, ответьте на вопросы:

1. Определите суммарный бал каждого ученика по двум предметам. Результат разместите в столбце Е таблицы.

2. Определите максимальный балл по математике. Ответ на этот вопрос запишите в ячейку J2 таблицы.

3. Определите минимальный балл по информатике. Ответ на этот вопрос запишите в ячейку J3 таблицы.

4. Определите средний балл по математике. Ответ на этот вопрос с точностью до двух знаков после запятой запишите в ячейку J4 таблицы.

5. Определите, сколько учеников школы № 5 принимало участие в тестировании. Ответ на этот вопрос запишите в ячейку J5 таблицы.

6. Определите максимальный балл по двум предметам. Ответ на этот вопрос запишите в ячейку J6 таблицы.

7. Определите средний балл по двум предметам. Ответ на этот вопрос с точностью до двух знаков после запятой запишите в ячейку J7 таблицы.

8. Определите, сколько учеников набрали больше 80 баллов по информатике. Ответ на этот вопрос запишите в ячейку J8 таблицы.

9. Определите, сколько учеников набрали меньше 50 баллов по математике. Ответ на этот вопрос запишите в ячейку J9 таблицы.

10. Определите, сколько учеников набрали больше 80 баллов по математике и больше 70 баллов по информатике. Ответ на этот вопрос запишите в ячейку J10 таблицы.

11. Чему равна наибольшая сумма баллов по двум предметам среди учащихся школы № 3? Ответ на этот вопрос запишите в ячейку J11 таблицы.

12. Сколько учащихся школы № 1 набрали по математике больше баллов, чем по информатике? Ответ запишите в ячейку J12 таблицы.

13. Чему равна наименьшая сумма баллов по двум предметам среди школьников, получивших больше 60 баллов по математике или информатике? Ответ на этот вопрос запишите в ячейку J13 таблицы.

14. Чему равна средняя сумма баллов по двум предметам среди учащихся школы № 8? Ответ с точностью до одного знака после запятой запишите в ячейку J14 таблицы.

15. Чему равна наибольшая сумма баллов по двум предметам среди учащихся школы № 1? Ответ на этот вопрос запишите в ячейку J15 таблицы.

16. Сколько процентов от общего числа участников составили ученики, получившие по математике больше 55 баллов? Ответ с точностью до одного знака после запятой запишите в ячейку J16 таблицы.

17. Сколько процентов от общего числа участников составили ученики школы № 7? Ответ с точностью до одного знака после запятой запишите в ячейку J17 таблицы.

18. Сколько процентов от общего числа участников составили ученики, получившие по информатике не менее 75 баллов? Ответ с точностью до одного знака после запятой запишите в ячейку J18 таблицы.

19. Постройте круговую диаграмму, отображающую соотношение учеников из школ «3», «6» и «9». Левый верхний угол диаграммы разместите вблизи ячейки О2. В поле диаграммы должны присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма.

20. Постройте круговую диаграмму, отображающую соотношение учеников, набравших по информатике меньше 50 баллов, от 50 до 75 баллов, 75 и более баллов. Левый верхний угол диаграммы разместите вблизи ячейки О18. В поле диаграммы должны присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма.

ПРАКТИЧЕСКАЯ РАБОТА. ОРГАНИЗАЦИЯ ВЫЧИСЛЕНИЙ
В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

(ОТВЕТЫ И ПОЯСНЕНИЯ)

1. Определите суммарный бал каждого ученика по двум предметам. Результат разместите в столбце Е таблицы.

В ячейку Е2 запишем формулу: =СУММ(C2;D2)

Скопируем формулу во все ячейки диапазона Е2:Е501.

2. Определите максимальный балл по математике. Ответ на этот вопрос запишите в ячейку J2 таблицы.

В ячейку J2 запишем формулу: =МАКС(C2:C501)

Ответ: 100

3. Определите минимальный балл по информатике. Ответ на этот вопрос запишите в ячейку J3 таблицы.

В ячейку J3 запишем формулу: =МИН(D2:D501)

Ответ: 10

В ячейку J4 запишем формулу: =СРЗНАЧ(C2:C501)

Ответ: 55,19

В ячейку J5 запишем формулу: =СЧЁТЕСЛИ(B2:B501;»5″)

Ответ: 60

6. Определите максимальный балл по двум предметам. Ответ на этот вопрос запишите в ячейку J6 таблицы.

В ячейку J6 запишем формулу: =МАКС(E2:E50)

Ответ: 198

В ячейку J7 запишем формулу: =СРЗНАЧ(E2:E501)

Ответ: 109,84

В ячейку J8 запишем формулу: =СЧЁТЕСЛИ(D2:D501;»>80″)

Ответ: 102

В ячейку J9 запишем формулу: =СЧЁТЕСЛИ(C2:C501;»<50″)

Ответ: 220

В ячейку J10 запишем формулу:

Ответ: 34

В ячейку F2 запишем формулу: =ЕСЛИ(B2=3;E2;» «)

Скопируем формулу во все ячейки диапазона F3:F501.

В ячейку J11 запишем формулу: =МАКС(F2:F501)

Ответ: 185

В ячейку G2 запишем формулу: =ЕСЛИ(И(B2=1;C2>D2);1;0)

Скопируем формулу во все ячейки диапазона G3:G501.

В ячейку J12 запишем формулу: =СУММ(G2:G501)

Ответ: 35

В ячейку H2 запишем формулу: =ЕСЛИ(ИЛИ(C2>60;D2>60);E2;» «)

Скопируем формулу во все ячейки диапазона H2:H501.

В ячейку J13 запишем формулу: =МИН(H2:H501)

Ответ: 75

В ячейку J14 запишем формулу: =СРЗНАЧЕСЛИ(B2:B501;»8″;E2:E501)

Ответ: 108,1

В ячейку К2 запишем формулу: =ЕСЛИ(B2=1;E2;» «)

Скопируем формулу во все ячейки диапазона K3:K501.

В ячейку J15 запишем формулу: =МАКС(K2:K501)

Ответ: 183

В ячейку J16 запишем формулу: =СЧЁТЕСЛИ(C2:C501;»>55″)/500*100

Ответ: 49

В ячейку J17 запишем формулу: =СЧЁТЕСЛИ(B2:B501;»7″)/500*100

Ответ: 11,8

В ячейку J18 запишем формулу: =СЧЁТЕСЛИ(D2:D501;»>=75″)/500*100

Ответ: 27,4

Школа №3: =СЧЁТЕСЛИ(B2:B501;»3″) = 61

Школа №6: =СЧЁТЕСЛИ(B2:B501;»6″) = 50

Школа №9: =СЧЁТЕСЛИ(B2:B501;»9″) = 55

Выделяем ячейки L2:M4. Выбираем Вставка → Круговая диаграмма.

Формулы количества и суммы в Excel

Подсчитывает количество ячеек, содержащих числа.
Учитывает аргументы, являющиеся числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, такое как «1»).

Синтаксис: СЧЁТ(значение1;[значение2];…)
Пример формулы: =СЧЁТ(A1:A20)

СЧЁТЗ

Учитывает данные любого типа, включая значения ошибок и пустой текст ( «»). Например, если в диапазоне есть формула, которая возвращает пустую строку, функция СЧЁТЗ учитывает это значение. Функция СЧЁТЗ не учитывает пустые ячейки.

Синтаксис: СЧЁТЗ(значение1;[значение2];…)
Пример формулы: =СЧЁТЗ(A2:A7)

СЧИТАТЬПУСТОТЫ

Подсчитывает пустые ячейки в указанном выше диапазоне.

Синтаксис: СЧИТАТЬПУСТОТЫ (значение1;[значение2];…)
Пример формулы: =СЧИТАТЬПУСТОТЫ(A2:A7)

СЧЁТЕСЛИ

Подсчитывает количество ячеек, отвечающих определенному условию (например, число поставщиков из определенного города). Функция СЧЁТЕСЛИ не учитывает регистр символов.

Синтаксис: =СЧЁТЕСЛИ(где нужно искать; что нужно найти)
Пример формулы: =СЧЁТЕСЛИ(A2:A5;»Лондон»); =СЧЁТЕСЛИ(A2:A5;A4)

СЧЁТЕСЛИМН

Подсчитывает количество ячеек, которые удовлетворяют нескольким условиям. Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

Синтаксис: =СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
Пример формулы: =СЧЁТЕСЛИМН(B2:B5,»=Да»,F2:F5,»>1″)

Формулы суммы Excel

СУММ

Суммирует значения. Вы можете складывать отдельные значения и диапазоны ячеек.

Пример формулы: =СУММ(A2:A10), =СУММ(A2:A10;C2:C10)

СУММЕСЛИ

Суммирует значения, которые соответствуют указанному условию. Можно задать условие для текущего диапазона, а просуммировать соответствующие значения из другого диапазона.
Например, формула =СУММЕСЛИ(B3:B9; «Яблоки»; C3:C9) суммирует только те значения из диапазона C3:C9, для которых соответствующие значения из диапазона B2:B5 равны «Яблоки».

Синтаксис: = СУММЕСЛИ(диапазон; условие;[диапазон_суммирования])
Пример формулы: =СУММЕСЛИ(B2:B25;»> 5″), =СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)

СУММЕСЛИМН

Суммирует все значения, которые удовлетворяют нескольким условиям. Например, с помощью функции СУММЕСЛИМН можно найти число всех поставщиков, (1) находящихся в определенном городе, (2)которые продают определенный товар.

Синтаксис: СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
Пример формулы: =СУММЕСЛИМН(B2:B9; C2:C9; «Рязань»; E2:E9; «Бананы»)

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

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

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

4. Чтобы на имеющуюся сумму закупить как можно больше конфет, следует закупать самые дешевые конфеты. Узнайте цену конфет за килограмм. Для этого:
1) ячейки G3 введите формулу ^В3/С3*1000;
2) скопируйте эту формулу в ячейки диапазона G4:G7.

Выборочные вычисления по одному или нескольким критериям

Имеем таблицу по продажам, например, следующего вида:

Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

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

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

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

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

Как вычислить минимальное значение в excel

Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

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

Какую функцию надо использовать в Excel?

В столбце А указаны фамилия и имя учащегося; в столбце В — округ учащегося; в столбцах С, D — баллы, полученные, соответственно, по физике и информатике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 266 учащимся. Порядок записей в таблице произвольный.

Чему равна наименьшая сумма баллов по двум предметам среди учащихся округа «Центральный»? Ответ на этот вопрос запишите в ячейку G1 таблицы

Источник

Функция МАКС в Excel: как найти максимальное значение

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

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

Функция МАКС — основные сведения
Найти максимальное в группе чисел
Вычисляем максимум в несмежных диапазонах
Как получить максимальную (последнюю) дату
Максимальное значение с условиями
Наибольшее число без учёта нулей
Как найти максимальное число игнорируя ошибки
Определяем абсолютное максимальное значение
Максимум применительно к тексту
Вычисление наибольшей разницы
Выводим адрес ячейки с максимальным значением
Как выделить цветом самое большое число
Почему не получается?

Функция МАКС

Функция МАКС в Excel возвращает наибольшее значение в указанном вами наборе данных.

Синтаксис у неё следующий:

МАКС(число1; [число2];…)

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

Число1 является обязательным, число2 и последующие аргументы необязательны.

Чтобы создать самую простую формулу, чтобы выбрать максимальное значение в таблице Excel, вы можете вводить числа непосредственно в список аргументов, например:

=МАКС(1; 2; 3)

На практике это – очень редкий случай. По большей части вы будете иметь дело с диапазонами и ячейками.

Самый быстрый способ найти наибольшее значение в диапазоне, – это:

В ячейке введите = МАКС(
Выберите диапазон чисел с помощью мыши.
Введите закрывающую скобку.
Жмём Enter для завершения.

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

=МАКС(A1:A6)

Если ваши числа находятся в непрерывном диапазоне (как в этом примере), вы можете заставить Excel автоматически создать формулу МАКС. Вот как:

Выделите ячейки со своими числами.
На вкладке «Главная» в группе «Форматы» нажмите «Автосумма» и выберите «Максимум» из раскрывающегося списка.

5 фактов, которые нужно знать о функции МАКС –

В текущих версиях Excel она может обрабатывать до 255 аргументов.
Если аргументы не содержат ни одного числа, возвращается ноль.
Если аргументы содержат одно или несколько значений ошибки, возвращается ошибка.
Пустые ячейки игнорируются.
Обрабатываются логические значения и текстовые представления чисел, предоставленные непосредственно в списке аргументов (ИСТИНА приравнивается к 1, ЛОЖЬ – к 0). Если используется ссылка на ячейку с логическим или текстовым значением, то они игнорируются.

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

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

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

Как выбрать максимальное значение в группе чисел.

Чтобы в Excel извлечь наибольшее число в группе чисел, передайте эту группу функции МАКС в качестве ссылки на диапазон. Диапазон может содержать любое количество строк и столбцов. Например, чтобы получить максимальное значение из C2: E7, используйте эту простую формулу:

=МАКС(C2:E7)

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

Применим функцию:

= МАКС(B2:B10)

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

Наибольшее значение в несмежных ячейках или диапазонах Excel

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

Начните вводить функцию МАКС в ячейку.
После того, как вы ввели открывающую скобку, нажмите и удерживайте Ctrl и выберите мышкой нужные ячейки и диапазоны.
Отпустите Ctrl и введите закрывающую круглую скобку.
Нажмите Enter.

Вы получите формулу максимального числа в Excel, подобную этому:

=МАКС(C2:E2;C7:E7)

Как получить наибольшую (последнюю) дату в Excel

Во внутренней системе Excel даты – это не что иное, как порядковые номера дней, поэтому функция МАКС обрабатывает их без проблем.

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

=МАКС(C2:C7)

Поскольку дата – это также число, всё работает замечательно.

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

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

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

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

МАКС + ЕСЛИ

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

=МАКС(ЕСЛИ(B2:B15=F1; C2:C15))

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

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

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

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

=СУММПРОИЗВ(МАКС((B2:B15=F1)*(C2:C15)))

МАКСЕСЛИ

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

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

=МАКСЕСЛИ(C2:C15;B2:B15;F1)

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

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

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

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

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

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

=МАКС(ЕСЛИ(C2:C7<>0; C2:C7))

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

=МАКСЕСЛИ(C2:C7;C2:C7;»<>0″)

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

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

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

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

=МАКС(ЕСЛИ(ЕОШИБКА(A1:B6); «»; A1:B6))

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

=МАКС(ЕСЛИОШИБКА(A1:B6; «»))

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

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

=МАКСЕСЛИ(A1:B6;A1:B6;»>=0″)

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

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

=АГРЕГАТ(4;6;A1:B6)

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

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

Как найти абсолютное наибольшее значение в Excel

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

Первая идея, которая приходит в голову, — получить абсолютные значения всех чисел в диапазоне с помощью функции ABS и передать их в МАКС:

{= МАКС(ABS( диапазон ))}

Это формула массива, поэтому не забудьте подтвердить ее с помощью Ctrl + Shift + Enter. Еще одно предостережение: он работает только с числами и приводит к ошибке в случае нечисловых данных.

Не нравится это решение? Тогда давайте построим что-нибудь более жизнеспособное

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

С исходными данными в A1: B5 расчёты выглядят следующим образом.

Формула массива (заполняется с помощью Ctrl + Shift + Enter):

=МАКС(ABS(A1:B5))

Обычная формула (заполняется клавишей Enter):

=МАКС(МАКС(A1:B5); -МИН(A1:B5))

или же

=МАКС(МАКС(A1:B5); ABS(МИН(A1:B5)))

На скриншоте ниже показаны результаты:

Вернуть максимальное абсолютное значение с сохранением знака

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

Предполагая, что числа находятся в ячейках A1: B5, вот выражение, которое следует использовать:

=ЕСЛИ(ABS(МАКС(A1:B5))>ABS(МИН(A1:B5)); МАКС(A1:B5);МИН(A1:B5))

Сложная на первый взгляд логика довольно проста. Сначала вы находите наибольшее и наименьшее числа в диапазоне и сравниваете их абсолютные значения. Если абсолютное (без учёта знака) максимальное значение больше абсолютного минимального значения, возвращается это наиболшее число, иначе – наименьшее число.

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

Максимальное значение применительно к тексту.

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

Здесь мы должны применить функцию ДЛСТР для вычисления длины имени.

=ДЛСТР(А2)

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

=МАКС(B2;B13)

А можно обойтись без дополнительного столбца и вычислить максимальную длину текстового значения в одной ячейке. Для этого нужно объединить эти формулы, вложив ДЛСТР в МАКС:

=МАКС(ДЛСТР(A2:A13))

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

Вычислить наибольшую разницу в сумме.

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

Ниже представлена таблица Excel с небольшой выборкой данных за 4 месяца.

Если решать проблему «в лоб», то можно использовать функцию ЕСЛИ со множеством условий:

=ЕСЛИ(И((МАКС(B2:C2)-МИН(B2:C2))>(МАКС(C2:D2)-МИН(C2:D2)), (МАКС(B2:C2)-МИН(B2:C2))>(МАКС(D2:E2)-МИН(D2:E2))), МАКС(B2:C2)-МИН(B2:C2), ЕСЛИ((МАКС(C2:D2)-МИН(C2:D2))>(МАКС(D2:E2)-МИН(D2:E2)), МАКС(C2:D2)-МИН(C2:D2),МАКС(D2:E2)-МИН(D2:E2)))

Но при помощи функции МАКС можно значительно упростить расчет и не пользоваться такой длинной и сложной конструкцией.

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

=МАКС(ABS(B2-C2);ABS(C2-D2);ABS(D2-E2))

Как вы можете видеть в таблице ниже, этот вариант намного проще и возвращает те же результаты.

Как найти адрес ячейки Excel с максимальным числом.

Есть ли решение, которое сообщит адрес ячейки с наибольшим числом, а не само число? То есть, если A4 равно 9 и это значение является самым большим, как мне составить формулу, которая возвратит координаты A4 вместо 9?

Вы можете использовать функции ЯЧЕЙКА , ИНДЕКС и ПОИСКПОЗ в сочетании с функцией МАКС, чтобы вернуть адрес ячейки с наибольшим значением.

Например, если вы хотите найти ячейку с наивысшим значением в столбце от А1 до А7, вы можете использовать следующий расчёт:

=ЯЧЕЙКА(«адрес»;ИНДЕКС(A1:A7;ПОИСКПОЗ(МАКС(A1:A7);A1:A7;0)))

Получаем результат в виде абсолютной ссылки $А$4, которая указывает на самое большое число из этого диапазона.

Однако учтите, что это выражение работает только с одним столбцом.

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

Формула массива –

{=АДРЕС(ПОИСКПОЗ(МАКС(F1:F7);F1:F7;0);СТОЛБЕЦ(F1:F7);4)}

Обычная формула, в которой явным образом нужно указать столбец:

=»F»&ПОИСКПОЗ(МАКС(F1:F7);F1:F7;0)

Ещё одна формула массива –

{=ЯЧЕЙКА(«address»;ИНДЕКС(F2:F7;ПОИСКПОЗ(МАКС(F2:F7);F2:F7;0)))}

На скриншоте вы видите, что все они возвращают один и тот же адрес:

И ещё одна формула массива для вывода адреса, которая работает с диапазоном из нескольких строк и столбцов:

{=АДРЕС(МАКС(ЕСЛИ((C2:E7=МАКС(C2:E7));СТРОКА(C2:E7))); МАКС(ЕСЛИ((C2:E7=МАКС(C2:E7));СТОЛБЕЦ(C2:E7))))}

В данном случае, как видите, мы ищем адрес наибольшего числа в двумерном диапазоне C2:E7.

Напомню, что формула массива вводится с использованием комбинации клавиш Ctrl + Shift + Enter.

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

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

Выделите максимальное число в диапазоне Excel

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

Выберите диапазон чисел (в нашем случае C2: E8).
На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование»> «Новое правило».
В диалоговом окне «Новое правило форматирования» выберите «Форматировать только первые или последние значения».
На нижней панели выберите «первым» из раскрывающегося списка и введите 1 в поле рядом с ним (это означает, что вы хотите выделить только одну ячейку, содержащую наибольшее значение).
Нажмите кнопку «Формат» и выберите стиль оформления.
Дважды щелкните OK, чтобы закрыть оба окна.

Как выделить цветом максимальное число в каждой строке

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

Выберите все строки, которые вы хотите проверить (C3:E8).
Кликните Новое правило > Использовать формулу.
В поле Форматировать значения, запишите:

=C3=МАКС($C3:$E3)

Где C3 — крайняя верхняя левая ячейка, а $C3:$E3 — координаты первой строки. Чтобы правило работало, обязательно зафиксируйте координаты столбца в диапазоне знаком $.

Нажмите кнопку «Формат» и выберите нужный формат.
Дважды щелкните ОК.

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

=C2=МАКС(C$2:C$7)

Дополнительные сведения см. в статье « Как создать правило условного форматирования на основе формул» .

Почему не получается найти наибольшее число?

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

МАКС возвращает ноль

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

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

Вы можете проверить это с помощью функции ЕЧИСЛО, например:

=ЕЧИСЛО(A1)

Если приведенная выше формула возвращает ЛОЖЬ, то значение в A1 не является числовым. Это означает, что вы должны устранять неполадки с исходными данными, а не с формулой.

МАКС возвращает значение #Н/Д, #ЗНАЧ или другую ошибку.

Пожалуйста, внимательно проверьте указанные ячейки. Если какая-либо из указанных ячеек содержит ошибку, поиск максимума приведет к той же ошибке. Чтобы обойти это, посмотрите, как получить максимальное значение, игнорируя все ошибки .

Вот как можно найти максимальное значение в Excel. Благодарю вас за чтение!

Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального…

Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…

проверка правильности ввода Excel при помощи функции ЕСЛИ

Как проверить правильность ввода данных в Excel? — Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить…

Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ…

Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.…

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

Источник

Как в офисе.

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

Для примера рассмотрим следующую таблицу. В ней указано имя и пол ученика, в каком классе учится и средний бал. Используя функцию СУММЕСЛИ, решим такую задачу: посчитаем сумму значений из столбца «Средний бал» для всех мальчиков и для всех девочек. Задача для функции СУММЕСЛИМН такая: посчитать сумму среднего бала для мальчиков и девочек, которые учатся в 8 и 9 классе отдельно.

Рассмотрим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(диапазон; критерий; [диапазон суммирования])

Диапазон — здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это Диапазон D2:D26 .

Критерий — здесь нужно выбрать одно значение из ранее указанного Диапазона. Сначала будем указывать мальчиков, затем девочек.

Диапазон суммирования — это столбец, значения в котором нужно просуммировать, если они относятся к выбранному Критерию. Например, если для выбранного Диапазона D2:D26 указан Критерий «мальчик» , значит в столбце «Средний бал» : С2:С26 , будут суммироваться все значения, которые относятся к мальчикам.

Пример функции СУММЕСЛИ.

Выделяем ячейку G4 и ставим «=» . Пишем в ней СУММЕСЛИ и открываем скобку «(» . В качестве Диапазона, из которого будут выбираться значения, выделяем D2:D26 . В качестве Критерия, нужно выбрать одно из значений в ранее указанном Диапазоне. У нас это «мальчик» , поэтому выделяем ячейку D3 . Диапазон суммирования — это столбец, в котором указан «Средний бал» . Выделяем ячейки С2:С26 . Между аргументами ставьте «;» , в конце, закройте скобку «)» . Нажмите «Enter» .

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

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

Выделяем ячейку G4 и в «Строке формул» после D2 , D26 , C2 , C26 нажимаем клавишу «F4» . В адресах на ячейки, появятся знаки «$» . Таким образом, мы делаем ссылки абсолютными. Чтобы растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при этом вид на черный плюсик.

Если Вам интересно, как сделать ссылки в Эксель: абсолютные, относительные, на другой лист или книгу, прочтите статью по этой теме.

Дальше смотрим на «Строку формул» . В качестве аргументов для Диапазона и Диапазона суммирования берутся правильные значения: D2:D26 , C2:C26 . В качестве Критерия укажите адрес нужной ячейки. Поскольку, нужно посчитать средний балл для всех девочек, в столбце D выбираем адрес любой ячейки, в которой указано «девочка» : D4 .

Теперь рассмотрим аргументы для функции СУММЕСЛИМН в Excel.

=СУММЕСЛИМН(диапазон суммирования; диапазон условия1; условие1; диапазон условия2; условие2)

Диапазон суммирования — это столбец, значения из которого будут выбираться, если они соответствуют одному и второму заданному условию. В примере, это С2:С26 .

Диапазон условия1 — это первый диапазон для сравнения. Нужно выбрать пол ученика, поэтому диапазон D2:D26 .

Условие1 — нужно указать то значение, которое будет выбираться из Диапазона условий1. Нас интересуют мальчики, поэтому укажем ячейку D3 .

Диапазон условия2 — второй диапазон сравнения. Здесь будем выбирать класс, в котором учится ребенок. Соответственно укажем диапазон В2:В26 .

Условие2 — конкретное значение, которое нас интересует из Диапазона условий2. Сначала нужно будет выделить ячейку, в которой указан «8 класс» , затем «9 класс» .

Пример функции СУММЕСЛИМН.

Решать будем вторую задачу. Выделяем ячейку Н8 , ставим «=» , пишем СУММЕСЛИМН и открываем скобку «(» . В качестве Диапазона суммирования указываем столбец со «Средним балом» : С2:С26 . Диапазон условия1 — это пол ученика: D2:D26 , Условие1 — это мальчики: ячейка D3 . Диапазон условия2 — это класс: В2:В26 , Условие2 — 8 класс: ячейка В3 . Закройте скобку, нажмите «Enter» .

В «Строке формул» сразу сделайте абсолютные ссылки на столбцы: Класс, Средний бал, Мал/Дев.

Растягиваем формулу на ячейки Н8:Н11 . Теперь укажем правильные ячейки в качестве Условия1 и Условия2.

В ячейке Н9 считается средний балл для мальчиков, которые учатся в 9 классе. Поэтому, Условие1 для первого диапазона «мальчик» : D3 , Условие2 для второго диапазона «9» : В6 .

В ячейке Н10 считаем средний балл девочек, которые учатся в 8 классе. Условие1 — «девочка» , D4 , Условие2 — «8» , В3 .

В ячейке Н11 считаем средний балл девочек, которые учатся в 9 классе. Условие1 — «девочка» , D4 , Условие2 — «9» , В2 .

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

Давайте теперь проверим, чтобы результат был правильным. Посчитаем сумму «Среднего бала» для всех значений в исходной таблице, для Задачи 1 и для Задачи 2. Сумма должна быть одинаковой.

Чтобы подробно узнать, как посчитать сумму в Эксель, перейдите по ссылке и прочтите статью.

Вот на таких несложных примерах мы разобрались, как работает функция СУММЕСЛИ и функция СУММЕСЛИМН в Эксель.

Поделитесь статьёй с друзьями:

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

A	B	C	D
1	Ученик	Район	Математика	Физика
2	Шамшин Владислав	Майский	65	79
3	Гришин Борис	Заречный	52	30
4	Огородников Николай	Подгорный	60	27
5	Богданов Виктор	Центральный	98	86

В столбце A указаны фамилия и имя учащегося; в столбце B — район города, в котором расположена школа учащегося; в столбцах C , D — баллы, полученные по математике и физике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 1000 учащимся. Порядок записей в таблице произвольный.

Выполните задание

Откройте файл с данной электронной таблицей . На основании данных, содержащихся в этой таблице, ответьте на два вопроса:

1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся Майского района? Ответ на этот вопрос запишите в ячейку G1 таблицы.

2. Сколько процентов от общего числа участников составили ученики Майского района? Ответ с точностью до одного знака после запятой запишите в ячейку G2 таблицы.

3. Постройте круговую диаграмму, отображающую соотношение количества участников из Майского, Кировского и Центрального районов. Левый верхний угол диаграммы разместите вблизи ячейки G6.

1. В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это ученик Майского района. Для ученика другого района ячейка будет содержать пустую строку. В ячейку E2 запишем формулу =ЕСЛИ(B2=»Майский»;C2+D2;»») Скопируем формулу во все ячейки диапазона Е3:Е1001. Для того чтобы найти наибольшую сумму, в ячейку G1 внесём формулу =MАКС(E2:E1001). Получаем: 194.

2. Для ответа на второй вопрос в ячейке H3, найдём количество учеников Майского района, принимавших участие

в тестировании, с помощью формулы: =СЧЁТЕСЛИ(B2:B1001;»Майский»). Выразим полученное значение в процентах от общего числа участников тестирования. Результат запишем в ячейку G2: =H3/1000*100. Получаем: 39,1.

3. В ячейку G2 вставим формулу =СЧЁТЕСЛИ(B2:B1001; «Майский»), в ячейку G3 вставим формулу =СЧЁТЕСЛИ(B2:B1001; «Кировский»), в ячейку G4 вставим формулу =СЧЁТЕСЛИ(B2:B1001; «Центральный»). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.

Источник

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

Способ №1;
Способ №2;
С условиями.

Видеоинструкция

Формула найти наибольшее в Excel

Выполнить данную задачу можно при помощи формулы:

=НАИБОЛЬШИЙ(Диапазон;аргумент)

Где

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

Способ №2

Следует указать нужную ячейку и кликнуть на знак функция (аналогично можно выполнить поиск наименьшего).

В появившемся окне выбираем в категории «Полный алфавитный перечень» и выбираем «НАИБОЛЬШИЙ».

Далее, как и в первом случае, выбираем массив и аргумент.

Готово, нажав на «Ок» данные появятся в ячейке.

Найти сумму двух наибольших чисел в Эксель

Решить задачу можно при помощи простой формулы:

=СУММ(НАИБОЛЬШИЙ(B2:B5;{1;2}))

В этом случае аргумент указан через {} и разделитель ; (точка с запятой).

Готово система самостоятельно посчитала результат.

Источник