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

Excel chart practice: pick the right chart type for each question

  • Начальный
  • ~9 мин
  • Charts

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

A small online plant shop is preparing its 2026 review. The Orders sheet holds the year's 240 order lines in A2:F241, newest first: order ID, date, sales channel, product, units and revenue. The owner has four questions for the review and wants one chart per question. Before any chart can show the year month by month, the summary block H1:J13 needs its monthly revenue figures.

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

  1. 1The block H1:J13 is the monthly summary. H2:H13 hold the first day of each month (displayed as Jan 2026 … Dec 2026) and I2:I13 the last day; J2:J13 must add up the revenue in column F of the orders dated between those two days.
  2. 2Click J2 and start a SUMIFS: the sum range comes first (F2:F241), then the order dates B2:B241 with the condition ">="&H2 (on or after the first day), then B2:B241 again with "<="&I2 (on or before the last day). The & joins the comparison sign to the date in the cell.
  3. 3Lock the three data ranges with $ (F4 on Windows, ⌘T on Mac) so they stay put, and leave H2 and I2 relative so they move to the next month as you fill down.
  4. 4Fill J2 down to J13 (double-click the fill handle, or select J2:J13 and press Ctrl+D / ⌘D). The twelve totals must add up to the total of column F; the orders dated on the 1st or on the last day of a month show whether your comparison signs are right.
  5. 5Then answer the four questions below. The pictures draw these same orders as charts; they stand in for Excel, because this practice grid has no chart command.

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

Вопросы

Ответы выбирайте здесь — кнопка «Проверить» оценит их вместе с ячейками ответа.

  1. Вопрос 1The owner asks: "Did revenue climb through 2026, or did it just bounce around?" Which chart type answers that best?

    Выберите один ответ.

    Two charts of the same revenue by month in 2026, titled "Revenue by month, 2026". Chart A is a pie chart with one slice per month, clockwise from January: Jan $1,056 (6%), Feb $940 (5%), Mar $916 (5%), Apr $1,033 (6%), May $1,534 (8%), Jun $1,285 (7%), Jul $1,642 (9%), Aug $1,132 (6%), Sep $1,785 (10%), Oct $2,210 (12%), Nov $2,259 (12%), Dec $2,639 (14%). Chart B is a line chart with the months from January on the left to December on the right and a dollar axis starting at $0: Jan $1,056, Feb $940, Mar $916, Apr $1,033, May $1,534, Jun $1,285, Jul $1,642, Aug $1,132, Sep $1,785, Oct $2,210, Nov $2,259, Dec $2,639.
  2. Вопрос 2The owner also wants to see how revenue splits across the four sales channels. Which of these charts show each channel as a share of a whole? Select all that apply.

    Выберите все подходящие варианты.

    Four charts of revenue by sales channel in 2026, one colour per channel (Webshop blue, Marketplace orange, Social shop grey, Wholesale gold). Chart A is a pie of the whole year: Webshop 47%, Marketplace 20%, Social shop 11%, Wholesale 22%. Chart B is a 100% stacked column per quarter, each column split by channel (whole percents that add up to 100): Q1: Webshop 50%, Marketplace 31%, Social shop 9%, Wholesale 10%; Q2: Webshop 44%, Marketplace 18%, Social shop 9%, Wholesale 29%; Q3: Webshop 50%, Marketplace 16%, Social shop 8%, Wholesale 26%; Q4: Webshop 47%, Marketplace 20%, Social shop 13%, Wholesale 20%. Chart C draws one line per channel across the four quarters and chart D one column per channel in each quarter, both from the same amounts: Webshop: Q1 $1,461, Q2 $1,694, Q3 $2,254, Q4 $3,318; Marketplace: Q1 $883, Q2 $698, Q3 $731, Q4 $1,405; Social shop: Q1 $269, Q2 $362, Q3 $371, Q4 $950; Wholesale: Q1 $299, Q2 $1,098, Q3 $1,203, Q4 $1,435.
  3. Вопрос 3Units sold per product: in the column chart below, the long product names are cut off. Which chart type keeps every name readable?

    Выберите один ответ.

    A column chart titled "Units sold by product, 2026", one column per product, largest first (ZZ plant Zamioculcas, 14 cm pot: 131; Monstera deliciosa, 17 cm pot: 123; Snake plant Laurentii, 12 cm pot: 111; Golden pothos, hanging basket: 94; Fiddle-leaf fig, 21 cm pot: 55; Olive tree, 24 cm pot: 41). The product names are written slanted under the columns and cut off after the first word or two, so none of them can be read in full.
  4. Вопрос 4Last question: "Do orders with more units also bring in more revenue, and how close is that link?" Every order gives you two numbers, units and revenue. Which chart type shows that?

    Выберите один ответ.

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

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

Задания

  1. Не провереноIn J2:J13, total the revenue (F2:F241) of each month, from the first day in column H to the last day in column I, with one SUMIFS formula filled down (SUMPRODUCT or SUM(FILTER(…)) are accepted too).
  2. Не провереноВопрос 1: The owner asks: "Did revenue climb through 2026, or did it just bounce around?" Which chart type answers that best?
  3. Не провереноВопрос 2: The owner also wants to see how revenue splits across the four sales channels. Which of these charts show each channel as a share of a whole? Select all that apply.
  4. Не провереноВопрос 3: Units sold per product: in the column chart below, the long product names are cut off. Which chart type keeps every name readable?
  5. Не провереноВопрос 4: Last question: "Do orders with more units also bring in more revenue, and how close is that link?" Every order gives you two numbers, units and revenue. Which chart type shows that?

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

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

