Excelで人員配置計画を最適化する分析モデル

人員配置計画を「感覚」から「数字」に変える—Excelでできる最適化の全体像

「今月は忙しそうだから、Aさんを多めに入れておこう」「この案件はBチームが得意だから任せよう」──人員配置はどうしても経験と勘に寄りがちです。もちろん現場感は大事。ただ、20代のサラリーマンが日々直面するのは、限られた人数・時間・スキルの中で、上司の期待（成果）と現場の負荷（働きやすさ）を両立させる現実です。ここを「感覚」だけで回すと、繁忙期に破綻したり、特定メンバーに負荷が偏ったり、説明責任（なぜこの配置？）に詰まったりします。

そこで役立つのが、Excelで作る人員配置計画の最適化モデルです。難しそうに聞こえますが、やることはシンプルで、ポイントは次の3つに分解できます。

• 需要：いつ・どの業務に・何人（何時間）必要か
• 供給：誰が・いつ働けて・どんなスキルを持つか（稼働可能時間）
• ルール：残業上限、スキル要件、担当固定、休暇など守るべき制約

この3つを数字で揃えた上で、Excelの機能（ピボット、条件付き書式、ソルバー）を使って、「最も条件に合う割り当て」を機械的に探すのが最適化の考え方です。具体的には、モデルの中に次の要素を置きます。

1. 意思決定変数：誰をどの業務（または日/週）に何時間割り当てるか
2. 目的関数：何を最優先で良くしたいか（例：不足時間を最小化、残業を最小化、コストを最小化）
3. 制約条件：守るべき条件（例：各人の上限時間、必要人数の充足、スキル一致、休暇日は0）

たとえば、目的を「不足（需要未達）を最小化」にすると、まずは穴埋め最優先の配置になります。一方で「残業を最小化」を強めれば、無理のない計画に寄ります。つまり最適化モデルは、あなたのチームが何を大事にするかを“言語化して数式化”する道具でもあります。上司に「なぜこの配置？」と聞かれても、目的と制約を示して説明できるのが大きな価値です。

本記事では、いきなりソルバーで解こうとせず、順序立てて進めます。まず2章で、最適化の成否を決めるデータ設計を作り、3章で可視化してどこが詰まっているかを把握。4章でソルバーを使い、Excelで回る最適化モデルに落とし込みます。そして5章で、机上の計画で終わらせないための運用（例外対応と改善サイクル）までつなげます。

ゴールは、「それっぽい表」を作ることではなく、毎週（毎月）更新しても崩れない、人員配置の意思決定が速くなる仕組みをExcelで手に入れること。次章から、まずは土台となるデータの設計を固めていきましょう。

まず整えるべきデータ設計—必要な項目・入力ルール・表の作り方

最適化で一番つまずきやすいのは、ソルバーの設定ではなくデータがバラバラで計算できないことです。Excelで人員配置を回すなら、先に「需要・供給・ルール」を表として分離し、入力ルールを固定しましょう。ここが整うと、3章の可視化も4章の最適化も一気にラクになります。

1) まず作るべき4つの表（シート）

• 案件/業務マスタ：業務ID、業務名、必要スキル、優先度、単価（任意）
• メンバーマスタ：社員ID、氏名、所属、スキル、時給/コスト（任意）、契約上限（任意）
• 需要（Required）：いつ・どの業務に・何時間必要か
• 供給（Availability）：誰が・いつ・何時間働けるか（休暇・会議含む）

ポイントは、需要と供給を「週×業務」「週×人」のように同じ粒度（週単位、日単位など）に揃えること。粒度がズレると、後工程で必ず破綻します。

2) 需要（Required）表の設計：列は固定、行で増やす

需要表は「横に日付が並ぶ表」より、最適化・ピボットに強い縦持ち（正規化）がおすすめです。

期間	業務ID	必要時間	必要スキル	優先度
2026-W04	WK001	40	SQL	High

• 期間：日付ではなく「2026-W04」など管理しやすいキーに（週次運用なら特に効きます）
• 必要時間：人数ではなく時間推奨（1人=8h換算より、欠勤/残業が扱いやすい）
• 必要スキル：後で制約にするなら、表記ゆれ禁止（プルダウン化）

3) 供給（Availability）表の設計：稼働可能時間を“先に確定”させる

「誰が忙しいか」は感覚でなく、供給表で確定させます。会議や既存案件を差し引いた純粋な割当可能時間を入れるのがコツです。

期間	社員ID	稼働可能時間	残業上限	休暇フラグ
2026-W04	MB012	32	5	0

• 稼働可能時間＝所定労働−固定予定（会議/教育/定例）−休暇
• 残業上限を列で持つと、後で「無理のない配置」を制約に落としやすい

4) 入力ルール：ズレを“人”ではなく“仕組み”で潰す

表があっても入力が崩れると終わりなので、次の3点は最低限入れてください。

• ID運用：業務名・氏名で結合しない（必ず業務ID/社員ID）
• 表記ゆれ防止：スキル・優先度はデータの入力規則（プルダウン）
• 空白禁止：必要時間/稼働可能時間は0を入れる（空白は集計・制約で事故ります）

5) 「割当表」はまだ作らない：2章のゴールは土台の完成

ここで急いでシフト表（誰をどこに入れるか）を作りたくなりますが、割当は4章で意思決定変数として作ります。2章の段階では、需要と供給が同じ期間キーで突合できる状態になっていることがゴールです。次章では、このデータを使ってピボットと条件付き書式でどこに不足が出ているか（ボトルネック）を可視化していきます。

可視化でボトルネックを特定—需要と供給のギャップ分析（ピボット／条件付き書式）

2章で「需要（Required）」「供給（Availability）」が同じ粒度（例：週）で揃いました。ここから先は、いきなり最適化に入る前にギャップ（不足/余剰）を見える化します。理由は単純で、現状の詰まりどころが見えないままソルバーを回すと、どの制約が効いて解けないのか、または解けても“現場感とズレた解”になりやすいからです。

ステップ1：ピボットで「需要側の山」を掴む

まずはRequired表から、どの期間・どの業務に負荷が集中しているかを出します。

• 行：期間
• 列：業務ID（または必要スキル）
• 値：必要時間（合計）
• フィルター：優先度（Highだけ表示できると便利）

ここでの狙いは「今週は総需要が多い」ではなく、何が原因で多いのかを切り分けること。業務ID別に突出が見えれば、後で「その業務に必要なスキルを持つ人が足りない」が仮説になります。

ステップ2：ピボットで「供給側の上限」を掴む

次にAvailability表を同じ期間軸で集計して、供給の天井を見ます。

• 行：期間
• 列：所属（任意）またはスキル（スキルを持たせているなら）
• 値：稼働可能時間（合計）

ポイントは「稼働可能時間」がすでに会議・休暇を引いた数字になっていること。つまり、この合計がそのまま「割当の上限」です。ここが曖昧だと、可視化しても“結局いける気がする”に戻ってしまいます。

ステップ3：ギャップ表を作る（需要−供給）

ピボットを2つ並べるだけでも雰囲気は掴めますが、意思決定に使うなら差分を1枚の表にします。おすすめは、期間をキーにして次の3列を作る形です。

期間	需要合計（h）	供給合計（h）	ギャップ（需要-供給）
2026-W04	180	160	20

式はシンプルでOKです（XLOOKUPやGETPIVOTDATAで参照）。ギャップがプラス＝不足、マイナス＝余剰。これで「そもそも総量として足りない週」と「総量は足りるがスキル偏りで詰まってる週」を分けて考えられます。

ステップ4：条件付き書式で“危険週”を一撃で炙り出す

20代の現場だと、細かい分析よりまず赤いところを潰すのが効きます。

• ギャップ列に条件付き書式（0より大きい＝赤、0以下＝緑）
• 需要合計にデータバーを入れて山の高さを直感表示
• 供給合計にもデータバーを入れ、需要との釣り合いを見る

これだけで「W04が不足20hで危険」というレベルの判断が、会議中でも即答できます。さらに余力があれば、不足が出ている期間だけをフィルターして眺めると、打ち手（前倒し、外注、優先度調整）が出やすくなります。

