엑셀｜빈칸이 아닌 곳의 위치 나타내기 (2) 숫자 크기에 따라 [ index, match, large, row, iferror ] (＊중복 값 제외)

 

저번 글에서는

2020/11/23 - [프로그램/엑셀｜수식&함수] - 엑셀｜빈칸이 아닌 곳의 위치 나타내기 (1) 순서대로 [ index, column, row, small ]

 

방법

1) 빈칸이 아닌 면적의 용도 표시 (숫자 크기에 관계없이 순서대로(좌->우) -> 숫자가 아닌 문자도 적용 가능)

 

2) 빈칸이 아닌 면적의 용도 표시 (숫자 크기에 따라)

 

 

중에서 1) 방법에 대해서 알아보았다

 

 

 

 

이번에는 2) 방법에 대해 포스팅한다

 

 

순서

- 빈칸이 아닌 곳의 위치를 나타내기

 

- 위치값을 Index & match 함수에 적용시켜 해당 면적의 용도 표시

 

  (이 글에서는 큰 값 순서대로 나타내본다)

 

---

 

이렇게 되었으면 좋겠다

 

이렇게 면적 범위에 빈칸이 아닌 곳의 데이터를 용도별로 큰 순서에 따라 나타내려 하는데

 

여기서 한 가지 주의해야 할 점이 있다

 

★ 주의

여기에 수반되는 가정 중 하나는 숫자들의 크기가 각각 달라야 하는 점이다   다시 말하면   처리하는 데이터의 값들이   중복된 숫자가 없다는 가정 하에 이 수식이 작동한다

 

왜냐하면 Large 함수를 이용하기 때문 (작은 순서대로 표시하고 싶으면 Small 함수)

Large / Small 함수는 ~~(범위) 중에서 가장 큰 / 작은 값을 나타내고

범위 중에서 중복된 데이터가 있으면 그중에서 가장 앞에 있는 값을 반환한다

 

때문에 나머지 값들은 반영이 안되어  

 

중복된 값이 있고 용도가 누락이 안되게 나타내려고 하는 경우는

 

아예 다른 함수 조합들로 이용을 해야 한다

 

---

구조

 

2층의 수식을 파헤쳐 보면

 

=IFERROR(INDEX($C$3:$F$3,MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0)),"")

이렇게 된다

 

1) Large 함수로 2층의 4가지 면적 중 ~번째로 큰 값을 찾아

 

2) Match 함수로 그 값에 해당하는 위치를 1, 2, 3, 4 중에 하나로 나타내어

 

3) Index 함수로 용도(APT, 오피스텔 등)의 범위를 지정하고 Match 함수의 값(1, 2 등)을 적용해 용도를 표시

 

4) 위의 예시처럼 면적이 빈칸이면 결과가 오류가 나는데

 

   Iferror 함수로 오류일 때 "" (빈칸)으로 나타내겠다는 함수로 마무리

 

 

---

 

따라 하기                                                  

 

 

 

2층을 먼저 해보면

 

1) Large                                                      

