※本ページはプロモーションが含まれています

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. コピーしたデータを『値のみ貼り付け』でペーストする

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

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

 

\お仕事の依頼やご相談はこちらから/

LINE公式に登録する

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

注目コンテンツ

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

ショッピングカートは機能や規模によって使う物を決めていきますが、私のおすすめはです。   ネットショップを開きたいけど種類が多くて選び方が分からない。 ネットショップのショッピングカートを比 ...

続きを見る

新着記事一覧

ネットショップノウハウ一覧

偽amazonから届く詐欺メールの暴き方

2023/9/22

本記事では、このような疑問を解決できるかもしれない手段を発見したので、共有しています。     Amazonを装った詐欺メールって多いですよね? しかも、年々手口が巧妙になってきて ...

インスタ 100万再生

Instagramの運用方法 WEBマーケティングのコツ ファンを作るSNS運用とは

Instagramでバズる事例紹介!100万再生だした動画の構成を徹底解説

2023/8/28

本記事では、このような疑問にお答えします。 Instagramのノウハウってたくさんあるけど、どれもハウツー動画を作る事が前提の物が多いですよね。 ハウツーが出せなかったり、動画編集にそこまで時間がか ...

MDS株式会社

Instagramの運用方法 ファンを作るSNS運用とは

怪しい営業DMが送られてくるインスタ代行サービスのMDSを半年間試してみた結果

2023/8/17

本記事では、このような悩みを解決します。 インスタグラムを運用してると怪しい営業DMが頻繁に届きますよね。 うちにも数多くの営業DMが来てて、普段なら「怪しいなぁ~」っと思いながらスルーするのですが、 ...

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

子供の面倒を見ながらでも出来る在宅ワークとは

2023/6/6

本記事では、このような悩みを解決します。 子育て中って忙しいですよね。 でも、そんな忙しい中でも将来の子供や家庭のために収入アップは絶対に必要!   実際に私も子供が小さい時には『収入を増や ...

Excel 分割

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

【Excelの小技】セル内の情報を自動で一括分割してくれるショートカット『フラッシュフィル』の使い方

2023/4/14

本記事ではこのような疑問を解決します。 Excelデータを一定の条件や文字が入ってる所で分割出来たら便利ですよね。 データを扱ってる職業だとよくある悩みだと思うので、今回はそんな時に便利なショートカッ ...

新着記事一覧を見る

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

井川 裕輝

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

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