HotXLS Excel wildcards: COUNTIF, MATCH, DSUM и Find

HotXLS Delphi Component чете един и същ pattern низ по четири различни начина, защото точно това прави Excel 16. В COUNTIF и SUMIF текстът a~b е литерал, освен ако критерият не съдържа още * или ?; в MATCH и XLOOKUP в wildcard режим тилдата винаги е escape, така че a~b намира ab; в DSUM и останалите database функции чистият текст значи „започва с“, а Find по цели клетки трябва да се връща назад до последния *. HotXLS следва тези измерени правила от v2.384.52, v2.384.60 и v2.384.64 насам

Бъг репортите в тази област никога не споменават wildcards. Оплакват се, че генериран от сървър отчет брои няколко реда по-малко от същия файл, преизчислен в Excel, или че артикулен номер с тилда се намира от една формула, а следващата го пренебрегва. Причината е matcher, който предполага, че един pattern значи едно и също навсякъде. Excel не работи така, а двигател, чиито кеширани резултати трябва да съвпадат с Excel, не може да си позволи друга крайност. Преди v2.384.52 HotXLS прокарваше всеки критерий през файлова маска в DOS стил — тя уцелваше обичайните pattern-и, но тихо грешеше точно по граничните случаи

Защо един и същ pattern значи четири различни неща в Excel?

Един pattern значи четири различни неща, защото Excel е наследил четири правила за мачване от четири функционалности и никога не ги е уеднаквил. Criteria функциите (COUNTIF, SUMIF, AVERAGEIF и семейството *IFS) решават за всеки критерий дали изобщо прилагат wildcards. Lookup функциите (MATCH с match type 0, XLOOKUP с match_mode 2) ги прилагат винаги. Database функциите (DSUM, DCOUNTA и компания) следват Advanced Filter, където голата дума е префикс. Find диалогът има собствени режими за цяла клетка и за частично съвпадение. Таблицата по-долу показва кои клетки мачва всеки pattern срещу една колона, съдържаща a~b, ab, AB, abc, abcb, a*b и axb, като всички функции са в своя default регистронечувствителен режим

Pattern	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP режим 2	DSUM критерий	Find, цяла клетка, wildcards включени
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	като при COUNTIF	всеки запис, включително abc	като при COUNTIF
a~b	само a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	само a*b	само a*b	само a*b	само a*b
=ab	ab, AB	не се прилага	ab, AB	не се прилага

Редът на a~b е този, в който COUNTIF и MATCH се разминават, а артикулните номера и ръчно въведените кодове съдържат тилди по-често, отколкото някой би очаквал. Редът на a*b показва другия капан: abc мачва за DSUM, но не и за COUNTIF, защото database функцията тихо долепя *. Записите за DSUM при ab, a*b и =ab са направо от прогони в Excel 16; записът за a~b следва от същото правило за префикс, защото долепеното * превръща критерия в wildcard pattern, в който ~b е escaped b

Кога COUNTIF превключва в wildcard режим?

COUNTIF превключва в wildcard режим само когато текстът на критерия съдържа * или ?, escaped или не. Без нито един от двата знака Excel сравнява критерия с всяка клетка като цял низ, без значение на регистъра, а тилдата е просто тилда, така че COUNTIF(A1:A7,"a~b") брои клетката, която буквално съдържа a~b. Добавете една звездичка и значението се обръща: в "a~b*" тилдата вече escape-ва b, pattern-ът се чете като „ab, следвано от каквото и да е“, а клетката a~b вече не се брои. HotXLS прилага това правило и в двата двигателя от v2.384.52 насам, чрез един criteria matcher в lxCalc, споделен от COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS и database функциите

Вътре в wildcard режима escape правилата са същите като навсякъде другаде в Excel: ~ прави следващия знак литерал, какъвто и да е той, така че ~b значи b, а ~~ значи една тилда, а тилда на самия край на pattern-а се отхвърля, така че "a*~" се държи като "a*". Квадратните скоби никога не са специални. Критерий "[x]" брои клетките, които съдържат трите знака [x], а "[a-z]" не брои нищо върху обикновени данни. TXLSXWorkbook.Calculate преизчислява низ с формула върху активния лист и връща Variant — най-бързият начин да проверите тези правила върху собствените си данни

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 ... така сумата от SUMIF сочи включените редове
    end;
    Sheet.Cells[8, 1].Value := 5;                // число; A9 остава празна

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    без * или ?: чист текст, клетката a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard режим: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    wildcard върху целия низ, без abc
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  всички редове освен abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    литералът a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    числото 5 и празната A9 се броят
    Show('=COUNTIF(A1:A9,"<>")');       // 8    непразните клетки
  finally
    Book.Free;
  end;
end.

Какво брои "<>text"?

Критерий "<>text" брои всяка клетка, която не е този текст, а в Excel 16 това включва числа, булеви стойности, грешки и празни клетки. Гол "<>" е съвсем друг въпрос: значи „не е празна клетка“, така че прескача празните клетки, но брои всяка стойност, включително празния текст, който формула като ="" връща. Старият код на HotXLS се справяше с текстовите клетки, но не и с числата: неравенство върху Variant караше Delphi да преобразува 'ab' в число, конверсията хвърляше exception, някой handler го поглъщаше като „няма съвпадение“, а числовите клетки тихо изчезваха от броя. Страната на тази история, свързана с празните клетки — включително с какво се изравнява празен операнд в обикновено сравнение — е разгледана в как HotXLS се справя със сравнителните вериги, празните клетки и SUMIF

Защо MATCH намира ab, когато търсите a~b?

MATCH намира ab, когато търсите a~b, защото MATCH с match type 0 и XLOOKUP с match_mode 2 са винаги в wildcard режим, така че тилдата е escape дори когато pattern-ът не съдържа нито *, нито ?. Excel 16 го потвърждава върху диапазон от две клетки с a~b и ab: MATCH("a~b",D1:D2,0) връща 2, а върху диапазон, съдържащ само a~b, същото извикване връща #N/A. За да потърсите литералния текст a~b, трябва да напишете "a~~b". Междувременно COUNTIF(D1:D2,"a~b") върху същите две клетки връща 1 и брои другата клетка. Един и същ низ, един и същ диапазон, противоположна клетка

Затова HotXLS държи двете решения разделени, вместо зад един вход „мачни pattern“. Самият matcher е споделен: от v2.384.52 насам MATCH, XLOOKUP и criteria функциите въртят един и същ backtracking matcher, с еднаква обработка на escape-ите и еднакво правило за тилда на края. Разликата е гейтът пред него. Пътят на критериите първо пита „съдържа ли този текст * или ??“; lookup пътят никога не пита. Сливането на двете би оправило едно семейство и би счупило другото, а двете посоки се сверяват със стойности от Excel 16 и в двата двигателя. Wildcard lookup-ите имат и собствено предусловие: XLOOKUP отхвърля wildcard мачване, комбинирано с binary search режим — правило, описано в ръководството на HotXLS за search режимите на XLOOKUP и XMATCH

Как DSUM и database функциите четат критерий от чист текст?

DSUM и останалите database функции четат текстов критерий без водещ =, < или > като „започва с“, като wildcards си остават активни. Това е правилото на Advanced Filter и нарочно се различава от COUNTIF. Прогон в Excel 16 върху колона Name с abc, ab, xab, AB, a~b и a*b: критерият ab мачва abc, ab и AB; =ab мачва само ab и AB; <>ab е неравенство върху целия запис; a*b и a? също са prefix pattern-и; >ab е обикновено сравнение. Преди v2.384.64 HotXLS мачваше ab точно, така че DSUM върху същите тестови данни връщаше 10 там, където Excel връща 11

Поправката трябваше да заобиколи condition parser-а, който сгъва и ab, и =ab в едно и също условие за равенство. Затова HotXLS оглежда суровия текст на критерия, преди да се довери на разпарсеното условие: текстов критерий, чийто първи знак не е =, < или >, получава долепен * и минава през wildcard matcher-а, а всичко останало запазва сравнението си върху целия запис. Една практична бележка, когато градите criteria диапазони в код: в XLSX двигателя задаването на низа '=ab' на TXLSXCell.Value записва текст, докато класическият TXLSWorkbook двигатель компилира стойност, започваща с =, като формула, освен ако не я префиксирате с апостроф

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';            // header на критерия в D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // остава текст в XLSX двигателя
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (започва с)
    // =ab  -> 6    ab, AB (целият запис)
    // <>ab -> 121  всичко освен ab и AB
    // a*b  -> 127  a*b* мачва всичките седем, включително abc
    // a~*  -> 32   само литералът a*b
  finally
    Book.Free;
  end;
end;

Една сродна разлика надживя поправката за префикса и има значение на по-стари версии. Текстовите сравнения от рода на >ab ползваха code-point подредба, докато Excel слага пунктуацията преди буквите, така че "a~b">"ab" е FALSE в Excel и беше TRUE в HotXLS. От v2.384.67 насам критериите с > и <, заедно с обикновеното текстово сравнение и сортирането, ползват word-sort колацията на Excel според текущия user locale и двете отново съвпадат

Защо Find по цели клетки пропускаше abcb?

Find по цели клетки пропускаше abcb, защото matcher-ът спираше на първото място, където pattern-ът се изчерпва, вместо да се върне назад до последната *. Matcher-ът за частично съвпадение зад Replace връща резултат веднага щом pattern-ът се изчерпи; Find по цели клетки го преизползва и после изискваше съвпадението да покрие цялата клетка: a*b срещу abcb спираше след ab, изяждаше 2 от 4-те знака и беше отхвърляно. От v2.384.60 насам whole-cell matcher-ът е отделна имплементация, която третира „pattern-ът свърши, текстът не“ като още едно несъвпадение и опитва отново от последната звездичка, така че a*b мачва abcb, а a?b*b мачва axbyb, точно както прави Find в Excel 16 с отметнато „Match entire cell contents“

Същият release промени и тилдата. Find в Excel 16, както в whole-cell, така и в partial режим, третира ~ като escape за произволен следващ знак: a~b намира ab, a~~b намира a~b, а тилда на края се игнорира, така че q~ се държи като q. По-старият matcher на HotXLS разпознаваше като escape-и само ~*, ~? и ~~, така че a~b намираше текста a~b. Find pattern от един-единствен ~ е нестабилен в самия Excel — мачва всяка клетка като празен pattern — и HotXLS не имитира това

В XLSX двигателя търсенето е TXLSXWorksheet.FindText с множество от тип TXLSXFindOptions: lxfUseWildcards включва *, ? и ~, lxfWholeCell изисква цялата клетка да съвпадне, а lxfMatchCase прави сравнението чувствително към регистъра. Без lxfUseWildcards всеки знак, звездичката включително, е литерал. Find гледа само текстовите стойности; числовите клетки се прескачат, а формулните също, освен ако не е зададен lxfSearchFormulas, при което се търси текстът на формулата. Котвата, зададена с StartRow и StartCol, е включваща, така че Find All цикъл стъпва една колона след всяко попадение

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 отхвърлена, abcb с връщане назад
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b е escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ е една литерална тилда

    // Частично съвпадение, Find All: котвата е включена, затова стъпвай след всяко попадение
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // редове 1, 2, 3 и 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Whole-cell wildcard replace презаписва само литерала a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Частичният цикъл намира и четирите реда, включително abc, защото в partial режим a*b трябва само да се появи някъде вътре в клетката. FindTextIn и ReplaceTextIn приемат същите опции плюс прозорец FirstRow, FirstCol, LastRow, LastCol — програмният еквивалент на търсене в селекция. Класическият двигатель излага същите правила през overload с три булеви стойности, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), плюс съответен ReplaceText overload, с резултати за ред и колона, броени от 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;

Какво грешеше старият matcher с DOS маски?

Старият matcher грешеше със специалните знаци, защото DOS файлова маска е различен език от Excel wildcard. Преди v2.384.52 criteria функциите и database функциите подаваха всеки pattern на MatchesMask — matcher за файлови маски в unit-а lxMasks. Синтаксисът му се застъпва с Excel-ския в обичайните случаи, затова проблемът оставаше скрит, но се разминава точно там, където реалните данни стават интересни:

• [x] се четеше като множество от знаци, така че COUNTIF(A1:A10,"[x]") броеше клетките със x вместо текста в скоби, а "[a-z]" мачваше всяка еднобуквена клетка
• Нямаше escape с тилда, така че "a~*b" не можеше да мачне литерална звездичка
• Повредена маска, например незатворена скоба, хвърляше exception, който извикващият поглъщаше като „няма съвпадение“, и печатска грешка в критерия се превръщаше в тихо грешен сбор
• От страната на lookup функциите MATCH и XLOOKUP третираха като escape-и само ~*, ~? и ~~, така че MATCH("a~b",…,0) намираше литерала a~b вместо ab

Ако работните ви книги са ползвали само * и ? върху чисти буквено-цифрови данни, резултатите вече са били верни и няма да се променят. Ако съдържат скоби, тилди, колони със смесени типове под "<>text" или DSUM критерии, написани като голи думи, преизчислението им с v2.384.64 или по-нова може да смени сборовете — а новите сборове са тези, които показва Excel. Същата разлика между това как Excel съхранява един критерий и как го сравнява излиза наяве при запазените филтри, разгледани в статията на HotXLS за BIFF8 AutoFilter DOPER критериите

Бърза справка: wildcard правилата на Excel в HotXLS

• COUNTIF, SUMIF, AVERAGEIF и семейството *IFS ползват wildcards само когато критерият съдържа * или ?; в противен случай сравняват цели низове без значение на регистъра и ~ е литерал (от v2.384.52 насам)
• MATCH с match type 0 и XLOOKUP с match_mode 2 винаги ползват wildcards, така че a~b намира ab, а за литерала трябва a~~b (от v2.384.52 насам)
• В wildcard режим ~ escape-ва произволен следващ знак, а ~ на края се отхвърля; [ и ] са обикновени знаци
• "<>text" брои числа, булеви стойности, грешки и празни клетки; гол "<>" брои непразните клетки, включително резултатите =""
• DSUM и останалите database функции третират чистия текст като „започва с“; =text и <>text сравняват целия запис (от v2.384.64 насам)
• Find по цели клетки с lxfUseWildcards и lxfWholeCell се връща назад, така че a*b мачва abcb; Find и Replace третират ~ като escape за произволен знак (от v2.384.60 насам)
• Текстовата подредба в критериите с > и < следва word-sort колацията на Excel, пунктуацията преди буквите (от v2.384.67 насам)

Excel съвместимостта в един формулен двигател е предимно куп гранични случаи като тези, измерени срещу Excel, а не отгатнати от документацията. HotXLS преизчислява COUNTIF, MATCH, XLOOKUP, DSUM и останалата си функция библиотека нативно в Delphi и C++Builder, и в двата двигателя — класическия и XLSX — без инсталиран Excel. Детайли, издания и пробното изтегляне са на страницата на HotXLS Delphi spreadsheet component