자주쓰는 엑셀함수 예제를 통해 쉽게 알아보자

오늘은 엑셀함수중 ★ SUMIF  &DSUM & COUNTIFS VLOOKUp & HLOOKUP & IFERROR
알아볼것데요 따라하면 어렵지 않습니다

🟧 SUMIF함수 
SUM과 IF가 합쳐진 형태이다. “만약(IF) 어떤 조건을 만족하면 SUM하라”는 의미 입니다

🟧  SUMIF  &DSUM
  ▶ SUMIF함수 : SUM과 IF가 합쳐진 형태이다. “만약(IF) 어떤 조건을 만족하면 SUM하라”는 의미이다.
  ▶   DSUM  데이터베이스(database)에서 조건(criteria)에 맞는 항목(field)의 합계를 구해준다.

SUMIFS 함수 : 시트의 범위내에서 단일 합계를 구할때 많이 쓰임

예제를 통해서 배워보도록 하겠습니다.

★ SUMIF (range,criteria,[sum_range])
    range 조건을 적용할 셀범위
    criteria 합계를 구할조건
    sum_rangel 합계를 구할 범위
   조건(criteria)에 맞는 범위 값을 더함

♠ 마케팅팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H6,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H6,$F$6:$F$25)

♠ 인사팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H10,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H10,$F$6:$F$25)

♠  회계팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H12,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H12,$F$6:$F$25)

마케팅팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H6,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H6,$F$6:$F$25)

인사팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H10,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H10,$F$6:$F$25)

회계팀
직무만족도 (합계) =SUMIF($C$6:$F$25,H12,$E$6:$E$25)
  평가            =SUMIF($C$6:$F$25,H12,$F$6:$F$25)

★ DSUM(database, field, criteria) 
인수 :
– database : 값을 찾을 셀 범위
– field : 합계를 구할 필드
– criteria : 조건을 설정할 범위

마케팅팀
직무만족도  =DSUM($C$5:$F$25,$J$16,$H$16:$I$17)
 평가        =DSUM($C$5:$F$25,$K$16,$H$16:$I$17)

🟧  COUNTIFS
 이용하면 여러 개의 조건을 만족하는 셀의 개수를 구할 수 있다.
COUNTIF함수의 IF에 S가 붙은 형태이다.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
여러 조건(criteria)에 맞는 셀의 개수를 구함

인수 :
– criteria_range1 : 첫번째 조건을 적용할 범위
– criteria1 : 개수를 구할 첫번째 조건
– criteria_range2 : 두번째 조건을 적용할 범위
– criteria2 : 개수를 구할 두번째 조건
criteria_range, criteria 는 최대 127개까지 지정할 수 있다.

/▶  COUNT
직무만족도 =COUNT(E6:E25)  / 평가       =COUNT(F6:F25)

▶   COUNTA    
직무만족도  =COUNTA(E6:E25) / 평가        =COUNTA(F6:F25)

▶   COUNTF
직무만족도 =COUNTIF(E6:E25,">=4.9")
평가       =COUNTIF(F6:F25,">=52.4")

▶   COUNTIFS
직무만족도 =COUNTIFS(E6:E25,">=4.9",E6:E25,"<7")
평가  = =COUNTIFS(F6:F25,">=52.4",F6:F25,"<90")

표에서 값을 찾을 때 가장 많이 사용하는 함수가 VLOOKUP인데, 이 함수는 결정적인  단점이 있다.

아래 그림과 같이 VLOOKUP함수의 첫번째 인수에 해당하는 “키값(품목코드)”이 반드시 “찾는 값(품명,단가)”의 왼쪽에 있어야 한다.

