دالة countif في excel شرح مبسط وشامل

دالة countif في excel

دالة countif في excel

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

شرح دالة countif

Advertisements

تعتبر دالة countif من الدوال التي يكثر استخدامها في اكسل لإيجاد عدد عنصر معين لو توافر فيه شرط معين حيث ان اذا كان المطلوب عدد الخلايا بشرط معين نستخدم دالة countif

حيث ان تُرجع الدالة countif عدد الخلايا التي تحقق شرط معين

صيغة دالة countif :

COUNTIF  ( range criteria  )=

تتكون دالة countif من وسيطين :

range النطاق : وهو النطاق الذي تريد ان تقوم الدالة بالبحث فيه عن المعيار بمعني ( اين تريد ان تبحث )

criteria المعيار : وهو المعيار او الشرط الذي تشترطه في البحث بمعني ( ما الذي تريد البحث عنه )

 

ناتج الدالة :

عدد الخلايا من النطاق range التي تحقق الشرط او المعيار criteria.

 

تطبيق على دالة countif في excel

Advertisements

فمثلاً امامنا جدول يحتوي على أسماء الطلاب وتقديراتهم ونريد إيجاد عدد الطلاب الذين حصلوا على تقدير امتياز و جيد جدا و جيد و مقبول .. نقوم بعمل الاتي :

1- نقوم بعمل جدول صغير يحتوي على المعايير التي نريد ان نقوم بإيجاد عدد وجودها في الجدول وفي حالتنا هذه هي ( امتياز – جيد جدا – جيد – مقبول ) وعمل عمود لـ ” العدد ” .. أي عمل جدول مثل الموجود بالصورة التالية :

تطبيق دالة countif في excel

 

2- نقوم بكتابة دالة COUNTIF بالشكل التالي

  • نقوم بكتابة يساوي = ثم COUNTIF مع فتح القوس
  • نقوم بتحديد الجدول الذي نريد بالبحث فيه ثم نقوم بتثبيت الجدول
  • كتابة علامة الفاصلة هذه , من خلال shift + حرف الواو
  • ثم اختيار الخلية التي ستبحث الدالة عن عدد وجودها في الجدول

لتصبح الدالة بالشكل التالي

=COUNTIF ( $H$3:$H$10 , L5 )

للتعرف على كيفية كتابة الدوال في excel بالتفصيل قُم بزيارة هذه الموضوع :

أهم 8 أساسيات لتعلم إدخال معادلات excel للمبتدئين

Advertisements

شرح دالة COUNTIF : 

COUNTIF وهي دالة تقوم بإيجاد عدد الخلايا في نطاق معين اذا تحقق فيها شرط معين

$H$3:$H$10 : هو الجدول الذي يحتوي على البيانات التي نريد ان نقوم بعَدّ عناصر منها ( range النطاق )

L5  : هي الخلية الخاصة بالعنصر الذي نريد البحث عنه ( criteria المعيار ) ، مع العلم يمكن كتابته يدوياً بدلا من كتابته في خليه منفصلة ولكن بطبيعة الحال يُفضل كتابة المعايير في خلايا لوحدها من خلال الكتابة وذلك من خلال كتابته بين اقواس مزدوجة ” “

مع ملاحظة انه تم تثبيت الجدول حتي لا يحدث مشاكل عند تكرار الدالة في خلايا اخري

دالة countif في excel

 

 

 

 

 

 

 

ثم نقوم بالضغط علي زر enter

 

3- نقوم بتكرار الدالة حتي نقوم بإيجاد عدد كل تقدير

تطبيق دالة countif في excel 2

ملاحظات على دالة CountIf

Advertisements

1- المعيار criteria يمكن ان يكون خلية معينة او نص او رقم او علامة من العلامات الحسابية

2- عند كتابة criteria المعيار داخل الدالة نفسها ( أي ليس اختيار خلية اخري ) يجب كتابتها في ” ” علامات اقتباس مزدوجة من خلال زر shift مع حرف ال ط في الكيبورد

3- يمكن البحث عن المعيار criteria من خلال العلامات الحسابية و رقم ( اكبر من – اصغر من – اكبر من او يساوي – اصغر من او يساوي – يساوي ) .. مثلاً نريد كتابة :

اكبر من 50 فتُكتب بين علامتين اقتباس “>50”

مع العلم العلامات الحسابية هي :

< اصغر من

> اكبر من

<= اصغر من او تساوي

>= اكبر من او تساوي

<> لا تساوي

4- يمكن عمل المعايير من خلال قائمة منسدلة

5- يجب تثبيت ال range بالأخص عند الحاجة لتكرار الدالة

6- اذا كنت تريد عَد الخلايا التي بها كلمة ” جيد ” حتي لو كان مقترنة بكلمة أخري فتكون بالشكل التالي :

=COUNTIF( $F$2:$H$10 , “*جيد*” )

7- اذا كنت تريد عَد خلايا أسماء الطلاب التي تبدأ بها كلمة ” محمد ” فتكون صيغة الدالة بالشكل التالي :

=COUNTIF( $F$2:$H$10, “محمد*” )

دالة Countifs بشرطين او اكثر

Advertisements

اذا كان المطلوب هو إيجاد عدد الخلايا التي تحتوي على شرطين معاً ، فمثلاً :

امامنا الجدول التالي ونريد ايجاد عدد الطلاب الذين حصلوا على تقدير امتياز مع ان يكونوا في الصف الأول ، عندئذ يكون استخدام دالة CountIfS وذلك من خلال الاتي :

دالة countifs في excel

 

1- نقوم بعمل عمود اخر للصف كما هو موضح في الصورة السابقة

2- كتابة دالة countifs بالشكل التالي :

=COUNTIFS ( $H$3:$H$10 , L5 , $F$3:$F$10 , K5 )

بعد كتابة =COUNTIFS نقوم باختيار العمود الذي يحتوي على المعيار الأول وفي حالتا هذه هو عمود التقديرات ، ثم نقوم بتحديد المعيار الأول ، ثم نقوم بتحديد العمود الذي يحتوي على المعيار الثاني وهو عمود ” الصف ” ثم تحديد المعيار الثاني

 

شرح دالة countifs :

COUNTIFS : دالة إيجاد عدد الخلايا التي تحقق فيها شرطين او اكثر

$H$3:$H$10 : هو العمود الذي يحتوي على التقديرات ( criteria_range1 ) نطاق المعيار الأول

L5 : هي خلية المعيار او الشرط الأول ( criteria1 )

$F$3:$F$10 : هو العمود الذي يحتوي علي ” الصفوف ” ( criteria_range2 ) نطاق المعيار الثاني

K5 : هي خلية المعيار او الشرط الثاني ( criteria2 )

 

صيغة دالة COUNTIFs :

COUNTIFs ( criteria_range1 , criteria1 , [criteria_range2 , criteria2] ,…)

 

يجب ملاحظة الاتي :

– مما سبق يتضح لنا ان criteria_range1 و criteria1 هما إلزاميان أما أي شروط اخري فهي اختيارية .

– تقوم دالة COUNTIFs بإيجاد عدد الخلايا التي تجتمع بها الشرطين criteria1 و criteria2 او اكثر الذي تم اضافتهم .

 

وهكذا قد قُمنا بشرح ميسر ومُفصل لـ دالة countif في excel و دالة countifs تكملة لما قُمنا بشرحه عن موضوع دوال IF في الموضوع السابق والذي انصحك بشدة ان تقوم بالمرور عليه لما فيه من الاستفادة التي ستحصل عليها منه وهو موضوع :

 شرح مبسط عن دالة IF في excel بالأمثلة

 

مواضيع ذات صلة : 

شرح مبسط عن دالة IF في excel بالأمثلة

شرح مبسط عن دالة IF في excel بالأمثلة

دالة IF في Excel

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

كيف يتم استخدام دالة IF

Advertisements

دالة IF تعتبر كباقي الدوال لها صيغة معينة يجب اتباعها وهي :

=IF(logical_test; [value.if true]; [value_if false])

دعونا نتعرف ببساطة عن شرح هذه الصيغة

IF تعني ” لو
وسيطات دالة IF هي

  • logical test او الاختبار المنطقي وهو الشرط الذى نقوم بإجراء المقارنة عليه
  • value if true ( حالة تحقق الشرط ) هو القيمة التي ستظهر لو تم تحقق الشرط
  • value if false (حالة عدم تحقق الشرط ) هو القيمة التي ستظهر لو لم يتم تحقق الشرط

صيغة دالة IF في الاكسل

طريقة استخدام دالة If

1- كتابة يساوي في الخلية التي تريد ان يظهر بها نتيجة الدالة

2- اكتب if ثم الضغط على زر Tab

3- تحديد ال logical test وهو يمكن ان يكون دالة او خلية معينة

ثم كتابة رمز { ; } ( وهو موجود في حرف الكاف بعد تحويل اللغة الي الإنجليزية ) او الرمز { , } ( الموجود في حرف الواو بعد تحويل اللغة الي الإنجليزية )
ملحوظة

  • يتم تحديد الرمز { ; } او { , } حسب الظاهر امامك في الصيغة الخاصة بالدالة حيث يختلف ذلك حسب اصدار الاكسل .

4- تحديد ال value if true وهو يمكن ان يكون دالة او خلية معينة او نص او رقم

5- تحديد ال value if false وهو يمكن ان يكون دالة او خلية معينة او نص او رقم

وهذا يرد على سؤال هل يمكن ان تكون نتيجة دالة IF أي شيء مثل نص أو رقم أو دالة أخرى؟

والان سنتعرف على مثال حي يمكننا تطبيق هذه الخطوات عليه ..

Advertisements

دالة IF البسيطة

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

تطبيق دالة IF البسيطة

1- كتابة الدالة كما تعرفنا في الفقرة السابقة بالشكل التالي :

=IF(J9>=60,”سلبي”,”إيجابي”)

حيث ان :

  • J9 : هي خلية نسبة التحليل
  • 60=< : اكبر من او تساوي

حيث يعتبر “J9>=60 ” هو الـ logical test ( الشرط الذى نقوم بإجراء المقارنة عليه )

  • “إيجابي” هي الكلمة التي ستُكتب لو تم تحقق الشرط – value if true ( حالة تحقق الشرط )
  • “سلبي” هي الكلمة التي ستُكتب لو لم يتم تحقق الشرط – value if false (حالة عدم تحقق الشرط )

مع العلم انه اذا كانت حالة تحقق الشرط او حالة عدم تحقق الشرط عبارة عن نص فـيجب كتابة النص بين double quotation marks علامات الاقتباس المزدوجة ” ”

2- تكرار المعادلة في باقي الخلايا لتصبح بالشكل التالي :

تطبيق دالة IF البسيطة 2

Advertisements

دالة If المركبة / المتداخلة

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

اذا كان عندنا جدول يحتوي على قائمة بأسعار المنتجات ونريد عمل خصم على المنتجات بناءاً على الكمية المشتراه حيث ان اذا كان الكمية المشتراه اكثر من 50 كرتونة يكون الخصم 20% واذا كان اكثر من 20 كرتونة يكون الخصم 10% واذا كانت الكمية المشتراه اكثر من 10 كراتين يكون الخصم 5% واذا كان اقل من 10 لا يكون هناك خصم ..

في هذه الحالة يجب استخدام دالة IF المركبة وذلك من خلال كتابة الدالة بالشكل التالي :

=IF( E5>=$M$5 , $K$5 , IF(E5>=$M$6 ,$K$6 , IF(E5>=$M$7,$K$7,0 )))

كما في الصورة التالية :

تطبيق دالة IF المركبة

شرح دالة IF المركبة :

دعونا ببساطة شديدة نوضح هذه الدالة بالتفصيل ، حيث ان الدالة السابقة تتكون من ثلاثة أجزاء

الجزء الأول : =IF( E5>=$M$5 , $K$5

الجزء الثاني : IF(E5>=$M$6 ,$K$6

الجزء الثالث : IF(E5>=$M$7,$K$7,0)))

حيث ان :

E5 هي خلية الكمية المشتراه

M5 هي خلية مكتوب بها عدد الكراتين 50

K5 هي خلية مكتوب بها النسبة 20%

الجزء الأول وهو يعني ان لو ان الكمية E5 اكبر من او تساوي الخلية 5$M$ ( وهي خلية مكتوب بها 50 يقصد 50 كرتونة ) تكون النتيجة هي الخلية 5$K$ ( وهي خلية نسبة الخصم 20% )

في حالة عدم تحقق الشرط سيتم الانتقال الي الجزء الثاني وهو يعني
لو ان الكمية E5 اكبر من او تساوي الخلية 6$M$ ( وهي خلية مكتوب بها 20 ) تكون النتيجة هي الخلية 6$K$ ( وهي خلية نسبة الخصم 10% )

وفي حالة عدم تحقق الشرط سيتم الانتقال الي الجزء الثالث وهو يعني
لو ان الكمية E5 اكبر من او تساوي الخلية 7$M$ ( وهي خلية مكتوب بها 10 ) تكون النتيجة هي الخلية 7$K$ ( وهي خلية نسبة الخصم 5% )

وفي حالة عدم تحقق الشرط الأخير ستكون النتيجة صفر

يجب ملاحظة الاتي :

1- مع كل شرط جديد يتم فتح دالة IF جديدة

2- متاح إضافة شروط أيا كان عددها

3- علامة اكبر من او اصغر من تُكتب قبل علامة يساوي

4- تم تثبيت خلية نسبة الخصم و عدد الكراتين حتي تتم عملية تكرار المعادلة بنجاح

5- تم عمل جدول صغير لنسبة الخصم وعدد الكراتين لكي يسهل علينا في أي وقت تعديل تلك البيانات بدون التعديل في الدالة نفسها

5- يتم غلق دالة IF بعدد الاقواس التي تم فتحها بمعني انه اذا تم فتح 3 اقواس يتم غلق 3 اقواس وهكذا ..

يمكنك مراجعة الدالة والمثال من خلال تحميل ملف الشرح من هنا

Advertisements

دالة IFERROR

تستخدم دالة IFERROR لمعالجة الأخطاء التي قد تحدث في دالة اكسل ، فهي تختبر ما اذا كانت الدالة تعطي خطأ ام لا حيث انها اذا أعطت خطأ تقوم دالة IFERROR بإرجاع قيمة محددة بدلاً من رسالة الخطأ value# ، علي سبيل المثال :

لدينا جدول يعتمد علي تضريب الكمية في السعر لإعطاءنا الإجمالي ، فبطبيعة الحال لا تستطيع دالة الضرب بضرب الرقم في نص وبالتالي يظهر لنا الخطأ value# كما هو موضح بالصورة التالية

دالة if error في الاكسل

ولإخفاء هذا الخطأ نقوم باستخدام دالة IFERROR من خلال كتابة الدالة قبل دالة الضرب لكي تظهر بالشكل التالي :

=IFERROR(H8*I8,””)

حيث ان :

صيغة دالة IF Error هي

=IFERROR(value,value_if_error)

value هي الدالة المطلوب إخفاء الأخطاء بها

value_if_error هو المطلوب كتابته عند ظهور خطأ وفي حالتنا هذه نريد ان لا يظهر شيء لذلك قمنا بكتابة علامة الاقتباس المزدوجة “”

ونقوم بتكرار الدالة لكي يتم تطبيقها علي باقي الخلايا بالشكل التالي

Advertisements

تطبيق دالة IF ERROR

استخدامات دالة IF

بعد ان تعرفنا على كيفية استخدام دالة IF في Excel نود ان نتعرف في عُجالة عن ماهيه اهم الأمور التي يجب ان نستخدم بها دالة IF

1- التحقق من صحة البيانات وتنظيمها

2- التحقق من ان قيم معينة تقع ضمن نطاق معين او انها تلبي شروطاً محددة ام لا

3- تصنيف و فرز البيانات حسب معايير محددة

4- انشاء تقارير تفصيلية بناءا على شروط محددة مثل تقارير المبيعات او غيرها

5- في مجال التعليم : فرز درجات الطلاب من حيث الناجح و الراسب

6- في مجال المرتبات والأجور : فرز الموظفين من حيث الراتب منخفض او متوسط او مرتفع

وغيرها من المجالات التي استخدامنا فيها لدالة IF بشكل من الاشكال يضيف له ويوفر عليه الكثير من الوقت والمجهود .

المشاكل التي يمكن ان تواجهك عند استخدام دالة IF

المشاكل الشائعة في دالة IF

1- عندما يكون النص الموجود في جدول البيانات يحتوي على مسافة فبالتالي لن تعمل دالة IF بشكل صحيح ، فمثلا لدينا جدول يقوم بتصنيف الملابس الي اطفالي و رجالي فإذا كان اطفالي يكون الخصم 20% واذا كان رجالي يكون الخصم 15% ، كما هو موضح بالصورةاستخدام دالة TRIM في الاكسل

بالنظر في المثال نلاحظ ان ” التيشرت ” اخذ نسبة خصم 20% رغم انه رجالي وذلك بسبب وضع مسافة بالخطأ مع كلمة ” رجالي ” ، ولحل هذه المشكلة نستخدم دالة TRIM التي تقوم بإزالة أي مسافة يمكن ان تكون موجودة في logical test وذلك من خلال تعديل الدالة لتصبح بالشكل التالي :دالة TRIM في اكسل

من خلال كتابة TRIM قبل logical test

ثم تكرار الدالة في جميع الخلايا

تطبيق دالة TRIM في اكسل

Advertisements

