Перейти к содержимому
GridBelt
  • Тест по Excel
  • Упражнения
  • Программы
  • Цены
Войти
Начать бесплатный тест
  • Тест по Excel
  • Упражнения
  • Программы
  • Цены
ВойтиСоздать аккаунт
Начать бесплатный тест
GridBelt

Тренируйте навыки Excel прямо в браузере.

Тест по Excel

  • Уровень 1: Начальный
  • Уровень 2: Средний
  • Уровень 3: Продвинутый
  • Подготовка к тесту по Excel за 7 дней
  • Таблица лидеров
  • Тест по Excel на собеседовании
  • Тест по Excel от Indeed

Упражнения

  • Все упражнения
  • Программы
  • Для начинающих

Материалы

  • Формулы Excel
  • Горячие клавиши Excel

Компания

  • О проекте
  • Цены
  • Как мы проверяем

© GridBelt. Все права защищены.

  • Политика конфиденциальности
  • Условия использования
  • Политика возврата средств

Excel — товарный знак корпорации Microsoft. Indeed, TestGorilla, SHL и другие товарные знаки принадлежат их владельцам. GridBelt — независимый сайт для практики: он не связан с Microsoft, TestGorilla, SHL или Indeed, не одобрен и не спонсируется ими.

  1. Упражнения
  2. /
  3. SUMIF and SUMIFS

SUMPRODUCT practice: weighted average price over 50 purchase lots

  • Продвинутый
  • ~8 мин
  • SUMIF and SUMIFS

Текст упражнения на английском

A coffee roaster bought 50 lots of green coffee in 2026 from 6 importers, listed in A2:F51 with the quantity in kilograms and the price paid per kilogram. Prices range from $4.26 to $12.63 per kg and lots from 60 kg to 840 kg: the cheap Brazil lots average 583 kg, the expensive Kenya lots 197 kg, and every importer gives a volume discount, so within one origin the big lots are the cheap ones too. A plain average of the price column ($8.04) therefore overstates what a kilogram really cost. Finance needs the total spend, the true average cost per kilogram for the year-end stock valuation, and the same figure for the origin typed in I5.

Что нужно сделать

  1. 1Click I2 and type =SUMPRODUCT(E2:E51,F2:F51). SUMPRODUCT multiplies each quantity by its price and adds the 50 products in one step — the total spend.
  2. 2Click I3 and divide that total by the kilograms bought: =SUMPRODUCT(E2:E51,F2:F51)/SUM(E2:E51). This is the quantity-weighted average price per kg; compare it with I4, which already holds =AVERAGE(F2:F51).
  3. 3Click I6 for the origin in I5: multiply the quantities by the test (D2:D51=I5) inside SUMPRODUCT, =SUMPRODUCT((D2:D51=I5)*E2:E51,F2:F51), and divide by the kilograms of that origin, SUMIF(D2:D51,I5,E2:E51). AVERAGEIF(D2:D51,I5,F2:F51) would average the prices without weights.
  4. 4Press Enter after each formula; no Ctrl+Shift+Enter (⌘+Shift+Enter on Mac) is needed — SUMPRODUCT handles the arrays itself in every Excel version.
  5. 5Type Kenya in I5: I6 changes to $11.87, the Kenya figure. Put Colombia back before you check.

Ячейки ответа

Обратная связь

Введите формулу в таблицу, нажмите Enter, а затем «Проверить». Мы оцениваем значение в каждой ячейке ответа и формулу, которая его даёт.

Задания

  1. Не провереноIn I2, use SUMPRODUCT to compute the total spend: every quantity in E2:E51 times its price in F2:F51, added up.
  2. Не провереноIn I3, use SUMPRODUCT to compute the weighted average price per kg: total spend divided by the total kilograms in E2:E51.
  3. Не провереноIn I6, use SUMPRODUCT with a condition to compute the weighted average price per kg of the origin in I5 only (spend on that origin divided by its kilograms).

Для заданий в таблице рекомендуем компьютер. Лист прокручивается внутри этой области.

StartInsertFormulasDataView
A1fxГотовим вашу рабочую книгу…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Введите формулу в лист, затем нажмите «Проверить».

Об этом упражнении

Набор данных (строк: 50) на одном листе, 3 ячейки ответа, около 8 мин. Проверка прямо в браузере: значение и формула, которая его даёт, с обратной связью о том, что исправить.

