エクセル期間集計の仕方: 週間、月間、年間
エクセル期間集計のやり方です。各種データの週間、月間または年間での期間集計はデータ分析でよく必要になります。この記事では作業列とSUMIFS関数を使ったやり方をステップバイステップでエクセル上で説明しています。
（動画時間：7:42）
エクセルの期間集計の第一歩は最終結果画面を決めることから
こんにちは、リーンシグマ、ブラックベルトのマイク根上です。業務改善コンサルをしています。
今日の話題はビューアさんのリクエストからです。
「エクセルでデータを週間、月間、四半期又は年間での期間集計のやり方を知りたいです。コツは何ですか？」
どんな分析でもそうなんですが、最初にやる事はこの分析結果を見る人がこの分析を見て何をやりたいのか、目的は何かを知る事です。依頼した人に直接それを聞いて下さい。そこから最終結果の表の構成を考え、最初に決めるのです。⇒「データ分析の基本は？やっぱりPDCAサイクルなんです。」
ここでは過去6週間の販売チャネル毎の週間売上での期間集計をやってみましょう。
最終画面を考えます。I列に販売チャネル、J列からO列に週間売上を出します。今回の集計では以前の記事でやったSUMIFS関数をまた使います。⇒「エクセル関数の覚え方と合計を求めるエクセル関数 (SUM、SUMIF、SUMIFS関数)」
2行目がヘッダーとなり、それをSUMIFS関数の条件式に使いますのでヘッダーの書式は重要です。セルO2（上図参照）に先週を表すヘッダーを入れたいです。どうやりましょうか？
グーグルで「エクセル 週番号」で検索するとWEEKNUM関数があるのが分かります。エクセルのセル内で「=WEEKNUM」とタイプすると「日付がその年の第何週目に当たるかを返します。」と言ってます。
最初のカッコを入れると引数は「シリアル値」と出ます。ここでは「シリアル値」は日付だと考えて下さい。今日は10月12日です。先週ですから７日前で、10月5日です。この書式は数値ではなく文字列なのでダブルコーテーションで囲んで下さい。
すると40と出ました。つまり先週は今年に入って40週目なのです。その前の5週間をとりあえず手入力しておきます。
作業列で条件となる対象期間を出力する
今度は元データを見ましょう。A列に販売日、B列に販売チャネル、D列に販売金額が並んでいます。E列を作業列にしてここにさっきのWEEKNUM関数で各販売日を週番号に変換します。「=WEEKNUM(」で販売日を選びカッコを閉じてこのセルの右下角をダブルクリックして下までコピーします。
これでSUMIFS関数を使って週間売上の期間集計が出来そうですが、実は一つ問題が有ります。元データが増えて複数年になった時、全ての年の同じ週番号の売上を合計してしまうのです。ですのでこの作業列に年の区別も入れた方が良いのです。
以前の記事でやったTEXT関数を使っても良いですが、年だけであればもっと簡単にYEAR関数で出来ます。⇒「TEXT関数：日付や数値の表示を読み易くする関数【データ分析エクセルスキル】」
WEEKNUM関数の前に「YEAR(」と入れて「シリアル値」として同じ販売日を選びカッコを閉じて年が出ます。その後に「&」マークで週番号と繋げられます。ですが連続した数字で読み難いですね。そこで間に「“ W”&」を入れると下記の結果になります。
ここの書式は最終結果の表にそのまま反映されますので、それも考えて決めて下さい。これを下までコピーして元データの準備が完了です。最終結果の表に戻りましょう。
最終結果の縦軸と横軸を完成させる
I列に全販売チャネルを入れる必要があります。どうしましょうか？元データの販売チャネルの列を一時的にどこかにコピペします。ここで「データ」から「重複の削除」を選び、「OK」をクリックするとあっという間に全販売チャネルを抽出しました。
ここでは二つだけですね。これらを結果の表にコピペします。「重複の削除」の機能は便利で僕はよく使いますが、あまり知られていないのではないでしょうか？ぜひ使って下さい。
作業列で年を入れたのでヘッダーの書式を変更する必要があります。ここで手入力で年を加えても良いのですが、それだと毎週手入力で変更しなくちゃいけません。数式で自動的に変更される様にしたいですね。お見せしましょう。
先ほどの作業列の数式をコピペします。今は空白を参照しています。セル参照部分に常に今日の日付となる「TODAY() ［括弧、括弧閉じる］」と入れます（下記図参照）。WEEKNUMの引数にも同じように入れてエンターを押すと、良い感じです。
これを全てのヘッダーにコピーすると同じ結果が出ます。ここは今日の日付に7日引いて先週になり、次は14日を引いて先々週となり同じ様に21、28、35、42と引いていきます。ちゃんと連続した週番号に成りました。
SUMIFS関数を使って期間集計を完成させる
「合計」の行は下の二つをただ足しましょう（上図セルJ3参照）。これは簡単ですね。次が最後の数式です。「販売チャネル」でSUMIFS関数を完成させます。「=SUMIFS(」と入れて【合計対象範囲】には販売金額（Ｄ列）を選びます。このセルを後で右にも下にもコピーするので前回やった絶対参照、相対参照を考えて下さい。⇒「エクセルのセルの絶対参照／相対参照とオートフィル機能【データ分析エクセルスキル】」
この参照先は変えたくないので僕はウィンドウズですので「F4」キーを押して$マークを入れて絶対参照にします。（Macでは「Command + T」）カンマを入れて、次の【条件範囲1】では先ほど作った週番号のE列を選択して、これもコピーしても参照先を固定したいので絶対参照にします。
カンマを入れて、【条件1】は上のヘッダーです。ここで注意が必要です。右にコピーした時は参照先は右にずれて欲しいけど、下にコピーした時は固定したいです。ですので行番号だけに$マークを入れます。
次の【条件範囲2】はB列の販売チャネルです。これも固定したいので$マークを入れて、カンマを入れます。【条件2】は隣の販売チャネルです。これはさっきとは真逆で右にコピーした時は固定したいけど、下にコピーした時はずれて欲しいので、列番号の前に$マークを入れカッコを閉じて完成です。
下にコピーしても右にコピーしてもちゃんと計算されました。最後にちょっと見栄えを良くして完成です。
これがちゃんと出来たら月間、四半期、年間の期間集計も同じ様に出来ます。作業列のTEXT関数を変更してそれぞれの期間結果が出る様にして、結果表のヘッダーもそれに基づいて変えれば良いのです。
＜＜データ分析の基本とエクセルスキルシリーズ＞＞
