スプレッドシート（エクセル） 条件付き書式のルールについて

スプレッドシートで体重の管理表を作成しています。（氏名はダミーです）
当月の入力がまだの方のお名前を色付けする条件付き書式で作成をしています。

2行目の月はそれぞれ2024/8/1といった入力を書式設定で８月として表示しています。

４行目以降のB列の名前について、当月の体重が未入力の場合、条件付き書式で
=and(B4<>"",OFFSET(A4,0,(month(today())-7)*3)="")　として、
該当者に色がつくよう設定をしています。

ただ、この場合、年が明けた１月以降、month以降の計算式が当てはまらずに
色がつかないことがわかりました。

１月以降もこちらの表を継続（右に列を追加していく）ことになりますので、
このエラーを修正したいと考えています。
どのような方法ですと、年明け以降も継続して条件付き書式が作動するでしょうか？
教えてください、よろしくお願いします。

No.1 ベストアンサー 回答者： otasuke3900z 回答日時： 2024/10/08 20:29

条件付き書式を使って、

年明け以降も体重の管理表で未入力者を色付けするためには、
月の計算式を調整する必要があります。
現在の式では、
1月以降に正しく機能しないため、以下の手順で修正できます。

修正方法
カスタム数式の変更:
現在使用している式
=AND(B4<>"", OFFSET(A4, 0, (MONTH(TODAY())-7)*3)="") は、
1月になると MONTH(TODAY()) が1になり、
計算がうまくいきません。この問題を解決するために、
次のように変更します。

=AND(B4<>"", OFFSET(A4, 0, (MONTH(TODAY())-1)*3)="")

ここで、(MONTH(TODAY())-1) により、1月の場合は0になります。
このため、最初の列（8月）のデータが適切に参照されます。

年を考慮したオフセット:
年が変わる場合も考慮し、列のオフセットを動的に
計算する方法もあります。例えば、
次のような式を使うことができます。


=AND(B4<>"", OFFSET(A4, 0, (YEAR(TODAY())-2024)*12 + (MONTH(TODAY())-8)) * 3)="")

この式では、基準年（2024年）から現在の年を引き、
その差を12倍して年数分の列オフセットを計算します。

さらに、現在の月から8月を引いて、その結果を足すことで、
正しい列を参照します。

実装手順
スプレッドシートを開きます。
条件付き書式の設定画面に移動します。
上記の新しい数式を入力します。
書式スタイル（色など）を設定し、「完了」をクリックします。
この設定により、1月以降も体重が未入力な方の
お名前が正しく色付けされるようになります。

以上、参考になれば幸いです。
( ´ー｀)y-~~

この回答へのお礼

回答ありがとうござい巻いた。
丁寧な解説で理解できました。
2025年以降も継続して使用できました。

お礼日時：2024/10/11 11:50

No.3 回答者： tatsumaru77 回答日時： 2024/10/08 22:18

来年の８月以降は、どうなるのでしょうか。

①元のD列に戻る。（８月～７月の循環形式）
　
②来年の７月の右側の列（AN列）に割り当てる。（延々と列を増加する形式）

①と②のどちらになりますか。
それによって、計算式も異なります。

No.2 回答者： fujillin 回答日時： 2024/10/08 22:15

こんばんは

ご提示の式は、「月」の数値を3倍してセル位置を求めていますので、翌年になると上手く行かないのはその通りすね。
（参照先が元のセル位置に戻ってしまいます）

＞2行目の月はそれぞれ2024/8/1といった入力を書式設定で～～
せっかく年月日を入力しているのなら、それを利用して検索するようにしてはいかがでしょうか。
月の入力欄がセルの結合をしてあるのかどうか、図からはハッキリしませんけれど、仮に、結合はなくD2、G2、J2・・・のセルに入力してあるのなら、その列位置は
　=MATCH(EOMONTH(TODAY(),0),$2:$2,1)
で、求めることができます。
（年月で検索しているので確実です。ただし、記入欄は右に行くほど年月は後になっている（＝昇順）と仮定しています。）

2行目の年月日が必ず1日付けで入力されている保証があるなら、上式の
　EOMONTH(TODAY(),0)　は単に
　TODATY()
と短くしても計算できます。
（2行目で「2024/8/1」が「2024/8/10」等の入力（＝表示では「8月」）になっていると、EOMONTHで計算しないとずれることがあります）


実際の入力欄の値は、ご提示のようにOFFSETで求めても良いですが、OFFSETは揮発性関数なので、例えば、INDEXに代えて
　=INDEX(4:4,MATCH(EOMONTH(TODAY(),0),$2:$2,1))
あるいは
　=INDEX(4:4,MATCH(TODAY(),$2:$2,1))
とすることでも求めることが可能です。
いずれにしろTODAYが揮発性なので、あまり意味はないですが（笑）

※ もしも、「月」の入力セルが結合セルで、C、E、I・・列の場合は、検索結果が1列分ずれますので、+1 するなどの調整をしてください。

この回答へのお礼

回答ありがとうございました。
当初、indexとmatchも検討したのですが、こちらの勉強不足で今一つ活用に至らずでした。もう少し知識を高めてこちらを活用したいと思います。教えていただき、ありがとうございます。

お礼日時：2024/10/11 11:53