Excel for Macで可変サイズの検索結果を結合するには、REDUCE と VSTACK を使って1つの矩形配列を作成します。この手順では、アートアセットの一覧を作成し、リクエストの順序を維持し、該当するグループがない場合も黙って省略せずに表示します。

目的はシンプルです。リクエストした各アセットグループは、複数のソース行、1行、または該当なしに一致する可能性があります。アセットのラベルと数量を一緒に保ちながら、これらすべての結果を1つの拡張するワークシート範囲にまとめます。

この方法をサポートするExcel for Macのバージョン

以下のメインの数式には、Excel for Microsoft 365 for Macを使用してください。Microsoftは、REDUCE関数のドキュメントでこのエディションを明示的に挙げています。現在のページにはExcel 2024 for Macが記載されていないため、ほかの最新の配列関数が使えるというだけでは、この数式全体が利用できるとは限りません。

関数 関連するMicrosoftドキュメントに記載されたMacのエディション ここでの役割
BYROW、MAP Microsoft 365 for Mac、Excel 2024 for Mac 元の方法とその制約を説明する
REDUCE Microsoft 365 for Mac リクエストの処理中に増加する結果を保持する
VSTACK、HSTACK Microsoft 365 for Mac、Excel 2024 for Mac 結果ブロックを結合し、フォールバック行を作成する
FILTER Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Mac 一致するすべてのアセット行を返す

これらのエディション情報は、MicrosoftのB YROW、MAP、VSTACK、HSTACK、FILTERのリファレンスページに基づいています。

インストールされているバージョンを確認するには、Excelメニューを開き、About Excelを選択します。MicrosoftのMac向け手順によると、Officeアプリケーションの「About」ダイアログにはバージョンとライセンスの種類が表示されます。どの数式を使うか決める際は、両方を確認してください。Officeについて: 使用しているOfficeのバージョンを確認するを参照してください。

Macの新しいBetaプレビューで入れ子配列の制約が変わる

Microsoftが2026年9月24日に発表した内容では、Betaチャネル向けのプレビュー機能として、入れ子配列とFLATTENが紹介されています。Macではバージョン16.114ビルド26092111以降が必要で、利用可能になる時期は段階的な展開に左右されます。ほとんどの入れ子配列の計算には、Formula > Calculation Optionsから選択するブック互換性バージョン3が必要です。これらの条件は重要です。Microsoft 365を使っているだけでは、プレビューが有効になっているとは限りません。Excelでリストと配列を使って1つのセルに複数の値を入れるを参照してください。

以下の解決策では、そのプレビューに依存せず、通常の矩形結果を作成します。これにより、基本的なアセット一覧の作成が入れ子配列の設定に依存することも避けられます。

BYROWやMAPから配列を返すと問題が起きる理由

Microsoftの説明では、BYROWは入力行ごとに1つの結果を生成します。リファレンスには、内側のLAMBDAから単一の値以外が返されると#CALC!エラーになると明記されています。複数のレコードを返す検索は、この仕様上の制約を超えます。BYROW関数を参照してください。

たとえば、次のセクションのサンプルデータを入力した後、以下の数式を使うと、単一値という仕様における問題の例を確認できます。

=BYROW(E2:E4,LAMBDA(key,FILTER(A2:C7,A2:A7=key,"No match")))

最初のリクエストは3行3列を返します。BYROWにそのブロックを1つの結果位置に配置させようとすると、入れ子配列の問題が発生します。外側の関数をMAPに変えても、従来の計算動作では汎用的な積み重ね処理にはなりません。内側の結果に複数のセルが含まれたままだからです。MicrosoftのMAPのドキュメントでは入力値を新しい値に対応付ける方法が説明され、#CALC!エラーの説明では入れ子配列の制約が記載されています。

失敗した計算をVSTACKで囲んでも、内側の結果は修正できません。リクエストを処理しながら各検索ブロックを追加すれば、途中の結果もすべて矩形配列になります。

REDUCEとVSTACKで完全なアセット検索結果を作成する

1. サンプルのソースデータを入力する

