この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。
広告
posted by fanblog
2024年01月21日
エクセルの条件付き書式、訳がワカラン！！＞ 曜日を出力。日曜祝日を網掛け 実現！！
会計係から渡された、上司の出勤日を報告するための
月報のフォーマット（書き込み用紙）を改良してました。
会計係の如何にもデキる雰囲気を醸し出す事務嬢が、
「書式が変わりましたからこれでお願いします」
とわざわざＵＳＢメモリ持参で持って来たから、
さぞや凝った作りをしているのだろうな、
とファイルを開いてみたら、エクセルでワープロのように作った表、だった。
毎回、これを曜日と月を手動で書き替えて、印刷して出せ、ということらしい。
もうね、アホかと。
なんかマイクロソフトか何かの資格を取ったと言っていたが、ほんとかなぁ。
というわけで作り始めましたよ。自分が楽をするために。
印刷エリア以外の所に西暦と報告月を入力するセルを設けて、そこを絶対参照で日付を式で生成し書類上の日にちのセルに表示させる。
更に、表示型式で曜日を出力する。日付の入力されたセルで表示形式を"aaa"を与えると簡単にその日付に対応する曜日を得ることが出来る。
出力された曜日が「日」だった時、そのセルを網掛けするよう、条件付き書式を設定する。
別のシートに今年の祝日をコピペしておいて、名前を「旗日」と定義し、
旗日の時には日にちのセルを網掛けする
という概念に問題はないはずなのだが、どうもうまく条件付き書式が作動しない。
相対参照にして書式コピーでコピペしても、式中で相対にしておいたセル番地が意図したように変化しない。
エクセル２００３になってから、この辺がとても使いづらくなった。
今日は不退転の覚悟で、原因究明に当たった。
このハードルを越えないと、ずっと悩むことになる。
で——、結局以下のことが分かった。
https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel2010/f9370114-36c9-461d-924f-427d2f436b1f
条件付き書式は範囲の最初のレコードで定義する。ややこしく慣れないな。
以下をエクセルの新シートのA1に貼り付ければ私がやってたことが分かるかも。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
2024 年 1 月←これを入力する
出 社 報 告 書 ="（"&$C$1&"月分）"
=JIS(IF($C$11,"令和"&TEXT($A$1-2019,"#")&"年１２月２１日〜令和"&TEXT($A$1-2018,"#")&"年１月２０日",
"令和"&TEXT($A$1-2018,"#")&"年"&$C$1-1&"月２１日〜"&$C$1&"月２０日")) =JIS("令和"&TEXT($A$1-2018,"#")&"年"&$C$1&"月20日報告")
日 曜 出社印 備 考 泊 日 曜 出社印 備 考 泊
21 =IF($C$11,TEXT(DATE($A$1-1,12,$A8),"aaa"),TEXT(DATE($A$1,$C$1-1,$A8),"aaa")) 6 =TEXT(DATE($A$1,$C$1,$G8),"aaa")
22 =IF($C$11,TEXT(DATE($A$1-1,12,$A9),"aaa"),TEXT(DATE($A$1,$C$1-1,$A9),"aaa")) 7 =TEXT(DATE($A$1,$C$1,$G9),"aaa")
23 =IF($C$11,TEXT(DATE($A$1-1,12,$A10),"aaa"),TEXT(DATE($A$1,$C$1-1,$A10),"aaa")) 8 =TEXT(DATE($A$1,$C$1,$G10),"aaa")
24 =IF($C$11,TEXT(DATE($A$1-1,12,$A11),"aaa"),TEXT(DATE($A$1,$C$1-1,$A11),"aaa")) 9 =TEXT(DATE($A$1,$C$1,$G11),"aaa")
25 =IF($C$11,TEXT(DATE($A$1-1,12,$A12),"aaa"),TEXT(DATE($A$1,$C$1-1,$A12),"aaa")) 10 =TEXT(DATE($A$1,$C$1,$G12),"aaa")
26 =IF($C$11,TEXT(DATE($A$1-1,12,$A13),"aaa"),TEXT(DATE($A$1,$C$1-1,$A13),"aaa")) 11 =TEXT(DATE($A$1,$C$1,$G13),"aaa")
27 =IF($C$11,TEXT(DATE($A$1-1,12,$A14),"aaa"),TEXT(DATE($A$1,$C$1-1,$A14),"aaa")) 12 =TEXT(DATE($A$1,$C$1,$G14),"aaa")
28 =IF($C$11,TEXT(DATE($A$1-1,12,$A15),"aaa"),TEXT(DATE($A$1,$C$1-1,$A15),"aaa")) 13 =TEXT(DATE($A$1,$C$1,$G15),"aaa")
29 =IF($C$11,TEXT(DATE($A$1-1,12,$A16),"aaa"),TEXT(DATE($A$1,$C$1-1,$A16),"aaa")) 14 =TEXT(DATE($A$1,$C$1,$G16),"aaa")
30 =IF($C$11,TEXT(DATE($A$1-1,12,$A17),"aaa"),TEXT(DATE($A$1,$C$1-1,$A17),"aaa")) 15 =TEXT(DATE($A$1,$C$1,$G17),"aaa")
31 =IF($C$11,TEXT(DATE($A$1-1,12,$A18),"aaa"),TEXT(DATE($A$1,$C$1-1,$A18),"aaa")) 16 =TEXT(DATE($A$1,$C$1,$G18),"aaa")
1 =TEXT(DATE($A$1,$C$1,$A19),"aaa") 17 =TEXT(DATE($A$1,$C$1,$G19),"aaa")
2 =TEXT(DATE($A$1,$C$1,$A20),"aaa") 18 =TEXT(DATE($A$1,$C$1,$G20),"aaa")
3 =TEXT(DATE($A$1,$C$1,$A21),"aaa") 19 =TEXT(DATE($A$1,$C$1,$G21),"aaa")
4 =TEXT(DATE($A$1,$C$1,$A22),"aaa") 20 =TEXT(DATE($A$1,$C$1,$G22),"aaa")
5 =TEXT(DATE($A$1,$C$1,$A23),"aaa")
／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼
名前定義「旗日」←別のシートにコピペ
1月1日 月 元日
1月8日 月 成人の日
2月11日 日 建国記念の日
2月12日 月 休日
2月23日 金 天皇誕生日
3月20日 水 春分の日
4月29日 月 昭和の日
5月3日 金 憲法記念日
5月4日 土 みどりの日
5月5日 日 こどもの日
5月6日 月 休日
7月15日 月 海の日
8月11日 日 山の日
8月12日 月 休日
9月16日 月 敬老の日
9月22日 日 秋分の日
9月23日 月 休日
10月14日 月 スポーツの日
11月3日 日 文化の日
11月4日 月 休日
11月23日 土 勤労感謝の日
／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼
条件書式の設定
セルA8:B19に
=VLOOKUP(DATE($A$1,$C$1-1,A8),旗日,1,FALSE)=DATE($A$1,$C$1-1,A8)
※定義された「旗日」データとカレントセルが合致したら、というトリガーで 網掛け
セルA20:B23に
=VLOOKUP(DATE($A$1,$C$1,A19),旗日,1,FALSE)=DATE($A$1,$C$1,A19)
※上記と一緒だが、A20以前は前月なので月から１を引いているが、ここからは今月なので-1をしない。
セルA8:B23に
=B8="日"
※曜日表記が「日」だったら、網掛けをしろ
右半分の説明は省略。自分で考えよう。
月報のフォーマット（書き込み用紙）を改良してました。
会計係の如何にもデキる雰囲気を醸し出す事務嬢が、
「書式が変わりましたからこれでお願いします」
とわざわざＵＳＢメモリ持参で持って来たから、
さぞや凝った作りをしているのだろうな、
とファイルを開いてみたら、エクセルでワープロのように作った表、だった。
毎回、これを曜日と月を手動で書き替えて、印刷して出せ、ということらしい。
もうね、アホかと。
なんかマイクロソフトか何かの資格を取ったと言っていたが、ほんとかなぁ。
というわけで作り始めましたよ。自分が楽をするために。
印刷エリア以外の所に西暦と報告月を入力するセルを設けて、そこを絶対参照で日付を式で生成し書類上の日にちのセルに表示させる。
更に、表示型式で曜日を出力する。日付の入力されたセルで表示形式を"aaa"を与えると簡単にその日付に対応する曜日を得ることが出来る。
出力された曜日が「日」だった時、そのセルを網掛けするよう、条件付き書式を設定する。
別のシートに今年の祝日をコピペしておいて、名前を「旗日」と定義し、
旗日の時には日にちのセルを網掛けする
という概念に問題はないはずなのだが、どうもうまく条件付き書式が作動しない。
相対参照にして書式コピーでコピペしても、式中で相対にしておいたセル番地が意図したように変化しない。
エクセル２００３になってから、この辺がとても使いづらくなった。
今日は不退転の覚悟で、原因究明に当たった。
このハードルを越えないと、ずっと悩むことになる。
で——、結局以下のことが分かった。
https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel2010/f9370114-36c9-461d-924f-427d2f436b1f
条件付き書式は範囲の最初のレコードで定義する。ややこしく慣れないな。
以下をエクセルの新シートのA1に貼り付ければ私がやってたことが分かるかも。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
2024 年 1 月←これを入力する
出 社 報 告 書 ="（"&$C$1&"月分）"
=JIS(IF($C$11,"令和"&TEXT($A$1-2019,"#")&"年１２月２１日〜令和"&TEXT($A$1-2018,"#")&"年１月２０日",
"令和"&TEXT($A$1-2018,"#")&"年"&$C$1-1&"月２１日〜"&$C$1&"月２０日")) =JIS("令和"&TEXT($A$1-2018,"#")&"年"&$C$1&"月20日報告")
日 曜 出社印 備 考 泊 日 曜 出社印 備 考 泊
21 =IF($C$11,TEXT(DATE($A$1-1,12,$A8),"aaa"),TEXT(DATE($A$1,$C$1-1,$A8),"aaa")) 6 =TEXT(DATE($A$1,$C$1,$G8),"aaa")
22 =IF($C$11,TEXT(DATE($A$1-1,12,$A9),"aaa"),TEXT(DATE($A$1,$C$1-1,$A9),"aaa")) 7 =TEXT(DATE($A$1,$C$1,$G9),"aaa")
23 =IF($C$11,TEXT(DATE($A$1-1,12,$A10),"aaa"),TEXT(DATE($A$1,$C$1-1,$A10),"aaa")) 8 =TEXT(DATE($A$1,$C$1,$G10),"aaa")
24 =IF($C$11,TEXT(DATE($A$1-1,12,$A11),"aaa"),TEXT(DATE($A$1,$C$1-1,$A11),"aaa")) 9 =TEXT(DATE($A$1,$C$1,$G11),"aaa")
25 =IF($C$11,TEXT(DATE($A$1-1,12,$A12),"aaa"),TEXT(DATE($A$1,$C$1-1,$A12),"aaa")) 10 =TEXT(DATE($A$1,$C$1,$G12),"aaa")
26 =IF($C$11,TEXT(DATE($A$1-1,12,$A13),"aaa"),TEXT(DATE($A$1,$C$1-1,$A13),"aaa")) 11 =TEXT(DATE($A$1,$C$1,$G13),"aaa")
27 =IF($C$11,TEXT(DATE($A$1-1,12,$A14),"aaa"),TEXT(DATE($A$1,$C$1-1,$A14),"aaa")) 12 =TEXT(DATE($A$1,$C$1,$G14),"aaa")
28 =IF($C$11,TEXT(DATE($A$1-1,12,$A15),"aaa"),TEXT(DATE($A$1,$C$1-1,$A15),"aaa")) 13 =TEXT(DATE($A$1,$C$1,$G15),"aaa")
29 =IF($C$11,TEXT(DATE($A$1-1,12,$A16),"aaa"),TEXT(DATE($A$1,$C$1-1,$A16),"aaa")) 14 =TEXT(DATE($A$1,$C$1,$G16),"aaa")
30 =IF($C$11,TEXT(DATE($A$1-1,12,$A17),"aaa"),TEXT(DATE($A$1,$C$1-1,$A17),"aaa")) 15 =TEXT(DATE($A$1,$C$1,$G17),"aaa")
31 =IF($C$11,TEXT(DATE($A$1-1,12,$A18),"aaa"),TEXT(DATE($A$1,$C$1-1,$A18),"aaa")) 16 =TEXT(DATE($A$1,$C$1,$G18),"aaa")
1 =TEXT(DATE($A$1,$C$1,$A19),"aaa") 17 =TEXT(DATE($A$1,$C$1,$G19),"aaa")
2 =TEXT(DATE($A$1,$C$1,$A20),"aaa") 18 =TEXT(DATE($A$1,$C$1,$G20),"aaa")
3 =TEXT(DATE($A$1,$C$1,$A21),"aaa") 19 =TEXT(DATE($A$1,$C$1,$G21),"aaa")
4 =TEXT(DATE($A$1,$C$1,$A22),"aaa") 20 =TEXT(DATE($A$1,$C$1,$G22),"aaa")
5 =TEXT(DATE($A$1,$C$1,$A23),"aaa")
／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼
名前定義「旗日」←別のシートにコピペ
1月1日 月 元日
1月8日 月 成人の日
2月11日 日 建国記念の日
2月12日 月 休日
2月23日 金 天皇誕生日
3月20日 水 春分の日
4月29日 月 昭和の日
5月3日 金 憲法記念日
5月4日 土 みどりの日
5月5日 日 こどもの日
5月6日 月 休日
7月15日 月 海の日
8月11日 日 山の日
8月12日 月 休日
9月16日 月 敬老の日
9月22日 日 秋分の日
9月23日 月 休日
10月14日 月 スポーツの日
11月3日 日 文化の日
11月4日 月 休日
11月23日 土 勤労感謝の日
／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼／＼
条件書式の設定
セルA8:B19に
=VLOOKUP(DATE($A$1,$C$1-1,A8),旗日,1,FALSE)=DATE($A$1,$C$1-1,A8)
※定義された「旗日」データとカレントセルが合致したら、というトリガーで 網掛け
セルA20:B23に
=VLOOKUP(DATE($A$1,$C$1,A19),旗日,1,FALSE)=DATE($A$1,$C$1,A19)
※上記と一緒だが、A20以前は前月なので月から１を引いているが、ここからは今月なので-1をしない。
セルA8:B23に
=B8="日"
※曜日表記が「日」だったら、網掛けをしろ
右半分の説明は省略。自分で考えよう。
