المراجع في Excel: الدليل الكامل للمراجع النسبية والمطلقة والمختلطة (مع أمثلة عملية)

ربما تذكر أول ملف أعددته في Excel. كتبت المعادلة في أول صف، وظهرت النتيجة صحيحة، ثم سحبت المعادلة إلى بقية الصفوف… فظهرت أصفار وأخطاء غريبة مثل #VALUE!. ظننت في البداية أن البرنامج "لا يعمل"، ثم أدركت أن الخطأ كان منك. تعلم المراجع يمكن أن ينقذك من ساعات طويلة من البحث عن الأخطاء وتصحيحها.

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

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

1. ما هو المرجع (Reference) في Excel؟

المرجع هو عنوان الخلية الذي نكتبه داخل المعادلة لنقول لـ Excel: خُذ القيمة من هنا.

انظر إلى هذه المعادلة:

=B2*C2

• B2 مرجع للخلية الواقعة في العمود B والصف 2 (السعر).

• C2 مرجع للخلية الواقعة في العمود C والصف 2 (الكمية).

• علامة = في البداية تخبر Excel أن ما بعدها عملية حسابية وليس نصًا.

وللمراجع ثلاثة أنواع في Excel:

1. نسبي (Relative) — الشكل: B2 — الفكرة باختصار: مرجع "يتحرّك" معك عند النسخ

2. مطلق (Absolute) — الشكل: $B$2 — الفكرة باختصار: مرجع "مثبّت" لا يتغيّر أبدًا

3. مختلط (Mixed) — الشكل: $B2 أو B$2 — الفكرة باختصار: نصفه مثبّت ونصفه متحرّك

2. المراجع النسبية (Relative References): الوضع الافتراضي

المرجع النسبي هو ما تكتبه بشكل طبيعي: B2. وهو الأكثر استخدامًا لأن معظم أعمالنا عبارة عن "لكل صف، احسب شيئًا من نفس الصف".

مثالنا الأول: فاتورة صغيرة في ورقة المراجع النسبية:

1. قلم — السعر: 5 — الكمية: 10 — الإجمالي: 50

2. دفتر — السعر: 12 — الكمية: 4 — الإجمالي: 48

3. حاسبة — السعر: 60 — الكمية: 2 — الإجمالي: 120

4. حقيبة — السعر: 85 — الكمية: 1 — الإجمالي: 85

المصدر: الخلية D2 تحتوي المعادلة =B2*C2 وتظهر النتيجة 50. الرقم (1) يشير إلى الخلية نفسها، والإطار يوضّح المعادلة المستخدمة فيها.

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

3. ماذا يحدث بالضبط عند نسخ المعادلة؟

هنا يتجلّى عمل المرجع النسبي. عندما ننسخ =B2*C2 من الصف 2 إلى الصف 5، يعدّلها Excel تلقائيًا إلى:

=B5*C5

أي أنه "زحزح" المراجع بنفس عدد الصفوف التي نزلناها: من 2 إلى 5. وهذا هو بالضبط ما نريده في الفاتورة: كل صف يضرب سعره في كميته.

الصورة: الخلية D5 تحتوي الآن =B5*C5 وتظهر النتيجة 85. الرقم (1) يشير إلى الصف الأول، و(2) إلى الصف الأخير لترى كيف تغيّر المرجع.

هذه القدرة على "التعديل الذاتي" هي سبب شعبية المراجع النسبية. لكنها أيضًا سبب المشاكل والأخطاء حين تكون أنت لا تريد أن يتحرّك المرجع… وهنا نحتاج النوع الثاني، المراجع المطلقة

4. المراجع المطلقة (Absolute)

لدينا فاتورة نضيف فيها ضريبة لكل منتج. نسبة ضريبة واحدة لكل الصفوف، لذلك نضعها في خلية واحدة ونشير إليها من كل الصفوف.

في ورقة المراجع المطلقة وضعنا نسبة الضريبة في الخلية B1، ثم حسبنا الضريبة بهذه المعادلة:

=B4*$B$1

الصورة: عمود "الضريبة (صحيحة)" يستخدم المعادلة =B4*$B$1. الرقم (1) يشير إلى خلية النسبة الثابتة في الأعلى، والرقم (2) إلى عمود النتائج الصحيحة.

ماذا تعني علامات $ الأربعة؟

• B2 — المعنى: نسبي بالكامل — عند النسخ إلى الأسفل: يتحرّك الصف — عند النسخ إلى اليمين: يتحرّك العمود

• $B$2 — المعنى: مطلق بالكامل — عند النسخ إلى الأسفل: لا يتحرّك — عند النسخ إلى اليمين: لا يتحرّك

• $B2 — المعنى: العمود مثبّت — عند النسخ إلى الأسفل: يتحرّك الصف فقط — عند النسخ إلى اليمين: لا يتحرّك العمود

• B$2 — المعنى: الصف مثبّت — عند النسخ إلى الأسفل: لا يتحرّك الصف — عند النسخ إلى اليمين: يتحرّك العمود فقط

