Delphi için HotXLS: AGGREGATE seçenekleri ve iç içe kapılar

El yapımı bir değerlendiricide =AGGREGATE(9,7,A1:A4) çağrısının 20 döndürmesi için dört hücre yeter; oysa Excel 40 gösterir. A1 10 tutar, A2 gizli bir satırda 20 tutar, A3 =1/0 tutar, A4 ise 30 değerinde bir grup ara toplamı olan =SUBTOTAL(9,A1:A2) tutar. 7 seçeneği; gizli satırları atla, hataları atla ve iç içe ara toplamları değer say demektir, bu yüzden Excel 10 ile 30'u toplar. 20 ise, A4 kendi değerini hesaplarken dış çağrının "gizli satırları atla" ayarı hâlâ açık kaldığında ortaya çıkar; bu da A4'ü 10'a küçültür. 40'a ulaşmak iki şey gerektirir: üç seçenek bitini doğru konumlarda çözümlemek ve başvurulan bir formülün, ona başvuran formülün seçeneklerini asla görmemesini sağlamak

AGGREGATE seçenekleri 0 ile 7 tam olarak neyi seçer?

Seçenekler bağımsız değişkeni üç bitlik bir matristir ve bitler birbirinden bağımsızdır. Bit 0 (değer 1) gizli satırları atlar, bit 1 (değer 2) hata değerlerini atlar, bit 2 (değer 4) ise kendi formülü SUBTOTAL ya da AGGREGATE olan hücreleri atlamayı bırakır, çünkü düşük kodlarda onları atlamak varsayılandır. Bunun iki parçasını ters okumak kolaydır. Gizli satır biti alt bittir; dolayısıyla AGGREGATE(9,1,...) filtrelenmiş toplam, AGGREGATE(9,2,...) ise hataya toleranslı biçimdir. Ve iç içe hücre politikası diğer ikisinin tersine çalışır: yalnızca 4 ile 7 arasındaki kodlar, iç içe bir toplamayı sıradan bir sayı sayar. ECMA-376 Bölüm 1 §18.17.7, SUBTOTAL'ı 1–11 ve 101–111 kodları boyunca aynı gizli satırı dahil et ya da hariç tut ayrımıyla tanımlar; OOXML'de _xlfn. öneki altında saklanan AGGREGATE bu ayrımı seçenekler bağımsız değişkenine katlar ve Microsoft'un fonksiyon için yayımladığı tablo, bir motorun karşılaması gereken sözleşmedir

Seçenek	Gizli satırlar	Hata değerleri	İç içe SUBTOTAL / AGGREGATE
0	dahil	yayılır	yok sayılır
1	yok sayılır	yayılır	yok sayılır
2	dahil	yok sayılır	yok sayılır
3	yok sayılır	yok sayılır	yok sayılır
4	dahil	yayılır	dahil
5	yok sayılır	yayılır	dahil
6	dahil	yok sayılır	dahil
7	yok sayılır	yok sayılır	dahil

0 ile 7 dışındaki her kod #VALUE! ile reddedilir; Excel'in yaptığı da tam olarak budur

Çözümler neden tek bitli kodlarda yanlış gider

Tabloyu değil tablonun bir özetini temel alan bir çözümleme genellikle şöyle görünür: 4 ve üzeri kodlarda hataları yok say, 2, 3, 6 ve 7 kodlarında gizli satırları atla ve iç içe politika diye bir şey hiç tanımlama. Bu sürüm üretimde uzun süre hayatta kalabilir. İnsanların gerçekten kullandığı bileşim, gizli satırlar artı hatalar, her iki okumada da 3 ve 7 kodlarına düşer; dolayısıyla yer değiştirme yalnızca tek bitli bir kodda belli olur. Bu durumda AGGREGATE(9,1,A1:A4) filtrelenmemiş toplamı döndürür, AGGREGATE(9,2,...) ise gizli satırları atlarken #DIV/0! hatasını yaymaya devam eder. Gerçek çalışma kitapları 1 ve 2 kodlarını tek başına seyrek kullanır; tam da bu yüzden çözümlemeyi yayımlanmış tabloya karşı gözden geçirmek, bir müşteri dosyası beklemekten fazlasını yakalar

