1. 일반 함수
※ DB함수말고는 수식에 필드명 쓰지 않기!
< ★ ★ ★ 참조 함수 - vlookup, hlookup>
=vlookup(찾을값, 참조범위, 열번호, 정확도) //0: 정확, 1: 유사
=hlookup(찾을값, 참조범위, 행번호, 정확도) //참조범위는 필드 제외 데이터 전체 범위
//찾아야 할 값이 세로로 되어있으면 vlookup, 가로로 되어있으면 Hlookup
< 조건이 들어간 함수 >
=countif(범위, 조건) : 조건을 만족하는 개수를 구함
=countifs(범위1, 조건1, 범위2, 조건2, ...): 모든 조건을 만족하는 개수를 구함
=sumifs(합계범위, 범위1, 조건1, 범위2, 조건2, ...): 모든 조건을 만족하는 합계를 구함
=averageif(범위, 조건, 평균범위)
< 그 외 중요한 함수 >
=round(숫자, 까지 자릿수)
=match(찾을값, 범위, 0)
//범위가 1개의 열이면 몇 번째 행에 있는지 찾아주고,
1개의 행으로 되어있으면 몇 번째 열에 있는지 찾아준다.(숫자로 표현해줌)
=iferror(값, 오류값): 값이 있으면 값을 출력, 아니면 오류값 출력
=replace(문자, 시작위치, 글자수, 교체문자)
=index(범위, 행, 열)
=EDATE(날짜,K): 날짜의 월에 K를 더한 날짜 반환
ex. =edate("2023-1-5", 3) -> 2023-4-5
ex. =edate("2023-4-5", -3) -> 2023-1-5
=Rank.EQ(구할값, 범위, 정렬)
<정렬>
0: 내림차순 / 1: 오름차순
=find("문자",셀)
=percentile(범위, 백분위값)
백분위값: 0~1사이로, 100% → 1
=weekday(날짜, 옵션)
<옵션>
1 또는 생략: 일요일을 1로 시작
2: 월요일을 1로 시작
3: 월요일을 0으로 시작
=date()
<문제>
*누적개수 구하기*
=countif(범위, 조건) ex. =countif($E$4:E4,E4)
//첫번째 셀을 찍고 shift + ;을 눌러준 후, 앞 범위만 F4를 눌러서 고정해준다.
*소득공제별, 소득공제내용별 금액의 평균*
=averageifs(평균범위, 범위1, 조건1, 범위2, 조건2, ...)
//범위는 전체영역, 조건은 셀 하나
*비율* countif/counta
=rept(문자, 개수)
=countif(범위, 조건) //범위에서 조건을 만족하는 개수를 구해준다.
=counta(범위) //전체 개수를 구해준다.
=text(값, "형식") //ex. =text(값, "0.00%")
//aaa : 월~일, aaaa : 월요일~일요일
//41.03%를 4개로 나타내려면 /10을 해주면 된다.
//100% -> 1, 25% -> 0.25
//n개로 표현하려면 *10
=rept("★", (countif/counta*10)) & text(countif/counta,"(0.00%)")
*비율* Quotient
=Quotient(숫자, K): 몫을 구할 때
=Mod(숫자, K): 나머지를 구할 때
//ex. 10%로 나눈 몫을 -> =Quotient(식, 10%)
=rept("♠",Quotient(식,10%)) & rept("♤",10-Quotient(식,10%))
//ex. ♠♠♠♠♤♤♤♤♤♤
*과정명, 접수인원을 이용하여 접수율을 표시하시오.*
▷접수율: 과정명별 접수인원/전체접수인원
▷백분율로 소수 2번째 자리까지 표시
=sumif(범위, 조건, 합계범위) //범위,합계범위는 전체 영역, 조건은 한 셀만(혼합참조)
=sumifs(합계범위, 범위1, 조건1, 범위2, 조건2, ...)
=Text(sumif/sum, "0.00%")
=Text(sumif(과정명,조건,접수인원)/sum(접수인원), "0.00%")
*‘총 납부액’을 ‘월 납부액’으로 나눠 납부기간과 잔여금을 구하시오.*
- 표시 방법 : 17개월(40000원)
- MOD, INT함수와 &연산자 사용
=INT(‘총 납부액’/‘월 납부액’)&“개월(”&MOD(‘총 납부액’,‘월 납부액’)&“원)”
*‘입학일자’와‘졸업일자’를 이용하여 재학기간을 계산하시오.*
- 1년은 365일, 한 달은 30일 기준으로하여 계산
- ‘3년 11개월’ 형태로 표시
-QUOTIENT, MOD 함수와 &연산자 사용
=QUOTIENT(‘졸업일자’-‘입학일자’,365)&“년 ”& QUOTIENT(MOD(‘졸업일자’-‘입학일자’,365)30)&“개월”
*‘연수시작일’,‘연수개월수’를 이용하여 ‘연수일수’를 표시하시오.*
- 연수일수: ‘연수시작일’로부터 ‘연수개월수’뒤 사이의 일 수를 표시
- DAYS, EDATE 함수와 &연산자 사용
=DAYS(EDATE(‘연수시작일’,‘연수개월수’),‘연수시작일’) & “일”
*‘판매량’과 ‘판매이익’을 이용하여 전체 판매이익의 평균을 초과하는 용품수와 판매량의 합계를 계산하여 표시하시오.*
- 표시 예 : 3종(418개)
- CONCATENATE, AVERAGE, SUMIF, COUNTIF 함수와 &연산자 사용
=CONCATENATE(COUNTIF(‘판매이익 전범위’,“>=”&AVERAGE(판매이익 전범위)),“종(”,SUMIF(‘판매이익 전범위’,“>=”&AVERAGE(‘판매이익 전범위’),‘판매량 전범위’),“개)”)
*‘신청인원’,‘모집인원’을 이용하여 신청률을 표시하시오.*
- 오류 발생 시 ‘신청인원없음’으로 표시
- IFERROR, TEXT, REPT 함수 사용
=IFERROR(REPT(“★”,(‘신청인원’/‘모집인원’)*10) & TEXT(‘신청인원’/‘모집인원’,“0.0%”),“신청인원없음”)
=CONCATENATE(SUBSTITUTE(‘거주지역’,“시”,“광역시”),“ ”,‘회원명’,“님”)
=CONCATENATE(‘아이디’,REPLACE(‘도메인’,1,3,“@eduwill”))
2. 배열 수식
수식 입력 후, Ctrl+Shift+Enter !
*코드를 이용하여 코드의 개수를 표시하시오. (SUM 함수 사용)*
=SUM((조건)*1): 조건을 만족하는 것의 개수를 구하는 것 //조건: 범위=찾을 값
=SUM((조건1)*(조건2))
=SUM(IF(조건1,1))
=SUM(IF((조건1)*(조건2),1) // *는 and, +는 or를 나타냄.
*구분-성별의 오른쪽 두글자를 이용하여 성별 개수를 표시하시오. (SUM, IF, RIGHT 함수 사용)*
=SUM(IF(RIGHT(구분-성별전체절대참조,2)=기준,1))
*코드, 구분-성별의 오른쪽 두글자를 이용하여 코드별 성별 개수를 표시하시오. (SUM, RIGHT 함수 사용)*
=SUM((코드전범위절대참조=코드기준1셀열고정)*(right(구분-성별전체절대참조,2)=성별기준1셀행고정))
*코드, 가입금액을 이용하여 코드별 가입금액의 합계를 계산하시오. (SUM, IF 함수 사용)*
=SUM(IF(조건1, 범위))
=SUM((조건1)*범위) //조건을 만족하는 범위의 합계를 구하는 것
=SUM(IF((조건1)*(조건2), 범위))
=SUM((조건1)*(조건2)*범위)
=SUM(IF((코드전체절대참조=코드기준1셀열고정, 가입금액전체절대참조))
*코드, 가입금액을 이용하여 코드별 가입금액의 평균을 계산하시오. (AVERAGE, IF 함수 사용)*
=average(if(조건1,범위))
*가입나이별 빈도수를 계산하시오. (Frequency 함수 사용)*
※Frequency 함수는 값 넣을 범위를 잡아놓고 시작해야 한다. ※
=Frequency(구할범위, 조건범위)
//범위1이 범위2에 해당하는 빈도수 구하기
//조건셀이 두 열로 되어 있다면 뒤에 범위를 넣어야 한다.
=Text(값, "[>0]00명;없음") //0보다 크면 00명, 그 외에는 없음으로 표시
*성명, 금액, 소득공제 오른쪽 2글자를 이용하여 성명별, 소득공제별 금액의 평균을 계산하시오.*
(Text, Average, IF, Left 함수 사용)
▷ 금액은 천단위마다 쉼표(,)표시
=Average(IF((조건1)*(조건2),범위))
=Text(식, "#,##0")
*성명별 소득공제가 일반의료비인 금액의 최대값을 표시하시오. (Large 함수 사용)*
//조건이 2개다. => 성명, 일반의료비
=Large((조건1)*범위,K) //K: K번째로 큰 값을 의미
=Large((조건1)*(조건2)*범위,K)
=Large(IF((조건1, 범위), K)
=Large(IF((조건1)*(조건2), 범위), K)
*성명, 금액을 이용하여 성명별 금액의 상위 4위까지 평균을 계산하시오. (Average, Large 함수 사용)*
=Large((조건)*범위, K)
=average(범위)
=average(값1,값2,값3)
=average(large((성명전체절대참조=성명조건1셀열고정)*금액전체절대참조,{1,2,3,4})) //average는 범위로 씀
*소득공제의 개수를 표시하시오. (REPT, COUNT, IF 사용)*
▷ 표시 예 : 4 -> ♣♣♣♣, 7 -> ♣♣♣♣♣♣♣
=REPT(문자, K)
=count(if(조건1,1))
=rept("♣",count(if(소득공제전체절대참조=소득공제기준1셀,1)))
*성명, 금액, 소득공제의 왼쪽 2글자를 이용하여 성명별 소득공제별 금액의 최대값의 순위를 표시하시오.*
(RANK.EQ, MAX, IF, LEFT 함수를 이용한 배열 수식과 & 연산자 사용)
▷ 표시 예 : 6 -> 6위
=max(if((조건1)*(조건2),범위)) //max(범위)
=rank.eq(기준셀, 범위, 0) //0 : 내림차순, 1 : 오름차순
=rank.eq(max(if((성명=성명)*(left(소득공제,2)=소득공제기준1셀),금액범위)),금액범위절대참조, 0)&"위"
*접수인원별 빈도수를 표시하시오. (Text, Frequency 함수 사용)*
▷ 표시 예 : 6 -> 06강좌
=Text(Frequency(접수인원전범위,접수인원기준뒤에범위 ),"00강좌")
*교육장소와 모집정원을 이용하여 교육장소별 접수인원의 빈도수를 표시하시오. (Frequency, IF 함수 사용)*
=Frequency(if(조건1,범위1),범위2)
//조건1을 만족하는 범위1에서 범위2에 해당하는 빈도수
★배열수식은 1개의 열을 블록잡고 시작해야 한다.
=Frequency(if(교육장소전체절대참조=교육장소기준1셀행고정,모집정원전체절대참조),모집정원기준전체절대참조)
*교육장소와 접수인원을 이용하여 교육장소별 접수인원의 최대값을 표시하시오. (MAX 함수 사용)*
=MAX((조건1)*범위) //조건1을 만족하는 범위의 최대값
=MAX((교육장소전체절대참조=교육장소기준1셀열고정)*접수인원전체절대참조)
*과목코드, 교육장소, 접수인원을 이용하여 교육장소의 접수인원에 최대값에 해당하는 과목코드를 찾아 표시하시오. (INDEX, MATCH, LARGE, IF 함수 사용)*
=Large(if(조건1,범위),1) //최대값
=match(찾을값,범위,정확도) //최대값을 찾아야 함//조건범위를 써야 함
=INDEX(범위, 행, 열)
=INDEX(과목코드전체절대참조, MATCH(Large(IF(교육장소전체절대참조=교육장소기준1셀열고정,접수인원전체절대참조),1),(교육장소전체절대참조=교육장소기준1셀열고정)*접수인원전체절대참조,0), 1)
*과목코드와 교육장소를 포함하는 개수를 표시하시오. (COUNT, IF, FIND 함수 사용)*
=count(if(조건, 범위),1)
=find(찾을값,문자): 문자에서 찾을값의 위치를 숫자로 나타낸다.
//search함수와 쓰임이 비슷.
//find는 대소문자 구분해서 찾음.
//find는 글자 안에 포함하는 값만 있으면 다 찾아준다.
=count(if((조건1)*(조건2),1))
=count(if(find(과목코드조건1셀열고정, 과목코드전체절대참조)*find(교육장소조건1셀행고정, 교육장소전체절대참조),1))
*순위*
=rank.eq(평균1셀, 평균전체절대참조, 0) //높은 수가 1등
=rank.eq(평균1셀, 평균전체절대참조, 0) + sumproduct((평균전체절대참조=평균맨위값)*(엑셀전체절대참조>엑셀맨위값))
//SUM으로 써도 된다.
//평균이 같은 것 중 엑셀 범위가 큰 값이 우선
*그룹별 순위 ( SUM 배열 수식 사용 ) ★공식 암기 ★*
=SUM((학과전체절대참조=학과맨위1셀)*(평균전체절대참조>평균맨위1셀))+1
