معادلة

اجمع كل صف ن

Ajm Kl Sf N

اجمع كل صف ن

  صيغة Excel: جمع كل صف ن صيغة عامة
=SUM(FILTER(data,MOD(SEQUENCE(ROWS(data)),n)=0))
ملخص

لجمع كل صف ن (أي كل صف ثاني ، كل صف ثالث ، إلخ) ، يمكنك استخدام صيغة تستند إلى وظيفة التصفية ، ال وظيفة MOD ، و ال دالة SUM . في المثال الموضح ، الصيغة في الخلية F6 هي:





=SUM(FILTER(B5:B16,MOD(SEQUENCE(ROWS(B5:B16)),F5)=0))

مع الرقم 3 في الخلية F5 من أجل ن ، والنتيجة هي 70.

تفسير

في هذا المثال ، الهدف هو جمع كل قيمة نونية في نطاق من البيانات ، كما هو موضح في ورقة العمل الموضحة أعلاه. على سبيل المثال ، إذا ن = 2 ، نريد جمع كل قيمة ثانية (كل قيمة أخرى) ، إذا ن = 3 ، نريد جمع القيمة الثالثة ، وهكذا. في ال أحدث إصدار من Excel ، أسهل طريقة للقيام بذلك هي استخدام وظيفة التصفية. في تراث إكسل ، يمكنك استخدام صيغة بديلة تستند إلى الدالة SUMPRODUCT كما هو موضح أدناه.





جميع البيانات في النطاق B5: B16 و ن تم إدخاله في الخلية F5 بالشكل 3.

ما هي فئة التسمية في اكسل

صيغة المثال

في المثال الموضح ، الصيغة في الخلية F6 هي:



=SUM(FILTER(B5:B16,MOD(SEQUENCE(ROWS(B5:B16)),F5)=0))

على مستوى عالٍ ، تستخدم هذه الصيغة الدالة FILTER لاستخراج القيم المرتبطة بكل صف أو بيانات ن ، والدالة SUM لإرجاع مجموع القيم المستخرجة.

استخراج البيانات

بالعمل من الداخل إلى الخارج ، فإن الخطوة الأولى في هذه المشكلة هي جمع البيانات التي يجب تلخيصها. يتم ذلك مع وظيفة التصفية مثله:

FILTER(B5:B16,include)

أين تضمن يمثل منطق الصيغة المطلوب لاستهداف كل قيمة نونية (كل قيمة ثالثة في المثال). لبناء المنطق الذي نحتاجه ، نستخدم مزيجًا من وظيفة MOD ووظيفة SEQUENCE:

MOD(SEQUENCE(ROWS(B5:B16)),F5)=0)

ترجع الدالة ROWS عدد الصفوف في النطاق B5: B16 ، وهو 12:

MOD(SEQUENCE(12),F5)=0)

مع 12 كوسيطة الصفوف ، فإن ملف دالة SEQUENCE إرجاع رقمي مجموعة مصفوفة من 12 رقمًا مثل هذا:

{1;2;3;4;5;6;7;8;9;10;11;12}

استبدال المصفوفة أعلاه وقيمة n (3) في الصيغة لدينا:

MOD({1;2;3;4;5;6;7;8;9;10;11;12},3)=0)

ال وظيفة MOD تُرجع باقي كل رقم في المصفوفة مقسومًا على 3:

{1;2;0;1;2;0;1;2;0;1;2;0}=0

تتم مقارنة النتيجة من MOD بالصفر ، والنتيجة هي مصفوفة من قيم TRUE و FALSE:

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

لاحظ أن كل قيمة ثالثة تساوي TRUE. هذه هي القيمة التي يتم إرجاعها إلى عامل التصفية باعتباره ملف تضمن جدال. يستخدم عامل التصفية هذه المصفوفة 'لتصفية' القيم في النطاق B5: B16. فقط القيم المرتبطة بـ TRUE تجعلها تمر عبر عملية التصفية. النتيجة هي مصفوفة تحتوي على كل قيمة ثالثة في البيانات:

{20;15;10;25}

يقوم FILTER بتسليم مجموعة القيم هذه مباشرةً إلى الدالة SUM ، والتي تُرجع المجموع (70) كنتيجة نهائية. هذه الصيغة ديناميكية. على سبيل المثال ، إذا تم تغيير قيمة n في الخلية F5 إلى 2 (كل قيمة ثانية) ، تكون النتيجة الجديدة 120.

صيغة Excel القديمة

في الإصدارات الأقدم من Excel التي لا تتضمن دالتي FILTER أو SEQUENCE ، يمكنك استخدام صيغة مختلفة بناءً على الدالة SUMPRODUCT:

=SUMPRODUCT(--(MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0),B5:B16)

المفهوم مشابه للصيغة الموضحة أعلاه ولكن النهج مختلف. بدلاً من استخراج قيم معينة فقط ، فإن هذه الصيغة 'تزيل الأصفار' آخر القيم. أولاً ، تستخدم الصيغة الدالة ROW لإنشاء مجموعة نسبية من أرقام الصفوف:

ROW(B5:B16)-ROW(B5)+1

تُرجع مصفوفة رقمية مثل هذا:

{1;2;3;4;5;6;7;8;9;10;11;12}

داخل وظيفة SUMPRODUCT ، نستخدم مرة أخرى وظيفة MOD لإنشاء مرشح:

=MOD(ROW(B5:B16)-ROW(B5)+1,F5)=0
=MOD({1;2;3;4;5;6;7;8;9;10;11;12},F5)=0

وترجع MOD مصفوفة من قيم TRUE FALSE مثل هذا:

كيفية إدراج الماكرو في Excel
{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}

مرة أخرى ، لاحظ أن كل قيمة ثالثة تساوي TRUE. أ خطأ مزدوج (-) تستخدم لتحويل قيم TRUE و FALSE إلى 1s و 0s:

=SUMPRODUCT({0;1;0;1;0;1;0;1;0;1;0;1},B5:B16)

يقوم SUMPRODUCT بعد ذلك بضرب المصفوفتين معًا وإرجاع مجموع المنتجات. فقط القيم الموجودة في B5: B6 المرتبطة بـ 1s تنجو من هذه العملية. القيم الأخرى 'تم استبعادها من الصفر':

=SUMPRODUCT({0;0;20;0;0;15;0;0;10;0;0;25}) // returns 70

النتيجة النهائية هي 70. هذه الصيغة ديناميكية أيضًا. إذا تم تغيير قيمة n في الخلية F5 إلى 2 (كل قيمة ثانية) تكون النتيجة الجديدة 120.

مؤلف ديف برونز


^