1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. GROUPBY関数を使って集計する

GROUPBY関数を使って集計する:Excel関数

• SQL(データベース言語)の「GROUP BY」と似ていますが、このページではExcelの関数、GROUPBY関数について書いています。
• Excel for Microsoft 365 で使用することができるようになったGROUPBY関数の使い方を丁寧に説明しています。
  引数の使い方も詳細に説明しています。
• 一つの列をグループ化して複数の関数で集計したり、複数の列をグループ化して複数の関数で処理することもできます。
  クロス集計をしたい場合はPIVOTBY関数を使います。

更新:2024/12/05;作成:2024/10/29

グループ化して集計などの計算をする
　グループ バイ
=GROUPBY(row_fields,values,function,
[field_headers],[total_depth],[sort_order],[filter_array],[field_relationship])

• GROUPBY関数がExcel for Microsoft 365 で使用することができるようになりました。(2024/10/29に確認しました)
  現在のチャネルのリリース ノート(Microsoft 365)によると、バージョン 2409 (ビルド 18025.20096),バージョン 2409: 9 月 23 日から使用できるようになったようです。
  

GROUPBY関数の引数　　topへ

• GROUPBY関数の引数(現時点では英語表記になっています。)
  

  引数		意味
  row_fields	必須	行のデータ
  values	必須	値
  function	必須	関数
  field_headers	省略可	ヘッダーの表示方法
  total_depth	省略可	合計の表示方法
  sort_order	省略可	並べ替え
  filter_array	省略可	フィルター
  field_relationship	省略可	リレーション

• row_fields：行のデータはユニークなデータが昇順に並べられます。
  複数の列を指定することもできます→列を入れ替えて計算する
• values：複数の列を指定することもできます→複数の関数(AVERAGEとSUM)で計算する

function：関数　　topへ

• イータ縮小ラムダ関数で指定します。明示的なLAMBDA関数で指定することもできます。
  イータ縮小ラムダ関数「SUM」は、LAMBDA(x,SUM(x))　というLAMBDA関数式の簡略版です。
  Helpには下表のように記されています。
  

  値の集計に使用される、明示的または eta の縮小ラムダ (SUM、PERCENTOF、AVERAGE、COUNT など)。 ラムダのベクトルを指定できます。 その場合、出力には複数の集計が含まれます。 ベクターの向きによって、行方向と列方向のどちらをレイアウトするかが決まります。

  
  SUBTOTAL関数のように数値で指定しません。またいくつかの関数が選択肢に増えています。
  (PERCENTOF、ARRAYTOTEXT、MODE.SNGL、LAMBDA)
  複数の計算方法を指定することもできます→複数の関数(AVERAGEとSUM)で計算する
  

  イータ縮小ラムダ関数	意味
  SUM	合計値
  PERCENTOF	構成比率
  AVERAGE	平均値
  MEDIAN	中央値
  COUNT	値の件数
  COUNTA	全ての件数
  MAX	最大値
  MIN	最小値
  PRODUCT	掛けた値
  ARRAYTOTEXT	文字を区切り文字付で結合する
  CONCAT	文字を結合する
  STDEV.S	標準偏差(標本)
  STDEV.P	標準偏差(母集団全体)
  VAR.S	分散(標本)
  VAR,P	分散(母集団全体)
  MODE.SNGL	最頻値
  LAMBDA	任意の計算方法

