HotXLS:n Excel-jokerimerkit: COUNTIF, MATCH, DSUM ja Find

HotXLS Delphi Component lukee saman mallimerkkijonon neljällä eri tavalla, koska Excel 16 tekee niin. Kohteessa COUNTIF ja SUMIF teksti a~b on literaali, ellei ehto sisällä myös *- tai ?-merkkiä; kohteessa MATCH ja XLOOKUP jokeritilassa tilde on aina pakomerkki, joten a~b löytää arvon ab; kohteessa DSUM ja muissa tietokantafunktioissa pelkkä teksti tarkoittaa "alkaa merkkijonolla"; ja kokosolu-Findin on palattava taaksepäin viimeiseen *-merkkiin. HotXLS noudattaa näitä mitattuja sääntöjä versioista v2.384.52, v2.384.60 ja v2.384.64 alkaen

Alueen bugiraportit eivät koskaan mainitse jokerimerkkejä. Niissä sanotaan, että palvelimella tuotettu raportti laskee pari riviä vähemmän kuin sama tiedosto Excelissä uudelleen laskettuna, tai että tildeä sisältävä osanumero löytyy yhdellä kaavalla ja jää huomiotta seuraavalla. Syy on sovitin, joka olettaa mallin tarkoittavan yhtä ja samaa kaikkialla. Excel ei toimi noin, joten moottori, jonka välimuistitettujen tulosten on sovittava Excelin kanssa, ei voi myöskään. Ennen versiota v2.384.52 HotXLS ajoi jokaisen ehdon DOS-tyylisen tiedostopeitteen läpi, joka sai arjen mallit oikein ja reunatapaukset hiljaisesti väärin

Miksi yksi mallimerkkijono tarkoittaa neljää eri asiaa Excelissä?

Yksi mallimerkkijono tarkoittaa neljää eri asiaa, koska Excel peri neljä sovittelusääntöä neljästä ominaisuudesta eikä koskaan yhtenäistänyt niitä. Ehtofunktiot (COUNTIF, SUMIF, AVERAGEIF ja *IFS-perhe) päättävät ehtoa kohden, sovelletaanko jokerimerkkejä ylipäätään. Hakufunktiot (MATCH hakutyypillä 0, XLOOKUP match_mode 2) soveltavat niitä aina. Tietokantafunktiot (DSUM, DCOUNTA ja kumppanit) noudattavat laajennettua suodatinta, jossa paljas sana on etuliite. Find-valintaikkunalla on omat kokosolu- ja osittaiset tilansa. Alla oleva taulukko listaa, mitkä solut täsmäävät kuhunkin malliin yhtä saraketta vasten, joka sisältää arvot a~b, ab, AB, abc, abcb, a*b ja axb, jokainen funktio oletustilassaan, joka ohittaa kirjainkodon

Malli	COUNTIF / SUMIF	MATCH(…,0) / XLOOKUP, tila 2	DSUM-ehto	Find, koko solu, jokerit päällä
ab	ab, AB	ab, AB	ab, AB, abc, abcb	ab, AB
a*b	a~b, ab, AB, abcb, a*b, axb	kuten COUNTIF	jokainen arvo, abc mukaan lukien	kuten COUNTIF
a~b	vain a~b	ab, AB	ab, AB, abc, abcb	ab, AB
a~*b	vain a*b	vain a*b	vain a*b	vain a*b
=ab	ab, AB	ei sovellu	ab, AB	ei sovellu

Rivi a~b on se, jossa COUNTIF ja MATCH eriävät, ja osanumerot ja käsin kirjoitetut koodit sisältävät tildeitä useammin kuin kukaan odottaa. Rivi a*b näyttää toisen ansan: abc täsmää kohteessa DSUM mutta ei kohteessa COUNTIF, koska tietokantafunktio liittää hiljaisesti *-merkin. Taulukon DSUM-arvot riveille ab, a*b ja =ab tulevat suoraan Excel 16 -ajoista; DSUM-arvo riville a~b seuraa samasta etuliitesäännöstä, sillä liitetty * muuttaa ehdon jokerimalliksi, jossa ~b on pakotettu b

Milloin COUNTIF siirtyy jokeritilaan?

COUNTIF siirtyy jokeritilaan vain, kun ehdon teksti sisältää *- tai ?-merkin, pakotettuna tai ei. Kumpaakaan merkkiä ollessa Excel vertaa ehtoa kuhunkin soluun kokonaisena merkkijonona kirjainkoosta välittäen, ja tilde on vain tilde, joten COUNTIF(A1:A7,"a~b") laskee solun, joka kirjaimellisesti sisältää arvon a~b. Lisää yksi tähti ja merkitys kääntyy: kohteessa "a~b*" tilde pakottaa nyt merkin b, malli luetaan muodossa "ab ja sitten mitä tahansa", eikä solua a~b enää lasketa. HotXLS on soveltanut tätä sääntöä kummassakin moottorissa versiosta v2.384.52 alkaen yhden ehdonsovittimen, lxCalcin, kautta, jota jakavat COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS ja tietokantafunktiot

Jokeritilassa pakosäännöt ovat samat kuin muuallakin Excelissä: ~ tekee seuraavasta merkistä literaalin, mikä se onkaan, joten ~b tarkoittaa b:tä ja ~~ yhtä tildettä, ja mallin ihan lopussa oleva tilde pudotetaan, joten "a*~" käyttäytyy kuten "a*". Hakasulkeet eivät ole koskaan erikoisia. Ehto "[x]" laskee solut, jotka sisältävät kolme merkkiä [x], ja "[a-z]" ei laske mitään tavallisella datalla. TXLSXWorkbook.Calculate laskee kaavamerkkijonon aktiivista taulukkoa vasten ja palauttaa Variantin, nopein tapa tarkistaa nämä säännöt omalla datallasi

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-yhteensä nimeää rivinsä
    end;
    Sheet.Cells[8, 1].Value := 5;                // luku; A9 pysyy tyhjänä

    Show('=COUNTIF(A1:A7,"a~b")');      // 1    ei *:ää eikä ?:tä: pelkkä teksti, solu a~b
    Show('=COUNTIF(A1:A7,"a~b*")');     // 4    jokeritila: ab, AB, abc, abcb
    Show('=COUNTIF(A1:A7,"a*b")');      // 6    koko merkkijonon jokeri, abc pois jätetty
    Show('=SUMIF(A1:A7,"a*b",B1:B7)');  // 119  jokainen rivi paitsi abc (8)
    Show('=COUNTIF(A1:A7,"a~*b")');     // 1    literaali a*b
    Show('=COUNTIF(A1:A9,"<>ab")');     // 7    luku 5 ja tyhjä A9 lasketaan
    Show('=COUNTIF(A1:A9,"<>")');       // 8    ei-tyhjät solut
  finally
    Book.Free;
  end;
end.

Mitä "<>teksti" laskee?

Ehto "<>teksti" laskee jokaisen solun, joka ei ole kyseinen teksti, ja Excel 16:ssa siihen kuuluvat luvut, totuusarvot, virhearvot ja tyhjät solut. Paljas "<>" on kokonaan toinen kysymys: se tarkoittaa "ei tyhjä solu", joten se ohittaa tyhjät solut mutta laskee jokaisen arvon, mukaan lukien tyhjän tekstin, jonka kaava kuten ="" palauttaa. Vanha HotXLS-koodi sai tekstisolut oikein mutta ei lukuja: Variant-erisuuruusvertailu sai Delphin muuntamaan arvon 'ab' luvuksi, muunnos nosti poikkeuksen, käsittelijä nielaisi sen "ei osumana", ja numeeriset solut putosivat hiljaisesti laskennasta. Tämän tarinan tyhjän solun puoli, mukaan lukien minkä tyhjä operandi on yhtä suuri tavallisessa vertailussa, on käsitelty artikkelissa miten HotXLS käsittelee vertailuketjut, tyhjät solut ja SUMIFin

Miksi MATCH löytää arvon ab, kun etsit arvoa a~b?

MATCH löytää arvon ab, kun etsit arvoa a~b, koska MATCH hakutyypillä 0 ja XLOOKUP match_mode 2 ovat aina jokeritilassa, joten tilde on pakomerkki, vaikka mallissa ei olisi *- eikä ?-merkkiä. Excel 16 vahvistaa sen kaksisoluisella alueella, joka sisältää arvot a~b ja ab: MATCH("a~b",D1:D2,0) palauttaa 2:n, ja alueella, joka sisältää vain arvon a~b, sama kutsu palauttaa #N/A:n. Löytääksesi literaalitekstin a~b sinun on kirjoitettava "a~~b". Samaan aikaan COUNTIF(D1:D2,"a~b") samojen kahden solun yli palauttaa 1:n, laskee toisen solun. Sama merkkijono, sama alue, eri solu

