今回の記事では
ピボットテーブルの日付を年単位で表示するにはどうしたら良いの？
ピボットテーブルが日付を自動で調整しちゃうから混乱してどうすれば良いか分からなくなる
ピボットテーブルで週単位表示をしたいんだけどそんなこと出来るの？
日付の設定を色々やってみたけど週単位の設定が無いから自分で調整するしか無いのかな
こういった疑問に答えます
✔ピボットテーブルの日付を月・週単位で表示する方法のテーマ
①ピボットテーブルで年単位表示をするならグループ化を上手く使うのが正解です
②週単位で日付を表示させたいなら日数を7日指定でグループ化するのが正解です
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「ピボットテーブルの日付を月・週単位で表示する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
ピボットテーブルで年単位表示をするならグループ化を上手く使うのが正解です
ピボットテーブルで日付を年単位表示するなら、グループ化の基本を押さえればすぐに解決出来ます
なぜなら、ピボットテーブルは「年月日」で日付をまとめることが得意な機能だからです
早速、ピボットテーブルを使った年単位の集計方法について解説していきます
前提として「データベースシートとピボットテーブルシート」を用意してある前提で話を進めていくのでピボットテーブルの基本がまだイマイチ分かっていない場合は「ピボットテーブルは情報分析に特化した表」という記事で解説しているのでまずはこちらの記事を読んで下さい
また、日付のグループ化の基本については「ピボットテーブルで日付を扱うには基本が大切」で詳しく解説しているので日付のグループ化って何？ と思ったら慌てずにこちらの記事を読んでおくのがオススメです
前提が長くなってしまいましたが今回のケースはこんな感じです
データベースには「2020年1月1日～2022年12月31日」までの3年間分のランダムな日付のデータが入力されています
ピボットテーブルはこんな風にシンプルな表示で作ってあります
データベースの情報が3年間分なので四半期単位で日付が自動的にグループ化されている状態です
まずは日付の詳細を確認してみましょう
詳細を見るとこんな感じですね
この四半期でグループ化するのはピボットテーブルの仕様(仕組み)なので四半期で見る必要がない場合は、日付を再度グループ化する必要があります
グループ化の作業をしていきますが、前もって「ピボットテーブルの日付の好きなセルをクリック」しておきましょう
ピボットテーブルを操作する時のコツは操作したい対象をクリックして選択しておくことにあります
では日付をグループ化して「年単位の表示」にしてみましょう
操作自体はすごく簡単です
「ピボットテーブルツール」の「ピボットテーブル分析」にある「グループのフィールドのグループ化」をクリック
「月・四半期・年」が自動的に選択(背景が青く)されているので「月と四半期」をクリックして選択を解除します
選択が解除されると「年」だけ選ばれた状態になるので「OK」ボタンをクリック
これで無事に「年単位」の表示になりました
右クリックでも同じ操作が出来るので好きな方法で設定しましょう
こんな風に年単位など日付をまとめて表示したい場合は、グループ化をするということを覚えておきましょう
繰り返しになりますが、ピボットテーブルで日付を年単位表示するなら、グループ化の基本を押さえればすぐに解決出来ます
週単位で日付を表示させたいなら日数を7日指定でグループ化するのが正解です
ピボットテーブルの日付を週単位で表示するには、グループ化で日数を7日に設定するのが一番速い方法です
なぜなら、1週間は7日というグループで出来ているからです
では具体的な手順について解説していきます
尚、日付の設定は初期設定の四半期に分けられた内容を修正する形で行います
やり方自体はすごく簡単です
「ピボットテーブルツール」の「ピボットテーブル分析タブ」にある「グループのフィールドのグループ化」をクリック
今回は「週単位で表示」させたいので「月・四半期・年」に選択をすべて外し
「日」の項目を選択します
「日」のみ選択して「日数を7」に設定したら「OK」ボタンをクリック
その結果、1週間つまり7日区切りで日付をまとめることが出来ました
但し、ここで一つポイントがあります
今の状態だと一番最初の日付が「2020年1月5日」になっていることです
この原因は1月5日以前のデータが無いためです
実務では売上が無い日は何も入力しない日が必ずあります
もちろん、売上がない日は「0」で入力するルールになっていれば問題ありませんが、そうでない場合もあります
このパターンだともしかしたら1日～4日の間に売上があるのにも関わらず入力ミスをしているかもしれません
それに1日～7日(7日間)までの一週間というルールになっていないので売上の比較をした時におかしな比較になってしまいます
この修正をするためにはグループ化の時点で日付を正しく調整してあげる必要がるのでその方法も見ておきましょう
前もってピボットテーブルの日付をクリックしてグループ化のダイアログを開いておいて下さい
開始日の「2020/1/5」を「2020/1/1」に修正します
修正が終わったら「OK」ボタンをクリック
ちゃんと「2020年1月1日～」7日単位で集計できました
但し、ここでも同じ落とし穴があります
それは抜けている日付を無視して7日間で集計してしまうことです
これでも問題ないのであればそのままでOKです
でも7日間ごとに集計したい場合は、売上がない日は「0」で入力するようにしましょう
繰り返しになりますが、ピボットテーブルの日付を表示を週単位で表示するには、グループ化で日数を7日に設定するのが一番速い方法です
よくある質問：週単位での表示方法は分かったけどどうしても売上が「￥０」の日は入力をしたくないけど、どうにか「第〇週」のような表示はさせれない？
WEEKNUM関数を使えば「第◯週」という表示をさせることは可能です
なぜなら、WEEKNUM関数は週の数を数字で表示してくれる関数だからです
今回の方法は少しむずかしいですが、どうしても全ての日付を入力することを避けたい場合は次の方法で解決出来ます
まず結論ですが次のように設定しましょう
こんな風に週の列を追加して「WEEKNUM(ウィーク・ナンバー)関数」の結果を計算式として設定すれば日付が抜けていてもちゃんと週単位でまとめてくれます
(今回は分かりやすいようにその他の項目を非表示にしています)
WEEKNUM関数は指定した日付が何週目に当たるのか表示してくれる関数です
つまり「2022/1/1は1」といった感じです
こんな風に「第◯週」を数字で表示してくれます
WEEKNUM関数の基本的な使い方は「=WEEKNUM(シリアル値,[週の基準])」です
シリアル値は日付のことですね
データベースはテーブルで作ってあるので日付の対象は[@注文日]です
ちなみに[@注文日]の意味は簡単に言うと「&A5」とほぼ同じ意味です
「週の基準」は日曜を週の初めにする場合は「1」月曜を週の初めにする場合は「2」といった具合に週の初めのを指定します
設定自体はすごく簡単ですよね
今回は日曜始まりにしたいので「1」で設定しました
これなら日付が抜けていてもちゃんとした「週単位」で集計が取れそうですよね
但し、まだ問題があるので修正する必要があります
基本が分かったところで一旦確認をしておきましょう
そもそも「WEEKNUM関数」を使う理由は未入力の日付があると7日単位でちゃんと集計出来ない問題を解決するためです
図解するとこんな感じです
こんな風にとにかく1日～7日間を1週としてまとめていたので「第◯週」では無かったということです
また日付が抜けていればどんどん期間がずれていってしまいます
そのため、データベースには日付を抜かさずに入力する必要があります
ただ日付が抜けていてもちゃんと集計するためには「第◯週」を判断できれば問題ないですよね
つまりこんな感じです
この判断をするには、「WEEKNUM関数」か必ず必要になります
ここまで整理できたところでさっきの結果をもう一度チェックしてみましょう
注目するのが2月1日の週の番号です
本来「第◯週」とカウントするのであれば、「1日に1」とリセットされないといけないですよね
ただ、「WEEKNUM関数」の場合は1週目がスタートしたら連番で週を数え続けてしまいます
じゃあ、どうしたら良いでしょうか？
答えは、毎月「1日」になったらカウントを「1にリセット」すれば良いんです
これが今回のポイントであり、難しい部分です
では改めて完成版の関数の内容を見てみましょう
「=WEEKNUM([@注文日],1)-WEEKNUM(DATE(YEAR([@注文日]),MONTH([@注文日]),1),1)+1」
これだと意味が分かりませんよね
最初の「WEEKNUM([@注文日],1)」は初めに解説した通りのシンプルな式なのでこの部分は置いておきましょう
次の「WEEKNUM(DATE(YEAR([@注文日]),MONTH([@注文日]),1),1)」の部分までが一番ややこしい部分です
この部分を日本語にしてみましょう「WEEKNUM(週の数を表示してね。DATE(指定された年数,指定された月数,1日),日曜始まりで)」という意味になります
これでもややこしいですが、ポイントは「DATE(YEAR([@注文日]),MONTH([@注文日]),1)」の部分です
DATE関数は「年月日」を数字として指定することで日付を作ってくれる関数です
つまり「年と月」は元の日付からそれぞれ拾ってきて「日にちは1日で固定」するようにしています
つまり、2022年1月5日でも2022年1月31日でも答えは「2022年1月1日」で表示してねという設定です
それを「WEEKNUM関数で週の数字に変換」する訳です
簡単に表現すると「WEEKNUM(2022/1/1,1)」としているのと同じことですこの結果は「1」になりますよね
なぜなら「2022年1月1日は第1週目だから」です
ここまでなんとなく理解できましたか？
今回の設定結果がこれです
この結果を見てピンとくるかもしれませんが、特に2月を見ると「6」になっていることが分かりますよね
つまり連番で「6週目」となったら「2月1日の番号である6を引いて」数字をリセットできる状態にしています
リセット用に引き算をした結果がこれです
「0週」と「1週」が表示されましたね
第1週は相殺されてしまうので「０」です
このままだと１ずつずれたままになってしまうので仕上げに「１」を足してあげれば良いのでこんな風になります
これで最初の設定と同じ結果になりました
分解すると何とか分かると思いますが、ちょっと複雑なので少しずつ理解してみて下さい
ちなみに「第◯週」という表示方法は「表示形式を”第”#”週”」と設定しているだけです
ピボットテーブルでも同じ表示形式を設定してあげればOKです
もし、ピボットテーブルの表示形式の設定方法が分からない場合は「ピボットテーブルで日付整理はグループ化」でも紹介しているので参考にして下さい
では最後にピボットテーブルの設定方法も確認しておきましょう
行フィルターに「注文日と週」項目を設定すると「月・注文日・週」という並びになります
仕上げに「注文日の項目を削除」しましょう
こんな風に設定が終わればちゃんと週単位でまとまっていますね
以上で終了です
ピボットテーブルは非常に便利ですが、クセやコツ・工夫が必要な時もあるので少しずつマスターして行きましょう
繰り返しになりますが、WEEKNUM関数を使えば「第◯週」という表示をさせることは可能です
オススメのピボットテーブルの解説書 5選
ピボットテーブルを本で学ぶ時は次に上げるものがオススメです
本はいつでも見ることができ知識の宝庫なので読んで損なしです
スキマ時間などを活用して本で独学や教わったことを復習するなど活用の幅はたくさんあります
コメントを残す