엑셀 함수 정리 (실무에서 사용 함수)

실무에서 필수적으로 사용되는 엑셀 함수 정리 예제에 대해 포스팅합니다.
엑셀에는 많은 함수들 있습니다.

이 많은 함수를 전부 사용한다는 건 불가능하고, 알고 있을 필요도 없습니다.
엑셀 프로그램을 시험을 통과하기 위한 것이 아니기에 실무에서 사용되는 함수들만 정리했습니다.
엑셀에는 많은 함수들 있습니다.

엑셀함수정리

함수 사용은 셀에 직접 수식을 입력하여 적용하는 방법, 상단 메뉴에 있는 함수 마법사 사용하는 방법이 있습니다.
함수 적용에 어느 정도 숙달이 된다면 직접 수식 입력하여 함수 적용을 추천드립니다.
단, 합계 및 평균값을 구하기 위한 함수 사용은 리본 메뉴에 함수 마법사 적용이 편리합니다.

함수 설명을 위한 예시.

1. 사칙연산 함수

▶ SUM함수 : 합계 값을 구할 때 사용합니다.
수식 : =SUM(A1:C5) / =SUM(A1, A2, A3)
수식(+사용) : =A1+A2+A3

▶ SUMIF함수 : 조건에 일치하는 합계/ 전체 합계가 아니고 부분적으로 합계를 구할 때 사용합니다.
수식 : SUMIF(합계를 위한 비교대상 범위, 조건, 합계 대상의 범위)

sumif

▶SUBTOTAL함수 : 필터 적용 시 사용, 보이는 값에(부분 값) 대해 합계 값을 구합니다.
수식 : =SUBTOTAL(9, A1:A5)
가장 많이 사용되는 인수 "9" 합계, "1" 평균입니다.

▶PRODUCT함수 : 곱하기 값을 구할 때 사용합니다. (보통 *로 사용)
수식 : =PRODUCT(A1:C5) / =PRODUCT(A1, A2, A3)
수식(*사용) : =A1*A2*A3

▶ "/" 나누기 값을 구할 때 사용합니다.
수식 : =A1/A2

2. 논리 함수

▶ IF함수 : 조건에 맞으면 참값을 표시하고 나머지는 거짓 값을 표시합니다.
수식 : =IF(조건, 참값(데이터), 거짓 값(데이터)
예시 ) 브라의 재고 수량이 50 미만 이면 "적정재고" 아니면 "악성 재고"
=IF(D <50, "적정재고", "악성 재고")
결과 값은 "적정재고"로 표시됩니다.

▶ AND함수 : 조건을 모두 만족하는 값을 표시합니다. (단독으로 사용 안 하고 IF함수의 조건에 넣어서 사용합니다)
수식 : =AND(조건 1, 조건 2, 조건 3)
=IF(AND(D <50, D=50), "적정재고", "악성 재고")

▶ OR함수 : 조건 중 하나만 만족해도 되는 값을 표시합니다.
수식 : =OR(조건 1, 조건 2 등)
=IF(OR(D <50, D=50), "적정재고", "악성 재고")

3. 문자열 함수

▶MID함수 : 문자열 중 시작 위치에서 끝 위치까지 지정하여 발췌를 할 수 있습니다.
수식 : =MID(문자열 참조하는 셀, 문자열 시작 위치, 시작 위치에서 가져올 문자수)
예시 ) 셀(A1) 가나 다라마 → =MID(A1,2,2) → 결괏값은 나다.

▶LEFT함수 : 문자열 중 왼쪽으로부터 숫자만큼 문자 발췌를 할 수 있습니다.
수식 : =LEFT(문자열 참조하는 셀, 발췌할 문자 수)
예시 ) 셀(A1) 가나 다라마 → =LEFT(A1,2) → 결과 값은 가나 입니다.

▶ RIGHT함수 : 문자열 중 오른쪽으로부터 숫자만큼 문자 발췌를 할 수 있습니다.
수식 : =RIGHT(문자열 참조하는 셀, 발췌할 문자 수)
예시 ) 셀(A1) 가나 다라마 → =RIGHT(A1,2) → 결과 값은 라마  입니다.

▶ LEN함수 : 문자열의 개수를 구합니다.
수식 : LEN(문자열 참조하는 셀)
예시 ) 셀(A1) 가나 다라마 → =LEN(A1) → 결과 값은 5 입니다.

4. 통계 함수

▶ AVERAGE함수 : 평균값을 구할 때 사용합니다.
수식 : =AVERAGE(A1:C5) / =AVERAGE(A1, A2, A3)

▶ MAX함수 : 최댓값을 구할 때 사용합니다.
수식 : =MAX(A1:C5) / =MAX(A1, A2, A3)

▶ MIN함수 : 최솟값을 구할 때 사용합니다.
수식 : =MIN(A1:C5) / =MIN(A1, A2, A3)

