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 nó có thể được sử dụng để lấy thông tin từ một cơ sở dữ liệu vào một ô trong một bảng 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 VLOOKUP và chỉ một trong số chúng được xử lý với cơ sở dữ liệu truy vấn. Trong bài viết này, bài thứ hai và cuối cùng trong loạt VLOOKUP, chúng ta xem xét điều này, sử dụng ít được biết đến hơn cho hàm VLOOKUP.
Nếu bạn chưa làm như vậy, hãy đọc bài viết VLOOKUP đầu tiên - 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 thành một "định danh duy nhất" phục vụ để 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). Số nhận dạng duy nhất này phải tồn tại trong cơ sở dữ liệu, nếu không VLOOKUP sẽ trả về cho chúng ta một lỗi. Trong bài viết này, chúng tôi sẽ xem xét một cách sử dụng VLOOKUP nơi mà mã định danh không cần phải tồn tại trong cơ sở dữ liệu. Dường như VLOOKUP có thể áp dụng phương pháp “gần đủ là đủ tốt” để trả lại dữ liệu chúng tôi đang tìm kiếm. Trong một số trường hợp, đâ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 với một ví dụ thực tế - đó 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 VLOOKUP. Kịch bản ban đầu trong công ty hư cấu của chúng tôi hoạt động như thế này: 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 họ kiếm được trên những doanh số đó là 30%. Nếu không hoa hồng của họ chỉ là 20%. Cho đến nay đây là một bảng tính khá đơn giản:
Ô B2 chứa phần thú vị duy nhất của bảng tính này - công thức quyết định tỷ lệ hoa hồng sẽ sử dụng: cái nào phía dưới ngưỡng 30.000 đô la, hoặc ngưỡng ở 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:
IF(condition,value if true,value if false)
Ở đâu điều kiện là một biểu thức đánh giá thật hoặc là sai. Trong ví dụ trên, điều kiện là biểu thức B1, có thể được đọc là "B1 có nhỏ hơn B5 không?", hoặc, đặt một cách khác, "Tổng doanh thu có thấp 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 thu 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 thu là ở trên ngưỡng.
Như bạn có thể thấy, sử dụng tổng doanh số 20.000 đô la 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 sẽ nhận được mức hoa hồng khác:
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 trở nên phức tạp hơn. Nếu bạn xem kỹ công thức, bạn sẽ thấy thông 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à hàm lồng nhau (một hàm trong hàm). Nó hoàn toàn hợp lệ trong Excel (thậm chí nó hoạt động!), Nhưng khó đọc và dễ hiểu hơn.
Chúng ta sẽ không đi sâu vào các hạt và bu lông về cách thức và lý do tại sao nó hoạt động, cũng như chúng ta sẽ kiểm tra các sắc thái của các hàm lồng nhau. Đây là một hướng dẫn về VLOOKUP, không phải trên Excel nói chung.
Dù sao, nó trở nên tồi tệ hơn! Còn khi chúng tôi 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 hoa hồng 60%?
Chắc chắn là có. VLOOKUP để giải cứu!
Hãy thiết kế lại bảng tính một chút. Chúng tôi sẽ giữ tất cả các hình ảnh giống nhau nhưng tổ chức theo một cách mới, dạng bảng đường:
Hãy dành một chút thời gian và tự xác minh rằng Bảng xếp hạng hoạt động chính xác giống 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 VLOOKUP để tra cứu tổng doanh thu của nhân viên bán hàng (từ B1) trong bảng giá 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ể đã tạo doanh số bán hàng thực sự không phải một trong năm giá trị trong bảng giá ($ 0, $ 30,000, $ 40,000, $ 50,000 hoặc $ 60,000). Họ có thể đã tạo ra doanh thu $ 34,988. Điều quan trọng cần lưu ý là $ 34,988 không phải xuất hiện trong bảng tốc độ. Hãy xem liệu VLOOKUP có thể giải quyết vấn đề của chúng ta không…
Chúng ta chọn ô B2 (vị trí chúng ta muốn đặt công thức của chúng ta), và sau đó chèn hàm VLOOKUP từ Công thức chuyển hướng:
Các Đối số chức năng hộp cho VLOOKUP xuất hiện. Chúng ta điền vào các đối số (tham số) từng cái một, bắt đầu bằng Lookup_value, trong trường hợp này, 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 trên ô B1:
Tiếp theo, chúng ta cần chỉ định VLOOKUP bảng nào cần 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ệ - không bao gồm tiêu đề:
Tiếp theo chúng ta phải xác định cột nào trong bảng chứa thông tin mà chúng ta muốn công thức của chúng ta 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, do đó 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: Đó là việc sử dụng trường này để phân biệt hai cách sử dụng VLOOKUP. Để sử dụng VLOOKUP với một cơ sở dữ liệu, tham số cuối cùng này, Range_lookup, luôn phải được đặt thành SAI, nhưng với việc sử dụng VLOOKUP khác, chúng ta phải để trống hoặc nhập giá trị THẬT. Khi sử dụng VLOOKUP, điều quan trọng là bạn thực hiện lựa chọn đúng cho tham số cuối cùng này.
Để rõ ràng, chúng tôi sẽ nhập giá trị thật bên trong Range_lookup cánh đồng. Bạn cũng nên để trống 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 được và Excel xây dựng công thức VLOOKUP của chúng tôi cho chúng tôi:
Phần kết luận
Trong phiên bản "cơ sở dữ liệu" của VLOOKUP, trong đó Range_lookup tham số là SAI, giá trị được truyền trong 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 việc sử dụng VLOOKUP này, chúng tôi không nhất thiết phải tìm kiếm một kết quả khớ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 gì bởi “gần đủ”? Hãy sử dụng 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ả lại cho chúng tôi giá trị 30%, đó là câu trả lời đúng. Tại sao nó chọn hàng trong bảng chứa 30%? Điều gì, trên thực tế, không "gần đủ" có nghĩa là trong trường hợp này? Hãy nói chính xác:
When Range_lookup is set to TRUE (or omitted), VLOOKUP will look in column 1 and match the highest value that is not greater than the Lookup_value parameter.
Đ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 VLOOKUP, tệp mẫu được minh họa trong bài viết này có thể được tải xuống từ đây.
