Andreas Unkelbach
Werbung
Dropbox Cloudspeicher mit Synchronisation


Mittwoch, 9. Mai 2018
17:44 Uhr

SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen

Wie schon am Ende im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" angekündigt sind die Verweisfunktionen (SVERWEIS oder auch INDEX,VERGLEICH) eingeschränkt wenn mehr als eine Übereinstimmung übernommen werden soll.

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:

Tabelle T_Hochrecnung

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.

SUMMEWENN Fehler

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.

Summe als Matrixformel

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.

Fehler in Wert

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:

Berichtszeitraum

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.

Summewenn mit Bereichsauswahl

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])

Summewenn mit mehrere Spalten als Ergebnis

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 :-)

Hinweis: Aktuelle Buchempfehlungen besonders SAP Fachbücher sind unter Buchempfehlungen inklusive ausführlicher Rezenssionen und Bestellmöglichkeit zu finden.
Espresso Tutorial - die digitale SAP Bibliothek

Tags: Excel

3 Kommentare - - Office

Artikel datenschutzfreundlich teilen

🌎 Facebook 🌎 Twitter
Diesen und weitere Texte von finden Sie auf http://www.andreas-unkelbach.de


Kommentare

Anonym am 7.8.2018 um 10:59 Uhr
Danke!


Michael am 27.1.2019 um 15:41 Uhr
Hallo, auch ich nutze excelformeln.de und habe dort viel gelernt. Matrixxformeln sind mächtig, habe aber den genannten Nachteil, dass man bei Änderungen immer an die korrekte Eingabe per strg+enter denken muss. Abhilfe schaft in vielen Fällen die Verwendung von
Summenprodukt. Im hier vorgestellten Beispiel würde es funktionieren, da die Multiplikation mit Wahrheitswerten ein Paradebeispiel dafür ist, wie aus der Matrixformel eine "normale", dynamische Formel wird. Viele Grüße, Michael


Andreas Unkelbach Homepage am 27.1.2019 um 16:02 Uhr
Hallo Michael,

Tatsächlich ist hier die Formel SUMMEPRODUKT eine gute Lösung auch weil diese ebenfalls mit Matrizen ohne dabei als Matrixformel maskiert zu werden arbeitet.

Gerade in Excel führen zum Glück mehrere Wege zum Ziel, so dass auch die SUMMEPRODUKT hier passend wäre.

Allerdings bleibt der Nachteil, dass auch diese in den Stammdaten zum Auftrag beim geänderten Berichtszeitraum angepasst werden muss.

Hier bin ich für einen anderen Bericht noch am Grübeln, wie dieses über einen Parameter wie Berichtsperiode zu steuern wäre.

Vermutlich würde ich hier ebenfalls noch einmal die Wahl der Formel überdenken.

Vielen Dank für den Hinweis

Andreas Unkelbach



Auch kommentieren?


Beim Versenden eines Kommentars wird mir ihre IP mitgeteilt. Diese wird jedoch nicht dauerhaft gespeichert; die angegebene E-Mail wird nicht veröffentlicht: beim Versenden als "Normaler Kommentar" ist die Angabe eines Namen erforderlich, gerne kann hier auch ein Pseudonyme oder anonyme Angaben gemacht werden (siehe auch Kommentare und Beiträge in der Datenschutzerklärung).

Eine Rückmeldung ist entweder per Schnellkommentar oder (weiter unten) als normalen Kommentar möglich. Eine persönliche Rückmeldung (gerne auch Fragen zum Thema) würde mich sehr freuen.

Schnellkommentar (Kurzes Feedback, ausführliche Kommentare bitte unten als normaler Kommentar)

Name (sofern kein Name angegeben wird erscheint Anonym):



Ich nutze zum Schutz vor Spam-Kommentaren (reine Werbeeinträge) eine Wortliste, so dass diese Kommentare nicht veröffentlicht werden. Sollte ihr Kommentar nicht direkt veröffentlicht werden, kann dieses an einen entsprechenden Filter liegen.

Im Zweifel besteht auch immer die Möglichkeit eine Mail zu schreiben oder die sozialen Medien zu nutzen. Meine Kontaktdaten finden Sie auf »Über mich«. Ansonsten antworte ich tatsächlich sehr gerne auf Kommentare und freue mich auf einen spannenden Austausch.

Normaler Kommentar

Name:

E-Mail (wird nicht veröffentlicht und ist nicht erforderlich):

Homepage:

Kommentar:





* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.
Hinauf




Werbung



Logo Andreas-Unkelbach.de
© 2004 - 2019 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter XING

Amazon Librarything

Schnelleinstieg ins SAP-Controlling (CO) von Martin Munzel und Andreas Unkelbach – 2., erweiterte Auflage
Privates

Kaffeekasse 📖 Wunschliste