HotXLS pakaitos simboliai: COUNTIF, MATCH, DSUM ir Find

HotXLS Delphi Component tą pačią šablono eilutę skaito keturiais skirtingais būdais, nes taip ir Excel 16. COUNTIF ir SUMIF tekstą a~b laiko literalu, nebent kriterijus turi ir * arba ?; MATCH ir XLOOKUP pakaitos režime tildė visada yra pabėgimas, tad a~b randa ab; DSUM ir kitos duomenų bazės funkcijos gryną tekstą supranta kaip „prasideda su“; o viso langelio Find turi grįžti į paskutinę *. HotXLS šių išmatuotų taisyklių laikosi nuo v2.384.52, v2.384.60 ir v2.384.64

Klaidų pranešimai šioje srityje niekada nemini pakaitos simbolių. Jie sako, kad serveryje sugeneruota ataskaita suskaičiuoja pora eilučių mažiau negu tas pats failas, Excel perskaičiuotas, arba kad detalės numeris su tilde vienos formulės randamas, o kitos ignoruojamas. Priežastis – atitiktuvas, kuris įsivaizduoja, jog šablonas visur reiškia tą patį. Excel taip nedirba, tad ir variklis, kurio sukaupptieji rezultatai turi sutapti su Excel, negali. Iki v2.384.52 HotXLS kiekvieną kriterijų leisdavo per DOS stiliaus failo kaukę, kuri kasdieninius šablonus pataikydavo, o kraštutinius atvejus tyliai klysdavo

Kodėl viena šablono eilutė Excel reiškia keturis skirtingus dalykus?

Viena šablono eilutė reiškia keturis skirtingus dalykus, nes Excel paveldėjo keturias atitikimo taisykles iš keturių funkcionalumų ir niekada jų nesuvienodino. Kriterijų funkcijos (COUNTIF, SUMIF, AVERAGEIF ir *IFS šeima) sprendžia kiekvienam kriterijui atskirai, ar pakaitos simboliai apskritai taikomi. Paieškos funkcijos (MATCH su match type 0, XLOOKUP su match_mode 2) taiko visada. Duomenų bazės funkcijos (DSUM, DCOUNTA ir bičiuliai) seka Advanced Filter, kur plikas žodis yra priešdėlis. Find dialogas turi savus viso langelio ir dalinio atitikimo režimus. Lentelė žemiau išvardija, kurie langeliai atitinka kiekvieną šabloną viename stulpelyje, laikančiame a~b, ab, AB, abc, abcb, a*b ir axb, kiekvieną funkciją laikant numatytuoju registro neatsižvelgiančiu režimu

Šablonas	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP mode 2	DSUM kriterijus	Find, visas langelis, pakaitos simboliai įjungti
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	kaip COUNTIF	kiekvienas įrašas, su abc	kaip COUNTIF
a~b	tik a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	tik a*b	tik a*b	tik a*b	tik a*b
=ab	ab, AB	netaikoma	ab, AB	netaikoma

a~b eilutė – ta, kur COUNTIF ir MATCH nesutaria, o detalės numeriai ir rankomis renkami kodai tildžių turi dažniau, nei kas nors numanytų. a*b eilutė rodo kitus spąstus: abc atitinka DSUM, bet ne COUNTIF, nes duomenų bazės funkcija tyliai prideda *. DSUM įrašai ab, a*b ir =ab paimti tiesiai iš Excel 16 paleidimų; DSUM įrašas a~b išplaukia iš tos pačios priešdėlio taisyklės, nes pridėtasis * kriterijų paverčia pakaitos šablonu, kuriame ~b yra pabėgęs b

Kada COUNTIF persijungia į pakaitos simbolių režimą?

COUNTIF į pakaitos simbolių režimą persijungia tik tada, kai kriterijaus tekste yra * arba ?, pabėgęs ar ne. Be nė vieno iš jų Excel kriterijų su kiekvienu langeliu lygina kaip ištisą eilutę, neatsižvelgdamas į registrą, o tildė tebėra tildė, tad COUNTIF(A1:A7,"a~b") suskaičiuoja langelį, kuriame tikrai yra a~b. Įdėkite vieną žvaigždutę, ir reikšmė apsiverčia: "a~b*" tildė jau pabėga b, šablonas skaitosi „ab ir po to bet kas“, o langelis a~b daugiau nesiskaito. HotXLS šią taisyklę abiejuose varikliuose taiko nuo v2.384.52, per vieną kriterijų atitiktvą lxCalc viduje, kuriuo dalijasi COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS ir duomenų bazės funkcijos

