엑셀 INDIRECT 함수, ADDRESS 함수 사용법 및 활용법

ADDRESS 함수를 사용하다 보면,일부 함수에선 단독으로 사용하는 경우도 있지만, INDIRECT와 함께 사용하는 경우도 굉장히 많은 편인데요.

 

이번 포스팅에선 ADDRESS 함수 사용법과 INDIRECT 함수 사용법, 그리고 ADDRESS 함수와 INDIRECT 함수를 조합해 사용하시 활용 빈도 높은 방법을 소개해 드리도록 하겠습니다.

 

 

엑셀 함수 - ADDRESS 함수 사용법

 

ADDRESS 함수는 주어진 행, 열, 시트 값과 2가지 옵션 값을 이용해, 셀 주소 값을 (ex : Sheets2!A2)을 문자 형태로 표시(변환)해주는 함수입니다.

 

 

▼ 형식 : ADDRESS(행 값, 열 값, 절대주소, R1C1 및 A1 스타일, 시트 이름) 

 

① 행 값 : 세로 행 위치 값을 몇 번째로 지정할지 기입합니다.

 

② 열 값 : 가로 열 위치 값을 몇 번째로 지정할지 기입합니다.

 

③ 절대주소 : 절대주소는 행, 열을 고정시킬 때 사용하는 방법인 $ 기호를 사용하며, 행만 붙일 수도, 열만 붙일 수도 있습니다.

옵션에 따른 주소값 변화) 1 : $A$1 , 2 : A$1, 3 : $A1, 4 : A1

 

④ 스타일 : R1C1 방식을 사용할지, A1 스타일을 사용할지 지정할 수 있는데요. 제 기억으론 R1C1은 오래전 엑셀에서 사용하는 방법이라 호환성을 위해 지원되는 것으로 기억하고, 아직 활용되는 부분이 있기는 하지만 대부분 A1 스타일을 사용하기 때문에 옵션 값 1인 A1 스타일을 지정해 주시면 특이사항 없이 진행할 수 있습니다.

옵션에 따른 주소값 변화) 0 : R1C1(주소 형식 : R[1]C[1]) , 1 : A1(주소 형식 : A1)

 

⑤시트 이름 : 동일한 시트에 존재하더라도 Sheet 이름을 지정하고 싶으시거나, 다른 Sheet에 있는 내용을 가져오는 경우 사용하시면 됩니다

 

참고 사항 : ④스타일과 ⑤시트 이름은 공백으로 입력시, A1 스타일의 시트이름 없는 셀 주소로 기입됩니다 ( ex : A1 )

 

 

예제 수식 : ADDRESS(3,2,1,1,"Sheet2")

결과 : Sheet2!$B$3

 

① 행 값 3, 열 값 2를 셀 주소 형식으로 변환합니다 (결과 : B3)

 

② 옵션 1을 사용해 절대 행 / 절대 열 값을 반영하며, 생략할 경우도 지금과 동일한 절대 행 / 절대 열 값이 반영됩니다 (결과 : $B$3)

 

③ 옵션 1을 적용해 A1 스타일을 사용하며, 생략할 수 있는 옵션입니다 (결과 : $B$3)

 

④ "Sheet2" 값을 시트 이름으로 사용하며, 생략할 수 있는 옵션입니다 (결과 : Sheet2!$B$3)

 

📌실업급여 수급자격 알려드립니다

실업급여 수급자격 알려드립니다

📌2021년 기준 중위소득 70% ~ 150%

2021년 기준 중위소득 70% ~ 150%

 

 

INDIRECT 함수 사용 방법과 ADDRESS 함수 함께 활용하기

 

INDIRECT 함수는 문자형 셀 주소 값을 받으면, 받은 셀 주소 값과 동일한 실제 셀 내용을 가져오는 함수입니다. 문자형 셀 주소를 사용한다는 말에서 ADDRESS와 조합이 잘 맞을 것 같은 느낌이 들지 않나요? 

 

어차피 따로 다루기엔 애매한 내용이기 때문에 함께 다뤄보도록 하겠습니다.

 

 

▼ 형식 : INDIRECT(문자형 셀 주소 값, 스타일 )

 

① 문자형 셀 주소 값 : 말 그대로 문자형 셀 주소 값을 기입할 수 있으며, 예로 "A1" 과같이 입력하면 됩니다

 

② 스타일 : ADDRESS에서 언급 드렸던 스타일과 일한 항목인데요. R1C1과 A1 스타일 중에 하나를 선택할 수 있으며, 입력하지 않을 경우 A1 스타일로 자동 반영됩니다

EX ) FALSE : R1C1 , TRUE : A1

 

 

INDIRECT 단일 사용 예제 수식 : INDIRECT("B3",TRUE)

INDIRECT 함수와 ADDRESS 함수 예제 : INDIRECT(C3,TRUE)

결과 : "ADDRESS 예제, 절대주소 1 및 공백" 라는 문자 표시됨

 

① INDIRECT에 주어진 주소 값의 자료를 읽어드리는데 단일로 사용할 경우 "B3" 라는 문자 그대로의 주소를, ADDRESS와 이용할 땐 C3 셀에 입력된 "Sheets4!$B$3" 셀의 내용을 가져옵니다. 결과적으로 두 조건이 동일한 값을 가져오는 것이지요. 

 

② TRUE 값을 입력했기 때문에 주소값은 A1 스타일로 인지해, ①번 결과와 동일한 B3 또는 Sheets!$B$3 주소의 내용을 가져옵니다 (결과 : "ADDRESS 예제, 절대주소 1 및 공백" 라는 문자 표시됨)

 

 

 

 

▼ INDIRECT 함수와 ADDRESS 함수를 응용한 극단적인 예제

 

실전에선 ADDRESS와 INDIRECT의 함수 활용 빈도가 높은 편은 아닌데, 비율을 따지자면 INDIRECT가 그나마 조금 더 높은 편인 것 같은데요. ADDRESS만 별도로 사용하는 경우보다 INDIRECT를 결합한 활용도가 더 높기 때문에, 두 함수를 활용한 예제를 하나 더 보록 하겠습니다

 

 

위 예제는 4가지 경우에 대해 다루고 있는 예제인데요. SUM만 사용한 경우, SUM과 INDIRECT + ADDRESS를 활용한 경우, SUM과 INDIRECT를 사용한 경우, SUM과 ADDRESS를 사용한 경우가 그것인데요

 

예제 1 : SUM(B3:C11)

예제 2 : SUM(INDIRECT(ADDRESS(3,2,1)):C11)

예제 3 : SUM(INDIRECT("B3"):C11)

결과 : 900

 

예제 4 : SUM(ADDRESS(3,2,1) & ": C11")

결과 : #VALUE

 

① 예제 1, 2, 3의 경우 결국 SUB(B3:C11)과 동일한 방식으로 계산되는데요. 예제 2번을 기준으로 풀이를 해보면 ADDRESS(3,2,1)이란 값을 통해 B3 라는 문자형 주소 값을 만들어냅니다.

 

② INDIRECT("B3") 형식을 통해 실제 B3의 값을 가져오는데, SUM함수에 포함되있는 주소 값이기 때문에 B3:C11 형식으로 사용됩니다. 결론적으로 SUM(B3:C11) 이란 값으로 계산되게 됩니다 (결과 : 900) 

 

③ 예제 4의 경우 동일한 방식으로 진행되긴 하지만, "B3:C11"이 주소값 형식이 아닌 문자 형식이기 때문에, SUM 연상 중 오류가 발생되는 것인데요. 이 경우 예제 2처럼 ADDRESS를 함께 사용해야 문제없이 사용 가능합니다

 

ADDRESS와 INDIRECT는 사용빈도가 높다고 할 수는 없는 함수인데요. 엑셀 함수의 난이도가 초 ~ 중급으로 넘어가게 되면 가끔씩 활용해야 되는 경우가 생기는데, 이럴 때 자신의 실력을 향상시킬 수 있는 좋은 함수라 할 수 있습니다. 

 

물론 자주 사용하지 않으면 잊어버리기 쉽겠지만, 한 번도 연습해보지 않은 것 보단 한 번이라도 연습해 보는 것이 기억하고 응용면에서 도움이 되시리라 생각됩니다.

📌엑셀 Vlookup 함수 사용법

엑셀 Vlookup 함수 사용법

📌엑셀 특정문자 추출 간단하게 진행하기

엑셀 특정문자 추출 간단하게 진행하기

📌엑셀 함수 정리 Top7

엑셀 함수 정리 Top7