Hintergrund
Ich habe ein Problem, bei dem ich auf einen benannten Bereich verweisen und dessen Werte anzeigen möchte. Der Bereich ist eine Liste von Werten, aber ich weiß nicht im Voraus, wie viele Elemente in der Liste sind. Nehmen wir an, die Liste enthält zwischen 1 und 8 Werte.
Ich gehe das so an, indem ich eine Array-Formel für einen 8-langen Bereich verwende und IFNA() verwende, um die #N/A-Ausgabe zu maskieren. Es ist durchaus akzeptabel, dass ich in der Ausgabe etwas Leerraum habe, aber die Anzeige von #N/A sieht nicht so professionell aus. Die ISNA funktioniert nicht und ich vermute, dass dies daran liegt, dass die Array-Formel das ISNA-Verhalten ändert. Beispiel unten im Bild.
Einschränkungen der Lösung
Die Daten werden zu einem späteren Zeitpunkt in PivotTables verwendet, daher reicht es nicht aus, die Daten einfach als unsichtbar zu maskieren – ich möchte wirklich keine #N/A-Werte erhalten. Eine leere Zeichenfolge ist jedoch in Ordnung; eine leere Zeichenfolge ist auch keine wirklich richtige Antwort, schlägt aber zumindest etwas eleganter fehl.
Die Lösung muss für andere relativ einfach zu warten sein, da ich das Modell für einige Kollegen entwickle und diese möglicherweise später weitere Änderungen vornehmen möchten. Daher möchte ich sehr lange Formeln und die umfangreiche Verwendung des Namensmanagers vermeiden.
Frage
Was ist die „richtige“ Art und Weise, einen Bereich variabler Größe einfach zu „kopieren“, ohne N/A zu erhalten?
... wobei „richtig“ eine einfache, kompakte Formel bedeutet, die leicht zu pflegen ist und keine versteckten Daten enthält.
Antwort1
Verwenden Sie stattdessen INDEX. Fügen Sie dies in die erste Zelle der Ausgabe ein und kopieren Sie die gewünschte Zeilenanzahl nach unten:
=IFERROR(INDEX(rng_1,ROW(1:1)),"")
ROW(1:1) wird iteriert, während es nach unten kopiert wird und das nächste in der Zeile zieht. Wenn es aufgebraucht ist, wird ein Fehler ausgegeben und es ""wird an seine Stelle gesetzt.
Antwort2
Was ist die richtige Methode, um einen Bereich variabler Größe einfach „als Array zu kopieren“, ohne N/A zu erhalten?
Ich glaube nicht, dass es eine gibt, aber möglicherweise könnten andere Methoden bei Ihrem Problem funktionieren, beispielsweise die bedingte Formatierung der Spalte mit dem Array oder die Verwendung einer Nicht-Array-Formel.
Bedingte Formatierung:
Formatieren Sie eine Spalte/einen Bereich bedingt, um die Formatierung des Fehlerzellentextes in Weiß oder eine andere Farbe zu ändern, die sich in Ihre Zellenfarbe einfügt.
Formelbeispiel:
Verwenden Sie eine Nicht-Array-Formel und lassen Sie den Fehler von der Formel behandeln.
=IFERROR(IF(INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))="","",INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))),"")
Antwort3
Mithilfe der Antwort von Scott Craner habe ich mich für Folgendes entschieden:
Es sollte etwas weniger fehleranfällig sein, da der ROWSBefehl auf denselben Bereich verweist, in den die Ausgabe geschrieben wird. Auf diese Weise sollte man keine Zeilen einfügen, die die Formel zu stark zerstören, ohne es zu merken.
Ich bin mit dieser Lösung nicht zufrieden, aber ich schätze, sie wird reichen müssen …