HotXLS-kaavat: miksi Excel lisää @-merkin ja #VALUE!-virheen

Excel 365 lisää @-merkin kaavaan kuten =SUM(A1:B1*{10,100}) ja näyttää #VALUE!-virheen, kun tiedosto tallentaa sen tavallisena kaavana, sillä silloin Excel soveltaa vanhaa implisiittistä leikkausta jokaiseen operaattorin operandiin. Versiosta v2.384.68 alkaen HotXLS Delphi Component tallentaa nämä operaattoripitoiset taulukkokaavat samaan tapaan kuin Excel 365: XLSX-muodossa yksisoluisina dynaamisten taulukoiden kaavoina ja XLS-muodossa yksisoluisina taulukkokaavoina

Oire läpäisee koodikatselmoinnin. Delphi-palvelusi kirjoittaa työkirjan, HotXLS laskee sen uudelleen ja välimuistittaa arvon 210 kaavalle =SUM(A1:B1*{10,100}), ja asiakas avaa tiedoston Excel 16:ssa nähdäkseen kaavarivillä =SUM(@A1:B1*@{10,100}) ja solussa #VALUE!-virheen. Tiedostossa ei ole mitään rikki. Puuttuva osa on metatieto, joka kertoo Excelille kaavan kirjoitetun dynaamisten taulukoiden säännöillä, ja ilman sitä Excel palaa dynaamista taulukkoa edeltävään laskentamalliinsa

Miksi Excel 365 lisää @-merkin kaavaan, jonka HotXLS laski oikein?

Excel 365 lisää @-merkin, koska kaava ilman dynaamisen taulukon merkintää on määritelmän mukaan vanha kaava, ja vanhat kaavat tiivistävät monisoluisen alueen yhteen soluun aina kun operaattori odottaa yksittäistä arvoa. Tiivistys on implisiittinen leikkaus: Excel ottaa alueesta solun, joka jakaa kaavan rivin (pystysuoralla alueella) tai sarakkeen (vaakasuoralla alueella), ja jos sellaista solua ei ole, tulos on #VALUE!. Excel 365 säilyttää tämän merkityksen vanhan tyylin kaavoille ja näyttää @-merkin tehdäkseen tiivistyksen näkyväksi

Laita =SUM(A1:B1*{10,100}) soluun E5, ja vanha tulkinta paljastuu heti. A1:B1 on vaakasuora alue, kaava istuu sarakkeessa E, alueella ei ole solua sarakkeessa E, joten @A1:B1 on #VALUE! ja koko SUM perii sen. Dynaamisten taulukoiden säännöillä sama teksti kertoo elementti elementiltä, 1 × 10 + 2 × 100, ja palauttaa 210:n. HotXLS:n kaavamoottori on laskenut dynaamisen taulukon tavalla versioiden v2.384.61 ja v2.384.63 julkaisuista lähtien; tiedostomuoto ei vain sanonut sitä. Kun A1:B2 sisältää luvut 1, 2, 3 ja 4, nämä ovat testikaavat ja se, mitä Excel 16 näyttää:

Kaava	HotXLS:n tulos	Excel 16, tallennettuna tavallisena kaavana	Tallennus versiosta v2.384.68 alkaen
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynaaminen taulukko, Excel näyttää 210:n
=SUM((A1:B2>2)*1)	2	Implisiittinen leikkaus, väärin tai virhe	Dynaaminen taulukko, Excel näyttää 2:n
=SUMPRODUCT((A1:B2>2)*1)	2	Implisiittinen leikkaus, väärin tai virhe	Dynaaminen taulukko, Excel näyttää 2:n
=MAX(A1:B2-1)	3	Implisiittinen leikkaus, väärin tai virhe	Dynaaminen taulukko, Excel näyttää 3:sta
=SUM(A1:B2)	10	10	Tavallinen kaava, muuttumaton

Viimeinen rivi on yhtä tärkeä kuin neljä ensimmäistä. SUM(A1:B2) välittää alueen suoraan funktioparametrille, joka hyväksyy viittauksia, joten mikään operaattori ei koskaan näe monisoluista aluetta eikä leikkausta voi tapahtua. Excel 365 itse tallentaa tuon kaavan tavallisena kaavana, ja HotXLS tekee samoin

Miten HotXLS tallentaa operaattoripitoiset taulukkokaavat XLSX- ja XLS-muotoon

HotXLS kirjoittaa operaattoripitoisen taulukkokaavan XLSX-muodossa yksisoluisena dynaamisena taulukkona: <c>-elementti kantaa cm="1"-attribuuttia, kaava on <f t="array" ref="E5">, ja paketti saa xl/metadata.xml-tiedoston XLDAPR-metatietotyypillä, jonka laajennus sisältää dynamicArrayProperties fDynamic="1"-määrityksen. cm-attribuutti on ykköspohjainen indeksi kyseisen osan cellMetadata-lohkoon, ja sen takana oleva XLDAPR-tietue on se, joka kertoo Excelille "laske tämä dynaamisen taulukon säännöillä". Rakenne on sama, jonka Excel 16 kirjoittaa, kun kirjoitat saman kaavan ja tallennat, ja juuri niin kohdeasettelu alun perin selvitettiin

XLS-muodossa ei ole metatieto-osaa, joten HotXLS käyttää BIFF8:n ainoaa taulukkolaskentaan tarjoamaa rakennetta: yksisoluista taulukkokaavaa. Solu saa FORMULA-tietueen, jonka tokenivirta on yksittäinen itseensä osoittava PtgExp, jota seuraa ARRAY-tietue ($0221), joka kantaa varsinaisen jäsennetyn kaavan yksisoluisen alueen yli. Excel 365 kirjoittaa dynaamisen taulukon kaavat XLS-muotoon samalla tavalla, ja vanhempi Excel-versio näkee tiedostossa klassisen Ctrl+Shift+Enter-taulukkokaavan

Uutta API:a ei ole kyseessä. Merkintä tapahtuu, kun sijoitat kaavan tavallisen solu-API:n kautta, kummassakin moottorissa. XLSX-puolella kyseessä on TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Operaattori alueen tai inline-taulukon yli: tallennetaan dynaamisena taulukkona
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Alue välitetään suoraan funktiolle: pysyy tavallisena <f>:nä
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // Taulukon juuren teksti säilyy ilman alussa olevaa =-merkkiä
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 ja E6 saavat cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Muunnoksen jälkeen TXLSXCell.Formula palauttaa tekstin ilman =-merkkiä, samassa muodossa kuin TXLSXRange.SetDynamicArrayFormula tallentaa, joten kaavamerkkijonoja sijoituksen jälkeen vertailevan koodin kannattaa normalisoida alussa oleva =

Klassinen moottori noudattaa samaa sääntää IXLSRange.Formulan kautta yksittäiselle solulle. Kaavan sijoittaminen ohjaa sen sisäisesti yksisoluisen taulukon polulle, joten tallennettu XLS sisältää FORMULA plus ARRAY -parin:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // ARRAY-tietue
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY-tietue
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // tavallinen FORMULA

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Jos ankkuroit monisoluista tulosta skalaarisen koosteen sijaan, eksplisiittiset API:t ovat yhä oikea työkalu: SetArrayFormula ennalleen mitoitetulle suorakulmiolle, kuten artikkelissa dynaamisen taulukon spill-kaavat HotXLS:llä kuvataan, tai TXLSXRange.SetDynamicArrayFormula, kun haluat XLSX:n dynaamisen taulukon merkinnän itse mitoittamallesi alueelle. Tämän artikkelin automaattinen polku kattaa vain yhteen soluun kirjoitetut kaavat

Mitkä kaavat HotXLS merkitsee dynaamisiksi taulukoiksi?

