ExcelでINDEX関数を使って複数の値を返すように設定しているにもかかわらず、期待する配列が正しく表示されない場合があります。また「配列を変更することはできません」というポップアップが出ることがあります。本記事では、その原因と解決方法について解説します。
1. 配列数式と動的配列の違い
従来のExcel（Office 2016以前）では、複数の値を返すINDEX関数は配列数式として入力する必要があり、入力後にCtrl+Shift+Enterで確定していました。この場合、セルに囲いが付き、動的配列として扱われます。
一方、Office 365やExcel 2021以降では動的配列機能が搭載され、INDEX関数を含む多くの関数が自動で複数セルにスピル（展開）されます。しかし、古いファイルや共有ブックではスピルが無効になる場合があり、結果として1つの値しか表示されません。
2. 「配列を変更することはできません」の原因
このポップアップは、INDEX関数や他の動的配列関数がスピルしている範囲のセルがすでに何らかのデータで埋まっている場合に表示されます。スピル範囲のどこか1セルでも書き込み済みだと、Excelは配列全体を展開できず、エラーを出します。
3. 解決策
以下の方法で問題を解決できます。
- スピル範囲を確認し、不要なデータや値を削除する
- Office 365やExcel 2021以降を使用して動的配列を利用する
- 古いExcelの場合、配列数式として
Ctrl+Shift+Enterで入力する - 共有ブックでは、動的配列や配列数式が正しく機能しないことがあるため、一時的に共有解除して操作する
4. 実例
例えば、A列に{10,15}の値があり、INDEX(A1:A2,{1,2})と入力した場合、Office 365では自動でA1とA2にスピルされます。しかし、A1かA2に既存データがあるとスピルできず、10しか表示されません。すべてのセルをクリアするか、別の列を使うことで正常に表示されます。
まとめ
INDEX関数で複数値を返す際に1つしか表示されない場合、原因はスピル範囲の衝突や古いExcelでの配列数式の扱いにあります。動的配列をサポートするExcelを使用するか、配列数式を正しく入力することで問題を回避できます。また、共有ブックでは制限があるため、操作前に共有解除することが推奨されます。
コメント