Wildcardy Excela w HotXLS: COUNTIF, MATCH, DSUM i Find

HotXLS Delphi Component czyta ten sam wzorzec na cztery różne sposoby, bo tyle robi Excel 16. W COUNTIF i SUMIF tekst a~b jest dosłowny, dopóki kryterium nie zawiera także * albo ?; w trybie wildcardów MATCH i XLOOKUP tylda jest zawsze znakiem ucieczki, więc a~b znajduje ab; w DSUM i pozostałych funkcjach bazodanowych czysty tekst znaczy „zaczyna się od”; a Find na całą komórkę musi cofać się do ostatniej *. HotXLS trzyma się tych wyzmierzonych reguł od v2.384.52, v2.384.60 i v2.384.64

Bugreporty z tego rejonu nigdy nie wspominają o wildcardach. Mówią, że raport generowany na serwerze liczy o parę wierszy mniej niż ten sam plik przeliczony w Excelu, albo że numer części zawierający tyldę znajduje jedna formuła, a następna go ignoruje. Przyczyną jest matcher zakładający, że wzorzec znaczy wszędzie jedno. Excel tak nie działa, więc silnik, którego cache'owane wyniki mają zgadzać się z Excelem, też nie może. Przed v2.384.52 HotXLS przepuszczał każde kryterium przez plikową maskę w stylu DOS, która codzienne wzorce trafiała, a przypadki brzegowe po cichu gubiła

Dlaczego jeden wzorzec znaczy w Excelu cztery różne rzeczy?

Jeden wzorzec znaczy cztery różne rzeczy, bo Excel odziedziczył cztery reguły dopasowywania po czterech funkcjonalnościach i nigdy ich nie ujednolicił. Funkcje kryteriów (COUNTIF, SUMIF, AVERAGEIF i rodzina *IFS) decydują per kryterium, czy wildcardy w ogóle wchodzą do gry. Funkcje wyszukujące (MATCH z typem dopasowania 0, XLOOKUP z match_mode 2) stosują je zawsze. Funkcje bazodanowe (DSUM, DCOUNTA i spółka) idą za filtrem zaawansowanym, gdzie gołe słowo to prefiks. Okno Find ma własne tryby: na całą komórkę i częściowy. Tabela niżej wymienia, które komórki pasują do każdego wzorca przy jednej kolumnie trzymającej a~b, ab, AB, abc, abcb, a*b i axb, z każdą funkcją w domyślnym trybie bez rozróżniania wielkości liter

Wzorzec	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP tryb 2	Kryterium DSUM	Find, cała komórka, wildcardy włączone
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	jak w COUNTIF	każdy wpis, w tym abc	jak w COUNTIF
a~b	tylko a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	tylko a*b	tylko a*b	tylko a*b	tylko a*b
=ab	ab, AB	nie dotyczy	ab, AB	nie dotyczy

Wiersz a~b to ten, w którym COUNTIF i MATCH się różnią, a numery części i ręcznie wpisywane kody zawierają tyldy częściej, niż ktokolwiek się spodziewa. Wiersz a*b pokazuje drugą pułapkę: abc pasuje dla DSUM, ale nie dla COUNTIF, bo funkcja bazodanowa po cichu dokleja *. Wpisy DSUM dla ab, a*b i =ab prosto z przebiegów Excela 16; wpis DSUM dla a~b wynika z tej samej reguły prefiksu, bo doklejona * zamienia kryterium we wzorzec z wildcardami, w którym ~b to ubezpieczone b

Kiedy COUNTIF włącza tryb wildcardów?

COUNTIF włącza tryb wildcardów tylko wtedy, gdy tekst kryterium zawiera * albo ?, ubezpieczone czy nie. Bez żadnego z tych znaków Excel porównuje kryterium z każdą komórką jako cały tekst, bez rozróżniania wielkości liter, a tylda jest po prostu tyldą, więc COUNTIF(A1:A7,"a~b") liczy komórkę, która dosłownie trzyma a~b. Dorzuć jedną gwiazdkę i znaczenie się odwraca: w "a~b*" tylda ubezpiecza teraz b, wzorzec czyta się jako „ab, po którym cokolwiek”, a komórka a~b nie jest już liczona. HotXLS stosuje tę regułę w obu silnikach od v2.384.52, przez jeden matcher kryteriów w lxCalc współdzielony przez COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS i funkcje bazodanowe

Wewnątrz trybu wildcardów reguły ucieczki są te same co wszędzie indziej w Excelu: ~ czyni następny znak dosłownym, czymkolwiek by był, więc ~b znaczy b, a ~~ znaczy jedną tyldę, i tylda na samym końcu wzorca jest odrzucana, więc "a*~" zachowuje się jak "a*". Nawiasy kwadratowe nigdy nie są specjalne. Kryterium "[x]" liczy komórki trzymające trzy znaki [x], a "[a-z]" na zwykłych danych nie liczy niczego. TXLSXWorkbook.Calculate wylicza tekst formuły na aktywnym arkuszu i zwraca Variant — najszybszy sposób, żeby sprawdzić te reguły na własnych danych

uses
  System.Variants, lxHandleX;

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;

  procedure Show(const Formula: string);
  begin
    Writeln(Formula, ' = ', VarToStr(Book.Calculate(Formula)));
  end;

begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i, 1].Value := Names[i];
      Sheet.Cells[i, 2].Value := 1 shl (i - 1);  // 1, 2, 4 ... żeby suma z SUMIF wskazywała swoje wiersze
    end;
    Sheet.Cells[8, 1].Value := 5;                // liczba; A9 zostaje puste

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    bez * i ?: czysty tekst, komórka a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    tryb wildcardów: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard na cały tekst, abc wyłączone
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  każdy wiersz poza abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    dosłowne a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    liczą się liczba 5 i puste A9
    Show('=COUNTIF(A1:A9,"<>")');       // 8    komórki niepuste
  finally
    Book.Free;
  end;
end.

Co liczy „<>tekst”?

Kryterium "<>text" liczy każdą komórkę, która nie jest tym tekstem, a w Excelu 16 wchodzą do tego liczby, booly, wartości błędów i puste komórki. Gołe "<>" to zupełnie inne pytanie: znaczy „niepusta komórka”, więc pomija komórki puste, ale liczy każdą wartość, łącznie z pustym tekstem, który zwraca formuła typu ="". Stary kod HotXLS trafiał komórki tekstowe, ale nie liczby: nierówność Variant zmuszała Delphi do konwersji 'ab' na liczbę, konwersja rzucała wyjątkiem, handler połykał go jako „brak dopasowania”, a komórki liczbowe po cichu wypadały z liczenia. Stronę tej historii dotyczącą pustych komórek, łącznie z tym, czemu równa się pusty operand w zwykłym porównaniu, opisuje artykuł o tym, jak HotXLS obsługuje łańcuchy porównań, puste komórki i SUMIF

Dlaczego MATCH znajduje ab, gdy szukasz a~b?

MATCH znajduje ab, gdy szukasz a~b, bo MATCH z typem dopasowania 0 i XLOOKUP z match_mode 2 są zawsze w trybie wildcardów, więc tylda jest ucieczką nawet wtedy, gdy wzorzec nie zawiera * ani ?. Excel 16 potwierdza to na zakresie dwóch komórek z a~b i ab: MATCH("a~b",D1:D2,0) zwraca 2, a na zakresie trzymającym wyłącznie a~b to samo wywołanie zwraca #N/A. Żeby wyszukać dosłowny tekst a~b, musisz napisać "a~~b". Tymczasem COUNTIF(D1:D2,"a~b") na tych samych dwóch komórkach zwraca 1, licząc drugą komórkę. Ten sam tekst, ten sam zakres, przeciwna komórka

