【Excel】関数だけでグループ化

Yahoo!知恵袋の質問に答えた中から、記事にします。名簿があって、それをもとに出勤予定者一覧をランダムに作ります。そのランダムな予定者一覧からグループごとにリストアップするにはどうするかといった内容の質問でした。

質問

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14277250214

B列に名前 C列にL1の表の通りに基本となるグループ番号が割り当てられています
E列にその日出勤する人が10～15人おり、出勤者をE15の表のようにグループごとにまとめて表示したいです。
まとめる際「１」「２」「４」などという数字は必要なく、名前だけわかれば十分です
（質問の際にあった方がわかりやすいかもと思って表示しているだけです）
ーー

基本となるグループは11グループとなってるのですが、固定出勤者とアルバイトの出勤で変動があるものの、だいたい毎日５～６グループで固めることができています
祝日は多くて９グループまで増えることがありますが、基本的には６グループが最大になると思います

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14277250214

この質問に添えられていた画像がこちら↓

回答

私の回答です↓

質問者様のやりたいことを関数だけで実現するためには、ワークシート上に作業領域が必要です。

私は試しにF1からQ12までを作業領域として使いました(月曜日出勤者分だけで、です。月曜日分だけでもこれだけ必要となる)。火曜日出勤者分から金曜日出勤者分まで全部を解決するためにはもっとたくさんの作業領域が必要となると思います。

最初に、F3からF12までVLOOKUP関数でグループ番号をC列からひっぱってきます。
このとき、F3に私が入れた関数は次のとおりです。

=VLOOKUP(E3,$B$1:$C$56,2,FALSE)

次に、G2に１と入れます。
H2に2と入れます。
その後、G2：H2を選択した状態で右にオートフィルし、11までの連番を入れます。

G3にはCOUNTIF関数とIF関数で次のような関数を入れます。

=IF(G$2=$F3,COUNTIF($F$3:$F3,G$2),””)

これをダブルクリックで下までオートフィルし、さらにQ列まで選択してCTRL+R(フィルレフト)します(一気に数式をコピー)。

この数式は、月曜日出勤者の中に各グループ番号がいくつ入っているかを把握するためのものです。

E14からの領域は質問者様は月曜日出勤者となるグループが存在するグループだけを抽出して表示していましたが、私のやり方だとどうしても、存在しないグループもいったん表示しないといけません。

どういうことかというと、まず1から11まで、E15からO15までのセルに連番を入力しておきます。

その番号の下に、関数で抽出した名前が表示されるというものです。

関数は、INDEX関数とMATCH関数を組み合わせて使います。
E16に入れた関数は、次のようなものです。

=INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0))

ただし、これだけだと対象データがないときに#N/Aエラー表示となりますので、IF文とERROR関数を組み合わせてエラーのときは何も表示しないようにします。そうすると結局E16には次のような長い関数が入ります。

=IF(ISERROR(INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0))),””,INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0)))

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14277250214

長いですね＾＾；読まなくていいです。↑に書いたのと同じことを、次の解説で細かく説明しています。

解説

VBAを使わず、関数だけでデータを抽出するには、ちょっとめんどうくさいです。

私は頭がごちゃごちゃしてしまうので、作業領域を用意してデータを順番に整理しながら抽出する方法をとります。

作業領域

まず作業領域をどこにするか決めます。今回は、月曜日出勤者の隣の領域を使うことにしました。

もし作業領域として使うのに差しさわりがあるのであれば、別シートを作って、そのシートを作業領域として使えばよいです。

今回は分かりやすいように同一シート内としました。

また、作業領域は印刷範囲外に設定したり、文字色を白くして目立たなくしたりしますが、今回は分かりやすいように標準のままとしました。

VLOOKUP関数でグループ番号を把握

最初に、VLOOKUP関数で月曜日出勤者のグループ番号をひっぱってきます。

グループ番号の情報源はC列です。

VLOOKUP関数の使い方は「VLOOKUP　関数　Excel」で検索してください(他力本願)。

COUNTIF関数でグループ番号が何個あるか把握

次に、COUNTIF関数でグループ番号が月曜日出勤者の中に何個含まれているか把握します。

このとき、どのグループが含まれているか分からないので、最初から1グループから11グループまで全部調べるということに注意してください。

だからどうしても作業領域が必要となるのです。

例えば、月曜日出勤者を見ると、2グループはゼロです。しかし、5グループは3人もいます。

これを関数だけで把握するためには、まずはCOUNTIF関数で月曜日出勤者に含まれるグループ番号に

１、２、３・・・と順番に番号を振っていきます。

そのための関数は、

図のように、F3：F3の範囲で、「１」というグループを数えるという内容になります。

これをコピーしますので、ちょうどいい具合に絶対参照にします。

「絶対参照って何？」という方は、「Excel　絶対参照」で検索してください(他力本願)。

絶対参照を設定するのは、コピーしやすくするためです。ちなみに、上の図の絶対参照の設定は間違っています(すみません)。正しくは、COUNTIF($F$3:$F3,G$2)です。

ダブルクリックでオートフィルします。

これをダブルクリックでオートフィルしたところがこちら↓です。

1グループ者の名前があるところだけ1と表示されています。

これを右側の2グループ以上のセルにもコピーします。

範囲を選択してCTRL+Rを押します。

例えば、5グループの場合、上から樋口さんが１、石井さんが２、古山さんが3というように連番が振られました。

INDEXとMATCHで抽出

ここまできたら、最後はINDEXとMATCHで名前を抽出することができます。

ここでまた問題が。

この段階に来ても、やはりどのグループの人が月曜日出勤者となったかはExcelには分からないです。

だから、最初から11グループまで全部のグループの人が表示できるだけの領域を用意しておきます。

ここに関数を埋め込んでいきます。

使うのはMATCH関数とINDEX関数です。

MATCH関数で連番の存在する行番号を把握し、INDEX関数でその行番号をもとに名前を把握します。

使い方は、「Excel　MATCH関数　INDEX関数」で検索してください(他力本願)。

ここでは私が埋め込んだ関数だけ紹介しておきます。セルE16に埋め込んだ関数です。

=IF(ISERROR(INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0))),””,INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0)))

重要なのは、INDEX($E$3:$E$12,MATCH($D16,G$3:G$12,0))の部分だけです。ただこれだけだと、次の図のように、

値がなかったときに#N/A表示が続いて見づらいので、IF文とERROR関数を組み合わせています。

おまけ

今回作成したファイルです。

ZIPにして置いておきます。

https://kn-sharoushi.com/wp-content/uploads/2023/03/20230323xhie_ランダムな名簿をグループ化したい.zip

蛇足

上記は分かったような説明をしていますが、実際は、

最初にINDEXとMATCHでグループ分けをしようとするけど挫折

→3件ある5グループの人が1件しかとってこれない！

→いきなりINDEXとMATCHIではだめだ。いったんグループごとに連番を振らないといけないな・・・。

→てことは作業領域がどうしても必要だな。

・・・という風に思考をたどっています。

こういうのは、試行錯誤というか、壁にぶつからないと答えが出ない系の問題ですね。試行錯誤しているときは脳が活性化しておもしろかったです＾＾