中国輸入ビジネスで売っチャイナ

EXCELで出来るネットショップの効率化、中国の習慣やどうでもいい話を記事にしています。

Excelを使って数千数万商品CSVの中から、在庫商品を秒殺で抜き出す魔法の関数(INDEX関数とMATCH関数のコラボ)

      2015/05/19

20150516-1

大量の商品CSVのデータの中から、
商品を絞り込みたい時ってネットショップを運営していると
色々な場面であると思います。

ネットショップでお馴染みの在庫一掃セールを例に説明してみましょう。

 

在庫一層SALEをやりたいが、数千数万商品CSVの中から商品を探すのが辛すぎる

在庫一層SALEをやりたいと思った時、在庫表の商品コードを1つコピーして、
数万商品あるCSVの中からExcelの検索(Ctrl+F)機能を使って検索して。

こういう作業繰り返していませんか?

在庫のアイテム数が5アイテム10アイテムぐらいならいいですが、
200アイテムとかになってくると、やり始める前から相当モチベーション下がりますよね…

今から説明させて頂く関数を使うことによって、そんな苦行から開放されます。

 

データの準備と使い方

まず、商品CSVと在庫一掃セールに出したい在庫のデータをExcelファイル(CSV)で準備して下さい。

Sheet1に楽天の商品CSV、Sheet2に在庫表とします。
※関数がよくわからない方はSheetの名前も上記と同じ(Sheet1,Sheet2)にしてください。

20150518-1Sheet1 楽天商品CSV

20150518-2Sheet2 在庫表

 

楽天CSVのA2セルに下記の様に記載します。(コピペでOKです)

=INDEX(Sheet2!$B$2:$B$350,MATCH(B2,Sheet2!$A$2:$A$350,0))
※注意:太字の部分(350)は在庫表の行数に合わせて下さい。

20150518-3

そして、A2のセルをコピーして後はA3~一番したまでコピペするだけです。
するとこうなります。

20150518-4

#N/Aと書かれたセルは在庫が無く、在庫があるもののみ在庫数が表示されています。

後は楽天CSVの全てのセルを選択して、
エクセルメニューのデータ→並び替え(ショートカット:Alt+D+S)で
コントロールカラムを昇順で並び替えれば、在庫のあるものが上の方に集まり
在庫の無いものは下の方に集まります。(下記画像参照)
20150518-5

 

 

 

上記を並び替えると下記の様になります。

20150518-6

 

 

後は在庫の無い列を削除すれば、在庫一掃セールの該当商品だけの抜き出しに成功です。

 

関数の説明

応用して使用したい方の為に関数の説明です。

=INDEX(Sheet2!$B$2:$B$350,MATCH(B2,Sheet2!$A$2:$A$350,0))

 

まずは INDEX関数

INDEX関数は INDEX(検索範囲,行番号,列番号) と指定します。
検索範囲として表示させたい列はB列(在庫数)だったので、B2:B350 までを指定しています。
行番号には、MATCH関数が入っています。

MATCH関数 MATCH(検索値,範囲,照合の型)となっています。
検索値にSheet1のB2をしてい、つまり商品管理番号を指定。
商品管理番号とマッチするのはSheet2のA2セルからA350までの何行目にあるかをMATCH関数で検索しています。
その後ろの0は検索の型で、「0」は完全一致になります。
※検索の型の説明は割愛します。

つまり、INDEX関数の行番号をMATCH関数で探しているのです。
MATCH関数で行番号を探して、見つかった商品だけをINDEX関数の検索範囲にしていした、B列の在庫数を
表示する様にしてしていると言う事です。

 

ちなみに Sheet2!$B$2:$B$350
セルの指定に記載がある「$」ですが、これはコピペしても値が変化しないようにする為です。
EXCELは非常に賢いので、セルが選択してある関数をコピペすると値が変化していくのですが、
それを固定する為に付けています。

 

ちょっとわからないと言う方は取り敢えず、上記の様にデータを準備して関数をコピペで入れてみて
使って見てください。
習うより慣れよです。

ハセガワは今何位?


 - Excelを使ってネットショップ効率化 , , ,

アドセンスPC

アドセンスPC

  関連記事

20130413-1
CSVを使って商品一括更新で効率アップ

皆様こんにちは。長谷川です。 北朝鮮のミサイル問題のTVでの報道もめっきり少なく …

鬼タイピング
Altで加速するスタイリッシュワーキングスタイル!!

  ネットショップをフルブーストで運営するにあなたにオススメしたいショ …

20150519-1
消費税増税時対策。EXCEL関数使って売価を一括更新する際に1,980円とか値頃感のイイ数字にしたい件

売価を決める時って原価計算して売価を決めると思いますが、 こないだの消費税が8% …

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>