Dlatego HotXLS trzyma te dwie decyzje osobno, zamiast chować je za jednym punktem wejścia „dopasuj wzorzec”. Sam matcher jest współdzielony: od v2.384.52 MATCH, XLOOKUP i funkcje kryteriów puszczają tekst przez ten sam matcher z backtrackingiem, z tą samą obsługą ucieczek i tą samą regułą końcowej tyldy. Różnica siedzi w bramce przed nim. Ścieżka kryteriów najpierw pyta „czy ten tekst zawiera * albo ??”; ścieżka wyszukiwania nigdy nie pyta. Scalenie obu naprawiłoby jedną rodzinę i wyłożyło drugą, a oba kierunki są sprawdzane względem wartości z Excela 16 w obu silnikach. Wyszukiwania z wildcardami mają i własny warunek wstępny: XLOOKUP odrzuca dopasowywanie wildcardów połączone z trybem wyszukiwania binarnego — regułę opisuje przewodnik HotXLS po trybach wyszukiwania XLOOKUP i XMATCH

Jak DSUM i funkcje bazodanowe czytają tekstowe kryterium bez operatora?

DSUM i pozostałe funkcje bazodanowe czytają tekstowe kryterium bez wiodącego =, < albo > jako „zaczyna się od”, przy wciąż aktywnych wildcardach. To reguła filtra zaawansowanego i różni się od COUNTIF zupełnie celowo. Pomiary na Excelu 16 nad kolumną Name z abc, ab, xab, AB, a~b i a*b: kryterium ab pasuje do abc, ab i AB; =ab pasuje tylko do ab i AB; <>ab to nierówność na całym wpisie; a*b i a? też są wzorcami prefiksowymi; >ab to zwykłe porównanie. Przed v2.384.64 HotXLS dopasowywał ab dokładnie, więc DSUM na tych danych testowych zwracał 10 tam, gdzie Excel zwraca 11

Poprawka musiała obejść parser warunków, który zwija ab i =ab w ten sam warunek równości. HotXLS ogląda więc surowy tekst kryterium, zanim zaufa sparsowanemu warunkowi: tekstowe kryterium, którego pierwszy znak to nie =, < ani >, dostaje doklejoną * i idzie przez matcher wildcardów, a wszystko inne zachowuje porównanie na całym wpisie. Jedna praktyczna uwaga, gdy budujesz zakresy kryteriów w kodzie: w silniku XLSX przypisanie tekstu '=ab' do TXLSXCell.Value zapisuje tekst, a klasyczny silnik TXLSWorkbook kompiluje wartość zaczynającą się od = jako formułę, chyba że dodasz z przodu apostrof

const
  Names: array [1..7] of string = ('a~b', 'ab', 'AB', 'abc', 'abcb', 'a*b', 'axb');
  Criteria: array [0..4] of string = ('ab', '=ab', '<>ab', 'a*b', 'a~*');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Db');
    Sheet.Cells[1, 1].Value := 'Name';
    Sheet.Cells[1, 2].Value := 'Val';
    for i := 1 to High(Names) do
    begin
      Sheet.Cells[i + 1, 1].Value := Names[i];
      Sheet.Cells[i + 1, 2].Value := 1 shl (i - 1);
    end;
    Sheet.Cells[1, 4].Value := 'Name';            // nagłówek kryteriów w D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // zostaje tekstem w silniku XLSX
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (zaczyna się od)
    // =ab  -> 6    ab, AB (cały wpis)
    // <>ab -> 121  wszystko poza ab i AB
    // a*b  -> 127  a*b* pasuje do wszystkich siedmiu, w tym abc
    // a~*  -> 32   tylko dosłowne a*b
  finally
    Book.Free;
  end;
end;

Jedna powiązana różnica przeżyła poprawkę prefiksu i ma znaczenie na starszych buildach. Porównania tekstowe typu >ab używały porządku punktów kodowych, a Excel stawia interpunkcję przed literami, więc "a~b">"ab" w Excelu daje FALSE, a w HotXLS dawało TRUE. Od v2.384.67 kryteria > i <, razem ze zwykłym porównywaniem tekstów i sortowaniem, używają collation word sort Excela wg bieżących ustawień regionalnych użytkownika i oba światy znów się zgadzają

Dlaczego Find na całą komórkę gubił abcb?

