엑셀 함수 정리, if 함수부터 vlookup 함수까지 총정리!(feat. 엑셀 단축키)

 

엑셀 함수 정리, if 함수부터 vlookup 함수까지 총정리!(feat. 엑셀 단축키)

 

 

 

알아두면 업무 하는데 많을 도움을 주는 필수 엑셀 함수를 총정리해 보겠습니다. 엑셀을 사용하실 때 많은 도움이 되었으면 합니다.

 

 

자세한 내용을 알아보기 전에 '사용자가 가장 자주 사용하는 10개 함수'를 먼저 알아보고 가겠습니다.

 

① SUM 함수
이 함수는 셀의 값을 더합니다.

② IF 함수
이 함수는 조건이 True이면 하나의 값을 반환하고, 조건이 False이면 다른 값을 반환합니다.

③ LOOKUP 함수
이 함수는 하나의 행 또는 열을 찾은 다음 두 번째 행 또는 열에서 같은 위치에 있는 값을 찾습니다.

④ VLOOKUP 함수
표나 범위에서 행을 기준으로 항목을 찾아야 할 경우 이 함수를 사용합니다. 예를 들어 사원 번호로 사원의 성을 조회하거나 전화번호부처럼 사원의 성을 조회해 전화번호를 찾을 수 있습니다. 

⑤ MATCH 함수
이 함수를 사용하여 셀 범위에서 항목을 검색한 다음 범위에서 해당 항목의 상대 위치를 반환합니다. 예를 들어 범위 A1:A3에 값 5, 7 및 38이 포함된 경우 수식 =MATCH(7, A1:A3,0)는 7이 범위의 두 번째 항목이므로 숫자 2를 반환합니다.

⑥ CHOOSE 함수
이 함수는 인덱스 번호를 기준으로 254개까지의 값 중에서 하나를 선택합니다. 예를 들어 value1에서 value7까지가 요일을 나타내고 1부터 7까지의 숫자 중 하나가 index_num으로 사용되는 경우 CHOOSE 함수는 요일을 반환합니다.

⑦ DATE 함수
이 함수는 특정 날짜를 나타내는 순차적인 일련 번호를 반환합니다. 이 함수는 수식이나 셀 참조에서 제공하는 연월일을 사용해야 하는 경우에 특히 유용합니다. 예를 들어 YYYYMMDD 등과 같이 Excel에서 인식할 수 없는 형식으로 작성된 날짜가 워크시트에 포함되어 있는 경우를 생각해 볼 수 있습니다.
DATEDIF 함수는 두 날짜 사이의 일, 월 또는 연도 수를 계산합니다.

⑧ DAYS 함수
이 함수는 두 날짜 사이의 일 수를 반환합니다.

⑨ FIND, FINDB 함수
FIND 및 FINDB는 두 번째 텍스트 문자열 내에서 하나의 텍스트 문자열을 찾습니다. 두 번째 텍스트 문자열의 첫 번째 문자에서 첫 번째 텍스트 문자열의 시작 위치 수를 반환합니다.

⑩ INDEX 함수
이 함수는 테이블이나 범위에서 값 또는 값에 대한 참조를 반환합니다.

 

 

---

 

[중요한 엑셀 함수 한눈에 볼수 있게 정리한 표]

 

 

 

 

[1] 날짜와 시간 함수

 

① DATE : =date(년, 월, 일)  /  년, 월, 일을 이용하여 특정 날짜를 표시

② TODAY : =today()  /  현재 컴퓨터 시스템의 날짜를 표시

③ NOW : =now()  /  현재 컴퓨터의 날짜와 시간을 표시
④ YEAR : =year(날짜)  /  연도에 해당하는 값(데이터) 표시
⑤ MONTH : =month(날짜)  /  월에 해당하는 값(데이터) 표시
⑥ WEEKDAY : =weekday(날짜, 유형)  /  해당하는 요일 번호를 표시
⑦ TIME : =time(시, 분, 초)  /  시, 분, 초를 이용하여 특정 시간을 표시

