이것은 엑셀이나 다른 ms office의 큰 장점인데 특히 엑셀의 매크로는 엑셀의 특성상 이 매크로 기능이 아주 큰 능력을 발휘한다.
특히나 어느 수준이상이 되는 회사들은 직원들의 컴퓨터에 이런 저런 프로그램들이 깔리는 것을 싫어하고 정품 사용문제로 매우 강력한 제한이 걸려져있다. 그래서, 자신의 컴퓨터에 쉐어웨어라하더라도 쉽게 깔기 힘든 상황이고 업무를 좀 자동화한다고 파이선이나 기타 프로그램을 깔기도 어려운 것이다.
이런 상황에서 visual basic급 프로그래밍이 가능한 excel VBA가 엑셀 매크로에서 가능하다.
그리고 엑셀 매크로의 기능을 이용하면 반복되는 엑셀을 작업을 자동화 할 수도 있고 거기에 VBA를 활용하면 더 강력한 자동화 기능을 사용할 수 있다. 때문에 엑셀에서 반복되는 작업을 많이 사용한다면 엑셀 매크로기능을 활용하는 것을 적극 추천한다.
그럼에도 불구하고 여기서 사용하는 수식은 충분히 더 복잡하고 중요한 자료에도 다 쓸 수 있는 수식임으로 다시 설명하겠다.
수입의 경우 수식은 "=SUMPRODUCT((장부!$A$2:$A$276=2019)*(장부!$F$2:$F$276)*(장부!$H$2:$H$276="수입"))"이고 여기서 회계년도 부분이 장부에서 귀속연도가 2019년 인 것을 논리 곱으로 금액과 곱하고 수입인 것과 논리 곱을 했다. 세부 항목부분도 마찬가지 방법으로 논리 곱을 하여 더한 것이다.
여기서 대차대조표의 항목은 항목계의 구성에서 정한 것으로 내역을 만드는 것이다. 그래서 처음에 이런 자료의 구성이 중요하다고 했었다.
이와 같은 것은 피벗 테이블을 통해서도 비슷하게 구현할 수 있으나 피벗 테이블로는 원하는 모양을 구현하기 위해서는 수정작업을 추가로 해야하고 더 유연한 수식을 구사하기가 힘들다. 그러나, 위의 sumproduct수식을 이용하면 좀더 유연하고 복잡한 결과들을 원하는 모양으로 간단히 구현 할 수 있다.
피벗 테이블이 유연하게 이렇게 저렇게 모양과 스타일을 바꿀 수 있고 편리하지만 어떤 특정한 형태로 만들고자하면 좀 많이 불편할 수 있으나 직접 수식을 입력하여 처리하면 원하는 서식에 맞게 간단히 만들 수 있으며 결과를 재설정하지않아도 표가 갱신되는 장점이 있다.
위의 그림에서 빨간색으로 표시된 부분이 조건서식으로 색상을 빨강색으로 표시되게 만든 것입니다. 앞에 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)
현재 기업들에서 사용하는 Oracle이나 Informix, Sybase등의 mDBMS의 경우에도 이런 구조를 잘못설정하면 데이터가 아주 느리거나 제대로된 결과가 나오지않을 수 있습니다.
엑셀에서도 어떤 처리를 해서 연산을 할 필요가 있기때문에 이런 분류를 해야만 합니다.
특별히 목록을 이용해서 함수 계산을 할 때 글자가 조금 미세하게 틀리는 문제로 연산결과가 안맞고
수정하는데 애를 먹을 수 있기 때문에 이런 문제로 종속과목계에는 미리 정한 목록만을 정확히 들어가도록 유효성 검사를 설정하고 목록내에서만 들어가게 하며 나머지 세부사항은 비고란에 자세히 입력할 수 있도록 해야합니다.
첫 항의 기준은 기준연도로 교회사 회사에서 회계년도가 1월이 아닌 11월 10월 3월등으로 다른 경우에 아주 유용하고 회사의 복리후생비 같이 예를 들어 분할로 지원금을 나누어 주는 경우에는 조금더 탄력적으로 기준 월까지 추가하여 만들 수도 있습니다. 이렇게 기준 월을 설정하면 연산할때 자연 연도와 상관없이 기준연도로 연산이 편하게되는데 다음 포스팅에서 연산 수식으로 확인하겠습니다.
여기서는 간단하게 항목과 계 내역목록을 만들고 유효성검사를 설정했습니다.
1. 유효성 검사 목록 설정방법
항목계의 내용이 들어갈 셀에 마우스 커서를 위치시키고 아래 그림의 숫자의 순서로 클릭을 한다.
데이터=> 데이터 유효성 검사 => 설정 => 목록
위의 원본항목을 클릭하여 유효성 검사를 위한 목록 영역을 설정합니다.
원본항목을 클릭하였을때 아래그림과 같이 데이터 유효성의 목록 설정 창이 나타나고 거기에 직접 입력하든지 마우스로 해당 목록에 마우스 왼쪽 버튼을 클릭하고 끌어서 아래 1번 영역같이 표시하면 된다. 그리고 확인을 눌러 작업을 완료한다.
항목계는 위의 요효성 검사 설정방법으로 설정하고 아래 열로 복사하여 합니다.
이렇게 복사하여 만들면 아래 그림처럼 목록에서 값을 입력하게 됩니다.
잔고항목의 첫 번행은 "=F2"수식을 그냥 썼는데 원래는 =IF(H2="수입",F2,IF(H2="지출",-F3))라고 입력했어야 맞습니다.
H2열의 값이 수입이면 F2의 값을 그냥 잔고로 입력하고 지출이면 마이너스를 해야하고 원래 일반적인 장부에서는 이월이 있어야하는데 이월이 없었기 때문에 그냥 이렇게 적었습니다.
두번째 행부터는 잔고행에 "=IF(H3="수입",G2+F3,IF(H3="지출",G2-F3))"과 같이 입력한다.
