엑셀에서 재미있는 수식입니다. 원하는 조건으로 값을 찾아 순서대로 정렬하는 건데요, 아마 많은 분이 이 수식을 찾고 있는 것 같아요
엑셀에서 조건에 맞는 값을 순서대로 채워가는 수식입니다. 대응하는 값이 없을 때는 빈자리로 남겨두는 것이 아닌 조건에 맞는 다음 값이 밑에 있더라도 차례대로 값을 반환하는 수식입니다. 먼저 전체 그림을 보겠습니다.
내용은 가수들이 업체를 통해서 공연하고 받은 금액을 확인해서 가장 낮은 업체를 확인하는 겁니다. 이런 내용은 매장에서는 상당히 필요한 장부입니다. 계약된 매입업체만을 통해야 하는 프랜차이즈는 이런 비교는 필요 없겠지만, 자체적으로 여러 업체를 통해서 매입할 때는 같은 상품이 항상 비슷한 가격을 가지고 있는 것은 아닙니다. 그리고 항상 낮은 업체를 통해서만 매입할 수도 없습니다. 인터넷 또는 대형 도매업체라고 해서 항상 내가 원하는 품목을 비치하고 있는 것도 아니기에 때에 따라서는 기본 매입 단가 이상으로 구매해야 할 때도 있습니다. 그래서 자체적으로 매입하는 업체라면 같은 상품이 여러 업체에서 구매할 때 이 양식을 통해서 어느 업체가 가장 싼 가격인지를 한눈에 알 수 있죠. 상세하게 설명하겠습니다.
가수들 이름 중에 C성연, E성연이 보입니다. 성연이라는 가수가 C 업체를 통해서 공연을 했을 때와 E 업체를 통해서 공연을 했을 받은 금액이 다르기에 이렇게 업체별 이니셜로 구별합니다. 가게 상품도 매입처마다 이니셜을 붙이면 혹시 불량이 났을 때 어떤 업체인지 알 수 있습니다. 똑같은 제품이 여러 업체에서 구매한다고 해서 최저 단가로 구매할 수 있는 업체를 두고 일부러 다른 업체에 발주를 내지는 않습니다. 같은 상품이 여러 업체와 섞이는 경우는 있지만, 그 품목은 손가락을 꼽을 정도라서 머릿속으로 기억할 수 있습니다. 지금 가수 이름으로 여러 이니셜을 붙인 것은 예를 들기 위한 것입니다.
가수 이름이 있는 열은 공연 업체 이니셜을 빼버린 상태입니다. 나중에 보면 이 과정이 필요합니다. 앞 글자 한자리를 없애는 엑셀 수식은 =RIGHT(B4, LEN(B4)-1) 입니다. 사용함수는 =RIGHT() 인데 왼쪽 첫 번째 자리가 사라졌습니다. =RIGHT(), 이 수식은 오른쪽부터 추출해서 마지막 하나를 날려버리라는 명령입니다. 그러니 왼쪽 첫 번째 글자가 사라졌죠, 그리고 중복삭제를 통해서 고윳값만 남겼습니다.
금액을 가져오는 수식입니다. 물론 'A이현' 이 자체로 가져올 수도 있습니다만, 업체와 이름을 합쳐서 금액을 가져오는 VLOOKUP 함수를 보겠습니다.
=IFERROR(VLOOKUP($D$3&C4,단가!$C$2:$D$35,2,0),"")
수식에서 두 개의 셀값에 대응하는 값을 가져오는 수식은 VLOOKUP($D$3&C4,~~) D3의 업체와 C4의 가수 이름을 합한 값으로 금액을 찾습니다.
조건부 서식입니다. 가수들이 업체를 통해서 공연을 한 금액을 불러왔고, 그 금액에서 최저 금액을 찾는 조건부 서식입니다.
조건부 서식에서 맨 아래 수식을 사용하여~ 을 선택하고 이 수식을 넣습니다. =MIN(IF($D4:$H4>=0,$D4:$H4))=D4
0을 제외한 최젓값을 찾는 수식입니다. 조건부 서식은 지금처럼 하나의 열만 정확하게 수식을 넣고 빠져나오면서 범위를 지정합니다. =$D$4:$H$36 이 범위는 중복제거하기 전의 범위인데요, 문제 되지 않습니다. 어차피 값이 없으면 빈 빈칸으로 나오니까요.
이렇게 불러온 가격으로 가장 낮은 업체를 찾습니다. 물론 가장 낮은 업체는 배경색이 자동으로 올라오며 한눈에 알 수 있지만, 가장 낮은 업체 이름을 빼내는 것은 다음에 업체에 출연한 가수의 이름, 즉 가장 낮은 업체에서 들어오는 상품의 이름을 알기 위한 사전 작업입니다. 가장 낮은 업체를 뽑아내는 수식은 =INDEX($D$3:$H$3,MATCH(MIN(D4:H4),D4:H4,0))
이곳을 설명하기 위해서는 전체적인 화면이 필요합니다. 혹시 이미지가 안 보이면 첨부파일 열어놓고 조금 더 읽으시기를 바랍니다. 주의해서 읽으셔야 합니다. 이곳이 이 포스팅에서 가장 중요한 자동 정렬되는 곳입니다. 해당 업체에서 입고되는 상품 중에 가장 낮은 가격이 형성되는 품목만을 정렬하는 수식이 들어가는 곳인데요,
=IFERROR(INDEX($C$1:$C$36,SMALL(IF($I$4:$I$36=$K$3,ROW($I$4:$I$36),""),ROW(A1))),"")
수식입니다. 이 수식을 복사해서 자신의 양식에 붙여넣고 셀 값을 제대로 넣었는데 안 된다는 분이 많습니다. 이런 이유는 그동안 엑셀 수식에서 수식의 범위는 셀 값이 있는 곳을 넣었습니다. 하지만, 이 수식은 셀 값이 들어있는 곳이 범위가 아닙니다. 지금 가수이름 아래 첫 번째 나오는 이름이 이현입니다. 그런데 수식에서 범위는 INDEX($C$1:$C$36 입니다. 범위가 이현이 있는 C4가 아닌 C1입니다. 어떤 열에서 차트를 만들더라도 시작은 1부터 넣습니다. 범위 끝은 넓더라도 상관없습니다. 자료가 있는 것만 나타나니까요. ROW(A1))) 수식 마지막에 A1이 있는데 이것은 K3 위치에 A가 있어서 A1이 들어가는 것이 아닙니다. 정렬하는 첫 번째는 무조건 A1으로 시작합니다. 두 번째는 B1, 세 번째는 제목의 이름과 상관없이 C1이 들어갑니다. 그냥 원칙이라고 알고 있으면 되겠습니다. 그리고 빈자리에 공백으로 나타내는 함수는 =IFERROR( (각종 수식),"" ) 입니다. 오류는 수식이 잘못되면 나타나지만, 대응하는 값이 없을 때도 오류가 나타납니다. 아무리 살펴봐도 수식에는 이상이 없는데 오류가 나타날 때는 이 함수를 사용하면 됩니다. 자동 정렬하는 수식에서 가장 긴 시간이 걸리는 곳이 이곳인데, 수식만 잘 살펴보면 많은 시간을 절약할 수 있습니다.
이 수식은 배열 수식이라서 수식 복사해서 옮기고 정리가 끝나면 수식 옆에 커서를 놓고 Shift+Ctrl+Enter를 치면 수식 앞뒤로 {} 가 나타납니다. 그리고 아래로 드래그하면 됩니다.