Über diese Übung
Ein Datensatz mit 240 Zeilen auf einem Tabellenblatt, eine Antwortzelle, etwa 9 Minuten. Bewertet in Ihrem Browser: der Wert und die Formel dahinter, mit Feedback, das die Korrektur benennt. Dazu kommen 4 Multiple-Choice-Fragen, die ebenfalls mit „Prüfen“ bewertet werden.
So lösen Sie die Aufgabe
- 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.
Andere Wege, die funktionieren
- =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
Antworten auf die Fragen
Frage 1. The owner asks: "Did revenue climb through 2026, or did it just bounce around?" Which chart type answers that best?
Antwort: 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.
Frage 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.
Antworten: A — the pie chart und 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.
Frage 3. Units sold per product: in the column chart below, the long product names are cut off. Which chart type keeps every name readable?
Antwort: 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.
Frage 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?
Antwort: 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.
Häufige Fehler
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.
So bewerten wir
Jede Formel, die das richtige Ergebnis liefert, wird akzeptiert, außer die Aufgabe nennt eine Funktion: Dann bringt eine andere Funktion die halbe Punktzahl. Wir vergleichen Werte mit einer kleinen Toleranz und ignorieren bei Text Groß- und Kleinschreibung sowie Leerzeichen. Eine richtige Zahl, die statt einer Formel von Hand eingetippt wurde, bringt die halbe Punktzahl. Aufgaben mit mehreren Zellen bringen die volle Punktzahl, wenn 100% der Zellen stimmen, und die halbe ab 50%. Wo es darauf ankommt, ändern wir außerdem eine Eingabezelle in einer Kopie Ihrer Tabelle und prüfen, ob Ihre Formel mitzieht.
Antworten nach den Regeln von Excel 365 berechnet und am 20. November 2026 in unserem Bewertungssystem geprüft. Verfasst von GridBelt team.