Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Sverweis und Index mit mehreren Ergebnissen

Sverweis und Index mit mehreren Ergebnissen
17.02.2014 09:54:07
manfred
Hallo Zusammen,
bin neu hier im Forum und habe die Suche nach meinem Problem bereits benutzt aber leider.....erfolglos
Ich habe folgende Problemstellung:
Ich habe eine Exceltabelle in der alle Aufträge mit Angabe Kundennummer, Auftragsnummer, Artikel und Verkaufsdatum in einer Zeile gelistet sind. Insgesamt sind das mehr als 4500 Zeilen mit einelnen Auftragsnummern.
Nun muss ich im nächsten Tabellenblatt eine Tabelle erstellen in der pro Kundennummer die drei genannten Kriterien (Bsp.: 505015 (Auftragsnummer), Weinglas (Artikel), 27.01.2010(Datum)) in den nächsten drei Feldern hinter der Kundennummer erscheinen.
Das Problem dabei ist, dass hinter einer Kundennummer auch mal 30 Datennsätze stecken, und alle 30 Datensätze immer in der gleichen Reihenfolge in der gleichen Zeile ausgegeben werden sollen.
Hab bereits mit INDEX und SVERWEIS probiert und probiert, aber leider versagen bei mir alle Lösungsansätze.
Ich hoffe ich konnte das Problem darstellen.
Das ganze soll als Grundlage für einen Serienbrief dienen, der an die einzelnen Kunden mit der Angabe der von Ihnen verkauften Artikeln und Verkaufsdatum etc. gesendet werden soll.
Vielen Dank im voraus für eingehende Hilfe
Gruß
Manfred

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis und Index mit mehreren Ergebnissen
17.02.2014 11:12:09
Maria
Hallo Manfred,
mal ne Frage: Willst du das die 30 Datensätze in einer Zelle erscheinen, also alle 30 Auftragsnummern in einer Zelle, oder willst du 30 Zeilen mit den Auftragsnummer? Für diesen Fall würde ich dir Pivot empfehlen, im anderen eventuell eine Matrixformel und ein Verketten der Ergebnisse...
Und noch eine Frage zur Reihenfolge? Du schreibst immer die gleiche Reihenfolge. Welche? Nach Datum aufsteigend oder genau so wie in deiner Ursprungstabelle? Wenn ein neuer Datensatz hinzu kommt, soll sich dieser dann unten angliedern oder an der richtigen Stelle einsortiert werden? Oder alles egal und Hauptsache bei Artikel, Auftragsnummer und Datum gleich?
Vielleicht ist den anderen das alles aus deiner Fragestellung klar, aber bevor ich dich mit Lösungsvorschlägen konvrontiere, die in deinem Fall gar nicht passen, frag ich lieber nochmal nach.
Schöne Grüße
Maria

Anzeige
eine Beispielauszug könnte hilfreich sein ...
17.02.2014 11:12:16
der
Hallo Manfred,
... möglicherweise kommst Du aber am schnellsten, wenn Du den Autofilter entsprechend einsetzt
Gruß Werner
.. , - ...

