■ 목차
1. 함수 설명
2. Indirect 함수의 구문 / 사용방법
3. Indirect 함수 사용해 보기
4. Indirect 함수 활용해 보자 - Address 함수와 콤보로 사용
5. Indirect 함수 활용해 보자 - 이중 목록 상자 만들기 (유효성 검사)
6. Indirect 함수 활용해 보자 - 이미지 불러오기
** 핵심 요약
1. 함수 설명
- Indirect 함수는 "텍스트 문자열로 지정된 참조를 반환(?)하는 함수라고 합니다. ^^
- 같은 말인데도 참 어렵게 적은 것 같습니다. 분명 한글인데도 직접 사용을 하고 있는 저 조차도 해석이 쉽지는 않습니다. ㅠㅠ
- 일반적인 엑셀 사용법은 등호( "=" )와 함께 셀 주소를 입력하면 입력한 셀 주소을 찾아서 거기에 입력된 값을 반환하는 방식입니다.
- 그런데 셀 주소가 아닌 "셀 주소를 텍스트 형식으로 입력"을 해도 Indirect 함수는 셀 주소로 변경해서 찾아 주겠다는 의미입니다.
- 그래서 셀 주소를 참조하는 방식과 동일하게 Indirect 함수를 사용을 하려면 셀 주소를 "텍스트" 형식으로 입력을 해줘야 합니다.
- 왜 힘들게 텍스트 형식으로 셀 주소를 입력하는 것일까요? 그냥 바로 입력하면 되지?
- Indirect 함수를 사용하는 이유는 "텍스트" 형식의 셀 주소를 사용할 수 있기 때문입니다.
- "텍스트"는 우리가 원하면 수식으로 만들 수있고 이렇게 수식으로 만든 다양한 셀 참조 주소를 사용하여 엑셀 자동화가 가능하기 때문입니다.
- 그리고 "이름 정의"라는 기능이 있어서 텍스트로 할 수 있는 일이 더욱 많아졌습니다.
2. Indirect 함수의 구문 / 사용방법
- INDIRECT(ref_text, [a1])
- Ref_text 셀에 대한 참조이거나 셀에 대한 텍스트 문자열 참조입니다.
- a1 선택 요소입니다. ref_text가 있는 셀의 참조 영역의 유형을 정하는 논리값입니다.
- 첫 번째 인수 "Ref_text"는 "텍스트" 형식의 셀 주소입니다.
- 텍스트이기 때문에 ①직접 주소를 입력하는 경우는 큰 따옴표( " " )를 사용해줘야 하고
- ② 셀 주소를 참조하는 경우 참조하는 셀 주소의 값이 텍스트 형식의 "셀 주소"이어야 합니다.
- 직접 입력하거나 다른 셀을 참조하거나 결과적으로 "텍스트"형식으로 셀 주소가 입력이 되어야 합니다.
- 여기서 "Ref_text"는 참조하는 셀 주소로 현재 시트, 다른 시트, 다른 통합 문서의 셀 주소를 텍스트 형식으로 입력할 수 있습니다.
- 물론 다른 시트나, 통합 문서는 전체 주소를 입력해줘야 하고 통합 문서를 참조할 경우 해당 통합 문서가 열려 있어야 합니다.
- Indirect 함수의 거의 모든 오류가 바로 "Ref_text"를 정확하게 입력되지 않아서 발생되기 때문에 주의해서 잘 입력해 주셔야 합니다.
- 두 번째 인수 "[a1]"의 셀 주소의 타입을 지정하는 옵션인데 "True", "False" 입력이 가능합니다.
- 엑셀에서 사용할 수 있는 셀 주소 타입은 ① "A1" 스타일 (생략 or True)과 ② "R1C1" 스타일(False) 2가지가 있습니다.
- 보통 우리가 사용하는 스타일이 "A1" 스타일이고 Indirect 함수에서 두 번째 인수를 생략하면 적용되는 기본 값입니다.
- 보통은 그냥 생략하고 사용을 하시면 되고 사실 자동화 관점에서는 "R1C1" 스타일이 더 편리한 경우도 있습니다.
- 참조로 A1 = Row 1, Column 1 = R1C1 (A1 = 1번째 행, 1번째 열)이 되게 됩니다.
- 가장 중요한 것은 "셀 주소을 텍스트 형식으로 정확하게 입력"해주는 것입니다. 거의 여기에서 오류가 발생이 되기 때문입니다.
3. Indirect 함수 사용해 보기
- 1) 셀 주소 참조와 Indirect 함수의 차이
- 동일한 셀 주소를 참조하는 데 있어서 기존의 "셀 주소" 참조 방식과 "Indirect 함수"의 차이점을 간단하게 집어 보고 가겠습니다.
- Indirect 함수에서 중요한 점은 "텍스트 형식의 셀 주소"를 정확하게 입력하는 것입니다.
- 모두 동일하게 "B2" 셀의 값인 "777"을 참조하고자 합니다.
- 첫 번째 일반 셀 주소 참조 방식은 =B2라고 입력을 하게 되면 B2 셀 주소를 바로 참조가 가능합니다. 평소에 우리가 사용하는 방식입니다.
- 두 번째 Indirect 함수를 사용하여 "직접 셀 주소를 입력하는 경우"입니다. =Indirect("B2")라고 반드시 큰 따옴표( " " )를 사용하여 텍스트 형식으로 입력해 주셔야 합니다.
- Indirect 함수는 텍스트 형식의 셀 주소를 "셀 참조 주소"로 변경을 해준다고 했습니다.
- 그래서 =Indirect("B2")를 "셀 참조 주소"로 변경하여 =B2를 만들어 주게 되고 결과적으로 B2 셀을 참조하여 해당 값 777을 가져오게 됩니다.
- 세 번째 Indirect 함수에 사용된 A2 셀 주소는 텍스트가 아닙니다. 그래서 그대로 "셀 참조 주소"로 A2 셀을 참조하여 해당 값인 텍스트 "B2"을 가져오게 됩니다.
- =Indirect(A2) → =Indirect("B2")가 되게 되고 2번째 예제와 동일한 경우가 되어서 결과 값 777을 가져오게 되는 방식입니다.
- 우리는 비슷비슷하게 보이는 ="A2", =A2 라도 엑셀은 하나는 큰 따옴표를 사용했기 때문에 "텍스트"이고 나머지 하나는 "셀 참조 주소"로 엄격하게 구분이 됩니다.
- 그래서 결론적으로 "Indirect(텍스트 셀 주소) = 셀 주소"가 된다고 보시면 될 듯합니다.
- 2) Indirect 함수 : 다른 시트, 다른 통합문서 참조하기
- Indirect 함수는 현재 시트뿐만 아니라 다른 시트, 다른 통합문서의 셀도 참조가 가능하데 여기에는 규칙이 있습니다.
- ① 다른 시트 참조 : 느낌표 ( " ! " )로 연결
- ② 다른 통합 문서 참조 : 대괄호 ( " [ ] " )로 연결
- ③ "시트 이름", "통합 문서 이름"에 공백이 있는 경우 " 작은따옴표 ( ' ' )로 둘러싸줌
- 셀 주소를 나타내는 방식으로 Indirect 함수에만 적용되는 규칙은 아니고 엑셀에서 적용되는 규칙입니다.
- 엑셀의 셀 주소 표기 방식이 Indirect 함수에도 적용이 된다고 보시는 게 맞을 것 같습니다.
- 여기서 중요한 것은 "시트 이름", "통합 문서 이름"에 공백이 있는 경우 반드시 작은따옴표로 묶어 줘야 합니다.
- 우리는 그냥 항상 참조하는 시트, 통합문서/시트의 "이름"에는 작은따옴표를 묶어서 표시하는 습관을 들이는 것도 방법입니다.
- 다른 통합문서를 Indirect 함수로 참조하는 경우 해당 문서는 열려 있어야 하고 닫힌 상태에서는 참조 오류가 발생이 됩니다.
- 닫힌 상태에서도 참조를 하고 싶다면 Indirect 함수가 아닌 직접 참조하는 방식으로 해야 합니다.
- 3) Indirect 함수 : 참조 셀 주소를 쉽게 Copy 하는 방법
- Indirect 함수에서 ref_text 인수는 오타 없이 정확하게 입력해야 하는데 직접 키보드로 입력하는 것은 쉽지가 않습니다.
- 그래서 보통은 빈 셀에 등호( "=")를 입력하고 참조하고자 하는 셀을 찾아서 마우스로 클릭하게 되면
- 다른 시트인 경우 시트 이름을 포함해서, 다른 파일인 경우 파일 이름을 포함해서 전체 풀네임의 참조 셀 주소를 엑셀이 만들어 줍니다. ^^
- 우리는 그렇게 만든 주소를 복사해서 필요하면 조금 수정해서 사용하는 것이 가장 편하고 정확하게 참조 셀의 주소를 입력하는 방법인 것 같습니다.
4. Indirect 함수 활용해 보자 - Address 함수와 콤보로 사용
- 행, 열 번호(숫자)로 셀 주소를 만들어 주는 Address 함수라는 게 있습니다.
- Address 함수로 주소를 만들고 이를 다시 Indirect 함수로 해당 셀 참조를 할 수 있는 콤보처럼 활용이 가능한 함수입니다.
- 정말 특수한 경우가 아니면 사용할 일이 없을 듯한데 참조하시기 바랍니다.
5. Indirect 함수 활용해 보자 - 이중 목록 상자 만들기 (유효성 검사)
- 초보분들에게는 조금 어려운 내용이 될 수 있을 듯한데 이렇게도 사용을 한다고 참조하시면 좋을 듯합니다.
- Indirect 함수의 대표적인 활용 사례이고 Indirect 함수의 존재의 이유라고 볼 수도 있는 유명한 적용 예제입니다. ^^
- 일단 "유효성 검사"라는 기능을 아셔야 하고 "이름 정의"또한 사용이 가능해야 적용이 가능한 예제입니다.
- 경험이 없으신 분들은 이런 것도 엑셀에서 된다고 봐주시고 나중에 필요하시면 다시 찾아보시기 바랍니다.
- 설명도 간단한 원리만으로 자세히 하지는 않도록 하겠습니다. 자세히 하기에는 분량이 너무 많아서 부담이 될 듯합니다.
- 보통 가계부 작성에서 유효성 검사를 하는 경우에 대분류를 선택하면 거기에 해당하는 소분류만을 보여주는 연동된 기능이 필요한 경우가 있습니다.
- 대분류 "지출"을 선택한 경우 "식비/교통비/…"가 선택이 가능하고 "수입"을 선택한 경우 "월급/이자소득/…"등이 선택 가능하도록
- 대분류의 선택에 따라서 소분류의 선택 가능한 목록(유효성 검사 항목)이 달라지게 만드는 방법입니다.
- 1) 목록 만들기 : 대분류, 소분류의 목록을 먼저 만들어 줍니다.
- 2) 이름 정의 : 소분류는 대분류의 값을 기준으로 "이름 정의"를 합니다.
- 3) 대분류의 유효성 검사 설정 : 대분류의 값으로 목록 형식의 유효성 검사를 설정해 줍니다.
- 4) 소분류의 유효성 검사 설정 : 소분류의 유효성 검사는 Indirect 함수를 사용하여 대분류의 선택값을 기준으로 연동이 되도록 유효성 검사를 설정해 줍니다.
- 이렇게 "대분류 값"으로 이름정의를 하고 Indirect 함수로 선택된 대분류의 이름에 해당하는 소분류 목록을 이름정의를 해서 찾아오는 방식입니다.
- 1) 목록 만들기 : 대분류, 소분류의 목록을 먼저 만들어 줍니다.
- 2) 이름 정의 : 소분류는 대분류의 값을 기준으로 "이름 정의"을 합니다.
- 목록("D7:E11")을 선택하여 "수식" > "정의된 이름" > "선택 영역에서 만들기"메뉴를 선택하고 "첫 행" ("수입", "지출")으로 이름을 만들어 줍니다.
- 아래와 같이 "수식" > "이름 관리자"에서 "수입", "지출"이라는 이름이 정의된 것을 확인할 수 있습니다.
- "D8:D11"의 범위는 "수입"이라는 이름으로, "E8:E11"의 범위는 "지출"이라는 이름으로 정의가 되었습니다.
- 3) 대분류의 유효성 검사 설정
- 대분류를 입력해야 하는 셀("B3")에 대분류 값에 대한 "유효성 검사"를 설정합니다.
- 4) 소분류의 유효성 검사 설정 :
- 이중 목록에서 가장 중요한 부분입니다.
- 대분류의 항목("수입, 지출")을 사용자가 선택하면 거기에 맞는 소분류 항목이 연동되어서 나와야 합니다.
- 그래서 우리는 "수입(D8:D11)", "지출(E8:E11)" 각각에 대해서 "이름 정의"를 해주었습니다.
- 대분류가 선택이 되면 선택된 텍스트를 기준으로 해당 "이름 정의"된 목록을 불러올 수 있도록 Indirect 함수를 아래와 같이 사용해 줍니다.
- "=INDIRECT(B3)"가 되고 만약 사용자가 대분류로 "수입"을 선택하게 되면 "=INDIRECT("수입")"이 되고
- "수입"으로 이름 정의된 "D8:D11"까지의 "수입"에 해당하는 항목(월금, 이자소득, 기타)을 불러오게 됩니다.
6. Indirect 함수 활용해 보자 - 이미지 불러오기
- 오늘은 간단하게 목록에서 선택된 이름으로 해당하는 사원의 이미지를 불러오는 방식을 설명드리겠습니다.
- 마찬가지로 이렇게도 사용이 가능하다 정도로 이해해 주시기 바랍니다.
- 1) 사원 목록 및 이미지 목록 데이터 정리
- 2) 사원 목록으로 "유효성 검사" 만들기
- 3) 사원에 해당하는 이미지의 셀 주소를 "이름 정의하기"
- 4) 사원 선택 및 이미지 표시하기
- 1) 사원 목록 및 이미지 목록 데이터 정리
- 사원 이름과 이미지가 각각 한 셀에 들어갈 수 있도록 아래와 같이 정리를 해줍니다.
- 2) 사원 목록으로 "유효성 검사" 만들기
- 조회할 사원명이 입력된 셀에 대하여 사원 목록으로 유효성 검사를 설정합니다.
- 3) 사원에 해당하는 이미지의 셀 주소를 "이름 정의하기"
- 오늘은 간단하게 그냥 사원명에 해당하는 이미지가 있는 셀들에 대해서 "이름 정의"를 "사원명"으로 하도록 하겠습니다.
- 그러면 사원 이미지가 있는 셀 주소에 이름이 정의가 되고 C5 ("김사과"), C6("박오렌지"), C7("전포도")로 각각 이름정의가 되게 됩니다.
- "이름 정의"가 아닌 선택된 사원의 이름으로 해당 이미지를 찾아오는 방식도 있습니다. 근데 좀 복잡합니다. ㅠㅠ
- 4) 사원 선택 및 이미지 표시하기
- 사원명 입력을 위해 C2 셀에 유효성 검사를 설정합니다. "B5:B7"의 "김사과, 박오렌지, 전포도"을 목록으로 만드시면 됩니다.
- 그리고 아래와 같이 "이름 정의"로 "=Indirect("사원명")이 되도록 C2 셀을 참조로 Indirect 함수를 적용해서 "사진"이란 이름으로 하나 만들어 줍니다.
- 다음은 C3 셀에 임의의 이미지 하나를 복사해서 준비합니다. 준비한 임의의 이미지에 이름을 연동하여 선택된 사원의 이미지를 연동해서 불러올 수 있게 작업을 할 예정입니다.
- 준비된 이미지를 선택하고 "수식 입력줄"에 "=사진"이라고 키보드로 직접 입력합니다.
- 전체적인 원리는 "사원명"을 유효성 검사로 목록에서 선택을 하게 되면
- 선택된 사원명이 텍스트로 Indirect 함수의 인수로 사용이 되어 실제 이미지가 있는 셀 주소를 참조하게 됩니다.
- 이렇게 선택된 사원명을 기반으로 항상 "사진"이라는 "이름 정의"는 해당하는 이미지의 주소를 참조하여 이미지를 보여줄 수 있게 됩니다.
- 오늘은 잘 사용하지 않지만 엑셀의 퀴즈 같은 문제들을 풀어내는 Indirect 함수의 대표적인 사용 예제 2가지를 간단하게 설명을 해보았습니다.
- 많이 어려운 문제입니다. 오늘 이해가 되지 않아도 상관없습니다.
- 엑셀이 할 수 있는 일에 이런 게 있다는 정도로 이해하시고 참조하시는 걸로 충분합니다.
- Indirect 함수는 텍스트를 입력 하면 "셀 참조 주소"를 만들어 준다 정도만 이해를 하시는 걸로 충분할 것으로 생각이 됩니다.
- 간단하게 Indirect("A1") ⇒ A1을 만들어 주는 함수입니다. ^^
- 고생하셨습니다.
** 핵심 요약 : Indirect 함수 사용법 - 텍스트를 조합해서 셀, 시트, 통합문서 주소 참조하기
1. 함수 설명
- Indirect 함수는"셀 주소를 텍스트 형식으로 입력"을 해도 셀 주소로 변경하여 찾아 주는 함수이다.
- Indirect 함수를 사용하는 이유는 "텍스트" 형식의 셀 주소를 사용할 수 있기 때문이다.
- "텍스트"는 수식으로 만들 수 있고 다양한 셀 참조 주소를 사용하여 엑셀 자동화가 가능하다.
2. Indirect 함수의 구문 / 사용방법
- INDIRECT(ref_text, [a1])
- Ref_text 셀에 대한 참조이거나 셀에 대한 텍스트 문자열 참조입니다.
- a1 선택 요소입니다. ref_text가 있는 셀의 참조 영역의 유형을 정하는 논리값입니다.
- 첫 번째 인수 "Ref_text"는 "텍스트" 형식의 셀 주소이다.
- 텍스트이기 때문에 ① 직접 주소를 입력하는 경우는 큰 따옴표( " " )를 사용해줘야 하고
- ② 셀 주소를 참조하는 경우 참조하는 셀 주소의 값이 "텍스트 형식의 셀 주소"이어야 한다.
- 두 번째 인수 "[a1]"는 셀 주소의 타입을 지정하는 옵션인데 보통 생략(True)하면 우리가 사용하는 A1 스타일이 기본 옵션으로 적용된다.
3. Indirect 함수 사용해 보기
- 1) 셀 주소 참조와 Indirect 함수의 차이
- 셀 주소 참조 방식 ( = B2 ), Indirect 함수 ( = Indirect ("B2") )로 사용한다.
- Indirect 함수의 인수는 텍스트이고 반드시 큰 따옴표 ( " " )를 사용해 주자.
- 2) Indirect 함수 : 다른 시트, 다른 통합문서 참조하기
- ① 다른 시트 참조 : 느낌표 ( " ! " )로 연결
- ② 다른 통합 문서 참조 : 대괄호 ( " [ ] " )로 연결
- ③ "시트 이름", "통합 문서 이름"에 공백이 있는 경우 " 작은따옴표 ( ' ' )로 둘러싸준다.
- 3) Indirect 함수 : 참조 셀 주소를 쉽게 Copy 하는 방법
- 빈 셀에 등호("=")늘 입력하고 마우스로 해당 셀을 선택하면 "수식 입력줄"에서 선택한 셀의 전체 주소 확인이 가능하다.
4. Indirect 함수 활용해 보자 - Address 함수와 콤보로 사용
- 행, 열 번호로 셀 주소를 만들어 주는 Address 함수라는 게 있다.
- Address 함수로 주소를 만들고 이를 다시 Indirect 함수로 해당 셀 참조를 할 수 있는 콤보처럼 사용이 가능하다.
5. Indirect 함수 활용해 보자 - 이중 목록 상자 만들기 (유효성 검사)
- 1) 목록 만들기 : 대분류, 소분류의 목록을 먼저 만들어 준다.
- 2) 이름 정의 : 소분류는 대분류의 값을 기준으로 "이름 정의"를 만든다.
- 3) 대분류의 유효성 검사 설정 : 대분류의 값으로 목록 형식의 유효성 검사를 설정한다.
- 4) 소분류의 유효성 검사 설정 : 소분류의 유효성 검사는 Indirect 함수를 사용하여 대분류의 선택값을 기준으로 유효성 검사를 설정해 준다.
6. Indirect 함수 활용해 보자 - 이미지 불러오기
- 1) 사원 목록 및 사진 목록 데이터 정리
- 2) 사원 목록으로 "유효성 검사" 만들기
- 3) 사원에 해당하는 사진의 셀 주소를 "이름 정의하기"
- 4) 사원 선택 및 사진 표시하기
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글