条件を抽出して該当したものだけを加算した数式の組み立て

Question

下記に表①があります。

四半期	顧客名	製品	金額
2025-Q1	A商社	リンゴ	¥44,550
2024-Q1	B商社	なし	¥26,350
2024-Q1	C商社	ばなな	¥55,500
2024-Q1	D商社	ぶどう	¥75,950
2024-Q1	A商社	いちご	¥119,250
2025-Q1	B商社	キウイ	¥150,300
2025-Q1	C商社	もも	¥168,300
2025-Q1	D商社	みかん	¥27,900

次に表②があります。

		リンゴ	なし	ばなな	ぶどう	いちご	きうい	もも	みかん	売上
A商社	2024-Q1	1	1		1	1	1	1	1
B商社	2024-Q1		1		1		1
C商社	2024-Q1	1			1			1
D商社	2024-Q1	1		1			1
A商社	2025-Q1	1		1		1		1	1
B商社	2025-Q1	1			1	1	1	1	1

元データは表①のフォーマットで抽出されます。

表②の売上の箇所へ該当の製品のフラグが立っている製品だけの売上を算出したいです。

「SUMIFS(表①金額、表①製品、表②りんごの列、...）」という場合とても長くなってしまいます。

1のフラグが立っている該当期間の該当製品のみの集計のなるべく短めの計算式はないのかな？と考えています。

よろしくお願いいたします。

Answer 1

こんにちは。

最初から確認で恐縮です。

文章にするとうまく表現できていないかもしれませんが、

『表①と表②で「四半期」「顧客名」「製品」の組み合わせが一致していて、且つ、表②に 1 が立っている「製品」についてのみ、その「金額」の総和を表②の「売上」列に求めたい。』

といったことで良いでしょうか？。

その場合ですが、お使いになられている Excel のバージョンが不明なので、取り合えず、最新版の Excel（ Microsoft365 の Excel、Excel2024、など）でのみ使える数式の具体例を考えてみましたのでご紹介します。

旧来の Excel（ Excel2021、Excel2019、Excel2016、 など）でも使える数式の具体例をご希望でしたら、返信でお知らせください。

両方とも一度にご紹介すると、かなり長い説明になってしまいますので。

尚、表①と表②については、具体的なセル配置（セルアドレス）が分かりませんので、下記の図１と図２のような配置の表があると仮定して数式を組んでいます。（図１と図２では、検証のため適当にデータを追加しています。ご了解ください。）

それと、各数式についてですが、表①のデータの入っている範囲は 2行目から 1000行目までと仮定して組んでいます。

・図１（ シート名：表① ）

・図２（ シート名：表② ）

※ K列（「売上」列 ）に数式の結果が表示されている状態です。

ここでは、表①と表②で「四半期」と「顧客名」の組み合わせが一致していて、且つ、表①にその「製品」が存在しているセルの背景をオレンジ色で色付けしています。（計算対象のセルを分かりやすくするためです。）

つまり、この色付けされたセルに 1 が立っている「製品」の「金額」の総和が K列（「売上」列 ）に表示されています。

尚、グリーンで色付けされているセルは、「金額」の異なる複数の同一の「製品」が表①に存在する場合を表しています。（この場合も正常に計算処理されます。）

下記の数式１は、図２（ 表② ）の K2セルに入れる数式になります。

繰り返しになりますが、最新版の Excel（ Microsoft365 の Excel、Excel2024、など）でのみ使える数式です。ご注意ください。

・数式１

=SUM(MAP(FILTER(C$1:J$1,C2:J2),LAMBDA(dt,SUMIFS(表①!$D$2:$D$1000,表①!$A$2:$A$1000,B2,表①!$B$2:$B$1000,A2,表①!$C$2:$C$1000,dt))))

この数式１では、表②に「製品」列が増えた場合でも FILTER 関数に指定しているセル範囲（ C$1:J$1 と C2:J2 )を変更するだけで済みますので、これ以上長い数式になることは基本的にありません。

※ K2セルに入れたこの数式１をコピーし、下方向（行方向）に必要なだけ貼り付けます。（図２では K9セルまで貼り付けています。）

＜数式１の動作概要＞

まず、FILTER 関数により、表②の 2行目（ C2:J2 の範囲 ）で 1 が立っているセルの列見出しの配列（ C$1:J$1 の範囲から抽出）が作成され MAP 関数に渡されます。

MAP 関数は、渡された配列の要素を一つずつ取り出し LAMBDA 関数に渡します。

LAMBDA 関数では、渡された値を dt と名前を付けた引数として受け取り SUMIFS 関数に渡します。

SUMIFS 関数では、表①の「製品」列の該当の値の合計金額を返すので、結果的に、MAP 関数から渡された値の数の合計金額の配列が作成され SUM 関数に渡されます。

SUM 関数では、渡された配列の値の総和を求めて表示します。

以上です。

もし、ご希望の結果が図２のようなものではない場合、ご希望の結果との相違点を詳しくお知らせいただければと思います。

Windows11 と Microsoft365 の Excel の組み合わせで動作確認しています。

ご参考になれば幸いです。

＜説明文の修正＞

下記説明文の一部が間違っていましたので修正しました。

誤：最新版の Excel（ Microsoft365 の Excel、Excel2024、Excel2021、など）

正：最新版の Excel（ Microsoft365 の Excel、Excel2024、など）

誤：旧来の Excel（ Excel2019、Excel2016 など）

正：旧来の Excel（ Excel2021、Excel2019、Excel2016 、など）

失礼いたしました。

＜説明文の修正＞

数式１の動作概要の中の 1行目の文章が分かり難くなってしまっていましたので修正しました。

＜数式の追加＞

返信がありませんが、念のため、旧来の Excel（ Excel2021、Excel2019、Excel2016、 など）でも使える数式も考えてみました。

※図２（ 表② ）の K2セルに入れる数式です。（数式１と同様のコピー・貼り付けが必要です。）

・数式２

=SUM(IF($C2:$J2=1,SUMIFS(表①!$D$2:$D$1000,表①!$A$2:$A$1000,$B2,表①!$B$2:$B$1000,$A2,表①!$C$2:$C$1000,C$1:J$1),0))

※この数式２は、入力後に Ctrl＋Shift＋Enter キー押下で確定して配列数式にする必要があります。

※配列数式になると自動的に数式全体が { } で囲われますので、必ずこれを確認してください。

尚、この数式２は最新版の Excel（ Microsoft365 の Excel、Excel2024、など）でも使えますので、その際は配列数式にする必要はありません。

Excel2021 をお使いの場合も配列数式にする必要はありません。

こちらの方が数式は短く出来ますね。

ただ、処理効率については、FILTER 関数で範囲を絞り込んでいる数式１の方が良い可能性もありそうですが・・・。

確かめてはいませんので、単なる推測です。

よろしければ、お試しになってみてください。

Answer 2

この応答は自動的に翻訳されています。 その結果、文法上の誤りや奇妙な言い回しが生じる可能性があります。

この応答は自動的に翻訳されています。その結果、文法上の誤りや奇妙な言い回しが生じる可能性があります。

C13の数式を作成し、数式を他のセルにコピーします。

=LET(a,COUNTIFS($A$2:$A$9,$B13,$B$2:$B$9,$A13,$C$2:$C$9,C$12),IF(a=0,"",1))