• ここの数式をトレースできるようにデータをリストにしておきますので、ご利用ください。
  

  	B	C	D	E	F	G
  3	番号	氏名	年齢	性別	血液型	得点
  4	1	上原嘉男	44	男	O	91
  5	2	森永彩芽	58	女	A	98
  6	3	古田恵	41	女	AB	61
  7	4	太田千恵子	34	女	B	46
  8	5	豊田啓一	38	男	O	78
  9	6	新村遥奈	29	女	A	76
  10	7	坂元彩香	38	女	AB	68
  11	8	坪井尚生	45	男	A	77
  12	9	西原舞	47	女	B	41
  13	10	中野野乃花	51	女	O	93
  14	11	岩渕佳代	44	女	A	61
  15	12	市村将文	58	男	AB	66
  16	13	芦田公平	59	男	A	90
  17	14	高見美姫	34	女	AB	81
  18	15	高山晴彦	65	男	B	76
  19	16	高島嘉子	53	女	A	85
  20	17	蛭田功一	46	男	O	60
  21	18	北奈那	42	女	B	84
  22	19	本田明莉	62	女	A	66
  23	20	東海林真依	63	女	AB	92

• データは「成績表」というテーブルに変換しています。
  計算方法に「SUM」を指定しました。
  血液型ごとに得点が合計されています。
  血液型のデータはユニークなデータが昇順に並べられています。
  SORT関数,UNIQUE関数,SUMIF関数での計算が一気にできた感じです。
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0)
  
  • 引数：field_headers：ヘッダーを表示しない、total_depth：合計を表示しない
  
• イータ縮小ラムダ関数 SUM を明示的なLAMBDA関数 LAMBDA(x,SUM(x)) で指定すると、下図のようになります。
  =GROUPBY(成績表[血液型],成績表[得点],LAMBDA(x,SUM(x)),0,0)
  
• GROUPBY関数が使えないExcel2021では、
  L3セルに　=SORT(UNIQUE(成績表[血液型]))
  M3セルに　=SUMIF(成績表[血液型],L4#,成績表[得点])
  と計算することができます。
  

field_headers：ヘッダーの表示方法　　topへ

• 引数の「field_headers：ヘッダーの表示方法」には下表の設定ができます。
  

  引数	意味
  0	フィールドにフィールド名がないので、表示しない
  1	フィールドにフィールド名が含まれているが、表示しません
  2	フィールドにフィールド名がないので、生成しない？ (実際は、Excelが勝手にヘッダーを表示します)
  3	フィールドにフィールド名が含まれているので、表示します

• 血液型ごとに得点が合計されています。データは「成績表」というテーブルに変換しています。
  
• テーブルの場合は、フィールド名は含まれていないので、0と2が設定できます。
  
  
  2　を指定した場合は 「行フィールド1」「値1」と表示されました。
  Excelが勝手に生成する？ということかも？
  
• テーブルの場合は、フィールド名は含まれていないので、1を指定すると、フィールド名は表示されません。
  ０を指定するのと同じなので1を指定する意味はないといえそうです。
  
  
  3を指定すると、データの１つ目がフィールド名とみなされて表示されてしまいます。
  テーブルの場合は 3は指定してはいけません。
  

• データ範囲にフィールド名がある場合は1と3を指定します。
  １を指定すると、ヘッダーは表示されません。
  
  
  ３を指定すると、データ範囲に含まれるフィールド名が表示されます。
  
• データ範囲にフィールド名がない場合は0と2を指定します。
  0を表示すると、フィールド名（ヘッダー）は表示されません。
  
  
  ２を指定すると、「行フィールド1」「値1」と表示されました。
  Excelが勝手に生成する？ということかも？
  
• MicrosoftのHelpに下記のような注意事項が書いてあります。
  

  Automatic は、値引数に基づいてデータにヘッダーが含まれていると想定します。 1 番目の値が text で、2 番目の値が数値の場合、データにはヘッダーがあると見なされます。 複数の行または列グループ レベルがある場合、フィールド ヘッダーが表示されます。

total_depth：合計の表示方法　　topへ

• 引数のtotal_depth：合計の表示方法　には下表の設定ができます。
  省略すると、総計が表示されます。
  

  引数	意味
  0	合計なし
  1	総計
  2	総計と小計
  -1	上部に総計
  -2	上部に総計と小計

  
  
• 0-合計なし　の場合は合計は表示されません。
  
• 1-総計　の場合は合計が表示されました。
  
• 2-総計と小計　小計と総計が表示されます。
  ここでは、2つの列でグループ化しています。1つの列では小計が計算できませんので、#VALUE! エラーとなります。
  
• -1-上部に総計　の場合は上部に合計が表示されます。
  
• -2-上部に総計と小計　の場合は　小計と総計が上部に表示されます。
  
• MicrosoftのHelpに下記のような注意事項が書いてあります。
  

  小計の場合、 フィールド には少なくとも 2 つの列が必要です。 フィールドに十分な 列がある場合、2 を超える数値がサポートされます。

sort_order：並べ替え　　topへ

• 引数は列の位置を指定します。
  昇順は正の値、降順は負の値で指定します。
• 並べ替えは列の場所を指定します
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0,1)
  ここでは 1 を指定していますので、
  血液型が昇順に並べ替えられました。
  
