Formel für eine Abfrage über mehrere Tabellenblätter

[Egon12]

die Formel erweitert bis zu letzten gefüllten Zelle in A derzu durchsuchenden Datensätze:

Code:

=LET(
  xLa; MAX(FILTER(ZEILE(Hausmann!A4:A1000); Hausmann!A4:A1000<>""));
  xLb; MAX(FILTER(ZEILE(Großhandel!A4:A1000); Großhandel!A4:A1000<>""));
  xLc; MAX(FILTER(ZEILE(Einzelhandel!A4:A1000); Einzelhandel!A4:A1000<>""));
  xBa; BEREICH.VERSCHIEBEN(Hausmann!A4; 0; 0; xLa - 3; 1);
  xAfk; BEREICH.VERSCHIEBEN(Hausmann!F4; 0; 0; xLa - 3; SPALTEN(Hausmann!F4:K4));
  xBb; BEREICH.VERSCHIEBEN(Großhandel!A4; 0; 0; xLb - 3; 1);
  xBfk; BEREICH.VERSCHIEBEN(Großhandel!F4; 0; 0; xLb - 3; SPALTEN(Großhandel!F4:K4));
  xBc; BEREICH.VERSCHIEBEN(Einzelhandel!A4; 0; 0; xLc - 3; 1);
  xCfk; BEREICH.VERSCHIEBEN(Einzelhandel!F4; 0; 0; xLc - 3; SPALTEN(Einzelhandel!F4:K4));
  xA; FILTER(xAfk; (xBa=A3) * (MMULT(--(xAfk<>""); MTRANS(SPALTE(Hausmann!F4:K4)^0))>0); "");
  xB; FILTER(xBfk; (xBb=A3) * (MMULT(--(xBfk<>""); MTRANS(SPALTE(Großhandel!F4:K4)^0))>0); "");
  xC; FILTER(xCfk; (xBc=A3) * (MMULT(--(xCfk<>""); MTRANS(SPALTE(Einzelhandel!F4:K4)^0))>0); "");
  VSTAPELN(WENNS(INDEX(xA;1;1)<>""; xA;INDEX(xB;1;1)<>""; xB;INDEX(xC;1;1)<>""; xC)))

Gruß Uwe

[maninweb]

Hallo,

Du hast recht, ich habe mich leider um eine Position vertan. Es muss nicht SEQUENZ(1; 6; 6) sondern SEQUENZ(1; 6; 7) sein.

Meine Formel hat sich zudem bei der Suche nur nach dem Kunden orientiert. Da habe ich Dich wohl falsch verstanden,
und es soll auch die Angebotsnummer berücksichtigt werden - sprich die zuerst hinterlegte gefunden werden.
Dann sieht die Formel wie folgt aus...

Code:

F3=LET(vnKunde; A3;
       vnQuelle; {"Hausmann";"Großhandel";"Einzelhandel"};
       vnHausmann; Hausmann!$A$4:$K$13;
       vnGrosshandel; Großhandel!$A$4:$K$13;
       vnEinzelhandel; Einzelhandel!$A$4:$K$13;
       vnSuchmatrix; VSTAPELN(HSTAPELN(SEQUENZ(ZEILEN(vnHausmann); 1; 1; 0); vnHausmann);
                              HSTAPELN(SEQUENZ(ZEILEN(vnGrosshandel); 1; 2; 0); vnGrosshandel);
                              HSTAPELN(SEQUENZ(ZEILEN(vnEinzelhandel); 1; 3; 0); vnEinzelhandel));
       vnKundensuche; FILTER(vnSuchmatrix; INDEX(vnSuchmatrix; 0; 2) = vnKunde; 0);
       vnAngebotssuche; WENN(INDEX(vnKundensuche; 1; 1) <> 0; FILTER(vnKundensuche; INDEX(vnKundensuche; 0; 7) <> ""; 0); 0);
       vnErgebniszeile; WENN(INDEX(vnAngebotssuche; 1; 1) <> 0; HSTAPELN(INDEX(vnAngebotssuche; 1; SEQUENZ(1; 6; 7)); INDEX(vnQuelle; INDEX(vnAngebotssuche; 1; 1); 1)); "");
       WENN(LÄNGE(vnErgebniszeile) > 0; vnErgebniszeile; ""))

Gruß

[anewelle]

Hi Uwe!
Die Formel funktioniert perfekt. Aber ich habe keine Ahnung was du da tolles geschrieben hast 

Viele Grüße
Anne

[Egon12]

Hallo Anne,

da dies wohl funktioniert habe ich mal noch die Ausgabe des Tabellenblattes, wo der Datensatz gefunden wurde eingebaut.

Überschaubarer für deine Zwecke ausreichender vereinfachter Lösungsweg (ich hatte das gestern Abend viel zu kompliziert angegangen), welcher nun auch das runterziehen sicherstellt:

Code:

=LET(
  xA;FILTER(Hausmann!$F$4:$K$1000;Hausmann!$A$4:$A$1000=A5;"");
  xB;FILTER(Großhandel!$F$4:$K$1000;Großhandel!$A$4:$A$1000=A5;"");
  xC;FILTER(Einzelhandel!$F$4:$K$1000;Einzelhandel!$A$4:$A$1000=A5;"");
xD;WENNS(INDEX(xA;1;1)<>""; xA;INDEX(xB;1;1)<>""; xB;INDEX(xC;1;1)<>""; xC);
xE;WENNS(INDEX(xA;1;1)<>"";"Hausmann";INDEX(xB;1;1)<>""; "Großhandel";INDEX(xC;1;1)<>""; "Einzelhandel");
HSTAPELN(xD;xE))

