HotXLS kompiluje vzorce zapísané v referenčnej notácii R1C1 prostredníctvom metódy TXLSCalculator.GetCompiledFormulaR1C1, ktorá prijíma tvary ako R2C3 (absolútny), R[-1]C[2] (relatívny posun) a RC (aktuálna bunka), prevádza ich na notáciu A1 voči riadku a stĺpcu bunky, v ktorej sa vzorec nachádza, a odovzdáva výsledok rovnakému kompilátoru, ktorý spracováva bežné vzorce A1. V kóde pre Delphi a C++Builder, ktorý generuje rovnaký vzorec naprieč stovkami riadkov, táto jediná metóda odstraňuje celú triedu chýb spojených so spájaním reťazcov
Táto kategória chýb je známa každému, kto programovo vypĺňal stĺpec. Prechádzate riadky v cykle a pre každý riadok zostavujete textový reťazec vzorca A1 pomocou Format('D%d*E%d', [Row, Row]). Každá iterácia vkladá čísla riadkov do textu, pričom čísla riadkov sú jedinou časťou, ktorá sa mení. Stačí raz spraviť chybu v posune, zmiešať počítadlo cyklu od nuly s 1-indexovanými číslami riadkov A1 alebo posunúť dátový blok o riadok hlavičky, a každý vzorec v stĺpci bude ukazovať o riadok vedľa. Nič nevyvolá výnimku; výsledky sú jednoducho nesprávne. Vzorec, ktorý ste skutočne mali na mysli: „vynásob dve bunky naľavo odo mňa“, vôbec neobsahuje číslo riadku a notácia R1C1 vám ho umožňuje presne takto zapísať
Čo je notácia R1C1 a kedy by ste ju mali použiť?
Notácia R1C1 adresuje bunky podľa čísla riadku a stĺpca namiesto písmena stĺpca plus čísla riadku a označuje relatívne odkazy ako explicitné posuny od bunky so vzorcom. Zápis R2C3 predstavuje absolútnu bunku na riadku 2, stĺpci 3, čo v notácii A1 zapisujeme ako $C$2. R[-1]C[2] je o jeden riadok vyššie a dva stĺpce vpravo od miesta, kde sa nachádza vzorec. RC označuje samotnú bunku so vzorcom. Podstatou sú posuny v hranatých zátvorkách: relatívny odkaz v R1C1 vyzerá rovnako bez ohľadu na to, ktorá bunka ho hostí, zatiaľ čo zápis toho istého odkazu v A1 sa mení s každým riadkom
Táto notácia sa osvedčuje najmä v jednom scenári, ktorý je však veľmi bežný: generovanie šablón, kde sa musí rovnaký relatívny vzorec vložiť do každého riadku dátovej oblasti. V A1 musíte pre každý riadok nanovo zostavovať text vzorca. V R1C1 je tento text konštantou. To je tiež bližšie tomu, ako tabuľkové formáty uvažujú interne: záznamy zdieľaných vzorcov ukladajú relatívne odkazy ako riadkové a stĺpcové posuny od hostiteľskej bunky, takže reťazec A1 pre každý riadok je niečo, čo váš kód syntetizuje len preto, aby ho parser vzápätí rozložil späť na posuny. R1C1 tento zbytočný cyklus preskakuje. Pre interaktívne vzorce vytvárané ľuďmi zostáva prirodzenou voľbou A1, čo je dôvod, prečo je v knižnici HotXLS predvolená všade
Ako HotXLS kompiluje vzorec R1C1?
HotXLS sprístupňuje túto funkciu na dvoch úrovniach zavedených vo verzii v2.175.0. Metóda TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula je tá, ktorú volá väčšina kódu: vracia skompilovaný vzorec priready na vyhodnotenie, presne ako jej ekvivalent pre A1 GetCompiledFormula, ale s dvoma extra parametrami určujúcimi 0-indexovaný riadok a stĺpec bunky, ktorej vzorec patrí. Pod ňou funkcia TXLSFormula.GetCompiledR1C1 produkuje čistý syntaktický strom a samostatná funkcia R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String vykonáva samotný prevod notácie. Spracovanie je zámerne jednoduché: preložiť text R1C1 na ekvivalentný text A1 s použitím súradníc hostiteľskej bunky a potom skompilovať text A1 cez existujúci engine — ten istý engine, ktorý rieši definované názvy a odkazy medzi hárkami a obsluhuje používateľské funkcie hárku
Keďže konverzia prebieha pred kompiláciou, všetko ostatné sa správa tak, akoby ste vzorec A1 napísali sami. Volanie R1C1ToA1('R2C3', 4, 3) vráti '$C$2' bez ohľadu na hostiteľskú bunku, pretože obe súradnice sú absolútne. R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — vzorec nachádzajúci sa v D5, keďže CurRow = 4 a CurCol = 3 sú indexované od 0 — vráti 'SUM(D2:D4)': posuny boli vyhodnotené voči riadku 5, stĺpcu D a vygenerované ako obyčajné relatívne odkazy A1. Rozsahy si nevyžadujú žiadne špeciálne ošetrenie; dvojbodka prechádza bez zmeny a každý koncový bod sa konvertuje samostatne
// The formula lives in D5: CurRow = 4, CurCol = 3 (both 0-based)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2' (absolute row and column)
S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)' (offsets resolved against D5)
S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)' (the R in ROUND is left alone)Vyplnenie stĺpca jedným relatívnym vzorcom
Prínos sa prejaví v cykle. Porovnajte verziu pre A1, ktorá nanovo zostavuje text vzorca pri každej iterácii, s verziou R1C1, kde je vzorec konštantou a menia sa len hostiteľské súradnice. Obe kompilujú cez TXLSCalculator a vyhodnocujú sa pomocou GetValue; kalkulátor pri konštrukcii callback poskytovateľa buniek, aby mohol vyhodnocovač preberať hodnoty buniek z vášho zdroja údajov
// A1 style: a different formula string for every row
for Row := 1 to 500 do
begin
FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]); // 1-based A1 rows
Compiled := Calc.GetCompiledFormula(FormulaText, 0);
// ... evaluate, store, free ...
end;const
AmountFormula = 'RC[-2]*RC[-1]'; // two cells to the left, same row
var
Calc: TXLSCalculator;
Compiled: TXLSCompiledFormula;
Value: Variant;
Row: Integer;
begin
Calc := TXLSCalculator.Create(nil, Provider.GetValue);
try
for Row := 1 to 500 do
begin
Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
try
if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
StoreResult(Row, 5, Value);
finally
Compiled.Free;
end;
end;
finally
Calc.Free;
end;
end;Cyklus R1C1 neobsahuje vôbec žiadnu aritmetiku riadkov v texte vzorca. 'RC[-2]*RC[-1]' znamená „rovnaký riadok, o dva stĺpce vľavo, krát rovnaký riadok, o jeden stĺpec vľavo“ rovnako na riadku 2 ako aj na riadku 500, a ak sa dátový blok neskôr posunie nadol o hlavičkový riadok, konštanta vzorca sa nemení — menia sa len hranice cyklu. Verzia pre A1 ako aj A1 verzia má dve miesta, kde sa dá pomýliť pri úprave o + 1; verzia R1C1 nemá žiadne
Ktoré formáty R1C1 konvertor akceptuje?
Konvertor R1C1ToA1 rozpoznáva formáty zdokumentované pre verziu v2.175.0: R[n]C[m] pre relatívne posuny v oboch smeroch, RnCm pre absolútny riadok a stĺpec, R[-n]C[m] s negatívnymi posunmi, samotné RC pre samotnú bunku so vzorcom a rozsahy ako R1C1:R3C3 alebo R[-1]C:R[1]C. Riadkové a stĺpcové zložky sa analyzujú nezávisle, takže fungujú aj zmiešané formáty ako R[1]C3 — relatívny riadok, absolútny stĺpec, pričom písmená nerozlišujú veľkosť znakov (case-insensitive), takže r[-1]c[2] sa kompiluje rovnako ako jeho verzia s veľkými písmenami. Časti v hranatých zátvorkách sa stávajú neukotvenými (relatívnymi) súradnicami A1; čísla bez zátvoriek sa stávajú absolútnymi súradnicami ukotvenými pomocou $
Zaujímavou otázkou je, ako konvertor predchádza znehodnoteniu všetkého ostatného vo vzorci, keďže R a C sú bežné písmená. Pravidlo rozlišovania je založené na tokenoch: písmeno R sa považuje za začiatok odkazu iba vtedy, ak pred ním nestojí iné písmeno, a kandidát sa potom musí kompletne analyzovať — voliteľná časť riadku, povinné C, voliteľná časť stĺpca — inak sa text obnoví bez zmeny. Preto ROUND(R[-1]C[0], 2) konvertuje iba vnútorný odkaz: po R v slove ROUND nasleduje O a nie číslica, zátvorka alebo C, so analýza zlyhá a názov funkcie prejde bez zmeny. Rovnaká logika chráni ROW() a názvy funkcií začínajúce na C nie sú kandidátmi vôbec, pretože odkaz začína iba na R. Poznámky k vydaniu v2.175.0 uvádzajú, že reťazcové literály a identifikátory sa taktiež preskakujú; napriek tomu, ak citovaný literál vo vašom vzorci náhodou obsahuje text tvarovaný presne ako odkaz R1C1, pred nasadením do produkcie sa oplatí skompilovaný výstup skontrolovať
Bázy súradníc a detaily ukotvenia, ktoré sa oplatí poznať
V tomto API sa stretávajú dve konvencie a ich správne rozlíšenie predchádza jedinej skutočnej pasci. Parametre CurRow a CurCol metódy GetCompiledFormulaR1C1 sú indexované od 0, v súlade s API kalkulátora, zatiaľ čo čísla vo vnútri samotného zápisu sú indexované od 1, čo zodpovedá zobrazeniu v Exceli: R2C3 je riadok 2, stĺpec 3, čo je $C$2 a nie $D$3. Ak je vaše počítadlo cyklu indexované od 0, odovzdáte ho priamo ako CurRow; úprava o + 1 prebieha vo vnútri konvertora a nie vo vašom kóde
Ešte jeden detail je dôležitý, ak má skompilovaný vzorec prežiť bunku, pre ktorú bol skompilovaný. Ak vynecháte riadkovú alebo stĺpcovú časť — formáty RC[-1] alebo R[2]C — vynechaná súradnica sa vyhodne voči hostiteľskej bunke a v skonvertovanom texte A1 sa vygeneruje ako absolútna súradnica ukotvená pomocou $. Pri vyhodnocovaní je to neviditeľné, keďže hodnota je v oboch prípadoch rovnaká. Relatívne verzus absolútne ukotvenie však určuje, ako sa odkazy posúvajú pri neskoršom vkladaní alebo odstraňovaní riadkov či stĺpcov, ako popisuje sprievodný článok o úprave odkazov vo vzorcoch. Ak potrebujete, aby súradnica zostala relatívna aj po štrukturálnych úpravách, napíšte posun explicitne — R[0]C[-1] namiesto RC[-1] — aby konvertor vygeneroval neukotvený odkaz A1
Kompilácia R1C1 je súčasťou vzorcového enginu v komponente HotXLS Delphi Excel Component, spolu s kompilátorom A1, grafom prepočtov a vyhodnocovacím API zobrazeným vyššie. Ak váš kód zostavuje tabuľky spúšťaním vzorcov v cykle po stĺpcoch, prechod od spájania reťazcov A1 k jedinej konštante R1C1 je jedným z najjednoduchších vylepšení spoľahlivosti, aké sú k dispozícii