indirect함수를 사용하면 편리한 경우로 계산하고자 하는 셀 주소가 변경되는 경우입니다.
아래 표는 구간별 명수만이 있어서 몇 개 구간에 대한 명수의 합계를 구할 경우 시작셀과 종료셀의 주소가 매번 달라지게 됩니다.
위와 같은 표로는 Match함수를 사용할 수 없으므로 아래와 같이 초과, 이하 수치 모두 기록하지 않고, 초과 수치만 지정해야 합니다.
그러면 0은 0 초과 60 이하가 되고, 60은 60 초과 70 이하가 됩니다.
1. Match함수를 이용하는 경우
가. 데이터 유효성 검사로 입력값 제한하기
D10셀과 E10셀에서 입력을 받는데, 중간값 예를 들어 65 등은 입력하면 안 되므로 데이터 유효성 검사를 이용해 목록으로 값을 제한합니다.
① D10셀부터 E10셀까지 마우스로 끌어서 범위를 선택합니다.
② Data(데이터) > Data Validation(데이터 유효성 검사)를 누르고,
③ Allow(제한 대상)로는 List(목록)을 선택하고, Source(원본)는 A10셀에서 A15셀까지 마우스로 끌어서 선택한 다음 OK(확인) 버튼을 누릅니다.
나. 일치하는 값 찾기
① D11셀에는 D10셀에 해당하는 값을 찾기 위해 Match함수를 이용합니다.
Match함수의 구문은 MATCH(lookup_value(찾을 값), lookup_array(찾을 범위), [match_type(일치 형식])인데,
match_type은 1,0 또는 -1이며 기본값은 1로서 생략 가능합니다.
| match_type | 동작 |
| 1 또는 생략 | MATCH는 lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. lookup_array 인수 값은 오름차순(...-2, -1, 0, 1, 2,...A-Z, FALSE, TRUE)으로 배치해야 합니다. |
| 0 | MATCH는 lookup_value와 같은 첫째 값을 찾습니다. lookup_array 인수 값은 임의의 순서로 지정해도 됩니다. |
| -1 | MATCH는 에서 lookup_value보다 크거나 같은 값 중에서 가장 작은 값을 찾습니다. lookup_value 인수 값은 내림차순으로 배치해야 합니다(예: TRUE, FALSE, Z-A, ... 2, 1, 0, -1, -2, ...등). |
그런데, 60점이면 0 초과 60 이하인 10행이 선택되어야 하므로 Match함수의 결괏값에서 1을 빼야 합니다.
따라서 수식은 =MATCH(D10,$A$10:$A$15,1)-1이 됩니다.
위 수식에서 match_type은 데이터가 오름차순으로 정렬되어 있고 정확한 값을 찾기 때문에 1 또는 0이 모두 가능하지만, -1은 내림차순으로 정렬되어 있어야 하므로 #N/A 에러가 발생합니다.
합계를 구하려면 셀 주소를 지정하는데, 70점은 11행이 되어야 하므로 9를 더하는데 -1이 있으므로 8만 더하면 됩니다.
따라서, 수식은 =MATCH(D10,$A$10:$A$15,1)+8이 됩니다.
② D11셀의 채우기 핸들을 E11셀까지 끌어 수식을 채웁니다. 당초 4였는데, 8을 더해서 12가 됐습니다.
다. 누계 값 구하기 1
① Sum함수의 인수인 셀 주소를 문자열로 입력하면 #VALUE! 에러 발생
B열의 11행과 12행의 값을 더해야 하므로
&연산자를 이용해
=SUM("B"&D11&":"&"B"&E11)라고 입력하고 엔터키를 누르면
#VALUE! 에러가 발생합니다.
이것은 =SUM(B11:B12)가 돼야 하는데, 마우스로 끌어서 "B"&D11&":"&"B"&E11를 선택한 후 F9키를 누르면
"B11:B12"로 셀 주소 양쪽에 큰따옴표가 있어서 문자열이기 때문입니다. Esc키를 눌러 원래 수식으로 돌아옵니다.
② 해결책 - Indirect 함수 사용
이때 indirect함수를 사용해 "B"&D11&":"&"B"&E11를 묶고 엔터키를 누르면
32라는 값이 반환됩니다.
만약 D11과 E11값을 구하는 것까지 수식에 넣으면
D11은 MATCH(D10,$A$10:$A$15)이고, E11은 MATCH(E10,$A$10:$A$15)이 되므로
=SUM(INDIRECT("B"&MATCH(D10,$A$10:$A$15)&":"&"B"&MATCH(E10,$A$10:$A$15)))이 됩니다.
2. Vlookup함수를 이용하는 경우
Vlookup함수는 행 번호를 반환할 수 없으므로 C열에 행 번호를 추가해야 합니다.
Match함수 대신 Vlookup함수로 행수를 구하려면
=VLOOKUP(D10,$A$10:$C$15,3,0)인데, 마찬가지로 70점이라면 11행이 되어야 하므로 -1을 해야 합니다.
D14셀의 채우기 핸들을 E14셀로 끌어 E14셀에 수식을 채웁니다.
그리고, 누계를 구하는 수식은 D12셀의 수식과 같으므로 D12셀을 복사한 후 D15셀에 붙여 넣습니다.
그러면 누계 32가 동일하게 구해집니다.
3. 시작점수와 종료 점수를 바꿔서 검증
먼저 시작 점수를 60으로 바꾸면 D11셀과 D14셀의 값이 10으로 바뀌고,
누계 값도 16 + 20 + 12 = 48로 바뀝니다.
종료 점수를 100으로 바꾸면 E11셀과 E14셀의 값이 14로 바뀌고,
누계 값도 16 + 20 + 12 + 14 + 15 = 77로 바뀝니다.
시작 점수와 종료 점수를 100과 80으로 거꾸로 지정해도
12 + 14 + 15 = 41로 맞습니다.
4. 수식 중간 값 알아보기
① F9키로 수식의 결괏값 알아보기
D15셀에서 INDIRECT("B"&D14&":"&"B"&E14)까지 범위를 잡은 후 F9키를 누르면
{12;14;15}로 배열이 반환됩니다. 원래 수식으로 돌리기 위해 Esc키를 누릅니다.
② 수식 계산 명령으로 수식 계산 과정 살펴보기
이번에는 Formulas(수식) - Evaluate Fomula(수식 계산) 버튼을 누른 후 아래와 같이 Evaluate(계산) 버튼을 7번 누르면 INDIRECT("B"&D14&":"&"B"&E14)가 $B$12:$B$14로 바뀌고,
한번 더 Evaluate 버튼을 누르면 {12;14;15} 배열을 보여주지 않고 결괏값 41을 바로 보여줍니다. Close버튼을 눌러 수식 계산을 마칩니다.
이와 같이 문자열로 된 셀 주소를 문자열이 아닌 셀 주소(B12:B14)로 바꾸거나, 셀 주소가 참조하는 값({12;14;15})을 반환하는 것이 Indirect함수입니다.
'Excel' 카테고리의 다른 글
| 조견표에서 일치하는 값 찾기(1) - SumIfs, Sum(if 배열 수식 (0) | 2023.03.31 |
|---|---|
| @ 연산자 - 동적 배열을 단일 값으로 변환 (0) | 2023.03.30 |
| indirect 함수(1) - 참조 셀의 값을 반환하는 함수 (0) | 2023.03.27 |
| Lambda 함수를 이용한 일정 간격의 값 합계 구하기 (0) | 2023.03.23 |
| Let 함수 - 변수에 값 할당 및 수식 계산 (0) | 2023.03.22 |