2024!A1 nie działa: cytowanie nazw arkuszy w formułach

Skoroszyt budżetowy ma arkusz o nazwie FY2024, komórka podsumowania mówi =FY2024!B7, a Excel nie umie przeczytać formuły. FY2024 jest też adresem komórki, więc nazwa potrzebuje apostrofów, jak w 'FY2024'!B7; HotXLS zapisuje je według tej samej reguły, którą Excel stosuje przy zapisie, a jego skoroszyt XLSX przepisuje każdą formułę, gdy arkusz zmienia nazwę albo zostaje usunięty

Które nazwy arkuszy muszą być w apostrofach w formule?

Nazwa arkusza potrzebuje apostrofów zawsze wtedy, gdy parser formuły mógłby przeczytać ją jako coś innego niż nazwa arkusza: liczbę, boolean albo odwołanie do komórki. Gramatyka formuł w ECMA-376 Part 1 §18.17 obejmuje nazwy arkuszy w apostrofach i bez, ale praktycznym punktem odniesienia jest tekst, który Excel zapisuje do elementów <f> w sheet1.xml, bo do tego tekstu kalibrowano każdy inny czytnik. Echo COM Range.Formula Excela i jego zapisany XML rozjeżdżają się przy niektórych nazwach: R1. i A1.x wracają gołe z .Formula, a w pliku niosą apostrofy. Regułę wyprowadzoną z echa COM przeczą własne pliki Excela. HotXLS idzie za formą zapisaną, która wymaga apostrofów dla:

• Każdego znaku poza A-Z, a-z, 0-9 i podkreślnikiem: spacje, łączniki, kropki, nawiasy
• Cyfry wiodącej: 2024, 2nd_Half i 3D wszystkie dostają apostrofy
• TRUE albo FALSE w dowolnej wielkości liter. TRUE1 i TRUEX zostają gołe, bo żadne nie czyta się jako boolean
• Nazwy czytającej się jako komórka A1 wewnątrz siatki: jedna do trzech liter tworzących kolumnę najpóźniej XFD, po których idzie wiersz od 1 do 1048576, z zerami wiodącymi dozwolonymi
• Nazwy startującej jak odwołanie R1C1: R, C albo RC same, albo po nich cyfra
• Wewnątrz nazwy w apostrofach apostrof się podwaja — Bob's Data zapisuje się jako 'Bob''s Data'!A1

Reguła A1 to ta, na którą naprawdę natykają się skoroszyty, bo tyle nazw arkuszy biznesowych przypadkiem jest poprawnymi adresami komórek. FY2024 to kolumna FY, wiersz 2024. Jan2024 to kolumna JAN, wiersz 2024, a JAN siedzi głęboko wewnątrz 16 384 kolumn, jakie ma współczesny arkusz. Q1, CR1 i Ra1 to też adresy, i wszystkie trzy muszą dostać apostrofy. Przypadki brzegowe idą w drugą stronę: A0 nie ma wiersza zero, XFE1 to kolumna za krawędzią, XFD1048577 to wiersz za nią, a ABCD1 ma cztery litery, więc Excel zostawia je wszystkie gołe. Zera wiodące nie ratują nazwy, i A01 nadal dostaje apostrofy, bo parser czyta je jako A1. Writer sprawdzający wyłącznie klasę znaków nazwy wypuści FY2024!B7 dla raportu roku fiskalnego, a Excel nie przeczyta takiej formuły

Sobowtóry R1C1 i argument za nadmiarowym cytowaniem

Dla nazw o kształcie R1C1 nie ma czystej reguły do odtworzenia z wyjścia Excela. Excel otpakowuje apostrofami R, C, RC, R1, C1, R1x, R1C1x, a także R1C16385 i C99999, które nazywają kolumny nieistniejące. A jednak zostawia gołe R0, C0, RC0, R0C1, R1048577, Rx, Cx i R_1. Żadna kombinacja limitów siatki i kształtów tokenów nie odtwarza obu list naraz, więc HotXLS bierze nadzbiór: nazwa, która jest R, C albo RC, albo zaczyna się od jednego z nich z cyfrą za nim, dostaje apostrofy. Nadzbiór otpakowuje kilka nazw, które Excel zostawia gołe, w tym R0 i R1048577. To akceptowalne z prozaicznego powodu: zapis w apostrofach każdej nazwy arkusza jest poprawną składnią formuł, więc każdy czytnik parsujący formuły go przyjmie, podczas gdy lista próbująca dorównać Excelowi nazwa po nazwie spoczywałaby na regule, której nikt nie umie sformułować. Zwyczajne nazwy jak Chart1, Region1 i Cost2024 wypadają poza oba testy i zostają gołe. Jeśli pracujesz też z tekstem R1C1, notatki o notacji formuł R1C1 w HotXLS opisują, jak te odwołania są w ogóle parsowane

