تابع FORMULATEXT برای اشکال‌زدایی در اکسل

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

چه زمانی FORMULATEXT مفید است؟

• بررسی سریع اینکه آیا یک سلول فرمول دارد یا مقدار ثابت.
• مقایسه فرمول‌ها بین ستون‌ها یا سطرها برای یافتن اختلافات.
• یافتن لینک‌های خارجی یا توابع خاص (مثل INDIRECT یا OFFSET) در کل شیت.
• مستندسازی مدل مالی یا گزارش با نمایش فرمول‌ها کنار نتایج.

نحو و نکات پایه

نحو تابع ساده است:

=FORMULATEXT(reference)

این تابع متن فرمول موجود در reference را برمی‌گرداند. اگر مرجع فاقد فرمول باشد یا به یک ورک‌بوک بسته اشاره شود، خطا باز می‌گردد (در نسخه‌های جدید اکسل نیز همین رفتار مشاهده می‌شود).

مثال‌های عملی

1) نمایش فرمول سلول A1:

=FORMULATEXT(A1)

این فرمول متن فرمول داخل سلول A1 را نمایش می‌دهد. اگر A1 مقدار ثابت داشته باشد یا خالی باشد، FORMULATEXT مقدار خطا (مثل #N/A) را برمی‌گرداند.

2) جلوگیری از نمایش خطا با IFERROR:

=IFERROR(FORMULATEXT(A1),"--No formula--")

با این ترکیب به‌جای خطا، متن جایگزین نمایش داده می‌شود که برای گزارش‌ها و داشبوردها مناسب‌تر است.

3) مقایسه فرمول‌ها بین دو سلول:

=FORMULATEXT(A2)=FORMULATEXT(B2)

نتیجه TRUE یا FALSE نشان می‌دهد فرمول‌های دو سلول دقیقاً یکسان هستند یا خیر. این روش برای بررسی همسانی فرمول‌ها در یک ستون مدل خیلی مفید است.

یافتن توابع یا لینک‌های خارجی

برای شناسایی سریع سلول‌هایی که از توابع خاص یا لینک به فایل‌های خارجی استفاده می‌کنند، می‌توانیم از SEARCH یا ISNUMBER همراه با FORMULATEXT استفاده کنیم.

=ISNUMBER(SEARCH("INDIRECT",FORMULATEXT(A1)))

اگر متن فرمول شامل عبارت INDIRECT باشد، این فرمول TRUE برمی‌گرداند و می‌توان از آن در قالب‌بندی شرطی یا فیلتر استفاده کرد.

=ISNUMBER(SEARCH("[",FORMULATEXT(A1)))

