SUMIF함수, RANK함수 등 엑셀함수 및 그래프, 단축키 등 유용한 엑셀 기능

SUMIF함수, RANK함수 등 엑셀함수 및 그래프, 단축키 등 유용한 엑셀 기능

직장생활을 하다 보면 수치 관련 데이터를 정리할 일이 반드시 있습니다. 이 때 빠르고 손쉽게 사용할 수 있는 프로그램이 바로 엑셀인데요. 엑셀은 많은 기능들 때문에 이용에 애를 먹기도 하지만 잘 알아두면 데이터 정리에 도움을 주어 직장인의 필수 프로그램으로 알려져 있습니다.

 

이번 포스팅에서는 익혀두면 직장인들에게 도움이 되는 엑셀함수와 함께 그래프, 단축키 등 유용한 엑셀 기능까지 살펴보겠습니다.

 대신 Balance 핵심정리 

∙ 엑셀함수 사용하기

 - SUM : 수치의 총합을 구하는 함수

 - SUMPRODUCT : 곱한 값의 합을 구하는 함수

 - RANK : 원하는 범위의 순위를 매기는 함수

 - COUNTIF : 전체 목록에서 선택한 항목만 골라, 해당 항목의 개수를 알려주는 함수

 - SUMIF : 선택한 항목의 수치만 계산하여, 그 총합을 구하는 함수

 

∙ 그 외 유용한 엑셀 기능

 - 엑셀 그래프 만들기

  : 막대그래프 – 범위 지정 → 조건부서식 → 데이터막대

  : 엑셀 차트 – 범위 지정 → 키보드의 'F11'

 - 엑셀 단축키

  : ▲F7 – 맞춤법 검사 ▲F11 – 지정 범위 차트로 만들기 ▲F12 – 다른 이름으로 파일 저장

  : ▲Ctrl + L – 표 만들기 ▲Ctrl + N – 새로운 문서 생성 ▲Ctrl + S – 파일 저장

   ▲Ctrl + Z – 실행 취소 ▲Ctrl + 0(숫자) – 열 숨기기 ▲Ctrl + 9 – 행 숨기기

   ▲Ctrl + Space Bar – 열 전체 선택

  : ▲Shift + F3 – 함수 마법사 ▲Shift + F9 – 행 전체 선택

  : ▲Ctrl + Shift + +(플러스 기호) – 셀 삽입 ▲Ctrl + Shift + F12 – 인쇄하기

 엑셀함수 활용하기 

함수는 '엑셀'하면 가장 먼저 떠오르는 기능입니다. 그 만큼 종류도 많고 활용범위도 높죠. 엑셀이 가지고 있는 함수 기능은 300개가 넘는데요. 워낙 함수의 종류가 많고 각 함수의 사용 방법도 다르다 보니 엑셀 초보자들이 가장 어려워하는 기능 중 하나입니다.

 

하지만 엑셀함수는 알면 알수록 업무에 큰 도움을 주는 기능인데요. 직장인들이 유용하게 사용할 수 있을만한 몇 가지 함수 기능의 사용법을 알아보겠습니다.

 

수치의 총합을 구하는 'SUM'

먼저 수치의 총합을 구하는 'SUM'함수를 살펴볼까요? 'SUM'은 행 또는 열의 총합을 구하는 가장 기초적인 함수인데요. 함수의 기초가 되는 만큼 사용 빈도도 굉장히 높습니다. 특히 매월 직원이 기록한 실적의 총합을 구하는 등 월별 실적 현황표를 만들 때 유용하게 사용할 수 있죠.

 

함수 삽입 클릭

우선 총합의 값을 기입할 셀을 선택한 후 '함수 삽입(fx)' 버튼을 클릭합니다.

 

SUM 선택

'함수 마법사' 창이 뜨면 '수학/삼각' 범주에서 'SUM'을 선택한 뒤 '확인'을 클릭합니다.

 

범위 지정

다음으로 '함수 인수'의 'Number1' 칸에 합계를 구하고자 하는 범위를 지정합니다. 위의 사진에서처럼 11/1~11/8 동안 팔린 사과의 총 개수를 구하고 싶다면 B2~I2까지의 셀을 드래그하여 선택한 뒤 '확인'을 클릭합니다.

 

다른 칸 합계 구하기

'확인'을 클릭한 후에는 해당 셀의 총합계가 나오는데요. 나머지 셀의 총합계도 구하고 싶다면 값을 구하고자 하는 셀까지 드래그 하면 수치가 자동으로 채워집니다.

 