Siksi HotXLS pitää kaksi päätöstä erillään sen sijaan, että laittaisi ne yhden "sovita malli" -sisääntulopisteen taakse. Itse sovitin on jaettu: versiosta v2.384.52 alkaen MATCH, XLOOKUP ja ehtofunktiot ajavat samaa takaisinkytkentäsovitinta, samalla pakokäsittelyllä ja samalla säännöllä mallin lopussa olevasta tildettä. Eri asia on sen edessä oleva portti. Ehtopolku kysyy ensin "sisältääkö tämä teksti *- tai ?-merkin?"; hakupolku ei koskaan kysy. Kahden yhdistäminen korjaisi toisen perheen ja rikkoisi toisen, ja molemmat suunnat tarkistetaan Excel 16 -arvoja vasten kummassakin moottorissa. Jokerihauilla on myös oma esiehtonsa: XLOOKUP hylkää jokerisovituksen yhdistettynä binäärihakutilaan, sääntö, joka on kuvattu HotXLS:n oppaassa XLOOKUPin ja XMATCHin hakutilat

Miten DSUM ja tietokantafunktiot lukevat pelkän tekstiehdon?

DSUM ja muut tietokantafunktiot lukevat tekstiehdon ilman alussa olevaa =-, <- tai >-merkkiä muodossa "alkaa merkkijonolla", jokerimerkkien ollessa yhä käytössä. Tämä on laajennetun suodattimen sääntö, ja se eroaa funktiosta COUNTIF tahallaan. Mittaukset Excel 16:lla Name-sarakkeen yli, joka sisälsi arvot abc, ab, xab, AB, a~b ja a*b, antoivat: ehto ab täsmää arvoihin abc, ab ja AB; =ab täsmää vain arvoihin ab ja AB; <>ab on kokonaisarvojen erisuuruusvertailu; a*b ja a? ovat myös etuliitemalleja; >ab on tavallinen vertailu. Ennen versiota v2.384.64 HotXLS sovitti arvon ab täsmälleen, joten DSUM kyseisen testidatan yli palautti 10:n siellä, missä Excel palauttaa 11:n

Korjauksen oli kierrettävä ehtojäsentäjä, joka taittaa sekä arvon ab että =ab samaan yhtäsuuruusehtoon. HotXLS tarkastaa siksi raakanehdon tekstin ennen kuin luottaa jäsennettyyn ehtoon: tekstiehto, jonka ensimmäinen merkki ei ole =, < tai >, saa *-merkin liitetyksi ja kulkee jokerisovittimen läpi, ja kaikki muu pitää kokonaisarvovertailunsa. Yksi käytännön huomio, kun rakennat ehtoalueita koodissa: XLSX-moottorissa merkkijonon '=ab' sijoittaminen kohteeseen TXLSXCell.Value tallentaa tekstin, kun taas klassinen TXLSWorkbook-moottori kääntää =-merkillä alkavan arvon kaavaksi, ellet etuliitä sitä heittomerkillä

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';            // ehtojen otsikko solussa D1
    for i := 0 to High(Criteria) do
    begin
      Sheet.Cells[2, 4].Value := Criteria[i];     // pysyy tekstinä XLSX-moottorissa
      Writeln(Criteria[i], ' -> ',
        VarToStr(Book.Calculate('=DSUM(A1:B8,"Val",D1:D2)')));
    end;
    // ab   -> 30   ab, AB, abc, abcb (alkaa merkkijonolla)
    // =ab  -> 6    ab, AB (koko arvo)
    // <>ab -> 121  kaikki paitsi ab ja AB
    // a*b  -> 127  a*b* täsmää kaikkiin seitsemään, abc mukaan lukien
    // a~*  -> 32   vain literaali a*b
  finally
    Book.Free;
  end;
end;

Yksi aiheeseen liittyvä ero eli etuliitekorjauksen ikänsä ja merkitsee vanhemmissa koontiversioissa. Tekstivertailut kuten >ab käyttivät koodipistejärjestystä, kun taas Excel laittaa välimerkit kirjainten eteen, joten "a~b">"ab" on FALSE Excelissä ja oli TRUE HotXLS:ssä. Versiosta v2.384.67 alkaen >- ja <-ehdot, tavallisen tekstivertailun ja lajittelun ohella, käyttävät Excelin sanajärjestyslajittelujärjestystä nykyisen käyttäjän maa-asetuksen alla, ja kaksi sopivat taas yhteen

Miksi kokosolu-Find ohitti arvon abcb?

Kokosolu-Find ohitti arvon abcb, koska sovitin pysähtyi ensimmäiseen kohtaan, jossa malli oli käytetty loppuun, sen sijaan että olisi palannut taaksepäin viimeiseen *-merkkiin. Replace:n takana oleva osittaisen osuman sovitin palaa heti, kun malli on käytetty loppuun; kokosolu-Find käytti sitä uudelleen ja vaati sitten, että osuma kattaa koko solun: a*b arvoa abcb vasten pysähtyi kohdan ab jälkeen, kulutti 2 merkkiä 4:stä ja hylättiin. Versiosta v2.384.60 alkaen kokosolusovitin on erillinen toteutus, joka käsittelee tilanteen "malli loppui, teksti ei" yhtenä lisäepäosumana ja yrittää uudelleen viimeisestä tähdestä, joten a*b täsmää arvoon abcb ja a?b*b täsmää arvoon axbyb, kuten Excel 16:n Find tekee valinnalla "Match entire cell contents" aktiivisena

Sama julkaisu muutti tildettä. Excel 16:n Find käsittelee sekä kokosolu- että osittaisessa tilassa ~-merkin pakomerkkinä mille tahansa seuraavalle merkille: a~b löytää arvon ab, a~~b löytää arvon a~b, ja lopussa oleva tilde ohitetaan, joten q~ käyttäytyy kuten q. Vanhempi HotXLS-sovitin tunnisti pakomerkkeinä vain muodot ~*, ~? ja ~~, joten a~b löysi tekstin a~b. Find-malli, jossa on yksittäinen ~, on epävakaa itse Excelissä, täsmäten minkä tahansa solun kuten tyhjä malli, eikä HotXLS jäljittele sitä

XLSX-moottorissa haku on TXLSXWorksheet.FindText TXLSXFindOptions-joukolla: lxfUseWildcards kytkee päälle merkit *, ? ja ~, lxfWholeCell vaatii koko solun täsmäämisen, ja lxfMatchCase tekee vertailusta kirjainkoon erottavan. Ilman flagia lxfUseWildcards jokainen merkki, tähti mukaan lukien, on literaali. Find katsoo vain tekstiarvoja; numeeriset solut ohitetaan, ja kaavasolut ohitetaan, ellei flagia lxfSearchFormulas ole asetettu, jolloin kaavan tekstiä etsitään. StartRow:n ja StartCol:n antama ankkuri on sisällyttävä, joten Find All -silmukka astuu sarakkeen verran jokaisen osuman ohi

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 hylätty, abcb palaa taaksepäin
    if Sheet.FindText('a~b', Row, Col, Opts, 1, 1) then
      Writeln('a~b  whole cell -> row ', Row);   // 4: ~b on pakotettu b
    if Sheet.FindText('a~~b', Row, Col, Opts, 1, 1) then
      Writeln('a~~b whole cell -> row ', Row);   // 3: ~~ on yksi literaalitilde

    // Osittainen osuma, Find All: ankkurisolu sisältyy, joten astu jokaisen osuman ohi
    NextRow := 1;
    NextCol := 1;
    while Sheet.FindText('a*b', Row, Col, [lxfUseWildcards], NextRow, NextCol) do
    begin
      Writeln('a*b  contained in row ', Row);     // rivit 1, 2, 3 ja 4
      NextRow := Row;
      NextCol := Col + 1;
    end;

    // Kokosolun jokerikorvaus kirjoittaa uudelleen vain literaalin a~b
    Changed := Sheet.ReplaceText('a~~b', 'a-b', Opts);
    Writeln(Changed, ' cell(s) replaced');         // 1
  finally
    Book.Free;
  end;
end;

Osittainen silmukka löytää kaikki neljä riviä, mukaan lukien abc, koska osittaisessa tilassa a*b:n tarvitsee vain esiintyä jossakin solun sisällä. FindTextIn ja ReplaceTextIn ottavat samat asetukset plus FirstRow-, FirstCol-, LastRow- ja LastCol-ikkunan, ohjelmallisen vastineen valinnan sisältä etsimiselle. Klassinen moottori paljastaa samat säännöt overloadin kautta, jolla on kolme totuusarvoa, TXLSWorksheet.FindText(SearchText, Row, Col, MatchCase, UseWildcards, WholeCell), plus vastaava ReplaceText-overload, ykköspohjaisilla rivi- ja saraketuloksilla:

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ä vanha DOS-peite-sovitin sai väärin?

Vanha sovitin sai erikoismerkit väärin, koska DOS-tiedostopeite on eri kieli kuin Excelin jokerimerkki. Ennen versiota v2.384.52 ehtofunktiot ja tietokantafunktiot välittivät jokaisen mallin funktiolle MatchesMask, tiedostopeitesovitin lxMasks-yksikössä. Sen syntaksi menee päällekkäin Excelin kanssa tavallisissa tapauksissa, mistä syystä ongelma pysyi piilossa, mutta se eroaa siellä, missä oikea data muuttuu kiinnostavaksi:

• [x] luettiin merkkijoukkona, joten COUNTIF(A1:A10,"[x]") laski solut, jotka sisältävät merkin x, sulkeiden sisältämän tekstin sijaan, ja "[a-z]" täsmäsi mihin tahansa yksikirjaimiseen soluun
• Tildepakoa ei ollut, joten "a~*b" ei voinut täsmätä literaalitähteen
• Viallinen peite, kuten sulkematon hakasulje, nosti poikkeuksen, jonka kutsuja nielaisi "ei osumana", muuttaen kirjoitusvirheen ehdoissa hiljaisesti vääräksi yhteissummaksi
• Hakupuolella MATCH ja XLOOKUP käsittelivät pakomerkkeinä vain muodot ~*, ~? ja ~~, joten MATCH("a~b",…,0) löysi literaalin a~b arvon ab sijaan

Jos työkirjasi käyttivät koskaan vain *- ja ?-merkkejä tavallisella aakkosnumeerisella datalla, tulokset olivat jo oikein eivätkä muutu. Jos ne sisältävät hakasulkeita, tildeitä, sekatyyppisiä sarakkeita ehdon "<>teksti" alla tai DSUM-ehtoja kirjoitettuina paljaina sanoin, niiden uudelleenlaskeminen versiolla v2.384.64 tai uudemmalla voi muuttaa yhteissummia, ja uudet summat ovat ne, jotka Excel näyttää. Sama ero siinä, miten Excel tallentaa ehdon ja miten se vertaa sitä, tulee vastaan tallennettujen suodattimien kohdalla, käsitelty artikkelissa HotXLS:n artikkeli BIFF8 AutoFilter DOPER -ehdoista

Pikaopas: Excelin jokerisäännöt HotXLS:ssä

• COUNTIF, SUMIF, AVERAGEIF ja *IFS-perhe käyttävät jokerimerkkejä vain, kun ehto sisältää *- tai ?-merkin; muuten ne vertaavat kokonaisia merkkijonoja kirjainkoosta välittäen ja ~ on literaali (versiosta v2.384.52 alkaen)
• MATCH hakutyypillä 0 ja XLOOKUP match_mode 2 käyttävät aina jokerimerkkejä, joten a~b löytää arvon ab ja literaali vaatii muodon a~~b (versiosta v2.384.52 alkaen)
• Jokeritilassa ~ pakottaa minkä tahansa seuraavan merkin ja lopussa oleva ~ pudotetaan; [ ja ] ovat tavallisia merkkejä
• "<>teksti" laskee luvut, totuusarvot, virheet ja tyhjät solut; paljas "<>" laskee ei-tyhjät solut, =""-tulokset mukaan lukien
• DSUM ja muut tietokantafunktiot käsittelevät pelkkää tekstiä muodossa "alkaa merkkijonolla"; =teksti ja <>teksti vertaavat koko arvoa (versiosta v2.384.64 alkaen)
• Kokosolu-Find flagilla lxfUseWildcards ja lxfWholeCell palaa taaksepäin, joten a*b täsmää arvoon abcb; Find ja Replace käsittelevät ~-merkkiä pakomerkkinä mille tahansa merkille (versiosta v2.384.60 alkaen)
• Tekstijärjestys ehdoissa > ja < noudattaa Excelin sanajärjestyslajittelujärjestystä, välimerkit ennen kirjaimia (versiosta v2.384.67 alkaen)

Excel-yhteensopivuus kaavamoottorissa on suurimmaksi osaksi tällaisia reunatapauksia, mitattu Exceliä vasten eikä arvattu dokumentaatiosta. HotXLS laskee funktiot COUNTIF, MATCH, XLOOKUP, DSUM ja muun funktiokirjastonsa natiivisti Delphissä ja C++Builderissa, sekä klassisessa moottorissa että XLSX-moottorissa, ilman asennettua Exceliä. Yksityiskohdat, versiot ja kokeilulataus ovat HotXLS Delphi -laskentataulukkokomponentin sivulla