[[20160213110728]]　『決まった値を返す関数』（うほうほ）　ページの最後に飛ぶ

『決まった値を返す関数』（うほうほ）

下記の様な表があるとして、
Ａ列は、入力規則のリストボックスがあり、
リストボックスから商品を選択します。
その商品を選択した時に、Ｂ列に決まった値を返したいです。

　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　・・・・
１　　りんご　 100
２　　みかん　 300
３　　ばなな　 500
４　　めろん　 800
５　　ぶどう　 200
６　　りんご　 100
７　　ばなな　 500
・
・
・

ネットで調べてCHOOSE関数なるものを見つけたのですが、
商品の項目がかなり（600位）ある為、複数の選択する方法がわかりません。

また、商品は今後も増えていくと思いますので、
商品と金額を別シートに記載して範囲指定で拡げて行けたらと考えてますが、
その辺についても、アドバイス頂ければと思います。
宜しくお願い致します。

＜使用アプリ：Excel2000、使用 OS：WindowsXP ＞

 ＜Sheet2＞		
 	A	B
 1	りんご	100
 2	みかん	300
 3	ばなな	500
 4	めろん	800
 5	ぶどう	200

 というリストを作っておいてSheet1のB1セルに =IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE))
 でどうでしょうか？列全体を選択してあるので追加があっても範囲を広げる必要がありません。
（se_9） 2016/02/13(土) 12:01

se_9さんバッチリでした！ありがとうございます！
これなら、追加があっても列に記入するだけで良いですし、
誰でも簡単に変更できるんで、とても助かります！！

最後に、あと１点だけ教えて頂きたいんですが、
商品の項目はあっても、金額が無い場合があるんで、
金額が無い商品を選んだ場合は、空白を返したいのですが、
お教え頂いた式の応用で出来るのでしょうか？

（うほうほ） 2016/02/13(土) 13:24

 数式に組み込む方法
 1.=IF(A1="","",IF(VLOOKUP(A1,Sheet2!A:B,2,FALSE)=0,"",VLOOKUP(A1,Sheet2!A:B,2,FALSE)))
 2.=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE))&""

 ※2の方法だと数値が文字列になります。

 表示形式で0を表示しない方法
 Sheet1のB列を選択して右クリック→セルの書式設定→表示形式
 ユーザー定義 0;;;
（se_9） 2016/02/13(土) 13:41

se_9さんすみません。
私のやり方が悪いと思うんですが、どちらの数式も、
金額の無い商品を選んだ時、
金額の値が空白にならずに「#N/A」になります。

ちゃんと文字列でやってみましたけど、
他になにが悪いのかわからないです。。。
（うほうほ） 2016/02/13(土) 16:43

末尾に「&""」をつけます。
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE)&"")
（マリオ） 2016/02/13(土) 19:55

 #N/Aということは金額が無いというより該当する商品名がないということかな。
 =IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(A1,Sheet2!A:B,2,FALSE)))
（se_9） 2016/02/13(土) 20:02

 =IFERROR(IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"")

 とか。

 と書いてよく見たら xl2000 でしたか。
 それでは、se_9 さんの式で。

 To マリオさん

 最後に "" を付加しても、#N/A は消えませんよね？

（β） 2016/02/13(土) 20:05

マリオさん、se_9さん、βさん
コメントありがとうございます。
風邪引きまして、返事遅くなってすみません。

se_9さんの仰るとおり、
該当する商品名が無い為に#N/Aとなっておりました。
完全に私のミスです。。。すみません。。。
教えて頂いた式で、商品名が無くても#N/Aが無くなりました。
ありがとうございます！！とても助かりました！！

また、
マリオさんもβさんも考えて下さり、ありがとうございました！
大変感謝です！！
（うほうほ） 2016/02/16(火) 17:28

下記の表で、
決まった値を返す式
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"",VLOOKUP(A1,Sheet2!A:B,2,FALSE)))
をお教え頂いたのですが、
条件が増えましたので、追加でお教え頂きたいです。

から、

　　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　・・・・
１　　1号店　　りんご　 100 りんご　 150
２　　　　　　　みかん　 300 みかん　 350
３　　　　　　　ばなな　 500 ばなな　 550
４　　　　　　　めろん　 800 めろん　　850
５　　　　　　　ぶどう　 200 ぶどう 250
６　　　　　　　りんご　 100 りんご　　150
７　　　　　　　ばなな　 500 ばなな　　550
・
・
・

となり、
Ａ1の値が1号店なら、
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!B:C,2,FALSE)),"",VLOOKUP(A1,Sheet2!B:C,2,FALSE)))
の範囲の値を返し
A1の値が2号店なら、
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!D:E,2,FALSE)),"",VLOOKUP(A1,Sheet2!D:E,2,FALSE)))
の範囲の値を返す様にしたいです。

よく分からず、
Ifを追加してみましたけど、ダメでした。
=IF(Sheet2!A1=1号店,(IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!B:C,2,FALSE)),"",VLOOKUP(A1,Sheet2!B:C,2,FALSE))),(IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!D:E,2,FALSE)),"",VLOOKUP(A1,Sheet2!D:E,2,FALSE))))