این فرمول وجود براکت باز “[” را در متن فرمول چک می‌کند که معمولاً نشان‌دهنده لینک به ورک‌بوک خارجی است.

قالب‌بندی شرطی با FORMULATEXT

می‌توانید قواعد قالب‌بندی شرطی را بر اساس محتوای فرمول‌ها بسازید. مثلاً هایلایت کردن سلول‌هایی که از INDIRECT یا OFFSET استفاده می‌کنند، تا در بازبینی‌ها سریع‌تر شناسایی شوند.

محدودیت‌ها و نسخه‌ها

• تابع FORMULATEXT از Excel 2013 و Excel برای Office 365 پشتیبانی می‌شود؛ در نسخه‌های قدیمی‌تر قابل دسترس نیست.
• اگر مرجع به یک ورک‌بوک خارجی باشد و آن ورک‌بوک بسته باشد، معمولاً خطای #N/A یا مشابه نمایش داده می‌شود.
• سلول‌هایی که حاوی خطا یا مقدار ثابت هستند، فرمولی برای نمایش ندارند و باید با IFERROR مدیریت شوند.

کاربردهای پیشرفته و نکات تخصصی

• مقایسه الگوریتم‌ها: با استخراج متن فرمول می‌توانید نسخه‌های مختلف یک محاسبه را مقایسه کرده و تغییرات انسانی یا خطاهای فرمول‌بندی را بیابید.
• شمارش وقوع توابع: با ترکیب FORMULATEXT و LEN/SUBSTITUTE می‌توانید تعداد استفاده از یک تابع خاص در یک سلول را محاسبه کنید.
• استفاده در گزارش تغییرات: قرار دادن FORMULATEXT در یک شیت مستندسازی باعث می‌شود تغییرات فرمول‌ها قابل پیگیری و بازبینی شوند.

نمونه: شمارش تعداد CALL به تابع VLOOKUP در یک سلول

=LEN(FORMULATEXT(A1))-LEN(SUBSTITUTE(UPPER(FORMULATEXT(A1)),"VLOOKUP",""))

این فرمول با مقایسه طول متن قبل و بعد از حذف عبارت VLOOKUP تعداد وقوع این تابع را محاسبه می‌کند. UPPER برای حساس نبودن به حروف کوچک و بزرگ استفاده شده است.

استخراج همه فرمول‌ها با VBA

اگر بخواهید تمام فرمول‌های یک شیت را به صورت متن در شیت دیگری استخراج کنید، می‌توانید از یک ماکرو ساده استفاده کنید:

Sub ExportFormulas()
  Dim ws As Worksheet, out As Worksheet
  Dim r As Range, cell As Range
  Set ws = ThisWorkbook.Sheets("Sheet1")   ' sheet to read
  Set out = ThisWorkbook.Sheets.Add(After:=ws)
  out.Name = "Formulas_Export"
  out.Range("A1:B1").Value = Array("Address","Formula")
  Dim rowIndex As Long: rowIndex = 2
  For Each r In ws.UsedRange
    For Each cell In r
      If cell.HasFormula Then
        out.Cells(rowIndex,1).Value = cell.Address(False,False)
        out.Cells(rowIndex,2).Value = cell.Formula
        rowIndex = rowIndex + 1
      End If
    Next cell
  Next r
End Sub

در این کد ماکرو، شیت “Sheet1” خوانده شده و برای هر سلول دارای فرمول، آدرس و متن فرمول در شیت جدیدی نوشته می‌شود. استفاده از property .HasFormula و .Formula برنامه را ساده و سریع می‌کند.

مثال عملی برای اشکال‌زدایی

فرض کنید خروجی عددی یک ستون با انتظارات تطابق ندارد. به‌جای باز کردن هر سلول، در ستون کناری از FORMULATEXT استفاده کنید تا فرمول‌ها را ببینید و با مقایسه سریع تشخیص دهید کدام ردیف‌ها فرمول متفاوت دارند:

=IFERROR(FORMULATEXT(C2),"Constant or Empty")

با کشیدن این فرمول برای کل ستون می‌توانید به سرعت الگوهای ناهماهنگ را شناسایی کنید (برای مثال فرمول‌های hardcoded یا اشاره به سلول اشتباه).

جمع‌بندی و توصیه‌های عملی

• برای محیط‌های پیچیده و مدل‌های مالی از FORMULATEXT به‌عنوان ابزار بازبینی و مستندسازی استفاده کنید.
• همیشه FORMULATEXT را با IFERROR ترکیب کنید تا گزارش‌ها خواناتر شوند.
• برای یافتن لینک‌های خارجی یا توابع پرخطر (INDIRECT، OFFSET، volatileها) از SEARCH روی خروجی FORMULATEXT بهره ببرید.
• در صورتی که نیاز به استخراج حجیم دارید، استفاده از VBA بسیار مؤثر و سریع‌تر است.

با ترکیب FORMULATEXT و ابزارهای ساده اکسل می‌توانید فرایند اشکال‌زدایی را سیستمی و کم‌خطا کنید و در زمان بازبینی مدل‌ها به‌طور چشمگیری صرفه‌جویی نمایید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال