العناوين النسبية والعناوين Relative/Absolute Reference في Excel

العناوين النسبية والعناوين  Relative/Absolute Reference

عند إدخال المعادلات في الخلايا غالبا ما نشير إلي خلايا أخري لاستخدام القيم بها في المعادلة وغالبا ما تسير الأمور بشكل جيد ولكن في بعض الأحيان عند نسخ المعادلات من خلايا إلي أخري تظهر لنا مشاكل كثيرة ... سنتناول  في هذا المقال كيفية مع هذه المواقف في مثال واقعي ... وليكن علي شركة الطوفان لها ثلاث أفرع في القاهرة والأسكندرية والمنصورة ... ومصروفات الشركة في مجال الداعية تقدر بـــ 50.000 ألف جنيه لهذا العام والمصروفات الإدارية 35.000 ومصروفات التخزين والنقل تقدر 20.000 ونريد تقسيم هذه المصروفات علي الفروع الثلاث للشركة كلأ علي حسب نسبة دخله السنوية لأنها تعبر عن كم استهلاه لمصادر الشركة فهيا تنجز هذا العمل .

1- أضف صفحة عمل جديدة وذلك باختيار Worksheet من القائمة Insert ثم أضغط اسم صفحة العمل الجديد وأكتب "توزيع الحصص" ثم أضغط Enter .

2- في الخلية A2 أكتب مصروفات دعاية ثم تحرك للخلية B2 وأكتب "50.000" ثم تحرك في الخلية A3 أكتب "مصروفات إدارية" اضغط السهم الأيسر لتتحرك للخلية B3 وأكتب 35.000 ... تحرك للخلية A4 وأكتب "مصروفات التخزين والنقل " ثم أضغط السهم الأيسر لتتحرك للخلية B4 وأكتب "20.000" ... أضغط حد العمود A الأيسر ضغطه مزدوجة ليتسع للمحتويات بشكل جيد .

إذا كان إتجاه صفحتك من اليسار إلي اليمين سوف نضغط الحد الأيمن للعمود A وليس الأيسر .

3- أكتب في الخلية C1 "فرع القاهرة" ثم أضغط السهم الأيسر لتتحرك للخلية D1 وأكتب "فرع الإسكندرية" ثم أضغط مفتاح السهم الأيسر وأكتب "فرع المنصورة" في الخلية E1 ... أضغط حدود الأعمدة C,D,E لتتسع لما أدخلت به .

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

4- أضغط رأس الصف الثاني عندما يتحول المؤشر إلي سهم يشير إلي اليسار اضغط لتحديد الصف الثاني واسحب علي الصف الثالث لتحديدهما ثم اختر الأمر Row  من القائمة insert لإضافة صفين جدد

5- في الخلية C2 اكتب 200.000 وهي قيمة الدخا السنوي لفرع القاهرة ثم اضغط السهم  الأيسر من لوحة المفاتيح لتنتقل للخلية D2 أكتب "150.000" ثم أضغط السهم الأيسر مرة أخري وأكتب "120.000" .

6- تحرك للخلية A2 وأكتب "الدخل السنوي للفرع" أضغط Enter ... لتنتقل الخلية A3 أكتب "حصة الفرع" .

7- أكتب "إجمالي العام " في الخلية F1 ... لحساب إجمالي الدخل حدد الخلايا من C2:F2 ثم أضغط الأيقونة AutoSum ليجمع لك Excel إجمالي الدخل للشركة في الخلية F1 .

8- تحرك للخلية C3 وأكتب معادلة تحسب نسبة دخل فرع القاهرة بالنسبة للدخل العام اكتب "=" ثم أضغط الخلية C2 الخلية التي بها قيمة دخل فرع القاهرة ثم أكتب "/" ثم أضغط الخلية F2 الخلية التي بها قيمة إجمالي الدخل ... ثم أضغط الأيقونة √ في شريط المعادلات لإدخال المعادلة

9- ستجد أن النسبة كتبت لك هكذا "0.42553" لتحويلها إلي نسبة مئوية % أضغط الأيقونة Percent Style لتنسيق القيمة في الخلية لنسبة مئوية .

10- أضغط الخلية C3 واسحب مربع الملأ التلقائي Auto Fill لنسخ المعادلة للخلايا D3,E3

وستجد أن Excel كتب لك #Div/o! في الخلية D3 ,E2 أي القسمة علي صفر وهذا يعني حدث خطأ ما مع إن نفس المعادلة تعمل بشكل سليم في الخلية C3 ... فهي تتعرف علي ما حدث بشئ من التفحص .

