Microsoft 365 for MacまたはExcel 2024 for Macで、CHOOSECOLS、TOCOL、HSTACK、FILTERを組み合わせて、繰り返されるアートアセットのラベルと数量を在庫の行に変換します。以下の数式例では、ペアの対応関係、数量のゼロ、不完全な項目が保持されます。

この手順は、属性列の後にその数量列が続く構成を繰り返すワークシートを使う、Macのデスクトップユーザー向けです。ここでは、各属性はCircle Tileなどのアートアセットのラベルです。ラベルと数量を切り離さずに、2列の在庫表を作成し、数量を検索することが目的です。

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

Microsoftは、3つの整形関数すべてについて、Excel for Microsoft 365 for MacとExcel 2024 for Macを明示的に挙げています。CHOOSECOLS、TOCOL、HSTACK。メインの数式には、これらのエディションのいずれかを使用してください。

Excel 2021 for Macには動的配列、FILTER、LET、XLOOKUPが含まれますが、これだけでは新しい整形関数への対応は確認できません。MicrosoftのExcel 2021 for Macの機能に関するドキュメントでは、利用できる配列機能と検索機能が説明されていますが、上記の3つの関数ページにはExcel 2021の記載がありません。

アプリケーションの「バージョン情報」ダイアログで、エディションとバージョンを確認してください。MicrosoftのOfficeのバージョンガイドによると、Macのダイアログにはバージョンとライセンスの種類が表示されます。お使いのエディションが整形関数に対応していない場合、自動処理にはMicrosoft 365 for MacまたはExcel 2024 for Macを使用するか、以下の手動で積み重ねる方法を使ってください。

値がすべてそろった例を用意する

空のワークシートのA1:F4に次のデータを入力します。3行目の中央にある2つの空セルは、実際に空のままにしてください。ラベルと数量はすべて説明用の例です。

属性 1 数量 1 属性 2 数量 2 属性 3 数量 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

隣接する各ペアが1つの在庫項目を表します。出力は、元データの各行を横方向に読み進めてから、下の行へ移る順序になります。Circle Tileは別々の項目として2回登場します。同じラベルをまとめる処理は、別途必要になります。

この方法では、各ラベルの直後に数量があり、選択したすべての列がこの繰り返しパターンに属していることを前提とします。シートにコレクション名など、各項目に付随させる別のフィールドがある場合、この2列の出力には保持されません。数式を使う前に、在庫表に必要なフィールドを決めてください。

繰り返されるペアを在庫の行に変換する

H1にAsset、I1にQuantityと入力します。見出しの下にある出力範囲は空けておいてください。この完全な数式をH2に貼り付けて、Returnキーを押します。Microsoftの配列数式ガイドで説明されているように、動的配列数式は一度入力すると隣接するセルに展開されます。

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

期待される結果はH2:I9に表示されます。

Asset Quantity
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

例のペアの順序、出力される8項目、ゼロの保持、数量の合計28は、小規模な決定的Python計算で確認しました。Excelの数式は、Microsoftが公開している関数の構文と照合しましたが、Excelで実行したものではありません。

数式でペアの対応関係を保つ仕組み

LETは中間結果に名前を付け、数式内で再利用できるようにします。source、clean、labels、quantities、keepという名前は、この数式内でのみ使われます。名前付き範囲を作成する必要はありません。Microsoftはこの動作をLETのリファレンスで説明しています。

CHOOSECOLSは、ラベル列1、3、5と数量列2、4、6を選択します。これらの数値は、選択した元データ範囲内での位置です。MicrosoftのCHOOSECOLSのドキュメントによると、この関数は配列から指定した列を返します。

2つのTOCOL呼び出しは、どちらも0を使ってすべての位置を保持し、FALSEを使って行方向に走査します。同じ順序で対応する位置をたどるため、各ラベルと数量の対応関係が保たれます。Microsoftは、TOCOLのリファレンスで両方の引数を説明しています。

HSTACKは、結果の2列を横に並べます。フィルター処理の前は両方とも9個の位置を含むため、長さは一致します。Microsoftの説明によると、HSTACKは配列を横方向に追加し、行数が異なる場合は短い配列をエラーで埋めます。

最後に、keepはラベル、数量、またはその両方が含まれる位置を選択します。FILTERは、完全に空のペアだけを取り除きます。最後の引数は、項目が残っていない場合にメッセージを表示します。これはMicrosoftのFILTERの構文に従っています。

空白とゼロを保持し、不完全な項目を見落とさない

空白の数量と数値のゼロは、異なる情報を表します。ゼロは入力済みの数量ですが、空白には解釈が必要です。MicrosoftのTOCOLの例では、空白セルをそのまま扱うとゼロが返されることが示されています。そのため、cleanの手順ではIFを使い、整形処理の前に空の元データセルを空の文字列として返します。Microsoftは、IFのリファレンスで条件に応じた戻り値を説明しています。

