![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
No.7ベストアンサー
- 回答日時:
何度もお邪魔します。
エラーの件でかなり悩まれていらっしゃるようなので・・・
エラーの原因はWEEKDAY関数のシリアル値の取得部分でエラーになります。
すなわちWEEKDAY関数の範囲内すべてが数値(シリアル値)でないとエラーとなります。
その解消のために前回のような方法を提案しました。
仮に見た目は「空白」でも数式によって「空白」表示されている場合はExcel的には「空白」とは判断してくれません。
Deleteキーで完全に数式まで消した場合は「空白」=0と判断してくれますので、
エラー表示は解消されます。
しかし、月が替わるたびに数式を入れたり・消したりは大変だと思い、
とりあえずWEEKDAY関数の「土日曜」とは全く関係のない平日(月曜日)の「2」を「空白」の代わりに
表示させた数式です。
尚、前回のセルの表示形式も手を加えたくない場合は
条件付き書式で セル値が「2」に等しい → 書式 → フォントの色を「白」としてもかまいません。
余談ですが、小の月の月末セルは
「0」→Excel的には1900/1/0 → 土曜
「1」→ 1900/1/1(日曜)
の数値であっても29行目以降が空白だと思いますので、
何らかの数値さえ入っていれば休日・祝日出勤とはカウントされません。
※ 土日と祝日が重複する月もあるはずですので、前回の数式のように
土日の出勤数+平日(月~金)で祝日の出勤数 を出す計算式としています。
単に土日曜+祝日 としてしまうと重複してカウントされる月が出てきます。
以上、長々と失礼しました。m(_ _)m
No.8
- 回答日時:
NO.4,5,6です
NO.7さんのおっしゃる通り
土日+祭日では重なった場合二重にカウントされます。
説明が不足していましたが、
祭日一覧から「土日」の「祭日」を除いた「祭日一覧表」
を作って使用しておりました。
それを使えばご希望の計算は出来ます。
祭日一覧表はハッピーマンデー(振替休日)の関係で
毎年作りますので、その際「土日」と重複した「祭日」を除いて
作られると1年間は使えます。
25年度で4日程重なっています。
日付について
日付は31日の欄まで式を入れておきます。
大の月小の月については条件付き書式で
翌月分は非表示にします。
条件付き書式は
=MONTH(日付)<>別セルの月 設定で
フォントの色を白にします。
従って1日~31日蘭まで日付データは必ず
設定されていますので、空白欄は有りません。
11月(小の月)では日付欄「$D$26:$AH$26」の
[AH26]欄は空白かも知れませんね。
空白欄が有ればエラーの原因かもしれません。
従って大の月、小の月で数式を変えることも有りません。
毎月1つの数式で計算されます。
後は「祭日一覧表」(土日重複を除く)を年に1回更新
するだけです。
この回答への補足
回答ありがとうございます。返事が遅れまして申し訳ありません。
図はAGで終わっていますが、表自体は大の月もあるので、AHまであります。11月は小の月なので、AGで切り取り貼り付けたので、空白列はないとお考えになられたと思います。大変申し訳ありません。その空白行が災いしているのか、AHまで式に含むとエラーになりますが、AGまでだとOKです。
また、土日と祝日の重なり考え付かなかったですが、ご指摘ありがとうございます。
No.5
- 回答日時:
NO.4です
式の記述が違っています。
>=SUMPRODUCT(((WEEKDAY(D29:AH29,2)>=6)+COUNTIF(祝日,D29:AH29))*($D29:$AH29<>""))
としていますが、
WEEKDAY 関数の引数は 「$D$26:$AH&26」の日付です。
26行目で固定します。
COUNTIF 関数も同じ 「$D$26:$AH&26」の日付です。
26行目で固定します。
調べる該当欄は 「$D29:$AH29」となります。
29行目で下にコピー出来る様に複合参照です。
26行 と 29行が違っています。
この回答への補足
セル番地が違っておりました。しかし、次のように入力しても#VALUE!エラーとなります。
=SUMPRODUCT(((WEEKDAY($D$26:$AH$26,2)>=6)+COUNTIF(祝日,$D$26:$AH$26))*($D29:$AH29<>""))
($D29:$AH29<>"")ヌルではないとありますが、図のように飛び飛びにヌルの所があるのですが、関係ありますか。よろしくお願いします。
No.4
- 回答日時:
初めて回答します。
同じような計算(個人々の土日祭の勤務日数計算→給与計算)をしていました。
名前「日程」($D$26:$AH$26)
名前「祝日」(祝日表)を定義しておきます。
式が読み易くなると思います。
=SUMPRODUCT(((WEEKDAY(日程,2)>=6)+COUNTIF(祝日,日程))*($D29$AH29<>""))
WEEKDAY(日程,2)>=6 で土日
COUNTIF(祝日,日程) で祝日の
$D29$AH29<>"" で該当欄($D29$AH29)の空白でないセル数を数えています。
以下、下にコピーします。
OKWaveで随分教えて頂きましたので少しでもお役に立てればと思います。
この式もOKWaveをヒントに考えたものです。
シルバー世代(68歳)ですが頑張っています。
この回答への補足
回答ありがとうございます。私も教えてもらうばかりではなく、人に教える様になりたいものです。
=SUMPRODUCT(((WEEKDAY(D29:AH29,2)>=6)+COUNTIF(祝日,D29:AH29))*($D29:$AH29<>""))
でトライしましたが、#VALUE!エラーが出ます。教えてください。
No.3
- 回答日時:
続けておじゃまします。
月ごとに数式を変えたくないというコトなので・・・
苦肉の策です。
SUMPRODUCT関数で数式が入った空白セルを範囲に含んだ場合はエラーになるようなので、
小の月の月末を空白にするのではなく、数値にしてセルの表示形式の操作でフォント色を「白」にする方法です。
※ シリアル値で0は土曜日・1は日曜日となりますので、「2」を表示させ、フォント色を「白」にする設定とします。
そこで表の式に手を加えさせていただきます。
D26セルの表示形式をユーザー定義から
[白][=2]G/標準;d
としておき
=IF(MONTH(DATE($D$1+1988,$F$1,COLUMN(A1)))=$F$1,DATE($D$1+1988,$F$1,COLUMN(A1)),2)
という数式を入力
D27セル(セルの表示形式はユーザー定義から aaa としておきます)は
=IF(D26=2,"",D26)
という数式を入れD26・D27セルを範囲指定 → D27セルのフィルハンドルで月末のAH列までオートフィルでコピー!
D28(28行目)の数式はそのままでOKです。
これで前回の数式がそのまま利用できると思います。
C29セルに
=SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)>=6)*(D29:AH29<>""))+SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)<6)*($D$28:$AH$28<>"")*(D29:AH29<>""))
という数式を入れオートフィルで下へコピーしてみてください。
おそらく大の月・小の月も対応できるはずです。
※ 結局いま入っているIF関数の「空白」の場合は「2」(2以外のシリアル値が土日以外でもよい)
を表示させるだけです。
お役に立ちますかね?m(_ _)m
この回答への補足
回答ありがとうございます。返事が遅れまして申し訳ありません。
まだ、トライしていませんが、エラーが出ないなら利用させていただきます。これからトライします。
No.2
- 回答日時:
No.1です!
前回の数式で間違いがありました。
列の範囲指定に空白列が入っているとエラーになってしまいますので、
お示しの画像の場合(小の月)は
数式内のAH部分をAGに変更してください。
大の月だけAHに変更する必要があります。
(2月はその年に適宜合わせてみてください)
検証せずに投稿してごめんなさいね。m(_ _)m
この回答への補足
回答ありがとうございます。
カレンダーについては別セルを参照して大の月、小の月を自動判別して表示しております。
プログラムの知識のないオペレーターが月毎に式を「いじる」のは好ましくないのでエラーのない式ができればと思います。
No.1
- 回答日時:
こんばんは!
画像では30日のAG列までしかありませんが、実際は大の月の場合31日のAH列までになると思いますので、
AH列までの範囲としてみました。
小の月の場合月末列は空白にとなる前提です。
C29セルに
=SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)>=6)*(D29:AH29<>""))+SUMPRODUCT((WEEKDAY($D$26:$AH$26,2)<6)*($D$28:$AH$28<>"")*(D29:AH29<>""))
という数式を入れオートフィルで下へコピーではどうでしょうか?m(_ _)m
この回答への補足
早速の回答ありがとうございます。
11月のカレンダーなので31日が抜けておりました。大の月は31日まで表示します。
さて、回答を当てはめてみましたが#VALUE!エラーが表示されうまくいきません。よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) マクロ/VBAについて教えてください。 10 2022/05/27 12:59
- Excel(エクセル) エクセルの数式について教えてください。 1 2023/03/21 09:46
- Excel(エクセル) エクセルの数式で教えてください。 4 2023/06/27 09:56
- Excel(エクセル) エクセルの数式で教えてください。 1 2023/02/28 08:59
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/01/25 11:42
- Excel(エクセル) 出勤簿の土、日、休日に色付けできない 2 2022/08/04 20:10
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
エクセルやワードを無料で使え...
-
現在、PC2台でMicrosoft 365 Pe...
-
英数字のみ全角から半角に変換
-
Microsoftにofficeアプリについ...
-
大学のレポート A4で1枚レポー...
-
Office2021を別のPCにインスト...
-
エクセルでXLOOKUP関数...
-
会社のPCに入っているExcelでバ...
-
Windows 11で、IME言語バー(IM...
-
以下マクロの処理を最終行まで...
-
Microsoft Formsの「個人情報や...
-
Outlook で宛先が複数の場合の人数
-
teams設定教えて下さい。 ①ビデ...
-
Microsoft365で写真をアルバム...
-
マクロ自動コピペ 貼り付ける場...
-
VBAファイルの保存先について
-
エクセルで英文字に入れた下線...
-
複数の写真を1枚に印刷
-
Excel 日付を比較したら、同じ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
「生産性ソフトウェア」とは何...
-
会社PCのメールが更新されない
-
【関数】○年○ヶ月と表示された...
-
WEBの記事を印刷する際にA...
-
エクセルでXLOOKUP関数...
-
Microsoft familyに追加されま...
-
会社のOutlookにてメールを予約...
-
Microsoft Formsの「個人情報や...
-
Microsoft365の一部を解約したい
-
マクロ自動コピペ 貼り付ける場...
-
Outlook で宛先が複数の場合の人数
-
outlookのメールが固まってしま...
-
【Excel VBA】PDFを作成して,...
-
大学のレポート A4で1枚レポー...
-
office365って抵抗感ないですか?
-
Microsoftにofficeアプリについ...
-
Excel テーブル内の空白行の削除
-
マイクロソフト 一時使用コード...
おすすめ情報