Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1676to1680
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
Inhaltsverzeichnis

Formel ergänzen - Vergleich String / 2 Listen

Formel ergänzen - Vergleich String / 2 Listen
07.03.2019 16:02:11
Kisska
Hallo zusammen,
ich benötige eure Hilfe bei der Erweiterung meiner Formel.
Anbei die Datei mit der Aufgabenstellung:
https://www.herber.de/bbs/user/128181.xlsx
Zusammenfassung:
Es sollen relevante Feiertage mit einem X gekennzeichnet werden.
Relevant sind die Feiertage dann, wenn diese Bedingungen erfüllt sind:
1. Es handelt sich tatsächlich um ein Feiertag
2. Dieser Feiertag ist entweder bundesweit vertreten (2.1) ODER in mind. einem der Bundesländer in dem ein Mitarbeiter wohnt (2.2).
Ich hab leider keine Idee wie man die Bedingung 2.2 realisiert.
Eine Hilfsspalte würde ich gerne vermeiden.
Freue mich auf eure Vorschläge.
VG, Kisska

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel ergänzen - Vergleich String / 2 Listen
07.03.2019 16:11:16
SF
Hola,
in C14:
=WENNFEHLER(VERWEIS(2;1/SUCHEN($B$19:$B$21;B14);$B$19:$B$21);"")

Die Formel bis C16 nach unten ziehen.
Dann in C2:
=WENN(SUMMENPRODUKT(($A$14:$A$16=C1)*(($B$14:$B$16="bundesweit")+($C$14:$C$16"")));"x";"")
Gruß,
steve1da
und ohne Hilfsspalte?
07.03.2019 16:22:53
Kisska
Hallo steve1da,
danke für die Prompte Antwort und die Lösung!
Geht es auch ohne die Hilfsspalte ab C14, sondern direkt in einer Formel ab C2?
VG, Kisska
AW: und ohne Hilfsspalte?
07.03.2019 16:34:25
SF
Hola,
möglich, aber dazu fehlt mir momentan die Zeit. Aber warum eigentlich? Excel hat so viele Zellen zur Verfügung.
Gruß,
steve1da
Anzeige
AW: und ohne Hilfsspalte?
07.03.2019 18:34:05
Kisska
Hey steve1da,
ich würde zwar die Lösung ohne die Hilfsspalte bevorzugen, dennoch interessiert mich deine Lösung mit der Hilfsspalte.
Deine Formel für die Hilfsspalte lautet ab C14:
=WENNFEHLER(VERWEIS(2;1/SUCHEN($B$19:$B$21;B14);$B$19:$B$21);"")
In meinen originalen Daten hat die Mitarbeiterliste leider auch Leerzeilen, es sieht wie folgt aus:
Mitarbeiter 1
Mitarbeiter 2
Mitarbeiter 3
LEER
BLOCK 2
Mitarbeiter 1
Mitarbeiter 2
Mitarbeiter 3
LEER
Bei den Zeilen die LEER sind oder einen Titel haben (wie hier BLOCK 2) gibt es in der Spalte B kein Bundesland. Dadurch bekomme ich bei deiner Formel überall eine 0 als Ergebnis :/ Kann man das irgendwie umgehen?
VG, Kisska
Anzeige
AW: z.B. mit ner Matrixfunktion(alität)sformel ...
07.03.2019 19:21:09
neopa
Hallo Kiska,
... in C1 die keines spez. Formelabschluss wie eine klassische Matrixformel benötigt und mit benutzerdefinierten Zahlenformat: "x";; in der Zelle(n):
=ANZAHL(INDEX(SUCHEN($B19:$B21;INDEX($B14:$B16;VERGLEICH(C1;$A14:$A16;0)));))+
WENNFEHLER((INDEX($B14:$B16;VERGLEICH(C1;$A14:$A16;0))=$B14);0)