この数式では、不完全なペアも意図的に表示されます。結果を使う前に、次の点を確認してください。

元データのペア 出力での扱い 対応
ラベルと数値の数量 両方を保持 数量が意図した値か確認する
ラベルとゼロ ゼロを保持 ゼロが意図した数量なら、そのまま残す
両方のセルが空 ペアを削除 修正不要
ラベルはあるが数量が空 ラベルと空の数量を保持 数量を入力する
ラベルは空だが数量がある 空のラベルと数量を保持 ラベルを入力する

ラベル配列と数量配列から、それぞれ独立して空白を削除しないでください。ある位置でラベルが欠け、別の位置で数量が欠けている場合、空白を別々に削除すると配列の位置がずれて、誤った組み合わせが生じることがあります。結合した後まで位置を保持することが、この数式の重要な設計上の選択です。

空に見えてもスペースが入力されたセルは、この数式では空として扱われません。そのような値は元データで修正してください。同様に、この数式はテキストとして入力された数量を自動変換せず、そのまま保持します。合計を計算する前に、そうした値を確認してください。

1件だけ検索するか、一致するすべての項目を返す

K2にCircle Tileと入力します。最初の数量を取得するには、L2に次の数式を入力します。

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2#は、H2から始まるスピル結果全体を参照するため、サイズの変更にも追従します。Microsoftはスピル範囲演算子のドキュメントで、この表記を説明しています。

結果は4です。明示的な一致モード0は完全一致を指定し、既定の検索では最初に一致した項目が返されます。MicrosoftのXLOOKUPのリファレンスでは、両方の動作が説明されています。また、XLOOKUPがExcel 2016およびExcel 2019では使用できないことも確認できます。

同じラベルが複数ある場合、最初の一致だけを検索する方法では不十分です。Circle Tileの項目をすべて返すには、完全な代替数式をN2に入力し、列N:Oに結果を表示する空きを確保します。

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

これにより、Circle Tileと4、およびCircle Tileと7の2行が返されます。MicrosoftのFILTERのリファレンスに記載されているように、FILTERは指定した条件を満たす行を返します。1件の一致で目的を満たせる場合は最初の一致を検索する数式を使い、すべての該当項目を確認する必要がある場合はフィルター結果を使ってください。

結果を確認し、元データの範囲を調整する

例では、出力が8行あることと、Dot Patternの数量がゼロのまま保持されていることを確認します。次に、元データの数量合計と整形後の数量合計を比較します。空いている2つのセルに次の数式を入力します。

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

どちらも28を返すはずです。MicrosoftのSUMのドキュメントでは、複数の範囲を加算できることと、テキスト値が無視されることが説明されています。そのため、合計が一致しても、すべての数量が数値であるとは限りません。また、数量が別のラベルに入れ替わっていても検出できません。ラベルと数量のペアも確認してください。

元データの行数が増えたら、メインの数式にあるA2:F4を、実際の範囲（例：A2:F100）に変更します。その範囲外の行は含まれません。繰り返されるペアが4組ある場合は、元データの範囲をA2:H100のように8列にし、ラベルの位置を1,3,5,7、数量の位置を2,4,6,8に変更します。

元データを8列に拡張するときは、出力をJ2のような空いている場所に移し、後続のスピル参照をJ2#に更新します。元データと出力は別の場所に置いてください。無効な列位置を指定するとエラーが発生します。詳細はCHOOSECOLSのエラーで説明されています。

出力できない場合の対処と手動で積み重ねる方法

メインの数式が#SPILL!を返す場合は、数式セルを選択し、出力される範囲を確認します。その範囲にある既存のデータを移動し、結合セルがないか確認してください。数式はExcelテーブルの外に配置します。Microsoftの#SPILL!のトラブルシューティングガイドによると、スピル配列数式はテーブル内や結合セル内には展開できません。

元データにエラーがある場合は、在庫表を使う前にその値を修正してください。この数式はエラーを抑制しません。Microsoftによると、FILTERの包含配列にエラーがあると、この関数もエラーを返します。変換処理全体をエラー抑制で囲むと、修正が必要な項目が隠れるおそれがあります。

Excel 2021 for Macで小規模なワークシートを扱う場合は、手作業で同じ2列の構造を作成できます。A2:B4をH2:I4に、C2:D4をH5:I7に、E2:F4をH8:I10にコピーします。両方のセルが空の行だけを削除します。この方法では、メインの数式の元データ行順ではなく、ペアのブロックごとに項目を積み重ねます。また、元データを変更するたびに作業を繰り返す必要があります。

手動で設定した後は、Excel 2021 for Macの、固定範囲に対するXLOOKUP機能を使えます。

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

完成した在庫表では、意味のあるペアをすべて保持し、不完全な項目を見えるようにし、繰り返されるラベルに適した検索方法を使います。まず例のデータでそれらの点を確認し、次にワークシートに合わせて元データの範囲を広げてください。

参考資料