lookup_value : 범위에서 원하는 값을 찾기 위한 키값
lookup_array : 값을 찾을 범위
return_array: 찾은 값을 반환할 범위
if_not_found : (생략가능) 값을 찾지 못했을 때 표시할 텍스트
match_mode : (생략가능) 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정
0 정확히 일치하는 값, 찾을 수 없는 경우 #N/A 를 반환
-1 정확히 일치 하는 값, 찾을 수 없는 경우 다음으로 작은 항목을 반환
1 정확히 일치 하는 값, 찾을 수 없는 경우 다음으로 증가 하는 항목을 반환
2 와일드 카드로 검색
search_mode : (생략가능)검색모드
1 첫 번째 항목부터 검색(기본값)
-1 마지막 항목부터 역방향 검색
2 오름차순 으로 정렬 되는 lookup_array에 의존 하는 이진 검색 , 정렬 되지 않은 경우 잘못 된 결과가 반환됨
-2 내림차순 으로 정렬 되는 lookup_array에 의존 하는 이진 검색, 정렬 되지 않은 경우 잘못 된 결과가 반환됨

🟧  VOOKUP (lookup_value,table_array,col_index_num,[range_lookup])
VOOKUP 함수 : 특정코드에 맞는 데이터의 내용이나 값을 빠르고 쉽게 가져와서 처리
범위(table_array)의 첫번째 열을 수직으로 내려가면서 키값(lookup_value)를 찾은 다음,
같은 행에 있는 지정된 열(col_index_num)의 값을 반환한다

인수 :
– lookup_value : 범위에서 원하는 값을 찾기 위한 키값
– table_array : 값을 찾을 범위
– col_index_num : 값을 찾을 범위에서 가져올 값이 있는 열의 위치
– range_lookup : 일치하는 키값을 찾을 것인지 근사값을 찾을 것인지 결정(TRUE-근사값,FALSE-일치하는 값)

🟧   IFERROR 함수
IF와 ERROR가 합쳐진 것이다. 만약(IF)에 에러(ERROR)가 발생하면 어떻게 처리하라는 의미이다.

IFERROR(value, value_if_error)
수식이 오류이면 사용자가 지정한 값을 반환한다.
*에러 일때 표시되는 오류 유형 : #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? , #NULL!

인수 :
– value : 입력된 수식
– value_if_error : 식의 결과가 에러이면 대신 표시할 값
수식에 오류가 있을 때 “수식오류”라고 표시하기

다음과  같이 이익율을 구하는 수식(이익을 판매실적으로 나눔)이 있다고 할 때
=D5/C5

계산할 셀에 숫자 대신에 문자가 들어간다든지
“나눗셈 수식”인데 분모가 0이면
다음 그림처럼 #VALUE!, #DIV/0! 처럼 알수 없는 값이 표시된다.
수식에 오류가 있다는 뜻이다.

평가/직무만족도 =F14/E14  
IFERROR 적용   =IFERROR(F14/E14,0)

🟧 목표값 찾기와 조견표 작성 방법
  L   INDEX 함수는 참조할 셀 범위에서 행과 열 번호에 해당하는 값을 나타냅니다. 

​산출된 년,월,일 기간확인 
년 =DATEDIF(H9,I9,"y")
월 = =DATEDIF(H9,I9,"ym")
일 =DATEDIF(H13,I13,"md")

산출된 년월일 평균
=DATEDIF(0,AVERAGE(I9:I13-H9:H13),"y")
=DATEDIF(0,AVERAGE(I9:I13-H9:H13),"ym")
=DATEDIF(0,AVERAGE(I9:I13-H9:H13),"md")
Ctrl +Shift +Enter 키를 눌러 배열수식 


최장기간 산출하기 
=INDEX(M9:M13,MATCH(MAX(I9:I13-H9:H13),I9:I13-H9:H13,0))
L M 근무기간 / I 퇴사기간 H 입사기간 


최장기간 사람 산출하기
=INDEX(G9:G13,MATCH(G16,M9:M13,0))
L G열 이름 / M 근무기간 


최단기간 산출하기 
=INDEX(M9:M13,MATCH(min(I9:I13-H9:H13),I9:I13-H9:H13,0))
L M 근무기간 / I 퇴사기간 H 입사기간 


최단기간 사람 산출하기 
=INDEX(G9:G13,MATCH(G18,M9:M13,0)) 
L G열 이름 / M 근무기간