ecoslymeです。
毎月、会社や学校の月例会議で月次レポートや月報の提出ってありませんか？
その時に毎回、社内のシステムからデータを出力してきて、データを少し加工してから所定のフォームに頑張って転記ということをしていませんか？
私の周りでは、営業の方はほぼ全員そのようなことをしています。管理部の方もそのようなことをしている部署もあります。
簡単なピボットテーブルであれば良いのですが、例えば1つの商品に対して数量、売上、利益の3段表記で、各々実績、計画、前年のマトリクス形式の表になっていて、更に推移が分かる表になっていることが非常に多いです。（言葉では分かりづらいので、下の表をご覧下さい）
意思決定を行う人にとってはこの形式の方が見やすいというのは分かりますが、この形式を作るのに営業マンは非常に時間がかかってしまいます。ですが、簡便に行う方法はないので毎月頑張って計数の転記を行います。
私も最初はその一人でした。
Webでいくら探しても良い方法が一つも見つからず、悪戦苦闘していました。
ですが、エクセルを勉強していてようやく便利な方法を見つけたので皆さんに共有したいと思います。
毎月1時間くらいかけてデータ収集＆加工、転記を繰り返している方、その作業がなくなります！
今回ご紹介させて頂く内容は営業部の方も管理部の方必見です。
複雑な手法を使いますので丁寧に説明させて頂きます。
一度作ればずっと使い回すことができて、今後はただ1回コピペするだけで終わる作業に激変することができます。
元データと結果
ピボットテーブル① ⇨ マトリクス表① の作成
下記の売上明細データのような形式で、システムから出力された場合を今回は想定します。
下の方で説明する形式で出力されるシステムもあるかと思いますので、この表じゃないと思われた方は下の表を見ていって頂ければ、該当する出力形式の表があるかもしれませんので、一番下の表まで是非ご覧下さい。
この横並びの表がかなりやっかいですが、売上明細として出てくるデータとしてはこの形式が多いのではないでしょうか。
まずは、ピボットテーブルを作成して下さい。
ピボットテーブルの作成方法が分からない方は、以下の記事をご確認下さい。
ecoslymeです。 この記事では、ピボットテーブルの基本的な使い方を説明します。ピボットテーブル（Pivot Table）はエクセル（Excel）の中にある機能です。 エクセルで、データがたくさん[…]
以下のように、項目を移動させてフィールドを設定して下さい。
最初のポイントとしては、”年月“を列ではなく行に持ってくることです。
また、すべてのラベル項目が「○○_△△」と規則正しい名前のつけ方になっていることもポイントです。
ピボットテーブルは下記のようになっているはずです。
＜ピボットテーブル①＞
その次が肝です。
列にある”値“を行の一番下へ持ってきて下さい。
以下のような表になりましたでしょうか。
＜マトリクス表①＞
項目ラベル（年月、商品、、、）を抜いてデータの行数が9倍になりました。
（これは、1列に数量、売上、粗利の3つ/セットについて、実績、計画、前年が3セット分、つまり3×3＝９ あることを意味します）
その後、最下行の総計は不要ですので、「総計の削除」をして下さい。
ここまで出来ればまず第一段階終了です。
マトリクス表① ⇨ ピボットテーブル②準備表 の作成
このマトリクス表①の上の2行が見た目的に邪魔なので、削除します。
項目ラベルが1行目に来たと思います。
別のシート（Sheet3）に移って、数式を使用してこのシート（Sheet2）の文字と数字をコピーして下さい。
特に難しくなく単純で、Sheet3のA1セルに「 =Sheet2!A1 」と入力すればできるはずです。
（A1セルで”＝”を入力してから、マウスでSheet2のA1セルを指定した方が早いです）
その後、A〜D列までコピペして、一番下の行より下の行までその数式をコピぺして下さい。
テクニックとして、「F5」を押せばショートカットで指定したセルへジャンプしてくれます。
その指定したセルで「Ctrl + Shift + ← + ↑」をしてセルを全選択した後に、「Ctrl + d 」を押せばショートカットでコピペできます。
以下の表の表になりましたか。
なお、一番下は“0”がたくさんある状態になっているはずです。
E列とF列に列を追加していきます。
E1セル：項目①
F1セル：項目②
としましょう。
MID関数を使います。
MID関数が分からない方は以下の記事をご覧下さい。
ecoslymeです。仕事中にエクセル（Excel）を利用していて、膨大なデータを加工しなくてはいけない場面に遭遇したことのある方は多いのではないでしょうか。力技で何とかできる場面もあれば、それが無理な場合もあります。[…]
以下の数式を入力して下さい。その後、下のセルへコピペして下さい。
E2セル：「 =MID(C2,6,2) 」
F2セル：「 =MID(C2,9,2) 」
以下の表のようになったはずです。
※”_“を使っていれば、”当月”や”計画”と文字数が違っていても（例えば”前年度”といった3文字）、FIND関数を利用すれば解決します
＜ピボットテーブル②準備表＞
ここまでできれば第2段階終了です。
ピボットテーブル②準備表 ⇨ ピボットテーブル② の作成
最後は簡単です。
ピボットテーブル②準備表でピボットテーブルを作成して下さい。
出来たピボットテーブルで、年月のフィルタから“0”を抜かして、並び順を変更したら以下の通り、欲しかった表が完成します。
＜ピボットテーブル② ※完成形＞
商品別に、数量、売上、粗利の3段構成で、当月、計画、前年の比較を年月の推移で確認することが可能です。
最後に
いかがでしたでしょうか。
やることが多かったため、難しいと感じたかもしれませんが、一度やってしまえば、あとは元データを貼り付けて、ピボットテーブルを更新すれば良いだけになります。
なお、「その他」という項目を売上先や商品で分類したいと思っている方も多いのではないでしょうか。
以下のリンクに「その他」を追加する作成方法をご紹介していますので、是非こちらもご覧下さい。
ecoslymeです。 別の記事で、マトリクス形式の計数集計におすすめなピボットテーブルの活用方法をご紹介しました。 営業の計数管理に必要な集計方法をご説明しましたが、その記事では「その他[…]
営業でも管理部でも絶対に習得して欲しい技術となります。
この機会にぜひデータ加工の効率化を計って、日々の無駄な作業を無くして頂ければと思います。
