エクセル表からのデータ抽出

質問

エクセル2010で、画像のように、行（横）方向に「氏名」、列（縦）方向に保有資格が入った表が有るのですが、この表から資格名を選択したら有資格者の氏名と種類が表のどこかに抽出されるようにしたいのですが、何とかならないでしょうか？
詰まりB列で資格４をフィルターで選択したら、表のどこか（違うシートでもOK）に保有者名と資格の種類（級）等が抽出されるような仕組みを構築したいので宜しくお願いします。

更にもし可能であれば、氏名を選択（どこかに入力でもOK）したらその人の保有資格が一覧で抽出されれば理想です。

先ずは資格名から氏名と種類が抽出されればありがたいのですが。

表そのものを作り替えるのは最後の手段にしたいので配慮いただけるとありがたい。

回答No.7

HohoPapa · ベストアンサー

回答No.6への追記です。

先ほどは、資格保有列に重複が無い条件でした。
今回は、２件までの重複なら耐えられる計算式を考えてみました。

K3セルに
=IF(INDEX($B$3:$G$14,MATCH(2,INDEX(1/($B$3:$B$20=$K$1),0),1),COLUMN()-10,1)="","",INDEX($B$3:$G$14,MATCH(2,INDEX(1/($B$3:$B$20=$K$1),0),1),COLUMN()-10,1))
の計算式

K4セルに
=IF(MATCH(2,INDEX(1/($B$3:$B$20=$K$1),0),1)=MATCH(1,INDEX(1/($B$3:$B$20=$K$1),0),0),"",IF(INDEX($B$3:$G$14,MATCH(1,INDEX(1/($B$3:$B$20=$K$1),0),0),COLUMN()-10,1)="","",INDEX($B$3:$G$14,MATCH(1,INDEX(1/($B$3:$B$20=$K$1),0),0),COLUMN()-10,1)))
の計算式

これをそれぞれ埋め、必要数、右方向に複写し
K1セルに、抽出したい資格名を埋めます。

ポストしておきながら、ナンナンデスが、
後々のメンテナンスなどを考えると
もはや事実上の限界を超え、
使い物にならないんじゃないかと思います。

もしよかったら、参考にしてみてください。　m(_ _)m

お礼

いつもお世話になっております。　
色々試してみたいのですが、少し別の仕事にかかりきりになっておりお礼も遅れてしましい申し訳なく・・・

上記の内容は当方には絶対に無理ですし、万が一うまく行ったとしても危なくて使えないと考えます。
但しこの情報は、ワードにコピーさせてもらいましたのでいつか必要になった時に勉強してチャレンジさせてもらいます。
現在の業務で使用している色々なエクセルのテクニックを業務引き継ぎの資料として、1つのフォルダーにまとめて目次を作り始めましたので。
折角のいろんな人の知恵なので。

本当にありがとうございました。

取りあえず使いやすい表の再作成を検討中です。

回答No.6

HohoPapa

資格保有列に重複が無いという条件で良ければ

添付画像のように
K3セルに
=IF(INDEX($B$3:$G$14,MATCH(1,INDEX(0/($B$3:$B$20=$K$1),0)),COLUMN()-10,1)="","",INDEX($B$3:$G$14,MATCH(1,INDEX(0/($B$3:$B$20=$K$1),0)),COLUMN()-10,1))
の計算式を埋め、必要数、右方向に複写し

K1セルに、抽出したい資格名を埋めることで
期待の動作になると思います。

回答No.5

HohoPapa

期待の処理を行おうとする場合、
一般的なエクセルのスプレッドシート関数では厳しいと思いますし
少なくとも私には実現できません。

Pivotテーブルを使う手があるかもしれませんが
これはあくまで集計であって、抽出ではないので
なかなかフィットしにくいと思います。

常識的にはフィルターで！。
ということになるものと思いますが
これは、抽出ではなく、余分な行を非表示にする機能ですので
今回の期待には応えにくいだろうと思います。

すると、ＶＢＡが候補となり、
多くの場合、
Do...Loopの中でIF文やcopy命令を使うことになりましょう。

もし、VBAでの解を目指すのであれば、
もう一歩進んで、SQLをも使うという解があります。
若干ハードルが上がりますが、
マスターできると大きな武器になるはずです。

もしよかったら、
参考に供せるだろうコードを書きますので
取り組んでみてください。
なお、コードの途中に
Sheet1$B2:Z1000　の記述が登場します。
これは、抽出元データの格納シート名とその範囲です。
範囲は、大胆に広く設定しても大丈夫（なハズ）です。

Sub Select1()　 '保有資格を条件に抽出