（うほうほ） 2016/03/01(火) 15:53

 ＜Sheet1＞				
 	A	B	C	
 1	1号店	りんご		
 2		みかん		
 3		ばなな		
 4		めろん		
 5		ぶどう		
 6		りんご		
 7		ばなな

 ＜Sheet2＞				
 	A	B	C	D
 1	りんご	100	りんご	150
 2	みかん	300	みかん	350
 3	ばなな	500	ばなな	550
 4	めろん	800	めろん	850
 5	ぶどう	200	ぶどう	250
 6	りんご	100	りんご	150
 7	ばなな	500	ばなな	550

 Sheet1のA1セルは1と入力して表示形式で 0"号店"
 Sheet1のC1セル =IF(B1="","",IF(ISERROR(VLOOKUP(B1,Sheet2!$A:$D,CHOOSE(A$1,2,4),FALSE)),"",VLOOKUP(B1,Sheet2!$A:$D,CHOOSE(A$1,2,4),FALSE)))
 下にフィルコピー
（se_9） 2016/03/01(火) 17:10

 質問です。
・店舗は2店舗だけなのか？
・店舗名は本当に「1号店」「2号店」なのか？
・Sheet2の価格表（？）で、なぜ店舗ごとに同じ品名を並べる必要があるのか？
・Sheet2の価格表（？）で、なぜ同じ店舗で品名が重複するのか？（「りんご」「ばなな」）
・Sheet1とSheet2の品名が同じ順に並んでいるが、実際そうなのか？
　実際そうなら検索するまでもないし、違うのなら例がテキトーすぎます。

 とりあえず、回答ですが、

	A	B	C
1		1号店	2号店
2	りんご	100	150
3	みかん	300	350
4	ばなな	500	550
5	めろん	800	850
6	ぶどう	200	250

 Sheet2を ↑ のようにできませんか？
 品名を1列だけして、1行目に店舗名を入れる

 これでいいのなら

 Sheet1のA1に店舗名、B1から下に品名だとして、

 C1 =IF($A$1="","",IF(COUNTIF(Sheet2!A:A,B1),VLOOKUP(B1,Sheet2!A:D,MATCH($A$1,Sheet2!$A$1:$C$1,0),FALSE),""))

 それとも、実際は店舗によって品名が全然違うとか？

 その他の質問
 ・なぜ価格表にない品名がプルダウンのリストに含まれているのか？
 ・600以上の品名をリストから選択することに日々ストレスを感じることはないのか？
（笑） 2016/03/01(火) 22:47

 > Sheet2を ↑ のようにできませんか？
 > 品名を1列だけして、1行目に店舗名を入れる

 誤）品名を1列だけして
 正）品名を1列だけにして

 以上、訂正です。
