HotXLS masyvo formulės: kodėl Excel prideda @ ir #VALUE!

Excel 365 įterpia @ į tokią formulę kaip =SUM(A1:B1*{10,100}) ir rodo #VALUE!, kai faile ji saugoma kaip paprasta formulė, nes tada Excel kiekvienam operatoriaus operandui taiko senąjį implicit intersection. Nuo v2.384.68 HotXLS Delphi Component tokias formules su masyvo operatoriais saugo taip pat, kaip Excel 365: XLSX – kaip vieno langelio dinaminio masyvo formules, XLS – kaip vieno langelio masyvo formules

Simptomas išgyvena ir kodo peržiūrą. Jūsų Delphi tarnyba sukuria darbaknygę, HotXLS ją perskaičiuoja ir =SUM(A1:B1*{10,100}) įsirašo į podėlį kaip 210, o klientas atidaro failą Excel 16 ir formulės juostoje mato =SUM(@A1:B1*@{10,100}), langelyje – #VALUE!. Faile nėra nieko sugadinto. Trūksta metaduomenų, pasakančių Excel, jog formulė parašyta pagal dinaminių masyvų taisykles, o be jų Excel grįžta prie senojo, iki dinaminių masyvų buvusio vertinimo modelio

Kodėl Excel 365 prie teisingai apskaičiuotos HotXLS formulės prideda @?

Excel 365 prideda @, nes formulė be dinaminio masyvo žymės apibrėžimo prasme yra senoji formulė, o senosios formulės ten, kur operatorius tikisi vienos reikšmės, kelių langelių diapazoną sutraukia į vieną langelį. Tas sutraukimas ir yra implicit intersection: Excel paima tą diapazono langelį, kuris su formule dalijasi eilute (vertikaliam diapazonui) arba stulpeliu (horizontaliam), o jei tokio langelio nėra, rezultatas – #VALUE!. Excel 365 seno stiliaus formulėms šia prasme išlieka ir rodo @, kad sutraukimas matytųsi

Įrašykite =SUM(A1:B1*{10,100}) į E5, ir senasis skaitymas tampa akivaizdus. A1:B1 yra horizontalus diapazonas, formulė stovi E stulpelyje, o diapazonas E stulpelyje neturi nė vieno langelio, tad @A1:B1 duoda #VALUE!, ir visa SUM tai paveldi. Pagal dinaminių masyvų taisykles tas pats tekstas daugina po elementą, 1 × 10 + 2 × 100, ir grąžina 210. HotXLS formulių variklis taip vertina jau nuo v2.384.61 ir v2.384.63 leidimų; tiesiog failo formato to nebuvo galima perskaityti. Kai A1:B2 laiko 1, 2, 3 ir 4, čia yra bandomosios formulės ir tai, ką rodo Excel 16:

HotXLS diagrama, lyginanti SUM(A1:B1*{10,100}) langelyje E5 vertinimą implicit intersection ir dinaminio masyvo būdu: senasis modelis horizontaliame diapazone A1:B1 E stulpelyje langelio neranda ir grąžina #VALUE!, o dinaminio masyvo modelis 1 sudaugina su 10 ir 2 su 100, grąžindamas 210 — Excel į paprastą formulę įterpia @ ir rodo #VALUE!, nes implicit intersection E stulpelyje nieko neranda; su HotXLS dinaminio masyvo žyme ta pati formulė daugina po elementą ir pasibaigia 210

Formulė	HotXLS rezultatas	Excel 16, saugoma kaip paprasta formulė	Saugoma nuo v2.384.68
`=SUM(A1:B1*{10,100})`	210	`#VALUE!`	Dinaminis masyvas, Excel rodo 210
`=SUM((A1:B2>2)*1)`	2	Implicit intersection, neteisinga arba klaida	Dinaminis masyvas, Excel rodo 2
`=SUMPRODUCT((A1:B2>2)*1)`	2	Implicit intersection, neteisinga arba klaida	Dinaminis masyvas, Excel rodo 2
`=MAX(A1:B2-1)`	3	Implicit intersection, neteisinga arba klaida	Dinaminis masyvas, Excel rodo 3
`=SUM(A1:B2)`	10	10	Paprasta formulė, nepakitusi