⑧ HOUR : =hour(시간) 시에 해당하는 값(데이터) 표시
⑨ MINUTE : =minute(시간)  /  분에 해당하는 값(데이터) 표시
⑨ SECOND : =second(시간)  /  초에 해당하는 값(데이터) 표시
⑩ DAY : =day(날짜)  /  일에 해당하는 값(데이터) 표시

 

 

[2] 수학 함수

 

① SUM : =sum (1, 2, 3... 숫자)  /  지정된 셀 범위의 합계를 구하는 함수 (인수는 최대 30개까지 사용)

② SUMIF : =sumif(조건범위, 조건, 찾을 범위)  /  조건을 만족하는 데이터의 합계를 구함
③ ROUND : =round(인수, 소수점 이하 자릿수)  /  표시된 소수점 이하 자릿수에서 반올림
④ ROUNDDOWN : =rounddown(인수, 내림할 자릿수)  /  지정된 자릿수 아래의 수를 내림

⑤ ROUNDUP : =roundup (인수, 올림 할 자릿수)  /  지정된 자릿수 아래의 수를 올림

⑥ INT : =int(실수)  /  실수에서 소수점 이하 절삭 후 정수로 내림
⑦ ABS : =abs(숫자)  /  숫자의 절댓값을 변환(부호가 없는 숫자)
⑧ SQRT : =sqrt(양수)  /  양수의 제곱근을 구함(숫자가 음수이면 오류값 표시)

⑨ EXP : =exp(숫자)  /  입력한 숫자를 e의 누승값

⑩ FACT : =fact(숫자)  /  인수로 입력한 숫자의 계층값

⑫ PI : =pi()  /  원주율을 결과로 말해줌

⑬ MOD : =mod(나누어질 인수, 나눌 인수)  /  나머지를 구하여 표시

⑭ PRODUCT : =product(인수 또는 셀 범위)  /  인수를 모두 곱한 값을 표시

⑮ SUMPRODUCT : =sumproduct(배열 또는 값)  /  배열 또는 범위의 대응되는 값끼리 곱하여 그 합을 구해 표시

 

 

[3] 문자별 함수

 

① MID : =mid (문자열 또는 문자열 참조 셀, 인수 1, 인수 2)  / 인수 1은 문자 위치, 2는 문자수 주어진 문자 열 중 시작위치부터 지정된 문자를 발췌하는 함수

② LEFT : =left (인수, 정수)

③ LOWER : =lower (문자열)  /  입력된 문자열을 모두 소문자로 표시

④ UPPER : =upper (문자열)  /  입력된 문자열을 모두 대문자로 표시

⑤ PROPER : =proper (문자열)  /  문자열에서 첫 단어만 대문자로 표시

⑥ TRIM : =trim (문자열)  /  문자열 사이에 한 칸 공백을 제외한 공백을 삭제
⑦ LEN : =len (문자열)  /  문자열 내의 문자열 개수를 구함

⑧ REPLACE : =repalce (문자열, 시작위치, 추출할 문자 열수, 바꿀 문자열)  /  문자열의 시작위치에서부터 지정하는 수만큼 문자열을 다른 문자로 변환

⑨ CONCATENATE : =concatenate (문자열)  /  여러 문자열을 하나로 조인

⑩ REPT : =rept (문자열, 반복 횟수)  /  문자열을 지정한 횟수만큼 반복

⑪ VALUE : =value (문자열)  /  문자열을 숫자로 변환
⑫ WON : =won (숫자 또는 셀 주소, 소수점이하 자릿수)  /  숫자를 문자열로 바꾸고 통화기호와 소수점 이하 자릿수를 적용

 

 

[4] 논리 함수

 

① IF : =if (조건, 참값(데이터), 거짓값(데이터))  /  조건을 만족하는 참값(데이터)과 데이터 값을 구함. 예) 국어가 70점 이상이면 합격! 아니면 탈락
② AND : =and (조건 1, 조건 2, 조건 3)  /  조건을 모두 만족하는 값(데이터)
③ OR : =or (조건 1, 조건 2...)  /  조건 중 하나만 만족해도 되는 값(데이터)
④ NOT : =not (조건)  /  논리식의 결과 값(데이터)을 반대로 표시

 

 

