Excel関数を利用して定員がある研修先へ優先順位・希望で社員を自動的に割り振るには?

l_bit202002071051170315 基礎コース

 

企業や学校において、その従業員や生徒を他の団体に研修として向かわせる・・・・

 

こういった場面はごく当たり前のようにありますし、また、ともすればその従業員たちを各団体へ人数を決めて振り分ける・・・といった立場になることもあるでしょう。

しかし、そうした時によく困るのが、従業員本人たちの希望や受け入れ先の定員がそれぞれ決まっている場合の対処です。
出来る限り従業員の希望に答えつつも各団体へ振り分ける作業は、経験されたことがある方にとっては非常に悩ましいお仕事となりますね。

 

では、エクセルを使って、このような時の手助けとなるであろう、「優先順位・希望を元に自動的に振り分ける」手法について参考事例を元にまとめてみましょう。

 

参考事例

とある企業にて、社員7名を外部の各企業へ研修に向かわせることにしました。
研修先を決める際に、以下の点についてあらかじめ実行・決定しています。

①研修期間は①~③の3つに分けられ、研修社員はその期間中の1回に、いずれかの企業に研修へと行ってもらう。

②社員別にアンケートを実施し、それぞれの第1~第3志望の研修先を聞き取りした。

③各社員には優先度として①~⑦を決定し、数字の若い順から志望先を優先的に振り分けるようにした。

④研修受け入れ先の企業は、A~Eまで5社あり、①の3つの研修期間中でそれぞれ受け入れることの出来る人数が決まっている。

このような時、エクセルにて自動的に社員の研修先を優先度に従って決定するように表や数式を組み立ててみましょう。

 

<構図>

l_bit202002071051170315

 

1.データの作成)各社員のアンケートと各企業の受け入れ可能人数から、元となる表を作成する。

社員のアンケートや、研修先の受け入れ可能人数を元に、まずは下のような2つの表を作成します。

50ecdcd499be5635de3d8a08cd44a95c-1

①研修先受入人数表

A~Eの各研修先にて受け入れることの出来る人数を、期間①~③ごとに入力しています。

②社員の研修先希望リスト

優先順位を割り振られた各社員ごとに、アンケートによる第1~第3の希望先を入力しています。
(その際、優先順位(D列)の昇順としています)

 

2.各企業の受け入れ可能な合計人数)SUM関数

研修先ごとで、期間全体でどれぐらいの人数が受け入れ可能なのかを知る為、I列に合計欄を追加し、F~Hの人数を合計させます。

=SUM(F5:H5)

ruikei5

 

3.各社の受け入れ可能人数の推移を入力)優先度の高い社員から研修先を割り当てた場合の、各企業の残り定員数を入力する。

下の図は、②の表左側に新しい項目列を作成し、それぞれの社員を上から順(社員優先度順)に、希望ごとに割り当てた場合の、各社の残り定員数を入力しています。

この、入力作業が今回の重要点になりますので、注意してください。

b

新しい項目列は次の通りにします。

A列 → 第1希望残
B列 → 第2希望残
C列 → 第3希望残
F列 → 研修先コード(決定)
G列 → 研修先名(決定)

 

A~C列の数字の入力方法について、少し分かりづらい為、どのように決定し、入力しているか、以下の(1)・(2)で解説します。

(1)
20171129_o0101

・「社員001」は、第1希望として「00AAH003」という企業を選択しています。

→赤枠

・この時点の「社員001」の第1希望先「00AAH003」の残り枠数はは、元の定員数であるセルI7の「2」となります。

→緑枠

 

(2)
image18040501

・「社員003」が第2希望として「社員001」と同じ「00AAH003」への研修を希望している為、セルB18(赤丸枠)は、「社員001」のセルA16(緑丸枠)から、1名差し引かれた人数の「1」となります。

 

このように、あくまで上から順(社員優先度順)に希望先の研修先を割り当てていった場合の人数を、あらかじめ入力するのですが、この考え方で全て逐一入力作業をするのは、少し骨が折れます。

そこで、次の(3)~にて数式での入力を見ておきましょう。

 

(3)-1 数式にて、1人目の「社員001」における、受け入れ可能人数(=合計人数)を自動的に表示させる。
67-0

①「社員001」が希望している研修先の残り受け入れ可能人数を取得する為、VLOOKUP関数にて次のように入力します。

=VLOOKUP(H16,$D$5:$I$9,6,FALSE)

これで、セルH16(第1希望先コード)を元に、上の表から受け入れ可能人数(=合計人数)を表示させます。

 

(3)-2 第2・第3希望も同様に数式を入力する。
02_01

同様の数式を入力して、受け入れ可能人数(=合計人数)を表示させますが、赤文字がそれぞれ第2・第3希望の研修先を参照していることに注意してください。

 

(4)-1 2人目の「社員002」における、受け入れ可能人数(=合計人数から前の人の選択分を差し引いた人数)を自動的に表示させる。
vlookup_2

①「社員002」が希望している研修先の残り受け入れ可能人数を取得する為、まず、VLOOKUP関数にて受け入れ可能な合計人数を参照します。