（笑） 2016/03/01(火) 23:01

 以上じゃなかった。

 > C1 =IF($A$1="","",IF(COUNTIF(Sheet2!A:A,B1),VLOOKUP(B1,Sheet2!A:D,MATCH($A$1,Sheet2!$A$1:$C$1,0),FALSE),""))
 　　　　　　　　　　　　　　　　　　　　　　　　　　　　~~~~~~~~~~~
 正）･･･,VLOOKUP(B1,Sheet2!A:C,･･･
 　　　　　　　　　　 　　~~~~~
 ひとまず、以上、訂正です。
（笑） 2016/03/01(火) 23:11

 何度もすみません。今度はお詫びと訂正です。

 >・Sheet1とSheet2の品名が同じ順に並んでいるが、実際そうなのか？
 >　実際そうなら検索するまでもないし、違うのなら例がテキトーすぎます。

 上記の質問は撤回します。すみませんでした。

 > 条件が増えましたので、追加でお教え頂きたいです。

 ↑ の下にあるのはSheet1だと思ってました。
（笑） 2016/03/02(水) 08:18

 もしかして ↓ も「Sheet1」なんですかね？

 > 　　　　Ａ　　　 Ｂ　　 　Ｃ　　　Ｄ　　　 Ｅ　・・・・ 
 > １　　1号店　　 りんご　 100 りんご　 150 
 > ２　　　　　　　みかん　 300 みかん　 350 
（後略）

 だとしたら質問の意味がまったく理解できていないことになります。
（笑） 2016/03/02(水) 08:42

(笑）さん
すみません。返答遅くなりました。

まだちゃんと読ませて頂いてないですが、
仰る通り、例がテキトーすぎですね。すみません。。。

店舗名は実際の名前と違います。。。
また現状は２つですが、今後増える可能性があります。

そこら辺については、教えて頂いて式を解読して自分で直したり、
どうしても無理であれば、質問しようと考えてました。

（うほうほ） 2016/03/02(水) 19:42

se_9さん
コメントのお礼遅れました。ありがとうございます。
お教え頂いた式でいろいろ試したのですが、
上手く行かないためお伺いしたいです。

お教え頂いた式、

=IF(B1="","",IF(ISERROR(VLOOKUP(B1,Sheet2!$A:$D,CHOOSE(A$1,2,4),FALSE)),"",VLOOKUP(B1,Sheet2!$A:$D,CHOOSE(A$1,2,4),FALSE)))

で、Sheet1 セルA1で文字で切り替えをするのはどうしたらよいでしょうか。
CHOOSE(A$1,2,4)
を変えると思っていろいろ試したのですがダメでした。

また、下記表の様に、選択範囲が１列隔てての場合はどうなるのでしょうか。

＜Sheet2＞

 	A	B	C	D         E
 1	りんご	100	         りんご	150
 2	みかん	300	         みかん	350
 3	ばなな	500	         ばなな	550
 4	めろん	800	         めろん	850
 5	ぶどう	200	         ぶどう	250
 6	りんご	100	         りんご	150
 7	ばなな	500	         ばなな	550

最後に、下の様な表し、2に切り替えると、
Sheet1の「なすび、ごぼう、とまと」に金額は表示されず、
「れもん、いちご、すいか」の金額に「なすび、ごぼう、とまと」の
金額が表示されてしまいます。

＜Sheet1＞

 	A	B	C	
 1	1号店	りんご		
 2		みかん		
 3		ばなな		
 4		めろん		
 5		ぶどう		
 6		りんご		
 7		ばなな		
 8                 れもん
 9                 いちご
 10　　　　　　　　すいか
 11                なすび
 12                ごぼう
 13                とまと

 ＜Sheet2＞				
 	A	B	C	D
 1	りんご	100	りんご	150
 2	みかん	300	みかん	350
 3	ばなな	500	ばなな	550
 4	めろん	800	めろん	850
 5	ぶどう	200	ぶどう	250
 6	りんご	100	りんご	150
 7	ばなな	500	ばなな	550
 8	れもん	400	なすび	450
 9	いちご	600	ごぼう	650
 10	すいか	900	とまと	950

聞いてばかりで大変恐縮ですが、
ヒントでも構いませんので、お教え頂ければと思います。
宜しくお願いします。
（うほうほ） 2016/03/03(木) 15:32

 １列隔てている場合は
 =IF(B1="","",IF(ISERROR(VLOOKUP(B1,CHOOSE(A$1,Sheet2!$A:$B,Sheet2!$D:$E),2,FALSE)),"",VLOOKUP(B1,CHOOSE(A$1,Sheet2!$A:$B,Sheet2!$D:$E),2,FALSE)))

 で試してみてください。
 隔ててない場合は $D:$E を $C:$D にするだけです。
（se_9） 2016/03/03(木) 17:10

se_9さん
ありがとうございます！バッチリです！

Sheet1 セルA1に、1or2入力で切り替わるのを、
セルA1に文字(1号店,2号店)にして、
A2にIF文で1号店なら「1」、2号店なら「2」と表示する様にし、
式の参照セルをA2にしました。
もうちょっと柔軟に考えれば良かっただけですね。すみません。

でも、本当にありがとございました！とても助かりました！！
（うほうほ） 2016/03/03(木) 17:37

 Sheet2は、なぜ店舗ごとに品名の列が必要なのか、なぜ重複している品名があるのか、
 という疑問が晴れないままですが、ご希望通りの構成だとして。

 現在2店舗で、今後どの程度まで増えるのか知りませんけど、
 Sheet2の店舗ごとの範囲を、店舗名で名前定義しておけば

 =IF(OR($A$1="",B1=""),"",VLOOKUP(B1,INDIRECT($A$1),2,FALSE))

 これでできますね。

 参照表を切り替えて「表引き」する
http://pc.nikkeibp.co.jp/pc21/tech/excel36/27/

 店舗が増えても、名前定義さえしておけば、数式を変更する必要はなし。
 なので、これ以上数式が長くなることはない。
 A2セルを使う必要もありません。

 #N/A を非表示にしたいのなら条件付き書式で。
 条件付き書式の数式を　=ISNA(C1)　として、フォント色を白にする。
　
　
 今後積極的な店舗展開が見込まれるのなら
 VLOOKUP関数とOFFSET関数を組み合わせるやり方もあります。

 具体的な式は提示しませんが、これならSheet2の1行目にでも店舗名さえ入れておけば、
 店舗の増減による数式の変更も、名前定義も必要ありません。

 参考まで。
（笑） 2016/03/04(金) 13:02

(笑)さんありがとうございます。

ご質問の件ですが、
＞Sheet2は、なぜ店舗ごとに品名の列が必要なのか、
これは、Sheet1のリストボックスで表示する時に、
無い商品を表示して欲しく無かったので、分けてみました。

＞なぜ重複している品名があるのか、
これは、最初の説明させて頂くときに、Sheet2の表を書く際、
Sheet1の表をコピーしたので、それが残ってたみたいです。すみません。

お教え頂いた式はまだ試してないですが、
かなり便利そうですので、近日中に試させて頂きます！
不明点等生じた際は、宜しくお願いします。

（うほうほ） 2016/03/04(金) 17:05

コメント返信：

[ 一覧(最新更新順) ]

[[20160213110728]] 『決まった値を返す関数』（うほうほ） ページの最後に飛ぶ

[[20160213110728]]　『決まった値を返す関数』（うほうほ）　ページの最後に飛ぶ