• この例では -2 としています。
  ２列目の得点を並べ替えますが、マイナスをつけていますので、降順に並べ替えます。
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0,-2)
  
• 複数の列を並べ替えることもできます。
  =GROUPBY(成績表[性別]:成績表[血液型],成績表[得点],SUM,0,0,{-1,2})
  性別の列が1でこの列は降順(-1:負の値)にしています。
  血液型のれつが2でこの列は昇順(2:正の値)にしています。
  

filter_array：フィルター　　topへ

• データをフィルターして計算します。
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0,,成績表[血液型]="O")
  成績表[血液型]="O"　のデータだけが表示されました。
  
• 得点が70より大きいデータだけの合計を求めてみました。
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0,,成績表[得点]>70)
  
• 40才代の人の血液型別合計得点を求めてみます。
  40以上、50未満　という条件を(成績表[年齢]>=40)*(成績表[年齢]<50)としました。
  =GROUPBY(成績表[血液型],成績表[得点],SUM,0,0,,(成績表[年齢]>=40)*(成績表[年齢]<50))
  

field_relationship：リレーション　　topへ

• field_relationship：リレーション　では0-階層（規定）　と1-テーブルが指定できます。
  
• どのような機能なのか今一つ理解できていないので、0-階層　を指定した例を示します。
  

列を入れ替えて計算する　　topへ

• 隣接した列を指定するのは何ら問題がないのですが、離れた列や順番を入れ替えたいときはほかの関数を利用します。
  CHOOSECOLS関数やHSTCK関数を利用します。
• テーブル「成績表」では性別、血液型の順番になっています。
  ここでは血液型。性別の順番に入れ替えたいと思います。
• =GROUPBY(CHOOSECOLS(成績表,{5,4}),成績表[得点],SUM,0,0)
  
• =GROUPBY(HSTACK(成績表[血液型],成績表[性別]),成績表[得点],SUM,0,0)
  

関数にPERCENTOFやMAXを使ってみた　　topへ

• 計算方法にイータ縮小ラムダ関数　PERCENTOF を指定した例です。
  =GROUPBY(成績表[血液型],成績表[得点],PERCENTOF,0,1)
  
• 明示的なLAMBDA関数 LAMBDA(x,y,PERCENTOF(x,y)) で指定した例です。
  =GROUPBY(成績表[血液型],成績表[得点],LAMBDA(x,y,PERCENTOF(x,y)),0,1)
  

• 計算方法にイータ縮小ラムダ関数　MAX　を指定した例です。
  =GROUPBY(成績表[血液型],成績表[得点],MAX,0,1)
  合計欄には最大値が表示されています。「合計」はちょっと違和感があります・・・。
  
• 明示的なLAMBDA関数　LAMBDA(x,MAX(x))　を指定した例です。
  =GROUPBY(成績表[血液型],成績表[得点],LAMBDA(x,MAX(x)),0,1)
  

複数の関数(AVERAGEとSUM)で計算する　　topへ

計算する列が1列で計算方法が異なる計算を行う(AVERAGEとSUM)

