Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
304to308
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
304to308
304to308
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Sverweis weitersuchen - Nächste Zelle

Sverweis weitersuchen - Nächste Zelle
03.09.2003 16:43:39
Newbe
Hallo Forum!

Ich habe eine Tabelle in der ich unsortiert Daten habe. Es handelt sich lediglich um Zwei Spalten in der ich den Namen habe und Die Kostenstelle zu der er gehört. Ich sollte jetzt für jede Kostenstelle alle zugehörigen Namen haben.

Bis jetzt zeigt er mir allerdings nur den ersten Namen an den er findet und dann hört er auf. Mein Ziel ist es zu schaffen alle Namen untereinander stehen zu haben pro Kostenstelle auf einem neuen Tabellenblatt. Wie kriege ich hin das er weiter sucht und den nächsten Namen in die nächste Zeile darunter schreibt

Ich wäre euch verbunden bevor ich durchdrehe

Liebe Grüße - Newbe

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis weitersuchen - Nächste Zelle
03.09.2003 17:01:22
Mac4:
Hallo Newbe,

hab hier vielleicht was für Dich:
Wenn in Spalte A die Namen und in Spalte B die Kostenstelle steht, dann schreib in Spalte C folgende Matrixformel (wobei der Suchbegriff (Kostenstelle) in dem Beispiel in E1 steht):

{=WENN(ZEILE()>ZÄHLENWENN($A$1:$A$8;E$1);"";INDEX($B$1:$B$8;KKLEINSTE(WENN((A$1:A$8=E$1);ZEILE($1:$8));ZEILE())))}

Viel Erfolg
Marc
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 08:50:13
newbe
Ich hoffe ich stelle mich nicht all zu blöd an, aber ich habe immer noch ein Problem mit der Auswertung. Und zwar verstehe ich nicht genau den aufbau aber dem Index. Die Funktion KKleinste war mir bis jetzt unbekannt. Könntet ihr mir vielleicht sagen was es auf sich hat - gerade die ($1:$8) Ich bin wahnsinnig froh um eure Hilfe - Ihr rettet mir wahrscheinlich Kopf und Kragen

Ich habe ein Anwendungsbeispiel auf den Server gestellt : https://www.herber.de/bbs/user/864.xls

Hoffe bald von euch zu hören

Gruß Stefan
Anzeige
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 09:57:21
Mac4
Hallo Stefan,

Dir jetzt diese Formel in allen Einzelheiten zu erklären, sprengt leider meinen zeitlichen Rahmen. Schau Dir die Formel in der Bearbeitungsleiste an und klick Dich durch die verschiednenen Funktionen - ich denke, die Formel wird sich Dir langsam erschließen, wobei das Verständnis einer solch komplexen Matrixformel schon nicht so ganz leicht ist.
Die ($1:$8) geben lediglich den Bereich an, auf den ich diese Formel in meinem Beispiel angewendet habe - das musst Du anpassen.

Viel Erfolg!