Как решить

  • I2=SUMPRODUCT(E2:E51,F2:F51)
  • I3=SUMPRODUCT(E2:E51,F2:F51)/SUM(E2:E51)
  • I6=SUMPRODUCT((D2:D51=I5)*E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51)

SUMPRODUCT takes ranges of the same size, multiplies them pair by pair and adds the products, so =SUMPRODUCT(E2:E51,F2:F51) is the total spend — the same as writing a quantity × price column and summing it, in one cell. Dividing that total by the total kilograms gives the weighted average: each lot's price counts in proportion to its size, which is why the result ($6.92) is lower than the plain AVERAGE of the price column ($8.04). A test such as (D2:D51=I5) inside SUMPRODUCT turns into 1 for the matching rows and 0 for the rest, so only that origin's spend is added; SUMIF supplies the matching kilograms.

Pitfall 1: =AVERAGE(F2:F51) is only right when every lot has the same quantity; with unequal weights it leans towards the small lots — here the small, dear Kenya lots pull it up, and the volume discount does the same inside each origin (AVERAGEIF gives $8.18 for Colombia instead of $7.91). Pitfall 2: the ranges must be the same shape, otherwise SUMPRODUCT returns #VALUE!; a text cell such as "n/a" counts as 0 in the comma form (SUMPRODUCT(E,F)) but produces #VALUE! in the multiplication form (SUMPRODUCT(E*F)). Pitfall 3: a condition on its own, SUMPRODUCT(D2:D51=I5,E2:E51), multiplies TRUE/FALSE values that SUMPRODUCT treats as 0 — multiply the test by a number range ((D2:D51=I5)*E2:E51) or coerce it with -- first. Alternatives: =SUM(E2:E51*F2:F51) does the same in Excel 365 (dynamic arrays) and needs Ctrl+Shift+Enter in Excel 2019; a helper column G = E2*F2 with =SUM(G2:G51)/SUM(E2:E51) is easier to audit and what many finance teams prefer; =SUMIFS(G2:G51,D2:D51,I5)/SUMIFS(E2:E51,D2:D51,I5) is the helper-column version of the origin figure. Rounding: keep the division unrounded for the valuation and format the cell as currency; =ROUND(...,2) is accepted here because the tolerance is half a cent.

Другие рабочие способы

  • =SUMPRODUCT(E2:E51*F2:F51)/SUM(E2:E51) — the multiplication form of SUMPRODUCT
  • =SUMPRODUCT(--(D2:D51=I5),E2:E51,F2:F51)/SUMIF(D2:D51,I5,E2:E51) — the origin figure with the test coerced by --
  • =SUMPRODUCT((D2:D51=I5)*E2:E51*F2:F51)/SUMPRODUCT((D2:D51=I5)*E2:E51) — the origin figure with SUMPRODUCT on both sides
  • =SUM(E2:E51*F2:F51)/SUM(E2:E51) — array multiplication inside SUM (Excel 365); not accepted here because the task names SUMPRODUCT

Частые ошибки

Using =AVERAGE(F2:F51) for the average price: it gives $8.04 instead of $6.92 because the 60 kg lots and the 840 kg lots count the same — the quantities must weight the prices.

Как мы проверяем

Принимается любая формула, которая даёт верный результат, если только задание не называет функцию: тогда другая функция даёт половину балла. Мы сравниваем значения с небольшим допуском и не учитываем регистр и пробелы в тексте. Верное число, введённое вручную вместо формулы, даёт половину балла. Задания с несколькими ячейками получают полный балл, если верны 100% ячеек, и половину — если верны от 50%. Там, где это важно, мы также меняем входную ячейку в копии вашего листа и проверяем, что формула пересчитывается правильно.

Ответы рассчитаны по правилам Excel 365 и 16 ноября 2026 г. сверены в нашей системе проверки. Автор: GridBelt team.

Входит в программы

  • Excel test in 7 days, План уровня 2День 6
  • Excel for Data AnalystsДень 10
  • Excel for Sales and MarketingДень 8

Похожие упражнения

  • Sales by region with SUMIFНачальный, ~5 мин
  • Sales by region and product with SUMIFSСредний, ~7 мин
  • Average and largest deal per rep (AVERAGEIFS, MAXIFS)Средний, ~8 мин
  • Total of orders above $500Начальный, ~4 мин

Справка по функции

Официальная документация

Следующее упражнение

Highlight overdue invoices (conditional formatting)