Teknik Makale

HotXLS dizi formülleri: Excel neden @ ve #VALUE! ekler?

Excel 365, =SUM(A1:B1*{10,100}) gibi bir formülün içine @ ekler ve dosya onu düz bir formül olarak sakladığında #VALUE! gösterir; çünkü Excel bu durumda her operatör operandına eski implicit intersection'ı uygular. HotXLS Delphi Component bu operatörlü dizi formüllerini Excel 365'in yaptığı gibi saklar: XLSX'te tek hücreli dinamik dizi formülleri, XLS'te tek hücreli dizi formülleri olarak

Belirti kod incelemesinden de kaçar. Bir Delphi servisi çalışma kitabı yazar, formül motoru düz bir formül hücresindeki =SUM(A1:B1*{10,100}) için 210'u cache'ler; müşteri dosyayı Excel 16'da açtığında formül çubuğunda =SUM(@A1:B1*@{10,100}) ve hücrede #VALUE! bulur. Dosyada bozuk olan hiçbir şey yoktur. Eksik olan, Excel'e formülün dinamik dizi kuralları altında yazıldığını söyleyen metadata'dır; o olmadan Excel dinamik dizi öncesi değerlendirme modeline geri düşer

Excel 365, doğru hesaplanmış bir formüle neden @ ekler?

Excel 365 @ ekler, çünkü dinamik dizi işaretlemesi taşımayan bir formül tanım gereği eski (legacy) bir formüldür ve eski formüller, bir operatör tek değer beklediği her yerde çok hücreli bir aralığı tek hücreye indirger. Bu indirgeme implicit intersection'dır: Excel, aralığın formülle aynı satırı (dikey aralıkta) ya da sütunu (yatay aralıkta) paylaşan hücresini alır; öyle bir hücre yoksa sonuç #VALUE! olur. Excel 365 bu anlamı eski tarz formüller için korur ve indirgemeyi görünür kılmak için @ gösterir

=SUM(A1:B1*{10,100})'ı E5'e koyun, eski okuma apaçık hâle gelir. A1:B1 yatay bir aralıktır, formül E sütununda durur, aralığın E sütununda hücresi yoktur; dolayısıyla @A1:B1 #VALUE! olur ve bütün SUM onu devralır. Dinamik dizi kuralları altında aynı metin eleman eleman çarpar, 1 × 10 + 2 × 100, ve 210 döndürür. HotXLS formül motoru dinamik dizi yoluyla değerlendirir ve dosya biçimi de aynı şeyi söylemek zorundadır. A1:B2'de 1, 2, 3 ve 4 varken işte test formülleri ve Excel 16'nın gösterdikleri:

E5 hücresindeki SUM(A1:B1*{10,100}) için implicit intersection ile dinamik dizi değerlendirmesini karşılaştıran HotXLS şeması: eski model yatay A1:B1 aralığının E sütununda hücresi bulamaz ve #VALUE! döndürür, dinamik dizi modeli ise 1'i 10'la, 2'yi 100'le çarpar ve 210 döndürür
Excel düz formülün içine @ ekler ve #VALUE! gösterir, çünkü implicit intersection E sütununda hiçbir şey bulamaz; HotXLS dinamik dizi işaretlemesiyle aynı formül eleman eleman çarpar ve 210'a ulaşır
FormülHotXLS sonucuExcel 16, düz formül olarak saklandığındaHotXLS'in saklayışı
=SUM(A1:B1*{10,100})210#VALUE!Dinamik dizi, Excel 210 gösterir
=SUM((A1:B2>2)*1)2Implicit intersection, yanlış ya da hataDinamik dizi, Excel 2 gösterir
=SUMPRODUCT((A1:B2>2)*1)2Implicit intersection, yanlış ya da hataDinamik dizi, Excel 2 gösterir
=MAX(A1:B2-1)3Implicit intersection, yanlış ya da hataDinamik dizi, Excel 3 gösterir
=SUM(A1:B2)1010Düz formül, değişmedi

Son satır ilk dördü kadar önemlidir. SUM(A1:B2), aralığı doğrudan referans kabul eden bir fonksiyon parametresine geçer; böylece hiçbir operatör çok hücreli bir aralık görmez ve hiçbir kesişim oluşamaz. Excel 365'in kendisi o formülü düz formül olarak kaydeder, HotXLS de aynısını yapar

HotXLS, operatörlü dizi formüllerini XLSX ve XLS'te nasıl saklar?

HotXLS, operatörlü bir dizi formülünü XLSX'te tek hücreli bir dinamik dizi olarak yazar: <c> elementi cm="1" taşır, formül <f t="array" ref="E5"> olur ve paket, uzantısında dynamicArrayProperties fDynamic="1" barındıran XLDAPR metadata tipiyle xl/metadata.xml kazanır. cm attribute'ı o part'ın cellMetadata bloğuna 1 tabanlı bir indekstir ve ardındaki XLDAPR kaydı, Excel'e "bunu dinamik dizi kuralları altında değerlendir" diyen şeydir. Bu yapı, aynı formülü yazıp kaydettiğinizde Excel 16'nın yazdığı yapıyla aynıdır; hedef düzen zaten böyle kurulmuştu

XLS'te metadata part'ı yoktur; dolayısıyla HotXLS, BIFF8'in dizi değerlendirmesi için sahip olduğu tek yapıyı kullanır: tek hücreli bir dizi formülü. Hücre, token stream'i kendine işaret eden tek bir PtgExp olan bir FORMULA record alır; ardından tek hücrelik aralık üzerinde gerçek ayrıştırılmış formülü taşıyan bir ARRAY record ($0221) gelir. Excel 365 dinamik dizi formüllerini XLS'e aynı şekilde yazar ve dosyayı okuyan eski bir Excel sürümü klasik bir Ctrl+Shift+Enter dizi formülü görür

Operatörlü SUM(A1:B1*{10,100}) formülü için HotXLS saklama şeması: XLSX motoru cm=1 ile tek hücreli dinamik dizi, array tipli bir f elementi ve küçük harfli GUID'i zorunlu olan xl/metadata.xml içinde bir XLDAPR kaydı yazar; XLS motoru ise PtgExp'li bir FORMULA record artı ARRAY record 0221 yazar
XLSX motoru hücreyi cm=1 artı bir XLDAPR metadata kaydıyla işaretler, klasik motor tek hücre üzerinde PtgExp'li FORMULA'yı bir ARRAY record'la eşleştirir; Excel 365 dinamik dizileri XLS'e aynı şekilde kaydeder

Yeni bir API söz konusu değildir. İşaretleme, formülü normal hücre API'siyle atadığınızda gerçekleşir, iki motorda da. XLSX tarafında bu TXLSXCell.Formula'dır:

uses
  SysUtils, Variants, lxCalc, lxHandle, lxHandleX;   // lxOk, lxCalc içinde tanımlıdır

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;

    // Aralık ya da inline array üzerinde operatör: dinamik dizi olarak saklanır
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Doğrudan fonksiyona geçen aralık: sıradan <f> olarak kalır
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

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

    // Dizi kökü metnini başındaki '=' olmadan tutar
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 ve E6 cm="1" + t="array" alır
  finally
    Book.Free;
  end;
end;

Dönüşümden sonra TXLSXCell.Formula, metni = olmadan döndürür; TXLSXRange.SetDynamicArrayFormula'ın sakladığı biçim de budur, dolayısıyla atamadan sonra formül string'lerini karşılaştıran kod baştaki ='i normalleştirmelidir

Klasik motor aynı kuralı tek hücrede IXLSRange.Formula üzerinden izler. Formülü atamak, onu içeride tek hücrelik dizi yoluna yönlendirir; dolayısıyla kaydedilen XLS, FORMULA artı ARRAY çiftini içerir:

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 record
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY record
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // düz FORMULA

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

Skaler bir toplam yerine çok hücreli bir sonuç sabitliyorsanız doğru araç hâlâ açık API'ler: önceden boyutlanmış dikdörtgen için SetArrayFormula — HotXLS ile dinamik dizi spill formülleri yazısında anlatıldığı gibi — ya da kendinizin boyutlandırdığı bir aralıkta XLSX dinamik dizi işaretlemesini istiyorsanız TXLSXRange.SetDynamicArrayFormula. Bu yazıdaki otomatik yol yalnızca tek hücreye yazılan formülleri kapsar

HotXLS hangi formülleri dinamik dizi olarak işaretler?

HotXLS bir formülü yalnızca bir operatörün dizi üreten bir operand alt ağacı olduğunda işaretler. Kontrol derlenmiş sözdizimi ağacı üzerinde koşar; bir operand, çok hücreli bir aralık, bir inline array sabiti ya da kendisi de böyle bir operand barındıran başka bir operatör ifadesiyse dizi üretir. Parantezler şeffaftır. Sayılan operatörler aritmetik olanlar (+ - * / ^), birleştirme (&), altı karşılaştırma, tek terimli artı ve eksi ve yüzde işaretidir:

  • A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) ve A1:B2-1 işaretlenir; formül içinde nerede dururlarsa dursunlar, SUMPRODUCT içindekiler dâhil
  • SUM(A1:B2) ve SUMPRODUCT(A1:A2,{1;10}) işaretlenmez; çünkü aralık ile dizi doğrudan bir fonksiyon argümanına girer ve hiçbir operatör onlara dokunmaz
  • A1*2 ya da SUM(A1,B1)*2 işaretlenmez: tek hücreli referanslar ve fonksiyon sonuçları bu kontrol için skalerdir

Üç sınır kasıtlıdır. Birincisi, işaretleme yalnızca formül API üzerinden girildiğinde olur; XLSX motorunda bu TXLSXCell.Formula, klasik motorda ise tek hücreli bir Formula ya da Value atamasıdır. Dosyadan yüklenen formüller bulundukları hâldeyle geri yazılır; çünkü başka bir üreticiden gelen eski bir formül bilerek implicit intersection'a bel bağlıyor olabilir. İkincisi, ne : ne { içeren metin ikinci bir derleme olmadan atlanır. Üçüncüsü, spill edecek bir formül — tek başına =A1:B1*2 gibi — sizin bıraktığınız yere sabitlenmiş tek hücreli bir dinamik dizi olarak işaretlenir. HotXLS onu spill etmez; Excel bir dahaki yeniden hesaplamada sonucu komşu hücrelere genişletir

Bu operand kuralı, HotXLS'te defined name'ler için implicit intersection yazısındaki argüman-class kuralının kardeşidir. O yazı value class olarak bildirilen fonksiyon parametrelerinden bahseder; bu ise, eski modelde daima değer talep eden operatörlerden söz eder

Hesaplama motoru Excel 365 değerleriyle nasıl eşleşir

Depolama işaretlemesi, formül motoru her iki motorda da baştan Excel 365 değerlerini döndürüyorsa anlam kazanır. En görünür olgu SUMPRODUCT'tır: yalnızca iki ya da daha fazla düz aralık kabul eden bir uygulama, SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) ve hatta tek argümanlı SUMPRODUCT(B1:B2) için #N/A döndürür. HotXLS ifade argümanlarını Excel'in kurallarıyla eleman eleman değerlendirir:

  • her argüman tam olarak aynı şekli taşımalı, skaler 1 × 1 sayılır; yoksa sonuç #VALUE! olur
  • herhangi bir argümanın içindeki bir hata değeri sonuç olarak döndürülür
  • metin ve mantıksal elementler 0 sayılır; dolayısıyla TRUE'yu 1'e çevirmek için (B1:B2>0)*1 ya da -- hâlâ gerekir
  • tamamı düz aralık olan argümanlar özgün streaming döngüsünü korur; böylece büyük aralıklar dizi olarak maddileştirilmez

SUM ailesi (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA), bir argüman aralık üzerinde bir operatör ifadesi olduğunda aynı eleman eleman değerlendiriciyi kullanır; böylece =SUM((B1:B2>0)*1) yalnızca ilk hücreye bakmak yerine iki satırı da sayar. Boşluk kesişim işleci iki referansın ortak dikdörtgenini döndürür, örtüşmezlerse #NULL!; dolayısıyla =SUM(A1:B2 B1:B2) 2 yerine 6'dır ve sonuç, ROWS ile INDEX gibi referans parametrelerine beslenebilir. Ayrıştırıcı, {1,2;3,4} gibi satır içi dizi sabitlerini (virgüller sütunları, noktalı virgüller satırları ayırır) ve (A1:B2,D4) gibi referans birleşimlerini kabul eder. Eleman eleman karşılaştırmalar da boş elemana karşı tarafın tipini verir; mantıksala karşı FALSE, HotXLS'te karşılaştırma zincirleri ve boş hücreler yazısında anlatılan skaler kuralıyla aynıdır

var
  V: Variant;
begin
  // Book, ilk örnekteki TXLSXWorkbook'tur;
  // etkin sayfası A1:B2 = 1, 2, 3, 4 tutar
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, tek argüman
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, ortak aralık B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, örtüşme iki kez sayılır
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, TRUE 1 sayılır
end;

TXLSXWorkbook.Calculate bir formül dizesini saklamadan etkin sayfaya karşı değerlendirir; motor davranışını kontrol etmenin hızlı yoludur. @'nin kendisi hakkında bir uyarı: HotXLS iki referans arasındaki @'yi ikili kesişim olarak kabul eder ve o biçimi gerçek kesişim semantiğiyle değerlendirir. Excel 365'te @ tek terimli bir implicit-intersection önekidir. Formül metnine Excel'in anlamında bir @ yazmayın; kesişim için boşluk kullanın ve dinamik dizi semantiğini yukarıdaki saklama kurallarına bırakın

Excel, dinamik dizi çıktısını açarken denetlediği üç ayrıntı

Excel'in dinamik dizi işaretlemesini kabul etmesi, kendi kendine gidiş-dönüş testinin yakalayamadığı üç ayrıntıya indirgenir; çünkü bir kütüphane kendi çıktısını her iki durumda da doğru geri okur. Her biri yalnızca çıktıyı Excel 16'da açıp birer birer değişken değiştirdiğinizde ortaya çıkar:

  1. Uzantı GUID'i tamamen küçük harf olmalıdır. xl/metadata.xml'deki ext uri, tam olarak {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} olmalıdır. Karışık harf boyuyla yazın ve Excel 16, yalnızca hücreyi değil bütün paketi açmayı reddeder
  2. Dizi kök metni baştaki ='i taşımaz. XLSX yazıcısı, dizi kökünün saklanan metnini <f>'e harfiyen basar. Dönüştürülen hücre ='ini korumuş olsaydı element <f t="array" ref="E5">=SUM(...)</f> okurdu; Excel bunu da açılışta reddeder. HotXLS onu dönüşüm sırasında soyar; TXLSXCell.Formula'nın onsuz geri okunmasının nedeni budur
  3. Delphi'de Double(True) -1'dir. Variant dönüşümü, TRUE'nun tüm bitlerin ayarlı olduğu COM kuralını izler ve VarIsNumeric(True) da True döndürür. Naif bir dönüşüm bu yüzden =TRUE*1'i -1 yapar ve mantıksal dizi elemanlarının sayı diye sınıflandırılmasına izin verir; (B1:B2>0)=TRUE gibi bir karşılaştırma yanlış gider. HotXLS, bir Variant'ı skaler aritmetikte, dizi aritmetiğinde ve dizi elemanı sınıflandırmasında sayı olarak ele almadan önce varBoolean testi yapar ve TRUE 1 sayılır

BIFF8 operand class'ları: biçim uygulayıcıları için bayt düzeyi ayrıntılar

BIFF8'de her operand token, operand class'ını token baytının kendisinde taşır ve Excel o class'a formülün yapısından çok güvenir. [MS-XLS], class'ı token'ın 5 ve 6. bitlerindeki iki bitlik bir PtgDataType alanı olarak tanımlar: reference için 1, value için 2, array için 3. Alçak beş bit token'ı adlandırır; böylece aynı area referansının üç yazılışı vardır:

TokenReference classValue classArray class
PtgRef$24$44$64
PtgArea$25$45$65
PtgArray$20$40$60

BIFF8 yazıcısında yanlış yapması kolay üç yer vardır ve her biri, yazıcının kendi okuyucusunda sorunsuz geri okunurken Excel'de ayrı bir belirti üretir:

  • Referans sınıflı dizi sabitleri. Bağlamdan sınıf seçen bir kodlayıcı, SUM ya da ROWS parametrelerinin referans sınıfı olduğunu görür ve =SUM({1,2})'i PtgArray $20 ile yazar. Excel bütün formülü =#N/A olarak gösterir. Bir dizi sabiti asla referans olamaz; HotXLS bu yüzden bağlam referans istediğinde dizi sınıfı $60 yazar
  • PtgIsect ile PtgUnion'un değer sınıflı operandları. İkili işleçlere değer sınıflı operand vermek * için doğrudur ama referans işleçleri için yanlıştır. PtgIsect'ten ($0F) önce $45 alanlarla Excel, =SUM(A1:B2 B1:B2)'yi =SUM(@A1:B2 @B1:B2) olarak okur ve #VALUE! döndürür. HotXLS, PtgIsect ile PtgUnion'un ($10) operandlarını referans sınıfında, $25 olarak yazar
  • ARRAY record'un içindeki değer sınıflı operandlar. Excel, bir operand değer sınıfındayken dizi formülünün içinde bile implicit intersection uygular. Orada $45 varken =SUM(A1:B1*{10,100})'in tek hücreli dizi formülü Excel'de 10'a değerlenir. HotXLS bu yüzden bir ARRAY record'un token stream'inde her değer sınıflı referansı ve dizi sabitini dizi sınıfına, $65 ve $60'a yükseltir; Excel'in yazdığı da budur
HotXLS BIFF8 şeması: her token baytının 5 ve 6. bitleri reference, value ya da array class'ı seçer, böylece PtgArea 25, 45 ve 65 olarak yazılır; üç düzeltilmiş kusur: 20 olarak yazılan dizi sabitleri #N/A gösterdi, 45 olarak yazılan PtgIsect operandları #VALUE! döndürdü ve 45 olarak yazılan ARRAY record operandları SUM(A1:B1*{10,100})'ün 10 döndürmesine yol açtı
Her BIFF8 operand token class'ını 5 ve 6. bitlerde taşır ve Excel o bitlere yapıdan çok güvenir; HotXLS dizi sabitlerini 60 olarak, PtgIsect operandlarını 25 olarak yazar ve ARRAY record token'larını array class'a terfi ettirir

Class bit'lerini yok sayan bir okuyucu üçünü de huzur içinde round-trip eder; dolayısıyla kendi BIFF8 yazıcınızı sürdürüyorsanız her operand token'ın class bit'lerini yalnızca token numaralarıyla değil, aynı formülün Excel ile kaydedilmiş bir dosyasıyla karşılaştırın

Hızlı başvuru

  • Bir düz, işaretsiz formüldeki bir operatör çok hücreli bir aralık ya da satır içi dizi aldığında Excel 365 @ gösterir
  • HotXLS bu tür formülleri XLSX tek hücreli dinamik diziler olarak (cm="1", t="array", XLDAPR metadata) ve XLS tek hücreli dizi formülleri olarak (PtgExp'li FORMULA artı ARRAY $0221) saklar
  • Yalnızca operatör operandları sayılır; doğrudan bir fonksiyon argümanına geçen aralık düz formül olarak kalır
  • Yalnızca TXLSXCell.Formula ya da klasik tek hücreli Formula / Value ile girilen formüller işaretlenir; dosyadan yüklenen formüllere dokunulmaz
  • Dönüştürülen kök hücre, baştaki = olmadan geri okunur
  • Dinamik dizi ext uri GUID'i küçük harf olmalıdır; yoksa Excel paketi reddeder
  • Delphi'de Double(True) -1'dir; sayısal dönüşümden önce varBoolean testi yapın
  • BIFF8: dizi sabitleri asla referans sınıfı olmaz, PtgIsect / PtgUnion operandları referans sınıfında, ARRAY record operandları dizi sınıfında

HotXLS, XLS ve XLSX çalışma kitaplarını Delphi ve C++Builder'dan yerel olarak okur, yazar ve hesaplar; operatörlü dizi formüllerini, Excel 365 onları HotXLS'in hesapladığı değerlerle açsın diye saklar. Sürümler, dokümantasyon ve deneme indirmesi için HotXLS Delphi elektronik tablo bileşeni sayfasına bakın