باستخدام المعايير في Microsoft Excel

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

تطبيق المعايير

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

COUNTIF

تتمثل المهمة الرئيسية لمشغل COUNTIF ، الذي يتعلق بمجموعة إحصائية ، في عد الخلايا التي تشغلها قيم مختلفة تستوفي شرطًا محددًا مسبقًا. تركيبه كما يلي:

=СЧЁТЕСЛИ(диапазон;критерий)

كما ترون ، هذا المشغل لديه وسيطين. "النطاق" هو عنوان مجموعة من العناصر في الورقة التي يتم احتسابها.

"المعيار" هو وسيطة تحدد الشرط الذي يجب أن يحتوي على خلايا المنطقة المحددة ليتم تضمينها في العد. كمعلمة ، يمكنك استخدام تعبير رقمي أو نص أو مرجع إلى الخلية التي يوجد بها المعيار. في هذه الحالة ، يمكنك استخدام العلامات التالية للإشارة إلى المعيار: "<" ( "أقل" ) ، ">" ( "المزيد" ) ، "=" ( "يساوي" ) ، "<>" ( "لا يساوي" ). على سبيل المثال ، إذا قمت بتحديد التعبير "<50" ، فسيتم أخذ العناصر المحددة بواسطة وسيطة "المدى" ، والتي تكون فيها القيم العددية أقل من 50 ، في الحسبان في الحساب.سيكون استخدام هذه العلامات لتحديد المعلمات مناسبًا لجميع الخيارات الأخرى التي ستتم مناقشتها. في هذا الدرس أدناه.

والآن دعونا ننظر إلى مثال ملموس عن كيفية عمل هذا المشغل في الواقع.

لذا ، هناك جدول يوضح إيرادات خمسة متاجر في الأسبوع. نحتاج إلى معرفة عدد الأيام لهذه الفترة ، حيث تجاوزت إيرادات المبيعات في المتجر 2 15000 روبل.

1. حدد عنصر الورقة الذي سيطبع فيه المشغل نتيجة الحساب. بعد ذلك ، انقر فوق رمز "إدراج الدالة" .
2. يبدأ معالج الدالة . ننتقل إلى الكتلة "الإحصائية" . هناك نجد وتميز اسم "البلدان" . ثم انقر على زر "موافق" .
3. يتم تنشيط نافذة من الحجج من المشغل أعلاه. في حقل "النطاق" ، حدد منطقة الخلية ، والتي سيتم تنفيذ الحساب من خلالها. في حالتنا ، يجب عليك تحديد محتويات السطر "متجر 2" ، الذي يقع فيه العائد لكل يوم. ضع المؤشر في الحقل المحدد ، واضغط على زر الماوس الأيسر ، حدد المصفوفة المقابلة في الجدول. سيتم عرض عنوان الصفيف المحدد في النافذة.

   في الحقل التالي "Criterion" ، تحتاج فقط إلى تحديد معلمة اختيار مباشر. في حالتنا ، نحتاج إلى حساب فقط عناصر الجدول التي تتجاوز القيمة فيها 15000. لذلك ، فإننا نستخدم لوحة المفاتيح لتوجيه التعبير "15000" إلى الحقل المحدد في الحقل المحدد.

   بعد الانتهاء من جميع التلاعبات المذكورة أعلاه ، انقر فوق الزر "موافق" .

4. يقوم البرنامج بحساب وعرض النتيجة في عنصر ورقة تم تمييزه قبل تنشيط معالج الدالة . كما ترون ، في هذه الحالة تكون النتيجة مساوية للرقم 5. وهذا يعني أنه في صفيف مختار في خمس خلايا هناك قيم تتجاوز 15000. وهذا يعني ، يمكننا أن نستنتج أنه في Shop 2 في خمسة أيام من السبعة التي تم تحليلها ، تجاوزت الإيرادات 15000 روبل.

الدرس: معالج الدالة في برنامج Excel

SCHOTESLIMN

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

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

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

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

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

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

