اكسل

صيغ ووظائف Excel

Excel Formulas Functions

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



ما هي الصيغة؟

الصيغة في Excel هي تعبير يُرجع نتيجة معينة. على سبيل المثال:

 
=1+2 // returns 3

مثال الصيغة الأساسية - 1 + 3 = 3





 
=6/3 // returns 2

مثال الصيغة الأساسية - 6/3 = 2

ملاحظة: يجب أن تبدأ جميع الصيغ في Excel بعلامة يساوي (=).



مراجع الخلايا

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

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

 
=A1+A2+A3 // returns 9

صيغة مع مراجع الخلايا

لاحظ أننا نستخدم مراجع الخلايا لـ A1 و A2 و A3 ، يمكن تغيير هذه القيم في أي وقت وسيظل C1 يعرض نتيجة دقيقة.

جميع الصيغ ترجع نتيجة

تُرجع جميع الصيغ في Excel نتيجة ، حتى عندما تكون النتيجة خطأ. يتم استخدام الصيغة أدناه لحساب النسبة المئوية للتغيير. تُرجع الصيغة النتيجة الصحيحة في D2 و D3 ، لكنها تُرجع # DIV / 0! خطأ في D4 ، لأن B4 فارغ:

يمكن أن تكون نتيجة الصيغة خطأ

هناك طرق مختلفة للتعامل مع الأخطاء. في هذه الحالة ، يمكنك تقديم القيمة المفقودة في B4 ، أو 'التقاط' الخطأ بامتداد دالة IFERROR وعرض رسالة أكثر ودية (أو لا شيء على الإطلاق).

نسخ الصيغ ولصقها

تكمن ميزة مراجع الخلايا في أنها يتم تحديثها تلقائيًا عند نسخ صيغة إلى موقع جديد. هذا يعني أنك لست بحاجة إلى إدخال نفس الصيغة الأساسية مرارًا وتكرارًا. في الشاشة أدناه ، تم نسخ الصيغة الموجودة في E1 إلى الحافظة باستخدام Control + C:

تم نسخ الصيغة الموجودة في E1 إلى الحافظة

أدناه: تم لصق الصيغة في الخلية E2 باستخدام Control + V. تم تغيير مراجع خلايا الإشعار:

تم لصق الصيغة في E1 على E2

تم لصق نفس الصيغة على E3. يتم تحديث عناوين الخلايا مرة أخرى:

تم لصق الصيغة في E1 على E3

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

تسمى مراجع الخلية أعلاه نسبيا المراجع. هذا يعني أن المرجع مرتبط بالخلية التي يعيش فيها. الصيغة في E1 أعلاه هي:

 
=B1+C1+D1 // formula in E1

حرفيا ، هذا يعني أن 'الخلية 3 أعمدة يسار' + 'عمود الخلية 2 يسار' + 'عمود الخلية 1 يسار'. لهذا السبب ، عندما يتم نسخ الصيغة إلى الخلية E2 ، فإنها تستمر في العمل بنفس الطريقة.

تعتبر المراجع النسبية مفيدة للغاية ، ولكن هناك أوقات لا تريد فيها تغيير مرجع الخلية. يُطلق على مرجع الخلية الذي لا يتغير عند نسخه اسم المرجع المطلق . لعمل مرجع مطلق ، استخدم رمز الدولار ($):

 
=A1 // relative reference =$A // absolute reference

على سبيل المثال ، في الشاشة أدناه ، نريد مضاعفة كل قيمة في العمود D في 10 ، والتي يتم إدخالها في A1. باستخدام مرجع مطلق لـ A1 ، فإننا 'نقفل' هذا المرجع حتى لا يتغير عند نسخ الصيغة إلى E2 و E3:

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

فيما يلي الصيغ النهائية في E1 و E2 و E3:

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

لاحظ أن المرجع إلى D1 يتم تحديثه عند نسخ الصيغة ، لكن المرجع إلى A1 لا يتغير أبدًا. يمكننا الآن بسهولة تغيير القيمة في A1 ، وإعادة حساب الصيغ الثلاث. أقل من القيمة في A1 تغيرت من 10 إلى 12:

تم تغيير مثال المرجع المطلق بعد القيمة في A1

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

