- ベストアンサー
エクセル表からのデータ抽出方法とは？
- エクセル2010で、行方向に氏名、列方向に保有資格が入った表から資格名を選択したら有資格者の氏名と種類が抽出される仕組みを構築したい。
- 資格４を選択したら保有者名と資格の種類が抽出される仕組みを構築したい。
- 氏名を選択したらその人の保有資格が一覧で抽出される仕組みを構築したい。
- akira0723
- お礼率68% (651/954)
- Excel（エクセル）
- 回答数7
- ありがとう数4
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
回答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
その他の回答 (6)
- HohoPapa
- ベストアンサー率65% (455/693)
期待の処理を行おうとする場合、 一般的なエクセルのスプレッドシート関数では厳しいと思いますし 少なくとも私には実現できません。 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
- imogasi
- ベストアンサー率27% (4737/17069)
関数での要求だと思うが、あえて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) の部分をシートに書き出す必要があるがここでは略。
お礼
ありがとうございます。 この表は将来的にどんどん情報が増えるので今のうちに表の構成から再度作成した方が色々使い勝手が良くなるように思えるので、関係者と相談中です。 ありがとうございました。
- Chiquilin
- ベストアンサー率30% (94/306)
> 表そのものを作り替えるのは最後の手段にしたいので データベースの形に入力すれば フィルタひとつで解決するし 今作っ てきる一覧表も そこから作れるのですから表の作りを見直した方が いいと思いますよ？ Excelの使い方が下手な人は必ず「印刷物を作る」感覚で表を作って しまいますが 一覧表を入力フォームにしたい人は 配列数式か VBAの 勉強が必要になります。それか Accessに移行するか。 その表を見直せない理由は何でしょうね。無理にやるというなら無理 やりな回答をしますけど それをあなたは自分でメンテナンスできるの でしょうか。
お礼
お礼が遅くなりました。 仰る通りでもう少し考えてみます。 表を作り直すのが、今後も色々便利な気がしています。
- Nobu-W
- ベストアンサー率39% (725/1832)
フィルタ～オプションを使っても可能かと思われますよっ https://allabout.co.jp/gm/gc/297791/1/ 上記サイトを参照して、３ペ～ジあるんですが勉強がてらにやってみる？
お礼
お礼が遅くなってしまい申し訳ありません。 フィルターオプションは非常に参考になりました。
- aokii
- ベストアンサー率23% (5210/22062)
挿入→ピボットテーブルで、ピボットテーブルを使ってみてください。
補足
当方ピボットテーブルは使用したことが無いのですが、最初にピボットテーブルかと思い試行してみたのですが、どうも目的とする抽出する設定が分からず質問させてもらいました。 全国、都道府県別、男女別、年齢別等の人数の集計は出来るのですが（見本があるので当然ですが）、資格を選択したら、人の名前と持っているランクが一覧になるように出来るのでしょうか？ どうもこの表の構成が向いていないことは分かっているのですが。。。
お礼
いつもお世話になっております。 色々試してみたいのですが、少し別の仕事にかかりきりになっておりお礼も遅れてしましい申し訳なく・・・ 上記の内容は当方には絶対に無理ですし、万が一うまく行ったとしても危なくて使えないと考えます。 但しこの情報は、ワードにコピーさせてもらいましたのでいつか必要になった時に勉強してチャレンジさせてもらいます。 現在の業務で使用している色々なエクセルのテクニックを業務引き継ぎの資料として、1つのフォルダーにまとめて目次を作り始めましたので。 折角のいろんな人の知恵なので。 本当にありがとうございました。 取りあえず使いやすい表の再作成を検討中です。