seychellesartprojects.org

وظيفة AGGREGATE في Excel

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

بناء الجملة

هناك تركيبان لصيغة AGGREGATE Formula:

1. بناء الجملة المرجعي

= AGGREGATE (function_num، options، ref1، ref2، ref [3]، ...)

2. بنية المصفوفة

= AGGREGATE (function_num، options، array، [k])

Function_num هي رقم يشير إلى وظيفة معينة نريد استخدامها ، وهو رقم من 1-19

الخيار: هو أيضًا قيمة عددية تتراوح من 0 إلى 7 وتحدد القيم التي يجب تجاهلها أثناء العمليات الحسابية

Ref1، ref2، ref [3]:  هي الوسيطة أثناء استخدام بناء الجملة المرجعي ، وهي قيمة رقمية أو قيم نريد إجراء الحساب عليها ، على الأقل وسيطتان مطلوبتان ، وسيطات الراحة اختيارية.

المصفوفة: هي مصفوفة من القيم التي نريد تنفيذ العملية عليها ، يتم استخدامها في بناء جملة الصفيف لوظيفة AGGREGATE في Excel

K: هي وسيطة اختيارية وهي قيمة رقمية ، يتم استخدامها عند استخدام دالة مثل LARGE أو SMALL أو PERCENTILE.EXC أو QUARTILE.INC أو PERCENTILE.INC أو QUARTILE.EXC في Excel.

أمثلة

يمكنك تنزيل قالب Excel الخاص بوظيفة AGGREGATE هنا - قالب Excel الخاص بوظيفة AGGREGATE

مثال 1

لنفترض أن لدينا قائمة بالأرقام وسنقوم بحساب المتوسط ​​، العدد الذي يمثل عدد الخلايا التي تحتوي على قيمة ، وعدد الخلايا غير الفارغة ، والحد الأقصى ، والحد الأدنى ، والمنتج ، ومجموع القيم الرقمية المحددة. القيم موضحة أدناه في الجدول:

دعونا أولاً نحسب المتوسط ​​في الصف 9 ، لجميع القيم المعطاة. لمتوسط ​​function_ num هو

في العمود C ، يتم إعطاء جميع القيم ولن نضطر إلى تجاهل أي قيم ، لذلك سنحدد الخيار 4 (تجاهل أي شيء)

واختيار نطاق القيم C1: C8 كمصفوفة من القيم الرقمية

نظرًا لأن ' k' هي وسيطة اختيارية وتستخدم عند استخدام دالة مثل LARGE أو SMALL في Excel أو PERCENTILE.EXC أو QUARTILE.INC أو PERCENTILE.INC أو QUARTILE.EXC ولكن في هذه الحالة ، نقوم بحساب المتوسط ​​لذلك نحن سيهمل قيمة k.

إذن ، متوسط ​​القيمة هو

وبالمثل ، بالنسبة للنطاق D1: D8 ، سنختار مرة أخرى الخيار 4.

بالنسبة للنطاق E1: E8 ، تحتوي الخلية E6 على قيمة خطأ ، إذا كنا سنستخدم نفس صيغة AGGREGATE ، فسنحصل على خطأ ، ولكن عند استخدام خيار مناسب ، فإن AGGREGATE في Excel يعطي متوسط ​​القيم المتبقية مع إهمال الخطأ القيمة في E6.

لتجاهل قيم الخطأ ، لدينا الخيار 6.

وبالمثل ، بالنسبة إلى النطاق G1: G8 ، سنستخدم الخيار 6 (تجاهل قيم الخطأ)

الآن ، بالنسبة للنطاق H3 إذا وضعنا قيمة 64 ، وقمنا بإخفاء الصف الثالث واستخدمنا الخيار 5 ، لتجاهل الصف المخفي ، سنقدم AGGREGATE في Excel القيمة المتوسطة للقيم الرقمية المرئية فقط.

الإخراج دون إخفاء الصف 3

الإخراج بعد إخفاء الصف 3

بتطبيق صيغة AGGREGATE لعمليات أخرى ، لدينا

مثال - # 2

لنفترض أن لدينا جدولاً للإيرادات المتحققة في تواريخ مختلفة من القنوات المختلفة كما هو موضح أدناه

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

نريد أنه عندما نقوم بتصفية القناة ، نحصل على مجموع القيم المرئية ، لذلك بدلاً من استخدام وظيفة SUM ، سنستخدم وظيفة AGGREGATE من أجل الحصول على مجموع القيم المرئية عندما يكون المرشح مطبق.

لذلك ، استبدال صيغة SUM بوظيفة AGGREGATE برمز الخيار 5 (تجاهل الصفوف والقيم المخفية) لدينا ،

الآن ، عندما نطبق عامل التصفية على قنوات مختلفة ، سيظهر عائدات هذه القناة فقط عندما يتم إخفاء بقية الصفوف.

إجمالي الإيرادات المحققة للقناة المباشرة:

إجمالي الإيرادات المحققة للقناة العضوية:

إجمالي الإيرادات المحققة للقناة المدفوعة:

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

لنفترض أنه بالنسبة لقناة الجدول والإيرادات نفسها ، تحتوي بعض قيم الإيرادات لدينا على خطأ ، والآن نحتاج إلى تجاهل الأخطاء وفي نفس الوقت ، إذا أردنا تطبيق مرشح ، يجب أن تتجاهل وظيفة AGGREGATE قيم الصف المخفية أيضًا.

عندما نستخدم الخيار 5 ، نحصل على الخطأ لمجموع إجمالي الإيرادات ، والآن من أجل تجاهل الأخطاء ، يتعين علينا استخدام الخيار 6

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

لذلك ، في هذه الحالة ، سنستخدم الخيار 7 الذي يتجاهل قيم الخطأ وفي نفس الوقت الصفوف المخفية

أشياء للذكرى

• لا تتعرف الدالة AGGREGATE على الدالة _ num قيمة أكبر من 19 أو أقل من 1 وبالمثل بالنسبة لرقم الخيار ، فإنها لا تتعرف على القيم الأكبر من 7 وأقل من 1 ، إذا قدمنا ​​أي قيم أخرى ، فإنها تعطي قيمة #VALUE ! خطأ
• يقبل دائمًا القيمة الرقمية ويُرجع دائمًا قيمة رقمية كمخرجات
• AGGREGATE في Excel له قيود ؛ يتجاهل الصفوف المخفية فقط ولكنه لا يتجاهل الأعمدة المخفية.