-
엑셀｜빈칸이 아닌 곳의 위치 나타내기 (1) 순서대로 [ index, column, row, small, iferror ]프로그램/엑셀｜수식&함수 2020. 11. 23. 20:46
엑셀 수식을 이용하는 이유중 하나는
편리한 데이터 관리일 것이다
정리하고 싶은(해야 할) 데이터를 일일이 수작업 하는것 보다는
'자동으로' 나타났으면 하는 마음일 것이다
이번에는 빈칸이 아닌 숫자or문자가 있는 위치를 나타내는 방법을 포스팅 해보려 한다
+ 그것을 이용한 원하는 값 추출
마땅히 예시가 떠오르지 않아 실제로 이용하려 했던 상황을 간략하게 표현해 적용해본다
예시)
* 다른 건물일 때마다 용도별로 면적이 항상 바뀐다 바뀔 때마다 파란색 테두리 칸만 수정을 하면
C13부터 시작되는 '용도'열에 자동으로 표시가 되었으면 좋겠다
0방법
1) 빈칸이 아닌 면적의 용도 표시 (숫자 크기에 관계없이 순서대로(좌->우) -> 숫자가 아닌 문자도 적용 가능)
2) 빈칸이 아닌 면적의 용도 표시 (숫자 크기에 따라)
1) 빈칸이 아닌 면적의 용도 표시 (숫자 상관없이)
이렇게 자동으로 나왔으면 좋겠다 2층을 먼저 적용 해볼때 간단히 과정을 확인해보면
1_ 2층의 범위 C4:F4 (4칸)안에 0이 아닌 값들을 따로 추출하여
400의 위치 = 2
50의 위치 = 4 로 환산
2_ C9,C10,C11,C12에 각각 위치로 환산한 숫자중 작은순서대로 반환하게 표시 (왼쪽부터 나오도록)
차근차근 따라해보기
(1)
index의 수식은
2020/10/22 - [엑셀/엑셀｜수식&함수] - [ index & match ] 엑셀 vlookup보다 유용한 index 와 match 조합 !!
에서 확인했듯이 범위에서 값 또는 값에 대한 참조를 반환하는데
=INDEX($C$4:$F$4,) 
이러한 구조에서
범위만 넣고 행,열번호를 넣지 않고 수식을 닫으면
이렇게 오류가 난다
결과는 오류지만 I4셀에 F2키로 수식확인 후 F9를 눌러 계산값을 보면
지정한 범위만큼의 배열이 생성된 것을 확인할 수 있다
(2)
=INDEX($C$4:$F$4<>0,) 그다음엔 그 범위에 0이 아닌(<>0)을 범위에 넣고 역시 행,열번호는 안넣으니
계산값에 생성된 배열은 0은 FALSE로, 0이 아닌값은 TRUE로 환산한 배열이 생성 되었다
(수식 결과는 맨 앞의 값(FALSE)을 반환)
(2.5)
=COLUMN(C4:F4)-2 행,열의 숫자 반환 함수 Row,Column에 관해 잠깐 말하면
Row 해당 셀의 행 숫자표시 Row(C4) = 4 (4행) = 'C4'셀에서 =Row() = 4 Column 해당 셀의 열 숫자표시 Column(C4) = 3 (C가 3열이라서) = 'C4'셀에서 =Column() = 3 여기에 Column의 인수로 범위를 넣으면
Column(C4:F4) = 3을 반환하면서 F9를 눌러 확인하면 ={3,4,5,6} 이라는 값들로 범위만큼을 배열생성한다
뒤에 나오게 될 Small함수로 3,4,5,6중 가장 작은값을 나타내는데
3,4,5,6중 작은값이나 1,2,3,4중 작은값이나 맨 앞의 값이 추출될 것은 똑같지만
포스팅 제목처럼 범위 내에서의 위치를 표시하기 위해
Column(범위)-2 를 하여 1,2,3,4가 나오도록 수정하였다
(수식 결과는 역시 맨앞의 1을 반환)
(3)
=IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2) 그래서 (2)단계의 {FALSE,TRUE,FALSE,TRUE} 배열자리에 (2.5)단계의 {1,2,3,4}가 대응되어 반환하도록 if함수로 합쳐주니
결과는 맨앞의 FALSE를 반환하며 계산값으로는 TRUE자리에만 2와 4가 반환되었다
(4)
숫자 크기에 관계없이 순서대로 왼쪽부터 오른쪽까지 ('APT'->'오피스텔'->'근린생활시설'->'하하하')
를 나타내기 위해 Small 함수를 이용한다
왜냐하면 앞에서 'APT', '오피스텔', '근린생활시설', '하하하'를 각각 {1,2,3,4}로 대체 했기 때문에
Small 함수로 작은 숫자부터 표시해야지 왼쪽부터 표시가 되기 때문
(범위)중 ~번째로 작은값을 반환하는 Small 함수의 범위에 (3)의 수식자체를 이용해서 2를 추출해보면
=SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),1) 
Small( (3)수식 통째로, 1 )
-> (3)의 범위{ FALSE , 2 , FALSE , 4 }중 1번째로 작은 값=2
** 그리고 여기서부턴 배열수식이기 때문에 수식 입력후 그냥 Enter를 하면 안되고
를 해주어야 한다
드디어 400의 위치 = 2로 환산하였다
사실 이 포스팅의 제목처럼 빈칸이 아닌 곳의 위치를 나타내는 것은 끝났다
50의 위치 = 4로 환산하려면
SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),1) 에서 1을 2로 바꾸면 {1,2,3,4}중 2번째로 작은값인 4를 나타낼 수 있다
그리고 맨 오른쪽부터 용도 '하하하'->'APT' 순서대로 나타내고 싶다면
Large함수를 쓰면 된다 ('APT','오피스텔','근린생활시설','하하하'를 각각 {FALSE,2,FALSE,4}로 변환했기에)
여기서부턴, 빈칸이 아닌 곳의 위치를 나타내고 나서
그것을 이용한 원하는 값 추출 단계이다
(5)
=INDEX($C$3:$F$3,SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),1)) 예시의 목표대로 용도가 나오도록 하면
의 구조에서
=index(범위,2) ='오피스텔'이 나오기 위해
범위를 용도가 써있는 C3:F3으로 (하나의 행),
행번호의 인수를 2가 쓰이도록 하기 위해 (4)단계의 함수를 통째로 쓴 것이다
(6)
거의다왔다
2층의 '용도'열 C13,C14,C15,C16에 수식을 각각 적용하려면
방금 (5)의 =INDEX($C$3:$F$3,SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),1)) 에서
Small함수의 '몇번째로?' 에 해당하는 맨뒤에 1부분을
C14~16셀에 각각 2,3,4로 바꿔 넣어야 하는데
이또한 수작업이고 다루는 데이터가 많을때는 번거롭고 오래 걸린다
그래서 맨뒤에도 1,2,3,4로 자동으로 바뀌게 하는 함수 ROW를 적용하였다
C13셀에 =Row()를 입력하면 13를 반환한다 (C13의 행이 13이라서)
그러므로 C13셀에 =Row()-12를 입력하면 =13-12가 되어 1을 반환한다
마찬가지로 C14에 Row()-12를 하면 2를 반환하기에
C13의 셀을 완성 후 C14~16까지 붙여넣으면
맨 뒷부분 역시 일일이 수정을 하지 않아도 된다
( + 배열수식으로 인해 Ctrl+Shift+Enter를 한번 한 셀은 그대로 복사 붙여넣기 해도 된다)
(7)
2층의 APT와 근린생활시설엔 면적이 없어 'FALSE'로 반환된 부분을 나타내다보니 오류가 난다
=IFERROR(INDEX($C$3:$F$3,SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),ROW()-12)),"") 그부분은 Iferror 함수를 이용하였다
이 수식은 말그대로 if(만약) error(에러)가 나면? 이고
=IFERROR( 수식 , "" )
=IFERROR(INDEX($C$3:$F$3,SMALL(IF(INDEX($C$4:$F$4<>0,),COLUMN($C$4:$F$4)-2),ROW()-12)),"")
빨간 수식이 에러가나면 "" (큰따옴표 사이에 아무것도 없으니) "빈칸으로 표시하겠다" 라는 말이다
(빈칸, 숫자, 문자 등 다양하게 표시 가능하다)
(8)
(결과)
이렇게 2층 한칸만 해놓고 나머지 3칸에 복사,
그리고 1층도 범위와 Row값을 바꾼 한칸을 나머지칸에 복사 하면 완성이 된다
수식이 잘 적용이 되었는지, 첫 예시 목표대로 파란색 테두리만 바꾸면 자동으로 적용이 되는지
확인해본다
0참고로 D13 이하의 '전용면적'열은
저번 포스팅의
2020/10/22 - [엑셀/엑셀｜수식&함수] - [ index & match ] 엑셀 vlookup보다 유용한 index 와 match 조합 !!
을 참고하면 C13셀 이하의 용도가 정해지자마자 옆에 같이 표시될 수 있게 만들 수 있다
2) 빈칸이 아닌 면적의 용도 표시 (숫자크기에 따라)
는 다음에 포스팅 하도록 하겠다
끝
이글은
http://lightblog.tistory.com/187
의 포스팅을 참고하였음을 알린다
반응형'프로그램 > 엑셀｜수식&함수' 카테고리의 다른 글