Perubahan menarik terjadi pada fungsi XLOOKUP di pembaruan Office Insiders yang dirilis pada 1 November 2019. Banyak Insiders akan menerima pembaruan ini saat mereka tiba untuk bekerja pada hari Senin 4 November 2019.
Jika Anda telah menggunakan fungsi XLOOKUP baru dan jika Anda telah menggunakan argumen Match_Mode untuk mencari nilai yang lebih besar atau lebih kecil, fungsi XLOOKUP Anda yang sudah ada akan rusak.
Perubahan baru ke XLOOKUP: argumen If_Not_Found, yang awalnya ditambahkan sebagai argumen keenam opsional, telah dipindahkan menjadi argumen keempat.
Pertimbangkan rumus berikut, yang sebelumnya meminta kecocokan lebih besar berikutnya:
=XLOOKUP(A2,H2:H99,J2:J99,1)
Saat Anda membuka buku kerja dengan rumus seperti ini, rumus tidak langsung rusak. Penghitungan ulang cerdas Excel tidak akan menghitung ulang rumus hingga Anda mengedit rumus, atau hingga Anda mengedit salah satu angka di H2: H99 atau J2: J99.
Namun, setelah Anda mengedit tabel pencarian, Excel akan menghitung ulang semua fungsi XLOOKUP yang menggunakan tabel tersebut. Sebelum perubahan, Anda meminta Kecocokan Perkiraan yang mengembalikan nilai lebih besar berikutnya. Setelah perubahan, Anda meminta Pencocokan Tepat (karena rumus asli Anda tidak memiliki argumen kelima) dan juga secara tidak sengaja menentukan bahwa jika pencocokan tepat tidak ditemukan, Anda ingin memasukkan 1 sebagai hasilnya.
"Ini benar-benar permainan yang sangat berbahaya," kata Bill Jelen, penerbit of.com. Anda menekan F2 untuk melihat rumus, dan rumus berhenti bekerja. Rumus lain di lembar kerja mungkin tampak tetap berfungsi, tetapi itu adalah bom waktu yang menunggu untuk menjadi salah saat pemicu ulang. "
Untuk melihat perubahan yang terjadi, tonton dari tanda detik 0:35 hingga 0:55 di video ini:
Menonton video
Saat Anda mendaftar untuk program Office Insiders, paragraf 7c dari Syarat dan Ketentuan mengatakan bahwa "Kami dapat merilis Layanan atau fiturnya dalam versi pratinjau atau beta, yang mungkin tidak berfungsi dengan benar atau dengan cara yang sama seperti versi final dapat berfungsi . "
Tim Excel menyarankan bahwa Anda perlu menyesuaikan rumus XLOOKUP apa pun yang menggunakan argumen opsional. Jika Anda telah sering menggunakan XLOOKUP, kode berikut akan memeriksa buku kerja dan mengidentifikasi kemungkinan rumus masalah.
Versi Dasar
Kode berikut mencari sel formula yang dimulai dengan =XLOOKUPdan berisi lebih dari 2 koma.
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 SubVersi Regex
Kode berikut menggunakan Regex untuk menemukan beberapa fungsi XLOOKUP yang digunakan dalam rumus yang sama, atau digunakan dengan fungsi lain mungkin berisi koma tambahan.
* Anda perlu menambahkan referensi Ekspresi Reguler Microsoft VBScript di Visual Basic untuk menggunakan kode ini (Alat> Referensi di 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
