آموزش جامع فرمول REDUCE در گوگل شیت

• فرمول REDUCE در گوگل شیت چیست و چه تفاوتی با سایر توابع آرایه‌ای دارد؟
• چگونه می‌توان از فرمول REDUCE برای محاسبات تجمعی و پیچیده روی مجموعه‌ای از داده‌ها استفاده کرد؟
• آیا می‌توان بدون استفاده از اسکریپت‌های پیچیده، چندین عملیات را روی یک آرایه با فرمول REDUCE انجام داد؟
• چه مثال‌های کاربردی و پیشرفته‌ای برای استفاده از تابع REDUCE در تحلیل داده‌های روزمره وجود دارد؟

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

فرمول REDUCE در گوگل شیت چیست؟

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

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

ساختار و آرگومان‌های تابع REDUCE

ساختار کلی این فرمول به شکل زیر است:

REDUCE(initial_value, array_or_range, lambda)

بیایید هر یک از این آرگومان‌ها را با دقت بررسی کنیم:

• initial_value: این آرگومان، مقدار اولیه‌ای است که محاسبات ما با آن شروع می‌شود. به این مقدار «انباشتگر» یا (accumulator) نیز می‌گویند. این مقدار می‌تواند صفر، یک رشته خالی (“”) یا هر مقدار دلخواه دیگری باشد که به عنوان نقطه شروع منطقی برای محاسبه شما عمل می‌کند.
• array_or_range: این محدوده یا آرایه‌ای است که می‌خواهید عملیات را روی آن انجام دهید. برای مثال، محدوده A1:A10 یا یک آرایه ساخته شده توسط فرمول دیگر.
• lambda: این قلب تپنده فرمول REDUCE است. لمبدا یک تابع سفارشی است که شما تعریف می‌کنید و دو پارامتر ورودی دارد:
  • accumulator: این پارامتر، مقدار انباشته شده از مراحل قبلی را در خود نگه می‌دارد. در اولین مرحله، مقدار آن برابر با initial_value است.
  • current_value: این پارامتر، مقدار سلول فعلی از array_or_range است که در حال پردازش می‌باشد.

در هر مرحله، تابع لمبدا مقادیر accumulator و current_value را گرفته، محاسبه‌ای را روی آنها انجام می‌دهد و نتیجه را به عنوان accumulator جدید برای مرحله بعدی برمی‌گرداند. این فرآیند تا آخرین سلول محدوده ادامه پیدا می‌کند و در نهایت، آخرین مقدار accumulator به عنوان خروجی نهایی فرمول REDUCE نمایش داده می‌شود.

مثال‌های کاربردی برای درک بهتر فرمول REDUCE

تئوری کافیست! بیایید با چند مثال عملی ببینیم این فرمول چگونه کار می‌کند. فرض کنید اعداد ۱ تا ۵ در محدوده A1:A5 قرار دارند.

مثال ۱: محاسبه مجموع اعداد (شبیه‌سازی تابع SUM)

ساده‌ترین مثال، محاسبه مجموع اعداد است. ما می‌خواهیم کاری را انجام دهیم که تابع SUM به راحتی انجام می‌دهد تا منطق REDUCE را درک کنیم.

فرمول به این شکل خواهد بود:

=REDUCE(0, A1:A5, LAMBDA(acc, curr, acc + curr))

بیایید مراحل اجرای این فرمول را ردیابی کنیم:

1. مقدار اولیه (initial_value): انباشتگر (acc) با مقدار 0 شروع می‌شود.
2. مرحله اول: فرمول اولین مقدار از محدوده (سلول A1 که حاوی 1 است) را برمی‌دارد. محاسبه لمبدا: acc + curr یا 0 + 1 = 1. مقدار جدید acc برابر 1 می‌شود.
3. مرحله دوم: مقدار بعدی (A2 که حاوی 2 است) برداشته می‌شود. محاسبه لمبدا: 1 + 2 = 3. مقدار جدید acc برابر 3 می‌شود.
4. مرحله سوم: مقدار بعدی (A3 که حاوی 3 است) برداشته می‌شود. محاسبه لمبدا: 3 + 3 = 6. مقدار جدید acc برابر 6 می‌شود.
5. مرحله چهارم: مقدار بعدی (A4 که حاوی 4 است) برداشته می‌شود. محاسبه لمبدا: 6 + 4 = 10. مقدار جدید acc برابر 10 می‌شود.
6. مرحله پنجم: مقدار نهایی (A5 که حاوی 5 است) برداشته می‌شود. محاسبه لمبدا: 10 + 5 = 15. مقدار جدید acc برابر 15 می‌شود.

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

مثال ۲: اتصال رشته‌های متنی (شبیه‌سازی تابع TEXTJOIN)

فرض کنید در محدوده B1:B3 به ترتیب رشته‌های “گوگل”، “شیت” و “عالی” را داریم و می‌خواهیم آنها را با یک فاصله به هم بچسبانیم.

فرمول به این شکل خواهد بود:

=REDUCE("", B1:B3, LAMBDA(acc, curr, IF(acc="", curr, acc & " " & curr)))

در این فرمول:

• مقدار اولیه یک رشته خالی (“”) است.
• در هر مرحله، لمبدا چک می‌کند که آیا انباشتگر (acc) خالی است یا نه. اگر خالی بود (یعنی در اولین مرحله)، فقط مقدار فعلی (curr) را برمی‌گرداند. در غیر این صورت، مقدار انباشته شده قبلی را با یک فاصله و سپس مقدار فعلی ترکیب می‌کند.
• خروجی نهایی این فرمول رشته “گوگل شیت عالی” خواهد بود.

کاربردهای پیشرفته فرمول REDUCE در گوگل شیت

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

