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

Программа · 12 дней · бесплатно

Excel for Data Analysts: a 12-Day Practice Program

33 graded exercises over 11 practice days, about 23 minutes a day, then the free Level 3 project test as the finish line. You rebuild PivotTable reports with formulas, clean raw exports, write the rule formulas behind highlights and input checks, and use expert lookups. Free, no sign-up.

  • 12 дней
  • 33 упражнения с автопроверкой
  • 17–31 мин в день
  • Итоговый тест: уровень 3, 50 мин
  • 7 упражнений используют функции, которых нет в Excel 2019 и более ранних версиях
  • Бесплатно, без регистрации
Начать день 1

Бесплатно, без регистрации. Прогресс хранится в этом браузере.

Текст программы на английском

  1. 1 День 1: Warm-up: mixed references, text pieces, formats
  2. 2 День 2: Summary grids: SUMIFS, COUNTIFS by date, AVERAGEIFS
  3. 3 День 3: Pivot without a pivot: months and shares
  4. 4 День 4: Errors and lookups: IFERROR, XLOOKUP, INDEX/MATCH
  5. 5 День 5: Clean a raw export: TRIM, split emails, UNIQUE
  6. 6 День 6: Rule formulas and PivotTable fields
  7. 7 День 7: Bands, ordered tiers and working days
  8. 8 День 8: Ranked reports: GROUPBY and SORTBY
  9. 9 День 9: Expert lookups: look left, two keys, two axes
  10. 10 День 10: The latest match, SWITCH codes, weighted averages
  11. P День 11: День проекта
  12. T День 12: День теста

T: день теста · P: день проекта

Финиш

Тест уровня 3 · 25 заданий · 50 мин · проходной балл — 700 из 1000, линейная оценка

В каждой попытке задания выбираются случайно, с новыми числами, поэтому некоторые могут повториться; на уровнях 2 и 3 рабочие книги и проекты тоже выбираются случайно.

No program can promise a pass: employers set their own pass marks and rarely publish them. Our bands and pass lines are our own cutoffs.

Подходит ли это вам?

Для кого

  • Junior data, business and reporting analysts, and people moving into an analyst role
  • Candidates facing an analyst workbook test or a MOS-style project exam
  • You already write SUMIF and VLOOKUP; if not, start with the 7-day Level 2 plan

Вы сможете

  • Rebuild PivotTable reports with formulas: SUMIFS and COUNTIFS grids with month bounds, % of grand total, GROUPBY
  • Clean a raw export: text dates in three shapes, padded names, emails split at the @, duplicates found with UNIQUE
  • Write the TRUE/FALSE formulas behind conditional formatting and data validation rules
  • Use expert lookups: XLOOKUP to the left and on two keys, two-way INDEX/MATCH, the latest match, SWITCH codes
  • Fix a PivotTable that counts instead of sums, and show revenue with margin % on a combo chart

Чего пока нет

  • PMT, NPER, Goal Seek and Scenario Manager: Level 3 can draw them (about 2 of 25 tasks), no exercise yet
  • Custom number-format codes such as #,##0.0,"K" and [h]:mm: only the Format Cells basics are practiced
  • Power Query, Power Pivot and DAX, VBA, Python in Excel and statistics
  • Building PivotTable and chart objects by hand: the grid rebuilds them with formulas and asks about pictured screens

По дням

12 дней, 33 упражнения с автопроверкой. Время примерное, в расчёте на одну попытку; ничего не блокируется, так что занимайтесь в своём темпе.

✓ выполнено · ◐ начато · ○ не начато

Дни 1–5

  1. День 1

    Warm-up: mixed references, text pieces, formats

    17 мин

    Three beginner skills every analyst task leans on: a grid that copies with mixed references, text cut into parts, and number formats.

    • Price grid for quantities × discounts (mixed references)

      Начальный · 7 мин

    • Extract category, year and serial from SKU

      Начальный · 6 мин

    • Apply currency and date formats

      Начальный · 4 мин

    • What an employer workbook test looks like

      Чтение, необязательно · 6 мин

  2. День 2

    Summary grids: SUMIFS, COUNTIFS by date, AVERAGEIFS

    21 мин

    Totals, counts and averages by several conditions, including a date range: the core of every report request.

    • Sales by region and product with SUMIFS

      Средний · 7 мин

    • Orders between two dates

      Средний · 6 мин

    • Average and largest deal per rep (AVERAGEIFS, MAXIFS)

      Средний · 8 мин

  3. День 3

    Pivot without a pivot: months and shares

    23 мин

    Month bounds with EOMONTH, revenue by category and month with SUMIFS, and each channel's share of the grand total.

    • Invoice due dates with EOMONTH and EDATE

      Средний · 6 мин

    • Pivot without a pivot: revenue by category and month with SUMIFS

      Средний · 10 мин

    • Share of revenue by channel: % of Grand Total with formulas

      Средний · 7 мин

  4. День 4

    Errors and lookups: IFERROR, XLOOKUP, INDEX/MATCH

    18 мин

    Margins without #DIV/0!, then the two lookups that replace VLOOKUP in analyst workbooks.

    • Margin % without #DIV/0!

      Средний · 6 мин

    • Replace VLOOKUP with XLOOKUP

      Средний · 6 минExcel 365

    • INDEX and MATCH exact lookup

      Средний · 6 мин

  5. День 5

    Clean a raw export: TRIM, split emails, UNIQUE

    21 мин

    Padded, mis-cased names, emails split at the @, and one row per customer: the cleanup before any analysis.

    • Clean padded, lower-case names with TRIM and PROPER

      Средний · 6 мин

    • Split email into user and domain (FIND/LEFT/RIGHT or TEXTBEFORE/TEXTAFTER)

      Средний · 7 мин

    • List unique customers (UNIQUE) — remove duplicates

      Средний · 8 минExcel 365

