컴활1급 배열수식 (average로 배열함수 이해하기)

 

 

 

 

 

 

컴활 1급 배열 수식

 

 

---

배열 함수 들어가기 전 절대 참조/ 혼합 참조 개념 이해하기 

 

 

 

엑셀에서 $ 달러 표시는 열 또는 행을 완전히 고정한다는 것을 의미합니다. 

 

절대참조

• $A$1 : 행과 열 모두를 고정한다는 의미

혼합참조

• A$1 : 행방향으로 고정 (즉, 가로로 이동은 OK)
• $A1 : 열 방향으로 고정 (즉 세로로 이동은 OK) 

 

 

배열 수식은 Ctrl+shift+enter

혼합 참조와 절대 참조는 f4 키를 누르면 됩니다. 

 

 

---

 

배열 수식 기본 공식

 

배열 수식의 기본적인 형태를 알아야 배열수식 문제를 풀 수 있는데요. 아래의 표를 확인해보세요. 

 

IF 가 있을 때	조건이 하나일 때	=계산함수(IF(조건,계산범위))
	조건이 두개일 때	=계산함수(IF((조건1)*(조건2),계산범위))
IF 가 없을 때	조건이 하나일때	=계산범위((조건)*(계산범위))
	조건이 두개일 때	=계산범위((조건1)*(조건2)*(계산범위))

 

보통 컴활 1급에서는 조건을 and로 연결하는 문제가 주로 나옵니다. 

 

조건 연결하는 방법

조건이 and 일 때 (조건 1)*(조건 2)

조건이 or 일떄 (조건 1)+(조건 2)

 

 

 

---

 

기출문제로 이해하기

 

아래의 문제는 대한상공회의소에서 공개한 시험문제입니다. 하단에 링크 첨부해 놓겠습니다. 직접 풀어보세요!

 

문제
-표 1의 가입나이, 코드, 가입기간을 이용하여 코드별 나이별 평균 가입기간을 표 5 영역에 계산하시오. 
:단 오류 발생 시 공백으로 표시
:average, if, iferror 함수를 이용한 배열 수식

 

 

 

이 데이터를 채워야 하는 겁니다.  배열 함수를 이용하기 전 다음에 질문에 답을 해봐야 합니다. 

 

1. 조건이 무엇인가? 

2. 계산 범위는 무엇인가? 

 

 

문제에서 힌트를 얻으시면 됩니다. 코드별, 나이별이라고 했으니 이게 조건이 될 거고 평균 가입기간을 구하라고 했으니 가입기간이 계산 범위가 될 겁니다. 이렇게 정리를 했다면 이제 아래에 표 중 어떤 상황에 해당하는지를 판단해야 합니다. 

 

 

IF 가 있을 때	조건이 하나일 때	=계산함수(IF(조건,계산범위))
	조건이 두개일 때	=계산함수(IF((조건1)*(조건2),계산범위))
IF 가 없을 때	조건이 하나일때	=계산범위((조건)*(계산범위))
	조건이 두개일 때	=계산범위((조건1)*(조건2)*(계산범위))

 

 

 

 

두 개의 데이터를 보고 조건을 만들어봅시다. 구분을 위해 색을 칠했으니 색으로 봐주시면 됩니다. 

 

• 전체 코드번호 = 코드 

 

•  가입나이>=나이

 

•  가입나이 <나이

 

• 계산 범위는가입기간이 되겠네요. 

 

 

그렇다면 이제  절대 참조와 혼합 참조를 어떻게 해줘야 하는지가 관건이겠네요. 이걸 쉽게 생각하는 방법은 셀이 어느 방향으로 움직이는지를 보는 건데요. 이건 실제 엑셀에서 보는 게 훨씬 쉬워요.

 

 

 

 

코드 

코드는 BM-SM-BW-SW로 아래로 이동해야 합니다. 그럼 O열은 고정이 되어야겠죠? 그럼 $O22가 되야겠네요. 

 

나이나이

그럼 반대로 나이는 0세 이상-20세 이상-30세 이상-40세 이상으로 이동해야 합니다. 그럼 P열은 고정되면 안 되겠죠? 

P$20이 되겠네요.  20세 미만-30세 미만-40세 미만-60세 미만 이것도 마찬가지로 P열이 고정되면 안 되겠죠? 

 

전체 코드번호 가입나이가입기간

얘네는 무조건 절대 참조로 만들어줍니다. 행과 열 모두 못 움직이게 고정해주세요!

 

---

 

 

배열 함수를 만들어보면 이렇게 되겠네요.

 

 

=average(if(( 전체 코드번호=코드)*(가입나이범위>=나이)*(가입나이범위 <나이), 가입기간범위))

 

 전체 코드번호: 절대참조(전체고정)

코드: 혼합참조 (알파벳 고정)

가입나이범위: 절대참조(전체고정)

나이: 혼합참조 (숫자고정)

나이: 혼합참조(숫자고정)

가입기간범위: 절대참조(전체고정) 

 

 

iferror 함수는 (value, value_if_error) 로 value 값에 오류가 생기면 value_if_error로 대체하겠다고 알아두시면 좋을 거 같습니다. 에러가 발생했을 때 공백으로 나오라고 했으니까 답은

 

=iferror(average(if(( 전체 코드번호=코드)*(가입나이범위>=나이)*(가입나이범위 <나이), 가입기간범위)),"")

 

ctrl+shift+enter 눌러주면 배열 수식 완성

 

=IFERROR(AVERAGE(IF(($C$4:$C$39=$O22)*($B$4:$B$39>=P$20)*($B$4:$B$39<P$21),$F$4:$F$39)),"")

 

 

 

 

 

https://license.korcham.net/co/examguide.do?mm=21&cd=0103 

종목소개 시험안내