カレンダーの自動色付けを２列ずつしたい

こんにちは。

提示されておられる表のセル配置とセル内容が今一つ良く分からないので、推測を交えて図１のような表を作り、「条件付き書式」を使いセルの背景を 2列ずつ色付けしてみました。

・図１

※横に大きな表になるので、AA列から BF列までを非表示にしています。

この図１は、以下のような方法で作成しています。

＜事前準備＞

• 表自体は、A2セル（奇数列）の 1日から始まり BJ25セルまでの 31日分・24行と仮定して作成しています。
  列数については、A列から BJ列までの 2列ずつ 31日分とし、行数については、2行目から 25行目までの 24行分と仮定しています。
  ※この A2:BJ25 のセル範囲を「条件付き書式」のルールの「適用先」として設定しています。
• A1セル（ここでは A1セルと B1セルを結合）にカレンダーの「年月」を入力することとし、「セルの書式設定」の「表示形式」の「ユーザー定義」で［ yyyy"年"m"月" ］を設定しています。
  例えば、2024/5 と入力すると、2024年5月 と表示されますが、値としては、2024/5/1 の「日付のシリアル値」（数値）が入力されます。
• A2セルから BJ2セルまでと A3セルから BJ3セルまでは 2列ずつセル結合した状態から下記の数式を入れ、それぞれに A1セルの「年月」に応じた「日付」とその「曜日」を表示させています。
  尚、値としては、2行目と 3行目には同一の「日付のシリアル値」（数値）が入ります。

A2セル（ A2セルと B2セルの結合セル）の数式
=A1

C2セル（ C2セルと D2セルの結合セル）の数式
=IF(A2＜EOMONTH($A1,0),A2+1,"")

A3セル（ A3セルと B3セルの結合セル）の数式**=A2※この A3セル（ A3セルと B3セルの結合セル）に入れた数式をコピーし、右方向（列方向）に BI3セル（ BI3セルと BJ3セルの結合セル）まで間を置かず貼り付けています。**

最後に、2行目と 3行目に「セルの書式設定」の「配置」の「横位置」で「中央揃え」を設定し、加えて 2行目には「セルの書式設定」の「表示形式」の「ユーザー定義」で［ d ］を設定し、更に 3行目には「ユーザー定義」で［ aaa ］を設定して見た目の体裁を整えています。

尚、セルの結合については、「セルの書式設定」の「配置」で「セルを結合する」で設定しています。

※以下では、必要な場合を除き煩雑さを避けるため、セル結合に関する断り書き（「 C2セルと D2セルの結合セル 」などの書き込み）は入れていません。ご了解ください。

ここまでで何をやっているかというと、日付の入力を全て「日付のシリアル値」で行うようにすることで「条件付き書式」のルールを作りやすくしています。

なので、「日付のシリアル値」以外（例えば全て文字列で入力するとか・・）で表を作成されている場合、以下でご紹介している「条件付き書式」のルールに設定する数式を変更しなければならない場合があります。ご注意ください。

※「日付のシリアル値」については、ご自身でネット検索なさってみてください。多くの情報が得られるかと思います。

＜「条件付き書式」のルールの作成＞

※念のため細かい手順を書いています。ご了解ください。

※以下でご紹介している数式では、祝日の判定を行うため、Sheet2 に事前に作っておいた図２のような「祝日の一覧表」（ 2024年版 ）を参照しています。

・図２

※A列の値は全て「日付のシリアル値」です。

※普通に日付を入力（ 2024/1/1 など）すれば、Excel 内部で「「日付のシリアル値」に変換されます。

以下、図１で使っているルールの作成手順です。

1. A2セルから BJ25セルまでの範囲を選択しておく。
   ここで選択した範囲がルールの「適用先」となります。
2. 「ホーム」タブの「条件付き書式」で「新しいルール」を選択する。
3. 表示される「新しい書式ルール」で、「数式を使用して、書式設定するセルを決定」を選択し、「次の数式を満たす場合に値を書式設定」欄に下記の数式を入力する。

これらの数式はご希望のように、土曜・日曜・祝日の全てを同一の背景色で色付けする場合の数式となります。
また、数式１は A2セル（奇数列）を左上端とした表を前提とした数式で、数式２は B2セル（偶数列）を左上端とした表を前提とした数式になっています。
どちらかの数式一つをお使いください。
長い数式なので、変な箇所で自動的に改行されているかもしれませんが、全部で 1行の数式です。

※最新版の Excel（ Microsoft365 の Excel、Excel2021 など）以外の旧版の Excel でも使用できるような数式となっています。

・数式１（奇数列用）
=(WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=7)+(WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=1)+IFERROR(VLOOKUP(OFFSET(A$2,,-MOD(COLUMN()-1,2)),Sheet2!$A$1:$A$25,1,0),0)

・数式２（偶数列用）
=(WEEKDAY(OFFSET(B$2,,-MOD(COLUMN(),2)))=7)+(WEEKDAY(OFFSET(B$2,,-MOD(COLUMN(),2)))=1)+IFERROR(VLOOKUP(OFFSET(B$2,,-MOD(COLUMN(),2)),Sheet2!$A$1:$A$25,1,0),0)

ここでは、図１に合わせて数式１を使います。

※ご自身の表の左上端が A2セル（奇数列）や B2セル（偶数列）ではない場合の数式中の変更箇所と「適用先」の変更例は以下のようになります。
・例１：表の左上端が C4セル（奇数列）の場合。
数式１を使い、A$2 の箇所（ 3箇所）を C$4 に変更し、「適用先」を $C$4:$BL$27 に変更してください。
・例２：表の左上端が D3セル（偶数列）の場合。
数式２を使い、B$2 の箇所（ 3箇所）を D$3 に変更し、「適用先」を $D$3:$BM$26 に変更してください。 4. 「書式」ボタンクリックし、表示される「セルの書式設定」の「塗りつぶし」タブの「背景色」で、土曜・日曜・祝日に色付けしたい色を選び、「OK」で閉じる。 5. 「新しい書式ルール」に戻るので「OK」で閉じる。
結果は図１のようになります。