Дни 6–10

  1. День 6

    Rule formulas and PivotTable fields

    27 мин

    The TRUE/FALSE formulas behind a highlight rule and an input rule, then a PivotTable that counts when it should sum.

    • Highlight overdue invoices (conditional formatting)

      Средний · 8 мин

    • Restrict quantity to whole numbers 1–100

      Средний · 7 мин

    • Fix a PivotTable that counts instead of sums

      Средний · 12 мин

  2. День 7

    Bands, ordered tiers and working days

    22 мин

    Approximate-match bands, tiers tested in the right order, and working days between two dates.

    • Shipping cost bands with approximate VLOOKUP

      Средний · 7 мин

    • Commission tiers with nested IF, IFS or a lookup

      Средний · 8 мин

    • Working days to resolve tickets (NETWORKDAYS)

      Средний · 7 мин

  3. День 8

    Ranked reports: GROUPBY and SORTBY

    23 мин

    Pivot-style reports that rebuild themselves: GROUPBY by region, GROUPBY with a filter sorted largest first, and a ranked product list.

    • A pivot-style sales report by region with GROUPBY

      Средний · 6 минExcel 365

    • Tickets per agent: GROUPBY with a filter, no total, largest first

      Продвинутый · 9 минExcel 365

    • Products ranked by sales with SORTBY

      Продвинутый · 8 минExcel 365

  4. День 9

    Expert lookups: look left, two keys, two axes

    24 мин

    XLOOKUP to the left of the key, a lookup on product and region together, and a two-way INDEX/MATCH in a matrix.

    • Look left: employee ID by email

      Продвинутый · 6 минExcel 365

    • Two-criteria lookup (product and region)

      Продвинутый · 10 мин

    • Two-way lookup in a matrix

      Продвинутый · 8 мин

  5. День 10

    The latest match, SWITCH codes, weighted averages

    26 мин

    The last order per customer, region names from codes, and a weighted average with SUMPRODUCT.

    • Last order date per customer

      Продвинутый · 10 мин

    • Region names from codes with SWITCH or IFS

      Продвинутый · 8 мин

    • Weighted average price with SUMPRODUCT

      Продвинутый · 8 мин

Дни 11–12

  1. День 11

    Project day: clean, slice and present an export

    День проекта · 31 мин

    The three things employer workbook tests grade: text dates turned into real dates, a live FILTER with its count and total, and a combo chart.

    • Turn text dates into real dates and group by month

      Продвинутый · 12 мин

    • Orders over $1,000 in West with FILTER

      Продвинутый · 10 минExcel 365

    • Revenue and margin % in one readable chart

      Продвинутый · 9 мин

  2. День 12

    Final test: Level 3

    Итоговый тест · 50 мин

    The free Level 3 test: 25 tasks from 5 of 10 projects in 50 minutes. It draws its projects at random, so it may use other data than this program.

    • Итоговый тест: уровень 3 · 25 заданий · 50 мин

      →
    • How we grade the test

      Чтение, необязательно · 5 мин

Вопросы об этом плане

How long does Excel for Data Analysts take?

12 days: 11 practice days of 17 to 31 minutes (about 23 on average), then the 50-minute Level 3 test. Go at your own pace; nothing locks, and exercises you already solved count.

What should I know before the analyst program?

SUM, IF, SUMIF and VLOOKUP. Day 1 is a short warm-up on references, text and number formats; if it feels hard, take the 7-day Level 2 plan first.

Does Excel for Data Analysts cover PivotTables?

Partly. The practice grid runs in your browser and has no PivotTable object, so you rebuild pivot reports with SUMIFS and GROUPBY and answer questions about pictured PivotTable screens. The Level 3 test asks about PivotTable layouts and settings in words.

How do I know the analyst program worked?

The finish line is the free Level 3 test: 25 tasks from 5 of 10 projects, scored 1–1000 with our pass mark at 700. No program can promise a pass: employers set their own pass marks, and our cutoffs are our own.

Другие программы

  • Prepare for your Excel test in 7 days

    Day 1: the free timed test at your level. Days 2–6: three graded exercises a day. Day 7: retake it and compare.

  • Excel for Sales and Marketing

    For sales and marketing ops: totals by region and rep, commission tiers, lookups, clean customer lists and live reports.

  • Excel for Accounting and Bookkeeping

    For AP/AR, bookkeeping and month-end work: tax lines, due dates, overdue invoices, SUMIFS by month and lookups.

Excel — товарный знак группы компаний Microsoft. Indeed, TestGorilla, SHL, iMocha, Adaface, Criteria и другие товарные знаки принадлежат их владельцам. GridBelt — независимый проект: он не связан ни с одной из этих компаний, не одобрен и не спонсируется ими.