HotXLS, bir XLSX çalışma sayfasına satır veya sütun eklediğinizde ya da sildiğinizde formül referanslarını otomatik olarak ayarlar. Motorun InsertRows, DeleteRows, InsertCols ve DeleteCols yöntemleri, yapısal düzenlemeden sonra A1 referanslarının (göreli, mutlak ve aralık) aynı veriyi göstermeye devam etmesi için hayatta kalan her formülü yeniden yazar ve silinen bir bloğa yapılan referanslar Excel'in davranışıyla eşleşecek şekilde #REF! haline gelir
Bunun önlediği hata, rapor oluşturmadaki en sessiz hatalardan biridir. Bir oluşturucu, C2:C9 hücrelerine günlük rakamları ve altına =SUM(C2:C9) yazar, ardından sonraki bir adım en üste bir başlık satırı ekler. Eğer motor yalnızca hücre değerlerini taşır ve formül metnini kendi haline bırakırsa, veriler artık C3:C10'da yaşarken o SUM hala C2:C9'u okur — böylece toplam sessizce son günü düşürür ve bir başlığı çift sayar. Hiçbir şey istisna oluşturmaz, dosya sorunsuz açılır ve sayı sadece yanlıştır. Sürüm 2.160'tan önce HotXLS XLSX motoru yapısal düzenlemeler sırasında formüllere dokunmazdı; 2.160'tan bu yana yeniden yazım otomatiktir ve ayarlanacak hiçbir bayrak yoktur
Excel'de satır eklediğinizde formüllere ne olur?
Excel'in kuralı, referansların adresleri değil verileri takip etmesidir. Bir satır eklendiğinde, satır dizini ekleme noktasında veya altında olan her referans eklenen satır sayısı kadar aşağı kayar; tamamen ekleme noktasının üzerindeki referanslara dokunulmaz. Satırları silmek de aynı kuralı tersine çalıştırır: Silinen bloğun altındaki referanslar yukarı kayar ve silinen bloğun kendisine yapılan referanslar, adlandırdıkları hücreler artık var olmadığı için #REF! haline gelir. Sütunlar diğer eksende de tamamen aynı şekilde davranır. Çıktısının Excel kullanıcılarıyla temasından sağ çıkmasını isteyen bir elektronik tablo kütüphanesi, bu mekanizmaları tam olarak yeniden üretmek zorundadır; çünkü kullanıcılar formülleri hakkında hiç düşünmeden bu terimlerle akıl yürütürler
Geliştiricileri şaşırtan kısım, mutlak referansların da hareket etmesidir. $B$2 içindeki $ çapa işaretleri, bir formül başka bir hücreye kopyalandığında veya doldurulduğunda ne olacağını kontrol eder — yapısal düzenlemeler sırasında hiçbir şey yapmazlar. Satır 2'nin üzerine bir satır ekleyin ve Excel, formülün bağlı olduğu değer fiziksel olarak satır 3'e taşındığı için dolar işaretlerini bozmadan $B$2'yi $B$3 olarak yeniden yazar. Yalnızca göreli referansları kaydıran bir motor, insanların en bilinçli şekilde sabitlediği formülleri tam olarak bozardı. HotXLS her iki biçimi de kaydırır ve yeniden yazılan metindeki $ işaretlerini korur
HotXLS formül referanslarını otomatik olarak nasıl kaydırır?
TXLSXWorksheet üzerindeki dört yapısal düzenleme yönteminin tümü tek bir geometri motoruna temsilcilik (delegate) eder: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) bunu pozitif bir satır farkı (delta) ile, DeleteRows(StartRow, Count) ise negatif bir farkla çağırır ve sütun yöntemleri de sütun ekseninde aynısını yapar. Rutin önce hücrelerin kendilerini taşır — silinen bir bloğun içine düşen hücreleri atar — ve ardından hayatta kalan her formül hücresini yürür ve metnini A1 tarzı referansları bulan ve satır ile sütun bileşenlerini kaydırmaya göre yeniden yazan bir tarayıcı olan XlsxAdjustFormulaRowColRefs'e geçirir. Aynı geçiş; birleştirilmiş aralıkları, köprüleri, yorumları, resimleri, grafikleri, koşullu biçimlendirmeleri, veri doğrulamalarını ve tablo aralıklarını yeniden konumlandırır, böylece tüm sayfa tek bir birim olarak hareket eder
// Düzenlemeden önceki düzen:
// C2..C9 günlük rakamlar
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // satır 2'den önce bir boş satır
// Çağrıdan sonraki düzen:
// C3..C10 günlük rakamlar
// C11 =SUM(C3:C10) -- aralık verilerle birlikte taşındıTarayıcının birkaç ayrıntısını bilmeye değer. XlsxAdjustFormulaRowColRefs, her dört çapa biçimindeki (A1, $A1, A$1, $A$1) tek hücreli referansları ve A1:B3 gibi iki köşeli aralıkları tanır ve her bir uç noktayı bağımsız olarak ayarlar. Metni zaten # ile başlayan formüller (önceki bir düzenlemeden kaynaklanan hata işareti) yeniden taranmak yerine atlanır. Ve InsertCols kendine has bir Excel benzeri incelik ekler: Yeni eklenen sütunlar, Excel'in Sayfa Sütunları Ekle komutunun yaptığı gibi, sol komşularının genişliğini devralır
Silinen bir referans ne zaman #REF! olur?
Tek bir satır veya sütun dizini için kaydırma kuralının üç sonucu vardır. Düzenleme noktasından önceki bir dizin değişmez. Düzenleme noktasındaki veya sonrasındaki bir dizin fark (delta) kadar hareket eder. Ve silme durumunda, silinen bloğun içine düşen bir dizinin anlamlı yeni bir değeri yoktur — hücre gitmiştir — bu nedenle tarayıcı referansın tamamını #REF! olarak yeniden yazar. Bir aralık referansı için, her iki uç nokta da aynı kuraldan geçirilir ve uç noktalardan biri silinen bloğun içine düşerse, referans yarı geçerli bırakılmak yerine #REF! olarak yeniden yazılır
// A12'de =A4+A6+A10 yazılı
Sheet.DeleteRows(5, 3); // satır 5..7 arasını sil
// Formül, şimdi A9'da, =A4+#REF!+A7 olarak okunur
// A4 : silinen bloğun üstünde, değişmedi
// A6 : satır 5..7 içinde, gitti -> #REF!
// A10 : bloğun altında, yukarı kaydı -> A7Sessizce yeniden hedeflemek yerine gürültülü bir #REF! üretmek doğru ticarettir ve Excel'in yaptığı da budur. Gerçek girdisi silindikten sonra komşu bir hücreyi işaret eden bir formül, mantıklı görünen bir sayı döndürebilirdi; #REF! bağımlı formüller aracılığıyla yayılır ve ilk duman testinde (smoke test) yüzeye çıkar. Aynı dönüşüm sütun ekseninde de geçerlidir
// E1'de =B1*$C$1 yazılı
Sheet.DeleteCols(3, 1); // C sütununu kaldır
// Formül, şimdi D1'de, =B1*#REF! olarak okunur
// Mutlak çapa $C$1'i korumadı -- hücrenin kendisi gittiHangi referans biçimleri yeniden yazılmaz?
Tarayıcı, açık bir sütun-harfi-artı-satır-sayısı şekline sahip aynı sayfadaki A1 referanslarını hedefler ve bunun dışında kalanlar konusunda kesin olmakta fayda vardır. A:A gibi tüm sütun referansları ve 1:1 gibi tüm satır referansları iki bileşenden birinden yoksundur, bu nedenle tarayıcı bunları yazıldığı gibi bırakır. Yapılandırılmış tablo referansları (Table1[Amount]) da benzer şekilde dokunulmadan geçirilir. Yeniden yazım ayrıca kesinlikle A1 notasyonunda çalışır — kodunuz R1C1 tarzında formüller oluşturuyorsa, Delphi'de R1C1 formül notasyonu hakkındaki eşlik eden makalede açıklandığı gibi yapısal bir düzenlemeden önce bunları A1'e dönüştürün
Sayfalar arası ve çalışma kitabı düzeyindeki yapılar, hücre metni tarayıcısı yerine ayrı geçişler tarafından işlenir. Düzenlenen sayfayı ayarladıktan sonra ShiftSheetGeometry, aynı geometri değişikliğini düzenlenen sayfaya başvuran diğer sayfalardaki formüllere, grafik serisi aralıklarına, dahili köprü hedeflerine ve tanımlanmış adlara (defined names) yayar. Tanımlanmış adlar sayfa yaşam döngüsü düzeyinde ek koruma alır: Sürüm 2.150'den bu yana, bir çalışma sayfasını silmek tanımlanmış bir adın formülü içindeki her SheetN! niteleyicisini #REF! olarak yeniden yazar ve bir sayfayı yeniden adlandırmak niteleyiciyi yeni ada göre yeniden yazar; böylece adlar asla artık var olmayan bir sayfayı göstermez. Adların ve sayfalar arası formüllerin nasıl bir araya geldiği, tanımlanmış adlar ve sayfalar arası formüller hakkındaki makalede ele alınmıştır
Kaydırmadan sonra yeniden hesaplama
Referans ayarlaması formül metnini yeniden yazar; sonuçları yeniden hesaplamaz. Yapısal bir düzenlemeden sonra, formüllerin yanında saklanan önbelleğe alınmış değerler eski geometriyi tanımlar, bu nedenle güvenilir sıra şudur: Önce tüm ekleme ve silme işlemlerini gerçekleştirin, ardından bir kez yeniden hesaplamayı tetikleyin ve kaydedin. Kaydırmayı yeniden hesaplamadan önce çalıştırmak bağımlılık bilgilerini de dürüst tutar — yeniden yazılan her referans kendi gerçek öncelini (precedent) adlandırır ki bu da artımlı yeniden hesaplama motorunun ve bunun bağımlılık grafiğinin etkilenen hücrelerin minimum kümesini yeniden hesaplamak için tam olarak ihtiyaç duyduğu şeydir. Yeniden yazılan bir formül artık #REF! içeriyorsa, yeniden hesaplama dosyada eski bir sayı bırakmak yerine hata değerini hemen yüzeye çıkarır
Formül referansı ayarlaması, Delphi and C++Builder için HotXLS Delphi Excel Bileşeni'ndeki XLSX motorunun standart davranışı olarak sunulur; ürün sayfası, burada gösterilen ekleme ve silme yöntemleri dahil olmak üzere çalışma sayfası düzenleme API referansının tamamını taşır