• متجر 1 - 14000 روبل.
• محل 2 - 15000 روبل ؛
• متجر 3 - 24000 روبل.
• متجر 4 - 11000 روبل.
• متجر 5 - 32000 روبل.

1. لتنفيذ المهمة المذكورة أعلاه ، قم بتمييز المؤشر بعنصر ورقة العمل التي سيتم إخراج نتيجة معالجة بيانات COUNTRY إليها. نضغط على الأيقونة "Insert function" .
2. بالانتقال إلى معالج الدالة ، ننتقل إلى الكتلة "الإحصائية" . في القائمة ، من الضروري العثور على اسم COUNTRY FORM وإجراء تحديده . بعد تنفيذ هذا الإجراء ، تحتاج إلى النقر فوق الزر "موافق" .
3. بعد تنفيذ خوارزمية الإجراءات المذكورة أعلاه ، يتم فتح نافذة الوسيطتين COUNTLINES .

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

   بالنظر إلى أن معدل الإيراد اليومي للمتجر هو 14000 روبل ، في حقل "الحالة 1" ، ندخل التعبير "> 14000" .

   في الحقل "نطاق الشرط 2 (3 ، 4 ، 5 )" ، يجب عليك إدخال إحداثيات الخط مع العائدات الأسبوعية وفقًا للمخزن 2 ، والمخزن 3 ، والمخزن 4 ، والخزينة 5. يتم تنفيذ الإجراء وفقًا للخوارزمية نفسها التي تستخدمها مع الوسيطة الأولى لهذه المجموعة.

   في حقول "Condition2" و "Condition3" و "Condition4" و "Condition5" ، ندخل القيم "> 15000 " و "> 24000" و "> 11000" و "> 32000" ، على التوالي. كما قد يتبادر إلى ذهنك ، تتطابق هذه القيم مع فاصل العائد الذي يتخطى القاعدة الخاصة بالمخزن المقابل.

   بعد إدخال جميع البيانات الضرورية (إجمالي 10 حقول) ، انقر فوق الزر "موافق" .

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

الآن دعونا تغيير المشكلة إلى حد ما. يجب أن نحسب عدد الأيام التي حصل فيها المتجر 1 على عائدات تزيد عن 14000 روبل ، ولكن أقل من 17000 روبل.

1. نضع المؤشر في العنصر حيث سيتم إخراج خرج نتائج الحساب. نضغط على أيقونة "Insert function" أعلى منطقة العمل في الورقة.
2. منذ أن قمنا مؤخراً بتطبيق المعادلة COUNCILS ، ليس من الضروري الآن الذهاب إلى مجموعة معالجات الدالة "الإحصائية" . يمكن العثور على اسم هذا المشغل في الفئة "10 المستخدمة مؤخرًا" . حدده وانقر على زر "موافق" .
3. يفتح نافذة الحجج من المشغل СЧЁТЕСЛИМН التي هي مألوفة لدينا بالفعل. نضع المؤشر في الحقل "نطاق الشرط 1" ، وبعد أن فرضت زر الماوس الأيسر ، حدد جميع الخلايا التي تحتوي على الأرباح بحلول أيام المتجر رقم 1. وهي تقع في الصف الذي يسمى "تسوق 1" . بعد ذلك ، ستنعكس إحداثيات المنطقة المحددة في النافذة.

   بعد ذلك ، نضع المؤشر في الحقل "Condition1" . هنا نحتاج إلى تحديد الحد الأدنى للقيم في الخلايا التي ستشارك في الحساب. حدد التعبير "> 14000" .

   في الحقل "نطاق الشرط 2" ، أدخلنا العنوان نفسه بالطريقة نفسها التي تم إدخالها في حقل " نطاق الشرط 1" ، أي أننا أدخلنا مرة أخرى إحداثيات الخلية مع قيم الأرباح في نقطة البيع الأولى.

   في الحقل "Condition2" ، نشير إلى الحد الأعلى للتحديد: "<17000" .

   بعد الانتهاء من جميع الإجراءات المذكورة أعلاه ، انقر فوق الزر "موافق" .

4. يقوم البرنامج بإخراج نتيجة الحساب. كما ترون ، القيمة النهائية تساوي 5. وهذا يعني أنه في 5 أيام من السبعة التي تم تحليلها ، كانت الإيرادات في المتجر الأول في النطاق من 14000 إلى 17000 روبل.

SUMIF

عامل آخر يستخدم المعايير هو SUMMER . على عكس الوظائف السابقة ، فإنه يشير إلى الكتلة الرياضية للمشغلين. وتتمثل مهمتها في تلخيص البيانات في الخلايا التي تتوافق مع شرط معين. الصيغة هي:

=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])

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

"المعيار" هو وسيطة إلزامية تحدد معلمة اختيار الخلية من منطقة البيانات المحددة التي سيتم جمعها. مبادئ الإشارات هي نفسها تلك الخاصة بالحجج المماثلة الخاصة بالمشغلين السابقين ، والتي رأيناها أعلاه.

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

الآن ، كما هو الحال دائما ، النظر في تطبيق هذا المشغل في الممارسة. بناء على نفس الجدول ، نواجه مهمة حساب مبلغ الإيرادات في متجر 1 للفترة التي تبدأ من 11.03.2017.

1. حدد الخلية التي سيتم إخراج النتيجة. نضغط على الأيقونة "Insert function" .
2. انتقل إلى معالج الدالة في كتلة "الرياضي" وابحث عن الاسم "SUMMER" . انقر على زر "موافق" .
3. يتم تشغيل نافذة الوسائط الخاصة بوظيفة SUMIFER . هناك ثلاثة حقول تقابل وسيطات المشغل المحدد.

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

   بما أننا نحتاج إلى إضافة العائدات فقط من 11 مارس ، فإننا ندخل القيمة "> 10.03.2017" في حقل "معيار" .

   في حقل "نطاق التجميع" ، تحتاج إلى تحديد منطقة ، سيتم جمع قيمها ، المقابلة للمعايير المحددة. في حالتنا ، هذه هي قيم إيرادات السطر "Shop1" . حدد الصفيف المناسب لعناصر الورقة.

   بعد الانتهاء من إدخال جميع البيانات المحددة ، انقر على زر "موافق" .

4. بعد ذلك ، سيتم عرض نتيجة معالجة البيانات بواسطة الدالة SUMMER في العنصر المحدد مسبقًا في ورقة العمل. في حالتنا ، هو 47921.53. هذا يعني أنه اعتبارًا من 11/3/2016 وحتى نهاية الفترة التي تم تحليلها ، كان إجمالي الإيرادات في المتجر 1 يبلغ 47921.53 روبل.

SUMIFS

ننتهي من دراسة المشغلين الذين يستخدمون المعايير ، والتوقف في وظيفة SUMMARY . مهمة هذه الوظيفة الرياضية هي جمع قيم المناطق المشار إليها في الجدول ، والتي يتم اختيارها من خلال عدة بارامترات. بناء جملة المشغل المحدد هو:

=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)

"نطاق التجميع" هو حجة هي عنوان هذا المصفوفة ، وهي الخلايا التي ستضاف إليها معايير معينة.

"نطاق الشرط" هو وسيطة تمثل مجموعة من البيانات التي يتم التحقق منها للامتثال للحالة ؛

"الشرط" هو الوسيطة التي هي معيار اختيار للإضافة.

هذه الوظيفة تشير إلى العمليات مع عدة مجموعات من المشغلين المتشابهة.

دعونا نرى كيف ينطبق هذا المشغل على حل المشاكل في سياق جدول إيرادات المبيعات لدينا في منافذ البيع. سنحتاج إلى حساب الدخل الذي جلبه المتجر 1 للفترة من 9 إلى 13 مارس 2017. في الوقت نفسه ، عند تلخيص الدخل ، ينبغي النظر فقط في تلك الأيام ذات الإيرادات التي تزيد عن 14000 روبل.

