エクセルのシフト表から月またぎの1週間を抽出するには、月ごとの表を同じブックにまとめ、日付に応じて参照するシートを切り替えます。シート名を「2026-11」「2026-12」のようにそろえ、INDEX・MATCH・INDIRECTを組み合わせると、2つの月間表から勤務記号を取り出せます。
11月30日〜12月6日なら、11月30日は11月のシート、12月1日以降は12月のシートを参照します。配布Excelの「週間抽出」シートに開始日を入れ、8人分の勤務と日ごとの人数を表示する手順を、架空の記入例で説明します。
月またぎの1週間をエクセルの月間シフト表から抽出するには
用意するのは、抽出したい期間(週)を含む前月・当月の月間表と、週の開始日です。月間表を修正用の原本、週間表を確認・掲示用の資料として使うシフト管理なら、勤務の変更は月間表へ入力します。週間表には参照式を残しておけば、元表を更新するだけで週間表にも反映でき、手作業の転記による入力ミスを減らせます。
自動化できるのは、入力済みの勤務記号の表示と人数のカウントです。誰を早番・遅番へ割り当てるかは、月間表側で決めてください。シフト管理システムを導入していない職場でも、Excelの数式だけで作れます。
月またぎ週の抽出Excelをダウンロード(.xlsx)配布Excelは、「2026-11」「2026-12」「週間抽出」「使い方」の4シート構成です。2か月分の記入例と抽出式が入った、マクロなしの無料のファイルで、登録は不要です。
前提:月ごとのシートとシート名の規則(yyyy-mm)
月間表をまだ作っていない場合は、エクセルでシフト表を作る方法で月間表の作り方を扱っています。別ファイルで月間表を管理している場合は、対象シートをコピーして同じブックへまとめます。そのうえで、各月のシートを次の配置にそろえてください。
| 項目 | 月間シートの位置・入力内容 |
|---|---|
| 年 | B2に「2026」 |
| 月 | D2に「11」または「12」 |
| 日付 | 4行目。C列が1日、D列が2日、以後1日につき1列 |
| 氏名 | B7:B14に8人分 |
| 勤務記号 | C7:AG14。C列が1日、AG列が31日 |
| シート名 | 「2026-11」「2026-12」 |
11月は30日までなので、31日分の列は使いません。途中の日の列を削除せず、1日から月末までを連続した列に置くことが、この式の前提です。日付の見出しを検索する式ではないため、平日だけの表や21日始まりの表へそのまま使うと、違う日の勤務を取り出すおそれがあります。
シート名は半角数字と半角ハイフンで統一します。このルールは、後述のTEXT関数が日付から変換する文字列と一致させるためのものです。B2やD2の年月を変えても、シート名は自動では変わりません。月間表の年月とシート名の両方を確認しましょう。
例:11月30日〜12月6日を11月・12月のシートから取り出す
例は、営業時間10:00〜20:00、従業員8人の架空の駅前の雑貨店です。氏名と勤務予定も架空で、記号は「早=10:00〜19:00」「遅=11:00〜20:00」「休=休み」とします。時刻は記号の意味を示すもので、ここでは実働時間を計算しません。
まず、抽出元となる2つの月間表の該当部分を見ます。11月30日は「2026-11」のAF列、12月1日〜6日は「2026-12」のC〜H列です。次の右端の欄は、左から12月1日、2日、3日、4日、5日、6日の順に並んでいます。
| 氏名 | 11月の元表:11/30 | 12月の元表:12/1〜12/6 |
|---|---|---|
| 佐藤 | 早 | 早・休・早・早・早・休 |
| 鈴木 | 遅 | 休・遅・遅・遅・休・遅 |
| 高橋 | 早 | 休・早・休・早・早・休 |
| 田中 | 休 | 遅・休・遅・休・遅・遅 |
| 伊藤 | 休 | 休・休・休・遅・早・早 |
| 渡辺 | 遅 | 遅・遅・休・休・休・休 |
| 山本 | 休 | 早・早・休・遅・遅・遅 |
| 中村 | 休 | 休・休・早・休・早・休 |
「週間抽出」のC3に「2026/11/30」を入れると、C〜I列に次の結果が並びます。6行目の参照シート名は、C列だけが「2026-11」、D〜I列は「2026-12」です。
| 氏名・集計 | 11/30(月) | 12/1(火) | 12/2(水) | 12/3(木) | 12/4(金) | 12/5(土) | 12/6(日) |
|---|---|---|---|---|---|---|---|
| 佐藤 | 早 | 早 | 休 | 早 | 早 | 早 | 休 |
| 鈴木 | 遅 | 休 | 遅 | 遅 | 遅 | 休 | 遅 |
| 高橋 | 早 | 休 | 早 | 休 | 早 | 早 | 休 |
| 田中 | 休 | 遅 | 休 | 遅 | 休 | 遅 | 遅 |
| 伊藤 | 休 | 休 | 休 | 休 | 遅 | 早 | 早 |
| 渡辺 | 遅 | 遅 | 遅 | 休 | 休 | 休 | 休 |
| 山本 | 休 | 早 | 早 | 休 | 遅 | 遅 | 遅 |
| 中村 | 休 | 休 | 休 | 早 | 休 | 早 | 休 |
| 出勤人数 | 4 | 4 | 4 | 4 | 5 | 6 | 4 |
| 休み人数 | 4 | 4 | 4 | 4 | 3 | 2 | 4 |
| 未作成・名前なし | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
たとえば佐藤の最初の「早」は11月の表から、2つ目の「早」は12月の表から取り出したものです。鈴木は月の境目で「遅」から「休」へ変わります。境目の2日を元表と見比べると、参照する月が切り替わったことを確認できます。
開始日から7日分を並べる「週間抽出」シートの作り方
配布ファイルでは以下の式が設定済みです。自分のブックに同じ抽出用シートを作る場合は、次の順に入力してください。
- シート名を「週間抽出」にし、C3に週の開始日「2026/11/30」を入力する。
- B7:B14へ、月間表と同じ表記で8人の氏名を入れる。月間表の氏名一覧からコピーすると、表記ゆれの対策になり、転記違いを防げます。
- C4に
=$C$3+0、D4に=$C$3+1を入れる。E4〜I4は末尾の数値を順に2、3、4、5、6へ変え、7日分を並べる。 - C5に
=CHOOSE(WEEKDAY(C4),"日","月","火","水","木","金","土")を入れ、I5まで右へコピーする。 - C6に
=TEXT(C4,"yyyy-mm")を入れ、I6まで右へコピーする。
4行目は日付、5行目は曜日、6行目は参照するシート名になります。C4:I4の書式(表示形式)を「m/d」にすると、年を省いた日付で表示できます。セルが持つ日付は2026年のままです。
日付行の式は、開始日を固定して末尾の数値だけを増やしています。C4の式をそのまま右へコピーするとすべて同じ日になるので、0〜6を入れ分けてください。C5・C6の式は右へコピーすると参照する列が変わります。
開始日は、月曜日に限らず、取り出したい特定の週の先頭の日を指定できます。元表の勤務記号の変更時も、週間抽出の式へ直接上書きせず、該当する月間シートを直します。
勤務記号を取り出す式:INDEX・MATCH・INDIRECT
「週間抽出」のC7へ入れる式は次のとおりです。C7からI7まで右へコピーし、その範囲を14行目まで下へコピーすると、8人×7日分を取り出せます。
=IFERROR(INDEX(INDIRECT("'"&TEXT(C$4,"yyyy-mm")&"'!"&"$C$7:$AG$14"),MATCH($B7,INDIRECT("'"&TEXT(C$4,"yyyy-mm")&"'!"&"$B$7:$B$14"),0),DAY(C$4)),IF(ISREF(INDIRECT("'"&TEXT(C$4,"yyyy-mm")&"'!"&"A1")),"名前なし","未作成"))
式は、参照先の月、氏名の行、日付の列を順に決めています。
参照先の月を決める部分は、TEXT(C$4,"yyyy-mm") です。C4が2026年11月30日なら「2026-11」、翌日の列なら「2026-12」になります。この文字列とセル範囲をつなぎ、INDIRECTで実際の参照先にします。INDIRECTは、文字列で指定したセル参照を扱う関数です。(Microsoft サポート「INDIRECT 関数」)
氏名の行を決める部分は、MATCH($B7,氏名の範囲,0) です。末尾の0で一致する氏名を探し、その位置をINDEXへ渡します。INDEXは、その行と列が交わる位置の勤務記号を取り出す役割です。(Microsoft サポート「Excel でデータ リスト内の値を検索する」)
日付の列を決める部分は、DAY(C$4) です。11月30日なら30、12月1日なら1を使います。勤務範囲のC列が1日なので、30番目はAF列、1番目はC列に対応します。
C$4 は日付の4行目を固定し、$B7 は氏名のB列を固定しています。横へコピーすると対象日が動き、下へコピーすると対象者が変わる仕組みです。
自店の表で配置が異なる場合は、勤務範囲の $C$7:$AG$14 と氏名範囲の $B$7:$B$14 を両方直します。スタッフが9人以上なら、2つの範囲を同じ行数だけ下へ広げてください。2つの範囲は同じ人から始まり、同じ人数を含む必要があります。月ごとに氏名の並び順が変わっても名前で探せますが、同じ名前が複数ある場合は、フルネームなど重複しない表記にそろえてください。
取り出せなかったときの表示(未作成・名前なし)
外側のIFERRORは、抽出処理がエラーになったときの表示を決めます。続くISREFで、その月のシートのA1を参照できるか確かめています。ISREFは、対象が参照ならTRUEを返す関数です。(Microsoft サポート「IS 関数」)
対象シートを参照できなければ「未作成」、シートを参照できるのに勤務を取り出せなければ「名前なし」と表示します。このため、シート名の間違いも「未作成」に含まれます。
ただし、「名前なし」は氏名違いだけを厳密に判定した結果ではありません。参照先の勤務セルにエラーがある場合なども、この表示になります。氏名が一致しているのに直らないときは、元表の勤務セルと数式の範囲を確認してください。
日ごとの出勤人数・休み人数を数える
勤務記号を取り出せたら、15〜17行目で日ごとの人数を把握します。COUNTIFは、指定した条件に一致するセルを数える関数です。(Microsoft サポート「Microsoft Excel で COUNTIF 関数を使用する」)
配布ExcelのC列には、次の式が入っています。自分で作る場合は、それぞれ入力してI列まで右へコピーしてください。
| セル | 内容 | 数式 |
|---|---|---|
| C15 | 出勤人数 | =COUNTIF(C7:C14,"早")+COUNTIF(C7:C14,"遅") |
| C16 | 休み人数 | =COUNTIF(C7:C14,"休") |
| C17 | 取り出せなかった数 | =COUNTIF(C7:C14,"未作成")+COUNTIF(C7:C14,"名前なし") |
この例の出勤人数は「4・4・4・4・5・6・4」、休み人数は「4・4・4・4・3・2・4」です。各日の出勤と休みの合計が8人になり、17行目がすべて0なら、記入例の人数と一致します。
17行目は空欄や独自の勤務記号まで検知する欄ではありません。自店で記号を追加した場合は、出勤・休みのどちらへ数えるかを決め、集計式にも条件を足してください。人数が出ても、営業時間中の各時間帯に何人いるかや、必要人数を満たすかの判定までは行いません。週単位の実働時間や休日の日数の確認も、別の作業です。
確認例:年またぎ、シート名の違い、氏名の表記ゆれ
確認は、別名で保存した配布ファイルのコピーで行い、条件を一つずつ変えます。次の確認へ進む前に、開始日・シート名・氏名を元へ戻すと、原因を切り分けられます。
1.年をまたぐ週を取り出す C3を「2026/12/28」に変えてください。12月28日〜31日は「2026-12」から取り出され、翌年1月1日〜3日は「2027-01」がないため8人とも「未作成」になります。17行目は「0・0・0・0・8・8・8」です。
直すには、同じ配置の「2027-01」シートを用意し、B2を2027、D2を1にして、氏名と1月分の勤務記号を入力します。12月のシートをコピーして作る場合は、前月の勤務記号を残したまま使わないようにしてください。年またぎ専用の抽出式へ書き換える必要はありません。
2.シート名を「12月」に変える 開始日を「2026/11/30」に戻し、「2026-12」のシート名を「12月」へ変更します。11月30日は表示されますが、12月1日〜6日は8人全員が「未作成」になり、17行目は「0・8・8・8・8・8・8」です。
元表の日付が12月でも、式が探す名前は「2026-12」です。シート名をその名前に戻せば再び参照できます。6行目を見れば、式がどの名前のシートを探しているか分かります。
3.氏名に全角スペースを付ける シート名を戻したうえで、「週間抽出」のB8を「鈴木 」に変更します。末尾に全角スペースがあるため月間表の「鈴木」と一致せず、C8:I8の7日分が「名前なし」になります。
B8末尾のスペースを削除するか、元表の氏名をコピーし直してください。複数の氏名に同じ不要なスペースがある場合は、対象の氏名欄を選び、検索と置換で全角スペースを空文字へ置き換えます。月間表と週間表の両方で、同じ表記になったことを確認しましょう。
INDIRECTを使うときの注意
今回の式は、同じブック内のシートを参照します。別ファイルの月間表をそのまま参照するには外部参照の指定が必要で、INDIRECTによる外部参照では参照元のブックを開いておく必要があります。閉じたブックを指定すると、#REF!の原因になります。(Microsoft サポート「INDIRECT 関数」)
月間表が別ファイルなら、まず必要な月のシートをコピーして1つのブックへまとめる方法で進めてください。配布式へファイル名を足す作業も不要になります。
また、シート名を変更しても、TEXTから作る「yyyy-mm」の文字列は追従しません。月のシート名を自由な名前へ変えず、6行目と一致する規則を保ちます。日付を入力する欄とシート名の対応は、次の月の表を追加するときにも確認してください。
INDIRECTには、再計算のたびに計算し直す「揮発性関数」という性質もあります。数式が増えると再計算の負担になるため、必要な範囲に絞って使います。この記事の例は8人×7日の56個の式ですが、月間表全体を参照する式を大量に作るケースでは、再計算の時間を確かめてから運用してください。(Microsoft Learn「Excel performance: Improving calculation performance」)
週間表を新しく作る場合と、確定シフトの出力
今回の方法は、作成済みの月間表から勤務記号を取り出すためのものです。週間表を白紙から作る場合や、取り出した結果を掲示用に整える場合は、週間シフト表のエクセルテンプレートで入力欄やA4横の印刷設定を確認してください。
20日締めのシフト表の日付表示、職員1人分だけの抽出・印刷、勤務時間の計算は、それぞれ別の記事で扱っています。
シフトラでは、作成したシフトを確定したあと、指定した日の分だけをCSVまたはExcelで出力でき、列や並び順も指定できます(2026-09-05時点の製品情報)。対象はサービス内の確定シフトで、手元のExcel月間表から本記事の式で週を取り出す機能ではありません。月間表の修正から週間表の掲示までをExcelで続けるか、確定シフトの出力に置き換えるかは、毎週の作業の量を見て判断してください。
まずは配布Excelで開始日を指定し、月の境目の勤務記号と日別の人数を確かめてください。基本の流れは、月間表を直す、週間抽出で確認する、印刷・配布する、の順です。自店の月間表へ置き換えた後も、参照範囲・氏名・シート名を確認してから、週間表の印刷や配布へ進めましょう。