HotXLS seçenekleri, tablonun her sütunu için bir tane olmak üzere üç küme üyelik testi olarak çözümler; üç sonucu da AGGREGATE çağrısı başlarken atar ve çağrı biterken geçerli olan eski değeri geri yükler. Atama bilinçli olarak koşulsuzdur. "Seçenek istiyorsa bayrağı aç" diye yazılan bir çözümleme, SUBTOTAL(109, ...) içine gömülü 4 kodlu bir AGGREGATE'in gizli satır ayarını temizlemek yerine dışarıdan devralmasına izin verir. Aşağıdaki taslak, kendi değerlendiricisini yazan ya da gözden geçiren herkes için aynı kuralı gösterir

// Kendi değerlendiriciniz için örnek çözümleme: TAggPolicy ve
// DecodeAggregateOptions sizin tanımladığınız adlardır, HotXLS API değildir
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // geri kalan her şey #VALUE! olur
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 temiz
end;

Filtrelenmiş satırlar ile elle gizlenmiş satırların karşılaştırması dahil, aynı gizli satır kurallarının SUBTOTAL tarafı SUBTOTAL ve AGGREGATE gizli satırları yazısında ele alınır

Dıştaki bir AGGREGATE neden başvurduğu formüllerin içine uzanır?

Çünkü "gizli satırları atla" ayarını altı ya da yedi hücre yürüyüş döngüsüne aktarmanın doğal yolu, hesaplayıcı nesnesinde bir alan tutmaktır ve tek bir hesaplayıcı, bir yeniden hesaplama turunda her formülü değerlendirir. Bu tasarım, alan kurulu çalıştığı sürece her şeyin onu kuran toplamaya ait olması koşuluyla sağlamdır. Sağlamlığı tek bir anda biter: hücre getirme. Yürüyücü bir hücre değeri istediğinde ve o hücre henüz önbelleklenmiş bir sonucu olmayan bir formül taşıdığında, çalışma kitabı formülü yerinde, aynı hesaplayıcı üzerinde, dış ayarlar hâlâ geçerliyken derleyip değerlendirir

Açılış örneğini böyle bir motor üzerinden yürütün. 7 kodlu AGGREGATE gizli satır atlamayı açar ve A1'de başlar. A1 10 verir, A2 gizlidir ve atlanır, A3 hatadır ve atlanır. Sonra yürüyücü, henüz hesaplanmamış olan A4'e ulaşır. A4'ün SUBTOTAL(9,A1:A2) çağrısı çalışır ve gizli satır atlamayı yalnızca 101 ile 111 arasındaki kodlar için açan (son derece makul bir yazım biçimi) bir SUBTOTAL gerçeklemesi onu 9 kodu için asla kapatmaz. Ayarı açık bulur, A2'yi atlar ve 10 döndürür. Dış toplam 10 + 10 = 20 olur. A4'ün formülünde gizli satırlarla ilgili hiçbir şey yoktur

İç içe toplama ayarı iki yöne de sızar. 0 ile 3 arasındaki kodlarda açıktır ve ona uyan genel bir aralık yürüyücüsü, B2'nin içinde tesadüfen bir SUBTOTAL varsa =SUM(B1:B3) gibi bir öncülün B2'yi düşürmesine yol açar. Ters yönde ise, işi biterken önceki durumu geri yüklemek yerine iç içe atlamayı kapatan bir SUBTOTAL, dış AGGREGATE'in politikasını yürüyüşünün geri kalanı için devre dışı bırakır. Geçici bir alanla bağlamı özyinelemeli bir yürüyüşe taşıyan her değerlendirici bu biçimedir; alan, onu kuran çerçeve için doğrudur ve onu devralan her çerçeve için yanlıştır

Politikayı getirme sınırında kesmek

