EXCEL: CÁCH LÀM BIỂU ĐỒ ĐỘNG VỚI NAMED RANGE & OFFSET

By

Khi biểu đồ tự cập nhật thay vì bạn phải sửa tay mỗi tháng

🎯 MỤC TIÊU BÀI HỌC

Sau bài này, bạn sẽ:

  • Hiểu vì sao biểu đồ Excel thường “chết cứng”
  • Biết cách dùng Named Range + hàm OFFSET để:
    • Biểu đồ tự mở rộng khi thêm dòng mới
    • Không cần chỉnh lại vùng dữ liệu
  • Áp dụng biểu đồ động cho:
    • Báo cáo tháng
    • Theo dõi KPI
    • Chuẩn bị dữ liệu cho Power BI

⚠️ VẤN ĐỀ THỰC TẾ TRONG DOANH NGHIỆP

Trong đa số file Excel:

  • Biểu đồ chỉ tham chiếu một vùng cố định
  • Thêm dữ liệu mới → biểu đồ không đổi
  • Muốn cập nhật → phải:
    • Chỉnh lại vùng
    • Vẽ lại biểu đồ
    • Dễ sai, mất thời gian

👉 Đây là lý do:

  • Báo cáo bị trễ
  • Số liệu không khớp
  • Người làm Excel trở thành “nút thắt cổ chai”

🧠 TƯ DUY ĐÚNG: BIỂU ĐỒ PHẢI THEO DỮ LIỆU, KHÔNG THEO CON NGƯỜI

Dữ liệu tăng → biểu đồ tự tăng theo

Để làm được điều này trong Excel, ta cần:

  • Named Range (vùng dữ liệu động)
  • OFFSET (dịch chuyển & mở rộng vùng)

👉 Đây là kỹ thuật nền tảng trước khi:

  • Lên Power BI
  • Tự động hóa báo cáo

🧪 DỮ LIỆU MẪU THỰC TẾ (≥ 15 DÒNG)

Ví dụ: Theo dõi doanh thu theo ngày

order_daterevenue
2025-01-015,000,000
2025-01-026,200,000
2025-01-034,800,000
2025-01-047,100,000
2025-01-056,900,000
2025-01-065,500,000
2025-01-078,200,000
2025-01-087,600,000
2025-01-096,400,000
2025-01-109,100,000
2025-01-118,700,000
2025-01-127,300,000
2025-01-139,800,000
2025-01-1410,200,000
2025-01-159,500,000

👉 Dữ liệu này sẽ còn tiếp tục tăng mỗi ngày.

🛠️ STEP BY STEP: TẠO BIỂU ĐỒ ĐỘNG VỚI NAMED RANGE & OFFSET

🔹 STEP 1: Chuẩn hóa bảng dữ liệu

  • Dữ liệu:
    • order_date ở cột A
    • revenue ở cột B
  • Không để dòng trống giữa bảng

👉 OFFSET rất nhạy với dữ liệu không liên tục.

🔹 STEP 2: Đếm số dòng dữ liệu (COUNTA)

Ta cần biết bảng đang có bao nhiêu dòng:

=COUNTA($A$2:$A$1000)

👉 Công thức này:

  • Tự tăng khi thêm ngày mới
  • Là “động cơ” cho biểu đồ động

🔹 STEP 3: Tạo Named Range cho trục X (ngày)

Vào Formulas → Name Manager → New

Name: date_range
Refers to:

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

👉 Ý nghĩa:

  • Bắt đầu từ A2
  • Mở rộng số dòng đúng bằng dữ liệu thực tế

🔹 STEP 4: Tạo Named Range cho trục Y (doanh thu)

Name: revenue_range
Refers to:

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000),1)

👉 Hai vùng này luôn đồng bộ số dòng.

🔹 STEP 5: Vẽ biểu đồ từ Named Range

  • Insert → Line Chart (hoặc Column Chart)
  • Trong Select Data:
    • Series Values: =Sheet1!revenue_range
    • Horizontal Axis Labels: =Sheet1!date_range

👉 Từ giờ:

  • Thêm dòng mới
  • Biểu đồ tự cập nhật
  • Không chỉnh tay nữa

📊 PHÂN TÍCH GIÁ TRỊ MANG LẠI

TrướcSau
Biểu đồ tĩnhBiểu đồ động
Phải sửa tayTự cập nhật
Dễ saiỔn định
Khó mở rộngChuẩn bị sẵn cho BI

👉 Đây là bước trung gian cực kỳ quan trọng trước khi lên Power BI.

🧾 KẾT LUẬN & ĐÁNH GIÁ

  • Biểu đồ động không phải kỹ thuật nâng cao
  • Nhưng là:
    • Dấu hiệu của tư duy hệ thống
    • Nền tảng của báo cáo tự động
  • Named Range + OFFSET giúp Excel:
    • “Sống” cùng dữ liệu
    • Không phụ thuộc người chỉnh

👉 Nếu biểu đồ của bạn vẫn phải sửa tay, vấn đề không nằm ở Excel.

🚀 GỢI Ý ÁP DỤNG THỰC TẾ

👥 Nhân sự

  • Theo dõi:
    • Số nhân sự
    • Nghỉ việc theo thời gian
  • Biểu đồ tự cập nhật hàng tháng

💰 Kế toán

  • Doanh thu – chi phí theo ngày
  • Không lo thiếu dòng cuối kỳ

📦 Quản lý kho

  • Nhập – xuất – tồn theo ngày
  • Biểu đồ luôn phản ánh dữ liệu mới nhất

🧠 Quản lý / Chủ doanh nghiệp

  • Xem xu hướng
  • Ra quyết định nhanh
  • Chuẩn bị dữ liệu lên Power BI

Scroll to Top