Dynamic revenue report [xem thêm về báo cáo doanh thu](/revenue-report-khach-san-huong-dan) với Data Validation (/dynamic-pricing-trong-khach-san-dinh-gia-dong-la-gi)

Xem thêm: Báo cáo doanh thu và Dynamic Pricing.

Một trong những ứng dụng mạnh mẽ nhất của Data Validation trong Excel là tạo báo cáo “động” — thay đổi nội dung chỉ bằng một cú chọn từ dropdown. Thay vì tạo 12 sheet cho 12 tháng, bạn chỉ cần một sheet với dropdown chọn tháng.

Khái niệm Dynamic Report

Dynamic report = Báo cáo tự động thay đổi nội dung dựa trên lựa chọn của người dùng.

Ví dụ:

• Chọn “Tháng 1” → Báo cáo hiển thị data tháng 1
• Chọn “Tháng 6” → Báo cáo tự động cập nhật data tháng 6
• Chọn “Khu vực Đà Nẵng” → Filter chỉ hiển thị data Đà Nẵng

Thiết lập Data Validation Dropdown

Bước 1: Tạo danh sách tháng

Tạo một vùng ô chứa 12 tháng:

Ô	Giá trị
A1	Tháng 1
A2	Tháng 2
…	…
A12	Tháng 12

Bước 2: Thiết lập Data Validation

1. Chọn ô đặt dropdown (ví dụ: B1)
2. Data → Data Validation
3. Allow: List
4. Source: =$A$1:$A$12
5. OK

Giờ ô B1 sẽ có dropdown với 12 lựa chọn.

Kết hợp INDIRECT với SUMIFS

INDIRECT cho phép bạn tham chiếu đến một ô có tên được xác định bởi giá trị của ô khác.

Ví dụ: Chọn tháng → Xem Occupancy

=SUMPRODUCT((MONTH(Date)=VALUE(SUBSTITUTE(B1,"Tháng ","")))*(Occupancy))

Hoặc dùng INDEX/MATCH:

=INDEX(DataRange, MATCH(B1, MonthList, 0))

Tạo báo cáo Dynamic với nhiều điều kiện

Thiết lập dropdown cho Tháng, Năm, Khu vực

1. Tháng: Ô B1 (dropdown 1-12)
2. Năm: Ô B2 (dropdown 2024, 2025)
3. Khu vực: Ô B3 (dropdown Đà Nẵng, Hội An, Huế)

Công thức với 3 điều kiện

=SUMPRODUCT((YEAR(Date)=B2)*(MONTH(Date)=VALUE(B1))*(Location=B3)*Revenue)

Công thức này lọc:

• YEAR = ô B2
• MONTH = ô B1
• LOCATION = ô B3

Dùng Named Ranges với INDIRECT

Đặt tên cho các vùng data để công thức dễ đọc:

• tblDate = Sheet1!$A$2:$A$1000
• tblRevenue = Sheet1!$D$2:$D$1000
• tblLocation = Sheet1!$E$2:$E$1000

Công thức:

=SUMPRODUCT((YEAR(tblDate)=B2)*(MONTH(tblDate)=VALUE(B1))*(tblLocation=B3)*tblRevenue)

OFFSET cho Dynamic Range

OFFSET tạo vùng data động, tự động mở rộng khi thêm dữ liệu mới:

=OFFSET(Data!$A$1, 0, 0, COUNTA(Data!$A:$A), 1)

Giải thích:

• Bắt đầu từ A1
• Không dịch chuyển hàng/cột
• Chiều cao = số ô không trống trong cột A
• Chiều rộng = 1 cột

Tạo Dynamic Chart

Sau khi thiết lập Dynamic Report, bạn có thể tạo biểu đồ động:

1. Vẽ biểu đồ từ vùng data hiện tại
2. Chọn vùng data → Chart Design → Select Data
3. Trong Chart data range, dùng OFFSET để tạo vùng động:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),3)

Khi thêm data mới, chart tự động cập nhật.

Tạo Dashboard với nhiều Dynamic Elements

Khu vực Filter (phía trên)

Ô	Nội dung
B1	Dropdown: Chọn Tháng
B2	Dropdown: Chọn Năm
B3	Dropdown: Chọn Khu vực

Khu vực KPIs

Các ô KPI sẽ tự động cập nhật khi thay đổi dropdown:

Total Revenue: =SUMPRODUCT((YEAR(Date)=B2)*(MONTH(Date)=VALUE(B1))*(Location=B3)*Revenue)
Occupancy: =SUMPRODUCT((YEAR(Date)=B2)*(MONTH(Date)=VALUE(B1))*(Location=B3)*Occupied)/SUMPRODUCT((YEAR(Date)=B2)*(MONTH(Date)=VALUE(B1))*Available)

Mẹo: Dùng SWITCH thay cho nhiều IF

Nếu muốn xử lý logic phức tạp theo từng tháng:

=SWITCH(B1, "Tháng 1", JanRevenue, "Tháng 2", FebRevenue, "Tháng 3", MarRevenue, ... )

SWITCH ngắn gọn hơn nhiều so với lồng nhiều IF.

Tóm tắt

Công cụ	Ứng dụng
Data Validation (List)	Tạo dropdown chọn
INDIRECT	Tham chiếu động
OFFSET	Tạo vùng động
SUMPRODUCT	Tính tổng nhiều điều kiện
SWITCH	Thay nhiều IF

Dynamic Report là bước tiến lớn từ báo cáo tĩnh. Hãy bắt đầu với một dropdown và một công thức SUMPRODUCT đơn giản, sau đó mở rộng dần.