EXCEL

エクセルの日付入力で、土日・祝日・指定した曜日を除いて入力する方法

スポンサーリンク

Excelで日程表やカレンダーなどを作成する場合、日付の入力でオートフィルを使うと、土日・祝日も含め全ての日付が入力されてしまいます。

もし、土日・祝日は、会社が休業日というこであれば、土日・祝日の欄は必要ありません。

それ以外にも、規定の休業日があれば、その曜日も入力する必要はありません。

土日・祝日・指定した曜日を除外して、仕事の稼働日だけの日付を簡単に入力する方法をご紹介します。

除外する曜日により3つの方法を解説します。

1.土日 だけを除くなら、「オートフィル」が最も簡単!

①1つだけ日付を入力します。

オートフィル

②入力したセルをクリックして、右下にマウスを合わせ、マウスポインタの形が+になったら、そのまま下方向、あるいは右方向にドラッグします。

この機能を「オートフィル」といいます。

「オートフィル」機能では、元になるセルの値より1ずつ加算された日付が入力されます。

では、日付のデータが連続で入力されたので、このあとオートフィルオプションで土日を除外します。

オートフィル実行後

③「オートフィル」を実行すると、コピーされた日付の最終セルの右下に青色のボタンが表示されます。マウスをポイントするとオートフィルオプションと表示されるボタンです。

オートフィルオプションは、コピー後のデータを他の値に変更することができます。

今回は、週末を除外したいので、オートフィルオプションをクリックして、一覧から《連続データ(週日単位)》をクリックしましょう。

オートフィルオプション

④除外された曜日を表示して、確認してみましょう。

《ホーム》タブー《数値》グループ右下の《ダイアログ起動ツール》ボタンをクリックー《セルの書式設定》ダイアログボックスー《表示形式》タブー分類:《ユーザー定義》をクリックー《種類》のボックスに、m/d(aaa) と入力します。

サンプルに 5/1(金)などと表示されたことを確認してOKをクリック。

※なお、《セルの書式設定》ダイアログボックスは、キーボードから Ctrlキーを押しながら1とタイプしても表示できます。

日付の表示形式

結果:曜日が表示されたので確認しましょう。土曜と日曜だけを除いた日付が入力されました。

m/d(aaa)という表示形式

2.土日・祝日を除くなら、「Workday関数」

①《Sheet 1》に、1年間の祝日を全て入力します。

祝日

②《Sheet 2》に、1つだけ日付を入力します。

日付の入力

③入力した日付の1つ下のセルに、「Workday関数」を入力します。

Workday関数

Workday関数

=Workday(開始日,日数,[祭日])

=Workday(A2,1,'Sheet1'!$A$2:$A$19)

Hint!

祭日の引数は、《Sheet1》の祝日をドラッグして指定しましょう。ドラッグしたら、祝日の位置が絶対にずれたり動いたりしないように、「絶対参照」の設定をします。

絶対参照は、キーボードの F4 キーを押すだけです。絶対参照を設定すると、セル位置の列番号と行番号の前に $ がつきます。

絶対参照の設定をしたら、すぐにEnterキーを押して、確定してください。

Workday関数絶対参照

④確定すると、数式を入力していた《Sheet2》に切り替わり、計算結果の日付が表示されました。

●5/2、3の土日と4、5、6日の祝日が除外されました。Workday関数コピー前

⑤数式を入れたセルを元にして、オートフィルでコピーしましょう。

●土日・祝日が除外された日付がコピーされました。

Workday関数オートフィル後

3.指定した曜日と祝日を除くなら 「Workday.INTL関数」

今回は、祝日と火曜日と水曜日を除外して日付を入力してみます。

①②は、「Workday」関数と同じで、《Sheet1》に祝日を入力し、《Sheet2》にひとつだけ日付を入力しておきます。

③入力した日付の1つ下のセルに、「Workday.INTL関数」を入力します。

Workday.Intl関数

Workday.INTL関数

=Workday(開始日,日数,[週末],[祭日])

=Workday(A2,1,4,'Sheet1'!$A$2:$A$19)

Hint! 週末の指定の方法Ⅰ

数値 除外したい曜日
1または省略 土曜日と日曜日
日曜日と月曜日
月曜日と火曜日
火曜日と水曜日
水曜日と木曜日
木曜日と金曜日
金曜日と土曜日
11 日曜日のみ
12 月曜日のみ
13 火曜日のみ
14 水曜日のみ
15 木曜日のみ
16 金曜日のみ
17 土曜日のみ

Hint! 週末の指定の方法Ⅱ

もし、上の表に除外したい曜日の組み合わせがない場合は、7つの数値で自由自在に曜日の指定ができます。

設定は、左から 「月火水木金土日」の曜日に、0と1の数値を割り当てていきます。0は除外しない、1は除外するです。

例えば、火水を除外する場合は、「0110000」となります。これを、[週末]の引数のところに入れます。ですから、[週末]の引数は、「4」でも「0110000」でもよいです。

この方法だと数値を調べる必要もなくどんな曜日にも対応できます。

Hint! 祭日の引数は、《Sheet1》の祝日をドラッグして指定し、F4 キーを押して絶対参照の設定をしておきましょう。その後、すぐにEnterキーを押して、確定してください。

④シートが切り替わり、日付が表示されました。

Workday.Intl関数入力後

⑤セル【A3】を元に下方向にオートフィルで、コピーしましょう。

週末に指定した数字は「」なので、火曜と水曜・そして祝日が除外されてコピーされました。

Workday.Intl関数オートフィル後

まとめ

  • 土日の除外→オートフィルオプションの連続データ(終日単位)を設定
  • 土日・祝日の除外→「Workday」関数
  • 指定した曜日・祝日の除外→「Workday.INTL」関数

それぞれ、使い分けて効率よく日付の入力をしていきましょう。

Yadokari
Excelの関数は全484種類あまりにもなります。多くの関数を知って、時短、仕事効率化につなげましょう!

↓↓はお勧めの書籍です。



手順通りに操作するだけ! Excel基本&時短ワザ[完全版] 仕事を一瞬で終わらせる 基本から応用まで 176のワザ

エクセルの使い方 基礎から応用まで①~㉕ 無料オンライン講座へ↓↓

Excel2019 使い方 基礎から応用まで①~㉕ 無料オンライン講座

スポンサーリンク

-EXCEL

© 2021 無料オンラインパソコンスクール-SANGOYA Powered by AFFINGER5