WENN WENN DANN INDEX DIES oder INDEX MATCH&MATCH?

Question

Hier ist ein Ansatz zum Erstellen dynamischer Listen von Elementen in Excel.

Angenommen, Sie hätten ein Array mit den Zeilennummern, in denen Ihre Kriterien liegen TRUE(d. h. Käufercode = JDH und Rest > 0). Dann könnten Sie dieses Array einer SMALL()Funktion zuführen, die die Zeilennummern auflistet, sobald sie ausgefüllt wurden. Und schließlich könnten Sie dies SMALL()als row_numsin einer INDEX()Funktion verwenden, die die entsprechenden Werte jeder Spalte auflistet, die Sie mit dem column_numParameter angeben.

Beginnen wir also damit, ein Array der Zeilennummern zu erhalten. Der Einfachheit halber gehe ich davon aus, dass Ihre Datentabelle nur 25 Zeilen hat und dass sich das Dashboard auf demselben Blatt wie die Daten befindet. Außerdem verwende ich den Käufercode ELN, der sich in Zelle B30 befindet.

Zuerst berechnen wir ein Array von TRUE/FALSEWerten, die Ihren Kriterien entsprechen:

($C$1:$C$25=$B$30)*($G$1:$G$25>0)

Der erste Ausdruck ergibt ein Array von TRUE/FALSEWerten, bei denen Käufercode (Spalte C) = ELN ist, und der zweite ergibt ein Array von TRUE/FALSEWerten, bei denen Rest (Spalte G) > 0 ist. Bei arithmetischen Operationen mit logischen Werten werden TRUEund FALSEin 1en und 0en umgewandelt, daher ergibt diese Multiplikation ein Array von 1en und 0en mit 1en an den Positionen, an denen Ihre Kriterien beide zutreffen TRUE.

Jetzt müssen wir dieses Array in eine Liste der Zeilennummern umwandeln, die Ihren Kriterien entsprechen TRUE. Wenn wir ein Array fortlaufender Zahlen durch dieses Array aus Einsen und Nullen dividieren, erhalten wir ein Array der Zeilennummern, wo immer eine 1 vorkommt, durchsetzt mit #DIV/0!Fehlern, wo immer eine 0 vorkommt.

ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))

SMALL()Als nächstes verwenden wir anstelle von AGGREGATE(), was dasselbe bewirkt wie SMALL()(function=15), außer dass es eine Option zum Ignorieren von Fehlern hat (option=6). AGGREGATE()Kann auch Arrays verarbeiten, ohne sie mit eingeben zu müssen CTRLShiftEnter.

Daher werden in der folgenden Formel AGGREGATE()die #DIV/0!Fehler ignoriert und ein Array ausgegeben, das nur die Zeilennummern enthält, die Ihren Kriterien entsprechen:

AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)

Ich habe diese Formel in Zeile 34, also bewirkt „-33“, dass die Funktion bei 1 beginnt und die Zeilennummern auflistet, wenn sie ausgefüllt werden. Ändern Sie diesen Parameter entsprechend Ihrer Situation.

Jetzt können wir dies in einem INDEX() verwenden und das Ganze in ein IFERROR()Einfügen von Leerzeichen einschließen, wenn es INDEX()ausgeht, row_numswährend es nach unten ausgefüllt wird. Diese Formel, nach unten ausgefüllt und rechts von B34:

=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")

liefert die unten gezeigten Ergebnisse.

Für die ELN des Käufers ist nur in den Zeilen 5 und 11 der Wert 0 verbleibend, und die Formel listet alle Zeilen außer diesen auf.

Beachten Sie, dass ich mich auf die Dashboard-Überschriften in der bezogen habe, MATCH()die die ergeben, indem ich als column_num'sverwendet habe . Wenn die Formel quer ausgefüllt ist, funktioniert dies für die Bestellnummer und die Beschreibung, aber die anderen Überschriften im Dashboard sind nicht dieselben wie die Überschriften in Ihrer Datentabelle.B$32lookup_value

Also habe ich für die anderen Spalten den Zellbezug B$32durch Anführungszeichen ersetzt: „LINE_NBR“ für die Spalte „PO Line“, „QUANTITY“ für die Spalte „Qty Ordered“ und „REMAINING“ für die Spalte „Qty Received“. Diese letzte Spalte beinhaltet eine Berechnung, daher lautet die Formel eigentlich:

=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")

Die letzte Spalte „Benötigt“ wird wie folgt berechnet:

=E34-F34, und nach unten ausgefüllt.

Ich hoffe, das hilft, und viel Glück.

Answer 1