엑셀 가격 DB에서 최신값을 선택하여 최신 판가표 작성하기

[ 과제 ]

엑셀을 이용해 월벌 기준 가격 DB를 운영 중인데, '최신 가격표'를 만들어야 한다.

※ 엑셀 가격 DB 사례

	A	B	C	D
1	년월	모델명	지역/유통	기준가격
2	2017년 3월	고기라면	대형마트	1000
3	2017년 4월	해물라면	대형마트	900
4	2017년 5월	고기라면	대형마트	950
5	2017년 6월	해물라면	대형마트	900
6	2017년 7월	고기라면	대형마트	900
7	2017년 8월	해물라면	대형마트	950

위 사례 수준이면, 고기라면은 900원(`17.7월), 해물라면은 950원(`17.8월)이라고 금방 알 수 있습니다. 그러나, 대형마트에 납품되는 품목수를 생각해보면, 수작업은 시간 소모하고 오류를 만드는 일임을 금방 깨닫게 됩니다.

[ 해결 방법 1 ]

1. 일단 A1:D7을 선택해서 피벗 테이블을 만듭니다.

2. 아래와 같이 '년월'을 내림 차순으로 정렬합니다.

  - 다른 시트에서 모델명으로 검색해 가격을 가져갈 수 있도록 '보고서 레이아웃'을 '테이블 형식으로 표시'하고, '모든 항목 레이블 반복'을 활성화 하였습니다.

  - 모든 필드를 '행' 영역에 두면 됩니다. 혹시 기준 가격이 여러개 존재하는 경우는 '기준가격'을 '∑ 값'영역으로 옮기고, 값 요약 기준을 상황에 따라 평균, 최대값, 최소값으로 설정하면 대표값이 나옵니다.

년월	지역/유통	모델명	기준가격
2017년 8월	대형마트	해물라면	950
2017년 7월	대형마트	고기라면	900
2017년 6월	대형마트	해물라면	900
2017년 5월	대형마트	고기라면	950
2017년 4월	대형마트	해물라면	900
2017년 3월	대형마트	고기라면	1000

3. 이제, 다른 시트에서 vlookup이나 index 함수를 사용해서 모델명을 찾아 기준 가격을 가져가면, 항상 맨 위에 있는 최신 값을 가져가게 됩니다.

[ 해결 방법 2 ]

만약, 가격이 내려가기만 하고 올라가는 경우가 전혀 없다면, '해결방법1'의 피벗 테이블에서 '년월' 필드를 제외하고, '기준가격'을 '∑ 값'영역으로 옮긴 후 '값 요약 기준'을 '최소값'으로 설정하면 간단히 해결됩니다. 다만, 위 사례에서는 해물라면 가격이 50원 올랐으므로, 적용할 수가 없네요. 

[ 해결방법 3 ]

해결방법 1은 엑셀 내에서 최신 기준 가격을 가져가기에는 좋으나, 당월 가격표를 만들기에는 부족함이 있습니다. 가격이 매달 갱신되는 것이 아니어서, `17년8월만 필터링 하면, 가격이 표시되지 않는 모델이 생기게 됩니다. 결국 당월 가격표를 만들려면, 원 DB에서 최신 가격인지 아닌지를 표시하는 필드를 만들어 주어야 합니다.

	A	B	C	D	E	F
1	년월	모델명	지역/유통	기준가격	모델명_지역/유통	다음값 존재 여부
2	2017년 3월	고기라면	대형마트	1000	고기라면_대형마트	2
3	2017년 4월	해물라면	대형마트	900	해물라면_대형마트	2
4	2017년 5월	고기라면	대형마트	950	고기라면_대형마트	1
5	2017년 6월	해물라면	대형마트	900	해물라면_대형마트	1
6	2017년 7월	고기라면	대형마트	900	고기라면_대형마트	0
7	2017년 8월	해물라면	대형마트	950	해물라면_대형마트	0
8	End of DB				_

년월을 기준으로 오름차순 정렬합니다. 마지막 행은 다음값 존재 여부 계산식을 위해 추가한 것입니다.

'모델명_지역/유통'은 다음의 함수로 모델 식별자를 만든 것입니다. 함수는 엑셀의 '표' 기능을 사용한 것입니다.

=[@ 모델명]&"_"&[@[ 지역/유통]]

'다음값 존재 여부'는 다음의 함수로 현재 위치보다 아래 행에 동일 모델 식별자가 있는지 확인합니다. F2 셀에 입력되는 수식 기준이며, 이렇게 입력하면 엑셀의 '표' 기능 중 수식 자동 체우기 기능이 동작하여, 아래 행들도 바르게 들어가게 됩니다.

=IF([@[모델명_지역/유통]]<>"_",COUNTIF(E3:$E$8,[@[모델명_지역/유통]]),"")

앞의 If 문은 '모델명_지역/유통'에 무의미한 값이 있을 때 '다음값 존재 여부'에 ""값을 기록하기 위한 것입니다.

COUNTIF(E3:$E$8,[@[모델명_지역/유통]]) 부분이 현재 위치인 E2보다 아래인 E3에서 $E$8까지에서 E2의 값과 같은 값이 있는지 그 횟수를 합산하게 됩니다.

이제 다시 피벗 테이블을 만들어서 F열, 또는 '다음값 존재 여부' 필드의 값이 '0'인 것만 표시하면 깔끔한 최신 가격표를 만들 수 있습니다. 

다음값 존재 여부	0
모델명	지역/유통	기준가격	년월
고기라면	대형마트	900	2017년 7월
해물라면	대형마트	950	2017년 8월

※ 주의사항 : 위 방법은 '년월' 기준으로 오름차순으로 정렬되어야 유효합니다. 

- 끝 -