Index und Aggregat mit Bereichsauswahl evtl. machbar ?

[Foregner]

Hallo !
Hab mal wieder ein Problem, wobei ich hoffe das ihr mir Helfen könnt.

Ich habe zwei Lagerräume, in jedem Lager sind zwei Regale eins Links und eins Rechts.
Die Tabelle ist Angeordnet nach:

ID-Nr/Lager/Artikelnummer/Bezeichnung/Anordnung1/Seite1/Anordnung2/Seite2/Anordnung3/Seite

Mit der ersten Formel Filter ich Lagerraum und Seite.

=WENNFEHLER(INDEX(Tabelle1[ID-Nr];AGGREGAT(15;6;ZEILE(Tabelle1[ID-Nr])/((Tabelle1[Lager]=$C$30)*(Tabelle1[Seite1]=$C$31));ZEILE()-34)-1;1);"")

Was mir die ID-Nummern meines Auswahlkriteriums Anzeigt. Da setzt meine zweite Formel an.
Da die Position des Materials, aus drei Anordnungen in den jeweiligen Lagerräumen besteht
lass ich mir durch die IndexBereichsauswahl, mit 1,2,3 die Daten der Berreiche Anzeigen.

WENNFEHLER(INDEX((Tabelle1[[Anordnung1]:[Seite1]];Tabelle1[[Anordnung2]:[Seite2]];Tabelle1[[Anordnung3]:[Seite3]]);VERGLEICH(B21;Tabelle1[ID-Nr];0);1;$C$18);"")

Was eigentlich auch Super Funktioniert.

Mein Problem ist, wenn Material die Seite wechselt, wird es Falsch Angezeigt.Da in der ersten Formel immer auf [Seite1] verwiesen wird.
hab auch schon einiges probiert, bin aber immer gescheitert.
Ich weiss man kriegt es mt drei Arbeitsblättern hin, würde aber gerne alle Daten aus einer Tabelle entnehmen.

LG Gerhard

[Elex]

Hi
 
Versuch mal die Formel.
 
'Matrixformel mit shift, strg, Enter abschließen
B35     =WENNFEHLER(INDEX(Tabelle1[ID-Nr];1/(KGRÖSSTE((Tabelle1[Lager]=$C$30)*(WAHL($C$32;Tabelle1[Seite1];Tabelle1[Seite2];Tabelle1[Seite3])=$C$31)/ZEILE($A$1:$A$12);ZEILE(A1))););"")

[Foregner]

Dankeschön, werd es später ausprobieren.

[Foregner]

Funktioniert, 1000 mal Dankeschön. was eigentlich meine freude nicht ausdrückt.
ich feier dich.........

[Jockel]

Hallo, und mit AGGREGAT() - also ohne CSE..:

PHP-Code:

=WENNFEHLER(AGGREGAT(15;6;$A$2:$A$13/($B$2:$B$13=$C$30)/(WAHL($C$32;$F$2:$F$13;$H$2:$H$13;$J$2:$J$13)=$C$31);ZEILE(A1));"") 


Und noch einwenig g'spielt..:

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C	D	E	F
30	Lager	Lager1
31	Seite	R
32	Bereiche 1-3	2
33
34	ID-Nr	Teilenummer	Bezeichnung	Anordnung	Seite
35	1	12-AB-2346	Schrauben M5	Regal-2	R
36	2	12-AB-2346	Schrauben M5	Regal-2	R
37	4	734-CK-12	Behälter H7	Regal-7	R
38
39
40

Zelle	Formel
B35	=WENNFEHLER(AGGREGAT(15;6;$A$2:$A$13/($B$2:$B$13=$C$30)/(WAHL($C$32;$F$2:$F$13;$H$2:$H$13;$J$2:$J$13)=$C$31);ZEILE(A1));"")
C35	=WENNFEHLER(SVERWEIS($B35;$A$1:$J$13;SPALTE(C$1);0);"")
D35	=WENNFEHLER(SVERWEIS($B35;$A$1:$J$13;SPALTE(D$1);0);"")
E35	=WENNFEHLER(SVERWEIS($B35;$A$1:$J$13;VERGLEICH(E$34&$C$32;$A$1:$J$1;0);0);"")
F35	=WENNFEHLER(SVERWEIS($B35;$A$1:$J$13;VERGLEICH(F$34&$C$32;$A$1:$J$1;0);0);"")

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Foregner]