VLOOKUP(H17,$D$5:$I$9,6,FALSE)

次に、前の人がもしF列にて同じ研修先を選択していた場合に、何人がその研修先を選択しているかを数えます。

COUNTIF($F16:F$16,H17)

注意点は、「$F16:F$16」の複合参照です。

特に、最初の「$F16:~」の部分では、列を固定して、行には「$」記号を付けず固定していません。
この状態で、もしひとつ下にオートフィルをした場合・・・

$F17:F$16 」

と変化し、参照される範囲が下に広がっていくような設定になっているのです。
これで、「社員003」の時は上の2人分を、「社員004」の時は上の3人分を参照出来るようになります。

最後に、全体の合計人数(VLOOKUP)から、すでに選択された数(COUNTIF)を差し引けば数式の完了です。

= VLOOKUP(H17,$D$5:$I$9,6,FALSE) COUNTIF($F16:F$16,H17)

 

(4)-2 第2・第3希望も同様に数式を入力する。
02_03

同様の数式を入力して、受け入れ可能人数(=合計人数-選択された数)を表示させますが、赤文字がそれぞれ第2・第3希望の研修先を参照していることに注意してください。

 

(5) 全てオートフィルすれば、受け入れ可能人数を自動的に表示する欄が完成します。
%d0%b1%d0%b5%d0%b7-%d0%bd%d0%b0%d0%b7%d0%b2%d0%b0%d0%bd%d0%b8%d1%8f-10

これで全ての準備が整いました。
もし、研修先コードに入力があった場合、2人目以降の社員のA~C列の数字(残り可能人数)は表示が変化していくことになります。

 

4.研修先コードの決定)IF関数による自動判別

ここまで出来れば、あと一歩です。左側の数値を参考に、実際に「研修先コード(決定)」を決定する数式を作成しましょう。

excel-vlookup

①IF関数を使って、次のように数式を入力します。

=IF(A16>0,H16,IF(B16>0,J16,IF(C16>0,L16,”残なし”)))

この数式は上から読み解くとこのような意味があります。

まず、IF関数にてセルA16(第1希望残)が「0」人以上、つまりまだ残り枠があるかを確認します。もしそうであれば、第1希望の研修先で決定です。

=IF(A16>0,H16 ,IF(B16>0,J16,IF(C16>0,L16,”残なし”)))

そうでなければ(「o」の場合)、次に、セルB16(第2希望残)が「0」人以上かどうかを確認します。もしそうであれば、第2希望の研修先で決定です。

=IF(A16>0,H16 ,IF(B16>0,J16 ,IF(C16>0,L16,”残なし”)))

第2希望枠も「0」の場合、最後に、セルC16(第3希望残)が「0」人以上かどうかを確認します。もしそうであれば、第3希望の研修先で決定ですが、違っていれば、希望した研修先は全て受け入れ可能枠が残っていない為、「残なし」と表示させます。

=IF(A16>0,H16,IF(B16>0,J16 ,IF(C16>0,L16,”残なし” )))

以上がこの数式の意味となります。3つのIF関数を入れ子にすることによって、希望先の残り人数を上から順に参照させています。

 

5.研修先名の決定)IF関数による自動判別

研修先コードを求めた時と同じ手法でIF関数による「研修先名(決定)」の自動判別をさせましょう。

image3

①IF関数を使って、次のように数式を入力します。

=IF(A16>0,I16,IF(B16>0,K16,IF(C16>0,M16,”残なし”)))

数式の作り方は全く同じですが、赤文字の部分がそれぞれの「研修先コード」ではなく、「研修先名」に変わっていることに注意してください。

 

最後に、小見出し4と小見出し5入力した式をオートフィルすることによって、全ての社員の研修先が決まりました。

27-oneanova1

ただ、問題が残りました。
このままでは、社員006は「残なし」となり、研修先が決まっていない状態です。

そこで、この時点で他の研修先企業にて残りの受け入れ枠がないかを確認してみましょう。

 

6.各研修先の残り受け入れ可能人数を調べる)COUNTIF関数

下図のように、①の表の右側に新しい列項目を作成し、残りの枠数を確認しましょう。

022

 

 

①COUNTIF関数を使って、次のように数式を入力します。

=I5-COUNTIF($F$16:$F$22,D5)

これで、全体の受け入れ可能人数(=セルI5)から、すでに決まっている研修先(=セル範囲[F16:F22]からセルD5を数えた結果)を差し引いた、残りの枠数が表示されます。

入力した数式はE研修先の行までオートフィルしておきます。

 

表の完成

以上で、自動的に研修先を割り当てる表が完成しました。
残念ながら、「残なし」となってしまった社員には、各研修先の残り枠数(①表J列)を参考に割り当て直すようにいたしましょう。

image18100701_l

 

参考になりましたでしょうか・・・

このようなケースは「社員の割り振り」のみならず、ビジネスの場等で多く発生いたしますが、エクセルの数式を有効に使って効率よく、かつ、希望通りの決定が自動的に出来るようにしておきましょう。

Rate article