الدوال والمراجع | الوحدة الأولى | الدرس الثاني

الدوال والمراجع هو عنوان الدرس الثاني من الوحدة الأولى التي تحمل اسم “تحليل البيانات” في الفصل الدراسي الثاني من مقرر “المهارات الرقمية – ثاني متوسط”.

سنتعرف في هذا الموضوع على كيفية استخدام الدوال النصية في برنامج مايكروسوفت إكسل، بالإضافة إلى استخدام المراجع النسبية (Relative References)، والمراجع المطلقة (Absolute References)، والمراجع المختلطة (Mixed References)، وطريقة التعامل مع رسائل الخطأ (Error Messages) في البرنامج.

الدوال والمراجع

احرص على قراءة نواتج التعلُّم جيدًا قبل بداية دراستك للموضوع، وتأكد من تحصيلها بعد انتهائك من دراسته.

نواتج التعلُّم

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

هيا لنبدأ!

استخدام الدوال النصية | الدوال والمراجع

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

ومن أهم هذه الدوال النصية:

دالة التبديل (SUBSTITUTE):

الوصف: تُبدِّل جزءًا أو كامل النص في الخلية بآخر جديد وفق شرط مُحدَّد.

مثال: استبدل كلمة أو كامل النص في هذه الجملة بنص آخر: “أثر استخدام التقنية على حياتك”

(مثلًا: استخدم كلمة الإنترنت بدلًا من التقنية)

دالة اليسار (LEFT):

الوصف: تستخرج عددًا من الحروف على الجانب الأيسر من النص في خلية.

مثال: استخرج آخر كلمة في هذه الجملة: “أثر استخدام التقنية على حياتك”

(مثلًا: كلمة “حياتك”)

دالة الوسط (MID):

الوصف: تستخرج عددًا من الحروف من منتصف النص في خلية.

مثال: استخرج الكلمة في منتصف هذه الجملة: “أثر استخدام التقنية على حياتك”

(مثلًا: كلمة “التقنية”)

دالة اليمين (RIGHT):

الوصف: تستخرج عددًا من الحروف على الجانب الأيمن من النص في خلية.

مثال: استخرج أول كلمة في هذه الجملة: “أثر استخدام التقنية على حياتك”

(مثلًا: كلمة “أثر”)

معلومة

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

دالة التبديل (SUBSTITUTE)

إذا كنت تريد استبدال جزء من نص في الخلية، يُمكِنك استخدام دالة التبديل (SUBSTITUTE).

في المثال التالي ستبدل الأحرف الأولى من الاسم والعائلة للطلبة، بالاسم الأول واسم العائلة.

اكتب الجدول التالي، ونسِّقه كما هو موضح بالصورة التالية:

الدوال والمراجع

لاستخدام دالة التبديل (SUBSTITUTE):

اضغط على الخلية B2.
من علامة التبويب الصيغ (Formulas)، وفي المجموعة مكتبة الدالات (Function Library)، اضغط على نص (Text).
من القائمة، اضغط على دالة التبديل (SUBSTITUTE).
من نافذة وسيطات الدالة (Function Arguments)، وفي مربع النص (Text) اكتب A2، وهي الخلية التي تحتوي على جزء النص الذي ستقوم بتبديله.
في مربع النص القديم (Old_text) اكتب A2، وهي الكلمة التي تريد تغييرها.
في مربع النص الجديد (New_text) اكتب “أحمد وليد”، وهي الكلمة الجديدة.
اضغط على موافق (OK).
تم استبدال النص الخاص بك.
كرِّر نفس الخطوات مع الخلايا B3 وحتى B10، مع ملء كتابة الاسم واسم العائلة كما هو موضح في الصورة.

دوال اليسار (LEFT) والوسط (MID) واليمين (RIGHT)

تستخدم الدوال: اليسار (LEFT) والوسط (MID) واليمين (RIGHT) لاستخراج قيم اليوم والشهر والسنة من تاريخ الميلاد في أعمدة منفصلة، سيسمح لك ذلك بمزيد من نتائج النموذج، ليس فقط من خلال تاريخ الميلاد المُحدَّد، ولكن أيضًا بحسب السنة أو الشهر أو يوم الميلاد.

