[요점만 집어보는 실무 엑셀] 엑셀 함수 종류 및 쓰는법

#HLOOKUP

수식: =HLOOKUP("1분기",D4:F5,2)

원하는 열의 정보를 원할 때 쓰는 함수.

"1분기"라는 글자가 들어간 열의 정보를 원한다, D4:F5 데이터가 위치해있는 범위, 2 나타낼 정보의 행위치.

표에 있는 1분기라는 열의 두 번째 행의 정보를 표시한다.

 

#INDEX

수식: =INDEX(D4:F6,3,1)

D4:F6 범위의 3번째 행, 1번째 열의 정보를 표시한다.

 

#MATCH

수식: =MATCH(G5,F5:F7,0)

G5: 원하는 데이터

F5:F7: 범위

1 : 보다 작음

0 : 같음

-1 : 보다 큼

범위 안의 원하는 데이터가 있는 행의 위치를 알고 싶을 때 쓴다 85보다 같은 경우 0을 넣고 85보다 작은 데이터의 경우 1 85보다 큰 경우 -1을 입력한다.

 

#VLOOKUP

수식: =VLOOKUP("1분기",B2:C4,2)

"1분기" : 구하고자 하는 목표.

B2:C4 : 범위.

2 : 데이터가 위치한 열 번호.

VLOOKUP의 단점은 구하고자 하는 부분이 반드시 앞쪽에 위치해야 한다는 것이다. 이런 단점을 보안하기 위해 위에서 알아보았던, INDEX와 MATCH를 복합적으로 쓰는 것이다.

만약 우표의 열의 위치가 앞뒤로 바뀌었을 경우를 생각해보자.

위와 같이 응용하면 된다.

 

#SUMIF

수식: =SUMIF(C2:C5,"신입",D2:D5)

원하는 항목만 골라서 더하고 싶을 때 쓴다.

C2:C5 : 항목이 위치한 열의 범위.

"신입" : 항목.

D2:D5 : 더할 데이터가 위치한 열의 범위.

 

#SUMIFS

수식: =SUMIFS(B3:B10,C3:C10,"=신입*",D3:D10,"서울")

SUMIF의 경우 조건이 하나만 선택 가능하지만, SUMIFS의 경우 여러 가지 조건을 설정할 수 있다.

B3:B10 : 더할 데이터 범위

C3:C10 : 첫 번째 조건 범위.

"=신입*": 첫번째 조건.

D3:D10 : 두 번째 조건 범위.

"서울": 두번째 조건.

수식 사용 시 신입 중 서울 거주자의 수 15명의 표시된다.

 

#SUMPRODUCT

수식: =SUMPRODUCT(B3:B7,C3:C7)

각 해의 숫자들을 곱한 것을 모두 더한 값을 구할 때 쓴다.

B3:B7 : 곱할 첫 번째 데이터들의 범위.

C3:C7 : 곱할 두 번째 데이터들의 범위.

 

#AVERAGE

수식: =AVERAGE(B2:B6)

B2:B6 : 원하는 데이터들이 위치한 셀의 범위.

평균을 구할 때 쓴다.

 

#AVERAGEIF

수식: =AVERAGEIF(C2:C5,"서울",B2:B5)

원하는 조건을 가진 항목들의 평균을 구할 때 쓴다.

C2:C5 : 원하는 조건이 위치한 범위.

"서울": 원하는 조건.

B2:B5 : 평균을 낼 데이터들이 위치한 범위.

 

#=AVERAGEIFS

수식: =AVERAGEIFS(B3:B10,C3:C10,"=경력*",D3:D10,"서울")

조건이 하나 이상인 항목의 평균 구하는 법.

B3:B10 : 평균을 구할 데이터들이 위치해 있는 범위.

C3:C10 : 첫 번째 조건이 위치한 범위.

"=경력*": 첫번째 조건.

D3:D10 : 두 번째 조건이 위치한 범위.

"서울" : 두번째 조건.

수식은 경력이면서 서울에 사는 사람들의 평균수로 결괏값은 16이다.

 

#COUNT

수식: =COUNT(B2:D10)

선택된 범위의 셀 중 숫자가들어간 셀의 수.

 

#COUNTA

수식: =COUNTA(B2:D10)

선택된 범위의 셀중 데이터가 들어간 셀의 수.

 

#COUNTBLANK

수식: =COUNTBLANK(B2:D10)

선택된 범위의 셀 중 데이터가 없는 셀의 수.

 

#COUNTIF

수식: =COUNTIF(B2:D10,"<15")

특정한 조건을 만족하는 셀의 수.

B2:D10 : 범위.

"<15": 조건.

 

#COUNTIFS

수식: =COUNTIFS(B2:B10,"<15",D2:D10,"서울")

여러 조건을 만족하는 셀의 수.

B2:B10 : 첫 번째 조건이 위치한 셀의 범위.

"<15" : 첫 번째 조건.

D2:D10 : 두 번째 조건이 위치한 셀의 범위.

"서울": 두번째 조건.

15보다 크면서 서울을 포함한 셀의 수.

 

#FREQUENCY

수식: =FREQUENCY(B2:B5,D2:D4)

빈도를 나타내는 함수.

B2:B5 : 목표하는 데이터들의 범위.

D2:D4 : 빈도를 구할 각 데이터들.

배열이기 때문에 수식 입력 후 CTRL+SHITR+ENTER을 눌러야 한다.

 

#RANK

수식:=RANK(5,A1:A8)

5: 몇 번째로 큰 숫자인지 구할 데이터.

A1:A8 : 순위를 구할 범위.