Artykuł techniczny

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

Przy a~b w D1 i ab w D2 formuła =COUNTIF(D1:D2,"a~b") zwraca 1, a liczy komórkę D1. =MATCH("a~b",D1:D2,0) zwraca 2, co wskazuje na D2. Ten sam wzorzec, te same dwie komórki, a Excel wybiera inną komórkę w zależności od tego, która funkcja czyta wzorzec. Silnik arkusza, który chce, by jego zapamiętane wyniki zgadzały się z Excelem, musi odtworzyć ten podział plus jeszcze dwie reguły: funkcje bazodanowe typu DSUM traktują goły tekst jako „zaczyna się od", a Find przy opcji całej komórki musi cofać się do ostatniej *

Raporty, które na to trafiają, rzadko wspominają o wildcardach. Mówią, że wygenerowany na serwerze skoroszyt liczy o dwa wiersze mniej niż ten sam plik przeliczony na desktopie, albo że numer części zawierający tyldę znajduje jedna formuła, a następna go ignoruje. Przepuszczanie każdego wzorca przez maskę plikową w stylu DOS daje trafienie w codziennych przypadkach, więc dryf ujawnia się dopiero, gdy w prawdziwych kodach produktów zaczną się pojawiać ~ albo [

Jeden wzorzec, cztery zestawy reguł Excela

Excel nigdy nie ujednolicił swoich reguł dopasowywania; każda funkcja przyniosła własne. Funkcje kryteriów (COUNTIF, SUMIF, AVERAGEIF i rodzina *IFS) decydują osobno dla każdego kryterium, czy wildcardy w ogóle obowiązują. Funkcje wyszukujące (MATCH z typem dopasowania 0, XLOOKUP z match_mode 2) stosują je zawsze. Funkcje bazodanowe (DSUM, DCOUNTA i reszta) dziedziczą regułę Advanced Filter, gdzie gołe słowo to prefiks. Okno Find ma własne tryby całej komórki i częściowy. HotXLS Delphi Component ewaluuje każdą rodzinę według jej własnych reguł, w klasycznym silniku i silniku XLSX. Tabela pokazuje, które komórki pasują do każdego wzorca w kolumnie trzymającej a~b, ab, AB, abc, abcb, a*b i axb, przy każdej funkcji w domyślnym trybie nieuwzględniania wielkości liter

WzorzecCOUNTIF / SUMIFMATCH(…,0) / XLOOKUP tryb 2Kryterium DSUMFind, cała komórka, wildcardy włączone
abab, ABab, ABab, AB, abc, abcbab, AB
a*ba~b, ab, AB, abcb, a*b, axbjak w COUNTIFkażdy wpis, w tym abcjak w COUNTIF
a~btylko a~bab, ABab, AB, abc, abcbab, AB
a~*btylko a*btylko a*btylko a*btylko a*b
=abab, ABnie dotyczyab, ABnie dotyczy

Dwa wiersze zasługują na drugie spojrzenie. W wierszu a~b COUNTIF i MATCH lądują na różnych komórkach, a tyld w ręcznie wpisywanych kodach jest więcej, niż ktokolwiek planuje. W wierszu a*b abc pasuje dla DSUM i nie pasuje dla COUNTIF, bo funkcja bazodanowa po cichu dokleja * do kryterium. Wpis DSUM dla a~b wynika z tej samej doklejonej gwiazdki: skoro tam jest, kryterium jest wzorcem z wildcardami, a ~b jest po prostu escapowanym b

Kiedy COUNTIF włącza tryb wildcardów?

COUNTIF włącza tryb wildcardów tylko wtedy, gdy tekst kryterium zawiera * lub ?, escapowane czy nie. Bez żadnego z tych znaków kryterium jest porównywane z każdą komórką jako cały łańcuch, bez rozróżniania wielkości liter, a tylda jest zwykłym znakiem, więc COUNTIF(A1:A7,"a~b") liczy komórkę, która dosłownie trzyma a~b. Jedna gwiazdka odwraca znaczenie. W "a~b*" tylda escapuje teraz b, wzorzec czyta się jako „ab i potem cokolwiek", a komórka a~b wypada z licznika. HotXLS stosuje tę bramkę w COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS, a także w funkcjach bazodanowych

Diagram bramki wildcardów w HotXLS: COUNTIF i SUMIF stosują wildcardy tylko wtedy, gdy kryterium zawiera gwiazdkę albo znak zapytania, więc a~b liczy komórkę dosłowną i zwraca 1, a MATCH typu 0 i XLOOKUP trybu 2 są zawsze w trybie wildcardów, więc a~b znajduje ab na pozycji 2
cała różnica siedzi w bramce: COUNTIF żąda gwiazdki albo znaku zapytania, zanim potraktuje tyldę jako ucieczkę, MATCH nigdy nie pyta, więc jeden wzorzec liczy jedną komórkę, a znajduje drugą

Wewnątrz trybu wildcardów reguła escape jest krótka. ~ czyni dosłownym następny znak, cokolwiek to jest, więc ~b znaczy b, a ~~ znaczy jedną tyldę; tylda na samym końcu wzorca jest porzucana, 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]" nie liczy nic na zwykłych danych. Najszybszy sposób sprawdzenia tego na własnych danych to TXLSXWorkbook.Calculate, które ewaluuje łańcuch formuły na aktywnym arkuszu i zwraca Variant; świeży skoroszyt czyni pierwszy dodany arkusz aktywnym

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');            // pierwszy arkusz, więc jest aktywny
    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 ... więc suma SUMIF wskaże swoje wiersze
    end;
    Sheet.Cells[8, 1].Value := 5;                // liczba; A9 zostaje pusta

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    brak * i ?: zwykły 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 po całym łańcuchu, abc wykluczone
    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    liczba 5 i pusta A9 się liczą
    Show('=COUNTIF(A1:A9,"<>")');       // 8    komórki niepuste
  finally
    Book.Free;
  end;
end.

Co tak naprawdę liczy „<>text"

Kryterium "<>text" liczy każdą komórkę, która nie jest tym tekstem, a w Excelu wchodzi w to liczby, wartości logiczne, wartości błędów i puste komórki. Gołe "<>" pyta o coś zupełnie innego: „niepusta komórka". Pomija puste komórki, ale liczy każdą wartość, łącznie z pustym tekstem, który zwraca formuła typu ="". Pułapka Delphi siedzi w porównaniu Variant. Porównanie 'ab' z numeryczną komórką sprawia, że Delphi próbuje skonwertować 'ab' na liczbę, konwersja się wykrusza, a handler połykający wyjątek jako „brak dopasowania" wyciąga komórki numeryczne z licznika. HotXLS je liczy, więc dwie ostatnie linie przykładu zwracają 7 i 8. Strona pustych komórek tego zagadnienia, łącznie z tym, do czego pusty operand jest równy w zwykłym porównaniu, jest opisana w artykule o łańcuchach porównań, pustych komórkach i SUMIF w HotXLS

Dlaczego MATCH znajduje ab, gdy szukasz a~b?

MATCH z typem dopasowania 0 i XLOOKUP z match_mode 2 są zawsze w trybie wildcardów, więc tylda jest escape'em nawet wtedy, gdy wzorzec nie zawiera ani *, ani ?. Na dwukomórkowym zakresie z otwarcia MATCH("a~b",D1:D2,0) zwraca 2. Na zakresie trzymającym tylko a~b to samo wywołanie zwraca #N/A. Wyszukanie dosłownego tekstu a~b wymaga "a~~b". Gdy klucze wyszukiwania pochodzą z wejścia użytkownika albo z zewnętrznego systemu i mogą zawierać tyldę, podwój każdą tyldę przed zbudowaniem formuły; ten jeden StringReplace jest tańszy niż późniejsze tłumaczenie brakującego wiersza

HotXLS trzyma te dwie decyzje osobno. MATCH, XLOOKUP i funkcje kryteriów dzielą jeden matcher z cofaniem, z tą samą obsługą escape i tą samą regułą końcowej tyldy; różni się bramka przed nim. Ścieżka kryteriów najpierw pyta, czy tekst zawiera * albo ?. Ścieżka wyszukująca nigdy nie pyta. Jeden wspólny punkt wejścia „dopasuj wzorzec" dla obu rodzin trafiałby jedną rodzinę dobrze, drugą źle — niezależnie od ustawienia bramki. Wyszukiwania z wildcardami niosą jeden warunek więcej: XLOOKUP zwraca #VALUE!, gdy match_mode 2 łączy się z trybem wyszukiwania binarnego, co jest opisane w przewodniku HotXLS po trybach wyszukiwania XLOOKUP i XMATCH

Kryteria DSUM: goły tekst znaczy „zaczyna się od"

DSUM i pozostałe funkcje bazodanowe czytają tekstowe kryterium bez wiodącego =, < albo > jako prefiks, przy nadal aktywnych wildcardach. To reguła Advanced Filter i celowo różni się od COUNTIF. Nad kolumną Name z abc, ab, xab i AB kryterium ab pasuje do abc, ab i AB, podczas gdy =ab pasuje tylko do ab i AB. <>ab to nierówność całego wpisu, a*b i a? też są wzorcami prefiksowymi, a >ab to zwykłe porównanie tekstowe. xab nigdy nie pasuje do ab; reguła brzmi „zaczyna się od", nie „zawiera"

Niewygodna strona dla implementacji: parser kryteriów, który zdejmuje operator, widzi ab i =ab jako ten sam test równości. Rozróżnia je tylko surowy tekst kryterium. HotXLS dlatego patrzy na surowy tekst, zanim zaufa sparsowanemu warunkowi: tekstowe kryterium, którego pierwszy znak nie jest =, < ani >, dostaje doklejoną * i przechodzi przez matcher wildcardów, a wszystko inne zachowuje porównanie całego wpisu

Diagram HotXLS reguły kryterium DSUM: gołe kryterium tekstowe dostaje doklejoną gwiazdkę i pasuje jako prefiks, więc ab sięga ab, AB, abc i abcb, kryterium równości porównuje cały wpis, kryterium z nawiasem ostrym wyklucza oba, a tylda z gwiazdką przetrwa jako dosłowne a*b, z wynikowymi sumami DSUM 30, 6, 121 i 32
Excel odziedziczył regułę Advanced Filter dla funkcji bazodanowych: goły tekst znaczy „zaczyna się od", podczas gdy wiodący znak równości albo nierówności porównuje cały wpis; HotXLS ogląda surowy tekst kryterium, zanim zaufa sparsowanemu warunkowi

Zbudowanie zakresu kryteriów w kodzie ma jedną pułapkę specyficzną dla silnika. W silniku XLSX przypisanie łańcucha '=ab' do TXLSXCell.Value zapisuje tekst, czyli dokładnie to, czego potrzebuje komórka kryterium. Klasyczny silnik TXLSWorkbook kompiluje wartość łańcuchową zaczynającą się od = jako formułę, chyba że poprzedzisz ją apostrofem. Dla zakresów kryteriów budowanych w runtime silnik XLSX jest wyborem mniej podatnym na błędy. Pętla poniżej podmienia pięć kryteriów w D2 i za każdym razem przelicza to samo DSUM

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;

Kryteria porządkujące typu >ab podlegają jeszcze innej regule. Zwykłe porównanie po punktach kodu daje dla "a~b">"ab" wynik TRUE, bo ~ siedzi nad wszystkimi literami w Unicode. Excel stawia interpunkcję przed literami i zwraca FALSE. Na Windows HotXLS ewaluuje tekstowe kryteria > i <, zwykłe porównanie tekstowe i sortowanie przy użyciu systemowego collation sortowania słów dla bieżących ustawień regionalnych użytkownika, więc wszystkie trzy zgadzają się między sobą i z Excelem

Find na całe komórki w Delphi: cofanie do ostatniej gwiazdki

Kuszący skrót dla Find na całych komórkach to użycie matchera częściowego stojącego za Replace z żądaniem, by dopasowanie pokryło całą komórkę. Na zwykłych danych to zawodzi. Matcher częściowy wraca, jak tylko wzorzec się wyczerpie, więc a*b przeciw abcb zatrzymuje się po ab — skonsumowane 2 z 4 znaków — i komórka jest odrzucana. HotXLS przepuszcza Find na całych komórkach przez osobny matcher, który traktuje „wzorzec się skończył, tekst nie" jako kolejne niedopasowanie i ponawia od ostatniej gwiazdki. Dzięki temu a*b pasuje do abcb, a a?b*b pasuje do axbyb, jak Find Excela z zaznaczonym „Match entire cell contents"

Diagram HotXLS cofania przy Find z wildcardami na całej komórce: wzorzec a*b konsumuje a i b w komórce abcb, a matcher zatrzymujący się po wyczerpaniu wzorca odrzuca komórkę, podczas gdy matcher HotXLS traktuje skończony wzorzec z pozostałym tekstem jako kolejne niedopasowanie i cofa do ostatniej gwiazdki, aż pasuje cała komórka
Dopasowanie całej komórki nie jest skończone w chwili, gdy wyczerpie się wzorzec; potraktowanie resztki tekstu jako kolejnego niedopasowania odsyła matchera do ostatniej gwiazdki i tak a*b sięga abcb, jak Find w Excelu

Find Excela, w trybie całej komórki i częściowym, traktuje ~ jako escape przed dowolnym następnym znakiem: a~b znajduje ab, a~~b znajduje a~b, a końcowa tylda jest ignorowana, więc q~ zachowuje się jak q. Matcher znający tylko ~*, ~? i ~~ sprawiłby, że a~b znajdzie tekst a~b — komórkę po przeciwnej stronie. HotXLS escapuje dowolny znak po tyldzie, w Find i Replace tak samo jak w formułach

W silniku XLSX to wywołanie TXLSXWorksheet.FindText z zestawem TXLSXFindOptions. lxfUseWildcards włącza *, ? i ~; lxfWholeCell wymaga dopasowania całej komórki; lxfMatchCase czyni porównanie czułym na wielkość liter. Bez lxfUseWildcards każdy znak, gwiazdka włącznie, jest dosłowny. Find patrzy wyłącznie na wartości tekstowe. Komórki numeryczne są pomijane, a komórki formuł są pomijane, dopóki nie ustawi się lxfSearchFormulas — wtedy przeszukiwany jest tekst formuły. lxfBackward i lxfByColumns zmieniają kolejność skanu. Kotwica wyznaczona przez StartRow i StartCol jest wliczana, więc pętla Find All musi skakać 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 zgłasza wszystkie cztery wiersze, w tym abc, bo w trybie częściowym a*b musi tylko wystąpić gdzieś w komórce. FindTextIn i ReplaceTextIn przyjmują te same opcje plus okno FirstRow, FirstCol, LastRow, LastCol — programową wersję szukania w ramach zaznaczenia; podaj 0 dla dowolnej granicy, by zostawić ją otwartą. Klasyczny silnik wystawia te same reguły dopasowania przez przeciążenie z trzema wartościami logicznymi, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus pasujące przeciążenie ReplaceText. Jego wyniki wiersza i kolumny są liczone od 1 i, jak wersja XLSX, pomijają komórki formuł oraz wartości nietekstowe

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;

Dlaczego maska plikowa DOS to zły matcher dla kryteriów Excela

Matcher masek plikowych, taki jak MatchesMask, mówi innym językiem niż wildcardy Excela, choć oba pokrywają się na * i ?. To pokrycie jest dokładnie powodem, dla którego bywa wybierany, i dlaczego jego błędy pozostają ukryte, dopóki dane nie staną się ciekawe:

  • [x] jest czytane jako zestaw znaków, więc COUNTIF(A1:A10,"[x]") liczyłby komórki z x zamiast tekstu w nawiasach, a "[a-z]" pasowałby do każdej jednoznakowej komórki
  • Nie ma escape tyldą, więc "a~*b" nie zdoła dopasować dosłownej gwiazdki
  • Zniekształcona maska, jak niedomknięty nawias, podnosi wyjątek, który wywołujący łatwo połknie jako „brak dopasowania", zamieniając literówkę w kryterium na złą sumę
  • Po stronie wyszukiwania zestaw escape ograniczony do ~*, ~? i ~~ też zawodzi: MATCH("a~b",…,0) ma znaleźć ab, nie dosłowne a~b

Jeśli twoje skoroszyty używają wyłącznie * i ? na zwykłych danych alfanumerycznych, wszystkie te matchery się zgadzają i nic z tego nie ma znaczenia. Nawiasy, tyldy, kolumny mieszanych typów pod "<>text" i kryteria DSUM zapisane jako gołe słowa to miejsca, gdzie sumy prostszego matchera dryfują od Excela — i to przypadki, które ćwiczy kod powyżej. Zapisane filtry podnoszą pokrewne pytanie o to, jak Excel przechowuje kryterium versus jak je porównuje — omawia je artykuł HotXLS o kryteriach DOPER AutoFiltra w BIFF8

HotXLS ewaluuje COUNTIF, MATCH, XLOOKUP, DSUM i resztę swojej biblioteki funkcji natywnie w Delphi i C++Builder, w klasycznym silniku i silniku XLSX, bez instalacji Excela na maszynie. Edycje, dokumentacja i build próbny są wypisane na stronie komponentu arkusza HotXLS dla Delphi