موتور محاسبه اکسل

نمای کلی

HotXLS شامل موتور داخلی برای تجزیه و محاسبه فرمول‌ها است؛ این موتور فرمول‌های سلول را به‌صورت برنامه‌نویسی درون پردازش شما ارزیابی می‌کند و نیاز به فراخوانی اکسل یا کنترل‌کننده‌های OLE خارجی برای حل مقادیر محاسبه‌شده کتاب کار را از بین می‌برد

اجرای محاسبه

پیش از ذخیره یا صادرات، با استفاده از رابط اصلی کتاب کار یک محاسبه کامل کتاب کار را راه‌اندازی کنید

procedure Calculate;

موتور خانواده‌های استاندارد توابع ریاضی، تاریخ، منطقی، رشته‌ای و آماری را پشتیبانی می‌کند؛ وابستگی‌های سلول را به‌صورت متوالی حل کرده و زنجیره‌های مرجع را برای جلوگیری از مراجع دوری دنبال می‌کند

ارزیابی درزمینه‌ای فرمول‌ها

TXLSWorksheet و TXLSXWorksheet متد EvaluateFormulaAt را در اختیارتان می‌گذارند تا متن فرمول را در سطر و ستون انتخابی (با اندیس مبنای یک) محاسبه کنید، بدون اینکه سلول موقتی نوشته شود یا وضعیت محاسبه کارپوشه عوض شود

سبک‌های ارجاع A1 و R1C1، نام‌های محلی، structured reference ها، وابستگی‌های فرمول، ریشه‌های آرایه‌ای، مقادیر خطای نوع‌دار اکسل، وضعیت volatile، مجوز صریح ارجاع خارجی، لغو عملیات و دسته‌های پایدار خطا، همگی از همان ارزیاب عادی کارپوشه استفاده می‌کنند

CreateFormulaEvaluationTemplate یک بار یک anchor تغییرناپذیر کامپایل می‌کند و آن را در سلول‌های هدف مختلف دوباره به کار می‌گیرد؛ structured reference های A1 که به سطر هدف حساس‌اند، برای هر زمینه هدف جداگانه حل می‌شوند و محدودیت‌های هر فراخوانی، طول فرمول، تعداد گام‌های آیتم‌های نحوی، عمق recursion و حجم بر حسب بایتِ آرایه تولیدشده را مهار می‌کنند

برای انواع نتیجه، بودجه‌های پیش‌فرض، قواعد طول عمر و مثال‌های Delphi و C++Builder به ارزیابی درزمینه‌ای فرمول‌ها سر بزنید

عملگرهای آرایه‌ای

عملگرهای محاسباتی، مقایسه‌ای، الحاق، نقیض یکانی و درصد، علاوه بر مقادیر اسکالر، آرایه‌های محاسبه‌شده را هم می‌پذیرند

یک اسکالر روی تک‌تک عناصر broadcast می‌شود و آرایه‌ای با یک سطر یا یک ستون در بُعدِ سازگار پخش می‌شود؛ شکل‌های ناسازگار #VALUE! برمی‌گردانند

خطاها در سلول‌های نتیجه خودشان حفظ می‌شوند؛ پس یک تقسیم بر صفر یا یک عنصر نامعتبر، مقادیر محاسبه‌شده برای بقیه سلول‌ها را دور نمی‌ریزد

COMBINA و PERMUTATIONA همان معناشناسی اسکالر و آرایه broadcast را دارند؛ آرگومان‌های صحیح نامنفی به سمت پایین truncate می‌شوند و سرریز بررسی می‌شود، در حالی که MUNIT یک ماتریس همانی دوبعدی فشرده از نوع Double برمی‌گرداند

FormulaArrayMemoryLimit به‌صورت پیش‌فرض 64 مگابایت است و سقف داده خروجی ماتریس‌های تولیدشده را تعیین می‌کند؛ MUNIT علاوه بر آن، به‌جای تحمیل سقف ابعاد ثابت، فضای spill باقی‌مانده کاربرگ را هم اعتبارسنجی می‌کند

بررسی وجود فرمول

ISFORMULA(reference) بدون محاسبه سلول ارجاع‌شده بررسی می‌کند که آیا سلول فرمول دارد یا نه؛ بنابراین حتی وقتی نتیجه کش‌شده فرمول خطا یا رشته خالی باشد، فرمول همچنان قابل شناسایی می‌ماند

