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 😉
Gestern in der Excelschulung. Ich erkläre den Autofilter. Wir haben eine Liste mit zirka 12.000 Datensätzen. Wir filtern alle Hamburger und Hamburgerinnen:
Zu der gefilterten Liste fügen wir alle Personen hinzu, die in Flensburg wohnen:
Und so machen wir weiter mit Bremen, Husum, Kiel, Buxtehude, Uelzen, Itzehoe, …
Ein Teilnehmer meldet sich und fragt, wie und ob man denn erkennen könne, welche Orte gefiltert seien:
Ich habe eine Weile überlegen müssen. Fährt man mit der Maus über das Filtersymbol, werde alle Filterkriterien im Quickinfo angezeigt:
Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:
„Hallo Rene
zum Thema Datumsberechnungen hätte ich gleich eine Frage:
kann Excel auch Zeiträume erkennen, die sich überschneiden, aber
unterschiedliche Anfangs- und Endzeiten haben?
Also zum Beispiel:
Mitarbeiter A arbeitet vom
01.05.2021 bis 31.08.2021
Mitarbeiter B arbeitet vom
01.06.2021 bis 15.09.2021
In welchem Zeitraum haben
beide gearbeitet
Oder
Von
Bis
Thema
8:00
12:00
Nachdenken
11:30
12:30
Pause
12:30
15:00
Nix tun
Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?
Im ersten Schritt habe ich das visualisiert:
Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:
=WENN(B33<$B$21;0)
Dann die umschließenden Bereiche:
=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)
Und schließlich die überschneidenden Bereiche:
Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)
Analog die zweite Überschneidung:
Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:
=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)
Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:
und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:
Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:
Fall: leere Menge
Fall: ganzer Bereich
Fall: Überschneidung
Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)
2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:
Natürlich hätte man die Bedingungen auch umdrehen können.
Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:
Fall: Anfang <= Oktoberfest Anfang? Ja: Fall a) Ende vor Oktoberfest Anfang? Fall b) Ende nach Oktoberfest Ende? Fall c) Ende zwischen Oktoberfest Anfang und Ende? […]
Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:
Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:
=SEQUENZ(A6-A5+1;1;A5)
Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:
Für die anderen Personen muss diese Formel nur entsprechend angepasst werden. Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus. Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.
Salü
Ernst
PS: Nachtrag:
Allerdings
kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus
Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine
einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge
eingetragen sind.
Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:
Dim xlBereich1 As Range
Dim xlBereich2 As Range
Dim xlSchnittmenge As Range
With ThisWorkbook.Worksheets("Helmut")
Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1),
.Cells(.Range("B3").Value2, 1))
Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1),
.Cells(.Range("B6").Value2, 1))
End With
Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
MsgBox xlSchnittmenge.Cells.Count
Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!
In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.
Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then
Klappt wunderbar.
Fast.
Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then
und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.
Ich prüfe:
der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:
AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:
If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then
Heute in der Excelschulung haben wir festgestellt, dass Kolleginnen mehrere Grautöne verwendet haben, um bestimmte Zeilen zu kennzeichnen.
Wir wollten es bereinigen und alle Grautöne filtern. Schade, dass Excel nicht mehrere Farben filtern kann. So wie bei dem Textfilter ein „dem Filter die aktuelle Auswahl hinzufügen“ möglich ist.
Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:
Eine Spalte ist als Text formatiert:
In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:
Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit
{=ODER(K2=$G$2:$G$22)}
(als Matrixformel) korrekt als unterschiedliche Werte erkannt:
Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:
{=ODER(IDENTISCH(K2;$G$2:$G$22))}
Ebenso übergeht ZÄHLENWENN diesen Unterschied:
Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!
Seltsam – ich bin sicher, dass Kunden aus Hamburg in unserer Liste vorhanden sind – Excel filtert jedoch keine.
Hamburg und Berlin
Der Grund ist ein sprachlicher. Sie sagen zwar, dass Sie alle Hamburger UND Berliner Kunden filtern, Sie müssen jedoch einstellen Hamburg ODER Berlin. In keiner Zelle steht gleichzeitig Hamburg UND Berlin. Das logische UND bedeutet immer „sowohl – als auch“, also gleichzeitig. Beispielsweise: Hamburg und weiblich, oder PLZ >= 10000 und PLZ < 20000.
wird mit einer Fehlermeldung quittiert. Alle Kunden, die entweder Platinum-Mitglieder sind oder jetzt schon mehr als 180 Euro bezahlen, sollen demnächst 20 Euro mehr Jahresbeitrag bezahlen – die übrigen erhalten eine Erhöhung von 10 Euro.
Die Antwort: UND und ODER sind in Excel keine Verknüpfungsoperatoren (wie beispielsweise in Programmiersprachen), sondern Funktionen. Und Funktionen müssen immer VOR den Argumenten geschrieben werden, also so:
