Bài đăng này khám phá các chức năng tra cứu của Excel, đối sánh gần đúng, tra cứu mờ và đối sánh chính xác. Các hàm tra cứu tích hợp trong Excel, chẳng hạn như VLOOKUP, thật tuyệt vời. Khi được triển khai đúng cách cho các dự án đặc biệt hoặc trong sổ làm việc sử dụng định kỳ, chúng có thể tiết kiệm rất nhiều thời gian. Riêng hàm VLOOKUP đã tiết kiệm vô số giờ trong sổ làm việc sử dụng định kỳ của tôi. Tuy nhiên, hàm VLOOKUP, tương tự như các hàm tra cứu khác của Excel như HLOOKUP và MATCH, được xây dựng để thực hiện đối sánh chính xác hoặc tra cứu phạm vi. Cả hai điều này hoàn toàn khác với đối sánh gần đúng hoặc tra cứu mờ. Bài đăng này thảo luận chi tiết về những ý tưởng này và trình bày cách thực hiện tra cứu mờ trong Excel 2010 trở lên Show *** BÀI ĐĂNG BLOG CẬP NHẬT. TRÒ CHƠI Mờ CÓ SẴN TRONG POWER QUERY *** Hiểu các hàm tra cứu tích hợpCác hàm tra cứu tích hợp trong Excel, chẳng hạn như VLOOKUP, HLOOKUP và MATCH, hoạt động với logic tra cứu tương tự. Để đơn giản hóa bài đăng này, chúng tôi sẽ chỉ sử dụng một ví dụ. Vì hàm VLOOKUP có lẽ là hàm tra cứu được sử dụng nhiều nhất và quen thuộc nhất nên chúng ta sẽ sử dụng nó khi khám phá những ý tưởng này Ý tưởng cơ bản của hàm tra cứu Excel là tìm kiếm một giá trị trong danh sách. Ví dụ: chúng ta có thể yêu cầu Excel tìm “Công ty ABC” trong danh sách tên khách hàng. Đó là ý tưởng cơ bản, nhưng có rất nhiều ứng dụng của các hàm tra cứu và việc triển khai có thể trở nên khá tinh vi và mạnh mẽ Đối với bài đăng này, tôi muốn chia các tác vụ mà hàm tra cứu thực hiện thành hai bước. Tôi sẽ gọi bước một là khớp và bước hai là quay lại. Trong bước đầu tiên so khớp, Excel phải tìm giá trị khớp. Bạn cho Excel biết giá trị cần tìm, chẳng hạn như “Công ty ABC” và bạn cho Excel biết nơi cần tìm, chẳng hạn như trong một dải ô. Bạn đang yêu cầu Excel tìm giá trị tra cứu trong phạm vi tra cứu Bước hai, trả về, là kết quả của hàm. Đó là giá trị mà hàm sẽ trả về cho ô. Một số hàm tra cứu, chẳng hạn như hàm MATCH, yêu cầu Excel trả về số vị trí. Các hàm tra cứu khác, chẳng hạn như hàm VLOOKUP, yêu cầu Excel trả về một giá trị liên quan. Vì vậy, dựa trên việc bạn chọn hàm tra cứu nào và giá trị đối số hàm nào bạn nhập, Excel sẽ biết nội dung trả về sau khi tìm thấy kết quả khớp. Càng xa càng tốt? Hãy làm một ví dụ nhanh vào thời điểm này Tôi muốn tìm tên khách hàng cụ thể “Công ty ABC” trong danh sách khách hàng và nếu tìm thấy, tôi muốn Excel trả về id khách hàng được tìm thấy trong cột tiếp theo Tôi sẽ sử dụng hàm VLOOKUP và tôi sẽ yêu cầu nó tìm “Công ty ABC” trong Bảng khách hàng và trả về ID. Giả sử tên khách hàng đã được nhập vào C7 và khách hàng được lưu trữ trong Bảng có tên là Bảng1, thì hàm sau sẽ thực hiện thủ thuật =VLOOKUP(C7, Bảng1, 2, SAI) Ở đâu
Chức năng này được nhập vào C8 trong ảnh chụp màn hình bên dưới Như bạn có thể thấy, ID AC100 đã được trả về thành công cho ô công thức C8. Và bạn tôi đó là ý tưởng cơ bản của hàm VLOOKUP. Tìm một giá trị (trận đấu) và tính toán kết quả (trả về) Điều quan trọng cần lưu ý là giá trị tra cứu, chuỗi văn bản “Công ty ABC” phải được tìm thấy trong phạm vi tra cứu. Ngoại trừ trường hợp (trên và dưới), hai giá trị phải khớp chính xác. “Công ty ABC” sẽ không khớp với “Công ty ABC, Inc. ”, “Công ty ABC” hoặc “Công ty ABC “. Không có khoảng trắng ở đầu, không có khoảng trắng ở cuối, không có chữ viết tắt hoặc ký tự thừa. Chúng phải giống nhau. Điều này được gọi là đối sánh chính xác. Nếu giá trị không giống nhau, hàm sẽ không khớp với giá trị đó và bạn sẽ gặp lỗi, như thể hiện trong ảnh chụp màn hình bên dưới Bây giờ chúng ta đã tìm hiểu những điều cơ bản, đã đến lúc khám phá đối số thứ tư của hàm VLOOKUP Sự thật về Đối số thứ tư VLOOKUPĐối số thứ tư của hàm VLOOKUP được đặt tên chính thức. range_lookup. Đó là một đối số boolean, nghĩa là bạn có thể chuyển cho nó một giá trị TRUE hoặc FALSE hoặc bất kỳ biểu diễn nào khác của TRUE hoặc FALSE. Điều có xu hướng đánh lừa người dùng Excel là mô tả mà Microsoft đã sử dụng cho các tùy chọn này. Excel mô tả giá trị TRUE là “Kết hợp gần đúng” và FALSE là “Kết hợp chính xác. ” Một mô tả rõ ràng hơn sẽ giống như ĐÚNG “Bạn đang thực hiện tra cứu phạm vi” và SAI “Bạn không thực hiện tra cứu phạm vi” nhưng trong bất kỳ trường hợp nào, các mô tả đều đúng như vậy Khi bạn chọn TRUE (Kết hợp gần đúng), bạn không yêu cầu Excel khớp các giá trị gần giống nhau. Mô tả Khớp gần đúng có xu hướng ngụ ý rằng hàm sẽ khớp với “Công ty ABC” và “Công ty ABC. ” vì chúng gần giống nhau. Trong một số trường hợp và trong một số tập dữ liệu, ý tưởng này sẽ hoạt động. Nhưng ý tưởng này không hoạt động trong mọi trường hợp và do đó, không thể dựa vào sổ làm việc của chúng tôi. Ví dụ: trong ảnh chụp màn hình bên dưới, chức năng không tìm thấy kết quả khớp giữa “ABC Company” và “ABC Company, Inc. ” bằng chứng là ID không chính xác được trả về trong C8 Tuy nhiên, trong ảnh chụp màn hình sau đây, chức năng đã tìm thấy sự trùng khớp giữa “Công ty ABC” và “Công ty ABC” bằng chứng là ID dự kiến được trả về cho C8 Cách mà chức năng thực sự hoạt động khi TRUE được chọn là thế này. nó đi xuống từng hàng trong danh sách và cuối cùng dừng lại ở hàng nhỏ hơn giá trị và nơi hàng tiếp theo lớn hơn giá trị. Đây là lý do tại sao phạm vi tra cứu phải được sắp xếp theo thứ tự tăng dần để hàm trả về kết quả chính xác khi đối số thứ tư là TRUE Ý tưởng này có thể gây nhầm lẫn khi nghĩ về các chuỗi văn bản, nhưng sẽ có ý nghĩa hơn khi nghĩ về các con số. Ví dụ: khi cố gắng tìm tỷ lệ hoa hồng chính xác dựa trên giá trị bán hàng. Trong trường hợp này, bạn muốn thực hiện tra cứu phạm vi. Bạn muốn tra cứu một giá trị từ trong một phạm vi. Điều này được minh họa trong ảnh chụp màn hình bên dưới Hàm đi xuống từng hàng cố gắng xác định hàng nào sẽ dừng lại. Nó tiếp tục đi xuống cho đến khi tìm thấy một hàng lớn hơn giá trị tra cứu và sau đó nó dừng lại ở hàng trước đó. Nó dừng trên hàng nhỏ hơn giá trị và ở vị trí hàng tiếp theo lớn hơn giá trị tra cứu. Điều này khá dễ hiểu khi nghĩ về các con số, nhưng có thể khó hình dung hơn khi nghĩ về các chuỗi văn bản. Tuy nhiên, chìa khóa để hiểu đối số hàm này là nhận ra rằng logic giống hệt nhau khi hoạt động trên chuỗi văn bản và số. Đây là lý do tại sao “Công ty ABC” không khớp với “Công ty ABC, Inc. ”, bởi vì “Công ty ABC. ” lớn hơn Công ty ABC. Đây là lý do tại sao "Công ty ABC" sẽ khớp với "Công ty ABC", bởi vì "Công ty ABC" nhỏ hơn "Công ty ABC". ” Như bạn có thể thấy, chúng tôi không nghĩ đến điều này khi nghĩ về đối sánh gần đúng Tra cứu mờ hay còn gọi là So sánh gần đúng là gìĐối với chúng tôi, đối sánh gần đúng có nghĩa là hai chuỗi văn bản gần giống nhau, nhưng không nhất thiết phải giống hệt nhau, phải khớp với nhau. Ví dụ: “Công ty ABC” phải khớp với “Công ty ABC, Inc. ,” “Công ty ABC,” và “Công ty ABC. ” Chúng tôi coi đối sánh gần đúng là một loại mờ, trong đó một số ký tự khớp nhưng không phải tất cả Ý tưởng của tra cứu mờ là các giá trị không khớp rõ ràng, chúng không giống nhau. Nhưng chúng có khả năng là trùng khớp, có khả năng chúng trùng nhau. Họ có khả năng đại diện cho cùng một thực thể cơ bản Bây giờ chúng ta nhận ra rằng hàm VLOOKUP không thực sự thực hiện logic đối sánh gần đúng, ít nhất là không theo cách chúng ta muốn, chúng ta sẽ làm gì? Thêm vào trongKhi bạn va phải một bức tường, hãy đi vòng qua nó. Do các hàm tra cứu tích hợp không thực hiện logic mờ khi thực hiện so khớp, nên chúng tôi gặp phải giới hạn tích hợp sẵn của Excel. Microsoft đã đưa ra một cách để giải quyết hạn chế này bằng cách cung cấp một phần bổ trợ miễn phí Microsoft cung cấp một bổ trợ miễn phí cho phép Excel thực hiện tra cứu mờ. Nó được gọi là “Phần bổ trợ tra cứu mờ cho Excel” và có sẵn tại thời điểm đăng bài này tại liên kết bên dưới http. //www. Microsoft. com/en-us/tải xuống/chi tiết. aspx?id=15011 Sau khi cài đặt, bổ trợ này thực hiện tra cứu mờ. Nó không thay đổi hành vi của bất kỳ hàm tra cứu tích hợp nào. Nó không cho phép các hàm VLOOKUP của bạn thực hiện tra cứu mờ. Đây là một phần bổ trợ về cơ bản xử lý hai danh sách và tính toán xác suất khớp Bạn chỉ định hai bảng và trong mỗi bảng, các cột cần kiểm tra. Về cơ bản, bạn xác định bước một trận đấu. Sau đó, bạn xác định bước hai bằng cách xác định cột nào từ các bảng sẽ được đưa vào kết quả. Bạn cũng có thể chỉ định ngưỡng xác suất. Bạn nhấn go và phần bổ trợ thực hiện công việc của nó, sau đó xuất bảng kết quả bắt đầu từ ô hiện hoạt. Về cơ bản, nó tạo một báo cáo tĩnh dựa trên cài đặt bạn chọn Dưới đây là ảnh chụp màn hình của đầu ra, cho thấy rằng nó đã khớp thành công “Công ty ABC” và “Công ty ABC. ” trong cùng một tập dữ liệu khiến chức năng VLOOKUP của chúng tôi bị lỗi Để biết thêm thông tin về bổ trợ tra cứu mờ và chi tiết hơn về cách sử dụng nó, vui lòng truy cập liên kết Microsoft ở trên. Phần bổ trợ đi kèm với hướng dẫn, tệp Excel mẫu và tệp pdf có nền và logic mà phần bổ trợ sử dụng để thực hiện phép thuật của mình. Nó cũng đi kèm với một giấy phép, vì vậy, bạn sẽ muốn chắc chắn đọc các điều khoản cấp phép trong LicenseTerms. tài liệu rtf đi kèm với bản tải xuống Có một số khoa học máy tính và toán học cực kỳ thú vị hoạt động đằng sau hậu trường, bao gồm tính tương tự của Jaccard, mã hóa bản ghi và phép biến đổi. Toán học khá nặng trong đó. Cảm ơn Nghiên cứu của Microsoft về bổ trợ này |
