HotXLS, natyvus Delphi ir C++Builder skaičiuoklės komponentas, vertina XLOOKUP ir XMATCH per vieną bendrą paieškos šerdį. Ši šerdis priima keturis atitikimo režimus (-1, 0, 1, 2) ir keturis paieškos režimus (-2, -1, 1, 2), vykdo logaritminį dvejetainį nusileidimą, kai absoliuti paieškos režimo reikšmė lygi 2, ir atmeta bet kokį kitą derinį su formulės klaida
Klaidos pranešimas, atvedantis jus čia, niekada nesako „paieškos režimas“. Jis sako, kad serverio sugeneruota darbaknygė rodo kitą skaičių nei tas pats failas, atvertas Excel, gal keturiose eilutėse iš devynių tūkstančių. Tos keturios eilutės visada turi kažką bendro: dubliuotą paieškos raktą, apytikslį atitikimą, kuriam teko pasirinkti kaimyną, ar paieškos stulpelį, kurį kažkas praeitą savaitę surikiavo pagal kitą stulpelį. Paieškos funkcijos — vieta, kur formulių variklis nustoja būti aritmetika ir tampa kontraktu, o kontraktas turi sąlygas, kurių dauguma iškviečiančiųjų niekada neskaito
Kuriuos režimo skaičius XLOOKUP iš tikrųjų priima?
Lygiai keturis iš kiekvieno, ir nieko daugiau. HotXLS patikrina match_mode pagal -1, 0, 1 ir 2, o search_mode pagal -2, -1, 1 ir 2, prieš paliesdama nė vienos ląstelės, ir bet kokia kita reikšmė grąžina #VALUE!, o ne apkarpoma iki artimiausio leistino režimo. Keturi atitikimo režimai — 0 tikslus, -1 tikslus arba kitas mažesnis, 1 tikslus arba kitas didesnis, o 2 pakaitos simboliams; keturi paieškos režimai — 1 tiesioginiam linijiniam skenavimui, -1 atgaliniam linijiniam skenavimui, 2 dvejetainei paieškai per didėjančius duomenis, -2 dvejetainei paieškai per mažėjančius duomenis. Jų praleidimas parenka atitikimo režimą 0 ir paieškos režimą 1, tą porą, kurią naudoja beveik kiekviena reali formulė. Argumentų skaičius tikrinamas taip pat: XLOOKUP ima nuo trijų iki šešių argumentų, o XMATCH — nuo dviejų iki keturių, ir bet kas už šių ribų — #VALUE! dar prieš prasidedant vertinimui
// 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;Vienu žingsniu anksčiau yra tylesnė patikra, kurią verta žinoti. Režimo argumentai atkeliauja kaip darbalapio išraiškos, todėl HotXLS juos paverčia skaičiumi, atmeta NaN ir begalybę, ir tada reikalauja, kad skaičius būtų lygus savo apvalintam variantui. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) yra #VALUE!, ne paieškos režimas 2 užmaskuotas. Tai svarbu, kai režimas ateina iš ląstelės, kurią sukūrė apvalinimų gausus skaičiavimas, o tai — dažniau sugeneruotose darbaknygėse nei rankomis rašytose
Kodėl search_mode 2 duoda neteisingą atsakymą nesurikiuotuose duomenyse?
Todėl, kad jis atlieka lygiai tai, ko paprašėte. Paieškos režimas 2 sako varikliui, kad paieškos vektorius jau surikiuotas didėjančia tvarka, o dvejetainė paieška negali patikrinti šio tvirtinimo be O(n) ėjimo, kuris sunaikintų priežastį, kodėl ją naudoti. HotXLS todėl pasitiki iškviečiančiuoju, padalija intervalą pusiau ir grąžina tai, kur nusileidžia nusileidimas. Nesurikiuotoje įvestyje atsakymas — ne klaida, jis tyliai neteisingas, ir tai — kontrakto pažeidimas, ne variklio defektas
Microsoft dokumentuoja tą pačią asimetriją XLOOKUP ir XMATCH: dvejetainiai režimai reikalauja surikiuotų duomenų ir kitaip duoda negaliojančius rezultatus. ISO 29500-1 18.17 punktas, apibrėžiantis SpreadsheetML formulių gramatiką, neša senesnius LOOKUP ir VLOOKUP aprašymus su savo pačių didėjančios tvarkos reikalavimu, o XLOOKUP ir XMATCH atsirado gerokai vėliau, todėl faile keliauja kaip _xlfn.XLOOKUP ir _xlfn.XMATCH pagal būsimų funkcijų konvenciją. Skirtinga karta, tas pats sandoris: iškviečiantysis pateikia tvarkos invariantą, variklis pateikia logaritmą
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;Atsekite antrąją formulę, ir gedimas — visiškai mechaninis. Nusileidimas patikrina vidurinę ląstelę, perskaito 10, nusprendžia, kad 10 mažesnis nei 40, atmeta kairę pusę, įskaitant eilutę, kuri iš tikrųjų turėjo 40, patikrina 30, atmeta vėl ir baigia intervalą. Excel elgiasi taip pat, ir tai — esmė: neteisingo atsakymo atkartojimas — suderinamumo reikalavimas, ne mandagumas. Tvarkos prielaida taip pat griežtesnė nei „skaičiai didėjantys“, nes komparatorius pirmiausia rikiuoja reikšmes pagal tipą, tvarka: skaičiai, tada tekstas, tada loginės reikšmės, tada klaidos reikšmės, tada tuščios, ir tik po to lygina tipo viduje. Skaitinių dalies kodų stulpelis, kuriame trys ląstelės saugo tekstą vietoj skaičių, nėra didėjantis pagal šį komparatorių, nesvarbu, kaip jis atrodo ekrane, o dvejetainiai režimai mielai jį neteisingai perskaitys
Kur atsiduria dubliuoti raktai?
Determinuotame dublikato serijos gale, ir kuris galas priklauso nuo paieškos režimo, ne nuo sėkmės. Kai dvejetainis nusileidimas pasiekia lygų raktą su paieškos režimu 2, jis užrašo poziciją ir tada tęsia siaurinimą į kairę, todėl rezultatas — mažiausias serijos indeksas; su paieškos režimu -2, per mažėjančius duomenis, jis užrašo poziciją ir siaurina į dešinę, todėl rezultatas — didžiausias indeksas. Linijiniai režimai paprastesni: paieškos režimas 1 grąžina pirmą pataikymą einant į priekį, paieškos režimas -1 — pirmą pataikymą einant atgal. Tai — detalė, sukurianti keturių eilučių neatitikimą iš pradinės pastraipos, nes darbaknygė, kurios raktai unikalūs, duoda identiškus atsakymus visuose keturiuose paieškos režimuose ir paslepia skirtumą kiekviename teste, kurį parašėte iš švaraus pavyzdinio failo. Pridėkite vieną dubliuotą kliento kodą prie gamybos duomenų, ir režimai pradeda nesutarti kaip tik tose eilutėse, kurios dubliavosi: variklyje niekas nepasikeitė, įvestis tiesiog nustojo būti aibė ir tapo daugiaibe
// 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 runKaip apytikslis atitikimas pasirenka vicečempioną?
Laikydamas geriausią kandidatą šalia tikslaus atitikimo paieškos ir jį grąžindamas tik tada, jei jokio tikslaus pataikymo nepasitaiko. HotXLS match_mode -1 laiko „didžiausia reikšme, ne didesne už taikinį“, o match_mode 1 — „mažiausia reikšme, ne mažesne“, ir abu išsprendžiami per visą nuskaitytą sritį, o ne sustojant ties pirmu priimtinu kaimynu. Dvejetainiame kelyje ta pati idėja iškrenta iš nusileidimo nemokamai: kiekvienas žingsnis, viršijantis ar nesiekiantis, atnaujina kandidatą, todėl galutinis kandidatas — ribinis elementas šalia pozicijos, kur raktas būtų buvęs įterptas
// 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;Atidžiai perskaitykite vidinę sąlygą, nes ten gyvena lygiavertiškumo sprendimas. Nauja ląstelė pakeičia esamą kandidatą tik tada, kai ji griežtai geresnė, niekada — kai tiesiog jai lygi, todėl tarp kelių ląstelių, laikančių tą pačią vicečempiono reikšmę, išlaikoma ta, kuri sutikta pirma skenavimo tvarka: mažiausias indeksas tiesioginiame skenavime, didžiausias — atgaliniame. Jei XLOOKUP ir XMATCH neranda nei tikslaus pataikymo, nei priimtino kaimyno, XLOOKUP kreipiasi į savo if_not_found argumentą, kai jis buvo pateiktas, ir į #N/A, kai nebuvo, o XMATCH visada duoda #N/A
Kodėl pakaitos simboliai ir dvejetainė paieška negali sugyventi
Todėl, kad pakaitos simbolio šablonas nėra pozicija tvarkoje. Atitikimo režimas 2 klausia, ar ląstelė atitinka kaukę, o kaukės atitikimas atsako taip arba ne; dvejetainiam nusileidimui reikia trijų krypčių atsakymo, sakančio, kurią pusę palikti. Nėra jokio pagrįsto būdo klausti, ar ACME-* yra kairėje, ar dešinėje nuo tam tikros ląstelės, todėl HotXLS iš karto atmeta match_mode 2, sujungtą su search_mode 2 arba -2, su #VALUE!, vietoj to, kad spėtų tvarką ir sukurtų tikėtinai atrodančią nesąmonę. Abu keliai taip pat skirtingai lygina reikšmes, kas sustiprina šį atskyrimą: linijinis skenavimas lygybę nusprendžia raidžių dydžio nejautriu teksto palyginimu arba kaukės atitikimu, kai pakaitos simboliai įjungti, o dvejetainis nusileidimas lygybę nusprendžia paklausdamas tvarkos komparatoriaus nulio. Tai sąmoninga, ne sluoksniavimo atsitiktinumas, nes dvejetainis kelias gali naudoti tik tą santykį, pagal kurį jis iš tikrųjų navigacija vykdo. Jei jums reikia pakaitos simbolių, naudokite paieškos režimą 1 arba -1 ir priimkite linijinę kainą, tas pats kompromisas, kurį saugoti nuo jūsų kritinio kelio skirtas priklausomybių sekimo mechanizmas už priaugantį perskaičiavimą
Formos klaidos: dvimatės sritys ir nesutampantys grąžinimo vektoriai
Abi funkcijos reikalauja tikrai vienmatės paieškos srities. Jei pateikta sritis apima daugiau nei vieną eilutę ir daugiau nei vieną stulpelį vienu metu, HotXLS grąžina #VALUE!, o ne pasirenka ašį už jus, o viena eilutė ar vienas stulpelis skaitomas išilgai savo ilgosios ašies. XLOOKUP prideda antrą formos taisyklę: grąžinimo sritis turi būti lygiai tokio pat ilgio kaip paieškos sritis atitinkamoje ašyje, todėl vertikali paieška per 500 eilučių, suporuota su 499 eilučių grąžinimo sritimi, yra klaida, ne tyliai per paskutinę eilutę išspręstas nukrypimas per vieną. Kai grąžinimo sritis platesnė nei vienas stulpelis vertikaliai paieškai, arba aukštesnė nei viena eilutė horizontaliai, XLOOKUP grąžina visą atitikusią atkarpą kaip masyvą, ir ji išsilieja į kaimynines ląsteles pagal tas pačias taisykles kaip ir kitos dinaminių masyvų funkcijos, aprašytos straipsnyje apie išsiliejimo sritis ir dinaminius masyvus. Tai tikrai naudinga, ištraukiant visą įrašą iš lentelės viena formule, ir tai — taip pat greičiausias būdas perrašyti stulpelį, kurį ketinote išsaugoti
Režimo pasirinkimas, kai niekas nežiūri į ekraną
Serverio pusės generavimas nusipelno griežtesnės politikos nei interaktyvus naudojimas, nes nėra žmogaus, kuris pastebėtų, kad suma atrodo neteisinga. Ginamas numatytasis — paieškos režimas 1 su atitikimo režimu 0: linijinis, tikslus, nepriklausomas nuo tvarkos ir negalimas sugadinti pakartotinai surikiuojant lapą. Siekite paieškos režimo 2 tik ten, kur tas pats kodo kelias taip pat sukūrė tvarką, tame pačiame paleidime, per tą patį stulpelį, ir užrašykite tą priklausomybę šalia formulės, nes dvejetainė paieška stulpelyje, surikiuotame pagal kitą raktą, — pigiausias įmanomas būdas apskaičiuoti pasitikintį neteisingą skaičių. Kai paieška iš tikrųjų aktyvi, o duomenys iš tikrųjų surikiuoti, nauda reali: nusileidimas nuskaito maždaug log n ląstelių vietoj n, o kiekvienas iš tų skaitymų eina per pilną darbaknygės ląstelės išsprendimą, todėl santaupa didesnė, nei rodytų instrukcijų skaičius
Jei problemos forma artimesnė domeno taisyklei nei paieškai, atgalinis iškvietimas į savo pačių Pascal kodą, aprašytas straipsnyje apie pasirinktines darbalapio funkcijas, paprastai pralenks bet kokį protingą įmontuotų funkcijų derinį. Čia aptariami XLOOKUP ir XMATCH realizacijos dalyvauja standartiniame HotXLS Delphi skaičiuoklės komponente, kurio produkto puslapyje pateikta pilna palaikomų funkcijų dokumentacija Delphi ir C++Builder platformoms