Getagged: SUMME

Excel-Grundkurs – Einstieg in Funktionen

In der siebten Folge des neuen Excel-Grundkurses gibt es einen Einstieg in die Verwendung von Funktionen. Exemplarisch an der wohl meistgenutzten Funktion SUMME.

Ich zeige aber auch die Funktionen MITTELWERT, MIN, MAX, KKLEINSTE, KGRÖSSTE, ANZAHL, ANZAHL2, ANZAHLLEEREZELLEN, HEUTE und PI.

In der Übungsdatei gibt es aber auch eine Übersicht über 490 Funktionen, von denen manche auch nicht in der Excel-Funktionsübersicht gelistet sind.

Link zum Video: https://youtu.be/CiJB4Dy6kW8

Download der Datei:

Die Datei kann im Rahmen der Creative-Common-Lizenz CC BY-ND 4.0 verwendet werden. Mehr Infos dazu: https://creativecommons.org/licenses/by-nd/4.0/deed.de

Viel Erfolg beim Erlernen / Vertiefen von Excel
Andreas

Bisherige Beiträge zu dieser Reihe:
Folgen 1-3 https://thehosblog.com/2020/11/17/neuer-excel-grundkurs/
Folge 4 https://thehosblog.com/2020/11/18/excel-grundkurs-autoausfullen/
Folge 5 https://thehosblog.com/2020/11/19/excel-grundkurs-bezuge/
Folge 6 https://thehosblog.com/2020/11/20/excel-grundkurs-aufbau-von-formeln/
Folge 7 https://thehosblog.com/2020/11/23/excel-grundkurs-einstieg-in-funktionen/
Folgen 8 und 9: https://thehosblog.com/2020/11/26/excel-grundkurs-zahlenformat-wahrung/
Folge 10: https://thehosblog.com/2020/11/28/excel-grundkurs-die-funktion-runden/
Folge 11: https://thehosblog.com/2020/11/30/excel-grundkurs-tabellen/
Folge 12: https://thehosblog.com/2020/12/14/excel-grundkurs-drucken-und-seitenlayout/

Excel-Grundkurs – Aufbau von Formeln

In der sechsten Folge des Excel-Grundkurses geht es um die Verwendung der Grundrechenarten und weiterer Berechnungsarten in Excel: Potenz, Wurzel, Prozent und Bruch. Mehr erfahrt ihr im Video.

Link zum Video: https://youtu.be/hnPoCET6RAo

Datei zum Download:

Die Datei kann im Rahmen der Creative-Common-Lizenz CC BY-ND 4.0 verwendet werden. Mehr Infos dazu: https://creativecommons.org/licenses/by-nd/4.0/deed.de

Vielen Dank für die super Resonanz unterhalb der Videos

Andreas

Bisherige Beiträge zu dieser Reihe:
Folgen 1-3 https://thehosblog.com/2020/11/17/neuer-excel-grundkurs/
Folge 4 https://thehosblog.com/2020/11/18/excel-grundkurs-autoausfullen/
Folge 5 https://thehosblog.com/2020/11/19/excel-grundkurs-bezuge/
Folge 6 https://thehosblog.com/2020/11/20/excel-grundkurs-aufbau-von-formeln/
Folge 7 https://thehosblog.com/2020/11/23/excel-grundkurs-einstieg-in-funktionen/
Folgen 8 und 9: https://thehosblog.com/2020/11/26/excel-grundkurs-zahlenformat-wahrung/
Folge 10: https://thehosblog.com/2020/11/28/excel-grundkurs-die-funktion-runden/
Folge 11: https://thehosblog.com/2020/11/30/excel-grundkurs-tabellen/
Folge 12: https://thehosblog.com/2020/12/14/excel-grundkurs-drucken-und-seitenlayout/

Excel – Dynamischen Bereich für Funktionen mit INDEX ermitteln

Aus einer Matrix heraus soll nicht eine Zelle über INDEX ermittelt werden, sondern ein ganzer Bereich. Diesen Bereich kann man natürlich auch mit BEREICH.VERSCHIEBEN identifizieren. Aber wie wäre es mit einem Spezialfall der INDEX-Funktion?

E385

=SUMME(INDEX(E2:H7;0;VERGLEICH(B1;E1:H1;0)))

Summe ermittelt die Summe über einen Bereich.
E2:H7 ist der Bereich der Matrix, der alle Werte beinhaltet.
Mit Vergleich wird der Wert B1 in den Überschriften oberhalb der Matrix gesucht. Ergebnis ist die Position, dies wird als Spaltennummer für die Matrix genutzt.
Mit dem Parameter 0 in INDEX wird gesagt, dass alle Werte aller Zeilen der gewünschten Spalte genommen werden sollen.

Übungsdatei zum Download:
E385_Index_Thehos

Hier geht es zum Video:

Excel – SUMME über Bereich – AGGREGAT und INDEX

Es soll die Summe eines Bereichs gebildet werden. Der Bereich wird allerdings über zwei Markierungen neben der Tabelle definiert. Von der Zeile der ersten x-Markierung bis zur Zeile der zweiten x-Markierung soll summiert werden. Der Clou ist also, dass sich der Bereich für die Summenfunktion dynamisch zusammensetzen soll. Statt aber nun zum Beispiel =SUMME(B7:B12) einzugeben, werden die Zeilen für B7, also 7, und B12, also Zeile 12, über die Funktion INDEX ermittelt.

E361

=SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1))

Dabei muss die Position, also die Zeile, des oberen x-Wertes ermittelt werden. Dies geschieht durch die Funktion: AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1). Nur in den Zellen, in denen ein x steht, wird auch durch WAHR geteilt – also in diesem Fall durch 1. Es werden also alle Zeilennummern des Bereichs genommen und durch 1 oder 0 geteilt. Von diesen Zeilennummern nimmt AGGREGAT(15;6;Array;1) die kleinste. Im Beispiel resultiert die Zahl 7.

Die Zeile des unteren x-Wertes wird durch AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1) ermittelt. Auch hier wird geprüft, ob ein x in einer der Zellen steht. Falls dies mit WAHR beantwortet werden kann, wird mit WAHR, also mit 1 multipliziert. Das Ergebnis ist eine List von Zeilennummern, die entweder mit 1 oder mit 0 bei FALSCH multipliziert werden. Von diesen Werten nimmt AGGREGAT(14;4;Array;1) den größten Wert. Im Beispiel resultiert die Zahl 12.

Statt nun B7:B12 in die Funktion SUMME als Bereich einzugeben, werden die Koordinaten über INDEX ermittelt. INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1) führt also zu INDEX(B:B;7;1) und dies wiederum zur Zelle B7. Es wird die siebte Zelle aus Spalte B genommen. Das gleiche Prinzip gilt für B12. Die beiden INDEX-Funktionen werden über einen Doppelpunkt verbunden, so dass im Endeffekt B7:B12 resultiert.

Sollte kein x gesetzt sein, resultiert die Fehlermeldung #Zahl!
Hier könnte man die Formel noch einmal um die Funktion WENNFEHLER ergänzen:

=WENNFEHLER(SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1));"")

Die Datei zum Download:
E361_SUMME_AGGREGAT_BEREICH

Das Video bei YouTube: