Từ VLOOKUP đến Power Query: Tự động hóa quy trình làm sạch dữ liệu

Thực tế, các nghiên cứu đã chỉ ra rằng dân văn phòng mất tới 60-80% thời gian chỉ để làm một việc duy nhất: Dọn dẹp dữ liệu thô (Data Cleaning) trước khi có thể thực sự làm báo cáo. Chúng ta thường cặm cụi viết những hệ hàm lồng nhau dài loằng ngoằng, kéo công thức xuống hàng chục nghìn dòng, để rồi... nhìn màn hình Excel chuyển sang màu trắng xóa kèm dòng chữ đáng sợ "Not Responding". Cảm giác hoang mang tột cùng xuất hiện, kèm theo lời khẩn cầu 'mọi chuyện bình an' để bảo toàn dữ liệu chưa kịp lưu. Bạn có nhận ra hình ảnh quen thuộc của chính mình đâu đó trong tình huống trên không?

Thực ra, viết hàm càng phức tạp, chứng tỏ quy trình làm việc của bạn càng thủ công và dễ vỡ. Hàm tra cứu rất tuyệt, nhưng chúng không được sinh ra để xử lý các quy trình (Workflow) lớn và lặp đi lặp lại. Excel đã bước sang kỷ nguyên của Modern Excel, nhưng đại đa số chúng ta vẫn đang dùng tư duy của 10 năm trước.

Chào mừng bạn đến với bài viết đầu tiên trên blog của tôi. Trong bài viết này, tôi sẽ chia sẻ với bạn hành trình tôi bứt phá khỏi tư duy Excel truyền thống: Từ việc phụ thuộc vào các hàm tìm kiếm cổ điển (VLOOKUP, HLOOKUP) cho đến vị vua hiện đại XLOOKUP, và cuối cùng là đích đến tối thượng – Tự động hóa 100% quy trình dọn dẹp dữ liệu bằng Power Query. Không chỉ là lý thuyết suông, thực hành trực tiếp qua một case study thực tế từ bộ dữ liệu Superstore Sales (mở trong tab mới), tôi sẽ chứng minh cho bạn thấy: Bạn hoàn toàn có thể dọn sạch đống mã sản phẩm bị lỗi khoảng trắng, ghép 2 bảng dữ liệu ngược hướng hoàn toàn tự động mà không cần viết một dòng hàm nào. Chỉ với 1 lần thiết lập, mọi thứ sẽ tự chạy sau 1 cú Click chuột."

1. Bài toán thực tế: Khi hàm tra cứu gặp dữ liệu rác

Để thấy rõ giới hạn của tư duy viết hàm truyền thống, chúng ta hãy cùng nhìn vào bài toán thực tế dưới đây. Tôi đã trích xuất hai bảng dữ liệu từ bộ Superstore Sales dataset quen thuộc và cố tình thả vào đó một vài "cái bẫy" ngẫu nhiên mà bất kỳ ai làm văn phòng cũng từng gặp phải:

• Bảng danh mục sản phẩm (Sheet: Product_Master): Chứa thông tin gốc gồm mã sản phẩm (product_id), tên sản phẩm (product_name) và đơn giá (unit_price). Tuy nhiên, do cấu trúc hệ thống xuất ra, cột product_id lại nằm ở bên phải cột unit_price.

• Bảng doanh thu bán hàng Thô (Sheet: Sales_Orders): Chứa các đơn hàng phát sinh hàng ngày. Nhiệm vụ của bạn là lấy thông tin unit_price từ bảng Product_Master đổ sang bảng này dựa vào product_id để tính tổng tiền.

Nếu bạn mang tư duy của 10 năm trước vào giải quyết bài toán này, hành trình của bạn sẽ va phải những "vết xe đổ" sau đây:

     Trường hợp 1: Sự bất lực của VLOOKUP khi gặp cấu trúc ngược

Vũ khí đầu tiên bạn nghĩ đến chắc chắn là hàm VLOOKUP huyền thoại. Nhưng ngay khi đặt bút viết công thức, bạn sẽ khựng lại. VLOOKUP chỉ có thể tìm kiếm dữ liệu từ trái sang phải. Việc cột mã sản phẩm product_id nằm ở bên phải cột unit_price ở bảng Product_Master đã chặn đứng hoàn toàn khả năng của hàm này.

Để chữa cháy, bạn buộc phải dùng chuột cắt cột product_id rồi chèn ngược lại sang bên trái bảng Product_Master, phá vỡ cấu trúc file gốc của hệ thống. Nguy hiểm hơn, nếu sau này bạn chèn thêm một cột bất kỳ vào giữa vùng tìm kiếm, toàn bộ hàm VLOOKUP ở bảng chính sẽ trả về kết quả sai lệch hoặc báo lỗi #REF! hàng loạt, vì vị trí cột sẽ thay đổi (col_index_num).

     Trường hợp 2: XLOOKUP giải cứu cấu trúc, nhưng gục ngã trước "Rác"

Bạn nâng cấp lên XLOOKUP – vị vua tra cứu hiện đại. Hàm này giải quyết triệt để lỗi ngược hướng của VLOOKUP mà không cần bạn phải dịch chuyển bất kỳ cột nào ở bảng gốc. Công thức viết ra trông rất gọn gàng và chuyên nghiệp.

Thế nhưng, khi bạn hí hửng kéo công thức xuống hàng chục nghìn dòng của file Superstore Sales, màn hình lập tức xuất hiện hàng loạt lỗi #N/A (Không tìm thấy dữ liệu). Bạn kiểm tra kỹ bằng mắt thường, mã sản phẩm ở hai bảng hoàn toàn giống nhau (ví dụ: OFF-TEN-10001585). Vậy tại sao hàm vẫn lỗi?

Cú lừa nằm ở đây: Do quá trình nhập liệu thủ công của con người hoặc lỗi xuất file, các mã sản phẩm đã bị dính các khoảng trắng thừa ngẫu nhiên. Dòng thì thừa dấu cách ở phía trước (" OFF-TEN-10001585"), dòng thì ẩn dấu cách ở phía sau ("OFF-TEN-10001585 "). Đối với Excel, hai chuỗi này hoàn toàn khác nhau.

Kết quả: XLOOKUP thông minh đến mấy cũng chịu chết trước đống dữ liệu nhiễm bẩn này. Để sửa lỗi, bạn lại phải lồng thêm các hàm dọn rác như TRIM, CLEAN khiến công thức dài ngoằng và làm bộ vi xử lý của máy tính chạy "hụt hơi", dẫn đến tình trạng Not Responding mà chúng ta đã nói ở trên.

2. Kỷ nguyên Power Query: Tự động hóa quy trình trong một cú Click

Khi đối mặt với hàng chục nghìn dòng dữ liệu bị lỗi khoảng trắng của bộ Superstore Sales, việc ngồi viết hàm hay dọn rác thủ công đều là những giải pháp tạm thời mang tính "chữa cháy". Tư duy của một người làm chủ dữ liệu hiện đại là: Không giải quyết phần ngọn, hãy xây dựng một hệ thống tự vận hành.

Đó là lý do bạn cần bước vào kỷ nguyên của Power Query – công cụ ETL (Extract - Transform - Load) được tích hợp sẵn từ Excel 2016 trở đi. Thay vì ép máy tính tính toán hàng vạn ô hàm phức tạp, Power Query hoạt động như một "robot ghi hình". Bạn chỉ cần làm mẫu quy trình dọn rác và ghép bảng một lần duy nhất, robot sẽ tự động lặp lại cho các lần sau.

Dưới đây là các bước thiết lập một dòng chảy dữ liệu tự động hoàn chỉnh:

     Bước 1: Nạp dữ liệu và dọn sạch "Rác" từ dữ liệu thô

Thay vì ngồi viết hàm TRIM cồng kềnh cho bảng Sales_Orders, bạn chỉ cần nạp dữ liệu vào cửa sổ Power Query (Vào tab Data -> From Table/Range).

