歓迎ランチの最適化問題をエクセルで解く
TL; DR
「新入社員向けの歓迎ランチ、誰が出る？」を考えるのも最適化問題と呼べるらしい
最適化問題をきちんと解くには複雑な計算式が必要そうだが、エクセルでもまぁいける
みんな、歓送迎会は大事にしような
背景
私の働く10Xという会社では、フルリモートでも働ける体制が整っています。
フルリモートはいいんですが、たまに雑談みたいなコミュニケーションは難しいです。特に、会社に入りたての頃はそうです。なので、新入社員向けの歓迎ランチという制度があります。
入社してから1ヶ月間、週1であります。いい感じですね。
最近はGatherという、ゲーム盤のような画面の中で人とコミュニケーションがとれるツールを試しています。一旦(オンラインの)場に集って、そこから1対1でも話せるし、適宜グループに分かれることができます。ブレイクアウトルームに機会的に飛ぶのではなく、バーチャルに移動する様が見えることもあり、Zoomと比較しても、一旦集まってから分けたりしても気まずさが少なく、運営としても便利です。
もともとは、一旦集まってから、集まった人の中でラッコツールズというのを使って人力で分けていたりしたのですが、人数も増えてくる & どれくらい来るかわからないと、結構運営としては大変になります。なので、事前に組分けをすることにしました。
やりたいことはシンプルですが、いい感じに分けるという最適化問題と呼べるそうです。10Xにはチーム分けを整数計画問題で解く人材もいますが、まだ60人くらいの時点でも方程式が1,000個以上必要らしく、「人半分くらいにならない？」「頑張って採用したので無理です」という会話をしてたような してないような くらいなので、ちょっと難しそうでした。ということで、なんとなくエクセル*でできる範囲でやってみることになりました。
*厳密にはGoogleスプレッドシートを使用していますが、敬意を込めてエクセルと呼称しています。以下の「スプレッドシート」はすべてGoogleスプレッドシートを指します。なお、スプレッドシートはスプレッドシートであり、スプシではありません。
解きたいイシュー(課題)
新しく会社に入ったメンバーが他のメンバーと接点を持つ機会を設ける
なるべく業務の中で関わりがなさそうなメンバーとの接点を増やす
運用をなるべくラクにすることで、続くようにする
同じ部署では歓迎会等があったりしますが、100名近くになってきて、部署が異なるメンバーと自然と接点を持つということも難しいこともあります。なので、なるべく部署が違うメンバーと接点を持つ機会があると良いと考えました。
30分(かつ1対1ではない)の中でめっちゃ仲良くなるというのは難しいので、こうした場ではまず「接点」を増やして、「話したことある」人の分母を増やすことが大事かなと思います。その先業務で関わる際も、ちょっと進めやすくなったりしますよね。
また、最高の組み合わせ(ってなんやって感じですが)も大事ですが、こうしたものはつづくことが大事です。なので、運営側も負荷なく・属人化せず続く仕組みも重要な観点だと考えました。
方針とやったこと
ファイルはこちら
大きな方針として、最適な解(=組み合わせ)を導きだすための式を構築するというよりも、ランダムに組み合わせを作った上でそれを評価し、微妙だったらまた組み直す というアプローチをとっています。これは式を構築することよりもだいぶ原始的と思うので どうなんだろうかと思ってましたが、トライアスロン仲間に「それはそれで最適化問題の解法の一つ」と言われたので、まぁいいやって進みました。
以下、やったこと。
N月に新しく入るメンバーのリストを取得する
N-4~N-1月に入社している既存社員のリストを取得する
N-4月までに入社してる既存社員のリストを取得する
それぞれいい感じにランダムに散らし、新メンバーへ割り当てる(1人に対して3名x2週分=6名)
割り当てられた社員と新メンバーの所属部署の重複をカウントする
4.5を繰り返し、重複がいい感じに少なくなったタイミングで確定する
以下、具体説明。
使用している関数名は{太字}で示しています。関数好きな人は大元を参照ください。
1-3 メンバーのリストの取得
社内に毎月メンテがされている「社員名簿」というスプレッドシートがあったので、これを{IMPORTRANGE}で持ってきた上で、必要な情報のみ抽出しました。必要な情報は名前と入社月、部署(本部と部)なので、カラムとしては3つほどです。名前は名字と名前でカラムが分かれており、Slackネームも記載があったので [名字] & “さん (” & [slackネーム] & “)” とする、等の簡易な加工しています。
その上で、{FILTER}関数を用い、入社月をベースに
(a) N月に入社
(b) N-4~N-1月に入社
(c) N-4月以前に入社
の3つのグループにわけています。
(a)はさておき(b),(c)をわけた理由としては、入って間もないメンバーの方が、「何がわからない・困りうるか」についての感度が高いと考え、優先して接点を持てるようにするためです。
そもそもはじめはこの歓迎ランチを、入社して3-4ヶ月以内の人と新メンバー、という立ち位置にでしたが、ありがたいことに毎月入社するメンバーの数が増えてくると それでは間に合わなくなるため、対象を全社員に広げました。N-4かN-3なのかといった点はあまり強くこだわるものでもないので、そこは可変としています。
4. それぞれいい感じにランダムに散らし、新メンバーへ割り当てる
基本的には乱数をベースにやっています。
「いい感じに散らす」というのがスプレッドシートだとややひと手間かかり、{RAND}関数にて0-1の間のランダムな乱数を生じさせて、それを{RANK}関数で並べて、それを{VLOOKUP}を用いて1から並べた番号をフックに並べる、といったことが必要になります。
上記でいい感じに並べ直すのはできるのですが、なんとなく、並べ直すということが面倒だなあと思い、別の方法を考えました。
(a) 新メンバーの数だけグループをわけて、そのグループ番号を順番をランダムにした既存社員に割り振る
(b) 既存社員の順番をランダム化して、通し番号を振り、対応表に応じて人を割り振る
(a)は入社月順(=社員番号)順に並んでいるそのままに、{RANDBETWEEN}にて1から{COUNTA(新メンバー数)}をランダムに割り振り、その上で各グループ内で{COUNTIFS}を使って1から番号をふるという方法ですが、結局{RANDBETWEEN}でグループ番号をふる際、均等にはならない (つまり、新メンバーが3人の際に既存社員20人を1-3のグループに分けるが、3のグループ番号が1人にしかふられない)ということが起こりうることがわかりました。たいていの場合は大丈夫なのですが、人数が少ないとエラーになるので、これはちょっと微妙だなと思いました。
(b) は、対応表というシートを1枚増やすことになりますが、結果的にうまくいきました。
下記のように、大元から引っ張ってきた社員名簿を乱数で並べ替え、通し番号を振ります。
その後、別シートで対応表を作ります。
メインのシートに戻り、{INDEX}関数に{MATCH}関数を(2回)かませて、先程作成した対応表から数字を持ってきます。各新メンバー1名ずつに対して、通し番号が振られます。
それを、先程通し番号を振った社員を割り振ります。上記で記載したように、1-2人目は直近(例 4ヶ月以内)入った人から、3-6人目はそれより前の人から割り振ります。
これで、新メンバーそれぞれに対して、3名x2週分=6名の既存メンバーを割り振ることができました。
5. 割り当てられた社員と新メンバーの所属部署の重複をカウントする
先程割り当てた新規・既存メンバーの所属(本部、部)を{VLOOKUP}または {INDEX}と{MATCH} で入れます。その重複の数をシンプルに{COUNTIFS}で算出し、その数の合計を出します。この合計が少ないほど、新しく入ったメンバーにとっては、違う部署のメンバーと接する機会があることになります。
6. 4と5を繰り返し、重複がいい感じに少なくなったタイミングで確定する
この組み合わせ表は乱数ベースで動いているので、シートをいじるたびに組み合わせが変わります。本部と部の重複の回数を数えているので、これらが一番少ない(=違う部署のメンバーと接する機会が多い)ところで止めます。
限界
どこかのセルを動かしてしまうとすぐ組み合わせが変わるので、いい組み合わせになってもどこか触ってしまうとまた動いてしまいます。ただ、100人くらい(今の弊社)だと肌感10回に1回くらいは重複ゼロになったので、まぁなんとかなるかなと思っています
選ばれた回数がカウントできてない。何度か回した際に、完全にランダムなので、同じ人がまた選ばれるみたいなこともあります。とはいえ当日予定入って出られなくなった、等もあるし 期待値的には100人だと数ヶ月に1回くらいなので、まぁそこはええかなと考えました
そもそもスプレッドシートの作り方を文章で伝えるのが無r
おわりに
ということで、弊社ではバチバチに歓迎体制を整えています。
ちゃんとランチだけじゃなくて飲み会もします。
何より、そろそろスプレッドシートではしんどくなってきたので、チーム分けをシュッとやってくれる3人目の人財を求めています。
まってます。
ファイルはこちら
この記事が気に入ったらサポートをしてみませんか？