Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Sverweis weitersuchen - Nächste Zelle

Forumthread: 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
Anzeige

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
Anzeige
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
Anzeige
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
Anzeige
;
Anzeige

Infobox / Tutorial

Sverweis für die nächste Zelle nutzen


Schritt-für-Schritt-Anleitung

Um alle zugehörigen Namen für eine Kostenstelle in Excel anzuzeigen, kannst Du die folgende Matrixformel verwenden. Dabei wird angenommen, dass die Namen in Spalte A und die Kostenstellen in Spalte B stehen, während der Suchbegriff (die Kostenstelle) in Zelle E1 steht.

  1. Wähle die Zelle aus, in der die Ergebnisse beginnen sollen (z.B. C1).

  2. Gib die folgende Formel ein:

    {=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())))}
  3. Bestätige die Eingabe mit Strg + Shift + Enter, um die Formel als Array-Formel einzugeben.

  4. Ziehe die Formel nach unten, um die nächsten Ergebnisse zu erhalten.

Diese Formel sucht in der angegebenen Datenreihe nach der Kostenstelle und gibt die dazugehörigen Namen in aufeinanderfolgender Reihenfolge aus.


Häufige Fehler und Lösungen

  • Fehler: Die Formel gibt nur leere Zellen zurück.

    • Lösung: Stelle sicher, dass das Suchkriterium in der Zelle E1 korrekt ist und dass die entsprechenden Werte in A und B vorhanden sind.
  • Fehler: "FALSCH" wird angezeigt.

    • Lösung: Überprüfe den Bereich der Formel (z.B. $1:$8), um sicherzustellen, dass die Daten in diesen Zeilen vorhanden sind.
  • Fehler: Die Formel funktioniert nicht nach dem Kopieren.

    • Lösung: Achte darauf, dass die Formel in der ersten Zeile beginnt. Verwende die Funktion ZEILEN($1:1), um die Zeilen dynamisch anzupassen.

Alternative Methoden

Eine alternative Methode, um die gleichen Informationen in Excel zu extrahieren, ist die Verwendung von Power Query:

  1. Lade Deine Tabelle in Power Query.
  2. Verwende die Funktion "Gruppieren nach", um die Namen nach Kostenstellen zu gruppieren.
  3. Lade die Ergebnisse zurück in Excel.

Diese Methode ist besonders nützlich für größere Datensätze und bietet eine benutzerfreundliche Oberfläche.


Praktische Beispiele

Angenommen, Du hast folgende Daten:

A (Namen) B (Kostenstelle)
Max 1000
Lisa 1000
Tom 2000
Anna 1000

Wenn Du in Zelle E1 die Kostenstelle 1000 eingibst und die obige Formel in C1 verwendest, bekommst Du folgendes Ergebnis:

C (Ergebnisse)
Max
Lisa

Wenn Du die Formel nach unten ziehst, wird die nächste Zelle für weitere Namen angezeigt.


Tipps für Profis

  • Nutze WENNFEHLER, um die Handhabung von Fehlern zu verbessern:

    {=WENNFEHLER(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()))); "")}
  • Experimentiere mit benutzerdefinierten Datenbereichen, um die Flexibilität zu erhöhen.

  • Halte Deine Daten gut strukturiert, um die Fehleranfälligkeit zu minimieren.


FAQ: Häufige Fragen

1. Wie funktioniert die KKLEINSTE-Funktion in der Formel?
Die KKLEINSTE-Funktion gibt den n-kleinsten Wert aus einem bestimmten Bereich zurück. In dieser Formel wird sie verwendet, um die Zeilennummern der übereinstimmenden Kostenstellen zu ermitteln.

2. Muss ich die Formel in der ersten Zeile eingeben?
Ja, die Formel sollte in der ersten Zeile der Ergebnisse stehen, um korrekt zu funktionieren. Andernfalls musst Du die Bezugnahmen in der Formel manuell anpassen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige