[파워쿼리Tip] 엑셀 다단계 비용 배부와 단수 조정

이번 포스팅은 엑셀로 다단계 배부를 처리하고 단수 보정까지 한 번에 처리하는 과정을 다룹니다.   

흔히 ERP 시스템에서 가지고 있는 부서간 다단계 배부 기능을 엑셀로 파워쿼리를 활용하여 구성해 보았습니다. 엑셀로 금액을 배부를 하는 경우는 매우 흔한 일이죠. 엑셀로는 아주 간단한 함수나 sumifs 등 배열 수식을 통해서 쉽게 배부가 가능하죠. 다단계 배부도 시트를 늘려가며 작성하면 그리 어려운 일은 아닙니다. 하지만  매번 변동되는 배부기준값을 적용해야 할 경우 작업이 복잡해지고 배부 결과의 추적 관리등 관리 효율이 떨어지는 문제를 가지고 있습니다. 

이 부분을 파워쿼리를 이용해서 보다 정규화 하여 관리효율을 향상하는 관점으로 포스팅하고자 합니다. 

1. 데이터 구성

포스팅의 데이터는 각 부서(Cost center)로 발생된 비용을 가지고 진행합니다. 비용 실적을 월 단위로 합산하여 지정된 경로에 따라 타 부서(Cost center)로 단계별 배부를 수행 합니다.

먼저 준비된 비용 데이터 구조를 살펴보죠. 

전표 단위의 비용실적

코스트센터(부서)와 회계계정별 비용금액으로 구성된 전표번호단위의 데이터입니다.  이 데이터를 월단위로 요약하여 배부 원본으로 구성 후 단계별 배부를 수행 합니다. 

배부를 하려면 3가지의 정의가 필요합니다. 

 ① 배부할 대상  ②배부받을 대상  ③ 배부 기준값

진행할 예제 데이터로 설명을 하자면

① 배부할 대상 :  '어느 코스트센터의 어떤 계정의 비용을 배부할 것인가?'를 정의하는 것이죠. 
② 배부받을 대상 : '어느 코스트센터가 배부받을 것인가?'  
③ 배부 기준값 : '어떤 기준으로 배부를 할 것인가?' 

배부 경로와 기준의 정의

위 그림은 이번 포스팅에서 배부를 위해 정의된 내용입니다. 

공통~생산~공정 순으로 단계배부를 할 것이고 각 코스트센터의 계정그룹별 배부기준과 경로를 요약한 내용입니다. 

이제 데이터 준비가 되었으니 본 작업을 수행하죠. 파워쿼리를 활용할 것이므로 필요한 데이터는 모두 표(Table)로 만들어서 쿼리로 구성합니다. 

이 부분을 포함하여 파워쿼리 관련 기본 기능에 대한 내용은 블로그의 다른 포스팅을 참고해 주세요.  

2020.06.23 - [엑셀/파워쿼리기능소개] - [파워쿼리기능소개] 1편. 전체 포스팅 개요

[파워쿼리기능소개] 1편. 전체 포스팅 개요

 

2. 쿼리 구성 

결과를 보고 구성 내역을 살펴보죠. 아래 작성된 쿼리는 작업단계를 보다 쉽게 확인하기 위해서 조금 많이 만들어져 있습니다. 쿼리보다 적게 만들면서 작업도 가능하지만 저도 처음 해보는 것이다 보니 많아졌네요. 

사용된 전체 쿼리 목록

자세한 쿼리 내용은 첨부된 파일을 직접 열어서 확인하는 것이 좋을 것 같네요. 모든 과정의 확인이 가능하도록 파워쿼리 편집기의 리본메뉴에 있는 도구만 사용하여 작성되어 있습니다. 고급편집기에 직접 입력된 것은 없으니 파워쿼리 공부를 어느 정도 마친 분들은 쉽게 파악 가능할 것이라 생각되네요. 

세부 과정보다는 이번 포스팅의 핵심인 단계배부의 구조와 단수 조정 논리를 중점으로 설명하겠습니다. 

1) 단계배부
: 단계배부를 위한 전처리를 보다 정규화하면 여러 쿼리를 한 개의 함수로 합쳐서 프로그램 같이 적용도 가능할 것이라 판단됩니다. 하지만 파워쿼리의 기본 도구를 최대한 활용하는 취지에는 맞지 않아 바로 연구를 포기하고 단계별로 쿼리를 다 만드는 것으로 방향을 잡았습니다. 

각 단계에서 적용된 기본 구조는 비용 실적 Table에 배부기준값 Table을 조인하여 데이터를 늘려준 후 여기에 다시 배부대상(From) 단위로 배부기준값을 합산하여 곱해줄 비율을 만듭니다. 그리고 그 비율을 곱해서 배부를 수행 하였습니다. 

