엑셀 여러가지 데이터 조건에 따른 합계 내는 방법

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제

 

 

01. 문제

Q. 여러가지 데이터 조건에 따른 합계 내는 방법

 

수백명 단위의 그룹 행사를 자주 운영하는 사람인데요, 호텔 예약 및 

객실료 계산을 하면서 좀 더 시간을 절약할 방법을 찾고 있습니다.

 

그룹 호텔 예약을 위해 간단한 예시를 만들어 봤는데요, 

같은 타입의 객실이라도 투숙 인원에 따라 객실 가격이 달라지므로 루밍 리스트에는 

Suite(1인), Suite(2인), King(1인), King(2인) 이런 식으로 방 타입의 이름을 다르게 넣어 놨지만 

요약 표에서는 1인, 2인, 3인과 상관 없이 그냥 Suite 또는 King, Twin 처럼 객실 타입별 수량 합계 및 

그 객실 타입들의 날짜별 투숙일을 카운트하는 방법이 궁금합니다. 

 

예약도 진행하면서 전체 숙박비 뿐만 아니라 방 타입 카테고리별로 숙박비 계산도 동시에 필요하고,

몇백명씩 투숙객 인원이 많아지만 호텔과 사전에 계약할 때도 기간 내 일별 최소 숙박일수가 몇 개 이상,

전체 총 숙박일수 몇 박 이상 이런 식으로 하기 때문에 아래와 같이 생긴 표로 진행할 수 밖에 없는데요, 

가장 효율적인 함수 사용 방법이 너무 궁금합니다.

 

 

문제를 요약하면, 

1) 방 타입에 따른 수량 카운트

2) 방 타입 & 날짜별 숙박일 카운트

 

02. 문제 해결 방법

1) 방 타입에 따른 수량 카운트

Suite에 해당하는 것은 Suite (1인), Suite (2인)

King에 해당하는 것은 King (1인), King (2인), King(3인)

Twin에 해당하는 것은 Twin (2인), Twin(3인) 이다.

 

1-1) 범주 필드를 하나 추가하여 범주를 쉽게 알아볼 수 있게 한다.

원본 데이터

 

범주 추가

  1-2) SUMIF 사용 범주가 특정 값이 경우 총 숙박일을 더한다. 

  SUMIF 함수는 다음 포스트를 참고하자.

  https://eggdrop.tistory.com/42

SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수

 

2) 방 타입 & 날짜별 숙박일 카운트

2-1) 데이터의 날짜 순서와 요약 시트의 날짜 순서가 같은 경우

   그냥 SUMIF를 사용할 수 있다.

 

03. 예제

1) 방 타입에 따른 수량 카운트

SUMIF를 이용해보자.

=SUMIF($G$3:$G$9,J4,$F$3:$F$9)

방 타입에 따라 총 숙박일이 합산된다.

J열에 있는 방타입과  G열에 있는 범주가 일치하는  경우 F열의 값을 더한다.

 

 

 

 

2) 방 타입 & 날짜별 숙박일 카운트

2-1) 데이터의 날짜 순서와 요약 시트의 날짜 순서가 같은 경우

위의 그림과 같은 경우 날짜별로 정리가 되어있기 때문에 특별한 작업을 할 필요는 없다.

=sumif($G$3:$G$9,$J4,C$3:C$9)

방  타입별 수량에서 총 숙박일의 합을 구했다면 여기서는 1월 1일에 해당하는 숙박일의 합을 구하면 된다.

다만 우리는 이 수식을 아래로도, 오른쪽으로도 드래그할 예정이기 때문에 절대주소 사용에 주의해야한다.

1월 1일에 해당하는 데이터인 C3:C9의 경우 열번호에 대해서만 절대 주소를 지정해준다.

또한 방 타입(J열의 경우)도 옆으로 복사할 거에 대비하여 J열에만 절대주소를 지정해준다.

 

1월 2일자에도 해당 수식을 복사해서 쓰고 싶기 때문이다.

 

절대 주소 상대주소에 대해서는 다음 포스트를 참고하자.

https://eggdrop.tistory.com/50

엑셀 절대주소 vs 상대주소 (절대주소 활용법)

 

작성한 수식을 L4:N6 범위에 복사해준다.

 

1월 1일자 수식을 그대로 복사하여 1월 2일자, 1월 3일자에도 붙여넣기한다.