FILTERでアートアセットのカテゴリを完全一致で抽出し、次にXLOOKUPで、入力した数量を超えない最大の数量しきい値に対応するティアを返します。この手順では、架空のラベルと境界値テストを使って、Excel for Macでの例を一通り作成します。

この数式に対応しているExcel for Macのバージョン

以下の数式では、LET、FILTER、XLOOKUPを使用します。Microsoftのドキュメントによると、この3つはいずれもExcel for Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Macで使用できます。Microsoftは、Excel 2021 for Macの新機能の概要でもこれらの関数を紹介しています。

Excel 2016とExcel 2019ではXLOOKUPを評価できません。Microsoftもこの制限を明記しています。この数式を使うには、上記のいずれかのMac版をご利用ください。これらの関数に対応していない古いエディションで関数を含むブックを開いても、関数が使えるようにはなりません。MicrosoftのXLOOKUPのドキュメントをご覧ください。

インストールされているエディションがわからない場合は、アプリケーションの「バージョン情報」ダイアログで、バージョンとライセンスの種類を確認してください。Microsoftによる確認方法は、Officeについて：使用中のOfficeのバージョンを確認するで説明されています。

カテゴリごとの最小数量を設定する

再利用可能なグラフィックアセットの数を管理し、カテゴリごとに説明的な在庫ティアを割り当てるとします。この処理には2つのルールがあります。カテゴリが一致していること、そして数量がティアの最小値以上であることです。以下のラベルと数量はすべて例示用です。

空のワークシートで、次の表をA1:C7に入力します。1行目は見出し、2～7行目はデータです。最小数量は数値として入力し、カテゴリとティアの各セルをすべて埋めてください。

カテゴリ 最小数量 ティアラベル
テクスチャタイル 5 コンパクト
テクスチャタイル 20 拡張
テクスチャタイル 50 広範
シェイプスタンプ 5 コンパクト
シェイプスタンプ 30 拡張
シェイプスタンプ 80 広範

次に、E1に確認するカテゴリ、F1に数量、G1に在庫ティアと入力します。E2にテクスチャタイル、F2に数値34を入力します。参照データと入力値を分けるため、D列は空けておきます。

最小数量は境界値を含む下限として扱います。テクスチャタイルでは、コンパクトは5から、拡張は20から、広範は50からです。したがって、数量が34なら拡張に該当します。シェイプスタンプでは同じラベルでも数量が異なるため、数量が20ならコンパクトに該当します。

数式を書く前に、ご自身の境界値が最小値と最大値のどちらを表すのか決めてください。この例では、すべて最小値として扱います。また、5未満の数量はいずれのティアにも含めず、最後のティアはその開始数量を超えても継続する設定です。これらはこの例で明示的に選んだルールです。意図するルールが異なる場合は、参照データを調整してください。

カテゴリと数量に対応する数式を入力する

G2を選択し、次の数式全体を数式バーに貼り付けてReturnキーを押します。上記のシートレイアウトにそのまま対応しており、名前付き範囲や補助列は必要ありません。

=LET(
  category,$E2,
  quantity,$F2,
  categories,$A$2:$A$7,
  minimums,$B$2:$B$7,
  labels,$C$2:$C$7,
  keep,categories=category,
  IF(
    OR(category="",NOT(ISNUMBER(quantity))),
    "Enter category and numeric quantity",
    IF(
      quantity<0,
      "Use zero or more",
      IF(
        SUM(--keep)=0,
        "Category not found",
        XLOOKUP(
          quantity,
          FILTER(minimums,keep),
          FILTER(labels,keep),
          "Below first tier",
          -1
        )
      )
    )
  )
)

カテゴリがテクスチャタイルで、数量が34の場合、期待される結果は拡張です。途中の計算で配列を使いますが、数式は1つのラベルを返します。最新の動的配列数式は通常の方法で入力できます。Microsoftは、動的配列数式とスピル配列の動作でEnterキーによる確定方法を説明しています。

カテゴリのフィルターで配列の対応関係を保つ

式categories=categoryは、keepという名前の包含配列を作成します。このサンプル入力では、テクスチャタイルの3行が選択されます。2つのFILTER呼び出しはいずれも同じ配列を使うため、最小値の5、20、50と、それぞれに対応するコンパクト、拡張、広範のラベルが組み合わされます。

Microsoftの説明によると、FILTERは、元の範囲と同じサイズのブール値の包含配列を使ってデータを選択します。両方の列で同じ包含配列を使うことで、列どうしの対応関係が保たれます。FILTER関数をご覧ください。

外側のLETでは、参照範囲、入力値、包含配列に読みやすい名前を割り当てています。これらの名前はこの数式内だけで有効であり、ブック内に別途作成する必要はありません。MicrosoftのLET関数のドキュメントで、この動作が説明されています。

