컴활 1급 실기 #6. 배열함수 공식, 문제유형, 기타응용문제, 자주 출제 되는 함수

*배열함수는 아래의 4가지를 알아야 문제 풀 수 있음
1. 조건 (~별)
2. 값구열
3. 함수
4. 찾는값

 

*배열함수 수식 공식

공식1
= Sum()
= Sum(()*())
= Sum(()*()*())
= Sum((조건1:범위=값)*(조건2:범위=값)*(값구열))

공식 2
=Sum(if())
=Sum(if(()*(),))
=Sum(if((조건1:범위=값)*(조건2:범위=값),값구열))

 

1. 조건 1유형과 2유형 구분하는 문제

*머리글로 구분 가능

ㄱ. 조건 1유형
(구하고자하는 표의 머리글이 특정한 열안의 값으로 들어가 있으면 1유형)
- 조건 2개, 비교값에 열고정(알파벳고정) / 행고정(숫자고정)

예) 부서별(총무부교육부개발부) 직책별(부장과장주임사원) 급여의 합계를 구해라
조건 : 부서별, 직책별
함수 : 합계 = SUM
값구열 : 급여

수식 : = Sum((조건1)*(조건2)*(값구열))
= Sum((부서전체절대참조=표부서열고정)*(직책전체절대참조=표직책행고정)*(급여전체절대참조))

ㄴ. 조건 2유형
(머리글 값이 문제의 표와 동일하면 2유형)
- 조건 1개, 비교값 열고정, 값구열 행고정

예) 부서별(개발부교육부총무부) 과목별(영어,수학) 점수의 합계를 구해라
조건 : 부서별, 과목별
함수 : 합계 = SUM
값구열 : 점수

수식 : = Sum((조건1)*(값구열))
= Sum((부서전체절대참조=표부서열고정)*(영어점수전체절대참조))

 

2. 기타응용문제 

ㄱ. REPT 함수
=REPT(반복할문자, 배열함수( ))
=REPT("■",SUM(IF($C$2:$C$33=$I37,$H$2:$H$33)/100))
* 반복할문자= ㅁ한자

ㄴ. Round 함수
=Round(배열함수( ) , 자릿수)

*Round 함수 > Average함수 > if함수
*소수점첫째자리 = 1 > =Round((),1)
*비교값 열고정, 값구열 행고정
=ROUND(AVERAGE(IF($A$3:$A$23=$G27,G$3:G$23)),1)

#반올림 (숫자, 자리수)
정수(소수점) : 0
소수점 아래 : 양수
-소수점 첫째자리까지 : 1
-소수점 둘째짜리까지 : 2
소수점 위 : 음수 -9개수
십단위: "0이 1개" -1
백단위: 결과적으로 0이 3개 /10의단위에서올리시오 -2
천단위 -3
만단위 -4
백만단위 -6

ㄷ. IFERROR 함수
=IFERROR(배열함수( ),"오류메시지")

ㄹ. TEXT 함수
=TEXT(배열함수( ), "표시형식")

*텍스트함수 = TEXT(값,"표시형식") 값을 내가 원하는 표시형식으로 표현됨

표시형식 - 0명

TEXT(값,"0명")
1자리= 0명
2자리= 00명
3자리= 000명

값에는 프리퀀시 들어감
TEXT(FREQUENCY,"00명")

참 : TEXT(FREQUENCY,"00명")
거짓 : 미가입

조건 프리퀀시가 0이상이냐

=if(FREQUENCY(나이,표뒤쪽>0), Text(FREQUENCY(나이,표뒤쪽>0),"00명","미가입"

 

3. 자주 출제되는 함수

ㄴ. INDEX 함수( INDEX - MATCH - MAX 또는 LARGE((),1)

=INDEX($B$3:$B$32,MATCH(MAX(($C$3:$C$32=$E46)*($E$3:$E$32)),($C$3:$C$32=$E46)*($E$3:$E$32),0),1)
=INDEX($B$3:$B$32,MATCH(MAX(IF($C$3:$C$32=$E46,$E$3:$E$32)),($C$3:$C$32=$E46)*($E$3:$E$32),0),1)

*맥스(최대값)에 if 들어갈때 ()*() > (), 으로 바꿔서 2개로 나눠주기

=INDEX(학과(찾는값),MATCH(,(단과대학=표$O8)*(건강보험연계 행고정만),0),1)
=INDEX($B$3:$B$32,MATCH(LARGE(($A$3:$A$32=$O8)*(E$3:E$32),1),($A$3:$A$32=$O8)*(E$3:E$32),0),1)

INDEX
어레이: 찾으려는 값 범위
행: 최대값과 일치한 행번호 찾기위해 MATCH 함수씀
1적어주기

MATCH
MAX((조건)*(조건)*(값구열)) - 아래의 조건 복붙
(조건)*(조건)*(값구열)
0

MAX IF라면?
IF
(조건)*(조건)
값구열

 

ㄷ. LARGE SMALL IF

LARGE(IF(조건)*(조건),값구열),순번

입력순서 : 순번>IF>값구열>조건

강의코드별로 2번째 큰 합계
=라지((합계)*(강의코드절대참조 = $G27열고정),2)
*Large((),1) 뒤에 꼭 숫자 꼭 써주기)
*라지(If(),2)
*라지(If(강의코드절대참조=행고정,합계값구열),2)

ㄹ. COUNT

=COUNT(IF((LEFT($A$3:$A$23,1)=$A31),($C$3:$C$23=B$30)),1)

 

 

ㅂ. FREQUENCY (숫자구간에 대한 빈도수(건수))

*비율
=Frequency()/COUNTA()
나의건수/전체건수 = 비율

*프리퀀시 = 빈도수 : 몇건이냐, 구간에 해당하는 건수

ㄱ. 범위 드래그 해놓고 시작
프리퀀시() 범위에서 이 범위에 해당하는 걸 골라내겠다
컨트롤 시프트 엔터 해야지 구해짐

ㄴ. 비율을 구해라
건수/카운타(전체개수) = 드래그하고 퍼센트 찍어주면 비율나옴

프리퀀시 + IF 조건 문제
프리퀀시(판매량 범위)*(제조사=갤럭시(행고정)와 같냐 : 조건, 프리퀀시표옆절대참조)

최신기출 5회

프리퀀시(1주차범위행고정, 표범위a33:36)

범위드래그하고 ctrl Shift Enter

 

최신기출 7회

=FREQUENCY(현재예치금범위, 표에서 뒤만 300~2000드래그)/COUNTA(머리글뺴고범위만)*100
전부 절대참조

최신기출 8회
나이대별 가입자수를 영역에 표시해라.
가입자수가 0보다 큰경우 계산된 값을 두자리 숫자로 뒤에 '명'을 추가해 표시하고, 그 외는 '미가입'으로 표시

가입자수 >0
- 참 : 두자리숫자 + "명" 05명
- 거짓 : "미가입"

조건: FREQUENCY()>0)
참: TEXT(FREQUENCY(),"00명")
거짓: "미가입"

 

 

 

최신기출 6회
7회 2-3
8회 2-4
9회 2-3, 2-4
10회 2-4

비율= 개별/전체
비율 섭취회수/전체
비율 SUM() / COUNTA()

*LEFT RIGHT MID = 텍스트 함수라서 숫자로 바꿔줘야함
MID() *1 해주면 숫자가 됨