Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1380to1384
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Zählen wenn /oder/und
22.09.2014 21:21:14
Markus
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

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Das geht schon mit SUMMENPRODUKT, ...
22.09.2014 22:21:33
Luc:-?
…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 …

Anzeige
AW: Das geht schon mit SUMMENPRODUKT, ...
24.09.2014 11:55:19
Markus
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

Dann kann eine MatrixFormel verwendet wdn, ...
24.09.2014 16:49:09
Luc:-?
…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 :-?

Anzeige
AW: Dann kann eine MatrixFormel verwendet wdn, ...
24.09.2014 20:28:32
Markus
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

Anzeige
Verstehe nicht, was daran hoch-mathematisch ...
25.09.2014 01:24:50
Luc:-?
…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 :-?

Anzeige
AW: Verstehe nicht, was daran hoch-mathematisch ...
26.09.2014 13:39:49
Markus
..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

Das hatte ich mir schon gedacht, ...
28.09.2014 20:18:10
Luc:-?
…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 :-?

Anzeige
Verneigung
30.09.2014 11:26:04
Markus
Hi Luc,
großartig, ich danke Dir!
Werde mal einen Bildungsurlaub Mathe für Doofe besuchen ;-)
VG Markus

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige