HotXLS Excel wildcardok: COUNTIF, MATCH, DSUM és Find

A HotXLS Delphi Component ugyanazt a mintaszöveget négyféleképpen olvassa, mert az Excel 16 is így teszi. A COUNTIF-ban és SUMIF-ban az a~b szöveg literális, hacsak a feltétel nem tartalmaz *-ot vagy ?-t is; a MATCH-ban és XLOOKUP wildcard módban a hullámjel mindig escape, így az a~b megtalálja az ab-et; a DSUM-ban és a többi adatbázisfüggvényben a sima szöveg „kezdete" jelent, és az egész-cellás Findnek vissza kell lépnie az utolsó *-ba. A HotXLS ezeket a lemért szabályokat követi v2.384.52, v2.384.60 és v2.384.64 óta

A hibajelentések ezen a területen soha nem emlegetnek wildcardot. Azt írják, hogy egy szerver által generált riport pár sorral kevesebbet számol, mint ugyanaz a fájl Excelben újraszámolva, vagy hogy egy hullámjeles cikkszámot az egyik képlet megtalál, a következő ignorálja. Az ok egy illesztő, ami azt hiszi, hogy egy minta mindenhol ugyanazt jelenti. Az Excel nem így működik, tehát egy olyan engine sem működhet így, aminek a cache-elt eredményeinek egyezniük kell az Excelel. v2.384.52 előtt a HotXLS minden feltételt egy DOS-stílusú fájlmaszkon vezetett át, ami a hétköznapi mintákat jól, a határeseteket csendben rosszul kezelte

Miért jelent négyféle dolgot ugyanaz a mintaszöveg Excelben?

Ugyanaz a mintaszöveg azért jelent négyféle dolgot, mert az Excel négy funkciótól örökölt négy illesztési szabályt, és soha nem egységesítette őket. A feltételfüggvények (COUNTIF, SUMIF, AVERAGEIF és a *IFS család) feltételenként döntik el, hogy a wildcardok élnek-e egyáltalán. A keresőfüggvények (a 0 match típusú MATCH, a 2 match_mode-os XLOOKUP) mindig alkalmazzák őket. Az adatbázisfüggvények (DSUM, DCOUNTA és társaik) az Advanced Filtert követik, ahol a csupasz szó előtag. A Find párbeszédnek megvannak a maga egész-cellás és részleges módjai. Az alábbi táblázat felsorolja, mely cellák egyeznek mindegyik mintával egy olyan oszlopon, ami a~b, ab, AB, abc, abcb, a*b és axb értékeket tart, mindegyik függvény alapértelmezett, kis- és nagybetű nélküli módjában

Minta	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP 2. mód	DSUM feltétel	Find, egész cella, wildcarddal
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	mint a COUNTIF	minden bejegyzés, abc-vel együtt	mint a COUNTIF
a~b	csak a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	csak a*b	csak a*b	csak a*b	csak a*b
=ab	ab, AB	nem alkalmazható	ab, AB	nem alkalmazható

Az a~b sor az, ahol a COUNTIF és a MATCH eltér, és cikkszámok meg kézzel ütött kódok gyakrabban tartalmaznak hullámjelet, mint bárki várná. Az a*b sor a másik csapdát mutatja: az abc DSUM-ra egyezik, COUNTIF-ra nem, mert az adatbázisfüggvény csendben hozzáfűz egy *-ot. Az ab, a*b és =ab DSUM bejegyzések egyből Excel 16 futásokból jönnek; az a~b DSUM bejegyzése ugyanebből az előtagszabályból következik, mivel a hozzáfűzött * wildcard mintává alakítja a feltételt, amiben a ~b egy escaped b

Mikor vált wildcard módba a COUNTIF?

A COUNTIF csak akkor vált wildcard módba, ha a feltételszöveg *-ot vagy ?-t tartalmaz, escapedet vagy sem. A karakterek nélkül az Excel a feltételt egész stringként hasonlítja minden cellához, kis- és nagybetű nélkül, és a hullámjel csak hullámjel, így a COUNTIF(A1:A7,"a~b") azt a cellát számolja, ami literálisan a~b-t tart. Adj hozzá egyetlen csillagot, és az értelme átfordul: az "a~b*"-ban a hullámjel most már az b-t escape-eli, a minta úgy olvasható, hogy „ab, majd bármi", és az a~b cella már nem számolódik. A HotXLS ezt a szabályt mindkét engineben alkalmazza v2.384.52 óta, egyetlen feltétel-illesztőn át a lxCalc-ban, amit a COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS meg az adatbázisfüggvények osztanak meg

