안녕하세요. 행아아빠입니다.
이번 엑셀 내용으로는 엑셀 서식으로 예약 관리 현황을 예약 데이터를 참조해서 예약 관리 현황을 시간대별로 날짜별로 확인할 수 있는 서식과 수식을 작성해 보도록 하겠습니다.
예제에서 작성한 샘플서식은 스크린골프, 아카데미골프와 같은 매장의 예약 현황 업무를 상상해서 작성한것으로 예약 관리를 해야 하는 업종에 따라 필요한 예약정보 데이터들을 활용하여 사용자의 환경에 맞는 서식과 수식을 작성해야 합니다.
여러분들이 예약 관리 현황을 엑셀 서식으로 작성하고자 하는 업종과는 다를수는 있겠지만, 예약 관리 현황 엑셀 서식을 작성하는데 참고 가이드로서 활용할 수 있는 수식과 서식이라는 관점에 집중해서 봐주시길 바라겠습니다.
■ 행아아빠의 Youtube 채널.
● 주요 컨텐츠 주제 - 내일은로또왕 / 내일은엑셀왕
● Youtube 채널 주소 - https://www.youtube.com/@economic-freedom
■ 행아아빠 후원 안내.
● Youtube 채널 맴버십 후원 - https://www.youtube.com/channel/UCPmC4OZu99Rqguwz5QEINjA/join
● Youtube 댓글 후원 - Youtube 영상 댓글에서 "Super Thanks" 구매 후 댓글 작성.
● 내일은엑셀왕 대표 Youtube 영상 - https://youtu.be/oYVZHgv_sKM (2023년 급여 자동 계산 서식)
1. 예약관리 서식 작동 방법.
엑셀로 예약 관리를 할 수 있는 데이터 시스템 서식을 이미지와 같이 샘플 서식을 작성했을때 자동 계산이 어떤 방식으로 작동하는지 먼저 살펴 보도록 하겠습니다.
① 먼저 예약 정보에 필요한 데이터를 왼쪽 상단에 날짜, 이름, 예약시간, 예약인원, 타석룸과 같이 예약에 필요한 기본 정보들을 입력합니다.
② 그리고 우측 상단의 표에서 예약현황 날짜에 예약이 가능한 타석룸과 시간대를 확인하고자 하는 날짜를 입력하게 되면 ①번 예약 데이터에 입력된 내용에서 이름과 인원이 몇시에 어느 타석룸에 예약이 되어 있는지 간단하게 할 수 있고, 예약이 비어 있는 시간대에 예약이 가능한 타석룸은 공란으로 처리 되어 있는것을 확인할 수 있기 때문에 예약하고자 하는 시간대에 비어있는 타석룸에 예약을 할수가 있습니다.
③ 중앙 하단에 예약 가능 시간 아래 있는 셀은 ②번에서 입력한 날짜를 자동으로 반영하게 되어 ②번에서 입력한 날짜에 시간대별로 몇개의 타석룸이 비어있는지 예약이 완료가 되었는지를 확인할 수 있게 됩니다.
2. 예약에 필요한 데이터를 입력했을때 자동으로 예약 현황을 확인할 수 있는 계산 수식 작성.
2-1. 예약에 필요한 고객 정보 데이터를 입력하는 박스안에서 작성해야할 수식. [①번 박스]
①번 박스에서 작성할 수 있는 수식은 예약 시간대와 예약 타석룸등의 예약 정보를 입력했을경우 동일한 날짜에 동일 시간대에 동일 타석룸에 1개이상의 예약정보가 입력될 경우 예약 가능, 불가능을 체크할 수 있는 메시지를 출력하도록 해서 잘못된 예약이 진행되지 않을 장치가 필요합니다.
따라서 H21셀에는 다음과 같은 수식을 작성하여 동일 날짜, 동일 시간대, 동일 타석룸에 2개이상의 예약 데이터가 입력될 경우 "예약불가" 메시지를 출력하고, 동일한 데이터가 입력되지 않고 예약 진행이 가능하다면 공란으로 처리 할 수 있는 수식을 작성합니다.
| =IF(COUNTIFS($B$21:$B$1000,$B21,$D$21:$D$1000,$D21,$F$21:$F$1000,$F21)>1,"예약불가","") |
2-2. 시간대와 타석룸에 예약된 예약 현황과 잘못된 예약이 진행되었을 경우 이를 체크할 수 있는 조건부서식. [②번박스]
②번 박스에서 P4셀부터 Y51셀 범위에 작성해야할 수식은 각 시간대마다 타석룸에 예약된 예약 정보를 이름과 인원을 간단하게 표기해서 시간대별로 타석룸에 예약된 현황을 확인할 수 있도록 합니다.
그리고 O열에는 해당 시간대에 남아 있는 예약 가능한 잔여 타석룸수를 표기 하는 수식을 작성하고, M열에는 해당 시간대에 예약된 총 예약 수를 표기 하도록해서 타석룸은 총 10타석이기 때문에 해당 시간대에 예약을 받을 수 있는 최대 예약은 10개의 예약으로 10개 예약을 초과해서 예약이 진행됐을 경우에는 셀의 색상을 빨간색으로 표기하는 조건부서식을 작성하여 해당 시간대에 잘못된 예약이 존재하는것을 체크할 수 있습니다.
이러한 내용으로 ②번 박스에서 작성해야할 수식은 다음과 같이 작성할 수 있습니다.
| 1. P4셀 - 다음의 수식을 작성한 후 Y51셀까지 수식 복사. =IFERROR(LOOKUP(1,1/(($B$21:$B$1000=$O$1)*($D$21:$D$1000=$N4)*($F$21:$F$1000=P$3)),$C$21:$C$1000)&" "& LOOKUP(1,1/(($B$21:$B$1000=$O$1)*($D$21:$D$1000=$N4)*($F$21:$F$1000=P$3)),$E$21:$E$1000)&"명","") 2. O4셀 - 비어있는 셀의 개수 수식. =COUNTIFS($P4:$Y4,"<>")-COUNTIFS($P4:$Y4,"><") 3. M4셀 - 예약이 완료된 셀의 개수 수식. =COUNTIFS($P4:$Y4,"<>")-COUNTIFS($P4:$Y4,"><") |
1번의 수식은 P4셀부터 Y51셀까지 작성해서 결과값을 표기 하는 수식으로 해당 시간대에 해당 타석룸에 예약된 예약자 이름과 인원을 간단하게 결과값으로 표기 하고자 하는 수식으로 Lookup함수로 ①번 박스에서 작성한 예약에 필요한 데이터의 날짜, 이름, 시간, 인원, 타석룸이 해당셀에 일치하는 데이터값을 결과값으로 표기하는 수식입니다.
2번의 수식은 해당 시간대에 10개의 타석룸중에서 예약이 가능한 잔여 타석룸의 개수를 파악하기 위한 수식입니다.
3번의 수식은 해당 시간대에 10개의 타석룸중에서 예약이 완료된 타석룸의 개수를 파악하기 위한 수식입니다.
그럼 이제 조건부서식으로 혹시나 동일한 시간대에 동일한 타석룸을 중복으로 예약했을 경우 해당 셀이 빨간색으로 변경되어 예약에 문제가 있음을 체크할 수 있는 조건부 서식의 규칙 수식을 다음과 같이 작성해 보도록 하겠습니다.
| 1. P4셀부터 Y51셀까지 중복 예약된 셀에 빨간색 음영을 적용하는 조건부 서식 규칙에 작성할 수식. =IF(COUNTIFS($B$21:$B$1000,$O$1,$D$21:$D$1000,$N4,$F$21:$F$1000,P$3)>=2,1) 2. M열 - 동시간대 최대 예약 가능한 예약수를 넘었을 경우 해당셀을 빨간색으로 음영처리하는 조건부 서식 규칙에 작성할 수식. =IF($M4>10,1) |
2-3. 시간대별로 예약 가능한 잔여 타석과 예약마감시 "예약마감" 메시지를 출력하고 예약마감된 시간대는 빨간색으로 음영처리 하는 수식 작성. [③번 박스]
③번 박스에서는 시간, 예약 가능한 잔여 타석, 예약 마감 여부 메시지를 출력하도록 해서 시간대별로 예약이 마감되었는지, 잔여타석은 몇타석이 남았는지를 확인할 수 있고, 예약이 마감이 되었다면 시간, 잔여타석, 예약마감셀을 조건부서식 규칙을 수식으로 작성해서 예약이 마감된 셀은 빨간색 음영을 적용하도록 합니다.
수식을 작성하면 다음과 같이 작성할 수 있습니다.
| 1. 잔여타석 수식. ① B5~L5셀 범위 적용 수식. =SUMIFS($O$4:$O$51,$N$4:$N$51,B$4) ② B8~L8셀 범위 적용 수식. =SUMIFS($O$4:$O$51,$N$4:$N$51,B$7) ③ B11~L11셀 범위 적용 수식. =SUMIFS($O$4:$O$51,$N$4:$N$51,B$10) ④ B14~L14셀 범위 적용 수식. =SUMIFS($O$4:$O$51,$N$4:$N$51,B$13) ⑤ B17~L17셀 범위 적용 수식. =SUMIFS($O$4:$O$51,$N$4:$N$51,B$16) 2. 잔여타석이 0이면 "예약마감" 메시지를 출력하는 수식. ① B6~L6셀 범위 적용 수식. =IF(B5="","",IF(B5=0,"예약마감","")) ② B9~L9셀 범위 적용 수식. =IF(B8="","",IF(B8=0,"예약마감","")) ③ B12~L12셀 범위 적용 수식. =IF(B11="","",IF(B11=0,"예약마감","")) ④ B15~L15셀 범위 적용 수식. =IF(B14="","",IF(B14=0,"예약마감","")) ⑤ B18~L18셀 범위 적용 수식. =IF(B17="","",IF(B17=0,"예약마감","")) 3. 잔여타석이 남아있지 않고, "예약마감"된 시간대는 빨간색 음영으로 적용하기 위한 조건부서식 규칙을 수식으로 작성. 조건부서식 규칙을 적용할 범위는 B4셀~L18셀 =IF(B$6="예약마감",1) |
여기까지 수식을 작성하게 되면 예약고객의 예약에 필요한 기본 정보 예약날짜, 이름, 예약시간, 인원수, 예약타석만 입력하게 되면 예약 가능여부와 예약 현황을 작성된 수식에 의해서 자동으로 계산되도록 해서 파악할 수 있습니다.
추가적으로 예약현황에 회원, 비회원, 이용금액등과 같은 추가적인 데이터를 연결하면 여러분들의 매출 상승에 필요한 데이터를 추출하고 매출 상승 영업 전략에 필요한 수치들을 파악할 수 있을것으로 기대할 수 있습니다.
예약현황은 업종과 사용자의 환경에 따라 다양한 형태의 서식을 작성할 수 있기 때문에 해당 샘플 서식을 참고 가이드 삼아서 여러분들의 실제 예약 현황 서식을 작성하고 적용해 보시길 바라겠습니다.
※ 예약현황관리 샘플 예제 서식 다운로드.
Blog : https://hang-a-fafa.tistory.com
Youtube : https://www.youtube.com/@economic-freedom
Naver 까페 : https://cafe.naver.com/hatime
'엑셀' 카테고리의 다른 글
| 엑셀 연차관리대장 자동 계산 서식 만들기. (0) | 2023.03.06 |
|---|---|
| 엑셀 날짜와 시간으로 입력된 데이터를 날짜와 시간으로 분리 시키는 수식 작성 방법. (0) | 2023.03.02 |
| 엑셀 급여 계산 사용자 조건에 맞는 급여 계산 서식 작성 방법. (0) | 2023.02.27 |
| 엑셀 자주 사용하는 함수 - index함수, match함수. (0) | 2023.02.24 |
| 엑셀 자주 사용하는 함수 - sumif함수, sumifs함수. (0) | 2023.02.22 |
댓글