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

Pivot table practice: revenue by category and month with SUMIFS

  • Средний
  • ~10 мин
  • Pivot tables

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

An office-furniture webshop exported its January–June 2026 order lines: 300 rows with the order date, the product category and the line revenue. The category manager wants the PivotTable she knows — Category in Rows, Order date grouped by Months in Columns, Sum of Revenue in Values, with grand totals — but as formulas that recalculate when the next export is pasted in; this practice grid has no PivotTable command anyway. Two habits of the shop make the month edges matter: every month opens with a deal day on the 1st, and business accounts are invoiced on the last day of the month.

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

  1. 1Look at the layout: the categories run down F2:F6, and G1:L1 hold the first day of each month — real dates formatted to show "Jan 2026" and so on, one column per month as in a PivotTable with the dates grouped by Months. A month runs from its 1st up to, but not including, the 1st of the next month.
  2. 2Click G2 and type =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<"&EDATE(G$1,1)). The first pair picks the category; the two date pairs keep the orders from the 1st of the month (">="&G$1) to before the 1st of the next month ("<"&EDATE(G$1,1)) — EDATE(G$1,1) is the same day one month later.
  3. 3Check the $ signs before copying: the data ranges are fully locked, $F2 keeps column F (the category) and G$1 keeps row 1 (the month). F4 (Windows) or ⌘T (Mac) cycles through the variants.
  4. 4Fill G2 right to L2 with the fill handle, then select G2:L6 and press Ctrl+D (Windows) / ⌘D (Mac) to fill the row down to row 6. (Excel also fills a selected block when you type the formula once and press Ctrl+Enter / ⌘+Return; in this practice grid that key runs Check.)
  5. 5Add the grand totals the way a PivotTable shows them: =SUM(G2:L2) in M2, filled down to M6, and =SUM(G2:G6) in G7, filled right to M7.
  6. 6Reconcile: M7 must equal =SUM(D2:D301), because every order falls into exactly one category and one month. If it does not, check the month bounds — ">" instead of ">=" loses the orders of the 1st, and "<=" with EDATE counts the next month's 1st twice.

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

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

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

Задания

  1. Не провереноFill G2:L6 with one SUMIFS formula (mixed references, copied across the grid) that totals Revenue (D2:D301) per category in F2:F6 and per month — from the month-start date in row 1 up to, but not including, the next month's 1st (SUMPRODUCT is accepted too).
  2. Не провереноAdd the grand totals: M2:M6 for each category, G7:L7 for each month and M7 for the whole grid.

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

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

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

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

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

Как решить

  • G2=SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<"&EDATE(G$1,1))
  • M2=SUM(G2:L2)
  • G7=SUM(G2:G6)

A PivotTable that groups dates by Months puts every order into the calendar month it falls in, from the 1st to the last day. The SUMIFS rebuilds that bucket with two conditions on the same date column — ">="&G$1 (on or after the month's 1st) and "<"&EDATE(G$1,1) (before the next month's 1st) — plus the category condition. Mixed references let one formula fill all 30 cells, and the grand totals reconcile to SUM(D2:D301) because every order lands in exactly one cell.

The PivotTable route in Excel: Insert → PivotTable, Category to Rows, Order date to Columns, Revenue to Values (Sum). Excel 2016 and later group a date field automatically; to set the grouping yourself, right-click a date → Group → Months (the labels then read Jan, Feb, …; select Years as well when the data spans more than one year, or January of both years lands in one column). The pivot needs Refresh after new data; these formulas recalculate by themselves, as long as a new export fits in rows 2–301 (for a longer one, extend the ranges or keep the data in an Excel Table). Pitfall 1: the bounds. ">"&G$1 drops every order of the 1st (the deal day), "<="&EDATE(G$1,1) counts the next month's 1st in two months, "<"&EOMONTH(G$1,0) drops the last day (the B2B invoices). Pitfall 2: timestamps. An order at 31 Jan 14:30 is later than EOMONTH(G$1,0), which is midnight, so "<="&EOMONTH(…) misses it, while "<"&EDATE(G$1,1) is safe with times too. Pitfall 3: text headers such as "Jan" cannot be compared with dates; with real dates in row 1 the same formula works for any month and year, and changing G1 re-targets its whole column. In Excel 365, =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 spills the whole grid from one cell — the + in front of G1:L1 matters, because Excel's EDATE rejects a plain range with #VALUE! but accepts an array. PIVOTBY builds a cross-tab with its own headers (=PIVOTBY(C2:C301,TEXT(B2:B301,"yyyy-mm"),D2:D301,SUM)); this practice grid does not support PIVOTBY.

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

  • =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<="&EOMONTH(G$1,0)) — the month end as the upper bound (fine while the dates carry no times)
  • =SUMPRODUCT(($C$2:$C$301=$F2)*(TEXT($B$2:$B$301,"yyyy-mm")=TEXT(G$1,"yyyy-mm"))*$D$2:$D$301) — compare year and month as text
  • =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 — Excel 365, one formula spills the whole grid (keep the + before G1:L1)
  • =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2) in M2 — a category total straight from the data instead of adding up the row

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

Writing ">"&G$1 instead of ">="&G$1: every order placed on the 1st — the monthly deal day — drops out of its month, so G2 already misses part of the January Accessories revenue.

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

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

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

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

  • Excel test in 7 days, План уровня 3День 4
  • Excel for Data AnalystsДень 3
  • Excel for Accounting and BookkeepingДень 5

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

  • Sales by region and product with SUMIFSСредний, ~7 мин
  • Orders per region and month with COUNTIFSСредний, ~7 мин
  • A pivot-style sales report by region with GROUPBYСредний, ~6 мин

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

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

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

A pivot-style sales report by region with GROUPBY