Excel (一般機能)

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

 
(Windows 7全般 : Excel 2013)
プルダウンで空白がある人は選べないようにしたい
投稿日時: 18/10/31 11:34:09
投稿者: konitan0216

いつもお世話になってます。
 
リストに名前、年齢、資格番号などがあり、
プルダウンで名前を選ぶとVLOOKUPでほかの項目もその名前の方の情報が入力されるようになってます。
資格を持っていない方は資格番号が空白なのですが、持ってない人はプルダウンで選べないようにすることってできますか?
 
リスト
 
名前       年齢     資格番号
山田         18      3000
小林         20      
佐藤         26      6300
 
技術者        26      佐藤 ▽←プルダウン
資格者                 ▽←資格を持っていない小林は選べない
 
 
 
 
 
 
 
 

回答
投稿日時: 18/10/31 12:40:43
投稿者: Mike

確認させてください。
左端列の「技術者」とか「資格者」は何を参照して表示されたのですか?
下の表で貴方が最初に入力するのはどの列ですか?
 

投稿日時: 18/10/31 13:16:57
投稿者: konitan0216

Mikeさんへ
説明不足ですいません・・・
技術者や資格者はセルにそのまま入力されています。
 
自分はプルダウンから名前を選ぶだけです。
名前を選ぶとほかの項目は入力されるようになっているので
技術者の名前を選ぶ、資格者の名前を選ぶ、というだけにしたかったのですが人数が多く資格が無い方を選んでしまうミスがありそうなので、できるなら選ぶ項目を少なくしたいです
 

回答
投稿日時: 18/10/31 16:20:20
投稿者: んなっと

Excel2013なら
 
 [Power Query]
→[テーブルから]
→クエリエディターで「資格」フィールドのフィルターから(null)を除外
→[閉じて読み込む]
→空白を除外した下のような表ができる
 
   I   J   K
1 名前 年齢 資格
2 山田  18 3000
3 佐藤  26 6300
 
→上の表のI2から下をリスト範囲にする
 
※一例です。ほかにもあります。

回答
投稿日時: 18/10/31 16:35:31
投稿者: んなっと

I2から下のリスト範囲を指定する式は
元の値(S):
=OFFSET($I$2,,,COUNTA($I:$I)-1)
 

投稿日時: 18/10/31 17:19:21
投稿者: konitan0216

んなっとさんへ
コメントありがとうございます。
わたしが説明不足でした。
リストに入っている人は資格を持っていなくても別の技術者欄などでは名前を使うのでリストから消せません。

回答
投稿日時: 18/10/31 19:59:32
投稿者: んなっと

消すのではないですよ。元の表はそのままでいいんです。
入力規則の参照範囲だけを、新しく抽出した表に変更しましょうという意味です。
 
関数だとこう。
 
   A   B   C D    E  F   G H   I   J   K
1 名前 年齢 資格   技術者 26 佐藤   名前 年齢 資格
2 山田  18 3000             山田  18 3000
3 小林  20                佐藤  26 6300
4 佐藤  26 6300             青木  30 4000
5 渡辺  24                        
6 青木  30 4000                     
 
G1
入力規則:リスト▼:元の値:
=OFFSET($I$2,,,COUNTA($C:$C)-1)
I1
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($1:$500)/($C$1:$C$500<>""),ROW(I1))),"")
右方向・→下方向・↓

投稿日時: 18/11/01 10:32:29
投稿者: konitan0216

コメントありがとうございます
 
関数ありがとうございます、調べながら使ってみましたがうまく並び替えられました!
 
ですがやはり、リストを並べ替えるやり方しかないですかね?
資格の種類もいろいろあるのでひとつずつリストにするのは見ずらいかなと思ったのでとりあえず一つにしてます。

回答
投稿日時: 18/11/01 12:40:27
投稿者: んなっと

最初の質問文では、単に「空白セルを除外したい」という希望しか書かれていませんでしたよ。
おそらく資格番号の特徴から「資格者」「技術者」など複数に分類されるのでしょうね。
しかし、その特徴を類推する情報が一切ありません。
情報不足の質問には的確な回答はできません。
 
   A   B   C   D   E F   G  H   I J   K   L   M
1 名前 年齢 資格1 資格2 資格3   資格1 26 佐藤   資格1 資格2 資格3
2 山田  18  3000          資格2 24 渡辺   山田  小林  小林
3 小林  20     5000  6400   資格3 30 青木   佐藤  渡辺  佐藤
4 佐藤  26  3000     6000             青木     青木
5 渡辺  24     5300                          
6 青木  30  3100     6800                      
 
K2
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($2:$500)/(C$2:C$500<>""),ROW(K1))),"")
右方向・→下方向・↓
I1
入力規則:リスト▼:元の値:
=OFFSET($K$2,,MATCH(G1,$C$1:$F$1,0)-1,COUNTA(INDEX($C:$F,,MATCH(G1,$C$1:$F$1,0)))-1)

投稿日時: 18/11/02 15:11:15
投稿者: konitan0216

コメントあいがとうございます
すいません。説明不足でした。
「リストを崩したくない」「資格者はいくつもある」ということを入力したらよかったんでしょうか。
できる、できない、ということがまだわかりません。
自分の中ではプルダウンの設定で関数を入れたらどうにかできるのではとおもったのでそこしか書きませんでしたすいません。
 
教えていただいた関数を変えて自分の思うとおりに動きました、ありがとうございました。