Find na całą komórkę gubił abcb, bo matcher zatrzymywał się w pierwszym miejscu, gdzie wzorzec się wyczerpał, zamiast cofnąć się do ostatniej *. Matcher dopasowań częściowych stojący za Replace kończy, jak tylko wzorzec się skończy; Find na całą komórkę go wykorzystywał, a potem żądał, żeby dopasowanie pokryło całą komórkę: a*b wobec abcb zatrzymywał się po ab, skonsumował 2 znaki z 4 i był odrzucany. Od v2.384.60 matcher na całą komórkę to osobna implementacja, która traktuje „wzorzec się skończył, tekst nie” jako kolejne niedopasowanie i ponawia próbę od ostatniej gwiazdki, więc a*b pasuje do abcb, a a?b*b pasuje do axbyb — tak jak Find w Excelu 16 z zaznaczonym „Match entire cell contents”

To samo wydanie zmieniło tyldę. Find w Excelu 16, w trybie na całą komórkę i częściowym, traktuje ~ jako ucieczkę dla dowolnego następnego znaku: a~b znajduje ab, a~~b znajduje a~b, a końcowa tylda jest ignorowana, więc q~ zachowuje się jak q. Starszy matcher HotXLS uznawał za ucieczki tylko ~*, ~? i ~~, więc a~b znajdował tekst a~b. Wzorzec Find z pojedynczą ~ jest niestabilny w samym Excelu — pasuje do każdej komórki jak pusty wzorzec — i HotXLS tego nie naśladuje

W silniku XLSX wyszukiwaniem jest TXLSXWorksheet.FindText ze zbiorem TXLSXFindOptions: lxfUseWildcards włącza *, ? i ~, lxfWholeCell wymaga dopasowania całej komórki, a lxfMatchCase czyni porównanie czułym na wielkość liter. Bez lxfUseWildcards każdy znak, łącznie z gwiazdką, jest dosłowny. Find patrzy wyłącznie na wartości tekstowe; komórki liczbowe są pomijane, a komórki formuł także, chyba że ustawisz lxfSearchFormulas — wtedy przeszukiwany jest tekst formuły. Kotwica podana przez StartRow i StartCol jest włączająca, więc pętla Find All stawia krok o jedną kolumnę za każdym trafieniem

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row, Col, NextRow, NextCol, Changed: Integer;
  Opts: TXLSXFindOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Parts');
    Sheet.Cells[1, 1].Value := WideString('abc');
    Sheet.Cells[2, 1].Value := WideString('abcb');
    Sheet.Cells[3, 1].Value := WideString('a~b');
    Sheet.Cells[4, 1].Value := WideString('ab');

    Opts := [lxfUseWildcards, lxfWholeCell];
    if Sheet.FindText('a*b', Row, Col, Opts, 1, 1) then
      Writeln('a*b  whole cell -> row ', Row);   // 2: abc odrzucone, abcb przez backtracking
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b to ubezpieczone b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ to jedna dosłowna tylda

    // Dopasowanie częściowe, Find All: komórka kotwicy jest wliczana, więc stawiaj krok za każdym trafieniem
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // wiersze 1, 2, 3 i 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Zamiana z wildcardami na całą komórkę przepisuje tylko dosłowne a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Pętla częściowa znajduje wszystkie cztery wiersze, łącznie z abc, bo w trybie częściowym a*b musi tylko gdzieś wystąpić wewnątrz komórki. FindTextIn i ReplaceTextIn biorą te same opcje plus okno FirstRow, FirstCol, LastRow, LastCol — programowy odpowiednik szukania w zaznaczeniu. Silnik klasyczny wystawia te same reguły przez przeciążenie z trzema boolami, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus pasujące przeciążenie ReplaceText, z wynikami wiersza i kolumny liczonymi od 1:

var
  Classic: IXLSWorkbook;
  Sheet: TXLSWorksheet;
  Row, Col: Integer;
begin
  Classic := TXLSWorkbook.Create;
  Sheet := Classic.Sheets.Add;
  Sheet.Range['A1', 'A1'].Value := 'abcb';
  // MatchCase = False, UseWildcards = True, WholeCell = True
  if Sheet.FindText('a*b', Row, Col, False, True, True) then
    Writeln('found at ', Row, ',', Col);           // 1,1
  if not Sheet.FindText('a*c', Row, Col, False, True, True) then
    Writeln('a*c does not cover abcb');
end;

Co stary matcher masek DOS robił źle?

Stary matcher mylił znaki specjalne, bo plikowa maska DOS to inny język niż wildcard Excela. Przed v2.384.52 funkcje kryteriów i funkcje bazodanowe podawały każdy wzorzec do MatchesMask, matchera masek plikowych z modułu lxMasks. Jego składnia pokrywa się z Excelową w codziennych przypadkach — dlatego problem pozostał ukryty — ale rozjeżdża się tam, gdzie prawdziwe dane stają się ciekawe:

• [x] było czytane jako zestaw znaków, więc COUNTIF(A1:A10,"[x]") liczyło komórki z x zamiast tekstu w nawiasach, a "[a-z]" pasowało do każdej komórki z jedną literą
• Nie było ucieczki tyldą, więc "a~*b" nie potrafiło dopasować dosłownej gwiazdki
• Zniekształcona maska, jak niedomknięty nawias, rzucała wyjątkiem, który wołający połykał jako „brak dopasowania”, zamieniając literówkę w kryterium na po cichu zły wynik
• Po stronie wyszukiwania MATCH i XLOOKUP uznawały za ucieczki tylko ~*, ~? i ~~, więc MATCH("a~b",…,0) znajdowało dosłowne a~b zamiast ab

Jeśli twoje skoroszyty używały wyłącznie * i ? na zwykłych danych alfanumerycznych, wyniki były już dobre i się nie zmienią. Jeśli zawierają nawiasy, tyldy, kolumny mieszanych typów pod "<>text" albo kryteria DSUM zapisane jako gołe słowa, ich przeliczenie na v2.384.64 lub nowszym może zmienić sumy — a nowe sumy to te, które pokazuje Excel. To samo rozróżnienie między tym, jak Excel przechowuje kryterium, a jak je porównuje, wraca przy zapisanych filtrach; opisuje je artykuł HotXLS o kryteriach DOPER AutoFiltra w BIFF8

Ściąga: reguły wildcardów Excela w HotXLS

• COUNTIF, SUMIF, AVERAGEIF i rodzina *IFS używają wildcardów tylko wtedy, gdy kryterium zawiera * albo ?; w przeciwnym razie porównują całe teksty bez rozróżniania wielkości liter, a ~ jest dosłowna (od v2.384.52)
• MATCH z typem dopasowania 0 i XLOOKUP z match_mode 2 zawsze używają wildcardów, więc a~b znajduje ab, a dosłowna wartość wymaga a~~b (od v2.384.52)
• W trybie wildcardów ~ ubezpiecza dowolny następny znak, a końcowa ~ jest odrzucana; [ i ] to zwykłe znaki
• "<>text" liczy liczby, booly, błędy i puste komórki; gołe "<>" liczy komórki niepuste, łącznie z wynikami =""
• DSUM i pozostałe funkcje bazodanowe traktują czysty tekst jako „zaczyna się od”; =text i <>text porównują cały wpis (od v2.384.64)
• Find na całą komórkę z lxfUseWildcards i lxfWholeCell cofa się do gwiazdki, więc a*b pasuje do abcb; Find i Replace traktują ~ jako ucieczkę dla dowolnego znaku (od v2.384.60)
• Kolejność tekstów w kryteriach > i < idzie za collation word sort Excela — interpunkcja przed literami (od v2.384.67)

Zgodność z Excelem w silniku formuł to głównie przypadki brzegowe właśnie takie jak te, wyzmierzone na Excelu, a nie odgadnięte z dokumentacji. HotXLS wylicza COUNTIF, MATCH, XLOOKUP, DSUM i resztę swojej biblioteki funkcji natywnie w Delphi i C++Builderze, w silniku klasycznym i w silniku XLSX, bez zainstalowanego Excela. Szczegóły, wydania i pobranie wersji próbnej znajdziesz na stronie komponentu arkuszy kalkulacyjnych HotXLS dla Delphi