webentwicklung-frage-antwort-db.com.de

Kann die INDEX-Funktion von Excel ein Array zurückgeben?

Wenn die Daten im Bereich A1:A4 wie folgt sind:

Apple
Banana
Orange
Strawberry

Dann kann INDEX verwendet werden, um einen beliebigen Wert aus dieser Liste einzeln zurückzugeben, z.

= INDEX(A1:A4,3)

Würde Orange zurückgeben.

Gibt es ähnliche Excel-Funktionen oder eine Kombination von Funktionen, mit denen Sie so etwas wie folgt ausführen können:

= INDEX(A1:A4,{2;3})

Welches würde ein Array {Banana;Orange} zurückgeben?

Ist das möglich (vorzugsweise ohne VBA) und wenn ja, wie? Es fällt mir schwer, herauszufinden, wie man dies selbst mit Hilfe von Helferzellen erreichen kann.

Ich kann eine etwas komplizierte Lösung herausfinden, wenn die Daten Zahlen sind (mit MMULT), aber die Tatsache, dass es sich bei den Daten um Text handelt, stört mich, weil MMULT nicht mit Text funktioniert.

OFFSET ist wahrscheinlich die gewünschte Funktion. 

=OFFSET(A1:A4,1,,2)

Um Ihre Frage zu beantworten, kann INDEX tatsächlich dazu verwendet werden, ein Array zurückzugeben. Oder eher zwei INDEX-Funktionen mit einem Doppelpunkt zwischen ihnen:

=INDEX(A1:A4,2):INDEX(A1:A4,3)

Dies liegt daran, dass INDEX tatsächlich eine Zellreferenz OR eine Zahl zurückgibt, und Excel bestimmt, welche davon Sie abhängig vom Kontext wünschen, in dem Sie gefragt werden. Wenn Sie einen Doppelpunkt in die Mitte von zwei INDEX-Funktionen setzen, sagt Excel "Hey, ein Doppelpunkt ... Normalerweise gibt es auf jeder Seite einer dieser Zellen eine Zellreferenz" und interpretiert den INDEX genau so. Mehr dazu lesen Sie unter http://blog.excelhero.com/2011/03/21/the_imposing_index/

Ich ziehe INDEX lieber als OFFSET vor, weil OFFSET flüchtig ist, dh es wird ständig neu berechnet und zwingt alle nachfolgenden Formeln dazu, dasselbe zu tun. Weitere Informationen hierzu finden Sie in meinem Beitrag https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

Sie können tatsächlich nur one INDEX verwenden und ein Array zurückgeben, aber es ist kompliziert und erfordert so genannte Dereferenzierungen. Hier sind einige Inhalte aus einem Buch, das ich dazu schreibe: 

Das Arbeitsblatt in diesem Screenshot hat einen benannten Bereich namens "Daten", der oben im Bereich A2: E2 zugewiesen ist. Dieser Bereich enthält die Zahlen 10, 20, 30, 40 und 50. Außerdem hat er einen benannten Bereich namens Elemente, der dem Bereich A5: B5 zugeordnet ist. Dieser Elementebereich gibt der Formel in A8: B8 an, welche dieser fünf Zahlen aus dem Datenbereich angezeigt werden.

 enter image description here

Wenn Sie sich die Formel in A8: B8 ansehen, werden Sie feststellen, dass es sich um eine in das Array eingegebene INDEX-Funktion handelt: {= INDEX (Data, Elements)}. Diese Formel besagt: "Gehen Sie zum Datenbereich und rufen Sie Elemente ab, die auf den Elementen basieren, die der Benutzer im Elementbereich ausgewählt hat." In diesem speziellen Fall hat der Benutzer das fünfte und das zweite Element von ihm angefordert. Und das ist genau das, was INDEX in die Zellen A8: B8 holt: die entsprechenden Werte von 50 und 20.

Aber schauen Sie sich an, was passiert, wenn Sie die INDEX-Funktion verwenden und versuchen, ein SUM um sie herum zu setzen, wie in A11 gezeigt. Sie erhalten ein falsches Ergebnis: 50 + 20 ist nicht gleich 50. Was ist mit 20 passiert, Excel?

Während =INDEX(Data,Elements) unglücklicherweise ungleiche Elemente von irgendwo abruft und diese Zahlen dann separat in einen Bereich zurückgibt, ist dies aus irgendeinem Grund eher unwillig, wenn Sie sie bitten, diese Nummern stattdessen einer anderen Funktion zu geben. Es ist tatsächlich so ungern, dass nur das erste Element an die Funktion übergeben wird.

Folglich sind Sie scheinbar gezwungen, die Ergebnisse der = INDEX (Data, Elements) -Funktion zuerst an das Raster zurückzugeben, wenn Sie etwas anderes damit tun möchten. Langweilig. Aber so ziemlich jeder Excel-Profi würde Ihnen einfach sagen, dass es keine Problemumgehung gibt ... So ist es einfach und Sie haben keine andere Wahl.

Buuuuuuuut, sie sind falsch. In dem Beitrag http://excelxor.com/2014/09/05/index-returning-an-array-of-values/ beschreibt der mysteriöse Formel-Superheld XOR zwei recht einfache Wege, um „zu de -referenz “INDEX, damit Sie die Ergebnisse direkt in anderen Formeln verwenden können; Eine dieser Methoden ist oben in A18 dargestellt. Es stellt sich heraus, dass, wenn Sie die INDEX-Funktion geringfügig ändern, indem Sie ein zusätzliches Bit hinzufügen, um dieses Elements-Argument einzukapseln, INDEX den Ball spielt. Und alles, was Sie tun müssen, ist, dieses Elements-Argument zu umhüllen, wie ich es unten getan habe:

N(IF({1},Elements))

In diesem Sinne ist Ihre ursprüngliche Formel, die sich schlecht benimmt:

=SUM(INDEX(Data,Elements))

... wird zu einem komplexen, aber wohlerzogenen Liebling:

=SUM(INDEX(Data, N(IF({1},Elements))))
10
jeffreyweir

Sie können dieses Verhalten ohne Arrayformel erhalten. In sagen D1

=IFERROR(INDEX($A$1:$A$4,CHOOSE(ROWS($1:1),2,3)),"")

und abschreiben. Beachten Sie, dass 2,3 in die Funktion CHOOSE() eingebettet ist.

 enter image description here

Sie können 2,3 durch einen beliebigen Satz von Indizes ersetzen.

3
Gary's Student

Richtig

pdate

Sie sollten die Matrixformel verwenden:

Für = INDEX(A1:A4,{2;3}) schreibe = INDEX(A1:A4,ROW($A$2:$A$3))

Für die Verwendung der Matrixformel (1) select ​​der Bereich der Zellen, für die vertikale Ergebnisse ausgegeben werden sollen (zum Beispiel B1: B2), und drücken Sie dann (2) F2 und obige Formel eingeben, dann (3) drücken Ctrl + Shift + Enter.

pdate

Erklären:

Sie können row_num- und column_num-Teile von INDEX mit Array-Formeln steuern, um speziellere gewünschte Ergebnisse zurückzugeben.

Es gibt zwei Möglichkeiten, Array-Formelergebnisse zurückzugeben:

  • (ICH)

    1. Wählen Sie den Bereich aus, in dem die zurückgegebenen Ergebnisse angezeigt werden sollen.
    2. Drücken Sie F2
    3. Geben Sie Ihre Matrixformularformel ein.
    4. Dann drücken Ctrl + Shift + Enter.

Beispiel:

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW($A:$A)))
  • (II)

    1. Geben Sie die Matrixformel in die erste Zelle ein, in der die zurückgegebenen Ergebnisse angezeigt werden sollen (drücken Sie dann Ctrl + Shift + Enter), dann verlängern Sie die Formel.

Beispiel:

=INDEX($A$1:$A$4,SMALL(IF($B$2=$A$1:$A$4,ROW($A$1:$A$4)-ROW($A$1)+1),ROW(A1)))

Fazit

Die Formel INDEX funktioniert in Array-Form.

Sie müssen in einem Array row_num oder column_num eingeben. Verwenden Sie hierfür geeignete Array-Formeln als: IF, SMALL, CHOOSE. Beachten Sie, dass MATCH keine Matrixformel ist.

Temporäre Beispieldatei (für 30 Tage): book.xlsx

Sheet

0
mgae2m