تابع SCAN در اکسل — معرفی و کاربردهای پیشرفته

تابع SCAN یکی از توابع جدید اکسل است که در نسخه‌های مایکروسافت 365 با قابلیت‌های آرایه‌ای (dynamic arrays) قابل‌دسترس است. این تابع به شما اجازه می‌دهد روی یک آرایه پیمایش (scan) کنید و برای هر عنصر، یک مقدار تجمعی (accumulator) محاسبه شده را بازگردانید. به عبارت دیگر، SCAN یک «اسکن تجمعی» انجام می‌دهد و نتایج میانی را به صورت آرایه خروجی می‌دهد.

سینتکس تابع

تفاوت کلیدی با REDUCE

تابع REDUCE نیز از LAMBDA استفاده می‌کند اما تنها نتیجهٔ نهایی accumulator را بازمی‌گرداند. SCAN برعکس، همهٔ مقادیر میانی (از جمله مقدار اولیه) را به صورت آرایه خروجی می‌دهد که برای محاسبهٔ مجموع تجمعی، تجمعات شرطی یا نمایش تاریخچهٔ محاسبات مفید است.

مثال 1 — مجموع تجمعی (Running Total)

=SCAN(0, A2:A10, LAMBDA(acc, x, acc + x))

این فرمول مجموع تجمعی مقادیر محدودهٔ A2:A10 را محاسبه می‌کند. خروجی آرایه‌ای شامل مقدار اولیه 0 و پس از آن جمع تا هر عنصر خواهد بود. اگر نمی‌خواهید مقدار اولیه در خروجی دیده شود، می‌توانید از تابع DROP برای حذف اولین عنصر استفاده کنید:

=DROP(SCAN(0, A2:A10, LAMBDA(acc, x, acc + x)), 1)

در این مثال، DROP اولین مقدار (که همان 0 است) را حذف می‌کند تا فقط مجموع‌های تجمعی واقعی نمایش داده شوند.

مثال 2 — درصد تجمعی از کل

=LET(
  totals, SCAN(0, B2:B20, LAMBDA(acc,x, acc + x)),
  final, INDEX(totals, ROWS(totals)),
  percentages, IF(final=0, 0, DROP(totals,1)/final),
  percentages
)

این ترکیب از LET و SCAN ابتدا مجموع تجمعی مقادیر ستون B را تولید می‌کند، مقدار نهایی کل را استخراج می‌کند و سپس درصد تجمعی هر ردیف از کل را محاسبه می‌کند. در انتها مقدار اولیه حذف شده تا درصدها هم‌تراز با مقادیر اصلی باشند.

مثال 3 — بیشینه تجمعی (Cumulative Max)

=DROP(SCAN(-1E+99, C2:C15, LAMBDA(acc,x, MAX(acc, x))), 1)

اگر بخواهید بیشینهٔ تجمعی تا هر سطر را محاسبه کنید، می‌توانید از MAX درون LAMBDA استفاده کنید. مقدار اولیه را یک عدد بسیار کوچک قرار داده‌ایم تا اولین مقدار درست مقایسه شود. سپس مقدار اولیه را با DROP حذف می‌کنیم.

مثال 4 — ترکیب متن تجمعی (Cumulative Concatenation)

=DROP(SCAN("", D2:D8, LAMBDA(acc, x, IF(acc="", x, acc & " - " & x))), 1)

این مثال مقادیر متنی در ستون D را به‌صورت تجمعی به هم متصل می‌کند و بین مقادیر جداکننده « – » قرار می‌دهد. شرط IF برای جلوگیری از اضافه شدن جداکنندهٔ اضافی در ابتدا استفاده شده است.

مثال 5 — حسابداری مانده (Running Balance) با شرط

=DROP(
  SCAN(0, E2:E50, LAMBDA(acc, x,
    IF(FILTER(G2:G50, G2:G50"")="", acc + x, acc + x - INDEX(H2:H50, XMATCH(x, E2:E50)))
  )),
1)

این مثال پیچیده‌تر نشان می‌دهد چگونه می‌توان شرط‌ها و فیلترها را داخل LAMBDA به کار برد تا ماندهٔ حساب بر اساس ورودی‌ها و پردازش‌های جانبی محاسبه شود. البته در موارد عملی نیاز به تطبیق دقیق آدرس‌دهی و شرایط دارید؛ نمونه فقط جهت توضیح ساختار است.

نکات و بهترین روش‌ها

• SCAN در نسخه‌های جدید Microsoft 365 (و اکسل وب) قابل‌دسترس است؛ در نسخه‌های قدیمی‌تر اجرا نمی‌شود.
• خروجی SCAN طول آرایهٔ ورودی به‌اضافهٔ یک مقدار (مقدار اولیه) خواهد بود. برای هم‌ترازسازی با دیتای اصلی معمولاً از DROP برای حذف مقدار اولیه استفاده می‌شود.
• اگر به فقط نتیجهٔ نهایی نیاز دارید، از REDUCE استفاده کنید که کارایی بهتری دارد.
• تابع SCAN با LAMBDA ترکیب می‌شود؛ توانایی ساخت توابع سفارشی و چندخطی را افزایش می‌دهد. برای کدهای پیچیده بهتر است LAMBDA را به نام ثبت کنید (Name Manager) تا خوانایی و نگهداری بهبود یابد.
• در محدوده‌های بسیار بزرگ (چند ده هزار سطر) مراقب مصرف حافظه و زمان محاسبه باشید؛ LAMBDAهای پیچیده ممکن است کند شوند. در صورت نیاز از محاسبات میانی محدود یا جدول‌بندی (Excel Tables) و فیلترهای هوشمند استفاده کنید.

موارد استفاده عملی (Use Cases)

• گزارش‌های مالی: مانده‌های تجمعی، جریان نقدی انباشته، درصد تحقق از هدف.
• آنالیز داده: بیشینه/کمینه تجمعی، میانگین تجمعی (می‌توان با SUM/COUNT داخل LAMBDA ساخت).
• آماده‌سازی متن خروجی: ساخت گزارش‌های مرحله‌ای که در هر مرحله متن جدید اضافه می‌شود.
• مقایسه‌های شرطی: محاسبه تجمعی تنها برای ردیف‌هایی که شرط خاصی را پاس می‌کنند (با استفاده از IF یا FILTER داخل LAMBDA).

محدودیت‌ها و سازگاری

تابع SCAN برای محیط‌هایی که از توابع آرایه‌ای دینامیک پشتیبانی نمی‌کنند در دسترس نیست (مثل Excel 2019/2016 و نسخه‌های آفلاین قدیمی‌تر). برای اشتراک‌گذاری فایل با کاربرانی که نسخهٔ قدیمی دارند، یا باید توابع را به روش‌های سنتی محاسبه کنید یا از VBA/Power Query استفاده نمایید.

جمع‌بندی

SCAN یک ابزار بسیار قدرتمند برای محاسبات تجمعی و traceable در اکسل است که همراه با LAMBDA امکانات حرفه‌ای و منعطفی فراهم می‌آورد. با کمی تمرین می‌توان کاربردهای ساده تا پیشرفته‌ای مثل مجموع تجمعی، درصد تجمعی، بیشینه تجمعی و حتی منطق‌های شرطی پیچیده را به‌سادگی پیاده‌سازی کرد. همیشه دقت کنید که مصرف منابع و سازگاری نسخه‌ها را در نظر بگیرید و در صورت نیاز توابع را در Name Manager ثبت کنید تا خوانایی و بازاستفادهٔ کد افزایش یابد.

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

ارسال