名簿を作ろう
世の中の潮流からすると、ようやく感があるけど、まぁなった。
吉野と言えば、ジェンダーに疎いのでこんな始末。
最近、ジェンダーレスの流れから名簿は男女混合になった。
— よしの とおる (@tohruyoshino) 2020年3月21日
個人的には保健名簿が男女別になるので、最初から男女別のが楽なんだけどなと思っていたが、それだとトランスジェンダーとかに対応できないのかと思い至ったので、やっぱり混合やな。
混合名簿は構わないが、
保健関係とか赤白振り分けとかの関係もあるので、男女別の名簿も欲しい。
なので今回は、
混合名簿に男女の属性をつけておくだけで男女別名簿が作れるようにする。
サンプル名簿
今回のサンプル名簿はこんな感じ。
左に番号があって、なんか名前が並んでいて、それから男女が振られている。
名前のところは、普通五十音順なんだろうけど、
今回は思いついた順番に並べているので、そこはご容赦を。
新しいナンバリングをする
とりあえず思いついた順でやってみる。
今は男女混合なので、各自に通し番号が付いているが
これに「男の１番」「女の１番」みたいな感じの番号が付けばいいはず。
ということで、番号をつけてみた。
COUNTIF関数は「範囲内で検索条件に合うものを数える」という関数。
フランキーの行の場合、「=COUNTIF($C$2:C9,C9)」だが、
「C2からC9までの中でC9（男）を数えて」という命令になるので、
フランキーまでの「男」を数えてくれる。
画像だと見えないが、フランキーは６番目になっているので
フランキーより前には５人の男がいる。
$C$2を固定しておくことで、オートフィルをかけた時も
一番上から自分までという範囲設定ができる。
コニスの場合はこんな感じ
男女とナンバリングをくっつける
男女別のナンバリングができたが、これをそのまま参照すると
「１番」が２人になっていまう。
なので「男１」「女１」と区別をする。
これは結構簡単でこんな感じにする。
ウソップの部分の場合、「＝C５＆D５」とすると、
「男（C5）」と「３（D5）」がくっついた「男３」という文字列が出せる。
文字列を足すには基本的に「＆」でつないであげてばいい。
応用編
今回はD列とE列で分けて、新しいナンバリングをしたが、
１回でもE列のような表示はできる。
D列にはもともとCOUNTIF関数が入っているので、
「＝C6＆D6」のD6の部分にCOUNTIF関数を代入する感じ。
折角なので、こちらのスマートな方を残して次へ行く。
（以降、D列とE列を削除するので、F列がD列に来ます。）
伝家の宝刀「vlookup」封じ
ここまで行けば後はvlookup関数で行けると思いきや、
検索したい文字列は検索値の左側にあるので、vlookupが使えない。
vlookupは範囲の一番左の列からを参照するので、
この場合「ルフィ」と入力して「男１」を自動で出るシステムになってしまう。それは逆やん。
打開案１：「男１」を名前の左へ持ってくる
要は「男１」とかの列が名前の左にあればいいので、
そもそも名前の左に「男１」列を作る。
ハイ。簡単。後はVlookup関数で行ける。
そもそも名簿エクセルって毎年コピペして使うケースが多いので、
性別列の右に入れておくと、コピペで消される恐れがあるんだよね。
そういう意味でも、こちらの方法はとても分かりやすく、合理的。
打開案２：Match関数とIndex関数を組み合わせる
Match関数とIndex関数を組み合わせると、
Vlookup関数と同じ働きをすることができる。
しかも検索列がどこでも良いというVlookup関数の上位互換とも言える。
具体的には「=INDEX(検索範囲,MATCH(検索範囲,検索値))」という入れ子構造になる。
分かりにくいので、少し細かく解説していく。
まずINDEX関数は「=INDEX（検索範囲 , 番号）」という形になっており
「検索範囲の中で上から何番目の文字列を教えて」という関数だ。
F2には、F３に示されたような関数が入っている。
「=INDEX(C2:C21,3)」なので、
「C２からC２１の範囲で、上から３番目を教えて」という形になる。
上から３番目はナミなので、ナミが表示される。
後は「ナミ」は「女１」なので、
「”女１”は３番目だよ」と数字を教えてくれる関数が必要になる。
それがMatch関数。
Match関数は「=MATCH（検索値 , 検索範囲）」という形になっており、
「検索値は、検索範囲の何番目だよ」と教えてくれる。
なので「=MATCH(E4,B2:B21,0)」と入れると、
「E4の文字列は、B2からB21の中で3番目にあるよ」と返してくれる。
表示上は「３」とだけ出てくる。
因みにMatch関数の最後の「0」は、
検索文字列が元の文字列と完全に一致しているかの判定をする。
まぁ０で良いんじゃないかな。
組み合わせると、こんな感じ。
F２のルフィの中にはF３のような関数が入っている。
実際には、こんな感じ。「=INDEX(C2:C21,MATCH(E2,B2:B21,0))」
具体的に見ていくと、
「【E2（男１）はB２からB２１の中で１番目で】、
C２からC２１で【1番目】は”ルフィ”だよ」となっている。
とりあえず、「男１」から「ルフィ」などを引っ張ってくる方法はできた。
男女別名簿を格好良く作る
じゃあ実際に名簿を作ってみる。
こんな感じで「男1」と書いて名簿だと楽だが、ちょっとカッコ悪い
できれば、こんな感じで番号だけで行きたい。
とりあえず分かりやすいから、Vlookup関数で行ってみるか。
Vlookup関数らしい感じで設定したが、
検索したセルの”G2”には「１」しか入っていないから正しく表示ができない。
これを何とか「男１」にしたい。
なので検索値を「G2」から「”男”＆G２」にしてみる。
やったー！
「ルフィ」って表示された―！
因みに「”男”＆G2」ではなく、「””」を取った「男＆G2」ではダメ。
文字列は「””」でくくらないといけないというルールがあるので。
後は少し調整して、できあがったのがこちら。
エラーメッセージがカッコ悪いので、IFERROR関数で消す。
これで完成かなー。
説明にすると長いけど、割とあっさりできたね。
振り返り
とりあえずできたが「男１」とかの列を足さないといけないのが少し気にかかった。
もう少し工夫すれば消せるかも？
あと今回は男女で列が分かれているケースだけど、
連続しているケースもあるわけで１列で表示するにはどうするか検討の余地があり。
とりあえず今日はここまで。
