آموزش تابع SCAN در اکسل

معرفی تابع SCAN

تابع SCAN یکی از توابع جدید و بسیار کاربردی اکسل است که برای انجام محاسبات تجمعی یا مرحله‌به‌مرحله روی یک آرایه یا محدوده استفاده می‌شود. این تابع روی هر مقدار از یک لیست حرکت می‌کند، نتیجه مرحله قبل را نگه می‌دارد و نتیجه جدید را برای هر ردیف برمی‌گرداند.

اگر بخواهیم ساده بگوییم، SCAN مثل این است که اکسل از بالا به پایین روی داده‌ها حرکت کند و در هر مرحله بپرسد: «نتیجه قبلی چه بود و مقدار جدید چیست؟ حالا نتیجه جدید چه می‌شود؟»

یکی از رایج‌ترین کاربردهای SCAN محاسبه جمع تجمعی یا مانده حساب است. مثلا اگر فروش روزانه در سلول‌های B2 تا B6 باشد، با تابع SCAN می‌توانیم جمع فروش تا هر روز را به‌صورت خودکار محاسبه کنیم.

=SCAN(0, B2:B6, LAMBDA(total, current, total+current))

در این فرمول، عدد 0 مقدار شروع است، محدوده B2:B6 داده‌هایی است که باید بررسی شوند، و LAMBDA مشخص می‌کند که در هر مرحله مقدار قبلی با مقدار فعلی جمع شود.

کاربردهای اصلی تابع SCAN

• محاسبه جمع تجمعی فروش، هزینه، امتیاز یا موجودی
• محاسبه مانده حساب پس از هر تراکنش
• ردیابی وضعیت مرحله‌به‌مرحله مثل قبولی، تأیید یا تکمیل کار
• محاسبه شمارش تجمعی یک شرط خاص، مثل تعداد فروش‌های بالای یک مقدار مشخص
• ساخت گزارش‌های پویا بدون نیاز به ستون‌های کمکی زیاد
• ترکیب با تابع LAMBDA برای ساخت منطق‌های سفارشی
• تحلیل داده‌های زمانی مانند فروش روزانه، مصرف ماهانه یا پیشرفت پروژه
• محاسبه موجودی انبار پس از ورود و خروج کالا

ساختار (Syntax)

ساختار انگلیسی تابع SCAN به شکل زیر است:

=SCAN([initial_value], array, LAMBDA(accumulator, value, body))

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

=SCAN(مقدار_اولیه; آرایه; LAMBDA(نتیجه_قبلی; مقدار_فعلی; عملیات))

نکته مهم این است که تابع SCAN همیشه همراه با LAMBDA استفاده می‌شود. LAMBDA منطق محاسبه را مشخص می‌کند؛ یعنی تعیین می‌کند که با نتیجه قبلی و مقدار جدید چه کاری انجام شود.

آرگومان‌ها

initial_value / مقدار اولیه

این آرگومان مقدار شروع محاسبه را مشخص می‌کند. مثلا برای جمع تجمعی معمولا مقدار اولیه را 0 قرار می‌دهیم. برای عملیات متنی ممکن است مقدار اولیه را متن خالی در نظر بگیریم و برای عملیات منطقی می‌توانیم از TRUE یا FALSE استفاده کنیم.

=SCAN(0, B2:B6, LAMBDA(total, current, total+current))

در این مثال مقدار اولیه برابر با 0 است.

array / آرایه یا محدوده داده‌ها

این آرگومان همان محدوده‌ای است که تابع SCAN باید روی آن حرکت کند. این محدوده می‌تواند یک ستون، یک ردیف یا یک آرایه محاسباتی باشد.

=SCAN(0, C2:C10, LAMBDA(acc, value, acc+value))

در این مثال، محدوده C2:C10 داده‌هایی هستند که مرحله‌به‌مرحله بررسی می‌شوند.

LAMBDA / تابع محاسباتی سفارشی

تابع LAMBDA مشخص می‌کند که در هر مرحله چه عملیاتی انجام شود. در SCAN، تابع LAMBDA معمولا دو پارامتر اصلی دارد: نتیجه قبلی و مقدار فعلی.

=SCAN(0, B2:B6, LAMBDA(accumulator, value, accumulator+value))

در این مثال، accumulator نتیجه قبلی و value مقدار فعلی است. خروجی هر مرحله دوباره به‌عنوان نتیجه قبلی مرحله بعد استفاده می‌شود.

