تابع SUMPRODUCT در اکسل

تابع Sumproduct در نرم افزار اکسل – آموزش اکسل درس 42

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

شکل استفاده اصلی از تابع SUMPRODUCT

  1. برای مثال، تابع SUMPRODUCT که در پائین ارائه شده است، مجموع مقدار مصرف شده را نمایش می‌دهد.

    استفاده از تابع SUMPRODUCT ساده

    استفاده از تابع SUMPRODUCT ساده

توضیحات، در اینجا تابع SUMPRODUCT برای رسیدن به نتیجه از این فرومول استفاده می‌کند. (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

  1. رنج سلول‌های انتخابی باید دارای یک ابعاد باشند، و گرنه نرم‌افزار اکسل به شما مقدار #VALUE! به معنای خطا در مقدار دهی را نشان می‌دهد.

    تناظر بین سلول ها در تابع SUMPRDOUCT

    تناظر بین سلول ها در تابع SUMPRDOUCT برای جلوگیری از ایجاد خطا

  2. تابع SUMPRODUCT در اینجا هر ورودی را بررسی می‌کند، در صورتی که مقدار آن به شکل عددی نباشد، در آن صورت به جای آن یک صفر قرار می‌دهد.

    بهره گیری از تابع SUMPRODUCT

    بهره‌گیری از تابع SUMPRODUCT

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

استفاده از تابع SUMPRODUCT به جای تابع SUM

استفاده پیشرفته‌تر از تابع SUMPRODUCT

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

1a. برای مثال تابع COUNTIF درمثال پائین تعداد خانه‌هائی که حاوی مقدارهای دقیقاً برابر با star باشند را می‌شمارد.

بهره گیری از تابع COUNTIF در اکسل

بهره گیری از تابع COUNTIF در اکسل

1b. تابع SUMPRODUCT در پائین نیز همان نتیجه را به صورت کامل بر می‌گرداند.

استفاده از تابع SUMPRODUCT به صورت آرایه ای

استفاده از تابع SUMPRODUCT به صورت آرایه ای

توضیحات: فرمول --(A1:A7="star") در اینجا می‌تواند به صورت یک مقدار ثابت آرایه در نظر گرفته شود و به صورت زیر نوشته شود:

--{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}

دو منفی که در ابتدای این فرمول گذاشته شده است، فرمول را مقید می‌کند که تنها دو مقدار صفر بابت مقدار نادرست و یک بابت مقدار‌های درست را به ما بازگرداند. نتیجه:

{1;0;0;0;0;1;0}

این آرایه از مقدارهای ثابت به دست آمد را می‌تواند به عنوان یک آرگومان برای تابع SUMPRODUCT به کار برد و در نتیجه مقدار 2 به دست می‌آید.

1c. تابع COUNTIF در پائین تعداد سلول‌هائی که در آن از عبارت start به علاوه یک کاراکتر دیگر استفاده شده باشد را می‌شمارد. یک علامت تعجب (?) در اینجا به معنای هر کاراکتر دیگر است.

استفاده از تابع COUNTIF

استفاده از تابع COUNTIF

1d. تابع SUMPRODUCT یک تابع همه کار و عالی نیست. شما نمی تواند در آن از کاراکترهای جایگزینی مانند ستاره و یا علامت تعجب استفاده کنید و تابع SUMPRODUCT از آن‌ها پشتیبانی نمی کند.

استفاده از تابع SUMPRODUCT با شرط بولین

استفاده از تابع SUMPRODUCT با شرط بولین

2a. فرمول آرایه ای که در زیر نشان داده شد است، تعداد کارکترها در یک رنج از سلول‌ها را شمارش می‌کند.

استفاده از تابع SUM به صورت آرایه ای

استفاده از تابع SUM به صورت آرایه ای

 

نکته: برای تبدیل کردن فرمول ساده به فرمول آرایه ای می‌توانید از فشردن ترکیبی کلیدهای CTRL+SHIFT+ENTER استفاده کنید. نرم‌افزار اکسل به صورت خودکار دو آکولاد را به ابتدا و انتهای آن فرمول اضافه می‌کند که به معنای آرایه ای بوده آن فرمول است.

2b. تابع SUMPRODUCT در پائین نیز همان نتیجه را به صورت کامل بر می‌گرداند.

استفاده از تابع SUMPRODUCT به شکل آرایه

استفاده از تابع SUMPRODUCT به شکل آرایه

نکته: این آرایه از ثابت‌ها به صورت {9;4;6;5} است که به عنوان یک آرگومان برای تابع SUMPRODCUT است و بعد از انجام محاسبه مقدار 24 را بر می‌گرداند. تابع SUMPRODUCT در اینجا می‌تواند آرایه‌های را به صورت کلی پشتیبانی کند و نیازی به فشردن کلیدهای ترکیبی CTRL + SHIFT +ENTER در انتهای وارد کردن فرمول‌های خودتان ندارید و این تابع خودش فرمول‌ها را به صورت آرایه ای انجام می‌دهد. این یکی از بزرگترین مزایایی است که تابع SUMPRODUCT دارد، زیرا اضافه شده علامت آکولاد به ابتدای فرمول‌ها در اکسل می‌تواند تازه کاران را سردرگم کند.

3a. فرمول آرایه ای که در پائین قرار گرفته است، مجموع فروش‌هائی که در سال 2018 انجام شده است را به ما نشان می‌دهد.

محاسبه مقدار بر حسب سال با تابع SUM آرایه ای

محاسبه مقدار بر حسب سال با تابع SUM آرایه ای

3b. تابع SUMPRODUCT در پائین نیز همان نتیجه را به صورت کامل بر می‌گرداند.

محاسبه تابع SUMPRODUCT برای محاسبه فروش در سال

محاسبه تابع SUMPRODUCT برای محاسبه فروش در سال

توضیحات: این تابع به صورت (YEAR(A1:A5)=2018)*B1:B5  است که در نتیجه آن خواهیم داشت:

({2018;2017;2018;2017;2017}=2018)*{10;1;7;20;3}  و این فرمول سبب تولید مقدار زیر می‌شود:

{TRUE;FALSE;TRUE;FALSE;FALSE}*{10;1;7;20;3}

ما نیزی به قرار دادن دو علامت منفی (مثال 1b را ببینید) در ابتدای این فرمول نداریم، زیرا عبارت ضرب (علامت ستاره) به صورت خودکار سبب ایجاد مقدارهای بولین (یک برای درست و صفر برای غلط) می‌شود. در نتیجه:

{10;0;7;0;0}

این آرایه از مقدارهای ثابت به دست آمد را می‌تواند به عنوان یک آرگومان برای تابع SUMPRODUCT به کار برد و در نتیجه مقدار 17 به دست می‌آید.

No votes yet.
Please wait...

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

منو اصلی

question