Excelの在庫検索で最も具体的な一致ルールを選択するには、まず対象となる行を特定し、次に指定されているフィールド数を数えます。最高スコアの行が1つだけなら、その行の数量を返し、同点の場合はその旨を表示します。この手順では、Excel for Macで架空のアート資産を使って結果を作成します。

この方法はどのExcel for Macのバージョンで使えますか？

以下の数式では、LET、XLOOKUP、動的配列計算を使用します。Microsoftの資料では、これらの機能はExcel for Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Macで利用できるとされています。Excel 2021 for Macの機能ガイドには、LET、XLOOKUP、動的配列が明記されています。Excel 2021 for Macの新機能、LET関数。

Microsoftの明示的な互換性に関する注記によると、Excel 2016とExcel 2019はXLOOKUPをサポートしていません。この数式をそのまま使うには、対応しているMac版を使用してください。数式の入力方法を変えても、対応していない関数は使えるようになりません。この記事後半の任意の確認用数式では、FILTERも使用します。上記の3つの対応Mac版で使えることが資料に記載されています。XLOOKUP関数、FILTER関数。

在庫で「最も具体的」とはどういう意味にしますか？

この例では、アセット名、仕上げ、サイズという3つの属性を使い、アート資産に割り当てられた数量を検索します。ルールのフィールドが空欄の場合、その属性の値を問わないルールとします。フィールドに値が入っている場合は、対応する入力値と一致する必要があります。対象となるルールの中で、入力済みフィールドが最も多いルールを採用します。

これは、この例のために定めた明示的なブックのルールです。Excelが説明文から自動的にそのルールを推測するわけではありません。ここでは、すべての属性を同じ重みで扱います。アセットと仕上げを指定したルールは、アセットとサイズを指定したルールと同じ具体性になります。両方が一致して最高スコアを共有する場合、数式は同点と表示します。

単純なXLOOKUPだけでは、この優先順位を決められません。Microsoftの資料によると、既定では最初に一致した項目を返します。検索方向を反転しても、検索を開始する側が変わるだけで、ルールの具体性に基づく順位付けは行われません。そのため、この数式ではXLOOKUPで数量を取得する前に、採用する行を計算します。XLOOKUP関数。

架空のアート資産ルールを設定する

空のワークシートで、A1:D6に次の見出しと値を入力します。以下の表で空欄になっているセルは、Excelでも空欄のままにしてください。ラベルと数量はすべて例示用です。

ワークシートの行 A：アセット B：仕上げ C：サイズ D：数量
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

2行目のルールは、意図的に既定値として設定しています。属性を指定していないため、具体性スコアは0です。3行目は属性を1つ、4行目は2つ、5行目は3つ指定しています。これらの数量はそれぞれ別の割り当て数量なので、検索結果として返る値は1つです。既定値と例外の数量を合算することはありません。

H1:K1に見出しAsset、Finish、Size、Resultを入力します。次に、H2にTile、I2にMatte、J2にSmallを入力します。3つの入力属性すべてに値を入れてください。この設計では、空欄はルール表の条件を制限しないことを表します。不完全な入力にはメッセージが表示されます。

数量は必要に応じて数値のゼロを含め、数値として入力します。範囲は実際のルール行だけに限定してください。未使用の空白行まで範囲を広げると、この照合ルールでは、すべてのフィールドが空欄の追加ルールとして扱われます。

採用するルールを選ぶ数式を入力する

K2を選択し、次の数式全体を入力してReturnキーを押します。この数式では、上記で定義したルール表と入力セルを使います。動的配列計算は数式内で評価され、最終結果は1つのセルに表示されます。Microsoftの資料によると、動的配列数式は従来の配列数式用キー操作を使わず、通常どおり入力します。動的配列数式とスピル配列の動作。

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

サンプルの入力値では、K2に7が返されます。4つのルールが対象になりますが、3つすべての属性を指定しているのは5行目のルールだけです。

対象条件と具体性の仕組み

LETは範囲と中間結果に数式内だけで使う名前を付け、選択処理を確認しやすくします。Microsoftの資料では、これらの名前は数式のスコープ内で有効とされています。LET関数。

各属性について、対象判定の式はルールのセルが空欄か、入力値と一致するかを確認します。加算でこの2つの可能性を組み合わせ、比較によって論理値に変換します。3つの属性テストを乗算することで、すべての条件が成立することを要求します。MicrosoftのFILTERの例でも、配列条件をAND論理で組み合わせるのに同じ乗算パターンを使っています。FILTER関数。

