엑셀 매입 상품 중에 가장 낮은 가격의 판매처를 찾는 수식 및 그 과정을 설명합니다
앞선 포스팅에서 업무 내용을 일부 소개했습니다. 같은 이름의 상품이 여러 거래처에서 입고될 때 업체를 구분하기 위해 이니셜을 상품 앞에 넣고 다시 분리하는 수식을 설명했습니다. 동네 조그만 가게지만 입고되는 상품의 수는 5천까지가 넘습니다. 이것도 비슷한 종류인데 가격이 같으면 하나의 이름으로 등록을 한 것이 이 정도인데요, 이를테면 꽃슬라임1000원 상품과 나비슬라임1000원은 같은 슬라임1000원 상품으로 묶어버립니다. 입고되는 상품을 하나하나 구분하면 최소 5배 이상은 될 것 같습니다.
그러다 보니 거래처마다 같은 상품이 입고되기도 하는데 가격은 다릅니다. 어떤 종류의 상품은 어떤 업체가 저렴하게 형성되는지는 알 수 있고, 일부 상품에 대해서는 정확하게 알고 있을 수도 있는데, 실제로 단가를 비교해 보면 엉뚱한 그동안 비싸다고 생각했던 업체가 특정 상품은 더 낮은 단가를 형성하고 있는 경우도 있습니다. 보통은 주문할 때는 하나하나 상품을 계산하기보다는 전체금액에 대해 기대하는 원가 이하라면 그냥 넘어갑니다. 하지만, 특정 상품의 가격이 어떤 업체가 낮은지 알 수 있다면 해당 품목만 특정 업체로 주문하면서 입고 단가를 낮출 수 있는데요, 실제로 이 방법은 그렇게 많은 시간을 필요하지 않기에 한 번 해보시기를 권합니다.
상품명은 거래처 이니셜+상품명으로 되어 있던 것은 영문자를 분리했습니다. 이 내용은 아래 이전 포스팅을 참고하세요.
단가를 불러오는 방법은 엑셀에서 가장 흔하게 사용하는 VLOOKUP 함수를 사용했습니다. 다만 판매처를 포함한 상품명으로 단가가 구성되어 있기에 업체명 영어 이니셜+상품명으로 단가를 불러옵니다. 그 수식은 =IFERROR(VLOOKUP(R4&$Q$5,단가!$B$4:$C$34,2,0),"") 이렇습니다.
그리고 해당 상품에서 가장 싼 업체를 구하는 수식은 =INDEX($R$4:$V$4,MATCH(MIN(R5:V5),R5:V5,0)) 입니다.
다음은 해당 업체에서 가장 싼 상품을 나열하는 수식을 알아보겠습니다.
=IFERROR(INDEX($Q$1:$Q$14,SMALL(IF($X$5:$X$14=Z$4,ROW($X$5:$X$14),""),ROW(A1))),"")
정상 파일로 수정했습니다.
이 수식은 수식이 있는 자리에 커서를 놓고 Ctrl + Shift + Enter 키를 이용해야 합니다.