Wildcard módon belül az escape szabályok ugyanazok, mint az Excel többi helyén: a ~ a következő karaktert literálissá teszi, legyen az bármi, így a ~b b-t jelent, a ~~ egy hullámjelet, és a minta legvégén álló hullámjelet eldobják, így az "a*~" úgy viselkedik, mint az "a*". A szögletes zárójelek soha nem különlegesek. Egy "[x]" feltétel azokat a cellákat számolja, amik a [x] három karaktert tartják, és az "[a-z]" rendes adaton semmit sem számol. A TXLSXWorkbook.Calculate képletszöveget értékel az aktív sheeten, és Variant-ot ad vissza, a leggyorsabb módja annak, hogy ezeket a szabályokat a saját adataidon ellenőrizd

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 ... így egy SUMIF összeg megnevezi a sorait
    end;
    Sheet.Cells[8, 1].Value := 5;                // egy szám; az A9 üres marad

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    nincs * vagy ?: sima szöveg, az a~b cella
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    wildcard mód: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    egész-string wildcard, az abc kimarad
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  minden sor az abc (8) kivételével
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    a literális a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    az 5-ös szám és az üres A9 is számol
    Show('=COUNTIF(A1:A9,"<>")');       // 8    nem üres cellák
  finally
    Book.Free;
  end;
end.

Mit számol a „<>text"?

Egy "<>text" feltétel minden olyan cellát számol, ami nem az a szöveg, és Excel 16-ban ez beleszámítja a számokat, booleaneket, hibaértékeket meg üres cellákat is. Egy csupasz "<>" teljesen más kérdés: azt jelenti, hogy „nem üres cella", tehát az üres cellákat kihagyja, de minden értéket számol, beleértve azt az üres szöveget is, amit egy ="" szerű képlet ad vissza. A régi HotXLS kód a szövegcellákat jól, a számokat nem kezelte: egy Variant egyenlőtlenség miatt a Delphi a 'ab'-et számmá akarta váltani, a konverzió kivételt dobott, egy handler „nincs találat"ként lenyelte, és a numerikus cellák csendben kiestek a számlálásból. A történet ürescella oldalát, beleértve azt is, mivel egyenlő egy üres operandus rendes összehasonlításban, a hogyan kezeli a HotXLS az összehasonlítási láncokat, üres cellákat és a SUMIF-et fedi le

Miért találja meg a MATCH az ab-et, ha a~b-re keresel?

A MATCH azért találja meg az ab-et, amikor a~b-re keresel, mert a 0 match típusú MATCH és a 2 match_mode-os XLOOKUP mindig wildcard módban van, így a hullámjel akkor is escape, ha a mintában nincs * vagy ?. Az Excel 16 egy a~b-t és ab-t tartó kétcellás tartományon megerősíti: a MATCH("a~b",D1:D2,0) 2-t ad, és egy csak a~b-t tartó tartományon ugyanez a hívás #N/A-t. A literális a~b szöveg felkutatásához "a~~b"-t kell írnod. Közben a COUNTIF(D1:D2,"a~b") ugyanezeken a két cellán 1-et ad, a másik cellát számolva. Ugyanaz a string, ugyanaz a tartomány, ellenkező cella

Ezért tartja a HotXLS a két döntést külön, nem egyetlen „minta illesztése" belépő mögött. Maga az illesztő közös: v2.384.52 óta a MATCH, XLOOKUP és a feltételfüggvények ugyanazt a visszalépéses illesztőt futtatják, ugyanazzal az escape kezeléssel és ugyanazzal a záró hullámjel szabállyal. Ami más, az a kapu előtte. A feltételút előbb azt kérdezi, „tartalmaz-e ez a szöveg *-ot vagy ?-t"; a keresőút soha nem kérdezi. A kettő összemosása az egyik családot javítaná, a másikat eltörné, és mindkét irányt mindkét engineben Excel 16 értékekkel ellenőrzik. A wildcard kereséseknek megvan a maga előfeltétele is: az XLOOKUP elutasítja a wildcard illesztést bináris keresési móddal kombinálva, egy szabály, amit a HotXLS útmutatója az XLOOKUP és XMATCH keresési módokhoz ír le

Hogyan olvassa a DSUM és az adatbázisfüggvények a sima szövegfeltételt?

A DSUM és a többi adatbázisfüggvény egy =, < vagy > kezdőjel nélküli szövegfeltételt „kezdete" jelentéssel olvas, wildcardokkal továbbra is aktívan. Ez az Advanced Filter szabálya, és szándékosan tér el a COUNTIF-tól. Az Excel 16 egy abc, ab, xab, AB, a~b és a*b értékeket tartó Name oszlopon mérve: az ab feltétel az abc, ab és AB-et találja el; az =ab csak az ab-et és az AB-et; a <>ab egész-bejegyzéses egyenlőtlenség; az a*b és az a? szintén előtagminták; a >ab rendes összehasonlítás. v2.384.64 előtt a HotXLS az ab-et pontosan illesztette, így a DSUM ezen a tesztadaton 10-et adott, ahol az Excel 11-et ad

A javításnak meg kellett kerülnie a feltételparsert, ami az ab-et és az =ab-et is ugyanabba az egyenlőségi feltételbe hajtogatja. Ezért a HotXLS a nyers feltételszöveget nézi meg, mielőtt megbízik a parzolt feltételben: egy olyan szövegfeltétel, aminek az első karaktere nem =, < vagy >, kap egy hozzáfűzött *-ot, és átmegy a wildcard illesztőn, minden más megtartja az egész-bejegyzéses összehasonlítását. Egy gyakorlati megjegyzés, amikor kódban építesz feltételtartományokat: az XLSX engineben a '=ab' string hozzárendelése a TXLSXCell.Value-hoz szöveget tárol, míg a klasszikus TXLSWorkbook engine =-tal kezdődő értéket képletként kompilál, hacsak nem elé teszel aposztráfot

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';            // feltételfej a D1-ben
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // szöveg marad az XLSX engine-ben
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (kezdete)
    // =ab  -> 6    ab, AB (teljes bejegyzés)
    // <>ab -> 121  minden az ab és AB kivételével
    // a*b  -> 127  az a*b* mind a hetet eltalálja, abc-vel együtt
    // a~*  -> 32   csak a literális a*b
  finally
    Book.Free;
  end;
end;

Egy rokon különbség túlélte az előtagjavítást, és öregebb buildeken számít. Az olyan szövegösszehasonlítások, mint a >ab, kódpontsort használtak, miközben az Excel az írásjeleket a betűk elé teszi, így a "a~b">"ab" Excelben FALSE, HotXLS-ben TRUE volt. v2.384.67 óta a > és < feltételek, a rendes szövegösszehasonlítással és rendezéssel együtt, az Excel word-sort collationjét használják az aktuális user locale alatt, és a kettő megint egyetért

Miért nem találta meg az egész-cellás Find az abcb-t?

Az egész-cellás Find azért nem találta meg az abcb-t, mert az illesztő ott állt meg, ahol a minta elfogyott, ahelyett hogy visszalépett volna az utolsó *-ba. A Replace mögötti részleges-illesztő azonnal visszaad, amint a minta kimerült; az egész-cellás Find újrahasznosította, majd megkövetelte, hogy a találat a teljes cellát fedje: az a*b az abcb-n az ab után megállt, 2 karaktert evett meg a 4-ből, és elutasításra került. v2.384.60 óta az egész-cellás illesztő külön implementáció, ami a „minta végzett, a szöveg nem" állapotot egy újabb eltérésként kezeli, és az utolsó csillagtól újrapróbál, így az a*b eltalálja az abcb-t, az a?b*b pedig az axbyb-t, ahogy az Excel 16 Findje teszi „A teljes cellatartalom egyezése" pipálva

Ugyanez a kiadás megváltoztatta a hullámjelet is. Az Excel 16 Findje egész-cellás és részleges módban egyaránt ~-t escape-ként kezel bármely következő karakterre: az a~b megtalálja az ab-et, az a~~b megtalálja az a~b-et, és a záró hullámjelet ignorálja, így a q~ úgy viselkedik, mint a q. Az öregebb HotXLS illesztő csak ~*-ot, ~?-t és ~~-t ismert escapeként, így az a~b megtalálta az a~b szöveget. Egyetlen ~-ból álló Find minta magában az Excelben instabil, bármely cellát talál üres mintaként, és a HotXLS nem utánozza ezt

Az XLSX engineben a keresés a TXLSXWorksheet.FindText, TXLSXFindOptions halmazzal: az lxfUseWildcards bekapcsolja a *, ? és ~ jeleket, az lxfWholeCell megköveteli, hogy a teljes cella egyezzen, az lxfMatchCase pedig kis- és nagybetű-érzékennyé teszi az összehasonlítást. lxfUseWildcards nélkül minden karakter, a csillag is, literális. A Find csak szövegértékeket néz; a numerikus cellák kimaradnak, a képletcellák pedig addig, amíg az lxfSearchFormulas nincs beállítva, ekkor a képletszöveg kerül keresésre. A StartRow és StartCol által adott horgony inkluzív, így egy Find All ciklus minden találat után egy oszlopot lép tovább

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: az abc elutasítva, az abcb visszalép
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: a ~b egy escaped b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: a ~~ egy literális hullámjel

    // Részleges találat, Find All: a horgonycella beleszámít, ezért lépj túl mindegyik találaton
    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 és 4. sorok
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Az egész-cellás wildcard csere csak a literális a~b-t írja át
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

