اجمع كل صف ن
صيغة عامة=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.
مؤلف ديف برونز