Kurznotiert – Excel / Sheets Auswertung (1)

Hallo zusammen,

heute möchte ich euch, aufgrund eines Projektes, an dem ich beteiligt bin, einen neuen Beitrag aus der Rubrik „Kurznotiert“ vorstellen.

Diesmal geht es um eine kleine Auswertmöglichkeit in Microsoft Excel.
Da ich privat Google Workspace nutze, werde ich parallel zu den Funktionen in Microsoft auch die Funktion in Google Sheets vorstellen.

Die Ausgangssituation ist folgende. In einer Datentabelle werden die erstellten Angebote erfasst inkl. des Angebotsstatus und die verkauften Produkte. Ferner wird in einer Spalte die Kalenderwoche notiert, in der das Angebot erstellt wurde. Somit soll eine Auswertung später erstellt werden, aus der ersichtlich ist, welches Angebot sich mit welchen Produkten in welchem Status ist.

Solange man sich im Microsoft Universum befindet, gibt es natürlich auch die Möglichkeit, das Ganze auch per SharePoint und Power Bi auszuwerten. Aufgrund gewisser Restriktionen soll die gesamte Auswertung jedoch in einer Microsoft Excel Tabelle erfolgen.

Die Datentabelle ist wie folgt aufgebaut

KundeKWReferenzStatusHemdKrawatte
A02-2021AN0123Abgerechnet62
B02-2021AN0456Abgerechnet42
C02-2021AN0789Offen82
B02-2021AN0987Offen64
A03-2021AN0654Offen72
A03-2021AN0321Abgerechnet21
C03-2021AN0192Planung23
B04-2021AN0384Offen43
C04-2021AN0576Abgerechnet24
A04-2021AN0320Planung25

Um innerhalb der Tabelle ein Auswahlmenü zu bekommen, habe ich eine “Hilfstabelle” mit den Werten erstellt, welche in der Auswahl zur Verfügung stehen sollen. In diesem Fall nehme ich die Werte aus der Spalte “KW” und “Status” und lösche die doppelten Einträge.

Das entsprechende Filtermenü erstellt man über die Funktion

Microsoft ExcelGoogle Sheets
Daten >>> DatenüberprüfungDaten >>> Datenvalidierung

In beiden Fällen ist nun der entsprechende Zellenbereich auszuwählen.

Microsoft Excel
Google Sheets

Als nächste Aufgabe steht nun aus, dass ich gerne wissen möchte, wie viele Hemden in einem bestimmten Zeitraum verkauft und abgerechnet wurden.

Um die entsprechenden Werte zu erhalten, arbeiten wir hier mit der Funktion “SUMMENPRODUKT”. Ggf. kann es sein, dass in Google Sheets mit dem englischen Begriff „SUMPRODUCT“ gearbeitet werden muss.

WertFormelBeispiel
Auswertung Gesamtzeitraum=SUMMENPRODUKT((Status aus dem Auswahlfeld=Zellenbereich in dem der Status steht)*Einzelne Werte, welche gezählt werden sollen)=SUMMENPRODUKT((D3:D12=D21)*F3:F12)
Auswertung in einer Kalenderwoche=SUMMENPRODUKT((Status aus dem Auswahlfeld=Zellenbereich in dem der Status steht)*(Kalenderwoche im Auswahlfeld, welches betrachtet werden soll=Zellenbereich in dem die Kalenderwoche steht)*Einzelne Werte, welche gezählt werden sollen)=SUMMENPRODUKT((D3:D12=D21)*(B3:B12=B21)*F3:F12)
Auswertung in einem bestimmten Zeitraum=SUMMENPRODUKT((Zellenbereich, der geprüft werden soll=Wert aus dem Auswahlfeld)*(Einzelne Werte, welche gezählt werden sollen)*(Zellenbereich der Kalenderwoche<=Enddatum)*(Zellenbereich der Kalenderwoche<=Startdatum))=SUMMENPRODUKT((D3:D12=D21)*(F3:F12)*(B3:B12<=C21)*(B3:B12>=B21))

Das Ergebnis sieht danach wie folgt aus

Nun kann man in der Übersichtstabelle noch in eine Zelle sich alle Angebotsnummern hintereinander anzeigen lassen. Um dies zu bewerkstelligen, ist sowohl bei Microsoft Excel als auch bei Google Sheets eine sog. Arrayformel notwendig.

In Microsoft Excel würde beispielsweise die Formel so aussehen:

{=TEXTVERKETTEN(" - ";WAHR;WENN(M7:M9=M11;N7:N9;""))}

In Google Sheets wird hierbei die Formel wie folgt erzeugt:

=ARRAYFORMULA(TEXTJOIN(" - ";WAHR;WENN(A3:A12=F27;C3:C12;"")))

In beiden Fällen würde die Anwendung beispielsweise prüfen, ob in der Übersichtstabelle ein bestimmter Kunde aufgeführt ist und dann alle Angebote zusammen in eine Zelle zusammenfassen.

Das Ergebnis sieht dann folgendermaßen aus

Viel Erfolg beim ausprobieren.

Solltet ihr Fragen haben, könnt ihr euch gerne bei mir per Nachricht melden.

Viele Grüße und bleibt gesund

euer Kai

Schreibe einen Kommentar