シートAとシートBの共通点は、「商品コード」とします。その価格が他の列に入っています。シートAを中心(正確)として、価格が合っている場合と間違っている場合を確認する関数を教えて下さい。
VLOOKUPは得意で頻繁に使用しますが、この場合は、無理なように思えます。一列 列挿入して、何かの関数をそこに入れるのではと思うのですが……。INDEXとかMATCHとか ネットで調べてみたのですが、この場合はこういった関数を使用するのでしょうか?ちなみに、INDEX関数やMATCH関数は使用した事がありません。宜しくお願いします。ヴァージョンは、2010です。
No.5ベストアンサー
- 回答日時:
Vlookupの検索の型は、私の間違いです。
完全一致は[FALSE]ですね。申し訳ありません。完全一致で検索をします。No-4の画像のような、近似値がないデータだったから[TRUE]でも抽出できただけです。[FALSE]が正解です。
並べ替えよりも、[FALSE]で完全一致にする方が重要です。
FALSEなら並び替えていないデータからでも、抽出できます。シートAの使っていない列にVlookup関数を入れて下までペーストすれば、完全一致のデータを抽出できます。
並べ替えているデータ [TRUE]近似値を抽出
並べ替えているデータ [FALSE]完全一致
並べ替えていないデータ[TRUE]#N/Aになることもありますが、違う値を取り出すこともあります。
違う値でもデータは抽出できたので、気付かない危険があります。
【並べ替えていないデータで[TRUE]は絶対NGです。】
並べ替えていないデータで、[FALSE]にして、同様の手順で抽出してみてください。
結果があっているのかの確認は、目視ではなく、
関数を使って正規の価格の列と比較してください。
正規の列が[T列] VLOOKUPの関数を入れた列が[W列]なら
=IF(T1=W1,1,0)
この式をデータのある【開始行】から【最終行】までペーストします。
そしてその隣の列に
=COUNTIF(W:W,1)と入れます。
その結果が、(【最終行】-【開始行】+1)になっていたらOKです。
開始行5 最終行2000なら [1996]です。
違う値になっていた場合は、半角 & 全角 などの違いが原因かもしれません。
原因がわかるまで元の値のままにしておきます。
No.4
- 回答日時:
VLOOKUP関数だけで、不一致データを取り出す方法を解説します。
※添付画像では、[商品コード]ではなく[商品名]にしてあります。コードの羅列だと見づらいかなと思ったからです。
それぞれの商品は、[テレビ○○円、パソコン▽▽円]のように決まっています。
正規の価格があるシートを【シートA】、価格修正をするシートを【シートB】とします。
シートAの【商品コード】のある列を全部コピーします。
ワークシートを追加します。追加したシートをシートCとします。シートCのA1セルを選択してペーストします。
次にシートAの【商品価格】がある列を全部コピーします。
シートCのB1セルを選択してペーストします。
【重要】
シートCに貼り付けた【データ全部】を、【A列を基準にして昇順】で並べ替えます。これをしないとVLOOKUP関数で正確な価格を取り出せません。
使用していない列を、データを取り出す列として【一時的に】使います。VLOOKUP関数でデータを呼び出したら価格のある列に貼り付けてDELETEで消してしまいます。
※列の挿入はしないでください。既にシートに入力してある関数の参照が変わってしまうかもしれませんので…。
図ではG1セルに入れました。
=VLOOKUP(E1,シートC!A:B,1,TRUE)
検索値は、E1セルの値を参照します。商品コードのある列がE列で、データが一行目から始まっているからです。
例えば、シートBの、商品コードの列がRで、5行目から始まっているとしたら、
=VLOOKUP(R5,シートC!A:B,1,TRUE)
にします。シートCも、挿入したシートの名前と同じにしてください。
すると、商品コードが出てくるはずです。
その行の商品コードの値と同じになっているはずです。
入力した関数を、下までコピー&ペーストします。
【それぞれの商品コードと一致しているか確認してください】
=VLOOKUP(E1,シートC!A:B,1,TRUE)
=VLOOKUP(E1,シートC!A:B,2,TRUE)
列番号を2に変更します。
今度は正規価格が表示されたはずです。正規価格表をコピーしたシートCから値を取り出しているわけです。
シートCの商品コードに重複値があっても、価格は一致しているので問題ありません。
VLOOKUP関数を入れたセルを全部選択して、【商品価格】がある列に【形式を選択して貼り付け(値)】でペーストします。
ペーストしたらVLOOKUP関数を入れた方のセルは、DELETEで消します。
シートCはもう使いませんので、削除してもかまいません。
No.3
- 回答日時:
シートBに記載されている「商品名(商品コード)」と紐づけられた「価格(単価)」が
シートAに記載されている「商品名(商品コード)」に紐づけられた「価格(単価)」と
一致するかを関数を使って示したい。
…で、いいのかな。
シートA、シートBともに、A列に「商品名(商品コード)」、B列に「価格(単価)」が入力されていたとすると、
シートBのC列などシートBのB列と容易に比較できる場所に、VLOOKUP関数を使ってシートAの「価格(単価)」を引っ張ってきたらよい。
=VLOOKUP(A1,シートA!A$1:B$600,2)
これで引っ張ってくることができる。(ついでに600行ほど下へコピー)
その後C列にB列の値と比較して等しくなければセルに色でも付ける書式設定を行えば見やすくなるだろう。
(自分ならC列をコピーしてB列に「値として貼り付け」を問答無用でやってしまうが)
そんなわけでシートBを使いやすいように整形(修正)しよう。たかが600行。
は な し は そ れ か ら だ 。
(表の作りが悪いのならそれを直してから考えるようにしよう…ということ。基本です)
確かに、元のデータを修正する事から始めないと作業のしようがないのは事実ですね。関数としてはVLOOKUPを使用するしかないようですね。有難うございます。
No.2
- 回答日時:
シートA は商品台帳のようなもので、商品コードと価格が入っている場合。
シートB のA列に商品Tの商品コードを入力したとき、B列に商品Tの価格を自動表示させるように作るのはダメですか。
シートB のB列にVLOOKUP関数を作っておけば良いわけですが、こういう使い方とはまた違うものを質問されているのでしょうか。
ご質問文の内容だけでは目的がよくわかりません。
「価格が合っている場合と間違っている場合を確認」して、その結果をどう処理したいのか、によって違ってくると思います。
No.1
- 回答日時:
シートAのA列が商品コード、B列が正確な価格。
シートBのA列が商品コード、B列が正しいか間違っているかの価格。
シートAのC列に、VlookUpで、シートBの価格を持ってきて比較するのはダメですか。
あるいは、間違っている箇所を探すというのなら、
シートBのC列に、シートAの正確な価格を持ってくるとか。
どのように確認されようとしていますか。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Excel(エクセル) Excel関数 情報引用する方法 4 2022/07/31 20:59
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- その他(パソコン・スマホ・電化製品) エクセル初心者です。 仕事でエクセルを使っていて、普段は素人でもできる簡単な関数を使ったことがある程 1 2022/05/25 11:17
- Excel(エクセル) Excelで、別シートの表のステータスに伴った動的な自動転記をしたいです。 2 2023/06/14 15:56
- Excel(エクセル) エクセルの関数について 5 2023/01/26 15:26
- Excel(エクセル) 【Excel関数】値が合致するセルの隣のセルを表示させたい 8 2022/10/12 17:44
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) 関数を教えてください。 2 2023/08/01 10:59
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
高校1年生です。バイトしたいの...
-
なぜ?掃除機ダイソンは日本で...
-
日本はG7を辞退すべきでは? 20...
-
タイミーで未経験者歓迎とも、...
-
後輩や年下が先に出世するのっ...
-
追い出し部屋への異動勧告を受...
-
私は事務に向いてないのでしょ...
-
学生バイトの人って、自分の働...
-
毎年行われる春の園遊会天皇陛...
-
前澤友作が始めた MZDAOという ...
-
10年後の日本、現在とどのあた...
-
今でも日本の東北地方などから...
-
異性の相手(目上で恋仲ではあり...
-
株式の暴落する日は、強風の日...
-
東京社会福祉協議会の生活福祉...
-
日本に来る外国人から入国税と...
-
役職が複数ある場合の紹介の仕...
-
高校生です。高級車乗って豪邸...
-
2025年から定年が65歳に義務付...
-
ドトールコーヒーだけが他のコ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
今、解雇を迫られています、、
-
電動ヘリコプターをなぜ執拗に...
-
会社への診断書の提出について
-
役職が複数ある場合の紹介の仕...
-
自分は今内定の会社入ったら社...
-
日本に来る外国人から入国税と...
-
仕事
-
歴史は繰り返す? こんなニュー...
-
40代半ば男です。 腰を据えて同...
-
職場で自分と同じ苗字の女性に...
-
今、色々な物の「出張買取」と...
-
仕事
-
株式の暴落する日は、強風の日...
-
企業で電話対応されている方に質問
-
仕事で使う体力はどうすれば、...
-
会社の弔事対応業務の流れについて
-
買わなきゃ損だの、今だけ!だ...
-
50歳で新しい部署に
-
地元の零細物流会社で働き始め...
-
日本はG7を辞退すべきでは? 20...
おすすめ情報
説明が悪くてすみません。
シートAには、商品コードがかなり多く600行くらいあります。商品名やサイズや色がらや受注年月日も別の列に入っています。抜けているセルもあります。
シートBは、もっと整理がされていなくて、年月日も書式が違っていたり、他の列に別の商品コードが入っていたりと大変見づらいです。
さらに、商品コードが違っていて同じ価格という商品が多すぎます。よって、VlookUpで価格を入力するのは無理そうです。
IF関数&AND関数とかで何とか出来ないでしょうか?
上記の説明では私の意図は伝わりにくいでしょうか?お忙しいところ申し訳ありません。
【確認の質問①】
有難うございます。今簡易的に、シートAとシートBにのデータを作って行ってみました。
少々確認してよろしいでしょうか?
①まず、商品コードは、A126,A3578,B00021,C584,D4520,D0047 等、アルファベットから始まっています。アルファベットは半角です。
そこで、シートAとシートBの商品コード列を選択して、置換機能を使ってアルファベット毎に、半角→全角に修正しました。これ以外に簡潔に全角にする方法って無いですよね?!
②そして、シートCを挿入。シートAの商品コードと商品価格をシートCのA列とB列にコピペしました。その後、2列ともコピー→値ペーストして数式をとりましたが、よかったでしょうか?
③シートCを商品番号を基準に昇順で並べ替えをしました。この場合、アルファベット基準に昇順となりました。並べ替えが重要なのですね?!
⇒【確認の質問②】へ続く
【確認の質問②】(【確認の質問①】の続き)
③シートBの空いている列に、VLOOKUPを使い、シートCの商品コードを引っ張りました。ところで、常に私は完全一致にしたいので、=VLOOKUP($E1,シートC!$A:$B,1,0)としてしまいますが、TRUE型でなくてもよろしいでしょうか?
④シートBの隣の列にもシートCの商品価格を挿入しました。
⑤そして、この2列をコピー→値ペーストして数式をとりました。
⑥あとは、不一致数値を確認するだけですね?!
以上の手順でよかったのでしょうか?理解が間違っているなら、ご指摘をお願い致します。
会社ではもっとデータが多いのですが、これで出来るのなら 確かに時間の短縮になると思います。
宜しくお願い致します。