ステップ5：スキル別ギャップで“真のボトルネック”を見る

総量で足りていても炎上する原因の多くは、特定スキルの不足です。Required表を「必要スキル」、Availability側も「保有スキル（または対応可能スキル）」で集計し、期間×スキルでギャップを出してください。

ここで「SQLだけ毎週不足」「テスト工程だけ供給過多」などが見えます。4章の最適化ではこの結果を踏まえて、不足が大きいスキルに優先的に人を寄せる（目的関数や重み付け）設計に繋げられます。

可視化のゴールは、グラフを綺麗に作ることではありません。不足が“いつ・どの業務/スキルで・何時間”起きているかを特定し、次章のソルバーで解くべき問題をクリアにすること。ここまでできれば、最適化は「闇雲な自動化」ではなく、狙い撃ちの改善になります。

最適化モデルを組む—目的関数・制約条件・意思決定変数の作り方（ソルバー活用）

3章で「いつ・どの業務/スキルが・何時間不足しているか」まで見えました。ここからは、その不足を埋める割当をExcelに“探させる”パートです。ポイントは、ソルバーの画面操作より先に、シート上で意思決定変数・目的関数・制約条件が計算できる形に落とすこと。

1) 意思決定変数：割当の“箱”を作る（誰×期間×業務）

新しく「Allocation」シートを作り、行に期間、列に社員ID×業務ID（または業務IDだけ）を置きます。セルの中身が意思決定変数です。

• x(期間, 社員, 業務) = その週にその人をその業務へ割り当てる時間
• まずは現実的に、期間は週、業務は主要なものだけに絞る（変数が増えるほど重い）

最初の実装では「社員×業務」の2次元でもOKです（期間を1週に固定）。慣れてきたら期間軸を足していくと運用に耐えます。

2) 目的関数：何を最優先で“最小化/最大化”するか

人員配置で扱いやすい目的は次の2つです。

1. 不足時間（未充足）を最小化：炎上を止める設計
2. コスト/残業を最小化：余裕が出た後の改善設計

おすすめは、まず「不足を最小化」を軸に置くこと。不足を表すために「Shortage」列（補助変数）を作ります。

• 各（期間×業務）で 割当合計 + Shortage = 必要時間
• Shortageは0以上（不足があるときだけ増える）

これなら「不足がある＝制約違反」ではなく、「不足がある＝目的関数で重く罰する」にでき、必ず何かしら解が出やすいのが実務向きです。目的セルはシンプルに、Shortageの合計（優先度があるならHighは重み2倍など）にします。

3) 制約条件：守るべきルールを“数式”にする

よく使う制約はこの4つです。2章の表設計（ID・稼働可能時間・残業上限）がここで効いてきます。

• 稼働上限：各（期間×社員）で割当合計 ≤ 稼働可能時間 + 残業上限
• 需要の定義：各（期間×業務）で割当合計 + Shortage = 必要時間
• 休暇日は0：休暇フラグ=1の期間は、その社員の割当合計 = 0
• スキル不一致は0：その業務に必要なスキルを持たない社員は、その業務列を0固定

スキル制約が一番事故りやすいので、先に「対応可否(flag)」の表を作ると安定します（社員ID×業務IDで0/1）。Allocationの該当セルはx ≤ M×flag（Mは大きい数）にすると、flag=0のとき自動で0に縛れます。

4) ソルバー設定：変数・目的・制約を“そのまま登録”する

シート上の計算ができたら、ソルバーは作業が早いです。

• 目的セル：不足（Shortage合計）を「最小化」
• 変数セル：Allocationの範囲（x）＋Shortage範囲
• 制約：上で作った上限/等式/0固定を追加
• 解法：基本はSimplex LP（線形で組めるならこれが速い）

時間単位の割当なら、変数は「0以上」にして連続値で解くのが現実的です（1時間単位に丸めたい場合は、最後に丸めて微調整）。どうしても「1人を入れる/入れない」のシフト型にしたいなら、0/1の整数計画になりますが、重くなるので最初は避けるのが無難です。

5) 解が不自然なときの見直しポイント（よくある詰まり）