@Jockel Dir auch Tausend Dank, Funktioniert Super.

Danke euch beiden für das Interesse, und vorallem die Zeit die ihr mit meinem
Problem verbracht habt.

[neopa]

Hallo Gerhard,

die prinzipielle Lösung Deiner Aufgabe durch Einsatz von WAHL(), haben Dir ja bereits Elex und Jörg aufgezeigt.

Ich würde nun noch dazu raten, in den Formeln konsequent den Bezug nur auf Deine formatierte Datentabelle zu nehmen. Damit bist Du unabhängig von evtl. Datenerweiterungen und Zeileneinschüben etc. Dies geht auch mit AGGREGAT() (die auch ich in Deiner Excelversion der klassischen Matrixformel bevorzugen würde) problemlos.
Den Namen Deiner formatierten Tabelle (jetzt Tabelle1) sollte man noch im Namensmanager in z.B. "Lagerdaten" umbenennen, damit die Formel noch etwas "sprechender" wird und sich damit auch vom Tabellenblattnamen unterscheidet.

Die der ID-Nr. zugeordneten Daten lassen sich entsprechend und mit nur 2 SVERWEIS() -Formeln ermitteln.
Formel für ID-Nr nach unten und die beiden SVERWEIS()-Formeln zusätzlich je eine Spalte nach rechts kopieren.

	B	C	D	E	F
30	Lager	Lager1
31	Seite	R
32	Bereiche 1-3	2
33
34	ID-Nr	Teilenummer	Bezeichnung	Anordnung	Seite
35	1	12-AB-2346	Schrauben M5	Regal-2	R
36	2	12-AB-2346	Schrauben M5	Regal-2	R
37	4	734-CK-12	Behälter H7	Regal-7	R
38

Formeln der Tabelle

Zelle Formel B35 =WENNFEHLER(AGGREGAT(15;6;Tabelle1[ID-Nr]/(Tabelle1[Lager]=$C$30)/(WAHL($C$32;Tabelle1[Seite1];Tabelle1[Seite2];Tabelle1[Seite3])=$C$31);ZEILE()-ZEILE(B$34));"") C35 =WENN($B35="";"";SVERWEIS($B35;Tabelle1[#Alle];SPALTE(Tabelle1[[#Kopfzeilen];[Artikelnummer]]);0)) E35 =WENN($B35="";"";SVERWEIS($B35;Tabelle1[#Alle];VERGLEICH(E$34&$C$32;Tabelle1[#Kopfzeilen]);0))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Folgender Hinweis jedoch noch hierzu:
Die Formeln sollte mE erweitert werden, z.B. derart:
=WENN(ANZAHL2(C$30:C$32)=3;[Formel];"Eingaben unvollständig")
oder Du müsstest definieren, wie ein eine jeweilige Fehleingabe gewertet werden soll.

Eine Frage hätte ich aber auch noch:
Mir erschließt sich nicht wirklich, warum Du offensichtlich nur die Daten der jeweiligen Anordnungs-Nr. auswerten willst, die in C32 ausgewählt wird, obwohl Du doch in B32 schreibst: "Bereiche 1-3". Müsste es da nicht richtig lediglich "Bereich:" lauten? Anderenfalls ergäbe sich auch eine andere mögliche Auswertungsinterpretation.

[Jockel]

Hallo Werner,  was ist jetzt neu in deinem Beitrag..? AGGREGAT() und SVERWEIS() hatte ich doch shon gepostet... Ich benutzte nur die normalen Zellbezüge...

[Foregner]

@Neopa Dir auch ein Dankeschön.

zu deiner Frage:
In dem Arbeitsblatt werte ich nur einen Bruchteil einer größeren Tabelle aus.
Dazu benötige ich nur die gefilterten ID-Nummern, um dieser einen von den
drei Bereichen zuzuordnen.
B32 ist nur als Beschriftung für C32 gedacht, da eine Zahl ansich nicht sehr aussagekräftig ist.