Pakaitos režimo viduje pabėgimo taisyklės tos pačios, kaip ir visur kitur Excel: ~ sekantį simbolį padaro literalu, koks jis bebūtų, tad ~b reiškia b, ~~ – vieną tildę, o šablono pačioje pabaigoje stovinti tildė nukerpama, tad "a*~" elgiasi kaip "a*". Kvadratiniai skliaustai niekada nėra ypatingi. Kriterijus "[x]" suskaičiuoja langelius, laikančius tris simbolius [x], o "[a-z]" paprastuose duomenyse nesuskaičiuoja nieko. TXLSXWorkbook.Calculate formulės eilutę įvertina prieš aktyvųjį lapą ir grąžina Variant – greičiausias būdas pasitikrinti šias taisykles ant savų duomenų

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 ... kad SUMIF suma įvardintų savo eilutes
    end;
    Sheet.Cells[8, 1].Value := 5;                // skaičius; A9 lieka tuščias

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    be * ar ?: grynas tekstas, langelis a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    pakaitos režimas: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    ištisinės eilutės pakaitos šablonas, be abc
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  visos eilutės, išskyrus abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    literalus a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    skaičius 5 ir tuščias A9 suskaičiuojami
    Show('=COUNTIF(A1:A9,"<>")');       // 8    ne tušti langeliai
  finally
    Book.Free;
  end;
end.

Ką skaičiuoja „<>tekstas“?

Kriterijus "<>text" suskaičiuoja kiekvieną langelį, kuris nėra tas tekstas, o Excel 16 tai apima skaičius, Boolean, klaidų reikšmes ir tuščius langelius. Nuogas "<>" – visai kitas klausimas: jis reiškia „ne tuščias langelis“, tad tuščius praleidžia, bet skaičiuoja kiekvieną reikšmę, įskaitant tuščiąjį tekstą, kurį grąžina tokia formulė kaip ="". Senas HotXLS kodas teksto langelius pataikydavo, o skaičius – ne: Variant nelygybė priversdavo Delphi 'ab' konvertuoti į skaičių, konversija išmesdavo išimtį, apdorotojas ją prariydavo kaip „nėra atitikmens“, ir skaitiniai langeliai tyliai iškrisdavo iš skaičiavimo. Tuščių langelių šios istorijos pusė, įskaitant tai, kam lygus tuščias operandas paprastame palyginime, aprašyta straipsnyje kaip HotXLS elgiasi su palyginimo grandinėmis, tuščiais langeliais ir SUMIF

Kodėl MATCH ieškant a~b randa ab?

MATCH ieškant a~b randa ab, nes MATCH su match type 0 ir XLOOKUP su match_mode 2 visada būna pakaitos režime, tad tildė yra pabėgimas net tada, kai šablone nėra * arba ?. Excel 16 tai patvirtina dviejų langelių diapazone su a~b ir ab: MATCH("a~b",D1:D2,0) grąžina 2, o diapazone, laikančiame vien a~b, tas pats kvietimas grąžina #N/A. Literalųjį tekstą a~b paieškai reikia rašyti "a~~b". Tuo metu COUNTIF(D1:D2,"a~b") ant tų pačių dviejų langelių grąžina 1, suskaičiuodamas kitą langelį. Ta pati eilutė, tas pats diapazonas, priešingas langelis

Todėl HotXLS tuos du sprendimus laiko atskirus, o ne už vieno „atitikti šabloną“ įėjimo taško. Pats atitiktuvas bendras: nuo v2.384.52 MATCH, XLOOKUP ir kriterijų funkcijos lekia per tą patį grįžtamąjį atitiktvą, su tokiu pačiu pabėgimų apdorojimu ir ta pačia galinės tildės taisykle. Skiriasi vartai priešais. Kriterijų kelias pirmiausia paklausia „ar šiame tekste yra * arba ??“; paieškos kelias neklausia niekada. Suvienijus abu, sutaisytų vieną šeimą ir sulaužytų kitą, o abi kryptys abiejuose varikliuose tikrinamos prieš Excel 16 reikšmes. Pakaitos paieškos turi ir savą prielaidą: XLOOKUP atmeta pakaitos atitikimą, sujungtą su dvejetainės paieškos režimu – taisyklė aprašyta vadove HotXLS XLOOKUP ir XMATCH paieškos režimai

Kaip DSUM ir duomenų bazės funkcijos skaito gryno teksto kriterijų?

DSUM ir kitos duomenų bazės funkcijos tekstinį kriterijų be priekinio =, < arba > skaito kaip „prasideda su“, o pakaitos simboliai lieka aktyvūs. Tai Advanced Filter taisyklė, ir ji nuo COUNTIF skiriasi tyčia. Excel 16 išmatuota ant Name stulpelio su abc, ab, xab, AB, a~b ir a*b: kriterijus ab atitinka abc, ab ir AB; =ab atitinka tik ab ir AB; <>ab yra viso įrašo nelygybė; a*b ir a? irgi priešdėlio šablonai; >ab – paprastas palyginimas. Iki v2.384.64 HotXLS ab atitiko tiksliai, tad DSUM ant tų bandomųjų duomenų grąžindavo 10 ten, kur Excel grąžina 11

Pataisymas turėjo apeiti sąlygų parserį, kuris ir ab, ir =ab sulenkia į tą pačią lygybės sąlygą. HotXLS todėl prieš pasitikėdamas išanalizuota sąlyga apžiūri žaliąjį kriterijaus tekstą: tekstinis kriterijus, kurio pirmasis simbolis nėra =, < arba >, gauna pridėtą * ir lekia per pakaitos atitiktvą, o visa kita išlaiko viso įrašo palyginimą. Praktinė pastaba, kai kriterijų diapazonus renkate kode: XLSX variklyje eilutės '=ab' priskyrimas TXLSXCell.Value išsaugo tekstą, o klasikinis TXLSWorkbook variklis reikšmę, prasidedančią =, sukompiliuoja kaip formulę, nebent priekin pridėsite 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';            // kriterijų antraštė D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // XLSX variklyje lieka tekstu
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (prasideda su)
    // =ab  -> 6    ab, AB (visas įrašas)
    // <>ab -> 121  viskas, išskyrus ab ir AB
    // a*b  -> 127  a*b* atitinka visus septynis, su abc
    // a~*  -> 32   tik literalus a*b
  finally
    Book.Free;
  end;
end;

Vienas susijęs skirtumas išgyveno priešdėlio pataisymą ir svarbus senesniuose variantuose. Teksto palyginimai kaip >ab naudojo kodo taškų tvarką, o Excel skyrybą deda prieš raides, tad "a~b">"ab" Excel yra FALSE, o HotXLS buvo TRUE. Nuo v2.384.67 kriterijai > ir <, kartu su paprastu teksto palyginimu ir rikiavimu, naudoja Excel word-sort rikiavimo taisyklę pagal dabartinę naudotojo vietovę, ir abu vėl sutaria

Kodėl viso langelio Find praleido abcb?

Viso langelio Find praleisdavo abcb, nes atitiktuvas sustodavo pirmoje vietoje, kur šablonas pasibaigdavo, vietoj to, kad grįžtų į paskutinę *. Dalinio atitikimo atitiktuvas už Replace grąžina atsakymą vos šablonui išsenkant; viso langelio Find jį pakartotinai naudojo ir tada reikalavo, kad atitikmuo dengtų visą langelį: a*b prieš abcb sustodavo po ab, suvartojęs 2 iš 4 simbolių, ir būdavo atmetamas. Nuo v2.384.60 viso langelio atitiktuvas – atskira implementacija, kuri „šablonas pasibaigė, tekstas ne“ laiko dar vienu nesutapimu ir bando iš naujo nuo paskutinės žvaigždutės, tad a*b atitinka abcb, o a?b*b – axbyb, kaip Excel 16 Find su pažymėtu „Match entire cell contents“

Tas pats leidimas pakeitė tildę. Excel 16 Find, ir viso langelio, ir daliniame režime, ~ laiko bet kurio sekančio simbolio pabėgimu: a~b randa ab, a~~b randa a~b, o galinė tildė ignoruojama, tad q~ elgiasi kaip q. Senesnis HotXLS atitiktuvas pabėgimais pripažindavo tik ~*, ~? ir ~~, tad a~b rasdavo tekstą a~b. Find šablonas iš vienos ~ pačiame Excel nestabilus – atitinka bet kurį langelį kaip tuščias šablonas, – ir HotXLS to neimituoja

XLSX variklyje paieška yra TXLSXWorksheet.FindText su TXLSXFindOptions aibe: lxfUseWildcards įjungia *, ? ir ~, lxfWholeCell reikalauja, kad atitiktų visas langelis, o lxfMatchCase padaro palyginimą jautrų registrui. Be lxfUseWildcards kiekvienas simbolis, įskaitant žvaigždutę, yra literalas. Find žiūri tik į teksto reikšmes; skaitiniai langeliai praleidžiami, o formulės langeliai praleidžiami, nebent nustatytas lxfSearchFormulas – tada ieškoma formulės tekste. Ankra, nurodyta StartRow ir StartCol, yra įskaitomoji, tad Find All kilpa žingsniuoja vienu stulpeliu toliau už kiekvieno radinio

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 atmestas, abcb grįžta
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b yra pabėgęs b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ yra viena tildė literalu

    // Dalinis atitikimas, Find All: ankros langelis įskaitomas, tad žingsniuokite pro kiekvieną radinį
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // eilutės 1, 2, 3 ir 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Viso langelio pakaitos pakeitimas perrašo tik literalų a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Dalinė kilpa randa visas keturias eilutes, įskaitant abc, nes daliniame režime a*b turi tik kur nors pasirodyti langelio viduje. FindTextIn ir ReplaceTextIn ima tas pačias parinktis plius FirstRow, FirstCol, LastRow, LastCol langą – programinį atitinkamą paieškai pažymėtoje srityje. Klasikinis variklis tas pačias taisykles atveria per perkrovimą su trimis Boolean, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plius jam atitinkantį ReplaceText perkrovimą, su rezultatais eilutėms ir stulpeliams, skaičiuojamais nuo 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;

