반드시 알아야 할 엑셀 함수 총 정리!

안녕하세요, 패스트캠퍼스 데이터분석 부트캠프 4일차입니다.

오늘은 직장인이 꼭 알아야 할 엑셀이라는 주제로 엑셀 기초부터 엑셀 시각화까지 배웠는데요, 그 중에서도 엑셀 함수에 대해 정리해볼까 합니다!

 

엑셀 함수가 어렵다 하시는 분들은 참고해주시면 좋을 것 같아요 :)

---

COUNT 함수

특정 범위에서 숫자 데이터가 들어가 있는 셀의 개수 파악

= COUNT(C5:C12) 

→ C5:C12 는 숫자 데이터의 개수를 파악할 셀 범위

 

COUNTA 함수

특정 범위에서 데이터가 들어가 있는 셀(비어 있지 않은 셀)의 개수 파악

= COUNTA(C5:C12)

→ C5:C12 는 데이터의 개수를 파악할 셀 범위

 

COUNTBLANK 함수

특정 범위에서 비어 있는 셀의 개수 파악

= COUNTBLANK(C5:C12)

→ C5:C12 는 빈 셀의 개수를 파악할 셀 범위

 

COUNTIF 함수

특정 범위에서 하나의 조건을 만족하는 셀의 개수 파악

= COUNTIF(C5:C12, "A")

→ C5:C12 는 개수를 파악해야 할 데이터가 있는 범위

→ "A" 는 조건(가정)

 