قاعدة ذهبية: كل علامة $ تُثبّت ما يأتي بعدها مباشرة. $B تثبّت العمود، و$1 تثبّت الصف. ولمّا كان $B$1 يثبّت الاثنين، فإنه لا يتغيّر مهما نسخت المعادلة في أي اتجاه.

5. ماذا لو نسيت علامة $؟ (هنا تحدث الكوارث)

تم وضع عمود كامل بالطريقة الخاطئة، بشكل متعمد، لتقارن وترى بنفسك.

الصورة: العمود الأخير يستخدم =B4*B1 (بدون $) بعد سحبه إلى الأسفل. الرقم (1) يشير إلى خلية النسبة، وعلامة (!) إلى العمود الذي ظهرت فيه النتائج الخاطئة.

اقرأ النتائج صفًا صفًا، لأنها درس كامل في سطر واحد:

1. الأول — المعادلة التي أصبحت فعليًا: =B4*B1 — النتيجة: 0.75 — لماذا؟: صحيحة بالحظ، لأن B1 هي الخلية المطلوبة نفسها

2. الثاني — المعادلة التي أصبحت فعليًا: =B5*B2 — النتيجة: 0 — لماذا؟: المرجع تحرّك إلى B2 وهي خلية فارغة

3. الثالث — المعادلة التي أصبحت فعليًا: =B6*B3 — النتيجة: #VALUE! — لماذا؟: المرجع تحرّك إلى B3 وهي تحتوي كلمة "السعر" (نص)

4. الرابع — المعادلة التي أصبحت فعليًا: =B7*B4 — النتيجة: 425 — لماذا؟: المرجع تحرّك إلى B4 وهي تحتوي الرقم 5، فصار الضرب في رقم غير مقصود

لاحظ أن Excel حرّك المرجع في كل صف. الحل كان في علامة $ فقط.

نصيحة عملية: إذا رأيت #VALUE! في خلية تنتج من ضرب، فغالبًا أحد المراجع يشير إلى خلية تحتوي نصًا (عنوان أو اسم) بدل رقم.

6. المراجع المختلطة (Mixed): تثبيت العمود أو الصف فقط

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

أشهر مثال: جدول الضرب.

الأرقام من 1 إلى 5 في الصف الأول (B1:F1) ونفس الأرقام في العمود الأول (A2:A6)، ونريد في كل خلية حاصل ضرب رقم صفها في رقم عمودها. المعادلة في الخلية B2 هي:

=$A2*B$1

الصورة: جدول الضرب بعد سحب المعادلة =$A2*B$1 في كل الاتجاهات.

في هذه المعادلة، كل جزء فيها يعرف دوره:

• $A2 : العمود A مثبّت (لأن أرقام الصفوف كلها في العمود A)، والصف متحرّك (لأننا ننزل صفًا في كل مرة).

• B$1 : الصف 1 مثبّت (لأن أرقام الأعمدة كلها في الصف 1)، والعمود متحرّك (لأننا ننتقل عمودًا في كل مرة).

الصورة: النتيجة النهائية: جدول ضرب كامل من 5×5 بمعادلة واحدة فقط.

والنتيجة جدول متكامل:

• 1 — 1: 1 — 2: 2 — 3: 3 — 4: 4 — 5: 5

• 2 — 1: 2 — 2: 4 — 3: 6 — 4: 8 — 5: 10

• 3 — 1: 3 — 2: 6 — 3: 9 — 4: 12 — 5: 15

• 4 — 1: 4 — 2: 8 — 3: 12 — 4: 16 — 5: 20

• 5 — 1: 5 — 2: 10 — 3: 15 — 4: 20 — 5: 25

7. ماذا لو نسينا $ في جدول الضرب؟

أنشأنا في الورقة جدولًا آخر بالطريقة الخاطئة: معادلة =A12*B11 نسبية بالكامل، ثم سحبنها في كل الاتجاهات.

الصورة: جدول الضرب بمعادلة نسبية بالكامل. علامة (!) تشير إلى الجدول الذي خرجت نتائجه عن السيطرة.

النتائج: 120، ثم 576، ثم 69120، ثم رقم خرافي مثل 6.37E+42. السبب أن كل خلية صارت تضرب في جيرانها بدل أن تضرب في رقم صفها ورقم عمودها. هذا مثال عملي على أن الخطأ في المرجع لا يظهر دائمًا كرسالة، بل أحيانًا كرقم كبير يبدو منطقيًا للوهلة الأولى.

8. جدول مقارنة: متى أستخدم كل نوع؟

هذه اللائحة توضح متى وكيفية اختيار نوع المرجع:

• نسبي (Relative) — الشكل: B2 — ما يتغيّر عند النسخ: الصف والعمود معًا — مثال من العمل اليومي: إجمالي كل سطر = السعر × الكمية

• مطلق (Absolute) — الشكل: $B$1 — ما يتغيّر عند النسخ: لا شيء إطلاقًا — مثال من العمل اليومي: نسبة ضريبة أو خصم واحدة لكل الصفوف

