Excel 自分の時間を作るプログラミング技術

エクセルの単価表から新しい単価を抽出して反映させる関数

メーカーから貰った単価表の単価だけを抽出して、既存の単価表に移植させたい!

本記事では、このような悩みを解決します。

 

この記事を書いたのはこんな人

ネットショップコンサルタント

 

メーカーが値上げすると商品全部の値段を見直さないといけないので大変ですよね。

商品点数が少なければ手動でもいいけど、数百、数千種類になると手動でなんてやってられない!

そんな時に使える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エラーじゃなければ(新単価)を表示させる』

っと言う処理をさせる事が出来ます。

 

エクセル,単価表,抽出

 

あとは関数で出した結果をコピーして

 

エクセル,単価表,抽出

『値のみを貼り付け』で貼り付けます。

普通にコピペすると関数情報が張り付けられてしまい、違うデータになるので注意。

必ず『値のみを貼り付け』で貼り付けて下さい。

 

エクセルの単価表から新しい単価を抽出して反映させる関数まとめ

今回はメーカーから貰った単価表を一括で参照入れ替えさせる方法について説明しました。

手順としてはこちら⇩

  1. VLOOKUP関数でJANコード検索をする
  2. IF関数とISNA関数を組み合わせてエラー処理をする
  3. コピーしたデータを『値のみ貼り付け』でペーストする

このような手順で行えば、単価表を一括で簡単に入れ替える事が出来ます。

作業効率がめちゃめちゃ上がるので、ぜひ試してみて下さい。

 

お得な特典があるサービス一覧

注目コンテンツ

ネットショップ開業におすすめなショッピングカート10選!現役ネットショップ店長が徹底比較!

ネットショップ開きたいけどネットショップのショッピングカートって種類多すぎてどれを選べばいいのか分からない。各サービス毎に特徴を教えて!ついでにおすすめはどれ? ショッピングカートは機能や規模によって ...

続きを見る

新着記事一覧

起業 商工会議所 メリット

社長になる第一歩!開業方法まとめ

起業した経営者が商工会議所に入会するメリット・デメリット

商工会議所ってどんな所なのか知りたい 起業したばかりの経営者が商工会議所に入るメリットを知りたい 商工会議所で人脈って作れるのか知りたい 本記事では、このような悩みを解決します。   &nb ...

Excel 自分の時間を作るプログラミング技術

エクセルの単価表から新しい単価を抽出して反映させる関数

メーカーから貰った単価表の単価だけを抽出して、既存の単価表に移植させたい! 本記事では、このような悩みを解決します。     メーカーが値上げすると商品全部の値段を見直さないといけ ...

excel 半角カタカナ 全角

Excel 自分の時間を作るプログラミング技術

excelで半角カタカナの文字を全角の文字に変換する方法

半角カタカナが入ってるセルを全角に変換したい! 本記事では、このような悩みを解決します。     excelで半角カタカナの文字を全角の文字に変換する方法 画像のように半角カタカナ ...

Excel

Excelで特定の文字が含まれていたら処理を起こす関数

Excelで特定の文字が含まれていたら処理を起こす関数と使い方を知りたい 本記事では、このような疑問を解決します。     特定の文字が含まれていたら処理を起こす関数【COUNTI ...

Excel 自分の時間を作るプログラミング技術

csvファイルを開いた時にjanコードが文字化けしてる時の対処法

csvファイルが文字化けした時の対処法について知りたい 文字化けした時の対処法で直らない原因を知りたい 本記事では、このような悩みを解決します。     csvファイルが文字化け表 ...

新着記事一覧を見る

  • この記事を書いた人
  • 最新記事

井川 裕輝

元倉庫業からEC総合責任者へ昇進。副業でもやってたEC事業で独立。その後Lステップと言うツールに出会い、岡山県でLステップ構築代行者として活動を始める。

-Excel, 自分の時間を作るプログラミング技術

© 2022 ネットショップ情報メディア『WellMaga』