2- لإرجاع قيمة نصية في دالة IF تحتاج الي إحاطة النص بعلامات الاقتباس المزدوجة ” ” ، لتكون شكل دالة IF بهذا الشكل

=IF(J9>=50,راسب,ناجح)

مع العلم انه عند ارجاع قيم منطقية لا نحتاج لإحاطة القيم بعلامات الاقتباس المزدوجة ، هذه القيم مثل True و False لتكون شكل الدالة كالآتي :

=IF(J10>=60,TRUE,FALSE)

3- لإرجاع فراغ في دالة IF تحتاج الي كتابة علامات الاقتباس المزدوجة بهذا الشكل “”

4- ليس فقط الرموز التي يمكن استخدامها هي اكبر من و اصغر من ، فهناك رموز اخري يمكننا استخدامها لتسهل علينا بعض الأمور والتي سنتعرف عليها الان :

= يساوي

<> لا يساوي

< اكبر من

> اصغر من

=< اكبر من او يساوي

=> اصغر من او يساوي

علي سبيل المثال :

=IF(H12<>0,”Not zero”,”zero”)

هذه الدالة البسيطة تختبر ما اذا كان الموجود في الخلية H12 هو صفر ام لا
5- خطأ #NAME؟ : يعني هذا الخطأ هو وجود خطأ كتابي في صيغة الدالة

كما عودناكم فإن جميع الأمثلة التي تم ذِكرها في الشرح بالدوال موجودة في الملف التالي برابط تحميل مباشر

رابط تحميل ملف الشرح

Advertisements

مواضيع ذات صلة :

شرح دالة vlookup بالصور

شرح دالة vlookup بالصور

دالة Vlookup

Vlookup هو اختصار لـ Vertical يعني العمودي و lookup يعني البحث (البحث العمودي) فهي دالة بحث من دوال البحث التي يوفرها برنامج Microsoft Excel فهي تستخدم للبحث عن قيمة معينة (القيمة المبحوث عنها ) في عمود محدد من خلال الاعتماد على قيمة أخرى معلومة مقابلة لها في نفس الصف فهذه العملية تتم علي جدول يحتوي علي بيانات مختلفة ، اي اذا كان لديك كمية كبيرة من البيانات وتريد جلب معلومة او اكثر من هذه البيانات ، لذلك فإن دالة Vlookup تعتبر من اهم الدوال واكثر الدوال استخداماً في برنامج Microsoft excel حيث يمكن استخدامها في البحث عن رواتب الموظفين او درجات الطلاب او أسعار المنتجات وغيرها من الاستخدامات حيث ان دالة vlookup لا غنى  عنها أياً كان المجال الخاص بك ، لذلك سنتحدث في هذا الموضوع عن شرح دالة vlookup بالصور التوضيحية و تطبيقات عملية و الأخطاء التي قد تواجهك عند استخدام دالة vlookup .

شرح دالة Vlookup

Advertisements

قبل ان نقوم بشرح الدالة نود ان نعرف اولاً ما هي صيغة دالة Vlookup ، فالدالة تتكون من اربع بنود

صيغة دالة vlookup

تطبيق على دالة Vlookup

ولكي يتضح لنا بشكل مبسط شرح دالة Vlookup نقوم بتطبيق مثال بسيط يوضح لنا ذلك :

مثلاً لدينا جدول بأسماء الطلاب و درجاتهم في كل مادة

شرح دالة vlookup بالتفصيل

كما عرفنا ان دالة Vlookup تشمل على اربعة بنود ، سنتطرق الآن الي كل بند بها مع شرحه من خلال التطبيق العملي :

1- اسم الطالب وهو lookup value وهو عبارة عن خلية محددة تكتب فيها اسم الطالب الذي تريد معرفة درجته ولتكن هذه الخلية مثلا D11 و ليكن اسم الطالب هو ” حسن ”

مع العلم : 

– اسم الطالب هو القيمة المعلومة

– درجة المادة هي القيمة المبحوث عنها

Advertisements

2- الجدول الذي ستقوم الدالة بالبحث فيه وهو table array ( نقوم بتحديد الجدول بالكامل ) ، واذا كنا سنقوم بتكرار المعادلة على اكثر من خلية فيجب تثبيت الجدول من خلال الضغط علي زر F4

 

3- تحديد العمود الذي تريد الدالة بالبحث فيه وهو col index num فمثلاً نريد معرفة درجة مادة الرياضيات فكيف نقوم بتحديد العمود ؟

دالة vlookup لا يمكنها تحديد العمود يدوي ولكن يتم التحديد من خلال رقم العمود وذلك من خلال الاتي :

يتم عد الاعمدة يدوياً في الجدول من الاتجاه الرئيسي لشيت الاكسل  ، فمثلا في هذا الشيت الاتجاه من اليمين الي اليسار اذا نقوم بالعد بداية من عمود ” اسم الطالب ” حتي نصل الي عمود مادة ” الرياضيات ”

فيكون عمود الرياضيات هو رقم 3 ، اذا فـ col index num تساوي 3

سؤال : 

كيف نعرف اذا كان الشيت من اليمين الي اليسار او العكس بطريقة سهلة ؟

اذا كان العمود A يمين الشيت اذا فهو من اليمين الي اليسار واذا كان العمود A في اليسار اذا فاتجاه الشيت من اليسار الي اليمين

 

4- اما خانة [range_lookup] فهي المسؤولة عن دقة النتائج ، لجعل النتيجة دقيقة 100% اكتب 0 او False ، اما في حالات معينة قد نحتاج الي نتائج تقريبية فنكتب 1 او True .

بعد ادخال هذه المدخلات فتصبح المعادلة بالشكل التالي :

=VLOOKUP(   D11  ,  $B$2:$H$8 ,  3  ,   FALSE  )

تطبيق دالة Vlookup

 

ملحوظة : 

يمكنك عمل قائمة منسدلة في خلية ” اسم الطالب ” بدلا من كتابة الاسم في كل مرة ، ولعمل قائمة منسدلة قم بزيارة هذا الموضوع : تعديل قائمة منسدلة في Excel

 

المطلوب منك الآن هو تطبيق الدرس وكتابة المعادلة لمعرفة درجة مادة العلوم

Advertisements

 

رابط تحميل تطبيق دالة Vlookup 

رابط التحميل

كيفية استخدام الدالة vlookup في excel

يوجد طريقتان لاستخدام دالة vlookup ، الطريقة الاولي 

هي كتابة الدالة بشكل عادي بمعني الكتابة اليدوية للدالة وهي كالتالي :

1- كتابة علامة يساوي ثم كتابة vlookup والضغط على زر tab

2- ثم اختر الخلية التي ستكتب بها اسم الطالب الذي تريد معرفة درجته

وبعد تحويل لغة الكيبورد ( لوحة المفاتيح ) الي اللغة الإنجليزية قم بالضغط على زر حرف الكاف لكتابة العلامة الفاصلة بين كل مدخل وهي هذه العلامة ;

او العلامة , الموجودة في حرف الواو  على حسب اصدار الاكسل عندك

3- ثم قم بتحديد الجدول بأكمله ثم العلامة ; او ,

4- قم بكتابة رقم العمود الخاص بالمادة التي تريد ان تعرف درجتها ثم العلامة ; او ,

