Getagged: SUMMENPRODUKT

Mit XODER Binärzahlen addieren

Mit der Hilfe der Funktionen XODER, SUMMENPRODUKT, TEIL und DEZINBIN kann ich 2 Binärzahlen (hier zweimal 8 Bit) addieren.

728_Thehos_01

Die folgenden Formeln habe ich dafür eingesetzt. Die Funktionen mit geschweiften Klammern (Matrixformeln) müssen mit STRG + SHIFT + ENTER bestätigt werden, also die Klammern nicht manuell eingeben.

728_thehos_02

Hier ist die Übungsdatei: Excel 728 XODER 2

Käufer des USB-Sticks finden die Datei im Ordner E728.

Hier geht es zum YouTube-Video:

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

Excel – Groß- und Kleinbuchstaben getrennt zählen

Auf den Seiten von Andreas Entenmann (Excel MVP) werden herrliche Fragen und Lösungen diskutiert. Eine wirklich geniale Lösung von Thomas Ramel (Excel MVP) möchte ich nicht vorenthalten.

Das Problem… In Spalte B befinden sich kleine und große U’s. Ein großes U steht für einen vollen Tag Urlaub und ein kleines u für einen halben Tag. Wie zählt man nun allein durch die U’s und ohne Hilfsspalte die Anzahl der genommenen Urlaubstage?

461

Die Formel:
=SUMMENPRODUKT(IDENTISCH(Tabelle1[Urlaub];"U")+IDENTISCH(Tabelle1[Urlaub];"u")/2)

IDENTISCH prüft, ob sich der gesuchte Buchstabe in einer Zelle befindet. IDENTISCH unterscheidet Groß- und Kleinschreibung. Bei IDENTISCH resultiert ein WAHR oder FALSCH.

Wenn man WAHR- und FALSCH-Werte addiert, wird ein WAHR als 1 und ein FALSCH als 0 behandelt. Die IDENTISCH werte für das kleine u werden durch 2 geteilt.

SUMMENPRODUKT prüft nun für alle Zellen eines Bereichs zunächst den Inhalt der jeweiligen Formeln und addiert dann die Produkte. Es gibt hier allerdings nichts zu multiplizieren, da SUMMENPRODUKT kein zweites Array enthält. Somit wird nur addiert.

Ein paar alternative Lösungsvorschläge (die bereits genannte ist unser Favorit):
{=SUMME(WENN(IDENTISCH(Tabelle1[Urlaub];"U")=WAHR;1;0))+SUMME(WENN(IDENTISCH(Tabelle1[Urlaub];"u")=WAHR;1;0))/2}
oder
{=summe(wennfehler(--(unicode(Tabelle1[Urlaub])=85);0))+summe(wennfehler(--(unicode(Tabelle1[Urlaub])=117);0))/2}

von Mourad Louha (Excel MVP):
SUMMENPRODUKT(1*(CODE(Tabelle1[Urlaub]&"$")=85)+0,5*(CODE(Tabelle1[Urlaub]&"$")=117))
oder
{=SUMME(WENN(CODE(Tabelle1[Urlaub]&"$")=85;1;0)+WENN(CODE(Tabelle1[Urlaub]&"$")=117;0,5;0))}

Allein an der Vielzahl der Lösungen – und dies ist nur eine Auswahl – kann man erkennen, wie vielfältig Aufgaben in Excel bewältigt werden können.

Die Übungsdatei:
Excel_461_Urlaubstage

Hier geht es zum Video:

Videolink: http://youtu.be/On6vMr64DDw

Excel – Wert über mehrere Suchkriterien finden – SUMMENPRODUKT

Zwei oder mehrere Suchkriterien können, insofern die Kombination der Suchkriterien keine Doppelten Paare erlaubt, genutzt werden, um über die Funktionen INDEX und SUMMENPRODUKT einen Zellinhalt wiederzugeben.

Anbei die Formel, falls der Datenbereich nicht als Tabelle formatiert ist.

434a

Hier die Formel:
=INDEX(A2:A10;SUMMENPRODUKT(ZEILE(A2:A10)-1;--(B2:B10=F2);--(C2:C10=F1));1)

INDEX(A2:A10;Zeile;1) sorgt dafür, dass später ein Wert aus diesem Bereich wiedergegeben wird.