HotXLS, AGGREGATE'in kendi hesaplamadığı bir değeri okuduğu her noktanın çevresine bir sınır çeker. Getirmeden önce her iki ayar da saklanır ve temizlenir; getirmeden sonra bir finally bloğunda geri yüklenir. Dış toplama, az önce getirdiği hücreye kendi politikasını yine uygular, çünkü gizli satır ve iç içe hücre testleri getirmenin çevresinde yürüyücüde yapılır. Başvurulan formül hiçbir politika olmadan çalışır; bu da Excel ile aynıdır. Üç yürüyücü ailesinin tamamı bu sınırdan geçer: SUM ailesi (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), kapalı biçimli indirgemeler (PRODUCT, STDEV, VAR) ve k bağımsız değişkeni alan biçimler (MEDIAN, LARGE, SMALL, yüzdelikler ve çeyreklikler)

// Kendi değerlendiriciniz için örnek yürüyücü. FPolicy, FetchCell,
// IsRowHidden ve IsSubtotalCell sizin tanımladığınız üyelerdir
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // başvurulan formül kendi politikasına sahiptir
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// aralık yürüyüşü içinde: dış politika getirmenin çevresinde uygulanır
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... hata politikasını Code / V üzerine uygula, sonra biriktir ...
  end;
end;

Düz aralık olmayan bağımsız değişkenler temizlenmiş bayraklardan fazlasını gerektirir. A1:A4/(B1:B4-20) gibi bir bağımsız değişken hesaplanmış bir dizidir ve 6 seçeneğinin yalnızca sıfıra bölünen elemanları düşürebilmesi için eleman yapısının korunması gerekir. HotXLS, başvurulan her aralığı aynı sınırdan geçirerek iki boyutlu bir diziye çevirir, başarısız olan bir hücreyi durdurmak yerine hata elemanı olarak saklar ve aritmetik ile karşılaştırma operatörlerini eleman eleman uygular. Birkaç sayfaya yayılan bir aralık ya da köşeleri ters çevrilmiş bir aralık #VALUE! döndürür. TXLSCalculator.FormulaArrayMemoryLimit için fazla büyük bir aralık (varsayılan 64 MB) kaynak sınırı sonucu döndürür ve bu sonuç, bilinçli olarak 2, 3, 6 ve 7 seçeneklerinin atlayabileceği hata kümesinin dışındadır. Formül #N/A yok saymayı istedi diye motorun kendi bellek yetersizliği sinyalini yutmak, kendinden emin ama yanlış bir sayı üretirdi; bunun yerine formül başarısız olur

AGGREGATE hataları yok saymadığında hangi hatayı döndürür?

Karşılaştığı ilki, değiştirmeden. Her hatayı #VALUE! içine indirmek kolay kısayoldur ve #DIV/0! tutan bir hücre üzerinde AGGREGATE(9,4,A1:A3) çağrısını, #DIV/0! hatasını geçen Excel ile uyuşmaz hâle getirir. HotXLS hem gerçek hata varyantlarını hem de yedi hata dizesini (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) kendi kodlarına geri eşler ve her yürüyücü kaydettiği ilk kodu döndürür. Formülü hiç hesaplanmamış bir hücre, hatasını önbelleklenmiş bir değer yerine getirmenin dönüş kodu olarak teslim eder; önbelleklenmiş olanla tam olarak aynı biçimde yayılır

İki sayma fonksiyonu SUM'ı değil SUBTOTAL'ı izler. 2 numaralı fonksiyon olan COUNT için, seçenek kodu ne olursa olsun bir hata hücresi asla sayılmaz ve asla yayılmaz; çünkü COUNT yalnızca sayıları sayar. 3 numaralı fonksiyon olan COUNTA için bir hata hücresi boş olmayan bir değerdir ve seçenek kodu hataları atlamıyorsa 1 sayılır. Bu asimetri, Excel'in COUNT ve COUNTA ile AGGREGATE dışında da davrandığı biçimdir; genel bir "hata varsa yay" kuralı ikisini de yanlış yapar

Sekiz seçeneğin tamamını tek bir çalışma sayfasında sınamak

