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, 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 và đơn giá bán (sales). 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 Đơn giá.
Bảng Doanh Thu Bán Hàng Thô (Sheet: Sales Order): 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 Đơn giá từ bảng Danh mục đổ 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:
Thế hệ 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 Đơn giá ở bảng Danh mục đã 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 Danh mục, 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).
Thế hệ 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 1 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ách quy trình xử lý dữ liệu của bạn thay đổi hoàn toàn chỉ với 3 bước chuột:
Bước 1: Nạp dữ liệu và dọn sạch "Rác" không dùng một dòng hàm
Thay vì ngồi viết hàm TRIM hay CLEAN cồng kềnh cho bảng Doanh thu, bạn chỉ cần nạp dữ liệu vào cửa sổ Power Query (Vào tab Data -> From Table/Range).
Tại đây, để xử lý triệt để đống lỗi khoảng trắng ngẫu nhiên ở đầu và cuối mã product_id:
Bạn click chuột phải vào tiêu đề cột product_id.
Chọn Transform -> Format -> Trim.