📖Lưu ý nhỏ cho bạn: Khi bấm nút From Table/Range để nạp dữ liệu vào Power Query, bạn sẽ thấy vùng dữ liệu thô của mình lập tức biến thành một chiếc Bảng màu xanh (Excel Table). Đừng lo lắng, đây là tính năng bắt buộc! Excel cần biến dữ liệu của bạn thành một chiếc "bảng thông minh" có khả năng tự co giãn để chuẩn bị cho nút Refresh thần thánh ở cuối bài.

Xử lý mã sản phẩm: Click chuột phải vào tiêu đề cột product_id -> Chọn Transform -> Format -> Trim. Toàn bộ khoảng trắng rác ẩn mình ở đầu và cuối mã biến mất trong chưa đầy 1 giây. Điều tuyệt vời là Power Query đã ghi lại bước này vào mục Applied Steps bên góc phải màn hình như một thuật toán cố định.

Bằng cách tương tự, tiếp tục load dữ liệu bảng Product_Master vào Power Query.

     Bước 2: Ghép bảng ngược hướng bằng "Merge Queries as New"

Để giải quyết bài toán ngược hướng (Cột product_id nằm bên phải cột unit_price ở bảng Product_Master) mà hàm VLOOKUP đã gục ngã, chúng ta sử dụng tính năng ghép bảng độc lập để tách rời nguồn và kết quả: sử dụng tính năng Merge Queries (nằm ở tab Home)-> Merge Queries As New.

Tư duy lúc này cực kỳ trực quan:

• Chọn bảng Sales_Orders làm bảng gốc, chọn bảng Product_Master làm bảng cần kết nối.

• Click chọn cột chung là product_id ở cả hai bảng (nhấn và giữ phím Ctrl) -> OK. Power Query sẽ sinh ra một Query kết quả hoàn toàn mới (ví dụ tên là Merge1).

Power Query không quan tâm cột product_id nằm ở bên trái hay bên phải, đầu bảng hay cuối bảng. Nó sẽ tự động quét và khớp dữ liệu dựa trên thuật toán hệ thống, nhanh và mượt mà hơn việc kéo hàm đơn lẻ gấp hàng chục lần. Sau khi khớp dữ liệu thành công, bạn chỉ cần bung cột unit_price ra.

Bước 3: Tính toán tự động ở "Hậu trường"

Tính toán tự động: Đến đây, một sai lầm kinh điển của người dùng Excel truyền thống là bấm xuất dữ liệu ra bảng chính, rồi lại dùng chuột gõ công thức = Số lượng * Đơn giá bằng tay. Đừng làm thế! Hãy vào tab Add Column -> Chọn Custom Column -> Nhập công thức: =[quantity] * [Product_Master.unit_price]. Power Query sẽ tự động tính toán tổng doanh số cho hàng vạn dòng dữ liệu ngay ở hậu trường. Bảng kết quả trả về Excel sẽ hoàn toàn là số liệu sạch, nhẹ nhàng và không chứa bất kỳ một chữ hàm nào.

Đó mới là đỉnh cao của Modern Excel!

Sau đó, tiến hành xóa cột Product_Master.unit_price: chọn cột -> Remove column hoặc chuột phải -> Remove

    Bước 4: Xuất dữ liệu ra một Sheet độc lập

Tại giao diện Query kết quả Merge1, bạn bấm vào mũi tên dưới nút Close & Load -> Chọn Close & Load To... -> Chọn New worksheet (Bảng tính mới) và nhấn OK.

Lúc này, file Excel của bạn sẽ được phân chia vô cùng khoa học thành 3 Sheet riêng biệt: Sheet Nguồn thô (Sales_order), Sheet Product_Master và Sheet Kết quả sạch 1 (Merge) (màu xanh). Bạn có thể đổi tên sao cho dễ nhớ và phù hợp như cầu của mình nhé.

      Bước 5: Nút bấm thay đổi cuộc đời – "Refresh"

