HotXLS ile Delphi'de SUBTOTAL ve AGGREGATE Gizli Satırlar

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. Bir elektronik tablo hesaplama motoru kurarken düşülmesi en kolay tuzaklardan biridir ve Delphi ve C++Builder için yerel Excel elektronik tablo bileşeni olan HotXLS, hesaplama motorunun çalışma sayfasına belirli bir satırın gizli olup olmadığını sorabilmesine izin vererek ondan kaçınır

Belirti nadiren formül kodları hakkında bir soru 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 neden gizli satırları 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. Akla gelen tasarım, hücre değerlerine tek bir değer callback'i üzerinden ulaşır; lxCalc.pas içindeki TXLSGetValue callback'i gibi, bir (sayfa, satır, sütun) üçlüsü için bir değerle yanıt veren ve başka hiçbir şey yapmayan. Görünürlük, satır kaydında saklanan bir sunum özelliğidir ve çağrı zincirinde başka bir şey de akmıyorsa, motor SUBTOTAL fonksiyon-numarası tablosunun her iki yarısının da çözüldüğü tek bir toplama yoluyla biter. Bu bir yuvarlama hatası sınıfı hata değildir: gizli-satır politikası, tablonun ikinci yarısının var olmasının tüm nedenidir. 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 ikisinden yalnızca birine eklenen gizli-satır kontrolü 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 altı döngü vardır; aralık değerlendirmesine, düz aralık toplamasına ve üç ayrı indirgeyiciye dağılmıştır

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 bir emsali vardır: 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. Gizli-satır kapısı ikincisidir: (SheetIndex, satır) fonksiyonu olarak Boolean döndüren, FIsRowHidden'da saklanan bir callback türü, TXLSIsRowHidden, 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 silahlandırılı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ın maliyetiyle

// Altı hücre-dolaşma döngüsünün hepsinde tekrarlanan şekil
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
      // ... Cells[rr, cc]'yi biriktiriciye katla ...
    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
  // Item.Child[2] .. Item.Child[ChildCount] üzerinde topla
  // aşağıdaki her Exit yolu finally tarafından kapsanır
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
end;

Assigned testi, hesaplayıcı API'sini uyumlu tutan şeydir. TXLSCalculator yapıcısı, varsayılanı nil olan üçüncü bir parametre alır; böylece iki-argümanlı çağrıyla hesaplayıcı kuran kod değişmeden derlenir ve gizli-dahil davranışını alır

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 gizli-dahil yanıtına geri döner

Filtrelenmiş çalışma kitapları için ne değişir

Gizli-satır politikasının en çok öneme sahip olduğu durum budur. 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. O satırlar kullanıcıya görünmezdir ve hesaplama motoru aynı satır durumuna danıştığı için, sunucu tarafı bir SUBTOTAL(109, ...) filtrelenmemiş olan yerine filtrelenmiş toplamı bildirir

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;   // eşleşmeyen satırları gizler

    Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
    Book.Recalculate;
    // Hücre değeri, Excel'in aynı filtre için gösterdiğiyle uyuşur,
    // ve VisibleRows ona kaç satırın girdiğini söyler

    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 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ı

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. 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; üreteciniz çakışan toplama aralıkları kuruyorsa, iç toplamları dış aralığın dışında tutun ve tekilleştirmeyi seçenek kodlarına bırakmayın

Aynı göndericide bir arity koruması

Aynı gönderici bir argüman-sayısı kontrolü taşır ve tasarım nedeni, taslak alanın arkasındaki nedenle aynıdır: kontrolü bir kez yazılabileceği yere koyun. Kabaca 280 yerleşik fonksiyon gövdesiyle, her birini kendi argüman sayısını Item.ChildCount'a karşı doğrulamaya bırakmak, çok fazla argüman durumu için tutarlı bir sınır bırakmaz ve tuzak kolay kaçar: =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şır. HotXLS, her yerleşiğin beyan edilmiş arity'sini fonksiyon kayıt defterinde saklar; THashFunc.ArgsCnt olarak açığa çıkar ve -1, SUM, IF ya da CONCAT gibi değişken-argümanlı bir fonksiyonu işaretler; bunu TXLSFormula.FuncArgsCntByPtg özelliği üzerinden iletir ve ana gönderici olan GetValueItemFunc'in başındaki tek bir kapıda denetler

lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
  lProvidedArgs := Item.ChildCount - 1;   // Child[0] işlev düğümüdür
  if lProvidedArgs > lDeclaredArgs then
  begin
    Result := lxErrorValue;               // =SIN(1,2) #VALUE! üretir
    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 bozar. 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 durumu her fonksiyon gövdesinde kalır, çünkü o gövdelerin her birinin kendi hata kodu anlambilimi vardır

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