• 解が出ない：需要を等式で縛っている／稼働可能時間が過小（会議控除が二重）
• 特定の人に偏る：目的が不足最小化だけだと起きやすい → 「個人の割当の偏り」を罰する項を追加
• 優先度が反映されない：不足に重み（High=3、Mid=2…）を掛けて目的関数へ

ここまでできると、あなたのExcelは「表」から意思決定マシンに変わります。次章では、このモデルを毎週回しても破綻しないように、シミュレーションや例外対応（急な休み・優先度変更）を含めた運用の型に落としていきます。

運用できる仕組みに落とす—シミュレーション、例外対応、改善サイクルの回し方

4章で最適化モデルが組めても、現場で使われない原因はだいたい同じです。「急な変更に弱い」「結果が読めない（怖い）」「毎週の更新が面倒」。ここでは、Excel最適化を“机上の正解”で終わらせず、毎週回る運用に落とす型を作ります。

1) まず「シナリオ」を用意する：通常／悪化／改善の3点セット

ソルバーは1回解いて終わりではなく、条件を変えて打ち手を検討するのが本領です。おすすめは入力を3パターンに分け、ボタン1つで切り替えられる形。

• 通常：今のRequired / Availabilityをそのまま
• 悪化：休暇+1名、需要+10%など（炎上耐性チェック）
• 改善：外注10h追加、優先度見直し（施策の効果測定）

実装はシンプルでOKです。Required/Availabilityに「シナリオ」列を追加し、ピボットや集計がその列で切り替わるようにするだけで、“もしも”が検証できる計画になります。

2) 例外対応は「手直し」ではなく「ルール追加」で吸収する

運用で必ず起きるのが、急な休み、優先度変更、担当固定、引き継ぎ不可などの例外です。ここでAllocation表を手で直すと、次週以降に再現できません。おすすめは、例外を制約（またはペナルティ）として追加してモデルに持たせること。

• 急な休み：Availabilityの稼働可能時間を0に（4章の「休暇日は0」制約が効く）
• 担当固定：該当セルを下限で縛る（例：x ≥ 10h）
• 引き継ぎNG：対応可否flagを0にして割当不可へ
• 「この人を優先したい」：目的関数に軽いコストを入れ、採用されやすくする

コツは、例外を「気合」で吸収しないこと。次回も同じ判断ができる形に落ちていれば、あなたの作ったモデルは“属人化しない武器”になります。

3) 結果の出し方を整える：現場向けは「合計」と「赤信号」だけでいい

最適化結果は細かい表ほど読まれません。20代の現場で刺さるのは、意思決定に必要な最小限のビューです。

• 不足時間（Shortage）：期間×業務で一覧。条件付き書式で赤表示
• 個人負荷：期間×社員で「割当合計」と「上限超過」を表示
• 優先度Highの未充足だけ：フィルターで上司説明が一瞬になる

さらに、結果シートの冒頭に「結論サマリ」を置くと運用が回ります（例：不足合計20h、ボトルネックはSQL、打ち手候補は外注10h or 優先度調整）。

4) 改善サイクルは「目的関数の見直し」から回す

運用を始めると、「不足は減ったけど偏りがキツい」「残業が増えた」など次の課題が出ます。ここで重要なのが、改善のレバーは人ではなく目的関数と重みだと理解すること。

• 偏りが問題：個人の割当が平均からズレるほど罰する項を追加
• 残業が問題：残業時間にコストを乗せて最小化
• 優先度が最重要：High不足に大きな重み（3〜5倍）

毎週は「データ更新→ソルバー→サマリ確認」だけ。月1回は「目的関数の重み調整→ルール追加」をやる。この二階建てにすると、回しながら強くなるモデルになります。

5) 最後に：運用の勝ち筋は「更新が楽」なこと

続く仕組みは、正確さより手軽さで決まります。Required/Availabilityの入力をテーブル化し、期間キーで追加するだけにする。例外はルールとして蓄積する。結果は赤信号だけ一目でわかる。ここまで揃うと、人員配置は「頑張って作る資料」ではなく、毎週の意思決定を速くするプロセスに変わります。