　Dim cn As Object
　Dim rs As Object
　Dim strSQL As String

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　strSQL = ""
　strSQL = strSQL & " SELECT * "
　strSQL = strSQL & " FROM [Sheet1$B2:Z1000]"　　　 '列名込みの範囲を指定する
　strSQL = strSQL & " WHERE [保有資格] = '資格９'"　'ここに抽出する保有資格をセット
　rs.Open strSQL, cn

　ThisWorkbook.Sheets("Sheet2").Cells(3, 2).CopyFromRecordset rs
　　　　'抽出結果を格納するシート名、開始位置を指定

　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Sub

Sub Select2()　'氏名を指定し、資格の種類（級）等の埋まった行を抽出

　Dim cn As Object
　Dim rs As Object
　Dim strSQL As String

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　strSQL = ""
　strSQL = strSQL & " SELECT [保有資格],[田中]"　'抽出対象列名を指定
　strSQL = strSQL & " FROM [Sheet1$B2:Z1000]"　　'列名込みの範囲を指定する
　strSQL = strSQL & " WHERE [田中] is not null " '抽出条件を指定
　'strSQL = strSQL & " ORDER BY [保有資格] "　　　'ソートキー列　省略可
　rs.Open strSQL, cn

　ThisWorkbook.Sheets("Sheet3").Cells(3, 2).CopyFromRecordset rs
　　　　'抽出結果を格納するシート名、開始位置を指定
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Sub

回答No.4

imogasi

関数での要求だと思うが、あえてVBAでおすすめ。
仕事でエクセルを使うなら、VBA利用は必須だと、経験から思う。
本件程度では、下記のように簡単だから（コードが行数が少なくてできるから）勉強しては。
ーーー
例データ　Sheet１のA1:D7
保有資格 田中 山田 木村
資格１ １級 特級 ２級
資格２ ☆ ☆
資格３
資格４
資格５ ２級
資格６
'-------
標準モジュールに下記をコピペ
Sub test01()
s = InputBox("資格名")
r = Cells.Find(s).Row
MsgBox r '資格がみつかった行。確認テスト用。削除可
For c = 2 To 10　’１０の部分はデータにより列数は最大列数に修正する
If Cells(r, c) <> "" Then
MsgBox Cells(1, c) & " " & Cells(r, 1) & "　" & Cells(r, c)
End If
Next c
End Sub
ーーー
F5キーで実行。
実例、処理とも、最低限簡略化してあるので,改善は必要だが、関数でやっても、相当複雑な式になるタイプの課題。
別シートに結果を書きだすなら、 Cells(1, c) & " " & Cells(r, 1) & "　" & Cells(r, c)
の部分をシートに書き出す必要があるがここでは略。

お礼

ありがとうございます。

この表は将来的にどんどん情報が増えるので今のうちに表の構成から再度作成した方が色々使い勝手が良くなるように思えるので、関係者と相談中です。

ありがとうございました。

回答No.3

Chiquilin

> 表そのものを作り替えるのは最後の手段にしたいので
データベースの形に入力すれば フィルタひとつで解決するし 今作っ
てきる一覧表も そこから作れるのですから表の作りを見直した方が
いいと思いますよ？

Excelの使い方が下手な人は必ず「印刷物を作る」感覚で表を作って
しまいますが 一覧表を入力フォームにしたい人は 配列数式か VBAの
勉強が必要になります。それか Accessに移行するか。

その表を見直せない理由は何でしょうね。無理にやるというなら無理
やりな回答をしますけど それをあなたは自分でメンテナンスできるの
でしょうか。

お礼

お礼が遅くなりました。

仰る通りでもう少し考えてみます。
表を作り直すのが、今後も色々便利な気がしています。

回答No.2

Nobu-W

フィルタ～オプションを使っても可能かと思われますよっ
https://allabout.co.jp/gm/gc/297791/1/
上記サイトを参照して、３ペ～ジあるんですが勉強がてらにやってみる？

参考URL：

https://allabout.co.jp/gm/gc/297791/1/

お礼

お礼が遅くなってしまい申し訳ありません。

フィルターオプションは非常に参考になりました。

回答No.1

aokii

挿入→ピボットテーブルで、ピボットテーブルを使ってみてください。

補足

当方ピボットテーブルは使用したことが無いのですが、最初にピボットテーブルかと思い試行してみたのですが、どうも目的とする抽出する設定が分からず質問させてもらいました。

全国、都道府県別、男女別、年齢別等の人数の集計は出来るのですが（見本があるので当然ですが）、資格を選択したら、人の名前と持っているランクが一覧になるように出来るのでしょうか？

どうもこの表の構成が向いていないことは分かっているのですが。。。