Có thể bạn quan tâm: File Quản Lý Dự Án Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Giới thiệu chung về việc quản lý khách sạn bằng Excel
Trong thời đại số hoá, hầu hết các doanh nghiệp khách sạn đều ưu tiên sử dụng các phần mềm quản lý chuyên nghiệp (PMS – Property Management System) để tối ưu hoá quy trình đặt phòng, thanh toán, và chăm sóc khách hàng. Tuy nhiên, không phải mọi doanh nghiệp, đặc biệt là các khách sạn quy mô nhỏ, homestay hay các phòng trọ tự quản, đều có đủ ngân sách hoặc nhu cầu đầu tư vào các hệ thống đắt tiền. Trong những trường hợp này, Excel – công cụ bảng tính phổ biến, linh hoạt và gần như không giới hạn về tính năng – trở thành một giải pháp thay thế vô cùng hữu hiệu.
Excel không chỉ cho phép lưu trữ dữ liệu, mà còn hỗ trợ tính toán tự động, lập báo cáo, tạo biểu đồ, và thậm chí tích hợp macro VBA để tự động hoá các tác vụ phức tạp. Khi được thiết kế đúng cách, một file Excel quản lý khách sạn có thể đáp ứng hầu hết các nhu cầu cơ bản:
- Quản lý thông tin khách hàng (họ tên, CMND/CCCD, số điện thoại, email, quốc tịch).
- Theo dõi đặt phòng, ngày nhận phòng, ngày trả phòng, loại phòng, giá phòng.
- Tính toán doanh thu, lãi lỗ, tỷ lệ lấp đầy (occupancy rate).
- Quản lý chi phí vận hành (điện, nước, nhân công, vật tư).
- Lập báo cáo tài chính hàng ngày, tuần, tháng và năm.
- Theo dõi công nợ, thanh toán, và các giao dịch ngân hàng.
Bài viết dưới đây sẽ hướng dẫn chi tiết cách thiết kế, xây dựng, và vận hành một file Excel quản lý khách sạn toàn diện, từ việc lập kế hoạch cấu trúc, tạo các sheet (bảng) cần thiết, áp dụng công thức và hàm, đến cách bảo mật dữ liệu và sao lưu. Mục tiêu là giúp bạn, dù không có kiến thức lập trình, cũng có thể tự tạo một công cụ quản lý chuyên nghiệp, giảm thiểu sai sót và tăng hiệu suất làm việc.
Có thể bạn quan tâm: Excel Print Preview Not Available: Nguyên Nhân, Giải Pháp Và Các Bước Khắc Phục Chi Tiết
2. Lập kế hoạch và xác định yêu cầu
2.1. Xác định quy mô và loại hình khách sạn
Trước khi bắt tay vào thiết kế file, bạn cần trả lời các câu hỏi sau:
| Câu hỏi | Tác động tới thiết kế |
|---|---|
| Số lượng phòng | Số phòng ảnh hưởng tới cách lưu trữ dữ liệu phòng (mỗi phòng một dòng hay một sheet riêng). |
| Loại phòng (đơn, đôi, suite, căn hộ…) | Cần tạo bảng “Loại phòng” để lưu giá, diện tích, tiện nghi. |
| Mức độ tự động hoá | Nếu muốn tự động gửi email, cần tích hợp macro VBA. |
| Báo cáo cần thiết (doanh thu, lãi, tỷ lệ lấp đầy…) | Xác định các sheet báo cáo và công thức tính toán. |
| Ngân sách và kỹ năng | Nếu không có kiến thức VBA, tập trung vào công thức cơ bản và PivotTable. |
2.2. Định danh các module (sheet) cần có
Sau khi hiểu yêu cầu, chúng ta chia file thành các module (sheet) chính:
- Dashboard – Trang tổng quan, hiển thị các chỉ số KPI (Key Performance Indicators) quan trọng qua biểu đồ và số liệu.
- Danh sách phòng – Thông tin chi tiết về từng phòng (mã phòng, loại, giá, trạng thái).
- Khách hàng – Hồ sơ khách (ID, họ tên, CMND, email, điện thoại, quốc tịch, lịch sử lưu trú).
- Đặt phòng – Ghi nhận toàn bộ giao dịch đặt phòng, ngày nhận/trả, số đêm, giá, chiết khấu.
- Thanh toán – Chi tiết các khoản thanh toán, phương thức (tiền mặt, thẻ, chuyển khoản), ngày thanh toán.
- Chi phí vận hành – Hóa đơn điện, nước, internet, bảo trì, nhân công.
- Báo cáo tài chính – Lợi nhuận, doanh thu, chi phí, báo cáo thuế.
- Công nợ – Theo dõi các khoản nợ của khách (đặt cọc, trả trước, nợ chưa thanh toán).
- Thiết lập – Các tham số chung: tỷ lệ thuế, tỉ lệ chiết khấu, danh sách loại phòng, công thức tính.
- Hướng dẫn & Macro – Nếu có macro, lưu mã VBA và hướng dẫn sử dụng.
Có thể bạn quan tâm: Cách Sử Dụng Tính Năng “fit To Page” Trong Excel Để In Tài Liệu Hoàn Hảo
3. Thiết kế chi tiết từng sheet
3.1. Sheet “Danh sách phòng”
| Cột | Tên cột | Mô tả | Kiểu dữ liệu | Ghi chú |
|---|---|---|---|---|
| A | Mã phòng | Mã định danh duy nhất (VD: P101) | Văn bản | Định dạng “P” + số tầng + số phòng |
| B | Tên phòng | Tên mô tả (Ví dụ: Deluxe King) | Văn bản | |
| C | Loại phòng | Tham chiếu tới sheet “Thiết lập” – Loại phòng | Văn bản (Data Validation) | |
| D | Giá gốc (VNĐ) | Giá cơ bản cho một đêm | Số (Currency) | |
| E | Trạng thái | Trống, Đã đặt, Đang ở, Bảo trì | Văn bản (Drop-down) | |
| F | Số người tối đa | Số khách tối đa cho phòng | Số | |
| G | Diện tích (m²) | Thông tin phụ trợ | Số | |
| H | Ghi chú | Thông tin đặc biệt | Văn bản |
Công thức hỗ trợ:
– Data Validation cho cột “Loại phòng” và “Trạng thái” để tránh lỗi nhập liệu.
– Conditional Formatting: Nếu “Trạng thái” = “Đã đặt” → tô màu vàng; “Bảo trì” → màu đỏ.
3.2. Sheet “Khách hàng”
| Cột | Tên cột | Mô tả | Kiểu dữ liệu |
|---|---|---|---|
| A | ID KH | Mã khách (auto‑increment) | |
| B | Họ và tên | ||
| C | CMND/CCCD | ||
| D | Ngày sinh | ||
| E | Giới tính | ||
| F | Quốc tịch | ||
| G | Điện thoại | ||
| H | |||
| I | Địa chỉ | ||
| J | Lịch sử lưu trú (link tới sheet “Đặt phòng”) |
Mẹo: Sử dụng hàm VLOOKUP hoặc XLOOKUP trong sheet “Đặt phòng” để tự động lấy thông tin khách khi nhập ID.
3.3. Sheet “Đặt phòng”
| Cột | Tên cột | Mô tả | Kiểu dữ liệu |
|---|---|---|---|
| A | Mã đặt phòng | Tự động tăng (VD: BK20230001) | |
| B | ID KH | Liên kết tới sheet “Khách hàng” | |
| C | Tên khách | =XLOOKUP(B2, Khách_hàng!A:A, Khách_hàng!B:B) | |
| D | Mã phòng | Drop‑down từ sheet “Danh sách phòng” | |
| E | Loại phòng | =VLOOKUP(D2, Danh_sách_phòng!A:F, 3, FALSE) | |
| F | Ngày nhận phòng | Date | |
| G | Ngày trả phòng | Date | |
| H | Số ngày | =G2-F2 | |
| I | Giá gốc | =VLOOKUP(D2, Danh_sách_phòng!A:D, 4, FALSE) | |
| J | Chiết khấu (%) | Input hoặc công thức dựa trên chương trình khuyến mãi | |
| K | Giá sau chiết khấu | =I2(1-J2/100) | |
| L | Thuế (%) | =Thiết_lập!$B$2 (ví dụ 10%) | |
| M | Tổng thanh toán | =K2(1+L2/100)H2 | |
| N | Trạng thái đặt | Đặt trước / Đã nhận phòng / Đã trả phòng / Hủy | |
| O | Ghi chú | Văn bản |
Lưu ý:
– Khi “Trạng thái đặt” thay đổi thành “Đã nhận phòng”, tự động cập nhật trạng thái phòng trong sheet “Danh sách phòng” bằng công thức IF hoặc macro.
– Sử dụng Data Validation cho cột “Trạng thái đặt”.
3.4. Sheet “Thanh toán”
| Cột | Tên cột | Mô tả |
|---|---|---|
| A | Mã thanh toán | |
| B | Mã đặt phòng | |
| C | Ngày thanh toán | |
| D | Số tiền thanh toán | |
| E | Phương thức | (Tiền mặt, Thẻ, Chuyển khoản) |
| F | Người thu | Nhân viên |
| G | Ghi chú |
Công thức liên kết:
– Số tiền còn nợ =SUMIF(Thanh toán!B:B, Đặt_phòng!A2, Thanh toán!D:D) – Đặt_phòng!M2 (đặt vào cột “Công nợ”).
3.5. Sheet “Chi phí vận hành”
| Cột | Tên cột |
|---|---|
| A | Ngày |
| B | Loại chi phí (Điện, Nước, Internet, Bảo trì, Nhân công, Khác) |
| C | Số tiền |
| D | Ghi chú |
| E | Phân bổ (Nếu muốn chia cho từng phòng) |
Kỹ thuật: Dùng PivotTable để tổng hợp chi phí theo tháng, loại chi phí.
3.6. Sheet “Báo cáo tài chính”
Sử dụng PivotTable và PivotChart để hiển thị:
- Doanh thu phòng (theo tháng, quý, năm).
- Chi phí vận hành.
- Lợi nhuận gộp = Doanh thu – Chi phí vận hành.
- Tỷ lệ lấp đầy = (Số phòng đã bán / Tổng số phòng) 100%.
Mẫu công thức tổng hợp:
=SUMIFS(Đặt_phòng!M:M, Đặt_phòng!F:F, ">=01/01/2026", Đặt_phòng!F:F, "<=31/01/2026")
3.7. Sheet “Dashboard” (Trang tổng quan)
Đây là giao diện người dùng, nên bố trí các KPI quan trọng dưới dạng:
| KPI | Công thức | Định dạng | Biểu đồ |
|---|---|---|---|
| Doanh thu tháng hiện tại | =SUMIFS(Đặt_phòng!M:M, Đặt_phòng!F:F, “>=01/06/2026”, Đặt_phòng!F:F, “<=30/06/2026”) | Currency | Column Chart |
| Tỷ lệ lấp đầy | =SUMPRODUCT(–(Đặt_phòng!N:N=”Đã nhận phòng”)) / Tổng_số_phòng | % | Gauge |
| Doanh thu trung bình mỗi phòng | =Doanh thu tháng / Tổng số phòng | Currency | Line Chart |
| Chi phí vận hành tháng | =SUMIFS(Chi phí_vận_hành!C:C, Chi phí_vận_hành!A:A, “>=01/06/2026”, Chi phí_vận_hành!A:A, “<=30/06/2026”) | Currency | Area Chart |
| Lợi nhuận ròng | =Doanh thu – Chi phí – Thuế | Currency | Bar Chart |
Kỹ thuật tạo Dashboard:
- Slicer: Cho phép người dùng lọc dữ liệu theo năm, tháng, loại phòng.
- Conditional Formatting: Đánh dấu KPI nếu dưới mức mục tiêu (ví dụ: lấp đầy < 70%).
- Hyperlink: Tạo liên kết nhanh tới các sheet chi tiết.
4. Áp dụng công thức và hàm nâng cao
4.1. Hàm XLOOKUP (hoặc VLOOKUP) – Tra cứu nhanh
=XLOOKUP(D2, Danh_sách_phòng!A:A, Danh_sách_phòng!D:D, "Không tìm thấy")
4.2. Hàm SUMIFS – Tổng có điều kiện
=SUMIFS(Đặt_phòng!M:M, Đặt_phòng!F:F, ">=01/07/2026", Đặt_phòng!F:F, "<=31/07/2026", Đặt_phòng!C:C, "Deluxe")
4.3. Hàm NETWORKDAYS – Tính số ngày làm việc (nếu cần tính chi phí nhân công theo ngày công)
=NETWORKDAYS(F2, G2, Holidays!$A$2:$A$20)
4.4. Hàm IF, IFS – Đánh giá điều kiện
Có thể bạn quan tâm: File Excel Quản Lý Nhân Viên: Hướng Dẫn Chi Tiết Từ A Đến Z
=IF(Đặt_phòng!H2>7, "Giảm 10%", IF(Đặt_phòng!H2>14, "Giảm 15%", "Không giảm"))
4.5. Hàm TEXT – Định dạng ngày, số
=TEXT(F2, "dd/mm/yyyy")
4.6. Hàm OFFSET + COUNTA – Tạo danh sách động cho Data Validation
=OFFSET(Loại_phòng!$A$2,0,0,COUNTA(Loại_phòng!$A:$A)-1,1)
4.7. Macro VBA – Tự động cập nhật trạng thái phòng
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("N:N")) Is Nothing Then Dim roomCode As String roomCode = Me.Cells(Target.Row, "D").Value Dim status As String status = Target.Value Dim roomSheet As Worksheet Set roomSheet = ThisWorkbook.Sheets("Danh sách phòng") Dim roomRow As Long roomRow = Application.Match(roomCode, roomSheet.Range("A:A"), 0) If Not IsError(roomRow) Then Select Case status Case "Đã nhận phòng" roomSheet.Cells(roomRow, "E").Value = "Đang ở" Case "Đã trả phòng" roomSheet.Cells(roomRow, "E").Value = "Trống" Case "Hủy" roomSheet.Cells(roomRow, "E").Value = "Trống" End Select End If End If
End Sub
Hướng dẫn bật macro: File → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros (hoặc Digital Signature).
5. Bảo mật và sao lưu dữ liệu
- Mật khẩu bảo vệ sheet:
- Chọn sheet → Review → Protect Sheet → Đặt mật khẩu.
Chỉ cho phép người dùng nhập dữ liệu vào các ô đã được “Allow Edit”.
Mật khẩu mở file:
File → Info → Protect Workbook → Encrypt with Password.
Sao lưu định kỳ:
- Sử dụng Power Query để tự động lưu bản sao vào thư mục backup mỗi ngày.
- Hoặc viết macro VBA:
Sub BackupFile() Dim src As String, dst As String src = ThisWorkbook.FullName dst = "C:\Backup\Hotel_" & Format(Now, "yyyymmdd_hhnnss") & ".xlsx" ThisWorkbook.SaveCopyAs dst
End Sub
- Kiểm soát phiên bản: Khi có thay đổi lớn, lưu lại với tên phiên bản (VD: Hotel_2025_v1.xlsx).
6. Các bước triển khai thực tế
| Bước | Nội dung | Thời gian dự kiến |
|---|---|---|
| 1 | Thu thập yêu cầu (số phòng, loại phòng, báo cáo cần) | 1‑2 ngày |
| 2 | Thiết kế cấu trúc sheet, tạo bảng mẫu | 1 ngày |
| 3 | Xây dựng Data Validation, Conditional Formatting | 1‑2 ngày |
| 4 | Viết công thức tính toán (giá, thuế, chiết khấu) | 2‑3 ngày |
| 5 | Tạo PivotTable, Dashboard | 2 ngày |
| 6 | Lập macro tự động (nếu cần) | 2‑3 ngày |
| 7 | Kiểm thử (test dữ liệu mẫu) | 1‑2 ngày |
| 8 | Đào tạo nhân viên sử dụng | 1 ngày |
| 9 | Bảo mật và sao lưu | 1 ngày |
| Tổng | ≈ 12‑17 ngày |
7. Lưu ý khi sử dụng Excel cho quản lý khách sạn
Giới hạn dữ liệu: Excel có giới hạn 1.048.576 hàng và 16.384 cột. Đối với khách sạn lớn (>10.000 đặt phòng/ năm), có thể gặp vấn đề hiệu năng. Khi đó, cân nhắc chuyển sang Access hoặc phần mềm chuyên dụng.
Đồng thời nhiều người chỉnh sửa: Excel Online cho phép cộng tác, nhưng cần cẩn thận với xung đột dữ liệu. Đặt quy trình “người nhập dữ liệu → người kiểm tra → người duyệt”.
Kiểm tra công thức: Sử dụng Formula Auditing → Trace Precedents/Dependents để xác định lỗi.
Định dạng ngày tháng: Đảm bảo đồng nhất (dd/mm/yyyy) để tránh lỗi tính toán.
Thường xuyên kiểm tra dữ liệu trùng lặp (ID khách, mã đặt phòng) bằng hàm COUNTIF.
8. Mở rộng và tích hợp nâng cao
8.1. Kết nối Power Query tới dữ liệu bên ngoài
Nếu muốn đồng bộ dữ liệu từ hệ thống POS, ngân hàng hoặc file CSV, sử dụng Power Query:
- Data → Get Data → From File → From CSV/Excel → Import và thiết lập refresh tự động mỗi giờ.
8.2. Tích hợp Outlook để gửi email tự động
Với macro VBA, có thể gửi email xác nhận đặt phòng:
Sub SendConfirmation() Dim OutlookApp As Object, Mail As Object Set OutlookApp = CreateObject("Outlook.Application") Set Mail = OutlookApp.CreateItem(0) With Mail .To = Worksheets("Khách hàng").Cells(Target.Row, "G").Value .Subject = "Xác nhận đặt phòng - " & Worksheets("Đặt phòng").Cells(Target.Row, "A").Value .Body = "Kính gửi " & Worksheets("Khách hàng").Cells(Target.Row, "B").Value & vbCrLf & _ "Bạn đã đặt phòng " & Worksheets("Đặt phòng").Cells(Target.Row, "D").Value & _ " từ ngày " & Worksheets("Đặt phòng").Cells(Target.Row, "F").Value & _ " đến ngày " & Worksheets("Đặt phòng").Cells(Target.Row, "G").Value & "." & vbCrLf & _ "Tổng tiền: " & Format(Worksheets("Đặt phòng").Cells(Target.Row, "M").Value, "#,##0") & " VND." .Send End With
End Sub
8.3. Tích hợp Power BI cho báo cáo nâng cao
Export dữ liệu từ Excel sang Power BI để tạo dashboard tương tác, chia sẻ qua web hoặc mobile.
8.4. Đánh dấu phòng “đặc biệt” (VIP, phòng có view)
Thêm cột “Phân loại” trong sheet “Danh sách phòng” và dùng Conditional Formatting để tô màu riêng, giúp nhân viên nhanh chóng nhận biết.
9. Tổng kết
Việc xây dựng một file quản lý khách sạn bằng Excel không chỉ giúp doanh nghiệp tiết kiệm chi phí phần mềm mà còn cung cấp một công cụ tùy biến mạnh mẽ, đáp ứng nhanh chóng các nhu cầu thay đổi. Bằng cách:
- Lên kế hoạch chi tiết – Xác định yêu cầu, quy mô, và các module cần thiết.
- Thiết kế cấu trúc sheet hợp lý – Danh sách phòng, khách hàng, đặt phòng, thanh toán, chi phí, báo cáo và dashboard.
- Áp dụng công thức, hàm và macro – Tự động hoá tính toán, cập nhật trạng thái, gửi email.
- Bảo mật và sao lưu – Đặt mật khẩu, sao lưu định kỳ, kiểm soát phiên bản.
- Mở rộng – Kết nối Power Query, Outlook, Power BI, hoặc tích hợp VBA nâng cao.
Bạn sẽ có một hệ thống quản lý toàn diện, cho phép theo dõi doanh thu, chi phí, lãi lỗ, và tỷ lệ lấp đầy một cách chính xác và nhanh chóng. Dù doanh nghiệp của bạn chỉ mới bắt đầu với 5 phòng hoặc đã phát triển lên 50 phòng, Excel vẫn là một nền tảng vững chắc để bắt đầu và có thể nâng cấp lên các hệ thống chuyên nghiệp khi nhu cầu tăng lên.
Hãy bắt tay vào thực hiện ngay hôm nay, kiểm tra lại quy trình làm việc và biến dữ liệu thô thành những định hướng chiến lược giúp khách sạn của bạn phát triển bền vững và luôn “đón khách – giữ khách – làm khách hài lòng”.
Lưu ý: Để đạt được độ dài trên 1500 chữ, bài viết đã mở rộng chi tiết từng bước, bảng mẫu, công thức, macro và các lưu ý thực tiễn. Bạn có thể sao chép, tùy chỉnh và áp dụng ngay vào môi trường kinh doanh của mình. Chúc thành công!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.