직장인 엑셀 함수 / 가계부 함수 / 데이터 유효성 : 선택박스 만드는 법 / 항목 전체 합계 함수 알아보기

 

개요

직장인들, 그리고 가정 주부들이 가계부를 작성하기 위해 사용하는 엑셀이나 구글 스프레드 시트 이용 시 자주 사용할 함수에 대해 한 가지를 소개하려고 한다. 이번 포스팅에서 소개할 엑셀 함수는 'SUM IF', 단순하게 셀들의 합계를 나타내 주는 함수인 SUM과는 다르게, 선택박스 안에 있는 카테고리를 지정 후 해당 카테고리들의 합계를 구하는 함수이기 때문에 정말 유용하게 사용할 수 있는 함수이다. 

선택 박스 지정하기

우선 선택박스를 설정하는 방법에 대해 알아보자. 선택 박스란 엑셀의 어느 셀을 클릭하게 되면 미리 지정해 놓은 값을 제외한 다른 값들은 입력할 수 없도록 일정 값만 입력하게 끔 해주는 기능이다. 예를 들어 A, B, C 이 세 가지 글자만 사용하게 끔 설정을 해 놓으면 이 A, B, C 세 가지를 제외하곤 해당 셀에 입력할 수 없다. 이런 경우에는 통계 자료를 작성하여 다른 사람들에게 배포하여 입력하게 끔 하거나 가계부 등을 작성 시 무척 유용하게 사용할 수 있다.

 

우선 선택박스를 만들 셀을 선택한 다음 최상단의 큰 카테고리 메뉴에서 데이터 > 데이터 유효성 순서로 들어가면 '데이터 유효성' 창이 뜨게 된다. 이곳에서 설정 > 허용에서 '목록'을 선택하도록 한다. 이후 아래에 있는 '원본'에 선택 박스에 들어갈 내용들을 적어주면 된다. 여기서 예시로 김밥, 스팸, 참기름을 입력해 보도록 하겠다. 

A1 셀을 선택하고나서 데이터 유효성을 이용하여 선택 박스를 만들면 위와 같이 잘 입력이 된 걸 볼 수 있다. 여기까지 선택 박스를 생성하는 과정에 대해 살펴봤으니, 이제 각 항목별 자동 합계를 해 주는 함수를 만들어 보도록 하자.

각 항목별 합계 구하는 함수: SUM IF

합계를 구하는 기본적인 함수인 SUM과는 또 다른 카테고리별 합계를 구할 수 있는 SUM IF 함수 사용법에 대해 알아보자. SUMIF함수를 입력하는 방법은 아래와 같다.

• =SUMIF(카테고리 전체 셀, 카테고리 이름, 카테고리별 가격 전체 셀) 

간단한 예시로 데이터 유효성 기능을 활용한 '김밥, 스팸, 참기름' 식재료 가격과 관련된 간단한 표를 만들어서 위의 함수를 적용해 보고자 한다. 김밥 전체의 가격을 각각 '김밥'이라는 데이터에 맞게 노란 샐에 입력하면 아래와 같이 입력이 된다. 

김밥 항목이 들어가는 전체 카테고리의 범위를 먼저 지정한다. 김밥, 참기름, 스팸들이 쭉 써있는 범위인 A3:A9를 입력하고 쉼표를 누른 다음, 우리가 구하고자 하는 값인 김밥 카테고리의 셀 번호인 E3, 그리고 마지막으로 가격이 쓰여 있는 셀들의 전체 범위인 B3:B9를 입력하고 괄호를 닫은 다음 엔터를 누르면 된다. 

이렇게 하면 전체 카테고리의 김밥이라고 적혀있는 셀들의 가격들만 골라서 SUM함수를 사용한 것 처럼 더해지게 된다. 이를 응용하면 나머지의 스팸, 그리고 참기름의 가격들 역시 쉽게 구할 수 있다. 

이를 실제로 활용한 가계부의 예시. 가계부 뿐만 아니라 회사에서 통계자료를 도출할 때 가장 많이 쓰이게 되는 함수이다. 이제 갓 사회 초년생이 되었거나 집에서 가계부를 엑셀, 혹은 구글 스프레드시트를 통해 작성해 보려고 한다면 SUMIF 함수를 사용하여 각 카테고리별 합계를 쉽게 구해보도록 하자.