今回の記事では
ピボットテーブルで日付をグループ化しようとすると出来ないことがある
どうしてピボットテーブルでグループ化出来る時と出来ない時があるの？
ピボットテーブルで日付をグループ化する方法がイマイチ分からない
日付をグループ化する方法をイメージしやすくする方法って無いの？
こういった疑問に答えます
✔ピボットテーブルで日付を扱うコツのテーマ
①ピボットテーブルで日付をグループ化するにはルールを押さえれば簡単です
②日付のグループ化はやりたいことを明確にすればイメージしやすくなります
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「ピボットテーブルで日付を扱うコツのテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
ピボットテーブルで日付をグループ化するにはルールを押さえれば簡単です
ピボットテーブルで日付をスムースにグループ化するにはルールを押さえることが重要です
なぜなら、ピボットグラフにはハッキリとしたルールがあるからです
ピボットテーブルの日付に関するトラブルは意外と多いものです
なぜなら、ピボットテーブルではルールに沿っていない場合エラーとして扱われるので原因が分かりにくいからです
また、元になるデータ(データベース)とピボットテーブルの関係を理解しておかないとトラブルの原因に対処できず、問題が解決出来ないという悪循環になってしまいます
今回は、そんなデータベースとピボットテーブルの関係からピボットテーブルのルールに合わせたデータの入力方法について解説していきます
前提として「データベース」と「ピボットテーブル」は最初から用意されている前提でお話を進めていきます
もし、ピボットテーブルの作り方の基礎に不安がある場合は「ピボットテーブルは情報分析に特化した表」という記事を参考にしてみて下さい
まず、これがデータベースの内容です
今回はシングルバーナーの販売を管理するための表を元にします
これが今回ピボットテーブルです
ピボットテーブルは「複数の年にまたがる」と自動で四半期というグループで日付をまとめるという機能が設定されているので、この点には注意しましょう
まずは、四半期でグループ化されている日付をバラしてみましょう
やり方は簡単です
ピボットテーブル内の日付をクリックしておきます
「ピボットテーブルツール」の「分析」タブ
「グループ」にある「グループ解除」ボタンをクリックします
日付のグループ化が解除されて1日単位でテーブルが表示されます
ただ、コレだと見にくいので「年月」はグループ化しておきます
ピボットテーブルの日付をクリックしておき、「ピボットテーブルツール」の「分析タブ」にある「グループ」の「フィールドのグループ化」ボタンをクリック
グループ化ダイアログが表示されるので「単位」項目の「月」と「年」をクリックして選択した状態で「OK」ボタンをクリック
設定の結果、年月単位で日付がグループ化出来ました
前置きが長くなってしまいましたが、改めて「データベース」と「ピボットテーブル」の関係性を整理していきましょう
ちょっと分かりづらいかもしれませんが、こんな風に「データベース」のデータをピボットテーブルのフィールドにまとめます
すべての項目(フィールド)を任意の位置に設置することで、ピボットテーブルが自動的に作られる
これが「データベース(元データ)」と「ピボットテーブル」の関係性です
また、ピボットテーブルのレイアウトや入力されている内容は直接編集することは出来ません
修正などが必要な時は必ずデータベース側の内容を変更する必要があります
ここまで来ると気づくかもしれませんが、日付がグループ化出来ない理由はデータベースの内容に原因があります
もし、ピボットテーブルで日付がグループ化出来ない場合はデータベースの内容を疑ってみましょう
では、ピボットテーブルの日付が変更出来ないケースを見ていきましょう
日付に空白が含まれている場合
まずは、注文日に空白があるパターンを見てみましょう
ピボットテーブルを更新します
この時点で空白というグループ訳をされていますが、年月でのグループ化が解除されてしまっています
もう一度、年月でのグループ化をしてみましょう
「グループ化のダイアログ」で「年月」を指定して「OK」ボタンをクリック
こんな風に日付が空白の項目は「2021年2月1日以降のどこか」といった具合に分類分けされました
こんな風に「空白」というグループでは自動的に分けてくれましたが、データベースの内容は原則未入力が無いようにしないと当然日付でのグループ分けはしてくれないので注意しましょう
日付以外の文字列が入力されている場合
次は日付の列に日付以外の文字列が入力されている場合のケースを見てみましょう
データベースの日付列に「不明」と入力しました
この状態でピボットテーブルの更新をしてみましょう
前回と同じように日付のグループ化が解除され、不明というグループが出来ています
また、「フィールドのグループ化」が押せなくなっています
グループ化は「グループ化の選択」というボタンでも出来るので試してみましょう
「フィールドのグループ化」の上の「グループ化の選択」をクリック
結果は、こんな風にグループ化出来ないメッセージが表示されるだけです
この原因は「日付と文字列をグループ化」しようとしたからです
日付と文字をまとめることは出来ませんよね
その結果グループ化するとエラーが発生するという訳です
日付に見える文字列が含まれている場合
最後に紹介するのが日付に見える文字列が含まれている場合です
これが一番厄介ですね
データベースにはこんな風にパッと見日付にしか見えない文字列が紛れる場合があります
表示の種類も日付なのにその正体は文字列というケース
よく見ると一部の「日付が左より」になっていますよね
実は、左よりは文字列で日付ななどの数字は右よりで表示されます
コレを知らないと罠にハマります
実際にピボットテーブルでデータの更新をした後に「グループの選択」ボタンを使ってグループ化してみましょう
結果はグループ化出来ません
これは日付の列に文字列を入力した時と一緒ですよね
こんな風にパッと見日付との差が無く日付がグループ化出来ない時は本当に日付かどうか疑ってみましょう
もし、文字列の日付が入力されていて数が少ない場合は表示形式を日付にしたまま正しい日付を入力し直しましょう
そうすれば、ピボットテーブルを更新することでちゃんとグループ化出来るようになります
こんな風にピボットテーブルは元にしているデータベースの入力内容に大きく影響されるので、「数字なら数字・文字列なら文字列」といった具合にルールに従って同じように入力するのが正解です
繰り返しになりますが、ピボットテーブルで日付をスムースにグループ化するにはルールを押さえることが重要です
日付のグループ化はやりたいことを明確にすればイメージしやすくなります
日付のグループ化はどんな風に日付を表示したいのかハッキリさせれば、イメージしやすくなります
なぜなら、最終的に日付をどんな風に表示させたいか決まっていないとグループ化してもよく分からなくなってしまうからです
それでは日付のグループ化について具体的に解説していきます
ただ、日付のグループ化の操作はいたって簡単なのでコツを押さえてしまえば楽勝です
今回は、四半期で表示されている状態からグループ化の設定をしてみましょう
一日単位で表示したい場合
これが一番簡単な方法です
一日単位で表示したいということはグループ化を解除するのと同じことなので「グループの解除」ボタンをクリックするだけです
「ピボットテーブルツール」の「分析」タブをクリック
「グループ」の「グループ解除」をクリック
これで「年月日」が表示されました
次は年単位でグループ化してみましょう
□年単位で表示したい場合
年単位のイメージは
こんな感じで年単位のみ表示してくれれば良い訳です
つまり、グループ化する時は「年」だけ選べばいいという訳です
グループ化の詳細は前はで解説したので割愛しますね
グループ化で「年」だけ選んだ状態にして「OK」ボタンをクリック
「2021年と2022年」の2年間だけ表示されました
これで、年売上の項目別内訳が出来るということですね
年月単位で表示したい場合
最後に年月単位の表示方法について見ていきましょう
前半にも触れているので簡単ですね
年月のイメージは次のような感じです
年度別に分かれていて日付は隠れている状態で表示したいという訳ですね
作業は簡単なので実際にやってみましょう
グループ化ダイアログで「年と月」をクリックして「OK」ボタンをクリック
年と月まで表示されたので日付は非表示になっています
こんな風に日付のグループ化は、表示したい内容を具体的にしてから設定すれば、何も難しいことはありません
繰り返しになりますが、日付のグループ化はどんな風に日付を表示したいのかハッキリさせれば、イメージしやすくなります
オススメのピボットテーブルの解説書 5選
ピボットテーブルを本で学ぶ時は次に上げるものがオススメです
本はいつでも見ることができ知識の宝庫なので読んで損なしです
スキマ時間などを活用して本で独学や教わったことを復習するなど活用の幅はたくさんあります
![VLOOKUP関数で０やエラー表示を避ける方法［IF・IFERROR関数との組み合わせ]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4327107_m.jpg?fit=1200%2C900&ssl=1&resize=40%2C40){kind=small}
コメントを残す