وللقيام بذلك، ستُضيف ثلاثة أعمدة جديدة بعد العمود C بعناوين: “اليوم”، “الشهر”، “السنة”.

لاستخدام دالة اليسار (LEFT):

اضغط على الخلية D2.
من علامة التبويب الصيغ (Formulas)، وفي المجموعة مكتبة الدالات (Function Library)، اضغط على نص (Text).
ثم اضغط دالة اليسار (LEFT).
من نافذة وسيطات الدالة (Function Arguments)، وفي مربع النص (Text) اكتب الخلية التي تحتوي على تاريخ الميلاد، ولاستخراج يوم الميلاد اكتب DAY(C2).
في مربع تحديد عدد الحروف المطلوب استخراجها (Num_Chars) اكتب 2.
اضغط على موافق (OK)، وستظهر النتيجة في الخلية D2.
استخدم أداة التعبئة التلقائية (Auto Fill) + لإكمال الجدول.

لاحظ أن

لتنسيق التواريخ في نطاق الخلايا C2:C10،

حدد نطاق الخلايا، ومن علامة تبويب الشريط الرئيسي (Home) ومجموعة رقم (Number)، اضغط على زر التوسيع.
من علامة التبويب رقم (Number)، حدد التاريخ (Data) من قائمة الفئة (Category).
ومن قائمة النوع (Type)، اختر تنسيق التاريخ، وسيتم معاينة التنسيق الخاص بك في مربع النموذج (Sample).

الدوال والمراجع

لاستخدام دالة الوسط (MID):

اضغط على الخلية E2.
من علامة التبويب الصيغ (Formulas)، وفي المجموعة مكتبة الدالات (Function Library)، اضغط على نص (Text).
ثم اضغط دالة الوسط (MID).
من نافذة وسيطات الدالة (Function Arguments)، وفي مربع النص (Text) اكتب الخلية التي تحتوي على تاريخ الميلاد، ولاستخراج الشهر اكتب MONTH(C2).
من صندوق بدء العد (Start_num) اكتب 1. (هذا هو موقع الحرف الأول الخاص بالشهر).
في صندوق تحديد عدد الحروف المطلوب استخراجها (Num_chars)، اكتب 2.
اضغط على موافق (OK)، وستظهر النتيجة في الخلية E2.
استخدام أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

الدوال والمراجع

لاستخدام دالة اليمين (RIGHT):

اضغط على الخلية F2.
من علامة التبويب الصيغ (Formulas)، وفي المجموعة مكتبة الدالات (Function Library)، اضغط على نص (Text).
ثم اضغط دالة اليمين (RIGHT).
من نافذة وسيطات الدالة (Function Arguments)، وفي مربع النص (Text) اكتب YEAR(C2) لاستخراج قيمة السنة من الحقل المحتوي على تاريخ الميلاد.
من صندوق تحديد عدد الحروف المطلوب استخراجها (Num_chars)، اكتب 4.
اضغط على موافق (OK)، وستظهر النتيجة في الخلية F2.
استخدام أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

الدوال والمراجع

نصيحة ذكية

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

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

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

تأخذ الخلية اسمها من حرف العمود ورقم الصف الذي ينتمي إليه، ويُعدُّ مَرجع الخلية عنوانًا للخلية ويُحدِّد موقعها، وعندما تريد نسخ الصيغة نفسها إلى خلايا جديدة، بإمكانك استخدام المَراجع النسبية (Relative References) والمَراجع المطلقة (Absolute References).

المَرجع النسبي (Relative Reference)

المَرجع النسبي هو مَرجع الخلية.

عند نسخ خلية تحتوي على صيغة، فإن الصيغة تتغير تلقائيًا، ويعتمد التغيير على المَوضع النسبي للصفوف والأعمدة.

مثال:

يتم ضرب سعر كل منتج في الكمية التي ستشتريها؛ لحساب السعر الإجمالي للمنتجات.