Paskutinė eilutė svarbi ne mažiau už pirmas keturias. SUM(A1:B2) diapazoną perduoda tiesiai funkcijos parametrui, kuris priima nuorodas, tad joks operatorius kelių langelių diapazono niekada nemato, ir jokios sankirtos įvykti negali. Pats Excel 365 tą formulę išsaugo kaip paprastą formulę, ir HotXLS daro tą patį

Kaip HotXLS saugo formules su masyvo operatoriais XLSX ir XLS

XLSX formule su masyvo operatoriumi HotXLS užrašo kaip vieno langelio dinaminį masyvą: <c> elementas neša cm="1", formulė yra <f t="array" ref="E5">, o paketas pasipildo xl/metadata.xml su XLDAPR metaduomenų tipu, kurio plėtinys laiko dynamicArrayProperties fDynamic="1". Atributas cm yra vienetu pradedamas indeksas į tos dalies cellMetadata bloką, o už jo stovintis XLDAPR įrašas ir yra tai, kas pasako Excel vertinti formulę pagal dinaminių masyvų taisykles. Tai ta pati struktūra, kurią Excel 16 užrašo, kai tą pačią formulę įrašote ir išsaugojate, – būtent taip ir buvo nustatytas tikslinis išdėstymas

XLS metaduomenų dalies nėra, tad HotXLS naudoja vienintelę BIFF8 konstrukciją masyvo vertinimui: vieno langelio masyvo formulę. Langelis gauna FORMULA įrašą, kurio tokenų srautas yra vienas PtgExp, rodantis į patį save, o paskui seka ARRAY įrašas ($0221) su tikra išanalizuota formule virš vieno langelio diapazono. Excel 365 dinaminio masyvo formules į XLS rašo taip pat, o senesnė Excel versija, skaitydama failą, mato klasikinę Ctrl+Shift+Enter masyvo formulę

HotXLS saugojimo diagrama formulei su masyvo operatoriumi SUM(A1:B1*{10,100}): XLSX variklis užrašo vieno langelio dinaminį masyvą su cm lygiu 1, f elementu tipo array ir XLDAPR įrašu xl/metadata.xml, kurio mažosiomis raidėmis parašytas GUID privalomas, o XLS variklis rašo FORMULA įrašą su PtgExp ir ARRAY įrašą 0221 — XLSX variklis langelį žymi cm=1 ir XLDAPR metaduomenų įrašu, o klasikinis variklis PtgExp FORMULA suporuoja su ARRAY įrašu virš vieno langelio; Excel 365 dinaminį masyvą į XLS išsaugo taip pat

Jokio naujo API čia nereikia. Žymė atsiranda tada, kai formulę priskiriate per įprastą langelio API, abiejuose varikliuose. XLSX pusėje tai 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;

    // Operatorius virš diapazono arba inline masyvo: saugoma kaip dinaminis masyvas
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Diapazonas perduotas tiesiai funkcijai: lieka paprasta <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Masyvo šaknis tekstą laiko be priekinio '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

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

Po konvertavimo TXLSXCell.Formula grąžina tekstą be =, tokia pati forma, kokią saugo TXLSXRange.SetDynamicArrayFormula, tad kodas, lyginantis formulių eilutes po priskyrimo, turėtų suvienodinti priekinį =

Klasikinis variklis tą pačią taisyklę taiko per IXLSRange.Formula vienam langeliui. Priskyrus formulę, ji viduje nukreipiama į vieno langelio masyvo kelią, tad išsaugotame XLS atsiduria FORMULA ir ARRAY pora:

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 įrašas
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY įrašas
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // paprasta FORMULA

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

Jei norite įtvirtinti kelių langelių rezultatą, o ne skaliarinę agreguotą sumą, teisingi įrankiai vis tiek yra tiesioginės API: SetArrayFormula iš anksto suformuotam stačiakampiui, kaip aprašyta straipsnyje dinaminio masyvo spill formulės su HotXLS, arba TXLSXRange.SetDynamicArrayFormula, kai XLSX dinaminio masyvo žymę norite uždėti pačiam suformuotam diapazonui. Automatinis šio straipsnio kelias dengia tik formules, įrašytas į vieną langelį

Kokias formules HotXLS žymi kaip dinamines masyvo formules?

HotXLS formulę žymi tik tada, kai operatorius turi operandų pomedį, kuris duoda masyvą. Patikrinimas vyksta ant sukompiliuotos sintaksės medžio, o operandas duoda masyvą, jei jis yra kelių langelių diapazonas, inline masyvo konstanta arba kito operatoriaus išraiška, kuri pati turi tokį operandą. Skliaustai permatomi. Skaitosi aritmetiniai operatoriai (+ - * / ^), sujungimas (&), šeši palyginimai, unarinis plius ir minus bei procentas:

A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) ir A1:B2-1 žymimos, kur jos beatrodytų formulėje, įskaitant SUMPRODUCT vidų
SUM(A1:B2) ir SUMPRODUCT(A1:A2,{1;10}) nežymimos, nes diapazonas ir masyvas patenka tiesiai į funkcijos argumentą, ir jų joks operatorius nepaliečia
A1*2 arba SUM(A1,B1)*2 nežymimos: vieno langelio nuorodos ir funkcijų rezultatai šiam patikrinimui yra skaliarai

Trys ribos yra sąmoningos. Pirma, žymė atsiranda tik tada, kai formulė įvedama per API, tai yra TXLSXCell.Formula XLSX variklyje ir vieno langelio Formula arba Value priskyrimas klasikiniame variklyje. Formulės, įkeltos iš failo, rašomos atgal lygiai tokios, kokios rastos, nes kitos kilmės senoji formulė implicit intersection gali remtis tyčia. Antra, tekstas, kuriame nėra nei :, nei {, praleidžiamas be antro kompiliavimo. Trečia, formulė, kurios rezultatas išplistų, pavyzdžiui =A1:B1*2 pati savaime, žymima kaip vieno langelio dinaminis masyvas, įtvirtintas ten, kur jį padėjote. HotXLS jos neplėčia, o Excel rezultatą į kaimyninius langelius išplės kitąkart perskaičiavęs

Ši operandų taisyklė yra sesuo argumentų klasės taisyklei, apie kurią rašo implicit intersection apibrėžtiems vardams HotXLS programoje. Tas straipsnis – apie funkcijų parametrus, deklaruotus kaip value klasė; šis – apie operatorius, kurie senajame modelyje visada reikalauja reikšmių

Kas pasikeitė skaičiavimo variklyje, kad rezultatai sutaptų

v2.384.68 saugojimo pataisymas remiasi tuo, kad HotXLS formulių variklis jau grąžina Excel 365 reikšmes, o tai pareikalavo kelių ankstesnių pataisymų abiejuose varikliuose. Matomiausias buvo SUMPRODUCT: iki v2.384.61 jis priimdavo tik du ar daugiau paprastų diapazonų, tad SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) ir net vieno argumento SUMPRODUCT(B1:B2) grąžindavo #N/A. Dabar HotXLS išraiškos argumentus vertina po elementą pagal Excel taisykles:

kiekvienas argumentas turi turėti tiksliai tokią pačią formą, skaliaras skaitomas kaip 1 × 1, kitaip rezultatas – #VALUE!
klaidos reikšmė bet kurio argumento viduje grąžinama kaip rezultatas
tekstiniai ir loginiai elementai skaitomi kaip 0, tad (B1:B2>0)*1 arba -- vis dar reikia, kad TRUE virstų 1
argumentai, kurie visi yra paprasti diapazonai, lieka ant originalios srautinės kilpos, tad dideli diapazonai atmintyje nevirsta masyvais

SUM šeima (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) naudoja tą patį vertintoją po elementą, kai argumentas yra operatoriaus išraiška virš diapazono, tad =SUM((B1:B2>0)*1) suskaičiuoja abi eilutes, o ne žiūri tik į pirmąjį langelį. v2.384.62 padarė, kad tarpo sankirtos operatorius grąžintų dviejų nuorodų bendrąjį stačiakampį, o kai jos nesikerta – #NULL!, tad =SUM(A1:B2 B1:B2) yra 6, o ne 2, ir rezultatas gali maitinti nuorodų parametrus, tokius kaip ROWS ir INDEX. v2.384.63 į parserį pridėjo inline masyvo konstantas, pavyzdžiui {1,2;3,4} (kableliai skiria stulpelius, kabliataškiai – eilutes), ir nuorodų sąjungas, pavyzdžiui (A1:B2,D4). Palyginimai po elementą tuščiam elementui taip pat duoda kitos pusės tipą, FALSE prieš loginę reikšmę, kaip v2.384.53 skaliaro taisyklė, aprašyta straipsnyje palyginimo grandinės ir tušti langeliai HotXLS programoje

var
  V: Variant;
begin
  // Book – TXLSXWorkbook iš pirmojo pavyzdžio;
  // jos aktyviame lape A1:B2 laiko 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, vienas argumentas
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, bendras diapazonas B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, sankirta suskaičiuota du kartus
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, iki v2.384.61 buvo -1
end;

TXLSXWorkbook.Calculate formulės eilutę įvertina prieš aktyvų lapą jos neišsaugodamas – greitas būdas patikrinti variklio elgseną. Viena įspėjimo apie patį @: HotXLS istoriškai @ tarp dviejų nuorodų priimdavo kaip dvinarę sankirtą, ir dabar ta forma vertinama tikra sankirtos semantika. Excel 365 @ yra unarinis implicit intersection priešdėlis. Nerašykite @ į formulės tekstą ir nesitikėkite Excel prasmės; sankirtai naudokite tarpą, o dinaminio masyvo semantiką palikite aukščiau aprašytoms saugojimo taisyklėms

Kodėl Excel atsisakydavo atidaryti failą arba apskaičiuodavo neteisingą reikšmę?

Kad Excel priimtų dinaminio masyvo žymę, prireikė trijų pataisymų, kurių joks savęs patikrinantis round-trip testas nebūtų pagavęs, nes HotXLS savo išvestį visais atvejais skaitė teisingai. Kiekvienas aptiktas atvėrus HotXLS išvestį Excel 16 ir pakeitus po vieną kintamąjį:

Plėtinio GUID turi būti vien mažosiomis raidėmis. ext uri faile xl/metadata.xml turi būti tiksliai {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Senesnis HotXLS šablonas jį rašydavo maišytomis raidėmis, ir Excel 16 atsisakydavo atidaryti visą paketą, o ne tik langelį. Darbaknygės, sukurtos su TXLSXRange.SetDynamicArrayFormula iki v2.384.68, turėjo tą pačią problemą
Masyvo šaknies tekstas neturi priekinio =. XLSX rašytojas saugomą masyvo šaknies tekstą į <f> išveda žodį į žodį. Jei konvertuotas langelis būtų likęs su savo =, elementas atrodytų kaip <f t="array" ref="E5">=SUM(...)</f>, kurį Excel taip pat atmeta atidarydamas. HotXLS jį nupjauna konvertavimo metu, todėl TXLSXCell.Formula skaito be jo
Double(True) Delphi programoje yra -1. Variant konversija seka COM sutartimi, kurioje TRUE yra visi nustatyti bitai, o VarIsNumeric(True) taip pat grąžina True. Iki v2.384.61 dėl to =TRUE*1 grąžindavo -1 ir loginius masyvo elementus leisdavo klasifikuoti kaip skaičius, todėl palyginimas (B1:B2>0)=TRUE klysdavo. Dabar HotXLS prieš laikydamas Variant skaičiumi skaliniame skaičiavime, masyvo skaičiavime ir masyvo elementų klasifikacijoje išbando varBoolean, o TRUE skaitomas kaip 1

BIFF8 operandų klasės: baitų lygio detalės formato įgyvendintojams

BIFF8 kiekvienas operandų tokenas savo operandų klasę neša pačiame tokeno baite, ir Excel ta klasę gerbia labiau negu formulės struktūrą. [MS-XLS] klasę apibrėžia kaip dviejų bitų PtgDataType lauką tokeno 5 ir 6 bituose: 1 nuorodai, 2 reikšmei, 3 masyvui. Žemieji penki bitai įvardija tokeną, todėl ta pati area nuoroda turi tris rašybas:

Tokenas	Reference klasė	Value klasė	Array klasė
`PtgRef`	`$24`	`$44`	`$64`
`PtgArea`	`$25`	`$45`	`$65`
`PtgArray`	`$20`	`$40`	`$60`

HotXLS trijose šių vietose klydo, ir kiekviena davė atskirą simptomą Excel programoje, nors atgal HotXLS skaitydavosi puikiai:

Reference klasės masyvo konstantos. Koduotojas klasę rinkdavosi iš konteksto, o SUM arba ROWS parametrai yra reference klasės, tad =SUM({1,2}) buvo rašoma su PtgArray kaip $20. Excel visą formulę parodydavo kaip =#N/A. Masyvo konstanta niekada negali būti nuoroda, todėl nuo v2.384.63 HotXLS ten, kur kontekstas prašo nuorodos, rašo array klasę $60
Value klasės PtgIsect ir PtgUnion operandai. Dvinariai operatoriai ėmė value klasės operandus, kas teisinga *, bet klaidinga nuorodų operatoriams. Su $45 areomis prieš PtgIsect ($0F) Excel =SUM(A1:B2 B1:B2) skaitydavo kaip =SUM(@A1:B2 @B1:B2) ir grąžindavo #VALUE!. Nuo v2.384.62 PtgIsect ir PtgUnion ($10) operandai rašomi reference klasėje, $25
Value klasės operandai ARRAY įrašo viduje. Excel implicit intersection taiko net masyvo formulės viduje, kai operandas yra value klasės. HotXLS ten rašydavo $45, tad vieno langelio masyvo formulė =SUM(A1:B1*{10,100}) Excel programoje įvertindavo į 10. Nuo v2.384.68 ARRAY įrašo tokenų srautas kiekvieną value klasės nuorodą ir masyvo konstantą pakelia į array klasę, $65 ir $60, – būtent taip rašo Excel

HotXLS BIFF8 diagrama: kiekvieno tokeno baito 5 ir 6 bitai renkasi reference, value arba array klasę, tad PtgArea rašosi kaip 25, 45 ir 65, su trimis sutvarkytais defektais: masyvo konstantos kaip 20 rodė #N/A, PtgIsect operandai kaip 45 grąžindavo #VALUE!, o ARRAY įrašo operandai kaip 45 privertė SUM(A1:B1*{10,100}) grąžinti 10 — Kiekvienas BIFF8 operandų tokenas savo klasę neša 5 ir 6 bituose, ir Excel tais bitais pasitiki labiau negu struktūra; HotXLS masyvo konstantas rašo kaip 60, PtgIsect operandus kaip 25, o ARRAY įrašo tokenus pakelia į array klasę

Skaitytuvas, kuris klasės bitų nepaiso, visus tris apvalina laimingai, tad jei palaikote savą BIFF8 rašytoją, lyginkite kiekvieno operandų tokeno klasės bitus su tos pačios formulės Excel išsaugotu failu, o ne tik tokenų numeriais

Trumpa atmintinė

Excel 365 rodo @, kai operatorius paprastoje, nežymėtoje formulėje gauna kelių langelių diapazoną arba inline masyvą
HotXLS v2.384.68 ir vėlesnės tokias formules saugo kaip XLSX vieno langelio dinaminio masyvo formules (cm="1", t="array", XLDAPR metaduomenys) ir kaip XLS vieno langelio masyvo formules (FORMULA su PtgExp plius ARRAY $0221)
Skaitosi tik operatorių operandai; diapazonas, perduotas tiesiai į funkcijos argumentą, lieka paprasta formulė
Žymimos tik formulės, įvestos per TXLSXCell.Formula arba klasikinį vieno langelio Formula / Value; įkeltos formulės neliečiamos
Konvertuota šakninė langelė skaitosi be priekinio =
Dinaminio masyvo ext uri GUID turi būti mažosiomis raidėmis, kitaip Excel atmeta paketą
Delphi programoje Double(True) yra -1; prieš skaitinę konversiją patikrinkite varBoolean
BIFF8: masyvo konstantos niekada ne reference klasės, PtgIsect / PtgUnion operandai – reference klasės, ARRAY įrašo operandai – array klasės

HotXLS XLS ir XLSX darbaknyges iš Delphi ir C++Builder skaito, rašo ir skaičiuoja natyviai, o formules su masyvo operatoriais saugo taip, kad Excel 365 jas atidaro su tomis pačiomis reikšmėmis, kurias apskaičiavo HotXLS. Leidimus, dokumentaciją ir bandomąją versiją rasite puslapyje HotXLS Delphi skaičiuoklės komponentas