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. v2.384.68'den beri HotXLS Delphi Component bu operatörlü dizi formüllerini Excel 365'in yaptığı gibi saklıyor: XLSX'te tek hücreli dinamik dizi formülleri, XLS'te ise tek hücreli dizi formülleri olarak

Belirti kod incelemesinden de kaçar. Delphi servisiniz bir çalışma kitabı yazar, HotXLS onu yeniden hesaplar ve =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öner

Excel 365, HotXLS'in doğru hesapladığı 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 v2.384.61 ve v2.384.63 sürümlerinden beri dinamik dizi yoluyla değerlendiriyor; dosya biçimi bunu sadece söylemiyordu. A1:B2'de 1, 2, 3 ve 4 varken işte test formülleri ve Excel 16'nın gösterdikleri:

Formül	HotXLS sonucu	Excel 16, düz formül olarak saklandığında	v2.384.68'den beri saklanışı
=SUM(A1:B1*{10,100})	210	#VALUE!	Dinamik dizi, Excel 210 gösterir
=SUM((A1:B2>2)*1)	2	Implicit intersection, yanlış ya da hata	Dinamik dizi, Excel 2 gösterir
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, yanlış ya da hata	Dinamik dizi, Excel 2 gösterir
=MAX(A1:B2-1)	3	Implicit intersection, yanlış ya da hata	Dinamik dizi, Excel 3 gösterir
=SUM(A1:B2)	10	10	Dü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

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:

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

Sonuçların eşleşmesini sağlamak için hesaplama motorunda ne değişti?

v2.384.68'deki saklama düzeltmesi, HotXLS formül motorunun çoktan Excel 365 değerlerini döndürüyor olmasına dayanır; bu da iki motorda da birkaç önceki düzeltme aldı. En görünür olanı SUMPRODUCT'tu: v2.384.61'e dek yalnızca iki ya da daha fazla düz aralık kabul ediyordu; dolayısıyla SUMPRODUCT((B1:B2>0)*1), SUMPRODUCT(--(B1:B2>0)) ve hatta tek argümanlı SUMPRODUCT(B1:B2) #N/A döndürüyordu. HotXLS artık ifade argümanlarını Excel'in kurallarıyla eleman eleman değerlendiriyor:

• 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 üzerinden bir operatör ifadesi olduğunda aynı eleman bazında değerlendiriciyi kullanır; dolayısıyla =SUM((B1:B2>0)*1) yalnızca ilk hücreye bakmak yerine iki satırı da sayar. v2.384.62, boşluk kesişim operatörünün iki referansın ortak dikdörtgenini döndürmesini sağladı; örtüşmuyorlarsa #NULL! olur, böylece =SUM(A1:B2 B1:B2) 2 değil 6'dır ve sonuç ROWS ile INDEX gibi referans parametrelerini besleyebilir. v2.384.63 ayrıştırıcıya {1,2;3,4} gibi inline array sabitlerini (virgüller sütunları, noktalı virgüller satırları ayırır) ve (A1:B2,D4) gibi referans birleşimlerini ekledi. Eleman bazında karşılaştırmalar ayrıca boş bir elemente karşı tarafın tipini verir, mantıksala karşı FALSE; v2.384.53'ten kalma skaler kuralla uyumludur, ayrıntısı HotXLS'te karşılaştırma zincirleri ve boş hücreler yazısında

var
  V: Variant;
begin
  // Book, ilk örnekteki TXLSXWorkbook;
  // aktif sayfası A1:B2 = 1, 2, 3, 4 içerir
  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, v2.384.61 öncesinde -1'di
end;

TXLSXWorkbook.Calculate, bir formül string'ini saklamadan aktif sayfaya karşı değerlendirir; motor davranışını hızlıca denemenin yoludur. @'nin kendisi hakkında bir uyarı: HotXLS tarih boyunca @'yi iki referans arasında ikili bir kesişim olarak kabul etti ve artık o biçimi gerçek kesişim semantiğiyle değerlendiriyor. Excel 365'te @ bir tek terimli implicit-intersection önekidir. Formül metnine @ yazıp Excel'in anlamını beklemeyin; kesişim için boşluk kullanın ve dinamik dizi semantiğini yukarıdaki saklama kurallarına bırakın

Excel neden dosyayı açmayı reddetti ya da yanlış değer hesapladı?

Excel'i dinamik dizi işaretlemesini kabul ettirmek, kendi kendine round-trip testinin yakalayamayacağı üç düzeltme aldı; çünkü HotXLS kendi çıktısını her durumda doğru okuyordu. Her biri, HotXLS çıktısı Excel 16'da açılarak ve birer birer değişken değiştirilerek bulundu:

1. Uzantı GUID'i tamamen küçük harf olmalı. xl/metadata.xml'deki ext uri, tam olarak {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3} olmak zorunda. Eski bir HotXLS şablonu onu karışık harfle yazıyordu ve Excel 16 yalnızca hücreyi değil bütün paketi açmayı reddetti. v2.384.68 öncesinde TXLSXRange.SetDynamicArrayFormula ile oluşturulan çalışma kitapları da aynı sorunu taşıyordu
2. Dizi kökü metni başında = taşımaz. XLSX yazıcısı, bir dizi kökünün saklanan metnini <f> içine aynen boşaltır. Dönüştürülen hücre ='ini korusaydı element <f t="array" ref="E5">=SUM(...)</f> okurdu; Excel bunu da açılış sırasında reddeder. HotXLS onu dönüşüm sırasında soyar; TXLSXCell.Formula'nın onu olmadan okumasının nedeni budur
3. Double(True) Delphi'de -1'dir. Variant dönüşümü, TRUE'nun tüm bitleri kümesi olduğu COM kuralını izler ve VarIsNumeric(True) da True döndürür. v2.384.61 öncesinde bu, =TRUE*1'in -1 döndürmesini ve mantıksal dizi elementlerinin sayı olarak sınıflandırılmasını sağlıyordu; dolayısıyla (B1:B2>0)=TRUE gibi bir karşılaştırma şaşıyordu. HotXLS artık skaler aritmetikte, dizi aritmetiğinde ve dizi element sınıflandırmasında bir Variant'ı 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:

Token	Reference class	Value class	Array class
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS bunlardan üçünü farklı yerlerde yanlış yazdı ve her biri, HotXLS'te geri okunurken Excel'de ayrı bir belirti üretti:

• Reference-class dizi sabitleri. Encoder class'ı bağlamdan seçiyordu ve SUM ya da ROWS parametreleri reference class'tır; dolayısıyla =SUM({1,2}), PtgArray ile $20 olarak yazılıyordu. Excel bütün formülü =#N/A olarak gösterir. Bir dizi sabiti asla referans olamaz; dolayısıyla v2.384.63'ten beri HotXLS, bağlam referans istediğinde array class $60 yazar
• PtgIsect ile PtgUnion'ın value-class operandları. İkili operatörler value-class operand alıyordu; * için doğru ama referans operatörleri için yanlış. PtgIsect ($0F) öncesinde $45 alanlar varken Excel, =SUM(A1:B2 B1:B2)'yi =SUM(@A1:B2 @B1:B2) olarak okudu ve #VALUE! döndürdü. v2.384.62'den beri PtgIsect ve PtgUnion'ın ($10) operandları reference class, $25 olarak yazılıyor
• ARRAY record içindeki value-class operandlar. Excel, bir operand value class olduğunda dizi formülünün içinde bile implicit intersection uygular. HotXLS oraya $45 yazıyordu; dolayısıyla =SUM(A1:B1*{10,100})'in tek hücrelik dizi formülü Excel'de 10'a değerleniyordu. v2.384.68'den beri bir ARRAY record'ın token stream'i her value-class referansı ve dizi sabitini array class'a, $65 ve $60'a terfi ettirir; Excel'in yazdığı da budur

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

• Excel 365, düz ve işaretsiz bir formüldeki bir operatör çok hücreli bir aralık ya da inline array aldığında @ gösterir
• HotXLS v2.384.68 ve sonrası bu formülleri XLSX'te tek hücreli dinamik diziler (cm="1", t="array", XLDAPR metadata) ve XLS'te tek hücrelik dizi formülleri (PtgExp'li FORMULA artı ARRAY $0221) olarak 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 üzerinden girilen formüller işaretlenir; 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 olmak zorunda; yoksa Excel paketi reddeder
• Delphi'de Double(True) -1'dir; sayısal dönüşümden önce varBoolean test edin
• BIFF8: dizi sabitleri asla reference class değildir, PtgIsect / PtgUnion operandları reference class'ta, ARRAY record operandları array class'ta yazılır

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