[5] 조건, 데이터 베이스 함수, 기타 함수

 

① DSUM : =dsum 전체범위, 찾을 열필드, 조건 범위)  / 조건을 만족하는 값(데이터)의 합계를 구함. 예) 총무부의 상식 점수 합계
② DAVERAGE : =daverage 전체범위, 찾을 열필드, 조건 범위)  /  조건을 만족하는 값(데이터)의 평균을 구함. 예) 총무부의 상식 점수 평균
③ DMAX : =dmax (전체범위, 찾을 열필드, 조건 범위)  /  조건을 만족하는 값(데이터)의 최댓값을 구함. 예) 총무부의 상식 점수의 최대값
④ DMIN : =dmin (전체범위, 찾을 열필드, 조건 범위)  / 조건을 만족하는 값(데이터)의 최솟값을 구함. 예) 총무부의 상식 점수의 최소값
⑤ DCOUNT : =dcount(전체범위, 찾을 열필드, 조건 범위)  /  조건을 만족하는 값(데이터)의 개수. 예) 상식 점수 중 총무부의 인원수
⑥ DGET : =dget (범위, 필드명이나 열번호, 조건 범위)  /  조건을 만족하는 값(데이터)을 범위에서 찾아 특정 필드에 서서 추출하여 표시
⑦ DPRODUCT : =dproduct (범위, 필드명이나 열번호, 조건 범위)  /  조건을 만족하는 값(데이터)을 범위에서 찾아 특정 필드에서서 곱을 구하여 표시
⑧ DSTDEV : =dstdev (범위, 필드명이나 열번호, 조건 범위)  /  특정필드에서 표준 편차를 구하여 표시
⑨ DVAR : =dvar (범위, 필드명이나 열번호, 조건 범위)  /  표본집단의 분산을 구하여 표시
⑩ ISERROR : =iserror (값(데이터))  /  값(데이터)이 오류값(데이터)을 참조하는지를 확인하여 true 또는 false로 표시

 

 

[6] 통계 함수

 

① AVERAGE : =average(숫자 1, 숫자 2...)  /  평균을 구하는 함수로, 주어진 참조 셀 범위는 모두 숫자 데이터가 되어야 함
② MAX : =max(숫자 1, 숫자 2...)  /  지정된 범위 안에서 최댓값(데이터)을 구함
③ MIN : =min(숫자 1, 숫자 2...)  /  지정된 범위 안에서 최솟값(데이터)을 구함
④ RANK : =rank(인수 1, 인수 2...)  /  인수 1-순위대상 셀 주소, 인수 2-순위비교 참조 범위, 인수 3-"0"일 때 내림차순이며, 생략이 가능, "1"일 때 오름차순
⑤ LARGE : =large(인수 또는 셀범위, 숫자 인수)  /  범위에서 지정한 숫자에 해당하는 큰 수. 예) 2번째로 큰 값(데이터)
⑥ SMALL : =small(인수 또는 셀범위, 숫자 인수)  /  범위에서 지정한 숫자에 해당하는 작은 수. 예) 3번째로 작은 값(데이터)
⑦ COUNT : =count(숫자 1, 숫자 2...)  /  인수로 입력된 숫자의 개수를 셈
⑧ COUNTA : =counta(범위 1, 범위 2...)  /  셀 범위 중 비어있지 않은 셀의 개수
⑨ COUNTBLANK : =countblank(인수 또는 셀범위)  /  공백개수를 구하여 표시
⑩ COUNTIF : =countif(범위, 조건)  /  범위에서 조건을 만족하는 개수

 

 

[7] 찾기 참조 영역 함수

 