یک محدوده مستطیلی، آرایه بولینی با همان شکل برمی‌گرداند؛ اعضای فرمول مشترک و فرمول‌های آرایه‌ای کلاسیک یا XLSX مقدار true برمی‌گردانند، اما در dynamic spill فقط سلول anchor یک سلول فرمول است

مقادیر مستقیم و نام‌های تعریف‌شده اسکالر #VALUE! برمی‌گردانند، ارجاع‌های نامعتبر #REF! را حفظ می‌کنند و آرایه‌های بولینی تولیدشده با FormulaArrayMemoryLimit محدود می‌شوند

XLSX مقدار _xlfn.ISFORMULA را می‌نویسد و هنگام باز کردن فایل، نام خام را بازیابی می‌کند؛ ODS از سینتکس استاندارد of:=ISFORMULA استفاده می‌کند و خروجی Classic XLS پیش از ثبت داده، تابع future پشتیبانی‌نشده را رد می‌کند

توابع متنی مدرن

UNICHAR ورودی‌های عددی را به سمت صفر truncate می‌کند و مقادیر اسکالر معتبر Unicode را به UTF-16 تبدیل می‌کند؛ از جمله خروجی surrogate pair برای صفحه‌های مکمل و همه بازه‌های private-use

ورودی صفر یا خارج از بازه #VALUE! برمی‌گرداند، در حالی که code point های surrogate منفرد و noncharacter های Unicode نتیجه #N/A می‌دهند؛ ورودی از نوع محدوده یا آرایه تولیدشده، این خطاها را به‌تفکیک عنصر حفظ می‌کند

BAHTTEXT اعداد متناهی را به متن بات و ساتانگ تایلندی تبدیل می‌کند، مقادیر میانی را با دو رقم اعشار دور از صفر رند می‌کند، وقتی قدرمطلق رندشده صفر می‌شود علامت منفی را نگه می‌دارد و نماد علمی را بدون تنگ‌کردن از میان یک نوع صحیح باز می‌کند

XLSX مقدار UNICHAR را به‌صورت _xlfn.UNICHAR ذخیره می‌کند و هنگام باز شدن دوباره، نام خام را بازیابی می‌کند؛ BAHTTEXT از هویت استاندارد تابع خودش استفاده می‌کند و در فرمول‌های XLS و XLSX بدون پیشوند می‌ماند

رگرسیون و پیش‌بینی

LINEST و LOGEST یک یا چند ستون یا سطر پیش‌بین می‌پذیرند و ضرایب را به ترتیب اکسل و در ادامه عرض از مبدأ اختیاری برمی‌گردانند

وقتی آرگومان آمار true باشد، هر دو تابع نتیجه کامل پنج‌سطری را برمی‌گردانند که شامل خطای ضرایب، آمارهای برازش، آماره F، درجات آزادی، مجموع مربعات رگرسیون و مجموع مربعات پسماند است

TREND و GROWTH از همان مدل چندمتغیره استفاده می‌کنند و شکل سطری یا ستونی مقادیر پیش‌بین جدیدِ داده‌شده را حفظ می‌کنند

محاسبه مجدد وابستگی‌ها در XLS کلاسیک

TXLSWorkbook.Recalculate در اولین فراخوانی، گراف وابستگی فرمول‌ها را می‌سازد و فرمول‌ها را به‌جای ترتیب فیزیکی سلول‌ها، با اولویت precedents ارزیابی می‌کند

فراخوانی‌های بعدی از همان گراف استفاده می‌کنند و فقط وابسته‌های ترتیبیِ مقادیر تغییرکرده به‌علاوه فرمول‌های volatile را دوباره محاسبه می‌کنند؛ ویرایش فرمول‌ها، نام‌ها، ساختار کاربرگ و بازنویسی ارجاع‌ها، گراف را به‌طور امن باطل و از نو می‌سازند

ارجاع‌های دوری از تنظیمات EnableIteration، MaxIterations و MaxIterationChange کارپوشه کلاسیک استفاده می‌کنند و UseFullPrecision=False دقت نمایشی اعداد را روی نتایج کش‌شده و محاسبات پایین‌دستی‌شان اعمال می‌کند

