ecoslymeです。
別の記事で、マトリクス形式の計数集計におすすめなピボットテーブルの活用方法をご紹介しました。
営業の計数管理に必要な集計方法をご説明しましたが、その記事では「その他」の項目を作成しない場合の集計方法でした。
この記事では、更に「その他」の項目を追加する方法をご紹介します。
売上先にその他売上先を追加する方法と、商品にその他商品を追加する方法を一緒にご紹介させて頂ければと思います。
数量、売上、金額の合計が各々の項目で集計されるように工夫をしています。
このエクセルを使うことで、資料作成の時間を劇的に削減することが可能になりますので、是非使えるようになって下さい。
営業マンだった筆者としても自信を持っておすすめできる資料となりますので、資料作りに困っている人がいましたら、是非この記事のURLを教えてあげて下さい。営業マンが楽できるようにと頑張ってこの記事を書いた私も非常に光栄です。
最初の部分は、下記の記事で書いた方法と同じ部分が多いです。途中で分からなくなった方は、一度こちらの記事もご覧頂ければと思います。
ecoslymeです。 毎月、会社や学校の月例会議で月次レポートや月報の提出ってありませんか？ その時に毎回、社内のシステムからデータを出力してきて、データを少し加工してから所定のフォーム[…]
元データと結果
ピボットテーブル① ⇨ マトリクス表① の作成
下記の売上明細データのような形式で、システムから出力された場合を今回は想定します。
普段利用しているデータではない場合もあると思いますが、応用は効くと思いますので、是非最後までご一読下さい。
普段利用している形式のデータが下の表で出てくる可能性もありますので、その部分からデータ加工を開始することができます。
上記の横並びの表が、会社の売上明細として出てくるデータとしては多い形式ではないでしょうか。
最初に事前準備をします。
一番左の行か一番右の列に「キー」という項目を追加して下さい。
この記事では、一番右に「キー」（M1セル）の項目を追加します。
M2セルに以下の数式を入力して、下の行へコピペして下さい。
「 =B2&C2 」
※売上先コードや商品コードがある場合は、そのコードをキーに使って下さい
その次は、ピボットテーブルを作成して下さい。
ピボットテーブルの作成方法が分からない方は、以下の記事をご確認下さい。
ecoslymeです。 この記事では、ピボットテーブルの基本的な使い方を説明します。ピボットテーブル（Pivot Table）はエクセル（Excel）の中にある機能です。 エクセルで、データがたくさん[…]
以下のように、項目を移動させてフィールドを設定して下さい。
最初のポイントとしては、”年月“を列ではなく行に持ってくることと、”キー“を売上先と商品の間に置くことです。
また、数字に関する全てのラベル項目が「○○_△△」と規則正しい名前のつけ方になっていることもポイントです。
ピボットテーブルの範囲に空白が含まれている場合、項目をクリックすると数字の項目なのにデフォルトで“行”へ自動で移動してしまうのでご注意下さい。
年月など、ラベルで空白がある場合は、「デザイン」タブの「レポートのレイアウト」の「すべてのアイテムのラベルを繰り返す」を選択すれば、下にも同じラベルが繰り返されます。
ピボットテーブルは下記のようになっているはずです。
＜ピボットテーブル①＞
その次が肝です。
列にある”値“を行の一番下へ持ってきて下さい。
以下のような表になりましたでしょうか。
＜マトリクス表①＞
項目ラベル（年月、商品、、、）を抜いてデータの行数が9倍になりました。
（これは、1行で表されていたレコードが、（数量、売上、粗利：3項目）×（実績、計画、前年：3項目）の 3×3＝９ あることを意味します）
その後、最下行の総計は不要ですので、「総計の削除」をして下さい。
ここまで出来ればまず第一段階終了です。
マトリクス表① ⇨ マトリクス表② の作成
このマトリクス表①を少しだけ加工します。
「売上先」で「その他」としたい売上先を全て選択して下さい。この場合”YとZ“を”その他 売上先”にしたいと思います。
Ctrlボタンを押しながら選択すれば複数選択できます。
なお、売上先は一つだけクリックすればOKです。
その後、「分析」タブから「グループの選択」を選択して下さい。
B列に「売上先2」の列が挿入されますので、「グループ1」と書かれているセルで「その他 売上先」と入力して下さい。
次に、C列の「売上先」（※B列の「売上先2」ではなく）で、「主要売上先」を全て選んで、同じように「グループの選択」を選択して下さい。
その後、B列に「グループ2」が表示されますので、「主要売上先」と記載して下さい
※この記事ではXの1社のみなのでグループ化はできません。B列では”X”のまま進めます。
同様にして、「キー」の列（今はD列）もグループ化します。「キー2」がD列に挿入されます。
なおその前に、B列の「その他 売上先」は非表示にしていて下さい。「その他 売上先」では「その他」の商品を作る意味がないためです。
ここでは、キーのXBとXCを「その他」にします。
※便宜上、一番最初の表の5行目の商品を”A”から”C”に書き換えています。ご了承下さい
その後、B列のフィルタを解除して下さい。
以下のような表になればOKです。
＜マトリクス表②＞
マトリクス表② ⇨ ピボットテーブル②準備表 の作成
このマトリクス表①の上の2行が見た目的に邪魔なので、削除していない場合は削除して下さい。
項目ラベルが1行目に来たと思います。
別のシート（Sheet3）に移って、数式を使用してこのシート（Sheet2）の文字と数字をコピーして下さい。
特に難しくなく単純で、Sheet3のA1セルに「 =Sheet2!A1 」と入力すればできるはずです。
（A1セルで”＝”を入力してから、マウスでSheet2のA1セルを指定した方が早いです）
その後、A〜H列までコピペして、一番下の行より下の行までその数式をコピぺして下さい。
テクニックとして、「F5」を押せばショートカットで指定したセルへジャンプしてくれます。
その指定したセルで「Ctrl + Shift + ← + ↑」をしてセルを全選択した後に、「Ctrl + d 」を押せばショートカットでコピペできます。
以下の表の表になりましたか。
なお、一番下は“0”がたくさんある状態になっているはずです。今後追加される項目のためにこのように準備しています。
I列〜L列に列を追加していきます。
I1セル：項目①
J1セル：項目②
K1セル：項目③
L1セル：項目④
としましょう。
MID関数を使います。
MID関数が分からない方は以下の記事をご覧頂ければと思います。
ecoslymeです。仕事中にエクセル（Excel）を利用していて、膨大なデータを加工しなくてはいけない場面に遭遇したことのある方は多いのではないでしょうか。力技で何とかできる場面もあれば、それが無理な場合もあります。[…]
以下の数式を入力して下さい。その後、下のセルへコピペして下さい。
J2セル：「 =MID(G2,6,2) 」 ⇨ 「当月」、「計画」、「前年」の項目を引っ張ってきます
K2セル：「 =MID(C2,9,2) 」 ⇨ 「数量」、「売上」、「粗利」の項目を引っ張ってきます
L2セル：「 =IF(B2=”その他 売上先”,B2&”_”&J2,B2) 」 ⇨ 売上先が「その他 売上先」であれば、数量か売上か粗利かを区別させます
M2セル：「 =IF(D2=”その他”,D2&”_”&J2,D2) 」 ⇨ 商品が「その他」であれば、数量か売上か粗利かを区別させます
以下の表のようになったはずです。
※ラベル項目が”_“を使っていれば、”当月”や”計画”と文字数が違っていても（例えば”前年度”といった3文字）、FIND関数を利用すれば解決します
＜ピボットテーブル②準備表＞
ここまでできれば第2段階終了です。
ピボットテーブル②準備表 ⇨ ピボットテーブル② の作成
最後は簡単です。
ピボットテーブル②準備表でピボットテーブルを作成して下さい。
フィールドリストは以下のような並びになります。
なお、「売上先2」と「キー2」の項目は使用しません。「項目③」と「項目④」を作るために作成しました。
また、この「売上先2」と「キー2」は、今後項目が増えてきたときに再度グループ化を行わなければなりませんが、それをやりやすくするために作成しました。
出来たピボットテーブルで、年月のフィルタから“0”を抜かして、「当月、計画、前年」と「数量、売上、粗利」の並び順を変更します。
ピボットテーブルは以下のようになったでしょうか？
最後に、項目③の「その他 売上先_○○」のセルの左にある「ー」のボタンと、項目4④の「その他_○○」のセルの左にある「ー」のボタンをクリックして下さい。
すると、余分な行が消えて、その他の項目が見やすくなります。
また、「総計」は意味のない数字の合計となってしまっているため、削除して下さい。
「粗利」セルの左のほうをクリックしてから、掛け線を下のほうに引くと見やすくなります。
＜ピボットテーブル② ※完成形＞
商品別に、数量、売上、粗利の3段構成で、当月、計画、前年の比較を年月の推移で確認することが可能です。
また、不要な行に関しては「その他」項目を使うことで、スッキリ見ることができました。
いつも利用している月報や月次レポートのフォームになったのではないでしょうか。
なお、数量、売上、粗利の各々の合計に関しては別にピボットテーブルを作成して、行に「項目②」、列に「年月」と「項目①」、値に「集計」を入れれば合計の数量、売上、粗利が確認できます。
最終的に以下のようなピボットテーブル（2つ）が完成します。 ※綺麗に見せるため、一部非表示にしています
最後に
いかがでしたでしょうか。
今回の記事は難易度が高かったと思います。何をしていたのか再度振り返って復習して頂ければと思います。
難しくはありましたが今回のことを一度やってしまえば、あとは元データを貼り付けて、ピボットテーブルを更新すれば良いだけになります。（取引先や商品が増えればその部分も更新が必要です）
非常に使える資料だと思うので、営業でも管理部でも絶対に習得して欲しい技術となります。
この機会に、是非データ加工の効率化を計って日々の無駄な作業を無くして頂き、アフター５を充実させて下さい！