Đây chính là "vũ khí tối thượng" tối ưu hóa toàn bộ quy trình làm việc của bạn. Hãy tưởng tượng tháng sau, hệ thống lại xuất ra một file Doanh thu mới với hàng nghìn dòng dữ liệu rác mới (khoảng trắng thừa, định dạng ngày tháng lộn xộn, mã sản phẩm ngược hướng). Nếu dùng VLOOKUP hay XLOOKUP, bạn lại phải copy dữ liệu, ngồi dọn rác thủ công, viết lại hàm và kéo lại công thức xuống dưới. Còn với Power Query?

• Mở file Excel, copy toàn bộ dữ liệu mới dán đè vào Sheet Nguồn thô (Bảng dữ liệu thô ban đầu). “Bảng thông minh” sẽ tự động phình to ra để ôm trọn dữ liệu mới.

• Di chuyển sang Sheet Kết quả sạch, click chuột phải vào bảng màu xanh và bấm Refresh (hoặc nhấn tổ hợp phím Alt + F5).

Robot Power Query sẽ tự động tái kích hoạt lại toàn bộ chuỗi hành động đã được ghi nhớ:Tự động nạp file mới -> Tự động dọn sạch khoảng trắng rác -> Khớp hai bảng ngược hướng-> Tự động nhân số lượng với đơn giá. Báo cáo mới tự động cập nhật chính xác mà bạn không cần động tay viết lại một dòng hàm nào! (Lưu ý: thời gian chạy sẽ phụ thuộc vào kích thước của dữ liệu).

 3. Kết luận: Đã đến lúc nâng cấp tư duy xử lý dữ liệu

Nhìn lại toàn bộ hành trình đi từ VLOOKUP cổ điển, qua vị vua tra cứu XLOOKUP, cho đến giải pháp hệ thống của Power Query, chúng ta thấy rõ một điều: Sự khác biệt không nằm ở việc bạn thuộc bao nhiêu hàm, mà nằm ở tư duy tổ chức dữ liệu của bạn.

Nếu công việc của bạn hàng tháng vẫn là mở những file báo cáo thô giống nhau, ngồi dọn rác thủ công và kéo hàng vạn dòng công thức, hãy mạnh dạn cất những hàm tra cứu sang một bên. Hãy bắt đầu xây dựng những chiếc "băng chuyền tự động" bằng Power Query để giải phóng sức lao động của chính mình. Sự kiên nhẫn thiết lập hệ thống ở lần đầu tiên sẽ được đền đáp bằng những tách cafe thảnh thơi vào mỗi kỳ báo cáo cuối tháng! 😊

🎁 Quà tặng thực hành dành riêng cho bạn

Học đi đôi với hành. Để bạn không chỉ đọc lý thuyết suông, tôi đã chuẩn bị sẵn file thực hành được trích xuất từ bộ dữ liệu bán hàng kinh điển Superstore Sales Dataset. Trong file này, tôi đã cố tình dùng công thức ngẫu nhiên để "làm nhiễm bẩn" dữ liệu nhằm thử thách bạn.

👉 [BẤM VÀO ĐÂY (mở trong tab mới) ĐỂ TẢI FILE THỰC HÀNH MIỄN PHÍ]

Thử thách dành cho bạn:

• Hãy thử dùng XLOOKUP để xem có bao nhiêu dòng bị báo lỗi #N/A do khoảng trắng thừa

• Làm theo hướng dẫn các bước Power Query ở trên để dọn sạch lỗi, tính doanh số trực tiếp trong hậu trường và bấm nút Refresh xem điều kỳ diệu gì xảy ra.

Nếu bạn gặp bất kỳ khó khăn nào trong quá trình thực hành hoặc có một "ca dọn rác dữ liệu" nào khó hơn chưa giải quyết được, hãy để lại bình luận ngay phía dưới bài viết này. Tôi sẽ cùng bạn phân tích và tối ưu hóa workflow ở các bài viết tiếp theo!