두 가지이상 조건을 만족하는 값을 구해주는 ~Ifs 함수

1. 정의

두 가지이상 조건을 만족하는 최대값, 최소값, 평균, 합계, 개수를 구해주는 함수로는 MaxIfs, MinIfs, Averageifs, SumIfs, CountIfs 함수가 있습니다. 그렇지만 한 가지 조건만 만족하는 경우에도 사용가능하기  때문에 CountIf, MaxIf 등 ~If 함수 들은 잊어도 됩니다.

 

2.구문

CountIfs함수만 조건 범위1, 조건1, 조건 범위2, 조건2... 형식으로 입력하고,

나머지 함수 들은 모두 최대값 등 값을 구할 범위, 조건 범위 1, 조건1, 조건 범위 2, 조건2... 식으로 최대값 등을 구할 범위를 맨 앞에 지정해야 합니다.

 

3. 적용

아래 자료를 대상으로 함수를 적용해보겠습니다.

분양대금 납부현황2.xlsx

0.01MB

 

 

 

 

가. 동호수가 1-101호이고, 구분이 1차 중도금인 약정금액의 최대값, 개수 구하기

 

(1) 동호수가 1-101호이고, 구분이 1차 중도금인 약정금액의 개수 구하기

=COUNTIFS(A4:A16,A4,C4:C16,C5)

입니다.

 

동호수가 있는 범위를 지정하고, 조건인 1-101호는 A4셀을 지정했고,

구분이 있는 범위를 지정하고, 조건인 1차 중도금은 C5 셀을 지정했습니다.

 

(2) 동호수가 1-101호이고, 구분이 1차 중도금인 약정금액의 최댓값 구하기

최대값을 구할 범위가 E4:E16이고, 조건은 동호수가 1-101이고, 구분이 1차 중도금이므로 CountIfs함수를 사용할 때와 같습니다.

따라서, =MAXIFS(E4:E16,A4:A16,A4,C4:C16,C5)가 됩니다.

 

(3) 표 형식으로 만들기

동호수별, 구분별 개수 또는 약정금액의 최대값을 구하는 것을 해보는데, 계약금은 한개이므로 제외하겠습니다.

 

수식을 지정할 때 셀 주소중 변하지 않아야 할 것(행 또는 열)에는 $표시를 붙이고, 위치에 따라 변해야 할 것(행 또는 열)에는 $표시를 안붙이는 것이 중요합니다.

 

(가) CountIfs 함수

J3셀의 CountIfs함수의 경우 =COUNTIFS($A$4:$A$16,$I3,$C$4:$C$16,J$2)로서

 

동호수가 있는 범위와 구분이 있는 범위는 고정이므로 앞뒤에 $표시를 모두 붙여서 절대참조형식으로 지정하며,

 

동호수 조건은 오른쪽으로 수식을 붙여넣을 때에도 i열이 변경되면 안되므로 i에 $를 붙이고, 3은 내려갈 때 4로 바뀌어야 하므로 $ 표시를  안붙여서 $i3식으로 혼합참조 형식으로 입력하고,

 

1차중도금인 j2셀은 오른쪽으로 붙여넣을 때 K, L로 변경돼야 하므로 $표시를 붙이지 않고, 내려가더라도 2행은 고정되어야 하므로 $표시를 붙여서 j$2식으로 혼합참조 형식으로 지정해야 합니다.

 

그러면 숫자 2가 구해집니다.

 

j3셀의 채우기 핸들을 오른쪽으로 끌고, 아래쪽으로 끌면 6개 셀에 모두 수식이 채워지고 값이 정확하게 구해집니다.

 

(나) MaxIfs 함수

동호수, 구분별로 약정금액의 최대값을 구해보겠습니다.

조건범위와 조건은 CountIfs와 동일하므로 수식 입력줄에서 CountIfs의 조건을 복사한 후

=maxifs(약정금액의 범위, countifs의 조건 붙여넣기) 식으로 입력하면 됩니다.

 

따라서, 수식은 =MAXIFS($E$4:$E$16,$A$4:$A$16,$I3,$C$4:$C$16,J$2)가 됩니다.

CountIfs의 조건을 복사하다 보니 i7셀과 j6셀이어야 하는데, i3셀과 j2셀 기준으로 되었지만 결과에 영향은 없습니다.

 

2차 중도금과 잔금이 #######로 표시되는데 숫자에 비해 열 너비가 좁기 때문입니다.

 