① VLOOKUP : =vlookup (찾을 값(데이터), 기준표, 열번호, 옵션)  / 옵션:비슷한 값(데이터)이나 생략=1
정확한 값(데이터)=0, 찾을 값(데이터)을 기준표에서 찾은 후 열번호에 해당하는 값을 찾음
② HLOOKUP : =hlookup (찾을 값, 기준표, 행번호 2, 옵션)  /  찾을 값(데이터)을 기준표에서 찾은 후 열번호에 해당하는 값을 찾음
③ CHOOSE : =choose (조건, 첫째 값, 둘째 값...)  /  조건의 값(데이터)이 첫 번째 인수를 기준으로 찾음
④ INDEX : =index (범위, 행번호, 열번호)  /  첫번째 인수로 입력한 범위에서 행번호와 열번호에 해당하는 셀에 입력된 값을 찾음
⑤ MATCH : =match(찾으려는 값&범위, 되돌릴 값)  /  배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치값을 찾아 표시
⑥ OFFSET : =offset(기본참조영역, 행간 격, 열간 격)  / 주어진 참조영역으로부터 지정한 행과 열만큼 떨어진 위치의 참조
영역을 표시

 

 

출처 : pixabay

 

 

 

[8] 공학 함수

 

단위변환

① CONVERT : 다른 단위 체계의 숫자로 변환합니다.

 

베셀함수

① BESSELI : 수정된 Bessel 함수 In(x)을 반환합니다.
② BESSELJ : Bessel 함수 Jn(x)을 반환합니다.
③ BESSELK : 수정된 Bessel 함수 Kn(x)을 반환합니다.
④ BESSELY : Bessel 함수 Yn(x)을 반환합니다.

 

진법변환

① BIN2DEC : 2진수를 10진수로 변환합니다.
② BIN2HEX : 2진수를 16진수로 변환합니다.
③ BIN2OCT : 2진수를 8진수로 변환합니다.
④ OCT2BIN : 8진수를 2진수로 변환합니다.
⑤ OCT2DEC : 8진수를 10진수로 변환합니다.
⑥ OCT2HEX : 8진수를 16진수로 변환합니다.
⑦ DEC2BIN : 10진수를 2진수로 변환합니다.
⑧ DEC2HEX : 10진수를 16진수로 변환합니다.
⑨ DEC2OCT : 10진수를 8진수로 변환합니다.
⑩ HEX2BIN : 16진수를 2진수로 변환합니다.
⑪ HEX2DEC : 16진수를 10진수로 변환합니다.
⑫ HEX2OCT : 16진수를 8진수로 변환합니다.

 

복소수

① COMPLEX : 실수부와 허수부의 계수를 복소수로 변환합니다.
② IMARGUMENT : 각도가 라디안으로 표시되는 테타 인수를 반환합니다.
③ IMAGINARY : 복소수의 허수부 계수를 반환합니다.
④ IMCONJUGATE : 복소수의 켤레 복소수를 반환합니다.
⑤ IMCOS : 복소수의 코사인 값을 반환합니다.
⑥ IMCOSH : 복소수의 하이퍼볼릭 코사인 값을 반환합니다.
⑦ IMCOT : 복소수의 코탄젠트 값을 반환합니다.
⑧ IMCSC : 복소수의 코시컨트 값을 반환합니다.
⑨ IMCSCH : 복소수의 하이퍼볼릭 코시컨트 값을 반환합니다.
⑩ IMDIV : 두 복소수의 나눗셈 몫을 반환합니다.
⑪ IMEXP : 복소수의 지수를 반환합니다.
⑫ IMLN : 복소수의 자연 로그값을 반환합니다.
⑬ IMLOG10 : 복소수의 밑이 10인 로그값을 반환합니다.
⑭ IMLOG2 : 복소수의 밑이 2인 로그값을 반환합니다.
⑮ IMPOWER : 복소수의 멱을 반환합니다.
⑯ IMPRODUCT : 복소수의 곱을 반환합니다.
⑰ IMREAL : 복소수의 실수부 계수를 반환합니다.
⑱ IMSEC : 복소수의 시컨트 값을 반환합니다.
⑲ IMSECH : 복소수의 하이퍼볼릭 시컨트 값을 반환합니다.
⑳ IMSIN : 복소수의 사인 값을 반환합니다.
㉑ IMSINH : 복소수의 하이퍼볼릭 사인 값을 반환합니다.
㉒ IMSQRT : 복소수의 제곱근을 반환합니다.
㉓ IMSUB : 두 복소수 간의 차를 반환합니다.
㉔ IMSUM : 복소수의 합을 반환합니다.
㉕ IMTAN : 복소수의 탄젠트 값을 반환합니다.
㉖ IMABS : 복소수의 절댓값을 반환합니다.

 

