Andreas Unkelbach
Werbung
Steuersoftware für das Steuerjahr 2018

Lexware TAXMAN 2019 (für das Steuerjahr 2018) *

WISO steuer:Sparbuch 2019 (für Steuerjahr 2018) *

Buhl Data Service tax 2019 (für Steuerjahr 2018) *

Siehe auch "Steuererklärung für das laufende Geschäftsjahr sowie Finanzen, Nebentätigkeit, Selbstständigkeit und VG Wort Vergütung".
* Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.


Samstag, 1. September 2018
12:12 Uhr

Excel Summen über gefilterte Werte oder die Formel Teilergebnis sowie Summe über ausgefilterte Werte einer Liste

Manchmal ist es in Excel tatsächlich eine Frage, einfach die passende Formel zu finden um mit dieser zu arbeiten. Während schon der Artikel "Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA" hier eine selten genutzte Formel für eine bestehende Anfrage genutzt hat hat doch zumindest der Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" die Möglichkeiten der Filterung von Daten ins Spiel gebracht und dabei auch die Frage aufgeworfen, wie denn nun mit gefilterten Daten umgegangen werden soll.

Sofern ich eine Tabelle "Als Tabelle formatiere" (siehe auch Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten") liefert mir Excel automatisch eine Lösung in der Ergebniszeile.

Hier wird in der Ergebniszeile automatisch die Formel TEILERGEBNIS verwendet, ohne dass man sich hier auf Anhieb bewust ist, worum es sich dabei eigentlich handelt.

Es stehen in der Ergebniszeile einfach die Möglichkeiten zur Erstellung einer Summe zur Verfügung und ebendiese wird auch ausgewählt.

Ergebniszeile mit Teilergebnis

In der Zelle B6 wird hier direkt die Formel =TEILERGEBNIS(109;[Betrag Summe]) vorgeschlagen um eine Summe über die Spalte zu ziehen.
 

Summe und Summewenn bei Filterfunktionen

Eine der ersten Formeln in Excel (mal abgesehen vom direkten Rechnen mit den einzelnen Zellen wie =B2+B3+B4+B5) ist die Formel SUMME um eine Summe über einen Zellenbereich zu ziehen. Diese Formel hat jedoch einen gewissen Nachteil, da sie immer eine Summe über den Zellenbereich zieht unabhängig davon, ob nun alle Daten angezeigt werden oder nicht. Sofern ich in der Tabelle CAESAR filtere würde mir eine Formel SUMME(B2:B5) weiterhin als Ergebnis 40 liefern, während die Formel Teilergebnis dann tatsächlich nur Anton, Berta und Detlef mit jeweils 10 zu 30 addieren würde.

Auf der anderen Seite kann dieses auch direkt gewünscht sein, da wie in oberen Beispiel die als "Nicht bezahlt" markierten Personen auch weiterhin über die Summenformeln wie SUMMEWENN oder SUMME weiterhin berücksichtigt werden.

Mit gefilterten Werten ein "Teilergebnis" als Summe berechnen


Im Formeltext (sofern man nicht einfach die Auswahl wählt) ist die Formel wie folgt aufgebaut:

TEILERGEBNIS(   Funktion ; Bezug ; [Bezug])

Positiv fällt schon einmal auf, dass hier mehr als ein Bezug möglcih ist, so dass ich auch mehrere Zellenbereiche auswerten kann. Die Frage ist nun nur, was es mit der Funktion auf sich hat.

Hier ist dann tatsächlich die Formelhilfe von Excel notwendig, die leider nur über den Formelassistenten verlinkt ist.

Zusammengefasst gibt es insgesamt 11 Funktionen die anhand von Nummern direkt angesprochen werden. Dabei berücksichtigen die Funktionen 1 bis 11 auch ausgeblendete Werte (die man manuell ausblendet) und die Formel 101 bis 111 ignoriert ausgeblendete Werte. Ausgefilterte Werte ignorieren dafür beide Formelfunktionen.

In der "als Tabelle formatierten" Datentabelle kann in der Ergebniszeile die einzelnen Funktionen direkt ausgewählt werden, dabei werden tatsächlich die 101 bis 109 Funktionen vorgeschlagen, womit ausgblendete Werte auch für die Summe oder die gewählte Funktion ausgeblendet bleiben.

Zu den wichtigsten Funktionen gehören 1,101 für den Mittelwert, 3,103 für Anzahl2 (nicht leere Zellen) oder auch 9,109 für die Summe.

Im Rahmen einer Excel-Schulung würde ich nicht auf die direkte Eingabe der Formel TEILERGEBNIS eingehen, da man sich tatsächlich die Funktion 101 oder 109 merken muss, aber sofern die Grundtabellen als Tabellen formatiert werden ist der Umgang damit gleich um ein vielfaches leichter
.

Summe über rausgefilterte Werte einer Liste

Tatsächlich lassen sich für eine bestimmte Fragestellung die Formeln SUMME und TEILERGEBNIS ebenfalls kombinieren.

Wenn eine Summe über ausgefilterte Werte erhoben werden sollen bietet sich eine Kombination der Formeln an.

Im Beispiel:

=SUMME(B2:B5) - TEILERGEBNIS(109;B2:B5)

Damit werden tatsächlich nur die ausgefilterten Werte summiert, da die Funktion SUMME weiterhin alle Werte umfasst und das TEILERGEBNIS nur die gefilterten Werte erfasst.

Fazit

Manchmal sind es tatsächlich einfache Formeln die den Alltag erleichtern aber bei der Summe an Funktionen und Formeln innerhalb von Excel sehr schnell untergehen können und die nicht so ohne weiteres besser wird dadurch, dass es mit neuen Office-Versionen auch immer neue Formeln und Funktionien gibt. Dieses ist auch einer der Gründe warum ich immer wieder gerne Blogartikel lese in denen auch Grundlagen näher vorgestellt werden.

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

Keine Kommentare - - Office

Artikel datenschutzfreundlich teilen

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


Keine Kommentare

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 - 2018 Andreas Unkelbach
Gießener Straße 75,35396 Gießen,Germany
andreas.unkelbach@posteo.de
Andreas Unkelbach

Stichwortverzeichnis
(Tagcloud)


Aktuelle Infos (Abo)

Facebook Twitter Google+

»Schnelleinstieg ins SAP Controlling (CO)« und »Berichtswesen im SAP ® ERP Controlling«
Privates

Kaffeekasse 📖 Wunschliste