POSデータ集計のためのエクセルテンプレート他

出版社の営業担当者などに向けたセミナーで説明している「体力表」「黒白表」とINDEX関数ですが、エクセルテンプレートを用意いたしました。ご活用ください。

実際の業務では、データの蓄積にはデータベースを利用し、手頃なサイズに集計したCSVファイルなどを、エクセルで自由に加工するのが現実的です。また、データベースの利用はなかなかハードルが高いので、POSによってはあらかじめ用意されている集計画面も、手間や学習コストを考えると活用すべきでしょう。

ですが、手元でデータを扱いたい、というニーズは必ずあると思います。「納返品データ」や「(書店コード入りの)営業日報」などとPOSデータとの統合は、社内でないとなかなか実現できないはずです(システム会社への発注も含む)。

こうした簡易な集計を提示することによって、出版営業におけるデータの有効性と、情報交換の前提としての標準化(ISBN・共有書店マスタ)、普及するEDI(電子商取引)と書誌情報(在庫情報含む)の開示、等々を考える一助となれば幸いです。(2017年10月30日、高島利行)

体力表(時系列と市中在庫の推定)

納返品データとPOSデータを使って売上予測や市中在庫を推定するためのグラフと集計を作成します。

※納返品は出版社が確実に使える正確なデータ。是非、活用したい。同時に、納返品を確実に把握するということは「在庫」を把握することでもある。この表では縦軸を「刷部数」とすることで「在庫」を見える化できる(実際にはヤレ本の廃棄が発生する)。
※納返品と実売を使った「市中在庫」の推定は可能。インテージの月次データ(調査店舗数が多い)を使うと、精度はかなり高い。また、「店頭在庫」がわかるサービスを利用することで、さらに正確な判断が可能になる。
※「推定総実売」とは、手に入れられるPOSデータ(標本調査)から、全体の実売を推定するという意味だが、インテージの月次データは実売の70%~85%程度をカバーしている(アイテムやジャンル、取得店舗数によって変わる)。上で「かなりの精度」としているのはそういう理由。逆に調査対象店舗数が少ないPOSデータ(例:PublineWeb)だけで市中在庫を推定する場合は、幅があることを念頭に置く。
※実際には毎回こうやって単品の市中在庫を追う必要はない(と思われる)。「市中在庫の存在とボリューム」が頭に入れば、増刷の判断から甘さが減る。

エクセルファイルのダウンロード

黒白表(実売分布と「売上ゼロ」の発見)

POSデータをクロス集計(ピボットテーブルを使用)して、店舗・アイテム毎の実売を「見える化」するとともに、「売上ゼロ(=営業によるアクションが必要)」のアイテム・店舗を発見します。

※色の塗り分けは、大型店での実際(2週間分の在庫が平積み)を念頭に、灰色に黒文字(1-4)=棚差しで回転させられる冊数、濃い灰色に白文字(5-8)=平積みを維持できるか出来ないかのライン、黒に白文字(9以上)=平積み(でないと出せない冊数)、としているが、店舗の規模や出版物のジャンルによって変わる。
※数字の入っていないセル(=白く抜けているセル)は、「売上ゼロ」の商品・店舗。売れている商品(縦軸)・売れている店舗(横軸)で集計しているので、当然、左上は真っ黒になるはず。なので、ここで発生している「売上ゼロ」はまずい。可能であれば原因を調べ対処する(=営業の重要な仕事)。
※突発的な売上(採用など)は、この集計から除いたほうが「全体の傾向」が見えてくる。
※商品ごとの総合順位(全店舗での実売合計)と順位(この範囲での実売合計)、上位店でここを見ると、「総合順位」>「順位」⇒上位店で集中的にではなく幅広い店舗で売れている、「総合順位」<「順位」⇒上位店で集中的に売れている、ことが見えてくる。この結果を受けて、営業・宣伝に違いが生まれてくる。
※縦軸の総合順位と順位では、「総合順位」>「順位」⇒幅広い品揃えで売上を確保、「総合順位」<「順位」⇒上位アイテム集中型、という店舗の傾向が見えてくる。この結果、営業のアプローチが変わってくる。

エクセルファイルのダウンロード

INDEX・MATCH関数の使用例

エクセルでデータを活用する際に不可欠のINDEX関数及びMATCH関数の簡易な使用例です。

INDEX関数とMATCH関数の基本(何が出来るか)

※INDEX関数は、=INDEX(範囲,X,Y)と書いて、「指定した範囲内のX番目(行)・Y番目(列)のセルの値を抽出」する関数(マニュアルなどには、=INDEX(配列,行番号,[列番号],[領域番号])とあるが、最後の[領域番号]は省略可なので、ここでは触れない)
※例:エクセルワークシートのA1からD3に下記の表のように数字を入力してから、E1に、=INDEX(A1:D3,2,2)と入れると「202」と表示される。
※MATCHは、=MATCH(値,範囲)と書いて、「指定した値が一致するセルが範囲の何番目か」を返す関数(マニュアルなどには、=MATCH(検査値, 検査範囲, [照合の型])とあるが、最後の[照合の型]は省略可なので、ここでは触れない)。
※例:下記の表のように数字を入れたエクセルワークシートで、E2に、=MATCH(302,C1:C3)と入れると「2」と、E3に=MATCH(302,A2:D2)と入れると「3」と表示される。

 ABCDE