위의 표를 예로 들어볼까요? 만약 사과뿐만 아니라 포도, 귤, 딸기, 수박이 지난 8일간 얼마나 팔렸는지 알고 싶다면 이미 값을 구한 J2 셀을 클릭하여 J6 셀까지 그대로 드래그 하면 되는데요. 드래그를 통해 선택한 모든 셀에 자동으로 수치가 채워집니다.

 

곱한 값의 합계를 구하는 'SUMPRODUCT'

'SUMPRODUCT'는 합을 구하는 함수라는 점에서 'SUM'과 유사하지만 곱한 값의 합을 구한다는 점이 다른데요. 이 함수는 매달 발생하는 매출의 총합을 구할 때 유용하게 사용할 수 있습니다.

 

SUMPRODUCT 선택

우선 곱한 값의 합이 기입될 셀을 선택한 후 '함수 삽입(fx)' 버튼을 클릭합니다. 다음으로 '함수 마법사' 창에서 '수학/삼각' 범주의 'SUMPRODUCT'를 선택한 뒤 '확인'을 클릭합니다.

 

범위 지정

'함수 인수' 창이 뜨면, 곱할 영역을 선택하여 각각의 'Array'에 범위로 지정할 수치를 기입합니다. 위 표의 경우 지난 8일간 팔린 다섯 과일의 가격 총합을 구하는 것이므로 'Array 1'에 과일 1개당 가격(J2~J6)을, 'Array 2'에 11/1~11/8동안 과일이 팔린 개수(K2~K6)를 범위로 지정하도록 하겠습니다.

 

긴 수식을 간단하고 쉽게 계산

모든 범위를 지정한 후, '확인'을 누르면 곱한 값의 총합이 나옵니다. 위 표의 경우 11/1~11/8까지 팔린 과일 가격의 총합을 'SUMPRODUCT'로 구했는데요. 직접 계산했다면 아래와 같이 긴 수식이 필요했을 겁니다.

 

(1,000*3,098) + (2,000*3,296) + (500*4,387) + (700*1,984) + (800*3,670) = 16,208,300

 

만약 실제 회사 매출 관련 데이터였다면 많은 항목과 높은 단위로 계산이 더 복잡했겠죠? 앞으로 매출의 총합을 구할 때는 엑셀의 'SUMPRODUCT'를 활용하여 간단하고도 정확하게 매출을 계산해보시기 바랍니다!

 

순위를 정해주는 'RANK'

이번에는 유용하게 사용할 수 있는 통계 함수를 알아보겠습니다. 'RANK'는 각 항목의 순위를 정해주는 통계 함수인데요. 영업실적이나 시험성적 등 순위나 등수를 매겨야 할 때 유용하게 사용할 수 있습니다.

 

RANK 선택

먼저 순위를 기입할 셀을 선택하고, '함수 삽입(fx)' 버튼을 클릭합니다. 그 다음 '함수 마법사' 창에서 '통계' 범주의 'RANK'를 선택하면 되는데요. 'RANK'는 2010년 버전의 엑셀부터 AVG와 EQ, 두 가지로 구분하고 있습니다.

 

'RANK.EQ'는 2010년 이전 버전의 통계 함수인 'RANK'와 같습니다. 순위를 매기고자 하는 값에서 동일한 값이 있는 경우 가장 높은 순위로 기록되죠. 예를 들어, 2위에 해당하는 값이 두 개인 경우 두 값 모두 2위로 기입되며 그 다음 값은 3위가 아닌 4위로 기록됩니다.

 

'RANK.AVG'는 기존의 'RANK'와 다른 방식으로 순위가 기록되는데요. 순위를 매기고자 하는 값에서 동일한 값이 있다면 그 값은 해당 순위의 평균 값으로 순위가 지정됩니다. 예를 들어 2위에 해당하는 값이 두 개라면, 두 값은 모두 2.5위로 기록되고 그 다음 값은 4위로 기록됩니다.

 

이번 예시에서는 'RANK.EQ'와 'RANK.AVG' 중 기존의 'RANK'와 같은 기능인 'RANK.EQ'를 선택하도록 하겠습니다.

 

범위 지정 및 수식 적용

다음으로 '함수 인수' 창에서 각각의 칸에 맞는 범위를 지정합니다. 위 표를 예로 들어볼까요? 현재 선택한 셀은 '사과'의 '매출 순위'입니다. 따라서 '과일당 총 팔린 가격'에서 '사과'에 해당하는 L2셀을 'Number'로 선택합니다.

 

'Ref'에는 순위를 매길 전체 영역을 범위로 지정해야 하는데요. 위 표의 경우 '과일당 총 팔린 가격'을 기준으로 순위를 매기고 있죠. 따라서 '과일당 총 팔린 가격'의 영역인 L2~L6를 범위로 지정합니다.

 

이 때, L2~L6에는 반드시 '$'를 붙여 범위를 고정해야 합니다. 만약 '$'를 붙이지 않으면 범위가 고정되지 않아, 정확한 순위가 기입되지 않습니다. 따라서 순위함수로 결과값을 도출하기 위해 조건이 되는 L2~L6은 '$L$2:$L$6'으로 기입해야 합니다.

 

'Order' 칸은 순위를 정하는 방식을 결정하는 칸인데요. 순위를 오름차순으로 나타내려면 'Order' 칸에 1을 기입해야 합니다. 반대로 순위를 내림차순으로 나타내고 싶으면 'Order' 칸을 비워두면 됩니다. 위 표에서는 가장 많은 매출을 기록한 과일을 1위로 지정할 것이므로 'Order' 칸을 비워두도록 하겠습니다.

 

그 다음, '확인'을 클릭하고 드래그 해주면 선택한 모든 셀에 순위가 기록됩니다.

 

선택한 항목의 개수를 알려주는 'COUNTIF'

매출 현황을 기록하는 데에 유용한 함수를 알아봤다면 이번에는 법인카드 사용내역 기록에 유용한 함수를 소개하겠습니다. 먼저 'COUNTIF'를 살펴볼까요? 'COUNTIF'는 지정된 범위 내에서 조건에 맞는 항목의 총 개수를 나타내는 함수인데요.

 

이 함수는 법인카드 사용내역을 정리하며 항목별 사용 횟수를 기록할 때 사용하면 좋습니다.

 

COUNTIF 선택

먼저 값을 기입하려는 셀을 선택한 후 '함수 삽입(fx)' 버튼을 클릭합니다. 그 후 '함수 마법사' 창에서 '통계' 범주의 'COUNTIF'를 선택합니다.

 

범위 지정 및 수식 적용

그 다음, 항목이 속한 전체 범위를 지정해야 하는데요. 위 표의 경우 '구분'에 속하는 '교통비'의 총 '사용 횟수'를 표시하고자 하는 것이므로 '교통비'가 속한 '구분(B3~B10)'을 범위로 지정합니다. 이 때 '구분'의 행(B)과 열(3~10)에 '$'를 붙여 범위를 고정합니다.

 

다음으로 개수를 세고자 하는 항목을 선택하는데요. 위의 표에서는 '구분' 중 '교통비'가 사용된 '사용 횟수' 값을 구하고자 하므로 '교통비(I3)'를 선택하도록 하겠습니다. 그 후 '확인'을 누른 뒤 드래그를 통해 값을 채워주면 각 항목들의 '사용 횟수'가 기입됩니다.

 

선택한 항목의 총합을 알려주는 'SUMIF'

다음에 살펴볼 함수는 'SUMIF'입니다. 'SUMIF'는 선택한 항목의 총합을 알려주는 함수인데요. 'SUM'의 경우, 서로 붙어있는 항목의 총합만 구할 수 있지만 'SUMIF'는 항목이 서로 떨어져 있어도 선택한 항목의 총합을 구할 수 있다는 차이점이 있습니다.

 

'SUMIF'는 법인카드 사용내역 정리 시 항목별 총 지출액을 기입하는 것에 유용하답니다.

 

SUMIF 선택

가장 먼저 '총 지출액'을 기입할 항목의 셀을 선택하고 '함수 삽입(fx)' 버튼을 클릭합니다. 다음으로 '함수 마법사' 창에서 '수학/삼각' 범주의 'SUMIF'를 선택합니다.

 

범위 지정 및 수식 적용

다음으로 'Range'에 '교통비'를 포함하고 있는 전체 항목을 범위로 설정합니다. 위 표를 예로 들면, '교통비'가 포함된 '구분'(B2~B10)이 해당되죠. 그 후, 'Criteria'에 총합을 구하고자 하는 항목을 선택하는데요. 위의 표에서는 지난 6일간 사용한 '교통비'의 총 지출액을 구하고자 하므로 '교통비'(I3)를 범위로 선택합니다.

 

'Sum_range'에는 합계를 구하려는 수치의 범위를 설정하는데요. 위 표의 경우, 지난 6일간 사용한 '교통비'의 '총 지출액'을 구하고자 하므로 '지출액'에 해당하는 C2~C10을 범위로 지정합니다. 이 때 범위가 고정되도록 'Range'와 'Sum_range'에 '$'를 붙여줍니다.

 

그 후, '확인'을 클릭하고 드래그를 통해 수치를 채우면 해당 항목에 대한 '총 지출액'이 기입됩니다.

 

