Trong một bài viết gần đây, chúng tôi đã giới thiệu hàm Excel được gọi là VLOOKUP và giải thích cách sử dụng nó để truy xuất thông tin từ cơ sở dữ liệu vào một ô trong trang tính cục bộ. Trong bài viết đó, chúng tôi đã đề cập rằng có hai cách sử dụng cho hàm VLOOKUP và chỉ một trong số chúng xử lý cơ sở dữ liệu truy vấn. Trong bài viết này, bài thứ hai và cũng là bài cuối cùng trong loạt bài về hàm VLOOKUP, chúng tôi xem xét cách sử dụng khác, ít được biết đến hơn cho hàm VLOOKUP.

Nếu bạn chưa làm như vậy, vui lòng đọc bài viết đầu tiên về hàm VLOOKUP – bài viết này sẽ giả định rằng nhiều khái niệm được giải thích trong bài viết đó đã được người đọc biết đến.

Khi làm việc với cơ sở dữ liệu, VLOOKUP được chuyển một “mã định danh duy nhất” dùng để xác định bản ghi dữ liệu nào chúng tôi muốn tìm trong cơ sở dữ liệu (ví dụ: mã sản phẩm hoặc ID khách hàng). Mã định danh duy nhất này phải tồn tại trong cơ sở dữ liệu, nếu không hàm VLOOKUP trả về cho chúng ta một lỗi. Trong bài viết này, chúng ta sẽ xem xét một cách sử dụng hàm VLOOKUP trong đó mã định danh hoàn toàn không cần tồn tại trong cơ sở dữ liệu. Dường như hàm VLOOKUP có thể áp dụng cách tiếp cận “đủ gần là đủ tốt” để trả về dữ liệu mà chúng tôi đang tìm kiếm. Trong một số trường hợp nhất định, đây là chính xác điều chúng ta cần.

Chúng tôi sẽ minh họa bài viết này bằng một ví dụ trong thế giới thực – đó là tính toán hoa hồng được tạo ra trên một tập hợp các số liệu bán hàng. Chúng ta sẽ bắt đầu với một kịch bản rất đơn giản, và sau đó dần dần làm cho nó phức tạp hơn, cho đến khi giải pháp hợp lý duy nhất cho vấn đề là sử dụng hàm VLOOKUP. Kịch bản ban đầu trong công ty hư cấu của chúng tôi hoạt động như sau: Nếu một nhân viên bán hàng tạo ra doanh số bán hàng trị giá hơn 30.000 đô la trong một năm nhất định, hoa hồng mà họ kiếm được từ những lần bán hàng đó là 30%. Nếu không thì hoa hồng của họ chỉ là 20%. Cho đến nay, đây là một bảng tính khá đơn giản:

Để sử dụng trang tính này, nhân viên bán hàng nhập số liệu bán hàng của họ vào ô B1 và ​​công thức trong ô B2 tính toán tỷ lệ hoa hồng chính xác mà họ được hưởng, được sử dụng trong ô B3 để tính tổng số tiền hoa hồng mà nhân viên bán hàng phải trả (mà là một phép nhân đơn giản của B1 và ​​B2).

Ô B2 chứa phần thú vị duy nhất của trang tính này – công thức để quyết định tỷ lệ hoa hồng sẽ sử dụng: một phía dưới ngưỡng 30.000 đô la hoặc một ở trên ngưỡng. Công thức này sử dụng hàm Excel được gọi là NẾU. Đối với những độc giả không quen thuộc với IF, nó hoạt động như thế này:

NẾU(điều kiện, giá trị nếu đúng, giá trị nếu sai)

Ở đâu tình trạng là một biểu thức đánh giá một trong hai thật hoặc là sai. Trong ví dụ trên, tình trạng là biểu thức B1 , có thể được đọc là “B1 có nhỏ hơn B5 không?”, hoặc, nói cách khác, “Tổng doanh số bán hàng có nhỏ hơn ngưỡng không”. Nếu câu trả lời cho câu hỏi này là “có” (đúng), thì chúng tôi sử dụng giá trị nếu đúng tham số của hàm, cụ thể là B6 trong trường hợp này – tỷ lệ hoa hồng nếu tổng doanh số là phía dưới ngưỡng. Nếu câu trả lời cho câu hỏi là “không” (sai), thì chúng tôi sử dụng giá trị nếu sai tham số của hàm, cụ thể là B7 trong trường hợp này – tỷ lệ hoa hồng nếu tổng doanh số là ở trên ngưỡng.

Như bạn có thể thấy, việc sử dụng tổng doanh số 20.000 đô la sẽ mang lại cho chúng tôi tỷ lệ hoa hồng là 20% trong ô B2. Nếu chúng tôi nhập giá trị $ 40.000, chúng tôi nhận được một tỷ lệ hoa hồng khác:

Vì vậy, bảng tính của chúng tôi đang hoạt động.

Hãy làm cho nó phức tạp hơn. Hãy giới thiệu ngưỡng thứ hai: Nếu nhân viên bán hàng kiếm được hơn 40.000 đô la, thì tỷ lệ hoa hồng của họ tăng lên 40%:

Dễ hiểu trong thế giới thực, nhưng trong ô B2, công thức của chúng ta ngày càng phức tạp hơn. Nếu bạn xem xét kỹ công thức, bạn sẽ thấy rằng tham số thứ ba của hàm IF ban đầu ( giá trị nếu sai) bây giờ là toàn bộ hàm IF theo đúng nghĩa của nó. Đây được gọi là chức năng lồng nhau (một chức năng trong một chức năng). Nó hoàn toàn hợp lệ trong Excel (thậm chí nó hoạt động!), Nhưng khó đọc và hiểu hơn.

Chúng ta sẽ không đi sâu vào vấn đề về cách thức và lý do tại sao nó hoạt động, cũng như chúng ta sẽ không xem xét các sắc thái của các hàm lồng nhau. Đây là hướng dẫn về VLOOKUP, không phải về Excel nói chung.

Dù sao, nó trở nên tồi tệ hơn! Còn khi chúng ta quyết định rằng nếu họ kiếm được hơn 50.000 đô la thì họ được hưởng 50% hoa hồng, và nếu họ kiếm được hơn 60.000 đô la thì họ được hưởng 60% hoa hồng?

Giờ đây, công thức trong ô B2, mặc dù đúng, nhưng hầu như không thể đọc được. Không ai phải viết các công thức trong đó các hàm được lồng vào nhau sâu bốn cấp độ! Chắc chắn phải có một cách đơn giản hơn?

Chắc chắn là có. VLOOKUP để giải cứu!

Hãy thiết kế lại trang tính một chút. Chúng tôi sẽ giữ tất cả các số liệu giống nhau, nhưng sắp xếp nó theo một cách mới, bảng đường:

Hãy dành một chút thời gian và tự mình xác minh rằng cái mới Bảng tỷ lệ hoạt động giống hệt như chuỗi các ngưỡng ở trên.

Về mặt khái niệm, những gì chúng ta sắp làm là sử dụng hàm VLOOKUP để tra cứu tổng doanh số của nhân viên bán hàng (từ B1) trong bảng tỷ lệ và trả lại cho chúng tôi tỷ lệ hoa hồng tương ứng. Lưu ý rằng nhân viên bán hàng có thể đã thực sự tạo ra doanh số bán hàng không phải một trong năm giá trị trong bảng tỷ giá (0 đô la, 30.000 đô la, 40.000 đô la, 50.000 đô la hoặc 60.000 đô la). Họ có thể đã tạo ra doanh số $ 34,988. Điều quan trọng cần lưu ý là $ 34,988 không không phải xuất hiện trong bảng tỷ lệ. Hãy xem liệu VLOOKUP có thể giải quyết vấn đề của chúng ta hay không…

Chúng tôi chọn ô B2 (vị trí chúng tôi muốn đặt công thức của mình), sau đó chèn hàm VLOOKUP từ Công thức chuyển hướng:

Các Đối số hàm hộp VLOOKUP xuất hiện. Chúng tôi điền lần lượt vào các đối số (tham số), bắt đầu bằng Lookup_value, trong trường hợp này là tổng doanh thu từ ô B1. Chúng tôi đặt con trỏ vào Lookup_value và sau đó bấm một lần vào ô B1:

Tiếp theo, chúng ta cần chỉ định cho VLOOKUP bảng nào để tra cứu dữ liệu này. Trong ví dụ này, đó là bảng tỷ lệ, tất nhiên. Chúng tôi đặt con trỏ vào Table_array và sau đó đánh dấu toàn bộ bảng tỷ lệ – loại trừ các tiêu đề:

Tiếp theo, chúng ta phải chỉ định cột nào trong bảng chứa thông tin mà chúng ta muốn công thức trả về cho chúng ta. Trong trường hợp này, chúng tôi muốn tỷ lệ hoa hồng, được tìm thấy trong cột thứ hai trong bảng, vì vậy chúng tôi nhập 2 vào Col_index_num cánh đồng:

Cuối cùng, chúng tôi nhập một giá trị vào Range_lookup cánh đồng.

Quan trọng: Việc sử dụng trường này phân biệt hai cách sử dụng hàm VLOOKUP. Để sử dụng hàm VLOOKUP với cơ sở dữ liệu, tham số cuối cùng này, Range_lookup, phải luôn được đặt thành SAI, nhưng với cách sử dụng VLOOKUP khác này, chúng ta phải để trống hoặc nhập giá trị THẬT. Khi sử dụng hàm VLOOKUP, điều quan trọng là bạn phải đưa ra lựa chọn chính xác cho tham số cuối cùng này.

Để rõ ràng, chúng tôi sẽ nhập một giá trị là thật bên trong Range_lookup cánh đồng. Bạn cũng có thể để trống nó, vì đây là giá trị mặc định:

Chúng tôi đã hoàn thành tất cả các thông số. Bây giờ chúng tôi nhấp vào đồng ý và Excel xây dựng công thức VLOOKUP cho chúng tôi:

Nếu chúng ta thử nghiệm với một vài tổng số tiền bán hàng khác nhau, chúng ta có thể tự hài lòng rằng công thức đang hoạt động.

Phần kết luận

Trong phiên bản “cơ sở dữ liệu” của VLOOKUP, nơi Range_lookup tham số là SAI, giá trị được truyền vào tham số đầu tiên (Lookup_value) phải có mặt trong cơ sở dữ liệu. Nói cách khác, chúng tôi đang tìm kiếm một kết hợp chính xác.

Nhưng trong cách sử dụng khác của hàm VLOOKUP, chúng tôi không nhất thiết phải tìm kiếm một kết quả phù hợp chính xác. Trong trường hợp này, “đủ gần là đủ tốt”. Nhưng chúng ta có nghĩa là gì khi “đủ gần”? Hãy sử dụng một ví dụ: Khi tìm kiếm tỷ lệ hoa hồng trên tổng doanh thu là $ 34,988, công thức VLOOKUP của chúng tôi sẽ trả về giá trị 30%, đây là câu trả lời chính xác. Tại sao nó lại chọn hàng trong bảng chứa 30%? Trên thực tế, “gần đủ” có nghĩa là gì trong trường hợp này? Hãy nói chính xác:

Khi nào Range_lookup được đặt thành THẬT (hoặc bị bỏ qua), hàm VLOOKUP sẽ tìm trong cột 1 và khớp với giá trị cao nhất không lớn hơn các Lookup_value tham số.

Điều quan trọng cần lưu ý là để hệ thống này hoạt động, bảng phải được sắp xếp theo thứ tự tăng dần trên cột 1!

Nếu bạn muốn thực hành với hàm VLOOKUP, bạn có thể tải xuống tệp mẫu minh họa trong bài viết này từ đây.

Tham khảo (HowToGeek)