Как найти наименьшую сумму баллов в excel

Сумма n наименьших и наибольших значений в Excel

Если нужно суммировать наименьшие значения в диапазоне. Rng в сумме — диапазон с нужными числами, а — массив количества наименьших, которые нужно суммировать.

Формула в примере:

НАИМЕНЬШИЙ в этом случае посчитает заданное количество наименьших значений, а СУММПРОИЗВ правильно просуммирует массив. Можно использовать и обычную СУММ, если ввести формулу массива:

Если вам нужно не 3, а 20 наименьших, прописывать 20 чисел будет сложно. В том случае создать ярлык — строку с числами:

Сумма наибольших N значений

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

В примере, активная ячейка содержит следующую формулу:

В своей простейшей форме, НАИБОЛЬШИЙ рассчитает «N-ые величины» значение в диапазоне. Формула рассчитает 3ье по размеру значение в диапазоне В3: В12, которым, в приведенном выше примере, является число 8.

Тем не менее, если вы подставляете «константу массива» (например, константа в виде ) в НАИБОЛЬШИЙ в качестве второго аргумента, НАИБОЛЬШИЙ получает массив результатов вместо одного результата. Таким образом, формула:

получит 1, 2 и 3-е по величине значение в диапазоне B3: B12. В приведенном выше примере, где В3: В12 содержит номера 1-10, результат от НАИБОЛЬШИЙ будет массив . СУММПРОИЗВ затем суммирует числа в этом массиве и получает общую сумму, которая равна 27.

Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ

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

Упражнение 1. «Магазин»

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

A	B	C	D	E	F
1	№	Наименование	Поставщик	Количество, л	Цена	Стоимость
2	1	Молоко	Поставщик № 1	76	43,00р.
3	2	Кефир	Поставщик № 2	40	26,00р.
4	3	Варенец	Поставщик № 3	86	34,00р.
5	4	Молоко	Поставщик № 3	45	26,00р.
6	5	Молоко	Поставщик № 4	87	24,00р.
7	6	Варенец	Поставщик № 4	34	41,00р.
8	7	Кефир	Поставщик № 4	67	32,00р.
9	8	Кефир	Поставщик № 3	45	39,00р.
10	9	Молоко	Поставщик № 2	43	35,00р.
11	10	Молоко	Поставщик № 5	32	33,00р.

3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

A	B	C	D	E	F
1	№	Наименование	Поставщик	Количество, л	Цена	Стоимость
2	1	Молоко	Поставщик № 1	76	43,00р.	3 268,00р.
3	2	Кефир	Поставщик № 2	40	26,00р.	1 040,00р.
4	3	Варенец	Поставщик № 3	86	34,00р.	2 924,00р.
5	4	Молоко	Поставщик № 3	45	26,00р.	1 170,00р.
6	5	Молоко	Поставщик № 4	87	24,00р.	2 088,00р.
7	6	Варенец	Поставщик № 4	34	41,00р.	1 394,00р.
8	7	Кефир	Поставщик № 4	67	32,00р.	2 144,00р.
9	8	Кефир	Поставщик № 3	45	39,00р.	1 755,00р.
10	9	Молоко	Поставщик № 2	43	35,00р.	1 505,00р.
11	10	Молоко	Поставщик № 5	32	33,00р.	1 056,00р.

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5
16	2	Кефир	3
17	3	Варенец	2

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

где F2:F11 — диапазон стоимости продукта из первой таблицы.

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.
16	2	Кефир	3	4 939,00р.
17	3	Варенец	2	4 318,00р.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.	32,20р.
16	2	Кефир	3	4 939,00р.	32,33р.
17	3	Варенец	2	4 318,00р.	37,50р.

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

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

Упражнение 2. «Студенческие стипендии» [1]

• стипендия не назначается, если среди оценок есть хотя бы одна двойка;
• 3,0 средний балл < 3,5 — 1000р.
• 3,5 средний балл < 4,0 — 1200р.
• 4,0 средний балл < 4,5 — 1500р.
• 4,5 средний балл < 5,0 — 1800р.
• средний балл = 5,0 — 2000р.

Определить общую сумму назначенных стипендий.

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.

• Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:

№	Фамилия	Предметы					Средний балл	Стипендия
		Матем.	Физика	Информ.	Химия	История
1	Алексеев	5	3	2	3	3
2	Гаврилов	4	4	3	3	3
3	Зайцев	3	3	3	3	3
4	Иванов	4	3	5	3	3
5	Краснов	5	4	4	4	4
6	Кузнецов	4	5	5	5	5
7	Петров	5	5	5	5	5
8	Сидоров	4	3	3	3	5
9	Смирнов	4	4	4	4	4
10	Солдатов	5	4	5	2	3

• Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

№	Фамилия	Предметы					Средний балл	Стипендия
		Матем.	Физика	Информ.	Химия	История
1	Алексеев	5	3	2	3	3	3,2	0р.
2	Гаврилов	4	4	3	3	3	3,4	1 000р.
3	Зайцев	3	3	3	3	3	3	1 000р.
4	Иванов	4	3	5	3	3	3,6	1 200р.
5	Краснов	5	4	4	4	4	4,2	1 500р.
6	Кузнецов	4	5	5	5	5	4,8	1 800р.
7	Петров	5	5	5	5	5	5	2 000р.
8	Сидоров	4	3	3	3	5	3,6	1 200р.
9	Смирнов	4	4	4	4	4	4	1 500р.
10	Солдатов	5	4	5	2	3	3,8	0р.
Итого:	11 200р.

Упражнение 3. «Завод железобетонных изделий»

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.

1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.

Допустим, нужно сделать в Excel это

Чему равна наименьшая сумма баллов по двум предметам среди школьников, получивших больше 50 баллов по географии или информатике? Как решить?

Голосование за лучший ответ

Пишешь школьников (в строчку например)
Потом (в столбик) пишешь» информатика» и география
присваиваешь им баллы
далее выделяешь все предметы и ищешь кнопку «Фильтры»
После, возле предметов появятся кнопки. Жмешь на них, после чего выбираешь «Числовые фильтры.» -> «Больше чем»
В первой строке выбери «Больше»,а во второй введи 50.
Нижние строки не трогай.
После этого делаешь так:
=[индекс ячейки информатики (Например B3)]+[индекс ячейки географии (например C3)]
после нажатия ENTER Excel сложит два числа
далее выделяешь ячейку и протягиваешь её.(Если предметы в столбик, то тянешь в столбик, и в случае с строчкой соответственно. )
У тебя в столбике будут все сложенные числа.

Похожие вопросы

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

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Предположим, нам нужно найти минимальную цену для каждого товара в базе данных по поставщикам:
Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки — столбец с ценами. Для будущего удобства, конвертируем исходный диапазон с ценами в «умную таблицу». Для этого выделите его и выберите на вкладке Главная — Форматировать как таблицу (Home — Format as Table) или нажмите Ctrl+T. Наша «поумневшая» таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1[Товар] или Таблица1[Цена]. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей «умной» таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA) , которую мы подробно разбирали ранее:

Ссылки по теме

• Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
• Выборочное вычисление суммы, среднего и т.д. по одному или нескольким критериям
• Что такое «умные таблицы» в Excel и как с ними работать