teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録
トップGoogle スプレッドシートに関する質問【googleスプレッドシート】今日を基準に指定された期限が1年を切ったらハイライトさせる条件付き書式

編集履歴

回答編集履歴

6

修正

2021/12/03 03:44

投稿

退会済みユーザー
test CHANGED
@@ -88,11 +88,11 @@
88
88
 
89
89
  よってセルB1が「2021/10/21」ならば、条件付き書式に指定したカスタム数式は
90
90
 
91
- =COUNTIF(D2,"<2021/10/21”)
91
+ =COUNTIF(D2,"<2023/10/21”)
92
92
 
93
93
  と計算されるため、
94
94
 
95
- D2に指定したセルが2021/10/21以前ならば「１」が返ってきます。
95
+ D2に指定したセルが2023/10/21より前ならば「１」が返ってきます。
96
96
 
97
97
  条件付き書式では、計算結果がゼロでなければ条件＝正という意味になるので、
98
98
 

5

2021/12/03 03:44

投稿

退会済みユーザー
test CHANGED
@@ -144,4 +144,4 @@
144
144
 
145
145
  　
146
146
 
147
- したがって、B!セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。
147
+ したがって、B1セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。

4

2021/12/02 14:47

投稿

退会済みユーザー
test CHANGED
@@ -124,6 +124,8 @@
124
124
 
125
125
  ---
126
126
 
127
+
128
+
127
129
  前半の
128
130
 
129
131
  ```
@@ -136,10 +138,10 @@
136
138
 
137
139
 
138
140
 
139
- D2セルにうるう年の2月29日を指定した場合、
141
+ B1セルにうるう年の2月29日を指定した場合、
140
142
 
141
143
  その2年後に29日が存在しないため、正しく計算されません。
142
144
 
143
145
  　
144
146
 
145
- したがって、D2セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。
147
+ したがって、B!セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。

3

2021/12/02 14:46

投稿

退会済みユーザー
test CHANGED
@@ -138,7 +138,7 @@
138
138
 
139
139
  D2セルにうるう年の2月29日を指定した場合、
140
140
 
141
- その2年前には同じ日が存在しないため、正しく計算されません。
141
+ その2年後に29日が存在しないため、正しく計算されません。
142
142
 
143
143
  　
144
144
 

2

追加

2021/12/02 14:43

投稿

退会済みユーザー
test CHANGED
@@ -23,3 +23,123 @@
23
23
 
24
24
 
25
25
  ![イメージ説明](5d0316e387bf5a21fd9feb669f2eec6d.png)
26
+
27
+
28
+
29
+ ---
30
+
31
+ # 設定内容の詳細について
32
+
33
+ 数式後半の
34
+
35
+ COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))
36
+
37
+ から解説します。
38
+
39
+ 　
40
+
41
+ COUNTIFは、第一引数に指定したセル範囲のうち、第二引数の条件にあてはまるセルの数を返す関数です。
42
+
43
+ たとえば
44
+
45
+ ```
46
+
47
+ =COUNTIF(A2:A100,"<10")
48
+
49
+ ```
50
+
51
+ とした場合
52
+
53
+ **A2:A100の範囲で、10より小さい数を持つセルの個数**が返ってきます。
54
+
55
+
56
+
57
+ 第二引数には日付条件を付けることができます。
58
+
59
+ たとえば
60
+
61
+ =COUNTIF(A2:A100,"<2021/10/21")　とすれば
62
+
63
+ A2:A100の範囲で、2021年10月21日より前の日付をもつセルの個数が返ってきます。
64
+
65
+
66
+
67
+ ```
68
+
69
+ COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))
70
+
71
+ ```
72
+
73
+ の後半「year($B$1)+2&"/"&month($B$1)&"/"&day($B$1))」
74
+
75
+ は、
76
+
77
+ セルB1に指定した年月日から、「年」を取り出して+2することで、「B1セルの2年後の日付」を作成しています。
78
+
79
+ 　
80
+
81
+ たとえばセルB1が「2021/10/21」ならば
82
+
83
+ year($B$1)+2&"/"&month($B$1)&"/"&day($B$1))
84
+
85
+ は「2023/10/21」になります
86
+
87
+ 　
88
+
89
+ よってセルB1が「2021/10/21」ならば、条件付き書式に指定したカスタム数式は
90
+
91
+ =COUNTIF(D2,"<2021/10/21”)
92
+
93
+ と計算されるため、
94
+
95
+ D2に指定したセルが2021/10/21以前ならば「１」が返ってきます。
96
+
97
+ 条件付き書式では、計算結果がゼロでなければ条件＝正という意味になるので、
98
+
99
+ カスタム数式の計算結果で「１」が返ってくる場合、指定した背景色が塗られます。
100
+
101
+ 　　
102
+
103
+ また、複数の範囲を選択して条件付き書式を設定した場合、数式に指定したセル番地は自動的に
104
+
105
+ 順番がずれてコピーされます。
106
+
107
+
108
+
109
+ 上記の場合、D2:D40の範囲を選択して条件付き書式を設定したので、
110
+
111
+ D2セルのカスタム数式には「=COUNTIF(D2,～)」
112
+
113
+ D3セルのカスタム数式には「=COUNTIF(D3,～)」
114
+
115
+ D4セルのカスタム数式には「=COUNTIF(D4,～)」
116
+
117
+ ....
118
+
119
+ ...
120
+
121
+ というように、D40セルまで順番に自動的に設定されることになります。
122
+
123
+
124
+
125
+ ---
126
+
127
+ 前半の
128
+
129
+ ```
130
+
131
+ if(and(month($B$1)=2,day($B$1)=29),COUNTIF(D2,"<"&year($B$1)+2&"/3/1")
132
+
133
+ ```
134
+
135
+ の部分は、うるう年を考慮したものです。
136
+
137
+
138
+
139
+ D2セルにうるう年の2月29日を指定した場合、
140
+
141
+ その2年前には同じ日が存在しないため、正しく計算されません。
142
+
143
+ 　
144
+
145
+ したがって、D2セルに2月29日が指定された場合は、「2年後の3月1日より前」という条件を作っています。

1

修正

2021/12/02 14:42

投稿

退会済みユーザー
test CHANGED
@@ -16,7 +16,7 @@
16
16
 
17
17
  ```
18
18
 
19
- =if(and(month($B$1)=2,day($B$1)=29),COUNTIF(D2,">"&year($B$1)-2&"/2/28"),COUNTIF(D2,">"&year($B$1)-2&"/"&month($B$1)&"/"&day($B$1)))
19
+ =if(and(month($B$1)=2,day($B$1)=29),COUNTIF(D2,"<"&year($B$1)+2&"/3/1"),COUNTIF(D2,"<"&year($B$1)+2&"/"&month($B$1)&"/"&day($B$1)))
20
20
 
21
21
  ```
22
22