accumulator / نتیجه قبلی

این پارامتر در داخل LAMBDA نشان‌دهنده نتیجه‌ای است که از مرحله قبل باقی مانده است. مثلا در جمع تجمعی، accumulator همان جمع تا ردیف قبلی است.

value / مقدار فعلی

این پارامتر مقدار فعلی از آرایه یا محدوده داده‌ها را نشان می‌دهد. تابع SCAN در هر مرحله یک مقدار از محدوده را وارد LAMBDA می‌کند.

body / عملیات یا منطق محاسبه

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

مثال‌های ساده و پایه

مثال 1: محاسبه جمع تجمعی فروش

فرض کنید مقدار فروش روزانه در سلول‌های B2 تا B6 وارد شده است. برای محاسبه جمع فروش تا هر روز، از فرمول زیر استفاده می‌کنیم:

=SCAN(0, B2:B6, LAMBDA(total, sale, total+sale))

اگر مقادیر فروش به‌ترتیب 100، 200، 150، 300 و 250 باشند، خروجی تابع به‌ترتیب 100، 300، 450، 750 و 1000 خواهد بود. یعنی هر ردیف جمع فروش از ابتدا تا همان ردیف را نشان می‌دهد.

مثال 2: محاسبه موجودی پس از هر تراکنش

فرض کنید موجودی اولیه انبار 500 عدد است و تغییرات ورود و خروج کالا در محدوده C2:C7 ثبت شده است. اعداد مثبت نشان‌دهنده ورود کالا و اعداد منفی نشان‌دهنده خروج کالا هستند.

=SCAN(500, C2:C7, LAMBDA(stock, change, stock+change))

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

مثال 3: محاسبه ضرب تجمعی

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

=SCAN(1, B2:B6, LAMBDA(result, number, result*number))

در این مثال مقدار اولیه 1 است، چون در ضرب، عدد 1 مقدار خنثی محسوب می‌شود.

مثال 4: ساخت متن تجمعی

فرض کنید نام مراحل یک پروژه در سلول‌های A2 تا A5 قرار دارد و می‌خواهید در هر ردیف مسیر طی‌شده تا آن مرحله را ببینید.

=SCAN("", A2:A5, LAMBDA(path, step, path&" - "&step))

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

=SCAN("", A2:A5, LAMBDA(path, step, IF(path="", step, path&" - "&step)))

مثال‌های کاربردی و واقعی

مثال 1: محاسبه مانده حساب بانکی

فرض کنید مانده اولیه حساب در سلول E1 برابر 10000000 است و تراکنش‌ها در محدوده B2:B10 قرار دارند. مبالغ واریزی مثبت و برداشت‌ها منفی ثبت شده‌اند.

=SCAN(E1, B2:B10, LAMBDA(balance, transaction, balance+transaction))

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

مثال 2: جمع تجمعی با تابع SUM

اگرچه برای جمع ساده می‌توان از علامت + استفاده کرد، اما برای خوانایی بیشتر یا هماهنگی با فرمول‌های دیگر می‌توانید از SUM هم داخل SCAN استفاده کنید.

=SCAN(0, B2:B10, LAMBDA(total, amount, SUM(total, amount)))

در این فرمول، در هر مرحله جمع قبلی و مقدار فعلی با تابع SUM محاسبه می‌شود.

مثال 3: بررسی قبولی تجمعی با AND

فرض کنید نمرات دانشجو در محدوده C2:C8 قرار دارد. می‌خواهیم بررسی کنیم تا هر درس، آیا همه نمرات قبلی و فعلی حداقل 10 بوده‌اند یا نه.

=SCAN(TRUE, C2:C8, LAMBDA(status, score, AND(status, score>=10)))

اگر حتی یک نمره کمتر از 10 باشد، از آن مرحله به بعد خروجی FALSE می‌شود. این مثال برای کنترل کیفیت، ارزیابی مرحله‌ای و بررسی شرایط پشت‌سرهم بسیار مفید است.

مثال 4: بررسی وقوع حداقل یک وضعیت با OR

فرض کنید وضعیت حضور کارکنان در محدوده D2:D12 قرار دارد و می‌خواهیم بدانیم تا هر ردیف آیا حداقل یک بار مقدار «غایب» دیده شده است یا نه.

