Excel 2020: Mười hai lợi ích của XLOOKUP - Mẹo Excel

Hàm XLOOKUP mới sẽ được triển khai cho Office 365 bắt đầu từ tháng 11 năm 2019. Joe McDaid của nhóm Excel đã thiết kế XLOOKUP để thống nhất những người sử dụng hàm VLOOKUP và những người sử dụng INDEX / MATCH. Phần này sẽ thảo luận về 12 lợi ích của XLOOKUP:

  1. Đối sánh chính xác là mặc định.
  2. Đối số thứ ba dựa trên số nguyên của hàm VLOOKUP hiện là một tham chiếu thích hợp.
  3. IFNA được tích hợp sẵn để xử lý các giá trị bị thiếu.
  4. XLOOKUP không có vấn đề gì khi đi sang trái.
  5. Tìm kết quả tiếp theo-nhỏ hơn hoặc tiếp theo-lớn hơn mà không cần sắp xếp bảng.
  6. XLOOKUP có thể làm HLOOKUP.
  7. Tìm trận đấu cuối cùng bằng cách tìm kiếm từ dưới cùng.
  8. Các ký tự đại diện bị "tắt" theo mặc định, nhưng bạn có thể bật lại.
  9. Trả lại tất cả 12 tháng trong một công thức duy nhất.
  10. Có thể trả về tham chiếu ô nếu XLOOKUP bên cạnh dấu hai chấm chẳng hạn như XLOOKUP (); XLOOKUP ()
  11. Có thể thực hiện đối sánh hai chiều như INDEX (, MATCH, MATCH) có thể làm được.
  12. Có thể tổng hợp tất cả các tra cứu trong một công thức duy nhất như LOOKUP có thể làm.

Đây là cú pháp: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

XLOOKUP Lợi ích 1: Kết hợp Chính xác theo Mặc định

99% công thức VLOOKUP của tôi kết thúc bằng FALSE hoặc, 0 để chỉ ra kết quả khớp chính xác. Nếu bạn luôn sử dụng phiên bản đối sánh chính xác của hàm VLOOKUP, bạn có thể bắt đầu tắt chế độ khớp lệnh khỏi hàm XLOOKUP của mình.

Trong hình sau, bạn đang tra cứu W25-6 từ ô A4. Bạn muốn tìm mục đó trong L8: L35. Khi nó được tìm thấy, bạn muốn giá tương ứng từ cột N. Không cần chỉ định False làm match_mode vì XLOOKUP mặc định là giá khớp chính xác.

XLOOKUP giá trị trong A4. Tìm trong L8: L35. Trả lại giá tương ứng từ N8: N35.

XLOOKUP Lợi ích 2: Results_Array là một tham chiếu thay vì một số nguyên

Hãy nghĩ về công thức VLOOKUP mà bạn sẽ sử dụng trước XLOOKUP. Đối số thứ ba sẽ là 3 để chỉ ra rằng bạn muốn trả về cột thứ 3. Luôn có một mối nguy hiểm rằng một đồng nghiệp không biết gì sẽ chèn (hoặc xóa) một cột trong bảng của bạn. Với một cột bổ sung trong bảng, hàm VLOOKUP đã trả về giá sẽ bắt đầu trả về mô tả. Vì XLOOKUP đang trỏ đến tham chiếu ô, công thức sẽ tự viết lại để tiếp tục trỏ đến giá hiện nằm trong cột O.

VLOOKUP cũ sẽ không thành công nếu ai đó chèn một cột mới vào bảng tra cứu. XLOOKUP tiếp tục hoạt động.

XLOOKUP Lợi ích 3: IFNA được tích hợp sẵn dưới dạng đối số tùy chọn

Lỗi # N / A đáng sợ được trả về khi không tìm thấy giá trị tra cứu của bạn trong bảng. Trước đây, để thay thế # N / A bằng một thứ gì đó khác, bạn sẽ phải sử dụng IFERROR hoặc IFNA được bao bọc xung quanh VLOOKUP.

Khi một mục không được tìm thấy, nó sẽ trả về # N / A từ hàm VLOOKUP hoặc XLOOKUP…

