|
المراجع وطرق الإشارة إليها
بسم الله الرحمن الرحيم
يقوم المرجع بتعريف خلية أو نطاق من الخلايا الموجودة في ورقة العمل ، ويستخدم المرجع في مختلف الصيغ و الدوال
أمثلة :
=A4*8
صيغة تعمل على إظهار حاصل ضرب القيمة الموجودة في الخلية A4 بالعدد 8
=SUM(S4:K7)
دالة تستخدم لإظهار مجموع القيم المحصورة بين الخليتين S4 و K7
ملاحظات :
ـ يجب وضع إشارة = في بداية أي صيغة أو دالة ، وذلك حتى يتسنى للاكسل تميزها عن القيم و العناوين
ـ لا يقوم الاكسل بعرض الصيغ و الدوال بل يعرض نتيجتها ، على سبيل المثال إذا قمنا بإدخال صيغة لجمع العددين 5 و 7 فإن الاكسل يبقي عليها ويعرضها في شريط الصيغة عندما يتم تحديد الخلية ، بينما يقوم بإظهار النتيجة 12 في الخلية نفسها
هناك عدة أساليب للدلالة على المراجع ، نوردها فيما يلي :
أولاً : ترميز A1
يستخدم الاكسل افتراضيًا نمط المرجع A1 ، والذي يشير إلى الأعمدة بواسطة أحرف (من A إلى IV ، بإجمالي 256 عمودًا) وإلى الصفوف بواسطة أرقام (من 1 إلى 65536) . حيث تتم الإشارة إلى خلية ما بإدخال حرف العمود متبوعاً برقم الصف .
إذا كنت تريد كتابة مراجع في صيغك باستخدام هذا الترميز ، فإنك ستستخدم إحدى الأساليب المتبعة في الأمثلة التالية:
ـ المرجع F2 يستخدم للإشارة إلى الخلية في العامود F و الصف الثاني
ـ النطاق R4:R12 يستخدم للإشارة إلى نطاق الخلايا في العامود R و الصفوف من الرابع إلى الثاني عشر
ـ النطاق C6:Q6 يستخدم للإشارة إلى نطاق الخلايا في الصف السادس و الأعمدة من C إلى Q
ـ النطاق D10:E15 يستخدم للإشارة إلى نطاق الخلايا في الأعمدة من D إلى E ، و الصفوف من العاشر إلى الخامس عشر
ـ النطاق 7:7 يستخدم للإشارة إلى كافة الخلايا في الصف السابع
ـ النطاق 7:10 يستخدم للإشارة إلى كافة الخلايا في الصفوف من السابع إلى العاشر
ـ النطاق B:B يستخدم للإشارة إلى كافة الخلايا في العمود B
ـ النطاق B:F يستخدم للإشارة إلى كافة الخلايا في الأعمدة من B إلى F
ـ النطاق A1:C7 B3:D11 يستخدم للإشارة إلى نطاق الخلايا B3:C7 الناتج عن تقاطع النطاقين السابقين
ملاحظة :
ـ النطاق هو مرجع مؤلف من مجموعة خلايا متجاورة أو متباعدة
هناك دالة INDIRECT تستخدم لإرجاع نطاق محدد بواسطة سلسلة نصية ، وأهميتها تتلخص في إمكانية تغيير مرجع معين (في أي صيغة أو دالة) من قبل المستخدم دون الحاجة لتغيير الصيغة أو الدلة نفسها . لنقم بعمل المثال التالي :
نلاحظ إمكانية إرجاع قيمة أي خلية بإدخال رقم صفها وترتيب عامودها ، وبالنظر إلى شريط الصيغة في الشكل السابق نلاحظ استخدام العلامة & لدمج السلسلتين النصيتين .
ثانياً : ترميز R1C1
في النمط RICI يشير الاكسل إلى موقع خلية معينة بالحرف R يتبعه رقم الصف و الحرف C يتبعه رقم العمود
لتشغيل نمط R1C1 و استخدامه في الصيغ لا بد أن تقوم بما يلي :
1. انقر فوق الخيار Options من القائمة Tools ، و من ثم انقر فوق علامة التبويب General .
2. تحت Settings ، حدد خانة الاختيار R1C1 reference style . انظر إلى الشكل التالي (الجزء المظلل باللون الأحمر) :
إذا كنت تريد كتابة مراجع في صيغك باستخدام هذا الترميز ، فإنك ستستخدم إحدى الأساليب المتبعة في الأمثلة التالية:
ـ المرجع R2C4 يستخدم للإشارة إلى الخلية في العامود الرابع و الصف الثاني
ـ النطاق R يستخدم للإشارة إلى كافة خلايا الصف الحالي
ـ النطاق R5 يستخدم للإشارة إلى كافة الخلايا الصف الخامس
ـ النطاق R:R2 يستخدم للإشارة إلى كافة الخلايا من الصف الحالي إلى الصف الثاني
ـ النطاق R3:R6 يستخدم للإشارة إلى كافة الخلايا في الصفوف من الثالث إلى السادس
ـ النطاق C يستخدم للإشارة إلى كافة خلايا العامود الحالي
ـ النطاق C3 يستخدم للإشارة إلى كافة الخلايا العامود الثالث
ـ النطاق C:C4 يستخدم للإشارة إلى كافة الخلايا من العامود الحالي إلى العامود الرابع
ـ النطاق C7:C10 يستخدم للإشارة إلى كافة الخلايا في الأعمدة من السابع إلى العاشر
ـ النطاق R1C1:R9C6 يستخدم للإشارة إلى كافة الخلايا الموجودة في النطاق المحصور بين الخلية الواقعة في الصف الأول و العامود الأول و الخلية الواقعة في الصف التاسع و العامود السادس
ـ النطاق R1C1:R10C5 R4C3:R16C8 يستخدم للإشارة إلى نطاق الخلايا R4C3:R10C5 الناتج عن تقاطع النطاقين السابقين
ملاحظة :
ـ هناك بعض الأساليب المعقدة في كتابة المراجع باستخدام نظام الترميز R1C1 نترك شرحها للدرس القادم
ثالثاًً : تعريف المرجع باسم محدد
وهي طريقة عملية تسمح لك بتعريف مجموعة من الخلايا تحت اسم محدد ، وتظهر فائدتها بأنها تتيح لك تسمية النطاقات المختلفة بأسماء تدل عليها وبالتالي فهي تسهل عليك كتابة الصيغ المختلفة و فهمها ، هذا فضلاً عن تخلصك من عناء كتابة الصيغ المعقدة و الطويلة
ولكي تستطيع استخدام هذه الميزة في الصيغ يجب عليك قبل كل شيء القيام بما يلي :
1. انقر فوق الخيار Options من القائمة Tools ، و من ثم انقر فوق علامة التبويب Calculation
2. تحت Workbook options ، حدد خانة الاختيار Accept labels in formulas . انظر إلى الشكل التالي (الجزء المظلل باللون الأحمر) :
ولتوضيح الفكرة نقوم برسم الجدول التالي :
نحدد الخلايا الأربعة و التي تمثل (جدلاً) عناصر التكاليف الثابتة لمنشأة معينة ، و نقوم بتسمية النطاق المحدد (و المكون من الخلايا الأربعة) و ذلك عن طريق إدخال اسم هذا النطاق في المستطيل الموجود على يسار شريط الصيغة ، كما هو موضح في الشكل السابق
هناك طريقة أخرى لتسمية النطاق ، وذلك عن طريق النقر فوق قائمة Insert واختيار Name ومن ثم اختيار Define ، حيث تظهر لك نافذة أدخل فيها اسم النطاق و اضغط OK
والآن لنقم بإدخال اسم النطاق الجديد في الصيغ . في المثال المبسط السابق نحتاج للدالة SUM من أجل حساب مجموع عناصر التكاليف الثابتة :
في الشكل السابق نلاحظ (من خلال النظر إلى شريط الصيغة) أننا كتبنا الدالة Sum بالطريقة
=SUM(fc)
بدلاً من الطريقة
=SUM(D3;B6;D9;F6)
، و لك الحكم مابين الطريقتين السابقتين .
ويجب عند تسمية النطاقات المختلفة الأخذ ببعض الاعتبارات من بينها :
1. يجب أن يكون الحرف الأول من الاسم حرفاً أبجدياً أو يكون خط التسطير السفلي ( _ )
2. لا يمكن أن تكون الأسماء هي نفس مراجع الخلايا ، مثل G$4 أو R3C5
3. لا يمكنك استخدام الفراغات في تسمية النطاقات
4. يمكن أن يتكون الاسم من 255 حرفاً كحد أقصى
5. لا يفرق الاكسل بين الأحرف اللاتينية الكبيرة و الصغيرة في الأسماء
والآن بفرض أننا كتبنا الدالة السابقة بالطريقة
=SUM(D3;B6;D9;F6)
ومن ثم قمنا بتسمية النطاق السابق بالاسم fc . ماذا يجب أن نعمل من أجل تحويل الدالة السابقة إلى الشكل
=SUM(fc)
. هل يجب أن نقوم بكتابة الدالة مرة ثانية؟ ، و هل هذا هو الحل المقبول في حالة وجود مئات الدوال الأخرى التي نريد إجراء نفس التحويل عليها؟!
نستطيع حل هذه المشكلة بطريقة بسيطة ومختصرة ، وتتلخص بما يلي :
1. نقوم بتحديد الخلايا التي تحتوي على الصيغ التي نريد إجراء التحويل عليها (أي نريد كتابة الصيغ فيها بدلالة أسماء النطاقات وليس بالطريقة التقليدية) .
2. ننقر فوق القائمة Insert و نختار Name ومن ثم نختار Apply ، فتظهر لنا نافذة نختار منها أسماء النطاقات التي نريد إدراجها في الصيغ ومن ثم نضغط OK .
والآن ماذا نفعل إذا نسينا أسماء النطاقات التي قمنا بتسميتها أو إذا كنا نريد تحديد أو الانتقال إلى خلايا ونطاقات ذات مواصفات معينة ؟
من القائمة Edit نختار Go To فتظهر النافذة التالية :
نلاحظ وجود قائمة بأسماء النطاقات ، نحدد النطاق المرغوب ونضغط OK للانتقال إليه ، أو نكتب المرجع أو اسم النطاق في المربع Reference ونضغط OK
للانتقال إلى مراجع تحتوي على معلومات محددة نضغط فوق الزر Special فتظهر لدينا النافذة التالية :
نختار نوع البيانات التي نبحث عنها ونضغط OK
للحديث عن المراجع تتمة في الدروس القادمة ..
المزيد من المواضيع
الدرس الثاني: بعض الحيل لتسهيل برنامج اكسل
إنشاء قائمة في مصنف إكسل
الدرس الرابع: إنشاء دالة خاصة لاستخراج النسبة المئوية وخاصية الترتيب.
التعامل مع مكونات مصنف الاكسل
مقدمة في الصيغ
الدوال في الاكسل
الفرق بين المرجع النسبي و المرجع المطلق
المراجع متعددة الأبعاد
التحكم في عملية تحديث المراجع البعيدة (الثلاثية الأبعاد)
كيفية عمل تخطيط بياني
كيفية نسخ الصفحات في برنامج الاكسل
الدرس الثالث : بعض تطبيقات الدوال في اكسل
مقدمة عامة حول الاكسل وحيل خاصة بالاكسل
|
