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

진행상태 표시(문제).xlsx

0.01MB

 
프로젝트별로 계획 대비 수행 실적을 비교해서 완료, 지연, 진행 필요, 미완료를 표시하려고 합니다.
 
날짜에 따라 진행 상태를 확인해야 하므로 B4셀에 기준일이 있습니다.
 

1. 경우의 수

프로그램이든 엑셀이든 맞는 값을 찾으려면 모든 경우의 수를 찾는 것이 중요합니다.
 
위 경우에 먼저 1월과 2월 이후로 나눠야 합니다.
 
따라서, 1월인 경우 계획 대비 수행을 모두 완료했다면 '완료'이고,
계획 대비 수행을 완료하지 않았다면 '진행필요'가 됩니다.
 
그리고, 2월 이후는
 
기준 월에 계획이 있는지 여부에 따라
있다면
① 기준월까지의 계획 대비 수행이 모두 됐다면 '완료'이고,
② 전월까지 계획 대비 수행이 모두 됐다면 '미완료'이고,
③ 전월까지 계획 대비 수행이 모두 완료되지 않았다면 '지연'이 됩니다.
 
없다면
① 전월까지 계획 대비 수행이 모두 됐다면 '완료'이고,
② 전월까지 계획 대비 수행이 모두 완료되지 않았다면 '지연'이 됩니다.
 

2. 기본적인 수식

위와 같이 조건이 복잡하기 때문에 기본적인 수식을 미리 만들어 놓는 것이 좋습니다.
 
수식으로 필요한 것은 기준 월과 전월 찾기,
기준월까지의 계획과 수행의 개수,
그리고, 전월까지의 계획과 수행의 개수가 필요합니다.
 

가. 기준월의 위치 구하기

기준월은 기준일자에 해당하는 월을 5행 다시 말해, B5에서 Y5에서 찾으면 되는데,
찾는 함수는 Match를 사용합니다.
 
수식은
=MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)
이 됩니다.
 
기준월은 날짜에서 월을 추출해야 하므로 Month함수를 사용하고,
찾는 월은 1월, 2월식으로 되어 있으므로 숫자 부분을 분리해 내야 하는데,
왼쪽부터 월의 길이에서 1을 뺀 개수만큼 가져와야 합니다.
 
그리고, Month($B$4)는 숫자인데, LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1) 까지 하면 이것은 문자이므로 에러가 발생합니다.

 
따라서, LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1) 를 숫자로 바꾸기 위해 *1을 한 것입니다.
 
그러면 3이 구해지는데, 셀 병합이 됐기 때문에 2월이 3이 되는 것입니다.

 

나. 기준 월의 계획 셀 위치 찾기

이번에는 7행에서 2월의 계획 셀인 D7을 구해보겠습니다.
현재 셀에서 오른쪽으로 3번째이므로 Offset함수를 사용하는데,
Offset함수의 구문은 OFFSET(reference, rows, cols, [height], [width])으로
reference는 참조 셀, 기준이 되는 셀이며,
rows는 행 방향으로 이동할 셀 수로 현재 위치는 0, 아래는 +, 위는 -입니다.
cols는 열 방향으로 이동하는 것으로 오른쪽으로 이동할 때는 +, 왼쪽으로 이동할 때는 -입니다.
height는 높이를 지정하는 것이며,
width는 너비를 지정하는 것입니다.
 
따라서, A7셀에서 오른쪽으로 세 번째 셀이므로
=OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0))
라고 입력하면 됩니다.

 
같은 행에 있으므로 rows를 0으로 했고, 3번째이므로 MATCH(MONTH($B$4),LEFT(B5:Y5,LEN(B5:Y5)-1)*1,0)값을 사용했습니다.
 

다. 전월 찾기

전월은 기준 월에서 -2입니다.
따라서, 전월의 계획은 
=OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-2)이고,

 
수행은 계획 오른쪽에 있으므로 -2가 아니라 -1을 하면 됩니다.
=OFFSET(A7,0,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1)

 

라. 기준월까지의 계획과 수행의 개수

(1) 기준월까지의 범위 구하기
기준 월까지는 Offset함수에서 width를 이용하면 되는데,
너비는 2월의 위치에 1을 더해야 합니다.
 
따라서 수식은
=OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1)인데,
#분산!라고 표시되므로(height는 1인데 입력하지 않아도 되므로 생략했습니다) 

 
 
수식 입력줄(주의)의 수식을 복사해서 AA 셀에 붙이겠습니다.
그러면 계획, 수행, 계획, 수행으로 표시됩니다.
그러나, 이것은 동적 배열 수식을 지원하는 엑셀 버전에서만 되는 기능입니다.

 
(2) 기준월까지의 계획의 개수 구하기
조건에 맞는 개수를 구하는 것이므로 CountIf 함수를 이용하며,
수식은 기준월까지의 범위에서 '계획'의 개수를 구하는 것이므로
=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"계획")가 됩니다.

 
(3) 기준월까지의 수행의 개수 구하기
위 수식에서 '계획'을 '수행'으로 바꾸기만 하면 됩니다.
=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행")

 
수행의 개수 2가 잘 구해졌습니다.
 

마. 전월까지의 계획과 수행의 개수

(1) 전월까지의 범위 구하기
전월까지는 기준월의 위치에서 1을 뺀 길이만큼을 가져오면 되므로
=OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1)라고
마지막의 +1을 -1로 바꾸면 됩니다.

 
(2) 전월까지의 계획 또는 수행의 개수 구하기
마찬가지로 기준월까지의 수행 개수를 구하는 수식에서 +1만 
=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)+1),"수행")
 
-1로 수정하면 됩니다.
=COUNTIF(OFFSET(A7,0,1,,MATCH(MONTH($B$4),LEFT($B$5:$Y$5,LEN($B$5:$Y$5)-1)*1,0)-1),"수행")

 
1월의 수행 개수 1이 제대로 구해졌습니다.

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

0.01MB