2018年10月07日

スライサーで月締めの在庫を集計してみた / ピボットテーブル / Excel

ピボットテーブルのスライサーを使って月締めの在庫を集計してみました。過去の月末在庫も簡単に確認できます。
Excel2013以降は日時のスライサーはタイムラインと言う専用のスライサーであってより簡単に操作することが出来る様になっています。





こんな、商品“ピュアデミグラスソース”と“だしこんぶ”の入荷と出荷の1月20日から11月18日までの日々のデータがあります。
数量は入荷を+で入力し、出荷は−で入力していますので、それぞれの商品毎に数量を合計すれば現在の在庫が分かります。
20181006_01.JPG

ピボットテーブルを作って、列に「商品名」、値に「数量」の合計を設定すると、この様なピボットテーブルが出来て、“だしこんぶ”の“93”、“ピュアデミグラスソース”の“95”が11月18日現在の在庫数ということになります。
20181006_02.JPG

ここで、8月末の在庫はいくつだったけかな?
と、調べたい時は。。。
普通に、ピボットテーブルで日付のフィルターを使って集計しようとすると、
まず、「日付」を行ラベルに設定して、日毎のだしこんぶとピュアデミグラスソースのクロス集計表を作ります。
20181006_03.JPG

そこで、行ラベルの日付フィルターから、「指定の値より前」を選択します。
20181006_04.JPG

つまり、8月31日より前にするわけです。
20181006_05.JPG

すると8月までのデータだけになり、“だしこんぶ”が“280”、“ピュアデミグラスソース”が“156”と集計されました。
20181006_06.JPG

日付を月単位でグループ化するともう少し操作しやすくなります。
ピボットテーブルの日付の中でどれかを選択しておいて、ピボットテーブルの「オプション」から「グループフィールド」を選択します。
20181006_07.JPG

「月」を選択します。
20181006_08.JPG

とこのように行ラベルが月毎にまとまりました。
20181006_09.JPG

そこで、9月〜下のチェックを外します。
20181006_10.JPG

と、8月までとなりそれぞれの8月末の在庫が表示されました。
20181006_11.JPG

で、他の月での在庫数を確認したい時は、同じように行ラベルでフィルターをかけなおしたり、月の選択を変更したりしていきます。

しかし、もう終わった月の在庫を調べなければならない時と言うのは何か特別な理由がある時とかです。なので、そんな時はより簡単に様々な月での在庫を調べられるととても便利です。そんな時に重宝するのがスライサー(や、タイムライン)です。
では、早速やってみましょう。

まず、日付は月毎にグループ化しておいた方が便利なので予めそうしておきます。
20181006_12.JPG

で後は行ラベルに日付や月を表示させる必要はないので行ラベルから“日付”を外します。
20181006_13.JPG

ピボットテーブルの「オプション」から「スライサー」を選択します。
20181006_14.JPG

「日付」にチェックを入れます。
20181006_15.JPG

と、この様なスライサーが表示されます。
ここで、1月から8月を洗濯すると、8月末の在庫が表示されます。
20181006_16.JPG

同じ様に1月〜5月にすると、5月末の在庫が表示されます。
20181006_17.JPG

この様に定型的な集計ではなく、何か調べ事がある時などはピボットテーブルのフィールドを弄るのではなくてスライサーを使うと簡単に色々な状態のフィルターをかけてみることができます。
とても便利です。






関連ページ
スライサーとタイムラインを使ってみた


posted by haku1569 at 01:29| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする
' + adSrc + '<' + '/body>'); d.close(); } loadAd(); window.setTimeout(loadAd, reloadSec * 1000); window.setTimeout(function() {adDiv.style.display = 'none'}, hideSec * 1000); }); }