5- ثم قم باختيار false ثم اغلق المعادلة من خلال علامة القوس (كتابة دالة vlookup يدويا

 

Advertisements

الطريقة الثانية لاستخدام دالة vlookup

1- من خلال الضغط على علامة fx المشار اليها في الصورة التالية واختر من select category اختر ALL واكتب في مربع البحث Vlookup وقم بالضغط على GO ، ستظهر لك الدالة بالأسفل قم باختيارها

دالة vlookup

2- سيظهر لك هذه النافذة

بنود دالة vlookup

في خانة lookup value قم باختيار خلية اسم الطالب الذي تريد معرفة درجته

وفي خانة table array قم بتحديد الجدول بالكامل وقم بتثبيته من خلال زر F4

وفي خانة col index num اكتب رقم العمود الذي تريد معرفة درجة المادة كما تعرفنا منذ قليل

وفي خانة [range_lookup] اكتب 0

ثم اضغط OK

استخدامات دالة vlookup

Advertisements

1- اذا كان لديك الكثير من الموظفين ولديك كشف بمعلومات عنهم وتريد معرفة معلومة معينة بدون مجهود .

2- اذا كنت مدرس ولديك كشف بأسماء الطلاب و معلومات عنهم او درجاتهم او تقديراتهم .

3- اذا كنت تاجر ولديك قائمة كبيرة من أسعار المنتجات وتريد معرفة سعر منتج بعينة بدون عناء البحث .

4- اذا كنت تعمل في مكتب محاسبة فهي لا غني عنه

5- بشكل عام فهي تفيد اكثر عندما تكون كمية البيانات كبيرة وتريد البحث او جلب معلومة او اكثر من هذه البيانات .

الخلاطة ان أي مجال من المجالات العملية يمكن ان تستخدم فيها دالة Vlookup لتسهل عليك امر ما او عملية تقوم بها العديد من المرات .

دالة vlookup باكثر من شرط

بعد ان فهمنا جيدا طريقة عمل دالة vlookup فعلمنا ان الدالة تبحث من خلال شرط واحد ( اسم الطالب مثلاً ) ، فماذا لو كنا بحاجة للبحث بأكثر من شرط ؟! .. نوضح ذلك من خلال المثال التالي :

لدينا جدول لأسماء الطلاب والفصول الخاصة بهم ولكن هنا أسماء طلاب متكررين كما هو واضح في الصورة التالية ، فهناك اكثر من ” محمد ” واكثر من ” علي ” .. فماذا نفعل ؟!

شرح دالة Vlookup باكثر من شرط

 

Advertisements

حل المثال 

1- نقوم بعمل عمود قبل الاسم ولنسميه مثلا ” مساعد ” ونكتب به علامة يساوي ثم اختر اسم الطالب ثم اكتب علامة & ثم اختر الفصل ، ليكون شكل الدالة كالاتي :

=C4&D4

معني ذلك اننا قمنا بإضافة الاسم مع الفصل ليكونوا في خلية واحدة

ونقوم بتكرار المعادلة لاسفل

2- وفي الخلية التي سنكتب بها دالة vlookup نقوم بكتابة يساوي vlookup وعند اختيار lookup value نقوم باختيار اسم الطالب ثم كتابة علامة & ثم اختيار الفصل ، واكمال باقي الدالة كما تعرفنا ، لتكون الدالة كالآتي :

=VLOOKUP(  F12&G12   , $B$3:$J$9  ,  4  ,  0  )


تطبيق دالة vlookup باكثر من شرط

 

كما لاحظنا في الصورة السابقة ان المعادلة تمت بنجاح وذلك من خلال اننا قمنا بتعديل دالة vlookup لكي تبحث عن شرطين ( اسم الطالب + الفصل ) وبنفس الطريقة يمكنك تعديل دالة vlookup لكي تبحث عن 3 شروط او اكثر

دالة vlookup لا تعمل

يجب ان نأخذ في الاعتبار ان لكل دالة قواعد يجب ان نلتزم بها حتي لا يحدث أخطاء بسبب عدم اتباع هذه القواعد ، فدعونا نعرف ما هي قواعد والاشياء التي يجب ان نلاحظها عند استخدام دالة vlookup :

1- يجب ان يكون القيمة الذي نبحث عنه في اول عمود في الجدول ( اذا كان الشيت من اليمين الي اليسار فيكون اول عمود في اليمين واذا كان الشيت من اليسار الي اليمين فيكون او عمود في اليسار ) .

2- يجب ان يكون هناك تطابق بين المبحوث عنه في lookup value و الموجود في العمود الأول ، حيث ان اذا كان هناك حرف زيادة او نافص او رقم زيادة او ناقص فستكون النتيجة error خطأ ، لذلك يُنصح دائماً بعمل قائمة منسدلة في الخلية لتفادي هذا الخطأ .

3- يجب ألا يحتوي table array عن اي خلايا مدمجة Merge .

4- افهم جيدا طريقة معرفة رقم العمود في الجدول كما شرحناها في بداية الدرس col index num واذا احتجت الي اي مساعدة لا تتردد في كتابة استفسارك في التعليقات .

5- في حالة اختيار ان تكون المطابقة غير تامة true او 1 ( البند الرابع في المعادلة )  فيجب ان يكون العمود الاول في table array مُرتب ترتيب تصاعدي وإلا ستكون النتيجة خاطئة

Advertisements

خيار المطابقة غير تامة او True يتم استخدامه عندما تكون النتيجة المطلوبة تقريبية ،

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

تطبيق دالة vlookup حالة المطابقة غير تامة

 

وهذه هي صيغة دالة vlookup المستخدمة :

=VLOOKUP(  C4  ,  $G$6:$H$11  ,  2  ,TRUE)

C4 هي خلية درجات الطالب 

$G$6:$H$11  هو جدول التقديرات 

2 هو رقم عمود التقديرات 

TRUE وهي حالة المطابقة غير التامة

Advertisements

6- لاحظ ان شكل الفاصلة تختلف من اختلاف إصدارات الاكسل ففي البعض يكون   والبعض الاخر يكون   ,    ، فعليك ملاحظة ذلك من خلال التعليمات التي يظهرها لك اكسل عند كتابة دالة vlookup او قم باستخدام الطريقة الثانية التي تم شرحها لكتابة دالة vlookup .

 

موضوعات قد تُهمك : 

شرح دالة الضرب في Excel بالصور

دالة الضرب في excel

دالة الضرب في Excel

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

طريقة الضرب بإستخدام قيم رقمية في excel

يمكننا اجراء عملية الضرب بكل بساطة بسهولة استخدام الالة الحاسبة وذلك من خلال عمل الاتي :
– كتابة يساوي = ثم الرقم الاول ثم رمز النجمة * ثم الرقم الثاني
لتظهر الدالة بالشكل التالي :

=2*3

ثم الضغط على enter
ليظهر لك النتيجة وهي 6

Advertisements

شرح الضرب باستخدام قيم الخلايا في excel

وهي طريقة اكثر تطوراً من الطريقة السابقة تمكنك من تعديل معطيات المعادلة بكل سهولة عند الحاجة لعمل ذلك ، ولتطبيق طريقة الضرب باستخدام قيم الخلايا نتبع الاتي :
1- كتابة كل عامل من عوامل المعادلة في خلية منفردة
2- وفي الخلية التي تريد ان يظهر بها الناتج نقوم بكتابة = ثم اختيار العامل الاول بالنقر عليه بالماوس ثم كتابة النجمة * ثم اختيار العامل الثاني ثم اضغط enter
3- واذا كانو اكثر من عاملين يجب ان يكون بين كل عامل والآخر النجمة * حيث انه يعتبر البديل لعلامة الضرب x في الرياضيات

لتظهر لنا بهذا الشكل :

=A1*B1

واذا كان اكثر من عامل تكون بهذا الشكل :

=A2*B2*C2

  • حيث ان هذه تعتبر هي الصيغة الصحيحة لضرب خليتين او اكثر
  • وهذه الخلايا في هذا الوضع تُسمى ب المرجع النسبي Relative reference
  • هذه الطريقة تسمى ضرب الأرقام الموجودة في الخلايا باستخدام العوامل الرياضية

وتمكننا هذه الطريقة ايضاً من ضرب عمود في عمود من خلال تكرار المعادلة

ضرب عمود من الأرقام في رقم ثابت

Advertisements

مثلاً لدينا مجموعة من اسعار منتجات ونريد ضرب الاسعار في نسبة الضريبة مع العلم ان نسبة الضريبة ثابتة ، حيث يمكننا ضرب اي عدد من الارقام في رقم ثابت بطريقة بسيطة من خلال اتباع الخطوات الاتية :
1- تُخصص خلية خارج الجدول لكتابة بها الرقم الثابت
2- نقوم في الخلية التي نريد ان يظهر بها نتيجة ضرب الارقام في رقم ثابت وكتابة دالة الضرب بالشكل التالي :
كتابة = ثم اختيار اول خلية في عمود الارقام بالماوس ثم كتابة النجمة * ثم اختيار خلية الرقم ثابت وهو ( نسبة الضريبة في حالتنا )

نأتي الان لأهم خطوة وهي تثبيت خلية نسبة الضريبة ، سنقوم بعمل ذلك من خلال الاتي

الضغط على اسم خلية الرقم الثابت حتى يصبح مؤشر الماوس بين حرف العمود ورقم الصف ثم الضغط على زر F4 في الكيبورد الموجود في الصف الاعلى ثم اضغط enter
ليصبح شكل المعادلة بهذا الشكل :

=B4*$H$3

ضرب عمود من الأرقام في رقم ثابت

حيث ان
B4 هي اول خلية في عمود الارقام
$H$3 هو الرقم الثابت بعد عمل تثبيت للخلية من خلال زر اختصار التثبيت وهو F4 ، حيث ان علامة الدولار dollar sign تدل علي التثبيت وفي حالتنا هذه فهي موضوعة قبل حرف العمود اي تتثبت العمود وموضوعة قبل رقم الصف اي تثبيت الصف بالتالي تثبيت الخلية .

حيث ان تثبيت الخلية في هذه الحالة يُسمى ب المرجع المطلق Absolute Reference

3- تكرار المعادلة في جميع الخلايا

دالة product

تقوم بضرب الأرقام والخلايا والنطاقات وتُعرفها مايكروسوفت بانها تضرب كافة الأرقام المحددة كوسيطات ، ميزة هذه الدالة انها تقوم بضرب مجموعة من الخلايا ببعضها البعض بدون كتابة كل خلية على حدى حيث انها تشبه كثيراً دالة الجمع SUM في آلية عملها ، لذلك تعتبر دالة product هي دالة الضرب في Excel الاساسية والتي يَكُثر استخدامها لسهولتها .
شرح استخدام دالة product
في الخلية التي تريد ان يظهر بها النتيجة اكتب

=product(

ثم قم بتظليل الخلايا المطلوب ضربها ثم اغلق القوس
ليظهر شكل المعادلة بالشكل التالي

=PRODUCT(B4:B7)

دالة product

دالة SUMPRODUCT

Advertisements

تقوم دالة SUMPRODUCT بوظيفة الجمع والضرب في نفس الوقت .. نشرح ذلك من خلال مثال ليتضح الامر ، فمثلاً اذا كان لدينا كميات من اصناف معينة وسعر كل صنف ونريد ايجاد اجمالي قيمة هذه البضاعة باستخدام معادلة واحدة فقط ، هنا تتكفل دالة SUMPRODUCT وتقوم بهذه الوظيفة حيث انها تقوم بضرب كل عنصر من عناصر الوسيط الاول في العنصر الذي يقابله في الوسيط الثاني وجمع كل ناتج عملية ضرب ، فهي يمكنها ايضاً بإيجاد اجمالي المبيعات او إجمالي المشتريات او قيمة المخزون وغيرها من الاستخدامات .
طريقة استخدام دالة SUMPRODUCT
1- قم بكتابة

=SUMPRODUCT(

2- قم بتحديد النطاق الاول
3- قم بالضغط على , ( من خلال زر Shift مع حرف الواو )
4- قم بتحديد النطاق الثاني
5- قم بغلق القوس والضغط علي enter
لتظهر المعادلة بالشكل التالي :

=SUMPRODUCT(C4:C7,D4:D7)

دالة sumproduct

كيفية عمل جدول الضرب في excel

Advertisements

يمكننا عمل جدول الضرب باستخدام معادلة واحدة .. ولكن قد تسأل ما الغرض من عمل جدول الضرب على الاكسل الغرض هنا هو تدريبك بشكل جيد على استخدام معادلة الضرب مع استخدام ميزة تثبيت الخلايا في الاكسل حيث انه يجب تثبيت خلايا معينة لكي تعمل دالة الضرب بشكل صحيح
طريقة عمل جدول الضرب في excel
1- نقوم بكتابة الارقام من 1 الي 10 في الصف 1 والعمود A مع ترك خلية A1 فارغة
عمل جدول الضرب في اكسل
2- نقوم بكتابة الدالة الاتية في الخلية B2 :
B$1*$A2=
وهنا نحن قمنا بتثبيت الصف 1 والعمود A فقط لانها هما اللذان يحتويان على ارقام جدول الضرب

وهذه الحالة من تثبيت الخلايا تسمى ب المرجع المختلط Mixed Reference

عمل جدول الضرب في اكسل

3- نقوم بتكرار دالة الضرب في جميع الخلايا .

عمل جدول الضرب في اكسل

 

عمل جدول الضرب في اكسل

وبذلك فقد تعرفنا علي جميع طرق اجراء عملية الضرب في excel والدوال المستخدمة وما هي دالة الضرب في excel .

 

مواضيع قد تُهمك :

تكرار المعادلة الحسابية على الخلايا في excel

تكرار المعادلة الحسابية في اكثر من خلية من ضمن الاشياء التي تستمتع بها اثناء استخدامك برنامج excel هي اننا يمكننا تكرار المعادلة في اكثر من خلية بدون الحاجة الي كتابة المعادلة في كل مرة فـبضغطة زر يمكننا تكرار المعادلة بسهولة تامة وذلك ماهو سنتعرف عليه في هذا الدرس الخفيف ولكن من اساسيات excel التي يجب ان تعرفها اذا كنت مُقبل علي تعلم برنامج الاكسل .

تكرار المعادلة الحسابية على الخلايا دون اعادة كتابتها

فمثلاً اذا كان لدينا جدول به اسماء الطلاب ودرجاتهم في كل مادة ونريد جمع هذه الدرجات في خانة الاجمالي وبعد ان قُمنا بكتابة معادلة الجمع sum في اول خلية هل من المنطق تكرار كتابة المعادلة في كل خلية لكل اسم طالب ؟!

بالطبع لا فنحن لسنا بحاجة الي كتابة المعادلة مع كل طالب فبعد كتابة المعادلة لمرة واحدة يمكننا تكرارها في باقي الخلايا من خلال استخدام مهارات النسخ والتكرار التي يتيحها لنا برنامج excel  والطريقة كالاتي :

Advertisements

طريقة عمل نسخ المعادلة لعمود

حيث انه يمكننا عمل نسخ للمعادلة بطريقتان
الطريقة الاولي لتطبيق معادلة علي عمود كامل
بعد ان تقوم بكتابة المعادلة نقوم بالمرور امام المربع الصغير الموجود يسار او يمين الخلية _حسب اتجاه الشيت_ وسيظهر هذا المؤشر مؤشر النسخ في excel بدلاً من مؤشر الماوس

مؤشر النسخ في excel
تقوم بالضغط مع السحب لأسفل حتي تقوم بنسخ المعادلة لآخر خلية تريدها

تكرار المعادلة الحسابية في excel
وستظهر لنا نتيجة المعادلات فور نسخها بالشكل التالي

تطبيق تكرار المعادلة الحسابية في excel

وللتأكد من النتيجة يمكنك بالطبع حساب هذه المعادلة يدوياً او علي الآلة الحاسبة للتأكد من ان نسخ المعادلات تم بشكل صحيح ..

الطريقة الثانية لتطبيق معادلة علي عمود كامل
وهذه الطريقة تستخدم أكثر عندما نُريد أن ننسخ المعادلة لعدد كبير من الخلايا مثلا المئات او الآلاف من الخلايا فهي أسهل وأسرع والطريقة كالآتي :
1- بعد ان تقوم بكتابة المعادلة

2- نقوم بالمرور امام المربع الصغير الموجود يسار او يمين الخلية _حسب اتجاه الشيت_ وسيظهر هذا المؤشر بدلاً من مؤشر الماوس

مؤشر النسخ في excel
ونقوم بالضغط مرتين متتاليتن وسيتم النسخ تلقائياً لجميع الخلايا التي اسفل هذه الخلية حتي اخر خلية تحتاج ان يكون بها المعادلة

Advertisements

 

قد تسأل هنا كيف يعرف الاكسل اخر خلية اريد ان انسخ بها المعادلة ؟!
حقيقة الامر ان آلية عمل هذه الميزة تعتمد على أنها تقوم بنسخ المعادلة حتي آخر خلية يوجد بالخلايا التي بجانبها محتوي اي انه لا ينسخ المعادلة عندما يكون الخلايا بجانبها فارغ .

تطبيق او نسخ المعادلة في نفس الصف

بعد ان تقوم بكتابة المعادلة نقوم بالمرور امام المربع الصغير الموجود يسار او يمين الخلية _حسب اتجاه الشيت_ وسيظهر هذا المؤشر مؤشر النسخ في excelبدلاً من مؤشر الماوس

تقوم بالضغط مع السحب ليمين او يسار الخلية حسب الخلايا المطلوب نسخ المعادلة بهاتكرار المعادلة في نفس الصف

وستظهر لنا نتيجة المعادلات فور نسخها

معلومة عل الماشي .. لمعرفة او تعديل نطاق اي معادلة يمكنك ذلك من خلال النقر مرتين علي الخلية وسيظهر النطاق الخاص بالمعادلة او الخلايا التي تضمها المعادلة .. ويمكنك حينها اذا اردت تعديل نطاق المعادلة من خلال التحديد يدوياً .
Advertisements

آلية عمل نسخ المعادلة في excel

يجب ان نفهم آلية عمل تكرار المعادلة الحسابية في excel وهي عندما نقوم بالسحب لاسفل يقوم برنامج excel تلقائياً بعمل نسخ copy للمعادلة ولصقها paste في الخلايا الآخري ولكن اذا كان النسخ واللصق بنفس اسماء الخلايا بالطبع ستكون النتيحة خاطئة .. اذاً ما الذي يحدث عندما نقوم بهذه العملية وتكون النتيجة صحيحة ؟!

والاجابة هي انه عندما نقوم بالسحب لاسفل او لأعلى يقوم excel تلقائياً بتثبيت اسم العمود
فمثلا اذا كانت المعادلة التي نريد تكرارها هي    sum(b3:g3)=   سنلاحظ بعد عمل تكرار للمعادلة ان حرف b وحرف g لم يتم تغييره حيث ان هذه الحروف تدل علي اسماء الاعمدة  .. وان ما يتغير هو الرقم 3 وهو الصف

اذا فهناك قاعدة هامة يجب ان نعرفها فعند السحب لأسفل او لأعلى يتم تلقائياً تثبيت اسم العمود في المعادلة ( وهي الحروف ) وذلك لأننا نتحرك في نفس العمود
وعند السحب او التحرك لليمين او اليسار يتم تلقائياً تثبيت اسم الصف في المعادلة ( وهي الارقام )

 

للمزيد عن تعريف الخلية والفرق بين الصفوف والأعمدة في برنامج Excel

 

>> والآن بعد استيعاب هذا الدرس جيداً يجب عليك الانتقال للدرس التالي لمعرفة كيفية تثبيت الخلية بالنقر هنا وشرح كامل وتطبيقي عن حالاتها المختلفة فهو درس هام ايضاً ولا يمكن الاستغناء عن إستخدام تثبيت الخلايا في العمل علي excel .

مواضيع قد تُهمك ايضاً :

شرح مفصل عن اهم الدوال في excel للمبتدئين

اهم الدوال في excel للمبتدئين

دوال excel

معادلات excel او دوال excel هي ببساطة عبارة عن كلمات يتم استخدامها بطريقة ما لعمل وظيفة معينة في شيت الاكسل ، فمثلاً كلمة SUM تعني مجموع وهي تقوم بوظيفة الجمع في اكسل تستخدم بطريقة معينة سنتعرف عليها بعد قليل ، لذلك تعتبر الدوال من الادوات الرئيسية التي لايمكن الاستغناء عنها في برنامج excel لأداء الكثير من المهام ، وفي نفس الوقت استخدامها سهل وبسيط فبمجرد معرفة وفهم طريقة كتابة المعادلة و وظيفتها ستستطيع كتابة اي معادلة في اكسل بنجاح ، وبطبيعة الحال يحتوي برنامج excel علي المئات من الدوال لذلك سنتعرف في هذا الموضوع عن اهم الدوال في excel للمبتدئين والتي تعتبر دوال excel الاكثر استخداماً .

اهم الدوال في excel

Advertisements

تعتبر من اهم دوال اكسل وهي الدوال التي تقوم بالمهام الاساسية مثل الجمع – الطرح – الضرب – القسمة – ايجاد متوسط القيم –  عدد القيم الرقمية – ايجاد اقل قيمة او اعلي قيمة لمجموعة من القيم

 

يمكنك الاطلاع على : شرح برنامج excel للمبتدئين بالصور من الصفر

دالة SUM للجمع

تعتبر SUM من اهم الدوال في excel  والتي تقوم بوظيفة الجمع التلقائي لمجموعة من الخلايا في الصف او العمود ايا كان عدد الخلايا المطلوب جمع القيم التي بداخلها .

 

طرق استخدام دالة SUM

الطريقة الاولي : هي ان تقوم بالخطوات الاتية :

1- بتحديد او تظليل الخلايا المطلوب ايجاد جمعها

2- ثم الضغط علي اداة Auto sum جمع تلقائي الموجودة في قسم editing تحرير في القائمة الرئيسية  Home

وسنجد تلقائيا انه تم ايجاد جمع الخلايا المحددة  وتم كتابة المجموع اسفل هذه الخلايا

استخدام دالة sum

 

بعد كتابة المعادلة والضغط علي enter تظهر نتيجة المعادلة وتختفي تفاصيل المعادلة .. لرؤية تفاصيل المعادلة ضغطة واحدة علي الخلية التي بها المجموع ستظهر المعادلة في هذه الخانة

رؤية تفاصيل المعادلة في excel

 

لمعرفة الفرق بين الصف والعمود في اكسل قم بزيارة هذا الموضوع

الطريقة الثانية لاستخدام دالة SUM :

هي ان نقوم بالضغط على الخلية التي تُريد ان يكون بها المجموع ثم الضغط علي اداة auto sum جمع تلقائي وستجد ان برنامج اكسل قام بتحديد جميع الخلايا التي تسبق هذه الخلية اذا كان صف او عمود

وبعد الضغط علي enter يظهر المجموع مباشرة

استخدام دالة sum

 

الطريقة الثالثة لاستخدام دالة SUM :

وذلك عن طريق كتابة صيغة المعادلة يدوياً ، وبالرغم من ان هذه الطريقة هي اصعبهم ولكن يجب ان تعرفها انت كمبتدئ وفي بداية مشوارك مع برنامج excel قد تتسائل لماذا يجب ان اعرفها ؟!

واقول لك حتي تتعرف علي كيفية بناء معادلة excel وماهي المكونات الاساسية للمعادلة والامر الذي سيُفيدك مستقبلاً لكتابة المعادلات الاحترافية ياصديقي ..

 

والطريقة كالاتي:

1- كتابة علامة اليساوي =

2- ثم كلمة sum ( لايشترط حروف كبيرة ام صغيرة )

3- ثم فتح المعادلة من خلال قوس الفتح (  _ عن طريق الضغط علي زر shift مع زر 9 الموجود في لوحة الارقام العلوية في الكيبورد

4- ثم قم بكتابة اسم اول خلية في العمود او الصف المطلوب جمعه ولتكن مثلاً A2

5- ثم اكتب علامة النقطتين : ( من خلال الضغط علي زر shift مع زر حرف ك )

6- ثم اكتب اسم اخر خلية في العمود او الصف المطلوب جمعه وليكن A14

7- قم الان بغلق المعادلة من خلال قوس الغلق وهو ) _ عن طريق الضغط علي زر shift مع زر 0 الموجود في لوحة الارقام العلوية في الكيبورد _

8- قم بالضغط علي enter ليظهر لك المجموع مباشرة

ويمكن ايضا تحديد الجزء المطلوب جمعه يدوياً من خلال القيام بتظليل الجزء المطلوب جمعه بعد فتح قوس المعادلة  بدلا من كتابة اسماء الخلايا

ليظهر لك شكل المعادلة كالتالي :

(A2:A14)SUM=

شرح المعادلة :

حيث ان A2 هي اول خلية و A14 هي اخر خلية وسيتم جمع جميع الخلايا من A2 حتي A14

اما اذا كنت تريد ان تجمع مجموعة من الخلايا المتفرقة اي ان لا نريد جمع جميع الخلايا التي بين اول واخر خلية نقوم بعمل الاتي :

بعد فتح قوس المعادلة قم بتحديد الخلايا المطلوب جمعها مع الاستمرار على الضغط علي زر ctrl حتي تنتهي من التحديد ثم الضغط علي enter

ليظهر لك شكل المعادلة كالتالي :

اذا كان الجزء المطلوب جمعه عبارة عن خلايا متفرقة

SUM(F9:H9,J9:K9,M9:N9)=

شرح المعادلة :

وهو يعني انه سيتم جمع الخلايا في الصف بين F9 حتي H9  ثم جمع من الخلية J9 حتي K9 ثم جمع من الخلية M9 حتي N9

حيث ان علامة ,    تقوم بالفصل بين الخلايا المطلوب جمعها .

 

للمزيد عن: كيفية عمل معادلة جمع في excel

Advertisements

عمل معادلة اعتماداً علي القيم الرقمية

بمعني ان المعادلة ستكون معادلة تحتوي على ارقام ونستخدم العلامات الحسابية المعتادة علامة الجمع والطرح والضرب والقسمة

مثال :

=12+4-(6*2)

4 وهي تعني اننا نريد جمع رقم 12 علي ناتج طرح 4 من  نتيجة ما بالاقواس (6في2) وهو 12 وستكون النتيجة

طريقة عمل معادلة لقيم رقمية

والطريقة بسيطة وهي كالاتي كتابة علامة اليساوي = ثم كتابة ارقام معادلتك .. مع الاخذ في الاعتبار الآتي :

لعمل معادلة ضرب في excel : علامة الضرب هي علامة النجمة * والتي يمكن كتابتها عن طريق الضغط علي زر shift مع زر رقم 8 الموجود في لوحة الارقام العلوية بالكيبورد  ..

 

ولعمل معادلة قسمة في excel : علامة القسمة في اكسل هي الشرطة المائلة لليمين /  الموجودة في حرف الظاء ويتم كتابتها من خلال تحويل الكيبورد الي اللغة الانجليزية ثم الضغط علي حرف الظاء

 

ولعمل معادلة طرح في excel : علامة الطرح هي علامة الشرطة الصغيرة – والتي يمكن كتابتها من خلال الضغط علي الزر الموجود بعد زر 0 في لوحة الارقام العلوية

 

ولعمل معادلة جمع في excel : وهي بالطبع علامة الزائد + يمكن كتابتها من خلال الضغط علي shift  مع زر اليساوي =

 

بعد كتابة المعادلة اضغط علي enter لرؤية نتيجة المعادلة فوراً .

ملحوظة : يمكن الجمع بين استخدام العلامات الحسابية الجمع والطرح والضرب والقسمة  ومعادلة SUM في معادلة واحدة مع مراعاة استخدام الاقواس في الاماكن المناسبة.

Advertisements

عمل معادلة اعتماداً علي قيم الخلايا

وذلك من خلال استخدام اسماء الخلايا بدلا من الارقام التي في الطريقة السابقة

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

مثال

=G13+G16-(G17/I14)

شرح المعادلة : 

وتعني المعادلة اننا نريد جمع محتوي الخلية G13 علي محتوي الخلية G16 ثم طرح الناتج من ناتج قسمة محتوي الخليةG17  علي الخلية I14

طريقة عمل معادلة لقيم الخلايا

والطريقة كالاتي اضغط علي علامة اليساوي = ثم اختر الخلايا يدوياً او كتابياً مع استخدام العلامات الحسابية التي تم شرحها في الطريقة السابقة

مع العلم ان :

  • يمكن اضافة قيم رقمية (ارقام عادية) في الدالة بدون اي مشكلة
  • يمكن الدمج بين دالة sum او اي دالة ضمن هذا النوع من صيغ المعادلات مثال :

=G17*(I14/10)+SUM(K8:K15)

دالة لايجاد متوسط مجموعة من القيم Average المتوسط

ووظيفة دالة Average هي ايجاد متوسط مجموعة من القيم ( قيم الخلايا ) ، والمتوسط في الرياضيات هو مجموع القيم مقسوم علي عدد هذه القيم.

المتوسط= مجموع القيم / عددهم

طريقة استخدام دالة average المتوسط هي 

1- اختر الخلية التي سيتم كتابة المتوسط بها

2- اضغط علي السهم الصغير الموجود بجوار Auto sum جمع تلقائي لتظهر لك قائمة قم باختيار منها average المتوسط

استخدام دالة average المتوسط

3- سيقوم تلقائياً بتحديد الخلايا التي تسبقها قم بالضغط علي enter للتأكيد علي هذه الخلايا او قم بتحديد الخلايا يدوياً ثم الضغط علي enter

ستكون المعادلة بالشكل التالي

AVERAGE(M8:M10)=

شرح المعادلة :

يعني اننا نريد ايجاد المتوسط للخلايا من الخلية M8 الي الخلية M10

ويمكنك ايضا استخدام اي طريقة من الطرق المذكورة في دالة Sum لكتابة المعادلة .

Advertisements

دالة لايجاد عدد القيم الرقمية لمجموعة من الخلايا count numbers عدد الارقام

تستخدم دالة count numbers عندما نكون بحاجة الي معرفة وايجاد عدد الخلايا الموجود بها قيم رقمية فقط ، اي انها لا تحتسب الخلايا الفارغة او الخلايا التي تحتوي علي محتوي نصي

طريقة استخدام دالة count numbers عدد الارقام هي 

1- اختر الخلية التي سيتم كتابة العدد بها

2- اضغط علي السهم الصغير الموجود بجوار Auto sum جمع تلقائي لتظهر لك قائمة قم باختيار منها count numbers عدد الارقام

دالة count numbers عدد الارقام

3- سيقوم تلقائياً بتحديد الخلايا التي تسبقها قم بالضغط علي enter للتأكيد علي هذه الخلايا او قم بتحديد الخلايا يدوياً ثم الضغط علي enter

ستظهر المعادلة بالشكل التالي :

COUNT(M8:M13)=

شرح المعادلة : 

تعني اننا نريد ايجاد عدد الخلايا الموجود بها ارقام من الخلية M8 الي الخلية M13

ويمكنك ايضا استخدام اي طريقة من الطرق المذكورة في دالة sum لكتابة المعادلة .

الدوال المسئولة عن ايجاد اعلي قيمة او اقل قيمة دالة max أكبر قيمة و دالة min اقل قيمة

احيانا نكون بحاجة الي معرفة اي من القيم الموجودة في الخلايا هي اكبر قيمة او اي منها هي اقل قيمة ، فعندما نحتاج الي معرفة اي اكبر قيمة نستخدم دالة max وعندما نحتاج الي معرفة اي اقل قيمة نستخدم دالة min

 

طريقة استخدام دالة max أكبر قيمة او دالة min اقل قيمة هي 

1- اختر الخلية التي سيتم كتابة القيمة المطلوبة بها

2- اضغط علي السهم الصغير الموجود بجوار Auto sum جمع تلقائي لتظهر لك قائمة قم باختيار منها max لأكبر قيمة أو دالة min لأقل قيمة

استخدام دالة Max ودالة Min

3- سيقوم تلقائياً بتحديد الخلايا التي تسبقها قم بالضغط علي enter للتأكيد علي هذه الخلايا او قم بتحديد الخلايا يدوياً ثم الضغط علي enter

ستظهر دالة Max بالشكل التالي :

MAX(M8:M14)=

شرح المعادلة :

هي اننا نريد معرفة ما هي اعلى قيمة في نطاق الخلايا المحددة من الخلية M8 الي الخلية M14

 

ولايجاد اقل قيمة نستخدم دالة Min

لتظهر دالة Min بالشكل التالي :

Min(M8:M14)=

شرح المعادلة :

هي اننا نريد معرفة ما هي اقل قيمة في نطاق الخلايا الخلايا المحددة من الخلية M8 الي الخلية M14

Advertisements

لمسح الدالة كاملاً او جزء منها

ولمسح اي دالة اخرج برة نطاق كتابة الدالة اولاً بالضغط علي enter ثم اضغط علي الخلية المطلوب حذف محتواها ضغطة واحدة ثم اضغط على زر delete

اما لحذف جزء من الدالة قم بالضغط علي الخلية ثم انتقل للخانة العلوية التي يظهر بها تفاصيل الدالة وقم بحذف ما تريده من الدالة .. علماً بان اي حذف غير منطقي من الدالة ( مثلا حذف علامة اليساوي ) يؤدي الي ظهور خطأ .

 

وهكذا قد انتهينا من معرفة اهم الدوال في excel وكيفية كتابة المعادلات واستخدامها والتي يجب ان يتعرف علي اي مبتدئ في بداية مشواره مع تعلم برنامج excel ، بالطبع هناك الكثير والكثير من دوال excel يمكن معرفتها بشكل سطحي من هذا الرابط ولكننا سنتعرف عليها بشئ من التعمق والتطبيق العملي والامثلة مع ذكر اهم وظائف واستخدامات كل دالة علي حدي في الدروس القادمة بإذن الله.

 

مواضيع ذات صلة :

كل ما تريد معرفته عن كيفية عمل معادلة جمع في excel

كيفية عمل معادلة جمع في excel

بطبيعة الحال فنحن نحتاج الي اجراء عملية الجمع أيا كان نوع المجال الذي نستخدم برنامج Microsoft excel من أجله ، حيث انه يتميز بسرعة اجراء عملية الجمع وتعدد طرق الجمع التي تناسب الاستخدامات المتنوعة فهناك طريقتين لإجراء عملية جمع في اكسل كل طريقة لها استخداماتها الخاصة حيث ان استخدام طريقة جمع القيم مُقتصر على جمع عدد قليل من العناصر او الحالات القليلة التي لا يمكن استخدام فيها دالة sum اما دالة sum فهي الأكثر شهرة واستخداماً في برنامج اكسل نظراً لمرونتها وسهولة استخدامها .. لذلك سنتعرف في هذا الدرس عن جميع طرق الجمع في Excel و طريقة الجمع التلقائي auto sum بالإضافة الي كيفية عمل معادلة جمع بالتفصيل .

طريقة الجمع من خلال دالة SUM

Advertisements

وهي الأكثر استخداما والتي يجب معرفتها لأنها تقوم بإجراء عملية الجمع بطريقة سريعة لمجموعة من الخلايا ، جمع لعدد لانهائي من القيم الموجودة في الخلايا التي في نطاق معين بضغطة زر وذلك من خلال استخدام معادلة SUM او المجموع

اذاً فمعادلة SUM هي المعادلة المسؤولة عن الجمع في excel

كيفية استخدام دالة الجمع التلقائي AutoSum

هناك أربعة طرق للوصول الي واستخدام دالة الجمع التلقائي في اكسل وهما :

الطريقة الأولى : من خلال تبويب Home الشريط الرئيسي ( وهي الطريقة الأكثر استخداماً )

يُعتبر الخيار الرئيسي في برنامج الاكسل بخصوص دالة الجمع هو دالة الجمع التلقائي auto sum ، وذلك لأنه موجود في تبويب الشريط الرئيسي Home في قسم editing تحرير كما هو موضح بالصورة التالية

اداة الجمع التلقائي autosum

 

 

الطريقة الثانية : من خلال تبويب Formulas الصيغ

ويمكنك ايجاده أيضا في تبويب Formulas او الصيغ كما هو موضح بالصورة التالية :

اداة الجمع التلقائي autosum في قائمة formulas

بقا لنا طريقتين احترافيتين بعض الشيء سنتعرف عليهم بعد قليل …

 

شرح عمل عملية جمع من خلال دالة الجمع التلقائي autosum 

لعمل عملية جمع تلقائي نقوم بعمل الاتي 

1- بالتوجه للخلية اسفل الخلايا المطلوب جمعها ثم نقوم باختيار autosum الجمع التلقائي من خلال أياً من الطريقتين

2- سنلاحظ ان الدالة تلقائياً قامت بتحديد الخلايا إلي فوق والي مطلوب جمعها

دالة الجمع التلقائي في اكسل

لتظهر الدالة بالشكل التالي

=sum( B2:B7 )

بعدها قم الضغط علي enter وسيظهر لك اجمالي الخلايا

وما بين القوسين يعني اننا نريد جمع القيم الموجودة من الخلية B2 حتــي الخلية B7

للتوضيح اكثر فإن هذه المعادلة تساوي الشكل التقليدي للجمع وهو :

=b2+b3+b4+b5+b6+b7

فمعنى هذا اننا اختصرنا شكل المعادلة من هذا الشكل

=b2+b3+b4+b5+b6+b7

الي هذا الشكل المبسط

=sum(b2:b7)

وبالطبع النتيجة واحدة

Advertisements

طرق احترافية لاستخدام دالة الجمع التلقائي

الطريقة الثالثة  : اختصار دالة الجمع التلقائي Autosum

يمكن الوصول الي دالة الجمع التلقائي autosum بسهولة من خلال الضغط علي زر Alt و علامة يساوي = ثم الضغط علي enter

 

الطريقة الرابعة : كتابة صيغة دالة sum يدوياً

وهي من خلال كتابة صيغة الدالة يدوياً أي بعد اختيار الخلية التي نريد ان يظهر بها الإجمالي نكتب علامة يساوي = ثم sum ثم الضغط على زر tab بالكيبورد بحيث تظهر المعادلة كالاتي

=sum(

ثم نقوم بتحديد الخلايا المطلوب تجميعها ثم اضغط على enter

 

وللمزيد عن : كيفية عمل جدول على Excel وتنسيقه بالصور

 

في كلتا الطريقتين معادلة sum تقوم بجمع عمود او صف

مثال :
– تحديد بسيط يجمع قيم الصف

= SUM (C2: H2)

– تحديد بسيط يجمع قيم العمود

= SUM (A3: A10)

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

= SUM (A3: A7، A10، A14: A17)

وهي تعني ان سيتم الجمع من الخلية A3 الي A7 ويتخطى الخلية A8 و A9 ثم يضيف اليها الخلية A10 ويتخطى A11 و A12 و A13 ويضيف من الخلية A14 الي الخلية A17

– يمكننا القسمة او الضرب أيضاً في معادلة SUM .. علما ان علامة / هي علامة القسمة وعلامة * هي علامة الضرب .. فمثلا :

= SUM (B2: B9) / 5

طريقة جمع القيم

Advertisements

وهي الطريقة التقليدية للجمع وتعتمد علي عمل معادلة لجمع قيم الخلايا .. وذلك من خلال الاتي :
الضغط علي علامة يساوي = و اختيار الخلية الأولى المطلوب جمعها ثم كتابة علامة الجمع + واختيار الخلية الثانية المطلوب جمعها ثم علامة + واختيار الخلية الثالثة ثم علامة + واختيار الخلية الرابعة وهكذا حتي الانتهاء من الخلايا المطلوب جمعها وبعد الانتهاء قم بالضغط على enter

لتظهر المعادلة بالشكل التالي مع اختلاف أسماء الخلايا

مثال : 

=a1+a2+a3+a4

امثلة علي طريقة جمع قيم 

مثال علي عمل معادلة اعتماداً علي جمع القيم الرقمية

=5+2
والنتيجة هنا ستكون 7

مثال علي عمل معادلة اعتماداً علي جمع قيم الخلايا

=A2+C9

مثال علي عمل معادلة تضم جمع قيم الخلايا والطرح وضرب القيم الرقمية وجمعها
= B5 + B2- (6 * 3) +1

في الحالتين السابقتين ستكون النتيجة معتمدة علي القيم الموجودة بالخلايا المذكورة في المعادلة

 

يمكنك الاطلاع علي : أهم 8 أساسيات لتعلم إدخال معادلات Excel للمبتدئين

Advertisements

طريقة الجمع بدون استخدام معادلات

ويمكن إيجاد اجمالي مجموعة من الأرقام بسرعة وبدون استخدام أي معادلة وهي من خلال عمل تحديد select عليها وسيظهر الإجمالي تلقائيا اسفل في شريط الحالة status bar بجانب كلمة sum

و Count تشير الي عدد الخلايا التي تم تحديدها وتحتوي علي قيم

و Average تشير الي المتوسط وهو يساوي قسمة SUM علي COUNT

الجمع بدون استخدام معادلات
موضوعات ذات صلة :

أهم 8 أساسيات لتعلم إدخال معادلات excel للمبتدئين

أهم 8 أساسيات لتعلم إدخال معادلات excel للمبتدئين

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

قم بالاطلاع على : اساسيات Excel للمبتدئين

المصطلحات الأساسية في Excel

هناك طريقتان أساسيتان لإجراء العمليات الحسابية في Excel .. الصيغ و الدوال او المعادلات.

نظام الصيغ

يُقصد بالصيغ او القيم الموجودة داخل الخلايا وهو عبارة عن اجراء عملية حسابية بسيطة ( جمع او طرح او ضرب او قسمة ) بين مجموعة من الخلايا .. مثلا

=A1+A2-A3*(A4/A5)

حيث ان علامة النجمة * تعني عملية ضرب

وعلامة / تعني عملية قسمة

الدوال او المعادلات

المعادلات عبارة عن صيغ محددة مسبقًا في Excel. حيث أنها تلغي الإدخال اليدوي الشاق للصيغ بينما تمنحها أسماء سهلة الاستخدام والتذكر. على سبيل المثال:

= SUM (A1: A3)

تجمع المعادلة جميع القيم من A1 إلى A3.

Advertisements

تعرف ايضا عن : كيفية عمل جدول على Excel وتنسيقه بالصور

كيفية اضافة معادلات excel

من اساسيات المعادلة في excel هو اسم الخلية او عنوانها اي موقعها في شيت الاكسل لذلك يجب ان نعرف ان اسم الخلية يتكون من حرف ورقم ( A1 ) .. الحرف هو اسم العمود الموجود به الخلية والرقم هو رقم الصف الموجود به الخلية .. فالخلية تعتبر تقاطع بين عمود وصف يتغير لون اسم العمود ورقم الصف عند الضغط على خلية معينة .

 

وللمزيد عن ماهي  اسم الخلية وكيفية معرفة اسم الخلية ايا كان موقعها تَفضل بزيارة هذا الموضوع

اساسيات ادخال معادلة في excel

1- كتابة علامة = اولاً ثم كتابة الارقام او الرموز ( اسماء الخلايا ) او المعادلات 

2- يجب عليك كـمُبتدئ ملاحظة ومتابعة دائما المستطيل الذي يقوم بعرض تفاصيل المعادلة عند كتابة المعادلة وهذا المستطيل موجود بالاعلي والمُشار إليه في الصورة التالية :

مربع محتوي الخلية في excel

مربع محتوي الخلية في excel

Advertisements

3- لسنا بحاجة الي ادخال اي مسافة space في المعادلة.

4- الخلية التي تقوم بكتابة بها المعادلة هي التي سيظهر بها نتيجة المعادلة.

5- معرفة اهم الدوال في excel مثل  sum و average  و COUNT و  MAX & MIN و IF ، ولاحظ ايضاً عند كتابة اول حرفين او ثلاثة من اسم الدالة سيظهر لك قائمة منسدلة بالدوال التي تبدأ بهذه الحروف يمكنك اختيار الدالة المقصودة من خلال الاسهم في الكيبورد ثم الضغط على زر tab لاسخدامها .

وللمزيد عن:  شرح اهم الدوال في excel للمبتدئين

 

6- علامة الدولار $ ( Dollar sign ) لها وظيفة هامة جدا في المعادلة وهي تثبيت خلية معينة عند تكرار المعادلة في اكثر من خلية  .. وللمزيد عن تثبيت الخلية في Excel قم بزيارة هذا الموضوع.

7- يمكن في excel الدمج بين اكثر من معادلة او عملية حسابية بدون اي مشكلة بشرط عدم التعارض مع بعضها البعض واحترام القوانين الاساسية للرياضيات مثل التعامل مع الاقواس والضرب والقسمة والجمع والطرح ومنطقية المعادلة.

8- بعد الانتهاء من كتابة اي معادلة يجب الضغط علي enter حتي تظهر النتيجة.

 

مواضيع ذات صلة :