Excel関数 情報引用する方法

Excel関数を使い、以下のようなことをしたいです。
添付画像をご参照ください。

＜やりたいこと＞
・「元情報」シートにある商品番号情報を、「単価有」「単価無」シート、それぞれへ引用させたい
・「単価マスタ」シートを参照し単価が0or空白の場合、「単価無」シートのA列へ「元情報」の商品番号、B列へ「単価マスタ」の単価を、
・「単価マスタ」シートを参照し単価が0or空白以外の場合、「単価有」シートのA列へ「元情報」の商品番号、B列へ「単価マスタ」の単価を、引用させたい
・「単価無」「単価有」シート上のA,B列にどのような関数を埋め込めばよいかをご教示ください（関数でできない場合には、VBAをご教示いただけるとうれしいです）

つたない説明かもしれませんが何卒ご容赦ください。

No.2 ベストアンサー 回答者： fujillin 回答日時： 2022/08/01 08:45

こんにちは

ご質問の通りの手順で、作業列を用いて順に処理してゆけばさほど難しくなくできると思います。
仮に、作業列を「元情報」シートに設けるとして（＝他のシートでもかまいません）

1）作業列Aに製品番号から単価を求める
　=VLOOKUP(製品番号, 単価マスタ!A:B, 2, 0)
　のような感じで、下方にフィルコピーで求められるでしょう。
2）作業列Aを見れば、「単価有」シートなのか「単価無」シートなのか判別できるので、
　作業列B（＝仮に単価有シートに対応と考える）に該当する行に序数を振ります
　=IF(単価有シートなら,COUNT(作業列B列$1:作業列B列1)+1,"")
　で下方にフィルコピーすれば、上から順に1,2,3・・と番号が振られます。
3）作業列Cに「単価無」の場合を同様に抽出して、序数を振ります。

4）単価有シートのA列で、ROW()関数を利用して、作業列Bの値を1から順にピックアップし、A列の製品番号を参照すれば、結果的に、該当する番号を詰めて抽出することができます。
Office365環境であればXLOOKUPで、そうでなければMATCH、INDEX関数の組み合わせで可能です。
5）単価無シートも同様に、作業列Cからピックアップすれば良いでしょう。

言葉で説明すると、面倒に見えるかもしれませんが、比較的単純で無駄のない方法だと思いますので、お勧めです。
なお、ご質問文に説明がないので、言及していませんが「元情報」シートの製品番号に「単価マスタ」に乗っていない番号がある場合、上記の方法ではどちらのシートにも抽出されません。
（添付図のA列「EEE」のようなデータがあった場合という意味です）


もしかすると、作業列など用いずに直接求める方法をお求めなのかも知れませんね。
できないことはありませんが、検索と条件判断と、上に詰めて抽出を同時に行うことになるため、式が複雑になるのと、まとめて計算するために不要な部分の計算も無駄に行うので効率が良いとは言えません。
このため、対象データ（＝行数）が大量になると、計算に時間がかかる可能性があります。
また、十分理解していないとメンテがほぼできなくなってしまうこともあって、上記の方法をお勧めする次第です。

とは言え、不可能ではないことのご参考として、添付図に要領のみを示しておきます。
添付図では簡略化して、各シートを1シートにまとめてありますので、列などは読み替えてご覧になってください。
　・A列が「元情報」シートのA列
　・D:E列が「単価マスタ」シートのA:B列
　・G:H列が「単価有」シートのA:B列
に該当します。
もう一方のシートは条件を変えるだけなので省略しました。
（>0 を =0 に変えるだけなので・・）

まず、G2セルに
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$99)/(A$2:A$99<>"")/(MMULT(TRANSPOSE((D$2:D$99=TRANSPOSE(A$2:A$99))*(E$2:E$99>0)),(ROW(D$2:D$99)/ROW(D$2:D$99)))>0),ROW(A1))),"")
の式を入力し、Ctrl+Shift+Enterで確定します。
（この確定操作は必須です）

H2セルに
=IF(G2="","",VLOOKUP(G2,D$2:E$99,2,0))
の式を入力します。
その上で、G2:H2セルを適当な範囲まで下方にフィルコピーします。
これによって、添付図のようにG:H列に「単価有」のデータを抽出することが可能となります。

※ Office365環境であれば、Filter関数などが利用できるので、もっと簡単な式にできるものと予想しますが、当方に環境がないので試してはいません。

No.4 回答者： banzaiA 回答日時： 2022/08/02 11:54

#1です。

補足説明を要求しましたが、補足がないので、単価の０と空白は同値。
Office365環境と判断しました。

「元情報』シートのB列に単価の作業列を挿入します。
その列に「単価マスタ」シートから単価を読み込みます。
（VLOOKUPとかXLOOKUP関数等を使用します。）
「単価無」シートの
A2：=FILTER(A2:B7,B2:B7=0)
「単価有」シートの
A2：=FILTER(A2:B7,B2:B7>0)

以上

No.3 回答者： mike_g 回答日時： 2022/08/01 11:43

添付図参照(Excel 2019)

単価ﾏｽﾀ!C2: =COUNTIF(元情番号,A2)
単価無!A2: =IFERROR(SMALL(IF((単価=0)*元情Ck,商品番号),ROW(A1)),"")
単価無!B2: =IFERROR(IF(VLOOKUP(A2,単価ﾏｽﾀ!A$2:B$10,2)="","",VLOOKUP(A2,単価ﾏｽﾀ!A$2:B$10,2)),"")
単価有!A2: =IFERROR(SMALL(IF(単価*元情Ck,商品番号),ROW(A1)),"")
単価有!B2: =IFERROR(IF(VLOOKUP(A2,単価ﾏｽﾀ!A$2:B$10,2)="","",VLOOKUP(A2,単価ﾏｽﾀ!A$2:B$10,2)),"")

【お断わり】単価無!A2、単価有!A2 の両式は、オートフィル前に必ず配列(CSE)数式として入力しておくこと。それ以外の式は通常通りの入力で。

オ・シ・マ・イ

この回答へのお礼

大変わかりやすかったです。ありがとうございました。

お礼日時：2022/08/11 22:33

No.1 回答者： banzaiA 回答日時： 2022/08/01 07:05

「単価無」シートの単価の列（B列）は必要なのですか？

空白と０に意味があるのかないのか。
Excelのバージョンはなにですか？
補足説明してください。