Formüllerde Excel Sayfa Adı Tırnağı: 2024!A1 Neden Çalışmaz

Bir bütçe çalışma kitabında FY2024 adında bir sayfa vardır, bir özet hücresi =FY2024!B7 der ve Excel formülü okuyamaz. FY2024 aynı zamanda bir hücre adresidir; bu yüzden adın tırnaklara ihtiyacı vardır — tıpkı 'FY2024'!B7'deki gibi. HotXLS bunları, Excel kaydederken uyguladığı kurala göre yazar ve XLSX çalışma kitabı, bir sayfa yeniden adlandırıldığında ya da silindiğinde her formülü yeniden yazar

Hangi sayfa adları bir formülde tırnaklanmalıdır?

Formül parser'ının adı bir sayfa adından başka bir şey olarak okuyabileceği her durumda bir sayfa adının tırnağa ihtiyacı vardır: bir sayı, bir boolean ya da bir hücre referansı gibi. ECMA-376 Part 1 §18.17'deki formül dilbilgisi, tırnaklı ve tırnaksız sayfa adlarını kapsar; ama pratikte referans, Excel'in <f> elemanlarına — sheet1.xml içinde — yazdığı metindir, çünkü öteki bütün okuyucular o metne göre ayarlanmıştır. Excel'in COM Range.Formula yansıması ile kaydettiği XML bazı adlarda anlaşmaz: R1. ve A1.x, .Formula'dan çıplak gelir ama dosyada tırnak taşır. COM yansımasından türetilmiş bir kural, Excel'in kendi dosyaları tarafından çürütülür. HotXLS saklanan biçimi izler; şu durumlar tırnak ister:

• A-Z, a-z, 0-9 ve alt çizgi dışındaki her karakter: boşluklar, tireler, noktalar, parantezler
• Baştaki rakam: 2024, 2nd_Half ve 3D üçü de tırnaklanır
• Her harf büyük/küçüğünde TRUE ya da FALSE. TRUE1 ile TRUEX çıplak kalır; ikisi de boolean olarak okunmaz
• Izgara içinde bir A1 hücresi gibi okunan ad: en fazla XFD'ye uzanan bir sütun kuracak bir ile üç arası harf, ardından 1 ile 1048576 arası bir satır; baştaki sıfırlara izin var
• R1C1 referansı gibi başlayan ad: kendi başına bir R, C ya da RC, ya da ardından bir rakam gelen
• Tırnaklı bir adın içinde kesme işareti ikilenir — Bob's Data, 'Bob''s Data'!A1 olarak yazılır

Gerçek çalışma kitaplarının gerçekten karşılaştığı kural A1'dir, çünkü çok sayıda iş sayfası adı tesadüfen geçerli hücre adresleri olur. FY2024, FY sütunu 2024 satırıdır. Jan2024, JAN sütunu 2024 satırıdır ve JAN, modern bir sayfanın 16,384 sütununun iyice içinde kalır. Q1, CR1 ve Ra1 üçü de adrestir ve üçü de tırnaklanmak zorundadır. Sınır durumlar ters yöne gider: A0'ın sıfır satırı yoktur, XFE1 kenarın bir sütun ötesindedir, XFD1048577 bir satır ötesindedir ve ABCD1 dört harflidir; Excel bu dördünü de çıplak bırakır. Baştaki sıfırlar bir adı kurtarmaz ve A01 yine de tırnak alır, çünkü parser onu A1 olarak okur. Yalnızca adın karakter sınıfını denetleyen bir yazıcı, bir mali yıl raporu için FY2024!B7 üretir ve Excel o formülü geri okuyamaz

R1C1 benzerleri ve fazla tırnaklamanın gerekçesi

R1C1 biçimli adlar için Excel'in çıktısından geriye dönüp toparlanacak temiz bir kural yoktur. Excel R, C, RC, R1, C1, R1x, R1C1x adlarını, ayrıca var olmayan sütunları adlandıran R1C16385 ile C99999'u tırnaklar. Ama R0, C0, RC0, R0C1, R1048577, Rx, Cx ve R_1'i çıplak bırakır. Izgara sınırlarının ve token biçimlerinin hiçbir bileşimi iki listeyi birden geri üretmez; bu yüzden HotXLS bir üst küme alır: R, C ya da RC olan ya da bunlardan biriyle başlayıp arkasına rakam alan ad tırnak alır. Üst küme, Excel'in çıplak bıraktığı birkaç adı — aralarında R0 ile R1048577 var — tırnaklar. Bunu kabul etmek için basit bir gerekçe vardır: herhangi bir sayfa adının tırnaklı yazımı geçerli formül sözdizimidir, formül parse eden her okuyucu onu kabul eder; oysa Excel'i adı adına eşleştirmeye çalışan bir liste, kimsenin ifade edemeyeceği bir kurala dayanmak zorunda kalır. Chart1, Region1 ve Cost2024 gibi sıradan adlar iki testin de dışında kalır ve çıplak kalır. R1C1 metniyle de çalışıyorsanız, HotXLS'te R1C1 formül gösterimi üzerine notlar, o referansların başta nasıl parse edildiğini anlatır

ASCII dışı harfler taşıyan adlar da fazla tırnaklanır. Excel bu konuda daha hoşgörülüdür. Excel, Données!A1 ile 数据!A1'i tırnaksız yazar; çünkü aksanlı ve CJK harfleri, parser'ına göre ad karakterleridir. HotXLS bir dosyayı açarken o çıplak niteleyicileri okur — Excel ürettiği için — ama bu tür adları tırnaklı yazar; tıpkı 'Données'!A1'deki gibi. Tırnaklı biçim her yerde geçerlidir ve yazıcının testi, her tüketicinin üzerinde anlaşması gereken bir Unicode harf tablosu yerine kısa, öngörülebilir bir liste olarak kalır

Delphi'de sayfa niteleyicileri kurmak

lxStandard.XlsSheetNameNeedsQuotes, kuralın açık biçimidir; XLSX yazıcısı da klasik motor da bir sayfa referansı üretirken aynı fonksiyonu kullanır. Kendi kodunuz sayfa adlarından formül metni kuruyorsa — bölge başına ya da yıl başına bir sayfaya bakan bir özet sayfası gibi — her adı Name + '!' birleştirmesi yerine bu fonksiyondan geçirin. Fonksiyon yalnızca evet ya da hayır der. İç kesme işaretlerini ikilemek çağıranın işidir — aşağıdaki yardımcının yaptığı gibi — ve XLSX hücre formülleri baştaki = olmadan atanır

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Elle kurulan formül metni için niteleyici: ad tırnak istediğinde
// kesme işaretleri konur, adın içindeki kesme işaretleri ikilenir
function SheetQualifier(const Name: WideString): WideString;
begin
  if XlsSheetNameNeedsQuotes(Name) then
    Result := '''' + StringReplace(Name, '''', '''''', [rfReplaceAll]) + '''!'
  else
    Result := Name + '!';
end;

const
  Candidates: array[0..10] of WideString = (
    'Summary', '2024', 'FY2024', 'Q1', 'TRUE', 'TRUE1',
    'R1C1', 'Region1', 'XFE1', 'Données', 'Bob''s Data');
var
  Book: TXLSXWorkbook;
  Map, Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Map := Book.Sheets.Add('Map');
    for I := Low(Candidates) to High(Candidates) do
    begin
      Sheet := Book.Sheets.Add(Candidates[I]);
      Sheet.Cells[1, 1].Value := (I + 1) * 10;
      // Sayfanın gerçekten aldığı adı kullanın, istenen adı değil
      Map.Cells[I + 1, 1].Value := Sheet.Name;
      Map.Cells[I + 1, 2].Formula := SheetQualifier(Sheet.Name) + 'A1';
      Writeln(Format('%-12s %s', [Sheet.Name, Map.Cells[I + 1, 2].Formula]));
    end;
    Book.SaveAs('sheet-map.xlsx');
  finally
    Book.Free;
  end;
end.

// Beklenen:
// Summary      Summary!A1
// 2024         '2024'!A1
// FY2024       'FY2024'!A1
// Q1           'Q1'!A1
// TRUE         'TRUE'!A1
// TRUE1        TRUE1!A1
// R1C1         'R1C1'!A1
// Region1      Region1!A1
// XFE1         XFE1!A1
// Données      'Données'!A1
// Bob's Data   'Bob''s Data'!A1

Bir XLSX dosyasındaki çalışma sayfası adı : \ / ? * [ ] karakterlerini içeremez, kesme işaretiyle başlayıp bitemez, 31 karakterle sınırlıdır, büyük/küçük harfe bakmaksızın benzersiz olmak zorundadır ve Excel'in sakladığı History olamaz. TXLSXSheets.Add ile TXLSXWorksheet.Name atayıcısı ikisi de bunu uygular: yasak karakterler alt çizgiye dönüşür, çakışma (2) eki alır, saklı ya da boş bir ad Sheet'e düşer. Attığınız değer ile geri okuduğunuz değer bu yüzden farklı olabilir ve istenen adla kurulan bir niteleyici, var olmayan bir sayfaya işaret eder. Her atamadan sonra Name'i geri okuyun ve onu tırnaklayın

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' bir sayfa adında kullanılamaz
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Bir sayfa silindikten sonra Excel ne saklar?

Bir sayfa silindiğinde, Excel ona yapılan bütün referansı #REF! ile değiştirir; niteleyiciyle hücre ya da aralık birden. SUM(Data!A1:B2)+1, SUM(#REF!)+1 olarak saklanır; COUNT(Data!C:C), COUNT(#REF!) olarak; Data!$A$1:$B$2'ye bakan bir tanımlı ad düz #REF! olarak ve bir grafik serisi aralığı <c:f>#REF!</c:f> olarak. COM görünümü de burada yanıltır: Range.Formula =#REF!A1+1 gösterir, sanki hücre bölümü kurtulmuş gibi; oysa Excel'in kaydettiği XML'de hücre bölümü hiç yoktur. COM ekranını kopyalamak, Excel'in kendisinin hiç yazmadığı formül metni verir — öteki araçların okuyacağı bir dosya için kötü bir bahistir. TXLSXSheets.DeleteByName saklanan biçimi yazar. Aynı #REF!'e çökme mantığı, satırları ya da sütunları ortadan kalkan referansları da yönetir; sayfa içi durumu, satır ekleme ve silmenin formül referanslarını nasıl ayarladığı yazısında işlenir

Üç boyutlu referanslar başka davranır. Excel, 3D aralığı yok etmek yerine daraltır. Sayfalar Data, Mid, Keep sırasındayken Data'yı silmek, SUM(Data:Keep!A1)'i SUM(Mid:Keep!A1)'e çevirir: silmeden önceki sekme sırasıyla ölçülür, kaybolan uç bir sayfa içeride kalır. Aralıkta yalnızca iki sayfa varken sonuç, tek sayfalı SUM(Keep!A1) biçimidir. Mid'yi silmek hiçbir formül metnini değiştirmez; çünkü aralık hâlâ iki canlı sayfayı adlandırır ve aradaki kapsamı bir azaltır. Yalnızca iki uç da silinen sayfayı adlandırdığında referans #REF! olur. Ölü bir referans formülün döndürdüğünü değiştirir; bu yüzden HotXLS, silme sırasında yeniden yazdığı her formül hücresinin önbellek sonucunu bayat işaretler; değerleri okumadan önce Recalculate çağırın. Bir adlandırma, önbellekteki değerlere dokunmaz; çünkü sayılar değişemez

uses
  System.SysUtils, lxHandleX;

procedure ShowFormulas(Book: TXLSXWorkbook; const Caption: string);
var
  Report: TXLSXWorksheet;
  Row: Integer;
begin
  Writeln(Caption);
  Report := Book.Sheets.SheetByName('Report');
  for Row := 1 to 4 do
    Writeln('  B', Row, ': ', Report.Cells[Row, 2].Formula);
  Writeln('  DataBlock: ', Book.DefinedNames.FindByName('DataBlock').Formula);
end;

var
  Book: TXLSXWorkbook;
  Report: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');
    Book.Sheets.Add('Mid');
    Book.Sheets.Add('Keep');
    Report := Book.Sheets.Add('Report');
    Book.DefinedNames.Add('DataBlock', 'Data!$A$1:$B$2');

    Report.Cells[1, 2].Formula := 'SUM(Data:Keep!A1)';
    Report.Cells[2, 2].Formula := 'SUM(Data!A1:B2)+1';
    Report.Cells[3, 2].Formula := 'Keep!A1*2';
    Report.Cells[4, 2].Formula := 'INDIRECT("Data!A1")';

    Book.Sheets.DeleteByName('Data');
    ShowFormulas(Book, 'After deleting Data:');

    Book.Sheets.SheetByName('Keep').Name := '2025';
    ShowFormulas(Book, 'After renaming Keep to 2025:');

    Book.Recalculate;   // Data'yı kaybeden hücreler bayat önbellek değerleri taşır
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Beklenen:
// Data silindikten sonra:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Keep, 2025 olarak yeniden adlandırıldıktan sonra:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Bir sayfayı adlandırmak, referansları çalışma kitabı boyunca yeniden yazar

Bir XLSX çalışma kitabında TXLSXWorksheet.Name atamak, yeni adı formülün yaşayabileceği her yere taşır ve DeleteByName, #REF! çökmesini uygulamak için aynı yerleri gezer. Liste, hücre formüllerinin çok ötesine uzanır:

• Her sayfadaki hücre formülleri. Bir paylaşılan formül, her takipçi hücrede açılmış halde tutulur ve bir dizi formül kök hücresinde yaşar; ikisi de özel işlem olmaksızın kapsanır
• Koşullu biçim formülleri; veri çubukları, renk ölçekleri ve simge setlerindeki formula türü eşikler dahil
• Veri doğrulama formülleri; öteki sayfadaki bir liste kaynağı gibi
• Tablo hesaplanan sütun ve toplamlar satırı formülleri
• Tanımlı adlar — tanımlı adlar ve sayfalar arası formüller üzerine kurulu modellerin omurgası; bir adlandırmada, kaynak aralığını adlandırılan sayfadan okuyan bir pivot cache de izler
• Grafik aralıkları: seri adları, kategoriler, değerler, kabarcık boyutları ve veri etiketi aralıkları, hata çubuğu artı ve eksi aralıkları ve grafik ile eksen başlıklarının arkasındaki hücre referansları

İki uçtan herhangi biri tırnak istediğinde 3D aralığı bütünüyle tırnaklanır: Keep'i 2025'e adlandırmak 'Mid:2025'!A1 üretir, asla Mid:'2025'!A1 değil; çünkü aralık tek bir niteleyicidir. Adlandırmanın konusu olmayan her niteleyici, yazıldığı gibi yeniden oynatılır. Formüllerden birinde gereksiz olduğu halde tırnaklı duran bir 'Sheet1'!B4 varsa, sonrasında da aynen öyle kalır. O niteleyicileri geçerken normalize etmek Excel'e zarar vermezdi; ama tek bir sayfa adlandırması, o zaman kitaptaki her formüle dokunur ve üretilmiş bir raporun iki sürümü arasındaki diff, gerçek değişikliği yüzlerce kozmetik değişikliğin altına gömerdi

Yalnızca bir sayfa referansı gibi görünen metin

Üç tür metin, desen aramasına bir Name! niteleyicisi gibi görünür ve değildir. String literal'inin içinde "Data!A1" metindir; dolayısıyla INDIRECT("Data!A1"), bir adlandırma ya da silmeden sonra eski ada işaret etmeye devam eder — Excel'de olduğu gibi. Rapor kodu INDIRECT için sayfa adları kuruyorsa, onları kodunuzun adlandırma ile birlikte güncellediği bir hücrede tutun. Köşeli parantezli bir kitap indeksinin önüne düştüğü bir niteleyici — tıpkı [1]Data!A1 gibi — tesadüfen Data adında bir sayfası olan bir dış çalışma kitabına aittir ve yerel Data sayfasını adlandırmak ona dokunmamak zorundadır; içinde [ taşıyan tırnaklı bir niteleyici de aynı muamele görür. Ve #REF!'in kendisi, REF adında bir niteleyicinin biçimini taşır; HotXLS'in önündeki #'yi denetlemeden REF!'i bir sayfa adı saymamasının nedeni budur. O denetim olmasaydı, REF adında bir sayfayı adlandırmak, kitaptaki her #REF!'i yeniden yazardı

Üretilmiş bir formülü kırmaya en yakın sayfa adları, boşluklu ya da noktalı adlar değil; FY2024, Q1 ve 2025 gibi sıradan olanlardır. Delphi kodunuz formül metni kurarken onları XlsSheetNameNeedsQuotes üzerinden tırnaklayın. Ondan sonra, HotXLS Delphi spreadsheet component içindeki bir adlandırma ya da silme, her formülü Excel'in kendisinin kaydedeceği biçimde bırakır