نصيحة: يمكنك التبديل بين البنية النسبية والمطلقة باستخدام ملف مفتاح F4 .

كيف تدخل صيغة

لإدخال صيغة:

  1. حدد خلية
  2. أدخل علامة يساوي (=)
  3. اكتب الصيغة ، واضغط على إدخال.

بدلاً من كتابة مراجع الخلية ، يمكنك الإشارة والنقر ، كما هو موضح أدناه. مراجع الملاحظات مرمزة بالألوان:

إدخال صيغة مع نقطة وانقر فوق المراجع

يجب أن تبدأ جميع الصيغ في Excel بعلامة يساوي (=). لا علامة يساوي ، لا صيغة:

نسيت إدخال علامة يساوي يعني عدم وجود صيغة ، نص فقط

كيفية تغيير الصيغة

لتحرير صيغة ، لديك 3 خيارات:

  1. حدد الخلية وتحريرها في شريط الصيغة
  2. انقر نقرًا مزدوجًا فوق الخلية ، وقم بالتحرير مباشرةً
  3. حدد الخلية ، اضغط على F2 ، قم بالتحرير مباشرة

بغض النظر عن الخيار الذي تستخدمه ، اضغط على Enter لتأكيد التغييرات عند الانتهاء. إذا كنت تريد الإلغاء ، وترك الصيغة دون تغيير ، فانقر فوق مفتاح Escape.

فيديو: 20 نصيحة لإدخال الصيغ

ما هي الوظيفة؟

عند العمل في Excel ، سوف تسمع الكلمتين 'الصيغة' و 'الوظيفة' بشكل متكرر ، وأحيانًا بالتبادل. ترتبط ارتباطا وثيقا ، ولكن ليس بالضبط نفس الشيء. من الناحية الفنية ، الصيغة هي أي التعبير الذي يبدأ بعلامة يساوي (=).

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

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

ال دالة AVERAGE ، كما تتوقع ، تُرجع متوسط ​​المراجع المعطاة:

 
= AVERAGE (1,2,3) // returns 2

وتقوم الدالتان MIN و MAX بإرجاع القيم الدنيا والقصوى ، على التوالي:

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

يحتوي Excel على مئات الوظائف المحددة . للبدء ، انظر 101 وظائف Excel الرئيسية .

الحجج الوظيفية

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

 
=FUNCTIONNAME(argument1,argument2,argument3)

على سبيل المثال ، ملف دالة COUNTIF تحسب الخلايا التي تفي بالمعايير ، وتأخذ وسيطين ، نطاق و المعايير :

 
= COUNTIF (range,criteria) // two arguments

في الشاشة أدناه ، النطاق هو A1: A5 والمعيار 'أحمر'. الصيغة في C1 هي:

 
= COUNTIF (A1:A5,'red') // returns 2

تتطلب COUNTIF وسيطتين ، النطاق والمعايير

فيديو: كيفية استخدام دالة COUNTIF

ليست كل الحجج مطلوبة. الحجج الموضحة بين قوسين مربعين اختيارية. على سبيل المثال ، ملف YEARFRAC (الدالة YEARFRAC) تُرجع عدد كسور من السنوات بين تاريخ البدء وتاريخ الانتهاء وتستغرق 3 وسيطات:

 
= YEARFRAC (start_date,end_date,[basis])

تاريخ البدء وتاريخ الانتهاء وسيطات مطلوبة ، والأساس هو وسيطة اختيارية. انظر أدناه للحصول على مثال حول كيفية استخدام YEARFRAC لحساب العمر الحالي بناءً على تاريخ الميلاد.

كيفية إدخال وظيفة

إذا كنت تعرف اسم الوظيفة ، فما عليك سوى البدء في الكتابة. فيما يلي الخطوات:

1. أدخل علامة يساوي (=) وابدأ في الكتابة. سيقوم Excel بإدراج وظائف المطابقة أثناء الكتابة:

أثناء الكتابة ، سيعرض Excel وظائف مطابقة

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

2. اكتب مفتاح Tab لقبول وظيفة. سوف يكمل Excel الوظيفة:

اضغط على Tab للدخول إلى الوظيفة المحددة

3. املأ الحجج المطلوبة:

أدخل الوسائط المطلوبة

4. اضغط على Enter لتأكيد الصيغة:

اضغط على Enter للتأكيد وأدخل الوظيفة

الجمع بين الوظائف (تداخل)

تستخدم العديد من صيغ Excel أكثر من وظيفة واحدة ، ويمكن أن تكون الدالات ' متداخل داخل بعضها البعض. على سبيل المثال ، يوجد أدناه تاريخ ميلاد في B1 ونريد حساب العمر الحالي في B2:

بحاجة إلى صيغة لحساب العمر الحالي في B2

ال YEARFRAC (الدالة YEARFRAC) سيحسب السنوات بتاريخ البدء وتاريخ الانتهاء:

سيحسب YEARFRAC السنوات بتاريخ البدء وتاريخ الانتهاء

يمكننا استخدام B1 لتاريخ البدء ، ثم استخدام ملف دالة TODAY لتقديم تاريخ الانتهاء:

B1 لتاريخ البدء ، الدالة TODAY لتوفير تاريخ الانتهاء

عندما نضغط على Enter للتأكيد ، نحصل على العمر الحالي بناءً على تاريخ اليوم:

 
= YEARFRAC (B1, TODAY ())

وظائف YEARFRAC و TODAY لحساب العمر الحالي

لاحظ أننا نستخدم وظيفة TODAY لإدخال تاريخ انتهاء لوظيفة YEARFRAC. بمعنى آخر ، يمكن أن تتداخل الدالة TODAY داخل دالة YEARFRAC لتوفير وسيطة تاريخ الانتهاء. يمكننا أن نأخذ الصيغة خطوة أخرى ونستخدم وظيفة INT لتقطيع القيمة العشرية:

 
= INT ( YEARFRAC (B1, TODAY ()))

YEARFRAC واليوم داخل INT

هنا ، تُرجع صيغة YEARFRAC الأصلية 20.4 إلى الدالة INT ، وتُرجع الدالة INT النتيجة النهائية البالغة 20.

ملحوظات:

  1. التاريخ الحالي في الصور أعلاه هو 22 فبراير 2019.
  2. متداخلة وظائف IF هي مثال كلاسيكي على وظائف التعشيش .
  3. ال دالة TODAY هي وظيفة Excel نادرة بدون وسيطات مطلوبة.

الوجبات الجاهزة الرئيسية: يمكن إدخال ناتج أي صيغة أو دالة مباشرة في صيغة أو دالة أخرى.

عوامل الرياضيات

يوضح الجدول أدناه معاملات الرياضيات القياسية المتوفرة في Excel:

رمز عملية مثال
+ إضافة = 2 + 3 = 5
- الطرح = 9-2 = 7
* عمليه الضرب = 6 * 7 = 42
/ قسم = 9/3 = 3
^ الأس = 4 ^ 2 = 16
() أقواس = (2 + 4) / 3 = 2

العوامل المنطقية

توفر العوامل المنطقية دعمًا للمقارنات مثل 'أكبر من' ، 'أقل من' ، إلخ. يتم عرض العوامل المنطقية المتوفرة في Excel في الجدول أدناه:

المشغل أو العامل المعنى مثال
= يساوي = A1 = 10
لا يساوي = A110
> أكثر من = A1> 100
< أقل من = A1<100
> = أكبر من أو يساوي = A1> = 75
<= اقل او يساوي = A1<=0

فيديو: كيفية بناء الصيغ المنطقية

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

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

  1. أقواس
  2. الدعاة
  3. الضرب والقسمة
  4. جمع وطرح
  5. سلسلة
  6. العوامل المنطقية

نصيحة: يمكنك استخدام ملف ميزة التقييم لمشاهدة برنامج Excel يحل الصيغ خطوة بخطوة.

تحويل الصيغ إلى قيم

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

فيديو: لصق الاختصارات الخاصة

كيفية نسخ البيانات من ورقة Excel إلى أخرى باستخدام vlookup

ماذا بعد؟

فيما يلي أدلة لمساعدتك في معرفة المزيد حول صيغ ووظائف Excel. كما نقدم تدريب بالفيديو عبر الإنترنت .

المؤلف ديف برونز


^