17:44 Uhr
SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen
Hier bietet sich dann tatsächlich die Formel SUMMEWENN an.
Anhand des Syntax
=summewenn(BEREICH;SUCHKRITERIEN;SUMMEBEREICH)
hatte ich die Hoffnung, dass als Summenbereich tatsächlich mehr als eine Spalte genommen werden kann und so habe ich meine Hochrechnung als Tabelle formatiert und als Hochrecnung benannt.
Um ein einfaches Beispiel zu haben sieht diese Tabelle wie folgt aus:
Hier sind für einzelne Innenaufträge mehrere Hochrechnungen erfasst. Da es sich dabei aber um eine Hochrechnung von Personalkosten handelt kann ein Innenauftrag auch mehrfach vorkommen. Im Beispiel bekommt hier Andreas sowohl in Zeile 3 als auch Zeile 8 entsprechende Zahlungen. Ziel der Auswertung ist nun die zu erwarteten Kosten für Mai bis Dezember für den Innenauftrag Andreas zu erhalten.
Mein erster Versuch war hier tatsächlich statt SVERWEIS oder INDEX die Formel SUMMEWENN.
=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[[Mai]:[Dezember]])
Hier sollte eine Summe über die Spalten Mai bis Dezember für den Eintrag Andreas beziehungsweise das Suchkriterium in der Zelle D3 erstellt werden.
Das Ergebnis von 10, wie in folgender Abbildung zu sehen war dann aber enttäuschend.
Offensichtlich kann der Summenbereich nur eine Spalte umfassen.
Zum Glück gibt es aber die Seite excelformeln.de auf der dann eine Lösung für mein Problem zu finden war.
Mal wieder eine Matrixfunktion, die im Beitrag "Nach Suchkriterium suchen und mehrere Spalten summieren (193)" erklärt wird.
In folgender Abbildung ist dann auch tatsächlich der zutreffende Wert von 80 berechnet worden.
Wichtig ist dabei, dass die Formel
=SUMME((T_Hochrechnung[Innenauftrag]=D3)*T_Hochrechnung[[Mai]:[Dezember]])
durch die Tastenkombination STRG + SHIFT und ENTER
als Matrixformel gespeichert wird.
Der Trick funktioniert dabei wie folgt:
Im ersten Teil der Summenfunktion wird geprüft ob die Spalte Innenauftrag mit der Zelle D3 übereinstimmt. Ist dieses der Fall ist das Ergebnis 1 (WAHR) sonst 0 (Falsch) dieses wird dann mit den Werten der Spalte Mai bis Dezember multipliziert, so dass hierdurch ein Ergebnis erzielt wird.
Der Nachteil dieser Vorgehensweise ist jedoch, dass man bei Änderung des Berichtszeitraum hier ebenfalls daran denken muss, dass es sich um eine Matrixformel handelt und diese mit der Tastenkombination angepasst werden muss, so dass aus Versehen diese, sofern nicht als Matrixformel abgeschlossen, einen Fehler hervorruft.
Wenn die Formel nicht als Matrixformel abgeschlossen wird ist das Ergebnis #WERT! (Fehler in Wert) und das mag auch niemand.
Nur als Matrixformel erscheint die {} im Formelfeld (ein manuelles Eingeben dieser Klammer hilft leider auch nichts).
Um etwaige Fehler zu vermeiden habe ich mich dazu entschlossen die Tabelle Hochrechnung um eine weitere Spalte zu erweitern:
Das schöne an "Als Tabelle formatierte" Tabellen ist ja, dass solche Formeln auch automatisch für jede Zeile mit ausgefüllt werden.
Damit habe ich also für jeden Innenauftrag die Formel
=SUMME(T_Hochrechnung[@[Mai]:[Dezember]])
und kann nun mit Summewenn wieder arbeiten, da ich hier ja nur nach einer Spalte suchen muss.
Durch die Unterstützung von Excel kann ich sogar direkt die Spalte Berichtszeitraum auswählen und erhalte so die Formel:
=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[Berichtszeitraum])
Diese Variante ist dann tatsächlich noch etwas einfacher als eine Matrixformel, auch wenn eine solche natürlich etwas cleverer ist.
Insgesamt erleichtert der Umgang mit der Funktion "Als Tabelle formatieren", wie im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten", an vielen Stellen der Zugang auch zu komplexeren Formeln und Funktionen :-)
Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.
Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden.
Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.
ein Angebot von Espresso Tutorials
unkelbach.link/et.books/
unkelbach.link/et.reportpainter/
unkelbach.link/et.migrationscockpit/
Diesen Artikel zitieren:
Unkelbach, Andreas: »SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen« in Andreas Unkelbach Blog (ISSN: 2701-6242) vom 9.5.2018, Online-Publikation: https://www.andreas-unkelbach.de/blog/?go=show&id=933 (Abgerufen am 19.5.2024)
5 Kommentare - Permalink - Office
Artikel datenschutzfreundlich teilen
🌎 Facebook 🌎 Twitter 🌎 LinkedIn