엑셀 필수 함수 총정리 1편 (계산/참조함수)
이번 강의에서는 실무자용 엑셀 필수 함수 20개 중 계산/참조에 해당하는 기초함수 9개와 연산자 사용법에 대해 알아봅니다. 이번 강의에 사용된 각 기초함수에 대한 보다 자세한 설명은 아래 관련 링크를 확인해주세요. 🙂
엑셀 필수 함수 목차 바로가기
엑셀 필수 함수 1편 - 영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 실무자 기초함수 20개 [1/2]예제파일[실무기초] 실무자 기초함수 20개 [1/2]완성파일
SUM함수를 사용하여 1월부터 6월까지의 각 업체별 총 매출액을 계산합니다. 예제파일 E2셀에 아래 수식을 입력한 뒤 자동채우기로 계산을 마무리합니다.
=SUM(G2:L2)
SUM 함수를 사용하여 1월~6월의 각 업체별 촐 매출액을 계산합니다. AVERAGE 함수를 이용하여 6개월간의 각 업체별 월 평균매출액을 계산합니다. 예제파일 F2셀에 아래 수식을 입력한 뒤 자동채우기하여 계산을 마무리합니다.
=AVERAGE(G2:L2)단, AVERAGE 함수는 선택된 범위에 0이 포함되어 있을시, 0을 포함하여 평균을 계산하므로 주의해야 합니다. 또한 '산술평균'으로 계산되므로, 상황에 따라 가중평균을 구해야 할 경우 다른 공식을 이용합니다.
엑셀 가중평균 계산 공식 바로가기
AVERAGE 함수를 이용하여 6개월간 월 평균 매출액을 계산합니다. IF 함수를 사용하여 각 업체별 사용언어를 구분합니다. 업체 국가가 한국일 경우 “한국어”를 출력하고 그 외의 국가일 경우 “영어”를 출력합니다.
각 국가별 더 많은 종류의 언어로 구분하려면 다중 IF 함수를 사용해야하며, 다중 IF 함수 사용법은 IF함수 상세설명 포스트에서 자세히 설명드렸습니다.
아래 수식을 예제파일 N2셀에 붙여넣기 한 뒤, 아래로 자동채우기 하여 마무리합니다.
=IF(C2="한국","한국어","영어")
IF 함수를 이용하여 국가별 사용언어를 입력합니다. MID 함수를 사용하여 업체의 12자리의 고유번호 중 중간에 위치한 4자리를 추출합니다. 고유번호(xxxx-xxxx-xxxx)의 중간 4자리는 붙임표(“-”) 이후 6번째 자리에서 시작한다는 점에 주의합니다. 아래 수식을 예제파일의 O2셀에 입력한 뒤, 자동채우기하여 마무리합니다.
=MID(A2,6,4)
MID함수를 사용하여 고유번호의 중간 4자리를 끊어옵니다. RIGHT 함수를 사용하여 업체의 고유번호(xxxx-xxxx-xxxx)에서 우측 4자리를 추출합니다. 아래 수식을 예제파일의 P2셀에 붙여넣기 한 뒤, 자동채우기하여 마무리합니다.
=RIGHT(A2,4)
RIGHT 함수로 고유번호의 끝 4자리를 끊어옵니다. COUNTA 함수는 범위내 빈칸을 제외한 셀의 개수를 구하는 함수입니다. COUNTA 함수로 조사대상업체의 개수를 구합니다. 아래 수식을 예제파일의 U2셀에 입력합니다.
=COUNTA(B2:B35)
COUNTA 함수로 조사업체의 개수를 셉니다. COUNTIF 함수는 조건을 만족하는 셀의 개수를 구하는 함수입니다. COUNTIF 함수를 사용하여 각 국가별 제조사의 개수를 게산합니다. 예제파일의 V21셀에 아래 수식을 붙여넣기 한 뒤, 아래로 자동채우기 하여 마무리합니다.
=COUNTIF(C:C,U21)
COUNTIF 함수를 이용하여 국가별 업체개수를 구합니다. SUMIF 함수를 사용하여 각 국가별 제조사의 매출합계를 계산할 수 있습니다. 합계를 구할 "매출액 범위를 3번째 인수"로 입력해줘야 한다는 점을 주의합니다. 아래 수식을 예제파일의 W21셀에 입력한 뒤, 아래로 자동채우기 하여 마무리합니다.
=SUMIF(C:C,V21,E:E)
SUMIF 함수를 사용하여 국가별 매출액 합계를 계산합니다. AVERAGEIF 함수를 사용하여 각 국가별 제조사의 월 평균매출액을 계산합니다. 단, AVERAGEIF 함수를 사용시 주의사항이 있습니다.
이전 AVERAGE 함수와 마찬가지로, AVERAGEIF 함수 또한 조건을 만족하는 값의 '산술평균'을 계산한다는 점을 주의해서 사용합니다. 산술평균과 가중평균의 차이점은 가중평균공식 포스트에서 자세히 설명드렸습니다.
=AVERAGEIF(D:D,V29,E:E)
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
감사합니다 ^^