11- أعرض شريط الأدوات Formula Auditing وذلك بالضغط علي أي من شرائط الأدوات بالمفتاح الأيمن يظهر لك قائمة أختر منها Formula Auditing ليظهر لك هذا الشريط والذي تستخدم أيقونات في التدقيق في المعادلات .

12- أضغط الخلية C3 وهي الخلية التي بها المعادلة وتعطي نتيجة صحيحة ثم أضغط الأيقونة Trace Precedents والتي تعرض لك الخلايا التي تستخدمها المعادلة بالخلية C3 وذلك برسم خطوط زرقاء من الخلايا إلي الخلية C3 كما بالشكل .

13- تحرك إلي الخلية D3 ثم أضغط الأيقونة Trace Precedents مرة أخري وستلاحظ أن المعادلة تستخدم الخلية G2 وهي خلية خالية لذلك نحصل علي القسمة علي صفر .

وذلك لأن المعادلة التي ننسخها تعني قسمة الرقم بالخلية أعلا الخلية الحالية علي الرقم بالخلية الرابعة إلي يسار أعلا الخلية الحالية ... وهذا لا يعمل جيدا عند نسخ المعادلة للخلية D3 أو E3 .

وهذا يعني أنك تريد ترك الجزء الأعلي من المعادلة البسط كما هو وتغير المقام ليشير إلي الخلية F2 بعد وقبل نسخه – في كل الأحوال – وهذا يعني أن تجعل المقام عنوان مطلق يشير إلي نفس الخلية مهما تم نسخه أو نقله .

لإخفاء أسهم الناتجة بالضغط علي الأيقونة Trace Precedents أضغط الأيقونة Remove all Arrows .

هيا نحاول استخدام المساعد في معرفة كيفية تحويل عناوين الخلايا من عناوين نسبية تتغير عن نسخها إلي عناوين مطلقة لا تتأثر بعملية النسخ أكتب في المربع Ask question for Help ''Absolute And Relative Reference'' ثم أضغط Enter تظهر لك قائمة ستجد بها وستجد أن هناك موضوعات كثيرة .

أضغط الموضوع الأول Switch Between Relative and mixed references .

وسيعرض لك هذا الموضوع جدول به عدة خيارات هي كالأتي ...

الأول:- الخلية عنوانها مطلق تماما Absolute وتكتب كما تعودنا مع وضع $ قبل الصف والعمود $F$2.

الثاني :- هو أن يكون العمود نسبي والصف مطلق وستجد قبل الصف $ مثل F$2 وعند نسخ هذا النوع من العناوين يتغير فقط رقم العمود أما الصف فسيظل كما هو دون تغيير.

الثالث:- هو أن يكون الصف نسبي والعمود مطلق وستجد قبل العمود $ مثل $F2 وعند نسخ هذا النوع من العناوين يتغير فقط رقم الصف أما العمود فسيظل كما هو دون تغيير.

الرابع:- الخلية عنوانها نسبي تماما Relative وتكتب كما تعودنا وليس بعنوانها أي علامة F2$.

الخلية التي بعنوانها جزء مطلق وجزء نسبي تسمي مختلط Mixed.

ولتغير عنوان خلية في معادلة إما أن تكتب $ امام جزء العنوان الذي تريد أن تجعله مطلق في شريط المعادلات ... وتترك جزء العنوان الذي تريد جعله نسبي... أو أن تحدد عنوان الخلية في شريط المعادلات وتضغط F4 وسوف تنتقل بك كل ضغطه بين وضع من الأوضاع الأربعة حتى تصل لما تريد وستجد أن Excel بمجرد تحديد عنوان خلية في شريط المعادلات يحد ذلك الخلايا التي تستخدم في المعادلة ليساعدك علي الوصول لما تريد.

اضغط الأيقونة Close لغلق المساعدة والعودة إلي العمل

- اضغط الخلية C3 تعرض لك المعادلة في شريط المعادلات اضغط F2 ضغطة مزدوجه لتحديدها ثم اضغط F4 وستجد أنها تحولت إلي $F$2 وستجد أن Excel يعرض لك الخلية التي تتعامل معهم المعادلة وحولهم مربع ... اضغط √ لإدخال تعديلك للمعادلة الخلية C3.

- انسخ المعادلة بسحب مربع الملأ التلقائي Auto Fill إلي الخلية D3 ، F3 وستجد أن المعادلة تم تصحيحها وأظهرت نتائج صحيحة.

