HotXLS, komponen spreadsheet native Delphi dan C++Builder, mengevaluasi XLOOKUP dan XMATCH lewat satu inti lookup bersama. Inti itu menerima empat mode match (-1, 0, 1, 2) dan empat mode search (-2, -1, 1, 2), menjalankan descent binary logaritmik kapan pun mode search absolutnya 2, dan menolak setiap kombinasi lain dengan error formula
Laporan bug yang mengirim Anda ke sini tidak pernah bilang "search mode". Ia bilang workbook hasil generate server menunjukkan angka berbeda dari file yang sama dibuka di Excel, di mungkin empat baris dari sembilan ribu. Empat baris itu selalu punya kesamaan: key lookup yang duplikat, atau kecocokan aproksimasi yang harus memilih tetangga, atau kolom lookup yang seseorang urutkan berdasarkan kolom berbeda minggu lalu. Fungsi lookup adalah tempat mesin formula berhenti menjadi aritmatika dan mulai menjadi kontrak, dan kontrak itu punya klausul yang jarang dibaca pemanggilnya
Angka mode mana yang sebenarnya diterima XLOOKUP?
Tepat empat masing-masing, dan tidak ada yang lain. HotXLS memvalidasi match_mode terhadap -1, 0, 1 dan 2 dan search_mode terhadap -2, -1, 1 dan 2 sebelum menyentuh satu sel pun, dan nilai lain apa pun mengembalikan #VALUE! alih-alih dijepit ke mode legal terdekat. Empat mode match adalah 0 untuk exact, -1 untuk exact atau lebih kecil berikutnya, 1 untuk exact atau lebih besar berikutnya, dan 2 untuk wildcard; empat mode search adalah 1 untuk pemindaian linear maju, -1 untuk pemindaian linear mundur, 2 untuk binary search atas data menaik, dan -2 untuk binary search atas data menurun. Mengabaikannya memilih mode match 0 dan mode search 1, pasangan yang dipakai hampir semua formula sungguhan. Jumlah argumen dipolisikan dengan cara yang sama: XLOOKUP menerima tiga hingga enam argumen dan XMATCH menerima dua hingga empat, dan apa pun di luar rentang itu adalah #VALUE! sebelum evaluasi dimulai
// Shared by XLOOKUP and XMATCH, before any cell is read
if ((RequestedMatchMode <> -1) and (RequestedMatchMode <> 0) and
(RequestedMatchMode <> 1) and (RequestedMatchMode <> 2)) or
((RequestedSearchMode <> -2) and (RequestedSearchMode <> -1) and
(RequestedSearchMode <> 1) and (RequestedSearchMode <> 2)) then
begin
Result := lxErrorValue; // #VALUE!
Exit;
end;
if Abs(RequestedSearchMode) = 2 then
begin
if RequestedMatchMode = 2 then // wildcards cannot ride a binary descent
begin
Result := lxErrorValue;
Exit;
end;
// ... O(log n) descent over the lookup vector
end;Satu langkah lebih awal ada pemeriksaan lebih diam-diam yang layak diketahui. Argumen mode datang sebagai ekspresi worksheet, jadi HotXLS meng-koersi ke angka, menolak NaN dan infinity, lalu menuntut angka itu sama dengan hasil pembulatannya sendiri. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) adalah #VALUE!, bukan search mode 2 dalam penyamaran. Ini penting saat mode-nya berasal dari sel yang dihasilkan kalkulasi berat-pembulatan, yang lebih umum di workbook hasil generate dibanding yang dibuat tangan
Kenapa search_mode 2 memberi jawaban salah pada data tak terurut?
Karena ia sedang melakukan persis apa yang Anda minta. Search mode 2 memberi tahu mesin bahwa vektor lookup sudah terurut menaik, dan binary search tidak bisa memverifikasi klaim itu tanpa pass O(n) yang akan menghancurkan alasan memakainya. HotXLS karena itu mempercayai pemanggil, membagi dua interval, dan mengembalikan apa pun yang didarati descent tersebut. Pada input tak terurut, jawabannya bukan error, ia diam-diam salah, dan ini adalah pelanggaran kontrak, bukan cacat di mesinnya
Microsoft mendokumentasikan asimetri yang sama untuk XLOOKUP dan XMATCH: mode binary membutuhkan data terurut dan menghasilkan hasil tidak valid jika tidak. ISO 29500-1 klausul 18.17, yang mendefinisikan grammar formula SpreadsheetML, membawa deskripsi LOOKUP dan VLOOKUP yang lebih lama dengan syarat urutan menaiknya sendiri, dan XLOOKUP serta XMATCH muncul cukup lama setelah teks itu sehingga mereka melintas dalam file sebagai _xlfn.XLOOKUP dan _xlfn.XMATCH di bawah konvensi future-function. Generasi berbeda, kesepakatan sama: pemanggil memasok invarian urutan, mesin memasok logaritmanya
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Rates');
Sheet.Cells[1, 1].Value := 40; Sheet.Cells[1, 2].Value := 0.10;
Sheet.Cells[2, 1].Value := 10; Sheet.Cells[2, 2].Value := 0.25;
Sheet.Cells[3, 1].Value := 30; Sheet.Cells[3, 2].Value := 0.15;
// Forward linear scan: finds key 40 wherever it sits
Sheet.Cells[5, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,1)';
// Binary ascending: the promise was broken, the key is never visited
Sheet.Cells[6, 1].Formula := 'XLOOKUP(40,A1:A3,B1:B3,"missing",0,2)';
Book.SaveAs('lookup-modes.xlsx');
finally
Book.Free;
end;
end;Telusuri formula kedua dan kegagalannya sepenuhnya mekanis. Descent-nya memeriksa sel tengah, membaca 10, memutuskan 10 lebih kecil dari 40, membuang separuh kiri termasuk baris yang sebenarnya berisi 40, memeriksa 30, membuang lagi, dan kehabisan interval. Excel berperilaku sama, dan itulah intinya: mereproduksi jawaban yang salah adalah persyaratan kompatibilitas, bukan kesopanan. Premis urutannya juga lebih ketat dari sekadar "angka menaik", karena komparatornya mengurutkan nilai berdasarkan jenis lebih dulu, dalam urutan angka, lalu teks, lalu boolean, lalu nilai error, lalu blank, dan baru membandingkan di dalam satu jenis setelah itu. Kolom kode bagian numerik yang punya tiga sel menyimpan teks alih-alih angka tidak menaik di bawah komparator itu tidak peduli bagaimana tampilannya di layar, dan mode binary akan dengan senang hati salah membacanya
Ke mana key duplikat mendarat?
Pada ujung deterministik dari rangkaian duplikat, dan ujung mana yang dipilih bergantung pada mode search, bukan pada keberuntungan. Saat descent binary mengenai key yang sama di bawah search mode 2, ia mencatat posisinya lalu terus menyempit ke kiri, jadi hasilnya adalah indeks terendah dari rangkaian itu; di bawah search mode -2, atas data menurun, ia mencatat posisinya dan menyempit ke kanan, jadi hasilnya adalah indeks tertinggi. Mode linear lebih sederhana: search mode 1 mengembalikan kecocokan pertama maju, search mode -1 kecocokan pertama mundur. Inilah detail yang menghasilkan perbedaan empat-baris dari paragraf pembuka, karena workbook yang key-nya unik memberikan jawaban identik di bawah keempat mode search dan menyembunyikan perbedaannya lewat setiap test yang Anda tulis dari file sampel bersih. Tambahkan satu kode pelanggan duplikat ke data produksi dan mode-mode itu mulai tidak sepakat persis pada baris yang terduplikasi: tidak ada yang berubah di mesinnya, inputnya hanya berhenti menjadi set dan menjadi multiset
// A1:A7 holds 1, 3, 5, 5, 5, 7, 9 - ascending, with a run of three
Sheet.Cells[1, 3].Formula := 'XMATCH(5,A1:A7,0,1)'; // 3, first forward hit
Sheet.Cells[2, 3].Formula := 'XMATCH(5,A1:A7,0,-1)'; // 5, first reverse hit
Sheet.Cells[3, 3].Formula := 'XMATCH(5,A1:A7,0,2)'; // 3, lowest index of the run
// B1:B7 holds 9, 7, 5, 5, 5, 3, 1 - descending
Sheet.Cells[4, 3].Formula := 'XMATCH(5,B1:B7,0,-2)'; // 5, highest index of the runBagaimana kecocokan aproksimasi memilih runner-up?
Dengan mempertahankan kandidat terbaik bersamaan dengan pencarian kecocokan-exact, dan hanya mengembalikannya jika tidak ada kecocokan exact yang muncul. HotXLS memperlakukan match_mode -1 sebagai "nilai terbesar yang tidak lebih besar dari target" dan match_mode 1 sebagai "nilai terkecil yang tidak lebih kecil", dan keduanya diselesaikan atas seluruh region yang dipindai alih-alih berhenti pada tetangga yang bisa diterima pertama. Di jalur binary, ide yang sama muncul gratis dari descent-nya: setiap langkah yang melampaui atau kurang dari target memperbarui kandidat, jadi kandidat akhirnya adalah elemen batas di sebelah posisi tempat key seharusnya disisipkan
// Linear path: refine the candidate only on a strict improvement
if (RequestedMatchMode = -1) or (RequestedMatchMode = 1) then
begin
CompareResult := CompareDynamicValues(CurrentValue, RequestedValue);
if ((RequestedMatchMode = -1) and (CompareResult <= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) > 0))) or
((RequestedMatchMode = 1) and (CompareResult >= 0) and
((CandidateIndex < 0) or
(CompareDynamicValues(CurrentValue, CandidateValue) < 0))) then
begin
CandidateIndex := ScanIndex;
CandidateValue := CurrentValue;
end;
end;Baca kondisi bagian dalam itu dengan saksama, karena tie-break ada di sana. Sel baru menggantikan kandidat yang berdiri hanya saat ia secara ketat lebih baik, tidak pernah saat sekadar setara, jadi di antara beberapa sel yang memegang nilai runner-up yang sama, yang dipertahankan adalah yang pertama ditemui dalam urutan pemindaian: indeks terendah di bawah pemindaian maju, tertinggi di bawah pemindaian mundur. Jika XLOOKUP dan XMATCH tidak menemukan kecocokan exact atau tetangga yang bisa diterima, XLOOKUP jatuh kembali ke argumen if_not_found-nya saat disediakan dan ke #N/A saat tidak, sementara XMATCH selalu menghasilkan #N/A
Kenapa wildcard dan binary search tidak bisa berdampingan
Karena pola wildcard bukan posisi dalam sebuah urutan. Match mode 2 menanyakan apakah sebuah sel cocok dengan mask, dan pencocokan mask menjawab ya atau tidak; descent binary membutuhkan jawaban tiga arah yang memberi tahunya separuh mana yang harus dipertahankan. Tidak ada cara yang bisa dipertanggungjawabkan untuk menanyakan apakah ACME-* berada di kiri atau kanan sel tertentu, jadi HotXLS menolak match_mode 2 dikombinasikan dengan search_mode 2 atau -2 sejak awal dengan #VALUE! alih-alih menebak sebuah urutan dan menghasilkan omong kosong yang terlihat masuk akal. Kedua jalur juga membandingkan nilai secara berbeda, yang memperkuat pemisahan itu: pemindaian linear memutuskan kesetaraan dengan perbandingan teks tidak peka huruf besar/kecil, atau dengan pencocokan mask saat wildcard aktif, sementara descent binary memutuskan kesetaraan dengan menanyakan komparator urutan untuk nol. Itu disengaja, bukan kebetulan dari layering, karena jalur binary hanya boleh memakai relasi yang sedang benar-benar dinavigasinya. Jika Anda butuh wildcard, pakai search mode 1 atau -1 dan terima biaya linear, yang merupakan trade-off yang sama dengan yang dijaga pelacakan dependensi di balik rekalkulasi incremental agar tidak masuk jalur kritis Anda
Error bentuk: range dua-dimensi dan vektor return yang tidak cocok
Kedua fungsi membutuhkan range lookup yang benar-benar satu-dimensi. Jika range yang disediakan membentang lebih dari satu baris dan lebih dari satu kolom sekaligus, HotXLS mengembalikan #VALUE! alih-alih memilih axis atas nama Anda, dan range satu-baris atau satu-kolom dibaca sepanjang axis panjangnya. XLOOKUP menambahkan aturan bentuk kedua: range return harus persis sepanjang range lookup sepanjang axis yang cocok, jadi lookup vertikal atas 500 baris dipasangkan dengan range return 499-baris adalah error, bukan selisih-satu yang diam-diam diselesaikan pada baris terakhir. Saat range return lebih lebar dari satu kolom untuk lookup vertikal, atau lebih tinggi dari satu baris untuk yang horizontal, XLOOKUP mengembalikan seluruh irisan yang cocok sebagai array dan itu tumpah (spill) ke sel tetangga di bawah aturan yang sama dengan fungsi array dinamis lainnya, dijelaskan di artikel tentang spill range dan array dinamis. Itu sungguh berguna untuk menarik seluruh record dari tabel dengan satu formula, dan itu juga cara tercepat untuk menimpa kolom yang seharusnya Anda pertahankan
Memilih mode saat tidak ada yang mengawasi layar
Generasi sisi-server layak mendapat kebijakan yang lebih ketat daripada penggunaan interaktif, karena tidak ada manusia yang menyadari bahwa sebuah total terlihat salah. Default yang bisa dipertanggungjawabkan adalah search mode 1 dengan match mode 0: linear, exact, tidak bergantung urutan, dan mustahil dibatalkan dengan mengurutkan ulang sheet. Raih search mode 2 hanya saat jalur kode yang sama juga menghasilkan urutan itu, dalam jalan yang sama, atas kolom yang sama, dan tulis dependensi itu di samping formulanya, karena binary search pada kolom yang diurutkan berdasarkan key berbeda adalah cara termurah untuk menghitung angka salah yang percaya diri. Ketika lookup-nya sungguh sering dipakai dan datanya sungguh terurut, hasilnya nyata: descent-nya membaca kira-kira log n sel alih-alih n, dan setiap pembacaan itu melewati resolusi sel workbook penuh, jadi penghematannya lebih besar dari yang disarankan jumlah instruksinya
Jika bentuk masalahnya lebih dekat ke aturan domain daripada lookup, callback ke kode Pascal Anda sendiri, sebagaimana dibahas di artikel tentang fungsi worksheet kustom, biasanya mengalahkan susunan cerdas apa pun dari fungsi bawaan. Implementasi XLOOKUP dan XMATCH yang dibahas di sini hadir dalam komponen spreadsheet Delphi HotXLS standar, yang halaman produknya membawa referensi fungsi terdukung lengkap untuk Delphi dan C++Builder