*배열함수는 아래의 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 해주면 숫자가 됨
'컴활' 카테고리의 다른 글
컴활 엑세스(ACCESS) 2016 #1.엑세스란, 기본키 외래키, 입력마스크, 유효성검사규칙, InStr 함수 (0) | 2021.06.03 |
---|---|
컴활 1급 실기 #7 사용자정의함수 (0) | 2021.05.31 |
컴활 1급 실기 #5. Edate 함수, 배열 함수 (0) | 2021.05.25 |
컴활 1급 실기 #4. 고급필터, 조건부서식, AND조건 OR조건, LEFT RIGHT MID 함수, Rank EQ Rank AVG 함수, LARGE SMALL 함수 (0) | 2021.05.19 |
컴활 1급 실기 #3. 사용자지정 셀서식 조건, 조건부서식(홈), 차트, 프로시저, 고급필터 (0) | 2021.05.15 |