Marc
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 10:05:32
newbe
Das kann ich von dir ja auch nicht verlangen. Das Problem ist, das die Aufgabe eigentlich relativ einfach ist die Lösung aber zu komlex.
Den Weg den du vorgeschlagen hast, habe ich schon probiert zu gehen - besser gesagt gestern Nacht ;-( Kannst du mir vielleicht wenigstens den Gedankengang kurz beschreiben den du dabei hattest - warum er zum Beispiel "" schreiben soll wenn die Anzahl der Zellen größer ist als die Anzahl der Ergebnisse der Kostenstellennummer. oder kannst du vielleicht ein file auf den server stellen an dem ich mich orientieren könnte?

tut mir Leid das ich so nerve, aber die Aufgabe wurde mir von ganz oben in der Firma gestellt.

Ich danke dir

Gruß Stefan
Anzeige
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 10:19:47
Mac4
Hi Stefan,

wenn das Ergebnis Zeilen($1:n) größer ist als die Anzahl der Übereinstimmungen mit dem Suchkriterium (Bsp. Formel ist bereits 6 Zeilen nach unten kopiert worden (Zellen($!1:6)=6, das Ergebnis Zählenwenn ergibt aber nur 5) muß der Eintrag "" sein.

Marc
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 10:30:05
newbe
Gut, das habe ich kapiert und soweit auch übernommen, jetzt kommt nur noch was er dann schreiben soll. Ich bin jetzt auf dem Sprung das Problem zu knacken. Er soll die werte ausgeben. Ich kappier nur noch nicht die wenn Funktion mit kkleinste

Steinige mich, aber ich bin so kurz davor

Gruß Stefan
Anzeige
Erklärung der Array-Formel
04.09.2003 12:13:04
Boris
Hi Stefan,

in E4 folgende Array-Formel und runterkopieren:

{=WENN(ZEILEN($1:1)>ZÄHLENWENN(B:B;$D$1);"";INDEX(A:A;KKLEINSTE(WENN($B$1:$B$100=$D$1;ZEILE($1:$100));ZEILEN($1:1))))}

Funktionsweise:
ZEILEN($1:1) ergibt 1. Wenn man die Formel runterkopiert, wird aus ZEILEN($1:1) dann ZEILEN($1:2), ZEILEN($1:3) etc. etc..., in Zahlen ausgedrückt also 1,2,3.....

Diese Zahl wird verglichen mit der Anzahl des Vorkommens der Kostenstelle. Diese Anzahl wird ermittelt mit =ZÄHLENWENN(B:B;$D$1)
In deinem Beispiel kommt die Kostenstelle "1000" 6 mal vor.
In der 1. Formel (mit ZEILEN($1:1) ) ergibt sich also der Vergleich: 1>6 - das Ergebnis ist somit FALSCH. Die DANN-Bedingung ("") kommt NICHT zum Tragen, sondern die SINST-Bedingung (Erklärung folgt).
In der 2. Formel (mit ZEILEN($1:2) ) ist der Vergleich 2>6 - also immer noch FALSCH. Das geht bis zur 6. Formel (ZEILEN($1:6)) - Vergleich 6>6 ist immer noch FALSCH. Erst ab Formel 7 (ZEILEN($1:7)) ergibt sich: 7>6 - und das ist WAHR - und somit bleibt die Zelle dann auch leer ("") (die DANN-Bedingung wird also "ausgeführt").

Der Hauptteil der Formel ist aber

INDEX(A:A;KKLEINSTE(WENN($B$1:$B$100=$D$1;ZEILE($1:$100));ZEILEN($1:1)))

INDEX(A:A) bildet einen Index aus A1:A65536. Und um aus diesem Index einen Wert herauszupicken, muss man die entsprechende Zeile angeben.
Und diese Zeile wird ermittelt mit
KKLEINSTE(WENN($B$1:$B$100=$D$1;ZEILE($1:$100));ZEILEN($1:1))

Es wird eine Array-Abfrage gestartet und somit ein NEUER Bereich gebildet mit:
WENN($B$1:$B$100=$D$1;ZEILE($1:$100))

Jeder Eintrag in B1:B100 wird NACHEINANDER mit D1 (=gesuchte Kostenstelle) vergleichen.
Ist der Vergleich WAHR - dann wird als DANN-Bedingung die ZEILENnummer des Eintrages zurückgegeben. Ist der Vergleich FALSCH, dann wird nur FALSCH zurückgegeben (da es keine SONST-Bedingung gibt).

Diese NEUE Array sieht dann in deinem Beispiel (=für B1:B100) so aus:

{FALSCH;FALSCH;3;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;10;FALSCH;FALSCH;FALSCH;FALSCH;15;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;21;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;27;FALSCH;FALSCH;FALSCH;31;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}

Und aus dieser Matrix wird mit KKLEINSTE(....;ZEILEN($1:1)) der kleinste Wert ausgelesen, mit KKLEINSTE(...;ZEILEN($1:2)) der 2.-kleinste etc. etc.

Und dieser Wert wird dann an die Funktion INDEX übergeben, die ja genau die Zeilennummern benötigt.

Alles klar? ;-)

Grüße Boris
Anzeige
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 10:11:33
newbe
Das kann ich von dir ja auch nicht verlangen. Das Problem ist, das die Aufgabe eigentlich relativ einfach ist die Lösung aber zu komlex.
Den Weg den du vorgeschlagen hast, habe ich schon probiert zu gehen - besser gesagt gestern Nacht ;-( Kannst du mir vielleicht wenigstens den Gedankengang kurz beschreiben den du dabei hattest - warum er zum Beispiel "" schreiben soll wenn die Anzahl der Zellen größer ist als die Anzahl der Ergebnisse der Kostenstellennummer. oder kannst du vielleicht ein file auf den server stellen an dem ich mich orientieren könnte?

tut mir Leid das ich so nerve, aber die Aufgabe wurde mir von ganz oben in der Firma gestellt.

Ich danke dir

Gruß Stefan
Anzeige
AW: Sverweis weitersuchen - Nächste Zelle
04.09.2003 10:28:27
Mac4
Hi Stefan,

Deine Beispieldatei kann ich mir leider nicht ansehen, da mir diese Möglichkeit von unseren EDV-Spezies weggeknippst wurde. Könntest sie mir höchstens zumailen unter marc.heintz@caritas-koeln.de

Marc
Nachtrag
03.09.2003 17:05:13
Mac4
Das ganze dann runterkopieren!

Marc
Nachtrag 2
03.09.2003 17:15:29
Boris
Hi Marc,

so solltest du aber auch den Hinweis geben, dass die Startformel unbedingt in Zeile 1 eingetragen wird - ansonsten fehlen nachher Ergebnisse.

Um diesen Hinweis im übrigen nicht geben zu müssen, hab ich mir angewöhnt, anstatt ZEILE() ZEILEN($1:1) zu verwenden.
Daraus wird beim runterkopieren ZEILEN($1:2), ZEILEN($1:3) etc. etc...
Somit ist die Startzeile völlig egal bzw. braucht nicht durch -x angepasst zu werden.

Zudem vielleicht noch der Standardhinweis mit Strg-Shift-Enter zur Eingabe einer Array-Formel.

Grüße Boris
Anzeige
AW: Nachtrag 2
04.09.2003 08:08:00
Mac4
Moin Boris,

danke für den Tipp. Hatte mir auch schon überlegt, noch den Nachtrag 2 zu schreiben und auf den Anpassungsbedarf hinzuweisen.
Das hat sich dank Deiner Hilfe erledigt - und Dein Vorschlag ist natürlich in meinem Archiv gelandet!

Einen schönen Tag wünsch ich Dir,

Marc

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge