Referencje formuł przy wstawianiu wierszy w HotXLS

HotXLS automatycznie dostosowuje referencje w formułach, gdy wstawiasz albo usuwasz wiersze lub kolumny w arkuszu XLSX. Metody silnika InsertRows, DeleteRows, InsertCols i DeleteCols przepisują każdą ocalałą formułę tak, by jej referencje A1 — względne, bezwzględne i zakresowe — po edycji strukturalnej nadal wskazywały te same dane, a referencje w usunięty blok stają się #REF!, zgodnie z zachowaniem Excela

Błąd, przed którym to chroni, jest jednym z najcichszych w generowaniu raportów. Generator wpisuje dzienne wartości do C2:C9 z =SUM(C2:C9) pod spodem, a późniejszy krok wstawia wiersz nagłówka na górze. Jeśli silnik przesuwa tylko wartości komórek i zostawia tekst formuły w spokoju, ta suma nadal czyta C2:C9, podczas gdy dane mieszkają już w C3:C10 — więc suma po cichu gubi ostatni dzień i liczy nagłówek podwójnie. Nic nie podnosi wyjątku, plik otwiera się bez problemu, a liczba jest po prostu błędna. Przed wersją 2.160 silnik XLSX w HotXLS zostawiał formuły nietknięte podczas edycji strukturalnych; od 2.160 przepisywanie jest automatyczne i nie ma żadnej flagi do ustawienia

Co dzieje się z formułami, gdy wstawiasz wiersz w Excelu?

Reguła Excela mówi, że referencje idą za danymi, a nie za adresami. Kiedy wstawiany jest wiersz, każda referencja, której indeks wiersza siedzi na punkcie wstawienia albo poniżej, przesuwa się w dół o liczbę wstawionych wierszy; referencje w całości powyżej punktu wstawienia zostają nietknięte. Usuwanie wierszy stosuje tę samą regułę w odwrotną stronę: referencje poniżej usuniętego bloku wjeżdżają w górę, a referencje w sam usunięty blok stają się #REF!, bo komórki, które nazywały, już nie istnieją. Kolumny zachowują się identycznie wzdłuż drugiej osi. Biblioteka arkuszowa, która chce, by jej wyjście przeżyło kontakt z użytkownikami Excela, musi odtworzyć tę mechanikę dokładnie, bo użytkownicy myślą o swoich formułach w tych kategoriach, nigdy się nad tym nie zastanawiając

Częścią, która zaskakuje programistów, jest to, że referencje bezwzględne też się przesuwają. Kotwice $ w $B$2 kontrolują to, co dzieje się, gdy formuła jest kopiowana lub wypełniana do innej komórki — podczas edycji strukturalnych nie robią nic. Wstaw wiersz nad wierszem 2, a Excel przepisze $B$2 na $B$3, z nietkniętymi znakami dolara, bo wartość, od której zależy formuła, fizycznie przeniosła się do wiersza 3. Silnik przesuwający tylko referencje względne psułby dokładnie te formuły, które ludzie kotwiczą najbardziej świadomie. HotXLS przesuwa obie formy i zachowuje znaczniki $ w przepisanym tekście

Jak HotXLS automatycznie przesuwa referencje w formułach?

Wszystkie cztery metody edycji strukturalnej na TXLSXWorksheet delegują do jednego silnika geometrii: ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). InsertRows(BeforeRow, Count) woła go z dodatnią deltą wiersza, DeleteRows(StartRow, Count) z ujemną, a metody kolumnowe robią to samo na osi kolumn. Procedura najpierw przenosi same komórki — porzucając każdą, która wpada w usunięty blok — a potem przechodzi po każdej ocalałej komórce formuły i przepuszcza jej tekst przez XlsxAdjustFormulaRowColRefs, skaner znajdujący referencje w stylu A1 i przepisujący ich składowe wiersza i kolumny wobec przesunięcia. Ten sam przebieg przenosi scalone zakresy, hiperłącza, komentarze, obrazy, wykresy, formaty warunkowe, walidacje danych i zakresy tabel, więc cały arkusz porusza się jako jedna całość

// Układ przed edycją:
//   C2..C9  dzienne wartości
//   C10     =SUM(C2:C9)
Sheet.InsertRows(2, 1);      // jeden pusty wiersz przed wierszem 2
// Układ po wywołaniu:
//   C3..C10 dzienne wartości
//   C11     =SUM(C3:C10)    -- zakres przeniósł się razem z danymi

Kilka szczegółów skanera warto znać. XlsxAdjustFormulaRowColRefs rozpoznaje referencje do pojedynczych komórek we wszystkich czterech formach kotwiczenia (A1, $A1, A$1, $A$1) oraz zakresy dwunarożne, takie jak A1:B3, dostosowując każdy koniec niezależnie. Formuły, których tekst zaczyna się już od # — znacznik błędu z wcześniejszej edycji — są pomijane, a nie skanowane ponownie. A InsertCols dokłada własny drobiazg zgodny z Excelem: nowo wstawione kolumny dziedziczą szerokość swojego lewego sąsiada, dokładnie tak, jak robi to polecenie wstawiania kolumn arkusza w Excelu

