조견표에서 일치하는 값 찾기(2) - 이름 관리자, Offset, Match, Indirect 함수

indirect(name)(final)1.xlsx

0.01MB

 

 

3. 이름 관리자를 이용하는 경우

C3셀부터 E3셀까지는 계절에 해당하므로
수식 탭의 이름 관리자를 클릭한 다음 새로 만들기 버튼을 누르고,

 
이름에는 '계절', 설명에는 '계절에 해당하는 셀', 참조 대상에는 C2셀부터 마우스로 E2셀까지 끌어 지정합니다.

 
그리고, 확인 버튼을 누르면 아래와 같이 '계절'이란 이름이 생기고 값에는 여름철 등이 표시되고, 참조대상은 아래를 보면 ='Sheet1 (2)'!$C$2:$E$2입니다.

 
또다시 새로 만들기를 한 후 부하는 A3에서 A8셀로 지정하고, 요금구분은 B3셀에서 B8셀로 지정하고, 더할 범위는 C3:E8셀로 지정합니다.
 
그러면 아래와 같이 계절, 부하, 요금, 요금구분 등 네 개의 이름이 정의되었습니다.

 
이제 C13셀의 수식에서 $A$3:$A$8이라는 부분을 마우스로 끌어서 선택한 다음 마우스로 A3셀에서 A8셀까지 끌면 $A$3:$A$8가 '부하'란 이름으로 수정됩니다. 

 
마찬가지로 B3셀에서 B8셀을 클릭하면 요금구분, C2셀에서 E2셀을 클릭하면 범위가 계절, C3셀에서 E8셀까지 선택하면 요금이란 이름으로 수정됩니다.

 
그리고, CSE키를 누르면 마찬가지로 45가 구해집니다.

 

4. Offset과 Macth 함수를 이용하는 경우

위 조견표는 X와 Y값으로 이뤄진 2차원 좌표로 볼 수 있습니다.
 
Offset함수의 구문은
OFFSET(reference, rows, cols, [height], [width])입니다.
 
reference는 참조 셀이고, rows는 상대적인 행 수로 자신의 위치는 0이 되고, 위로 올라갈 때는 -, 아래로 내려갈 때는 +로 지정합니다.
cols는 상대적인 열 수로 마찬가지로 자신의 위치는 0이고, 왼쪽으로 갈 때는 -, 오른쪽으로 갈 때는 +로 지정합니다.
 
따라서, reference를 C3셀로 지정한 다음 그것과의 상대적인 위치를 rows와 cols로 지정함으로써 원하는 값을 구할 수 있습니다.
 
다시 말해 봄,가을철인 경우는 cols가 1이고, 6행은 rows가 3이 됩니다.
 
중간부하이면서 선택요금인 경우는 중간부하는 rows가 +2이고, 선택요금이면 rows를 다시 +1해야 합니다. 그러나 일반요금인 경우는 같은 행이므로 +1을 할 필요가 없습니다.
 
상대적인 위치를 구할 때는 Match함수를 이용할 수 있습니다. 그런데 Offset함수의 rows와 cols는 0부터 시작하므로 -1을 해야 합니다.
 
Match 함수에 대한 설명은 아래 글을 참고 바랍니다.
https://lsw3210.tistory.com/entry/indirect-%ED%95%A8%EC%88%982-%EB%88%84%EC%A0%81%EA%B0%92-%EA%B3%84%EC%82%B0-%ED%95%98%EA%B8%B0
 
 
위와 같은 내용을 기반으로 수식을 작성하면 아래와 같습니다.
=OFFSET(C$3,MATCH(C$11,부하,0)-1+MATCH(A14,$B$3:$B$4,0)-1,MATCH(B14,계절,0)-1)

 
요금 구분과 일치하는 위치를 찾을 때 Sum(If 배열함수의 경우는 B3셀부터 B8셀에서 일치하는 것을 찾았지만, Offset함수의 경우는 부하 구분과 요금 구분을 각각 구분해서 위치를 더하기 때문에 요금을 찾을 범위를 B3:B8로 하지 않고, B3:B4로 바꿨고,
 
첫 번째 일치하는 부하 구분을 기준으로 위치를 찾기 때문에 A4, A6, A8셀이 비어 있어도 됩니다.

 
그러나, C13셀을 보면 값이 0으로 바뀌었는데, 이유는 A6셀 값이 비어 있기 때문입니다.
 
 

5. Indirect, Offset, Match 함수를 이용하는 경우

indirect 함수를 이용해 경부하와 중간부하, 최대부하를 C3, C5, C7셀을 참고하도록 할 수 있습니다.
 
마찬가지로 이름 관리자를 이용해 경부하는 C3셀, 중간부하는 C5셀, 최대부하는 C7셀로 지정합니다.

 
 
이제 Offset함수를 이용해 찾을 값의 위치를 지정하는데, 선택요금제일 때는 rows에 1을 입력하고, 계절에 맞는 Cols는 3번과 동일합니다.
 
따라서, 수식은 
=OFFSET(INDIRECT(C$11),MATCH(A13,$B$3:$B$4,0)-1,MATCH(B13,계절,0)-1)로서
 
Indirect(C$11)로 경부하 등에 해당하는 위치 C3, C5, C7셀을 찾을 다음
MATCH(A13,$B$3:$B$4,0)-1로 요금 구분이 일반요금이면 0, 선택요금이면 1이므로 한 행 아래 값을 찾고,
MATCH(B13,계절,0)-1은 위와 같습니다.

 
4번과 마찬가지로 A4셀 등에 경부하 등 값이 비어 있어도 값을 찾는데 문제가 없습니다.
 
(결론)
위와 같이 1번 사례부터 3번 사례까지는 부하 구분의 값이 모두 채워있어야 하고,
4번과 5번 사례는 값이 일부 비어 있는 경우도 문제없이 값을 찾을 수 있는 차이점이 있으므로 4번이나 5번과 같이 사용하는 것이 수식은 복잡하지만 좋은 방법입니다.
 
 
완성된 파일은 아래와 같습니다.

indirect(name)(final)2.xlsx

0.01MB