• مختلط: عمود مثبّت — الشكل: $A2 — ما يتغيّر عند النسخ: الصف فقط — مثال من العمل اليومي: جدول ضرب: أرقام الصفوف في العمود A

• مختلط: صف مثبّت — الشكل: B$1 — ما يتغيّر عند النسخ: العمود فقط — مثال من العمل اليومي: جدول ضرب: أرقام الأعمدة في الصف 1

من المهم التساؤل دائما: هل هذه القيمة تقع في خلية واحدة أريدها في كل مرة؟ إن كان الجواب نعم، ضع $ قبل حرف العمود وقبل رقم الصف.

9. أسرع طريقة للتثبيت: مفتاح F4

لا حاجة لكتابة علامات $ يدويًا. اكتب المعادلة بشكل عادي، ثم:

1. ضع مؤشر الكتابة داخل المرجع الذي تريد تثبيته (وسط B1 مثلًا) داخل شريط الصيغة.

2. اضغط مفتاح F4 مرة: يصبح $B$1 (مطلق).

3. اضغط مرة ثانية: يصبح B$1 (الصف مثبّت).

4. اضغط مرة ثالثة: يصبح $B1 (العمود مثبّت).

5. وضغطة رابعة تعيده إلى B1 نسبيًا.

في الحواسيب المحمولة قد تحتاج Fn + F4.

10. أخطاء شائعة أخرى (وكيف تتجنّبها)

بعد المراجع النسبية والمطلقة، تظهر أخطاء أخرى مرتبطة بها مباشرة:

1. رسالة #REF! بعد حذف عمود أو صف: إذا كانت معادلتك تشير إلى خلية ثم حذفت العمود الذي يحتويها، فلا يجد Excel ما يشير إليه فيظهر #REF!. الحل: لا تحذف الصفوف/الأعمدة التي تعتمد عليها المعادلات، أو صحّح المعادلة قبل الحذف.

2. التعبئة في الاتجاه الخطأ: مرجع $B2 مثبّت في العمود، فإن سحبت المعادلة إلى اليمين (بدل الأسفل) ستحصل على نفس العمود في كل الخلايا. اسأل نفسك دائمًا: هل سأسحب لأسفل أم إلى اليمين؟

3. الإشارة إلى ورقة أخرى: يمكنك الإشارة إلى خلية في ورقة أخرى بكتابة اسم الورقة ثم علامة تعجّب، مثل: =المراجع المطلقة!B4. وإذا كان اسم الورقة يحتوي مسافة (كما في أوراقنا: "المراجع المطلقة")، ضع الاسم بين علامتي تنصيص مفردتين: ''المراجع المطلقة''!B4. وعند نسخ معادلة تشير إلى ورقة أخرى، تنطبق عليها نفس قواعد $ تمامًا.

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

11. تمرين عملي للتثبيت (الورقة الرابعة)

استخدم هذا الجدول للتمرين:

الصورة: ورقة التمرين. الرقم (1) يشير إلى خلية نسبة الخصم التي ستستخدمها بمرجع مطلق.

المطلوب منك:

1. اكتب في الخلية D8 معادلة الإجمالي باستخدام مرجع نسبي (السعر × الكمية)، ثم اسحبها إلى بقية الصفوف.

2. اكتب نسبة الخصم 10% في الخلية G3، ثم احسب الخصم في العمود E باستخدام مرجع مطلق للخلية G3.

3. احذف علامات $ من المعادلة وانظر بعينك كيف تتغيّر النتائج عند السحب إلى الأسفل (ستتذكّر هذه اللحظة أكثر من أي شرح).

4. أنشئ جدولًا صغيرًا آخر واضرب صفًا في عمود باستخدام مرجع مختلط كما فعلنا في جدول الضرب.

وإذا واجهت خطأ، لا تحذفه فورًا: اقرأ المعادلة في الخلية واسأل نفسك: إلى أي خلية يشير المرجع الآن؟ في الغالب ستجد الإجابة بنفسك خلال ثوانٍ.

12. خلاصة سريعة

تذكر دائما هذه القواعد:

1. B2 نسبي: يتحرّك معك عند النسخ، وهو المناسب لحسابات الصف نفسه.

2. $B$2 مطلق: لا يتحرّك أبدًا، وهو المناسب للقيم الثابتة (ضريبة، خصم، سعر صرف).

3. $B2 و B$2 مختلطان: أحدهما يثبّت العمود والآخر يثبّت الصف، وهما أساس أي جدول متقاطع.

4. وF4 يقلّب بين الأنواع الأربعة في ثانية.

ومن هنا فصاعدًا، عندما تنسخ معادلة ويظهر لك رقم غريب أو #VALUE! أو 0، لا تُصلح النتيجة يدويًا: اذهب إلى المعادلة واقرأ مراجعها. المشكلة في التسعة والتسعين بالمئة من الحالات تكون في علامة $ ناقصة.

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