Xử lý dữ liệu với VLOOKUP trong bảng tính

Đôi khi, khoa học dữ liệu không phải là về học sâu, dữ liệu lớn hay hệ thống gợi ý. Nhiều lúc, đặc biệt nếu bạn làm việc trong các lĩnh vực như tài chính hoặc tại công ty vốn phụ thuộc nhiều vào Excel trước đây, rất có thể bạn sẽ phải làm việc với bảng tính Excel khá thường xuyên. Tôi cũng thuộc nhóm thứ hai. Nơi tôi làm việc dựa rất nhiều vào Excel, vì vậy tôi đã phải học nhiều về các khả năng khác nhau của nó để phục vụ một số nhiệm vụ công việc.

Dù R và Python có nhiều ưu thế rõ ràng so với Excel ở nhiều khía cạnh, Excel vẫn là công cụ mạnh cho một số tác vụ như tạo báo cáo kinh doanh. Trong hướng dẫn này, tôi sẽ tập trung vào một trong những hàm tích hợp của Excel hỗ trợ rất nhiều khi bạn làm việc đồng thời với nhiều bảng Excel.

Hàm tôi muốn nói đến là VLOOKUP. VLOOKUP cho phép người dùng liên hệ các cột khác nhau trong cùng một trang tính hoặc giữa các trang tính khác nhau trong cùng sổ làm việc. Về bản chất, nó giúp bạn tra cứu một giá trị từ một cột và trả về giá trị tương ứng từ cột khác. Tính năng này cực kỳ hữu ích nếu bạn có các bảng dữ liệu lớn chưa được sắp xếp và cần liên kết chúng dựa trên một cột nào đó (lý tưởng là chứa khóa duy nhất).

Hãy minh họa cách dùng hàm VLOOKUP giữa các cột trong cùng một trang tính bằng một bảng dữ liệu ví dụ đơn giản:

Ảnh chụp màn hình trên cho thấy một biểu thức VLOOKUP thường gặp, được viết như khi bạn làm báo cáo kinh doanh. Các thành phần như sau:

• Hàm IFERROR: Dùng để thay thế lỗi bằng dấu gạch ngang (-) nếu khóa trống hoặc không có trong bảng dữ liệu. Đây là thực hành tốt để xử lý lỗi, giúp trang tính sạch sẽ hơn khi lỗi xảy ra, nhưng không bắt buộc để VLOOKUP hoạt động.
• Các đối số của VLOOKUP:
  • E2 là giá trị tra cứu (trong ví dụ này chúng ta dùng KOG, nhưng có thể là bất kỳ khóa nào). Giá trị này phải nằm ở cột ngoài cùng bên trái của phạm vi bảng mà bạn muốn tìm.
  • B1:C18 là phạm vi mảng/bảng để tra cứu. Cột ngoài cùng bên trái có chỉ số 1 như thể hiện. Cột này cũng phải chứa các giá trị tra cứu để hàm hoạt động.
  • 2 là số chỉ mục cột trong phạm vi bảng đã chọn mà bạn muốn lấy giá trị. Ở đây chúng ta muốn lấy mô tả nên chọn cột 2 của mảng bảng.
  • FALSE là cờ cho biết bạn muốn khớp chính xác giá trị tra cứu. Ở đây chọn FALSE vì bạn muốn khớp chính xác khóa KOG. Tùy chọn còn lại là TRUE, nghĩa là tìm khớp gần đúng và là mặc định nếu bạn không chỉ định đối số này. Nói chung không nên chọn TRUE khi khớp khóa hoặc chuỗi vì có thể cho ra kết quả kỳ lạ.

Sau khi viết xong câu lệnh VLOOKUP đó, nếu bạn nhấn ENTER trong Excel, bạn sẽ nhận được kết quả sau:

Như bạn thấy, hàm VLOOKUP trả về đúng như kỳ vọng: "King of Gondor", tức mô tả gắn với khóa KOG. Điều này đúng với bất kỳ khóa nào bạn nhập vào ô E2. Chẳng hạn, hãy xem điều gì xảy ra nếu nhập khóa KIN.

The King in the North! Chính xác, nếu nhập khóa KIN, chúng ta kỳ vọng nhận được mô tả cho Jon (Snow).

Tuy nhiên, VLOOKUP còn có nhiều cách dùng hơn là chỉ truy xuất một giá trị theo khóa. Giả sử chúng ta tách bảng đó thành hai bảng, mỗi bảng ở một trang tính khác nhau trong cùng sổ làm việc.

Kết hợp bảng

Như bạn thấy, dữ liệu giờ được tách thành hai trang khác nhau (S1 và S2) và các khóa trong bảng ở trang thứ hai (S2) có thứ tự khác. May mắn là cột khóa chung cho cả hai trang bất kể thứ tự, nên chúng ta có thể ghép dữ liệu lại ở S1 bằng VLOOKUP (bạn có thể hình dung như một phép nối trái theo một cột). Cú pháp để thực hiện như sau:

Lần này, đối số đầu tiên, giá trị tra cứu, là B2 vì chúng ta muốn khớp khóa B2 trong bảng ở S2. Phạm vi bảng giờ là toàn bộ bảng dữ liệu ở S2 với cột đầu chứa các khóa chung. Bạn sẽ để ý phạm vi bảng có dấu đô-la. Điều này đạt được bằng cách nhấn F4 sau khi chọn phạm vi, giúp cố định phạm vi để khi bạn kéo công thức xuống ở S1, VLOOKUP vẫn tham chiếu đúng phạm vi ban đầu thay vì trôi theo. Số chỉ mục cột vẫn là 2 vì chúng ta cần mô tả, là cột thứ hai ở S2. Cuối cùng, đối số cuối là FALSE vì chúng ta muốn khớp chính xác các khóa.

