1.날짜와 시간함수
=NOW() : 현재날짜+시각
=TODAY() : 현재날짜
=YEAR(1999-09-09) : 1999
=MONTH(1999-09-09) : 9
=DAY(1999-09-09) : 9
=HOUR(7:45:30PM) : 19
=MINUTE(7:45:30PM) : 45
=SECOND(7:45:30PM) : 30
=DATE(1999,09,09) : 1999-09-09
=TIME(19,45,30) : 7:45:30PM
=WEEKDAY(날짜,1) -> 1이 ‘일요일’ 부터로 계산 ~7(토요일)
=WEEKDAY(날짜,2) -> 1이 ‘월요일’ 부터로 계산 ~7(일요일)
=WEEKDAY(날짜,3) -> 0이 ‘월요일’ 부터로 계산 ~6(일요일)
=DAYS(끝 날짜, 시작날짜) : ‘끝 날짜’ 에서 ‘시작 날짜’를 뺀 일 수를 계산 (근무일수,남은일수 등)
=EDATE(시작 날짜, 개월 수) : ‘시작 날짜’ 에서 ‘개월 수’를 더한 날짜를 반환 (한달 전은 -1)
=EOMONTH(시작 날짜, 개월 수) : ‘시작 날짜’에서 ‘개월 수’를 더한 달의 마지막 날짜 반환
=WORKDAY(시작 날짜, 일 수, 휴일 날짜) : ‘시작 날짜’에 토,일요일과 ‘휴일 날짜’를 제외하고
‘일수’만큼 지난 날짜를 반환 (휴가 출발일, 휴가 일수로 출근날 계산하기)
2. 논리함수
=IF(조건,참,거짓)
=AND(조건1,조건2,…) : 조건이 다 참이어야 ‘참’이 나옴
=OR(조건1,조건2,…) : 조건 중 하나라도 참이면 ‘참’
=NOT(조건) : 조건의 반대 값을 반환 (참->거짓 / 거짓->참)
=IF(AND(OR(조건1,조건2),조건3),”참”,”거짓”) : 조건1또는2 중 하나가 참이고, 조건3이 참이면 ‘참’
=IFERROR(인수,”거짓”)
=IFS(조건1,참1,조건2,참2,조건3,참3…거짓): 기존 IF를 이어붙여 만들던 것을 축소화
=SWITCH(기준값,조건1,결과값1,조건2,결과값2…) : 기준값이 문자열 함수로 추출된 값이면, A 이면 ‘APPLE’,
B 이면 ‘BANANA’ 라고 표기하는 경우임.
3. 데이터베이스 함수
데이터베이스함수( 표범위 , 열제목(열번호) , 조건(드래그범위) )
=DAVERAGE: 평균 계산
=DCOUNT: 해당 셀 개수 계산
=DCOUNTA: 해당 조건에 맞으면서 비어있지 않은 셀 개수 계산
=DMAX: 가장 큰 값
=DMIN: 가장 작은 값
=DSUM: 합계 계산
4.문자열 함수
=LEFT(텍스트, 개수) : ‘텍스트’ 왼쪽부터 지정한 ‘개수’만큼 추출
=RIGHT(텍스트, 개수) : ‘텍스트’ 오른쪽부터 지정한 ‘개수’만큼 추출
=MID(텍스트, 시작위치, 개수) : ‘텍스트’의 ‘시작위치’부터 지정한 ‘개수’만큼 추출
=TRIM(텍스트) : 문자열의 공백 제거
=LEN(텍스트) : 텍스트의 길이(개수) 반환
=UPPER(텍스트) : 다 대문자로 변환
=LOWER(텍스트) : 다 소문자로 변환
=PROPER(텍스트) : 첫 문자만 대문자로 변환
=FIND(찾는 문자, 찾을 텍스트, 시작 위치) : ‘문자열’의 ‘시작위치’에서 ‘찾을 텍스트’를 찾아 그 위치를 반환
=SEARCH(찾는 문자, 찾을 텍스트, 시작 위치) : FIND와 똑같지만, 대소문자 구분 X, 와일드카드(*,?) 사용 가능
※ FIND,SEARCH 함수는 위치에 공백도 포함됨.
5. 수학과 삼각 함수
=ABS(인수) : 숫자를 절대값으로 계산
=INT(인수) : 주어진 인수보다 크지 않은 정수로 변환 =INT(5.1) -> 5 / =INT(-5.1) -> -6
=TRUNC(인수,자릿수) : ‘인수’에 대해 지정한 ‘자릿수’ 의 아래 수치를 버림 =TRUNC(23.123,2) -> 23.12
=MOD(인수1,인수2) : 1을 2로 나눈 나머지 계산 =MOD(67,5) -> 2
=POWER(인수, 제곱값) : 인수를 제곱값만큼 거듭 곱한 값 계산 =POWER(5,2) -> 25
=ROUND(인수, 자릿수) : 인수에 대해 지정한 자릿수까지 반올림
=ROUNDUP(인수, 자릿수): 인수에 대해 지정한 자릿수까지 올림
=ROUNDDOWN(인수, 자릿수) : 인수에 대해 지정한 자릿수까지 내림
=RAND() : 0보다 크거나 같고 1보다 작은 난수를 구하는 함수. 무작위로 0.98429384와 같은 숫자가 추출됨
로또번호 구하기 =INT(RAND() * (로또 끝번호;43-시작번호;1)+1) : 1~43 숫자중 0.484848와 같은
아무거나 곱한 다음 정수로 숫자 뽑기
=RANDBETWEEN(인수1,인수2) : 인수1과 인수2 사이의 임의의 정수 출력
=SUM(인수1,인수2,,,) : 합계 계산
=SUMIF(조건 범위, 조건, 합계 범위) : 조건 범위에서 조건에 해당하는 것만 합계 계산
=SUMIFS(합계 범위, 조건 범위1, 조건1, 조건 범위2, 조건2 ,,,) : 여러 개 조건범위에서 여러 개 조건에
해당하는 것들을 합계
6. 찾기와 참조함수
=HLOOKUP(기준셀,범위,행 번호, 0 OR 1)
=VLOOKUP(기준셀,범위,열 번호, 0 OR 1)
0: 정확한 값(TRUE)
1: 유사한 값(FALSE)
=CHOOSE(K,첫번째,두번째,세번째) : K(인수)값 만큼 해당 번호에 맞는 결과값 도출 =CHOOSE(2,”1등”,”2등”) -> 2등
=INDEX(찾을 범위,행 번호,열 번호) : 범위에서 행,열에 따른 값을 찾아줌
=MATCH(찾을값, 찾을 범위,0 OR 1) : 몇번째 열/행인지 찾아줌
=COLUMN(위치) : 지정된 위치의 열 번호를 반환
=COLUMNS(범위) : 지정된 범위의 열의 개수를 반환
=ROW(위치) : 지정된 위치의 행 번호를 반환
=ROWS(범위) : 지정된 범위의 행의 개수를 반환
7. 통계함수
=AVERAGE(범위) : 범위 평균 계산
=AVERAGEA(범위) : 문자가 포함된 범위도 평균 계산할 때 씀 (문자->0취급, TRUE->1취급)
=AVERAGEIF(범위,조건,평균범위)
=AVERAGEIFS(평균범위,범위1,조건1,범위2,조건2…)
=COUNT(범위) : 숫자가 들어있는 셀 개수 계산
=COUNTA(범위) : 자료가 입력되어 있는 셀 개수 계산
=COUNTBLANK(범위) : 범위 중 자료가 없는 셀의 개수 계산
=COUNTIF(범위,조건) : 범위 중 조건에 해당하는 셀 개수 계산
=COUNTIFS(범위1,조건1,범위2,조건2…): 여러 범위, 여러 조건에 해당하는 셀 개수 계산
=LARGE(범위,K) : 범위중 K번째로 큰 값을 반환
=SMALL(범위,K) : 범위 중 K번째로 작은 수 반환
=MAX(범위) : 범위 중 가장 큰 수 반환
=MAXA(범위) : 문자,빈 셀,TURE를 포함한 범위 중 가장 큰 값 반환
=MIN(범위) : 범위 중 가장 작은 수 반환
=MINA(범위) : 문자,빈 셀,TURE를 포함한 범위 중 가장 작은 값 반환
=MEDIAN(범위) : 범위 인수 숫자들 중에서 중간에 해당하는 값 반환 =MEDIAN(“1”,”3”,”6”) -> 5
=MODE.SNGL(범위) : 범위 인수 중 가장 빈도수가 많은 인수 값 반환
=RANK.EQ(기준셀, 범위, 0 OR 1): 순위 계산하기
0: 내림차순 기준 (가장 큰 값이 1위)
1: 오름차순 기준 (가장 작은 값이 1위)
=STDEV.S(범위) : 범위의 표본표준편차를 계산
=VAR.S(범위) : 범위 표본집단의 분산을 계산
8. 추가로 헷갈렸던 부분
<사용자 지정 표시 형식>
#-> 숫자 0이면 표시 x
0-> 숫자 0이면 0으로 표시됨
숫자 뒤에 “개”, “명” 등 문자 삽입 시 : 0”개”
천 자릿수 표시, 문자 삽입 시 : #,##0”원”
0 생략하고 뒤에 “천원” 삽입 시 : #,##0,”천원” (, 쉼표 하나가 0 세개 생략)
160000 [#,##0,”천원”] -> 160천원
0~100에 ‘%’ 삽입 시 : @”%” (0~100은 문자이므로 @로)
날짜 일련번호에서 날짜와 요일만 표시할 경우: dd”일(“aaaa”)” (aaaa -> 월요일)
‘2019’ 를 ‘*2019년’으로 표시할 시 : “*”0”년” (2019는 날짜 일련번호가 아닌 그냥 2019 숫자임)
<조건부 서식 – 수식을 사용하여 서식을 지정할 셀 결정>
‘A반’이 아닌 행만 수식할 경우 [=기준셀<>”A’반”] 기준셀 상대참조
아니다 : <> (고급필터에서도 사용됨; <>무농약, <>카드결제 등)
날짜 이후: >=2023-01-01 (이상)
날짜 이전: <=2023-01-01 (이하)