▶ RANK함수 : 순위를 구할 때 사용합니다.
수식 : =RANK(참조하는 셀, 계산할 값의 범위, 1 오름차순 또는 0 내림차순)
예시 ) 재고 수량에 대한 순위를 구했습니다. 오름차순 정렬 기준으로 전체 재고 수량 중 적은 수량으로 2등입니다.

rank함수

▶ COUNT함수 : 지정한 범위 내에 숫자가 있는 셀의 수를 세어 결과 값으로 보여줍니다.
수식 : =COUNT(셀 범위)

▶ COUNTA함수 : 지정된 범위 내에서 빈칸이 아닌 셀의 수를 결괏값으로 보여줍니다.
셀이 숫자로 채워져 있든 텍스트로 채워져 있든 상관없습니다.
수식 : =COUNTA(셀 범위)

▶ COUNTIF함수 : 지정된 범위 내에서 조건에 맞는 값을 가진 셀의 개수를 구할 수 있습니다.
조건은 하나만 설정할 수 있고, 조건은 숫자나 텍스트, 수식 등이 가능합니다..
대신 수식이나 텍스트로 조건을 만들 경우 조건 부분에 “”(큰 따옴표)를 넣어주어야 합니다.
수식 : =COUNTIF(셀 범위 지정, 조건 지정) / =COUNTIF(A1:A5, "팬티") → 결괏값은 "2"입니다.

▶ COUNTIFS함수 : COUNTIF와 같은 특징을 가지는데요,
조건을 하나만 지정할 수 있는 COUNTIF와는 다르게 두 개 이상의 조건을 지정하고, 그에 맞는
결과 값을 얻을 수 있습니다.
수식 : =COUNTIFS(셀 범위 지정, 1번째 조건, 셀 범위 지정, 2번째 조건,....)
=COUNTIFS(A1:A5, "팬티", B1:B5, ">200") → 결괏값은 "1"입니다.

5. 날짜 함수

▶ DATEIF함수 : 날짜를 경과일 계산 할 때 사용합니다.
수식 : =DATEIF(시작일자, 종료일자, "단위")

dateif함수

▶ TEXT함수 : 날짜를 텍스트 변환할 때 사용합니다.
수식 =text(참조 할셀 , "yy-mm-dd") 결괏값은 21-07-27
예시) =text(참조할 셀, "yyyy-mm-dd-aaa") 결괏값은 2021-07-27-화
" " 따옴표 안에 필요한 날짜 형식에 맞춰 텍스트로 변환하여 사용할 수 있습니다.

6. 가져오기 함수

가져오기함수 예시

▶ VLOOKUP함수 : 입력된 데이터에서 열 번호에서 필요한 데이터 값만 가져오는 함수입니다. (블로그 내 참조 바랍니다)
수식 : =VLOOKUP(찾을 데이터 값 범위, 기준표, 열 번호, 찾는 옵션 TRUE 근사치, FALSE 정확한 값)

vlookup함수

▶ HLOOKUP함수 : 입력된 데이터에서 행 번호에서 필요한 데이터 값만 가져오는 함수입니다.
수식 : =HOOKUP(찾을 데이터 값 범위, 기준표, 행 번호, 옵션 TRUE 근사치, FALSE 정확한 값)

▶ INDEX함수 : 배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾아 표시합니다.
수식 : INDEX(참조할 셀 범위, 행 번호, 열 번호) / 다른 함수와 응용하여 사용됩니다.

▶ MATCH함수 : 배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾아 표시합니다.
수식 : =MATCH(찾을 값, 참조할 셀 범위, 찾는 옵션 0 정확히 일치하는 위치 값(행, 열) )
다른 함수와 응용하여 사용됩니다.

7. 절상, 절사, 사사오입 함수(블로그 내 엑셀 반올림 함수, 엑셀 버림 함수 참조해주세요)

▶ ROUND 함수(절상, 사사오입) : 지정한 자릿수에서 사사오입 적용하여 반올림하는 함수입니다.
수식 : =ROUND(반올림할 수, 반올림할 자릿수)

▶ ROUNDUP함수(절상, 무조건 반올림) : 지정한 자릿수에서 무조건 반올림 함수입니다.
수식 : =ROUNDUP(반올림할 수, 반올림할 자릿수)

▶ ROUNDDOWN(절사, 무조건 버림) : 지정한 자릿수로 내림(0에 가까운 방향으로 내림)하는 함수입니다.
수식 : =ROUNDDOWN(버림 할 수, 버림 할 자릿수)

▶ INT함수 : 소수점을 버리는 함수입니다.
수식 : =INT(값)

▶ TRUNC 함수 : 지정한 자릿수만큼 버림 하는 함수입니다.
수식 : =TRUNC(값, 버림 할 자릿수)