실무자 필수함수 (계산/참조)
분야 상관없이 실무자라면 반드시 알아야 할 엑셀 필수함수! (1/2) - 계산/참조 함수
이 강의에서는 실무에서 가장 자주 쓰이는 9가지 계산·참조 함수와 연산자 사용법을 다룹니다. SUM·AVERAGE·IF·MID·RIGHT·COUNTA·COUNTIF·SUMIF·AVERAGEIF 함수의 핵심 구문을 매출 데이터 예제로 단계별로 정리하고, 마지막에는 수식 안에서 연산자와 숫자를 안전하게 결합하는 방법까지 함께 알아봅니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
실습 가이드
이번 강의에서는 실무자가 반드시 알아야 할 엑셀 필수 함수 20개 중 계산·참조에 해당하는 기초 함수 9개와 연산자 사용법을 정리합니다. 강의에 사용된 각 함수의 보다 자세한 설명은 아래 관련 링크에서 확인할 수 있습니다.
엑셀 필수 함수 1편 - 영상강의
SUM 함수를 사용하여 1월부터 6월까지 각 업체별 총 매출액을 계산합니다. 예제파일의 E2셀에 아래 수식을 입력한 뒤, 자동 채우기로 나머지 행을 채워 마무리합니다.
AVERAGE 함수를 사용하여 6개월간의 각 업체별 월 평균매출액을 계산합니다. 예제파일의 F2셀에 아래 수식을 입력한 뒤, 자동 채우기로 나머지 행을 채워 마무리합니다.
단, AVERAGE 함수는 선택한 범위에 0이 포함되어 있을 경우 0을 포함하여 평균을 계산하므로 주의해야 합니다. 또한 산술평균으로 계산되므로, 상황에 따라 가중평균이 필요할 때는 별도의 공식을 사용합니다.
엑셀 가중평균 계산 공식 바로가기
IF 함수를 사용하여 각 업체별 사용언어를 구분합니다. 업체 국가가 한국이면 "한국어"를 출력하고, 그 외의 국가는 "영어"를 출력합니다.
각 국가별로 더 다양한 언어를 구분하려면 다중 IF 함수를 사용해야 하며, 다중 IF 함수의 자세한 사용법은 IF 함수 상세설명 포스트에서 확인할 수 있습니다.
아래 수식을 예제파일의 N2셀에 입력한 뒤, 아래로 자동 채우기 하여 마무리합니다.
MID 함수를 사용하여 업체의 12자리 고유번호 중 중간에 위치한 4자리를 추출합니다. 고유번호(xxxx-xxxx-xxxx)의 중간 4자리는 첫 번째 붙임표("-") 이후 6번째 자리에서 시작한다는 점에 주의합니다. 아래 수식을 예제파일의 O2셀에 입력한 뒤, 자동 채우기로 마무리합니다.
RIGHT 함수를 사용하여 업체 고유번호(xxxx-xxxx-xxxx)에서 마지막 4자리를 추출합니다. 아래 수식을 예제파일의 P2셀에 입력한 뒤, 자동 채우기로 마무리합니다.
COUNTA 함수는 범위 내에서 빈 셀을 제외한 셀의 개수를 구하는 함수입니다. COUNTA 함수로 조사 대상 업체의 개수를 계산합니다. 아래 수식을 예제파일의 U2셀에 입력합니다.
COUNTIF 함수는 조건을 만족하는 셀의 개수를 구하는 함수입니다. COUNTIF 함수를 사용하여 각 국가별 제조사의 개수를 계산합니다. 아래 수식을 예제파일의 V21셀에 입력한 뒤, 아래로 자동 채우기 하여 마무리합니다.
SUMIF 함수를 사용하여 각 국가별 제조사의 매출 합계를 계산할 수 있습니다. 합계를 구할 매출액 범위는 세 번째 인수로 입력해야 한다는 점에 주의합니다. 아래 수식을 예제파일의 W21셀에 입력한 뒤, 아래로 자동 채우기 하여 마무리합니다.
AVERAGEIF 함수를 사용하여 각 국가별 제조사의 월 평균 매출액을 계산합니다. 단, AVERAGEIF 함수를 사용할 때는 다음과 같은 주의사항이 있습니다.
앞선 AVERAGE 함수와 마찬가지로, AVERAGEIF 함수 또한 조건을 만족하는 값의 산술평균을 계산한다는 점에 주의합니다. 산술평균과 가중평균의 차이는 가중평균공식 포스트에서 자세히 설명드렸습니다.
연산자는 숫자의 대소를 비교하거나 조건의 참·거짓 여부를 판단할 때 사용하는 기호입니다. 연산자는 종류가 매우 다양하며, 사용 시 알아두어야 할 중요 참고사항은 다음과 같습니다.
- 연산자는 텍스트로 취급됩니다. 따라서 수식 안에서는 반드시 큰따옴표(" ") 안에 입력해야 합니다.
- 연산자와 숫자를 함께 사용할 때는, 앰퍼샌드(&) 기호를 사용하여 연산자와 숫자를 분리해 입력하는 습관이 매우 중요합니다.
=COUNTIF(A1:A10,"<10") ' 10 미만인 셀의 개수를 구합니다. 다만 [ "<"&10 ] 형태가 올바른 수식입니다.
=COUNTIF(A1:A10,"<10+10") ' 오류를 반환합니다.
=COUNTIF(A1:A10,"<"&10+10) ' 20 미만인 셀의 개수를 구합니다.
실무에 바로 적용하다가 궁금한 점이 있어 문의 드립니다.
SUMIF 함수 적용하여 국가의 매출액 계산시, raw data가 한 sheet에 연도별로 적용(18년/19년/20년 고객별 국가별 매출액이 혼재) 되어 있을 경우, 특정 연도의 매출액(19년의 독일지역 매출액)만 계산하려면 어떻게 함수 적용을 해야 하나요?
SUMIFS 함수를 사용해보시겠어요?
=SUMIFS(매출액범위, 국가범위, "미국, 연도범위, 2019)
자세한 설명은 아래 링크를 확인해보세요 :)
https://www.oppadu.com/엑셀-sumifs-함수
실무에 바로 적용하다가 궁금한 점이 있어 문의 드립니다.
SUMIF 함수 적용하여 국가의 매출액 계산시, raw data가 한 sheet에 연도별로 적용(18년/19년/20년 고객별 국가별 매출액이 혼재) 되어 있을 경우, 특정 연도의 매출액(19년의 독일지역 매출액)만 계산하려면 어떻게 함수 적용을 해야 하나요?
사용중인 인터넷 브라우저 사양에따라 '뀏쀩뀂'과 같은 알수없는 파일명으로 다운로드 되는경우가 있습니다. 그럴경우 파일명은 올바른 파일명으로 'abc.xlsx' 등으로 바꾼 뒤 다시 실행해보시겠어요?^-^
감사합니다.
강의에 사용된 예제파일은 해당페이지 중간에 있는 다운로드 버튼을 클릭하시면 자동으로 다운로드 됩니다.
또는 아래 링크를 클릭해보시겠어요?
https://www.oppadu.com/download/4532
감사합니다 ^^