조견표에서 일치하는 값 찾기(1) - SumIfs, Sum(if 배열 수식

아래와 같은 조견표에서 계절별, 부하별, 요금 종류별 세 가지 조건에 맞는 값을 찾을 경우 indirect함수를 사용할 수 있습니다.

indirect(name).xlsx

0.01MB

 

 

 

1. Sumifs함수를 이용하는 경우

sumifs함수를 사용하려면 아래와 같이 검색 조건과 요금이 같은 줄에 있도록 표가 변경되어야 합니다. 이와 같이 생긴 것이 데이터베이스입니다. 부하별, 요금별, 계절별, 요금 등 필드가 있고, 필드별 데이터가 모여 1줄 레코드가 되고, 그러한 레코드들이 모여 데이터베이스가 됩니다.

 

중간부하, 선택요금, 봄,가을철 등 여러 가지 조건을 만족시키는 요금을 Sumifs함수를 이용해 구해보면

=SUMIFS(D19:D36,A19:A36,A39,B19:B36,B39,C19:C36,C39) 이 됩니다.

 

Sumifs 함수의 구문은 아래와 같으며,

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

먼저 sum_range 더할 범위를 입력하고, 그다음으로는 criteria_range1 조건 범위 1, criteria1 조건1 식으로 여러 가지 조건 범위와 조건을 연속적으로 입력하는 것입니다.

 

따라서 아래와 같이 Sumif 함수의 구문이 

SUMIF(range, criteria, [sum_range])

조건 범위와 조건이 먼저 나오고 더할 범위가 맨 마지막에 나오는 것과 구조가 다릅니다.

 

따라서 위 수식

=SUMIFS(D19:D36,A19:A36,A39,B19:B36,B39,C19:C36,C39) 을 살펴보면

더할 범위는 요금이 있는 필드 D19에서 D36이고, 

조건은 ① 부하별 필드의 데이터 A19에서 A36중에서 A19셀 중간부하와 일치하는 레코드,

② 그다음은 요금별에 해당하는 데이터 B19에서 B36중에서 B19셀 선택요금과 일치하는 레코드,

③ 그다음은 계절별에 해당하는 데이터 C19에서 C36중에서 C19셀 선택요금과 일치하는 레코드이므로,

이들 세 가지 조건을 모두 만족하는 요금의 합계를 구하는 것입니다.

 

조건을 "중간부하", "선택요금", "봄,가을철"이라고 입력해도 되지만 가변성 있게 A19셀부터 C19셀에 값을 입력하고 그 셀을 지정한 것입니다.

 

2. 배열수식 이용하는 경우

배열 수식을 이용할 경우 수식은

=SUM(IF(($A$3:$A$8=C$11)*($B$3:$B$8=A13)*($C$2:$E$2=B13),$C$3:$E$8))가 되는데,

CSE(Ctrl + Shift + Enter) 키를 눌러 수식을 입력하면(Microsoft 365 버전에는 엔터키만 눌러도 됨)

수식 좌우에 중괄호({}) 표시가 있고, 값이 0으로 구해집니다.

 

수식

=SUM(IF(($A$3:$A$8=C$11)*($B$3:$B$8=A13)*($C$2:$E$2=B13),$C$3:$E$8))의 내용을 살펴보면

if조건을 만족하는 C3:E8의 값의 합계(Sum)를 구하는 것인데,

조건은 And 조건이므로 *로 연결했고, 각각의 조건식은 괄호 안에 조건범위= 조건식으로 입력한 것입니다.

 

$A$3:$A$8=C$11는 부하가 중간부하인 경우이고,

$B$3:$B$8=A13는 요금이 선택요금인 경우이고,

$C$2:$E$2=B13는 계절이 봄,가을철인 경우입니다.

$가 앞뒤로 모두 붙어 있는 경우는 다른 셀로 복사하더라도 행과 열이 모두 변경되면 안 되는 것이고,

$가 C$11과 같이 되어 뒤에만 붙어 있는 경우는 다른 셀에 복사할 경우에도 11행은 변하면 안 되고, C열은 변경 가능하도록 하는 것이며,

A13등과 같이 $가 하나도 없는 경우는 다른 셀과의 상대적인 위치에 따라 행과 열이 변경되도록 하는 것입니다.

 

그런데 값이 0으로 나온 것은 중간부하이면서 선택요금이고, 봄,가을철인 것은 D6셀인데, A6셀이 비어있기 때문입니다.

 

따라서 위 조견표를 아래와 같이 비어 있는 경부하 등 칸을 채워야 합니다.

 

그러면 아래와 같이 값 45가 구해집니다.

 

indirect(name)(final)1.xlsx

0.01MB