HotXLS, нативният компонент за електронни таблици за Delphi и C++Builder, оценява XLOOKUP и XMATCH през едно споделено ядро за търсене. Това ядро приема четири режима на съвпадение (-1, 0, 1, 2) и четири режима на търсене (-2, -1, 1, 2), изпълнява логаритмично бинарно спускане винаги когато абсолютният режим на търсене е 2, и отхвърля всяка друга комбинация с грешка на формула
Докладът за бъг, който ви праща тук, никога не казва „режим на търсене“. Той казва, че работна книга, генерирана от сървъра, показва различно число от същия файл, отворен в Excel, на може би четири реда от девет хиляди. Тези четири реда винаги имат нещо общо: дублиран ключ за търсене, или приблизително съвпадение, което е трябвало да избере съсед, или колона за търсене, която някой е сортирал по различна колона миналата седмица. Функциите за търсене са мястото, където машина за формули спира да бъде аритметика и започва да бъде договор, а договорът има клаузи, които повечето извикващи никога не четат
Кои номера на режими всъщност приема XLOOKUP?
Точно четири от всеки, и нищо друго. HotXLS валидира match_mode спрямо -1, 0, 1 и 2 и search_mode спрямо -2, -1, 1 и 2, преди да докосне дори една клетка, а всяка друга стойност връща #VALUE!, вместо да бъде прикачена към най-близкия легален режим. Четирите режима на съвпадение са 0 за точно, -1 за точно или следващо по-малко, 1 за точно или следващо по-голямо, и 2 за wildcard; четирите режима на търсене са 1 за напредващо линейно сканиране, -1 за обратно линейно сканиране, 2 за бинарно търсене над възходящи данни, и -2 за бинарно търсене над низходящи данни. Пропускането им избира режим на съвпадение 0 и режим на търсене 1, комбинацията, използвана от почти всяка реална формула. Броят аргументи се проверява по същия начин: XLOOKUP приема от три до шест аргумента, а XMATCH — от два до четири, и всичко извън тези диапазони е #VALUE!, преди оценяването да започне
// 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;Една стъпка по-рано има по-тиха проверка, която си струва да се знае. Аргументите за режим пристигат като изрази на работния лист, така че HotXLS ги привежда до число, отказва NaN и безкрайност, и после изисква числото да е равно на собствената си закръглена стойност. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) е #VALUE!, не режим на търсене 2 прикрит. Това има значение, когато режимът идва от клетка, произведена от изчисление, богато на закръгляне, което е по-често в генерирани работни книги, отколкото в ръчно написани
Защо search_mode 2 дава грешен отговор върху несортирани данни?
Защото прави точно това, което сте поискали. Режим на търсене 2 казва на машината, че векторът за търсене вече е във възходящ ред, а бинарно търсене не може да провери това твърдение без O(n) проход, който би унищожил причината за използването му. HotXLS затова се доверява на извикващия, преполовява интервала и връща каквото спускането случайно попадне. Върху несортиран вход отговорът не е грешка, той е тихо грешен, и това е нарушение на договора, а не дефект в машината
Microsoft документира същата асиметрия за XLOOKUP и XMATCH: бинарните режими изискват сортирани данни и произвеждат невалидни резултати иначе. ISO 29500-1 клауза 18.17, която дефинира граматиката на формулите SpreadsheetML, носи по-старите описания на LOOKUP и VLOOKUP със собственото им изискване за възходящ ред, а XLOOKUP и XMATCH датират след този текст достатъчно, за да пътуват във файла като _xlfn.XLOOKUP и _xlfn.XMATCH под конвенцията за бъдещи функции. Различно поколение, същата сделка: извикващият подава инварианта за подредба, машината подава логаритъма
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;Проследете втората формула и провалът е напълно механичен. Спускането сондира средната клетка, чете 10, решава че 10 е по-малко от 40, изхвърля лявата половина, включително реда, който всъщност е държал 40, сондира 30, изхвърля отново, и изчерпва интервала. Excel се държи по същия начин, което е смисълът: възпроизвеждането на грешния отговор е изискване за съвместимост, не любезност. Предпоставката за подредба е и по-строга от „възходящи числа“, защото компараторът първо класира стойностите по вид, в реда числа, после текст, после булеви, после стойности за грешка, после празни, и сравнява в рамките на вида едва след това. Колона с числови кодове на части, която има три клетки, съхраняващи текст вместо число, не е възходяща под този компаратор, независимо как изглежда на екрана, и бинарните режими с радост ще я прочетат грешно
Къде кацат дублираните ключове?
На детерминиран край на серията от дубликати, и кой край зависи от режима на търсене, а не от късмет. Когато бинарното спускане удари равен ключ под режим на търсене 2, то записва позицията и после продължава да стеснява наляво, така че резултатът е най-ниският индекс на серията; под режим на търсене -2, над низходящи данни, то записва позицията и стеснява надясно, така че резултатът е най-високият индекс. Линейните режими са по-прости: режим на търсене 1 връща първото попадение напредвайки, режим на търсене -1 — първото попадение отзад напред. Точно тази подробност произвежда разминаването от четири реда от началния параграф, защото работна книга, чиито ключове са уникални, дава идентични отговори под всичките четири режима на търсене и крие разликата през всеки тест, който сте написали от чист примерен файл. Добавете един дублиран код на клиент към производствени данни и режимите започват да не се съгласяват точно на редовете, които са се дублирали: нищо не се е променило в машината, входът просто е спрял да бъде множество и е станал мултимножество
// 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 runКак приблизителното съвпадение избира заместника?
Като пази най-добрия кандидат заедно с търсенето за точно съвпадение и го връща само ако не се появи точно попадение. HotXLS третира match_mode -1 като „най-голямата стойност, която не е по-голяма от целта“, а match_mode 1 като „най-малката стойност, която не е по-малка“, и двете се разрешават над целия сканиран регион, вместо да спрат на първия приемлив съсед. В бинарния път същата идея пада безплатно от спускането: всяка стъпка, която надхвърля или не достига, обновява кандидата, така че крайният кандидат е граничният елемент до позицията, където ключът би бил вмъкнат
// 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;Прочетете вътрешното условие внимателно, защото разбиването на равенство живее там. Нова клетка заменя стоящия кандидат само когато е строго по-добра, никога когато просто е равна на него, така че сред няколко клетки, държащи една и съща стойност на заместник, запазената е първата срещната в реда на сканиране: най-ниският индекс при напредващо сканиране, най-високият при обратно сканиране. Ако XLOOKUP и XMATCH не намерят нито точно попадение, нито приемлив съсед, XLOOKUP се връща към аргумента си if_not_found, когато такъв е подаден, и към #N/A, когато не е, докато XMATCH винаги дава #N/A
Защо wildcard и бинарно търсене не могат да съществуват заедно
Защото wildcard шаблон не е позиция в подредба. Режим на съвпадение 2 пита дали клетка съвпада с маска, а съвпадението с маска отговаря да или не; бинарно спускане се нуждае от троен отговор, който му казва коя половина да задържи. Няма защитим начин да се попита дали ACME-* лежи наляво или надясно от дадена клетка, така че HotXLS отхвърля match_mode 2, комбиниран с search_mode 2 или -2, направо с #VALUE!, вместо да гадае подредба и да произведе правдоподобни глупости. Двата пътя сравняват стойности и по различен начин, което подсилва разделянето: линейното сканиране решава равенство с нечувствително към регистъра текстово сравнение, или със съвпадение по маска, когато wildcard е включен, докато бинарното спускане решава равенство, като пита компаратора за подредба за нула. Това е умишлено, а не случайност на слоевете, тъй като бинарният път може да използва само отношението, по което всъщност навигира. Ако ви трябват wildcard, използвайте режим на търсене 1 или -1 и приемете линейната цена, което е същата сделка, която проследяването на зависимости зад инкременталното преизчисляване е проектирано да държи извън критичния ви път
Грешки във формата: двумерни диапазони и несъответстващи върнати вектори
И двете функции изискват истински едномерен диапазон за търсене. Ако подаденият диапазон обхваща повече от един ред и повече от една колона едновременно, HotXLS връща #VALUE!, вместо да избира ос от ваше име, а диапазон с единичен ред или единична колона се чете по дългата си ос. XLOOKUP добавя второ правило за форма: диапазонът за връщане трябва да е точно толкова дълъг, колкото диапазона за търсене по съвпадащата ос, така че вертикално търсене над 500 реда, съчетано с диапазон за връщане от 499 реда, е грешка, не отместена с едно тихо разрешена на последния ред. Когато диапазонът за връщане е по-широк от една колона за вертикално търсене, или по-висок от един ред за хоризонтално, XLOOKUP връща целия съвпаднал отрязък като масив, и той се разлива в съседните клетки под същите правила като другите функции за динамичен масив, описани в статията за диапазони на разливане и динамични масиви. Това е наистина полезно за изтегляне на цял запис от таблица с една формула, и е и най-бързият начин да презапишете колона, която сте искали да запазите
Избор на режим, когато никой не гледа екрана
Генерирането на сървъра заслужава по-строга политика от интерактивната употреба, защото няма човек, който да забележи, че сборът изглежда грешно. Защитимата стойност по подразбиране е режим на търсене 1 с режим на съвпадение 0: линеен, точен, независим от подредбата, и невъзможен за анулиране чрез пресортиране на лист. Посягайте към режим на търсене 2 само там, където същият път на кода е произвел и подредбата, в същото изпълнение, над същата колона, и запишете тази зависимост до формулата, защото бинарно търсене над колона, сортирана по различен ключ, е най-евтиният възможен начин да изчислите уверено грешно число. Когато търсенето е наистина често и данните наистина сортирани, възвращаемостта е реална: спускането чете от порядъка на log n клетки вместо n, а всяко от тези четения минава през пълно разрешаване на клетка на работната книга, така че спестяването е по-голямо, отколкото броят инструкции подсказва
Ако формата на проблема е по-близо до правило на домейн, отколкото до търсене, обратен извиквания в собствения ви Pascal код, разгледано в статията за персонализирани функции на работния лист, обикновено ще победи всяка хитра подредба на вградените. Имплементациите на XLOOKUP и XMATCH, разгледани тук, се доставят със стандартния HotXLS Delphi компонент за електронни таблици, чиято страница на продукта носи пълната справка за поддържани функции за Delphi и C++Builder