نشانه‌گذاری فرمول R1C1 در دلفی با HotXLS

کامپوننت HotXLS فرمول‌های نوشته‌شده در نشانه‌گذاری مرجع R1C1 را از طریق TXLSCalculator.GetCompiledFormulaR1C1 کامپایل می‌کند، که اشکالی مانند R2C3 (مطلق)، R[-1]C[2] (آفست نسبی) و RC (سلول فعلی) را می‌پذیرد، آنها را در برابر ردیف و ستون سلولی که فرمول در آن قرار دارد به نشانه‌گذاری A1 تبدیل می‌کند و نتیجه را به همان کامپایلری می‌دهد که فرمول‌های معمولی A1 را مدیریت می‌کند. برای کدهای دلفی و C++Builder که فرمول یکسانی را در صدها ردیف تولید می‌کنند، این یک متد کل یک کلاس از باگ‌های الحاق رشته را حذف می‌کند

کلاس باگ برای هر کسی که به صورت برنامه‌نویسی یک ستون را پر کرده است آشناست. شما روی ردیف‌ها حلقه می‌زنید و برای هر ردیف یک رشته فرمول A1 را با Format('D%d*E%d', [Row, Row]) می‌سازید. هر تکرار اعداد ردیف را به متن متصل می‌کند و اعداد ردیف تنها بخشی هستند که تغییر می‌کنند. اگر یک بار آفست را اشتباه بگیرید، شمارنده حلقه مبتنی بر ۰ را با شماره ردیف‌های A1 مبتنی بر ۱ مخلوط کنید، یا بلوک داده را با یک ردیف هدر جابجا کنید، هر فرمول در ستون به اندازه یک ردیف جابجا می‌شود. هیچ استثنایی رخ نمی‌دهد؛ اعداد به سادگی اشتباه هستند. فرمولی که در واقع مد نظر شما بود، "ضرب دو سلول سمت چپ من"، اصلاً به شماره ردیف اشاره نمی‌کند و نشانه‌گذاری R1C1 به شما اجازه می‌دهد آن را به همین صورت بنویسید

نشانه‌گذاری R1C1 چیست و چه زمانی باید از آن استفاده کرد؟

نشانه‌گذاری R1C1 به جای حرف ستون به علاوه عدد ردیف، سلول‌ها را با شماره ردیف و ستون آدرس‌دهی می‌کند و ارجاعات نسبی را به عنوان آفست‌های صریح از سلول فرمول علامت‌گذاری می‌کند. R2C3 سلول مطلق در ردیف ۲، ستون ۳ است که در 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 متدی است که بیشتر کدهای برنامه آن را صدا می‌زنند: این متد یک فرمول کامپایل‌شده آماده برای ارزیابی را برمی‌گرداند، دقیقاً مانند متد همزاد A1 خود GetCompiledFormula، اما با دو پارامتر اضافی که ردیف و ستون مبتنی بر ۰ سلولی را که فرمول به آن تعلق دارد نام‌گذاری می‌کنند. در زیر آن، 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 and CurCol = 3 مبتنی بر ۰ هستند — مقدار 'SUM(D2:D4)' را برمی‌گرداند: آفست‌ها در برابر ردیف ۵، ستون 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 ارزیابی می‌شوند؛ ماشین‌حساب در زمان ایجاد یک کال‌بک ارائه‌دهنده سلول را می‌گیرد تا ارزیابی‌کننده بتواند مقادیر سلول‌ها را از منبع داده شما استخراج کند

// 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 به جای یک رقم، کروشه یا C، حرف O قرار گرفته است، بنابراین تجزیه با شکست مواجه می‌شود و نام تابع بدون تغییر عبور می‌کند. همین منطق از ROW() نیز محافظت می‌کند و نام‌های توابع که با C شروع می‌شوند اصلاً نامزد بررسی نیستند، زیرا فقط R یک ارجاع را شروع می‌کند. یادداشت‌های انتشار نسخه v2.175.0 بیان می‌کنند که رشته‌های لفظی و شناسه‌ها نیز نادیده گرفته می‌شوند؛ با این حال، اگر یک عبارت نقل‌قول شده در فرمول شما حاوی متنی دقیقاً به شکل ارجاع R1C1 باشد، ارزش دارد قبل از اعتماد به آن در مرحله تولید، یک بار خروجی کامپایل‌شده را بررسی کنید

پایه‌های مختصات و جزئیات لنگراندازی که دانستن آنها ارزش دارد

دو قرارداد در داخل این API با هم ملاقات می‌کنند و دقیق نگه داشتن آنها مانع از تنها تله واقعی می‌شود. پارامترهای CurRow و CurCol در GetCompiledFormulaR1C1 بر پایه ۰ هستند و از API ماشین‌حساب پیروی می‌کنند، در حالی که اعداد داخل خود نشانه‌گذاری بر پایه ۱ هستند و با آنچه اکسل نشان می‌دهد مطابقت دارند: R2C3 ردیف ۲، ستون ۳ است که $C$2 است و نه $D$3. اگر شمارنده حلقه شما از قبل مبتنی بر ۰ است، آن را مستقیماً به عنوان CurRow پاس می‌دهید؛ مقدار + 1 در داخل مبدل زندگی می‌کند و نه در کد شما

اگر فرمول کامپایل‌شده بیشتر از سلولی که برای آن کامپایل شده است عمر کند، یک جزئیات دیگر نیز اهمیت دارد. وقتی یک بخش ردیف یا ستون را حذف می‌کنید — مانند اشکال RC[-1] یا R[2]C — مختصات حذف‌شده به سلول میزبان منتهی می‌شود و به عنوان مختصات مطلق لنگردار با $ در متن تبدیل‌شده A1 صادر می‌گردد. در زمان ارزیابی این امر نامرئی است، زیرا مقدار در هر دو صورت یکسان است. اما لنگراندازی نسبی در مقابل مطلق تعیین می‌کند که وقتی ردیف‌ها یا ستون‌ها بعداً درج یا حذف می‌شوند، ارجاعات چگونه جابجا شوند، همانطور که در مقاله همراه در مورد تنظیم ارجاع فرمول پوشش داده شده است. اگر نیاز دارید یک مختصات در طول ویرایش‌های ساختاری نسبی باقی بماند، آفست را به صراحت بنویسید — R[0]C[-1] به جای RC[-1] — تا مبدل یک ارجاع بدون لنگر A1 صادر کند

کامپایل R1C1 بخشی از موتور فرمول در HotXLS Delphi Excel Component در کنار کامپایلر A1، گراف محاسبه مجدد و API ارزیابی نشان داده شده در بالا است. اگر کد شما صفحات گسترده را با چرخاندن فرمول‌ها در ستون‌ها می‌سازد، انتقال آن حلقه‌ها از A1 الحاق‌شده با رشته به یک ثابت R1C1 واحد، یکی از ارزان‌ترین ارتقاهای قابلیت اطمینان موجود است