A részleges ciklus mind a négy sort megtalálja, az abc-t is, mert részleges módban az a*b-nek csak a cellán belül valahol előfordulnia kell. A FindTextIn és ReplaceTextIn ugyanazokat az opciókat veszi át, plusz egy FirstRow, FirstCol, LastRow, LastCol ablakot, a kijelölésen belüli keresés programozott megfelelője. A klasszikus engine ugyanezeket a szabályokat egy három booleanes overloadon át adja, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), meg egy illeszkedő ReplaceText overloaddal, egyes-alapú sor és oszlop eredményekkel:

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;

Mit rontott el a régi DOS-maszkos illesztő?

A régi illesztő a speciális karaktereket rontotta el, mert egy DOS fájlmaszk más nyelv, mint egy Excel wildcard. v2.384.52 előtt a feltételfüggvények és az adatbázisfüggvények minden mintát a MatchesMask-nak adtak át, egy fájlmaszk-illesztőnek a lxMasks unitban. A szintaxisa hétköznapi esetekben átfed az Excellel, ezért maradt rejtve a probléma, de ott tér el, ahol a valódi adat érdekessé válik:

• A [x]-et karakterhalmazként olvasta, így a COUNTIF(A1:A10,"[x]") az x-et tartó cellákat számolta a zárójeles szöveg helyett, és az "[a-z]" bármely egybetűs cellát talált
• Nem volt hullámjel-escape, így az "a~*b" nem tudott literális csillagot találni
• Egy formátlan maszk, mint egy le nem zárt zárójel, kivételt dobott, amit a hívó „nincs találat"ként nyelt le, így egy elütés a feltételben csendesen rossz összeggé vált
• A kereső oldalon a MATCH és XLOOKUP csak ~*-ot, ~?-t és ~~-t kezelt escapeként, így a MATCH("a~b",…,0) a literális a~b-t találta meg az ab helyett

Ha a munkafüzeteid csak *-ot és ?-ot használtak sima alfanumerikus adaton, az eredmények már jók voltak, és nem változnak. Ha zárójeleket, hullámjeleket, vegyes típusú oszlopokat "<>text" alatt, vagy csupasz szavakkal írt DSUM feltételeket tartalmaznak, újraszámolásuk v2.384.64-gyel vagy újabbal változtathatja az összegeket, és az új összegek azok, amiket az Excel mutat. Ugyanez a különbség aközött, ahogy az Excel tárol egy feltételt, és ahogy összehasonlítja, a mentett szűrőknél is felmerül, amit a HotXLS cikk a BIFF8 AutoFilter DOPER feltételekről tárgyal

Gyorsreferencia: Excel wildcard szabályok a HotXLS-ben

• A COUNTIF, SUMIF, AVERAGEIF és a *IFS család csak akkor használ wildcardot, ha a feltétel *-ot vagy ?-t tartalmaz; egyébként egész stringeket hasonlít kis- és nagybetű nélkül, és a ~ literális (v2.384.52 óta)
• A 0 match típusú MATCH és a 2 match_mode-os XLOOKUP mindig wildcardot használ, így az a~b megtalálja az ab-et, a literálisnak pedig a~~b kell (v2.384.52 óta)
• Wildcard módban a ~ bármely következő karaktert escape-eli, és a záró ~-et eldobják; a [ és ] rendes karakterek
• A "<>text" számokat, booleaneket, hibákat és üres cellákat számol; egy csupasz "<>" a nem üres cellákat számolja, ="" eredményekkel együtt
• A DSUM és a többi adatbázisfüggvény a sima szöveget „kezdete"-ként kezeli; az =text és <>text a teljes bejegyzést hasonlítja (v2.384.64 óta)
• Az lxfUseWildcards-szal és lxfWholeCell-lel futó egész-cellás Find visszalép, így az a*b eltalálja az abcb-t; a Find és Replace a ~-t bármely karakter escape-eként kezeli (v2.384.60 óta)
• A szövegsorrend a > és < feltételekben az Excel word-sort collationjét követi, írásjelek a betűk előtt (v2.384.67 óta)

Az Excel kompatibilitás egy képletengine-ben főleg ilyen határesetekből áll, Excelel szemben lemérve, nem dokumentációból kitalálva. A HotXLS a COUNTIF-ot, MATCH-ot, XLOOKUP-ot, DSUM-ot és a függvénykönyvtára többi részét natívan értékeli Delphiből és C++Builderből, mind a klasszikus, mind az XLSX engineben, Excel telepítése nélkül. Részletek, kiadások és a próbaverzió letöltése a HotXLS Delphi spreadsheet component oldalon