Eine Liebeserklärung an MS Excel
Excelschulung. Wir üben SUMMEWENN.
Ein Teilnehmer fragt, warum seine Formel ein anderes Ergebnis liefert als meine. Ich schaue es mir an:
Die Formel:
=SUMMEWENN(D2:D51;"Spanien";I:I)Klar: die erste Zelle der Spalte I ist I1. Sie wird verwendet, wenn in D2 der korrekte Wert (hier: „Spanien“) steht. Somit werden „versetzte“ Werte geholt.
Amüsante Frage. Warum die Funktion SUMMEWENN denn falsch rechne. Ob es einen Bug in Excel in dieser Funktion gebe.
Nicht, dass ich wüsste. Ich schaue es mir an:
Aha – er überprüft mit SUMMEWENN nicht eine Spalte, sondern einen Bereich:
Die ID (hier: 11) wird nicht in der ersten Spalte (der markierten Matrix) gefunden, sondern in der Spalte daneben.
Das Resultat – auch der Suchbereich verschiebt sich (auch wenn nicht ausgewählt – das Ergebnis ist der Wert aus F2.
Wahrscheinlich hatte der Anwender die Funktion SVERWEIS im Kopf als er diese SUMMEWENN erstellte. Ich erkläre ihm, wie es richtig funktioniert:
Geht doch:
Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.
In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.
Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.
Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!
Hallo Herr Martin,
ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.
Soweit so gut.
Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.
Hallo Frau S.
ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.
beispielsweise so
=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)Hallo Herr Martin,
vielen Dank für die pragmatische Lösung, gefällt mir 😉
Guten Tag Herr Martin,
ich hatte schon lange kein Problem mehr mit Excel, sicherlich auch dank Ihrer Kurse, die ich mir in LINDEDIN Learning immer wieder einmal ansehe.
Heute nun habe ich ein Problem, bei dem Sie mir vielleicht helfen können. Wenn es allerdings nicht so nebenher geht, dann können Sie mir dies gerne mitteilen, dann muss ich weiter forschen.
Ich möchte mir hervorgehobene Zellen zählen lassen, genau genommen, möchte ich beim Wahlverfahren D’Hondt sofort sehen, wie viele Sitze hat Liste 1, 2…
Möglicherweise geht es auch nicht mit dem Zählen der hervorgehobenen Zellen sondern anders.
Ich habe es mit SVERWEIS versucht, was leider dann ab der 5. Zeile ein „NV“ brachte – mir leider unverständlich. Mit INDEX und VERGLEICH komme ich auch nicht weiter, weil ich ja keine genaue Zeilen oder Spalenzahl angeben kann. Diese kann ja – je nach Höchstwert – variieren.
Ich sende Ihnen die Datei einmal zu und freue mich auf Ihre Antwort, kann aber auch verstehen, wenn Sie schreiben: Kann ich Ihnen nun leider nicht mitteilen, dauert zu lange…
Trotzdem danke für Ihr offenes Ohr.
Ich wünsche Ihnen noch einen schönen Tag.
Hallo Frau P.,
das ist eine hübsche Fingerübung.
Ein paar Anmerkungen:
1. man kann Farben in Excel nicht zählen. Und wenn ich jetzt von Ihnen die Funktion ZELLE höre – mit Einschränkungen ja. Aber – diese Funktion reagiert nicht auf Formatänderungen. Und: zeigt auch nicht alle Formate an!
2. Ich würde es klassisch mit SUMMEWENN lösen. SVERWEIS, INDEX & co greifen auf mehrere Spalten zu. Mit der neuen Funktion XVERWEIS kann man es auch lösen – aber ich weiß nicht, ob sie diese schon haben. Werfen Sie einen Blick auf meine Lösung in Spalten M:O, bzw. zusammengefasst in Spalte Q.
=KGRÖSSTE($B$6:$E$10;ZEILE(A1))
=SUMMENPRODUKT((M6=$B$6:$E$10)*ZEILE($A$1:$A$5))
=INDEX($A$6:$A$10;N6)
3. Für welches Beispiel/Land verwenden Sie das? Ich lese bei wikipedia, dass noch die Schweiz, Spanien, Portugal, Belgien, Polen und Finnland dieses Verfahren verwenden. Sie wissen, dass dies in Deutschland 1985 durch das Hare-Niemeyer-Verfahren abgelöst wurde. Ich hätte es nämlich fälschlicherweise in meinem Excel-Formelbuch erläutert … und dann gemerkt, das wir so (in Deutschland) gar nicht mehr rechnen.
Hallo Herr Martin,
herzlichen Dank für Ihre schnelle Rückmeldung und Ihren Vorschlag der Berechnung.
Ich habe es jetzt noch einmal nachgebaut und verstanden, obwohl ich niemals auf die Formel gekommen wäre.
Zu Nr. 3:
Ich weiß nur, dass die Hochschule Furtwangen den Vorschlag unserer Justiziarin aus Stuttgart aufgegriffen hat und diesen in ihrer Wahlordnung nun festgelegt hat. Wir haben bisher mit Hare-Niemeyer gewählt. Aber leider soll es auch bei D’Hondt bleiben, obwohl wir eine Hochschule mit drei Standorten sind und unserer kleinster Standort mit diesem Wahlverfahren ernste Schwierigkeiten bekommen könnte. Ich hatte das (als Wahlleitung) angemerkt, es bleibt trotzdem bei D’Hondt.
Ich wünsche Ihnen noch einen schönen Tag und weiterhin viel Erfolg bei Ihrer Arbeit.
Amüsant: Überprüft man, ob in einer leeren Zelle der Wert 0 steht, also =A1=0, so lautet das Ergebnis WAHR. Leer wird als 0 interpretiert. Das gleiche Ergebnis erhält man bei der Funktion SUMMENPRODUKT:
=SUMMENPRODUKT((B2:B17>=0)*1)
Die leeren Zellen werden als 0 interpretiert. Anders dagegen ZÄHLENWENN, SUMMEWENN & co – dort ist 0 etwas anderes, wie
=ZÄHLENWENN(B2:B17;“>=0″)
zeigt:
Danke an Dominic Dauphin für diesen Hinweis.
Excelschulung. Eine Teilnehmerin beschwert sich:
ZÄHLENWENN und SUMMEWENN & co denken: „ich suche WO – WAS“
VERGLEICH und SVERWEIS verlangen: „ich suche WAS – WO“.
Hätte man das nicht auf die gleiche Art strukturieren können?
Tja!
Heute in der Excelschulung. Ich zeige den Teilnehmer das „Leerzeichenproblem“: Manchmal geben Anwender und Anwenderinnen am Ende eines Textes Leerzeichen ein, das man nicht sichtbar machen kann. Das ist fatal beim Sortieren, bei Pivottabellen, die Funktionen WENN, ZÄHLENWENN, SUMMEWENN rechnen vermeintlich falsch … Man kann solche Leerzeichen mit der Funktion RECHTS ausfindig machen, man kann sie mit GLÄTTEN entfernen, … All das zeige ich. Weise darauf hin, dass man (zugegeben: sehr schlecht!) die Leerzeichen sichtbar machen kann, wenn man die Texte rechtsbündig formatiert:
Eine Teilnehmerin meldet sich und fragt, warum bei ihr die Texte am Ende KEINE Lücke aufweisen, obwohl sie ein Leerzeichen eingefügt hat:
Die Antwort: Wenn am Ende eines Textes sich ein Leerzeichen befindet, wird es unterdrückt, wenn ein Textumbruch eingeschaltet ist !?!
Stefan ist genervt. SUMME kann dreidimensional rechnen. Beispielsweise:
=SUMME(Januar:Dezember!C2:C58)
Leider sind SUMMEWENN, SUMMEWENNS, … nicht 3-d-fähig. Noch nicht einmal ZÄHLENWENN.
Schon mal probiert?
Eine Datei greift mit einer Formel auf eine zweite Datei zu. Kein Problem.
Doch: ist ein Problem – nämlich, wenn es sich bei der Formel um SUMMEWENN, ZÄHLENWENN & co handelt. Dann muss nämlich die Quelldatei offen sein, damit kein Fehler angezeigt wird. Sehr erstaunlich!
Warum rechnet die Funktion ZÄHLENWENN (übrigens auch SUMMEWENN) manchmal nicht?
ZÄHLENWENN rechnet nicht.
Schauen Sie die Funktion genau an. ZÄHLENWENN (und auch SUMMEWENN) verlangt als Reihenfolge zuerst WO wird etwas gesucht und anschließend WAS wird gesucht. Wenn Sie die Reihenfolge vertauschen, zählt er, wie oft der Bereich in der einen Zelle vorkommt und liefert kein Ergebnis.
Die Reihenfolge ist hier falsch.
Warum ermittelt die Funktion ZÄhLENWENN (und SUMMEWENN) nicht die richtige Zahl?
Zählenwenn zählt falsch ?!
Man muss sich die einzelnen Daten ansehen. In der Bearbeitungsleiste stellt man fest, dass in einigen der Texte noch weitere Informationen („München“) stehen (sie wurden übrigens weiß formatiert). Man hätte auch durch ein Ändern der Ausrichtung auf rechtsbündig feststellen können, dass hinter dem angezeigten Text noch etwas steht.
Die Bearbeitungsleiste liefert das Ergebnis
Oder man formatiert die Zellen rechtsbündig.
Übrigens: =ZÄHLENWENN(D2:D10;“Rene Martin*“) hätte die korrekte Anzahl ermittelt.