Açılıştaki dört hücre, her kod boyunca derli toplu bir kontrol sağlar. 0 ile 7 arasındaki her kod için A1:A4 üzerinde SUM biçimini ve MEDIAN biçimini değerlendirin ve elle çıkardığınız değerlerle karşılaştırın. 0, 1, 4 ve 5 kodları A3'teki #DIV/0! hatasını yaymak zorundadır; hiçbiri hataları atmaz. 2 kodu, iç içe A4 atlanmış hâlde 10 ve 20'den SUM 30 ve MEDIAN 15 verir. 3 kodu 10 ve 10 verir. 6 kodu, A4'teki 30 artık sayıldığı için 60 ve 20 verir. 7 kodu 40 ve 20 verir ve getirme sınırı olmayan bir motorun 20 olarak bildirdiği kod da budur. Kontrol yalnızca AGGREGATE çalışırken A4 önbelleklenmemişse anlamlıdır; bu yüzden her durumu bir kez öncüller hesaplanmış, bir kez hesaplanmamış hâlde değerlendirin

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';    // grup ara toplamı = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  gizli atlanır, hata yayılır
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       gizli, hata ve iç içe atlanır
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       yalnızca hatalar atlanır
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20, bir ayarın A4'e sızdığı anlamına gelir
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     10, 20, 30 için MEDIAN
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Kaydedilen dosyayı Excel'de açıp Ctrl+Alt+F9'e basmak, aynı hücreler hakkında ikinci bir görüş verir. Daha geniş bir tarama için 1'den 19'a kadar her fonksiyon numarasını sekiz kodun tamamına karşı çalıştırın

Hesaplayıcıyı kendiniz kurarken geri çağrıları bağlamak

Her iki ayar da iki geri çağrıya dayanır. TXLSCalculator.Create, değer getiriciden sonra bir TXLSIsRowHidden ve bir TXLSIsSubtotalCell alır; ikisi de varsayılan olarak nil'dir. Birisi eksik olduğunda tablonun ilgili sütunu hiçbir zaman uygulanmaz; böylece her seçenek kodu gizli satırları, iç içe toplamaları ya da ikisini birden kapsar. XLSX ve klasik XLS çalışma kitabı nesneleri kendi gerçeklemelerini kurucularında geçirir; kendi veri kaynağı üzerinde elle bir TXLSCalculator kuran kod ikisini de sağlamak zorundadır. Çalışma kitapları, iç içe bir toplamayı SUBTOTAL(, AGGREGATE( ya da _xlfn.AGGREGATE( ile başlayan bir formülden tanır, baştaki eşittir işareti olsun ya da olmasın; özel bir veri kaynağı da aynı kuralı kullanmalıdır

// TMySheetSource ve FormulaAt sizin kendi türlerinizdir; üç metot
// imzası TXLSGetValue, TXLSIsRowHidden ve TXLSIsSubtotalCell ile aynıdır
// (SheetIndex 1 tabanlı, Row ve Col ise çalışma kitaplarının geçirdiği gibi 0 tabanlı)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern, ana uygulamanızın hâlihazırda tuttuğu sayfa adı tablosudur
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

Önek kuralının üretilen çalışma kitapları için bir sonucu vardır. =ROUND(SUBTOTAL(9,B2:B9),2) yazan bir üretici, önek testinin tanımadığı bir hücre üretir; böylece 0 ile 3 arasındaki kodlar onu sayar ve grup, genel toplama iki kez düşer. SUBTOTAL ya da AGGREGATE çağrısını en dışta tutun ve yuvarlamayı sayı biçimine bırakın. Tam bir Recalculate öncülleri bağımlılardan önce değerlendirir; bu yüzden toplamalar normalde önbelleklenmiş değerleri okur ve aynı sıralama, bağımlılık grafiği üzerinden artımlı yeniden hesaplama altında da geçerlidir. Bir toplam hâlâ yanlış görünürken formül metni doğru görünüyorsa, değerlendirmeyi adım adım izlemek bir öncülün yürüyüş içinde talep üzerine mi çalıştığını yoksa bir geri çağrının hiç bağlanmadığını mı gösterir

Burada anlatılan seçenek çözücü, getirme sınırı ve geri çağrı kancaları, HotXLS Delphi elektronik tablo bileşeni ile birlikte gelir; bu bileşen Delphi ve C++Builder kodundan, makinede Excel kurulu olmadan XLS, XLSX ve ODS çalışma kitaplarını okur, yazar ve yeniden hesaplar