Microsoft Excel

Herbers Excel/VBA-Archiv

Zählen wenn /oder/und

Betrifft: Zählen wenn /oder/und von: Markus B
Geschrieben am: 22.09.2014 21:21:14

Hallo zusammen!

Ich habe folgende Anforderung, derer ich nicht Herr bzw. Frau werde:

Die Tabelle sieht ungefähr so aus:

In Spalte A stehen Wochentage (eine Schleife immer Montag bis Sonntag)
In Spalte B steht ein Buchstabenkürzel, meist u oder x

Als Ergebnis möchte ich die Zellen der Spalte B zählen, wenn hier ein "U" steht und zudem in der Spalte A in gleicher Zeile der Wochentag ein Arbeitstag ist(also Montag bis Freitag).
Meine Versuche sahen grob und offensichtlich falsch ungefähr so aus (um zu verdeutlichen was ich meine...):
=ZÄHLENWENN(B2:B50;"u")UND(ODER(A2:A50;<>"Samstag";<>"Sonntag"))

Habe es mit Zählenwenn versucht (ZählenwennS fällt leider flach wegen Excel 2003) und auch Summenprodukt, finde aber nicht die Lösung, für die Wochentage ODER Bedingungen und für Spalte B gleihzeitig eine UND Bedingung zu erstellen, also Montag ODER Dienstag ODER Mittwoch UND B="U".

Da ich das Ergebnis in einer anderen Tabelle darstellen muss und ich in der Ursprungstabelle keine Formeln unterbringen kann sind Zwischenschritte, was relativ einfach wäre, nicht möglich.

Ich freue mich auf Vorschläge,

Markus

  

Betrifft: Das geht schon mit SUMMENPRODUKT, ... von: Luc:-?
Geschrieben am: 22.09.2014 22:21:33

…Markus,
kaum aber mit ZÄHLENWENN. In Xl ist UND im Ggsatz zu VBA eine Fkt, kein Operator und man kann 2 Fktt nicht einfach so aneinder­reihen wie du es zeigst. Außerdem können die Xl-LogikFktt in einer Fml nur EinzelZellen/-Werte verarbeiten, keine ganzen Zell­Bereiche/Daten­felder.
Je nachdem in welcher Form die WoT in Spalte A vorliegen, sind unterschiedliche Fmln möglich:
1. Basis ist ein echter Datumswert, der mit "TTTT" formatiert wurde:
=SUMMENPRODUKT(N(B2:B50="U");N(WOCHENTAG(A2:A50;2)<6))
2. Die WoT sind reiner Text:
=SUMMENPRODUKT(N(B2:B50="U");N(WOCHENTAG(HEUTE()-WOCHENTAG(HEUTE();2)+ZEILE(1:49);2)<6))
In der 2.Fml wird das Szenario der 1. nachgestaltet. Dadurch ist der direkte Vgl mit Spalte A überflüssig, falls vorausgesetzt wdn kann, dass diese immer mit Montag beginnt und sich die WoTe zyklisch lückenlos wiederholen.
Es ist übrigens egal, ob u oder U notiert wird, hier wird beides einbezogen!
Gruß, Luc :-?

Besser informiert mit …


  

Betrifft: AW: Das geht schon mit SUMMENPRODUKT, ... von: Markus
Geschrieben am: 24.09.2014 11:55:19

Hallo Luc,

ersteinmal vielen Dank für die tolle Unterstützung!

Da kein Datumsformat vorliegt sonndern der Wochentag als reiner Text vorliegt kommt zunächst nur Formel 2 in Frage.
Hier hast Du geschrieben die basiert auf der Ausgangslage, dass die Spalte mit Montag beginnt. Dies ist leider nicht der Fall, es kann mit jedem Wochentag beginnen. Gibt es hierfür auch eine Lösung?

Gruß Markus


  

Betrifft: Dann kann eine MatrixFormel verwendet wdn, ... von: Luc:-?
Geschrieben am: 24.09.2014 16:49:09

