위의 그림에서 빨간색으로 표시된 부분이 조건서식으로 색상을 빨강색으로 표시되게 만든 것입니다. 앞에 sumproduct함수와 if 함수로 N열에서 납입금액을 계산한 것으로 조건서식에서 수식을 입력하여 색상을 표시한 것입니다. 납입개월수와 잔여금액은 조건서식으로 표시된 결과가 맞는지 확인하는데 도움이 되도록 계산을 하였습니다.
조건서식 만들기
위 오른쪽 그림은 홈에서 조건부 서식을 클릭했을 때 메뉴이다. 위와 같이 수식을 삽입하여 만드려면 새규칙을 입력해야 한다. 오른쪽 그림의 규칙 설명편집에서 "=($O3-COLUMN(B$3)+2>0)"을 넣는다.
COLUMN(B$3)은 열의 위치를 숫자로 표시하는 함수이다. B3의 값은 3이다.
참고로 COLUMNS(범위)는 배열의 셀수를 세는 함수이다.
조건서식이 시작하는 B3셀에서 서식 설정한 후 B3셀 서식을 복사해서 B3셀부터 시작해서 M13셀까지 셀을 붙여넣기한다.
B3셀에 적용되는 수식의 의미는 "O3의 납입개월수에서 현재 컬럼수의 값을 빼고 2를 더한 값이 "0"보다 크면 빨강색으로 표시한다."이다. 빨강색 서식을 지정하려면 서식을 눌러 아래와 같은 창이 나오면 채우기에서 원하는 색을 선택한 후 확인을 누른다.
이 수식은 if함수와 sumproduct 함수를 섞은 것으로 sumproduct 결과가 "0"이면 빈칸으로 두고 아니면 sumproduct의 결과 값을 입력하는 것입니다.
if 함수는 "=if (조건, 참일때 값, 거짓일때 값)"과 같이 사용합니다.
특별히 계산할 때 셀값에 N/A등의 오류 표시가 나지 않도록 할때 if( iserror(수식), "",수식)과 같이 입력하면 오류가 표시되지않도록 할 수 있습니다.
sumproduct 함수 "배열의 연산 합계를 구합니다."
여기서 논리 연산을 이해해야 합니다.
장부!$A$2:$A$181=2019는 장부 시트의 A2에서 A181까지의 셀에서 "셀의 값이 '2019'이다" 가 참이 되면 '1', 아니면 '0'이 된다. 0이 되는 것은 뒤에 값이 무엇이 되든 0임으로 '0'이고 '1'되면 '장부!$F$2:$F$181'를 곱하는데, F2에서 F181까지의 셀값을 곱하게 된다. '장부!$E$2:$E$181=$A3'는 E2에서 E181의 셀 중에 A3와 같은 값을 갖으면 1 아니면 0입니다. A열의 값은 회원이 이름입니다. 장부에서 회원의 이름을 찾는 것입니다. 장부!$I$2:$I$181="회비에서도 S열은 계에서 내역이 '회비'인 경우에만 1이는 것입니다.
풀어서 다시 생각해보면 (장부!A2=2019)*(장부!F2)*(장부!E2=A3)*(장부!I2="회비")+(장부!A3=2019)*(장부!F3)*(장부!E3=A3)*(장부!I3="회비")+ ..... 와 같이 됩니다.
위 그림에서 적용해 해보면
(장부!A2=2019)*(장부!F2)*(장부!E2=A3)*(장부!I2="회비")
=( 기준년이 2019로 1)*(장부의 금액 30,000)*(홍길동이 아닌 곽재우임으로 0)*(회비임으로 1)
