Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2016)
返したデータの表示の方法
投稿日時: 19/01/21 16:25:19
投稿者: ゆうひ

1シートに下記の在庫データがある。
 
A B      C    D     
a@型式     数量  相当品型式
1 リンゴ-01 100個  リンゴ-03
2 ブドウ-02  50個
3 リンゴ-03   0個  リンゴ-01 
 
2シートには1シートの在庫をB列の型式と同じであれば在庫数が確認できる。
 
A B      C    D      E     
a@在庫数    型式  相当品型式  相当品在庫数
 
 
C列に "リンゴ-01" と入力するとA列にa@B列シート1の在庫数が返す仕組みになっている。
式は↓
 
=INDEX(在庫!A:A,MATCH(C2,在庫!B:B,0))
 
C列に "リンゴ-03" と入力すると在庫は無いため
D列に 在庫がある相当品の型番と E列に相当品の在庫数を表示させたい。
 
上記の式を単純に引用して表示させることはできるが、C列が空白の場合
1シートの2の型式と在庫数が表示される。
 
これを防ぐにはどのような式を追加すればよいでしょうか?
 
条件付き書式を使う方法、もしくは関数が解る方、ぜひご教授お願いいたします。
 
 

回答
投稿日時: 19/01/21 16:58:13
投稿者: んなっと

望んでいる結果も必ず書いてください。
とりあえずこれらを試してみてください。
 
  A    B     C      D       E
1 a@在庫数    型式 相当品型式 相当品在庫数
2  1   100 リンゴ-01             
3  2   50 ブドウ-02             
4  3    0 リンゴ-03  リンゴ-01      100
 
A2
=IFERROR(INDEX(在庫!A:A,MATCH(C2,在庫!B:B,0)),"")
下方向・↓
B2
=IFERROR(VLOOKUP(A2,在庫!A:C,3,FALSE),"")
下方向・↓
D2
=IFERROR(IF(B2=0,VLOOKUP(A2,在庫!A:D,4,FALSE)&"",""),"")
下方向・↓
E2
=IFERROR(VLOOKUP(D2,在庫!B:C,2,FALSE),"")
下方向・↓

投稿日時: 19/01/22 11:27:26
投稿者: ゆうひ

望んでいる結果も必ず書いてください。
↓↓
とのことでしたので説明不足な点追記しました。
 
 
1シートに下記の在庫データがある。(実際に使用しているものは800種程在庫登録しています)
  
 A     B     C      D     
在庫a@  型式    在庫数   相当品型式
 1   リンゴ-01  100個   リンゴ-03
 2   ブドウ-02   50個
 3   リンゴ-03    0個   リンゴ-01 
 4
 
  
2シートには、1シートの在庫データを検索して在庫を探すことが出来る表となっている。
 
例えば、下記の2シート表c列に"リンゴ-03"と型式を入力すると、1シートに登録している型式と
同じであれば、2シートの a・b列"在庫aE在庫数"に1シートの同じ型式のbニ在庫数が確認できる。
この時に下記2シートの検索で"リンゴ-03"は在庫が無いことがわかったので、
d列には、"リンゴ-03"の代わりに販売できる型式が表示するようになっており、
e列には、d列の "リンゴ-01" の在庫数が表示されるようになっている。
 
 
↓2シート
  
 a     b      c       d        e     
在庫a@  在庫数     型式    相当品型式   相当品在庫数
 3     0個    リンゴ-03   リンゴ-01    100個
 
 
c列を抜かし、下記の計算式が入っています。
 
=INDEX(1シート!A:A,MATCH(C2,1シート!B:B,0))
 
上記の式を単純に引用して返したデータを表示させることはできるが、
 
ここからがどうしたいか・・・です。
 
2シートのc列が空白の場合 1シートの2列目に登録されているの型式と在庫数が表示される。
  
 a     b      c       d        e     
在庫a@  在庫数     型式    相当品型式   相当品在庫数
 2     50個    ブドウ-02    #N/A      #N/A
 0      0     (空白)   ブドウ-02     50個
 0      0     (空白)   ブドウ-02     50個
                      ↑       ↑ 
                      この表示を防ぎたい。
                    
  
これを防ぐにはどのような式を追加すればよいでしょうか?
  
ということでした。
教えていただいた
=IFERROR(INDEX(在庫!A:A,MATCH(C2,在庫!B:B,0)),"")
をd・eに入れてみたところ、やはり空白の場合空白と同じ最上部の在庫データを
返すため、思っていたようにはなりませんでした。
c列に何らかの入力があれば "#N/A" となり問題ないのですが空白のときの
必要の無い表示を解決できる方法をご存知あれば教えていただきたいです。
 
説明は理解出来ますでしょうか?
 
宜しくお願いいたします。
 
 
 
  
 

回答
投稿日時: 19/01/22 11:50:06
投稿者: んなっと

そうですよね、
=INDEX(1シート!A:A,MATCH(C2,1シート!B:B,0))
が正しい式とシート名ですね。
 
なぜ最初の書き込みで、
=INDEX(在庫!A:A,MATCH(C2,在庫!B:B,0))
という式を書き込んだのですか?
このような矛盾は、回答する側としては非常に迷惑です。
どう考えてもうっかりミスとは考えられません。

投稿日時: 19/01/22 12:52:16
投稿者: ゆうひ

んなっと様
 
