INDEX と、完全一致を指定した 2 つの MATCH 関数を使って、アセットの行と形式の列が交差する位置の数量を返します。このガイドでは、小さなアートアセット用ワークシートを作成し、数式を解説するとともに、対応する Mac 版で使えるネストした XLOOKUP の代替方法を紹介します。
Mac 版 Excel で使える検索方法は？
この作業では、Dot Grid のようなアセット名と、PNG のような形式を指定して、その交差位置にある数量を 1 つ取得します。これは一般に、双方向検索と呼ばれます。
Microsoft によると、INDEX と MATCH は、Excel for Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Mac で利用できます。この記事で紹介する両方の方法は、この Mac 版の対応範囲で使用できます。Microsoft の INDEX 関数のドキュメントと MATCH 関数のドキュメントを参照してください。
XLOOKUP も、これらの Mac 版で使用できると記載されています。Microsoft は、ページのより広い適用範囲に Excel 2016 と Excel 2019 が含まれているにもかかわらず、この関数はそれらのエディションでは利用できないと明記しています。お使いのエディションで XLOOKUP が使えない場合は、INDEX と MATCH を使う方法を選んでください。Microsoft: XLOOKUP 関数
学習中に行と列の位置をそれぞれ確認したい場合は、まず INDEX と MATCH を使ってください。すでにその関数を使っていて、既定の完全一致を利用したい場合は、ネストした XLOOKUP を選びましょう。
小さなアートアセット数量表を作成する
空のワークシートの A1:D5 に、以下のサンプルデータを入力します。下の表の左端の列は、ワークシートの列 A に相当します。行番号用の列を追加しないでください。数量は説明用の例です。
| アセット | SVG | PNG | JPG |
|---|---|---|---|
| Tile Arc | 12 | 18 | 6 |
| Dot Grid | 8 | 24 | 10 |
| Loop Ribbon | 15 | 9 | 0 |
| Soft Square | 7 | 14 | 5 |
各アセットは 1 行、各形式は 1 列を占めます。この例では、アセット名と形式の見出しがそれぞれ重複しないようにします。数量がゼロだと分かっている場合は、明示的に数値の 0 を入力します。未確認の数量は、表を使う前に確認できるよう空欄にしておきます。
同じワークシート上に、検索用の領域を別に作成します。
| セル | 入力する内容 |
|---|---|
F1 |
検索するアセット |
F2 |
Dot Grid |
G1 |
検索する形式 |
G2 |
PNG |
H1 |
数量 |
3 つの参照範囲にはそれぞれ異なる役割があります。A2:A5 にはアセット名、B1:D1 には形式の見出し、B2:D5 には数量だけが入っています。数量の範囲に見出しを含めないことで、相対位置が正しく対応します。
INDEX と 2 つの MATCH 関数で数量を返す
H2 を選択し、次の数式をすべて入力して Return キーを押します。
=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))
結果は 24 になるはずです。これは PNG 列にある Dot Grid の数量です。Microsoft の Mac 向け手順では、結果を表示するセルを選び、= で始まる数式を入力して Return キーを押すよう説明しています。Microsoft: 数式を入力する
2 つの位置を確認する
最初の MATCH は A2:A5 内でのアセットの位置を調べ、2 つ目は B1:D1 内での形式の位置を調べます。最後の 0 は完全一致を指定します。この引数を省略すると、MATCH は並べ替えを必要とする近似一致を既定で使用します。Microsoft: MATCH 関数
入力した値の場合、内側にある 2 つの計算結果は次のとおりです。
=MATCH(F2,$A$2:$A$5,0)
結果: 2。
=MATCH(G2,$B$1:$D$1,0)
結果: 2。
したがって、数式全体は数量範囲内の行 2、列 2 を選択します。
=INDEX($B$2:$D$5,2,2)
これはワークシートのセル C3 にあたり、そこには 24 が入っています。INDEX は指定された範囲内で位置を数えるため、ここでは 2 行目がワークシートの 3 行にあたります。Microsoft の説明では、両方の位置を指定すると、それらが交差する値が返されます。Microsoft: INDEX 関数
数式をコピーしても参照範囲がずれないようにする
ドル記号によって参照範囲を固定します。入力値を参照する F2 と G2 は相対参照のままなので、H2 を下の H3 にコピーすると、次の数式は同じデータ表を使いながら F3 と G3 を読み取ります。Mac では、数式バーの参照を選択して Command+T を押すと、参照の種類を切り替えることもできます。Microsoft: 相対参照と絶対参照を切り替える
たとえば、F3 に Tile Arc を、G3 の JPG に入力し、H2 を H3 にコピーします。新しい結果は 6 になるはずです。元のアセット表を変えずに、小さなリクエスト一覧を作成できます。
対応する Mac 版でネストした XLOOKUP を使う
同じワークシートと検索値を使い、代わりに H2 に次の数式を入力できます。
=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))
Dot Grid と PNG に対して、24 が返されます。Microsoft のドキュメントには、縦方向と横方向の両方を検索して交差する値を返す、ネストした XLOOKUP の例が掲載されています。完全一致が既定です。Microsoft: XLOOKUP 関数
この例では、内側の検索で PNG の数量列が選択されます。値は 18、24、9、14 です。外側の検索では、Dot Grid に対応する値が選択されます。参照する 3 つの範囲を同じにすれば、この方法と先ほどの数式を簡単に比較できます。
大きな表で使う前に結果を確認する
次のケースを簡単な受け入れ確認として使います。各行について F2 と G2 を変更し、H2 を期待される結果と比較します。元の例をそのまま使いたい場合は、確認後に Dot Grid と PNG に戻してください。
| アセットの入力値 | 形式の入力値 | 期待される結果 | 確認できること |
|---|---|---|---|
| Dot Grid | PNG | 24 | 中央の交差位置 |
| Tile Arc | JPG | 6 | 最初のアセットと最後の形式 |
| Soft Square | SVG | 7 | 最後のアセットと最初の形式 |
| Loop Ribbon | JPG | 0 | 明示されたゼロの数量 |
| Tile Arcs | PNG | #N/A |
存在しないアセット名 |
| Dot Grid | TIFF | #N/A |
存在しない形式の見出し |
数値が違う場合は、検索方法を変える前に範囲の境界を確認します。この表では、A2:A5 は B2:D5 の 4 行に対応し、B1:D1 はその 3 列に対応している必要があります。たとえば、アセットの検索範囲にだけ A1 を含めると、計算された位置が意図した数量行からずれてしまいます。
この表にアセットを追加して 6 行に配置する場合は、アセットの範囲を A2:A6 に、数量範囲を B2:D6 に更新します。E 列に形式を追加する場合は、見出しの範囲を B1:E1 に、数量範囲を B2:E5 に更新します。このように両方を更新すれば、数式で使う配置を維持できます。
重複する名前にも注意が必要です。完全一致の MATCH は最初に一致した位置を返し、大文字と小文字を区別しません。また、テキスト検索では * と ? をワイルドカードとして解釈します。この設定では、これらの文字を含まない重複しない名前を使ってください。Microsoft: MATCH 関数
ラベルが見つからないときに分かりやすいメッセージを表示する
基本の検索が動作したら、見つからない場合のエラーを読みやすいメッセージに置き換えられます。
=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")
囲んだ数式が #N/A を返すとき、IFNA は指定したメッセージを返します。それ以外の場合は、数式の結果をそのまま返します。この関数は Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Mac で使えると記載されています。Microsoft: IFNA 関数
この例では、Tile Arcs に対して Check asset or format が表示されます。一方、JPG を指定した Loop Ribbon では、引き続き 0 が返されます。こうすることで、ラベルが見つからない状態と、記録された数量がゼロの状態を区別できます。ラベルが正しいのにエラーが出る場合は、元のセル自体に #N/A が含まれていないか確認してください。この関数はそのエラーも置き換えるためです。
日常的に使う際は、アセット名を重複させず、3 つの参照範囲の対応関係を保ち、表を変更した後に既知の交差位置をいくつか確認します。そうすれば、アセットと形式の入力値を変えるだけで、必要なアートアセットの数量をそれぞれ取得できます。
出典
- INDEX 関数 — Microsoft サポート。
- MATCH 関数 — Microsoft サポート。
- XLOOKUP 関数 — Microsoft サポート。
- 数式を入力する — Microsoft サポート。
- 相対参照と絶対参照を切り替える — Microsoft サポート。
- IFNA 関数 — Microsoft サポート。