Kiedy usunięta referencja staje się #REF!?

Reguła przesunięcia dla pojedynczego indeksu wiersza albo kolumny ma trzy wyniki. Indeks przed punktem edycji zostaje bez zmian. Indeks na punkcie edycji albo za nim przesuwa się o deltę. A przy usuwaniu indeks wpadający w usunięty blok nie ma sensownej nowej wartości — komórki nie ma — więc skaner przepisuje całą referencję jako #REF!. Dla referencji zakresowej oba końce przechodzą przez tę samą regułę, a jeśli którykolwiek koniec ląduje w usuniętym bloku, referencja jest przepisywana jako #REF!, zamiast zostać w połowie ważna

// A12 trzyma  =A4+A6+A10
Sheet.DeleteRows(5, 3);      // usuń wiersze 5..7
// Formuła, teraz w A9, brzmi  =A4+#REF!+A7
//   A4  : nad usuniętym blokiem, bez zmian
//   A6  : wewnątrz wierszy 5..7, znikła   -> #REF!
//   A10 : pod blokiem, wjeżdża w górę     -> A7

Wyprodukowanie głośnego #REF! zamiast cichego przecelowania to właściwy kompromis i taki właśnie robi Excel. Formuła wskazująca sąsiednią komórkę po usunięciu jej prawdziwego wejścia zwracałaby liczbę wyglądającą wiarygodnie; #REF! propaguje przez formuły zależne i wychodzi w pierwszym teście dymnym. Ta sama konwersja obowiązuje na osi kolumn

// E1 trzyma  =B1*$C$1
Sheet.DeleteCols(3, 1);      // usuń kolumnę C
// Formuła, teraz w D1, brzmi  =B1*#REF!
// Bezwzględna kotwica nie ochroniła $C$1 -- samej komórki już nie ma

Które formy referencji nie są przepisywane?

Skaner celuje w referencje A1 na tym samym arkuszu o jawnym kształcie litera kolumny plus numer wiersza i warto precyzyjnie powiedzieć, co wypada poza to. Referencje do całych kolumn, takie jak A:A, i do całych wierszy, takie jak 1:1, nie mają jednej z dwóch składowych, więc skaner zostawia je tak, jak zapisano. Referencje strukturalne tabel (Table1[Amount]) są tak samo przepuszczane nietknięte. Przepisywanie działa też ściśle na notacji A1 — jeśli twój kod buduje formuły w stylu R1C1, przekonwertuj je do A1 przed edycją strukturalną, jak opisuje towarzyszący artykuł o notacji formuł R1C1 w Delphi

Konstrukcje międzyarkuszowe i na poziomie skoroszytu obsługują osobne przebiegi, a nie skaner tekstu komórek. Po dostosowaniu edytowanego arkusza ShiftSheetGeometry propaguje tę samą zmianę geometrii do formuł na innych arkuszach odwołujących się do edytowanego arkusza, do zakresów serii wykresów, do celów hiperłączy wewnętrznych i do nazw zdefiniowanych. Nazwy zdefiniowane dostają dalszą ochronę na poziomie cyklu życia arkusza: od wersji 2.150 usunięcie arkusza przepisuje każdy kwalifikator SheetN! wewnątrz formuły nazwy zdefiniowanej na #REF!, a zmiana nazwy arkusza przepisuje kwalifikator na nową nazwę, więc nazwy nigdy nie wskazują arkusza, którego już nie ma. Jak nazwy i formuły międzyarkuszowe składają się razem, omawia artykuł o nazwach zdefiniowanych i formułach międzyarkuszowych

Przelicz po przesunięciu

Dostosowanie referencji przepisuje tekst formuły; nie przelicza wyników. Po edycji strukturalnej buforowane wartości przechowywane obok formuł opisują starą geometrię, więc niezawodna kolejność brzmi: najpierw wykonaj wszystkie wstawienia i usunięcia, potem raz wyzwól przeliczenie, potem zapisz. Uruchomienie przesunięcia przed przeliczeniem utrzymuje też uczciwą informację o zależnościach — każda przepisana referencja nazywa swój prawdziwy poprzednik, czyli dokładnie to, czego potrzebuje silnik przeliczania przyrostowego i jego graf zależności, by przeliczyć minimalny zbiór dotkniętych komórek. Jeśli przepisana formuła zawiera teraz #REF!, przeliczenie od razu wystawia wartość błędu, zamiast zostawiać w pliku nieświeżą liczbę

Dostosowywanie referencji w formułach przychodzi jako standardowe zachowanie silnika XLSX w HotXLS Delphi Excel Component dla Delphi i C++Buildera; strona produktu niesie pełną referencję API edycji arkusza, wraz z pokazanymi tu metodami wstawiania i usuwania