Bước cuối cùng là kéo công thức xuống đến dòng cuối của bảng. Bảng kết quả ở S1 như sau:

Voilá! Chỉ đơn giản vậy, chúng ta đã ghép dữ liệu lại bằng VLOOKUP. Kết hợp bảng theo cách này rất phổ biến trong môi trường kinh doanh, nơi SQL, R hay Python không phải công cụ mà lãnh đạo cấp cao quen dùng. Ví dụ, bạn có hai tệp chứa các chi tiết khác nhau về các cơ hội bán hàng đủ điều kiện, có thể liên kết với nhau bằng một ID cơ hội duy nhất cho báo cáo Excel bán hàng hàng tháng (trường hợp sử dụng Excel điển hình của tôi).

Sử dụng khớp gần đúng

Đến giờ bạn có thể nhận thấy chúng ta chưa dùng TRUE làm đối số cuối (tức khớp gần đúng) trong bất kỳ hàm VLOOKUP nào. Lý do là vì chúng ta tập trung khớp chính xác các khóa duy nhất. Tuy vậy, bạn có thể dùng TRUE cho đối số cuối của VLOOKUP một cách an toàn khi làm việc với dữ liệu số.

Chẳng hạn, giả sử chúng ta bán cá và cung cấp mức chiết khấu sau khi khách hàng mua theo số lượng nhất định (Nếu ai hỏi vì sao là cá, đơn giản thôi. Cá thật tuyệt!).

Hàm VLOOKUP trong ô F2 lần này là:

Khi đã có công thức này, mức chiết khấu sẽ là mức gần nhất với các ngưỡng trong bảng. Ví dụ, nếu mua 25 con cá, chiết khấu là 5%; nếu mua 70 con, chiết khấu là 10%; và nếu mua 120 con, chiết khấu là 15%. Tất cả ví dụ này được minh họa dưới đây:

Giới thiệu về INDEX-MATCH

VLOOKUP là một hàm mạnh để liên hệ các cột khác nhau. Tuy nhiên, có một cách thay thế: kết hợp INDEX-MATCH. Khác với VLOOKUP là một hàm đơn, INDEX-MATCH là sự kết hợp của hai hàm và có một số ưu điểm so với VLOOKUP tiêu chuẩn, dù có thể phức tạp hơn khi viết.

Trong khuôn khổ hướng dẫn này, chúng ta sẽ đi qua ví dụ đơn giản dùng INDEX-MATCH để nối hai bảng dựa trên khóa chung như đã làm ở trên với VLOOKUP và giải thích cú pháp (xem bên dưới).

Các thành phần của kết hợp INDEX-MATCH như sau:

• Đối số của INDEX:
  • M2:M18: Đây là phạm vi cột chứa các giá trị bạn muốn lấy. Ở đây chúng ta cần cột mô tả nên chọn phạm vi đó. Lưu ý chúng ta đã nhấn F4 sau khi chọn phạm vi để cố định, giúp công thức không bị trôi khi kéo xuống.
• Đối số của MATCH:
  • B2: Đây là khóa cần tra cứu. Tương đương với giá trị tra cứu trong VLOOKUP.
  • L2:L18: Đây là phạm vi cột chứa các khóa, nơi giá trị tra cứu phải nằm. Lưu ý chúng ta cũng nhấn F4 ở đây.
  • 0: Tương đương FALSE trong VLOOKUP. Đây là cờ cho khớp chính xác.

Nhìn vào cú pháp, bạn có thể nhận ra một trong những ưu điểm chính của INDEX-MATCH so với VLOOKUP: khả năng “miễn nhiễm cột”. Tức là, nếu bây giờ chúng ta quyết định thêm một cột giữa Key và Description ở bảng bên phải, cũng không vấn đề gì vì công thức vẫn tham chiếu đúng đến cột Description. Xem minh họa dưới đây:

Với VLOOKUP thì điều này không thể nếu không quay lại chỉnh công thức, vì tham chiếu cột của nó là tĩnh. Nói cách khác, trong ví dụ trên, ban đầu bạn sẽ đặt 2 làm số chỉ mục cột để lấy Description. Nhưng sau khi thêm cột Fish, VLOOKUP sẽ trả về giá trị ở cột Fish vì đó bây giờ là cột số 2 trong phạm vi bảng. Bạn phải vào lại hàm VLOOKUP và đổi số chỉ mục cột thành 3 để hàm hoạt động đúng.

Như vậy, để nối mô tả vào tên trong bảng bên trái bằng INDEX-MATCH, quy trình giống hệt VLOOKUP. Chúng ta chỉ cần kéo công thức xuống như dưới đây:

Lưu ý rằng việc nối bảng giữa các trang khác nhau trong cùng sổ làm việc cũng tương tự. Điểm khác biệt duy nhất là bạn sẽ phải chọn phạm vi cột chứa khóa chung cũng như phạm vi cột có các giá trị muốn lấy ở trang thứ hai.

Kết luận

Hy vọng bạn thấy hữu ích. Hai hàm VLOOKUP và INDEX-MATCH gần như là thiết yếu với bất kỳ ai làm việc với Excel trong bối cảnh kinh doanh. Tôi khuyến khích bạn tìm hiểu sâu hơn về INDEX-MATCH ngoài những gì đề cập ở đây, vì nó có nhiều khả năng hay ho như khớp phân biệt chữ hoa/thường (Mặc định INDEX-MATCH và VLOOKUP không phân biệt hoa/thường) hoặc khớp dựa trên nhiều cột. Tiếp tục học hỏi — giới hạn là bầu trời!

Nếu bạn muốn tìm hiểu thêm về VLOOKUP, hãy tham gia khóa Phân tích dữ liệu với bảng tính của DataCamp.