Transcript 첨부화일 : 배열 (419190 Bytes)

컴퓨터활용능력1급
배열 수식
비호전산교육학원
053-853-5554
http://www.bihocom.com
 문제 이해
 배열 수식을 이용하여 임대인이 ‘김승진’인 데이터의 계약면적의 합계를 계산한다.
 IF 함수를 사용하였을 때와 사용하지 않았을 때의 수식의 변화를 알아보자.
 배열 수식은 Ctrl + Alt + Enter 로 결과를 확인할 수 있다.
1. 조건 작성
 임대인이 ‘김승진’인 데이터에 대해 논
리 수식으로 작성하여 보자.
( D11 = “김승진” )
 임대인 데이터가 ‘김승진’이라면 True,
‘김승진’이 아닌 데이터는 False 결과가
나타난다.
 위 조건을 배열 범위로 작성하여 보면
다음과 같이 된다.
( D11:D29 = “김승진” )
 즉, 각 셀로 비교할 조건을 배열 범위로
조건 수식을 작성한다고 보면 된다.
 배열조건 : ( D11:D29 = “김승진” ) → 조건
↓
조건을 비교할 배열 범위
2. 배열 수식 작성
 논리 결과가 수식에 사용이 될 경우에
는 True는 1로, False는 0으로 계산이 된
다.
( D11 = “김승진” ) * F11
 배열조건에 계약면적을 각각 곱하면 논
리결과가 True이면 계약면적 값이 결과
로 나오지만 False라면 0(-)이 결과로 나
타난다.
 위 수식에서 임대인과 계약면적의 데이
터를 배열 범위로 변환하면 다음 수식
과 같다.
( D11:D29 = “김승진” ) * F11:F29
 조건이 참인 경우의 계약면적의 값을
반환하여 함수 수식에 사용을 한다.
계산할 배열 범위
 수식 : ( D11:D29 = “김승진” ) * F11:F29
3. 여러 조건 입력
 여러 개의 조건을 만족하는 수식이라면 조건별로 배열 조건으로 작성하면 된다.
건물이름
임대인
조건1
조건2
장수빌딩
김승진
False
True
제주빌딩
김승진
True
True
미래빌딩
신동협
False
False
우리빌딩
임꺽정
False
False
제주빌딩
김승진
True
True
장수빌딩
장승업
False
False
AND
OR
1
1
1
 조건1 : 건물이름이 ‘제주빌딩’이다.
( 건물이름 = “제주빌딩” )
 조건2 : 임대인이 ‘김승진’이다.
( 임대인 = “김승진” )
1
1
 조건이 여러 개일 경우 AND, OR 조건을 구분하여야 한다.
 배열의 AND 조건인 경우 두 조건의 곱으로 계산하면 두 조건이 참일 경우에만 1이 나타난다.
AND 수식 : ( 조건1 ) * ( 조건2 )
 OR 조건의 경우는 두 조건의 합으로 계산할 수 있는데 두 조건이 모두 참일 경우 2가 나타난다.
이때, 두 조건이 참일 경우를 빼주면 된다.
OR 수식 : ( 조건1 ) + ( 조건2 ) – ( 조건1 ) * ( 조건2 )
두 조건이 참일 경우에만 1이 나오므로 모두 참일때 빼주게 된다.
다만 두 조건이 모두 참일 경우가 발생되지 않는다면 생략하여도
된다.
4. 합계 계산 배열
 앞에서 작성된 배열 수식에 SUM 함수를 사용하면 다음과 같이 된다.
= SUM ( ( D11:D29 = “김승진” ) * F11:F29 )
IF 함수를 사용하여 조건과 합 배열을
구분하여 계산할 수도 있다.
배열 조건과 합 배열의 곱으로 조건에
만족하는 합을 계산할 수 있다.
 IF 함수를 사용한 배열 수식
 합계를 계산하는 문제일 경우 IF 함수와 함께 사용하여 결과를 반환할 수도 있다. IF 함수를 수식