پردازش داده‌های دو بعدی (چند ستونی)

یکی از قابلیت‌های شگفت‌انگیز REDUCE، توانایی کار با آرایه‌های دو بعدی است. فرض کنید جدولی از محصولات، تعداد فروش و قیمت هر واحد را در محدوده A2:C5 دارید.

حالا می‌خواهیم مجموع درآمد حاصل از فروش “موس” را محاسبه کنیم. با استفاده از SUMIF یا SUMIFS می‌توان این کار را انجام داد، اما بیایید ببینیم REDUCE چگونه این مسئله را حل می‌کند.

برای این کار، ما به تابعی نیاز داریم که بتواند به هر سطر دسترسی داشته باشد. می‌توانیم از تابع BYROW در ترکیب با REDUCE استفاده کنیم، اما یک راه هوشمندانه‌تر استفاده از خود لمبدا برای پردازش ستون‌هاست. متاسفانه، REDUCE به تنهایی برای پردازش سطر به سطر طراحی نشده و معمولاً روی یک آرایه تک بعدی کار می‌کند. برای پردازش آرایه‌های دو بعدی، ترکیب آن با توابع دیگر مانند MAP یا BYROW ضروری است.

اما یک سناریوی دیگر را در نظر بگیرید. فرض کنید می‌خواهیم یک آرایه خروجی تولید کنیم که شامل درآمد کل هر محصول باشد. در این سناریو، تابع MAP یا BYROW انتخاب بهتری است. REDUCE زمانی می‌درخشد که هدف، کاهش کل آرایه به یک مقدار واحد باشد.

برای مثال، بیایید مجموع کل درآمد از تمام محصولات را محاسبه کنیم. اما این بار با یک شرط: فقط محصولاتی که تعداد فروش آنها بیشتر از ۳ عدد است را در نظر بگیر.

برای حل این مسئله، ما به سه محدوده نیاز داریم: نام محصول (A2:A5)، تعداد (B2:B5) و قیمت (C2:C5). این کار با REDUCE کمی پیچیده می‌شود. راه حل بهتر، استفاده از تابع FILTER برای جداسازی داده‌ها و سپس استفاده از SUMPRODUCT یا MAP است.

پس چه زمانی REDUCE بهترین گزینه است؟ زمانی که نتیجه هر مرحله به نتیجه مرحله قبل وابسته است. این ویژگی کلیدی REDUCE است.

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

تصور کنید لیستی از تراکنش‌های انبار (ورود و خروج کالا) در یک شیت دارید. ستون A تاریخ تراکنش و ستون B مقدار تغییر (مثبت برای ورود و منفی برای خروج) را نشان می‌دهد. موجودی اولیه انبار 100 واحد بوده است.

ما می‌خواهیم با یک فرمول، موجودی نهایی را محاسبه کنیم.

محدوده B2:B10 شامل مقادیر زیر است: +50, -20, +30, -15, -10, +40, -25.

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

=REDUCE(100, B2:B10, LAMBDA(acc, curr, acc + curr))

• initial_value: موجودی اولیه ما 100 است.
• array_or_range: محدوده تغییرات موجودی B2:B10 است.
• lambda: در هر مرحله، مقدار تغییر فعلی (curr) به موجودی انباشته شده (acc) اضافه می‌شود.

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

مقایسه REDUCE با توابع MAP و SCAN

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

REDUCE vs. MAP

• هدف: تابع MAP یک آرایه ورودی می‌گیرد و یک عملیات را روی تک تک اعضای آن انجام می‌دهد و یک آرایه خروجی هم‌اندازه با آرایه ورودی برمی‌گرداند. در مقابل، REDUCE کل آرایه را به یک مقدار واحد کاهش می‌دهد.
• خروجی: خروجی MAP یک آرایه است. خروجی REDUCE یک مقدار منفرد است.
• مثال: اگر بخواهید تمام اعداد یک ستون را دو برابر کنید، از MAP استفاده می‌کنید. اگر بخواهید مجموع تمام اعداد آن ستون را به دست آورید، از REDUCE (یا SUM) استفاده می‌کنید.

REDUCE vs. SCAN

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

فرمول SCAN برای مثال موجودی انبار:

=SCAN(100, B2:B10, LAMBDA(acc, curr, acc + curr))

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

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

1. استفاده از نام‌های معنی‌دار در لمبدا: به جای استفاده از `acc` و `curr`، می‌توانید از نام‌های واضح‌تری مانند `موجودی_انباشته` و `تغییر_فعلی` استفاده کنید تا فرمول شما خواناتر شود.

   =REDUCE(100, B2:B10, LAMBDA(موجودی_انباشته, تغییر_فعلی, موجودی_انباشته + تغییر_فعلی))

2. ترکیب با سایر توابع: فرمول REDUCE در گوگل شیت را می‌توان با توابعی مانند FILTER, UNIQUE, SORT و… ترکیب کرد تا ابتدا داده‌ها را آماده‌سازی کرده و سپس عملیات کاهش را روی داده‌های فیلتر شده یا مرتب شده انجام دهید.
3. مقدار اولیه پویا: مقدار اولیه (initial_value) می‌تواند یک مقدار ثابت نباشد، بلکه خودش نتیجه یک فرمول دیگر باشد. این به شما انعطاف‌پذیری بالایی می‌دهد.

جمع‌بندی

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

هرچند برای محاسبات ساده مانند جمع یا میانگین، توابع سنتی همچنان گزینه‌های بهتری هستند، اما هر زمان که با مسئله‌ای مواجه شدید که در آن “نتیجه فعلی به نتیجه قبلی وابسته است”، بدانید که فرمول REDUCE در گوگل شیت احتمالاً بهترین و تمیزترین راه‌حل موجود برای شماست.