Nội dung
Hướng dẫn nhỏ này giải thích cách tạo một hàm VPR (VLOOKUP) phân biệt chữ hoa chữ thường, hiển thị một số công thức khác mà Excel có thể tìm kiếm theo cách phân biệt chữ hoa chữ thường và chỉ ra điểm mạnh và điểm yếu của từng hàm.
Tôi đoán mọi người dùng Excel đều biết chức năng nào thực hiện tìm kiếm dọc. Đúng rồi, đó là một chức năng VPR. Tuy nhiên, ít người biết rằng VPR không phân biệt chữ hoa chữ thường, tức là các ký tự chữ thường và chữ hoa giống hệt nhau.
Đây là một ví dụ nhanh chứng minh sự bất lực VPR ghi nhận đăng ký. Giả sử trong một ô A1 chứa giá trị “hóa đơn” và ô A2 – “Hóa đơn”, công thức:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… sẽ ngừng tìm kiếm “hóa đơn” vì giá trị đó xuất hiện đầu tiên trong danh sách và trích xuất giá trị từ ô B1.
Ở phần sau của bài viết này, tôi sẽ chỉ cho bạn cách làm VPR trường hợp nhạy cảm. Ngoài ra, chúng ta sẽ tìm hiểu thêm một số hàm có thể thực hiện tìm kiếm phân biệt chữ hoa chữ thường trong Excel.
Chúng ta sẽ bắt đầu với cách đơn giản nhất – XEM (TÌM KIẾM) và SUMPRODUCT (TỔNG HỢP), thật không may, có một số hạn chế đáng kể. Tiếp theo, chúng ta sẽ xem xét kỹ hơn công thức phức tạp hơn một chút INDEX + MATCH (INDEX+MATCH), hoạt động hoàn hảo trong mọi tình huống và với mọi tập dữ liệu.
Hàm VLOOKUP phân biệt chữ hoa chữ thường
Như bạn đã biết, chức năng thông thường VPR không phân biệt chữ hoa chữ thường. Tuy nhiên, có một cách để làm cho nó phân biệt chữ hoa chữ thường. Để làm điều này, bạn cần thêm một cột phụ vào bảng, như trong ví dụ sau.
Giả sử trong một cột B có mã định danh sản phẩm (Mặt hàng) và bạn muốn trích xuất giá của sản phẩm và comment tương ứng từ các cột C и D. Vấn đề là mã định danh chứa cả ký tự chữ thường và chữ hoa. Ví dụ: giá trị ô B4 (001Tvci3u) và B5 (001Tvci3U) chỉ khác nhau ở ký tự cuối cùng, u и U tương ứng.
Như bạn có thể tưởng tượng, công thức tìm kiếm thông thường
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
sẽ trở lại $ 90, vì giá trị 001Tvci3u nằm trong phạm vi tìm kiếm sớm hơn 001Tvci3U. Nhưng đó không phải là thứ chúng ta cần, phải không?
Để tìm kiếm với một chức năng VPR trong Excel có phân biệt chữ hoa chữ thường, bạn sẽ phải thêm một cột trợ giúp và điền vào các ô của nó bằng công thức sau (trong đó B là cột tra cứu):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Công thức này chia giá trị mong muốn thành các ký tự riêng biệt, thay thế mỗi ký tự bằng mã của nó (ví dụ: thay vì A thay vào đó là ở tuổi 65 a mã 97) rồi kết hợp các mã này thành một chuỗi số duy nhất.
Sau đó, chúng ta sử dụng một hàm đơn giản VPR đối với tìm kiếm phân biệt chữ hoa chữ thường:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Vận hành đúng chức năng VPR phân biệt chữ hoa chữ thường phụ thuộc vào hai yếu tố:
- Cột trợ giúp phải là cột ngoài cùng bên trái trong phạm vi có thể xem được.
- Giá trị bạn đang tìm kiếm phải chứa mã ký tự thay vì giá trị thực.
Cách sử dụng chức năng CODE đúng cách
Công thức được chèn vào các ô của cột phụ giả định rằng tất cả các giá trị tìm kiếm của bạn đều có cùng số ký tự. Nếu không, thì bạn cần biết số nhỏ nhất và số lớn nhất và thêm càng nhiều tính năng càng tốt SỐ PHIẾU (IFERROR) có bao nhiêu ký tự là sự khác biệt giữa giá trị được tìm kiếm ngắn nhất và dài nhất.
Ví dụ: nếu giá trị tìm kiếm ngắn nhất là 3 ký tự và dài nhất là 5 ký tự, hãy sử dụng công thức sau:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Đối với chức năng PSTR (MID) Bạn cung cấp các đối số sau:
- Đối số thứ nhất – văn bản (văn bản) là văn bản hoặc tham chiếu ô chứa các ký tự cần trích xuất (trong trường hợp của chúng tôi là B2)
- Đối số thứ nhất – số_bắt đầu (start_position) là vị trí của ký tự đầu tiên được trích xuất. bạn vào 1 trong chức năng đầu tiên PSTR, 2 – trong chức năng thứ hai PSTR và vv
- Đối số thứ nhất – số_chars (number_of_characters) – Chỉ định số lượng ký tự cần trích xuất từ văn bản. Vì chúng ta chỉ cần 1 ký tự mọi lúc nên trong tất cả các hàm chúng ta viết 1.
GIỚI HẠN: Chức năng VPR không phải là giải pháp tốt nhất cho việc tìm kiếm phân biệt chữ hoa chữ thường trong Excel. Đầu tiên, việc bổ sung thêm cột phụ là cần thiết. Thứ hai, công thức chỉ hoạt động tốt nếu dữ liệu đồng nhất hoặc biết chính xác số ký tự trong các giá trị tìm kiếm. Nếu đây không phải là trường hợp của bạn, tốt hơn là sử dụng một trong những giải pháp mà chúng tôi trình bày bên dưới.
Hàm LOOKUP để tìm kiếm phân biệt chữ hoa chữ thường
Chức năng XEM (TÌM KIẾM) liên quan VPR, tuy nhiên cú pháp của nó cho phép tìm kiếm phân biệt chữ hoa chữ thường mà không cần thêm cột phụ. Để làm điều này, sử dụng XEM kết hợp với chức năng CHÍNH XÁC (CHÍNH XÁC).
Nếu chúng ta lấy dữ liệu từ ví dụ trước (không có cột phụ), thì công thức sau sẽ giải quyết được nhiệm vụ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Tìm kiếm công thức trong phạm vi A2: A7 khớp chính xác với giá trị ô F2 phân biệt chữ hoa chữ thường và trả về giá trị từ cột B của cùng hàng.
Như VPRchức năng XEM hoạt động như nhau với các giá trị văn bản và số, như bạn có thể thấy trong ảnh chụp màn hình bên dưới:
Quan trọng! Để có chức năng XEM hoạt động chính xác, các giá trị trong cột tra cứu phải được sắp xếp theo thứ tự tăng dần, tức là từ nhỏ nhất đến lớn nhất.
Hãy để tôi giải thích ngắn gọn cách hoạt động của chức năng này CHÍNH XÁC trong công thức hiển thị ở trên, vì đây là điểm mấu chốt.
Chức năng CHÍNH XÁC so sánh hai giá trị văn bản trong đối số thứ 1 và thứ 2 và trả về TRUE nếu chúng hoàn toàn giống nhau hoặc FALSE nếu không giống nhau. Điều quan trọng đối với chúng tôi là chức năng CHÍNH XÁC trường hợp nhạy cảm.
Hãy xem công thức của chúng tôi hoạt động như thế nào XEM + CHÍNH XÁC:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Chức năng CHÍNH XÁC so sánh giá trị ô F2 với tất cả các phần tử trong một cột A (A2:A7). Trả về TRUE nếu tìm thấy kết quả khớp chính xác, nếu không thì FALSE.
- Vì bạn đưa ra đối số hàm đầu tiên XEM giá trị TRUE, nó chỉ trích xuất giá trị tương ứng từ cột được chỉ định (trong trường hợp của chúng tôi là cột B) nếu tìm thấy kết quả khớp chính xác, phân biệt chữ hoa chữ thường.
Tôi hy vọng lời giải thích này đã rõ ràng và bây giờ bạn đã hiểu ý chính. Nếu vậy thì bạn sẽ không gặp bất kỳ khó khăn nào với các chức năng khác mà chúng tôi sẽ phân tích sâu hơn, bởi vì. tất cả đều hoạt động trên cùng một nguyên tắc.
GIỚI HẠN: Dữ liệu trong cột tra cứu phải được sắp xếp theo thứ tự tăng dần.
TỔNG HỢP – tìm giá trị văn bản, phân biệt chữ hoa chữ thường nhưng chỉ trả về số
Như bạn đã hiểu từ tiêu đề, SUMPRODUCT (SUMPRODVEL) là một hàm Excel khác sẽ giúp bạn thực hiện tìm kiếm phân biệt chữ hoa chữ thường nhưng sẽ chỉ trả về các giá trị số. Nếu tùy chọn này không phù hợp với bạn, thì bạn có thể chuyển ngay sang gói INDEX + MATCH, đưa ra giải pháp cho mọi trường hợp và cho mọi loại dữ liệu.
Đầu tiên, hãy để tôi giải thích ngắn gọn cú pháp của hàm này, điều này sẽ giúp bạn hiểu rõ hơn về công thức phân biệt chữ hoa chữ thường sau đây.
Chức năng SUMPRODUCT nhân các phần tử của mảng đã cho và trả về tổng kết quả. Cú pháp trông như thế này:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Vì chúng tôi cần tìm kiếm phân biệt chữ hoa chữ thường nên chúng tôi sử dụng hàm CHÍNH XÁC (CHÍNH XÁC) từ ví dụ trước dưới dạng một trong các số nhân:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Như bạn nhớ, CHÍNH XÁC so sánh giá trị ô F2 với tất cả các phần tử trong một cột A. Trả về TRUE nếu tìm thấy kết quả khớp chính xác, nếu không thì FALSE. Trong các phép toán, Excel lấy TRUE làm 1và FALSE cho 0Xa hơn SUMPRODUCT nhân các số này và tính tổng kết quả.
Các số 0 không được tính vì khi nhân lên chúng luôn cho kết quả 0. Chúng ta hãy xem xét kỹ hơn điều gì sẽ xảy ra khi một kết quả khớp chính xác trong một cột A tìm thấy và trả lại 1… Hàm số SUMPRODUCT nhân số trong cột B on 1 và trả về kết quả – chính xác là cùng một con số! Điều này là do kết quả của các tích khác bằng 0 và chúng không ảnh hưởng đến tổng kết quả.
Thật không may, chức năng SUMPRODUCT không thể hoạt động với các giá trị văn bản và ngày tháng vì chúng không thể nhân lên được. Trong trường hợp này, bạn sẽ nhận được thông báo lỗi #GIÁ TRỊ! (#VALUE!) như trong một ô F4 trong hình dưới đây:
GIỚI HẠN: Chỉ trả về giá trị số.
INDEX + MATCH – tìm kiếm phân biệt chữ hoa chữ thường cho bất kỳ loại dữ liệu nào
Cuối cùng, chúng ta đã tiến gần đến một công thức tìm kiếm không giới hạn và phân biệt chữ hoa chữ thường, hoạt động với bất kỳ tập dữ liệu nào.
Ví dụ này xuất hiện cuối cùng, không phải vì món ngon nhất được dành cho món tráng miệng mà vì kiến thức thu được từ các ví dụ trước sẽ giúp bạn hiểu công thức phân biệt chữ hoa chữ thường tốt hơn và nhanh hơn. INDEX + MATCH (CHỈ SỐ + TRẬN ĐẤU).
Như bạn có thể đoán, sự kết hợp của các chức năng THÊM ĐƯỢC TIẾP XÚC и INDEX được sử dụng trong Excel như một giải pháp thay thế linh hoạt và mạnh mẽ hơn cho VPR. Bài viết Sử dụng INDEX và MATCH thay vì VLOOKUP sẽ giải thích một cách hoàn hảo cách các hàm này phối hợp với nhau.
Tôi sẽ chỉ tóm tắt lại những điểm chính:
- Chức năng THÊM ĐƯỢC TIẾP XÚC (MATCH) tìm kiếm một giá trị trong một phạm vi nhất định và trả về vị trí tương đối của nó, tức là số hàng và/hoặc số cột;
- Tiếp theo, chức năng INDEX (INDEX) trả về giá trị từ một cột và/hoặc hàng được chỉ định.
Để công thức INDEX + MATCH có thể tìm kiếm phân biệt chữ hoa chữ thường, bạn chỉ cần thêm một hàm vào đó. Không khó để đoán nó là gì nữa CHÍNH XÁC (CHÍNH XÁC):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Trong công thức này CHÍNH XÁC hoạt động tương tự như khi kết hợp với hàm XEM, và cho kết quả tương tự:
Lưu ý rằng công thức INDEX + MATCH được đặt trong dấu ngoặc nhọn là một công thức mảng và bạn phải hoàn thành nó bằng cách nhấn Ctrl + Shift + Enter.
Tại sao INDEX+MATCH là giải pháp tốt nhất cho tìm kiếm phân biệt chữ hoa chữ thường?
Ưu điểm chính của gói INDEX и THÊM ĐƯỢC TIẾP XÚC:
- Không yêu cầu thêm cột phụ, không giống như VPR.
- Không yêu cầu phải sắp xếp cột tìm kiếm, không giống như XEM.
- Hoạt động với tất cả các loại dữ liệu – số, văn bản và ngày tháng.
Công thức này có vẻ hoàn hảo phải không? Trên thực tế thì không phải vậy. Và đó là lý do tại sao.
Giả sử rằng ô trong cột giá trị trả về được liên kết với giá trị tra cứu trống. Công thức sẽ trả về kết quả gì? KHÔNG? Hãy xem công thức thực sự trả về kết quả gì:
Rất tiếc, công thức trả về số 0! Đây có thể không phải là vấn đề lớn nếu bạn đang làm việc với các giá trị văn bản thuần túy. Tuy nhiên, nếu bảng chứa các số, bao gồm cả số 0 “thực”, thì đây sẽ là một vấn đề.
Trên thực tế, tất cả các công thức tra cứu khác (VLOOKUP, LOOKUP và SUMPRODVEL) mà chúng ta đã thảo luận trước đó đều hoạt động theo cùng một cách. Nhưng bạn muốn công thức hoàn hảo, phải không?
Để làm cho công thức phân biệt chữ hoa chữ thường INDEX + MATCH hoàn hảo, đặt nó vào một chức năng IF (NẾU) sẽ kiểm tra một ô có giá trị trả về và trả về kết quả trống nếu nó trống:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Trong công thức này:
- B là một cột có giá trị trả về
- 1+ là số biến vị trí tương đối của ô được hàm trả về THÊM ĐƯỢC TIẾP XÚC, đến địa chỉ thực của ô. Ví dụ, trong chức năng của chúng tôi THÊM ĐƯỢC TIẾP XÚC mảng tìm kiếm đã cho A2: A7, nghĩa là vị trí tương đối của ô A2 sẽ 1, vì đây là cái đầu tiên trong mảng. Nhưng vị trí thực sự của ô A2 trong cột là 2, vì vậy chúng tôi thêm 1để tạo nên sự khác biệt và có chức năng GIÁN TIẾP (INDIRECT) lấy giá trị từ ô mong muốn.
Các hình ảnh dưới đây hiển thị công thức phân biệt chữ hoa chữ thường đã được sửa INDEX + MATCH Trong hành động. Nó trả về kết quả trống nếu ô trả về trống.
Tôi viết lại công thức thành cột B:Dđể vừa với thanh công thức trên ảnh chụp màn hình.
Công thức trả về 0nếu ô trả về chứa số 0.
Nếu bạn muốn liên kết INDEX и THÊM ĐƯỢC TIẾP XÚC hiển thị một số thông báo khi giá trị trả về trống, bạn có thể viết nó trong dấu ngoặc kép cuối cùng (“”) của công thức, ví dụ như sau:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
