エクセル関数を9つ組み合わせて毎月のデータ集計を自動化

エクセル関数を組み合わせることで、データ集計を自動化することができます。

エクセル関数で上手に仕組み化すると、必要なデータを入力するだけで勝手に集計させることが可能です。

ここでは以下の関数を組み合わせて、月別データ集計の効率化にチャレンジしていきます。

・UNIQUE
・TRANSPOSE
・TEXT
・SUMIFS
・EDATE
・OFFSET
・COUNTA
・IF
・OR

以下で詳しく説明していきます。

1 エクセル関数を9つ組み合わせてデータ集計を自動化
2 エクセル関数でデータ集計を自動化するための5ステップ
3 エクセルを効率的に扱う方法
- 3.1 エクセルマクロVBAでできること
- 3.2 VBAの勉強方法

エクセル関数を9つ組み合わせてデータ集計を自動化

このページでは紹介するのは以下のSheet1のデータを集計します。

上記のとおり、現在Sheet1にはA1:D16までデータが入力されています。

このデータの集計結果をSheet2に出力します。

上記のデータ集計を自動で行う方法を紹介していきます。

エクセル関数でデータ集計を自動化するための5ステップ

1. 取引先を行表示｜UNIQUE
2. 日付データから年月を列に表示可能｜TRANSPOSE, UNIQUE, TEXT
3. データ計算｜SUMIFS, EDATE
4. 最終行まで範囲を自動変更｜OFFSET, COUNTA
5. 余計な表示を消す｜IF,OR

以下で詳しく説明します。

1. 取引先を行表示｜UNIQUE

=UNIQUE(Sheet1!D2:D16)

上記のUNIQUE関数を使えば、Sheet1のD2:D26の値を取得し、Sheet2のA2に出力することができます。

これによりSheet2のA2に重複削除したリストを表示可能です。

UNIQUE関数で重複除外したリストを表示できることは、以下のページで詳しく紹介しています。

エクセル関数を9つ組み合わせて毎月のデータ集計を自動化

エクセル関数を組み合わせることで、データ集計を自動化することができます。エクセル関数で上手に仕組み化すると、必要なデータを入力するだけで勝手に集計させることが可能です。ここでは以下の関数を組み合わせて、月別データ集計 […]

2. 日付データから年月を列に表示可能｜TRANSPOSE, UNIQUE, TEXT

=TRANSPOSE(UNIQUE(TEXT(Sheet1!B2:B16,”yyyy-mm”)))

まず「UNIQUE(TEXT(Sheet1!B2:B16,”yyyy-mm”))」でSheet1のB2:B16の日付を取得し、年月(yyyy-mm)に変換して重複除外します。

UNIQUE関数とTEXT関数の組み合わせは以下で紹介しています。

エクセル関数を9つ組み合わせて毎月のデータ集計を自動化

年月の重複除外したリストをTRANSPOSE関数によって、行列を入れ替えます。

行列を入れ替えたリストをSheet2のB1に出力します。

UNIQUE関数とTRANSPOSE関数の組み合わせは以下で紹介しています。

エクセル関数を9つ組み合わせて毎月のデータ集計を自動化

3. データ計算｜SUMIFS, EDATE

=SUMIFS(Sheet1!$C$2:$C$16,Sheet1!$D$2:$D$16,$A2,Sheet1!$B$2:$B$16,”>=”&B$1,Sheet1!$B$2:$B$16,”<"&EDATE(B$1,1))

上記関数を使うと、以下の3つの条件で、Sheet1のC列の値を合計することができます。

条件1. Sheet1のD列の値(取引先)で、Sheet2のセルA2と同じであること

条件2. Sheet1のB列の日付で、Sheet2のセルB1の日付以降であること

条件3. Sheet1のB列の日付で、Sheet2のセルB1に1ヶ月加算した日付より前であること

これで各取引先別(条件1)、月別(条件2,条件3)でデータを出力できます。

以下の表でさらに詳しく解説しています。

引数	本事例の値	説明
合計対象範囲	Sheet1!$C$2:$C$16	値を合計するセル範囲を指定します。ここではSheet1の$C$2:$C$16($で絶対参照として扱う)
条件範囲1	Sheet1!$D$2:$D$16	条件範囲1と条件1をペアとして、条件設定します。ここではSheet1の$D$2:$D$16($で絶対参照として扱う)の値の内、Sheet2の$A2に合致する内容を条件とします
条件1	$A2	上記のとおり
条件範囲2	Sheet1!$B$2:$B$16	条件範囲2と条件2をペアとして、条件設定します。ここではSheet1の$B$2:$B$16($で絶対参照として扱う)の値の内、Sheet2のB$1の日付以降の内容を条件とします
条件2	“>=”&B$1 すなわち2021/8/1	上記のとおり
条件範囲3	Sheet1!$B$2:$B$16	条件範囲3と条件3をペアとして、条件設定します。ここではSheet1の$B$2:$B$16($で絶対参照として扱う)の値の内、Sheet2のB$1にEDATEで1ヶ月加算した日付より前の日付を条件とします
条件3	<&EDATE(B$1,1) すなわち2021/9/1	上記のとおり

EDATE関数

上記でEDATE関数を使っていますので、EDATE関数について補足しておきます。

=EDATE(開始日, 月)

EDATE関数は、上記の引数を取ります。

引数1「開始日」に、引数2「月」の数値だけ月を加算します。

たとえば開始日が2021/8/1で月が2であれば、開始日に2ヶ月加算した日付を返すため、2021/10/1となります。

この事例では、開始日=B1、月=1なので、セルB1の日付に1ヶ月加算した日付を取得することができます。

ショートカットキーでエクセル関数を表全体に出力

エクセル関数を入力したら、Ctrl + R とCtrl + D でエクセル関数を表全体にコピペします。

これで表全体に関数で自動計算された値が出力されます。

4. 最終行まで範囲を自動変更｜OFFSET, COUNTA

ステップ3で、データ集計まで完成しました。

しかしSheet1のデータが増えていくと、Sheet2の範囲を修正しなくてはなりません。

なぜならステップ1~ステップ3では16行目までしか指定していないからです。

実際、以下のようにSheet1にデータが追加されたとしても、Sheet2の合計値は変化しません。

そこでデータが追加されても対応できる形に改善していきます。

具体的には以下のとおり、OFFSETとCOUNTAを組み合わせて、ステップ1~ステップ3の関数を書き換えます。