저번 포스팅에서 수식, 함수의 기본 원리에 대해 알아보았으니!
이번 포스팅부터는 본격적으로 자주 사용하는 엑셀 함수에 대해 배워보도록 하겠습니다!
① AVERAGE 함수
AVERAGE(대상1, 대상2, 대상3, …)
AVERAGE 함수는 인수들의 평균을 구하는 함수입니다!
예를 보시죠!
화장품 제조판매업체 기술분류별 화장품 분야 연구개발비 데이터를 공공 데이터 포탈에서 가져왔는데요!
평균이 없으니 연도별 연구 개발비의 평균을 구해보도록 할게요!
이 때 쓰는 함수가 AVERAGE함수입니다.
B7의 값을 구하고 자동 채우기를 해서 표를 완성해 볼게요!
AVERAGE 함수로 연도별 평균을 순식간에 입력해 보았습니다
①-(1) AVERAGEA 함수
AVERAGE 함수와 굉장히 비슷해 보이지만 AVERAGEA 함수는 조금 다른데요!
AVERAGE 함수는 대상 영역에서 숫자 셀만의 평균을 구합니다!
중간 중간 문자 데이터가 입력된 셀로 평균 대상에 포함시키려면 AVERAGEA 함수를
사용해야 합니다.
AVERAGEA 함수를 사용하기 위해 위의 데이터를 조금 변형시켜보았습니다!
이렇게 표 중간중간 문자 데이터를 입력하여 표를 변형시켰는데요!
AVERAGE 함수는 숫자 셀만의 평균을 구하기 때문에 예를 들어
2016년의 연구개발비 평균을 구하면 문자 데이터를 무시하고
소재기술과 제형기술을 더해 2로 나눌 것입니다.
하지만 AVERAGEA 함수를 사용하면 문제 데이터가 입력된 셀도 평균 대상에 포함시키므로
소재기술과 제형기술을 더한 후 4로 나눈 값이 반환됩니다!
이렇게 말이죠!!
①-(2) AVERAGEIF 함수
AVERAGEIF(비교 대상,”조건식”,평균을 구할 대상)
특정 범위에서 지정한 조건에 만족하는 셀의 평균을 구한다.
예제를 통해 확인해보시죠!
예를 들어 이 표에서 ‘판매 여부가 X에 해당하는 제품의 정상가격의 평균’을 구하고 싶을 때는 어떻게 해야할까요!
이렇게 특정 범위에서 지정한 조건에 만족하는 셀의 평균을 구할 때 쓰는 함수가
바로 AVERAGEIF입니다!
AVERAGEIFS(평균을 구할 대상, 비교대상1,”조건식1”,비교대상2,”조건식2”,…)
조건을 비교할 대상 범위를 F2:F17로 잡고
조건은 X를 따로 입력해 지정하였구요
평균을 구할 대상은 정상가격인 D2부터 D17로 잡았습니다!
판매여부가 X인 제품들의 정상 가격의 평균이 구해질까요?
이렇게 나오네요!!
①-(3) AVERAGEIFS 함수
그런데 조건이 하나라는 법은 없잖아요? 조건이 하나 이상일 때는 어떻게 해야할까요?
이럴 때 쓰는 함수가 바로 AVERAGEIFS 함수입니다!
여러 개의 조건을 써도 되니까 뒤에 S가 붙은 것 같네요!
방금 예제에서
위의 같은 예시에서 판매여부는 X[조건1]이고 할인판매가는 40000[이 넘는[조건2] 제품들의 정상 가격의 평균을 구하고 싶다면 어떻게 함수를 사용해야 할까요?
AVERAGEIF 함수에서는 조건을 비교할 대상을 먼저 입력했다면
AVERAGEIFS 함수에서는 평균을 구할 대상을 먼저 입력하는 것이 큰 차이겠죠!
② COUNT 함수
COUNT(대상1, 대상2, 대상3…)
COUNT함수는 지정한 영역에서 숫자 셀의 개수를 구하는 함수입니다
②-(1) COUNTA 함수
COUNTA(대상1, 대상2, 대상3…)
COUNTA 함수는 지정한 영역에서 빈 셀을 제외한 값이 입력된 셀의 개수를 구하는
함수입니다.
②-(2) COUNTBLANK 함수
COUNTBLANK 함수는 지정한 영역에서 빈 셀의 개수를 구하는
함수입니다!
한번 예제를 통해 알아보도록 하겠습니다, 위의 데이터를 살짝 변형해보았어요!
이렇게 상황에 맞게 COUNTA, COUNT, COUNTBLANK 함수를 사용할 수 있습니다!
②-(3) COUNTIF, COUNTIFS 함수
앞서 AVERAGE 함수에서도 봤듯이 조건을 만족하는 개수를 구하고 싶을 때도 있잖아요!
그럴 때는 앞에서 본 것과 비슷하게 COUNTIF 함수를 쓰면 되고 조건이 한 개 이상일 때는 COUNTIFS 함수를 사용하면 됩니다!
COUNTIF(비교 대상,”조건식1”)
COUNTIFS(비교 대상1,”조건식1”,비교 대상2,”조건식2”,…)
예제를 봅시다!
강원도_강릉시_방문판매업소 현황을 살짝 변형해보았는데요!
신고일자가 3월 이후인 가게의 수를 세고 싶다고 가졍해보죠!
그때 쓸 수 있는 함수가 바로 COUNTIF 함수입니다!
결과를 볼까요?
완벽하게 12라는 숫자가 결과값으로 반환되었네요!
③ SUM 함수
SUM은 저번 포스팅에서도 봤듯이 인수들의 합계를 구하는 함수입니다
③-(1) SUMIF 함수
SUMIF(비교 대상,”조건식”,합을 구할 대상)
SUMIFS(합을 구할 대상, 비교 대상1,”조건식1”,비교 대상2,”조건식2”,…)
아까 AVERAGEIF와 AVERAGEIFS와 공식이 비슷하죠?
평균을 구하는 것과 합을 구하는 것의 차이라고 보시면 됩니다!
+) 추가로 화면에 보이는 셀만으로 전체 개수와 합계를 구하는 함수에 대해 다루어보겠습니다
바로 SUBTOTAL이라는 함수인데요
숨겨진 셀을 제외하고 화면에 보이는 셀만으로 평균, 개수, 합계 등의 계산식을 구하는 함수입니다.
이런 데이터가 있다고 가정해볼게요!
SUM 함수를 이용하여 계산한 합은 35,200이에요!
그런데 6행을 숨기고 싶어서 6행만 숨기기를 해볼게요
6행을 선택하고 마우스 오른쪽 버튼을 눌러서 숨기기를 누르면 됩니다
그리고 다시 SUM 함수를 써서 계산해볼게요!
이렇게요! 6행은 포함되지 않고 계산결과가 나올까요?
아닙니다! 숨겨진 행까지 다 포함하여 원래의 결과가 나오는 것을 볼 수 있죠?
그러면 숨겨진 행은 빼고 화면에 보이는 셀만으로 합계를 구하려면 어떤 함수를 써야할까요?
그때 쓰는게 바로 SUBTOTAL 함수입니다
SUBTOTAL(함수 번호,참조 대상1,참조 대상2,…)
이렇게 쓰는데요!
‘함수 번호’라는 인수는 이 인수에 어떤 숫자를 적용하느냐에 따라 합계를 구하는 함수를 쓸지 개수를 구하는 함수를 쓸지가 달라지는 거에요!
또한 번호에 따라 숨겨진 값을 포함할지 아니면 무시할지도 나뉘는데요
9을 입력하면 SUM함수를 구하면서 숨겨진 값을 포함하고
109을 입력하면 SUM함수를 구하면서 숨겨진 값을 무시해요
이렇게 말이죠!!
그럼 오늘은 AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS
COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS
SUM, SUMIF, SUMIFS
SUBTOTAL 함수에 대해 알아보았습니다!!
가장 기본적인 함수들이니 꼭 기억해두시길 바라요!!!
'Data analysis story' 카테고리의 다른 글
| [엑셀] 실무에서 자주 사용하는 엑셀 함수 익히기 ③ (AND, OR, MAX, MIN, MEDIAN, LARGE, SMALL, TEXT, VALUE 함수) (0) | 2020.03.02 |
|---|---|
| [엑셀] 실무에서 자주 사용하는 엑셀 함수 익히기 ② (IF 함수, 다중 IF 함수, IFERROR 함수) (0) | 2020.03.02 |
| [엑셀] 수식, 함수 원리 이해하기 [입력 규칙, 연산자, 상대참조, 절대참조 등] (0) | 2020.03.01 |
| [엑셀]데이터 입력 팁에 관한 모든 것! (2) | 2020.03.01 |
| [엑셀] 데이터 표시형식의 모든 것! (0) | 2020.02.29 |