=SCAN(FALSE, D2:D12, LAMBDA(found, status, OR(found, status="غایب")))

خروجی از اولین باری که کلمه «غایب» دیده شود، TRUE خواهد شد و تا انتها TRUE باقی می‌ماند.

مثال 5: شمارش تجمعی یک محصول با COUNTIF

فرض کنید نام محصولات فروخته‌شده در محدوده A2:A20 قرار دارد و می‌خواهید تعداد تجمعی فروش محصول «سیب» را تا هر ردیف محاسبه کنید.

=SCAN(0, ROW(A2:A20), LAMBDA(count, r, COUNTIF(A$2:INDEX(A:A, r), "سیب")))

این فرمول در هر مرحله محدوده‌ای از A2 تا ردیف فعلی را بررسی می‌کند و تعداد دفعات تکرار «سیب» را برمی‌گرداند.

مثال 6: محاسبه جمع تجمعی قیمت با XLOOKUP

فرض کنید کد کالاها در محدوده A2:A10 قرار دارد. جدول مرجع کد کالا در F2:F20 و قیمت هر کالا در G2:G20 است. می‌خواهیم مجموع قیمت کالاها را به‌صورت تجمعی محاسبه کنیم.

=SCAN(0, A2:A10, LAMBDA(total, code, total+XLOOKUP(code, F2:F20, G2:G20, 0)))

در این فرمول، XLOOKUP قیمت هر کد کالا را پیدا می‌کند و SCAN مجموع قیمت‌ها را مرحله‌به‌مرحله محاسبه می‌کند.

ترکیب تابع SCAN با فرمول‌های دیگر

تابع SCAN زمانی قدرتمندتر می‌شود که آن را با توابع دیگر اکسل ترکیب کنید. چند ترکیب رایج و کاربردی عبارت‌اند از:

• ترکیب SCAN با SUM برای جمع تجمعی خواناتر

=SCAN(0, B2:B10, LAMBDA(total, value, SUM(total, value)))

• ترکیب SCAN با IF برای محاسبه تجمعی شرطی

=SCAN(0, B2:B10, LAMBDA(total, value, IF(value>1000000, total+value, total)))

این فرمول فقط مقادیری را به جمع اضافه می‌کند که بزرگ‌تر از 1000000 باشند.

• ترکیب SCAN با AND برای بررسی ادامه‌دار بودن یک شرط

=SCAN(TRUE, C2:C10, LAMBDA(ok, value, AND(ok, value>=50)))

• ترکیب SCAN با OR برای بررسی رخ دادن یک اتفاق تا هر مرحله

=SCAN(FALSE, D2:D10, LAMBDA(found, value, OR(found, value="تکمیل")))

• ترکیب SCAN با COUNTIF برای شمارش تجمعی

=SCAN(0, ROW(A2:A20), LAMBDA(count, r, COUNTIF(A$2:INDEX(A:A, r), "تهران")))

• ترکیب SCAN با XLOOKUP برای محاسبه تجمعی بر اساس جدول مرجع

=SCAN(0, A2:A10, LAMBDA(total, code, total+XLOOKUP(code, F2:F20, G2:G20, 0)))

• ترکیب SCAN با TEXTJOIN برای ساخت خروجی متنی تجمعی

=SCAN("", A2:A6, LAMBDA(text, item, IF(text="", item, TEXTJOIN("، ", TRUE, text, item))))

خطاهای رایج و روش رفع آنها

1) خطای #VALUE! به دلیل تعریف اشتباه LAMBDA

اگر تعداد پارامترهای LAMBDA درست نباشد، اکسل ممکن است خطای #VALUE! نمایش دهد. در تابع SCAN، تابع LAMBDA معمولا باید دو پارامتر داشته باشد: یکی برای نتیجه قبلی و یکی برای مقدار فعلی.

روش رفع: مطمئن شوید LAMBDA دقیقا دو ورودی اصلی دارد و سپس عملیات محاسبه را بعد از آن نوشته‌اید.

=SCAN(0, B2:B6, LAMBDA(total, value, total+value))

2) خطای #CALC! به دلیل ناقص بودن LAMBDA

اگر تابع LAMBDA را ناقص بنویسید یا بخش عملیات را مشخص نکنید، احتمال مشاهده خطای #CALC! وجود دارد.

روش رفع: همیشه بعد از تعریف پارامترهای LAMBDA، منطق نهایی محاسبه را بنویسید.

