PENTING: Pengadopsi Awal Perlu Menyemak Formula XLOOKUPnya - Berita

Perubahan menarik berlaku pada fungsi XLOOKUP dalam kemas kini Office Insiders yang keluar pada 1 November 2019. Banyak Orang Dalam akan menerima kemas kini ini ketika mereka tiba untuk bekerja pada hari Isnin 4 November 2019.

Sekiranya anda telah menggunakan fungsi XLOOKUP baru dan jika anda telah menggunakan argumen Match_Mode untuk mencari nilainya yang lebih besar atau lebih kecil, fungsi XLOOKUP yang ada akan rosak.

Perubahan baru ke XLOOKUP: argumen If_Not_Found, yang awalnya ditambahkan sebagai argumen keenam pilihan, telah dipindahkan menjadi argumen keempat.

Pertimbangkan formula berikut, yang sebelumnya meminta pertandingan yang lebih besar berikutnya:

=XLOOKUP(A2,H2:H99,J2:J99,1)

Apabila anda membuka buku kerja dengan formula seperti ini, formula itu tidak akan rosak. Pengiraan semula pintar Excel tidak akan mengira semula formula sehingga anda mengedit formula, atau sehingga anda mengedit salah satu nombor dalam H2: H99 atau J2: J99.

Namun, setelah anda mengedit jadual pencarian, maka Excel mengira semula semua fungsi XLOOKUP yang menggunakan jadual tersebut. Sebelum perubahan, anda meminta Pertandingan Kira-kira yang mengembalikan nilai yang lebih besar berikutnya. Selepas perubahan itu, anda meminta Pertandingan Tepat (kerana formula asal anda tidak mempunyai argumen kelima) dan juga secara tidak sengaja menyatakan bahawa jika pencocokan tepat tidak dijumpai, maka anda ingin memasukkan hasil 1 sebagai gantinya.

"Ini benar-benar permainan yang menipu," kata Bill Jelen, penerbit.com. Anda menekan F2 untuk melihat formula, dan formula berhenti berfungsi. Rumus lain dalam lembaran kerja mungkin kelihatan terus berfungsi, tetapi itu adalah bom waktu yang menunggu untuk menjadi salah ketika penentuan semula dipicu. "

Untuk melihat perubahan yang berlaku, tonton dari tanda 0:35 hingga 0:55 saat dalam video ini:

Tonton Video

Semasa anda mendaftar untuk program Office Insiders, perenggan 7c dari Syarat dan Ketentuan mengatakan bahawa "Kami mungkin melepaskan Perkhidmatan atau ciri mereka dalam versi pratonton atau versi beta, yang mungkin tidak berfungsi dengan betul atau dengan cara yang sama versi akhir dapat berfungsi . "

Pasukan Excel menasihatkan bahawa anda perlu menyesuaikan formula XLOOKUP yang menggunakan argumen pilihan. Sekiranya anda sering menggunakan XLOOKUP, kod berikut akan memeriksa buku kerja dan mengenal pasti kemungkinan formula masalah.

Versi Asas

Kod berikut mencari sel formula bermula dengan =XLOOKUPdan mengandungi 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 Sub

Versi Regex

Kod berikut menggunakan Regex untuk mencari beberapa fungsi XLOOKUP yang digunakan dalam formula yang sama, atau digunakan dengan fungsi lain mungkin mengandungi koma tambahan.

* Anda perlu menambahkan rujukan Ekspresi Biasa Microsoft VBScript dalam Visual Basic untuk menggunakan kod ini (Alat> Rujukan 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