どちらからも検索して一覧表を作成できるエクセル表を作りたい

たとえば、社内データベースで資格の一覧表を作るとします。

Ａさん　日商簿記　２級、日商珠算　３級
Ｂさん　税理士、弁理士
Ｃさん　税理士、司法書士、日商簿記　１級
・・・

とした場合、
人で分類したり、資格の種類で分類したりしたいのです。
たとえば、人の検索なら、個人票を作れば済みますが、
資格を持っている人の検索なら少し工夫がいりますよね。

税理士資格をもっているのは、Ｂさん、Ｃさん。
というような個人票からの逆引きした表を作成するにはどうしたら良いでしょうか？
一からつくるので前提条件無しでどんな作り方でも良いです。
但し、社内のどのパソコンにも入っているエクセルであることが条件です。
他にソフトを使うことはできません。

No.5 回答者： banzaiA 回答日時： 2021/11/27 11:10

Excelのバージョンは何ですか？

No.4 回答者： goomania 回答日時： 2021/11/27 09:37

No.3さんの回答された内容がシンブルですし、一般的に考えてこれで良いとは思うのですが、ご質問者が

>人の検索なら、個人票を作れば済みますが･･･
とおっしゃっているように、このリストをどのように作成するか考えてみました。
(1)既に個人票があり、そこに保有資格が記入されているため、これを元にリスト化する
(2)個人票はなく、各人の保有資格は把握されていないため、保有資格申告書により申告してもらってリスト化する
のどちらかだと思います。
どちらにしても、リスト作成者は各人の資格をリストに入力することになります。
その際、No.3さんのお示しになった表だと、ある人の2つの資格を入力するのに、列を8列移動するというようなことが起こります。
資格の種類が増えると、ある人の2つの資格を入力するのに、列を20列移動するなどというケースもあるかも知れません。
その程度のことは大きな問題ではないのですが、できれば一名の2つの資格入力なら列1つの移動で済ませたいわけです。

そこで、添付画像のような表を考えてみました。
各人の資格保有数は最大10種類だと仮定し、「資格1～資格10」と見出しをつけています。これは適宜増減してください。(「ハケンの品格」の大前春子なら10では絶対足りないとは思いますが･･･)

添付画像③のように、sheet2のA1セルから下に向かって、組織として管理すべき資格名称を読みのあいうえお順に列挙します。(保有していても、【例えば、金融機関が「着付け技能士」の資格保有者を採用する場合などの】業務と無関係な資格をリスト化するのか否かを予め決めておきます)
そのうえで、sheet1の各人の資格名称の入力欄に「入力規則」を設定しています。「入力値の種類」は「リスト」を選択し、「元の値」には

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

という数式を記述しています。
こうすることで、sheet2のA列に新しい資格名称が追加されると、入力規則リストにも自動的に追加され選択できるようになります。
これで、リスト作成者は各人の資格をドロップダウンリストから選択入力すればよいことになり、左から右へ一つずつ列を進めて入力できます。

リストの「資格10」の右列であるL列に「有資格者」を設けています。
添付画像でいえばL5セルに

=COUNTIF($B5:$K5,"*"&$M$2&"*")

という数式を記述し、下方向へコピーしています。

これはM2セルに検索したい資格を文字で入力したとき該当文字を含む資格を持つ人には1以上の数字が表示されるようするためのものです。
つまり、M2に検索したい資格を入力し、「L列が1以上」でフィルターをかければ、有資格者が抽出されるというわけです。

添付画像①は「税理士」を検索したものです。添付画像②は「日商」を検索したもので、この状態でL列1以上でフィルターをかければ、「珠算」でも「簿記」でも資格名称の一部に「日商」があれば抽出されるということになります。

複合条件(二つの資格を有する者)の検索を必要とするときは、M2セルのとなりN2セルにもう一つ検索ワードの入力セルを作り、M列を「有資格者2」としてL5セルの右M5セルに

=COUNTIF($B5:$K5,"*"&$N$2&"*")

という数式を記述し、下方向へコピーすばよいと思います。
二つ以上なら同様に右方向へ「検索ワード」と「有資格者」列を増設します。
これでフィルターと組み合わせて複数条件検索にも対応できると思います。

No.3 回答者： fujillin 回答日時： 2021/11/26 18:43

こんにちは

自由に検索・抽出などをなさりたいのであれば、データベースを利用するのが宜しいと思いますけれど、「エクセル」との制限付きなので・・・

ご質問に示されている程度の内容（他にもあるのかも知れませんけれど）であるなら、添付図のような元データの作り方をしておけば、そのままでも一覧できるのではと思います。
資格の種類が多いようであれば、「ウィンドウ枠の固定」などを利用することでも、見やすくすることが可能です。

また、ある条件に合う人だけ（＝複合条件など）を抽出したいような場合でも、Filter関数等を利用すれば、比較的簡単に抽出することもできるでしょう。
https://support.microsoft.com/ja-jp/office/filte …

No.2 回答者： satoumasaru 回答日時： 2021/11/26 18:42

Excelの表ですよね。

資格の列をいれておいてフィルターで検索すれば事足りると思いますが？

No.1 回答者： zab_28258 回答日時： 2021/11/26 18:19

資格の区分を作る．．．1つの区分で１なら司法書士、2なら税理士．．．

または、司法書士区分や税理士区分を作って該当の区分に１を入れる