specificityは、ルールのうち入力済みフィールドの数を数えます。二重単項演算子は、空欄でないかどうかの判定を数値に変換するため、スコアは0から3になります。IFは、順位計算で対象外の行にマイナス1のスコアを割り当てます。その後、MAXが対象行の最高スコアを求めます。マイナス1にすることで、対象外の行は、制限のない既定値のスコア0よりも低くなります。IF関数、MAX関数。

winnerは、最高スコアの対象行だけを示します。数式は数量を返す前に、該当なしと採用候補が複数ある場合を確認します。最後の数値チェックでは、数量が空欄の場合や数値が文字列として保存されている場合を意図的に除外します。Microsoftの資料によると、ISNUMBERは文字列の数値を数値に変換しません。数値のゼロは有効な結果として扱われます。IS関数。

ワークシートを広げる前に結果を確認する

H2:J2の値を、以下の各組み合わせに変更します。これらはサンプルのルールから導いた期待結果であり、在庫の実測値ではありません。

アセット 仕上げ サイズ 期待される結果 採用条件
Tile Matte Small 7 3フィールドを指定
Tile Matte Large 12 アセットと仕上げ
Tile Gloss Small 18 アセットのみ
Disc Gloss Large 10 アセットとサイズ
Arc Matte Small 30 制限のない既定値

次に、6行目を一時的にアセットTile、仕上げMatte、サイズSmall、数量9に変更します。元の入力値に戻すと、結果はTie: review rulesになるはずです。3つの属性すべてが一致するルールが2つになったためです。数量が同じでも、どのルールを選ぶかが曖昧なので同点になります。その後、6行目を元に戻します。

該当なしの分岐をテストするには、数式内の4つのルール範囲を一時的に3行目から開始するよう変更し、既定値を除外します。入力がArc、Matte、Smallなら、No matching ruleが返されるはずです。テストが終わったら、範囲を2行目から始まるように戻します。数量チェックをテストするには、元の入力値を使ったままD5をクリアします。Check rule quantityが返されたら、7を元に戻します。

重複するルールを確認して同点を解消する

現在の入力値に該当するすべてのルールを表示するには、別の数式をM2に入力します。事前にM2:P6を空欄にしてください。出力には一致するルールが元の順序で表示され、順位付けはされません。

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTERは、ブール値の包含配列で選択された行を返します。最後の引数には、条件に該当する行がない場合に表示するメッセージを指定します。この確認用の出力では、元データの空欄セルがゼロとして表示されることがあります。元のルール表では、空欄セルを引き続き制限のない条件として扱ってください。FILTER関数。

確認用の数式は、隣接するセルにスピルします。Excelのテーブルの外に置き、出力範囲を空けてください。Microsoftの資料によると、スピル配列数式はテーブル内ではサポートされません。また、出力先に値が入っていると#SPILL!エラーが発生することがあります。動的配列数式とスピル配列の動作。

同点を解消するには、重複を修正する、重複範囲があるルールの条件を絞り込む、または別の優先順位ルールを定めます。たとえば、ここでは「アセットと仕上げ」を指定するルールと「アセットとサイズ」を指定するルールは、どちらもスコアが2です。どの属性を重視するかは、その優先順位を数式に組み込まない限り、ブック側では決められません。この記事のルールでは、行の順序を変えても結果は変わりません。

意味を変えずに検索範囲を広げる

別のアセットを検索するには、その3つの属性をH3:J3に入力し、K2をK3にコピーします。入力セルへの参照は数式とともに移動し、ドル記号によってルール範囲は固定されます。6行目より後にルールを追加する場合は、メインの数式の4つの範囲と、確認用数式の2つの範囲をすべて同じように拡張します。

属性ごとに列を分け、余分なスペースが入らないようラベルを統一します。この数式はフィールド全体を比較します。アセットの説明を結合した文字列の中から一部分を検索するものではありません。数量チェックで選択した値が数値であることは確認できます。数量を0以上の整数にするなど、その他の在庫要件がある場合は、元データに適用してください。

この検索は、明確な手順で判定します。入力がすべて揃っていることを確認し、対象ルールを特定し、最も具体的なものを選び、同点を除外してから、選択した数値の数量を返します。この手順により、全般的な既定値と詳細な例外を併用しながら、未解決の選択を明確にできます。

参考資料