HotXLS компілює формули, написані в нотації посилань R1C1, за допомогою методу TXLSCalculator.GetCompiledFormulaR1C1, який приймає такі форми, як R2C3 (абсолютне), R[-1]C[2] (відносне зміщення) та RC (поточна комірка), перетворює їх на нотацію A1 відповідно до рядка та стовпця комірки, в якій знаходиться формула, і передає результат тому самому компілятору, який обробляє звичайні формули A1. Для коду Delphi та C++Builder, що генерує одну й ту саму формулу для сотень рядків, цей один метод усуває цілий клас помилок, пов'язаних із конкатенацією рядків
Цей клас помилок знайомий кожному, хто програмно заповнював стовпці. Ви проходите по рядках у циклі і для кожного рядка створюєте рядок формули A1 за допомогою Format('D%d*E%d', [Row, Row]). Кожна ітерація вставляє номери рядків у текст, і ці номери рядків є єдиною частиною, що змінюється. Достатньо один раз помилитися зі зміщенням, переплутати індекс циклу з базою 0 із номерами рядків A1 з базою 1 або змістити блок даних на рядок заголовка — і кожна формула в стовпці буде вказувати не на той рядок. Жодних винятків не виникне, просто отримані значення будуть неправильними. Формула, яку ви насправді мали на увазі: «помножити дві комірки ліворуч від мене», взагалі не містить номерів рядків, і нотація R1C1 дозволяє записати її саме так
Що таке нотація R1C1 і коли її слід використовувати?
Нотація R1C1 адресує комірки за номерами рядків і стовпців замість букви стовпця та номера рядка, і вона позначає відносні посилання як явні зміщення від комірки з формулою. Наприклад, R2C3 — це абсолютна комірка в рядку 2, стовпці 3, що в нотації A1 записується як $C$2. Запис R[-1]C[2] вказує на одну комірку вище і дві комірки праворуч від місця розташування формули. Запис RC посилається на саму комірку з формулою. Суть полягає у зміщеннях у квадратних дужках: відносне посилання в R1C1 виглядає однаково незалежно від того, в якій комірці воно знаходиться, тоді як представлення цього ж посилання в нотації A1 змінюється з кожним рядком
Нотація повністю виправдовує себе в одному поширеному сценарії: генерація за шаблонами, коли одну й ту саму відносну формулу необхідно вставити в кожен рядок області даних. У нотації A1 вам доведеться заново формувати текст формули для кожного рядка. У нотації R1C1 цей текст залишається константою. Це також ближче до того, як формати електронних таблиць влаштовані зсередини: записи спільних формул зберігають відносні посилання як зміщення рядків і стовпців від поточної комірки, тому рядок A1 для кожного рядка є тим, що ваш код спочатку синтезує, а потім парсер розбирає назад на зміщення. R1C1 дозволяє уникнути цього зайвого кроку. Для інтерактивних формул, створюваних користувачами вручну, природним вибором залишається A1, тому вона є стандартною скрізь у HotXLS
Як HotXLS компілює формулу R1C1?
HotXLS надає доступ до цієї функції на двох рівнях, які були додані у версії v2.175.0. Метод TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula використовується найчастіше: він повертає скомпільовану формулу, готову до обчислення, точно так само, як і аналогічний метод GetCompiledFormula для A1, але з двома додатковими параметрами, які вказують на 0-базові рядок і стовпець комірки, до якої належить формула. Під ним метод TXLSFormula.GetCompiledR1C1 створює вихідне дерево синтаксису, а окрема функція R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String виконує безпосереднє перетворення нотацій. Процес простий: перекласти текст R1C1 у відповідний текст A1, використовуючи координати поточної комірки, а потім скомпілювати отриманий текст A1 за допомогою існуючого движка — того самого движка, який розпізнає визначені імена та міжаркушні посилання й виконує користувацькі функції робочого аркуша
Оскільки перетворення відбувається перед компіляцією, всі подальші кроки виконуються так, ніби ви самі написали формулу в нотації A1. Виклик R1C1ToA1('R2C3', 4, 3) повертає '$C$2' незалежно від поточної комірки, оскільки обидві координати є абсолютними. Виклик R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — для формули, що знаходиться в комірці D5 (оскільки CurRow = 4 та CurCol = 3 задані з базою 0) — повертає 'SUM(D2:D4)': зміщення обчислюються відносно рядка 5 та стовпця D і виводяться як звичайні відносні посилання A1. Діапазони не потребують спеціальної обробки; двокрапка переноситься як є, а кожна кінцева точка перетворюється окремо
// 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)Заповнення стовпця однією відносною формулою
Перевага стає очевидною в циклі. Порівняйте версію A1, яка заново генерує текст формули на кожній ітерації, з версією R1C1, де формула залишається константою, а змінюються лише координати поточної комірки. Обидва варіанти компілюються через TXLSCalculator та обчислюються за допомогою GetValue; калькулятор приймає зворотний виклик (callback) провайдера комірок при створенні, тому оцінювач може отримувати значення комірок безпосередньо з вашого джерела даних
// 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;Цикл R1C1 взагалі не містить арифметики рядків у тексті формули. Запис 'RC[-2]*RC[-1]' означає «той самий рядок, на дві комірки ліворуч, помножити на той самий рядок, на одну комірку ліворуч» як для другого рядка, так і для п'ятисотого, і якщо блок даних згодом зміститься через появу рядка заголовка, константа формули не зміниться — зміняться лише межі циклу. Версія A1 має два місця для потенційної помилки при коригуванні + 1; версія R1C1 не має жодного
Які форми R1C1 підтримує конвертер?
Конвертер R1C1ToA1 розпізнає форми, задокументовані для версії v2.175.0: R[n]C[m] для відносних зміщень у будь-якому напрямку, RnCm для абсолютних рядків і стовпців, R[-n]C[m] з від'ємними зміщеннями, просто RC для посилання на поточну комірку, а також діапазони, такі як R1C1:R3C3 або R[-1]C:R[1]C. Рядки та стовпці розбираються незалежно, тому змішані форми на кшталт R[1]C3 (відносний рядок, абсолютний стовпець) також працюють, а самі літери нечутливі до регістру, тому r[-1]c[2] компілюється так само, як і варіант із великими літерами. Компоненти у квадратних дужках стають відносними координатами A1, а прості числа — абсолютними координатами із символом $
Цікаве питання полягає в тому, як конвертер уникає спотворення решти тексту у формулі, адже R та C — дуже поширені літери. Правило розрізнення базується на токенах: символ R розглядається як початок посилання лише тоді, коли перед ним немає іншої літери, а сам кандидат має повністю відповідати структурі посилання — необов'язковий рядок, обов'язкова літера C, необов'язковий стовпець; в іншому випадку текст залишається без змін. Саме тому у формулі ROUND(R[-1]C[0], 2) перетворюється лише внутрішнє посилання: за літерою R у назві ROUND йде O, а не цифра, дужка чи C, тому розбір не відбувається і назва функції переноситься без змін. Ця ж логіка захищає ROW(), а назви функцій, що починаються з C, взагалі ніколи не розглядаються як кандидати, оскільки посилання може починатися лише з R. У примітках до релізу v2.175.0 зазначено, що рядкові літерали та ідентифікатори також пропускаються; проте, якщо рядковий літерал у лапках у вашій формулі містить текст, схожий за формою на посилання R1C1, варто один раз перевірити скомпільований результат перед використанням у робочому коді
Базові координати та деталі закріплення посилань
У цьому API поєднуються дві різні угоди, і їх чітке розрізнення допомагає уникнути єдиної реальної пастки. Параметри CurRow та CurCol у методі GetCompiledFormulaR1C1 є 0-базовими, відповідно до API калькулятора, тоді як числа в самій нотації починаються з 1, як це відображається в Excel: R2C3 — це рядок 2, стовпець 3, тобто $C$2, а не $D$3. Якщо лічильник вашого циклу вже починається з 0, ви передаєте його напряму як CurRow; додавання + 1 відбувається всередині конвертера, а не у вашому коді
Ще одна деталь має значення, якщо скомпільована формула буде існувати довше, ніж комірка, для якої вона була створена. Коли ви опускаєте частину рядка чи стовпця — наприклад, RC[-1] або R[2]C — пропущена координата приводиться до поточної комірки і виводиться як абсолютна координата з символом $ у перетвореному тексті A1. Під час обчислення це непомітно, оскільки значення в обох випадках однакові. Однак тип закріплення (відносне чи абсолютне) визначає, як зміщуватимуться посилання при подальшому вставленні або видаленні рядків чи стовпців, як описано в супутній статті про коригування посилань у формулах. Якщо вам потрібно, щоб координата залишалася відносною під час структурних змін, вказуйте зміщення явно — R[0]C[-1] замість RC[-1] — щоб конвертер створив відносне посилання A1
Компіляція R1C1 є частиною формульного движка в складі компонента HotXLS Delphi Excel Component, разом із компілятором A1, графом перерахунку та описаним вище API оцінювання. Якщо ваш код будує електронні таблиці шляхом запису формул у стовпці через циклі, перехід від конкатенації рядків A1 до використання константи R1C1 є одним із найпростіших та найефективніших способів підвищення надійності роботи програми