اگر تاکنون در اکسل جمع انباشته ساخته باشید، احتمالاً فرمولی مانند {=SUM(A$۱:A2)} نوشته و آن را بارها در طول ستون به پایین کشیده‌اید. این روش به‌اندازه کافی ساده است؛ تا وقتی که مجموعه‌داده‌تان بزرگ شود یا پی‌درپی تغییر کند. از آن پس، دقیق نگه‌داشتن همه‌چیز می‌تواند دردسرساز شود. ساخت جدول در اکسل یا استفاده از محدوده‌های پویا کار را کمی آسان‌تر می‌کند، اما مشکل را به‌طور کامل از میان نمی‌برد. همچنان باید فرمول‌ها را کپی کنید، محدوده‌ها را گسترش دهید و امیدوار باشید چیزی به هم نریزد.
اینجاست که SCAN به کار می‌آید. آخرین باری را به یاد بیاورید که جمع انباشته ساختید. حال تصور کنید همان کار را با یک فرمول انجام دهید؛ فرمولی که با تغییر داده‌ها خودکار گسترش می‌یابد و به‌روز می‌شود و هرگز لازم نیست آن را در طول ستون کپی کنید. این توانایی SCAN است و تازه آغاز کارهایی است که این تابع می‌تواند انجام دهد.
تابع SCAN دقیقاً چه می‌کند؟
با تابعی آشنا شوید که مانند شما می‌اندیشد
ساده‌ترین برداشت از SCAN این است که محاسبه‌ای سفارشی را روی تک‌تک عناصر یک محدوده یا آرایه انجام می‌دهد و همه نتیجه‌های میانی را در طول فرایند برمی‌گرداند. اگر این توضیح انتزاعی به نظر می‌رسد، نگران نباشید؛ با دیدن کاربرد عملی آن، موضوع کاملاً روشن خواهد شد.
فرض کنید آمار فروش ماهانه در سلول‌های A2:A8 قرار دارد و می‌خواهید جمع انباشته را محاسبه کنید. در روش سنتی، باید فرمول زیر را در B2 وارد و سپس آن را تا B8 کپی کنید:
=SUM(A$2:A2)یعنی هفت سلول و هفت فرمول. اگر کسی بعداً سطری اضافه کند یا داده‌هایتان به‌روز شوند، ناچارید محدوده‌ها را دوباره تنظیم یا نتیجه کارتان را بازبینی کنید.
با SCAN می‌توانید همه این کارها را با تنها یک فرمول در B2 جایگزین کنید:
=SCAN(0, A2:A8, LAMBDA(a, v, a + v))با فشردن کلید Enter، اکسل تمام مقادیر جمع انباشته را به‌طور خودکار در طول ستون سرریز می‌کند. یک فرمول دارید، اما هفت نتیجه می‌گیرید.
ساختار پایه تابع SCAN چنین است:
فرمول تابع به این صورت است: =SCAN([initial_value], array, LAMBDA(accumulator, value, body))
در این فرمول، Initial_value نقطه آغاز محاسبه است. برای جمع انباشته، این مقدار معمولاً ۰ است، اما می‌تواند هر عدد یا حتی یک متن باشد.
اگر مقدار آغازین شما متنی است، آن را میان علامت‌های نقل‌قول دوتایی (“”) قرار دهید.
Array محدوده داده‌هایی است که می‌خواهید پردازش شوند. در این نمونه، A2:A8 همان داده‌های فروش ماهانه شماست. بااین‌حال، به‌جای ارجاع به سلول‌ها می‌توانید به جدولی مانند Table1 ارجاع دهید.
در پایان، LAMBDA با پارامترهای accumulator، value و body منطق محاسبه را تعریف می‌کند. به زبان ساده، SCAN محدوده را موردبه‌مورد پیمایش می‌کند، محاسبه را انجام می‌دهد، نتیجه را به خاطر می‌سپارد و در گام بعدی به کار می‌گیرد. انباشتگر (a) نتیجه جاری را نگه می‌دارد، مقدار (v) همان مورد کنونی است و بدنه، یعنی ترکیب a و v، تعیین می‌کند در هر گام چه اتفاقی بیفتد.
در نمونه بالا، بدنه فرمول a + v است که جمع انباشته می‌سازد؛ اما می‌توانید از هر عمل دلخواه دیگری نیز استفاده کنید:
- ضرب (a * v)
- تفریق (a – v)
- به‌هم‌پیوستن متن‌ها (a & ", " & v)
- منطق شرطی (IF(v > ۱۰۰, a + ۱, a))
هرچند SCAN در همه موقعیت‌ها جایگزین توابعی مانند SUM یا SUMIFS نمی‌شود، هنگامی که منطق محاسبه باید در طول یک محدوده به‌تدریج شکل بگیرد، ابزاری بسیار توانمند است.
تابع SCAN در اکسلِ Microsoft ۳۶۵ در دسترس است. اگر از نسخه‌ای قدیمی‌تر از Excel ۲۰۲۴ استفاده می‌کنید، به این تابع دسترسی نخواهید داشت.
SCAN چگونه شیوه فرمول‌نویسی را دگرگون می‌کند؟
از تکرار تا پیشروی گام‌به‌گام
همان‌طور که دیدید، نمونه جمع انباشته تنها گوشه‌ای از توانایی‌های SCAN را نشان می‌دهد. در عمل، SCAN رویکرد شما به ساخت فرمول‌ها را از سه جنبه اساسی دگرگون می‌کند:
نخست، به‌جای آنکه فرمول‌ها را در طول یک ستون کپی کنید، تنها یک فرمول پویا می‌نویسید. از آنجا که SCAN آرایه‌ای پویا برمی‌گرداند، نتایج به‌طور خودکار متناسب با داده‌ها گسترش می‌یابند؛ بنابراین با بزرگ‌تر شدن مجموعه‌داده، دیگر نیازی به کشیدن فرمول‌ها یا تنظیم دوبارهٔ محدوده‌ها نیست. دوم، محاسبات سادهٔ شما را به منطقی چندمرحله‌ای تبدیل می‌کند. SCAN از پس محاسبات پیش‌رونده‌ای برمی‌آید که در آن‌ها هر نتیجه به نتیجهٔ پیشین وابسته است؛ در نتیجه می‌توانید با تغییر داده‌ها، تک‌تک مقادیر میانی را دنبال کنید. سرانجام، SCAN از همان ابتدا با کل آرایه کار می‌کند؛ یعنی هرگاه داده‌ای را بیفزایید یا حذف کنید، فرمول بی‌درنگ خود را با این تغییرات سازگار می‌کند.
به‌محض آنکه استفاده از SCAN را در مجموعه‌داده‌های واقعی آغاز کنید، این تفاوت‌ها را خواهید دید. برای نمونه، جدولی را در نظر بگیرید که امتیاز و وضعیت روزانهٔ یک پروژه را ثبت می‌کند. در چنین موقعیتی، می‌توان از SCAN به سه شیوهٔ کاربردی بهره گرفت:
بیشینهٔ تجمعی
این یکی از ساده‌ترین روش‌ها برای پیگیری یک وضعیت پیش‌رونده است. هدف این است که بیشترین امتیاز به‌دست‌آمده تا هر تاریخ نمایش داده شود.
=SCAN(0, B2:B9, LAMBDA(a, v, MAX(a, v)))منطق کار روشن است: SCAN در هر مرحله امتیاز کنونی (v) را با بیشینهٔ تجمعی (a) مقایسه می‌کند. اگر امتیاز تازه بیشتر باشد، جای بیشینهٔ قبلی را می‌گیرد. انباشتگر (a) نیز همواره بزرگ‌ترین امتیازی را که تا آن لحظه دیده شده است، نگه می‌دارد.
شمارش تجمعی روزهای فعال
به‌جای جمع‌زدن امتیازها، می‌توانید بشمارید که یک شرط مشخص چند بار برقرار شده است؛ در اینجا، اینکه وضعیت پروژه چند بار روی «فعال» قرار گرفته است.
=SCAN(0, C2:C9, LAMBDA(a, v, IF(v="Active", a + 1, a)))در اینجا SCAN وضعیت کنونی (v) را بررسی می‌کند. اگر وضعیت برابر با «فعال» باشد، عدد ۱ را به انباشتگر (a) می‌افزاید؛ در غیر این صورت، شمارش را بدون تغییر نگه می‌دارد. حاصل کار، شمارش تجمعی روزهای فعال است.
حاصل‌ضرب تجمعی شرطی
این نمونه، ضرب را با منطق شرطی درهم می‌آمیزد. فرض کنید می‌خواهید حاصل‌ضرب تجمعی امتیازهای روزانه را محاسبه کنید، اما فقط برای روزهایی که وضعیت «فعال» است. هرگاه وضعیت به «غیرفعال» تغییر کند، حاصل‌ضرب باید به مقدار پیش‌فرض ۱ بازگردد.
از آنجا که ضرب به مقدار آغازین ۱ نیاز دارد، فرمول از همین عدد شروع می‌شود:
=SCAN(1, C2:C9, LAMBDA(a, v, IF(v="Active", a * INDEX(B:B, ROW(v)), 1)))ابتدا SCAN وضعیت کنونی (v) را از ستون C بررسی می‌کند. اگر وضعیت «فعال» باشد، حاصل‌ضرب قبلی (a) را در امتیاز متناظرِ ستون B ضرب می‌کند؛ امتیازی که با تابع INDEX فراخوانی می‌شود. اما اگر وضعیت «غیرفعال» باشد، حاصل‌ضرب را به ۱ بازمی‌گرداند و انباشت را تا روز فعال بعدی متوقف می‌کند.
این‌گونه فرمول‌ها در سناریوهای تجاری بسیار سودمندند. برای نمونه، تیم فروشی را تصور کنید که تا وقتی یک حساب با ضوابط سازگار می‌ماند، ضریب پورسانتش هر روز افزایش می‌یابد. اگر حساب بررسی روزانه را با موفقیت پشت سر بگذارد («فعال» باشد)، مبلغ فروش آن در حاصل‌ضرب جاری منظور می‌شود. اگر در بررسی رد شود («غیرفعال» باشد)، ضریب به ۱ بازمی‌گردد. به این ترتیب، مسئولان حساب‌ها انگیزه پیدا می‌کنند سازگاری با ضوابط را حفظ کنند تا رشتهٔ پاداش‌هایشان از نو آغاز نشود.
کار را به SCAN بسپارید
تا اینجا دیده‌اید که SCAN تا چه اندازه توانمند است و چگونه فرمول‌های سنتی را به منطقی مقیاس‌پذیر و سرریزپذیر تبدیل می‌کند که همراه با داده‌های شما گسترش می‌یابد. هر زمان که به‌جای صرفاً نتیجهٔ نهایی، می‌خواهید روند پیشرفت یک محاسبه را ببینید، از آن بهره بگیرید. این کاربردها پیش‌بینی‌های مالی‌ای را در بر می‌گیرند که هر دوره در آن‌ها بر دورهٔ پیشین بنا می‌شود، رهگیری موجودی که سطح کالا را با هر فروش یا تحویل به‌روز می‌کند و نمودارهای کنترل کیفیتی که عیب‌های انباشته را زیر نظر می‌گیرند.
ساده‌ترین راه برای تشخیص اینکه SCAN ابزار مناسبی است، توجه به این نیاز است: «در این محاسبه باید از نتیجهٔ ردیف قبلی استفاده کنم.» هرگاه چنین نیازی داشتید، دقیقاً در قلمرو SCAN هستید و Excel باقی کار را انجام می‌دهد.
منبع: این مطلب ترجمه و بومی‌سازی مقاله‌ای از MakeUseOf به قلم Adaeze Uche است. مشاهده مقاله اصلی