HotXLS merkitsee kaavan vain, kun jollakin operaattorilla on taulukon tuottava operandipuu. Tarkistus ajetaan käännetyn syntaksipuun yli, ja operandi tuottaa taulukon, jos se on monisoluinen alue, inline-taulukkovakio tai toinen operaattorilauseke, jolla itsellään on tällainen operandi. Sulkeet ovat läpinäkyviä. Laskettaviin operaattoreihin kuuluvat aritmeettiset (+ - * / ^), yhdiste (&), kuusi vertailua, unaarinen plus ja miinus sekä prosentti:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) ja A1:B2-1 merkitään, missä tahansa ne kaavassa esiintyvät, myös SUMPRODUCTin sisällä
• SUM(A1:B2) ja SUMPRODUCT(A1:A2,{1;10}) ei merkitä, koska alue ja taulukko menevät suoraan funktion argumenteiksi eikä mikään operaattori koske niihin
• A1*2 tai SUM(A1,B1)*2 ei merkitä: yksisoluiset viittaukset ja funktion tulokset ovat skalaareja tälle tarkistukselle

Kolme rajaa ovat tahallisia. Ensinnäkin merkintä tapahtuu vain, kun kaava syötetään API:n kautta, eli TXLSXCell.Formula XLSX-moottorissa ja yksisoluinen Formula- tai Value-sijoitus klassisessa moottorissa. Tiedostosta ladatut kaavat kirjoitetaan takaisin täsmälleen siinä muodossa kuin ne löydettiin, sillä toisen tuottajan vanha kaava voi tahallaan nojata implisiittiseen leikkaukseen. Toiseksi teksti, jossa ei ole :- eikä {-merkkiä, ohitetaan ilman toista kääntökertaa. Kolmanneksi sellainen kaava, jonka tulos leviäisi useisiin soluihin (spill), kuten =A1:B1*2 yksinään, merkitään yksisoluiseksi dynaamiseksi taulukoksi siihen kohtaan, mihin sen sijoitit. HotXLS ei levitä sitä, vaan Excel laajentaa tuloksen viereisiin soluihin seuraavan uudelleenlaskennan yhteydessä

Tämä operandisääntö on artikkelin implisiittinen leikkaus määritetyille nimille HotXLS:ssä kattaman argumenttiluokkasäännön sisarus. Kyseinen artikkeli käsittelee arvoluokaksi julistettuja funktioparametreja; tämä käsittelee operaattoreita, jotka vanhassa mallissa vaativat aina arvoja

Mitä laskentamoottoriin muuttui, jotta tulokset osuivat kohdalleen

Tallennuskorjaus versiossa v2.384.68 nojaa siihen, että HotXLS:n kaavamoottori jo palautti Excel 365:n arvoja, mikä vaati useita aiempia korjauksia kumpaankin moottoriin. Näkyvin oli SUMPRODUCT: versioon v2.384.61 asti se hyväksyi vain kaksi tai useamman tavallista aluetta, joten SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) ja jopa yksittäisen argumentin SUMPRODUCT(B1:B2) palauttivat #N/A-virheen. HotXLS laskee nykyään lausekeargumentit elementti elementiltä Excelin säännöillä:

• jokaisen argumentin on oltava täsmälleen saman muotoinen, skalaari lasketaan 1 × 1 -muotoiseksi, muuten tulos on #VALUE!
• minkä tahansa argumentin sisällä oleva virhearvo palautetaan tuloksena
• teksti- ja loogiset elementit lasketaan nollaksi, joten (B1:B2>0)*1 tai -- tarvitaan yhä muuttamaan TRUE luvuksi 1
• puhtaista alueista koostuvat argumentit säilyttävät alkuperäisen streamaussilmukan, joten suuria alueita ei materialisoida taulukoiksi

SUM-perhe (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) käyttää samaa elementti elementiltä laskevaa laskijaa, kun argumentti on operaattorilauseke alueen yli, joten =SUM((B1:B2>0)*1) laskee molemmat rivit katsomatta vain ensimmäistä solua. v2.384.62 muutti välilyönnillä tehtävän leikkausoperaattorin palauttamaan kahden viittauksen yhteisen suorakulmion tai #NULL!-virheen, kun alueet eivät leikkaa; näin =SUM(A1:B2 B1:B2) on 6 eikä 2, ja tulos kelpaa viittausparametreille kuten ROWS ja INDEX. v2.384.63 lisäsi jäsentäjään inline-taulukkovakiot kuten {1,2;3,4} (pilkut erottavat sarakkeet, puolipisteet rivit) ja viittausunionit kuten (A1:B2,D4). Myös elementtikohtaiset vertailut antavat tyhjälle elementille toisen puolen tyypin, FALSE loogista vastaan, yhdenmukaisesti version v2.384.53 skalaarisäännön kanssa, joka on kuvattu artikkelissa vertailuketjut ja tyhjät solut HotXLS:ssä

var
  V: Variant;
begin
  // Book on TXLSXWorkbook ensimmäisestä esimerkistä;
  // sen aktiivinen taulukko sisältää arvot A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, yksittäinen argumentti
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, yhteinen alue B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, päällekkäisyys laskettu kahdesti
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, oli -1 ennen versiota v2.384.61
end;

TXLSXWorkbook.Calculate laskee kaavamerkkijonon aktiivista taulukkoa vasten tallentamatta sitä, nopea tapa tarkistaa moottorin käytös. Yksi varoitus @-merkistä itsessään: HotXLS on historiallisesti hyväksynyt @-merkin kahden viittauksen välissä binäärisenä leikkauksena, ja se laskee nyt kyseisen muodon todellisella leikkaussemantiikalla. Excel 365:ssä @ on unaarinen implisiittisen leikkauksen etuliite. Älä kirjoita @-merkkiä kaavatekstiin odottaen Excelin merkitystä; käytä välilyöntiä leikkaukseen ja anna yllä olevien tallennussääntöjen hoitaa dynaamisen taulukon semantiikka

Miksi Excel kieltäytyi avaamasta tiedostoa tai laski väärän arvon?

Excelin saaminen hyväksymään dynaamisen taulukon merkintä vaati kolme korjausta, joita mikään omaa tuotosta kieräyttävä testi ei olisi havainnut, sillä HotXLS luki omaa tuotostaan oikein jokaisessa tapauksessa. Jokainen löydettiin avaamalla HotXLS:n tuotos Excel 16:ssa ja vaihtamalla yhtä muuttujaa kerrallaan:

1. Laajennuksen GUIDin on oltava kokonaan pienellä. ext uri -attribuutin xl/metadata.xml-tiedostossa on oltava täsmälleen {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Vanhempi HotXLS-mallipohja kirjoitti sen sekoittamalla kirjainkokoa, ja Excel 16 kieltäytyi avaamasta koko pakettia, ei vain solua. Versiota v2.384.68 aiemmin TXLSXRange.SetDynamicArrayFormulalla luodut työkirjat kärsivät samasta ongelmasta
2. Taulukon juuren tekstissä ei ole alussa olevaa =-merkkiä. XLSX-kirjoittaja emittoi taulukon juuren tallennetun tekstin sellaisenaan <f>-elementtiin. Jos muunnettu solu olisi pitänyt =-merkkinsä, elementti lukisi <f t="array" ref="E5">=SUM(...)</f>, jonka Excel hylkää myös avaushetkellä. HotXLS poistaa sen muunnoksen yhteydessä, minkä vuoksi TXLSXCell.Formula lukee sen takaisin ilman merkkiä
3. Double(True) on -1 Delphissä. Variant-muunnos noudattaa COM-käytäntöä, jossa TRUE on kaikki bitit asetettuina, ja VarIsNumeric(True) palauttaa samoin Truen. Ennen versiota v2.384.61 se sai =TRUE*1:n palauttamaan -1:n ja loogiset taulukkoelementit luokiteltiin luvuiksi, joten vertailu kuten (B1:B2>0)=TRUE meni pieleen. HotXLS testaa nykyään varBoolean-tyypin ennen kuin kohtelee Variantia lukuna skalaarilaskennassa, taulukkolaskennassa ja taulukon elementtien luokittelussa, ja TRUE lasketaan luvuksi 1

BIFF8:n operandiluokat: tavutason yksityiskohdat muodon toteuttajille

BIFF8:ssä jokainen operanditokeni kantaa operandiluokkaansa tokenitavussa itsessään, ja Excel luottaa kyseiseen luokkaan enemmän kuin kaavan rakenteeseen. [MS-XLS] määrittelee luokan kaksibittisenä PtgDataType-kenttänä tokenin biteissä 5 ja 6: 1 viittaukselle, 2 arvolle, 3 taulukolle. Alimmat viisi bittiä nimeävät tokenin, joten samalla alueviittauksella on kolme kirjoitusasua:

Tokeni	Viittausluokka	Arvoluokka	Taulukon luokka
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS sai näistä kolme väärin eri kohdissa, ja jokainen tuotti oman, erillisen oireen Excelissä vaikka HotXLS luki takaisin aivan oikein:

• Viittausluokan taulukkovakiot. Enkooderi valitsi luokan kontekstista, ja SUM- tai ROWS-parametrit ovat viittausluokkaa, joten =SUM({1,2}) kirjoitettiin PtgArraylla luokassa $20. Excel näyttää koko kaavan muodossa =#N/A. Taulukkovakio ei voi koskaan olla viittaus, joten versiosta v2.384.63 alkaen HotXLS kirjoittaa taulukon luokan $60 aina kun konteksti vaatii viittausta
• Arvoluokan operandit operaattoreissa PtgIsect ja PtgUnion. Binaarioperaattorit ottivat arvoluokan operandeja, mikä sopii *-merkille mutta on väärin viittausoperaattoreille. Kun $45-alueet olivat ennen PtgIsectiä ($0F), Excel luki kaavan =SUM(A1:B2 B1:B2) muodossa =SUM(@A1:B2 @B1:B2) ja palautti #VALUE!-virheen. Versiosta v2.384.62 alkaen PtgIsectin ja PtgUnionin ($10) operandit kirjoitetaan viittausluokassa $25
• Arvoluokan operandit ARRAY-tietueen sisällä. Excel soveltaa implisiittistä leikkausta jopa taulukkokaavan sisällä, kun operandi on arvoluokkaa. HotXLS kirjoitti sinne $45:n, joten kaavan =SUM(A1:B1*{10,100}) yksisoluinen taulukkokaava laski Excelissä arvon 10. Versiosta v2.384.68 alkaen ARRAY-tietueen tokenivirta ylentää jokaisen arvoluokan viittauksen ja taulukkovakion taulukon luokkaan, $65 ja $60, mikä on juuri se, mitä Excel kirjoittaa

Lukija, joka ohittaa luokkabitit, kieräyttää kaikki kolme onnellisesti, joten jos ylläpidät omaa BIFF8-kirjoittajaasi, vertaa jokaisen operanditokenin luokkabittejä Excelin tallentamaan tiedostoon samasta kaavasta, ei pelkkiä tokeninumeroita

Pikaopas

• Excel 365 näyttää @-merkin, kun operaattori tavallisessa, merkitsemättömässä kaavassa vastaanottaa monisoluisen alueen tai inline-taulukon
• HotXLS v2.384.68 ja uudemmat tallentavat tällaiset kaavat XLSX-yksisoluisina dynaamisina taulukkoina (cm="1", t="array", XLDAPR-metatieto) ja XLS-yksisoluisina taulukkokaavoina (FORMULA PtgExpllä plus ARRAY $0221)
• Vain operaattorien operandit ratkaisevat; alue, joka välitetään suoraan funktion argumentiksi, pysyy tavallisena kaavana
• Vain TXLSXCell.Formulan tai klassisen yksisoluisen Formula / Valuen kautta syötetyt kaavat merkitään; ladatut kaavat jätetään rauhaan
• Muunnettu juurisolu lukee takaisin ilman alussa olevaa =-merkkiä
• Dynaamisen taulukon ext uri-GUIDin on oltava pienellä, muuten Excel hylkää paketin
• Delphissä Double(True) on -1; testaa varBoolean ennen numeerista muunnosta
• BIFF8: taulukkovakiot eivät koskaan viittausluokkaa, PtgIsect / PtgUnion -operandit viittausluokassa, ARRAY-tietueen operandit taulukon luokassa

HotXLS lukee, kirjoittaa ja laskee XLS- ja XLSX-työkirjoja natiivisti Delphistä ja C++Builderista, ja tallentaa operaattoripitoiset taulukkokaavat niin, että Excel 365 avaa ne samoilla arvoilla, jotka HotXLS laski. Tutustu HotXLS Delphi -laskentataulukkokomponenttiin saadaksesi versiot, dokumentaation ja kokeilulatauksen