HotXLS, natywny komponent arkuszy kalkulacyjnych dla Delphi i C++Builder, ocenia XLOOKUP i XMATCH przez jeden współdzielony rdzeń wyszukiwania. Ten rdzeń akceptuje cztery tryby dopasowania (-1, 0, 1, 2) i cztery tryby wyszukiwania (-2, -1, 1, 2), uruchamia logarytmiczne zejście binarne zawsze, gdy bezwzględny tryb wyszukiwania wynosi 2, i odrzuca każdą inną kombinację błędem formuły
Zgłoszenie błędu, które przywodzi Cię tutaj, nigdy nie mówi „tryb wyszukiwania”. Mówi, że skoroszyt wygenerowany na serwerze pokazuje inną liczbę niż ten sam plik otwarty w Excelu, na może czterech wierszach z dziewięciu tysięcy. Te cztery wiersze zawsze mają coś wspólnego: zduplikowany klucz wyszukiwania, albo dopasowanie przybliżone, które musiało wybrać sąsiada, albo kolumnę wyszukiwania, którą ktoś posortował według innej kolumny w zeszłym tygodniu. Funkcje wyszukiwania to miejsce, w którym silnik formuł przestaje być arytmetyką i zaczyna być kontraktem, a kontrakt ma klauzule, których większość wywołujących nigdy nie czyta
Które numery trybów faktycznie akceptuje XLOOKUP?
Dokładnie cztery każdego rodzaju i nic więcej. HotXLS waliduje match_mode wobec -1, 0, 1 i 2 oraz search_mode wobec -2, -1, 1 i 2, zanim dotknie choćby jednej komórki, a każda inna wartość zwraca #VALUE! zamiast być przycinana do najbliższego legalnego trybu. Cztery tryby dopasowania to 0 dla dokładnego, -1 dla dokładnego lub następnego mniejszego, 1 dla dokładnego lub następnego większego, i 2 dla wieloznacznika; cztery tryby wyszukiwania to 1 dla liniowego skanowania w przód, -1 dla liniowego skanowania wstecz, 2 dla wyszukiwania binarnego nad danymi rosnącymi, i -2 dla wyszukiwania binarnego nad danymi malejącymi. Pominięcie ich wybiera tryb dopasowania 0 i tryb wyszukiwania 1, parę, której używa niemal każda realna formuła. Liczby argumentów są egzekwowane w ten sam sposób: XLOOKUP przyjmuje od trzech do sześciu argumentów, a XMATCH od dwóch do czterech, i cokolwiek poza tymi zakresami daje #VALUE!, zanim ocena się rozpocznie
// 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;Krok wcześniej jest ciszejsze sprawdzenie warte poznania. Argumenty trybu przychodzą jako wyrażenia arkusza, więc HotXLS wymusza je do liczby, odrzuca NaN i nieskończoność, a potem wymaga, by liczba równała się swojej własnej zaokrąglonej wartości. XLOOKUP(x, A:A, B:B, "none", 0, 1.5) to #VALUE!, nie zamaskowany tryb wyszukiwania 2. Ma to znaczenie, gdy tryb pochodzi z komórki, którą wyprodukowało obliczenie mocno korzystające z zaokrągleń, co jest częstsze w skoroszytach generowanych niż w pisanych ręcznie
Dlaczego search_mode 2 daje złą odpowiedź na niesortowanych danych?
Ponieważ robi dokładnie to, o co poprosiłeś. Tryb wyszukiwania 2 mówi silnikowi, że wektor wyszukiwania jest już w porządku rosnącym, a wyszukiwanie binarne nie może zweryfikować tego twierdzenia bez przejścia O(n), które zniszczyłoby powód jego użycia. HotXLS ufa więc wywołującemu, dzieli przedział na pół i zwraca to, na czym wyląduje zejście. Na niesortowanych danych wejściowych odpowiedź nie jest błędem, jest po cichu błędna, i to jest naruszenie kontraktu, nie usterka silnika
Microsoft dokumentuje tę samą asymetrię dla XLOOKUP i XMATCH: tryby binarne wymagają posortowanych danych i inaczej produkują nieprawidłowe wyniki. ISO 29500-1 klauzula 18.17, która definiuje gramatykę formuł SpreadsheetML, niesie starsze opisy LOOKUP i VLOOKUP z ich własnym wymogiem porządku rosnącego, a XLOOKUP i XMATCH powstały na tyle później od tego tekstu, że w pliku podróżują jako _xlfn.XLOOKUP i _xlfn.XMATCH pod konwencją funkcji przyszłościowych. Inne pokolenie, ten sam układ: wywołujący dostarcza niezmiennik porządku, silnik dostarcza logarytm
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;Prześledź drugą formułę, a awaria jest całkowicie mechaniczna. Zejście sonduje środkową komórkę, czyta 10, decyduje, że 10 jest mniejsze niż 40, odrzuca lewą połowę wraz z wierszem, który faktycznie trzymał 40, sonduje 30, odrzuca ponownie i wyczerpuje przedział. Excel zachowuje się tak samo, i o to chodzi: odtworzenie błędnej odpowiedzi to wymóg kompatybilności, nie uprzejmość. Założenie porządku jest też bardziej rygorystyczne niż „liczby rosnące”, ponieważ komparator najpierw rangowuje wartości według rodzaju, w kolejności liczby, potem tekst, potem wartości logiczne, potem wartości błędów, potem puste, i dopiero potem porównuje w obrębie rodzaju. Kolumna numerycznych kodów części, w której trzy komórki przechowują zamiast tego tekst, nie jest rosnąca dla tego komparatora, bez względu na to, jak wygląda na ekranie, a tryby binarne chętnie ją błędnie odczytają
Gdzie lądują zduplikowane klucze?
Na deterministycznym końcu przebiegu duplikatów, a który koniec zależy od trybu wyszukiwania, nie od przypadku. Gdy zejście binarne trafi na równy klucz przy trybie wyszukiwania 2, zapisuje pozycję i dalej zawęża w lewo, więc wynikiem jest najniższy indeks przebiegu; przy trybie wyszukiwania -2, nad danymi malejącymi, zapisuje pozycję i zawęża w prawo, więc wynikiem jest najwyższy indeks. Tryby liniowe są prostsze: tryb wyszukiwania 1 zwraca pierwsze trafienie idąc w przód, tryb wyszukiwania -1 pierwsze trafienie idąc wstecz. To właśnie ten szczegół produkuje rozbieżność czterech wierszy z akapitu otwierającego, ponieważ skoroszyt, którego klucze są unikalne, daje identyczne odpowiedzi we wszystkich czterech trybach wyszukiwania i ukrywa różnicę przez każdy test, jaki napisałeś na czystym pliku próbki. Dodaj jeden zduplikowany kod klienta do danych produkcyjnych, a tryby zaczynają się nie zgadzać dokładnie na wierszach, które się zduplikowały: w silniku nic się nie zmieniło, dane wejściowe po prostu przestały być zbiorem i stały się multizbiorem
// 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 runJak dopasowanie przybliżone wybiera zastępcę?
Trzymając najlepszego kandydata obok wyszukiwania dokładnego dopasowania i zwracając go tylko wtedy, gdy żadne dokładne trafienie się nie pojawi. HotXLS traktuje match_mode -1 jako „największą wartość, która nie jest większa od celu”, a match_mode 1 jako „najmniejszą wartość, która nie jest mniejsza”, i obie są rozwiązywane nad całym przeskanowanym regionem, a nie przez zatrzymanie się na pierwszym akceptowalnym sąsiedzie. Na ścieżce binarnej ta sama idea wypada z zejścia za darmo: każdy krok, który przestrzeliwuje lub niedostrzeliwuje, aktualizuje kandydata, więc końcowym kandydatem jest element graniczny obok pozycji, gdzie klucz zostałby wstawiony
// 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;Przeczytaj wewnętrzny warunek uważnie, ponieważ tam mieszka rozstrzyganie remisów. Nowa komórka zastępuje stojącego kandydata tylko wtedy, gdy jest ściśle lepsza, nigdy gdy jedynie jej dorównuje, więc spośród kilku komórek trzymających tę samą wartość zastępczą zachowana jest ta pierwsza napotkana w kolejności skanowania: najniższy indeks przy skanowaniu w przód, najwyższy przy skanowaniu wstecz. Jeśli XLOOKUP i XMATCH nie znajdą ani dokładnego trafienia, ani akceptowalnego sąsiada, XLOOKUP spada do swojego argumentu if_not_found, gdy został dostarczony, i do #N/A, gdy nie został, podczas gdy XMATCH zawsze daje #N/A
Dlaczego wieloznaczniki i wyszukiwanie binarne nie mogą współistnieć
Ponieważ wzorzec wieloznaczny nie jest pozycją w porządku. Tryb dopasowania 2 pyta, czy komórka pasuje do maski, a dopasowanie maski odpowiada tak albo nie; zejście binarne potrzebuje odpowiedzi trójwartościowej, która mówi mu, którą połowę zachować. Nie ma obronnego sposobu, by zapytać, czy ACME-* leży na lewo czy na prawo od danej komórki, więc HotXLS odrzuca match_mode 2 połączony z search_mode 2 lub -2 od razu przez #VALUE! zamiast zgadywać porządek i produkować prawdopodobnie wyglądające bzdury. Obie ścieżki porównują też wartości inaczej, co wzmacnia podział: skanowanie liniowe decyduje o równości przez porównanie tekstu bez rozróżniania wielkości liter, albo przez dopasowanie maski, gdy wieloznaczniki są włączone, podczas gdy zejście binarne decyduje o równości, pytając komparator porządku o zero. To celowe, nie przypadek warstwowania, ponieważ ścieżka binarna może używać wyłącznie relacji, według której faktycznie nawiguje. Jeśli potrzebujesz wieloznaczników, użyj trybu wyszukiwania 1 lub -1 i zaakceptuj koszt liniowy, co jest tym samym kompromisem, który śledzenie zależności stojące za przeliczaniem przyrostowym ma za zadanie trzymać z dala od Twojej ścieżki krytycznej
Błędy kształtu: zakresy dwuwymiarowe i niedopasowane wektory zwracane
Obie funkcje wymagają prawdziwie jednowymiarowego zakresu wyszukiwania. Jeśli dostarczony zakres obejmuje więcej niż jeden wiersz i więcej niż jedną kolumnę jednocześnie, HotXLS zwraca #VALUE! zamiast wybierać oś za Ciebie, a zakres jednowierszowy lub jednokolumnowy jest czytany wzdłuż swojej długiej osi. XLOOKUP dodaje drugą regułę kształtu: zakres zwracany musi być dokładnie tak długi, jak zakres wyszukiwania wzdłuż dopasowywanej osi, więc pionowe wyszukiwanie nad 500 wierszami sparowane z 499-wierszowym zakresem zwracanym to błąd, nie po cichu rozwiązane przesunięcie o jeden przy ostatnim wierszu. Gdy zakres zwracany jest szerszy niż jedna kolumna dla wyszukiwania pionowego, albo wyższy niż jeden wiersz dla poziomego, XLOOKUP oddaje cały dopasowany wycinek jako tablicę, a ta rozlewa się do sąsiednich komórek pod tymi samymi regułami co inne dynamiczne funkcje tablicowe, opisane w artykule o zakresach rozlewania i tablicach dynamicznych. To naprawdę przydatne przy wyciąganiu całego rekordu z tabeli jedną formułą, i jest to też najszybszy sposób na nadpisanie kolumny, którą chciałeś zachować
Wybór trybu, gdy nikt nie patrzy na ekran
Generowanie po stronie serwera zasługuje na bardziej rygorystyczną politykę niż użycie interaktywne, ponieważ nie ma człowieka, który zauważyłby, że suma wygląda źle. Obronnym ustawieniem domyślnym jest tryb wyszukiwania 1 z trybem dopasowania 0: liniowe, dokładne, niezależne od porządku i niemożliwe do unieważnienia przez ponowne posortowanie arkusza. Sięgnij po tryb wyszukiwania 2 tylko tam, gdzie ta sama ścieżka kodu wyprodukowała też porządek, w tym samym przebiegu, nad tą samą kolumną, i zapisz tę zależność obok formuły, ponieważ wyszukiwanie binarne na kolumnie posortowanej według innego klucza jest najtańszym możliwym sposobem obliczenia pewnej siebie, błędnej liczby. Gdy wyszukiwanie jest naprawdę gorące, a dane naprawdę posortowane, zysk jest realny: zejście czyta rzędu log n komórek zamiast n, a każdy z tych odczytów przechodzi przez pełne rozwiązanie komórki skoroszytu, więc oszczędność jest większa, niż sugeruje liczba instrukcji
Jeśli kształt problemu jest bliższy regule dziedzinowej niż wyszukiwaniu, wywołanie zwrotne do Twojego własnego kodu Pascal, omówione w artykule o niestandardowych funkcjach arkusza, zwykle pokona każdy sprytny układ funkcji wbudowanych. Omówione tu implementacje XLOOKUP i XMATCH są dostarczane ze standardowym komponentem arkusza Delphi HotXLS, którego strona produktu niesie pełną dokumentację obsługiwanych funkcji dla Delphi i C++Builder