Bạn đang tìm cách theo dõi tình trạng đặt phòng và lịch đặt của khách sạn? Bạn muốn một công cụ miễn phí và dễ sử dụng để kiểm tra sự sẵn có của các phòng vào một ngày cụ thể hoặc trong một khoảng thời gian? Thì hôm nay mình xin giới thiệu một mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets!
Đặc điểm của mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets
Mẫu này được tạo ra dựa trên trạng thái đặt phòng và sự sẵn có của các phòng trong khách sạn. Bạn có thể dễ dàng tìm hiểu tình trạng đặt phòng vào bất kỳ ngày nào hoặc trong một khoảng thời gian cụ thể. Bạn có thể tìm hiểu thêm về mẫu lịch đặt và tình trạng đặt phòng này tại đây.
Dưới đây là những đặc điểm chính của mẫu lịch đặt và tình trạng đặt phòng này:
- Mẫu này hoàn toàn miễn phí và dễ sử dụng, ngay cả khi bạn chỉ mới là người mới bắt đầu sử dụng Google Sheets.
- Mẫu này cho phép bạn xem tình trạng đặt phòng trong ba tháng dựa trên ngày bắt đầu đã cung cấp. Bạn có thể chọn ngày bắt đầu khác nhau.
- Mẫu đã được thiết kế sẵn cho 50 phòng, nhưng bạn có thể thêm nhiều phòng hơn nếu bạn có nhiều hơn 50 phòng.
- Mẫu sử dụng định dạng điều kiện để hiển thị tình trạng sẵn có (màu xanh nhạt) hoặc không sẵn có (màu cam, xanh lá cây nhạt hoặc đỏ) của các phòng. Mặc định, mẫu định dạng cho tình trạng đặt phòng của khách sạn, nhưng bạn có thể sử dụng mẫu này cho bất cứ điều gì tương tự, chẳng hạn như xem trạng thái đặt chương trình/sự kiện hoặc trạng thái đặt xe đạp.
- Tên người đặt phòng hiển thị trong biểu đồ.
Đó là những đặc điểm chính của mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets. Với mẫu này, bạn có thể dễ dàng tìm hiểu tình trạng đặt phòng vào bất kỳ ngày nào hoặc trong một khoảng thời gian cụ thể. Để biết thêm chi tiết và tải mẫu lịch, hãy truy cập Crawlan.com.
Hướng dẫn sử dụng mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets cho doanh nghiệp của bạn
Mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets sử dụng hai Sheets trong một file:
- Sheet “Sẵn có”
- Sheet “Đặt phòng”
Sheet “Sẵn có” là trang chủ của mẫu lịch và tình trạng đặt phòng. Trong sheet này, bạn có thể xem trạng thái đặt phòng và sự sẵn có trong khoảng C4:CP53. Dữ liệu mẫu đã được điền vào các ô A4:B53. Bạn hãy thay thế chúng bằng dữ liệu của riêng bạn, bao gồm số phòng (A4:A53) và loại phòng (B4:B53).
Bạn chỉ cần thay đổi một số thông tin khác. Trong ô C1 có một “datepicker”. Đây là công cụ điều chỉnh phạm vi ngày cho biểu đồ C3:CP3.
Sheet “Đặt phòng” dùng để nhập dữ liệu đặt phòng. Bạn có thể giữ tất cả dữ liệu đặt phòng (cũ, hiện tại và sắp tới) trên sheet này. Để sử dụng mẫu lịch này cho doanh nghiệp của bạn, bạn cần thay đổi một số thông tin sau:
1. Sheet “Sẵn có”: Làm thế nào để tùy chỉnh nó phù hợp với nhu cầu của bạn
Sheet “Sẵn có” là trang chủ của mẫu lịch đặt và tình trạng đặt phòng. Trong sheet này, bạn có thể thấy trạng thái đặt phòng và sự sẵn có trong khoảng C4:CP53.
Bạn cần thay thế một số thông tin giả định trong ô A4:B53 bằng dữ liệu của riêng bạn, bao gồm số phòng (A4:A53) và loại phòng (B4:B53) (có thể bỏ qua thông tin về loại phòng).
Bạn có thể thêm tối đa 50 số phòng, bởi vì phạm vi trong A4:A53 có 50 hàng. Nếu bạn có nhiều phòng hơn, chỉ cần thêm thêm hàng ở cuối.
Bạn chỉ cần thay đổi một lần nữa. Có một công cụ “datepicker” trong ô C1. Điều này điều chỉnh phạm vi ngày cho biểu đồ C3:CP3.
2. Sheet “Đặt phòng”: Làm thế nào để tùy chỉnh nó phù hợp với nhu cầu của bạn
Sheet “Đặt phòng” được sử dụng để nhập dữ liệu đặt phòng. Bạn có thể giữ tất cả dữ liệu đặt phòng (cũ, hiện tại và sắp tới) trên sheet này.
Bạn cần nhập hoặc chọn các thông tin sau:
- Chọn số phòng trong khoảng A3:A (Các phần thả xuống sẽ được điền với tất cả các số phòng trong hàng A4:A53 của sheet “Sẵn có”).
- Nhập ngày nhận phòng trong khoảng E3:E và ngày trả phòng trong khoảng F3:F.
- Nhập tên người đặt phòng trong khoảng B3:B.
- Trong các ô H3:H, chọn trạng thái đặt phòng từ hai tùy chọn: “Confirmed” và “Tentative”. Màu của các thanh sẽ là màu xanh nhạt và cam lòe sáng tương ứng. Tuy nhiên, nếu đặt phòng chỉ được một đêm, các quy tắc này sẽ bị ghi đè và màu sẽ là màu đỏ.
Hãy đảm bảo chú ý đặc biệt khi nhập dữ liệu trong sheet này. Tránh nhập trùng lặp. Ví dụ, nếu một phòng được đặt từ ngày 1 tháng 1 năm 2023 đến ngày 31 tháng 1 năm 2023, phòng đó không được đặt cho bất kỳ ngày nào trong khoảng thời gian đó.
Điều này quan trọng vì mục đích của mẫu lịch đặt và tình trạng đặt phòng là kiểm tra sự sẵn có của các phòng.
Để tránh nhập trùng lặp, hãy kiểm tra khu vực biểu đồ trong sheet “Sẵn có” để biết sự sẵn có của phòng trước khi nhập mỗi đặt phòng. Bạn cũng nên chọn ngày phù hợp trong ô Availability!C1.
Lưu ý: Sau cột H, bạn chỉ có thể chèn hoặc xóa các cột.
Hướng dẫn tạo mẫu lịch đặt và tình trạng đặt phòng trên Google Sheets
Ở phần này, mình sẽ chia sẻ các công thức được sử dụng trong cả hai sheet “Sẵn có” và “Đặt phòng” và mục đích của chúng.
1. Công thức trong sheet “Đặt phòng”
Trong ô G2 của sheet “Đặt phòng”, mình có công thức mảng sau đây, giúp tính số ngày đặt phòng từ ngày nhận phòng và ngày trả phòng của khách:
=ARRAYFORMULA(VSTACK("Số đêm đặt phòng",IF(A3:A="",,DAYS(F3:F,E3:E))))
Lưu ý: Mình không cần công thức này để tạo thanh kéo trong tab “Sẵn có” của lịch đặt và tình trạng đặt phòng.
Giải thích công thức:
- Hàm DAYS được sử dụng trong công thức mảng để tính số ngày giữa ngày nhận phòng và ngày trả phòng cho mỗi phòng.
- Hàm VSTACK tạo một dãy dọc gồm hai mảng: mảng đầu tiên chứa văn bản tiêu đề “Số đêm đặt phòng” và mảng thứ hai chứa số đêm đặt phòng được trả về bởi hàm DAYS.
- Hàm IF kiểm tra xem số phòng trong cột A có trống không hay không. Nếu nó trống, công thức sẽ trả về giá trị trống. Nếu không, công thức sẽ trả về số ngày giữa ngày nhận phòng (cột E) và ngày trả phòng (cột F) cho phòng đó.
2. Công thức trong sheet “Sẵn có”
Mình đã sử dụng công thức SEQUENCE sau đây trong ô C3 để tạo dãy thứ tự từ ngày bắt đầu đến ngày kết thúc (scale thời gian) với ba tháng:
=SEQUENCE(1,DAYS(EDATE(C1,3),C1),C1)
Công thức này hoạt động như thế nào?
- Hàm EDATE(C1,3) trong công thức trả về một ngày nằm sau ba tháng so với ngày bắt đầu trong ô C1.
- Hàm SEQUENCE trả về các số thứ tự trong một hàng và n cột, bắt đầu từ ngày trong ô C1. Giá trị của n được xác định bằng số ngày giữa ngày trong C1 và EDATE(C1,3).
- Công thức C2 sau đây chuyển đổi các ngày được tạo ra trong ô C3:CP3 thành các ngày trong tuần:
=ARRAYFORMULA(IF(LEN(C3:3),TEXT(C3:3,"ddd"),))
Công thức C4 sau đây trong sheet “Sẵn có” lấy tên đặt phòng từ sheet “Đặt phòng” và đặt chúng vào các ô ngày nhận phòng:
=LET( dt,C3:3, room,A4:A, guest,Reservations!$B$3:$B, r_room,Reservations!$A$3:$A, start,Reservations!$E$3:$E, MAP(dt,LAMBDA(c, MAP(room,LAMBDA(r, JOIN(" ?",IFNA(FILTER(guest,(r_room=r)*(start=c)))) )))) )
Giải thích công thức:
- Hàm LET định nghĩa năm biến:
- dt: Các ngày trong hàng 3 của sheet “Sẵn có”.
- room: Các số phòng trong cột A của sheet “Sẵn có”.
- guest: Tên khách trong cột B của sheet “Đặt phòng”.
- r_room: Các số phòng trong cột A của sheet “Đặt phòng”.
- start: Ngày nhận phòng trong cột E của sheet “Đặt phòng”.
- Hàm MAP được sử dụng để lặp qua các ngày trong dt và các số phòng trong room, cả hai đều thuộc sheet “Sẵn có”.
- Đối với mỗi ngày (dt) và số phòng (room), hàm MAP sử dụng hàm FILTER để lọc tên khách trong guest (“Đặt phòng”) để chỉ bao gồm tên khách đã đặt phòng vào ngày đó cho phòng đó. Kết quả của hàm MAP là một mảng hai chiều.
- Hàm JOIN kết hợp các tên khách được lọc thành một chuỗi, phân tách bằng dấu phẩy (nó là một phần của việc sửa lỗi vì không có hai tên trong cùng một ngày đặt phòng cho cùng một phòng).
3. Quy tắc định dạng điều kiện (tô màu) trong sheet “Sẵn có”
Quy tắc định dạng là yếu tố quan trọng trong việc tạo ra mẫu lịch đặt và tình trạng đặt phòng dễ nhìn trên Google Sheets.
Mình sử dụng ba quy tắc định dạng điều kiện cho khoảng C4:CP53. Để xem chúng, hãy nhấp vào ô C4 và vào menu “Định dạng” > “Định dạng điều kiện”.
Quy tắc đầu tiên tô màu các ô tương ứng với các ngày đã đặt trong dải thời gian (C3:CP3) với màu cam cho các đặt phòng chưa được xác nhận (tựa colum H trong sheet “Đặt phòng”).
Quy tắc thứ hai thay đổi màu cam thành màu xanh nhạt cho các đặt phòng đã được xác nhận cũng tương tự trên cột H trong sheet “Đặt phòng”.
Quy tắc thứ ba tô màu đỏ cho đêm trước ngày trả phòng (không phải ngày trả phòng). Vì vậy, nếu đặt phòng chỉ là một đêm, màu sắc sẽ là màu đỏ, không phải màu xanh nhạt hoặc cam như bình thường.
Dưới đây là các công thức cho quy tắc này (quy tắc được sử dụng cho khoảng C4:CP53):
Các ngày đã đặt (Màu cam)
=LET( from_to,"Đặt phòng!E3:F", booked_room,"Đặt phòng!A3:A", status,"Đặt phòng!H3:H", room,$A4, dt,C$3, ftr,FILTER(INDIRECT(from_to),(INDIRECT(booked_room)=room)* (INDIRECT(status)="Tentative")), SUM( MAP(CHOOSECOLS(ftr,1),INDEX(CHOOSECOLS(ftr,2)-1,0), LAMBDA(st,en, N(ISBETWEEN(dt,st,en)))) ) )
Công thức Google Sheets này là hạt nhân của mẫu lịch đặt và tình trạng đặt phòng. Nó vẽ thanh cho các đêm đã đặt trong khu vực biểu đồ. Làm thế nào?
Giải thích công thức:
- Hàm LET xác định bốn biến:
- from_to: Khoảng ô trong sheet “Đặt phòng” chứa ngày nhận phòng và ngày trả phòng của mỗi đặt phòng.
- booked_room: Khoảng ô trong sheet “Đặt phòng” chứa số phòng của từng đặt phòng.
- status: Khoảng ô trong sheet “Đặt phòng” chứa trạng thái của từng đặt phòng.
- room: Số phòng trong ô A4 của sheet “Sẵn có”.
- dt: Ngày trong ô C3 của sheet “Sẵn có”.
- ftr: Lọc các ô trong from_to dựa trên điều kiện r_room = room và status = “Tentative”.
- Hàm FILTER lọc phạm vi from_to để chỉ bao gồm các hàng mà số phòng trong r_room trùng khớp với giá trị của biến room và trạng thái đặt phòng là “Tentative”.
- Hàm CHOOSECOLS tách phạm vi lọc from_to thành hai phần: ngày bắt đầu và ngày kết thúc, được đặt tên là st và en trong hàm MAP.
- Hàm MAP lặp qua st và en trong ISBETWEEN để kiểm tra xem dt (C$3) có nằm trong khoảng đó không. Đầu ra sẽ là TRUE hoặc FALSE, hàm N chuyển đổi thành 1 hoặc 0. Hàm SUM trả về tổng của nó.
- Nếu tổng lớn hơn 0, ô đó được tô màu.
Xác nhận đặt phòng (Thay đổi màu cam thành xanh nhạt)
Cùng quy tắc “Cam” đã được sao chép và sửa đổi với những thay đổi sau:
- Cập nhật hàm FILTER để thay thế điều kiện “Tentative” bằng “Confirmed”.
- Điều kiện lọc trong Quy tắc “Cam” là:
(INDIRECT(status)="Tentative"). - Điều kiện lọc trong Quy tắc “Xanh nhạt” là:
(INDIRECT(status)="Confirmed").
Đêm cuối cùng (Màu đỏ)
=LEN(LET( dt,C$3, room,$A4, guest,"Đặt phòng!$B$3:$B", r_room,"Đặt phòng!$A$3:$A", end,"Đặt phòng!$F$3:$F", IFNA(FILTER(INDIRECT(guest),(INDIRECT(r_room)=room)* (INDIRECT(end)-1=dt))) ))
Giải thích công thức:
- Hàm LET xác định năm biến:
- dt: Ngày trong ô C3 của sheet “Sẵn có”.
- room: Số phòng trong ô A4 của sheet “Sẵn có”.
- guest: Khoảng ô trong sheet “Đặt phòng” chứa tên khách cho mỗi đặt phòng.
- r_room: Khoảng ô trong sheet “Đặt phòng” chứa số phòng cho mỗi đặt phòng.
- end: Khoảng ô trong sheet “Đặt phòng” chứa ngày trả phòng cho mỗi đặt phòng.
- Hàm FILTER lọc phạm vi guest để chỉ bao gồm các hàng mà số phòng trong r_room trùng khớp với giá trị của biến room và ngày trả phòng trong end trừ 1 có giá trị bằng dt.
- Hàm IFNA được sử dụng để xử lý trường hợp không có khách nào đã đặt phòng vào ngày đã cho. Trong trường hợp này, hàm IFNA trả về chuỗi trống.
- Hàm LEN trả về độ dài của chuỗi được trả về bởi hàm IFNA. Nếu độ dài lớn hơn 0, công thức sẽ tô màu ô đó.
- Quy tắc này được áp dụng cho toàn bộ khu vực biểu đồ. Vì biến dt (C$3) sử dụng một hàng tuyệt đối và biến room sử dụng một cột tuyệt đối, nó được áp dụng cho tất cả các khoảng trong biểu đồ.
Mẫu lịch đặt và tình trạng đặt phòng: Cách xem ngày trả phòng (không phải đêm cuối cùng)
Một số người dùng có thể muốn tô màu từ ngày nhận phòng đến ngày trả phòng (từ ngày nhận phòng đến ngày kết thúc). Kể trên, chúng tôi xem xét các đêm đã đặt.
Để thực hiện thay đổi này, bạn chỉ cần xóa -1 khỏi các quy tắc tô màu cam, xanh nhạt và đỏ. Bạn có thể cuộn lên để xem các công thức mà tôi đã tô đậm.
Hy vọng bạn sẽ thích mẫu lịch đặt và tình trạng đặt phòng miễn phí này! Dưới đây là một số mẫu phổ biến khác:
- Mẫu xem lịch trên Google Sheets (Mẫu tuỳ chỉnh)
- Công thức mảng để chia sẻ chi phí nhóm trong Google Sheets (Mẫu)
- Tạo mẫu theo dõi thói quen trên Google Sheets: Hướng dẫn từng bước (Mẫu)
