[엑셀] 구간값을 참조하여 결과값을 불러오는 수식 작성 방법.

안녕하세요.

행아아빠입니다.

​

오늘 설명드릴 #엑셀 내용은

"구간값을 참조하여 결과값 불러오기"입니다.

​ 

이 내용은 사실 앞서 설명드린적이 있습니다만,

저 역시도 그랬었지만 함수를 응용한다는 것이 처음부터 쉽지만은 않은것 같습니다.

​

구간값을 참조하여 결과값을 불러오는 기본 내용은 아래 작성된 글을 링크해 드리니 참고하실분은 읽어보시면 좋겠습니다.

Index함수와 Match함수의 활용 - 숫자의 구간에 따른 결정값을 불러오고자 할때 수식 작성 방법.

https://hang-a-fafa.tistory.com/27

그럼 오늘의 질문과 함께 함수 수식 작성에 대해 알아보겠습니다.

---

 

▼ 오늘의 질문은 아래와 같습니다.

 

 각 지점에서 지점으로 견인거리를 몇천개정도 셀에 입력해놓을건데

이 모든 거리 값에

0~10km까지는 10만원

11~20km까지는 15만원

20km초과 시 5천원

​이런식으로 함수를 적용 시키고 싶습니다..

적용시키는 값은 제가 수정할 수 있게 부탁드립니다.

 

▲ 질문자 분께서는 질문 제목을 IF함수에 대한 문의라고 작성해 주셨습니다.

    물론 해당 질문에 대한 함수와 수식을 IF함수를 사용해서 작성할수도 있겠지만,

    질문내용을 기준으로 추측했을때 거리라는 구간의 설정이 상황에 따라 변경 가능해야 하는 것 같습니다.

​

    또한 질문에서는 20KM초과시에는 5,000원이라고 해 주셨으나

    이것은 20KM를 초과하는 부분에 대해서는 이동거리 1KM당 추가요금 5,000원이 붙는다는 내용인 것 같습니다.

​

    즉, 이동거리가 21KM라면 20KM구간까지의 요금에 해당하는 요금 150,000원과

    20KM를 초과하는 1KM에 대해 추가요금 5,000원이 합산되어 총 요금이 155,000원이 나오게 되는 것을 말씀 하시는 것 같습니다.

​

    만약, IF함수를 사용해서 수식을 작성한다면,

    =IF(E11<=10,100000,IF(E11<=20,150000,IF(E11>20,((SUM(E11-20)*5000)+150000))))

    위와 같이 작성할 수 있겠습니다.

​

    하지만 이렇게 작성할 경우

    만약 고정적으로 추가되는 거리가 있다면

    추가되는 구간만큼의 설정값을 함수에 반영시켜서 수정해 줘야 하는 번거로움이 발생하게 될 수 있습니다.

    수식이 길어지면 길어질수록.... 작성자도 헷갈리수가 있기 때문에... 나중에는 수식자체를 변경하는건 힘들어 질지도 몰라요 ㅠ

​

    그럼 어떻게 작성하면 좋을까요??

    위에 제가 링크를 걸어드린 내용을 읽어보셨다면, 쉽게 추측 하실 수 있으실 것 입니다.

    바로 INDEX함수와 MATCH함수를 사용하는 것 입니다.

▲ 우선은 "참조"할 시트에 위 그림과 같이 설정할 구간들과 그 구간들에 대한 각각의 결정값을 입력해 줍니다.

    (보통 VLOOKUP함수를 사용할때 이러한 표를 참조해서 값을 불러오잖아요~~?? 그렇게 생각하시면 쉬울 것 같습니다.)

 

▲ 그리고, "참조" 시트와 별개의 시트에

    이동거리를 입력할 셀에 이동거리를 입력하면,

    위에 "참조"시트에 작성한 구간과 구간값들을 참조해서 요금이 자동으로 나오게할 시트에 간략히 표현해 봤습니다.

​

    A2 셀에 이동거리를 입력하면, C2셀에 그 이동거리만큼의 요금이 자동으로 계산될 수 있는 함수만 입력하면 되겠지요~?

​

    이동거리가 21KM라면,

    20KM에 대한 요금 150,000원 + 1KM 추가요금 5,000원 = 155,000원이 나오면 되겠네요~!!

 

▲ =IF(A2<=20,INDEX(참조!C2:C4,MATCH(Sheet1!A2,참조!A2:A4)),

    INDEX(참조!C2:C4,MATCH(20,참조!A2:A4))+((A2-20)*INDEX(참조!C2:C4,MATCH(Sheet1!A2,참조!A2:A4))))

    위 함수를 작성해서 적용하면

    이동거리 21KM에 대한 요금 155,000원으로 잘 나오게 됩니다.

 

혹시 수식에 오류는 없는지 몇가지 임의로 이동거리를 입력해서 요금을 확인해 보자구요!!

▲ Q1. 이동거리 3KM일때 가격?

 

▲ Q2. 이동거리 15KM일때 가격?

▲ Q3. 이동거리 25KM일때 가격?

▲ Q4. 이동거리 100KM일때 가격?

 

거리가 늘어나거나 줄어들어도 오류없이 참조할 영역을 잘 구분해서 요금값이 자동으로 잘 입력됩니다~!!

---

 

이렇게 구간을 설정하고 그 구간에 따라 결정값이 정해져 있을때, INDEX 함수와 MATCH 함수를 사용하면 된다는 것!!

실제 업무에서 잊지말고 사용하면 좋을 것 같습니다.

​

그럼 오늘 내용도 많은 분들께 도움이 되시길 바라며,

기분 좋은 하루 보내시길 바라겠습니다~^^!