Ką senasis DOS kaukių atitiktuvas padarydavo ne taip?

Senasis atitiktuvas klydo dėl ypatingųjų simbolių, nes DOS failo kaukė – kita kalba nei Excel pakaitos simbolis. Iki v2.384.52 kriterijų funkcijos ir duomenų bazės funkcijos kiekvieną šabloną perdavinėdavo MatchesMask, failo kaukių atitiktvui lxMasks modulyje. Jo sintaksė su Excel sintakse persidengia paprastais atvejais – todėl problema ir liko paslėpta, – bet išsiskiria ten, kur tikri duomenys tampa įdomūs:

• [x] buvo skaitoma kaip simbolių aibė, tad COUNTIF(A1:A10,"[x]") suskaičiuodavo langelius su x, vietoj skliaustuose užrašyto teksto, o "[a-z]" atitiko bet kurį vienos raidės langelį
• Tildės pabėgimo nebuvo, tad "a~*b" negalėjo atitikti literalios žvaigždutės
• Iškraipyta kaukė, pavyzdžiui neuždarytas skliaustas, išmesdavo išimtį, kurią kvietėjas prariydavo kaip „nėra atitikmens“, – rašybos klaida kriterijuje virdavo į tylią, bet neteisingą sumą
• Paieškos pusėje MATCH ir XLOOKUP pabėgimais laikydavo tik ~*, ~? ir ~~, tad MATCH("a~b",…,0) rasdavo literalų a~b, o ne ab

Jei jūsų darbaknygės niekada nenaudojo nieko, išskyrus * ir ? ant paprastų raidinių-skaitinių duomenų, rezultatai jau buvo teisingi ir nepasikeis. Jei jose yra skliaustai, tildės, mišraus tipo stulpeliai po "<>text" arba plikais žodžiais parašyti DSUM kriterijai, perskaičius juos su v2.384.64 ar naujesne versija sumos gali pasikeisti, ir naujosios sumos – tos, kurias rodo Excel. Tas pats skirtumas tarp to, kaip Excel kriterijų saugo ir kaip lygina, iškyla ir išsaugotiems filtrams, apie kuriuos rašo HotXLS straipsnis apie BIFF8 AutoFilter DOPER kriterijus

Trumpa atmintinė: Excel pakaitos simbolių taisyklės HotXLS

• COUNTIF, SUMIF, AVERAGEIF ir *IFS šeima pakaitos simbolius naudoja tik tada, kai kriterijus turi * arba ?; kitu atveju lygina ištisas eilutes neatsižvelgdami į registrą, o ~ yra literalus (nuo v2.384.52)
• MATCH su match type 0 ir XLOOKUP su match_mode 2 pakaitos simbolius naudoja visada, tad a~b randa ab, o literalui reikia a~~b (nuo v2.384.52)
• Pakaitos režime ~ pabėga bet kurį sekantį simbolį, o galinė ~ nukerpama; [ ir ] – paprasti simboliai
• "<>text" skaičiuoja skaičius, Boolean, klaidas ir tuščius langelius; nuogas "<>" skaičiuoja ne tuščius langelius, kartu ir ="" rezultatus
• DSUM ir kitos duomenų bazės funkcijos gryną tekstą laiko „prasideda su“; =text ir <>text lygina visą įrašą (nuo v2.384.64)
• Viso langelio Find su lxfUseWildcards ir lxfWholeCell grįžta, tad a*b atitinka abcb; Find ir Replace ~ laiko bet kurio simbolio pabėgimu (nuo v2.384.60)
• Teksto tvarka kriterijuose > ir < seka Excel word-sort rikiavimą, skyryba prieš raides (nuo v2.384.67)

Excel suderinamumas formulių variklyje daugiausia yra tokie kraštiniai atvejai, išmatuoti prieš Excel, o ne atspėti iš dokumentacijos. HotXLS COUNTIF, MATCH, XLOOKUP, DSUM ir likusią funkcijų biblioteką vertina natyviai Delphi ir C++Builder aplinkoje, ir klasikiniame variklyje, ir XLSX variklyje, be įdiegto Excel. Detalės, leidimai ir bandomosios versijos atsiuntimas yra HotXLS Delphi skaičiuoklės komponento puslapyje