Nhờ đề xuất từ ​​Rico trên kênh YouTube của tôi, nhóm Excel đã kết hợp đối số thứ tư tùy chọn cho if_not_found. Nếu bạn muốn thay thế các lỗi # N / A đó bằng 0, chỉ cần thêm 0 làm đối số thứ tư. Hoặc, bạn có thể sử dụng một số văn bản, chẳng hạn như "Không tìm thấy giá trị".

Đối số thứ tư tùy chọn trong XLOOKUP là "nếu không tìm thấy". Đặt một số 0 hoặc "Không tìm thấy" ở đó.

XLOOKUP Lợi ích 4: Không có vấn đề gì khi nhìn sang bên trái của trường khóa

VLOOKUP không thể nhìn sang bên trái của trường khóa mà không sử dụng đến hàm VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Với XLOOKUP, không có vấn đề gì khi có Results_array ở bên trái của Lookup_array.

Với XLOOKUP, không có vấn đề gì khi trả lại Danh mục từ cột F trong khi tra cứu số bộ phận trong cột G. Đây luôn là điểm yếu của VLOOKUP: nó không thể nhìn sang bên trái.

XLOOKUP Lợi ích 5: Đối sánh tiếp theo nhỏ hơn hoặc lớn hơn tiếp theo mà không cần phân loại

VLOOKUP có một tùy chọn để tìm giá trị khớp chính xác hoặc chỉ giá trị nhỏ hơn. Bạn có thể bỏ đối số thứ tư ra khỏi hàm VLOOKUP hoặc thay đổi False thành True. Để điều này hoạt động, bảng tra cứu phải được sắp xếp theo trình tự tăng dần.

Ví dụ về phiên bản Đối sánh gần đúng của hàm VLOOKUP. Bất kỳ doanh số bán hàng nào từ 10 nghìn đến 20 nghìn sẽ được thưởng $ 12.

Nhưng hàm VLOOKUP không có khả năng trả về kết quả khớp chính xác hoặc mục lớn hơn tiếp theo. Vì vậy, bạn phải chuyển sang sử dụng MATCH với -1 làm match_mode và bạn phải cẩn thận rằng bảng tra cứu được sắp xếp giảm dần.

Đối số thứ năm tùy chọn match_mode của XLOOKUP chỉ có thể tìm kiếm đối sánh chính xác, bằng hoặc chỉ nhỏ hơn, bằng hoặc chỉ lớn hơn. Lưu ý rằng các giá trị trong XLOOKUP có ý nghĩa hơn trong MATCH:

  • -1 tìm giá trị bằng hoặc nhỏ hơn
  • 0 tìm một kết hợp chính xác
  • 1 tìm giá trị bằng hoặc chỉ lớn hơn.

Nhưng, phần tuyệt vời nhất: bảng tra cứu không cần phải được sắp xếp và bất kỳ match_mode nào sẽ hoạt động.

Dưới đây, match_mode là -1 tìm mục tiếp theo nhỏ hơn.

Đối số thứ năm của XLOOKUP là Match_Mode. 0 là đối sánh chính xác. Giá trị phủ định được sử dụng cho Đối sánh chính xác hoặc Mục nhỏ hơn tiếp theo. Tích cực 1 là đối sánh chính xác hoặc mục lớn hơn tiếp theo. 2 là đối với Wildcard Match. Để sao chép hàm VLOOKUP với True trong đối số thứ tư, hãy đặt một giá trị phủ định làm đối số match_mode trong XLOOKUP.

Ở đây, match_mode là 1, tìm loại xe nào cần thiết tùy thuộc vào số lượng người trong nhóm. Lưu ý rằng bảng tra cứu không được sắp xếp theo hành khách và tên xe nằm bên trái chìa khóa.

XLOOKUP có thể làm điều gì đó mà VLOOKUP không thể làm: tìm kết quả khớp chính xác hoặc chỉ lớn hơn. Trong trường hợp này, một công ty du lịch có một danh sách đặt chỗ. Dựa trên số lượng hành khách, bảng tra cứu cho biết bạn cần xe gì cho những người đó.

Bảng ghi:

  • Xe buýt chứa 64 người
  • Ô tô chứa được 4 người
  • Xe máy chứa được 1 người
  • Tour Van chứa được 12 người
  • Vân giữ 6 người.

Như một phần thưởng, dữ liệu được sắp xếp theo Xe (trong giải pháp cũ, sử dụng MATCH, bảng sẽ phải được sắp xếp giảm dần theo Công suất. Ngoài ra: Xe nằm bên trái Sức chứa.

XLOOKUP Lợi ích 6: Sideways XLOOKUP thay thế HLOOKUP

Lookup_array và results_array có thể nằm ngang với XLOOKUP, giúp việc thay thế HLOOKUP trở nên đơn giản.

Ở đây bảng tra cứu nằm ngang. Trước đây, điều này sẽ yêu cầu HLOOKUP, nhưng XLOOKUP có thể xử lý một bảng đi ngang.

XLOOKUP Lợi ích 7: Tìm kiếm trận đấu mới nhất từ ​​dưới cùng

Tôi có một video cũ trên YouTube trả lời một câu hỏi từ một trang trại ngựa ở Anh. Họ có một đội xe. Mỗi khi một chiếc xe đến để lấy nhiên liệu hoặc dịch vụ, họ ghi lại chiếc xe, ngày tháng và quãng đường đi trong một bảng tính. Họ muốn tìm quãng đường đã biết mới nhất cho mỗi chiếc xe. Mặc dù MAXIFS thời đại Excel-2017 có thể giải quyết vấn đề này ngày hôm nay, nhưng giải pháp nhiều năm trước là một công thức phức tạp sử dụng LOOKUP và liên quan đến phép chia cho 0.

Ngày nay, đối số thứ sáu tùy chọn của XLOOKUP cho phép bạn chỉ định rằng tìm kiếm phải bắt đầu từ cuối tập dữ liệu.

Tìm trận đấu cuối cùng trong danh sách.

Ghi chú

Mặc dù đây là một cải tiến tuyệt vời, nhưng nó chỉ cho phép bạn tìm thấy kết quả phù hợp đầu tiên hoặc cuối cùng. Một số người hy vọng điều này sẽ cho phép bạn tìm thấy kết quả phù hợp thứ hai hoặc thứ ba, nhưng đó không phải là mục đích của đối số search_mode.

Thận trọng

Hình trên cho thấy có các chế độ tìm kiếm sử dụng tìm kiếm nhị phân cũ. Joe McDaid khuyên không nên sử dụng những thứ này. Đầu tiên, thuật toán tra cứu được cải tiến từ năm 2018 đủ nhanh nên không có lợi ích về tốc độ đáng kể. Thứ hai, bạn có nguy cơ gặp phải một đồng nghiệp thiếu hiểu biết sắp xếp bảng tra cứu và đưa ra các câu trả lời sai.

XLOOKUP Lợi ích 8: Các ký tự đại diện bị "tắt" theo mặc định

Hầu hết mọi người đều không nhận ra rằng hàm VLOOKUP đang coi dấu hoa thị, dấu chấm hỏi và dấu ngã là các ký tự đại diện như được mô tả trong "# 51 Sử dụng ký tự đại diện trong hàm VLOOKUP" trên trang 143. Với XLOOKUP, các ký tự đại diện bị tắt theo mặc định. Nếu bạn muốn XLOOKUP coi các ký tự này như một ký tự đại diện, hãy sử dụng 2 làm Match_Mode.

Rất ít người nhận ra rằng hàm VLOOKUP đang coi dấu hoa thị trong giá trị tra cứu như một ký tự đại diện. Theo mặc định, XLOOKUP không sử dụng ký tự đại diện, nhưng bạn có thể buộc nó hoạt động giống như VLOOKUP nếu bạn sử dụng Chế độ so khớp 2: Đối sánh ký tự đại diện.

Lợi ích XLOOKUP 9: Trả lại tất cả 12 tháng trong một công thức duy nhất!

Đây thực sự là một lợi ích của Mảng Động, nhưng đó là lý do tôi yêu thích XLOOKUP. Khi bạn phải trả lại tất cả 12 tháng trong một lần tra cứu, một công thức duy nhất được nhập vào B6 với một return_array hình chữ nhật sẽ trả về nhiều kết quả. Các kết quả đó sẽ tràn sang các ô liền kề.

Trong hình bên dưới, một công thức duy nhất được nhập vào B7 trả về tất cả 12 câu trả lời được hiển thị trong B7: M7.

Một XLOOKUP duy nhất trong cột tháng 1 trả về các số cho tháng 1 đến tháng 12. Điều này được thực hiện bằng cách chỉ định một mảng kết quả có 12 cột.

XLOOKUP Lợi ích 10: Có thể trả về Tham chiếu ô nếu Liền kề với Colon

Cái này phức tạp nhưng đẹp. Trước đây, có bảy hàm sẽ thay đổi từ trả về giá trị ô thành trả về tham chiếu ô nếu hàm chạm vào dấu hai chấm. Ví dụ, hãy xem Sử dụng A2: INDEX () làm OFFSET không bay hơi. XLOOKUP là hàm tám để cung cấp hành vi này, kết hợp CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET và SWITCH.

Hãy xem xét hình sau. Ai đó chọn Cherry trong E4 và Fig trong E5. Bạn muốn một công thức tổng hợp mọi thứ từ B6 đến B9.

Hình bên cho thấy hai công thức XLOOKUP trong hai ô. Đầu tiên trả về 15 từ ô B6. Lần thứ hai chạy lại 30 từ B9. Nhưng sau đó trong ô thứ ba, có một công thức kết hợp hai công thức XLOOKUP bằng dấu hai chấm và sau đó kết hợp nó trong một hàm SUM. Kết quả là SUM của B6: B9, vì XLOOKUP có thể trả về tham chiếu ô nếu hàm xuất hiện bên cạnh một toán tử chẳng hạn như dấu hai chấm. Để chứng minh rằng điều này đang hoạt động, một số hình tiếp theo sẽ hiển thị công thức này trong hộp thoại Đánh giá công thức.

Trong hình trên, bạn có thể thấy rằng một XLOOKUP của E4 sẽ trả về giá trị 15 từ ô B6. XLOOKUP của E5 sẽ trả về giá trị 30 từ B9. Tuy nhiên, nếu bạn lấy hai hàm XLOOKUP từ các ô D9 và D10 và đặt chúng cùng với dấu hai chấm ở giữa, hành vi của XLOOKUP sẽ thay đổi. Thay vì trả về 15, XLOOKUP đầu tiên trả về địa chỉ ô B6!

Để chứng minh điều này, tôi đã chọn D7 và sử dụng Công thức, Đánh giá Công thức. Sau khi nhấn Đánh giá hai lần, phần tiếp theo được tính là XLOOKUP ("Cherry", A4: A29, B4: B29), như được hiển thị ở đây.

Điều này hiển thị hộp thoại Đánh giá Công thức ngay trước khi đánh giá XLOOKUP đầu tiên. XLOOKUP này xuất hiện ngay trước dấu hai chấm.

Nhấn Đánh giá một lần nữa và đáng kinh ngạc, công thức XLOOKUP trả về $ B $ 6 thay vì 15 được lưu trữ trong B6. Điều này xảy ra vì có dấu hai chấm ngay sau công thức XLOOKUP này.

Nhấp vào Đánh giá và XLOOKUP đầu tiên trả về $ B $ 6 thay vì 15.

Nhấn Đánh giá hai lần nữa và công thức tạm thời sẽ là = SUM (B6: B9).

Sau khi đánh giá XLOOKUP thứ hai, công thức tạm thời là = SUM (B6: B9).

Đây là hành vi đáng kinh ngạc mà hầu hết mọi người không biết. Excel MVP Charles Williams cho tôi biết rằng nó có thể được kích hoạt bằng bất kỳ toán tử nào trong ba toán tử này bên cạnh XLOOKUP:

  • Đại tràng
  • Dấu cách (Toán tử giao lộ)
  • Dấu phẩy (toán tử Union)

XLOOKUP Lợi ích 11: Kết hợp hai chiều như INDEX (, TRẬN ĐẤU, TRẬN ĐẤU)

Đối với tất cả những người bạn VLOOKUP của tôi, những người INDEX / MATCH đã chờ xem liệu XLOOKUP có thể xử lý một trận đấu hai chiều hay không. Tin tuyệt vời: nó có thể làm được. Tin xấu: phương pháp này hơi khác so với INDEX / MATCH mà người hâm mộ mong đợi. Nó có thể là một chút qua đầu của họ. Nhưng tôi chắc chắn rằng họ có thể sử dụng phương pháp này.

Đối với đối sánh hai chiều, bạn muốn tìm hàng nào chứa số tài khoản A621 được hiển thị trong J3. Vì vậy, XLOOKUP bắt đầu khá dễ dàng: = XLOOKUP (J3, A5: A15. Nhưng sau đó bạn phải cung cấp results_array. Bạn có thể sử dụng thủ thuật tương tự như trong XLOOKUP Lợi ích 9: Trả lại tất cả 12 tháng trong một công thức ở trên, nhưng sử dụng nó để trả về một vectơ dọc. XLOOKUP bên trong tìm kiếm tháng J4 trong các tiêu đề tháng trong B4: G4. Mảng return_array được chỉ định là B5: G15. Kết quả là XLOOKUP bên trong trả về một mảng giống như mảng được hiển thị trong I10 : I20 dưới đây. Vì A621 được tìm thấy trong ô thứ năm của lookup_array và 104 được tìm thấy trong ô thứ năm của results_array, bạn sẽ có câu trả lời chính xác từ công thức. Dưới đây, J6 hiển thị theo cách cũ. J7 trả về cách mới.

XLookup J3 trong danh sách các tài khoản ở A5: A15. Đối với Mảng kết quả, hãy sử dụng XLOOKUP (J4, B4: G4, B5: G15). Trong công thức này, B4: G4 là danh sách các tháng. B5: G15 là mảng giá trị hình chữ nhật cho tất cả các tài khoản cho tất cả các tháng. Trong một ô khác, chỉ XLOOKUP bên trong hiển thị cách nó trả về toàn bộ cột giá trị cho tháng Năm.

XLOOKUP Lợi ích 12: Tính tổng tất cả các giá trị tra cứu trong một công thức duy nhất

Hàm LOOKUP cổ xưa đưa ra hai thủ thuật kỳ lạ. Đầu tiên, nếu bạn đang cố gắng tìm ra tổng chi phí tiền thưởng để tích lũy, bạn có thể yêu cầu LOOKUP tra cứu tất cả các giá trị trong một công thức duy nhất. Trong hình ảnh bên dưới, LOOKUP (C4: C14 đang thực hiện 11 lần tra cứu. Nhưng hàm LOOKUP không cung cấp kết quả khớp chính xác và yêu cầu bảng tra cứu phải được sắp xếp.

Tra cứu 13 giá trị và tính tổng chúng. Điều này được sử dụng để làm việc với LOOKUP, nhưng nó cũng hoạt động với XLOOKUP. Chỉ định tất cả các giá trị tra cứu C4: C14 làm đối số đầu tiên. Bao bọc XLOOKUP trong một hàm SUM.

Với XLOOKUP, bạn có thể chỉ định một phạm vi vì lookup_value và XLOOKUP sẽ trả về tất cả các câu trả lời. Lợi ích là XLOOKUP có thể khớp chính xác.

Thủ thuật sử dụng LOOKUP để tổng hợp tất cả các kết quả tra cứu chỉ hoạt động với phiên bản đối sánh gần đúng của Lookup. Ở đây, XLOOKUP đang thực hiện đối sánh chính xác tất cả các tên trong L4: L14 và nhận được tổng số tất cả các kết quả.

Mẹo bổ sung: Còn một LOOKUP Xoắn thì sao?

Excel MVP Mike Girvin thường cho thấy một mẹo của hàm LOOKUP trong đó Lookup_Vector là chiều dọc và Result_Vector là chiều ngang. XLOOKUP sẽ không hỗ trợ thủ thuật này. Tuy nhiên, nếu bạn gian lận một chút và bọc results_array trong hàm TRANSPOSE, bạn có thể quản lý một tra cứu xoắn.

Ở đây mảng tra cứu là theo chiều dọc và mảng kết quả là theo chiều ngang. Hàm LOOKUP cũ có thể xử lý điều này, nhưng để làm điều đó với XLOOKUP, bạn phải bọc một trong hai mảng trong TRANSPOSE.

thú vị bài viết...