الدوال والمراجع

لاستخدام المَراجع النسبية:

اضغط على الخلية D2 واكتب B2*C2=.
اضغط على Ctrl + Enter للبقاء في الخلية النشطة.
استخدم أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

لا حظ أن

بإمكانك استخدام أمري النسخ (Copy) واللصق (Paste) بدلًا من استخدام أداة التعبئة التلقائية.

لاحظ كذلك أن

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

المَرجع المُطلق (Absolute Reference)

في بعض الأحيان تريد الاحتفاظ بخلية ثابتة عند نسخ صيغة، ويُمكِنك تنفيذ ذلك عند إنشاء الصيغة باستخدام علامة الدولار ($)، وبهذه الطريقة يُمكِنك إنشاء مَرجع مطلق لا يتغير عند نسخه أو عند استخدام التعبئة التلقائية.

مثال:

للمَرجع المطلق هو مَرجع الخلية $A$1، حيث إن الخلية لا تتغير عند نسخها، ويظل كل من العمود والصف ثابتين.

اكتب الجدول التالي ونسِّقه كما هو موضح.

لحساب سعر كل مُنتج بدون الضريبة، تُكتب الضريبة في الخلية F2. وعند نسخ الصيغة، يجب ألا يتم تعديل مرجع الخلية F2 وأن يظل مكانها ثابت من حيث حرف العمود ورقم الصف.

لاستخدام المَراجع المطلقة:

اضغط على الخلية B2 واكتب $F$2/C2=.
اضغط على Ctrl + Enter للبقاء في الخلية النشطة.
استخدم أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

يُمكِنك الضغط على الخلية التي تريد قفلها والضغط على F4 لتطبيق المَرجع المُطلق.

لاحظ أن

عندما يتغير رقم الصف، تظل الخلية التي بها علامة الدولار ($) كما هي.

بشكل أكثر تحديدًا، في حالة استخدام المَرجع المُطلق.

بتغيير الصف عند نسخ الصيغة $F$2/C3= لأسفل في باقي الخلايا، لا يتغيَّر رقم الصف 2 ولا حرف العمود F في أي مثال آخر، على سبيل المثال: في الخلية B3 تُصبح الصيغة $F$2/C3= وهكذا.

المَرجع المختلط (Mixed Reference) | الدوال والمراجع

يُشير المَرجع المُختلط (Mixed Reference) في مايكروسوفت إكسل إلى أن جزءًا من المَرجع مُثبَّت (Fixed)، إما الصف أو العمود، بحيث يكون الجزء الآخر نسبي.

وبخلاف المَراجع المُطلقة، يتم تطبيق علامة الدولار ($) واحدة فقط، إما أمام حرف العمود أو رقم الصف.

يأتي المَرجع المُختلط في أحد الشكلين التاليين:

$A1 تكون علامة الدولار ($) قبل حرف العمود، فيبقى العمود ثابتًا، ويسمى ذلك المَرجع المُطلق للعمود (Column Absolute Reference).
A$1 تكون علامة الدولار ($) قبل رقم الصنف، فيبقى الصف ثابتًا، ويسمى ذلك المَرجع المُطلق للصف (Row Absolute Reference).

اكتب الجدول التالي ونسِّقه كما هو موضح في الصورة التالية:

الدوال والمراجع

المَرجع المطلق للصف (Row Absolute Reference)

عليك حساب التكلفة النهائية لعدد من المنتجات، بعد الخصم الموجود في الخلية B7.

نظرًا لأنك ستنسخ الصيغة لأسفل في باقي الخلايا، فتتغيَّر الصفوف، ولكنك تريد أن يظل رقم صف مَرجع الخلية B7 ثابتًا.

لإنشاء صيغة ونسخها باستخدام المَرجع المطلق للصف:

اضغط على الخلية C2 واكتب B2*B$7=.
اضغط على Ctrl + Enter للبقاء في الخلية النشطة.
استخدم أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

بشكل أكثر تحديدًا، عند نسخ الصيغة B2*B$7= لأسفل في باقي الخلايا، فبتغيير الصف، لا يتغيَّر رقم الصف.

