مجموع سلولهایی که معیارهای متعددی را با Excel SUMPRODUCT دارند

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

01 از 02

مجموع سلول هایی که بین دو مقدار قرار می گیرند

تابع SUMPRODUCT در اکسل یک تابع بسیار همه کاره است که بسته به نحوه وارد کردن آرگومان های تابع، نتایج متفاوتی به دست می دهد.

معمولا همانطور که از نامش پیداست، SUMPRODUCT عناصر یک یا چند آرایه را ضرب می کند تا محصول آنها را بدست آورد و سپس محصولات را با هم جمع یا جمع می کند.

با تنظیم نحو تابع، با این حال، می توان از آن برای جمع کردن تنها داده های موجود در سلول هایی که معیارهای خاصی را برآورده می کنند استفاده کرد.

از سال 2007 اکسل، این برنامه شامل دو تابع - SUMIF و SUMIFS - است که داده‌ها را در سلول‌هایی که یک یا چند معیار مجموعه را برآورده می‌کنند جمع می‌کند.

اما در مواقعی کار با SUMPRODUCT برای یافتن چندین شرایط مربوط به همان محدوده ای که در تصویر بالا نشان داده شده است آسان تر است.

SUMPRODUCT نحو تابع جمع سلولها

نحوی که برای جمع کردن SUMPRODUCT برای جمع کردن داده‌ها در سلول‌هایی که شرایط خاص را دارند، استفاده می‌شود:

=SUMPRODUCT([condition1][condition2][array])

شرط 1، شرط 2 - شرایطی که باید قبل از اینکه تابع حاصل ضرب آرایه را پیدا کند باید برآورده شوند.

آرایه - محدوده پیوسته ای از سلول ها

مثال: جمع کردن داده ها در سلول هایی که شرایط چندگانه را دارند

مثال در تصویر بالا داده ها را در سلول های محدوده D1 تا E6 که بین 25 تا 75 هستند اضافه می کند.

ورود به تابع SUMPRODUCT

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

1. روی سلول B7 در کاربرگ کلیک کنید تا به سلول فعال تبدیل شود؛
2. فرمول زیر را در سلول B7 وارد کنید: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
3. پاسخ 250 باید در سلول B7 ظاهر شود
4. با جمع کردن پنج عدد در محدوده (40، 45، 50، 55، و 60) که بین 25 و 75 هستند، به پاسخ رسیدیم. مجموع آنها 250 است.

شکستن فرمول SUMPRODUCT

وقتی از شرایط برای آرگومان هایش استفاده می شود، SUMPRODUCT هر عنصر آرایه را در برابر شرط ارزیابی می کند و یک مقدار بولی (TRUE یا FALSE) برمی گرداند.

برای اهداف محاسباتی، اکسل مقدار 1 را برای آن دسته از عناصر آرایه ای که TRUE هستند (شرط را برآورده می کنند) و مقدار 0 اختصاص می دهد.برای عناصر آرایه که FALSE هستند (شرط را برآورده نمی کنند).

مثلاً عدد 40:

• برای شرط اول درست است، بنابراین مقدار 1 در اولین آرایه اختصاص داده می شود؛
• برای شرط دوم درست است، بنابراین مقدار 1 در آرایه دوم اختصاص داده می شود.

عدد 15:

• برای شرط اول FALSE است بنابراین مقدار 0 در اولین آرایه اختصاص داده می شود؛
• برای شرط دوم درست است، بنابراین مقدار 1 در آرایه دوم اختصاص داده می شود.

یکها و صفرهای مربوطه در هر آرایه با هم ضرب می شوند:

• برای عدد 40 - 1 x 1 داریم که مقدار 1 را برمی گرداند؛
• برای عدد 15 - ما 0 x 1 داریم که مقدار 0 را برمی گرداند.

ضرب یکها و صفرها در محدوده

این یکها و صفرها سپس در اعداد در محدوده A2 ضرب می شوند: B6

این کار برای ارائه اعدادی انجام می شود که با تابع جمع می شوند.

این کار می کند زیرا:

• 1 برابر هر عددی با عدد اصلی
• 0 برابر هر عددی برابر است با 0

بنابراین ما به این نتیجه می رسیم:

• 140=40

  015=0

  022=0

  145=45

  150=50

  155=55

  025=0

  075=0

  160=600100=0

جمع بندی نتایج

SUMPRODUCT سپس نتایج بالا را برای یافتن پاسخ خلاصه می کند.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250