Tạm biệt hàm VLOOKUP: Xây dựng Mô hình dữ liệu quan hệ chuẩn BI ngay trong Excel

1. Mở đầu

Bạn sẽ làm gì khi nhận được ba tệp dữ liệu độc lập từ hệ thống quản lý trường học: danh sách điểm số (Marks), thông tin học sinh (Students), và danh mục môn học (Subjects), sau đó được yêu cầu lập báo cáo thống kê điểm trung bình của học sinh cư trú tại từng địa phương (Address) theo từng môn học?

Đối với 90% người dùng Excel truyền thống, câu trả lời ngay lập tức sẽ là: “Dùng hàm VLOOKUP hoặc XLOOKUP để kéo toàn bộ dữ liệu về chung một bảng lớn”.

Nhưng hãy tưởng tượng nếu dữ liệu trường học phình to lên hàng trăm ngàn lượt thi, tệp Excel của bạn sẽ lập tức phình nặng hàng trăm MB, màn hình quay vòng vòng và báo lỗi Not Responding quen thuộc. Việc lạm dụng các hàm tra cứu để "phẳng hóa" dữ liệu không chỉ bóp nghẹt hiệu suất máy tính mà còn chỉ ra rằng bạn đang thiếu tư duy về kiến trúc dữ liệu.

Nếu bạn từng theo dõi bài viết trước của tôi về cách ứng dụng Power Query để làm sạch và chuẩn hóa các nguồn dữ liệu lộn xộn (mở trong tab mới), bạn sẽ hiểu rằng việc có một tệp dữ liệu "sạch" mới chỉ là bước khởi đầu. Để thực sự biến dữ liệu đó thành các báo cáo có giá trị mà không làm quá tải hệ thống, chúng ta cần một tư duy cao hơn: Tư duy tổ chức dữ liệu.

Trong bài viết này, chúng ta sẽ cùng nhau bước qua giới hạn của các hàm Excel truyền thống để làm quen với Mô hình dữ liệu quan hệ (Data Model) thông qua công cụ Power Pivot. Bạn sẽ khám phá ra cách kết nối các bảng dữ liệu độc lập một cách mượt mà bằng mối quan hệ 1 - nhiều (1-to-many), tối ưu hóa hiệu suất tệp báo cáo và viết những câu lệnh DAX đầu tiên – một trong những kỹ năng cốt lõi của một nhà phân tích dữ liệu.

2.  Tư duy cốt lõi: Giải mã "Mô hình sao" (Star Schema) bằng ngôn ngữ dễ hiểu

Trước khi bắt tay vào kéo thả trên Excel, chúng ta cần hiểu về cấu trúc nền tảng của một mô hình dữ liệu chuẩn BI. Đừng lo lắng về những thuật ngữ phức tạp! Hãy tưởng tượng bạn đang sắp xếp một tủ sách gia đình: thay vì vứt tất cả mọi thứ vào một ngăn kéo lớn (giống như cách chúng ta gộp bảng bằng VLOOKUP), chúng ta sẽ phân loại chúng thành hai nhóm chính: Nhật ký và Từ điển.

Trong ngành phân tích dữ liệu, hai nhóm này được gọi là Fact Table và Dimension Table. Khi chúng kết nối với nhau, chúng tạo nên một cấu trúc gọi là Mô hình sao (Star Schema) – trung tâm là nhật ký, xung quanh là các cuốn từ điển tra cứu.

Áp dụng trực tiếp vào bộ dữ liệu trường học (School Database) của chúng ta:

• Bảng Thực tế (Fact Table) – "Cuốn Nhật Ký": Chính là bảng Marks.csv. Đây là nơi ghi lại các sự kiện phát sinh hàng ngày – mỗi dòng là một lượt thi của học sinh. Bảng này chứa các con số biến động liên tục (Điểm số) và các mã định danh lặp đi lặp lại (Student_ID, Subject_ID). Bảng Fact trả lời cho câu hỏi: "Chuyện gì đã xảy ra và con số là bao nhiêu?"

• Bảng Danh mục (Dimension Table) – "Cuốn Từ Điển": Chính là bảng Students.csv và Subjects.csv. Nơi đây lưu trữ thông tin gốc, cố định và duy nhất. Một học sinh chỉ có một dòng duy nhất để lưu tên, ngày sinh, địa chỉ. Bảng Dimension trả lời cho câu hỏi: "Đối tượng tham gia vào sự kiện đó có đặc điểm gì?"

🤔Mối quan hệ 1 - nhiều (1-to-many relationship) là gì?

