بسم الله الرحمن الرحيم

اهلا بكم متابعى موقع عالم الاوفيس

المراجع الخلوية المطلقة والنسبية والمختلطة في الاكسل Absolute, Relative References in Excel

تتكون ورقة العمل في Excel من خلايا. ويمكن الإشارة إلى هذه الخلايا من خلال تحديد قيمة الصف وقيمة العمود.

على سبيل المثال، يشير A1 إلى الصف الأول (المحدد بـ 1) والعمود الأول (المحدد بـ A). وعلى نحو مماثل، يشير B3 إلى الصف الثالث والعمود الثاني.

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

الآن هناك ثلاثة أنواع من مراجع الخلايا التي يمكنك استخدامها في Excel:

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

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

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

ما هي مراجع الخلايا النسبية في Excel؟

دعني آخذ مثالاً بسيطًا لشرح مفهوم مراجع الخلايا النسبية في Excel.

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

لحساب الإجمالي لكل عنصر، نحتاج إلى ضرب سعر كل عنصر بكمية هذا العنصر.

بالنسبة للعنصر الأول، ستكون الصيغة في الخلية D2 هي B2*C2 (كما هو موضح أدناه):

الآن، بدلاً من إدخال الصيغة لجميع الخلايا واحدة تلو الأخرى، يمكنك ببساطة نسخ الخلية D2 ولصقها في جميع الخلايا الأخرى (D3:D8). عندما تفعل ذلك، ستلاحظ أن مرجع الخلية يتكيف تلقائيًا للإشارة إلى الصف المقابل. على سبيل المثال، تصبح الصيغة في الخلية D3 هى B3*C3 وتصبح الصيغة في الخلية D4 هى B4*C4

تسمى مراجع الخلايا هذه التي تعدل نفسها عند نسخ الخلية مراجع الخلايا النسبية في Excel.

متى تستخدم مراجع الخلايا النسبية في الاكسل Relative Cell References in Excel؟

تعتبر مراجع الخلايا النسبية مفيدة عندما يتعين عليك إنشاء صيغة لمجموعة من الخلايا وتحتاج الصيغة إلى الإشارة إلى مرجع خلية نسبي.

في مثل هذه الحالات، يمكنك إنشاء الصيغة لخلية واحدة ونسخها ولصقها في جميع الخلايا.

ما هي مراجع الخلايا المطلقة في الاكسل Absolute Cell References Excel؟

على عكس مراجع الخلايا النسبية، لا تتغير مراجع الخلايا المطلقة عند نسخ الصيغة إلى خلايا أخرى.

على سبيل المثال، افترض أن لديك مجموعة البيانات كما هو موضح أدناه حيث يتعين عليك حساب العمولة لإجمالي مبيعات كل عنصر.

العمولة 20% ومدرجة في الخلية G1

للحصول على مبلغ العمولة لكل عملية بيع سلعة، استخدم الصيغة التالية في الخلية E2 وانسخها لكل الخلايا:

= D2*$G$1

لاحظ وجود علامتي دولار ($) في مرجع الخلية الذي يحتوي على العمولة - $G$2.

ماذا تفعل علامة الدولار ($)؟

عند إضافة رمز الدولار أمام رقم الصف والعمود، يجعله مطلقًا (أي يمنع رقم الصف والعمود من التغيير عند نسخه إلى خلايا أخرى).

على سبيل المثال، في الحالة أعلاه، عندما أنسخ الصيغة من الخلية E2 إلى E3، تتغير من =D2*$G$1 إلى =D3*$G$1.

لاحظ أنه بينما تتغير الخلية D2 إلى D3، فإن $G$1 لا تتغير.

نظرًا لأننا أضفنا رمز دولار أمام "G" و"1" في الخلية G1، فلن يسمح بتغيير مرجع الخلية عند نسخها.

وبالتالي، فإن هذا يجعل مرجع الخلية مطلقًا.

متى تستخدم مراجع الخلايا المطلقة في Excel؟

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

وبينما يمكنك أيضًا ترميز هذه القيمة في الصيغة (على سبيل المثال، استخدام 20% بدلاً من $G$2)، فإن وجودها في خلية ثم استخدام مرجع الخلية يسمح لك بتغييرها في تاريخ مستقبلي.

على سبيل المثال، إذا تغير هيكل العمولة الخاص بك وأنت تدفع الآن 25% بدلاً من 20%، يمكنك ببساطة تغيير القيمة في الخلية G2، وستتم تحديث جميع الصيغ تلقائيًا.

ما هي مراجع الخلايا المختلطة في الاكسل Mixed Cell References in Excel ؟

تعد مراجع الخلايا المختلطة أكثر صعوبة بعض الشيء من مراجع الخلايا المطلقة والنسبية.

يمكن أن يكون هناك نوعان من مراجع الخلايا المختلطة:

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

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

دعنا نرى كيف يعمل ذلك باستخدام مثال.

يوجد أدناه مجموعة بيانات حيث تحتاج إلى حساب المستويات الثلاثة للعمولة بناءً على قيمة النسبة المئوية في الخلية E2وF2 وG2.

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

أدخل الصيغة أدناه في الخلية E4وانسخها لجميع الخلايا.

=$B4*$C4*E$2

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

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

$B$ ,(و$C4) - في هذا المرجع، توجد علامة الدولار قبل تدوين العمود مباشرةً ولكن ليس قبل رقم الصف. هذا يعني أنه عند نسخ الصيغة إلى الخلايا الموجودة على اليمين، سيظل المرجع كما هو حيث يتم إصلاح العمود. على سبيل المثال، إذا قمت بنسخ الصيغة من E4 إلى F4، فلن يتغير هذا المرجع. ومع ذلك، عند نسخها لأسفل، سيتغير رقم الصف لأنه غير مقفل.

E$2 – في هذا المرجع، توجد علامة الدولار قبل رقم الصف مباشرةً، ولا يوجد علامة دولار في تدوين العمود. وهذا يعني أنه عند نسخ الصيغة لأسفل الخلايا، لن يتغير المرجع لأن رقم الصف مقفل. ومع ذلك، إذا قمت بنسخ الصيغة إلى اليمين، فسيتغير أبجدية العمود لأنه غير مقفل.

كيف تغير المرجع من نسبي إلى مطلق (أو مختلط)؟

لتغيير المرجع من نسبي إلى مطلق، تحتاج إلى إضافة علامة الدولار قبل تدوين العمود ورقم الصف.

على سبيل المثال، A1 هو مرجع خلية نسبي، وسيصبح مطلقًا عندما تجعله $A$1.

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

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

عند تحديد مرجع خلية (في شريط الصيغة أو في الخلية في وضع التحرير) والضغط على F4، يتغير المرجع.

لنفترض أن لديك المرجع =A1 في خلية.

إليك ما يحدث عند تحديد المرجع والضغط على المفتاح F4.

اضغط على المفتاح F4 مرة واحدة: يتغير مرجع الخلية من A1إلى $A$1 (يصبح "مطلقًا" من "نسبيًا").
اضغط على المفتاح F4 مرتين: يتغير مرجع الخلية من A1إلى A$1 (يتغير إلى مرجع مختلط حيث يتم قفل الصف).
اضغط على المفتاح F4ثلاث مرات: يتغير مرجع الخلية من A1إلى$A1 (يتغير إلى مرجع مختلط حيث يتم قفل العمود).
اضغط على المفتاح F4أربع مرات: يصبح مرجع الخلية A1مرة أخرى.

عالم الاوفيس

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