Một thay đổi thú vị đã xảy ra với chức năng XLOOKUP trong bản cập nhật Người dùng nội bộ Office ra mắt vào ngày 1 tháng 11 năm 2019. Nhiều Người dùng nội bộ sẽ nhận được bản cập nhật này khi họ đến làm việc vào Thứ Hai ngày 4 tháng 11 năm 2019.
Nếu bạn đang sử dụng hàm XLOOKUP mới và nếu bạn đã sử dụng đối số Match_Mode để tìm kiếm giá trị lớn hơn hoặc chỉ nhỏ hơn, thì các hàm XLOOKUP hiện có của bạn sẽ bị hỏng.
Thay đổi mới đối với XLOOKUP: đối số If_Not_Found, ban đầu được thêm vào làm đối số thứ sáu tùy chọn, đã được chuyển thành đối số thứ tư.
Hãy xem xét công thức sau, công thức trước đây yêu cầu kết quả phù hợp lớn hơn tiếp theo:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Khi bạn mở một sổ làm việc có công thức như thế này, công thức không bị ngắt ngay lập tức. Recalc thông minh của Excel sẽ không tính lại công thức cho đến khi bạn chỉnh sửa công thức hoặc cho đến khi bạn chỉnh sửa một trong các số trong H2: H99 hoặc J2: J99.
Tuy nhiên, khi bạn chỉnh sửa bảng tra cứu, thì Excel sẽ tính toán lại tất cả các hàm XLOOKUP đã sử dụng bảng. Trước khi thay đổi, bạn đã yêu cầu Đối sánh gần đúng trả về giá trị lớn hơn tiếp theo. Sau khi thay đổi, bạn đang yêu cầu Đối sánh chính xác (vì công thức ban đầu của bạn không có đối số thứ năm) và cũng vô tình chỉ định rằng nếu không tìm thấy đối sánh chính xác, thì bạn muốn chèn 1 làm kết quả.
Bill Jelen, nhà xuất bản của.com cho biết: “Đó thực sự là một trò chơi quỷ quyệt. Bạn nhấn F2 để xem một công thức và công thức đó ngừng hoạt động. Các công thức khác trong trang tính có vẻ vẫn tiếp tục hoạt động, nhưng chúng là một quả bom hẹn giờ tích cực chờ trở thành sai khi một recalc được kích hoạt. "
Để xem sự thay đổi đang diễn ra, hãy xem từ mốc 0:35 đến 0:55 giây trong video này:
Xem video
Khi bạn đăng ký chương trình Người dùng nội bộ Office, đoạn 7c của Điều khoản và Điều kiện nói rằng "Chúng tôi có thể phát hành Dịch vụ hoặc các tính năng của chúng trong phiên bản xem trước hoặc phiên bản beta, phiên bản này có thể không hoạt động chính xác hoặc theo cách mà phiên bản cuối cùng có thể hoạt động . "
Nhóm Excel khuyên rằng bạn cần điều chỉnh bất kỳ công thức XLOOKUP nào đang sử dụng các đối số tùy chọn. Nếu bạn thường xuyên sử dụng XLOOKUP, đoạn mã sau sẽ kiểm tra sổ làm việc và xác định các công thức vấn đề có thể xảy ra.
Phiên bản cơ bản
Đoạn mã sau tìm kiếm các ô công thức bắt đầu bằng =XLOOKUPvà chứa nhiều hơn 2 dấu phẩy.
Sub findXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim foundCells As String Set sht = ActiveSheet For Each cll In sht.UsedRange If cll.HasFormula Then If InStr(cll.Formula, "=XLOOKUP") = 1 Then If UBound(Split(cll.Formula, ","))> 2 Then foundCells = foundCells & vbCrLf & cll.Address End If End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End SubPhiên bản Regex
Mã sau đang sử dụng Regex để tìm nhiều hàm XLOOKUP được sử dụng trong cùng một công thức hoặc được sử dụng với các hàm khác có thể chứa thêm dấu phẩy.
* Bạn cần thêm tham chiếu Biểu thức chính quy Microsoft VBScript trong Visual Basic để sử dụng mã này (Công cụ> Tham chiếu trong VBA).
Sub advancedFindXLOOKUPs() Dim sht As Worksheet Dim cll As Range Dim rgx As RegExp Dim rMatches As Object Dim rMatch As Object Dim foundCells As String Set sht = ActiveSheet Set rgx = New RegExp With rgx .Pattern = "XLOOKUP(((^,))*,)(3,)(^,)*)" .MultiLine = False .IgnoreCase = True .Global = True End With For Each cll In sht.UsedRange If cll.HasFormula Then Set rMatches = rgx.Execute(cll.Formula) If rMatches.Count Then For Each rMatch In rMatches 'Debug.Print rMatch foundCells = foundCells & vbCrLf & cll.Address Next rMatch End If End If Next cll If foundCells = "" Then MsgBox sht.Name & " is not affected with XLOOKUP function structure update.", vbOKOnly + vbInformation, "No errors" Else MsgBox sht.Name & " is likely affected with XLOOKUP function structure update. Please check formulas in the following cells:" & foundCells, vbOKOnly + vbExclamation, "Error(s) found" End If End Sub