Nazwy z literami poza ASCII też dostają apostrofy z zapasem. Excel jest tu łagodniejszy. Excel zapisuje Données!A1 i 数据!A1 bez apostrofów, bo akcentowane i CJK litery liczą się u jego parsera jako znaki nazwy. HotXLS czyta te gołe kwalifikatory, gdy otwiera plik, bo to Excel je produkuje, ale takie nazwy zapisuje w apostrofach, jak w 'Données'!A1. Forma w apostrofach jest poprawna wszędzie, a test writera zostaje krótką, przewidywalną listą, a nie tablicą liter Unicode, z którą każdy konsument musiałby się zgadać

Budowanie kwalifikatorów arkuszy w Delphi

lxStandard.XlsSheetNameNeedsQuotes to publiczna forma tej reguły i to funkcja, której writer XLSX i klasyczny silnik oba używają, gdy wypuszczają odwołanie do arkusza. Gdy twój własny kod składa tekst formuły z nazw arkuszy — na przykład arkusz podsumowania wskazujący po jednym arkuszu na region albo rok — przepuszczaj każdą nazwę przez nią zamiast sklejać Name + '!'. Funkcja odpowiada wyłącznie tak albo nie. Podwajanie wewnętrznych apostrofów należy do wołającego, jak robi to pomocnik poniżej, a formuły komórek XLSX przypisuje się bez wiodącego =

program SheetQualifiers;

{$APPTYPE CONSOLE}

uses
  System.SysUtils, lxStandard, lxHandleX;

// Kwalifikator dla ręcznie składanego tekstu formuły: apostrofy, gdy
// nazwa ich potrzebuje, z każdym apostrofem wewnątrz nazwy podwojonym
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;
      // Użyj nazwy, którą arkusz faktycznie dostał, nie tej zamówionej
      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.

// Oczekiwane:
// 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

Nazwa arkusza w pliku XLSX nie może zawierać : \ / ? * [ ], nie może zaczynać się ani kończyć apostrofem, jest ograniczona do 31 znaków, musi być unikalna niezależnie od wielkości liter i nie może być History, które Excel rezerwuje. TXLSXSheets.Add i setter TXLSXWorksheet.Name oba tego pilnują: zakazane znaki stają się podkreślnikami, kolizja dostaje przyrostek (2), a nazwa zarezerwowana albo pusta cofa się do Sheet. Wartość, którą przypiszesz, i wartość, którą odczytasz z powrotem, mogą się więc różnić, a kwalifikator zbudowany z zamówionej nazwy wskazuje arkusz, który nie istnieje. Odczytuj Name z powrotem po każdym przypisaniu i cytuj to

Sheet := Book.Sheets.Add('Totals');
Sheet.Name := 'Q1/Q2 Sales';                 // '/' nie jest dozwolone w nazwie arkusza
Writeln(Sheet.Name);                         // Q1_Q2 Sales
Writeln(SheetQualifier(Sheet.Name) + 'C9');  // 'Q1_Q2 Sales'!C9

Co Excel przechowuje po usunięciu arkusza?

Gdy arkusz zostaje usunięty, Excel wymienia całe odwołanie do niego na #REF! — kwalifikator razem z komórką albo zakresem. SUM(Data!A1:B2)+1 jest zapisywane jako SUM(#REF!)+1, COUNT(Data!C:C) jako COUNT(#REF!), zdefiniowana nazwa wskazująca na Data!$A$1:$B$2 jako gołe #REF!, a zakres serii wykresu jako <c:f>#REF!</c:f>. Widok COM zwodzi i tutaj: Range.Formula wyświetla =#REF!A1+1, jakby część komórkowa przeżyła, podczas gdy XML, który Excel zapisuje, nie ma części komórkowej wcale. Skopiowanie obrazu COM daje tekst formuły, którego Excel sam nigdy nie zapisze — sługi zakład dla pliku, który będą czytać inne narzędzia. TXLSXSheets.DeleteByName zapisuje formę przechowywaną. Ta sama logika zapadania w #REF! rządzi odwołaniami, których wiersze albo kolumny znikają — co omawia tekst o tym, jak wstawianie i usuwanie wierszy dostosowuje odwołania formuł, w przypadku wewnątrzarkuszowym

Odwołania trójwymiarowe zachowują się inaczej. Excel kurczy zakres 3D zamiast go niszczyć. Przy arkuszach w kolejności Data, Mid, Keep usunięcie Data zamienia SUM(Data:Keep!A1) w SUM(Mid:Keep!A1): znikający koniec przesuwa się o jeden arkusz do środka, mierzone w kolejności kart takiej, jaka była przed usunięciem. Przy tylko dwóch arkuszach w zakresie wynikiem jest forma jednoarkuszowa SUM(Keep!A1). Usunięcie Mid nie zmienia żadnego tekstu formuły, bo zakres nadal nazywa dwa żywe arkusze i po prostu obejmuje o jeden mniej pomiędzy. Dopiero gdy oba końce nazywają usunięty arkusz, odwołanie staje się #REF!. Martwe odwołanie zmienia to, co zwraca formuła, więc HotXLS oznacza jako nieświeże zapamiętaną wartość każdej komórki formułowej, którą przepisał podczas usuwania; zawołaj Recalculate przed czytaniem wartości. Zmiana nazwy zostawia zapamiętane wartości w spokoju, bo liczby nie mogą się zmienić

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;   // komórki, które straciły Data, niosą nieświeże zapamiętane wartości
    Book.SaveAs('report.xlsx');
  finally
    Book.Free;
  end;