大変申し訳ありませんでした。
 
実際使用している表の計算式をコピペして例題の表にあわせて編集したつもりでしたが、
"在庫!"の部分だけ編集がもれていることに追記の文章作成のときに気がつきました。
 
ご指摘、ご教授ありがとうございます。
今後、気をつけます。

回答
投稿日時: 19/01/22 13:15:28
投稿者: んなっと

本当に?
1シート!ではなくて、'1シート'!ではないのですか?
文章が長いわりに、情報が信用できません。
じっくり考えて質問文を作成するようにしてください。
 
一応最初の質問文に対する回答です。シート名だけ変えました。
 
  A    B     C      D       E
1 a@在庫数    型式 相当品型式 相当品在庫数
2  1   100 リンゴ-01             
3  2   50 ブドウ-02             
4  3    0 リンゴ-03  リンゴ-01      100
 
A2
=IFERROR(INDEX('1シート'!A:A,MATCH(C2,'1シート'!B:B,0)),"")
下方向・↓
B2
=IFERROR(VLOOKUP(A2,'1シート'!A:C,3,FALSE),"")
下方向・↓
D2
=IFERROR(IF(B2=0,VLOOKUP(A2,'1シート'!A:D,4,FALSE)&"",""),"")
下方向・↓
E2
=IFERROR(VLOOKUP(D2,'1シート'!B:C,2,FALSE),"")
下方向・↓
 
 
内容が信用できないので、2回目の質問文はほとんど無視しています。

投稿日時: 19/01/22 15:37:54
投稿者: ゆうひ

んなっと さんの引用:
本当に?
1シート!ではなくて、'1シート'!ではないのですか?
文章が長いわりに、情報が信用できません。
じっくり考えて質問文を作成するようにしてください。
 
一応最初の質問文に対する回答です。シート名だけ変えました。
 
  A    B     C      D       E
1 a@在庫数    型式 相当品型式 相当品在庫数
2  1   100 リンゴ-01             
3  2   50 ブドウ-02             
4  3    0 リンゴ-03  リンゴ-01      100
 
A2
=IFERROR(INDEX('1シート'!A:A,MATCH(C2,'1シート'!B:B,0)),"")
下方向・↓
B2
=IFERROR(VLOOKUP(A2,'1シート'!A:C,3,FALSE),"")
下方向・↓
D2
=IFERROR(IF(B2=0,VLOOKUP(A2,'1シート'!A:D,4,FALSE)&"",""),"")
下方向・↓
E2
=IFERROR(VLOOKUP(D2,'1シート'!B:C,2,FALSE),"")
下方向・

 
 
 
上記、教えて頂いた計算式を実際に使用している表に引用してみたところ、
 
ゆうひ さんの引用:

 a     b      c       d        e     
在庫a@  在庫数     型式    相当品型式   相当品在庫数
 2     50個    ブドウ-02    #N/A      #N/A
 0      0     (空白)   ブドウ-02     50個
 0      0     (空白)   ブドウ-02     50個
                      ↑       ↑ 
                      この表示を防ぎたい。
                    

 
新規Bookで試したところうまく出来そうと思ったのですが・・・
実際の表に使用してみたところ
c列空白のときd・e列の表示を防ぐことが出来ませんでした。
 
原因はおそらく 1シート のデータは Accessの入力データが元となっていて
一度エクセルにエクスポートしたものをまた別のエクセルファイルにデータをリンクさせて
編集して使っていること。
a・bの値の表示がc列が空白の場合 "0" と表示されるのもこれが原因かと・・・。
 
c列が空白のときd・eも空白にすることは可能でしょうか?
マクロはわからないので関数で出来るとありがたいのですが、方法が無いのであれば諦めます。
 
D2
=IFERROR(IF(B2=0,VLOOKUP(A2,'1シート'!A:D,4,FALSE)&"",""),"")
ちなみに   ↑ B2=0では無くて 0個ではくても表示する方法はありますか。
 

回答
投稿日時: 19/01/22 16:04:28
投稿者: んなっと

ほとんど質問文を読んでいません。勘だけで回答。
 
  A    B     C      D       E
1 a@在庫数    型式 相当品型式 相当品在庫数
2  1  100個 リンゴ-01             
3  2  50個 ブドウ-02             
4  3   0個 リンゴ-03  リンゴ-01     100個
 
A2
=IF(C2="","",IFERROR(INDEX('1シート'!A:A,MATCH(C2,'1シート'!B:B,0)),""))
下方向・↓
B2
=IF(C2="","",IFERROR(VLOOKUP(A2,'1シート'!A:C,3,FALSE),""))
下方向・↓
D2
=IF(C2="","",IFERROR(IF(ASC(B2)="0個",VLOOKUP(A2,'1シート'!A:D,4,FALSE)&"",""),""))
下方向・↓
E2
=IF(OR(C2="",D2=""),"",IFERROR(VLOOKUP(D2,'1シート'!B:C,2,FALSE),""))
下方向・↓
 

投稿日時: 19/01/24 17:05:59
投稿者: ゆうひ

 
んなっと様
 
お世話になります。
 
この度、は厳しいご指摘ありながらも、丁寧にご回答いただきまして
ありがとうございました。
ご教授いただいた式の引用で私の望んでいる結果にたどり着くことが出来ました。
 
関数の勉強にもなりました。
 
また、何かありましたら宜しくお願いいたします。