Gizli satırlar içeren bir çalışma kitabında SUBTOTAL(109, ...) ve SUBTOTAL(9, ...) aynı sayıyı döndürüyorsa, ikisinden biri yanlıştır. Delphi ve C++Builder için yerel Excel elektronik tablo bileşeni olan HotXLS, sürüm 2.197.0'a kadar tam olarak böyle davranıyordu, çünkü hesaplama motorunun bir çalışma sayfasına belirli bir satırın gizli olup olmadığını sorma yolu yoktu
Belirti nadiren formül kodları hakkında bir hata raporu olarak gelir. Bir uyumsuzluk olarak gelir: sunucudaki bir toplu iş bir toplam hesaplar, bir kullanıcı aynı dosyayı bir filtre uygulanmış Excel'de açar ve iki sayı, filtrelenmiş dışarıda kalan satırların toplamı kadar farklıdır. Kimse toplama fonksiyonundan şüphelenmez, çünkü hücredeki formül dizesi her iki yerde de aynıdır. Fark tamamen değerlendiricinin görmesine izin verilen şeydedir
SUBTOTAL 109 gizli satırları neden içerir?
Çünkü çoğu motor tasarımında bir formülü değerlendiren katman, satır görünürlüğünü hiçbir zaman öğrenmez. HotXLS ders kitabı vakasıydı: lxCalc.pas içindeki hesaplama motoru, hücre değerlerine, bir (sayfa, satır, sütun) üçlüsü için bir değerle yanıt veren ve başka hiçbir şey yapmayan tek bir TXLSGetValue callback'i üzerinden ulaşıyordu. Görünürlük, satır kaydında saklanan bir sunum özelliğidir ve o kaydın hiçbir parçası çağrı zincirinde aşağı seyahat etmiyordu. Motorun bu yüzden tek bir toplama yolu vardı ve SUBTOTAL fonksiyon-numarası tablosunun her iki yarısı da ona çözülüyordu. Bu bir yuvarlama hatası sınıfı kusur değildir: tablonun ikinci yarısının var olmasının tüm nedeni budur. ISO/IEC 29500-1 olarak yayınlanan ECMA-376 Bölüm 1, formül fonksiyon tanımlarında (§18.17.7) hem iç toplamayı hem de gizli-satır politikasını seçen bir ilk argümanla SUBTOTAL'ı tanımlar. 1'den 11'e kadar kodlar, manuel olarak gizlenmiş satırlardaki değerleri dahil ederken AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR ve VARP'ye eşlenir. 101'den 111'e kadar kodlar aynı on bir toplamayı seçer ve onları hariç tutar. 9 yerine 109 yazan bir kullanıcı, gizli veri hakkında kasıtlı bir beyanda bulunuyordur ve ayrımı çökerten bir motor bu beyanı sessizce geçersiz kılar
Fonksiyon numaraları motorun içinde neye eşlenir
HotXLS, SUBTOTAL ilk argümanını CalcSubtotalFunc'te çözer; bu, 101'den 111'e kadar olan kodları 1'den 11'e kadar olan kodlarla aynı iç fonksiyon tanımlayıcılarına normalleştirir ve ardından toplamanın kendisi üzerinde gönderim yapar. Ailenin çoğu, SUM, COUNT, COUNTA, MIN, MAX ve AVERAGE'i işleyen artımlı ExcelSum biriktiricisinden akar. Beşi bunu yapamaz: STDEV, VAR, STDEVP, VARP ve PRODUCT, veri üzerinde kapalı-form bir geçiş gerektirir, bu yüzden CalcSubtotalFunc, 12, 46, 193, 194 ve 183 iç kodlarını ayrı bir indirgeyiciye, SubtotalReduceVariance'a yönlendirir. Bu ayrım, herhangi bir şeye dokunmadan önce haritalanmaya değer ilk şeydir, çünkü iki bağımsız toplama yolu, iki bağımsız hücre-gezinme döngüsü demektir ve yalnızca birine uygulanan bir düzeltme mümkün olan en kötü sonucu üretir: SUBTOTAL(109, ...) filtreye saygı gösterirken aynı aralıktaki SUBTOTAL(107, ...) göstermez. AGGREGATE dahil edildiğinde HotXLS'te döngüleri saymak, aralık değerlendirmesine, düz aralık toplamasına ve üç ayrı indirgeyiciye dağılmış altısını ortaya çıkardı
Neden altı yeni imza yerine bir taslak alan?
Çünkü altı hücre-gezinme fonksiyonu boyunca ve onları çağıran her şey boyunca yeni bir parametreyi geçirmek, tek bir boolean uğruna sıcak bir kod yoluna geniş bir değişikliktir. HotXLS'in alternatif için zaten bir emsali vardı: GetRangeInfo'nun bir 3D referansın harici bir çalışma kitabına çözüldüğü zamanı kaydetmek için kullandığı taslak alanla aynı ruhla, hesaplayıcı üzerinde geçici bir alan. Sürüm 2.197.0 ikinci bir tane ekledi. Motor, (SheetIndex, satır) fonksiyonu olarak Boolean döndüren, FIsRowHidden'da saklanan bir callback türü, TXLSIsRowHidden kazandı, artı geçici bir FIgnoreHiddenRows bayrağı. Bayrak, CalcSubtotalFunc'in girişinde fonksiyon kodu 101 ile 111 arasına düştüğünde ve CalcAggregateFunc'in girişinde gizli-satır hariç tutmayı seçen AGGREGATE seçenek kodları için silahlanır. Her hücre-gezinme döngüsü daha sonra bunu denetler ve ayarlandığında bir satırı atlar, her birine tek bir satır ekleyerek
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Silahlanma kodundaki iki ayrıntı, tüm şemanın doğruluğunu taşır. Bayrak basitçe ayarlanıp temizlenmek yerine kaydedilir ve geri yüklenir, çünkü bir SUBTOTAL argümanı, dış toplama hâlâ yığındayken kendi değerlendirmesini çalıştıran bir ifade içerebilir ve bu iç içe geçmiş iş, dış kapıyı miras almamalı ya da yok etmemelidir. Ve geri yükleme bir finally bloğunda yaşar, çünkü CalcSubtotalFunc'in hata kodları için birkaç erken çıkışı vardır; bir hata dönüşünden sonra silahlı kalan bir bayrak, yeniden hesaplama sırasındaki bir sonraki ilgisiz formülü sessizce bozardı
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Assigned testi, değişikliği uyumlu tutan şeydir. HotXLS, hesaplayıcı yapıcısını varsayılan olarak nil olan üçüncü bir parametreyle genişletti, bu yüzden eski iki-argümanlı çağrıyla bir TXLSCalculator oluşturan herhangi bir kod hâlâ derlenir ve hâlâ eski gizli-dahil davranışını alır. Mevcut API'nin şekli hakkında hiçbir şey değişmedi
Gizli-satır biti gerçekte nereden gelir?
Çalışma sayfasından, iki farklı kaynak üzerinden, çünkü HotXLS iki çalışma kitabı motoru taşır. Eski BIFF tarafı, TXLSWorkbook.GetRowHidden üzerinden ulaşılan TXLSRowInfoList.GetHidden'dan yanıtlar. OOXML tarafı, TXLSXWorkbook.GetCalcRowHidden üzerinden ulaşılan TXLSXWorksheet.GetRowHidden'dan yanıtlar. Her ikisi de, yansıttıkları hücre-değeri callback'inin yanında, oluşturma zamanında hesaplayıcıya kablolanır. Satır kuralları, bu tür bir köprünün normalde yanlış gittiği yerdir, bu yüzden açıkça belirtilmeye değer. Hesaplayıcı, TXLSGetValue'nun zaten kullandığı koordinatlara uyan, 0-tabanlı bir satırı callback'e verir. XLSX çalışma sayfası, tam olarak Excel'in satırları numaralandırdığı gibi, gizli-satır haritasını 1-tabanlı satır numarasıyla anahtarlar; bu, açık RowHidden[ARow] özelliğinin de gösterdiği şeydir. XLSX köprüsü bu yüzden aramadan önce bir ekler, BIFF köprüsü eklemez, çünkü TXLSRowInfoList zaten 0-tabanlıdır. Her iki köprü de geçerli aralığın dışındaki bir sayfa indeksini ya da satırı görünür olarak değerlendirir, bu yüzden sınır dışı bir sorgu, veriyi düşürmek yerine eski gizli-dahil yanıtına geri döner
Filtrelenmiş çalışma kitapları için ne değişir
Bu, destek biletlerini üreten durumdur. HotXLS'te ApplyAutoFilter aracılığıyla bir AutoFilter uygulamak, sütun kriterlerini değerlendirir ve eşleşmeyen her veri satırını gizler; bu, tam olarak bir kullanıcı bir filtre açılır listesine tıkladığında Excel'in yaptığı şeydir. v2.197.0'dan önce bu gizli satırlar kullanıcıya görünmezdi ve hesaplama motoruna tamamen görünürdü, bu yüzden sunucu tarafı bir SUBTOTAL(109, ...), filtrelenmemiş toplamı bildiriyordu. Şimdi aynı çağrı filtrelenmiş olanı bildiriyor
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Manuel gizleme aynı şekilde çalışır, çünkü RowHidden[ARow] := True, filtrenin yazdığı aynı durumdur. Bu eşdeğerlik Excel'de kasıtlıdır ve artık HotXLS'te de geçerlidir. Bir sonuç, oluşturduğunuz çalışma kitaplarıyla birlikte gönderilen hangi belgede olursa olsun bir not hak eder: kod 109 ile hesaplanan bir toplam, görünüme bağlı bir sayıdır, bu yüzden filtreyi temizleyen bir alıcı onu değiştirir. Bir rapor, okuyucunun görünüme ne yaptığından bağımsız olarak sabit bir rakam belirtmek zorunda olduğunda, kod 9 doğru seçimdir ve her zaman öyleydi. Filtreler, doğrulama ve tablolar birlikte veri doğrulama, AutoFilter ve tablolar üzerine yazıda ele alınmıştır. Satırları gizlemek hiçbir formüle dokunmadığından, kendi başına bağımlılık grafiğini de kirletmez; büyük çalışma kitaplarını duyarlı tutmak için kirli alt-grafik üzerinden artımlı yeniden hesaplamaya güveniyorsanız bu bilinmeye değer
AGGREGATE seçenek kodları ve hâlâ açık olan bir sınır
AGGREGATE, ikinci bir politika argümanına sahip SUBTOTAL'dır ve HotXLS bunu CalcAggregateFunc'te ele alır. Seçenek argümanı bağımsız anahtarları kodlar: aralık içindeki iç içe SUBTOTAL ve AGGREGATE çağrılarının atlanıp atlanmadığı, gizli satırlardaki değerlerin atlanıp atlanmadığı ve hata değerlerinin yayılmak yerine bastırılıp bastırılmadığı. HotXLS, seçenek kodları 2, 3, 6 ve 7 için paylaşılan gizli-satır kapısını silahlandırır ve seçenek kodları 4 ile 7 için hata değerlerini bastırır. Fonksiyon-numarası argümanı daha sonra, varyans, standart sapma ve çarpımın kendi indirgeyicileri üzerinden yönlendirilmesi dahil, tam olarak SUBTOTAL'ın yaptığı gibi toplamayı seçer. Belgelenmiş bir boşluk kalıyor ve burada belirtilmesi üretimde keşfedilmesinden daha iyi: düşük seçenek kodlarıyla ilişkili iç-içe-SUBTOTAL-yoksay anlambilimi HotXLS'te uygulanmamıştır. Referans verilen bir aralık içindeki iç içe bir SUBTOTAL'ı tespit etmek, bir iç toplamanın kendisini dıştakine duyurabilmesi için değerlendirici özyineleme durumunu işaretlemeyi gerektirir; bu, gizli-satır kapısından daha büyük bir değişikliktir. Pratikte maruziyet küçüktür, çünkü gerçek çalışma kitapları neredeyse her zaman SUBTOTAL formüllerini diğer SUBTOTAL formüllerinin toplama yaptığı aralıkların dışına yerleştirir. Üretecinizin çakışan toplama aralıkları oluşturması durumunda, onları tekilleştirmek için düşük seçenek kodlarına güvenmeyin
Onunla birlikte gönderilen arity koruması
Sürüm 2.197.0 ayrıca aynı gönderici içindeki bir doğrulama boşluğunu kapattı ve tasarım nedeni, taslak alanı motive eden nedenle aynıdır: kontrolü bir kez yazılabileceği yere koyun. Kabaca 280 yerleşik fonksiyon gövdesinin her biri kendi argüman sayısını Item.ChildCount'a karşı doğruluyordu; bu, çok fazla argüman durumu için tutarlı bir sınır bırakmıyordu. =SIN(1,2) gibi bir çağrı, ilk argümanını inceleyen, fazlalığı yoksayan ve Excel'in #VALUE! döndürdüğü yerde makul bir sayı döndüren bir fonksiyon gövdesine ulaşıyordu. HotXLS zaten her yerleşiğin beyan edilmiş arity'sini fonksiyon kayıt defterinde saklıyordu, THashFunc.ArgsCnt olarak açığa çıkıyordu ve -1, SUM, IF ya da CONCAT gibi değişken-argümanlı bir fonksiyonu işaretliyordu. Sürüm 2.197.0 bunu yeni bir TXLSFormula.FuncArgsCntByPtg özelliği üzerinden iletti ve ana gönderici olan GetValueItemFunc'in başına bir kapı ekledi
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Koruma çok fazla argümanı reddeder ve kasıtlı olarak çok az argüman hakkında hiçbir şey söylemez. Sondaki isteğe bağlı bir argümanı atlamak, VLOOKUP, SUBSTITUTE ve daha uzun bir liste için Excel'de yasaldır, bu yüzden simetrik bir kontrol, yanlış olanları yakalamak için doğru formülleri bozardı. Bilinmeyen tanımlayıcılar değişken-argümanlı olarak rapor edilir ve kapıyı tamamen atlar; bu, kullanıcı tanımlı fonksiyonları onun yolundan uzak tutan şeydir; kendi fonksiyonlarınızı kaydediyorsanız, formül motoru ve özel fonksiyonlar kılavuzunda anlatılan davranış etkilenmez. Çok-az durumunu merkezileştirmek ayrı bir iştir, çünkü o 280 gövdenin her birinin kendi hata kodu anlambilimi vardır ve varsayılmak yerine tek tek incelenmeleri gerekir
Burada anlatılan hesaplama motoru, her iki çalışma kitabı arayüzü ve onu besleyen AutoFilter ve satır-görünürlük API'leri, çalıştıran makinede Excel kurulumu gerektirmeyen ve Delphi ile C++Builder için tam kaynak kodla gönderilen HotXLS Delphi elektronik tablo bileşeni'nin bir parçasıdır