Alle mit x benannten Elemente der Formel sind Variablen in der LET() Funktion, welche entweder mit Arrays oder mit Werten gefüllt werden. 

Mit der Funktion FILTER() wird nach der passender Kundennummer gesucht und ein Trefferarray in die betreffende Variable übergeben. 

Die WENNS() Abfrage prüft ob in den jeweiligen Arrayzellen Zellen der Spalte 1 ein Wert enthalten ist. Damit werden Treffer ohne Wert ausgeschlossen. 

HSTAPELN() setzt in der Zeile die beiden Variablen hintereinander (stapelt Horizontal). 

Gruß Uwe

[snb]

Oder einfach in F3:

PHP-Code:

=INDEX(Hausmann!F$4:F$13;MATCH(Übersicht!$A3;Hausmann!$A$4:$A$13;0))&INDEX(Großhandel!F$4:F$13;MATCH(Übersicht!$A3;Großhandel!$A$4:$A$13;0))&INDEX(Einzelhandel!F$4:F$13;MATCH(Übersicht!$A3;Einzelhandel!$A$4:$A$13;0))


 nach unten und nach rechts ziehen.

[Egon12]

Hallo Anne,

Um sich das Ziehen zu ersparen dann so in Zelle F3:

Code:

=MATRIXERSTELLEN(ANZAHL2(Übersicht!A3:A1000);ANZAHL2(F2:Z2);
  LAMBDA(pA;pB;LET(
      xA; INDEX(Übersicht!A3:A1000; pA);
      xB; FILTER(Hausmann!F4:K1000; Hausmann!A4:A1000 = xA; "");
      xC; FILTER(Großhandel!F4:K1000; Großhandel!A4:A1000 = xA; "");
      xD; FILTER(Einzelhandel!F4:K1000; Einzelhandel!A4:A1000 = xA; "");
      xE; WENNS(INDEX(xB;1;1)<>""; xB; INDEX(xC;1;1)<>""; xC; INDEX(xD;1;1)<>""; xD);
      xF; WENNS(INDEX(xB;1;1)<>""; "Hausmann"; INDEX(xC;1;1)<>""; "Großhandel"; INDEX(xD;1;1)<>""; "Einzelhandel");
      WENN(INDEX(xE;1;1)=""; ""; WENN(pB<=6; INDEX(xE; 1; pB); xF)))))

Die Spalten ab F3:L & letzte Zeile muss leer geräumt sein, damit sich die Matrix ausbreiten kann, da sonst #Überlauf ausgegeben wird.

Gruß Uwe

[anewelle]

Allen vielen vielen lieben Dank! Es hat mir sehr weitergeholfen! Auch wenn ich mit dem LET Befehlt echt überfodert bin

Tausend Dank, dass ihr hier so super weiterhelft!!!!!

[Klaus-Dieter]

Hallo zusammen,

zunächst einmal meinen Respekt für alle Formellösungen, die hier vorgestellt wurden. Trotzdem möchte ich noch einmal auf meinen Hinweis unter #7 zurückkommen. Das unten abgebildete Ergebnis habe ich erreicht, indem ich, wie vorgeschlagen, alle Inhalte auf einem Blatt zusammen kopiert habe. In dieser Liste habe ich mit dem Autofilter die Zeilen ohne Inhalt ausgefiltert. Das hat das gleiche Ergebnis gebracht, wie die Formel von snb, nur ganz ohne Formel:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Kunde	Firma	Strasse	PLZ	Ort	Angebotsnr.	Laufzeit in Monaten	Währung	Einmalgebühr	Monatliche Gebühr	Anbieter	Liste
2	1000	Auherhahn	Bahnhofstrasse	45654	Düsseldorf	55	60	CHF	1000	540	Gipfli	Hausmann
3	1003	Wanschqua	Meisenweg	19889	Berlin	687-98	40	GBP	450	123	Wilgo Ltd.	Hausmann
4	1004	Luzmatique	Silvanallee	60331	Frankfurt	487111	36	EUR	1260	323	Hangbert GmbH	Hausmann
5	1008	Extraller	Burgstift	75642	Stuttgart	123-442-5J	36	EUR	325	12	Schmitt & Co	Hausmann
6	1001	Multitool	Gewerbegebiet	89345	München	113353	12	EUR	5000	678	Severatin	Großhandel
7	1007	Lexono	Amargasse	56981	Köln	65-44-AQ-J89	23	EUR	2350	350	Santano	Großhandel
8	1002	Aquamare	Kohlstrasse	21099	Hamburg	AB-432-GK	24	USD	4500	562	Global Care	Einzelhandel
9	1005	Wohnquartier	Berliner Allee	10981	Rostock	GL-11-32-J	60	ZAR	12490	5687	Safari Co.	Einzelhandel
10	1006	Taratulla	Spinnweg	41878	Neuss	5642-111	60	EUR	498	23	Malert GmbH	Einzelhandel
11	1009	Kaballo	Menzstrasse	34562	Braunschweig	5A-333AQ-23	60	GBP	2500	768	Britisch Company	Einzelhandel

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8