IFNAを使ってスピルする検索式を囲み、#N/Aの結果を2つ目の参照テーブルから補完します。このExcel for Macの例では、アセットラベルを照合し、主テーブルですでに見つかった数量を保持し、両方のテーブルにないラベルを明確に示します。
この方法に対応するExcel for Macのバージョン
Excel for Microsoft 365 for Mac、Excel 2024 for Mac、またはExcel 2021 for Macを使用してください。Microsoftは、これらのエディションをXLOOKUPとIFNAの両方の対象として挙げています。XLOOKUPのドキュメントでは、ページのより広範な適用対象リストに含まれているにもかかわらず、Excel 2016とExcel 2019は明示的に対象外とされています。以下の数式は、対応するMacエディションを対象としています。XLOOKUP function、IFNA function
作業内容は明確です。要求するアセットラベルを1列に並べ、各ラベルに対して数量を1つ返します。主参照を優先し、主検索で#N/Aが返された場合に限り、2つ目の参照から数量を取得します。以下のラベルと数量はすべてサンプルデータです。
2つの参照テーブルと要求アセットのリストを設定する
1つのブック内にある空白のワークシートから始めます。数式が参照先を変更せずに機能するよう、以下のセル位置を使用してください。これらの小さな参照テーブルは通常のワークシート範囲です。Excelテーブルオブジェクトに変換する必要はありません。
主参照をD1:E4に入力します。
| D列のセル | アセットラベル | E列のセル | 数量 |
|---|---|---|---|
D1 |
アセット | E1 |
数量 |
D2 |
葉の輪郭 | E2 |
8 |
D3 |
ドットテクスチャ | E3 |
0 |
D4 |
タイルパターン | E4 |
12 |
2つ目の参照をG1:H5に入力します。
| G列のセル | アセットラベル | H列のセル | 数量 |
|---|---|---|---|
G1 |
アセット | H1 |
数量 |
G2 |
ドットテクスチャ | H2 |
9 |
G3 |
曲線スケッチ | H3 |
5 |
G4 |
グレインテクスチャ | H4 |
3 |
G5 |
葉の輪郭 | H5 |
10 |
J1に「要求アセット」と入力し、次の5つのラベルをJ2:J6に1セルずつ、この順番で入力します。
- 葉の輪郭
- ドットテクスチャ
- 曲線スケッチ
- グレインテクスチャ
- アークモチーフ
数量は数値として保持し、2つの参照内の数量セルをすべて入力してください。この例では、ゼロは記録された数量がゼロであることを意味します。アセットがないことを意味するものではありません。
ラベルのスピルリストを作成する
A1、B1、C1に「アセット」、「主数量」、「補完後の数量」という見出しを入力します。A2:C20は空のままにし、A2に次の数式を入力します。
=FILTER(J2:J20,J2:J20<>"","")
この数式は、要求リストにある空白セルを除外します。上記のデータでは、5つのアセットラベルがA2:A6にスピルします。Microsoftは、上記の3つのMacエディションでFILTERが使えると説明し、条件を満たす行がない場合の結果を最後の引数で指定できるとしています。ここでは、その結果として空文字列を指定します。FILTER function
後続の数式では、このスピル全体を参照するためにA2#を使います。#演算子はスピルのサイズに追随するため、現在の最終行をA6のように固定値で指定する必要がありません。Microsoftはこの動作をスピル範囲演算子のドキュメントで説明しています。
2つ目の参照から不足している数量を補完する
まず、B2に主検索の数式を入力します。
=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)
この数式は、主アセット列を検索し、対応する数量を返します。XLOOKUPは既定で完全一致を使い、一致がなく代替結果も指定されていない場合は#N/Aを返します。XLOOKUP function
このサンプルでは、B2:B6に8、0、#N/A、#N/A、#N/Aが表示されます。次の手順で使うため、見つからなかった場合のエラーはそのままにしておきます。
次に、C2に補完用の数式を入力します。
=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))
IFNAは主検索のスピルを調べます。#N/Aの位置では、2つ目の検索で対応する結果を返します。それ以外の主検索の結果は変更せずに渡します。Microsoftは、最初の引数が配列数式の場合、IFNAが結果の配列を返すことを明記しています。IFNA function
ワークシートには、次の結果が表示されます。
| A列のアセット | B列の主数量 | C列の補完後の数量 |
|---|---|---|
| 葉の輪郭 | 8 | 8 |
| ドットテクスチャ | 0 | 0 |
| 曲線スケッチ | #N/A |
5 |
| グレインテクスチャ | #N/A |
3 |
| アークモチーフ | #N/A |
リストにありません |
これらの行で、優先順位が正しく機能することを確認できます。2つ目の参照に10があっても、葉の輪郭は8のままです。2つ目の参照に9があっても、ドットテクスチャは0のままです。曲線スケッチとグレインテクスチャには、2つ目の参照から数量が補完されます。アークモチーフはどちらの参照にもないため、未解決のまま表示されます。
2つの配列は、同じ要求アセットを同じ順序で表す必要があります。この設定では、どちらもA2#を元にしています。既存のブックに適用する場合、主検索の結果と、順序の異なるラベルリストに基づく代替検索を組み合わせないでください。
中間列が不要な場合は、1つの数式を使う
主検索と補完をまとめるには、C2の数式を次の完全な代替式に置き換えます。
=IFNA(
XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)
これは同じサンプルデータを使い、同じ5つの補完後の数量を返します。この計算ではB列は不要になります。ブックの設定中は2段階の数式を使うと、各結果がどの参照から取得されたかが分かりやすくなります。
結果が本当に不足しているかを判断する
数式を調整する前に、最初の検索で何が返されるか確認します。ラベルが見つからない場合、空に見える結果、数量がゼロの場合は、それぞれ異なる判断が必要です。
| 主検索の結果 | この補完数式の動作 | 確認すること |
|---|---|---|
0を含む数値の数量 |
その値を保持する | 意図した主数量であることを確認します。 |
#N/A |
2つ目の検索を使う | このエラーがラベルの欠落を表していることを確認します。 |
空文字列、"" |
その値を保持する | 主数式が見つからない場合を隠していないか確認します。 |
| その他のエラー | 表示したままにする | 元の数式または参照を修正します。 |
たとえば、既存の主検索で""をif_not_found引数として使っている場合、IFNAはその空文字列を#N/Aとして認識できません。主検索からその引数を削除し、B2のようにするか、上記の統合数式を使ってください。補完ロジックを機能させるには、見つからない場合に認識可能な値を返す必要があります。XLOOKUP function、IFNA function
参照内の数量も確認してください。IFNAが応答するのはエラー値であり、数量セルに値が入力済みかどうかを判定するわけではありません。一致した主レコードの数量が未入力の場合は、そのレコードを完成させるか、この手順を適用する前に未入力数量に対する別のルールを定めてください。
さらに、主数量データ内に既存の#N/Aエラーがある場合も、この代替処理が実行されます。この例では、数量列に数値が正しく入力されており、#N/Aが検索の失敗によってのみ発生することを前提としています。すべての置換結果をアセットの不一致によるものとして扱う前に、この条件を確認してください。
優先順位を確認し、スピルを維持する
独自のアセットラベルに置き換える前に、サンプルの5行を使って簡単な受け入れ確認を行います。主参照での一致、有効なゼロ、2つ目の参照での一致、どちらの参照にもないラベルをそれぞれ確認できます。
追加確認として、2つ目の参照にある葉の輪郭の数量を一時的に10から14に変更します。補完後の数量は8のままになるはずです。次に、2つ目の参照にある曲線スケッチの数量を5から6に変更します。補完後の数量は6になるはずです。その後、元の値に戻してください。これらはブックでの確認例であり、Excelでのテスト結果を報告するものではありません。
各参照には、アセットラベルごとに意図したレコードを1つだけ保持してください。XLOOKUPは通常、先頭の項目から検索して最初の一致を返すため、重複ラベルの数量が合算されることは期待できません。この検索方法を使う前に、重複ラベルの扱いを決めてください。XLOOKUP function
出力範囲が塞がれている場合の対処
スピルする数式はそれぞれ開始セルにのみ入力します。Excelは隣接するセルを自動入力し、スピル範囲の編集は左上のセルでのみ行えます。数式はExcelテーブルオブジェクトの外に配置してください。Microsoftはテーブルをソースデータとして使うことはサポートしていますが、テーブル内でのスピル数式はサポートしていません。動的配列数式とスピル配列の動作
#SPILL!が表示された場合は、数式セルを選択して、想定される出力範囲を確認します。利用できる場合は、エラー通知の「妨げているセルを選択」を使い、結果を妨げている既存の値を移動します。結合セルもスピルを妨げることがあります。その場合は出力先を移動するか、該当セルの結合を解除します。#SPILL!エラーを修正する方法
入力範囲を意図的に広げる
要求リストの数式はJ2:J20を調べます。この範囲内に追加したラベルは自動的に含まれますが、範囲より下に入力したラベルを含めるには、数式の範囲を広げる必要があります。同様に、参照検索では4行目と5行目で終わる固定範囲を使います。レコードを追加するときは、各アセット範囲と対応する数量範囲を一緒に拡張してください。
大きなワークシートでは、構造化参照を使うExcelテーブルを利用すると、行を追加または削除したときにソース範囲を自動調整できます。出力数式はテーブルの外にあるワークシートのセル範囲に配置してください。動的配列数式とスピル配列の動作
この例の入力は同じブック内に保持してください。Microsoftは、ブック間での動的配列のサポートには制限があると説明しています。リンクされた動的配列を使うには両方のブックを開いたままにする必要があり、ソースを閉じた後に更新すると#REF!が発生することがあります。動的配列数式とスピル配列の動作
期待した結果が得られたら、補完後の列をアセット数量として使います。主参照を優先するルールを維持し、意味のあるゼロはそのまま保持し、どちらの参照にもないラベルは「リストにありません」と表示したままにして、参照データを適切に補完してください。
出典
- XLOOKUP function — Microsoft サポート。
- IFNA function — Microsoft サポート。
- FILTER function — Microsoft サポート。
- Spilled range operator — Microsoft サポート。
- Dynamic array formulas and spilled array behavior — Microsoft サポート。
- How to correct a #SPILL! error — Microsoft サポート。