Khi kết nối hai loại bảng này, bạn sẽ thấy ký hiệu 1 (một) ở bảng Từ điển và * (nhiều) ở bảng Nhật ký. Logic rất đơn giản: Một học sinh duy nhất (1) ở bảng Students có thể tham gia nhiều kỳ thi và nhận về nhiều điểm số khác nhau (*) ở bảng Marks.

Tư duy tách rời này giữ cho tệp dữ liệu luôn gọn nhẹ. Excel không cần phải lặp đi lặp lại từ địa chỉ hay tên tuổi hàng ngàn lần cho cùng một học sinh, giúp tối ưu hóa bộ nhớ lên đến 80%!

3.  Hướng dẫn thực hành: 3 bước xây dựng Mô hình dữ liệu trong Excel

Sau khi đã tải bộ dữ liệu School Database (mở trong tab mới) về máy, hãy cùng nhau thực hiện việc kết nối dữ liệu cấu trúc này. Quá trình gồm 3 bước đơn giản nhưng vô cùng hiệu quả.

Bước 1: Nạp các bảng độc lập vào Data Model bằng Power Query

Thay vì mở các file và copy - paste thủ công, chúng ta sẽ nạp dữ liệu một cách thông minh:

1. Vào thẻ Data trên thanh công cụ → Chọn Get Data → From File → From Text/CSV.