※ルールの確認・変更・削除などを行う場合は、「ホーム」タブの「条件付き書式」から「ルールの管理」を開いて行ってください。

※今回の場合、ルールは一つで十分に機能しますので、余分なルールが有る場合は削除しておいてください。相互の干渉を防ぐためです。

＜数式の動作概要＞

※数式１についての動作概要です。数式２については省略します。

• 数式全体は + で繋がれた 3つの部分から出来ています。+ は論理和の意味合いで使用しています。
• 「条件付き書式」のルールの数式の場合、「適用先」のセルアドレスが順番に評価されていき、数式中の相対参照箇所も「適用先」のセルアドレスの移動に合わせて変化し、数式が TRUE になった場合のみ、評価中のセルアドレスに指定した書式が設定されます。

これらを踏まえて、数式を見ていきます。

3つの部分で共通の OFFSET(A$2,,-MOD(COLUMN()-1,2)) の箇所では、この時点で評価されているセル（例えば A2セル）の列番号が奇数であれば A2セルを参照し、この時点で評価されているセル（例えば B2セル）が偶数であれば、同じく A2セルを参照します。

つまり、列結合された 2つのセルの左側のセルを常に参照するということになります。

なので、WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=7 は、その参照しているセルの値が「日付のシリアル値」の「土曜日」ならば TRUE となります。

同じく、WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=1 は、その参照しているセルの値が「日付のシリアル値」の「日曜日」ならば TRUE となります。

また、IFERROR(VLOOKUP(OFFSET(A$2,,-MOD(COLUMN()-1,2)),Sheet2!$A$1:$A$25,1,0),0) では、Sheet2 の「祝日の一覧表」の A列の中にその参照しているセルの値（「日付のシリアル値」）が有れば、その値を返し、無ければ数値の 0 を返します。

ここでは、0 以外の数値は全て TRUE と見なされます。

数式全体として見ると、参照しているセル（ 2行目 ）の値が土曜・日曜・祝日の場合に限り 2列分のセルに色付けされることになります。

＜運用方法＞

A1セルにカレンダーとして表示させたい「年月」を、例えば 2024/5 と入力する（ 2024年5月と表示される）と、A2セルから始まる「日」と「曜日」の一覧が図１のように全て表示されます。

この際、2024/2 を入力したとすると、2024年はうるう年なので 29日まで表示され、余ったセル（ 31日分を用意しているので 2日分が余る）には ""（空白の文字列）が表示されるようになっています。

各セルの背景色についても、2行目の「日」のデータ（実際には「年月日」を表す日付のシリアル値（数値））を基にして「条件付き書式」のルールが働き色付けされます。

つまり、表の「日付」と「曜日」、それとセルの背景色については、A1セルに「年月」のデータを入れるだけで全て更新され表示されます。

以上です。

何か不明点やご希望と違う点がありましたら、お知らせください。

下記の組み合わせで動作確認しています。

・Windows11 と Excel2021

・Windows10 と Excel2016

ご参考になれば幸いです。

＜追記・修正＞

文章中の言葉足らずな箇所や抜け、誤記などを追記・修正しました。

数式に変更はありません。

2024/5/19

数式の動作概要の中の「3つの部分で共通の・・」の文章に誤記がありましたので修正しました。

失礼いたしました。

＜数式の追加＞

質問文中で、「・・COUNTIF を使う・・」と書かれていた意味合いが遅まきながらやっと分かりました。

祝日の判定に使われていたのですね。

数式１（奇数列用）と数式２（偶数列用）を COUNTIF 関数を使って書きなおすと下記のようになります。

・数式３（奇数列用）

=(WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=7)+(WEEKDAY(OFFSET(A$2,,-MOD(COLUMN()-1,2)))=1)+COUNTIF(Sheet2!$A$1:$A$25,OFFSET(A$2,,-MOD(COLUMN()-1,2)))

・数式４（偶数列用）

=(WEEKDAY(OFFSET(B$2,,-MOD(COLUMN(),2)))=7)+(WEEKDAY(OFFSET(B$2,,-MOD(COLUMN(),2)))=1)+COUNTIF(Sheet2!$A$1:$A$25,OFFSET(B$2,,-MOD(COLUMN(),2)))

こちらの方が効率が良いかと思います。

どちらの数式でも、ご自身で慣れておられる方の数式でお試しになってみてください。

＜「数式の動作概要」の差し替え＞

「数式の動作概要」の中で『また、・・・では、Sheet2 の「祝日の一覧表」の A列の中に 』 の部分の説明が数式２からのコピーを使ってしまっていましたので、数式１からのコピーに差し替えました。数式１や数式２自体の変更はありません。

・差し替え前

IFERROR(VLOOKUP(OFFSET(B$2,,-MOD(COLUMN(),2)),Sheet2!$A$1:$A$25,1,0),0)

・差し替え後

IFERROR(VLOOKUP(OFFSET(A$2,,-MOD(COLUMN()-1,2)),Sheet2!$A$1:$A$25,1,0),0)

失礼いたしました。

フォーラムオーナー注：

現在、コミュニティプラットフォーム内で発生しているバグを回避するために、上記数式５内で半角英数の ”<>” が入力されるべきところを、全角の "＜＞" に置き換えて入力しています。実際の数式では、半角英数の ”<>” に置き換えください。