エクセル 月間シフト表で曜日ごとの休み数をカウントしたい

エクセルで図のようなシフト表を作成しています。
（会社の都合上、データが持ち出せないため、スプレッドシートで同様のものを作成しましたが
実際の操作はエクセルになります）

A1セルはyyyy/m/dの入力をyyyy年m月の表示形式に
B2セルは=A1、以下右に手前セル+1の数式で31日まで入力
B3セルは=B2で表示形式を曜日にしてあります（以下右同様）


やりたいこと
AG列で★のcountifを求めたのち、各職員の曜日ごとの休み数（空白）をAH以降で求めたい
できるだけ同じ曜日での休みが発生しないようにする目安となります


★のカウントはできたのですが、上記の休み曜日が求められず、
アドバイスをいただければと思います。
VBAではなく、できれば関数で求めたいと考えていますが、可能でしょうか？
xlookupやcountifs、weekdayの組み合わせなのかと思うのですが、知識不足で思いつきません。

ご存じの方、教えてください。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2025/03/03 00:37

こんばんは

図がちょっとはっきりしないのですが、集計欄のAH2:AN2に「月～日」が表示されているものと仮定しました。

本来なら、「AH2:AN2と同じ曜日の★印の個数を数える」という式にするべきところだと思いますが・・
AH2:AN2の値がどのような値になっているのか不明なので、以下は、「AH列＝月曜日」～「AN列＝日曜日」と決め打ちにした式にしてあります。
（ですので、AH2:AN2の表示値を変えても、それに連動はしません）

上記の仮定でよければ、AH4セルに
=SUMPRODUCT(($B4:$AF4="★")*(WEEKDAY($B$2:$AF$2,2)=COLUMN(A1)))
の式を入力し、必要な範囲に右方、下方にフィルコピーすることで算出できると思います。

※ ★印の数ではなく、空白セルの数を数えたいのであれば、式中の「"★"」を「""」に変えれば、そのような意味の式になります。

この回答へのお礼

回答ありがとうございました。
SUMPRODUCT関数でしたね。以前にも使った経緯がありました。

お礼日時：2025/03/08 11:52

No.2 回答者： goomania 回答日時： 2025/03/04 00:05

>A1セルはyyyy/m/dの入力をyyyy年m月の表示形式に

>B2セルは=A1、以下右に手前セル+1の数式で31日まで入力
>B3セルは=B2で表示形式を曜日にしてあります（以下右同様）
とのご説明ですが、この状態だと集計に使える関数は、ほぼ「SUMPRODUCT一択」になってしまいます。

従って、fujillinさんの回答のように、

AH4: =SUMPRODUCT(($B4:$AF4="")*(WEEKDAY($B$2:$AF$2,2)=COLUMN(A1)))･･･①

として、右方向、下方向へコピーする対応になります。
ご質問者が掲出された画像である2025年3月の対応であればこの数式で解決するはずです。

しかし、ご質問者がA1にyyyy/m/dと入力しているのは、A1の入力を変更することで、2025年4月以降もこの表を使い続けるのだと推察します。
だとすると困ったことが起きます。B4～AF4は31日分あるからです。
このため、2,4,6,9,11月の小の月は日数が31日ないので、翌月分の日付が混入してしまいます。

例えば2025年4月の例でいえばAF2は2025/5/1になってしまいます。
★を数える場合、5/1に何も入力しなければ、カウントされないので問題ないのですが、逆に各職員の曜日ごとの休み数（空白）を求める場合、5/1に何も入力しなければ休み数としてカウントされてしまうことになります。
つまり、①の数式は常に31日分の★または空白をカウントする数式になっているということです。

そこで、①を翌月分はカウントしないように修正します。

AH4: =SUMPRODUCT(($B4:$AF4="")*($B$2:$AF$2<EDATE($A$1,1)*(WEEKDAY($B$2:$AF$2,2)=COLUMN(A1))))･･･②

という数式にすれば、大の月、小の月関係なく、当月分のみを集計できます。

fujillinさんの数式は、ご質問者が既に入力された内容をできるだけ弄らないという方針の基「AH2:AN2の値がどのような値になっているのか不明」という問題意識があって、「AH2:AN2と同じ曜日の空白の個数を数える」という王道の数式を使わずに作成した工夫の結果のように思います。

「ご質問者が既に入力された内容をできるだけ弄らないという方針」はご質問者には親切なのかもしれませんが、ここでは一旦適用除外とさせていただいて、異なる方法をお示しします。添付画像をご覧ください。
内容が細かく、画像が見にくいので以下のURLにもアップしておきます。
https://gyazo.com/0bc66e07e51aa257665469d3ec28a3a5

3行目の曜日を

B3: =REPT(TEXT(B2,"aaa"),B2<EDATE($A$1,1))･･･③

として、AF3までコピーし曜日を文字列に変えています。
これで、翌月の日付には曜日が入らず「空白」になりますので、休みの曜日のカウント対象外になります。

また、AH2:AN2には文字列として月、火、水、木、金、土、日を入れているものとします。このようにしておけば、極めて数式は簡潔になり

AH4: =COUNTIFS($B4:$AF4,"",$B$3:$AF$3,AH$2)･･･④

として、右方向、下方向へ必要分をコピーすれば休み数(空白)が曜日ごとにカウントされることになります。

この回答へのお礼

回答ありがとうございました。
おっしゃる通り、左上の年月で日付が連動しますが、31日の列が月によって翌月1日に日付が変わりそうなものは条件付き書式でセル色を変更するため、誤入力は避けられます。

提示していただいた関数も参考に、改変したいと思います

お礼日時：2025/03/08 11:55