Ответьте на вопросы, заполните ячейки и проверьте.

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

Набор данных (строк: 240) на одном листе, одна ячейка ответа, около 9 мин. Проверка прямо в браузере: значение и формула, которая его даёт, с обратной связью о том, что исправить. Также в упражнении есть 4 вопроса с выбором ответа — их проверяет та же кнопка «Проверить».

Как решить

  • J2=SUMIFS($F$2:$F$241,$B$2:$B$241,">="&H2,$B$2:$B$241,"<="&I2)

SUMIFS adds the revenue of the rows that pass every condition: the order date must be on or after the first day of the month and on or before its last day, so each row of the block collects one month. Those twelve totals are what a chart of the year plots. For the questions: change over time is a line chart (months in order from left to right); one total split into parts is a pie or a 100% stacked column; long category names fit a bar chart; and two numbers per order, units and revenue, belong on a scatter chart.

In Excel, select the data and use Insert → Recommended Charts to see what Excel suggests for that range (Windows and Mac); to pick the type yourself, use the All Charts tab of that dialog on Windows or the chart buttons on the Insert tab on a Mac. For an existing chart, Chart Design → Change Chart Type swaps the type (in Excel 2016–2019 for Windows the tab is called Design, under Chart Tools). Pitfall 1: ">"&H2 drops the orders dated on the 1st (9 of the 12 months here have one) and "<"&I2 those on the last day (9 months). Pitfall 2: a pie needs one series of positive parts of one whole; with more than about seven slices it gets hard to read, and it can never show order or a trend. Pitfall 3: Excel draws the first row of the data at the bottom of a bar chart; sort the table ascending, or tick Categories in reverse order under Format Axis, to put the largest bar on top. A PivotTable with the dates grouped by Months gives the same totals; in Excel 365, =SUMIFS($F$2:$F$241,$B$2:$B$241,">="&H2:H13,$B$2:$B$241,"<="&I2:I13) in J2 spills all twelve months from one formula.

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

  • =SUMPRODUCT(($B$2:$B$241>=H2)*($B$2:$B$241<=I2)*$F$2:$F$241) — the array form, works in every version
  • =SUMIFS($F$2:$F$241,$B$2:$B$241,">="&H2,$B$2:$B$241,"<"&EDATE(H2,1)) — no last-day column needed: before the 1st of the next month
  • =SUM(FILTER($F$2:$F$241,($B$2:$B$241>=H2)*($B$2:$B$241<=I2))) — Excel 365 only

Ответы на вопросы

  1. Вопрос 1. The owner asks: "Did revenue climb through 2026, or did it just bounce around?" Which chart type answers that best?

    Ответ: Line chart, months from left to right

    A trend is change over time: a line chart keeps the months in order along the horizontal axis and joins them, so a climb shows as a rising line. A pie or a doughnut only shows each month's share of the year, with the months going round in a circle, so a climb does not show; a bar chart runs time from bottom to top instead of left to right.

  2. Вопрос 2. The owner also wants to see how revenue splits across the four sales channels. Which of these charts show each channel as a share of a whole? Select all that apply.

    Ответы: A — the pie chart и B — the 100% stacked column chart

    A pie splits one total, here the year's revenue, into slices, and a 100% stacked column splits every column, here each quarter, into shares that add up to 100%. The line and the clustered columns show amounts in dollars: good for comparing channels or following one through the year, but the reader has to add up the parts to see a share.

  3. Вопрос 3. Units sold per product: in the column chart below, the long product names are cut off. Which chart type keeps every name readable?

    Ответ: Bar chart (horizontal bars)

    A bar chart writes the names to the left of horizontal bars, where a long name gets the whole width of the label area; under a column chart each name only gets the width of its own column slot, so Excel slants it or cuts it off. Line and area charts put the names under the horizontal axis just like columns, so they are cut off the same way. Bars also suit a ranking: sorted data puts the longest bar at one end.

  4. Вопрос 4. Last question: "Do orders with more units also bring in more revenue, and how close is that link?" Every order gives you two numbers, units and revenue. Which chart type shows that?

    Ответ: Scatter (XY) chart, one dot per order

    A scatter chart puts one measure on each axis, units across and revenue up, and draws one dot per order, so a cloud that rises to the right shows the link and unusual orders stand out. Two lines or two column series only show both measures side by side, order after order.

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

Typing ">"&H2 or "<"&I2 instead of ">="&H2 and "<="&I2: the orders dated on the 1st or on the last day of a month drop out, and 9 or more of the twelve monthly totals come out too low.

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

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

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

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

  • Excel test in 7 days, План уровня 1День 5
  • Excel for Administrative AssistantsДень 5
  • Learn Excel From ScratchДень 9

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

  • Read a clustered column chart of revenue by regionНачальный, ~6 мин
  • Pivot without a pivot: revenue by category and month with SUMIFSСредний, ~10 мин
  • Sales by region and product with SUMIFSСредний, ~7 мин

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

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

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

Read a clustered column chart of revenue by region