Nội dung
Gần đây, chúng tôi đã dành riêng một bài viết về một trong những hàm Excel hữu ích nhất được gọi là VPR và chỉ ra cách nó có thể được sử dụng để trích xuất thông tin cần thiết từ cơ sở dữ liệu vào một ô trang tính. Chúng tôi cũng đã đề cập rằng có hai trường hợp sử dụng cho hàm VPR và chỉ một trong số chúng giải quyết các truy vấn cơ sở dữ liệu. Trong bài viết này, bạn sẽ tìm hiểu một cách khác ít được biết đến hơn để sử dụng hàm VPR trong Excel.
Nếu bạn chưa làm điều này, hãy nhớ đọc bài viết cuối cùng về hàm VPR, bởi vì tất cả thông tin dưới đây giả định rằng bạn đã quen thuộc với các nguyên tắc được mô tả trong bài viết đầu tiên.
Khi làm việc với cơ sở dữ liệu, các hàm VPR một số nhận dạng duy nhất được chuyển, được sử dụng để xác định thông tin chúng tôi muốn tìm (ví dụ: mã sản phẩm hoặc số nhận dạng khách hàng). Mã duy nhất này phải có trong cơ sở dữ liệu, nếu không VPR sẽ báo lỗi. Trong bài này, chúng ta sẽ xem xét cách sử dụng hàm này VPRkhi id hoàn toàn không tồn tại trong cơ sở dữ liệu. Như thể hàm VPR đã chuyển sang chế độ gần đúng và chọn dữ liệu sẽ cung cấp cho chúng tôi khi chúng tôi muốn tìm thứ gì đó. Trong những trường hợp nhất định, đây chính xác là những gì cần thiết.
Một ví dụ từ cuộc sống. Chúng tôi đặt ra nhiệm vụ
Hãy 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 dựa trên một loạt các số liệu bán hàng. Chúng tôi sẽ bắt đầu với một tùy chọn rất đơn giản, và sau đó chúng tôi sẽ dần dần phức tạp hóa nó cho đến khi giải pháp hợp lý duy nhất cho vấn đề là sử dụng hàm VPR. Kịch bản ban đầu cho nhiệm vụ giả tưởng của chúng tôi như sau: nếu một nhân viên bán hàng kiếm được hơn 30000 đô la doanh thu trong một năm, thì hoa hồng của anh ta là 30%. Nếu không, hoa hồng chỉ là 20%. Hãy đặt nó dưới dạng một bảng:
Người bán nhập dữ liệu bán hàng của họ vào ô B1 và công thức trong ô B2 xác định tỷ lệ hoa hồng chính xác mà người bán có thể mong đợi. Đổi lại, tỷ lệ kết quả được sử dụng trong ô B3 để tính tổng hoa hồng mà người bán sẽ nhận được (chỉ cần nhân các ô B1 và B2).
Phần thú vị nhất của bảng được chứa trong ô B2 - đây là công thức để xác định tỷ lệ hoa hồng. Công thức này chứa một hàm Excel được gọi là IF (NẾU). Đối với những độc giả không quen thuộc với chức năng này, tôi sẽ giải thích cách hoạt động của nó:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Điều kiện là một đối số hàm nhận giá trị của một trong hai MÃ ĐÚNG (Hoặc đúng KHÔNG ĐÚNG (SAI). Trong ví dụ trên, biểu thức B1
Có đúng là B1 nhỏ hơn B5 không?
Hoặc bạn có thể nói cách khác:
Có đúng là tổng số tiền bán hàng trong năm nhỏ hơn giá trị ngưỡng không?
Nếu chúng tôi trả lời câu hỏi này CÓ (TRUE), sau đó hàm trả về giá trị nếu đúng (giá trị nếu TRUE). Trong trường hợp của chúng tôi, đây sẽ là giá trị của ô B6, tức là tỷ lệ hoa hồng khi tổng doanh số bán hàng dưới ngưỡng. Nếu chúng tôi trả lời câu hỏi KHÔNG (FALSE) sau đó trả về giá trị nếu sai (giá trị nếu FALSE). Trong trường hợp của chúng tôi, đây là giá trị của ô B7, tức là tỷ lệ hoa hồng khi tổng doanh số bán hàng cao hơn ngưỡng.
Như bạn có thể thấy, nếu chúng ta lấy tổng doanh thu là 20000 đô la, chúng ta sẽ nhận được tỷ lệ hoa hồng 2% trong ô B20. Nếu chúng tôi nhập giá trị $ 40000, thì tỷ lệ hoa hồng sẽ thay đổi 30%:
Đây là cách bảng của chúng tôi hoạt động.
Chúng tôi phức tạp hóa nhiệm vụ
Hãy làm cho mọi thứ khó khăn hơn một chút. Hãy đặt một ngưỡng khác: nếu người bán kiếm được nhiều hơn $ 40000, thì tỷ lệ hoa hồng sẽ tăng lên 40%:
Mọi thứ có vẻ đơn giản và rõ ràng, nhưng công thức của chúng ta trong ô B2 trở nên phức tạp hơn đáng kể. Nếu bạn nhìn kỹ vào công thức, bạn sẽ thấy rằng đối số thứ ba của hàm IF (IF) biến thành một hàm chính thức khác IF (NẾU). Cấu trúc này được gọi là lồng các chức năng vào nhau. Excel vui vẻ cho phép các cấu trúc này và chúng thậm chí hoạt động, nhưng chúng khó đọc và hiểu hơn nhiều.
Chúng tôi sẽ không đi sâu vào chi tiết kỹ thuật - tại sao và cách nó hoạt động, và chúng tôi sẽ không đi sâu vào các sắc thái của việc viết các hàm lồng nhau. Rốt cuộc, đây là một bài báo dành riêng cho chức năng VPR, không phải là hướng dẫn đầy đủ về Excel.
Dù là trường hợp nào, công thức sẽ trở nên phức tạp hơn! Điều gì sẽ xảy ra nếu chúng tôi giới thiệu một tùy chọn khác với tỷ lệ hoa hồng là 50% cho những người bán kiếm được hơn 50000 đô la bán hàng. Và nếu ai đó đã bán được hơn $ 60000, họ sẽ trả 60% hoa hồng?
Bây giờ công thức trong ô B2, ngay cả khi nó được viết mà không có lỗi, đã trở nên hoàn toàn không thể đọc được. Tôi nghĩ rằng có rất ít người muốn sử dụng các công thức có 4 cấp độ lồng vào nhau trong các dự án của họ. Nó phải là một con đường dễ dàng hơn?!
Và có một cách như vậy! Chức năng sẽ giúp chúng ta VPR.
Chúng tôi áp dụng hàm Vlookup để giải quyết vấn đề
Hãy thay đổi thiết kế của bàn của chúng tôi một chút. Chúng tôi sẽ giữ tất cả các trường và dữ liệu giống nhau, nhưng sắp xếp chúng theo cách mới, gọn gàng hơn:
Hãy dành một chút thời gian và đảm bảo rằng bảng mới Bảng tỷ lệ bao gồm dữ liệu giống như bảng ngưỡng trước đó.
Ý tưởng chính là sử dụng hàm VPR để xác định mức thuế quan mong muốn theo bảng Bảng tỷ lệ tùy thuộc vào khối lượng bán hàng. Xin lưu ý rằng người bán có thể bán hàng hóa với số tiền không bằng một trong năm ngưỡng trong bảng. Ví dụ, anh ta có thể bán với giá 34988 đô la, nhưng không có số tiền đó. Hãy xem chức năng như thế nào VPR có thể đối phó với một tình huống như vậy.
Chèn hàm VLOOKUP
Chọn ô B2 (nơi chúng tôi muốn chèn công thức của mình) và tìm VLOOKUP (VLOOKUP) trong Thư viện Hàm Excel: Công thức (công thức)> Thư viện hàm (Thư viện chức năng)> Tra cứu & Tham khảo (Tài liệu tham khảo và mảng).
Một hộp thoại xuất hiện Đối số hàm (Các đối số của hàm). Chúng tôi điền lần lượt vào các giá trị của các đối số, bắt đầu bằng Tra cứu_value (Giá trị tra cứu). Trong ví dụ này, đây là tổng số tiền bán hàng từ ô B1. Đặt con trỏ vào trường Tra cứu_value (Lookup_value) và chọn ô B1.
Tiếp theo, bạn cần xác định các chức năng VPRtìm kiếm dữ liệu ở đâu. Trong ví dụ của chúng tôi, đây là một bảng Bảng tỷ lệ. Đặt con trỏ vào trường Bảng_mảng (Bảng) và chọn toàn bộ bảng Bảng tỷ lệngoại trừ tiêu đề.
Tiếp theo, chúng ta cần chỉ định cột nào để trích xuất dữ liệu bằng cách sử dụng công thức của chúng tôi. Chúng tôi quan tâm đến tỷ lệ hoa hồng, nằm trong cột thứ hai của bảng. Do đó, đối với lập luận Col_index_num (Column_number) nhập giá trị 2.
Và cuối cùng, chúng tôi giới thiệu đối số cuối cùng - Phạm vi_lookup (Khoảng_tra cứu).
Quan trọng: chính việc sử dụng đối số này đã tạo ra sự khác biệt giữa hai cách áp dụng hàm VPR. Khi làm việc với cơ sở dữ liệu, đối số Phạm vi_lookup (range_lookup) phải luôn có một giá trị KHÔNG ĐÚNG (FALSE) để tìm kiếm kết quả phù hợp chính xác. Trong việc sử dụng chức năng của chúng tôi VPR, chúng ta phải để trống trường này hoặc nhập một giá trị MÃ ĐÚNG (THẬT). Điều cực kỳ quan trọng là chọn tùy chọn này một cách chính xác.
Để làm rõ hơn, chúng tôi sẽ giới thiệu MÃ ĐÚNG (TRUE) trong lĩnh vực này Phạm vi_lookup (Interval_lookup). Mặc dù, nếu bạn để trống trường, đây sẽ không phải là lỗi, vì MÃ ĐÚNG là giá trị mặc định của nó:
Chúng tôi đã điền vào tất cả các thông số. Bây giờ chúng tôi nhấn OKvà Excel tạo công thức cho chúng ta bằng một hàm VPR.
Nếu chúng tôi thử nghiệm với một số giá trị khác nhau cho tổng số tiền bán hàng, thì chúng tôi sẽ đảm bảo rằng công thức hoạt động chính xác.
Kết luận
Khi hàm VPR làm việc với cơ sở dữ liệu, đối số Phạm vi_lookup (range_lookup) phải chấp nhận KHÔNG ĐÚNG (SAI). Và giá trị được nhập là Tra cứu_value (Giá trị tìm kiếm) phải tồn tại trong cơ sở dữ liệu. Nói cách khác, nó đang tìm kiếm một kết hợp chính xác.
Trong ví dụ mà chúng tôi đã xem xét trong bài viết này, không cần thiết phải có kết quả khớp chính xác. Đây là trường hợp khi hàm VPR phải chuyển sang chế độ gần đúng để trả về kết quả mong muốn.
Ví dụ: Chúng tôi muốn xác định tỷ lệ nào để sử dụng trong tính toán hoa hồng cho một nhân viên bán hàng có doanh số bán hàng là $ 34988. Hàm số VPR trả lại cho chúng tôi giá trị 30%, điều này hoàn toàn chính xác. Nhưng tại sao công thức lại chọn hàng chứa chính xác 30% chứ không phải 20% hoặc 40%? Tìm kiếm gần đúng có nghĩa là gì? Chúng ta hãy rõ ràng.
Khi đối số Phạm vi_lookup (khoảng_lookup) có một giá trị MÃ ĐÚNG (TRUE) hoặc bị bỏ qua, hàm VPR lặp qua cột đầu tiên và chọn giá trị lớn nhất không vượt quá giá trị tra cứu.
Tâm điểm: Để lược đồ này hoạt động, cột đầu tiên của bảng phải được sắp xếp theo thứ tự tăng dần.