FILTERを使って、Excel for Macでアートアセットのラベルに関連付けられたすべての数量を返します。結果の配列はワークシートのセルにスピルさせたり、別の計算に使ったり、重複する一致項目を失わずに合計したりできます。
この手順では、架空のグラフィックアセットのラベルと数量を使います。目的は、ラベルを1つ選び、それに該当する数量をすべて取得し、それぞれの数値を計算に再利用することです。Microsoftの説明によると、FILTERは指定した条件に基づいて配列を返します。FILTER関数。
この方法をサポートするExcel for Macのバージョンは？
Microsoftの最新の関数ドキュメントには、Excel for Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for MacがFILTERをサポートすると明記されています。以下の数式には、これらのいずれかのエディションを使用してください。FILTER関数。
インストールされているバージョンを確認するには、Excelを開き、Excel → Excelについてを選択します。Microsoftの説明では、このMacのメニュー操作が案内されています。また、バージョン確認ガイドによると、Macアプリケーションの「Excelについて」ダイアログにはバージョン番号とライセンスの種類が表示されます。ビルド番号だけでなく、エディションも確認してください。Office for Macの他のバージョンとのドキュメント共有; Officeについて: 使用しているOfficeのバージョンを確認する。
Excel 2019 for MacとExcel 2016 for Macは、FILTERの対応状況としてドキュメントに記載されていません。いずれかのエディションを使用している場合、この動的配列のワークフローを実行するには、一覧にあるMacエディションのいずれかでブックを開いてください。以下の手順はこの前提条件に依存しており、旧式の配列数式で代用する方法ではありません。
アセット数量の簡単な例を準備する
空のワークシートのA1:B7に、次のデータを入力します。1行目は見出しです。B6のゼロを含め、数量は数値として入力してください。
| ワークシートの行 | A列: アセット | B列: 数量 |
|---|---|---|
| 1 | アセット | 数量 |
| 2 | Tile A | 4 |
| 3 | Arc B | 7 |
| 4 | Tile A | 2 |
| 5 | Dot C | 5 |
| 6 | Tile A | 0 |
| 7 | Tile A | 4 |
D1に選択したアセット、D2にTile Aと入力します。F1には一致した数量と入力します。結果が展開できるよう、F2の下のセルは空けておいてください。
この例には、同じラベルに対して数量4が2つ、数量0が1つ含まれています。正しい結果では、該当する4行すべてを保持する必要があります: 4、2、0、4。重複する値を削除したり、ゼロを除外したりすると、求める結果が変わってしまいます。
FILTERですべての一致する数量を返す
F2を選択し、次の数式を入力してReturnキーを押します:
=FILTER(B2:B7,A2:A7=D2)
この数式では、数量の配列としてB2:B7を使い、包含判定にA2:A7=D2を使います。これらの範囲は同じ6つの元データ行を対象としており、包含配列の高さまたは幅が対応している必要があるというMicrosoftの要件を満たします。FILTER関数。
このサンプルデータの場合、結果はF2:F5に表示されます:
| セル | 結果 |
|---|---|
| F2 | 4 |
| F3 | 2 |
| F4 | 0 |
| F5 | 4 |
数式はF2だけに入力します。Excelが追加の結果を隣接するセルに自動的に配置します。Microsoftはこの動作をスピルと呼んでいます。後で数式を変更するときは、結果の左上のセル（ここではF2）を編集します。動的配列数式とスピル配列の動作。
4が2回出現するのは、該当する元データの行が別々だからです。選択したアセットの出現分すべてを次の計算に反映させるには、両方を残してください。
XLOOKUPではこの4行を取得できない理由
Microsoftの説明によると、XLOOKUPは最初に見つかった一致項目に対応する値を返します。一致した行から複数の列を返すことはできますが、同じラベルを含むすべての行を列挙する機能ではありません。XLOOKUP関数。
たとえば、次の数式はサンプルから4だけを返します:
=XLOOKUP(D2,A2:A7,B2:B7)
一致する行が1つあれば答えが得られる場合には、この結果が適切です。この作業では、4つの別々の一致行から数量を取得するため、FILTERの結果を使用してください。
配列を後続の計算で再利用する
F2#で結果全体を参照します。Microsoftの説明では、ハッシュ記号はスピル範囲演算子であり、開始セルから生成された配列全体を参照します。これにより、現在の4セルの結果に合わせて後続の数式を固定せずに済みます。配列数式のガイドラインと例。
後続の数式は結果のすぐ下ではなく横に置き、さらに一致する数量が増えても表示できる空間を確保してください。以下の例ではH列からJ列を使います。
一致した数量をすべて合計する
H1に合計数量と入力し、H2に次の数式を入力します:
=SUM(F2#)
例としての結果は10です。4に2、0、4を加えた値です。SUMは範囲を受け取り、その数値を合計します。SUM関数。
合計だけが必要な場合は、代わりに別の空のセルで直接計算できます:
=SUM(FILTER(B2:B7,A2:A7=D2))
個々の数量を確認または再利用する場合は、表示される配列を選びます。ワークシートに最終的な合計だけが必要な場合は、入れ子の数式を選びます。
各数量に共通の係数を掛ける
この例で、一致した数量ごとに3部ずつ作るとします。I1に部数、I2に数値3を入力します。J1に調整後の数量と入力し、J2に次の数式を入力します:
=F2#*$I$2
J2:J5に期待される結果は、12、6、0、12です。Microsoftは、値を乗算して複数の結果を返す配列計算について説明しています。また、Mac向けの電卓ガイドでは、アスタリスクが乗算演算子として示されています。配列数式のガイドラインと例; Excel for Macを電卓として使う。
調整後の合計だけが必要な場合は、H4に次の数式を入力します:
=SUM(F2#*$I$2)
期待される合計は30です。これらは提示した架空のデータを使った計算例です。算術計算は別途検算し、ブック内の数式はExcel for Macで実行せずにMicrosoftのドキュメントと照合しました。
一致がない場合の処理を決める
D2を、サンプルに存在しないラベルWave Dに変更します。元の2引数のFILTER数式は、Excelが空の配列を返せないため#CALC!を返します。Microsoftの説明では、結果がない場合の値を指定する方法として、任意の第3引数が案内されています。#CALC!エラーの修正方法。
次の計算で必要な内容に合わせて、その値を選んでください。以下はこのワークシートでの判断材料です:
| 要件 | 数式または方法 | 結果 |
|---|---|---|
| 計算中も一致しないことを見える状態にする | =FILTER(B2:B7,A2:A7=D2)を維持する |
一致がないと#CALC!が返され、確認が必要であることがわかります。 |
| 主に読みやすい一覧が必要 | =FILTER(B2:B7,A2:A7=D2,"No matches")を使う |
一致がないとテキストメッセージが返されます。 |
| 存在しないラベルを合計に0として加える | =SUM(FILTER(B2:B7,A2:A7=D2,0))を使う |
一致がないと合計は0になります。 |
代替値として0を使うのは、意図的な計算ルールです。一致する元データの行に数量0があることを意味するわけではありません。このサンプルでは、Tile Aには実際に数量0の行がありますが、Wave Dに該当する元データの行はありません。アセットの有無が重要な場合は、この違いを保ってください。
テキストメッセージにも注意が必要です。Microsoftの説明では、SUMは参照先セルのテキストを無視します。そのため、No matchesだけを含む結果を合計すると0になり、一致行がないことが見えなくなる可能性があります。ラベルが見つからない場合に後続の計算を停止させたいときは、エラーを表示する方法を使ってください。SUM関数。
元の数値例を続ける前に、D2をTile Aに戻してください。
作業で必要な場合にのみ2つ目の条件を追加する
後の作業で正の数量だけが必要になった場合は、ラベルの条件と数量が正である条件の両方を使います:
=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))
サンプルでは、4、2、4が返されます。Microsoftの説明では、FILTERにおいて、条件間の乗算はAND判定として機能します。この方法では一致するゼロが意図的に除外されるため、一致する数量をすべて取得する必要がある場合は元の数式を使ってください。FILTER関数。
結果をトラブルシューティングし、新しい行も対象にする
数式が期待した4つの数量を返さない場合は、次の順でワークシートを確認してください:
- 出力先の空き領域を確認します。 結果の展開先がふさがれていると
#SPILL!が発生します。再試行する前に、スピル範囲にある必要な内容を移動してください。 - 数式の場所を確認します。 スピルする配列数式は、Excelテーブルの外にあるワークシートのセルに入力します。
- ブック間の依存関係を確認します。 Microsoftでは、ブック間でリンクした動的配列を使用できるのは、両方のブックを開いている間に限られます。元のブックを閉じてから更新すると、
#REF!が発生することがあります。
これらの制限は動的配列数式とスピル配列の動作に記載されています。
次に、元データのラベルと数量を確認します。D2を対象のアセットセルと照合し、数量が数値として入力されているか確認してください。SUMはテキスト値を無視することがあるため、もっともらしい合計だけでは確認として不十分です。SUM関数。
サンプルの数式は元データの2～7行目を参照しています。8行目にデータを追加する場合は、両方の元データ範囲を含めるよう編集してください。たとえば、A8にTile A、B8に数値の数量3を入力した後、F2の数式を次の式に置き換えます:
=FILTER(B2:B8,A2:A8=D2)
期待される配列は4、2、0、4、3となり、合計は13になります。F2#参照は拡張後の結果全体を参照しますが、元の範囲に含まれていない行を自動的に追加することはできません。
頻繁に行が増える一覧には、行の追加や削除に応じて参照が調整される構造化参照付きのExcelテーブルをMicrosoftは推奨しています。スピルする出力はそのテーブルの外に置いてください。動的配列数式とスピル配列の動作。
合計を信頼する前に、まず個々の結果を確認します。元データの6行でTile Aを選択すると、4つの数量が返され、その中には両方の4と0が含まれるはずです。それを確認したら、計算でF2#を再利用し、ワークシートの目的に合った、一致がない場合の処理を明示的に選んでください。
参照元
- FILTER関数 — Microsoft Support。
- Office for Macの他のバージョンとのドキュメント共有 — Microsoft Support。
- Officeについて: 使用しているOfficeのバージョンを確認する — Microsoft Support。
- 動的配列数式とスピル配列の動作 — Microsoft Support。
- XLOOKUP関数 — Microsoft Support。
- 配列数式のガイドラインと例 — Microsoft Support。
- SUM関数 — Microsoft Support。
- Excel for Macを電卓として使う — Microsoft Support。
- #CALC!エラーの修正方法 — Microsoft Support。