本記事では、このような悩みを解決します。
この記事を書いたのはこんな人
メーカーが値上げすると商品全部の値段を見直さないといけないので大変ですよね。
商品点数が少なければ手動でもいいけど、数百、数千種類になると手動でなんてやってられない！
そんな時に使えるExcel関数の機能を紹介します。
エクセルの単価表から新しい単価を抽出して反映させる関数『VLOOKUP』の使い方
まず上の画像のようなデータがあるとします。
左側が自分が持ってる単価表、右側がメーカーから貰った単価表とだと思ってください。
実際にはExcelファイルは別々のものになりますが、Excel関数は別のファイルのデータも参照出来るのでやり方は同じです。
左のデータを元にして右側から自分の欲しいデータを見つけるには『VLOOKUP関数』と言う物を使います。
上の画像ではすでに関数を書いているのですが、VLOOKUP関数とは
新単価＝VLOOKUP(検索文字,検索範囲,検索範囲内の番号,検索条件)
っと言う式で新しい単価を見つけ出す関数です。
今回は
=vlookup(E2,M2:N5,2,false)
っと言う式を書いたのですが、これを説明すると
『M2～M5までの範囲の中からE2と一致したデータを探し出し、M行から右に2列目のデータと完全一致するデータを呼びだす』
っと言った物になります。
もっと分かりやすく言うと、
『既存の単価表のJANコードと、メーカーから送られた単価表のJANコードが完全に一致した行の新単価を表示させる』
って言う処理になります。
式を変更すると商品番号や商品名で検索する事も出来ますが、
『サイズ』などが複数あると『5L』や『6L』などの大きいサイズの単価欲しいのに、
小さいサイズの単価として拾ってしまう事があります。
なので、JANコードがある場合にはJANコードで一致させた方がズレなく処理する事が出来ます。
『VLOOKUP』を使う時の注意点
次にVLOOKUP関数を使って単価表を調べる時に起こりやすいトラブルについて紹介していきます。
VLOOKUP関数の最後は必ずfalseにする
VLOOKUP関数の一番最後は必ず『false』にして下さい。
=vlookup(E2,M2:N5,2,false)⇐ここの一番右側の部分
なぜかと言うと『false』を入れると『完全一致』になるのですが、『true』にすると『類似一致』になってしまうのです。
つまり、なんとなく似た感じのJANコードを参照するため、正しい単価を拾う事が出来なくなります。
なので、VLOOKUP関数の一番最後は必ず『false』を入れるようにして下さい。
新単価が記入されてない時にはif処理をする
よくあるトラブルとして、既存の単価表にある商品がメーカーからの新単価表に入ってない事があります。
するとどうなるかと言うと、『＃N/A』のようなエラーが表示されてしまいます。
エラーに気づかずにデータを上書きしてしまうと、過去の単価が分からなくなるので注意が必要です。
なので、さらに一手間としてif関数を使ってエラーを潰していきましょう。
方法としては、
=IF(ISNA(検索セル),一致した時に呼びだすセル,不一致の時に呼びだすセル)
このように『IF関数』の中に『ISNA関数』を入れてエラーの時には旧単価を入れる処理をします。
IF関数とは
条件一致処理関数です。条件に一致した時にはA処理、不一致の時にはB処理をさせる事が出来ます。
ISNA関数とは
NAエラーを判断する関数です。NAエラーの時には『true』、エラーじゃない時には『false』を返します。
なので今回のように
=IF(ISNA(G4),F4,G4)
っと書くと、
『G4セルがNAエラーならF4（旧単価）、NAエラーじゃなければ（新単価）を表示させる』
っと言う処理をさせる事が出来ます。
あとは関数で出した結果をコピーして
『値のみを貼り付け』で貼り付けます。
普通にコピペすると関数情報が張り付けられてしまい、違うデータになるので注意。
必ず『値のみを貼り付け』で貼り付けて下さい。
エクセルの単価表から新しい単価を抽出して反映させる関数まとめ
今回はメーカーから貰った単価表を一括で参照入れ替えさせる方法について説明しました。
手順としてはこちら⇩
- VLOOKUP関数でJANコード検索をする
- IF関数とISNA関数を組み合わせてエラー処理をする
- コピーしたデータを『値のみ貼り付け』でペーストする
このような手順で行えば、単価表を一括で簡単に入れ替える事が出来ます。
作業効率がめちゃめちゃ上がるので、ぜひ試してみて下さい。
：