und nach rechts kopieren.
Gruß Werner
.. , - ...
Könnte man Leerzeilen berücksichtigen?
07.03.2019 20:50:36
Kisska
Hallo Werner,
wow, danke! Die Formel ist für mich zu komplex, um diese zu verstehen, aber sie funktioniert hervorragend, wenn man keine Leerzeilen hat.
In meinen originalen Daten hat die Mitarbeiterliste leider auch Leerzeilen, es sieht wie folgt aus:
BLOCK 1
Mitarbeiter 1
Mitarbeiter 2
Mitarbeiter 3
usw.
LEERZEILE BLOCK 1
BLOCK 2
Mitarbeiter 1
Mitarbeiter 2
Mitarbeiter 3
usw.
LEERZEILE ENDE BLOCK 2
Die Zeilen "LEERZEILE BLOCK 1", "BLOCK 2" und "LEERZEILE ENDE BLOCK 2" haben kein Bundesland in der Spalte B, müssen aber leider mit in den Bereich eingeschlossen werden. Dadurch bekomme ich ähnlich wie beim Helfer "steve1da" falsche Ergebnisse.
Lässt sich deine Formel erweitern?
VG, Kisska
Anzeige
AW: damit ändert sich nichts am Ergebnis ...
08.03.2019 09:10:38
neopa
Hallo Kiska,
... wenn der Auswertungsbereich für die Mitarbeiter in der Formel erweitert wird, z.B. auf =ANZAHL(INDEX(SUCHEN($B19:$B49;... ändert sich zwar der ermittelte Wert in der Zelle aber durch das benutzerdefinierte Zahlenformat wird trotzdem korrekt "x" angezeigt.
Solltest Du was anderes festgestellt haben, so stelle doch Dein neue Beispieldatei ein und zeige darin die von Dir festgestellte ungewollten Ergebnisse auf. Dann sehe ich woran es liegen könnte.
Gruß Werner
.. , - ...
hier das Beispiel
08.03.2019 16:13:58
Kisska
Hallo Werner,
danke für deine Antwort!
Anbei das Beispiel (die Feiertage sind ausgedacht):
https://www.herber.de/bbs/user/128225.xlsx
Hier sieht man, dass der Feiertag 06.01.2019 (in Rot) auch mit einem "X" versehen ist, obwohl kein Mitarbeiter im Bundesland BW, BY oder ST wohnt.
Wenn man die Zeilen 19,20 und 26 entfernt, dann funktioniert die Formel wieder, d.h. bei dem Datum 06.01.2019 wird kein "X" gesetzt.
VG, Kisska
Anzeige
AW: jetzt verständlich, ...
08.03.2019 17:21:24
neopa
Hallo Kiska,
... dafür erweitere ich meine Zellformel in C2 um die zusätzliche Bedingung, dass leere Datenzellen nicht berücksichtigt werden dürfen.
In C2:
=ANZAHL(INDEX(SUCHEN($B14:$B26;INDEX($B6:$B10;VERGLEICH(C1;$A6:$A10;0)));)/($B14:$B26""))
+WENNFEHLER((INDEX($B6:$B10;VERGLEICH(C1;$A6:$A10;0))="bundesweit");0)

und Formel wieder nach rechts ziehend kopieren
Gruß Werner
.. , - ...
AW: jetzt verständlich, ...
08.03.2019 17:44:33
Kisska
Hallo Werner,
es klappt nicht :( Ein "x" erscheint nun nur bei den bundesweiten Feiertagen.
VG, Kisska
hat sich erledigt
09.03.2019 01:24:25
Kisska
Hallo Werner,
nach langem Experimentieren hab ich nun meine richtigen Ergebnisse :-)
Bei deiner Formel habe ich die zusätzliche Bedingung

/($B14:$B26"")
verschoben und zwar an die Stelle vor dem Semikolon-Zeichen, also nach der Such-Funktion und schon werden richtige Ergebnisse geliefert.
Besten Dank für deine Hilfe! :-)
VG, Kisska
Anzeige
AW: genauso ...
09.03.2019 13:16:24
neopa
Hallo Kiska,
... wie ich die Formel eingestellt habe, hatte ich diese auch zunächst in C2. Als ich sie dann nach rechts kopierte, merkte ich in J8, das da was falsch ist. Der Fehler war für mich schnell gefunden. Aber die korrigierte Formel hab ich nur nach rechts kopiert und deshalb offensichtlich die nicht korrigierte eingestellt, sorry. Allerdings hat dies dann wohl den Vorteil, dass Du nun die Formel besser verstanden hast.
Gruß Werner
.. , - ...
AW: genauso ...
11.03.2019 12:59:27
Kisska
Alles gut! :-) Und ja, ich habe die Formel besser verstanden, danke!
VG, Kisska

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige