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
Чому wildcards та бінарний пошук не можуть співіснувати
Тому що шаблон wildcard — не позиція в порядку. Режим збігу 2 запитує, чи клітинка відповідає масці, а зіставлення маски відповідає так чи ні; бінарному спуску потрібна триважлива відповідь, що вказує, яку половину зберегти. Немає захищеного способу запитати, чи ACME-* лежить ліворуч чи праворуч від даної клітинки, тож HotXLS відхиляє match_mode 2 у поєднанні з search_mode 2 чи -2 наперед з #VALUE! замість того, щоб вгадувати порядок і видавати правдоподібну нісенітницю. Два шляхи також порівнюють значення по-різному, що підсилює розрив: лінійне сканування вирішує рівність порівнянням тексту без урахування регістру, або зіставленням маски, коли wildcards увімкнено, тоді як бінарний спуск вирішує рівність, запитуючи компаратор порядку про нуль. Це навмисне, а не випадковість шарування, оскільки бінарний шлях може використовувати лише те відношення, за яким він насправді навігує. Якщо потрібні wildcards, використовуйте режим пошуку 1 чи -1 і прийміть лінійну вартість, той самий компроміс, що й відстеження залежностей за інкрементним перерахунком призначене тримати подалі від вашого критичного шляху
Помилки форми: двовимірні діапазони та невідповідні вектори повернення
Обидві функції вимагають справді одновимірного діапазону пошуку. Якщо наданий діапазон охоплює більше одного рядка й більше одного стовпця одночасно, HotXLS повертає #VALUE! замість того, щоб обрати вісь за вас, а однорядковий чи одностовпчиковий діапазон читається вздовж своєї довгої осі. XLOOKUP додає друге правило форми: діапазон повернення має бути рівно такої довжини, як діапазон пошуку вздовж осі збігу, тож вертикальний пошук над 500 рядками, поєднаний з діапазоном повернення на 499 рядків, — це помилка, а не тихо розв'язана похибка на один рядок на останньому. Коли діапазон повернення ширший за один стовпець для вертикального пошуку, чи вищий за один рядок для горизонтального, XLOOKUP повертає весь зіставлений зріз як масив, і він розливається у сусідні клітинки за тими самими правилами, що й інші функції динамічних масивів, описані в статті про діапазони розливу та динамічні масиви. Це справді корисно для витягу цілого запису з таблиці одною формулою, і це також найшвидший спосіб перезаписати стовпець, який ви мали намір зберегти
Вибір режиму, коли ніхто не дивиться на екран
Генерація на боці сервера заслуговує на суворішу політику, ніж інтерактивне використання, бо немає людини, яка помітить, що підсумок виглядає неправильно. Захищена типова політика — режим пошуку 1 з режимом збігу 0: лінійний, точний, незалежний від порядку, і неможливо зробити недійсним повторним сортуванням аркуша. Сягайте по режим пошуку 2 лише там, де той самий шлях коду також створив упорядкування, у тому самому прогоні, над тим самим стовпцем, і запишіть цю залежність поряд з формулою, бо бінарний пошук у стовпці, відсортованому за іншим ключем, — найдешевший можливий спосіб обчислити впевнене неправильне число. Коли пошук справді гарячий, а дані справді відсортовані, віддача реальна: спуск читає порядку log n клітинок замість n, і кожне з цих читань проходить через повне розв'язання клітинки книги, тож економія більша, ніж підказує кількість інструкцій
Якщо форма проблеми ближча до доменного правила, ніж до пошуку, зворотний виклик у вашому власному коді Pascal, як охоплено в статті про власні функції аркуша, зазвичай переможе будь-яке хитре розташування вбудованих. Реалізації XLOOKUP та XMATCH, обговорені тут, постачаються зі стандартним компонентом електронних таблиць HotXLS Delphi, чия сторінка продукту несе повну довідку підтримуваних функцій для Delphi та C++Builder