数量からティアを選択する

XLOOKUPの5番目の引数-1は、数量の完全一致、またはそれより小さい次の値を検索するよう指定します。フィルター後のテクスチャタイルの行では、34を指定すると最小値20が選択され、拡張が返されます。数量がちょうど50なら、広範が選択されます。これらのモードについては、XLOOKUP関数のドキュメントでMicrosoftが定義しています。

この数式は、不明なカテゴリと最初のティア未満の数量も区別します。SUM(--keep)は、一致するカテゴリの行数を数えます。一致する行がない場合は、フィルター後の検索を試みる前にカテゴリが見つかりませんを返します。カテゴリは存在するものの、最小値がすべて数量を超えている場合、検索結果は最初のティア未満になります。

境界値とカテゴリごとの分離をテストする

E2とF2を次のペアに置き換え、1組ずつ確認します。中央の値だけを試すよりも、最初のしきい値、境界値ちょうど、カテゴリ間の違い、見つからないカテゴリを検証できるため有効です。

E2のカテゴリ F2の数量 期待される結果
テクスチャタイル 0 最初のティア未満
テクスチャタイル 5 コンパクト
テクスチャタイル 19 コンパクト
テクスチャタイル 20 拡張
テクスチャタイル 34 拡張
テクスチャタイル 50 広範
シェイプスタンプ 20 コンパクト
シェイプスタンプ 34 拡張
シェイプスタンプ 100 広範
カラースウォッチ 34 カテゴリが見つかりません

数量20の2つのテストは特に重要です。テクスチャタイルでは拡張、シェイプスタンプではコンパクトが返る必要があります。どちらも拡張になる場合は、数量検索にそのカテゴリで絞り込んだ行が使われているか確認してください。

この10件の期待結果は、記載した最小しきい値のルールを実装した簡単なPython計算で確認しました。Excel数式はMicrosoftが公開している構文と照合しましたが、Excel上では実行していません。

入力メッセージも確認してください。カテゴリが空欄の場合や、数量が文字列として入力されている場合はカテゴリと数値の数量を入力してください、数値の数量が負の場合は0以上を指定してくださいが返る必要があります。アセット数には整数を使ってください。この数式は数量が数値で0以上であることを確認しますが、整数であることまでは検証しません。

予期しない結果を調べ、ワークシートを拡張する

書式の問題をティアのルール変更につなげないよう、次の順に確認してください。

  1. カテゴリの文字列を確認する。 入力値と参照ラベルを、スペースも含めて比較します。Microsoftは、検索のトラブルシューティングガイダンスで、予期しない文字や見えないスペースが検索時によくある問題だと説明しています。
  2. セルが数値か確認する。 数量の入力値と最小値には、数値に見える文字列ではなく数値を入力してください。同じMicrosoftのガイダンスでは、データ型の不一致も検索時の問題として挙げられています。
  3. 範囲の行をそろえる。 カテゴリ、最小値、ラベルの各範囲は、同じデータ行を対象にする必要があります。この例では、すべて2行目から7行目までです。
  4. ルールの重複を確認する。 カテゴリと最小数量の組み合わせごとに、ティアラベルを1つだけ設定します。同じ境界値に異なるラベルを割り当てる行が2つある場合は、参照データで曖昧さを解消してください。
  5. 境界値の扱いを確認する。 期待より1つ上または下のティアが返る場合、参照値が最大値であり、この数式が想定する最小値ではない可能性があります。

結果を目で確認するには、未使用のセル（たとえばI2）に次の診断用数式を入力します。E2にテクスチャタイルが入っている場合、テクスチャタイルの全3行が表示されるはずです。これは独立した診断用数式であり、G2の代わりに使うものではありません。

=FILTER($A$2:$C$7,$A$2:$A$7=$E2,"Category not found")

診断結果が3列にわたり、3行下まで展開できるよう、空き領域を確保してください。単独の配列結果は隣接セルにスピルします。セルが埋まっていると#SPILL!が発生することがあります。Microsoftは、動的配列数式とスピル配列の動作でこの動作を説明しています。

参照データに行を追加したら、メインの数式にある3つの参照範囲をすべて一緒に延長します。たとえば、最終行が10行目になった場合は、定義をcategories,$A$2:$A$10、minimums,$B$2:$B$10、labels,$C$2:$C$10に変更します。その後、追加したカテゴリについて境界値テストを繰り返してください。

複数の入力行を処理するには、E2:F2の下にカテゴリと数量のペアを追加し、G2を下方向にコピーします。参照にドル記号が含まれるため、参照範囲は固定され、入力行の番号は順に変わります。カテゴリごとの境界値を明確に設定し、カテゴリを組み合わせたテストで、返されるラベルが意図した在庫ルールに従うことを確認してください。

参照元