على سبيل المثال، في الخلية C3 تُصبح الصيغة B3*B$7= وهكذا.

المَرجع المطلق للعمود (Column Absolute Reference)

اكتب ونسِّق الجدول التالي:

الدوال والمراجع

عليك حساب التكلفة النهائية لعدد من المنتجات، بعد الخصم الموجود في G2.

نظرًا لأنك ستنسخ الصيغة إلى اليسار في باقي الخلايا، فتتغيَّر الأعمدة، ولكنك تريد أن يظل حرف العمود لمَرجع الخلية G2 ثابتًا.

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

اضغط على الخلية B3 واكتب B2*$G2=.
اضغط على Ctrl + Enter للبقاء في الخلية النشطة.
استخدم أداة التعبئة التلقائية (Auto Fill) لإكمال الجدول.

بشكل أكثر تحديدًا، عند نسخ الصيغة B2*$G2= إلى اليسار في باقي الخلايا، فبتغيير العمود، لا يتغيَّر العمود المَرجعي.

على سبيل المثال، في الخلية C3 تُصبح الصيغة C2$G2= وهكذا.

نصيحة ذكية

هناك طريقة سهلة لتذكُّر طريقة استخدام علامة الدولار ($) وهي التفكير في الطريقة التي تريد بها استخدام أداة التعبئة التلقائية.

إذا كنت تريد استخدامها أفقيًا، فاكتب علامة الدولار أمام الحرف (العمود)، وإذا كنت تريد استخدامها عموديًا، فاكتب علامة الدولار أمام الرقم (الصف).

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

رسائل الخطأ | الدوال والمراجع

عند استخدام مايكروسوفت إكسل لتنفيذ العمليات الحسابية، قد تحصل على نتائج، مثل: ####، أو 0!/#DIV، أو A!/#N، أو #VALUE!.

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

####
تظهر عندما تكون القيمة أو النص الذي تكتبه أكبر من الخلية، وعليك ضبط عرض العمود لإظهار جميع المعلومات.

0!/#DIV
تظهر عندما تحاول القسمة على 0، وعليك التحقُّق من الأرقام.

A!/#N
تظهر عندما لا يُمكِن للصيغة أو الدالة العثور على البيانات المَرجعية.

#NAME?
تظهر عندما لا يتم التعرُّف على النص الموجود في الصيغة.

#NULL!
تظهر عندما لا يتم فصل مَرجعي خلية أو أكثر بشكل صحيح في صيغة، وعليك التحقُّق من الصيغة واستخدام الفاصلة؛ لفصل مراجع النطاق (Range Reference).

#NUM!
تظهر عندما تحتوي الصيغة على بيانات رقمية غير صالحة لنوع العملية التي تحاول إجراء العملية الحسابية لها.

#REF!
تظهر عندما يكون المَرجع غير صالح، وعليك التحقُّق من الصيغة.

#VALUE!
عليك التحقُّق من طريقة كتابة الصيغة أو الخلايا التي تُشير إليها.

بإمكانك تصحيح الخطأ بالضغط على الزر الذي يظهر بجوار الخلية الذي يعرض الرسالة واختيار تحرير في شريط الصيغة (Edit in Formula bar).

الدوال والمراجع

قم بمراجعة محتوي هذا القسم بعنوان رسائل الخطأ من موضوع الدوال والمراجع من خلال الرابط التالي:

https://www.youtube.com/watch?v=CS–NHVTnRI

اختبر تحصيلك لمحتوى الموضوع من خلال الرابط التالي:

الواجب الإلكتروني

إلى هنا يكون قد انتهى موضوع “الدوال والمراجع”، لا تنسوا مراجعة نواتج التعلُّم أعلى المقال، وانتظرونا في الموضوع القادم!

روابط هامة

مقررات الفصل الدراسي الثاني

مشاركة المقال عبر:

الدوال والمراجع | الوحدة الأولى | الدرس الثاني

استخدام الدوال النصية | الدوال والمراجع