이전 편에서 엑셀매크로를 활용하여 회계업무를 자동화하기 위해 업무를 어떻게 처리하고 있는지 현황(AS-IS)를 분석했습니다. 이번 편에서는 TO-BE 분석 및 실제 개선 방법에 대해서 설명 드리도록 하겠습니다.
회계업무 자동화 사례공유: 엑셀 매크로 활용 방법 #2 - TO-BE 분석하기
이전 편에 이어 오늘은 TO-BE 분석을 통해 엑셀 매크로로 회계업무를 어떻게 개선했는지 알아보도록 하겠습니다.
엑셀 매크로를 활용한 회계업무 자동화 TO-BE 분석하기
일단 가장 먼저 횡으로 관리하던 데이터를 종으로 변경하고 계약청구 시스템에서 다운받은 엑셀에서 당월 매출금액을 일일이 찾아 입력하던 것을 엑셀 함수를 사용해서 자동으로 가져오도록 하겠습니다.
이 작업이 가장 많은 시간이 들었는데요. 이유는 여러가지 있겠지만 일단 횡으로 관리하던 것을 종으로 변경하려면 전월과 당월 데이터의 관계를 잘 분석해야 합니다. 즉, 당월에 전월의 데이터를 참고해야 한다면 엑셀 함수를 사용해서 전월 데이터를 가져와야 합니다. 이 작업이 엑셀을 잘 모르시는 분들이 계속해서 데이터를 횡으로 관리하게 만드는 주요 원인입니다.
예를 들어 아래와 같은 엑셀 데이터가 있다고 가정해 보겠습니다.
여기서 4월에도 동일한 금액이 들어간다면 열을 추가해서 입력을 할 겁니다. 그럼 횡으로 데이터가 쌓일 수 밖에 없습니다. 많지 않은 데이터일 경우 이렇게 하는게 효율적이겠지만 만약 10년치의 데이터를 이렇게 한다면 어떻게 될까요? 국가가 3개가 아니라 300개라면 어떻게 될까요? 매달 국가의 순서가 달라진다면 어떻게 될까요? (데이터 관리의 기본적인 사항은 설명드리지 않겠습니다.)
위의 표를 아래와 같이 바꾸는 것이 횡에서 종으로 바꾸는 작업입니다.
엑셀 데이터 관리를 횡에서 종으로 바꾸려면 일단 모든 데이터가 유일한 키(Unique Key)를 가지고 있어야 합니다. 위의 표에서 2월 한국의 금액을 알려면 2와 한국을 합쳐야 찾을 수 있습니다. 즉 “2한국” 이 유일한 키가 됩니다.
엑셀에서는 2 & 한국 으로 표현할 수 있습니다.
그럼 위에서 3월 누계를 함수를 사용해서 자동으로 넣어주려면 어떻게 해야 할까요?
아래 그림을 보겠습니다. E열에 “=A2&B2” 를 추가해서 유일한 키를 만들어 원하는 데이터를 찾을 수 있도록 해 주었습니다.
그리고 XLOOKUP를 사용해서 데이터를 찾아 계산이 되도록 수식을 사용했습니다.
XLOOKUP은 O365에서 지원하는 함수로 현재 사용하시는 엑셀버젼이 낮을 경우에는 VLOOKUP를 사용하시면 됩니다.
이번 회계업무 개선에 실제로 작성한 수식은 아래와 같이 조금 복잡해 보이지만 위와 동일한 구조이기 때문에 천천히 보시면 이해하실 수 있으실 겁니다.
=IF(XLOOKUP((A69-1)&B69&C69&E69,$N:$N,$M:$M,0)=0, 0, XLOOKUP((A69-1)&B69&C69&E69,$N:$N,$M:$M,0)-M69)
앞에서 회계담당자가 횡으로 관리하던 엑셀 시트를 종으로 바꾼 결과는 아래와 같습니다.
보시는 것과 같이 A~M까지의 열(column)만을 사용하고, N에 유일한 키(Unique Key)를 만들어 원하는 데이터를 찾을 수 있도록 했습니다.
[시트명 : 선수수익-EAs 외 (변경후)]
그리고 A열에 월을 추가하여 발생하는 데이터를 월별로 관리할 수 있도록 했습니다.
이번 편에서는 회계 월말결산 업무의 AS-IS 분석을 통해 도출된 문제를 TO-BE 분석으로 개선한 방법과 수식 적용 과정을 살펴보았습니다. 다음 편에서는 실제 소스코드 예시를 들어 설명해 드리겠습니다.
