VLOOKUP trong Excel, phần 2 Sử dụng VLOOKUP mà không cần cơ sở dữ liệu
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 làm thế nào nó có thể được sử dụng để lấy thông tin 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ố đó xử lý các cơ sở dữ liệu truy vấn. Trong bài viết này, phần thứ hai và cuối cùng trong loạt bài VLOOKUP, chúng tôi kiểm tra cái khác, ít được biết đến hơn cho chức năng VLOOKUP.
Nếu bạn chưa làm như vậy, vui lòng đọc bài viết VLOOKUP đầu tiên - bài viết này sẽ cho 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 thông qua một mã đị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). Mã định danh duy nhất này phải tồn tại trong cơ sở dữ liệu, nếu không, VLOOKUP trả về cho chúng tôi một lỗi. Trong bài viết này, chúng tôi sẽ kiểm tra cách sử dụng VLOOKUP trong đó định danh hoàn toàn không cần tồn tại trong cơ sở dữ liệu. Gần như thể VLOOKUP có thể áp dụng một 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ụ thực tế - 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 tôi 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ố trị giá hơn 30.000 đô la trong một năm nhất định, hoa hồng họ kiếm được trên 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:
Để sử dụng bảng 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ó quyền nhận, được sử dụng trong ô B3 để tính tổng hoa hồng mà nhân viên bán hàng đang nợ (mà là phép nhân đơn giản của B1 và B2).
Ô 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 nào 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 điều kiện là một biểu thức đánh giá một trong hai thật hoặc là sai. Trong ví dụ trên, điều kiện là biểu thức B1
Như bạn có thể thấy, sử dụng tổng doanh số 20.000 đô la 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 đô la, chúng tôi sẽ nhận được tỷ lệ hoa hồng khác nhau:
Vì vậy, bảng tính của chúng tôi đang làm việ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 tôi ngày càng phức tạp hơn. Nếu bạn nhìn kỹ vào công thức, bạn sẽ thấy rằng tham số thứ ba của hàm IF gốc ( giá trị nếu sai) bây giờ là toàn bộ hàm IF theo đúng nghĩa của nó. Điều này được gọi là một hàm 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 các loại hạt và bu lông về cách thức và lý do tại sao điều này hoạt động, chúng ta cũng sẽ không kiểm tra 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 trên Excel nói chung.
Dù sao, nó trở nên tồi tệ hơn! Điều gì sẽ xảy ra 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%?
Bây giờ công thức trong ô B2, trong khi chính xác, đã trở nên hầu như không thể đọc được. Không ai phải viết công thức trong đó các hàm được lồng 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 bảng 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, nhiều hơn nữa dạng bảng đường:
Hãy dành một chút thời gian và tự kiểm chứng rằng cái mới Bảng tỷ lệ 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 tôi sắp làm là sử dụng 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ố không phải một trong năm giá trị trong bảng tỷ lệ ($ 0, $ 30.000, $ 40.000, $ 50.000 hoặc $ 60.000). Họ có thể đã tạo ra doanh số 34.988 đô la. Điều quan trọng cần lưu ý là $ 34,988 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 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) 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 tôi điền từng đối số (tham số), bắt đầu bằng Tra cứu_value, trong trường hợp này là tổng doanh số từ ô B1. Chúng tôi đặt con trỏ trong Tra cứu_value trường 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, tất nhiên đó là bảng tỷ lệ. Chúng tôi đặt con trỏ trong Bảng_array trường, và sau đó làm nổi bật toàn bộ bảng tỷ lệ - không bao gồm các 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 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 một 2 vào Col_index_num cánh đồng:
Cuối cùng, chúng tôi nhập một giá trị trong Range_lookup cánh đồng.
Quan trọng: Chính 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 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 việc sử dụng VLOOKUP khác này, chúng ta phải để trống hoặc nhập giá trị là THẬT. Khi sử dụng VLOOKUP, điều quan trọng là bạn phải 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ị thật bên trong Range_lookup cánh đồng. Nó cũng sẽ ổn nếu để trống, vì đây là giá trị mặc định:
Chúng tôi đã hoàn thành tất cả các tham số. Bây giờ chúng tôi bấm vào được và Excel xây dựng công thức VLOOKUP cho chúng tôi:
Nếu chúng tôi thử nghiệm với một số tiền tổng doanh số khác nhau, chúng tôi có thể tự hài lòng rằng công thức đang hoạt động.
Phần kết luận
Trong cơ sở dữ liệu, phiên bản VLOOKUP của VLOOKUP, nơi Range_lookup tham số là SAI, giá trị được truyền trong tham số đầu tiên (Tra cứu_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 trận đấu chính xác.
Nhưng trong cách sử dụng VLOOKUP khác này, chúng tôi không nhất thiết phải tìm kiếm một kết hợp chính xác. Trong trường hợp này, thì gần đủ là đủ tốt. Nhưng ý nghĩa của chúng ta là gì bởi những người gần như đủ tầm? Hãy sử dụng một ví dụ: Khi tìm kiếm tỷ lệ hoa hồng trên tổng doanh số 34,988 đô la, công thức VLOOKUP của chúng tôi sẽ trả về 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%? Trên thực tế, có nghĩa là những gì gần như có ý nghĩa trong trường hợp này? Hãy chính xác:
Khi nào Range_lookup được đặt thành THẬT (hoặc bỏ qua), VLOOKUP sẽ xem trong cột 1 và khớp giá trị cao nhất không lớn hơn các Tra cứu_value tham số.
Cũng cần lưu ý rằng để 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.
