فرمول‌های سرریز آرایه پویا در دلفی با استفاده از HotXLS

ابزار HotXLS فرمول‌های سرریز آرایه پویا (dynamic array spill formulas) اکسل ۳۶۵ را به صورت بومی در دلفی و سی‌پلاس‌پلاس‌بیلدر ارزیابی می‌کند: شما به آن کارپوشه‌ای می‌دهید که سلول‌های آن حاوی توابع XLOOKUP یا FILTER هستند، و موتور فرمول آن مجموعه نتایج را محاسبه کرده و در مستطیل خروجی متصل‌شده سرریز می‌کند، یعنی دقیقاً همان مقادیری که اکسل تولید می‌نماید. کامپوننت اکسل HotXLS دلفی یک فرمول سرریز را به عنوان یک سلول ریشه که مالک محاسبات است به همراه بلوکی از سلول‌های وابسته که از آن می‌خوانند، مدل‌سازی می‌کند؛ این تمام چیزی است که قبل از نوشتن یک خط کد باید بدانید

این مدل اهمیت دارد زیرا کارهای روزمره انتزاعی نیستند. یک مشتری یک فایل .xlsx ساخته‌شده در اکسل ۳۶۵ را ارسال می‌کند که شیت‌های آن پر از =FILTER(...) و =XLOOKUP(...) است، و سرویس شما باید دقیقاً همان اعداد را به صورت بدون سر (headless) روی سیستمی که هیچ اکسلی روی آن نصب نیست تولید کند، و سپس یا مقادیر سرریز شده را بخواند یا یک ناحیه سرریز جدید بنویسد. آرایه‌های پویا قرارداد محاسبات را از «یک فرمول، یک سلول» به «یک فرمول، یک مستطیل از سلول‌ها» تغییر دادند، و موتور HotXLS به جای شبیه‌سازی آن با یک شبکه از پیش گسترده‌شده، از همان قرارداد پیروی می‌کند

توابع XLOOKUP و FILTER را چگونه در دلفی ارزیابی کنیم؟

ابزار HotXLS هر تابع آرایه پویا را از طریق یک نقطه ورود ارزیاب واحد، یعنی CalcDynArrayFunc در lxCalc.pas ارسال می‌کند، بنابراین کل این خانواده از یک مسیر کد سرریز مشترک استفاده می‌نمایند. مجموعه پشتیبانی‌شده شامل XLOOKUP و FILTER (دو تابع اصلی)، به همراه توابع XMATCH، SORT، UNIQUE و SEQUENCE است. هر کدام یک آرایه متغیر دو بعدی را به جای یک مقدار اسکالر (scalar) برمی‌گردانند: تابع SEQUENCE(3;2;1;1) یک شبکه ۳ در ۲ تولید می‌کند، SORT ردیف‌ها را بر اساس هر ستون کلیدی به صورت صعودی یا نزولی مرتب می‌نماید، UNIQUE ردیف‌های تکراری را حذف کرده و اولین مورد را نگه می‌دارد، و XMATCH موقعیت مبتنی بر ۱ یک مقدار را در حالت‌های مطابقت دقیق، نویسه عمومی (wildcard)، بزرگ‌تر بعدی و کوچک‌تر بعدی که مشابه XLOOKUP است، گزارش می‌دهد. برخلاف توابع توزیع آماری اسکالر که یک عدد در هر فراخوانی برمی‌گردانند، این توابع یک ساختار کامل را پس می‌دهند

برای محاسبه کارپوشه‌ای که از قبل حاوی این فرمول‌ها است، آن را باز کنید، یک بار متد Recalculate را فراخوانی کنید و سلول‌هایی را که سرریز در آنها انجام شده بخوانید. متد TXLSXWorkbook.Recalculate گراف وابستگی را پیمایش کرده و هر فرمول تغییریافته را به ترتیب توپولوژیک ارزیابی می‌کند، بنابراین یک ریشه سرریز فقط یک بار محاسبه می‌شود و عناصر آن مستقیماً در سلول‌های عضو نوشته می‌شوند

اگر کارپوشه به تابعی نیاز دارد که موتور آن را ارائه نمی‌دهد، همان ارزیاب به شما اجازه می‌دهد منطق خود را از طریق رویداد توابع سفارشی کاربرگ متصل کنید، و یک تابع سفارشی نیز آزاد است که یک آرایه متغیر برگرداند تا دقیقاً مانند توابع داخلی سرریز کند

اتصال محدوده سرریز با SetArrayFormula

ابزار HotXLS هرگز حدس نمی‌زند که اندازه سرریز چقدر باید باشد: شما مستطیل خروجی را مشخص می‌کنید و متد TXLSRange.SetArrayFormula فرمول را به آن متصل می‌نماید. این متد فرمول را یک بار کامپایل می‌کند، درخت نحو را در سلول ریشه بالا-چپ به همراه محدوده متصل‌شده ذخیره می‌نماید و به هر سلول دیگر در مستطیل، یک فرمول سبک‌وزن می‌دهد که مرجعی ضعیف به ریشه دارد. در زمان محاسبه مجدد، ریشه یک بار ارزیابی می‌شود و عناصر ماتریس آن مستقیماً به هر سلول عضو ارسال می‌گردد؛ این همان روشی است که یک ریشه سرریز به عنوان یک گره در گراف وابستگی محاسبه مجدد افزایشی نشان داده می‌شود. این روشی است که باید در زمانی که کار نوشتن یک ناحیه سرریز در فایل است استفاده شود، به جای خواندن آن

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // Size the rectangle to the result: a 3-row by 2-column grid.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // fill A1:B3 with 1..6 down the rows
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

نتیجه اتصال صریح، محدودیتی است که ارزش بیان صریح دارد: HotXLS بر خلاف اکسل تعاملی، اندازه محدوده سرریز را به طور خودکار تغییر نمی‌دهد. اکسل با تغییر ورودی‌ها، ناحیه سرریز شده را بزرگ یا کوچک می‌کند و در صورتی که سلول‌های مقصد اشغال شده باشند خطای #SPILL! را نشان می‌دهد. در HotXLS، مستطیلی که به SetArrayFormula می‌دهید دقیقاً همان چیزی است که دریافت می‌کنید، بنابراین اندازه آن را بر اساس نتیجه‌ای که انتظار دارید تنظیم نمایید؛ اگر یک تابع ردیف‌های بیشتری نسبت به آنچه متصل کرده‌اید تولید کند، مقدار اضافی جایی برای قرار گرفتن نخواهد داشت

ابزار HotXLS عملگرهای محاسباتی را زمانی که هر یک از طرفین عملیات یک آرایه باشد، به صورت عنصر به عنصر اعمال می‌کند. تابع کمکی ApplyArrayBinaryOp عملگرهای +، -، *، /، و ^ را روی آرایه‌های متغیر ۱ بعدی و ۲ بعدی پوشش می‌دهد: یک عملوند اسکالر به هر عنصر اعمال می‌شود، دو آرایه باید هم‌شکل باشند در غیر این صورت عملیات رد می‌شود، و تقسیم بر صفر به صورت خطا منتقل می‌گردد تا اینکه کرش رخ دهد. مسیر محاسبات اسکالر دست‌نخورده باقی می‌ماند، بنابراین این فرآیند تنها زمانی فعال می‌شود که حداقل یک عملوند واقعاً آرایه باشد، مانند یک محدوده سرریز شده یا نتیجه یک تابع ماتریسی. این بدان معنی است که فرمولی مانند =D2:D13*1.1 که روی یک ستون متصل شده است هر عنصر را به نوبت ضرب می‌کند، و فرمول =D2:D13*E2:E13 دو ستون با شکل یکسان را موقعیت به موقعیت ضرب می‌نماید

// Scalar broadcast: every cell of the anchor gets D(n) * 1.1.
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// Two arrays of identical shape multiply element by element.
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

عملگر @ در مقابل یک آرایه قدیمی CSE چه کاری انجام می‌دهد؟

ابزار HotXLS یک کاراکتر @ صریح را به عنوان عملگر تلاقی مرجع (reference-intersection operator) می‌خواند، نه به عنوان تلاقی ضمنی انتخاب ردیف در اکسل پویا. در موتور محاسباتی، عبارت A1:A3 @ B1:B3 تک‌سلولی را که دو محدوده با هم تلاقی دارند برمی‌گرداند، و این عملگر قوی‌تر از ^ و ضعیف‌تر از % عمل می‌کند. یک نکته در این باره: شکل تلاقی ضمنی جداشده با فاصله هنوز توکنایز (tokenised) نشده است زیرا لکسر (lexer) همچنان فضاهای خالی را نادیده می‌گیرد، بنابراین شما تلاقی را فقط از طریق نماد صریح @ دریافت می‌کنید

تمایز عمیق‌تر بین فرمول آرایه قدیمی CSE و یک آرایه پویای مدرن است، و در HotXLS هر دو از طریق همان ساختار اتصال جریان می‌یابند. یک فرمول آرایه‌ای کلاسیک، بلوک {=...} بود که با کلیدهای میانبر Ctrl+Shift+Enter روی یک انتخاب با اندازه از پیش تعیین‌شده وارد می‌شد و هر سلول در یک فرمول کامپایل‌شده مشترک شریک بود. یک آرایه پویا یک فرمول واحد است که نتیجه آن شکل خودش را تعیین می‌کند. ابزار HotXLS آنها را در متد SetArrayFormula یکپارچه می‌کند: شما همیشه مستطیل را مشخص می‌کنید، ریشه مالک درخت کامپایل‌شده است، و اعضا به آن ارجاع می‌دهند، چه فرمول یک عبارت ماتریسی با سبک قدیمی باشد و چه یک تابع جدید SORT. آنچه هرگز دریافت نمی‌کنید، رفتار خودکار اکسل است که در آن ناحیه به طور بی‌صدا تغییر اندازه می‌دهد، و به خاطر داشتن این تفاوت مانع از مخدوش شدن این دو مدل ذهنی می‌شود

جایی که پشتیبانی از آرایه پویا متوقف می‌شود

شناختن مرزهای کاری از ساعت‌ها دیباگ کردن جلوگیری می‌کند. تابع SORT از جهت کلید پیروی می‌کند که به طور پیش‌فرض صعودی و با منفی بودن آرگومان ترتیب، نزولی است؛ جهت مقایسه جایی است که ارزش دارد ستون‌های کلیدی خود را در برابر اکسل آزمایش کنید، زیرا یک مقایسه معکوس به طور بی‌صدا ردیف‌ها را با ترتیب معکوس برمی‌گرداند تا اینکه با خطا مواجه شود. تابع UNIQUE اولین رخداد هر ردیف متمایز را نگه می‌دارد که موتور محاسباتی آن را با بررسی ردیف‌های قبلی تأیید می‌کند به جای اینکه به تعداد تکرار خام تکیه کند. تابع TABLE (جدول‌های داده تحلیل What-If) شناسایی می‌شود تا در فایل رفت و برگشت داشته باشد، اما به یک فیلد نگهدارنده (placeholder) ارزیابی می‌شود زیرا نتایج جایگزینی واقعی همان مقادیر کش‌شده‌ای هستند که اکسل از قبل ذخیره کرده است، و HotXLS شبکه تحلیل what-if را دوباره اجرا نمی‌کند

تابع LET مشخص‌ترین محدودیت برای ذکر کردن است. HotXLS تابع LET را ثبت می‌کند و مسیر ارزیابی برای آن دارد، اما پارسر یک نام خام را که از قبل تعریف نشده باشد رد می‌کند، بنابراین عبارت =LET(x;10;x) در حین پارس کردن و قبل از اینکه به ارزیاب برسد با خطا مواجه می‌شود. با LET به عنوان یک تابع غیرپشتیبانی‌شده رفتار کنید تا زمانی که پارسر قابلیت تشخیص دامنه (scope awareness) را برای نام‌های متصل به دست آورد. یک نکته کوچک درباره قابلیت حمل: رشته‌های فرمول در این مثال‌ها از جداکننده لیست موتور محاسباتی یعنی علامت سمی‌کالن استفاده می‌کنند، بنابراین در زمان آماده کردن متن فرمول، آن را با هر جداکننده‌ای که سیستم شما انتظار دارد هماهنگ کنید. توابع آرایه پویا که در اینجا پوشش داده شدند، از XLOOKUP و FILTER گرفته تا SORT، UNIQUE، SEQUENCE و XMATCH، همراه با HotXLS Delphi Excel Component ارائه می‌شوند که بخش مرجع فرمول آن کاتالوگ کامل توابع و حالت‌های آرگومان پشتیبانی‌شده هر تابع را لیست کرده است