Heute gibt’s mal wieder ein paar Tipps aus der klassischen Excel-Formelküche. Es geht eigentlich um ganz einfache Fragen: Die Bestimmung der Anzahl von Werten in einer Liste und die Summe daraus. Wer jetzt gelangweilt gähnt und schon ein Fingerzucken über der linken Maustaste verspürt, für den habe ich noch eine kleine Zusatzinformation:
Gezählt und summiert werden sollen nämlich nur die geraden oder nur die ungeraden Werte in der Liste. Wenn du jetzt ein wenig ins Grübeln kommst, solltest du vielleicht doch weiterlesen…
Und so geht’s:
Zählen von ungeraden (geraden) Werten
Ich habe eine kleine Liste mit zwanzig zufälligen Werten zwischen 10 und 99 vorbereitet:
Beispielliste mit 20 Zufallszahlen
Es gibt – wie fast bei jedem Excel-Problem – verschiedene Möglichkeiten, um die ungeraden (bzw. die geraden) Elemente zu zählen.
Variante 1
Mit Hilfe der ISTUNGERADE-Funktion lässt sich für einen einzelnen Wert bestimmen, ob er ungerade ist. Die Funktion liefert den Wahrheitswert WAHR zurück, wenn die Zahl ungerade ist und FALSCH, wenn sie gerade ist:
Variante 1: Die Funktion ISTUNGERADE
Da sich Wahrheitswerte nicht so gut addieren lassen, stellen wir der Funktion doppelte Minuszeichen voraus. Dadurch wird aus WAHR die Zahl 1 und aus FALSCH die Zahl 0 (die Multiplikation mit 1 liefert übrigens das gleiche Ergebnis):
Erweiterung der Variante 1
Am Ende der Liste noch die Summenformel eingetragen und schon haben wir die Anzahl der ungeraden Werte.
Variante 2
Exakt das gleiche Ergebnis lässt sich mit der REST-Funktion bestimmen:
=REST(Zahl;Divisor)
Wenn eine ungerade Zahl durch 2 geteilt wird, bleibt als Rest immer der Wert 1 übrig:
Variante 2: Die Funktion REST
Der Nachteil bei diesen beiden Varianten ist aber offensichtlich: Wir müssen mit einer Hilfsspalte arbeiten.
Variante 3
Manchmal geht es ohne Hilfsspalten überhaupt nicht und manchmal sind sie zumindest hilfreich, um sich an eine elegantere Lösung heranzutasten. So wie in diesem Fall.
Jetzt kombinieren wir nämlich die REST-Funktion mit einer häufig verkannten Geheimwaffe in Excel, um ganz ohne Hilfsspalte auszukommen. Die Rede ist von der Funktion SUMMENPRODUKT:
=SUMMENPRODUKT(REST(B2:B21;2))
Damit wird einfach die Summe aller Werte gebildet, die die innere REST-Funktion ermittelt:
Variante 3 (ohne Hilfsspalte): SUMMENPRODUKT
Und wer anstelle der ungeraden lieber die geraden Werte zählen möchte, kommt mit folgender kleinen Abwandlung zum Ziel:
=SUMMENPRODUKT(--(REST(B2:B21;2)=0))
Zur Erklärung:
Bei der REST-Funktion wird einfach geprüft, ob als Rest der Division mit 2 der Wert 0 (Null) herauskommt. Das Ergebnis ist WAHR oder FALSCH. Und mit den vorangestellten doppelten Minuszeichen wird daraus wieder 1 oder 0.
(Falls Du etwas mehr über die SUMMENPRODUKT-Funktion erfahren möchtest, empfehle ich dir die beiden Artikel von Gerhard Pundt: hier und hier)
Addieren von ungeraden (geraden) Werten
Mit dem eben Gelernten ist der nächste Schritt nicht mehr weit: Nun soll die Summe aller ungeraden Werte berechnet werden.
Dazu muss die SUMMENPRODUKT-Funktion nur um einen Wertebereich erweitert werden:
=SUMMENPRODUKT(B2:B21;REST(B2:B21;2))
Zur Erklärung:
SUMMENPRODUKT multipliziert erst die beiden Wertebereiche sozusagen Zeile für Zeile und addiert dann die Einzelergebnisse. Die REST-Funktion liefert für gerade Zahlen ja den Wert 0 zurück. Und eine gerade Zahl mit Null multipliziert ergibt halt wieder Null. Somit werden nur alle ungeraden Werte addiert, wie im folgenden Bild zu sehen ist:
Summenbildung ohne Hilfsspalte
Analog dazu funktioniert die Summenbildung für gerade Werte:
… und nochmal für gerade Zahlen
Und wieder einmal sieht man, was für eine mächtige und universelle Funktion SUMMENPRODUKT ist.
Fallen dir noch andere Lösungswege ein, um nur ungerade (oder gerade) Werte zu summieren? Dann lass‘ es uns unten in den Kommentaren wissen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Eine wunderbare Zusammenstellung, Excel ist eben vielseitig.
Vielen Dank für die Links im Beitrag.
Hall Martin,
für die Summe der geraden Zahlen funktioniert auch diese Funktion:
=SUMMENPRODUKT(B2:B21;–NICHT(REST(B2:B21;2)))
Gruß von Luschi
aus klein-Paris
Hallo Luschi,
ja, die Umkehrung mit NICHT ist auch eine sehr schöne Lösung. Vielen Dank dafür!
Schöne Grüße,
Martin
Ich habe noch eine weitere Lösung gefunden. Hier die Formeln für die Geraden Zahlen. Komplizierter geht immer:
In Die Zelle C1 kommt die Formel: =WENN(ODER(RECHTS(B1;1)=“0″;RECHTS(B1;1)=“2″;RECHTS(B1;1)=“4″;RECHTS(B1;1)=“6″;RECHTS(B1;1)=“8″);B1;0)
Die wird dann runtergezogen (in unserem Beispiel bis C21). In C22 kommt die Formel: =SUMME(C1:C21).
Zur Erklärung: In der Spalte C wird das letzte Zeichen aus der Spalte B als Text wiedergegeben, und wenn dieser Text 0, 2, 4, 6 oder 8 heißt, dann wird die Zahl aus der Spalte B wiedergegeben, ansonsten der Wert 0. Dies geschieht zeilenweise. Am Ende muß nur die Spalte C aufsummiert werden.
Hallo Hans-Peter,
auch eine interessante Lösung, die gut funktioniert.
Vielen Dank und schöne Grüße,
Martin
Hallo zusammen,
eine Alternative stellt auch die Berechnung via Matrixformel (Bestätigung der Formel mit STRG + SHIFT + ENTER) dar:
={SUMME(WENN(REST(A:A;2)=0;A:A;0))}
SG
Marcel
Hallo Marcel,
ja, die Matrixformel ist auch eine sehr gute Lösung.
Vielen Dank und schöne Grüße,
Martin
Hallo,
bei dem Problem der geraden undungraden Zahlen, habe ich ein Problem: Ich möchte bei einer Tabelle bei geraden Zahlen, dass die Zahl (z. B. in A1) dann in B2 erscheint und zwar durch 2(also „/“), und wenn die Zahl in A1 ungrade ist, soll sie mit (B1=A1*3+1) erscheinen. Leider weiß ich nicht, wie ich die Tabelle erstellen kann, weil es ja einerseits /2 und andererseits *3+1 rechnen muss. Und beides zusammen in einer Formel kann ich leider nicht.
Mit dieser Formel könnte ich das Problem der Collatz-Zahl lösen. Das besagt, dass jede belibige Zahl (vielleicht auch bis 50 Stellen (!) bei dieser Anwendung immer auf „1“ enden muss!? (Beispiel: 1024 512 256 128 64 32 16 8 4 2 1)
Eine Formel wäre sehr hilfreich. Vielen Dank im Voraus.
Freundliche Grüße
Norbert Hoyer
Hallo Norbert,
da hilft eine WENN-Abfrage mit einer Prüfung auf gerade bzw. ungerade:
=WENN(ISTGERADE(A1);A1/2;A1*3+1)
Schöne Grüße,
Martin
Hallo Martin,
wenn es nicht respektlos wäre im normalen Austausch mit anderen, so behalte ich diese Anrede gerne bei. Zunächst einmal herzlichen Dank für diese Unterstützung. Ich lese schon diese Rubrik jahrelang, habe sehr viele Bücher, gerade über Excel, gekauft und weitere Informationen zur Kenntnis genommen. Das Problem mit den geraden und ungeraden Zahlen wollte ich gerne lösen. Mit einer Formel wäre es leichter gewesen. Jetzt, wo ich diese Formel lese, fällt es mir wie Schuppen aus den Haaren: Klar, das ist jetzt viel einfacher geworden. Jetzt muss ich nur noch versuchen, diese Formel so zu erweitern, damit sie bei dem Endpunkt „1“ auch sich sozusagen selbst beendet. Ansonsten zählt sie immer weiter mit „4; 2,1, 4,2, 1“ etc.
Im übrigen noch einmal ein großes Kompliment zu Ihren Berichten und Informationen.
Freundliche Grüße
Norbert Hoyer
Hallo Norbert,
wenn einem Kommentar nicht direkt etwas anderes zu entnehmen ist, verwende ich gerne die Anrede mit dem Vornamen und das „Du“, ohne jemandem dabei zu Nahe treten zu wollen. Ansonsten natürlich gerne auch die formellere Anrede, falls gewünscht.
In jedem Fall vielen Dank für Ihr nettes Feedback zu meinen Artikeln, so etwas freut mich natürlich immer sehr.
Schöne Grüße,
Martin