Об этом упражнении
Набор данных (строк: 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.