Перейти к содержимому
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

AVERAGEIFS and MAXIFS practice: average and largest won deal per rep (180 deals)

  • Средний
  • ~8 мин
  • SUMIF and SUMIFS

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

The CRM export holds 180 deals from April to September 2026 for five account executives, with the stage (Won, Lost or Open) and the deal amount. For the quarterly review the sales director wants two numbers per rep, counting won deals only: the average deal size and the largest deal closed. The rep names are already listed in H2:H6; fill the two columns next to them with formulas that read the stage, so open and lost deals never leak into the figures.

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

  1. 1Click I2. The rep for this row is in H2; the deal amounts are in F2:F181, the reps in B2:B181 and the stages in E2:E181.
  2. 2Write AVERAGEIFS with the average range first (F2:F181), then two pairs of range, criterion: B2:B181 against H2 and E2:E181 against "Won". Lock the three ranges with $ (F4 on Windows, ⌘T on Mac).
  3. 3Click J2 and write MAXIFS with exactly the same arguments — max range first, then the same two pairs.
  4. 4Fill I2 down to I6 and J2 down to J6 (select I2:J6 and press Ctrl+D / ⌘D after filling the first row). Every average must be smaller than the largest deal on its row. Then click Check (Ctrl+Enter / ⌘+Return).

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

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

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

Задания

  1. Не провереноIn I2:I6, use AVERAGEIFS to show the average amount (F2:F181) of the deals whose rep in B2:B181 is the name in column H and whose stage in E2:E181 is "Won" — one formula in I2, filled down.
  2. Не провереноIn J2:J6, use MAXIFS with the same two conditions to show each rep's largest won deal — one formula in J2, filled down.

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

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

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

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

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

Как решить

  • I2=AVERAGEIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")
  • J2=MAXIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")

AVERAGEIFS and MAXIFS belong to the same family as SUMIFS: the range to summarise comes first, followed by pairs of range and criterion that every counted row must satisfy. Here the pairs are "rep equals the name in column H" and "stage equals Won", so lost and open deals are excluded from both the average and the maximum. With the data ranges locked and the rep reference left relative, one formula per column filled down covers all five reps.

Pitfall 1: AVERAGEIFS returns #DIV/0! when no row matches (a rep with no won deals yet); wrap it in IFERROR(…,0) or IFERROR(…,"–") on live dashboards. MAXIFS returns 0 in the same situation, which can look like a real value. Pitfall 2: averaging the whole pipeline (AVERAGEIF on the rep only) mixes open and lost deals into "average deal size" — the stage condition is what makes the metric meaningful. Pitfall 3: MAXIFS and MINIFS need Excel 2019 or 365; in Excel 2016 the equivalent is the array formula MAX(IF((B2:B181=H2)*(E2:E181="Won"),F2:F181)) entered with Ctrl+Shift+Enter, or AGGREGATE(14,6,F2:F181/((B2:B181=H2)*(E2:E181="Won")),1). Alternatives: SUMIFS(...)/COUNTIFS(...) reproduces the average and makes the deal count visible; LARGE(IF(...),1) is the general form for the second- or third-largest deal. Reading the result: the gap between average and largest deal shows how dependent a rep is on one big win — add COUNTIFS for the number of won deals to complete the review.

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

  • =SUMIFS($F$2:$F$181,$B$2:$B$181,H2,$E$2:$E$181,"Won")/COUNTIFS($B$2:$B$181,H2,$E$2:$E$181,"Won") — average as sum ÷ count
  • =AGGREGATE(14,6,$F$2:$F$181/(($B$2:$B$181=H2)*($E$2:$E$181="Won")),1) — largest value without MAXIFS (Excel 2010+)
  • =MAX(IF(($B$2:$B$181=H2)*($E$2:$E$181="Won"),$F$2:$F$181)) — array formula; Ctrl+Shift+Enter before Excel 365

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

Leaving out the stage condition (AVERAGEIFS($F$2:$F$181,$B$2:$B$181,H2)): the figures include lost and open deals, so every average is off and the grader names the first rep whose numbers differ.

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

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

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

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

  • Excel for Data AnalystsДень 2
  • Excel for Sales and MarketingДень 3

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

  • Sales by region and product with SUMIFSСредний, ~7 мин
  • Sales by region with SUMIFНачальный, ~5 мин
  • Orders per region and month with COUNTIFSСредний, ~7 мин

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

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

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

Weighted average price with SUMPRODUCT