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

안녕하세요.

행아아빠입니다.

​

이번 #엑셀 내용은

#INDEX 함수와 #MATCH 함수의 활용에 대한 내용입니다.

​

▼ 앞서 INDEX 와 MATCH 함수를 이용해서 가로, 세로의 표로 작성된 데이터를 결과값으로 불러오는 내용으로 설명드렸는데요.

 [엑셀] 가로세로 형식으로 작성된 표안의 내용을 결과값으로 가져오고 싶을때!!

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

이번 내용은

1 ~ 100 : 100

101 ~ 200 : 200

201 ~ 300 : 300

        .

        .

        .

이러한 구간에 따른 결정값이 정해져있는 표에서 구간 사이에 해당하는 값을 작성하면 결정값이 나오도록 할 수 있는 수식 작성 방법입니다.

 

도움이 되셨다면

구독, 좋아요, 영상공유 부탁드리겠습니다. 감사합니다^^!

---

 

질문 주셨던 내용을 예시로 들어보겠습니다.

데이터 유효성 검사로 A가 나오고

 ​①에 수치를 넣으면 ①옆 노란 부분에 기준치 수치가 들어가게 하고 싶습니다.

 ​   예로, 1520 이면 1500 이렇게 정규화된 수치를 넣고 싶은 생각이 있습니다.

 ​

 ②또한 수치를 넣으면 ②옆 노판부분에 기준치 수치가 들어가게 할려고 하는데

 ​   단, 유효성검사로 A가 나올때와 B가 나올때의 기준치는 틀립니다.

 

 A가 나오고 ① 에 1520면 ①옆 노란부분에 1500이 되고

 B가 나오면 ① 에 1342면 1400 이 되게 하고 싶습니다.

질문을 주신 분은 그림파일을 보여주시면서 질문을 해 주셨기 때문에 무엇을 하고자 하시는지 대략적으로 파악할 수 있었으나,

그림파일 없이 질문 내용만 보시는 여러분께서는 무슨 내용인지 이해하지 못할 수도 있기 때문에

▲ 질문 내용을 INDEX 함수와 MATCH 함수로 수식을 작성할 부분에 대해서만 다시 정리해보면

    그림에서 보는 것처럼

    1번에 1520을 입력하면, 2번에 1500이란 숫자가 나오고

    3번에 1342를 입력하면, 4번에 1400이란 숫자가 나왔으면 좋겠다는 질문 내용입니다.

​

    유효성 검사에 대한 질문은 다음번에 유효성 검사에 대해서만 따로 내용 정리해 볼게요~

​

    즉, 1500 이상 1600미만이면 1500을 결과값으로,

    1300 이상 1400미만이면 1400을 결과값을 가져오라는 그런 규격화된 표로 정리할 수 있는 데이터가 존재할 것 같습니다.

책상			침대			책장
이상	미만	규격사이즈	이상	미만	규격사이즈	이상	미만	규격사이즈
300	600	600	300	600	600		800	800
600	900	900	600	800	800	800	900	900
900	1000	1000	800	1000	1000	900	1200	1200

▲ 그리고

    질문자분께 추가로 규정하고 있는 사이즈의 정보를 확인할 수 있었는데,

    위 표처럼 책상, 침대, 책장이란 품목마다 사이즈가 300 이상 600미만은 사이즈 600으로 결정이라는 규격화된 정보가 있다는 것입니다.

    그럼 이제 이 표를 기준으로

    A2 셀에 책상, 침대, 책장의 품목을 입력하고,

   1번과 3번에 사이즈를 입력하면 규격화된 사이즈의 값이 2번과 4번에 자동으로 나오도록 할 수 있습니다.

    결과값은 꼭 숫자가 아니고, 텍스트로 입력되어 있어도 될 것 같습니다.

---

그럼 위 내용으로 수식을 작성해 보겠습니다.

▲ 우선은 "참조"라는 시트에

    이렇게 품목별로 구간별에 따른 규격화된 결과값을 표로 정리해 두었습니다.

▲ 그리고, 다른 "Sheet1"시트에 이러한 값을 넣어 규격 값을 얻을 수 있는 기본 양식을 작성했습니다.

    가로, 세로의 노란색 음영으로 칠해 놓은 곳에 수식을 작성하여,

    임의의 숫자를 입력했을 때 A2 열의 품목에 해당하는 규격화된 정에 규격 사이즈의 값을

    "참조"시트에 작성한 표를 참조하여 결과값을 가져오게 할 생각입니다.

 

    ​수식은

    가로의 경우

    =IFERROR(IF(A2="책상",INDEX(참조!$E$3:$E$1000,MATCH(Sheet1!C2,참조!$C$3:$C$1000)),

    IF(A2="침대",INDEX(참조!$I$3:$I$1000,MATCH(Sheet1!C2,참조!G3:G1000)),

    IF(A2="책장",INDEX(참조!M3:M1000,MATCH(Sheet1!C2,참조!K3:K1000))))),"")

    이렇게 작성을 해 주시면 표를 참조해서 품목이 책상일 때와 침대, 책장일 때 각각의 품목에 따라 "참조"시트에 작성해놓은

    표에 따라서 어떤 숫자의 구간에 해당하는지에 대한 결과값을 가져오게 됩니다.

​

    세로의 경우에는

    =IFERROR(IF(A2="책상",INDEX(참조!$E$3:$E$1000,MATCH(Sheet1!E2,참조!$C$3:$C$1000)),

    IF(A2="침대",INDEX(참조!$I$3:$I$1000,MATCH(Sheet1!E2,참조!G3:G1000)),

    IF(A2="책장",INDEX(참조!M3:M1000,MATCH(Sheet1!E2,참조!K3:K1000))))),"")

    이렇게 작성해 주시면, 가로의 경우와 마찬가지로 품목에 따라 표를 참조해서 결과값을 가져오게 됩니다.

---

"백문이 불여일견"이라는 말이 있듯이

직접 예시를 생각해 보시고, 수식을 한번 작성해 보시면 글을 읽어보시는 것보다 이해가 더 쉬울 거예요~

한 번씩 직접 제가 작성해 드린 수식을 보시고, 해당 셀 주소 값들을 바꾸어 작성해 보시기 바랄게요~!!

​

그럼 오늘 내용도 많은 분들께 도움이 되시길 바라겠습니다.

좋은 하루들 보내세요~^^!