EXEL関数

質問

2つのEXCELシートが有り、各シートには以下の様なデーターが載ってます。

例えば、A・Bシート共に
A列：商品No
B列：型番
C列：数量　　と記載されてます。

ただ、BシートにD列：棚番　が所々に記載されてます。

このA・Bシートの 商品No　と　型番　が一致するデーターに対して、Bシートの所々に記入されて
いる棚番をAシートのＢシートと一致する列にワンクリックで転記したいのですが、関数の使い方、
関数の設定の仕方が解りません。
何方かわかる方、関数の設定の仕方を教えてください。

Ａシート
品番　　型式　　数量
1234 ABCD 100
1257 abcde 2
1266 xzabc 15
1556 CHYU 85
1324 ccdeu 158

Bシート
品番　　型式　　数量 棚番
1234 ABCD 100　 Ａ－１
1457 abcdg 2
1536 CHwU 85
1266 xzabc 15　 Ｂ－４
1335 cＤdeu 168　Ｅ－８

　　　　　　↓
Ａシート
品番　　型式　　数量　 棚番
1234 ABCD 100　 Ａ－１
1257 abcde 2
1266 xzabc 15　 Ｂ－４
1556 CHYU 85
1324 ccdeu 158

回答No.6

Nouble

要は在庫表を整理したい
型番、品番のセット毎に
何処の棚と何処の棚に
分散して置いてあるか
一覧を作りたい
そういう事でしょうか？

裏を返せば
型番、品番のセットは
一度切り現れる
とは、限らない
ですよね？

ＶＬＯＯＫＵＰは
最初にヒットしたものしか表さない
ので、向きません
よね

こういう場合、配列数式が有効です。

次の式を
AシートのD3に入力して
横にずらっとコピー(フィル)し
そのまま縦にもコピー(フィル)して
ください

=if(column(a1)>=
　sumproduct(
　　(Ｂシート!$a:$a=$a3)
　　*(Ｂシート!$b:$b=$b3)
　　)
　,""
　,offset(
　　Ｂシート!$a$1
　　,small(
　　　index(
　　　　(Ｂシート!$a:$a=$a3)
　　　　*(Ｂシート!$b:$b=$b3)
　　　　*row(
　　　　　Ｂシート!$a:$a
　　　　)
　　　　+not(
　　　　　(Ｂシート!$a:$a=$a3)
　　　　　*(Ｂシート!$b:$b=$b3)
　　　　)*999999999999999
　　　,,)
　　,column(
　　　a1
　　)-1
　　,3,1,1
　)
)

確認して無い
ので、難なのですが
如何でしょうか？

お役に立てて居たならば幸いです

尚、
望まれる場合は解説致します
ので、
その旨、お申し付けください。
（※注：動作異常の時も解説をお申し込みください）

回答No.5

bunjii

回答No.4に見落としがありましたので訂正します。
「このA・Bシートの 商品No　と　型番　が一致する」となっていることを商品Noのみの検索として回答してしまいました。
一般的には商品Noで管理するときは重複が無いものとしますので、あなたの設問では重複する商品Noがあると言うことですよね？
基本的にはVLOOKUP関数で可能ですが、検索値と検索範囲および抽出結果について其々に加工が必要になります。
AシートのD2に次の数式を設定してください。
=IFERROR(SUBSTITUTE(VLOOKUP(B2&A2,INDEX(Bシート!B$2:D$10&Bシート!A$2:A$10,0),3,FALSE),A2,""),"")
念のために模擬データを追加して検証した画像を貼付します。
尚、検索範囲の加工に配列式を使っていますので負荷を軽減するためBシートの2行目から10行目までにしてあります。
検索範囲を必要な行数まで拡大して運用してください。

回答No.4

bunjii

>Bシートの所々に記入されている棚番をAシートのＢシートと一致する列にワンクリックで転記したいのですが、関数の使い方、関数の設定の仕方が解りません。
「ワンクリックで転記」と言う動作はVBA等のプログラムを使わないとできません。
「Bシートから抽出して代入」と言う動作は関数でできます。
あなたはどちらをしたいのですか？

>関数の設定の仕方を教えてください。
関数で処理する場合はVLOOKUP関数で代入できます。
AシートのD2セルへ次の数式を設定し、下へ必要数コピーすれば目的に合うでしょう。
=IFERROR(VLOOKUP(A2,Bシート!A:D,4,FALSE),"")
但し、Excelのバージョンは2003以前ではIFERROR関数が組み込まれていないので再現できません。
Excel 2003以前のときはAシートのD2セルに次の数式を設定してください。
=IF(COUNTIF(Bシート!A:A,A2),VLOOKUP(A2,Bシート!A:D,4,FALSE),"")

回答No.3

MackyNo1

BシートがSheet2ならAシートのD2セルに以下の式を入力して下方向にオートフィルしてください。
=IFERROR(INDEX(Sheet2!$D$2:$D$1000,MATCH(A2&B2,INDEX(Sheet2!$A$2:$A$1000&Sheet2!$B$2:$B$1000,),0))&"","")

回答No.2

kagakusuki

もしBシートのA列に入力されているデータに同じ品番が入力されている行が複数存在している様な事が無い場合には、AシートのD列のセル(何行目のセルであっても同じ関数を入力すればOKです)に次の様な関数を入力すれば良いと思います。

=IF(INDEX($A:$A,ROW())="","",IF(ISERROR(1/(VLOOKUP(INDEX($A:$A,ROW()),B!$A:$D,4,FALSE)<>""),"",VLOOKUP(INDEX($A:$A,ROW()),B!$A:$D,4,FALSE)))

　尚、もしも上記の

>BシートのA列に入力されているデータに同じ品番が入力されている行が複数存在している様な事が無い場合

という条件が満たされておらず、BシートのA列に入力されている品番とBシートのB列に入力されている型式の双方が同じ値となっている行が複数存在している事があり得る場合には、それら複数の行のD列のセルに入力されている棚番データの内の、どの値を採用すれば良いのか判断する基準が存在しませんので、その場合は転記する方法は存在しません。

回答No.1

aokii

BシートのE2に、
=A2&B2
BシートのF2に、
=F2
AシートのD2セルに、
=IF(ISNA(VLOOKUP(A2&B2,B!E:F,2,FALSE)),"",VLOOKUP(A2&B2,B!E:F,2,FALSE))