Các hàm trong Excel có thể rất mạnh nếu bạn biết kết hợp các công thức khác nhau.Những gì từng tưởng chừng như không thể bỗng chốc trở thành trò chơi của trẻ con.
Một ví dụ như vậy là tìm giá trị gần nhất trong tập dữ liệu Excel.
Có một số hàm tra cứu hữu ích trong Excel (chẳng hạn như hàm VLOOKUP và INDEX MATCH) có thể tìm thấy hàm gần nhất值(như trong ví dụ bên dưới).
Nhưng phần tốt nhất là bạn có thể kết hợp các hàm tra cứu này với các hàm Excel khác để làm được nhiều việc hơn thế (bao gồm cả việc tìm kết quả phù hợp nhất với giá trị tra cứu trong danh sách chưa được sắp xếp).
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách sử dụng công thức tra cứu để tìm kết quả phù hợp nhất với giá trị tra cứu trong Excel.
Có nhiều tình huống khác nhau mà bạn cần tìm giá trị khớp gần nhất (hoặc giá trị khớp gần nhất).
Đây là một ví dụ mà tôi sẽ đề cập trong bài viết này:
- Tìm tỷ lệ hoa hồng dựa trên doanh số bán hàng
- Tìm ứng viên tốt nhất (dựa trên kinh nghiệm gần nhất)
- Tìm ngày sự kiện tiếp theo
hãy bắt đầu!
Nội dung
Tìm tỷ lệ hoa hồng (tìm giá trị bán hàng gần nhất)
Giả sử bạn có một tập dữ liệu như bên dưới, nơi bạn muốn tìm tỷ lệ hoa hồng cho tất cả nhân viên bán hàng.
Hoa hồng được phân phối dựa trên giá trị của việc bán hàng.Điều này được tính toán bằng cách sử dụng bảng bên phải.
Ví dụ: nếu tổng doanh số của nhân viên bán hàng là 5000, hoa hồng là 0% và nếu tổng doanh số của nhân viên bán hàng là 15000, hoa hồng là 5%.
Để có được tỷ lệ hoa hồng, bạn cần phải tìm phạm vi bán hàng gần nhất mà chỉ thấp hơn giá trị bán hàng.Ví dụ: đối với doanh số bán hàng 15000, hoa hồng là 10,000 (hoặc 5%) và đối với doanh số bán hàng 25000, tỷ lệ hoa hồng là 20,000 (hoặc 7%).
Để tìm giá trị bán hàng gần nhất và nhận tỷ lệ hoa hồng, bạn có thể sử dụng đối sánh gần đúng trong hàm VLOOKUP.
Công thức sau có thể thực hiện điều này:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
Lưu ý rằng trong công thức này, tham số cuối cùng là 1, cho biết công thức sử dụng tra cứu gần đúng.Điều này có nghĩa là công thức sẽ đi qua giá trị bán hàng trong cột E và tìm giá trị ngay bên dưới giá trị tra cứu.
Công thức VLOOKUP sau đó sẽ cung cấp tỷ lệ hoa hồng cho giá trị đó.
注意: Để làm điều này, bạn cần sắp xếp dữ liệu theo thứ tự tăng dần.
Tìm ứng viên tốt nhất (dựa trên kinh nghiệm gần nhất)
Trong ví dụ trên, dữ liệu cần được sắp xếp theo thứ tự tăng dần.Nhưng có thể có trường hợp dữ liệu không được sắp xếp.
Vì vậy, hãy lấy một ví dụ và xem cách chúng ta có thể sử dụng kết hợp công thức để tìm kết quả phù hợp nhất trong Excel.
Dưới đây là tập dữ liệu ví dụ mà tôi cần tìm tên của những nhân viên có kinh nghiệm làm việc gần nhất với giá trị mong muốn.Giá trị kỳ vọng trong trường hợp này là 2.5 năm.
Lưu ý rằng dữ liệu không được sắp xếp.Ngoài ra, những trải nghiệm gần nhất có thể ít hơn hoặc nhiều hơn một trải nghiệm nhất định.Ví dụ, 2 năm và 3 năm gần bằng nhau (chênh lệch 0.5 năm).
Đây là công thức cho chúng ta kết quả:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Mẹo trong công thức này là thay đổi mảng tra cứu và các giá trị tra cứu để tìm ra sự khác biệt nhỏ nhất theo kinh nghiệm giữa giá trị mong muốn và giá trị thực tế.
Trước tiên, hãy hiểu cách bạn thực hiện theo cách thủ công (và sau đó tôi sẽ giải thích cách hoạt động của công thức này).
Khi bạn thực hiện việc này theo cách thủ công, bạn đi qua từng ô trong cột B và tìm sự khác biệt giữa trải nghiệm bạn muốn và trải nghiệm có.Khi bạn đã có tất cả các điểm khác biệt, bạn sẽ tìm thấy điểm khác biệt nhỏ nhất và lấy tên của người đó.
Đó chính xác là những gì chúng tôi làm với công thức này.
Hãy để tôi giải thích.
Giá trị tra cứu trong công thức MATCH là MIN (ABS (D2-B2: B15)).
Phần này cung cấp cho bạn sự khác biệt tối thiểu giữa kinh nghiệm đã cho (tức là 2.5 năm) và tất cả các kinh nghiệm khác.Trong trường hợp này, nó trả về 0.3
Lưu ý rằng tôi sử dụng ABS để đảm bảo rằng tôi đang tìm kiếm gần nhất (có thể nhiều hơn hoặc ít hơn so với trải nghiệm đã cho).
Bây giờ, giá trị tối thiểu này trở thành giá trị tra cứu của chúng tôi.
Mảng tra cứu trong hàm MATCH là ABS (D2- $ B $ 2: $ B $ 15).
Điều này cho chúng ta một mảng số mà từ đó 2.5 (yêu cầu kinh nghiệm) bị trừ.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
Hàm MATCH tìm vị trí 0.3 trong mảng này, là vị trí tên của người có kinh nghiệm gần nhất.
Sau đó, hàm INDEX sử dụng số công việc để trả về tên của người đó.
Lưu ý: Nếu có nhiều ứng viên có cùng kinh nghiệm tối thiểu, công thức trên sẽ cung cấp tên của nhân viên phù hợp đầu tiên.
Tìm ngày sự kiện tiếp theo
Đây là một ví dụ khác trong đó bạn có thể sử dụng công thức tra cứu để tìm ngày tiếp theo của sự kiện dựa trên ngày hiện tại.
Dưới đây là tập dữ liệu mà tôi có tên sự kiện và ngày diễn ra sự kiện.
Điều tôi muốn là tên của sự kiện tiếp theo và ngày diễn ra sự kiện sắp tới này.
Đây là công thức cung cấp tên của sự kiện sắp tới:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Công thức sau sẽ cung cấp các ngày diễn ra sự kiện sắp tới:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Hãy để tôi giải thích cách hoạt động của công thức này.
Để lấy ngày diễn ra sự kiện, hàm MATCH tìm ngày hiện tại trong cột B.Trong trường hợp này, chúng tôi không tìm kiếm đối sánh chính xác mà là đối sánh gần đúng.Do đó, tham số cuối cùng của hàm MATCH là 1 (nó tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu).
Vì vậy, hàm MATCH sẽ trả về vị trí của ô có ngày xảy ra nhỏ hơn hoặc bằng ngày hiện tại.Vì vậy, trong trường hợp này, sự kiện tiếp theo sẽ nằm trong ô tiếp theo (vì danh sách được sắp xếp theo thứ tự tăng dần).
Vì vậy, để có được ngày diễn ra sự kiện sắp tới, chỉ cần thêm một vào vị trí ô được trả về bởi hàm MATCH và nó sẽ cung cấp cho bạn vị trí ô của ngày diễn ra sự kiện tiếp theo.
Giá trị này sau đó được cung cấp bởi hàm INDEX.
Để lấy tên sự kiện, hãy sử dụng cùng một công thức và thay đổi phạm vi trong hàm INDEX từ cột B sang cột A.
Ví dụ này xuất hiện trong đầu tôi khi một người bạn đưa ra yêu cầu.Anh ấy liệt kê sinh nhật của tất cả bạn bè / người thân của mình trong một cột và muốn biết sinh nhật tiếp theo sắp đến (và tên của người đó).
Ba ví dụ trên cho thấy cách sử dụng công thức tra cứu để tìm giá trị gần nhất trong Excel.
