엑셀 INDIRECT 함수 설명 사용법
※INDIRECT 함수는 셀의 값을 참조하는 함수로 여러 방법으로 많이 쓰입니다.
이 포스팅에서는 indirect 함수로 범위 데이터를 하나의 열로 바꾸는 방법을 알아보겠습니다.
이 방법을 하기 위해서는 Countifs, Counta, Match, Offset 함수도 알아야 하기 때문에,
결코 쉬운 함수가 아닙니다.
더 나은 방법으로 하시는 분도 있지만 저는 이방법을 선택했습니다.
시작하겠습니다.
※왼쪽에 뿌려져있는 데이터를 아래로 데이터 쌓아서 표시하는 방법을 알아보겠습니다.
실무에서 이런식의 데이터를 처리하시는 분들을 보았기에,
이 포스팅이 그분들에게 유익했으면 합니다.
위의 표에서는 보조열2개, 보조열기 준 총 보조열을 3개를 썼습니다.
※Counta 함수를 쓴 이유는 범위내의 빈셀을 제외하고 개수를 세기 위함입니다.
그래서 A2번 셀 부터 5, 7, 8, 4, 6, 7이라는 숫자가 나와있는데,
그 이유는 범위내에서 ABCDE라는 텍스트가 5개가 있어서 A2번 셀에는 5라는 숫자가 나타납니다.
COUNTA의 범위는 뿌려진 데이터가 더 많으면 더 늘어날 수 있습니다.
※추가로 보조열기준 시작셀인 B2 셀에는 숫자 2가 들어간 이유는
2번째 행이 데이터의 시작점이기 때문에 2로 시작합니다.
그다음부터는 COUNTA의 숫자 A2셀 + B2셀(2) 값을 더한 값을 B2번 셀부터 적습니다.
숫자들이 2, 7, 14, 22, 26, 32라는 숫자들이 나오는데,
여기서의 숫자는 오른쪽에 데이터를 나열하기 위한 씨앗 역할을 한다고 보시면 됩니다.
아래 계속 설명
※Match함수를 활용한 이유는 숫자를 반환하여
OFFSET 및 INDIRECT함수에서 시작점을 맞추기 위해서입니다.
아래 참고하시면 좋은 함수들
2022.05.03 - [엑셀공부/엑셀 하루에 하나씩] - 엑셀 INDEX MATCH 함수 다중 범위 브이룩업 다중 조건
2022.01.07 - [엑셀공부/엑셀 하루에 하나씩] - 엑셀 Match함수 매치 함수 알아보기 직장인 엑셀
"가"는 텍스트를 C열에서 정확히 일치하는 값을 찾으면 2번째 행에 있기에 숫자 2를 반환하고,
"나"는 3번째 행에 있기에 3이라는 숫자를 반환합니다.
※위의 이미지에서 INDIRECT함수가 나왔습니다.
INDIRECT("C"의 의미는 "C열의 텍스트를 참조할 거다"입니다.
INDIRECT("C"& 의 의미는 "C열과 숫자를 합친 셀을 참조 반환 할 거다"입니다.
예) INDIRECT("C"&2)는 C2번 셀의 값을 반환하겠지요.
INDIRECT("C"&Match( 의 의미는 "C열에서 match함수로 가져온 숫자를 합쳐서
나타난 셀을 참조 반환입니다.
INDIRECT("C"&MATCH(ROW()의 의미는
"해당하는 셀의 값(ROW함수)을 MATCH 함수를 통해 숫자로 반환 한 뒤, C열과 합쳐서 INDIRECT 함수로 참조 반환 할 거다"입니다.
※추가설명
ROW함수는 입력된 셀의 행값을 숫자로 반환하는 것입니다.
예) ROW()라는 함수가 P23셀에 있으면 숫자 23을 반환할 것입니다.
INDIRECT("C"&MATCH(ROW(),$B$2:$B$1000)의 의미는
입력된 셀의 숫자를 B2:B1000 범위에서 찾으려는 LOOKUP_VALUE=ROW() 값 보다 작거나 같은 값을 찾아서 나타냅니다.
MATCH 함수의 3번째 인수에는 빈값이 있기에 기본값인 1이 들어가 있습니다.
1은 Lookup_value보다 작거나 같은 값을 찾을 때 쓰입니다.
즉, N2셀의 INDIRECT 함수의 쓰임은 INDIRECT(C1) 셀을 반환할 것입니다.
하지만 저희가 필요로 하는 값은 2번째 행부터 시작하기에
INDIRECT("C"&MATCH(ROW(),$B$2:$B$1000)+1)
+1의 값을 넣어서 C2셀을 반환하게 하는 것입니다.
N3 셀의 값 또한 3보다 작거나 같은 값을 찾아야 하기에,
당연히 INDIRECT(C1+1) 값인 C2 값인 "가"라는 값이 나오게 됩니다.
※여기서 어려울 수 있습니다.
OFFSET 함수는 동적 배열 함수로 시작점 기준으로 좌 우 위아래를 숫자를 입력해서 확장하는 함수입니다.
=OFFSET(INDIRECT("D"&M2),0, COUNTIFS($M$2:M2, M2)-1)
시작점은 INDIRECT("D"&M2)로 M열은 Match 함수로 숫자를 반환해놓은 값들입니다.
D2셀이 OFFSET 함수의 시작점이 되겠습니다.
=OFFSET(INDIRECT("D"&M2),0 숫자 0의 의미는
행이 위아래로 움직일 필요가 없기 때문에 0 값 고정입니다.
=OFFSET(INDIRECT("D"&M2),0, COUNTIFS($M$2:M2, M2)-1)
Countifs 함수를 넣은 이유는
중복값을 1,2,3,4를 찾아서 -1을 해서 D열부터 K열까지 오른쪽으로 열 확장이라 보시면 됩니다.
=OFFSET(INDIRECT("D"&M2),0,0)
=OFFSET(INDIRECT("D"&M3),0,1)
=OFFSET(INDIRECT("D"&M4),0,2)
=OFFSET(INDIRECT("D"&M5),0,3)
이런 식의 값이 되겠지요. 그래서 기준점을 잡은 다음 오른쪽으로 몇 칸 이동해서 값을 가져올 것인지를 OFFSET함수를 활용해서 값을 가져왔습니다.
※마지막 GIF로 어떤 식으로 값들이 변하는지 보고 포스팅 마치겠습니다.
※참고하면 도움 될 만한 실무엑셀 함수
2022.05.21 - [엑셀공부/엑셀 하루에 하나씩] - 엑셀 SUMIFS 함수 : 합계 더하기 2개 이상 복수 조건 사용법
2022.06.06 - [엑셀공부/엑셀 하루에 하나씩] - 엑셀 조건부서식 색상 다중 조건 색 넣기 IF AND함수
제작문의
https://open.kakao.com/o/sj8kxmde
'엑셀공부 > 엑셀 하루에 하나씩' 카테고리의 다른 글
| 엑셀 필터 단축키 및 필터 합계 설정 추가방법 (0) | 2023.01.20 |
|---|---|
| 엑셀 중복값 찾기 제거 표시 방법 알아보기 (0) | 2022.12.23 |
| 엑셀 브이룩업 Vlookup Match 함수 알아보기 (0) | 2022.11.04 |
| 엑셀 countif 카운트이프 함수 갯수세기 개수세기 방법 (2) | 2022.10.13 |
| 엑셀 빈칸채우기 자동채우기 자동완성 단축키 (0) | 2022.10.05 |