LARGE($C$4:$F$4,ROW()-12

Large 함수의 구조는 이렇다

이 중에서 2층의 C4 : F4를 범위로 지정하고

 

*  C13 ~ C16까지 2층의 4줄이 

 

C4 : F4의 같은 범위를 이용해야 하니 범위에 F4를 눌러

 

C4 : F4 → C$4$ : F$4$ (고정) 시켜준다

 

 

그리고 몇 번째로 큰?에서는

Row()-12가 들어가 있는데 이 Row()-12로 인해서

 

2층의 C13 ~ C16 이 각각 

 

C13 : 첫 번째로 큰

C14 : 두 번째로 큰

C15 : 세 번째로 큰

C16 : 네 번째로 큰

 

의 의미가 된다

 

왜나햐면 앞의 글

2020/11/23 - [프로그램/엑셀｜수식&함수] - 엑셀｜빈칸이 아닌 곳의 위치 나타내기 (1) 순서대로 [ index, column, row, small ]

 

에서 Row와 Column의 기능을 확인할 수 있는데

 

C13 셀에서 행을 의미하는 =Row()를 입력하면 값은 13이 된다

 

첫 번째로 큰의 1을 만들기 위해 C13 셀에 Row()-12를 해주면 13-12 = 1이 되고 (첫번째로 큰)

 

Row() 뒤에 -12 값은 고정이기에 C14~C16에 복사를 하면

 

C14의 Row()-12는 14-12 = 2  즉, 두번째로 큰이 되고 

 

마찬가지로 C15, C16은 각각 세번째, 네번째로 큰 이라는 의미가 된다

 

LARGE($C$4:$F$4,ROW()-12

그러므로

 

 

 

LARGE($C$4:$F$4,ROW()-12  =   

C13에서는 200

C14는 100

C15는 50

C16에서는 {#NUM!} (오류)를 반환할 것이다

 

 

 

 

 

2) Match                                                      

MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0)

 

Match의 구조는 이렇다

즉 범위 안에 있는 기준값의 상대적인 위치를 숫자로 반환한다

 

 

 

C13 셀은 앞서 Large로 구한 값이 200 일 테고

 

2층의 C4 : F4 안에서 200의 위치는 두 번째 칸이기에 

 

Match 함수 값은  2 를 반환한다

=MATCH ( 200 , C4:F4 , 0 ) = 2 

(정확도는 0으로 해야 '정확히 일치'하는 값을 반환)  --------------------------->

 

그런데 기준값인 첫 번째로 큰 200의 값을

 

C13 셀에 수기로 직접 써서 나타낼 것이 아니고

 

C14~C16까지 두 번째로 큰 100, 세 번째로 큰 50의 값이 자동으로 나와야 하기 때문에

 

앞의 Large 수식을 넣어 준 것이므로

 

Match 함수의 구조는

그래서

MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0)

의 결과가 나온 것이다

 

기준값이 속한 범위인 C4 : F4 역시 2층의 4칸(C13~C16)에 모두 같은 범위로 쓰일 것이기 때문에 F4로 고정

 

=$C$4:$F$4

 

---

 

결국  2 라는 숫자를 자동으로 구하기 위해서 여기까지 왔고

 

제목의 '빈칸이 아닌 곳의  위치 나타내기'는 끝이다

 

 2 라고 나타낸 것이다 

 

이제 이  2 를 가지고 원하는 값 (APT, 오피스텔 등)이 나오게 하는것이 다음 과정이다

 

---

 

3) Index                                                      

INDEX($C$3:$F$3,MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0))

Index의 구조 역시 저번 글을 참고하여

2020/11/23 - [프로그램/엑셀｜수식&함수] - 엑셀｜빈칸이 아닌 곳의 위치 나타내기 (1) 순서대로 [ index, column, row, small ]

이러한 구조이고 

 

범위 내에서 행, 열의 숫자만큼 그곳으로 찾아가 값을 나타낸다

저번 글에서 확인할 수 있다시피

 

범위가 위 사진처럼 하나의 행 or 하나의 열 이면 행 번호, 열 번호를 둘 표시 안하고

 

하나만 표시해도 수식이 작동한다

 

위 사진 역시 범위가 하나의 행이기에

(범위 내에서 APT는 1행 1열, 오피스텔은 1행 2열, ~~)

 

모두가 1행이기에 행번호에 숫자를 넣는 의미가 없어

이렇게만 넣어도 수식이 작동한다

 

결국 범위는 $C$3:$F$3 / 열 번호는 2

= index( $C$3:$F$3 , 2 ) = 오피스텔

 

인데 이 2를 자동으로 반환되게 하려고

 

앞에서 match 함수로 열심히 구해놓았기에

INDEX($C$3:$F$3,MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0))

즉 이렇게 성립이 될 수 있는 것이다

 

 

거의 다 왔다

 

 

 

 

 

 

4) Iferror                                                      

IFERROR(INDEX($C$3:$F$3,MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0)),"")

이건 간단하다

만약 수식이 에러가 난다면 이렇게 해라

 

 

이러한 구조이고

 

앞에서 구한 Index까지의 긴 수식을

 

모든 수식 칸에 왕창 때려 넣고

 

,  (쉼표) 넣고

 

빈칸으로 나타내려면

 

""

 

을 넣으면 된다

 

이 수식은 C16칸을 위한 것이다

 

F4의 용도'하하하'에 면적이 없어서

 

3)까지의 과정을 거치면 C16은 #NUM! 를 반환한다

 

이렇게 나오도록 가만히 내버려 둘 직장인은 없기에

 

오류를 깔끔하게 빈칸으로 표시하는 마무리 작업이다

IFERROR(INDEX($C$3:$F$3,MATCH(LARGE($C$4:$F$4,ROW()-12),$C$4:$F$4,0)),"")

 

---

끝났다

 

이렇게 1층 C17~C20까지도 범위와 Row 값을 바꾸어 적용하고

 

신나게 숫자를 입력하고 지워보며 잘 만들어졌는지 확인해본다

 

 

 

---

지금까지는 숫자가 큰 순서대로 만들었고

 

작은 순서대로 만드려면 Large 대신 Small 함수를 이용한다

 

---

 

 

다시 언급하자면

 

숫자들의 중복이 없다는 가정 하에

 

이 글이 유효하다

 

 

 

 

 

 

 

 

 

 

 

 

 

Rmx

 

 

 

---