REDUCE در اکسل هوشمند است، اما این تابع سنتی همان چیزی است که به آن اعتماد دارم.

اکسل به‌طور مداوم توابع پیشرفته‌ای برای دستکاری آرایه‌های پیشرفته عرضه می‌کند و من واقعاً از این موضوع قدردانم. در نهایت، چه‌کس نمی‌خواهد روش بهتری برای کار در اکسل داشته باشد؟ تابع REDUCE، که یکی از گروه جدید توابع اکسل است، مثال کاملی است. توانایی آن در ادغام یک بازه از مقادیر به یک نتیجه تجمعی واحد با استفاده از توابع سفارشی LAMBDA تقریباً حس علمی‑تخیلی دارد. با این حال، هوشمندانه بودن، هرچند هیجان‌انگیز است، همیشه آنچه من در یک صفحه‌گسترده نیاز دارم نیست.

Excel به‌صورت مستمر توابع پیشرفته‌ای برای دست‌کاری آرایه‌های پیشرفته ارائه می‌دهد و من واقعاً از این موضوع قدردانی می‌کنم. در نهایت، چه‌کس نمی‌خواهد روش بهتری برای کار در Excel داشته باشد؟ تابع REDUCE، یکی از **گروه جدید توابع Excel**، نمونه‌ای کامل است. توانایی آن در جمع‌کردن یک بازه از مقادیر به‌صورت یک نتیجه تجمعی واحد با استفاده از توابع سفارشی LAMBDA تقریباً شبیه علمی‑تخیلی به نظر می‌رسد. اما هوشمندانه بودن، هرچند هیجان‌انگیز است، همیشه آن‌چه من در یک صفحه‌گسترده نیاز دارم، نیست.

به همین دلیل من اغلب به سمت SUMPRODUCT بازمی‌گردم. این تابع قدیمی‌تر، ساده‌تر است و به‌طور مستمر ثابت کرده که انتخاب بهتری در بیشتر کارهایی است که من می‌خواهم به جای REDUCE از آن استفاده کنم. و اگر کار روزانه شما چیزی شبیه به کار من باشد، احتمالاً با من توافق خواهید کرد.

چرا SUMPRODUCT نسبت به REDUCE

این تابع منطق پیچیده را در یک گام بدون نیاز به LAMBDA سفارشی انجام می‌دهد

استفاده از REDUCE مستلزم تعریف یک LAMBDA با سه پارامتر الزامی است: accumulator، value و body. می‌توانید حتی یک initial_value برای تعیین نقطه شروع accumulator فراهم کنید. تمام این ساختار، انعطاف‌پذیری عظیمی به REDUCE می‌دهد. به‌عنوان مثال می‌توانید از آن برای محاسبه هزینه کل (قیمت هزینه در‌ضرب تعداد فروخته شده)، جمع مقادیر مربعی (a+b^2)، ایجاد توابع سفارشی (مانند جمع فقط مقادیری که بیش از ۵۰ هستند) یا حتی شمارش فقط مقادیر زوج در یک آرایه استفاده کنید. بی‌تردید این تابع قدرتمند است.

=REDUCE(0, SEQUENCE(ROWS(G2:G20)), LAMBDA(total, row_num, total + INDEX(G2:G20, row_num) * INDEX(H2:H20, row_num)))

با این وجود، این پیشرفته‌گی به این معنی است که اگر یک LAMBDA نامعتبر یا تعداد پارامترهای اشتباه ارائه دهید، با خطای #VALUE! مواجه می‌شوید، یکی از **خطاهای سرسخت Excel**. دیباگ کردن آن همیشه ساده نیست، به‌ویژه چون LAMBDAها به‌دقت توابع سرراست نیستند. در عین حال، نوشتن و رفع اشکال در SUMPRODUCT بسیار آسان‌تر است.

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

=SUMPRODUCT(G2:G20, H2:H20)

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

همچنین مسئلهٔ در دسترس بودن وجود دارد. REDUCE فقط در Excel برای Microsoft 365، Excel برای Microsoft 365 برای Mac و Excel وب کار می‌کند. هر کس که هنوز از Excel 2016، 2019، 2021 یا حتی 2024 استفاده می‌کند، به این تابع دسترسی ندارد که همکاری را دشوارتر می‌کند.

به همین دلیل، با وجود هوشمندی REDUCE، من همچنان به SUMPRODUCT روی می‌آورم. این تابع به‌طور یکنواخت در دامنهٔ وسیع‌تری از نسخه‌های Excel کار می‌کند و مهم‌تر از آن، از بار ساختاری که REDUCE می‌طلبد، دور می‌ماند. بدون LAMBDA و پارامترهای پیچیده، و با فرصت‌های کمتری برای بروز خطا.

چگونه من به جای REDUCE از SUMPRODUCT استفاده می‌کنم

محاسباتی که در آن‌ها SUMPRODUCT ساده‌تر، شفاف‌تر و کمتر قابل شکسته شدن است

طبیعتاً، SUMPRODUCT نمی‌تواند همهٔ کارهایی را که REDUCE می‌تواند انجام دهد، انجام دهد. این تابع به محاسبات عددی آرایه‌ای که با جمع پایان می‌یابد محدود است. با این حال، تمام شمارش‌ها، جمع‌های شرطی ساده، جمع‌های چند شرطی و میانگین‌های وزن‌دار که REDUCE می‌تواند مدیریت کند، در مواردی که به منطق ترتیبی نیاز ندارند، پوشش می‌دهد.

SUMPRODUCT در یک عملیات یکپارچه کار می‌کند (مثلاً ضرب یک آرایه در آرایهٔ دیگر و جمع نتایج) در حالی که **REDUCE هر گام را به‌صورت ترتیبی پردازش می‌کند**. این رویکرد گام‌به‌گام برای محاسباتی مانند ترازهای جاری یا مجموع‌های انبوه ضروری است، جایی که مقدار گام B به آنچه در گام A رخ داده وابسته است. بنابراین، اگر مجموع شما به این منطق نیاز داشته باشد، SUMPRODUCT کار نخواهد کرد. در غیر این صورت، می‌توانید به سادگی استفاده کنید.

به‌صورت پیش‌فرض، SUMPRODUCT مجموع حاصل‌ضرب محدوده‌های متناظر را برمی‌گرداند، بنابراین عمل اصلی آن ضرب است، با این نحو:

=SUMPRODUCT(array1, [array2], ...)

اگرچه به ضرب محدود نیست. با جایگزینی کاماها با عملگرهای ریاضی مورد نیاز (*، /، +، -) می‌توانید جمع، تفریق یا تقسیم را روی آرایه‌ها انجام دهید. فرض کنید می‌خواهید کل مبلغی که برای ماه بیش از بودجه یا زیر بودجه است را برای چندین دسته هزینه محاسبه کنید. فرمول من به این شکل خواهد بود:

=SUMPRODUCT((H2:H10 - I2:I10))

SUMPRODUCT مقدار واقعی را از مقدار برنامه‌ریزی شده برای هر مورد (B2‑C2، B3‑C3 و غیره) کم می‌کند و سپس این اختلاف‌ها را جمع می‌زند. اگر نتیجه مثبت باشد، به این معنی است که به‌طور کلی زیر بودجه هستید؛ اگر نه، می‌دانید که باید تنظیماتی انجام دهید.

من همچنین می‌توانم یک قدم جلوتر رفته و کل مبلغی را که بیش از بودجه صرف شده است محاسبه کنم:

=SUMPRODUCT(--((H2:H10 - I2:I10)

این فرمول مقدار مطلق اختلاف منفی بین دو ستون را جمع می‌کند (200 از H3‑I3، به‌علاوه 300، 20 و 50 از ردیف‌های دیگر) و نشان می‌دهد که مبلغی که در تمام دسته‌های بیش‌بودجه‌دار صرف شده است، 570 دلار است.

من همچنین از SUMPRODUCT برای جمع مقادیر مبتنی بر معیارهای متعدد استفاده می‌کنم. برای مثال، برای محاسبهٔ کل درآمد برای محصولاتی که چند شرط را برآورده می‌کنند (مانند اندازهٔ متوسط، غیر از موارد کاغذی) یا برای شمارش تعداد کاغذهای دست‌مالی که منطقهٔ شرق فروشنده است.

=SUMPRODUCT((A2:A10="Tissue Paper")*(C2:C10="Medium")*D2:D10) 
=SUMPRODUCT((A2:A10="Tissue Paper")*(B2:B10="East"))

به‌جای نوشتن **دستورات IF تو嵐嵥嵿** پیچیده، SUMPRODUCT به من اجازه می‌دهد شرایط را به‌صورت مستقیم در فرمول لایه‌بندی کنم.

قوانین اصلی این است که تمام آرایه‌ها باید ابعاد یکسانی داشته باشند. اگر این شرط برآورده نشود، SUMPRODUCT نیز خطای #VALUE! را برمی‌گرداند. خوشبختانه این نیازپذیری قابل پیش‌بینی و آسان برای بررسی است—بسیار ساده‌تر از دیباگ کردن یک تابع مینیاتوری داخل تابع دیگر. برای اکثر کارهای صفحه‌گسترده که با جمع پایان می‌یابند، به‌ویژه آن‌هایی که می‌خواهم با دیگران به اشتراک بگذارم، ساختار واضح SUMPRODUCT مهم‌تر از ظرافت REDUCE است.

SUM ساده است. این قدرتمند است.

برخی توابع قدیمی‌تر همچنان با کمترین تلاش مشکل را حل می‌کنند

REDUCE برای آنچه می‌تواند انجام دهد شایستهٔ تقدیر است، به‌ویژه اگر به‌صورت انحصاری در جدیدترین نسخهٔ Excel کار می‌کنید و به منطق آرایه‌ای بسیار سفارشی نیاز دارید. با این حال، برای اکثر کارهای روزمره و در بسیاری از نسخه‌های Excel، SUMPRODUCT همچنان نتایج پایدار و قابل پیش‌بینی را فراهم می‌کند که من به آن نیاز دارم. و همین قابلیت اطمینان است که باعث می‌شود همچنان به این تابع اعتماد داشته باشم.

منبع مقاله