SUMMENPRODUKT(ZEILE(A2:A10)-1;–(B2:B10=F2);–(C2:C10=F1)) ermittelt diese Zeile wie folgt:
Die Arrays aus SUMMENPRODUKT werden mit einander multipliziert. Das wäre zum einen die jeweilige Zeile – 1
ZEILE(A2:A10)-1, das wäre die Prüfung, ob in der jeweiligen passenden Zeile in Spalte B das gewünschte Suchkriterium steht und ob das Suchkriterium auch in Spalte C steht. Das Doppelminus sorgt dafür, dass aus einem WAHR als Ergebnis der Prüfung eine 1 wird. Man hätte hier auch WAHR mit dem Wert Eins multiplizieren können.
Wenn dann also Zeilennummer * 1 * 1 genommen werden kann, haben wir die richtige Zeilennummer gefunden. Bei FALSCH bei einer Prüfung käme entweder Zeilennummer * 0 * 1, Zeilennummer * 1 * 0 oder Zeilennummer * 0 * 0 heraus. In Summe macht das beim SUMMENPRODUKT halt dann nur wirklich den Wert der Zeilennummer.

Sollte der Bereich mit Als Tabelle formatieren oder STRG + T als Tabelle formatiert worden sein, lassen sich die Namen verwenden und die Formel bezieht sich immer auf den kompletten Datenbereich.

434b

Hier ist die Übungsdatei
Excel_434_SUMMENPRODUKT_2_Suchkriterien

Hier geht es zum Video

Excel – Gaußsche Summenformel – Matrixformel

Ich weiß noch genau, wie wir uns direkt bei der Einführung des neuen 10 DM-Scheins den Herrn Gauß angeschaut haben. Und die Geschichte des jungen (rechenfaulen) Schülers Gauß hat uns imponiert.

Bei der Gaußschen Summenformeln werden mit (n+1)*(n/2) oder auch (n²+n)/2 alle Werte von i=1 bis n summiert. Das kann man natürlich sehr schnell für einen beliebigen Wert aus dem natürlichen ganzzahligen Zahlenraum berechnen.

E420

Zur Verdeutlichung, was man mit Matrixformeln und der Funktion AGGREGAT erreichen kann, zeige ich hier aber noch andere Wege auf. F2 beinhaltet z.B. den Wert 10.

{=SUMME(ZEILE(INDIREKT("1:"&F2)))}
und
=AGGREGAT(14;6;SUMME(ZEILE(INDIREKT("1:"&F2)));1)

ZEILE(INDIREKT(„1:“&F2)) wird somit zu ZEILE(INDIREKT(1:10)). Als Array oder durch Abschluss als Matrixformel mit STRG + SHIFT + ENTER wird daraus {1;2;3;4;5;6;7;8;9;10}. In eine SUMME verfrachtet also automatisch die Summe der Werte von 1 bis 10.

AGGREGAT(9;…) erlaubt leider bei der Summe keine Arrays, so dass ich hier erst die Summe bilde – wodurch ein Wert resultiert – und davon dann den kleinsten oder größten Wert nehme (ist ja eh nur einer). AGGREGAT(14) und AGGREGAT(15) erlauben die Verwendung von Arrays ohne Abschluss durch STRG + SHIFT + ENTER.

Ebenso möglich und auch ohne Matrixschreibweise funktioniert hier aber auch die Funktion SUMMENPRODUKT, in der die Angabe von Arrays möglich ist.

=SUMMENPRODUKT(ZEILE(INDIREKT("1:"&F2)))

Hier ist die Übungsdatei:
E420_Gaussformel

Hier geht es zum Video:

Und dem Nachtrag zum SUMMENPRODUKT:

Excel – Bedingten Bereich über INDEX erzeugen – Namen und Matrixformeln

SUMMENPRODUKT, SUMMEWENN, SUMMEWENNS, ZÄHLENWENN und ZÄHLENWENNS errechnen eigentlich genau das, was ich ermitteln möchte. Die bilden nur dann bedingt eine Summe oder zählen die Werte eines Bereichs, wenn ein bestimmtes Kriterium mit den Werten des Bereichs übereinstimmt.

Zusätzlich kann man natürlich noch Matrixformeln vom Typ {=SUMME(WENN(Prüfungen;Bereich;““))} nutzen.

Aber der Bereich an sich lässt sich auch vorab dynamisch auf die gewünschten Kriterien reduzieren und dann darüber die einfache Summe bilden. Also bitte nicht schimpfen, dass es 1000-fach einfacher geht. Hier zeige ich auf, wie man INDEX auch noch nutzen könnte.

Download der Datei:
E387_Index_Thehos

Hier geht es zum YouTube-Video: