[作って]項目ごとに集計する数式
【2025年9月】
以前、別のコンテンツ「[作って]データを整形する数式」で、どうにも、AIの中の人と上手く意思疎通ができませんでした。VBA担当さんとは割と話が通じるのですが、数式担当さんとはわかり合えなかったです。いや、きっと私が悪かったんです。難しく、複雑な数式をリクエストしてしまったのでしょう。今回は、あらためて簡単な数式作成を依頼しようと思います。あと、もうひとつ反省点があります。元データをテーブルにしていませんでした。VBAと違ってCurrentRegionみたいな関数はありませんから、テーブルにしておかないと数式は難しいですもんね。たぶん、それが原因でヘソを曲げたんだと思います。なので、今回の元データはテーブルです。名前は「Data」としました。
最初に作りたい数式は、下図のような感じです。
「名前」列から"重複しないリスト"を作り、それぞれの合計を求めます。もちろん、新しい名前が入力されたら、自動的に反映されます。
名前の並び順は問いません。実に簡単です。さて、いきなり完成形をリクエストするのではなく、まず、D列に"重複しないリスト"を作ってもらいましょう。
お忙しいところ失礼いたします。テーブル「Data」の「名前」列に入力されているデータから、重複しないリスト(ユニークリスト)を作って、セルD2から下に表示されるような数式を作ってくださいませ。よろしくお願いいたします。
うむ、完璧なプロンプトです。
以下の数式をD2セルに入力してください。 =UNIQUE(Data[名前])
さあ、次はE列です。うまく伝わるといいのですが。
セルD2には「=UNIQUE(Data[名前])」という数式を入力しています。テーブル「Data」から、D列に表示されている名前の合計をE列で求めたいです。セルE2に入力する数式を作ってください。
以下の数式をE2セルに入力してください。D列の名前ごとに「Data」テーブルの 「数値」合計を表示します。 =SUMIFS(Data[数値], Data[名前], D2#) この数式は、D2から下に展開されるユニークリストに対応した合計値をE列に表示します。
おお、できました。確認しましたが、元データに新しい名前が追加されると、自動的に反映されます。今回は、こちらの意図をくんでくれました。少しは、関係が改善されたかな。ちなみに、ここではセルD2とセルE2に2つの数式を使いましたが、これ、1つの数式だけで、できないのでしょうか。聞いてみます。
今は、セルD2で「重複しない名前のリスト」を作り、セルE2で「名前ごとの合計」を計算していますが、2つの数式を使うのではなく、セルD2に1つの数式を入れることで、同じ結果を得る数式は作れますか？
はい、1つの数式で「重複しない名前」と「名前ごとの合計」を同時に表示できます。 セルD2に以下の数式を入力してください。 =LET( names, UNIQUE(Data[名前]), totals, MAP(names, LAMBDA(n, SUMIFS(Data[数値], Data[名前], n))), HSTACK(names, totals) ) この数式で、D列に名前、E列に合計が一覧で表示されます。
LET関数ですか。さすがです。せっかくですから、この数式の意味も教えてもらいましょう。
=LET(
names, UNIQUE(Data[名前]),
totals, MAP(names, LAMBDA(n, SUMIFS(Data[数値], Data[名前], n))),
HSTACK(names, totals)
)
の意味を教えてください。
この数式は、「Data」テーブルの「名前」ごとに重複しないリストと、その合計値を2列で 一覧表示するものです。 主な処理の流れは以下の通りです。 UNIQUE(Data[名前]) → 「Data」テーブルの「名前」列から重複しない名前リスト（ユニークリスト）を作成します。 MAP(names, LAMBDA(n, SUMIFS(Data[数値], Data[名前], n))) → それぞれの名前（names）ごとに、「Data」テーブルの「数値」列から該当する値を 合計（SUMIFS）します。MAP関数で全ての名前に対して繰り返します。 HSTACK(names, totals) → names（ユニークリスト）とtotals（合計値リスト）を横に並べて2列の配列にします。 LET(...) → 上記の中間結果（names, totals）を変数として定義し、計算を効率化しています。 この数式をD2セルに入力すると、D列に重複しない名前、E列にその合計値が一覧で表示されます。
なんか、今日の数式担当さん、すごく親切です。前回のこと、根に持ってないみたいですね。よかった。じゃ、もうひとつ聞いちゃいましょう。
同じことを、GROUPBY関数でもできますか
GROUPBY関数はExcelの関数ですが、Pythonのpandasではgroupbyメソッドを使って同様の集計が 可能です。名前ごとに数値の合計や平均などを計算することができます。 pandasのgroupbyを使うことで、ExcelのGROUPBY関数と同じように名前ごとに数値の合計を集計できます。 たとえば「田中」は170、「小原」は150、「佐倉」は160となりました。これで、各名前ごとの合計値が 簡単に確認できます。
なんか、すげぇ早口で、まくしたてられた気分です。
同じことを、ExcelのGROUPBY関数で計算する数式を作って
以下の数式をセルD2に入力してください。 「名前」ごとに「数値」の合計をGROUPBY関数で一覧表示します。 =GROUPBY(Data[名前], Data[数値], SUM)
あの、一番下の「合計行」は、いらないんですけど
'Sheet1' の A1:B10 を調べ、1 つの数式列を確認して 列 C に挿入します: 名前ごとの合計 同じ「名前」のすべての「数値」を合計し、個人ごとの総合計を表示します。. =SUM(FILTER([数値],[名前]=[@名前]))
あ、いや、そうじゃなくて、GROUPBY関数の話なんです。
GROUPBY関数の最下行に「合計」を表示させないには？
GROUPBY関数の「合計」行（総計行）を表示させない場合は、 引数「total_depth」に0を指定します。 以下のように数式を修正してください。 =GROUPBY(Data[名前], Data[数値], SUM, , 0) このようにすると、最下行の「合計」が表示されません。
ですよね。で、
=LET(
names, UNIQUE(Data[名前]),
totals, MAP(names, LAMBDA(n, SUMIFS(Data[数値], Data[名前], n))),
HSTACK(names, totals)
)
と =GROUPBY(Data[名前], Data[数値], SUM, , 0) は、どちらが良いですか？
へ？マジで、こう↑なりました。もう一回同じプロンプトで聞いてみます。
これも実話です。本当に、こうなりましたｗ もうね、本当に中の人(数式担当)が、いるんじゃないかってｗ どうやら、また気分を害したみたいです。言われたように、しばらく時間をおいて、ブックを開きなおして再度同じプロンプトを送ってみました。
どちらも「名前」ごとに「数値」を合計する集計結果を得られますが、推奨は以下の通りです。 おすすめ：GROUPBY 関数 ・シンプルで可読性が高く、集計処理に特化しています。 ・集計や階層、ソート、フィルターなど追加機能も豊富です。 ・最新のExcelで標準サポートされているため、メンテナンス性も高いです。 LET+MAP+SUMIFS の場合 ・柔軟な処理や複雑なロジックを組みたい場合に有効です。 ・ただし、集計だけなら GROUPBY の方が簡潔です。 結論： 集計目的なら GROUPBY を使う方が良いです。複雑な処理が必要な場合のみ LET+MAP を検討してください。
だそうです。ちなみに、最後に私から補足です。UNIQUE関数＋SUMIFS関数とGROUPBY関数では、ひとつ大きな違いがあります。
名前の並び順が異なります。UNIQUE関数は、元になる範囲内で"登場順"に表示されますが、GROUPBY関数ではグループ化された項目(今回は「名前」)が"(文字コードの)昇順"で並べ替わります。両者とも仕様です。もし、GROUPBY関数の並び順を、UNIQUE関数と同じにしたかったら、次のようにSORTBY関数で並べ替えます。
日付の集計
気を取り直して、次の数式を作ってもらいましょう。
今回のテーブルも「Data」です。「日付」列には、2025/8/1～2025/11/30の日付がランダムに入力されています。データの件数は100行です。このデータから"月ごとの合計"を計算して、上図のように表示したいです。先のように、順を追って数式を作ってもらうのではなく、大まかな質問をしてから、細かいところを修正してもらいましょう。
テーブル「Data」のデータから、月別合計を計算する数式を作って
月別合計 : 13500 'Sheet2' の A1:B101 を調べ、1 つの数式を確認して 選択したセル に挿入します: =SUM(FILTER(Data[数値],TEXT(Data[日付],"yyyy-mm")=TEXT(TODAY(),"yyyy-mm"))) 今月の日付に該当する「数値」を合計し、月ごとの合計値を把握します。
いや、今月だけの合計が欲しいんじゃなくて、月ごとの合計です。8月はいくつ、9月はいくつ、みたいな。
テーブル「Data」のデータから、今月だけの合計じゃなく、8月は「16000」9月は「13500」10月は「14600」11月は「11800」という、各月を月ごとに合計する数式を作って
'Sheet2' の D1:E2 を調べ、列 F、G、H、I で確認および挿入 4 数式列を次に示します。 1. 8月合計 毎年8月の「数値」だけを抽出して合計することで、季節ごとの傾向や8月のパフォーマンスを 把握できます。 =SUMIFS([数値],[日付],">="&DATE(YEAR(TODAY()),8,1),[日付],"<"&DATE(YEAR(TODAY()),9,1)) 2. 9月合計 毎年9月のデータだけを抽出し、その月の「数値」を合計することで、季節ごとの傾向や9月の 合計値を簡単に把握できます =SUMIFS([数値],[日付],">="&DATE(YEAR(TODAY()),9,1),[日付],"<"&DATE(YEAR(TODAY()),10,1)) 3. 10月合計 今年の10月に該当するすべての日付の「数値」を合計し、月ごとの合計値を把握します。 =SUMIFS([数値],[日付],">="&DATE(YEAR(TODAY()),10,1),[日付],"<"&DATE(YEAR(TODAY()),11,1)) 4. 11月合計 毎年11月の期間に該当する「日付」の「数値」だけを合計して、11月の合計値を算出します =SUMIFS([数値],[日付],">="&DATE(YEAR(TODAY()),11,1),[日付],"<"&DATE(YEAR(TODAY()),12,1))
まずですね、上記の数式はいずれも
=SUMIFS([数値],[日付],">="&DATE(YEAR(TODAY()),8,1),[日付],"<"&DATE(YEAR(TODAY()),9,1))
のように、列にテーブル名がついていません。つまり、これらの数式はテーブル内に入力するための数式です。試しに、言われたとおり入力してみました。
こうなります。確かに、AIの回答には、次の図が表示されていました。
まったく…君のような勘の悪いAIは嫌いだよ。もし、私が会社員で、部下に「月別の合計を出して」と指示をしたとして、こういう表を作ってきたら、おそらく呼び出して注意しますね。
作戦を変えましょう。大まかに作ってもらって、細部を修正しようと考えましたが、その"大まかな叩き台"を作ってくれません。なので、さっきと同じように、細かい手順を最初から指定します。まず、全日付データから"月を表す数値"を取り出します。全日付ですから、とうぜん同じ月が複数存在します。それら複数の"月を表す数値"から、重複しない"月を表す数値"リストを作ります。下のような感じです。
まずは、ここまでやってもらいます。
テーブル「Data」の「日付」列には、2025/8/1から2025/11/30までの任意の日付が入力されています。それらの日付から月の数値を取得します。取得したすべての"月の数値"から重複しない(ユニークな)"月の数値"リストを作り、セルD2から下方向へ「8」「9」「10」「11」のように入力する数式を作ってください。なお、テーブル「Data」の「日付」列に、2025/12/1以降の日付が新しく入力された場合は、D列の"月の数値"リストにも自動的に「12」が表示されるようにしてください。
=SORT(UNIQUE(MONTH(Data[日付]))) この数式をセルD2に入力してください。 「Data」テーブルの「日付」列から月の数値を抽出し、重複を除いて 昇順でリスト化します。新しい月が追加された場合も自動で反映されます。
私が試しに入力した数式は、削除してから質問しましたので、まったく私と同じ数式を提示してきたからといって、別にカンニングを疑ってはいませんよ。ええ、誰が考えても、この数式になりますよね。さて、次は各月の合計です。
セルD2には「=SORT(UNIQUE(MONTH(Data[日付])))」という数式を入力しています。テーブル「Data」から、D列に表示されている月の合計をE列で求めたいです。セルE2に入力する数式を作ってください。
'Sheet2' の A1:B101 を調べ、1 つの数式列を確認して 列 C に挿入します: 月合計 各行の日付と同じ年・月に該当するすべての「数値」を合計し、その月の合計値を算出します。 =SUM(FILTER([数値],(YEAR([日付])=YEAR([@日付]))*(MONTH([日付])=MONTH([@日付])))) 各行の "日付" 列の値と同じ年・月に該当する "数値" 列の合計を計算します: YEAR 関数で "日付" 列と現在の行の "日付" の年を取得します。 MONTH 関数で "日付" 列と現在の行の "日付" の月を取得します。 FILTER 関数で、"日付" 列の年と月が現在の行の "日付" と一致する行の "数値" 列だけを 抽出します。 SUM 関数で、抽出された "数値" 列の値を合計します。
「セルE2に入力する数式を作ってください」って言ってんのに、なんで「列 C に挿入します」って数式を作るんでしょうね。ちなみに、言われたとおりC列に数式を入力すると、こうなります。
これを"月別の合計"と言うんですか？少なくとも、日本語ではそう言いません。
テーブル「Data」の中に入力するのではなく、セルE2に入力する次の数式を作ってください。セルD2には「=SORT(UNIQUE(MONTH(Data[日付])))」という数式を入力しています。テーブル「Data」の「日付」列から、D列に表示されている"月を表す数値"ごとに「数値」列を合計して、D列の"月を表す数値"の右セルに合計値を表示するような、セルE2に入力する数式を作ってください。
=BYROW(D2#, LAMBDA(m, SUM(FILTER(Data[数値], MONTH(Data[日付])=m)))) この数式をセルE2に入力してください。D列の各月ごとに「Data」テーブルの「数値」合計が 自動で表示されます。
上記の「名前別合計」のとき、確かMAP関数使ってましたよね。今回のD列って1列ですよね。1列に対してBYROW関数使って"行単位"に抜き出したって、その行って1列ですから1セルですよね。だったら、ここもMAP関数でいいですよね。それって、あなたの感想ですよね。ま、いいや疲れた。最後に、これらをひとつの数式でやってもらいます。
今は、セルD2で「重複しない"月を表す数値"のリスト」を作り、セルE2で「"月を表す数値"ごとの合計」を計算していますが、2つの数式を使うのではなく、セルD2に1つの数式を入れることで、同じ結果を得る数式は作れますか？
これ、さっきのプロンプトと同じ文章です。さっきは、これで作ってくれました。だから、今回も作ってくれますよね。
'Sheet2' の D1:E2 を調べ、列 F、G で確認および挿入 2 数式列を次に示します。 1. 月 各行の日付から月を抽出することで、データがどの月に属しているかを簡単に把握できます。 =MONTH([@日付]) 2. 月合計 各行の日付と同じ月に該当するすべての「数値」を合計し、その月ごとの合計値を算出します。 =SUM(FILTER([数値],MONTH([日付])=MONTH([@日付])))
さすがに、少しキレそうですｗ もっと念入りに言わないと伝わらないようですね。そうですか、分かりました。
セルD2に1つの数式を入力します。作ってもらう数式は1つだけです。入力するセルはD2だけです。テーブル「Data」内に入力するのではありません。今は、セルD2で「重複しない"月を表す数値"のリスト」を作り、セルE2で「"月を表す数値"ごとの合計」を計算していますが、2つの数式を使うのではなく、セルD2に1つの数式を入れることで、同じ結果を得る数式を作って。セルD2に入力する数式を作って。
以下の数式をセルD2に入力してください。 この数式は、「Data」テーブルの「日付」列から重複しない月の数値を抽出し、その月ごとに 「数値」列の合計を右隣に表示します。 =LET( months, SORT(UNIQUE(MONTH(Data[日付]))), sums, BYROW(months, LAMBDA(m, SUM(FILTER(Data[数値], MONTH(Data[日付])=m)))), HSTACK(months, sums) ) この数式でD列に月、E列に合計が一覧表示されます。
とりあえず、できましたね。ふうぅ～現在、午前2:24です(何時から始めたかは内緒)。ちなみに、私ならこうやります。
まとめ
まず重要なことは「自分がやりたいことを的確に言語化できるか」です。これにつきます。私の、最後の悪戦苦闘を見てください。もう一度、送ったプロンプトを見せしましょうか。
テーブル「Data」の「日付」列には、2025/8/1から2025/11/30までの任意の日付が入力されています。それらの日付から月の数値を取得します。取得したすべての"月の数値"から重複しない(ユニークな)"月の数値"リストを作り、セルD2から下方向へ「8」「9」「10」「11」のように入力する数式を作ってください。なお、テーブル「Data」の「日付」列に、2025/12/1以降の日付が新しく入力された場合は、D列の"月の数値"リストにも自動的に「12」が表示されるようにしてください。
テーブル「Data」の中に入力するのではなく、セルE2に入力する次の数式を作ってください。セルD2には「=SORT(UNIQUE(MONTH(Data[日付])))」という数式を入力しています。テーブル「Data」の「日付」列から、D列に表示されている"月を表す数値"ごとに「数値」列を合計して、D列の"月を表す数値"の右セルに合計値を表示するような、セルE2に入力する数式を作ってください。
セルD2に1つの数式を入力します。作ってもらう数式は1つだけです。入力するセルはD2だけです。テーブル「Data」内に入力するのではありません。今は、セルD2で「重複しない"月を表す数値"のリスト」を作り、セルE2で「"月を表す数値"ごとの合計」を計算していますが、2つの数式を使うのではなく、セルD2に1つの数式を入れることで、同じ結果を得る数式を作って。セルD2に入力する数式を作って。
まぁ、さすがに最後のは少しふざけてますけどｗ いずれにしても、AIに数式を作成してもらうのなら、何はともあれ、どんな数式を作りたいのかを、明確に＆詳細に＆第三者が初めて聞いても理解できるくらい丁寧に記述する練習をしてください。元々ライターの私でも、けっこう苦労しました。とにかく、詳細に書くトレーニングをしてください。
さらに、もうひとつ。AIが作る数式には一貫性がありません。VBAのマクロよりも酷いです。上記の「名前別合計」で、AIはLET関数＋MAP関数＋SUMIFS関数を使った数式を提示してきました。私が「GROUPBY関数でも可能？」と聞いたら、GROUPBY関数を提示しました。そして、両者のどちらが良いか(この質問自体あいまいですが)とたずねたら、
GROUPBY関数の方が
- シンプルで可読性が高く、集計処理に特化しています。
- 集計や階層、ソート、フィルターなど追加機能も豊富です。
- 最新のExcelで標準サポートされているため、メンテナンス性も高いです。
とのことです。だったら、最初からGROUPBY関数のやり方を提示しろよ、ってのは人間的な発想ですね。相手はAIです。機械です。AIは「良いか or 悪いか」は判断しません。確率で考えていますから「できるか or できないか」です。その数式を使うことで(たとえ答は合っているとしても)、「可読性がどうか」「メンテナンス性がどうか」は考慮していません。それは、しかたありませんね。それが現在のAIです。だから、私たち人間が補完してあげなければ、せっかくのAIを活用できないです。AIを使って楽をするためにも、がんばってください。