월별 판가표를 사용한 연간 할인율 계산

주의 : 아래 내용은 개념 설명을 위한 것으로,

        함수의 문법적 오류나 오타를 포함할 수 있습니다.

 

[ 문제 ]

 

1. 매월 변경되는 판가표를 기준으로 월별/분기별/연간 기준판가 대비 실적판가의 분석을 해야함

 

2. 제품의 실적은 거래건별로 DB나 엑셀의 한 행으로 관리됨

예)

년도	월	거래처	제품코드	수량	매출	...	실적판가	...
2015	1	ABC	SS111222	100	1000		10
2015	2	DEF	DD112234	50	2000		40

 

3. 판가표는 월 1회 갱신하여 기준 판가를 고지하고 있음

예) '1월_판가표'

제품코드	기준 판가	...
SS111222	8
DD112234	30

 

'2월_판가표'

제품코드	기준 판가	...
SS111222	7
DD112234	29

 

4. 각 행에 다음의 개념으로 함수를 구현하여 기준판가와 실적판가를 비교하길 원함

   기준판가 = 가져오기함수( 제품코드, 해당월판가표, 지역)

 

※ 아래 내용은 엑셀에서 지원하는 '표(Table)' 기능을 사용하면 편리하게 적용할 수 있습니다.

 

[ 나쁜 풀이 ]

 

1. 기초함수를 사용한다면 12개의 if문을 중첩해서 사용하면 가능합니다.

 

예1) =if(월=1,vlookup([@제품코드],1월_판가표,2,false),

                if(월=2,vlookup([@제품코드],2월_판가표,2,false),

                if(월=3,vlookup([@제품코드],3월_판가표,2,false),

                if(월=4,vlookup([@제품코드],4월_판가표,2,false),

                if(월=5,vlookup([@제품코드],5월_판가표,2,false),

                if(월=6,vlookup([@제품코드],6월_판가표,2,false),

                if(월=7,vlookup([@제품코드],7월_판가표,2,false),

                if(월=8,vlookup([@제품코드],8월_판가표,2,false),

                if(월=9,vlookup([@제품코드],9월_판가표,2,false),

                if(월=10,vlookup([@제품코드],10월_판가표,2,false),

                if(월=11,vlookup([@제품코드],11월_판가표,2,false),

                if(월=12,vlookup([@제품코드],12월_판가표,2,false),"에러"))))))))))))

 

예2) =vlookup(제품코드,if(월=1,1월_판가표,

                                         if([@월]=2,2월_판가표,

                                         if([@월]=3,3월_판가표,

                                         if([@월]=4,4월_판가표,

                                         if([@월]=5,5월_판가표,

                                         if([@월]=6,6월_판가표,

                                         if([@월]=7,7월_판가표,

                                         if([@월]=8,8월_판가표,

                                         if([@월]=9,9월_판가표,

                                         if([@월]=10,10월_판가표,

                                         if([@월]=11,11월_판가표,

                                         if([@월]=12,12월_판가표,"에러")))))))))))) ,2,false)

 

간단해 보입니다만, '0월_판가표'가 실은 'sheet6!A$3$:B$1000$'같은 모양이 되고, 이게 다른 파일에라도 있으면 'c:\document\user\AAA\'sheet6!A$3$:B$1000$'같은 모양이 되어서 복잡하고, 길고, 가독성이 떨어지고, 어느 괄호가 틀렸는지 확인하기도 힘들게 됩니다.

 

[ 좋은 풀이 ]

 

1. 월별 판가표를 만들어 둡니다.

2. 월과 판가표를 매칭하는 표를 별도로 만듭니다.

 

[월별판가표] 

월	판가표 이름
1	1월_판가표
2	2월_판가표
...	...

 

3. DB 또는 엑셀 데이타에 판가표 이름으로 열을 추가합니다.

 

년도	월	거래처	제품코드	판가표 이름	수량	매출	...	실적판가	...
2015	1	ABC	SS111222		100	1000		10
2015	2	DEF	DD112234		50	2000		40

 

이 '판가표 이름'열에는 다음과 같은 함수가 들어가면 됩니다.

 

=vlookup([@월],월별판가표,2,false)

 

3. 그러면 아래와 같이 자동으로 판가표 이름을 가져옵니다. 그리고 그 옆에 기준판가열을 추가합니다.

 

년도	월	거래처	제품코드	판가표 이름	기준 판가	수량	매출	...	실적판가	...
2015	1	ABC	SS111222	1월_판가표		100	1000		10
2015	2	DEF	DD112234	2월_판가표		50	2000		40
...	...	...	...	...		...	...	...	...	...

 

이 '기준 판가'열에는 다음과 같은 함수가 들어가면 될 듯 합니다.

 

=vlookup([@제품코드],[@[판가표 이름]],2,false)

 

즉, 판가를 찾을 판가표를 상수가 아닌 변수로 입력하자는 생각입니다. 그러나, 생각과 다르게 이 기능은 동작하지 않습니다. 함수 입력시 테이블명을 직업 입력할 때는 이를 테이블로 인식하지만, 이렇게 변수로 지정하면 에러납니다.

 

이 때, 사용할 수 있는 함수가 indirect()입니다. 이를 이용해 다음과 같이 수정하면 짠 하고 동작합니다.

 

=vlookup([@제품코드],INDIRECT([@[판가표 이름]]),2,false) 

 

년도	월	거래처	제품코드	판가표 이름	기준 판가	수량	매출	...	실적판가	...
2015	1	ABC	SS111222	1월_판가표	8	100	1000		10
2015	2	DEF	DD112234	2월_판가표	29	50	2000		40
...	...	...	...	...		...	...	...	...	...

 

이제 실적 판가와 기준판가를 구했으므로, 마음껏 분석할 수 있습니다.

 

 

[ 정리 ]

 

여러 표 중 조건에 맞는 한 표에서 값을 참조해서 가져오는 경우, 표 이름열을 만든 후 이를 INDIRECT()함수로 감싸면 구현 가능합니다.