=SCAN(0, B2:B6, LAMBDA(acc, x, acc+x))

3) خطای #SPILL! به دلیل نبود فضای کافی برای خروجی

تابع SCAN یک تابع آرایه پویا است و خروجی آن ممکن است در چند سلول نمایش داده شود. اگر سلول‌های پایین یا کنار فرمول پر باشند، خطای #SPILL! نمایش داده می‌شود.

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

4) نتیجه اشتباه به دلیل انتخاب مقدار اولیه نامناسب

مقدار اولیه در تابع SCAN بسیار مهم است. برای جمع معمولا 0، برای ضرب 1، برای منطق AND مقدار TRUE و برای منطق OR مقدار FALSE مناسب است.

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

=SCAN(1, B2:B6, LAMBDA(result, value, result*value))

5) خطا به دلیل استفاده از تابع SCAN در نسخه‌های قدیمی اکسل

تابع SCAN در نسخه‌های جدید اکسل ارائه شده و در نسخه‌های قدیمی مثل Excel 2016 یا Excel 2019 دائمی ممکن است در دسترس نباشد.

روش رفع: از Microsoft 365 یا نسخه‌های جدید اکسل که از توابع آرایه پویا و LAMBDA پشتیبانی می‌کنند استفاده کنید.

6) خطای منطقی به دلیل ترکیب متن و عدد

اگر محدوده شما شامل متن و عدد باشد و بخواهید عملیات عددی انجام دهید، نتیجه ممکن است خطا یا خروجی غیرمنتظره باشد.

روش رفع: قبل از محاسبه، داده‌ها را پاک‌سازی کنید یا با IF و ISNUMBER فقط اعداد را وارد محاسبه کنید.

=SCAN(0, B2:B10, LAMBDA(total, value, IF(ISNUMBER(value), total+value, total)))

نکات حرفه‌ای و ترفندهای مهم

• برای خوانایی بهتر، نام پارامترهای LAMBDA را معنی‌دار انتخاب کنید؛ مثلا total، amount، balance یا stock.
• همیشه مقدار اولیه را با دقت انتخاب کنید؛ چون روی کل خروجی اثر می‌گذارد.
• اگر هدف شما فقط نتیجه نهایی است، به‌جای SCAN از REDUCE استفاده کنید. SCAN همه مراحل را برمی‌گرداند، اما REDUCE فقط نتیجه نهایی را نمایش می‌دهد.
• برای گزارش‌های مالی، SCAN گزینه‌ای عالی برای محاسبه مانده حساب، موجودی انبار و جمع تجمعی است.
• اگر خروجی تابع در چند سلول پخش می‌شود، قبل از نوشتن فرمول مطمئن شوید فضای کافی برای خروجی وجود دارد.
• در فرمول‌های پیچیده، ابتدا منطق LAMBDA را با یک مثال ساده تست کنید و سپس آن را در SCAN قرار دهید.
• برای جلوگیری از خطا در داده‌های خالی یا متنی، از IF، ISNUMBER یا IFERROR در داخل LAMBDA استفاده کنید.
• در فایل‌های تیمی، کنار فرمول‌های SCAN توضیح کوتاه بنویسید؛ چون ممکن است برای کاربران مبتدی در نگاه اول پیچیده به نظر برسد.
• تابع SCAN با آرایه‌های پویا کار می‌کند؛ بنابراین بهتر است آن را در نسخه‌های جدید اکسل استفاده کنید.

تفاوت تابع SCAN با توابع مشابه

تفاوت SCAN با SUM

تابع SUM فقط مجموع نهایی را برمی‌گرداند، اما SCAN مجموع یا نتیجه مرحله‌به‌مرحله را نمایش می‌دهد.

=SUM(B2:B6)

=SCAN(0, B2:B6, LAMBDA(total, value, total+value))

فرمول اول فقط یک عدد نهایی می‌دهد، اما فرمول دوم برای هر ردیف یک نتیجه تجمعی تولید می‌کند.

تفاوت SCAN با REDUCE

تابع REDUCE مثل SCAN محاسبه تجمعی انجام می‌دهد، اما فقط نتیجه نهایی را برمی‌گرداند. در مقابل، SCAN همه نتایج میانی را هم نمایش می‌دهد.

=REDUCE(0, B2:B6, LAMBDA(total, value, total+value))

=SCAN(0, B2:B6, LAMBDA(total, value, total+value))

اگر فقط جمع نهایی را می‌خواهید REDUCE مناسب است، اما اگر جمع تا هر ردیف را می‌خواهید SCAN انتخاب بهتری است.

تفاوت SCAN با MAP

تابع MAP روی هر مقدار به‌صورت جداگانه عملیات انجام می‌دهد، اما نتیجه مرحله قبل را نگه نمی‌دارد. در حالی که SCAN در هر مرحله از نتیجه مرحله قبلی استفاده می‌کند.

=MAP(B2:B6, LAMBDA(value, value*10))

=SCAN(0, B2:B6, LAMBDA(total, value, total+value))

MAP برای تبدیل هر مقدار به‌صورت مستقل مناسب است، اما SCAN برای محاسبات وابسته به مراحل قبلی کاربرد دارد.

تفاوت SCAN با ستون کمکی

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

سازگاری با نسخه‌های مختلف اکسل

تابع SCAN از توابع جدید اکسل است و در نسخه‌هایی پشتیبانی می‌شود که از LAMBDA و آرایه‌های پویا پشتیبانی می‌کنند.

• Microsoft 365: پشتیبانی می‌شود.
• Excel for the web: در نسخه‌های جدید پشتیبانی می‌شود.
• Excel 2021: ممکن است بسته به به‌روزرسانی و کانال انتشار، همه توابع جدید در دسترس نباشند.
• Excel 2019 و نسخه‌های قدیمی‌تر: معمولا پشتیبانی نمی‌شود.
• Excel 2016 و قبل‌تر: پشتیبانی نمی‌شود.

اگر هنگام نوشتن SCAN اکسل تابع را نمی‌شناسد یا خطای #NAME? نمایش می‌دهد، احتمالا نسخه اکسل شما از این تابع پشتیبانی نمی‌کند یا نرم‌افزار نیاز به به‌روزرسانی دارد.

سؤالات پرتکرار درباره تابع SCAN

تابع SCAN چه کاری انجام می‌دهد؟

تابع SCAN محاسبات را مرحله‌به‌مرحله روی یک محدوده انجام می‌دهد و خروجی هر مرحله را نمایش می‌دهد. این تابع برای جمع تجمعی، مانده حساب، موجودی انبار و بررسی شرایط مرحله‌ای بسیار کاربردی است.

آیا SCAN بدون LAMBDA کار می‌کند؟

خیر. تابع SCAN برای تعریف منطق محاسبه به LAMBDA نیاز دارد.

تفاوت SCAN و REDUCE چیست؟

SCAN همه نتایج میانی را برمی‌گرداند، اما REDUCE فقط نتیجه نهایی را نمایش می‌دهد.

چرا خروجی SCAN در چند سلول نمایش داده می‌شود؟

چون SCAN یک تابع آرایه پویا است. تعداد خروجی‌ها معمولا متناسب با تعداد مقادیر موجود در آرایه ورودی است.

چرا خطای #SPILL! می‌گیرم؟

چون فضای کافی برای نمایش خروجی آرایه‌ای وجود ندارد. باید سلول‌های مسیر خروجی را خالی کنید.

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

بله. SCAN فقط برای عدد نیست و می‌توان با آن متن‌ها را نیز مرحله‌به‌مرحله ترکیب کرد.

=SCAN("", A2:A5, LAMBDA(text, item, IF(text="", item, text&" - "&item)))

آیا SCAN برای گزارش‌های مالی مناسب است؟

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

جمع‌بندی و پیشنهاد یادگیری بعدی

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

مهم‌ترین نکته در استفاده از SCAN این است که مفهوم LAMBDA، مقدار اولیه، نتیجه قبلی و مقدار فعلی را به‌خوبی درک کنید. بعد از یادگیری این مفاهیم، می‌توانید بسیاری از محاسباتی را که قبلا با ستون‌های کمکی انجام می‌دادید، با یک فرمول حرفه‌ای و تمیز انجام دهید.

برای ادامه یادگیری، پیشنهاد می‌شود بعد از SCAN سراغ توابع LAMBDA، REDUCE، MAP، BYROW و BYCOL بروید. این توابع در کنار هم ابزارهای بسیار قدرتمندی برای ساخت فرمول‌های پویا، حرفه‌ای و قابل توسعه در اکسل هستند.