
Excel関数を使い、以下のようなことをしたいです。
添付画像をご参照ください。
<やりたいこと>
・「元情報」シートにある商品番号情報を、「単価有」「単価無」シート、それぞれへ引用させたい
・「単価マスタ」シートを参照し単価が0or空白の場合、「単価無」シートのA列へ「元情報」の商品番号、B列へ「単価マスタ」の単価を、
・「単価マスタ」シートを参照し単価が0or空白以外の場合、「単価有」シートのA列へ「元情報」の商品番号、B列へ「単価マスタ」の単価を、引用させたい
・「単価無」「単価有」シート上のA,B列にどのような関数を埋め込めばよいかをご教示ください(関数でできない場合には、VBAをご教示いただけるとうれしいです)
つたない説明かもしれませんが何卒ご容赦ください。

No.4
- 回答日時:
#1です。
補足説明を要求しましたが、補足がないので、単価の0と空白は同値。
Office365環境と判断しました。
「元情報』シートのB列に単価の作業列を挿入します。
その列に「単価マスタ」シートから単価を読み込みます。
(VLOOKUPとかXLOOKUP関数等を使用します。)
「単価無」シートの
A2:=FILTER(A2:B7,B2:B7=0)
「単価有」シートの
A2:=FILTER(A2:B7,B2:B7>0)
以上
No.3
- 回答日時:
添付図参照(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)数式として入力しておくこと。それ以外の式は通常通りの入力で。
オ・シ・マ・イ

No.2ベストアンサー
- 回答日時:
こんにちは
ご質問の通りの手順で、作業列を用いて順に処理してゆけばさほど難しくなくできると思います。
仮に、作業列を「元情報」シートに設けるとして(=他のシートでもかまいません)
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関数などが利用できるので、もっと簡単な式にできるものと予想しますが、当方に環境がないので試してはいません。

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Excel(エクセル) 単価シートから単価をエクセル関数で自動取得する方法 1 2023/07/02 22:00
- Visual Basic(VBA) VBAで最新のデータを別シートに転記する方法をお教えください。 3 2022/04/07 19:20
- Visual Basic(VBA) VBA 最終行まで数式をコピーする 3 2023/01/03 15:44
- Excel(エクセル) 関数を教えてください。 2 2023/08/01 10:59
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
- Visual Basic(VBA) VBAで質問があります 1 2022/10/19 10:32
- その他(お金・保険・資産運用) 至急!【Wolt】各メニューの価格設定の簡単な計算方法 3 2023/03/05 11:58
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・【大喜利】【投稿~1/31】『寿司』がテーマの本のタイトル
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・【大喜利】【投稿~1/20】 追い込まれた犯人が咄嗟に言った一言とは?
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・【大喜利】【投稿~1/9】 忍者がやってるYouTubeが炎上してしまった理由
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
外構見積もりましたが、どうで...
-
製造業に勤めています。 外注さ...
-
はじめまして。私、配管業を個...
-
一式という言葉の使い方
-
表示方法
-
1工数1人工
-
卸単価の95掛けってどうゆう意...
-
単価の前についている「@」
-
CIFでの輸入の際LCLの場合 の...
-
初級SE、中級SE、上級SEはどの...
-
価格などで使う「@」の意味?
-
接客販売店員の一日の平均売り上げ
-
坪単価70万円で30坪の家だと、...
-
事務所ビルの電気料金について...
-
天井開口費の見積について
-
ACCESSのレコード順番の入れ替え
-
旅館リピーター率
-
水はつり工事の費用を教えてく...
-
売上、客数、単価から買い上げ...
-
建築現場の、土工、雑工、多能...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
はじめまして。私、配管業を個...
-
製造業に勤めています。 外注さ...
-
外構見積もりましたが、どうで...
-
天井開口費の見積について
-
積算 高所作業車 建築
-
一式という言葉の使い方
-
表示方法
-
仕入単価
-
公共建築工事(設備)の積算に...
-
1工数1人工
-
卸単価の95掛けってどうゆう意...
-
事務所ビルの電気料金について...
-
CIF単価→FOB単価の算出の仕方を...
-
リストボックスの項目に、他の...
-
ACCESSのレコード順番の入れ替え
-
SE単価交渉用文書
-
出精値引の意味は?
-
2024年 職人不足で単価が上がる...
-
IFERROR、SMALL関数について
-
売上、客数、単価から買い上げ...
おすすめ情報