Untersuche diese Figur:
Angenommen, Sie möchten daraus einen Bericht erstellen, als ob Sie nach der Region gefiltert hätten. Das heißt, wenn Sie nach Norden filtern, sehen Sie:
Aber was ist, wenn Sie eine formelbasierte Version derselben Sache wollen?
Hier ist das gesuchte Ergebnis in den Spalten I: K:
Es ist natürlich der gleiche Bericht, aber hier gibt es keine gefilterten Elemente. Wenn Sie einen neuen Bericht über Ost wünschen, wäre es schön, einfach den Wert in G1 in Ost zu ändern:
Hier ist, wie es gemacht wird. Erstens wird VLOOKUP nicht verwendet. Also habe ich über den Titel dieser Technik gelogen!
Spalte F wurde zuvor nicht angezeigt und kann ausgeblendet (oder an einen anderen Ort verschoben werden, damit der Bericht nicht beeinträchtigt wird).
In Spalte F werden die Zeilennummern angezeigt, in denen sich G1 in Spalte A befindet. Das heißt, welche Zeilen enthalten den Wert "Nord"? Bei dieser Technik wird die obige Zelle verwendet, sie muss also mindestens in Zeile 2 beginnen. Sie entspricht dem Wert „Nord“ für Spalte A, verwendet jedoch anstelle der gesamten Spalte eine OFFSET-Funktion : OFFSET($A$1,F1,0,1000,1).
Da F1 0 ist, ist OFFSET(A1,0,0,1000,1)dies A1: A1000. (Die 1000 ist willkürlich, aber groß genug, um die Arbeit zu erledigen - Sie können eine beliebige andere Zahl festlegen).
Der Wert 2 in F2 ist der erste „Norden“. Sie möchten auch den Wert von F1 am Ende wieder hinzufügen, dies ist jedoch bisher Null.
Die „Magie“ wird in Zelle F3 lebendig. Sie wissen bereits, dass der erste Norden in Zeile 2 gefunden wird. Sie möchten also zwei Zeilen unter A1 suchen. Sie können dies tun, indem Sie 2 als zweites Argument der OFFSET-Funktion angeben.
Die Formel in F3 wird automatisch auf die 2 weist die in Zelle F2 berechnet: Wenn man die Formel nach unten zu kopieren, sehen Sie , =OFFSET($A$1,F2,0,1000,1)was OFFSET($A$1,2,0,1000,1)das ist , A3: A1000. Sie vergleichen also North mit diesem neuen Bereich und es findet North in der dritten Zelle dieses neuen Bereichs, sodass der MATCH 3 ergibt.
Wenn Sie den Wert aus der obigen Zelle F2 zurückaddieren, sehen Sie die 3 plus die 2 oder 5, die die Zeile ist, die den zweiten Norden enthält.
Diese Formel ist weit genug ausgefüllt, um alle Werte zu erhalten.
Dadurch erhalten Sie die Zeilennummern, in denen alle Norddatensätze gefunden werden.
Wie übersetzen Sie diese Zeilennummern in die Ergebnisse in den Spalten I bis K? Es wird alles mit einer einzigen Formel gemacht. Geben Sie diese Formel in I2 ein : =IFERROR(INDEX(A:A,$F2),””). Nach rechts kopieren und dann nach unten kopieren.
Warum IFERROR verwenden? Wo ist der Fehler? Beachten Sie die Zelle F6 - sie enthält # N / A (weshalb Sie Spalte F ausblenden möchten), da nach Zeile 15 keine Nords mehr vorhanden sind. Wenn also Spalte F ein Fehler ist, geben Sie ein Leerzeichen zurück. Nehmen Sie andernfalls den Wert aus Spalte A (und, wenn richtig gefüllt, B & C).
Das $ F2 ist eine absolute Referenz auf Spalte F, daher bezieht sich das Füllrecht immer noch auf Spalte F.
Dieser Gastartikel stammt von Excel MVP Bob Umlas. Es ist eine seiner Lieblingstechniken aus seinem Buch Excel Outside the Box.
Excel außerhalb der Box »
