Excel [지식인 해결 시리즈] 007. 상반기/하반기 매출 집계 (SUMIF)

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

문제를 해석해보면, 다음과 같습니다.

  1) 일단위로  기록되는 일매출표가 있다.

  2) 일매출표에는 매출 발생 일자, 매출, 업체명이 기록된다.

  3) 일매출표에 있는 내용을 집계하여 월매출 표를 작성해야 한다.

  4) 월매출표에는 기업별로 매출이 합산되어야 한다.

  5) 매출발생일자를 기준으로 1~15일은 상반기, 16~31일은 하반기로 나눈다.

  6) 기업별, 반기별 매출을 합산하여 월 매출표에 표기해야 한다.

 

정리하면 일매출표에 있는 데이터를 기반으로 월매출표를 작성하되, 기업별, 상반기/하반기 매출의 합을 구하라는 이야기로 보입니다.

 

월 매출표에는 기업명, 상반기/하반기 정보가 정리되어 있고, 매출이 자동으로 계산되게 하면 될 것 같습니다. 

 

시트 중 일부분에 다음과 같이 일매출표를 작성합니다.

이 내용을 기반으로 다음과 같은 월매출표를 작성하는 것을 목표로 합니다.

  아래 그림에서 업체명/반기 정보는 사용자가 작성해야됩니다.

  매출만 수식으로 계산하는 작업을 수행해봅시다.

 

02. 문제 해결 방법

 

위 문제를 해결하기 위해서는 다음과 같은 작업을 해야합니다.

  - 날짜를 기반으로 상반기/하반기를 구분한다.

  - 기업명 + 상반기/하반기 정보를 그룹으로해서 매출의 합을 구한다.

 

1) 날짜를 기반으로 상반기/하반기를 구분한다.

일매출표에 포함되어 있는 날짜 부분의 서식이 날짜라면 여기서 (일자)만 분리하는 것이 가능하다.

 

일자 분리에 대해서는 예전에 다룬 적이 있으므로 다음 포스트를 참조하면 도움이 될 것 같다.

https://eggdrop.tistory.com/27?category=1087790 

Excel [지식인 해결 시리즈] 006. 근무시간에서 특정 시간 구하기 (야간 근무 인정 시간)

날짜에서 일자만 분리하기 위해서는 Day()함수를 이용하면된다.

 

C2에 입력된 날짜 09월 01일 에서 일자인 1만 분리해온다.

 

상반기/하반기의 구분은 IF 함수를 이용하여 구할 수 있다.

 

 

2) 기업명 + 상반기/하반기 정보를 그룹으로해서 매출의 합을 구한다.

 

SUMIF 함수를 사용해 보자.

SUMIF 함수는 지정된 영역에 있는 데이터 중에서 특정 조건을 만족하는 데이터만 합하는 함수이다. 

SUMIF 함수의 파라미터는 위의 그림과 같다.

 - range: 특정 조건을 조사할 영역

 - criteria: 특정 조건

 - [sum_range]: 덧셈을 할 데이터와 조건을 따져야하는 데이터가 다를 때, 덧셈을 할 데이터 영역을 따로 지정할 수 있다. 조건을 만족할 경우 조건과 같은 인덱스의 값이 더해진다.

 

아래 예제를 통해 SUMIF를 익혀보자.

 

 - 첫번째 파라미터는 업체명이 있는 B열이다.

두번째 파라미터는 "현대"가 저장되어 있는 셀이다.

세번째 파라미터는 매출액이다.

함수의 결과는 다음과 같다.

지정한 영역에서 기업명이 현대일 경우 매출을 더한 값을 표기한다.

 

03. 예제 및 설명

 

날짜로 상반기/하반기를 구분하는 방법과 특정 조건에 맞는 데이터만 합하는 방법을 다뤘으니, 이제 실제 문제를 해결해보자.

 

우리는 기업명 + 반기정보(상반기 or 하반기)로 그룹을 만들고 그 그룹의 매출을 합쳐야 한다.

 

공동으로 합할 데이터에 그룹 아이디를 부여해보자.  기업명 + 상반기/하반기 로 부여할 수 있다.

 

"기업명_분기명" 형태의 아이디를 부여하여, 부여된 아이디로 SUMIF 연산을 하면 기업별/반기 실적을 구할 수 있다.

 

Criteria 부분은 업체명과 반기를 합쳐서 구성하여, 해당 ID의 열을 조회 후 매출을 합할 수 있다.

 

실행한 결과는 다음과 같다.

*SUMIF 부분에 주소를 절대주소로 입력하는 이유는 채우기 핸들로 수식을 밑으로 확장할 예정이기 때문이다.