Báo cáo doanh thu động trong Excel cho phép chọn kỳ hoặc phân khúc rồi cập nhật bảng hiển thị từ một nguồn dữ liệu nhất quán. Trước khi dùng dropdown và công thức, hãy kiểm tra dữ liệu gốc, cách đối soát và phạm vi doanh thu được tính.
Tóm tắt nhanh
- Tạo danh sách chọn tháng, năm hoặc khu vực bằng Data Validation để một bảng báo cáo dùng được cho nhiều lát cắt.
- Liên kết lựa chọn với vùng dữ liệu và công thức tổng hợp có điều kiện; kiểm tra định dạng tháng, ngày và phạm vi dữ liệu trước khi dùng.
- Cho biểu đồ và chỉ số lấy từ cùng dữ liệu đã lọc, rồi thử thêm dòng mới và đổi từng lựa chọn để xác nhận báo cáo cập nhật đúng.
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
- Chọn ô đặt dropdown (ví dụ: B1)
- Data → Data Validation
- Allow: List
- Source:
=$A$1:$A$12 - 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
- Tháng: Ô B1 (dropdown 1-12)
- Năm: Ô B2 (dropdown 2024, 2025)
- 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$1000tblRevenue= Sheet1!$D$2:$D$1000tblLocation= 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:
- Vẽ biểu đồ từ vùng data hiện tại
- Chọn vùng data → Chart Design → Select Data
- 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.
