كيفية استخدام وظيفة Excel الإجمالية لتحسين الحسابات

تتيح لك وظيفة Excel الإجمالية إجراء الحسابات مع تجاهل الصفوف المخفية أو الأخطاء أو الوظائف الأخرى التي تظهر في البيانات. إنه مشابه للوظيفة الفرعية ولكن يوفر المزيد من خيارات الحساب ويمنحك مزيدًا من التحكم في ما تريد استبعاده من الحساب.

بناء الجملة الكلي

قبل أن ننظر إلى بعض الأمثلة على الوظيفة الإجمالية قيد الاستخدام ، دعنا نرى كيف تعمل. تحتوي الوظيفة الإجمالية على بناء جملة – أحدهما للمراجع والآخر للمصفوفات – على الرغم من أنك لا تحتاج إلى ربط نفسك بعقدة تُستخدمها ، حيث يختار Excel الواردة ذات الصلة اعتمادًا على الوسائط التي تدخلها. يمكنك رؤية كلا الجملة قيد الاستخدام عندما أريكم بعض الأمثلة قريبًا.

بناء جملة النموذج المرجعي

بناء الجملة للشكل المرجعي للوظيفة الإجمالية هو:

=AGGREGATE(a,b,c,d)

أين

• أ (مطلوب) هو رقم يمثل الوظيفة التي تريد استخدامها في الحساب ،
• ب (مطلوب) هو رقم يحدد ما تريد أن يتجاهله الحساب ،
• ج (مطلوب) هو نطاق الخلايا التي سيتم تطبيق الوظيفة عليها ، و
• د (اختياري) هو الأول من 252 وسيطات إضافية تحدد نطاقات أخرى.

بناء جملة نموذج الصفيف

من ناحية أخرى ، إذا كنت تعمل مع المصفوفات ، فإن بناء الجملة هو:

=AGGREGATE(a,b,c,d)

أين

• أ (مطلوب) هو رقم يمثل الوظيفة التي تريد استخدامها في الحساب ،
• ب (مطلوب) هو رقم يحدد ما تريد أن يتجاهله الحساب ،
• ج (مطلوب) هو مجموعة القيم التي سيتم تطبيق الوظيفة عليها ، و
• د هي الحجة الثانية المطلوبة من قبل وظائف المصفوفة مثل كبيرة ، صغيرة ، مئوية ، وغيرها.

الوظائف والاستثناءات (الحجج أ و ب)

عند إدخال الحجج أ و ب في أي نموذج بناء الجملة أعلاه ، سيكون لديك خيارات مختلفة للاختيار من بينها.

يوضح الجدول أدناه الوظائف المختلفة التي يمكنك استخدامها في الحساب الكلي (الوسيطة أ). على الرغم من أنك قد تميل إلى كتابة اسم الوظيفة ، تذكر أن هذه الوسيطة يجب أن تكون رقمًا يمثل الوظيفة التي تريد استخدامها. الوظائف من 1 إلى 13 مخصصة للاستخدام مع بناء جملة النموذج المرجعي ، والوظائف من 14 إلى 19 للاستخدام مع بناء جملة نموذج الصفيف.

يسرد هذا الجدول الأرقام التي يمكنك إدخالها لاستبعاد قيم معينة عند إنشاء صيغة الإجمالية الخاصة بك (الوسيطة ب):

الآن ، دعونا نلقي نظرة على بعض الأمثلة على كيفية استخدام الوظيفة الإجمالية في سيناريوهات العالم الحقيقي.

مثال 1: استخدام الإجمالي لتجاهل الأخطاء

يحتوي جدول بيانات Excel هذا على قائمة من لاعبي كرة القدم وعدد الألعاب التي لعبوها وعدد الأهداف التي سجلوها ونسب لعبة كل الهدف. هدفك هو تحديد متوسط ​​نسبة اللعبة لكل الهدف لجميع اللاعبين مجتمعين.

إذا كنت تريد استخدام الوظيفة المتوسطة بمفردها عن طريق الكتابة:

=AVERAGE(Player_Goals(Games per goal))

في الخلية C1 ، من شأنه أن يعيد خطأ ، لأن النطاق المشار إليه يحتوي على #div/0! الأخطاء.

بدلاً من ذلك ، يمنحك استخدام الوظيفة الإجمالية خيار تجاهل هذه الأخطاء وإعادة المتوسط ​​للبيانات المتبقية. للقيام بذلك ، في الخلية C2 ، تحتاج إلى الكتابة:

=AGGREGATE(1,6,Player_Goals(Games per goal))

أين

• 1 (دعوى أ) يمثل متوسط ​​الوظيفة ،
• 6 (دعوى ب) يخبر Excel بتجاهل الأخطاء ، و
• player_goals (ألعاب لكل هدف) هو المرجع.

تتمثل طريقة بديلة لتحقيق نفس النتيجة في استخدام وظيفة iferror في العمود D لاستبدال أي أخطاء بقيمة فارغة.

مثال 2: استخدام الإجمالي لتجاهل الصفوف المخفية (المرجع)

باستخدام نفس جدول البيانات ، فإن هدفك التالي هو حساب إجمالي عدد الأهداف التي سجلها الفريق.