…Markus,
die sich auf den Start-WoT bezieht:
{=SUMMENPRODUKT(N(B2:B50="U");N(WOCHENTAG(VERGLEICH(A2;TEXT(ZEILE(2:8);"TTTT");0)+ZEILE(1:49);2)< 6)) }
Die Eingabe als MxFml ist wg des Vgls erforderlich, sonst liefert TEXT kein Datenfeld und der Vgl schlägt fehl.
Hintergrund ist, dass Tag 2 der Xl-Tageszählung, also der 2.1.1900, durch TEXT als Montag formatiert wird (obwohl das nur dem SchaltjahresFehler 1900 geschuldet ist und dieser Tag eigentlich ein Dienstag war).
Alle anderen zuvor genannten Bedingungen müssen aber eingehalten wdn! Außerdem darf Xl nicht auf 1904-Datumswerte (damit wird der genannte Fehler üblicherweise vermieden) eingestellt sein.
Gruß, Luc :-?


  

Betrifft: AW: Dann kann eine MatrixFormel verwendet wdn, ... von: Markus
Geschrieben am: 24.09.2014 20:28:32

Hi Luc,

das übersteigt leider bei weitem meinen mathematischen Horizont und bei dem Versuch es dennoch zu nutzen bekomme ich keine Ergebnisse bzw. "#NV". Mir scheint auch dass nicht ganz klar geworden ist was ich suche oder aber es geht halt nicht ohne Mathestudium ;-)

Falls es doch noch Ideen gibt hier die Erklärung noch mal so:

In einer Zeile stehen in Spalte A beliebige Wochentage (als Text!), in Spalte B alles mögliche, in einigen Zellen ein einzelnes U (für Urlaub).
Escel soll nun in Spalte B die U´s zählen, vor denen in Spalte A Montag, Dienstag, Mittwoch, Donnerstag oder Freitag steht. Also die Urlaube an Arbeitstagen, nicht Samstag oder Sonntag.
Die U´s passend zu einem Wochentag zu zählen habe ich keine Probleme, ich bekomme nur die "oder" variante nicht unter...

VG Markus


  

Betrifft: Verstehe nicht, was daran hoch-mathematisch ... von: Luc:-?
Geschrieben am: 25.09.2014 01:24:50

…sein soll, Markus,
und warum die Fml bei dir nicht fktioniert! Weißt du, was eine Matrixformel ist? Falls nicht, das ist hier und anderswo schon so oft erklärt worden und kann unter dieser Bezeichnung auch in der Xl-Hilfe nachgelesen wdn, dass ich diese Kenntnis einfach mal voraussetze… ;-]
Bei mir sieht das Ergebnis (in 3 Start-WoT-Varianten) jedenfalls so aus:

 ABCDEFGHI
1WoT - Var1Signggalte WoT-NrWoT - Var2Signggalte WoT-NrWoT - Var3Signggalte WoT-Nr
2Montagx1Dienstagx1Donnerstagx1
3Dienstagx2Mittwochx2Freitagu2
4Mittwochx3Donnerstagx3Samstagu3
5Donnerstagx4Freitagu4Sonntagu4
6Freitagu5Samstagu5Montagu5
7Samstagu6Sonntagu6Dienstagu6
8Sonntagu7Montagu7Mittwochx7
9Montagu1Dienstagu1Donnerstagx1
10Dienstagu2Mittwochx2Freitagx2
11Mittwochx3Donnerstagx3Samstag 3
12Donnerstagx4Freitagx4Sonntag 4
13Freitagx5Samstag 5Montagu5
14Samstag 6Sonntag 6Dienstagx6
15Sonntag 7Montagu7Mittwochx7
16Montagu1Dienstagx1Donnerstagx1
17Dienstagx2Mittwochx2Freitagx2
18Mittwochx3Donnerstagx3Samstag 3
19Donnerstagx4Freitagx4Sonntag 4
20Freitagx5Samstag 5Montagx5
21Samstag 6Sonntag 6Dienstagx6
22Sonntag 7Montagx7Mittwochx7
23Montagx1Dienstagx1Donnerstagx1
24Dienstagx2Mittwochx2Freitagx2
25Ergebnis44 43 45
26richtigB25: {=SUMMENPRODUKT(N(B2:B50="U");N(WOCHENTAG(VERGLEICH(A2;TEXT(ZEILE(2:8);"TTTT");0)+ZEILE(1:49);2)<6))}
27 E25: {=SUMMENPRODUKT(N(E2:E50="U");N(WOCHENTAG(VERGLEICH(D2;TEXT(ZEILE(2:8);"TTTT");0)+ZEILE(1:49);2)<6))}
28 H25: {=SUMMENPRODUKT(N(H2:H50="U");N(WOCHENTAG(VERGLEICH(G2;TEXT(ZEILE(2:8);"TTTT");0)+ZEILE(1:49);2)<6))}
29falschC25:=SUMMENPRODUKT(N(B2:B50="U");N(WOCHENTAG(HEUTE()-WOCHENTAG(HEUTE();2)+ZEILE(1:49);2)<6))
30(alte Fml)F25:=SUMMENPRODUKT(N(E2:E50="U");N(WOCHENTAG(HEUTE()-WOCHENTAG(HEUTE();2)+ZEILE(1:49);2)<6))
31 I25:=SUMMENPRODUKT(N(H2:H50="U");N(WOCHENTAG(HEUTE()-WOCHENTAG(HEUTE();2)+ZEILE(1:49);2)<6))
32alte WoT-NrC2[:C24;F2:F24;I2:I24]: {=WOCHENTAG(HEUTE()-WOCHENTAG(HEUTE();2)+ZEILE(1:49);2)}

Gruß, Luc :-?


  

Betrifft: AW: Verstehe nicht, was daran hoch-mathematisch ... von: Markus
Geschrieben am: 26.09.2014 13:39:49

..sein soll...Luc, das ist halt nicht mein tägliches Geschäft, ich bin aber sehr dankbar für Deine Mühe!

Ich habe Deine Formel in meine Tabelle eingefügt (c40) und die Bereiche entsprechend meiner Tabelle angepasst, anscheinend mache ich was falsch (mangelndes Formelverständnis...) denn es kommt nix bei raus.
Deshalb Tabelle anbei mit der Bitte Dir das noch mal anzuschauen.

https://www.herber.de/bbs/user/92835.xls

VG Markus


  

Betrifft: Das hatte ich mir schon gedacht, ... von: Luc:-?
Geschrieben am: 28.09.2014 20:18:10

…Markus,
als ich mal kurz vom Meißener Weinfest aus bei Herber reinsah und deine Frage las, dass du zuviel „angepasst“ hast (da bist du aber gerade auch unter den Fragestellern der letzten Woche nicht der Einzige!), weil du die Formel nicht wirklich verstehst. Ich hatte doch nicht umsonst den 2.1.1900 erwähnt, der den Vglstext Montag liefert. Du hast TEXT(ZEILE(2:8) aber auf 9:15 geändert, was hier zwar (zufälligerweise) ebenfalls die WoTe von Montag bis Sonntag ergibt, aber nicht erforderlich ist. Entscheidend ist aber deine Änderung von +ZEILE(1:49) auf 9:38, was sich nicht auf die von dir benutzten Zeilen bezog, sondern auf eine reine, stets mit 1 beginnende Zählung der insgesamt von dir benutzten Zeilen. Abgesehen mal davon, dass deine letzte Zeile ohnehin die 39 (nicht 38) ist, müsstest du also die 8 vorstehenden Zeilen abziehen und kämst so auf 1:31.
In deinem Bsp identifiziert der VERGLEICH deinen StartTag Freitag als 5.Tag in der WoT-Folge Montag…Sonntag (bezogen auf das - falsche - WoT-Ergebnis vom 2.-8.1.1900). Durch Addition von 1…31 ergibt sich ein Datum, dass per Fkt WOCHENTAG die lfd Nr des jeweiligen WoTs lt dt Zählung (…;2 → beginnend mit 1=Montag) liefert. Als 1.Datum wird so der 6.1.1900 eingestellt, der nach dt Zählung der 5.WoT, also Freitag war (in Wirklichkeit war's natürlich schon Sonnabend, aber das ist hierbei irrelevant, denn es geht ja nur um die richtige Reihenfolge der Folge-WoT ab wechselndem Start-WoT).
Die richtige MxFml in C40 lautet also:
{=SUMMENPRODUKT(N(C9:C39="U");N(WOCHENTAG(VERGLEICH(A9;TEXT(ZEILE(2:8);"TTTT");0) +ZEILE(1:31);2)<6)) }
Gruß, Luc :-?


  

Betrifft: Verneigung von: Markus
Geschrieben am: 30.09.2014 11:26:04

Hi Luc,

großartig, ich danke Dir!
Werde mal einen Bildungsurlaub Mathe für Doofe besuchen ;-)

VG Markus


 

Beiträge aus den Excel-Beispielen zum Thema "Zählen wenn /oder/und"