Supportnet / Forum / Tabellenkalkulation
Namen anhand von Zimmernummern automatisch in einer Tabelle ausgeben
Frage
Hallo zusammen,
ich habe folgendes Problem:
Ich habe (vereinfacht dargestellt) eine Tabelle mit folgenden Feldern:
Name, Vorname,Abteilung,Zimmer
In einem Zimmer können mehrere (bis zu 3 ) Personen sitzen.
Ich möchte in einer anderen Tabelle, die den Grundriss der Räume nachbildet und in jedem Raum die Zimmernummer enthält automatisch anhand der Zimmernummer die Namen der Personen in maximal 3 Ausgabezellen ausgeben lassen.
Mit SVERWEIS komme ich da nicht weiter, da für viele Zimmernummern mehrere Datensätze (=Personen) vorhanden sind.
Eine Formel müßte also prüfen, ob für das entsprechende Zimmer mehrere Einträge vorhanden sind und diese dann "innerhalb des Raumes" in die betreffenden 3 Zellen eintragen.
Ich hoffe, das Problem ist halbwegs verständlich.
Ich benötige auf jeden Fall eine Formellösung und keine VBA-Lösung.
Im Voraus schon mal vielen Dank für jegliche Mühe ;-)
CU
want2cu
Antwort 1 von AS
Hallo want2cu,
in Tabelle1 stehen in Spalten A bis D Name, Vorname, Abteilung, Zimmer-Nr.
In Tabelle 2 steht in
B1 die gewünschte Zi-Nr
A2 "Name 1"
A3" Name 2"
A4 "Name3 "
usw.
Array-Formel in B2:
=WENN(A2="";"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Eingabe mit Strg/Shift/Enter
und runterkopieren!
Gruß Arnim
in Tabelle1 stehen in Spalten A bis D Name, Vorname, Abteilung, Zimmer-Nr.
In Tabelle 2 steht in
B1 die gewünschte Zi-Nr
A2 "Name 1"
A3" Name 2"
A4 "Name3 "
usw.
Array-Formel in B2:
=WENN(A2="";"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Eingabe mit Strg/Shift/Enter
und runterkopieren!
Gruß Arnim
Antwort 2 von want2cu
Hallo Arnim,
ganz herzlichen Dank für deine Hilfe und die tolle Lösung.
Ich habe die Formel für meine Zwecke lediglich noch wie folgt abgeändert:
=WENN(ISTFEHLER(INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))));"";INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Klar: auch hier natürlich STRG-SHIFT-ENTER, um die geschweiften Klammern zu erzeugen.
Mit der geänderten Formel erreiche ich folgendes:
1. die Anzeige erfolgt nicht in Abhängigkeit von einem anderen Zellinhalt (die WENN-Bedingung ist bei mir entbehrlich).
2.ich verhindere die Fehlermeldung #ZAHL, wenn in dem betreffenden Zimmer nicht die "maximale" Personenzahl sitzt.
Deine LÖsung hat mir auf jeden Fall sehr weiter geholfen. Nochmals DANKE!
CU
want2cu
ganz herzlichen Dank für deine Hilfe und die tolle Lösung.
Ich habe die Formel für meine Zwecke lediglich noch wie folgt abgeändert:
=WENN(ISTFEHLER(INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))));"";INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Klar: auch hier natürlich STRG-SHIFT-ENTER, um die geschweiften Klammern zu erzeugen.
Mit der geänderten Formel erreiche ich folgendes:
1. die Anzeige erfolgt nicht in Abhängigkeit von einem anderen Zellinhalt (die WENN-Bedingung ist bei mir entbehrlich).
2.ich verhindere die Fehlermeldung #ZAHL, wenn in dem betreffenden Zimmer nicht die "maximale" Personenzahl sitzt.
Deine LÖsung hat mir auf jeden Fall sehr weiter geholfen. Nochmals DANKE!
CU
want2cu
Antwort 3 von AS
Hallo want2cu,
jawohl, so ist es!
Wenn es sich immer nur um 2-3 Leutchen handelt, kann man auch die Formel so schreiben:
=WENN((A2="")+(ZEILE(C1)>ZÄHLENWENN(Tabelle1!D$2:D$500;B$1));"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Gruß Arnim
PS, ich bin Dir ja noch was schuldig vom Jahresanfang. Aber der Geist ist willig, nur das Fleisch ist schwach! Doch im Laufe des Jahres werde ich mich schon noch aufraffen!
jawohl, so ist es!
Wenn es sich immer nur um 2-3 Leutchen handelt, kann man auch die Formel so schreiben:
=WENN((A2="")+(ZEILE(C1)>ZÄHLENWENN(Tabelle1!D$2:D$500;B$1));"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN((Tabelle1!D$2:D$500=B$1);ZEILE(Tabelle1!$2:$500));ZEILE(A1))))
Gruß Arnim
PS, ich bin Dir ja noch was schuldig vom Jahresanfang. Aber der Geist ist willig, nur das Fleisch ist schwach! Doch im Laufe des Jahres werde ich mich schon noch aufraffen!
Antwort 4 von want2cu
Hallo Arnim,
nochmals vielen Dank für die 2.Lösungsalternative.
Ich konnte sie jetzt leider nur "quick&dirty" testen und erhalte dabei ebenfalls die Fehlermeldung #ZAHL.
Morgen sehe ich mir die Sache genauer an, um festzustellen, was ich beim anpassen der Formel wohl falsch gemacht habe.
Die von mir in Antwort 2 aufgeführte Formel löst mein aktuelles Problem auf jeden Fall schon mal. Aber es geht ja auch um Alternativen und das hinzulernen.
Du bist mir übrigens absolut nichts schuldig. Sich gegenseitig zu helfen, sollte eigentlich selbstverständlich sein.
In diesem Sinne ;-)
Viele Grüsse
want2cu
P.S.: Falls es sich um die Excel-Workshop-Dateien gehandelt haben sollte, kann ich dir gerne ein aktuelles Update schicken. Ich bin immer wieder begeister, was man mit Formeln und "ganz ohne VBA" so alles lösen kann. ALIBA ist für etliche der Tipps redaktionell verantwortlich
nochmals vielen Dank für die 2.Lösungsalternative.
Ich konnte sie jetzt leider nur "quick&dirty" testen und erhalte dabei ebenfalls die Fehlermeldung #ZAHL.
Morgen sehe ich mir die Sache genauer an, um festzustellen, was ich beim anpassen der Formel wohl falsch gemacht habe.
Die von mir in Antwort 2 aufgeführte Formel löst mein aktuelles Problem auf jeden Fall schon mal. Aber es geht ja auch um Alternativen und das hinzulernen.
Du bist mir übrigens absolut nichts schuldig. Sich gegenseitig zu helfen, sollte eigentlich selbstverständlich sein.
In diesem Sinne ;-)
Viele Grüsse
want2cu
P.S.: Falls es sich um die Excel-Workshop-Dateien gehandelt haben sollte, kann ich dir gerne ein aktuelles Update schicken. Ich bin immer wieder begeister, was man mit Formeln und "ganz ohne VBA" so alles lösen kann. ALIBA ist für etliche der Tipps redaktionell verantwortlich
Antwort 5 von AS
Hallo want2cu,
absolut nichts dagegen!
Ich formelbastele ja selbst zu gerne!;-)
Danke!
Arnim
absolut nichts dagegen!
Ich formelbastele ja selbst zu gerne!;-)
Danke!
Arnim
Antwort 6 von want2cu
Hallo zusammen,
für alle, die vielleicht ein ähnliches problem haben, hier die von mir verwendeten Formeln:
A3=WENN(ISTFEHLER(INDEX(Daten!$A:$A;KKLEINSTE(WENN((zimmer=A2);ZEILE(Daten!$2:$500));ZEILE(A1))));"";INDEX(Daten!$A:$A;KKLEINSTE(WENN((zimmer=A2);ZEILE(Daten!$2:$500));ZEILE(A1))))
Es ist eine Matrixformel, die mit STRG-SHIFT-ENTER zu beenden ist.
Der Formelparameter ZEILE(A1) trägt den ersten in Zimmer vorgefundenen Namen ein, für weitere Personen ist dieser Parameter entsprechend in der Formel an 2 Stellen anzupassen,
also ZEILE(A2) für den 2.Namen, ZEILE(A3) für den dritten Namen etc.
Im Tabellenblatt DATEN steht in Spalte A der Name und in Spalte D die Zimmernummer.
So kann man z. einen mit Excel erstellten Gebäudeplan vollautomatisch aus einer Basistabelle mit den dort sitzenden Personen (und natürlich weiteren Zusatzinfomrationen) füllen lassen.
Wer dazu weitere Infos haben möchte, einfach per Mail bei mir melden.
CU
want2cu
P.S.: Nochmals ein dickes DANKESCHÖN an Arnim Schindler für den entscheidenden Lösungshinweis ;-)
für alle, die vielleicht ein ähnliches problem haben, hier die von mir verwendeten Formeln:
A3=WENN(ISTFEHLER(INDEX(Daten!$A:$A;KKLEINSTE(WENN((zimmer=A2);ZEILE(Daten!$2:$500));ZEILE(A1))));"";INDEX(Daten!$A:$A;KKLEINSTE(WENN((zimmer=A2);ZEILE(Daten!$2:$500));ZEILE(A1))))
Es ist eine Matrixformel, die mit STRG-SHIFT-ENTER zu beenden ist.
Der Formelparameter ZEILE(A1) trägt den ersten in Zimmer vorgefundenen Namen ein, für weitere Personen ist dieser Parameter entsprechend in der Formel an 2 Stellen anzupassen,
also ZEILE(A2) für den 2.Namen, ZEILE(A3) für den dritten Namen etc.
Im Tabellenblatt DATEN steht in Spalte A der Name und in Spalte D die Zimmernummer.
So kann man z. einen mit Excel erstellten Gebäudeplan vollautomatisch aus einer Basistabelle mit den dort sitzenden Personen (und natürlich weiteren Zusatzinfomrationen) füllen lassen.
Wer dazu weitere Infos haben möchte, einfach per Mail bei mir melden.
CU
want2cu
P.S.: Nochmals ein dickes DANKESCHÖN an Arnim Schindler für den entscheidenden Lösungshinweis ;-)