기타 공학 함수

① BITAND : 두 숫자의 '비트 단위 And'를 반환합니다.
② BITLSHIFT : shift_amount비트씩 왼쪽으로 이동한 값 숫자를 반환합니다.
③ BITOR : 두 숫자의 비트 단위 Or를 반환합니다.
④ BITRSHIFT : shift_amount비트씩 왼쪽으로 이동한 값 숫자를 반환합니다.
⑤ BITXOR : 두 숫자의 비트 단위 '배타적 Or'를 반환합니다.
⑥ DELTA : 두 값이 같은지 여부를 검사합니다.
⑦ ERF : 오차 함수를 반환합니다.
⑧ ERF.PRECISE function : 오차 함수를 반환합니다.
⑨ ERFC ERF : 함수의 여값을 반환합니다.
⑩ ERFC.PRECISE : x에서 무한대까지 적분 된 ERF 함수의 여값을 반환합니다.
⑪ GESTEP : 숫자가 임계값보다 큰지 여부를 검사합니다.

 

 

 

 

 

[9] 엑셀 단축키 모음

1) F2 : 선택한 셀 편집
 - F2를 누르면 셀 편집모드로 들어가고 셀 끝으로 커서가 이동.

2) F4 : 마지막 편집 반복
 - F4를 누르면 마지막 동작이나 편집이 반복.

3) Ctrl + Alt + F9 : 모든 워크시트 계산
 - 열린 창에 있는 모든 워크시트를 계산.

4) F11 : 새 차트
 - F11을 사용하는 것은 새 차트를 만들 수 있는 편리하고 빠른 방법. 데이터를 선택하고 F11을 누르면 선택한 데이터가 들어간 기본 차트가 자동으로 나타남.

5) ALT : 리본 액세스
 - ALT 키를 누르면 도구 모음 리본에 작은 문자가 나타남. 이것들은 키보드로만 리본을 탐색할 수 있는 키 팁.

6) ALT + = : 자동 SUM() 선택
 - 데이터 블록에 있는 것들을 자동으로 넣는 자동합계.

7) ALT + enter : 동일 셀에서 새 라인 시작
 - 셀 안에서 줄 바꾸기.

8) ALT + H + O + I : 열 자동 크기 조정
 - 먼저 Alt + H를 눌러 홈 탭으로 이동하고, 그런 다음 O를 입력하여 형식 메뉴를 선택한 후(계속 Alt를 누르고 있음). 그런 다음 I을 입력하여 열 너비를 자동 조정하면 됨.

9) PG + UP : 다음 워크시트로 이동
 - 워크시트 탭을 오른쪽으로 전환.

10) PG + DOWN : 이전 워크시트로 이동
 - 워크시트 탭을 왼쪽으로 전환.

11) Ctrl + ' : 공식 표시
 - 셀의 공식과 값을 한 번에 표시하는 것 사이에서 빠르게 전환할 수 있음.

12) Ctrl + 백스페이스 : 활성 셀 표시
 - Ctrl + Backspace를 누르면 창이 바로 활성 셀로 이동.

13) Ctrl + Shift + # : 날짜 형식을 일, 월 및 연도로 변경
 - 이 바로 가기를 사용하면 기본 날짜 형식에 빠르게 액세스 할 수 있음.