AW: eine Beispielauszug könnte hilfreich sein ...
17.02.2014 11:48:41
manfred
Hallo,
vielen dank für die Mühe.
Zur Erläuterung: Die erwähnten 30 Datensätze (bei mir im Bsp. besteht der aus drei Zellen -(Auftragsnummer, Artikel, Datum) sollen alle hintereinander, immer in der gleichen Reihenfolge (Auftragsnummer, Artikel, Datum)in der gleichen ZEILE hinter die Kundennummer ausgelesen werden.
Ich habe eine kleine Beispieldatei mit hochgeladen.
https://www.herber.de/bbs/user/89315.xlsx
Filtern möchte ich eigentlich nicht, da ich dann für jeden der ca. 500 Kunden filtern müsste und ich soll ja einen Serienbrief erzeugen. Ich bräuchte alle Daten in einzelnen Zellen zum ansteuern beim Serienbrief.
Viele Grüße
Manfred

Anzeige
mit nur zwei INDEX()-Matrixformeln ...
17.02.2014 12:36:54
der
Hallo Manfred,
... in G3 benutzerdefiniertes Zahlenformat: 0;; und die Formel nach unten kopieren.
Die Formel in H3 nach rechts und unten kopieren. Evtl. den Bereich (bei mir 1:99 an den bei Dir erforderlichen anpassen. Wenn es es ca. 5000 Matrixformeln werden, könnte die Berechnung natürlich etwas dauern.
 ABCDEFGHIJKLMNOP
1Beispieltabelle (vorhanden)     Ergebnis sollte so aussehen        
2AuftragsnummerKD-NummerArtikelDatum  KD-NummerAuftragsnummerArtikelDatumAuftragsnummerArtikelDatumAuftragsnummerArtikelDatum
323082110024Artikel 113.08.2012  10024230821Artikel 113.08.2012      
423614010029Artikel 207.06.2013  10029236140Artikel 207.06.2013      
522701710031Artikel 210.01.2012  10031227017Artikel 210.01.2012      
622874110033Artikel 219.04.2012  10033228741Artikel 219.04.2012239213Artikel 214.11.2013   
723921310033Artikel 214.11.2013  11331234989Artikel 209.04.2013235821Artikel 122.05.2013237332Artikel 306.08.2013
823498911331Artikel 209.04.2013            
923582111331Artikel 122.05.2013            
1023733211331Artikel 306.08.2013            
11                
12                

Formeln der Tabelle
ZelleFormel
G3{=INDEX(B:B;2+MIN(WENN(ISTNV(VERGLEICH(B$3:B$99;G$2:G2;)); ZEILE($1:$97))))+0}
H3{=WENN(KÜRZEN((SPALTE(A1)-1)/3)+1>ZÄHLENWENN($B:$B;$G3); "";INDEX($A:$D;KKLEINSTE(WENN($B$1:$B$99=$G3;ZEILE($1:$99)); KÜRZEN((SPALTE(A1)-1)/3)+1); VERGLEICH(H$2;$A$2:$D$2;)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: mit nur zwei INDEX()-Matrixformeln ...
17.02.2014 13:44:20
manfred
Hallo Werner,
vielen Dank für deinen Ansatz. Aber bei bestem Willen...ich komme nicht klar mit den Formeln. Ich habe die Formeln genauso eingebaut aber bei mir kommt bereits bei der ersten Formel #NV. (Matrixformel natürlich eingegeben :-) )
Kannst du mir auch diesen Part erklären.
G3 {=INDEX(B:B;2+MIN(WENN(ISTNV(VERGLEICH(B$3:B$99;G$2:G2;)); ZEILE($1:$97))))+0}
Ich raffs nicht was das bedeutet.
Was ich natürlich vergessen habe, war der Umstand, dass die Datentabelle in einem Tabellenblatt ist und die Auswertung im nächsten Tabellenblatt.
Ich bin mir nicht ganz sicher welche Auswirkungen dies auf deine Formel hat.
Viele Grüße
Manfred

Anzeige
Formeln entsprechend angepasst
17.02.2014 14:30:36
der
Hallo Manfred,
... so dass die Auswertung nun in einem eigenem Tabellenblatt vorgenommen wird.
A3 nach unten und B3 nach rechts und unten kopieren:
Auswertung

 ABCDEFGHIJ
1Ergebnis sollte so aussehen        
2KD-NummerAuftragsnummerArtikelDatumAuftragsnummerArtikelDatumAuftragsnummerArtikelDatum
310024230821Artikel 113.08.2012      
410029236140Artikel 207.06.2013      
510031227017Artikel 210.01.2012      
610033228741Artikel 219.04.2012239213Artikel 214.11.2013   
711331234989Artikel 209.04.2013235821Artikel 122.05.2013237332Artikel 306.08.2013
8          

Formeln der Tabelle
ZelleFormel
A3{=INDEX(Daten!B:B;2+MIN(WENN(ISTNV(VERGLEICH(Daten!B$3:B$99;A$2:A2;)); ZEILE($1:$97))))+0}
B3{=WENN(KÜRZEN((SPALTE(A1)-1)/3)+1>ZÄHLENWENN(Daten!$B:$B;$A3); "";INDEX(Daten!$A:$D;KKLEINSTE(WENN(Daten!$B$1:$B$99=$A3;ZEILE($1:$99)); KÜRZEN((SPALTE(A1)-1)/3)+1); VERGLEICH(B$2;Daten!$A$2:$D$2;)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Daten

 ABCD
1Beispieltabelle (vorhanden)   
2AuftragsnummerKD-NummerArtikelDatum
323082110024Artikel 113.08.2012
423614010029Artikel 207.06.2013
522701710031Artikel 210.01.2012
622874110033Artikel 219.04.2012
723921310033Artikel 214.11.2013
823498911331Artikel 209.04.2013
923582111331Artikel 122.05.2013
1023733211331Artikel 306.08.2013
11    


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige

368 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige