진행 상태 표시 - 완료, 지연, 진행필요, 미완료(2)

진행상태 표시(완성)1.xlsx

0.01MB

 
 
1편에서 만든 수식을 경우에 알맞게 조합하면 됩니다.
그리고, if문을 여러 번 중복해서 사용해야 하며,
if문을 중복해서 사용한다는 것은 if함수 안에서 다시 if를 사용하는 것입니다.

 
 
예를 들어 아래 화면에서 AA6셀의 수식은
=IF(AA5=1,"1임",IF(AA5>1,"1보다 큼"))로서
AA5셀의 값이 1이면 "1임"이라고 표시(반환)하고,
(1이 아니고) 1보다 크면 "1보다 큼"이라고 표시(반환)하는 것입니다.

 
위의 경우 AA5셀의 값이 2이므로 "1보다 큼"이라고 AA6셀에 값이 표시됩니다.
 
이런 식으로 2번뿐만 아니라 여러 번 if함수를 중첩적으로 사용할 수 있습니다.
 

1. 1월인 경우

1월인 경우 참인 값(value_if_true)에 계획과 수행이면 "완료", 아니면 "진행필요"라고 표시하고,

1월이 아니면(value_if_false) 일단 ""(공란)으로 만들겠습니다.

=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),"")

 
위의 경우 7행과 8행은 계획과 수행이 있어서 '완료'라고 표시되고, 9행은 계획만 있어서 '진행필요'라고 표시됩니다.,
 
그리고, 기준일을 2월로 바꾸면 거짓인 경우에 ""(공백)"으로 했으므로 모두 빈칸이 됐습니다.

 
 

2. 1월이 아닌 경우

위 수식에서 "" 부분에 if함수를 다시 사용합니다.

이 때는 조건이 좀 복잡해서 기준 월이 "계획인 경우"와 "계획이 없는 경우"로 구분해야 합니다.
 
여기까지 하면
=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))="계획","계획","계획 없음"))이 됩니다.

 

3. 기준월이 '계획'인 경우 처리

기준월이 '계획'인 경우
기준월이 '수행'이면 완료인데,
 
Act 란이 비어 있다면
전월까지의 실적이 완료인 경우는 '미완료'가 되고,
완료가 아닌 경우는 '지연'으로 표시돼야 하므로
 if 안에 다시 if가 들어가야 합니다.

가. 기준월에 계획과 수행이 있는 경우

=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))="계획",IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1)="수행","완료","없음"),"계획 없음"))
 
"계획"부분을
IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1)="수행","완료","없음")로 대체했습니다.

 
계획과 수행이 있는 경우는 완료, 기준일에 계획이 없는 경우는 '계획 없음'이라고 표시됩니다.
 
그런데 9행은 당월이 계획대로 수행되었다 하더라도 1월이 수행되지 않았으므로 '지연'으로 표시돼야 하는데 '완료'라고 표시되므로 당월의 Act란만 비교하면 안 되고 당월까지의 계획과 수행 개수를 비교하는 것으로 수정해야 합니다.

 

=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))="계획",IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료", ,"없음"),"계획 없음"))

 
7행에서 수행을 지우니 "없음"이라고 표시됩니다.

 

나. 기준월에 계획이 있지만 Act가 비어 있는 경우

전월까지의 계획과 수행 개수를 비교해서 같다면 "미완료" 아니면 "지연"이라고 표시해야 하며,
"없음"부분에 if함수를 추가합니다.
 
=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))="계획",IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료",IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"수행"),"미완료","지연")),"계획 없음"))

 
2월 Act에 다시 '수행'을 입력하면
'미완료'가 '완료'로 바뀝니다.
 

다. 기준월에 계획이 없는 경우

기준월에 계획이 없으면 전월까지의 계획과 수행 개수를 비교해서 같으면 "완료", 아니면 "지연"이라고 표시하면 됩니다.
 
if함수를 이용한 수식을 "계획 없음"부분에 넣으면 됩니다.
=IF(MONTH($B$4)=1,IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료","진행필요"),IF(OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))="계획",IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행"),"완료",IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"수행"),"미완료","지연")),IF(COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"계획")=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"수행"),"완료","지연")))
 
그러니까 8행이 "계획 없음"에서 "완료"로 바뀌었습니다.

 
월을 3월로 변경하니 "완료", "미완료", "지연"으로 잘 표시됩니다.

 
1월로 변경하니 "완료", "완료", "진행 필요"라고 제대로 표시됩니다.

 

진행상태 표시(완성)2.xlsx

0.01MB

복잡한 것도 한 단계씩 해나가면 됩니다.
너무 처음부터 겁먹을 필요가 없습니다.