エクセルバージョン
マイクロソフト365
今回は株やFX、家計簿などの月別収支など、月別でなにか算出したいという時の方法をご紹介していきたいと思います(^O^)
それでは行ってみましょう！
Menu
今回の完成後
今回はA列に日付、B列に収支を記入していった場合のサンプルをご紹介します。
【今回使用するデータ】
【完成後】
完成後は月別の合計した金額が取得できます(*^^)v
今回の作成方法3つ
・ピボットテーブルで作成
・SUMPRODUCT関数で作成
・配列数式で作成
作成方法は色々ありますが、今回はこの3つの作成方法を解説していきたいと思います(*ﾟ▽ﾟ)ﾉ
ピボットテーブルで作成
まずは1番簡単で手っ取り早いピボットテーブルで月別収支の作成をご紹介します。
こちらの方法はサクッと完成します！
➀挿入→ピボットテーブルをクリックします。
➁下記①～③を入力します。
➀テーブル（データ）の範囲をドラッグで指定します。
➁既存ワークシートにチェック。
➂場所は配置するセルを選択します。
よろしければOKボタンを押します。
➂日付と収支を、下の行と値にドラッグします。
➃行と値が下記のような配置になればOKです。
➄サクッと完成します＼(^^)／
※データを変更したら更新、データの範囲の変更はデータベースの変更で更新します。
日付をシリアル値にして書式変更
ピボットテーブルの形式ではダメという場合は、ここから関数を使用した解説をしていきます(*ﾟ▽ﾟ)ﾉ
SUMPRODUCT関数と配列数式で作成する場合D列の月を最終的にオートフィルさせて作成したほうが後々楽チンなので、まずは日付(シリアル値)に変更した後に書式を変更していきます。
月別収支の月をシリアル値に変更
下記D2セルに1月と入力して作成すると文字列の日付になってしまうので、シリアル値の1月に変更していきます。
➀D2セルに1/1と入力します。
➁D2セルをオートフィルして連続データ（月単位）を選択します。
➂1月1日～5月1日に変更されます。
➃日付の書式を変更していきます。赤枠の矢印をクリックします。
➄表示形式 → ユーザー設定 → 種類を選択。
m”月” と入力します。
➅すると下記のようなシリアル値の月別表示ができあがります。
これでこの後作成する月別収支表の日付のD列の日付をオートフィルさせれば即完成します(★‿★)
これで下準備が出来上がったので次から早速関数を使用して作成していきます！
SUMPRODUCT関数で作成
SUMPRODUCT数式
=SUMPRODUCT((MONTH(A$2:A$100)=MONTH(D2))*B$2:B$100)
E2セルに上記のSUMPRODUCT関数を入力します。
E2セルをE6セルの5月までオートフィルして完成です＼(^^)／
【解説】
1月の月別収支
➀MONTH($A$2:$A$100)=MONTH(D2)
ここでMONTH関数を上手に使用します。
A列の日付の月の数字が、D2セルの日付の月の数字と等しかったらという内容です。
上記の数式は比較演算子を使用してるので下記のような結果なります。
等しかったらの場合 → TRUEで → 1
等しくなかったらの場合 → FALSEで → 0
1月の月別収支の結果として下記のようにTRUEの時だけ、TRUE(1)×収支をして足すので9,000になります。
あとはE2セルを5月までオートフィルさせれば同じように各月の月別収支はTRUEの時だけ計算されます。
ちなみに2月は下記のC列のような論理値が取得されてるのでE3セルは52,000の結果になります。
※A列の日付に空白があると正しい値が取得できないので空白のないデータを作成してください。
SUMPRODUCT関数の詳細はこちら
MONTH関数の詳細はこちら
配列数式で作成
配列数式 ← かっこいいですね～！名前が…でも手順が面倒なんですっ↷↷↷
特に配列数式を使用しなくても前回解説したSUMPRODUCT関数で取得できるので、参考程度に解説します。
配列数式を使用する場合もSUMPRODUCT関数と同様の形式の収支表をまず作成してください。
数式
=SUM(IF(MONTH($A$2:$A$100)=MONTH(D2),$B$2:$B$100))
※バージョンがマイクロソフト365でない場合、もしくは未対応のバージョンの場合は入力後、Ctrl+Shift+Enterを押下します。
下記のように先頭と末尾に波括弧が付きます。
{=SUM(IF(MONTH($A$2:$A$100)=MONTH(D2),$B$2:$B$100))}
E2セルをE6セルの5月までオートフィルして完成です(*^^)v
【解説】
例えば1月の月別収支の場合
➀MONTH関数でA列とD2セルの月の整数部分の値が一致するかをIF関数を使用して判定します。
➁一致した(TRUE)時のみ、B列の収支をSUM関数で合計します。
※A列の日付に空白があると正しい値が取得できないので空白のないデータを作成してください。
まとめ
さくっと作成したい場合はピボットテーブル、普通の形式で作成したい場合は関数で作成したりして、その時に応じて使い分けて使用してみてください！
以上です。
関連記事
SUMPRODUCTの概要
日付の月の数字のみを取得（MONTH関数）
株やFXの最大ドローダウンを取得する