• 複数の列を異なる集計方法で計算することもできます。
  ちょっと煩わしい感じですが・・・こんなことも可能です。
  ここではテーブルの名前は「成績表W」としています。
• 計算方法にAVERAGE,SUMの2つを指定しています。
  引数の値は　成績表W[得点]　の一つの列になっています。
  つまり、得点の平均値と合計点を計算して表示しています。
  HSTACK関数を使うと複数列に表示され、VSTACK関数を使うと複数行に表示されます。
  • =GROUPBY(成績表W[血液型],
    成績表W[得点],
    HSTACK(AVERAGE,SUM))
  • =GROUPBY(成績表W[血液型],
    成績表W[得点],
    VSTACK(AVERAGE,SUM))
  

計算する列が2列で計算方法が異なる計算を行う(AVERAGEとSUM)

• こんどは、計算する列を2つにした例です。、
  CHOOSECOLS(成績表W[[年齢]:[得点]],1,4)
  と
  HSTACK(成績表W[年齢],成績表W[得点])
  のどちらでもよさそうです。
  • =GROUPBY(成績表W[血液型],
    CHOOSECOLS(成績表W[[年齢]:[得点]],1,4),
    HSTACK(AVERAGE,SUM))
  • =GROUPBY(成績表W[血液型],
    HSTACK(成績表W[年齢],成績表W[得点]),
    HSTACK(AVERAGE,SUM))
  年齢の平均値と、得点の合計値が計算されています。
  
• テーブルを範囲に変換して、ヘッダーの表示方法の3を指定すると、わかりやすい表示になります。
  範囲の指定時にフィールド名を含めて指定します。
  =GROUPBY(F3:F23,
  HSTACK(D3:D23,G3:G23),
  HSTACK(AVERAGE,SUM),3)
  

計算する列が3列で計算方法が異なる計算を行う(AVERAGEとAVERAGEとCOUNT)

• このようなこともできます。
  行のデータに２列、値に3つ指定し、計算方法に３つ指定しています。
  列のフィールド名(J2:N2)は手入力しています。
  J3セルには以下の数式を入力しています。
  =GROUPBY(HSTACK(F3:F23,E3:E23),
  HSTACK(H3:H23,G3:G23,G3:G23),
  HSTACK(AVERAGE,AVERAGE,COUNT),1,,,,1)
  

複数のシートを集計する　　topへ

• Sheet1〜Sheet3のデータを集計することもできます。
  ピボットテーブルや統合機能を使って集計することができましたが、GroupBy関数で簡単に複数シートを集計することができます。
  • ピボットテーブルで複数シートを集計する方法は　複数シートをピボットテーブルで集計する　をご覧ください。
    統合機能で複数シートを集計する方法は　統合機能を使って複数シートを集計する　をご覧ください。

1. Sheet1〜Sheet3にそれぞれ　テーブル1〜テーブル3があります。
   これらのデータをGroupBy関数で集計します。
   
2. 列のフィールド名(E2:G2)は入力しています。
   E3セルには
   =GROUPBY(CHOOSECOLS(VSTACK(テーブル1,テーブル2,テーブル3),1),
   CHOOSECOLS(VSTACK(テーブル1,テーブル2,テーブル3),{2,3}),
   SUM)
   と入力しています。
   
   • VSTACK(テーブル1,テーブル2,テーブル3)
     テーブル1〜テーブル3をVSTACK関数で縦に結合しています。
     
   • CHOOSECOLS(VSTACK(テーブル1,テーブル2,テーブル3),1)
     行のデータに結合したデータの1列目をCHOOSECOLS関数で指定しています。
     
   • CHOOSECOLS(VSTACK(テーブル1,テーブル2,テーブル3),{2,3})
     値には結合したデータの2列目と3列目を指定しています。
     
   • 計算方法は SUM としています。

スポンサードリンク

PageViewCounter

Since2006/2/27