空のワークシートから始めます。次のタブ区切りブロックをA1に貼り付けます。数量はアートアセットの個数を示す例です。

Group	Asset	Quantity
Mosaic	Tile Amber	2
Orbit	Ring Silver	3
Mosaic	Tile Blue	4
Prism	Panel Clear	5
Mosaic	Tile Coral	1
Prism	Panel Frost	2

次のリクエスト一覧をE1に貼り付けます。期待される結果と比較できるよう、表示された順序のままにしてください。

Requested group
Mosaic
Orbit
Pebble

このサンプルには意図的に3つの結果が含まれています。Mosaicには3件、Orbitには1件が一致し、Pebbleには一致するものがありません。Prismはリクエストされていないため、出力に含めないでください。

2. 1つの検索結果を個別に確認する

L1など、未使用の領域に次の数式を入力します。

=FILTER($A$2:$C$7,$A$2:$A$7=$E$2,"No match")

Mosaicに一致する3件が返されます。FILTERはグループの比較を使って、ソースから完全な行を選択します。条件配列の高さまたは幅はソースと一致している必要があります。ここでは、ソースと条件の両方が行2:7を対象としています。FILTER関数を参照してください。

この簡単な確認で、検索処理と積み重ねの処理を切り分けられます。行が正しくない場合は、外側の計算を追加する前に、ソースのラベルまたは参照を修正してください。

3. 積み重ねる完全な数式を入力する

列G:Iに空きを確保し、G1に次の数式を入力します。

=REDUCE(
  $A$1:$C$1,
  $E$2:$E$4,
  LAMBDA(acc,key,
    VSTACK(
      acc,
      FILTER(
        $A$2:$C$7,
        $A$2:$A$7=key,
        HSTACK(key,"No match","")
      )
    )
  )
)

Returnを押します。出力の左上セルに数式を1回だけ入力すると、Excelがスピル範囲のサイズを自動調整します。スピル配列数式はテーブル内ではサポートされていないため、Excelのテーブル外に入力してください。どちらの動作についても、Microsoftの動的配列数式とスピル配列の動作で説明されています。

4. 期待される結果と比較する

指定したデータの場合、出力はG1:I6を占めます。

Group Asset Quantity
Mosaic Tile Amber 2
Mosaic Tile Blue 4
Mosaic Tile Coral 1
Orbit Ring Silver 3
Pebble No match

期待されるサイズはサンプルから直接導けます。ヘッダー行が1行、Mosaicの行が3行、Orbitの行が1行、一致しないリクエストの行が1行です。一致した4件の数量の合計は10です。Pebbleの数量が空欄であるため、検索結果がない場合と数量が0のアセットを区別できます。

数式が結果を組み立てる仕組み

REDUCEは、A1:C1をアキュムレーターとして処理を開始します。E2:E4の各値について、keyが現在リクエストされているグループを保持し、accがそこまでに組み立てられた結果を保持します。Microsoftは、初期値、アキュムレーター、現在値を使うこのパターンをREDUCE関数で説明しています。

この数式では、FILTERが次の一致ブロックを作り、VSTACKがそれをaccの下に追加します。実際のヘッダーを初期値として使うことで、最初から結果が3列になり、使い捨ての空の開始行を追加せずに済みます。VSTACKは、VSTACK関数の説明どおり、配列を順番に縦方向へ追加します。

一致するものがない場合、FILTERの3番目の引数がHSTACK(key,"No match","")を返します。これにより、リクエストされたグループ、説明用ラベル、空欄の数量からなる3列のフォールバック行が作成されます。MicrosoftはFILTER関数で空の結果を返す引数を、HSTACK関数で水平方向の結合を説明しています。

フォールバックを3列にすることが重要です。入力配列の幅が異なる場合、VSTACKは不足する列を#N/Aで埋めるとMicrosoftは説明しています。1つのセルだけのフォールバックでは、その横に不要な埋め合わせが生じます。VSTACK関数を参照してください。

ワークシートを拡張する前に出力動作を決める