هي تستخدم شريط الأدوات Formula Auditing لتوضيح عناوين الخلايا المطلقة والنسبية بصورة أوضح .. اضغط الخلية C3 ثم اضغط الأيقونة Trace Precedents يظهر لك أسهم توضح الخلايا التي تمثل المعادلة في C3 وستجد أن المعادلة تستخدم الخلية 2 كمقام للمعادلة و F2 كبسط... اضغط الخلية D3 ثم اضغط الأيقونة Trace Precedents وستجد أن المعادلة تستخدم الخلية D2 كمقام للمعادلة و F2 كبسط... اضغط الخلية E3 ثم اضغط الأيقونة Trace Precedents وستجد أن المعادلة تستخدم الخلية E2 كمقام للمعادلة و F2 كبسط.

اضغط الأيقونة Remove all Arrows من شريط الأدوات Formula Auditing .

- حدد الخلايا F3 ، C3 ثم اضغط الأيقونة ∑ Autosum ليضاف لك مجموعة الخلايا E3 : C3 في الخلية F3.

- لنكمل باقي حسابات ورقة العمل تحرك إلي الخلية C4 ثم اكتب "=" ثم اضغط الخلية B4 واكتب "*" – علامة الضرب في برامج الكمبيوتر – ثم اضغط الخلية C3 وبذلك نحسبه نسبة فرع القاهرة من مصروفات الدعاية اضغط √ لإدخال المعادلة في الخلية C4.

- اضغط الخلية C4 ثم اسحب مربع الملأ التلقائي لنسخ المعادلة للخلايا E4 . D4 وسنلاحظ أن المعادلة لا تعمل جيدا لأن مجموعة الخلايا C4+D4+E4 = القيمة بـ B4 وهي 50.000.

- اضغط الخلية C4 ثم اضغط الأيقونة Trace Precedents يظهر لك أن المعادلة في الخلية C4 تستخدم الخلية B4 والخلية C3 وهذا شئ صحيح... اضغط الخلية D4 ثم اضغط الأيقونة  Trace Precedents يظهر لك أن المعادلة في الخلية D4 تستخدم الخلية C4 والخلية D3 وهذا خطأ يجب أن تستخدم الخلية B4 والخلية D3.

- اضغط الأيقونة Remove all Arrows ثم اضغط الخلية E4 ثم اضغط الأيقونة Trace Precedents ليظهر لك أن المعادلة في الخلية E4 تستخدم الخلية E3 والخلية B4.

ما نريده أن تظل المعادلات الثلاثة تشير إلي الخلية B4.. هذا إلي الآن...

ولكن ماذا لو قمنا بنسخ المعادلات للصفوف التالية لحساب باقي النسب وذلك بتحديد الخلايا E4: C3 ثم سحب مر بع الملأ التلقائي Auto fill إلي الصف 6.

حاول تحرير بعض الخلايا واضغط الأيقونة Trace Precedents لتوضح لك الصورة ما تريده.

الآن المعادلة C3 *B4 يجب تعديلها بشكل يسمح بعملها بشكل جيد وذلك علي خطوتين.

أولاً:- الشق الأول من المعادلة:

- ستلاحظ أن العمود B يجب أن يظل ثابت بينما يتغير رقم الصف من 4 لحساب مصاريف الدعاية إلي 5 لحساب المصروفات الإدارية ثم إلي 6 لحساب مصروفات النقل والتخزين... لذلك يجب جعل B مطلق وذلك بكتابة $ قبله أو ضع المؤشر بين B و4 في المعادلة كما بالشكل وضغط مفتاح F4 ثلاث مرات حتى نحصل علي B4$.

ثم اضغط √ لإدخال تعديل المعادلة في الخلية C4 ... ويمكنك اختبار هذا التعديل بنسخ المعادلة لباقي الخلايا وضغط الأيقونة  Trace Precedents ملاحظة الأسهم تشير إلي الخلية الصحيحة في شق المعادلة الأول.

ثانياً:- الشق الثاني من المعادلة.

- ستلاحظ أن الصف 3 يجب أن يظل ثابت بينما يتغير العمود من C لحساب نسبة فرع القاهرة إلي D لحساب نسبة فرع الإسكندرية ثم إلي E لحساب نسبة فرع المنصورة لذلك يجب جعل الصف 3 مطلق وذلك بكتابة $ قبله أو وضع المؤشر بين C3 في المعادلة كما بالشكل وضغط مفتاح F4 مرتين حتى نحصل علي C$3.

ثم اضغط √ لإدخال تعديل المعادلة في الخلية C4 ... ويمكنك اختبار التعديل بنسخ المعادلة لباقي الخلايا وضغط الأيقونة  Trace Precedents وملاحظة الأسهم تشير إلي الخلايا الصحيحة في شق المعادلة الثاني والأول.

- حدد الخلايا F6 :C3 ثم اضغط الأيقونة ∑ Auto sum ليظهر لك في العمود F مجموع نسب كل فرع من المصروفات والتي يجب أن تساوي قيمة بند المصروفات في العمود B.