'C1000' costcenter를 예시로 살펴보면 

부서 비용을 클래스별로 요약

배부기준 정보를 조인하여 배부타겟(To)데이터를 확장

최초 배부대상(From)의 실적은 2건으로 요약되어 있었으나 배부기준정의 데이터와 조인하여 배부받을 costcenter와 배부기준값을 포함한 6줄로 확장한 상태입니다. 

여기에 costcenter(From)와 Class2(비용그룹), 배부기준 단위로 배부기준값을 합산한 Table을 '참조'로 만들어서 조인을 통해 쿼리를 만들어 줍니다. 

배부수행 결과

 위 그림의 [부분합] 필드는 앞서 언급한 배부기준값의 합산값입니다. 즉 배부비율을 만들 때 적용될 분모에 해당합니다. 

배부결과 필드의 계산식

이 과정을 1~2단계 쿼리에 동일하게 작성하여 처리했습니다. 먼저 1단계를 만들고 나서 2단계 쿼리는 복사를 통해서 만들어서 수정을 하는 방식으로 작업했습니다. 예전 포스팅에서 엑셀로 배부를 하는 팁을 다룬 적이 있는데요. 그때 사용한 방식을 파워쿼리로 만들었다고 보시면 됩니다.

2018.07.11 - [엑셀/빠른 엑셀] - 엑셀로 배부하기

엑셀로 배부하기

2) 단수조정 

단수조정은 배부 대상 단위로 배부결괏값을 합산하고 이것을 원본 금액값과의 차이를 계산하여 배부 결과 Table에 덧붙여 주는 방식으로 처리했습니다. 

위 그림은 costcenter(From), class2, 배부기준 이 3개의 필드를 그룹화하여 합산한 [단수조정] 필드와 해당 원본 금액인 [Amount] 필드의 차이를 [배부단수]로 계산해 놓을 상태입니다. 이 결과를 앞서 설명된 단계배부 결과에 '쿼리 추가(Union all)' 기능을 적용하여 합산하게 되면 단수로 계산된 '-1'이 배부 결과에 합산되어 단수 차이 없이 배부가 완성됩니다. 

단수 차이분을 쿼리추가로 데이터를 추가한 결과

처리된 과정을 다시 요약하자면 "단수차이 계산" > "단수차이를 배부결과에 추가" > "부족한 정보의 채우기"의 3가지로 설명할 수 있는데요. 이 부분의 키 포인트는 마지막 부족한 정보의 채우기입니다. 배부로 인한 단수는 비율을 곱한 후 반올림을 적용하므로 발생됩니다. 즉 배부 전과 비교해서 계산된 차이는 배부대상(From) 단위로만 존재합니다.

이 경우 어느 costcenter(To)로 단수를 정리할지 알 수 없는 것이죠. 저는 이것을 부족한 정보라고 표현했습니다. 여기서는 가장 작은 배부결과를 갖는 costcenter(To)에 주는 방식을 택했습니다.  

3. 결론   

배부 결과는 아래와 같이 정리됩니다. 

배부결과 요약 Table과 pivot

원본 실적 데이터 합산 Pivot

결과를 보시면 공통부서와 생산부서의 비용이 모두 A로 시작되는 공정 부서로 단계 배부된 것을 확인할 수 있습니다. 

첨부된 파일에는 모든 쿼리를 로드하지는 않았습니다. 중간 과정을 확인하려면 각 단계의 쿼리를 엑셀시트에 로드하여 확인하시면 되겠네요. 

파워쿼리로 단계별 비용 배부 with 단수조정.xlsx

0.07MB

포스팅을 마치며... 

파일 작업 시간이 제법 걸렸네요. 집중해서 만들지 못하다 보니 조금 늘어졌는데 보다 많은 데이터를 넣고 속도 문제도 검토하면서 파일을 정규화해놓으면 제 실제 업무에서도 활용이 가능할 것 같네요. 

이번 케이스는 단순히 데이터를 취합하고 요약하는 수준을 벗어나 배부라는 본격적인 가공을 파워쿼리로 다루었는데 아무래도 파워쿼리의 본래 용도와는 조금 맞지 않는 듯 한 느낌을 받았습니다. 

완전 자동보다는 반자동이 엑셀에는 더 어울리는 것 같네요. 다만 엑셀로 만든 데이터가 아니라 DB에 저장된 데이터를 직접 SQL로 받아 올 때는 보다 효율적인 쿼리가 가능하므로 이때는 단순 SQL로는 어려운 기능을 수행할 수 있으니 시너지가 있을 것 같습니다.