엑셀 ADDRESS 함수 사용법을 기본적으로 알아보겠습니다. 그리고 INDIRECT(ADDRESS 함수) 사용법까지 자세히 알아보겠습니다. 지금부터 엑셀 셀 위치 함수 ADDRESS 함수에 관해 총정리해보겠습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 ADDRESS 함수 사용법
- ADDRESS 함수: 입력한 열,행 번호의 셀주소를 내가 지정한 형식으로 입력되도록 하는 함수
- =ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
엑셀 ADDRESS 함수는 열 번호, 행 번호를 입력하여 내가 원하는 스타일로 셀 주소가 입력되도록 해주는 함수입니다.
예를들어 A1 이라는 셀 주소가 입력되도록 하기 위해서 ADDRESS 함수를 사용할 수 있습니다.
ADDRESS 함수 내 행번호, 열번호는 필수적으로 입력을 해야 합니다. 하지만 참조유형, 주소형식, 참조시트는 필요한 경우에만 입력을 하면 됩니다.
참조유형, 주소형식, 참조시트 부분의 경우 조금 복잡하기 때문에 일단 요약 정리부터 하고, 예시는 아래에서 자세히 살펴보겠습니다.
<참조유형>
- 1 입력 시 $A$1 (절대참조)
- 2 입력 시 A$1 (행만 절대참조)
- 3 입력 시 $A1 (열만 절대참조)
- 4 입력 시 A1 (상대참조)
<주소형식>
- 1 입력 시: A1 스타일
- 0 입력 시: R1C1 스타일
<참조시트>
- 셀 주소에 시트명이 입력되도록 하고 싶은 경우 시트 명을 큰 따옴표 안에 입력하기
- 참조시트를 입력 안 할 경우 자동으로 현재 시트의 셀이 입력이 됨
- 현재 시트의 시트명을 입력하고 싶다면 현재 시트 이름을 큰 따옴표 안에 입력하면 됨
엑셀 ADDRESS 함수 기본사용법
엑셀 ADDRESS 함수 기본 사용법을 하나하나 자세히 알아보겠습니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1) → $A$1
첫 번째로 ADDRESS 함수 내 행번호, 열번호만 입력해보겠습니다.
예시로 A1에 해당되는 셀의 주소가 입력이 되도록 해보겠습니다. 따라서 위와 같이 수식을 입력합니다. 그럼 결과값으로 $A$1 이 나옵니다.
A1 셀의 행번호는 1이고, 열번호도 A이니까 1입니다. 따라서 =ADDRESS(1,1) 이라고 입력을 한 것입니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,1) →$A$1
이번에는 행번호, 열번호에 참조유형까지 입력해보겠습니다.
참조유형을 1로 입력하면 행과 열에 둘다 절대참조가 적용됩니다. 따라서 결과 값으로 $A$1 이 나옵니다.
바로 위에서 참조유형을 입력하지 않았을 때도 $A$1이라는 값이 나왔습니다. 이처럼 참조유형을 입력하지 않으면 자동으로 절대참조가 적용이 됩니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,2) →A$1
이번에는 참조유형 부분에 2를 입력해보겠습니다. 참조유형에 2를 입력하면 행에만 절대참조가 적용됩니다. 따라서 결과값으로 A$1 가 나옵니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,3) →$A1
참조유형에 3을 입력하면 열에만 절대참조가 적용됩니다. 따라서 결과값으로 $A1가 나옵니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,4) →A1
이번에는 참조유형에 4를 입력합니다. 그럼 행과 열에 둘다 상대참조가 적용됩니다. 따라서 결과값으로 A1이 나오게 됩니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,1,1) →A1
이번에는 주소형식을 입력해보겠습니다. 주소형식은 2가지로 나뉩니다. 주소형식에 1을 입력하면 A1 과 같은 모양으로 셀 주소가 입력됩니다. 반면 주소형식에 0을 입력하면 R1C1과 같은 모양으로 셀 주소가 입력됩니다.
하지만 R1C1 스타일로 주소를 입력하는 경우가 거의 없습니다. 따라서 주소형식 인수에는 1을 입력한다! 이렇게 외워둬도 괜찮을 것 같습니다. 또는 주소형식을 입력하지 않으면 자동으로 A1 형태로 셀주소가 입력됩니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,1,0) →R1C1
주소형식에 0을 입력하면 R1C1이라는 결과값이 나옵니다. 하지만 R1C1과 같은 모양의 셀 주소는 실무에서도 거의 사용하지 않을 것입니다.
따라서 주소형식의 경우 입력을 하지 않거나 1을 입력을 해주면 됩니다.
=ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
=ADDRESS(1,1,1,1,”abc”) →abc!$A$1
이번에는 ADDRESS 함수 마지막 인수인 참조시트 부분을 입력해보겠습니다.
예를들어 현재 시트가 아닌 다른 시트에 있는 셀 주소를 입력하고 싶을 수 있습니다. 또는 현재 시트의 시트명이 입력되도록 하고 싶을 수 있습니다. 이 경우 참조시트 인수에 시트명을 입력해주면 됩니다.
예시로 아래 사진처럼 “abc” 라는 시트의 “A1” 셀 주소를 입력해보고 싶다고 해봅시다. 그러기 위해서 =ADDRESS(1,1,1,1,”abc”) 라고 입력을 해주면 되는데요. 시트명을 입력할 때는 반드시 큰 따옴표 안에 시트명을 입력해야 합니다.
입력을 다 한 후 엔터키를 누르면 abc!$A$1 이라는 결과값이 나옵니다. 즉, abc 라는 시트명이 셀주소 내에 입력이 된 것입니다.
INDIRECT(ADDRESS 함수) 사용법
ADDRESS 함수는 단독으로 사용되기 보다 INDIRECT 함수와 함께 사용되는 경우가 많습니다. 따라서 ADDRESS 및 INDIRECT 함수를 함께 사용하는 방법을 알아보겠습니다.
아래 사진을 보시면 A열에 과일명이 나와 있습니다. 여기서 우리가 ADDRESS 및 INDIRECT 함수를 이용해서 A2 셀에 위치한 ‘오렌지’ 라는 값을 찾아와보겠습니다.
그러기 위해서 아래와 같이 함수식을 입력해줍니다.
=INDIRECT(ADDRESS(2,1))
‘오렌지’ 는 현재 A2 셀에 위치해있습니다. 즉, 행번호가 2이고, 열번호가 1입니다. 따라서 ADDRESS 함수 내에 2,1 이라고 입력을 하였습니다.
ADDRESS 함수가 먼저 실행되므로 함수식은 아래와 같이 바뀝니다.
=INDIRECT(A2)
INDIRECT 함수는 셀 주소를 문자로 입력하면 입력한 셀의 텍스트를 반환해주는 함수입니다. A2 셀의 값은 ‘오렌지’ 이므로 ‘오렌지’ 라는 결과값이 반환이 됩니다.
그런데 INDIRECT 및 ADDRESS 함수를 이용해서 ‘오렌지’ 값을 구할수도 있지만 INDEX 함수를 이용해서 ‘오렌지’ 값을 구할 수도 있습니다.
아래와 같이 수식을 입력하면 됩니다.
=INDEX(A1:A5,2,1)
그럼 마찬가지로 ‘오렌지’ 라는 결과값이 나옵니다. 그런데 INDEX 함수의 경우 과일 이름이 입력되어 있는 범위를 선택한 후에, 행 번호와 열 번호를 입력하였습니다.
그에 비해 INDIRECT 및 ADDRESS 함수를 사용할 때는 범위 없이 행번호와 열번호만 있으면 되었습니다.
따라서 범위를 입력하지 못하는 경우라면 INDEX 함수 대신 INDIRECT와 ADDRESS 함수를 사용하여 결과값을 구할 수 있습니다.
SUM(INDIRECT(ADDRESS)) 사용법
이번에는 SUM, INDIRECT, ADDRESS 3가지 함수를 모두 사용하는 방법을 알아보겠습니다. 아래 사진의 A열에 나와 있는 숫자들을 모두 더해본다고 해봅시다.
이 경우 아래와 같이 함수식을 입력하면 A 열에 있는 숫자들의 합계를 구할 수 있습니다.
=SUM(A1:A5) → 25
또는 아래와 같이 함수식을 입력해도 같은 결과 값을 낼 수 있습니다.
=SUM(INDIRECT(“A1”):A5) → 25
혹은 아래와 같이 함수식을 입력하더라도 같은 결과 값을 내게 됩니다.
=SUM(INDIRECT(ADDRESS(1,1,1)):A5) → 25
즉, 위 3가지 방법을 사용해서 숫자들의 합계를 동일하게 구할 수 있습니다.
근데 아래와 같은 함수식을 사용하는 경우에는 오류 값이 나옵니다.
=SUM(ADDRESS(1,1,1)&”:A5″) → #VALUE!
바로 위의 함수식에서는 INDIRECT 함수를 사용하지 않았습니다. ADDRESS 함수는 단순히 문자만 입력이 되도록 하는 함수입니다. 해당 문자에 대한 셀주소를 반환하기 위해선 INDIRECT 함수가 필요하죠.
셀주소가 아닌 문자만 입력이 된 것이므로 오류값이 나온 것입니다. 따라서 정확한 값을 구하기 위해선 INDIRECT 및 ADDRESS 함수를 모두 사용해서 함수식을 입력해줘야 합니다.
<결론>
▶︎엑셀 ADDRESS 함수 사용법
- ADDRESS 함수: 입력한 열,행 번호의 셀주소를 내가 지정한 형식으로 입력되도록 하는 함수
- =ADDRESS ( 행번호, 열번호, [참조유형], [주소형식], [참조시트] )
이상 엑셀 ADDRESS 함수 사용법에 관한 글이었습니다.