背景
名前付き範囲を参照してその値を表示したいという問題があります。範囲は値のリストですが、リスト内の要素の数が事前にわかりません。リストには 1 から 8 個の値があるとします。
私のやり方は、8 の長い範囲で配列数式を使用し、IFNA() を使用して #N/A 出力をマスクすることです。出力に空白部分があることは問題ありませんが、#N/A を表示するとプロフェッショナルに見えません。ISNA は機能しませんが、これは配列数式が ISNA の動作を変更しているためだと思われます。下の画像の例。
ソリューションの制限
データは後の段階でピボットテーブルで使用されるため、単にデータを非表示としてマスクするだけでは不十分です。#N/A 値を取得したくないのです。ただし、空の文字列は問題ありません。空の文字列も実際には正しい答えではありませんが、少なくとも少しはうまく失敗します。
私は同僚のためにモデルを開発しており、同僚は後の段階でさらに変更を加える可能性があるので、ソリューションは他の人にとって比較的簡単に保守できるものでなければなりません。したがって、非常に長い数式や名前マネージャーの多用は避けたいと考えています。
質問
N/A を取得せずに変数サイズの範囲を単純に配列コピーする「適切な」方法は何ですか?
...「適切」とは、保守が容易で、隠しデータを含まない、シンプルでコンパクトな形式を意味します。
答え1
代わりに INDEX を使用し、これを出力の最初のセルに入力して、必要な行数をコピーします。
=IFERROR(INDEX(rng_1,ROW(1:1)),"")
ROW(1:1) は、コピーされるときに次の行にコピーされ、反復されます。行がなくなると、エラーがスローされ、""その場所に配置されます。
答え2
N/A を取得せずに、可変サイズの範囲を単純に「配列コピー」する適切な方法は何ですか?
そのような方法はないと思いますが、配列を含む列の条件付き書式設定や、配列以外の数式の使用など、他の方法が問題の解決に役立つ可能性があります。
条件付き書式：
列/範囲を条件付きで書式設定して、エラー セル テキストの書式を白またはセルの色と調和する色に変更します。
数式の例:
配列以外の数式を使用し、数式でエラーを処理します。
=IFERROR(IF(INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))="","",INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))),"")
答え3
Scott Craners の回答を参考にして、私は次のように決めました。
コマンドは出力が書き込まれるのと同じ領域を参照するため、エラーが少し少なくなるはずですROWS。この方法により、気付かないうちに数式を壊してしまうような行を挿入することがなくなります。
この解決策には満足していませんが、これで我慢するしかないと思います...