この数式が実装する方針は明確です。すべてのリクエストを順に処理し、一致するソース行をすべて残し、一致しないグループには目印となる行を1行表示します。そのため、リクエスト一覧でグループを繰り返すと、一致するブロックも繰り返されます。ソースレコードが重複している場合も保持されます。この数式には重複を削除する処理はありません。

例を応用するときは、次の選択ガイドを参考にしてください。

希望する結果 適した方法 条件
グループごとに1つの件数 MAPとCOUNTIF 各リクエストが単一の値を返す
リクエスト順にすべてのアセットレコードを表示 上記の完全なREDUCE数式 ドキュメントに記載されたMicrosoft 365 for Macの関数が必要
明示的に指定した少数の検索ブロック 個別のFILTER呼び出しをVSTACKで直接結合 リクエストごとに数式の引数を追加する
セル内に入れ子配列を保持 Microsoftの入れ子配列プレビュー ドキュメントに記載されたBetaの提供状況とブック設定が必要

件数を簡潔に確認するには、K6に次の完全な数式を入力し、その下に3つのセルを空けておきます。

=MAP($E$2:$E$4,LAMBDA(key,COUNTIF($A$2:$A$7,key)))

期待される件数は3、1、0です。各リクエストが1つの数値を返すため、これはMAPの適切な使い方です。COUNTIFは条件を満たすセルの数を数えます。この例では、シンプルなサンプルラベルを使っています。MAP関数とMicrosoft ExcelでCOUNTIF関数を使うを参照してください。

Excel 2024 for Mac向けの固定リクエストの代替方法

お使いのエディションでFILTER、HSTACK、VSTACKが使え、REDUCEが使えない場合は、以前の数式を置き換えて、G1に次の完全な代替数式を入力してください。指定した3つのリクエストについて、同じ期待結果が得られます。

=VSTACK(
  $A$1:$C$1,
  FILTER($A$2:$C$7,$A$2:$A$7=$E$2,HSTACK($E$2,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$3,HSTACK($E$3,"No match","")),
  FILTER($A$2:$C$7,$A$2:$A$7=$E$4,HSTACK($E$4,"No match",""))
)

Microsoftは、必要な3つの関数すべてのドキュメントでExcel 2024 for Macを挙げています。FILTER、HSTACK、VSTACKを参照してください。代わりにメンテナンス上の負担が生じます。リクエストするグループを追加するたびに、FILTERの引数を追加する必要があります。

エラーを診断し、結果を漏れなく保つ

#SPILL!エラーが発生した場合は、数式セルを選択し、出力予定の領域を確認します。既存の値をその領域から移動し、結合セルがないか確認してください。Microsoftは、数式をテーブル内に配置することも原因として挙げています。出力は通常のワークシート範囲に置いてください。#SPILL!エラーを修正する方法を参照してください。

#CALC!エラーの場合は、まずどの計算で発生したかを特定します。複数セルのブロックを返すBYROW数式は、単一値という仕様上の制約に達しています。一方、一致するものがなく、フォールバックも指定されていない単独のFILTERでは、空配列エラーが発生することがあります。Microsoftの#CALC!エラーの説明では、これらは別々の原因として説明されています。

ソースレコードを追加するときは、$A$2:$C$7と$A$2:$A$7の両方を同じ最終行まで拡張してください。REDUCE版にリクエストを追加するときは、$E$2:$E$4を最後に処理するリクエストまで拡張します。未使用のリクエストセルを含めないでください。数式は範囲内のすべてのセルを処理します。

ソース一覧が定期的に増える場合は、ソースをExcelのテーブルに格納し、固定のソース参照を構造化参照に置き換えることを検討してください。Microsoftの説明によると、構造化参照はテーブルの行が追加または削除されると自動調整されます。スピルする結果はテーブルの外に置いてください。動的配列数式とスピル配列の動作を参照してください。

組み立てたアセット一覧を使う前に、グループごとのブロックをリクエスト一覧と照合し、一致しないものを示す目印があるか確認してください。この例では、確認内容は明確です。Mosaicが3件、Orbitが1件、Pebbleの目印が1つ表示され、Prismは含まれません。これで、指定したデータに対する選択と順序が意図どおりであることを確認できます。

参照元