= COUNTIF( (C5:C12, ">=5")

→ C5:C12 는 개수를 파악해야 할 데이터가 있는 범위

→ ">=5" 는 조건(가정)

 * 일반적으로 함수 안에 부등식을 사용할 때는 따로 " "를 붙이지 않지만, COUNTIF 등의 함수의 조건으로 사용할 대는 " "를 붙여야 함

 

COUNTIFS 함수

특정 범위에서 2개 이상의 조건을 동시에 만족하는 셀의 개수 파악

= COUNTIFS(C5:C12, ">5", D5:D12, "<8")

→ C5:C12 는 개수를 파악해야 할 데이터가 있는 범위1

→ ">=5" 는 조건1

→ D5:D12 는 개수를 파악해야 할 데이터가 있는 범위2

→ "<8" 는 조건2

 

IF 함수

부등호/등호(>,<,=)를 활용해 조건을 가정하고 조건에 만족하는 값과 만족하지 않는 값을 다르게 표시

IF함수를 1번 사용할 때마다 데이터가 2가지로 구분되므로 데이터를 분류할 때 사용

* IF함수를 N번 중첩 사용하면 데이터가 N+1가지로 구분 됨

= IF(A1>=90, "합격", "불합격")

→  A1>=90 은 조건

→ 합격 은 조건을 만족할 경우 표시할 값

→ 불합격 은 조건을 만족하지 않을 경우 표시할 값

 

= IF(B1>=1000, "Platinum", IF(B1>=500, "Gold", "Silver"))

→ B1>=1000 은 첫번째 IF의 조건

→ Platinum 은 첫번째 IF의 조건을 만족할 경우 표시할 값

→ B1>=500 은 두번째 IF의 조건

→ Gold 는 두번째 IF의 조건을 만족할 경우 표시할 값

→ Silver 는 두번째 IF의 조건을 만족하지 않을 경우 표시할 값

 

VLOOKUP 함수

공통 기준 열을 기준으로 N번째에 있는 데이터를 찾아오는 함수

*조건*

• 현재 작성중인 표와 원래 데이터 간의 공통 기준 열이 있어야 함
• 내가 불러오고자 하는 데이터가 원래 데이터의 공통 기준 열 오른쪽에 있어야 함
• 공통 기준 열에 중복된 데이터가 없어야 함

= VLOOKUP(D5, 직원정보LIST!$D:$J, 3,0)

→ D5 는 찾을 기준 데이터

→ 직원정보LIST!$D:$J 는 원래 데이터의 범위(공통 기준열부터~)

→ 3 은 불러올 데이터의 열 번호(공통 기준열을 1로 했을 때)

→ 0 은 FALSE값 (정확히 일치하는 값) / 만약 0대신 1을 쓰면 TRUE값(근사치의 값)

   *대부분의 경우 정확한 값을 찾으려고 하기때문에 보통은 0을 주로 씀

 

MATCH 함수

내가 찾고 싶은 값이 한 행/열에서 몇 번째에 있는지를 숫자로 알려줌

결과가 반드시 숫자로 나옴

= MATCH(lookup_value, lookup_array, match type)

→ lookup_value 는 내가 찾고 싶은 값

→ lookup_array 는 내가 찾고 싶은 값이 포함된 단일 열 또는 행 범위

→ match type 는 정확히 일치하는 값 or 근사치 여부 (0 : 정확히 일치하는 값 / 1 : 근사치)

 

INDEX 함수

특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러옴

= INDEX(array, row_num, column_num)

→ array 는 범위

→ row_num 은 찾고 싶은 데이터의 범위 내 행 번호

→ column_num 은 찾고 싶은 데이터의 범위 내 열 번호

 

SUMIF 함수

특정 조건에 맞는 데이터들의 합계 계산

= SUMIF($C$5:$C$35, "식비", $D$5:$D$35)

→ $C$5:$C$35 는 더할 조건들의 범위

→ 식비 는 조건

→ $D$5:$D$35 는 더할 값들의 범위

 

SUMIFS 함수

2개 이상의 조건을 동시에 만족하는 데이터들의 합계 계산

= SUMIFS(D5:D35, B5:B36, "2030년", C5:C36, "매출액")

* SUMIFS에서는 SUMIF와 다르게 더할 값들의 범위를 가장 먼저 지정해야 함

→ D5:D35 는 더할 값들의 범위

→ B5:B36 는 더할 조건 범위1

→ 2030년 은 조건1

→ C5:C36 는 더할 조건 범위2

→ 매출액 은 조건2

 

SUMPRODUCT 함수

인수(배열)끼리 곱하여 곱들의 합계를 계산

* 배열 : 여러 셀들의 집합

= SUMPRODUCT(A2:A6, B2:B6)

→ A2:A6 은 배열인수1

→ B2:B6 은 배열인수2

 

IFERROR 함수

입력된 수식이나 함수의 결과가 오류가 아니면 수식의 결과값을 출력

입력된 수식이나 함수의 결과가 오류 값이면 내가 원하는 값을 출력 가능

= IFERROR(N5, "수식확인")

→ N5 은 수식이 입력된 셀

→ 수식확인 은 오류가 발생했을 때 표시할 값

* 오류가 없다면 수식의 결과값이 출력됨

 

FIND 함수

긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력

띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분함

= FIND("안녕", A1) → 100

→ 안녕 은 찾을 텍스트

→ A1은 긴 텍스트

→ 결과값 100은 첫번째 "안녕" 텍스트의 위치

 

= FIND("안녕", A1, 101) → 145

→ 안녕 은 찾을 텍스트

→ A1은 긴 텍스트

→ 101 은 문자열을 찾기 시작할 위치 (이 부분은 생략가능한데, 생략하면 첫번째부터 찾음)

→ 결과값 145는 두번째 "안녕" 텍스트의 위치

 

SEARCH 함수

긴 텍스트에서 특정 단어나 문장이 시작하는 위치를 숫자로 출력

띄어쓰기까지 포함하여 문자를 세며 대소문자를 구분하지 않음

함수 원리는 FIND 함수와 동일

 

LEFT / RIGHT 함수

텍스트의 가장 왼쪽 / 오른쪽부터 원하는 문자열까지 추출

= LEFT(B5, 3)

→ B5 는 전체 텍스트

→ 3 은 불러올 문자열 수

 

= RIGHT(B5, 8)

→ B5 는 전체텍스트

→ 8 은 불러올 문자열 수

 

MID 함수

텍스트의 중간 시작 위치부터 원하는 문자열까지 추출

= MID(B5, 5, 3)

→ B5 는 전체 텍스트

→ 5 는 불러올 문자열의 시작 위치

→ 3 은 불러올 문자열의 수

 

DAYS / DATEDIF 함수

시작일에서 종료일까지 날짜의 수를 계산해주는 함수

*단 계산된 값은 종료일을 포함하지 않음

*종료일까지 날짜를 포함해야 할 경우 결과값에 +1 을 해야 함

= DAYS(H3, D3)

→ H3 은 종료일

→ D3 은 시작일

 

= DATEDIF(D3, H3, "D")

→ D3 은 시작일

→ H3 은 종료일

→ D 는 일 수

* D 자리에 넣을 수 있는 값

M : 월 수

Y : 연 수

YM : 올해의 월 수

MD : 이번 달의 일 수

 

YEAR / MONTH / DAY 함수

연도 / 월 / 일 추출해주는 함수

= YEAR(날짜있는 셀) → 연도 추출

= MONTH(날짜있는 셀) → 월 추출

= DAY(날짜있는 셀) → 일 추출