محور زمانی مدل مالی پویا با SEQUENCE و LET

محور زمانی مدل مالی ستون فقرات هر مدل است: تاریخ، سال، ماه و فصل هر ستون از همین‌جا می‌آید. روش سنتی یعنی کشیدن فرمول به راست و تکرار آن با هر تغییر افق مدل. در این نوشته سه روش ساخت محور زمانی را مقایسه می‌کنیم: روش سنتی، روش پویا با SEQUENCE و روش تک‌فرمولی با LET.

چرا محور زمانی مدل مالی باید پویا باشد؟

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

محور زمانی پویا دو ورودی دارد و بقیه را خودش حساب می‌کند:

• تاریخ شروع مدل (مثلاً خانه‌ی C5)
• تعداد ماه‌های مدل (مثلاً خانه‌ی C6)

با عوض‌کردن همین دو عدد، تمام ستون‌ها کم یا زیاد می‌شوند. این همان اصل «ورودی را یک‌جا نگه دارید» است که در فرض‌های مدل مالی و محور زمانی یکدست هم دیده‌ایم.

روش اول: محور زمانی سنتی

در این روش هر ردیف با یک تابع معمولی ساخته می‌شود و باید برای هر ماه کشیده شود:

ایراد اصلی روشن است: اگر تعداد ماه‌ها عوض شود، باید دوباره بکشید. در مدل بزرگ، فراموش‌کردن یک ردیف هم ممکن است تا مرحله‌ی بررسی دیده نشود.

روش دوم: محور زمانی پویا با SEQUENCE

تابع SEQUENCE یک دنباله‌ی عددی می‌سازد و EDATE آن را به ماه‌های متوالی تبدیل می‌کند. فقط خانه‌ی اول تاریخ را می‌نویسید:

=EDATE(C5, SEQUENCE(1, C6, 0, 1))

یعنی از تاریخ شروع، یک ردیف با C6 ستون بساز و هر ستون را یک ماه جلوتر ببر. نتیجه به راست «سرریز» می‌شود (spill) و دیگر لازم نیست چیزی را بکشید.

برای ردیف‌های بعدی کافی است به آرایه‌ی سرریزشده با علامت # ارجاع بدهید. اگر تاریخ‌ها از D10 شروع شوند:

• سال: =YEAR(D10#)
• ماه: =MONTH(D10#)
• فصل: ="Q"&ROUNDUP(D11#/3,0) که D11 همان ردیف ماه است
• فصل و سال: ترکیب ردیف فصل با RIGHT(D12#,2) که D12 ردیف سال است

علامت # یعنی «کل محدوده‌ی سرریز». هر ردیف به ردیف قبلی وصل است و همه با هم کوچک و بزرگ می‌شوند. اگر C6 را از ۳ به ۶ تغییر دهید، هر پنج ردیف خودکار شش ستونی می‌شوند.

روش سوم: کل محور زمانی در یک فرمول با LET

تابع LET اجازه می‌دهد به نتیجه‌ی یک محاسبه نام بدهید و بعد در همان فرمول بارها از آن نام استفاده کنید. محور زمانی را می‌توان با هفت نام ساخت: دو ورودی و یک نام برای هر ردیف:

=LET(
  st, C5,
  n, C6,
  dt, EDATE(st, SEQUENCE(1, n, 0, 1)),
  yr, YEAR(dt),
  mt, MONTH(dt),
  qt, "Q"&ROUNDUP(mt/3, 0),
  qy, qt&"-"&RIGHT(yr, 2),
  HSTACK(
    VSTACK("Date","Year","Month","Quarter","Quarter & Year"),
    VSTACK(dt, yr, mt, qt, qy)
  )
)

فرمول چگونه خوانده می‌شود؟

• st و n: ورودی‌ها هستند. از این به بعد به‌جای C5 و C6 فقط st و n می‌نویسیم.
• dt تا qy: هر نام یک ردیف محور زمانی است. هر کدام از نام‌های قبلی استفاده می‌کند؛ مثلاً qt از mt می‌آید.
• VSTACK اول: برچسب‌های ثابت را در یک ستون زیر هم می‌چیند.
• VSTACK دوم: پنج ردیف داده را روی هم می‌گذارد. چون هر ردیف پویا است، این بلوک به‌اندازه‌ی n ستون پهن می‌شود.
• HSTACK: ستون برچسب‌ها را کنار بلوک داده می‌گذارد تا جدول کامل شود.

نکته: نام‌های st و n فقط ورودی هستند و در خروجی نمی‌آیند. فقط پنج ردیفی که لازم داریم در VSTACK دوم قرار می‌گیرند.

مثال عددی ساده

فرض کنید تاریخ شروع مدل ۱ ژانویه ۲۰۲۶ و تعداد ماه‌ها ۶ باشد. خروجی هر سه روش یکسان است:

اگر ماه‌ها را به ۱۲ تغییر دهید، در روش دوم و سوم شش ستون جدید خودکار اضافه می‌شود. در روش اول باید هر پنج ردیف را دستی بکشید.

مزایا، محدودیت‌ها و نکته‌های ممیزی

مزیت تک‌فرمولی: خوانایی بهتر (هر نام معنی دارد)، سرعت بهتر (هر مقدار فقط یک بار محاسبه می‌شود) و یک نقطه‌ی واحد برای اصلاح. اما چند محدودیت هم هست:

• این توابع به نسخه‌های جدید اکسل (Microsoft 365 یا اکسل ۲۰۲۱ به بعد) نیاز دارند.
• فرمول فصل بالا بر پایه‌ی سال تقویمی است. اگر سال مالی شما از ماهی غیر از ژانویه شروع می‌شود، باید ماه را جابه‌جا کنید.
• اگر خانه‌ای در مسیر سرریز پر باشد، اکسل خطای #SPILL! می‌دهد؛ فضای سمت راست را خالی بگذارید.
• برای مدل‌های مشترک با همکارانی که نسخه‌ی قدیمی دارند، روش سنتی امن‌تر است.

حتی اگر هرگز این سبک را در مدل خود به کار نبرید، آشنایی با آن برای ممیزی مدل لازم است؛ چون مدل‌های جدیدتر بیشتر شبیه یک بلوک کد هستند تا «یک فرمول در هر خانه». برای عادت‌های کلی ساخت مدل، نُه نکته‌ی مدل‌سازی مالی و مدیریت مدل مالی بزرگ مکمل خوبی هستند.

پرسش‌های متداول

تفاوت EDATE و EOMONTH در محور زمانی چیست؟

EDATE همان روز ماه را حفظ می‌کند (۱۵ ژانویه می‌شود ۱۵ فوریه) و EOMONTH آخر ماه را برمی‌گرداند. برای محور ماهانه‌ی مدل، اگر شروع از اول ماه باشد EDATE کافی است؛ اگر پایان ماه مهم است، EOMONTH را به کار ببرید.

آیا استفاده از LET فرمول را کندتر می‌کند؟

نه، معمولاً برعکس. هر نام فقط یک بار محاسبه می‌شود و در ادامه دوباره استفاده می‌شود، پس در فرمول‌های سنگین سرعت بهتر می‌شود.

اگر فرمول سرریز با خطای SPILL مواجه شد چه کنم؟

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

برای مدل ماهانه‌ی ۶۰ ماهه هم این روش مناسب است؟

بله. فقط n را ۶۰ بگذارید و ردیف‌های بعدی خودکار پهن می‌شوند. فقط مطمئن شوید سایر بخش‌های مدل هم به همین محور وصل‌اند.

جمع‌بندی

محور زمانی سنتی با کشیدن فرمول کار می‌کند؛ روش SEQUENCE آن را با سرریز خودکار می‌کند و روش LET کل محور را در یک بلوک خوانا جمع می‌کند. ورودی‌ها را در دو خانه نگه دارید (تاریخ شروع و تعداد ماه)، بقیه را بسپارید به فرمول، و پیش از انتشار مدل برای دیگران، سازگاری نسخه‌ی اکسل و تعریف سال مالی را بررسی کنید.