14) Ctrl + K : 하이퍼링크를 삽입하려면
 - 먼저 하이퍼링크를 표시할 위치를 선택해야 함. 키 입력을 누르면 하이퍼링크 삽입 상자가 나타나 프로세스가 훨씬 쉬워짐.

 

15) Ctrl+Shift + $ : 선택한 셀에 통화 형식 적용
 - 셀을 통화 형식으로 빠르게 변경하려면 셀을 선택하고 이 바로 가기를 누르면 됨.

16) Ctrl + Shift + & : 셀에 테두리 적용
 - 셀에 테두리를 빠르게 추가하는 방법.   

17) Ctrl + B : 굵게
 - 셀 또는 텍스트를 빠르게 굵게 하거나 풀려면 이 옵션을 사용하세요.

18) Ctrl + I : 이탤릭체
 - 이것을 사용하여 셀이나 텍스트를 빠르게 이탤릭 화하거나 비활성화.

19) Ctrl + U : 밑줄
 - 셀 또는 텍스트에 빠르게 밑줄을 긋거나 밑줄을 긋지 않으려면 이 옵션을 사용하세요.

20) Ctrl + Shift + ~ : 일반 스타일 번호
 - 이 명령은 당신의 셀을 일반 스타일 번호로 스타일화.

21) Ctrl + Shift + % : 백분율 스타일 번호
 - 이 명령은 셀을 백분율 스타일 번호로 스타일 지정.

22) Ctrl + Shift + ^ : 지수표기법 스타일
 - 지수로 표현하고 싶을 때 사용.

23) Ctrl + Shift + @ : 시간 스타일
 - 시간으로 표현하고 싶을 때 사용.

24) Ctrl + Shift +! : 번호 스타일
 - 번호로 표현하고 싶을 때 사용.

25) Ctrl + F12 : 열기
 - 이 키를 누르면 다른 파일을 열 수 있음.

26) Ctrl + 스페이스바 : 전체 열 선택
 - 마우스를 사용하여 머리글을 클릭하는 대신 이 바로 가기를 사용하여 작업 중인 전체 열을 자동으로 선택.

27) Ctrl + [] : 현재 셀에서 사용되는 공식의 참조 셀로 이동
 - 현재 셀에서 사용하고 있는 수식을 동일하게 사용하는 참조 셀을 빨리 보려면 이 바로가기를 사용하여 셀을 확인.

28) Ctrl + ; : 활성 셀에 실제 날짜 삽입
 - 오늘 날짜 기입 기능.

29) Ctrl + : 활성 셀에 실제 시간 삽입
 - 현재 시간 기입 기능.

30) Ctrl + A : 모두 선택
 - 모두 선택하는 단축키.

31) Ctrl + Shift + J : 상수 나열
 - 이를 사용하면 워크시트 내에 속성/방법/조건이 나열.

32) Ctrl + D : 수식 복사 다운
 - 이 명령은 그 위의 셀에 있는 공식의 정확한 사본을 만들 것이다.

33) Ctrl + F : 찾기

34) Ctrl + H : 찾기&바꾸기
 - 찾아서 대체하는 기능.

35) Ctrl + 오른쪽 화살표 : 오른쪽으로 이동

36) Ctrl + 왼쪽 화살표 : 왼쪽으로 이동

37) Ctrl + 위쪽 화살표 : 위로 이동

38) Ctrl + 아래쪽 화살표 : 아래로 이동

39) Ctrl + enter : 여러 셀에 동일한 수식
 - 여러 셀에 동일한 수식을 넣으려면 잡아끄는 방법 말고 사용할 수 있는 방법.

40) Shift + Spacebar : 전체 행 선택
 - 전체 행을 선택.

41) Shift + F3 : 이전 항목 찾기

 

 

 

 

---

 

 

 

 

이상으로 엑셀 함수 정리에 모든 것을 총정리해 보았습니다. 추가로 엑셀 단축키도 알아보았습니다. 찾으셨던 내용이 있어서 많은 도움이 되었기를 바랍니다.