【Excel】関数を使って月別に土日祝を除いた日付を取得する方法 2023 7/22 Excel関数 2023年6月13日2023年7月22日 当ページのリンクには広告が含まれています。 こんな感じのフォーマットを作成してみます </noscript><iframe title="Excelで土日祝を除外した日付を表示させる" width="500" height="375" data-src="https://www.youtube.com/embed/idk8A852or8?feature=oembed" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen class="lazyload" > 流れとしてはこんな感じ STEP祝日シートを作成 ネットから「Excel 祝日」と検索して祝日一覧のシートを作成 祝日の範囲に「祝日」と名前の定義をしておくと、翌年以降の範囲の変更が楽になる STEPWORKDAY関数を使用して土日祝を除外した日付を取得 WORKDAY関数を使えば、土日祝を除外した日付を取得できる 当月の平日のみを表示するために、月末と翌月の日付を比較する数式を作成 STEPスピンボタンを作成してボタンで任意の月を選択できるようにする スピンボタンで月を選択できるようにすれば、各月の平日を容易に取得できる 目次祝日一覧シートを作成 このフォーマットを使って作成していきます。シート名は任意です。 ①新規シート「祝日一覧」を作成 ②セル[A1]に祝日の一覧を貼り付ける 祝日データの取得方法 ネットで検索してコピペする WEBSERVICE関数を使用して取得する こちらのサイトでお好きな方法を選んでください Excel：2023年の日本の祝日一覧 | ExcelAPI ③祝日一覧の日付のみのセル範囲[A1:A17]を選択 ④選択範囲を「祝日」と名前の定義をしてEnter これで祝日シートの完成です。 土日祝を除外した日付、平日を取得する 西暦と月のセルに名前を定義する ①シート「平日カレンダー」をクリックし、セル[D2]に1と入力 ②選択セルを「西暦」と名前の定義をしてEnter 月も同じように設定します。 ③セル[D4]に「1」と入力 ④選択セルを「月」と名前の定義をしてEnter これで名前の定義ができました。 WORKDAY関数を使用して土日祝を除外する ①表のタイトル行下のセル[A2]に以下の数式を入力しEnter Visual Basic =WORKDAY(DATE(西暦,月,1)-1,1,祝日) 1 =WORKDAY(DATE(西暦,月,1)-1,1,祝日) セル[D4]に月（今回は1月）の月初の平日が表示されるようになりました。 日付の表示形式を「〇〇年〇〇月〇〇日（〇）」に変更する方法 ①表示形式を変えたいセル範囲[A2:A24]を範囲選択 ②「ホーム」タブの「数値」の右下矢印をクリック ③「ユーザー定義」をクリック ④種類の枠内に「YYYY年MM月DD日(AAA)」と入力 ⑤最後に「OK」をクリック 月を1桁(ex.1月)にしたい場合は「YYYY年M月DD日(AAA)」と入力 日にちも1桁(ex.1日)にしたい場合は「YYYY年M月D日(AAA)」と入力 「AAA」というのは曜日を表している ②月初の平日の1つ下のセル[A3]に以下の数式を入力しEnter Visual Basic =WORKDAY(A2,1,祝日) 1 =WORKDAY(A2,1,祝日) ③セル[A3]の右下十字を[A23]までドラッグすると以降の平日が表示されます 関数を理解したい方はこちらをクリック！ DATE関数 概要西暦/月/日を取得書式=DATE(年,月,日）詳細DATE 関数 – Microsoft サポート 今回の事例の場合… Visual Basic =DATE(西暦,月,1) 1 =DATE(西暦,月,1) 引数指定した引数内訳年西暦（セル[D2]）セル[D2]に「西暦」という名前で定義をしている。名前の定義をしていない場合はセル[D2]を指定する。月月（セル[D4]）[D4]に「月」という名前を定義している。名前の定義をしていない場合はセル[D4]を指定する。日1毎月1日目を取得したいので、固定の「1」を指定 上記の結果、2023/1/1を取得できる。 WORKDAY関数 概要開始日から指定された日数に対応する日付を取得。自動的に土曜日・日曜日、指定した日程[祝日]を除外します。書式=WORKDAY(開始日, 日数, [祝日])詳細WORKDAY 関数 – Microsoft サポート 今回の事例の場合… Visual Basic =WORKDAY(A2,1,祝日) 1 =WORKDAY(A2,1,祝日) 引数指定した引数内訳開始日セル[A2]セル[A2]には既に「=WORKDAY(DATE(西暦,月,1)-1,1,祝日)」という数式が入力されているため、結果「2023/1/3」を取得している。日数1開始日が「2023/1/3」なので、その1日後「2013/1/4」を参照祝日祝日シート「祝日一覧」のセル範囲[A1:A17]に「祝日」という名前を定義している。指定したセル範囲には「2013/1/4」の記載はなし、水曜日なので平日扱いになる。 上記の結果、「2013/1/4」を取得できる。 IF・DAY関数を使って翌月を非表示にする 月によって平日の日数は変わるので、平日の日数が少ない月だと翌月分も表示されてしまいます。 なので、翌月分を非表示にしてみます。 ①当月末2日分＋翌初月2日分のセル範囲[A20:A23]をドラッグして選択 ②Deleteキーで選択範囲を削除 ③削除した範囲の一番上のセル[A20]に以下の数式を入力してEnter Visual Basic =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") 1 =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") ④削除した範囲[A20:A23]までをオートフィルすると翌月分が表示されなくなります 関数を理解したい方はこちらをクリック！ DAY関数 概要日にちを取得書式=DAY(シリアル値）詳細DAY 関数 – Microsoft サポート 今回の事例の場合… Visual Basic =DAY(WORKDAY(A19,1,祝日)) 1 =DAY(WORKDAY(A19,1,祝日)) 引数指定した引数内訳シリアル値WORKDAY(A19,1,祝日)引数の値が「2023年1月27日」。日にちの「27」を取得できる。 上記の結果、「27」を取得できる。 ※シリアル値＝「2023年1月27日」というのは厳密には違いますが、今は気にしなくていいです。またシリアル値について記事書きます。 IF関数 概要条件式に対して一致した場合と不一致の場合の結果を返す書式=IF(条件式,条件が一致した場合,条件が不一致の場合）詳細IF 関数 – Microsoft サポート 今回の事例の場合… Visual Basic =(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)) 1 =(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)) 引数指定した引数内訳条件式DAY(WORKDAY(A19,1,祝日))<DAY(A19)30<27になので、条件式は不一致条件が一致した場合“”条件が一致した場合は空白にする条件が不一致の場合WORKDAY(A19,1,祝日)条件が不一致の場合は、WORKDAY(A19,1,祝日)の結果「2023/1/30」を返す 上記の結果、条件式とは一致していないので「2023/1/30」を取得できる。 IFERROR関数 概要エラーが起きた時の処理をする書式=IFERROR(値,エラーの場合）詳細IFERROR 関数 – Microsoft サポート 今回の事例の場合… Visual Basic =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") 1 =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") 引数指定した引数内訳値IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),””,WORKDAY(A19,1,祝日))引数の値が「2023年1月27日」。日にちの「27」を取得できる。ただし、セル[A19]が空白だとエラーになる。エラーになった場合“”値を取得できなかった場合、空白にする。今回の場合、1つ上のセルが空白だとエラーになる。 ちょっとわかりにくいので、エラーになる事例を出してみます。 もしIFERRORを入れなかったら… セル[A20]に Visual Basic =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") 1 =IFERROR(IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)),"") セル[C20]に Visual Basic =IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)) 1 =IF(DAY(WORKDAY(A19,1,祝日))<DAY(A19),"",WORKDAY(A19,1,祝日)) それぞれ入力して23行目までオートフィルしてみます。（数式の入っている22行以前は非表示にしています） するとIFERROR関数を入れていない方に「#VALUE!」という値が不明のエラーが出てきました。 ビックリマークをクリックしてエラーの原因を調べ見ます。 引数「DAY(WORKDAY($C$22,1,祝日)」で既に値エラーが出ています。引数「DAY(C22)」も調べてみます。 「DAY(C22)」も値エラーが出ました。つまりセル[C22]の値がわからない、空白の状態のため参照できる情報がなくエラーが出てしまうということです。 Excel上はエラーとして表示されていますが、私たちユーザー側としては空白になる＝エラーが正解なのでIFERROR関数を使ってエラーを非表示にします。 スピンボタンで日付を変更できるようにする 西暦のスピンボタンを作成 ①「開発」タブ内の「挿入」をクリック ②「スピンボタン（フォームコントロール）」をクリック 「開発」タブが見当たらない ①左上の「ファイル」をクリック ②左下の「オプション」をクリック 見当たらない場合は「その他」のメニュー内にあります ③「リボンのユーザー設定」をクリック ④「開発」にチェックを入れる ⑤「OK」をクリック ③西暦の値を入力しているセルの右側（セル範囲[E2:E3]あたり）にドラッグしてスピンボタンを配置 配置したスピンボタンを右クリックして「コントロール書式設定(F)」をクリック ④「現在値」に現在の西暦を入力 ⑤「リンクするセル(L)」をクリック後、西暦セル[D2]をクリック ⑥「OK」をクリック スピンボタンで西暦を変更できるようになりました。 月のスピンボタン作成 さきほどと同じやり方で、月の値を入力しているセルの右横あたりにスピンボタンを配置します。 ⑦「最小値(M)」を「1」、「最大値(X)」を「12」と入力 ⑧「リンクするセル(L)」をクリック後、セル[D4]をクリック ⑨「OK」をクリック スピンボタンで月の変更ができるようになりました。 スピンボタンで選択した西暦と月を表のタイトル行に反映させる この辺はもうお好みですが、文字演算子を使ってみたい方はぜひやってみて下さい。 表のタイトル、セル[A1]に以下の数式を入力してEnter Visual Basic =西暦&"年"&月"月の平日" 1 =西暦&"年"&月"月の平日" 表のタイトルが完成しました。 Excel関数 URLをコピーしました！ 当ブログの管理人まぜきとブログについて 【Excel】時間帯ごとの人数も自動カウント！ガントチャート形式のシフト表の作成方法 この記事を書いた人 まぜき データ入力のバイトでご飯食べてます。めんどくさがりなので実務でちまちまマクロ組んでます。学生時代にMOS Excel2010 Expert やExcel VBA ベーシックなどオフィス系の検定試験を取得 。MOS日本学生大会2012ワードで金賞。 関連記事関連する記事はまだ見つかりませんでした。 コメント コメントする コメントをキャンセルコメント ※ 名前 ※ メール ※ サイト 次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。
コメント