وابستگی‌های نام‌های تعریف‌شده

محاسبه افزایشی، نام‌های سطح کارپوشه و سطح کاربرگ را در میان فرمول‌های تودرتو و ارجاع‌های چندناحیه‌ای دنبال می‌کند

فرمول‌های نامِ بدون دور، وابستگی‌های دقیق سلولی‌شان را به گراف اضافه می‌کنند، اما چرخه‌های بازگشتی نام‌ها به‌طور امن به محاسبه مجدد volatile برمی‌گردند

جدول‌های داده What-If

هم کاربرگ‌های XLS کلاسیک و هم کاربرگ‌های مدرن XLSX می‌توانند جدول‌های داده What-If تک‌متغیره یا دومتغیره را بسازند، محاسبه کنند، بخوانند، بنویسند، کپی کنند و از نظر ساختاری تنظیم کنند

function AddDataTable(
  const AResultRange, ARowInputCell, AColumnInputCell: WideString
): TXLSDataTable;

محدوده نتیجه فقط سلول‌های خروجی محاسبه‌شده را در بر می‌گیرد، در حالی که فرمول‌ها و مقادیر آزمایشی از سلول‌های اطراف، با همان چیدمان اکسل استفاده می‌کنند

• جدول با ورودی ستونی — ARowInputCell را خالی بگذارید، فرمول مبدأ را بالای هر ستون نتیجه قرار دهید و مقادیر آزمایشی را سمت چپ سطرهای نتیجه بگذارید
• جدول با ورودی سطری — AColumnInputCell را خالی بگذارید، فرمول مبدأ را سمت چپ هر سطر نتیجه قرار دهید و مقادیر آزمایشی را بالای ستون‌های نتیجه بگذارید
• جدول دومتغیره — هر دو سلول ورودی را بدهید، فرمول مبدأ را بالا و سمت چپ محدوده نتیجه قرار دهید، مقادیر آزمایشی ورودی سطری را بالای نتایج و مقادیر آزمایشی ورودی ستونی را سمت چپ بگذارید

Sheet.Range['B1', 'B1'].Value := 0;
Sheet.Range['D1', 'D1'].Formula := '=B1*2+1';
Sheet.Range['C2', 'C2'].Value := 1;
Sheet.Range['C3', 'C3'].Value := 2;
Sheet.Range['C4', 'C4'].Value := 3;

Table := Sheet.AddDataTable('D2:D4', '', 'B1');
Workbook.Recalculate;

در همین مثال با ورودی ستونی، مقادیر 3 و 5 و 7 در D2:D4 محاسبه می‌شوند؛ هر مقدار آزمایشی از طریق یک زمینه محاسبه ایزوله اعمال می‌شود، پس فرمول‌هایی که به‌طور غیرمستقیم و از راه سلول‌های فرمول دیگر به B1 می‌رسند هم درست محاسبه می‌شوند

AddDataTable برای محدوده نامعتبر، ارجاع ورودی نامعتبر، یا وقتی هر دو ارجاع ورودی خالی باشند، nil برمی‌گرداند؛ مجموعه DataTables کاربرگ، تعریف حاصل و پرچم‌های ورودی حذف‌شده آن را در اختیارتان می‌گذارد

حذف سلول ورودیِ ارجاع‌شده، تعریف جدول را نگه می‌دارد و برای خروجی‌هایش #REF! برمی‌گرداند؛ مطابق همان معناشناسی کارپوشه ذخیره‌شده

توابع تعریف‌شده توسط کاربر

موتور فرمول را با منطق کسب‌وکار سفارشی گسترش دهید؛ توابع خود را با استفاده از رویدادهای زیر ثبت کنید

نام‌های خارجی خطرناک و نام‌های با سبک macro، قبل از اجرای آرگومان‌ها یا handler ها رد می‌شوند، مگر اینکه کارپوشه یا یک ارزیابی درزمینه‌ای خاص، AllowUnsafeFormulaCallbacks را صریحاً فعال کرده باشد

• رویداد OnUserFunction — مدیریت توابع پایه تعریف‌شده توسط کاربر
• رویداد OnUserFunctionEx — حل توابع پیچیده با پارامترهای بازه سلول
• فراخوان تابع کاربر — الزامات امضای کنترل‌کننده‌های خارجی