1101201301401=INDEX(A1:D3,2,2)
2102202302402=MATCH(302,C1:C3)
3103203303403=MATCH(302,A2:D2)

※上記の例ではセルの指定に相対参照を使っている。実際に使う際には絶対参照で指定する。以下の練習課題では絶対参照(列番号行番号の前に$が入った参照形式、例:$A$1)を使用する。
※絶対参照を簡単に説明すると、「数式をコピペしても参照するセルの位置が変わらないから」となる。相対参照は数式をコピペすると、参照するセルの参照部分が自動的に変わる(それが便利な場合も多い)が、絶対参照では参照するセルの参照部分が変わらない。課題を終えたあと、試してみると納得できるはず。

INDEX関数とMATCH関数を組み合わせて使う

※INDEX関数とMATCH関数を組み合わせて使うと、共通のコードを使って、データに商品名や店舗名を表示できる。

練習課題

準備1(データ):まず、空白のワークシートに以下の例のように数字と文字を入れる。

     
 ABCDE
1商品コード店舗コード販売冊数商品名店舗名
2112  
3121  
4211  
5231  
6312  

準備2(商品マスター):次に、エクセルワークシートのA列10行目から、以下の例のように数字と文字を入れる。

     
 ABCDE
10商品コード商品名本体価格刊行年月 
111○○○○1600201701 
122■■■■1500201702 
133▼▼▼▼1800201703 
144☆☆☆☆1600201704 
155    

準備3(店舗マスター):続いて、エクセルワークシートのA列20行目から、以下の例のように数字と文字を入れる。

     
 ABCDE
20店舗コード店舗名都道府県坪数 
211XXX書店東京都1000 
222YYY書店埼玉県300 
233本のZZZ千葉県250 
244ブックストアAAA神奈川県80 
255    

演習1:エクセルワークシートのD列1行目から、以下の例のように数式を入れる。

     
 ABCDE
1商品コード店舗コード販売冊数商品名店舗名
2112=INDEX($A$11:$B$15,MATCH(A2,$A$11:$A$15),2) 
3121=INDEX($A$11:$B$15,MATCH(A3,$A$11:$A$15),2) 
4211=INDEX($A$11:$B$15,MATCH(A4,$A$11:$A$15),2) 
5231=INDEX($A$11:$B$15,MATCH(A5,$A$11:$A$15),2) 
6312=INDEX($A$11:$B$15,MATCH(A6,$A$11:$A$15),2) 

結果1(商品名の表示):D列に、以下の値が表示される。

     
 ABCDE
1商品コード店舗コード販売冊数商品名店舗名
2112○○○○ 
3121○○○○ 
4211■■■■ 
5231■■■■ 
6312▼▼▼▼ 

結果が画面のようにならなかった場合、数式を確認する。要注意は「A2」…「A6」と変化する部分。その他、準備1~3で入力した値(と、セルの位置)も確認する。

演習2:エクセルワークシートのE列1行目から、以下の例のように数式を入れる。

     
 ABCDE
1商品コード店舗コード販売冊数商品名店舗名
2112○○○○=INDEX($A$21:$B$25,MATCH(B2,$A$21:$A$25),2)
3121○○○○=INDEX($A$21:$B$25,MATCH(B3,$A$21:$A$25),2)
4211■■■■=INDEX($A$21:$B$25,MATCH(B4,$A$21:$A$25),2)
5231■■■■=INDEX($A$21:$B$25,MATCH(B5,$A$21:$A$25),2)
6312▼▼▼▼=INDEX($A$21:$B$25,MATCH(B6,$A$21:$A$25),2)

結果2(店舗名の表示):E列に、以下の値が表示される。

     
 ABCDE
1商品コード店舗コード販売冊数商品名店舗名
2112○○○○XXX書店
3121○○○○YYY書店
4211■■■■XXX書店
5231■■■■本のZZZ
6312▼▼▼▼XXX書店

エクセルファイル(画面とは別の、やや発展的な内容です)のダウンロード

リンク集

日本書店商業組合連合会
※昭和20年12月17日に「日本出版物小売統制組合連合会」(略称=全連)として結成され、以来、65年を超える歴史を持つ書店人の全国組織です。

一般社団法人日本出版取次協会
※出版社と小売書店の中間にあって、書籍・雑誌などの出版物を出版社から仕入れ、小売書店に卸売りする販売会社の団体(一般社団法人)です。

EDItEUR
※EDItEURは、EDIや書誌情報、出版物のユニークIDなどの標準化を手がける団体です。
※ISBNやONIX(出版情報登録センターの近刊情報が使っているフォーマット)などもEDItEURが定めた国際標準に則っています。

日本図書コード管理センター
※ISBN国際機関(本部ロンドン)との契約に基づいて日本国内におけるISBNの発行と運用管理を独占的に委任された団体です。

JPO出版情報登録センター(JPRO)
※2015年7月、JPO近刊情報センターが、JPO出版情報登録センター(JPRO)としてさらなる発展を遂げました。
 情報提供・受信についての詳細は、リンク先でご確認ください。

日本出版インフラセンター(JPO)
※出版情報および出版業界システムの基盤整備により業務の共同化・標準化等を進め、業界内の効率化を図る団体(一般社団法人)です。