تتمثل إحدى الطرق لعرض الإجماليات في التحقق من “Total Row” في علامة تبويب تصميم الجدول على الشريط ، مما يضع الإجماليات في أسفل الجدول. ومع ذلك ، إذا كنت تعمل مع مجموعة بيانات كبيرة ، فإن التمرير باستمرار لأسفل لرؤية المجاميع قد تضيع الوقت. بدلاً من ذلك ، فكر في وضع الإجماليات في الجزء العلوي من جدول البيانات خارج الجدول المنسق ، بحيث يتم عرضها دائمًا.

على وجه التحديد ، تريد عرض إجماليين. الأول هو المجموع الإجمالي عند الجمع بين الأهداف التي سجلها جميع اللاعبين ، ولكن الثانية هي إجمالي اللاعبين الذين يعرضون فقط في الجدول بعد تطبيق المرشحات.

لحساب المجموع الإجمالي ، في الخلية C1 ، النوع:

=SUM(Player_Goals(Goals scored))

الآن ، حتى بعد تطبيق مرشح على أحد الأعمدة ، مثل عرض اللاعبين الذين لعبوا فقط 15 لعبة أو أكثر ، لا تزال صيغة SUM التي قمت بتطبيقها تتضمن الصفوف التي يتم ترشيحها.

هذا هو المكان الذي ستوفر فيه وظيفة Aggreate اليوم ، لأنها تسمح بحسابك بتجاهل الصفوف التي يتم ترشيحها. في الواقع ، ستعمل الوظيفة الإجمالية أيضًا إذا كنت ترغب في تجاهل الصفوف التي أخفتها عن طريق النقر بزر الماوس الأيمن على رأس الصف والنقر فوق “إخفاء”.

في الخلية C2 ، اكتب:

=AGGREGATE(9,5,Player_Goals(Goals scored))

أين

• 9 (دعوى أ) يمثل وظيفة المجموع ،
• 5 (دعوى ب) يخبر Excel بتجاهل الصفوف المخفية ، و
• player_goals (الأهداف المسجلة) هو المرجع.

الآن ، لاحظ أن نتيجة هذه الصيغة تختلف عن نتيجة الصيغة المبلغ التي استخدمتها في الخلية C1 ، لأنها تعتبر الصفوف فقط المعروضة.

​​​​

مثال 3: استخدام الإجمالي لتجاهل الصفوف المخفية (صفيف)

بعد ذلك ، دعنا نقول أنك تريد سرد أعلى طواف الأهداف للاعبين الذين لعبوا 20 مباراة أو أقل.

يمكنك تطبيق المرشح أولاً ثم إنشاء صيغتك ، ولكن لأغراض هذا العرض التوضيحي ، دعنا ننشئ الصيغة أولاً.

في الخلية C1 ، اكتب:

=AGGREGATE(14,5,Player_Goals(Goals scored),{1;2})

أين

• 14 (دعوى أ) يمثل الوظيفة الكبيرة ،
• 5 (دعوى ب) يخبر Excel أن يتجاهل الصفوف المخفية ،
• player_goals (الأهداف المسجلة) هي مجموعة القيم ، و
• {1 ؛ 2} يخبر Excel أنك تريد أن تعيد أكبر (1) وثاني أكبر قيم (2) على صفوف منفصلة (؛).

عند الضغط على Enter ، لاحظ أن النتيجة هي صفيف مسكوب يغطي الخلايا C1 و C2 لأنك أخبرت Excel بإعادة الجزء العلوي اثنين قيم.

الآن ، قم بتصفية العمود التي لعبت الألعاب لتشمل فقط اللاعبين الذين لعبوا 20 لعبة أو أقل ، ويرون أن نتيجة الصيغة الإجمالية التي أدخلتها تغييرات سابقة لتجاهل الصفوف المخفية.

أشياء يجب ملاحظتها عند استخدام وظيفة الإجمالية

قبل المضي قدمًا واستخدم الوظيفة الإجمالية في مصنفات Excel الخاصة بك ، خذ لحظة لتدوين المؤشرات التالية:

• تعمل وظيفة Excel الإجمالية مع النطاقات الرأسية فقط ، وليس النطاقات الأفقية. لذلك ، عند الرجوع إلى النطاق الأفقي ، لن يتجاهل Accregate الصفوف في الأعمدة المخفية.

• دعوى ج في الصيغة الإجمالية لا يمكن أن تكون نفس الخلية أو نطاق الخلايا عبر أوراق عمل متعددة (المعروفة أيضًا باسم المراجع ثلاثية الأبعاد).

• على الرغم من أن الوظيفة الإجمالية هي وسيلة رائعة لتجاوز الأخطاء في الحسابات ، لا تدخل عادة في تجاهل الأخطاء تمامًا. إنهم موجودون لسبب ما ويمكنهم مساعدتك في استكشاف المشكلات المتعلقة ببياناتك.

• لن يتجاهل شكل صفيف الوظيفة الإجمالية الصفوف المخفية ، أو المجندات الفرعية المتداخلة ، أو المجاميع المتداخلة إذا كانت حجة المصفوفة تتضمن حسابًا.

---

هناك طريقة أخرى لإخفاء الصفوف في جداول Excel بحيث لا تتضمن الوظيفة الإجمالية فقط ما يظهر عن طريق إدخال المقاطع ، الأزرار التفاعلية التي يمكنك النقر عليها لجعل التصفية أكثر وضوحًا.