1. مرة أخرى ، حدد الخلية لعرض الإجمالي وانقر فوق رمز "إدراج الدالة" .
2. في معالج الوظائف ، أولاً ، ننتقل إلى الكتلة "الرياضية" ، وهناك نختار عنصرًا يسمى "SUMMESLIMN" . انقر فوق "موافق" زر.
3. يتم إطلاق نافذة حجج المشغل ، واسمه المذكور أعلاه.

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

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

   نضع المؤشر في الحقل "Condition1" . الشرط الأول هو أننا سنلخص البيانات في موعد لا يتجاوز 9 مارس. لذلك ، ندخل القيمة "> 08.03.2017" .

   ننتقل إلى الحجة "نطاق الشرط 2" . هنا تحتاج إلى إدخال نفس الإحداثيات التي تم تسجيلها في الحقل " نطاق الشرط 1" . نقوم بذلك بنفس الطريقة ، وذلك من خلال إبراز الخط مع التواريخ.

   اضبط المؤشر في الحقل "Condition2" . الشرط الثاني هو أن الأيام التي ستضاف إليها الأرباح يجب ألا تتعدى 13 مارس. لذلك ، نكتب التعبير التالي: «<14.03.2017» .

   انتقل إلى حقل " نطاق الشرط 2" . في هذه الحالة ، نحتاج إلى تحديد نفس المصفوفة التي تم إدخال عنوانها كمصفوفة تجميعية.

   بعد عرض عنوان الصفيف المحدد في النافذة ، انتقل إلى الحقل "Condition3" . مع الأخذ بعين الاعتبار أن القيم التي تتجاوز 14،000 روبل فقط سيتم تضمينها في المجموع ، سنقوم بتدوين الحرف التالي: "> 14000" .

   بعد الإجراء الأخير ، انقر فوق الزر "موافق" .

4. يقوم البرنامج بإخراج النتيجة إلى ورقة. تساوي 62491.38. وهذا يعني أنه بالنسبة للفترة من 9 إلى 13 مارس 2017 ، كان مبلغ الإيرادات عندما تمت إضافته في الأيام التي تجاوز فيها 14000 روبل هو 62491.38 روبل.

التنسيق الشرطي

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

حدد الخلايا في الجدول باللون الأزرق ، حيث تتجاوز قيم اليوم 14000 روبل.

1. نختار المجموعة الكاملة من العناصر في الجدول ، والتي تشير إلى إيرادات منافذ البيع بالأيام.
2. ننتقل إلى علامة التبويب "المنزل" . نضغط على أيقونة "تنسيق شرطي" ، الموجود في كتلة "الأنماط" على الشريط. يتم عرض قائمة الإجراءات. ننقر في الموضع "إنشاء قاعدة ..." .
3. يتم تنشيط نافذة إنشاء قاعدة التنسيق. في مجال تحديد نوع القاعدة ، حدد الاسم "تنسيق الخلايا التي تحتوي على" فقط . في الحقل الأول لحظر الشرط ، من قائمة الخيارات الممكنة ، حدد "Cell value" . في الحقل التالي ، حدد موضع "المزيد" . في الأخير ، نحدد القيمة نفسها ، والتي نحتاج إلى تنسيق عناصر الجدول بها. لدينا 14000. لاختيار نوع التنسيق ، نضغط على زر "تنسيق ..." .
4. يتم تنشيط نافذة التنسيق. الانتقال إلى علامة التبويب "التعبئة" . Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец» , клацаем по кнопке «OK» .
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK» .
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

Более подробно о возможностях условного форматирования рассказывается в отдельной статье.

الدرس: Условное форматирование в программе Эксель

Как видим, с помощью инструментов, использующих при своей работе критерии, в Экселе можно решать довольно разноплановые задачи. Это может быть, как подсчет сумм и значений, так и форматирование, а также выполнение многих других задач. Основными инструментами, работающими в данной программе с критериями, то есть, с определенными условиями, при выполнении которых активируется указанное действие, является набор встроенных функций, а также условное форматирование.

• ← بناء الرسم البياني التبعية في Microsoft Excel
• إنشاء جدول الشبكة في Microsoft Excel →