Excel XP Benutzerdef. Formel

Anonym · 12. Oktober 2003 um 15:57

Hallo,

ich brauche eine Formel in Excel XP.
Sowas wie „Verkettenwenn()“
Mein Problem: Mit =Sverweis(…) wird ja immer nur ein Wert wiedergegeben. Wenn ich jetzt aber mehrere, in Frage kommende Zellen habe komme ich mit Verweis nicht weiter.

Bsp.:

Name Geburtstag

Peter 5. April
Klaus 8. Mai
Heinz 5. April

Jetzt will ich alle Namen sehen, die am 5.April Geburtstag haben und das in einer Zelle (ohne extra Spalte).

Ansätz habe ich leider keinen einzigen. Ich hoffe es gibt jemanden, der 'ne Idee hat.

Gruß

Malte

tomcat_371ddf · 13. Oktober 2003 um 11:44

Wenn ich das richtig verstehe stehen Tag Monat Jahr aber alles in einer Zelle der Liste - schonmal ungünstig.

Spontane ANsätze: Autofilterfunktion, dann wird zumindest deine Liste durchforstet und du siehst es.

Wenn du die Werte in eine andere Zelle geben willst müsstest du die abfrage eventuell schachteln. da er sich ja nur auf Teile beziehen soll, könnte Formel „links“ bzw. „rechts“ in verbindungen mit wenn-dann und sverweis helfen…
Musst mal etwas genauer sagen was du vor hast und wozu?!

MfG
T

Anonym · 13. Oktober 2003 um 13:08

Hallo Malte,
ich hätte da eine Idee, aber die ist nun ziemlich umständlich und hätte dazu noch ein paar Nachteile. Ich denke, am besten kommt man mit einem Makro zu dem, was du haben möchtest. Hier aber erst mal grob der Ansatz, den ich ohne Makro hätte:

Hinter deine Geburtstage müsste eine Hilfsspalte, in der mit WENN-Funktionen automatisch an den Stellen ein X eingetragen wird, deren Geburtstag du abfragen willst. (Hier bräuchtest du auch die Funktionen Tag() und Monat(), um die Abfrage jahresunabhängig zu machen.)
In einer weiteren Hilfsspalte kann man (wieder mit WENN-Funktionen) neben die Namen eine ganzzahlige Nummerierung hinbekommen, so dass nur die Geburtstagskinder eine fortlaufende Nummer bekommen (alle anderen Zeilen nicht).
In einer dritten Hilfsspalte kombiniert man die laufende Nummer mit dem Datum (z.B. über Textverkettung), so dass dort Daten wie „01-17.10“, „02-17.10“ o.ä. auftauchen.
Nun kannst du SVERWEIS-Funktionen verketten, die nach diesen ‚Codes‘ von Punkt 3 suchen und die zugehörigen Namen liefern.

Ich hoffe, das ist schon mal vom Prinzip her verständlich. Ich könnte mir aber vorstellen, dass du mit einem Makro besser fährst.

Viel Erfolg
Wolfgang

ich brauche eine Formel in Excel XP.
Sowas wie „Verkettenwenn()“
Mein Problem: Mit =Sverweis(…) wird ja immer nur ein Wert
wiedergegeben. Wenn ich jetzt aber mehrere, in Frage kommende
Zellen habe komme ich mit Verweis nicht weiter.

Bsp.:

Name Geburtstag

Peter 5. April
Klaus 8. Mai
Heinz 5. April

Jetzt will ich alle Namen sehen, die am 5.April Geburtstag
haben und das in einer Zelle (ohne extra Spalte).

Ansätz habe ich leider keinen einzigen. Ich hoffe es gibt
jemanden, der 'ne Idee hat.

Gruß

Malte

Anonym · 13. Oktober 2003 um 14:24

Hi Malte,
hier ein paar konkrete Formeln zu meinem Vorschlag ohne Makro. Es ist etwas anders als ursprünglich vorgeschlagen - z.B. brauchte ich eine der ursprünglich gedachten Hilfsspalten gar nicht -, aber das Prinzip bleibt gleich.

Mein Beispiel:
Ich habe im Bereich A2:B23 die Namen und Geburtstage (bei Bedarf auch mit Jahresangabe) stehen.
In Zelle G2 trage ich das Datum ein, das ich suchen möchte.

Nun die Hilfsspalten:

Zelle C2:

=WENN(UND(TAG($G$2)=TAG(B2);MONAT($G$2)=MONAT(B2));"X";"")

Zelle D2:

=WENN(C2="X";D1+1;D1)

Zelle E2:

=A2

Diese Formeln müssen jeweils nach unten bis in die Zeile 23 kopiert werden. Zur Erläuterung:
In Spalte C tauchen nun X’e bei den Namen auf, deren Geburtstag du suchst. In Spalte D sind ganze Zahlen zu sehen, und zwar neben jedem der X’e (aus Spalte C) eine neue Zahl. Diese Zahl wird nach unten so lange wiederholt, bis wieder ein X gefunden wurde. In Spalte E tauchen die Namen aus Spalte A noch einmal auf, weil sonst die SVERWEIS-Funktion nicht geht.

Nun muss ich in einem anderen Tabellenbereich (vielleicht machst du das später besser auf einem ganz anderen Tabellenblatt?) die gewünschte Ausgabe zurechtbasteln. Ich mache das hier:

Zelle H4:

=SVERWEIS(1;$D$2:blush:E$23;2;FALSCH)

Zelle H5:

=SVERWEIS(2;$D$2:blush:E$23;2;FALSCH)

Zelle H6:

=SVERWEIS(3;$D$2:blush:E$23;2;FALSCH)

(u.s.w.)

Zelle H12:

=SVERWEIS(9;$D$2:blush:E$23;2;FALSCH)

In der Spalte H stehen nun maximal 9 Namen, die dem gesuchten Geburtsdatum entsprechen. Sind weniger als 9 Namen vorhanden, erscheinen die SVERWEIS-typischen #NV’s

Zelle I4:

=WENN(ISTNV(H4);"";H4)

Zelle I5:

=WENN(ISTNV(H5);"";VERKETTEN(", ";H5))

…und den Inhalt von I5 bis nach I12 kopieren. Hier werden nun die #NV’s nicht mehr dargestellt und trennende Kommata eingefügt.

Zelle H2:

=VERKETTEN(I4;I5;I6;I7;I8;I9;I10;I11;I12)

Hier (neben deinem eingegebenen Suchdatum) werden nun alle Texte aus der Spalte I verknüpft. Voila

Hoffen wir nur, dass nicht mehr als 9 Personen deiner Liste gleichzeitig Geburtstag feiern. Sonst musst du vorsorglich deinen Auswertungsbereich verlängern. Und natürlich immer schön im Blick behalten, dass die Formeln derzeit nur Eingaben bis Zeile 23 berücksichtigen!

Viel Erfolg nochmal
Wolfgang

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 13. Oktober 2003 um 23:01

Name Geburtstag
Peter 5. April
Klaus 8. Mai
Heinz 5. April
Jetzt will ich alle Namen sehen, die am 5.April Geburtstag
haben und das in einer Zelle (ohne extra Spalte).

Hallo Malte,
probier mal das Nachfolgende, die Fpormeln in F1-F4 sind Matrixformeln, also mit Strg+Shift+Enter eingeben.
Gruß
Reinhard

 A B C D E F 
05. Apr meier 05.04.03 meier
05. Apr müller müller
03. Dez schulze huhu
05. Apr huhu #ZAHL!
01. Jan fdshs 



 Ausgabe: meier müller huhu 


in F1:=WENN(ISTFEHL(INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));3)));"";INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));1)))
in F2:=WENN(ISTFEHL(INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));3)));"";INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));2)))
in F3:=WENN(ISTFEHL(INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));3)));"";INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));3)))
in F4:=WENN(ISTFEHL(INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));3)));"";INDEX(B:B;KKLEINSTE(WENN(A1:A100"";WENN(MONAT(A1:A100)+TAG(A1:A100)/100=MONAT(D1)+TAG(D1)/100;ZEILE(1:100)));4)))


in F15: =VERKETTEN(WENN(ISTFEHL(F1);"";F1);" ";WENN(ISTFEHL(F2);"";F2);" ";WENN(ISTFEHL(F3);"";F3);" ";WENN(ISTFEHL(F4);"";F4);WENN(ISTFEHL(F5);"";F5);WENN(ISTFEHL(F6);"";F6);WENN(ISTFEHL(F7);"";F7);WENN(ISTFEHL(F8);"";F8);WENN(ISTFEHL(F9);"";F9);WENN(ISTFEHL(F10);"";F10);WENN(ISTFEHL(F11);"";F11);WENN(ISTFEHL(F12);"";F12))

m_bus · 16. Oktober 2003 um 13:25

Hi Malte,

Autofilter dürfte dir hier weiterhelfen. Klicke in deine Liste, gehe auf Daten -> Filter -> Autofilter

Dir steht dann eine Auswahlliste zur Verfügung, bei der du das gewünschte Datum auswählen kannst. Excel zeigt dir dann alle Datensätze zu diesem Geburtstag an.