Перейти к содержимому
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. FILTER, SORT and UNIQUE

FILTER function practice: orders over $1,000 in West, counted and totalled

  • Продвинутый
  • ~10 мин
  • FILTER, SORT and UNIQUE
  • Excel 365

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

The order log for July–October 2026 holds 300 orders from five regions in A2:E301. The West regional director wants a live list of her large orders — every order over the amount in H3, all five columns — plus how many there are and what they add up to, and she wants to change the region in H2 or the amount in H3 and see everything follow. The Region column was typed by hand: 28 rows differ from the standard spelling only by letter case or a trailing space ("west", "WEST", "West "), and 73 orders belong to West once you read them that way. One West order is exactly $1,000.00 — not over it.

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

  1. 1Click J2 and start the formula: =FILTER(A2:E301, — the first argument is the whole order table, all five columns.
  2. 2Build the include argument as two tests multiplied together: (TRIM(C2:C301)=H2)*(E2:E301>H3). TRIM strips the stray spaces before the comparison, the comparison itself ignores letter case, and > (not >=) leaves out the order of exactly $1,000.00.
  3. 3Add the third argument so an empty result reads as text instead of #CALC!: ..., "No orders") and press Enter. The matching orders spill into J2:N52 on their own — keep those cells empty, or Excel shows #SPILL!.
  4. 4Click H4 and count the matches with the same test: =ROWS(FILTER(A2:A301,(TRIM(C2:C301)=H2)*(E2:E301>H3))), or =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)).
  5. 5Click H5 and add them up: =SUM(FILTER(E2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3))).
  6. 6Test it: type 5000 in H3 or North in H2 and watch the list, the count and the total change (Ctrl+Z / ⌘Z undoes the edit). Put West and 1000 back before you check.

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

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

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

Задания

  1. Не провереноIn J2, use FILTER to list every order (all columns A:E) whose region matches H2 — compared after TRIM — and whose amount is over the amount in H3; return "No orders" when nothing matches. The list spills across J:N.
  2. Не провереноIn H4, count how many orders match the region in H2 (trimmed) and are over the amount in H3 — for example ROWS around a FILTER, or SUMPRODUCT over the same two tests.
  3. Не провереноIn H5, add up the amounts of those same orders — SUM around a FILTER of column E, or SUMPRODUCT.

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

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 мин. Проверка прямо в браузере: значение и формула, которая его даёт, с обратной связью о том, что исправить.

Как решить

  • J2=FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders")
  • H4=ROWS(FILTER(A2:A301,(TRIM(C2:C301)=H2)*(E2:E301>H3)))
  • H5=SUM(FILTER(E2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)))

FILTER keeps the rows of A2:E301 for which the include argument is TRUE. Two conditions are combined by multiplying them: TRUE*TRUE is 1, anything else is 0, so (TRIM(C2:C301)=H2)*(E2:E301>H3) is an AND that works across a whole column at once. TRIM matters because "West " with a trailing space is not equal to "West" — and the first large West order in this log is typed exactly like that — while > keeps the $1,000.00 order out, because it is not over $1,000. The optional third argument replaces the #CALC! error with "No orders" when nothing qualifies, and ROWS and SUM around the same FILTER give the count and the total.

Pitfall 1: text comparisons in Excel ignore case but not spaces; TRIM also collapses double spaces inside a name, while CLEAN removes the non-printing characters that copy-pastes bring along. Pitfall 2: use * for AND and + for OR between array conditions; AND() and OR() collapse the arrays to a single TRUE/FALSE and FILTER then returns everything or nothing. Pitfall 3: ROWS(FILTER(...,"No orders")) returns 1 for an empty result because the fallback text is a 1×1 array — count with a FILTER that has no fallback, or with SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)), which returns 0. Alternatives: SUMPRODUCT handles the count, and SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) the total, in Excel 2019; COUNTIFS and SUMIFS are faster on very large tables but cannot TRIM the criteria range, so they miss the 3 rows typed "West " here — clean the column first. Layout: a spilled list grows and shrinks, so never put anything below or to the right of its anchor; SORT(FILTER(...),5,-1) sorts the result by amount, and CHOOSECOLS(FILTER(...),1,5) returns only the columns you need. Version note: FILTER and SORT need Excel 365 or Excel 2021 (CHOOSECOLS: Excel 365); in Excel 2019 the equivalent is an AutoFilter plus SUBTOTAL, or Advanced Filter for a static copy.

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

  • =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3)) — the count without FILTER (works in Excel 2019)
  • =SUMPRODUCT((TRIM(C2:C301)=H2)*(E2:E301>H3),E2:E301) — the total without FILTER
  • =FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3)) — the same list without the fallback; shows #CALC! when nothing matches
  • =SORT(FILTER(A2:E301,(TRIM(C2:C301)=H2)*(E2:E301>H3),"No orders"),5,-1) — largest order first (a different order, so not for this check)

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

Comparing the region without TRIM: =FILTER(A2:E301,(C2:C301=H2)*(E2:E301>H3)) skips the 3 matching orders typed "West " — including the very first one, so J2 shows SO-41012 instead of SO-41008 and H4 shows 48 instead of 51.

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

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

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

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

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

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

  • List unique customers (UNIQUE) — remove duplicatesСредний, ~8 мин
  • Products ranked by sales with SORTBYПродвинутый, ~8 мин
  • Highlight the top 10 % of salesСредний, ~8 мин

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

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

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

Products ranked by sales with SORTBY