VLOOKUP関数で効率的な商品データベース管理

 

前回のエクセル活用の記事では主に文字列操作に関しての記事だったので、今回はデータベース的な活用方法をご紹介します。

更新を手作業で行っている場合においては、効率がヘタしたら１０００倍、１００００倍変わってくるノウハウです。

 

Chapter 1. 楽天CSVの種類

楽天で提供されている商品CSVデータは３種類あります。

1. 商品基本データ：item.csv
2. 商品のオプションデータ：select.csv
3. 商品カテゴリ属性データ：item-cat.csv

それぞれ、どういうデータかというと、

item-cat.csvは商品が入れられている（紐付いている）カテゴリ情報のデータで成り立ちます。
※カテゴリに紐付いていない商品はその他カテゴリに分類されetc.csvに格納されています。

select.csvは商品にカラーやサイズなどの設定項目がある場合や何かしらのオプションがある場合作成されるデータです。

item.csvはitem-catやselect以外のデータ、商品名や価格、管理番号、送料区分、商品説明文等の基本データ郡から成り立っています。

それぞでのCSVの中身や項目については重要ではないので説明は割愛しますが、とりあえずポイントは一つの商品取ってみても３種類のCSVデータの取り扱いが必要になる、ということです。

僕が運営していたショップは商品点数が約４万点、SKUを入れると１３万点くらいあったのですが、これだけあると商品データのメンテナンスは人力では不可能に近くなって来ます。

CSVデータは３種類あるわけですから、仮に４万点全部をメンテナンスする場合だと、途方も無いデータを修正しなければいけなくなるのはおわかりいただけるかと思います。

で、これだけの量のデータを修正しようとする時に厄介なのが「抽出作業」です。

item、select、item-catのそれぞれは「商品管理番号」以外共通するデータはありません。
※itemとitem-catは商品名は共通データ

つまり、select.csvの中で「共通説明文大が○○の物だけ修正したい」とか、「商品名に☓☓とあるものだけ修正したい」といった場合、select.csvはその情報を保持していないので、修正できないということです。

ではどうするか？

こういう場合、select.csvに「共通説明文大」や「商品名」等の情報を持たせてしまえばよいのです。

 

Chapter 2. item、select、item-catを紐付ける「VLOOKUP」関数

３種類のCSVで共通して持っているユニークなデータが「商品管理番号（商品URL）」です。

例えば、item、selectがそれぞれ数のようなデータ構成になっている場合

select.csvの商品管理番号を検索対象としてitem.csvから一致する管理番号の商品名を持ってこれれば、「select.csvにitem.csvのデータを持たせる」事ができないでしょうか。

で、こういう場合に使うのが「VLOOKUP関数」です。

まずはざっくり画像で順序を説明します。

空いているセルにでも「=vlookup」と入力します。

 

するとヒントでVLOOKUPと出てくるのでクリック。

検索値の指定を商品管理番号の列、この場合だとB列を指定（クリック）します。

 

値指定の区切り文字「,（カンマ）」を入力、次に検索する範囲を指定します。

item.csvのB列からC列を指定。管理番号と商品名の列ですね。

 

区切り文字の「,（カンマ）」を打って、持ってきたいデータの列番号、「２」を入力。

 

区切り文字「,（カンマ）」を打って、完全一致をクリックして完了です。

 

あとはコピーで最終行までコピーすれば完了。
※または画像のように黒ちょぼをダブルクリックで最終行まで自動計算されます。

 

 

あとはフィルター機能などを使って抽出したい条件を加えていく等して料理してください。

 

VLOOKUP関数はデータ量が多くなればなるほど有効です。

例では30行そこそこですが、楽天商品CSVの１データ最高行の５万行も、この関数を使えば数秒で完了します。

正直この関数を知っているかいないかで、作業の効率性は天文学的数字倍 変わってくると思いますので是非マスターしてください。

以降はおまけ的な使える関数です。

 

Chapter 3. 条件に一致するセルの個数を表示する「COUNTIF」関数

 

エクセルには重複したデータの削除を自動でやってくれるボタンがついていますが、削除まではいらないといった場合も少なからず出てきます。

重複しているかどうかだけをチェックしたい。

そういった場合「COUNTIF関数」が役に立ちます。

=COUNTIF（範囲,検索条件）

範囲を管理番号の列（B列）、検索条件をB2にして自動計算させると、重複している個数を返してくれる関数です。

２個重複していたら「２」、５個重複していたら「５」という具合に表示されます。

あとはフィルタで煮るなり焼くなりです。

 

Chapter 4. 条件によって表示を分岐させる「IF」関数

COUNTIF関数と組み合わせて使うことが多かったIF関数です。

もし○○ならX、△△ならY、というような条件分岐をさせる関数です。

=IF(=COUNTIF(B:B,B2)>1,”重複”,””)

この関数を使うと、重複している場合は、「重複」とセルに入力され、重複していない場合は空白のままになります。

「重複していて、かつ ○○」といったような抽出の条件を複数も受ける場合に、とりあえず重複しているかどうかだけチェックしたい時に使ってました。

 

Chapter 5. まとめ

ある日、掲載商品が１万点ほど誤ってデータ消去されてしまった時の事。

なにやら外注さんが社長の指示を誤って認識してしまい、item.csvで一括消去してしまったようでした。

１万点の商品を手作業で復旧させようとしたら、数ヶ月は余裕でかかる量です。

社長は顔面蒼白。そりゃそうです。

「難しいことは判っているが、何とかして欲しい」と懇願され、僕は「良いですよ～」と快諾。

５時間ほどで復旧完了させた事がありました。

「終わった・・・orz」と絶望に打ちひしがれていた社長が、あまりにも簡単に解決してしまったのでポカーンとなり「なんだったんだアレは・・・」と狐につままれた様になっていました。

当時の従業員の間では「なんだったんだアレは事件」と読んでいた事件です。

 

データベースの中からある条件のもとで該当するものだけをリストアップする。

VLOOKUP関数は是非抑えておきたい関数です。

 

ちなみに３種類のCSVデータは定期的にバックアップしておくことをおすすめします。

「なんだったんだアレは事件」の様にいつ何時データが失われてしまうかわかりませんからね。

直ぐに復旧できる様にバックアップの癖はつけておいたほうが良いですね。

 

エクセル活用術についてお問い合わせやご質問・ご相談等お気軽にどうぞ。
下記のコメント欄または、メールフォームから個別にご相談等も承ります。

問い合わせフォームから不明点を問い合わせる