体力表（時系列と市中在庫の推定）

納返品データと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」と表示される。

	A	B	C	D	E
1	101	201	301	401	=INDEX(A1:D3,2,2)
2	102	202	302	402	=MATCH(302,C1:C3)
3	103	203	303	403	=MATCH(302,A2:D2)

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

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

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

練習課題

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

　　　　　

	A	B	C	D	E
1	商品コード	店舗コード	販売冊数	商品名	店舗名
2	1	1	2
3	1	2	1
4	2	1	1
5	2	3	1
6	3	1	2

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

　　　　　

	A	B	C	D	E
10	商品コード	商品名	本体価格	刊行年月
11	1	○○○○	1600	201701
12	2	■■■■	1500	201702
13	3	▼▼▼▼	1800	201703
14	4	☆☆☆☆	1600	201704
15	5

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

　　　　　

	A	B	C	D	E
20	店舗コード	店舗名	都道府県	坪数
21	1	XXX書店	東京都	1000
22	2	YYY書店	埼玉県	300
23	3	本のZZZ	千葉県	250
24	4	ブックストアAAA	神奈川県	80
25	5

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

　　　　　

	A	B	C	D	E
1	商品コード	店舗コード	販売冊数	商品名	店舗名
2	1	1	2	=INDEX($A$11:$B$15,MATCH(A2,$A$11:$A$15),2)
3	1	2	1	=INDEX($A$11:$B$15,MATCH(A3,$A$11:$A$15),2)
4	2	1	1	=INDEX($A$11:$B$15,MATCH(A4,$A$11:$A$15),2)
5	2	3	1	=INDEX($A$11:$B$15,MATCH(A5,$A$11:$A$15),2)
6	3	1	2	=INDEX($A$11:$B$15,MATCH(A6,$A$11:$A$15),2)

結果1（商品名の表示）：D列に、以下の値が表示される。

　　　　　

	A	B	C	D	E
1	商品コード	店舗コード	販売冊数	商品名	店舗名
2	1	1	2	○○○○
3	1	2	1	○○○○
4	2	1	1	■■■■
5	2	3	1	■■■■
6	3	1	2	▼▼▼▼

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

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

　　　　　

	A	B	C	D	E
1	商品コード	店舗コード	販売冊数	商品名	店舗名
2	1	1	2	○○○○	=INDEX($A$21:$B$25,MATCH(B2,$A$21:$A$25),2)
3	1	2	1	○○○○	=INDEX($A$21:$B$25,MATCH(B3,$A$21:$A$25),2)
4	2	1	1	■■■■	=INDEX($A$21:$B$25,MATCH(B4,$A$21:$A$25),2)
5	2	3	1	■■■■	=INDEX($A$21:$B$25,MATCH(B5,$A$21:$A$25),2)
6	3	1	2	▼▼▼▼	=INDEX($A$21:$B$25,MATCH(B6,$A$21:$A$25),2)

結果2（店舗名の表示）：E列に、以下の値が表示される。

　　　　　

	A	B	C	D	E
1	商品コード	店舗コード	販売冊数	商品名	店舗名
2	1	1	2	○○○○	XXX書店
3	1	2	1	○○○○	YYY書店
4	2	1	1	■■■■	XXX書店
5	2	3	1	■■■■	本のZZZ
6	3	1	2	▼▼▼▼	XXX書店

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