Excel for Macで対応する在庫項目を結合するには、一致する数値配列を + で加算します。資産の行の順序が異なる場合は、まず各資産を検索し、返された数量を加算します。これにより、各項目の結果が個別に保たれます。

この手順では、2つの項目（印刷物の部数と見本カード）を持つ架空のアート資産を使用します。目的は、各資産と項目を識別できる状態を保ちながら、2か所の保管場所にある数量を結合することです。すべての数量は説明用です。

資産の対応関係に応じて数式を選ぶ

数式を書く前に、どの値同士を対応させるのかを確認します。位置による対応が有効なのは、両方の入力範囲で資産の順序と項目の順序が同じ場合だけです。資産ラベルで対応させれば、元データの行の順序が異なっていても処理できます。

入力条件 対応する方法 結果
資産、行の順序、項目の列がすべて同じ 同じサイズの数値範囲を加算する 資産と項目ごとに1つの結果
資産ラベルが一意で、行の順序が異なる 複数列の XLOOKUP の結果を2つ加算する 1つの資産の各項目が別々のセルに表示される
結合する必要がある重複資産ラベルがある 項目ごとに個別の SUMIF 計算を使う 指定した資産と項目ごとに1つの結果

以下の例では、Microsoftが公開している配列計算、複数項目の検索結果の返却、条件付き合計の仕様に基づいて、これらの方法を説明します。数式をコピーする前に、入力データの構造に合った方法を選んでください。

お使いのMac版を確認する

検索の例は、Excel for Microsoft 365 for Mac、Excel 2024 for Mac、Excel 2021 for Macに対応しています。ページ全体の適用対象一覧には記載されているものの、Microsoftは XLOOKUP がExcel 2016およびExcel 2019では使用できないと明記しています。MicrosoftのXLOOKUPのドキュメント。

Macでは、Officeアプリのアプリケーションメニューにある About ダイアログで、バージョンとライセンスの種類を確認できます。Excelでは、Excel → About Excel を選択します。数式を選ぶ前に、バージョン番号だけでなく製品エディションも確認してください。Officeについて: 使用しているOfficeのバージョンを確認する。

この記事で後述する任意の HSTACK 数式には、Excel for Microsoft 365 for MacまたはExcel 2024 for Macが必要です。Microsoftは、この関数をExcel 2021 for Macの対応対象として記載していません。主な計算にはこの関数は必要ありません。MicrosoftのHSTACKのドキュメント。

2つの小さなアート資産在庫を準備する

新しいワークシートに、通常のセル範囲を使用します。最初の保管場所の在庫を A1:C4 に入力します。

資産 印刷物の部数 見本カード
Tile-A 8 3
Tile-B 5 7
Tile-C 6 2

2つ目の保管場所の在庫を E1:G4 に入力し、列 D は空欄にします。

資産 印刷物の部数 見本カード
Tile-A 4 2
Tile-B 9 1
Tile-C 3 5

どちらの数値範囲も3行×2列です。最初の数値列は印刷物の部数、2列目は見本カードの枚数を表します。ラベルは計算範囲の外にあります。

J1:L4 に以下の出力領域を準備します。最初は見出しと資産ラベルだけを入力し、数量セルは空欄にしておきます。

資産 印刷物の合計部数 見本カードの合計枚数
Tile-A
Tile-B
Tile-C

これらの例では、すべての数量が数値で、各資産は各データ元に1回ずつ登場します。レコードが存在しない場合と、記録された数量がゼロの場合では意味が異なります。数量がゼロだと分かっている場合は0を使い、レコードがない場合は0として扱う前に確認してください。

配列を対応させて加算し、対応する結果をすべて保つ

元データの行が表示されている順序どおりの場合、K2 に次の完全なワークシート数式を入力します。

=B2:C4+F2:G4

Return キーを押して数式を確定します。動的配列に対応したExcelでは、結果は K2:L4 に展開されます。つまり、資産の3行と項目の2列です。Microsoftの説明によると、配列数式は複数の結果を返すことができ、動的配列の結果は隣接するセルにスピルします。配列数式のガイドラインと例。

期待される出力は次のとおりです。

資産 印刷物の合計部数 見本カードの合計枚数
Tile-A 12 5
Tile-B 14 8
Tile-C 9 7

この計算は位置に基づいています。B2+F2 は12、C2+G2 は5になります。残りの各位置も同じ方法で計算されます。2つの元データ範囲は、同じ大きさの1つの結果範囲になります。

1列の在庫の場合、同等の数式は =B2:B4+F2:F4 です。3つの数量が行ごとに1つずつ返されます。2列の例では、1つ目の項目に加えて2つ目の項目も保持します。

外側のSUMで形が変わる理由

次の数式は総計を計算します。

=SUM(B2:C4,F2:G4)

=SUM(B2:C4+F2:G4)

この例では、どちらも55を返します。この数値には、3つすべての資産と2つの項目が混在しています。Microsoftの説明によると、SUM は数値、参照、範囲として指定された値を加算します。そのため、結果配列を SUM で囲むと、1つの値に集約されます。MicrosoftのSUMのドキュメント。

上記の6セルの結果を合格条件として使用してください。1つのセルに55だけが表示される場合、計算自体は正しくても、別の問題に答えています。

行が対応している場合のバージョン非依存の配置