실제 법인카드 사용내역을 데이터로 정리하려면 예로 든 표보다 더 많은 항목을 가지고 있어 데이터를 정리하기 더 복잡하죠. 이번 월말에는 'COUNTIF'와 'SUMIF'를 활용하여 법인카드 사용내역을 간단하게 정리해보는 것이 좋겠습니다.

 

 그 외의 유용한 엑셀 기능 

엑셀에는 함수 말고도 유용하게 사용할 수 있는 기능이 많습니다. 알아두면 좋을 엑셀 기능에는 어떤 것들이 있는지 간단히 살펴보도록 하겠습니다.

 

엑셀 그래프 만들기

엑셀에는 정리한 데이터를 시각화 할 수 있는 그래프 기능도 있는데요. 이 기능을 활용하면 파워포인트 등 다른 프로그램을 별도로 사용하지 않아도 간단한 그래프를 만들 수 있습니다. 수치와 함께 기입할 수 있어 보통 영업사원들의 실적 비교에 시각화 자료로 사용합니다.

 

숫자 칸 안에 막대그래프 만들기

먼저 막대그래프를 만들어볼까요? 막대그래프는 엑셀의 '조건부서식'을 이용하면 간단하게 만들 수 있습니다. 막대그래프는 숫자를 기입한 칸 안에 배경으로 들어가도록 할 수도 있고 자신이 원하는 다른 셀에 만들 수도 있습니다.

숫자가 있는 칸 안에 막대그래프를 만들려면 막대그래프를 만들고 싶은 영역을 선택한 후 '조건부서식'의 '데이터 막대'에서 원하는 색깔의 그래프를 선택하면 됩니다.

 

숫자 칸과 별도로 막대그대프 만들기

숫자가 있는 칸과 별도로 막대그래프를 만들고 싶다면 먼저 막대그래프를 넣고 싶은 칸에 그래프로 나타내려는 수치를 기입합니다. 다음으로 막대그래프를 표시할 범위를 지정한 후 '조건부서식'의 '데이터 막대'에서 '기타 규칙'을 클릭합니다.

그 후 '새 서식 규칙'에서 '막대만 표시'를 선택하면 숫자 칸과 별도로 막대그래프를 만들 수 있습니다.

 

엑셀 차트 만들기

엑셀로 차트를 만드는 법은 막대그래프를 만드는 법보다 더 간단한데요. 차트를 만들고 싶은 범위를 지정한 뒤 키보드의 'F11'만 눌러주면 해당 범위를 간단하게 차트로 나타낼 수 있답니다.

 

엑셀 차트는 수치가 가득한 데이터를 한눈에 비교하여 살펴볼 수 있는데요. 보고서를 작성할 때 데이터 수치와 함께 차트도 삽입한다면 가독성을 높인 좋은 결과물을 전달할 수 있겠죠?

 

엑셀 단축키 사용하기

또한 단축키를 사용하면 더욱 짧은 시간 안에 엑셀로 데이터를 정리할 수 있는데요. 많은 엑셀 단축키 중에서도 작업 시 사용 빈도가 높은 단축키들을 표로 정리해 보았습니다. 아래 표를 출력해 책상 앞에 두고 틈틈이 보며 활용해봐도 좋겠죠?

 

F7	맞춤법 검사
F11	지정 범위 차트로 만들기
F12	다른 이름으로 파일 저장
Ctrl + L	표 만들기
Ctrl + N	새로운 문서 생성
Ctrl + S	파일 저장
Ctrl + Z	실행 취소
Ctrl + 0 (숫자)	열 숨기기
Ctrl + 9	행 숨기기
Ctrl + Space Bar	열 전체 선택
Shift + F3	함수 마법사
Shift + F9	행 전체 선택
Ctrl + Shift + +(플러스 기호)	셀 삽입
Ctrl + Shift + F12	인쇄하기

 

출처 : http://me2.do/FjqOIRck

지금까지 엑셀함수와 그래프 삽입, 단축키 등 엑셀에 유용하게 사용할 수 있는 기능에 대하여 살펴봤습니다. 엑셀은 기능이 많아 복잡할 수는 있지만, 잘 알고 사용하게 되면 업무의 속도를 높여주기 때문에 직장인들에게 매우 효과적인 프로그램인데요. 이 포스팅을 참고하셔서 엑셀 프로그램에 도전해보는 건 어떨까요?

[관련 포스팅]

컴퓨터 속도 저하 원인과 고클린 등 컴퓨터 속도 빠르게 하는 법

웹 브라우저 특징 및 점유율 등 소개 - 인터넷 익스플로러, 크롬, 파이어폭스 등

아웃룩 제대로 활용하기! 아웃룩 메일 연동법 및 관련 유용한 기능, 비즈니스 메일 용어 소개