Hallo liebe Excelgemeinde,
bin auch der Sucher nach einer Formel und komme einfach nicht drauf :(
Tabelle1
Spalte A = Artikelnummer
Spalte B = Charge
Tabelle2
Hier möchte ich eine Formel haben, die in Tabelle1 nachguckt welche Artikel die Chargenummer XX und yy hat.
Nur wenn beide Chargen vorhanden! Wenn nur eine Charge dann nicht!
Ich hoffe das ist verständlich! :)
Danke im Voraus
Alexandra
C2:
=(SUMME(INDEX(N(ZÄHLENWENNS(A:A;A2;B:B;E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)
(in der angehängten Datei)[
attachment=13025]
Überschreitest Du eine gewisse Menge an Datensätzen, ist folgende genauere und längere Formel schneller (ist in der Datei nicht verwendet):
C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
A:A;A2;
B:B;
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)
wird dann zu (blau: verlängerte Formelteile!)
C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2;A:A;)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2;A:A;)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)
Eine weitere Optimierung ist möglich, wenn ein Dummydatensatz mit Artikelnr. 0 vorangestellt wird und die Art-Nr
positiv-ganzzahlig sind. Dann kann auch der vordere VERGLEICH mit dem Parameter ;1 statt ;0 laufen:
C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2
-1%;A:A)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2
-1%;A:A)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)
Alexandra,
jenseits von Formeln:
Filter
Advanced Filter
Pivot-Tabelle
Power Query
Hallo Alexandra,
wie eben sehe, hat Lupo schon ein Vorschlag eingestellt. Da ich mich aber nun damit beschäftigt habe, hier noch mein Formelvorschlag (der einfacheren Übersicht halber im gleichen Tabellenblatt) .
Formel E2 (setzt mindestens die Excel 2010er Version voraus) einfach nach unten kopieren.
Für eine Massendatenauswertung würde ich es wohl mit Hilfe einer Pivotdatenauswertung vornehmen. Mit PowerQuery (hab ich nicht) sollte es aber vielleicht noch einfacher gehen.
| A | B | C | D | E |
| 1 | Artikel | Charge | | | Ergebnis |
| 2 | A_01 | aa | | | A_02 |
| 3 | A_02 | xx | | | A_03 |
| 4 | A_03 | xy | | | |
| 5 | A_01 | xx | | | |
| 6 | A_02 | bb | | | |
| 7 | | | | | |
| 8 | A_03 | yy | | | |
| 9 | A_02 | yy | | | |
| 10 | | | | | |
| 11 | A_04 | xx | | | |
| 12 | A_03 | xx | | | |
| 13 | | | | | |
| Formeln der Tabelle |
| Zelle | Formel | | E2 | {=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(A$2:A$99)/((ZÄHLENWENNS(A$2:A$99;A$2:A$99;B$2:B$99;"xx")>0)+(ZÄHLENWENNS(A$2:A$99;A$2:A$99;B$2:B$99;"yy")>0)=2)/(ZÄHLENWENN(E$1:E1;A$2:A$99)=0);1));"")} |
|
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen! |
| Matrix verstehen |
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
(29.08.2017, 12:35)lupo1 schrieb: [ -> ]Eine weitere Optimierung ist möglich, wenn ein Dummydatensatz mit Artikelnr. 0 vorangestellt wird und die Art-Nr positiv-ganzzahlig sind. Dann kann auch der vordere VERGLEICH mit dem Parameter ;1 statt ;0 laufen:
C2: =(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A2-1%;A:A)):INDEX(A:A;VERGLEICH(A2;A:A));A2;
INDEX(B:B;VERGLEICH(A2-1%;A:A)):INDEX(B:B;VERGLEICH(A2;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A2<>A1)
Das war nicht korrekt. Richtig muss die Formel, jetzt mit Dummyzeile 2 (mit A2:
0), so heißen:
C3:
=(SUMME(INDEX(N(ZÄHLENWENNS(
INDEX(A:A;VERGLEICH(A3-1%;A:A)+1):INDEX(A:A;VERGLEICH(A3;A:A));A3;
INDEX(B:B;VERGLEICH(A3-1%;A:A)+1):INDEX(B:B;VERGLEICH(A3;A:A));
E$2:INDEX(E:E;ANZAHL2(E:E)))>0);))=ANZAHL2(E:E)-1)*(A3<>A2)