Ich habe ein Arbeitsblatt, in dem ich die Index-/Matching-Formel nutzen möchte, um genaue Werte für die eindeutige Schnittmenge von Abteilungs- und Kontowerten in ein separates Datenblatt einzufügen.
Allerdings muss das Index-Array flexibel sein, da ich über mehrere Spalten hinweg nach derselben „eindeutigen“ Schnittmenge von Abteilungs- und Kontowerten suche (die Spalten stellen die Monate in meinem Datenblatt dar).
Ich habe versucht, eine indirekte Formel zu verwenden, indem ich auf den Wertebereich im Datenblatt verwiesen habe. Ich habe auch eine andere Variante der indirekten Formel ausprobiert, indem ich meinen Bereich benannt habe. Beide Formeln liefern keine Ergebnisse, obwohl ich weiß, dass an meinem Schnittpunkt Werte vorhanden sind. Wenn ich mein Index-Array hart codiere, funktioniert die Formel und ich erhalte die Ergebnisse, die in mein Arbeitsblatt eingetragen werden.
Hier sind jetzt meine Formeln:
=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Wobei _2015_December_Act ein benannter Bereich auf meiner Essbase-Datenregisterkarte für Werte im Bereich von Q8:Q356 ist.
und auch:
=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Wobei A8 = Essbase-Daten und b8 = Q8:Q356 – dieser Bereich stellt die Werte im Monat Dezember auf meinem Essbase-Datenblatt dar.
Was mache ich falsch?
Ich versuche, die Aktualisierung der Array-Referenz zu automatisieren, sodass der Bericht monatlich mit minimalen manuellen Aktualisierungen des Index-Arrays aktualisiert werden kann.
Antwort1
Essbase Data
| Field Name | Jan - 2016 | Feb - 2016 | Mar - 2016 |
| Key 1 | A | B | C |
| Key 2 | D | E | F |
| Key 3 | G | H | I |
| Key 4 | J | K | L |
| Key 5 | M | N | O |
Report
| Date: | Mar - 2016 |
| Tab: | Essbase Data |
| Date range: | ? |
| Field range: | ? |
| Data range: | ? |
| Column: | ? |
| | |
| Field Name | Value |
| Key 1 | ? |
| Key 2 | ? |
| Key 3 | ? |
| Key 4 | ? |
| Key 5 | ? |
| ... | ... |
Identifizieren Ihrer Bereiche
*Remote-Tab-Namen, die Leerzeichen enthalten, sollten in einfache Anführungszeichen ( ) eingeschlossen werden 'Tab name'. Könnte an einer schlechten Schriftart liegen, aber aus meiner Sicht sieht es so aus, als ob sie in Ihren obigen Formeln fehlen.
Zunächst möchten Sie den Bereich ermitteln, in dem Ihre Fixpunkte übereinstimmen. Datumsbereich:
="'" & B2 & "'!$1:$1"
Eine Beispielausgabe wäre 'Essbase Data'!$1:$1. Dies wird für MATCH()die gewünschte Spalte verwendet.
Zweitens möchten Sie den Bereich für die Übereinstimmung Ihrer Feldnamen ermitteln. Feldbereich:
="'" & B2 & "'!$A:$A"
Eine Beispielausgabe wäre 'Essbase Data'!$A:$A. Dies wird für MATCH()die gewünschte Zeile verwendet.
Zuletzt möchten Sie den Bereich Ihres gesamten Datensatzes identifizieren. Datenbereich:
="'" & B2 & "'!$A:$D"
Beispielausgabe wäre 'Essbase Data'!$A:$D. Dies wird für INDEX()die gewünschte Spalte und Zeile verwendet. Sie können COUNTA()und verwenden, ADDRESS()um diese dynamisch zu konstruieren. ie
="'" & B2 & "'!$A$1:" & ADDRESS(COUNTA('Essbase Data'!A:A), COUNTA('Essbase Data'!1:1))
Beispielausgabe wäre'Essbase Data'!$A$1:$D$6
Indizierung
Zunächst möchten Sie die von Ihnen angegebene Spalte identifizieren, in Ihrem Beispiel anhand des Datumsbereichs. Spalte:
=MATCH(B1,INDIRECT(B3),0)
Eine Beispielausgabe wäre 4. Dabei B1ist die angegebene Überschrift und B3der angegebene Datumsbereich aus der Reportobigen Tabelle.
Zuletzt möchten Sie Ihre Formel für die Zeilenindizierung erstellen, die den jeweiligen Wert aus der oben angegebenen Spalte zurückgibt.
=INDEX(
INDIRECT($B$5), <-- Data range
MATCH($A9, <-- Field lookup value
INDIRECT($B$4), <-- Field range
0
),
$B$6 <-- Column number
)
Ziehen Sie nach unten und Ihre Ausgabe sollte folgendermaßen aussehen:
| Field Name | Value |
| Key 1 | C |
| Key 2 | F |
| Key 3 | I |
| Key 4 | L |
| Key 5 | O |
| ... | ... |