個別のセル数式を使う場合は、K2 に =B2+F2 を入力します。フィルハンドルを右にドラッグして L2 までコピーし、次に K2:L2 を4行目まで下にコピーします。相対参照が調整され、対応する各値の組み合わせが計算されます。MicrosoftはExcel for Macでのこのフィルハンドルの動作を説明しています。Excel for Macを計算機として使用する。

これにより、スピル結果を使わずに同じ6つの数量が得られます。ただし、元データの行の順序が一致している必要があります。

元データの行順が異なる場合は資産ラベルで照合する

行の位置を信用できない場合は、まず K2 の前の結果を消去します。J2:J4 は要求する資産の一覧として残します。K2 に次の数式を入力します。

=XLOOKUP($J2,$A$2:$A$4,$B$2:$C$4)+XLOOKUP($J2,$E$2:$E$4,$F$2:$G$4)

各検索で J2 から資産を探し、数量の2列を返します。返された行を加算すると、K2 に12、L2 に5が表示されます。Microsoftは、既定で完全一致検索が行われることと、複数列の戻り値範囲から複数項目を返す機能を説明しています。MicrosoftのXLOOKUPのドキュメント。

K2 の数式を K3:K4 にコピーし、横方向に結果が表示される L2:L4 は空欄のままにします。ドル記号は元データ範囲を固定し、要求する資産は出力行ごとに変わります。

確認として、2つ目の元データのレコード全体を並べ替え、Tile-C を先頭に移動します。各ラベルを2つの数量と一緒に移動してください。検索式を使った出力は期待される値のままですが、位置による加算では異なる資産同士が組み合わされます。

見つからないラベルは、任意の見つからない場合の置換値が数式に指定されていないため、#N/A を返します。ラベルが重複している場合は、レコードを合算せず最初に一致したものを返します。これらは、ドキュメントに記載されている検索関数の制約です。MicrosoftのXLOOKUPのドキュメント。

出力に含まれるのは J2:J4 に一覧表示された資産だけです。独自のデータを使う前に、いずれかの保管場所からレポートするすべての資産がこの一覧に含まれていることを確認してください。

重複する資産レコードを項目ごとに結合する

同じ資産に属する行が複数ある場合は、条件付き合計を使います。元の例の範囲を使用し、K2 に次の完全な数式を入力します。

=SUMIF($A$2:$A$4,$J2,B$2:B$4)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

右隣の L2 にコピーし、次に K2:L2 を4行目まで下にコピーします。右に移動すると、ラベル範囲は固定されたまま、数量範囲が見本カードの列に変わります。各出力セルは、その資産と項目の数量だけを合計します。Microsoftは、条件を判定する範囲と対応する数量を含む範囲を別々に指定する方法を説明しています。MicrosoftのSUMIFのドキュメント。

重複レコードの例として、A5:C5 に Tile-A、2、1を追加します。再計算する前に、最初の元データの条件範囲と数量範囲を5行目まで広げます。

=SUMIF($A$2:$A$5,$J2,B$2:B$5)+SUMIF($E$2:$E$4,$J2,F$2:F$4)

変更した数式を、これまでと同じように右と下にコピーします。説明用の Tile-A の結果は、印刷物が14部、見本カードが6枚になります。4行目で終わる固定範囲では追加されたレコードが除外されるため、この方法では範囲の管理も必要です。

条件範囲と数量範囲は、同じサイズと形にしてください。また、SUMIF では * と ? がワイルドカードとして扱われます。ここで使う架空のラベルには、どちらの文字も含まれていません。MicrosoftのSUMIFのドキュメント。

スピルエラーを解決して在庫の完成状態を確認する

スピル数式には、結果全体を表示するための空き領域が必要です。Excelのテーブルの外に配置し、左上のセルを通して編集または置換します。Microsoftは両方の制限を説明しています。動的配列数式とスピル配列の動作。

#SPILL! が表示された場合は、出力先に既存の値や結合セルがないか確認します。妨げになっている内容を別の場所に移動するか、空いている出力領域を選んでください。Microsoftは、出力先がふさがれていることや結合セルをスピルエラーの原因として挙げています。#SPILL! エラーを修正する方法。

加算で #VALUE! が返る場合は、数量に文字列が含まれていないか確認します。Microsoftの説明によると、数値以外の文字列に対する直接加算は失敗することがありますが、SUM は文字列を無視します。そのため、数式を SUM に置き換えると、結果の形と文字列の扱いの両方が変わります。MicrosoftのSUMのドキュメント。

Microsoft 365 for MacまたはExcel 2024 for Macでラベル付きの結果範囲を任意で作成する場合は、新しい空の領域で元の対応済みデータを使います。

=HSTACK(A2:A4,B2:C4+F2:G4)

資産ラベルが計算された項目の横に表示されます。HSTACK は列を追加し、数式内の + が加算を行います。ラベルを照合したり、異なる元データの行順を修正したりすることはありません。MicrosoftのHSTACKのドキュメント。

最後に3点を確認します。出力に意図したすべての資産が含まれていること、各項目がそれぞれ別の列に保たれていること、手作業で確認した1行が元データの数量と一致することです。入力の行順が対応している場合は範囲を加算します。一意のラベルがあり行順が異なる場合は検索結果を加算します。重複レコードがある場合は、資産ごとに項目を個別に合計します。完成した在庫表では、各数量が何を数えているのかを特定するための詳細が保たれている必要があります。

参考資料