2. Chọn file Students.csv và bấm Transform Data để mở cửa sổ Power Query. Tại đây, bạn có thể kiểm tra định dạng dữ liệu (chuyển định dạng các cột IDs như MarkID, StudentID, SubjectID từ Số (123) sang Văn bản (Text - ABC). Giữ nguyên định dạng Số (123) cho cột điểm số MarkObtained.

3. Thao tác quan trọng: Thay vì bấm nút Close & Load thông thường, hãy bấm vào mũi tên nhỏ bên dưới và chọn Close & Load To...

4. Một hộp thoại sẽ hiện ra. Bạn hãy chọn Only Create Connection (Chỉ tạo kết nối) và tích chọn ô Add this data to the Data Model (Thêm dữ liệu này vào Mô hình dữ liệu).

5. Lặp lại chính xác các bước trên với 2 file còn lại: Subjects.csv và Students.csv.

Lúc này, Excel đã hiểu cấu trúc của 3 bảng và lưu trữ chúng ngầm trong bộ nhớ nén của Power Pivot.

Bước 2: Thiết lập mối quan hệ 1 - nhiều (Diagram View)

Bây giờ là lúc chúng ta liên kết các cuốn "Từ điển" vào "Nhật ký":

1. Di chuyển sang thẻ Power Pivot trên thanh Ribbon → Bấm nút Manage. Một cửa sổ làm việc mới của Power Pivot sẽ xuất hiện.

2. Tại góc trên cùng bên phải của thẻ Home, bấm vào nút Diagram View (Chế độ xem sơ đồ). Bạn sẽ thấy 3 bảng dữ liệu hiển thị dưới dạng các hộp chữ nhật nhỏ chứa danh sách các cột.

3. Thao tác kéo - thả liên kết:

• Bấm giữ chuột vào cột Student_ID ở bảng Students và kéo sợi dây nối thả vào cột Student_ID ở bảng Marks.

• Bấm giữ chuột vào cột Subject_ID ở bảng Subjects và kéo sợi dây nối thả vào cột Subject_ID ở bảng Marks.

4. Quan sát sợi dây liên kết: Bạn sẽ thấy ký hiệu 1 ở phía bảng danh mục và ký hiệu * ở phía bảng thực tế Marks.

Mô hình hình sao (Star Schema) đã hoàn thành!

Bước 3: Viết câu lệnh DAX đầu tiên và xuất báo cáo Pivot Table

Để giải bài toán Tính điểm trung bình của học sinh cư trú tại từng địa phương theo từng môn học, chúng ta sẽ tạo một Pivot Table thế hệ mới:

1. Ngay tại cửa sổ Power Pivot, bấm vào nút PivotTable trên thanh công cụ và chọn xuất ra một Existing Worksheet hoặc New Worksheet.

2. Vùng PivotTable Fields bên phải màn hình giờ đây sẽ hiển thị cả 3 bảng cùng một lúc.

3. Để tính toán chuẩn chỉnh theo tư duy BI, chúng ta không dùng cột tính toán ẩn. Hãy tạo một phép tính DAX Measure:

• Nhấp chuột phải vào tên bảng Marks trong danh sách Fields → Chọn Add Measure.

• Đặt tên Measure là: Average_Grade

• Viết công thức: AVERAGE(Marks[MarkObtained]) và bấm OK.

4. Kéo thả thành quả:

• Kéo cột Subject_Name (từ bảng Subjects) vào ô Rows.

• Kéo cột Address (từ bảng Students) vào ô Filters và chọn lọc giá trị là F (Nữ).

• Kéo phép tính Average_Grade vừa tạo vào ô Values.

Kết quả hiện ra ngay lập tức. Bạn vừa tạo ra một báo cáo xuyên suốt 3 bảng dữ liệu độc lập mà không cần sử dụng bất kỳ hàm VLOOKUP nào!

4.  VLOOKUP với Data model: Sự khác biệt thực tế - trận chiến hiệu suất khi xử lý dữ liệu lớn (Big Data).

Khi nhìn vào một bảng báo cáo nhỏ gọn, bạn có thể nghĩ: "Dùng VLOOKUP kéo dữ liệu về rồi Pivot cũng ra kết quả y hệt, tại sao phải phức tạp hóa?". Nhưng sự nâng cấp thực sự nằm ở năng lực vận hành hệ thống lớn:

1. Hiệu suất (Performance): Nếu hệ thống phình to lên 1 triệu dòng, hàm VLOOKUP chạy lặp đi lặp lại sẽ khiến file Excel nặng tới 300MB và liên tục bị treo máy. Trong khi đó, Data Model sử dụng thuật toán nén ngầm, giúp file chỉ nặng khoảng 10MB và tính toán mượt mà.

2. Bảo trì (Maintenance): Khi có dữ liệu mới nạp vào mỗi tháng, thay vì phải dùng chuột kéo lại công thức VLOOKUP thủ công (rất dễ sót lỗi), bạn chỉ cần ấn Refresh All, toàn bộ mô hình và Pivot Table sẽ tự động cập nhật chính xác.

3. Liên kết gián tiếp: Bảng Students và Subjects hoàn toàn không có cột chung. VLOOKUP bất khả thi trong việc kết nối chúng nếu không biến bảng Marks thành một bãi rác chứa đủ loại cột phụ. Data Model giải quyết bài toán này một cách sạch sẽ thông qua cơ chế lọc chéo (Cross-filtering).

5.  Kết luận

Kết quả trên bảng Pivot Table đã chứng minh một sự thật: Chúng ta hoàn toàn có thể tạo ra các báo cáo đa chiều phức tạp mà không cần đến bất kỳ hàm VLOOKUP hay XLOOKUP nào.

Bằng cách dịch chuyển từ tư duy "phẳng hóa dữ liệu" (Data Flattening) sang tư duy "Mô hình hóa dữ liệu" (Data Modeling), bạn mang lại 3 giá trị cốt lõi cho doanh nghiệp:

1. Tối ưu hóa hiệu suất tối đa: Tệp Excel của bạn không còn bị phình to hàng trăm MB vì các hàm tra cứu lặp đi lặp lại. Bộ nhớ nén của Power Pivot giúp hệ thống vận hành mượt mà ngay cả với dữ liệu lớn.

2. Duy trì tính toàn vẹn của dữ liệu (Data Integrity): Dữ liệu gốc của từng bảng (Students, Subjects, Marks) được giữ sạch sẽ, nguyên bản, không bị chèn thêm các cột phụ hỗn tạp.

3. Tạo bước đệm vững chắc sang Power BI: Sơ đồ mối quan hệ hình sao (Star Schema) và ngôn ngữ DAX mà bạn vừa thực hành trong Excel chính là 90% nền tảng cốt lõi của Power BI.

6.  Thử thách thực hành

Bạn đã sẵn sàng tự tay xây dựng mô hình? [BẤM VÀO ĐÂY (mở trong tab mới) ĐỂ TẢI FILE THỰC HÀNH]

🔥 Thử thách: Hệ thống quản lý trường học vừa cập nhật thêm một tệp dữ liệu thứ tư mang tên Teachers.csv. Hiệu trưởng đưa ra một yêu cầu mới "Hãy thống kê điểm số trung bình của học sinh theo từng Giáo viên phụ trách tại Kenya"

💡 Gợi ý thực hiện:

1. Nạp Teachers.csv vào Power Query, chuyển các cột ID sang dạng Text, rồi chọn Add to Data Model

2. Vào Diagram View, kéo dây kết nối 1-to-many giữa bảng Teachers mới và bảng phù hợp trong mô hình.

3. Dùng lại công thức DAX Average_Grade để tạo Pivot Table ra kết quả theo yêu cầu.

👉 Hãy để lại bình luận: Bạn mất bao nhiêu phút để vượt qua thử thách này? Sơ đồ quan hệ (Data Model) của bạn sau khi thêm bảng Giáo viên trông như thế nào? Nếu gặp lỗi định dạng, hãy chụp ảnh và bình luận phía dưới để tôi hỗ trợ ngay nhé! 😊