K열을 클릭하고 L열까지 끌어서 K열과 L열을 선택한 다음 K열과 L열 사이의 경계선을 더블 클릭하면 열 너비가 숫자의 길이에 맞게 자동 조절됩니다.

 

(다) MinIfs, AverageIfs, SumIfs 함수

MinIfs함수를 적용할 때는 j7셀에 MaxIfs 함수가 있으므로, 수식 입력줄에 커서를 넣고 전체 수식을 복사한 후 J10셀에 붙여넣고, Max를 Min으로 수정하기만 하면 됩니다.

 

수식입력줄의 수식을 복사하지 않고, J8셀을 복사해서 J11셀에 붙여넣으면

 

값이 99,900,000이 나오는데, 이것은 동호수 조건이 $i8이어야 하는데, $i7로 돼서 그렇습니다.

 

수식 입력줄에 커서를 넣으면 쉽게 잘못된 것을 알 수 있습니다.

 

AverageIfs함수는 J7셀의 수식 입력줄을 복사한 후 J12셀에 붙여넣고 max를 avearge로 바꾼 다음,

J12셀의 채우기 핸들을 J13셀까지 끌면 됩니다.

 

그러면 최대값, 최소값, 평균이 동일한 것을 알 수 있습니다. 1차 중도금이 동일하기 때문입니다.

 

마찬가지로 SumIfs함수를 J14, J15셀에 적용하면 합계이기 때문에 1차중도금이 2배로 됐습니다. 

 

 

(4) 조건이 6개인 경우

동호수 조건은 i18,i19셀에 있고, 1차중도금 등 구분의 조건은 J17에서 L17셀까지 있고, 약정일과 납부일의 조건은 J16셀과 J17셀에 있으므로 6개의 조건을 만족하는 납부금액의 합계를 구해보겠습니다.

 

동호수와 1자중도금등은 위치에 따라 하나만 고정인 혼합참조형식으로 입력하는데, 날짜는 시작일과 종료일 그리고 더할 범위는 고정돼야 하므로 절대참조형식으로 입력해야 합니다.

 

J18셀의 수식은

=SUMIFS($G$4:$G$16,$A$4:$A$16,$I3,$C$4:$C$16,J$2,$D$4:$D$16,">="&$J$16,$D$4:$D$16,"<="&$K$16,$F$4:$F$16,">="&$J$16,$F$4:$F$16,"<="&$K$16)

로서 복잡하기는 하지만 하나씩 따져보면

$G$4:$G$16는 더할 범위인 납부금액의 범위로서 절대참조형식이고,

 

$A$4:$A$16는 조건 범위1, 다시 말해 동 호수를 비교할 범위로서 절대참조형식으로 되어 있으며,

 

$I3는 조건1, 다시 말해 1-101로서 오른쪽으로 이동하거나 아래로 내려갈 때 동호수가 자동으로 변하도록 혼합참조형식을 사용했습니다.

 

$C$4:$C$16,J$2는 조건 범위2와 조건2로 구분이 1차 중도금인 경우를 말하는데, 오른쪽으로 이동할 때는 열이 자동으로 바뀌고, 내려갈 때는 행이 바뀌면 안되므로 열에는 $표시가 없고, 행에만 $표시가 있습니다.

 

$D$4:$D$16,">="&$J$16는 약정일이 $J$16셀의 값 2023-01-01이상인 경우를 말하는데, ">=2023-01-01"로 써도 되지만 J16셀의 값과 연결하기 위해 &연산자를 사용한 것입니다.

 

$D$4:$D$16,"<="&$K$16은 약정일이 $K$16셀의 값 2023-12-31이하인 경우를 말하는데 & 결합연산자를 이용해 문자 "<="과 값 $K$16을 결합한 것입니다.

 

 

$F$4:$F$16,">="&$J$16,$F$4:$F$16,"<="&$K$16은 약정일 대신 납부일을 비교하는 것입니다. 조건이 변하면 안되므로 절대참조형식으로 입력했습니다.

 

이제 J18셀의 수식을 오른쪽으로 끌고, 다시 아래로 끌면 아래와 같이 되는데,

3-101호의 1차중도금은 두번째 납부일이 2024년이기 때문에 첫번째 납부금액인 100,000,000으로 정확하게 표시됐고,

2차중도금은 납부일이 2023년인 것은 있지만 약정일이 2024년으로서 해당하는 값이 없어 0으로 표시되었습니다.

분양대금 납부현황2(완성).xlsx

0.01MB