end.

// Oczekiwane:
// Po usunięciu Data:
//   B1: SUM(Mid:Keep!A1)
//   B2: SUM(#REF!)+1
//   B3: Keep!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!
// Po zmianie nazwy Keep na 2025:
//   B1: SUM('Mid:2025'!A1)
//   B2: SUM(#REF!)+1
//   B3: '2025'!A1*2
//   B4: INDIRECT("Data!A1")
//   DataBlock: #REF!

Zmiana nazwy arkusza przepisuje odwołania w całym skoroszycie

Przypisanie TXLSXWorksheet.Name w skoroszycie XLSX wnosi nową nazwę do każdego miejsca, gdzie formuła może mieszkać, a DeleteByName obchodzi te same miejsca, by zastosować zapad w #REF!. Lista sięga daleko poza formuły komórek:

• Formuły komórek na każdym arkuszu. Formuła współdzielona jest trzymana rozwinięta na każdej komórce naśladowanej, a formuła tablicowa mieszka na swojej komórce korzeniowej, więc oba są objęte bez specjalnej obsługi
• Formuły formatowania warunkowego, łącznie z progami typu formuła w paskach danych, skalach kolorów i zestawach ikon
• Formuły walidacji danych, jak źródło listy na innym arkuszu
• Formuły kolumn obliczanych i wierszy sum w tabelach
• Zdefiniowane nazwy, kręgosłup modeli zbudowanych na zdefiniowanych nazwach i formułach międzyarkuszowych; przy zmianie nazwy podąża też cache tabeli przestawnej czytający swój zakres źródłowy z przemianowanego arkusza
• Zakresy wykresów: nazwy serii, kategorie, wartości, rozmiary bąbli i zakresy etykiet danych, zakresy plus i minus słupków błędów oraz odwołania do komórek stojące za tytułami wykresu i osi

Zakres 3D jest cytowany w całości, gdy którykolwiek koniec potrzebuje apostrofów: zmiana nazwy Keep na 2025 produkuje 'Mid:2025'!A1, nigdy Mid:'2025'!A1, bo zakres to jeden kwalifikator. Każdy kwalifikator, o którym zmiana nazwy nie jest, jest odtwarzany taki, jak zapisano. Jeśli formuła gdzie indziej mówi 'Sheet1'!B4, z apostrofami technicznie zbędnymi, nadal to mówi i potem. Normalizowanie tych kwalifikatorów przy okazji byłoby Excelowi nieszkodliwe, ale zmiana nazwy jednego arkusza dotknęłaby wtedy każdej formuły w skoroszycie, a diff między dwiema wersjami generowanego raportu zakopałby prawdziwą zmianę pod setkami kosmetycznych

Tekst, który tylko wygląda jak odwołanie do arkusza

Trzy rodzaje tekstu wyglądają jak kwalifikator Name! dla wyszukiwania wzorcem i nie są nim. Wewnątrz literału napisowego "Data!A1" jest tekstem, więc INDIRECT("Data!A1") dalej wskazuje starą nazwę po zmianie nazwy albo usunięciu — tak samo jak w Excelu; jeśli raport buduje nazwy arkuszy dla INDIRECT, trzymaj je w komórce, którą twój kod aktualizuje razem ze zmianą nazwy. Kwalifikator poprzedzony nawiasowym indeksem skoroszytu, jak w [1]Data!A1, należy do zewnętrznego skoroszytu, który przypadkiem ma arkusz o nazwie Data, i zmiana nazwy lokalnego arkusza Data nie wolno go dotknąć; kwalifikator w apostrofach zawierający [ jest traktowany tak samo. A samo #REF! ma kształt kwalifikatora o nazwie REF, dlatego HotXLS sprawdza poprzedzający #, zanim uzna REF! za nazwę arkusza. Bez tej kontroli zmiana nazwy arkusza zwanego REF przepisałaby każde #REF! w skoroszycie

Nazwy arkuszy najbardziej skłonne złamać generowaną formułę to zwyczajne, jak FY2024, Q1 i 2025, a nie nazwy ze spacjami albo interpunkcją. Cytuj je przez XlsSheetNameNeedsQuotes, gdziekolwiek twój kod w Delphi składa tekst formuły. Od tego momentu zmiana nazwy albo usunięcie w HotXLS, komponencie arkuszy dla Delphi zostawia każdą formułę w formie, w jakiej sam by ją zapisał Excel