에 사용하게 되면 배열 조건이 IF 조건 항목에 입력이 되고, 이 조건이 참일 경우 배열 범위 내
결과를 반환, 거짓일 경우 False를 그대로 반환한다. 따라서, IF 조건의 참인 경우에만 배열 범위
내의 합계를 계산할 수 있다.
임대인
계약면적
조건
결과
김승진
398.0
True
398.0
김승진
107.6
True
107.6
신동협
134.0
False
임꺽정
84.5
False
김승진
101.0
True
장승업
101.0
False
101.0
= SUM ( IF ( D11:D29 = “김승진” , F11:F29 ) )
조건이 참일 경우 참 결과를
거짓일 경우 데이터를 제외
5. 평균 계산 배열
 SUM 함수에서 조건이 거짓일 경우 0으로 계산이 되어 합을 계산할 경우 결과에 영향을 미치지
않는다. 하지만 AVERAGE 함수의 경우 IF를 사용하지 않으면 올바른 결과를 구할 수 없다.
 아래 표에서 임대인이 ‘김승진’인 데이터의 계약면적의 평균을 계산하여 보자.
임대인
계약면적
조건
결과
김승진
398.0
True
398.0
김승진
107.6
True
107.6
신동협
134.0
False
임꺽정
84.5
False
김승진
101.0
True
장승업
101.0
False
101.0
= AVERAGE ( ( 임대인 = “김승진” ) * 계약면적 )
 이 수식의 결과는 101.2 가 나온다. 이는 거짓인
경우에 0으로 계산한 결과이다. 즉, 김승진의
계약면적의 합인 607에서 6으로 나눈 결과이다.
IF 함수를 사용하여 계산값에서 제외함
= AVERAGE ( IF ( 임대인 = “김승진” , 계약면적 ) )
 IF 함수를 사용하여 거짓일 경우 결과 값을 제외하여 평균을 계산한다. 평균 결과는
202.3이 나타난다. 김승진의 계약면적의 합인 607에서 개수 3으로 나눈 결과이다. 따라서,
배열 평균을 계산할 경우 IF 함수를 사용하여 조건에 만족하지 않는 데이터를 제외하는
방식의 수식을 작성하여야 올바른 평균이 계산된다.
6. 개수 계산 배열
 COUNT 함수와 배열 수식을 이용하여 조건에 만족하는 건수나 인원수와 같은 개수를 계산할 수
있다. 앞에서 배운 평균을 계산하는 구조와 유사하다.
= COUNT ( IF ( 배열조건 , 숫자 ) ) COUNT 함수는 수치 데이터의 개수를 나타내므로
조건이 만족할 경우 숫자 데이터로 반환하여 수치
데이터의 개수를 반환하는 구조이다.
 SUM 함수를 사용하여 개수 구하기
 SUM 함수 배열 수식을 이용하여도 개수를 계산할 수 있다.
= SUM ( IF ( 배열조건 , 1 )
 COUNT 대신 SUM이 사용되면 IF 참 인수에 반드시 1을 입력하여야 한다. 조건에 만족하는 데이
터를 1를 주어 합을 계산하는 방식이라 생각하면 된다.
 IF를 사용하지 않고 SUM 만으로도 개수를 계산할 수 있다.
조건이 1개일 경우 : = SUM ( ( 배열조건 ) * 1 )
여러 개일 경우 : = SUM ( ( 배열조건1 ) * ( 배열조건2 ) )
배열조건이 여러 개일 경우 곱하거나 더하여 합하면 개수가 계산이 되지만
조건이 1개일 경우에는 1을 곱한 결과를 더하여야 개수가 계산된다.
7. 연습 문제 ( 파일 : 배열수식.xls )
1. 건물번호별 임대건수를 C3:C7에 계산하시오.
 SUM 함수 사용
2. 건물번호별 계약면적의 합계를 D3:D7에 계산하시오.
 SUM, IF 함수 사용
3. 건물번호별 임대인이 김승진인 계약면적의 합계를 E3:E7에 계산하시오.
 SUM 함수 사용
4. 건물번호별 월임대료의 평균을 F3:F7에 계산하시오.
 AVERAGE, IF 함수 사용
5. 건물번호별 최대 임대가격을 G3:G7에 계산하시오.
 MAX 함수 사용