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

Conditional formatting practice: highlight overdue invoices with a formula rule

  • Средний
  • ~8 мин
  • Conditional formatting

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

The receivables list holds 80 invoices issued between July and October 2026, sorted by customer, with the due date in column D and the payment date in column F — blank while the invoice is unpaid. On the report date in J2 (2026-10-31), 46 invoices are still open: some are past due, others are not due yet (one falls due on the report date itself) and 3 have no due date because the terms are still being agreed; 16 other invoices were paid late but are paid. The controller wants the overdue rows highlighted by a conditional-formatting rule. The rule is a TRUE/FALSE formula, so you write it in column G first and watch it work row by row.

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

  1. 1Click G2 and type =AND(D2<>"",D2<$J$2,F2=""). An invoice is overdue when it has a due date, the due date is before the report date and nothing has been paid. Select J2 in the formula and press F4 (Windows) or ⌘T (Mac) to lock it as $J$2.
  2. 2D2<>"" matters: an empty due date counts as 0, the earliest date there is, so without it the 3 invoices with no terms (rows 21, 45, 64) would show TRUE.
  3. 3Press Enter, then fill G2 down to G81: double-click the fill handle, or select G2:G81 and press Ctrl+D (Windows) / ⌘D (Mac). Each row shows TRUE or FALSE — exactly what a formatting rule evaluates.
  4. 4Click J3 and count the TRUE values: =COUNTIF(G2:G81,TRUE). Click J4 and total their amounts: =SUMIF(G2:G81,TRUE,E2:E81).
  5. 5Now apply the rule itself: select A2:F81, then Home → Conditional Formatting → New Rule → "Use a formula to determine which cells to format" (Windows keyboard: Alt, H, L, N; on Mac the same menu path). Enter =AND($D2<>"",$D2<$J$2,$F2=""), pick a light red fill and click OK. The checker grades your column G and the totals; the coloured rows are the result you would hand to the controller.
  6. 6Change J2 to 2026-10-15: J3 drops to 19. Put 2026-10-31 back before you check.

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

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

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

Задания

  1. Не провереноIn G2, write a formula that returns TRUE when the invoice is overdue — it has a due date (D2), that date is before the report date in J2, and Paid on (F2) is empty — and FALSE otherwise; fill it down to G81.
  2. Не провереноIn J3, count the overdue invoices (for example COUNTIF over your TRUE/FALSE column, or COUNTIFS on the dates and the empty payment cells).
  3. Не провереноIn J4, total the amounts (E2:E81) of the overdue invoices.

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

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

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

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

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

Как решить

  • G2=AND(D2<>"",D2<$J$2,F2="")
  • J3=COUNTIF(G2:G81,TRUE)
  • J4=SUMIF(G2:G81,TRUE,E2:E81)

A conditional-formatting rule of the "use a formula" kind is just a formula that returns TRUE or FALSE for each row, so writing it in a helper column first shows you exactly which rows the rule will colour. AND needs all three tests to hold: D2<>"" (there is a due date), D2<$J$2 (it is in the past relative to the report date) and F2="" (no payment yet). The $ signs lock J2 so every row compares against the same report date when you fill down, while D2 and F2 stay relative and move row by row. COUNTIF and SUMIF then count and total the TRUE rows.

Pitfall 1: in the rule dialog the formula is written for the top-left cell of the selection (A2) and Excel shifts it for every other cell, so the column references need a $ ($D2, $F2) — otherwise the cells in column B would test E and G instead. Pitfall 2: an empty cell compares as 0, so D2<$J$2 is TRUE for an invoice without a due date — 3 invoices here; D2<>"" (or ISNUMBER(D2)) keeps them out. COUNTIFS(D2:D81,"<"&J2,F2:F81,"") gets this right on its own, because COUNTIFS skips blank cells for a "<" criterion. Pitfall 3: < versus <= — an invoice due on the report date (row 77) is not overdue yet; and a paid invoice is never overdue, even a late one, so a rule on the due date alone would also colour the 31 paid invoices whose due date has passed (16 of them were paid late). Alternatives: =IF(AND(...),TRUE,FALSE) is the same with an explicit IF; =(D2<>"")*(D2<$J$2)*(F2="") returns 1 or 0 — enough inside a formatting rule, where any non-zero number counts as TRUE, but this column is checked for TRUE/FALSE; =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") count and total without the helper column. Reporting: TODAY() instead of a typed report date keeps the list live, but the file then changes every day and cannot be reproduced for an audit — many controllers keep the report date in a cell exactly as here. Windows shortcuts: Alt, H, L, N opens New Rule and Alt, H, L, R manages the rules; on Mac use Home → Conditional Formatting → Manage Rules.

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

  • =AND(ISNUMBER(D2),D2<$J$2,ISBLANK(F2)) — the same tests with ISNUMBER and ISBLANK
  • =IF(AND(D2<>"",D2<$J$2,F2=""),TRUE,FALSE) — explicit IF around the same test
  • =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") — count and amount without the helper column
  • =SUMPRODUCT(--G2:G81) — counts the TRUE values by turning them into 1s

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

=AND(D2<$J$2,F2="") without the due-date test: an empty due date counts as 0, so the 3 invoices with no terms (rows 21, 45, 64) show TRUE and the count in J3 comes out 27 instead of 24.

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

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

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

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

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

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

  • Highlight the top 10 % of salesСредний, ~8 мин
  • Drop-down list of regions (data validation)Средний, ~7 мин
  • Orders over $1,000 in West with FILTERПродвинутый, ~10 мин

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

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

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

Highlight the top 10 % of sales