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

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

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

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

=SUM(A1+A2)

يمكنك استخدام الأنواع التالية من مراجع الخلايا لتحقيق نتائج محددة:

• بشكل افتراضي، المراجع في Excel هي نسبي، وهو ما يشير إلى الموضع النسبي للخلية. إذا كنت تكتب صيغة في الخلية A1 التي تتضمن الخلية A2، فأنت تشير إلى الخلية التي تقع أسفل الخلية التي تكتب فيها. إذا كنت تريد نسخ الصيغة إلى خلية أخرى، فسوف يقوم المرجع تلقائيًا بنفس الإجراء النسبي كما في موقعه السابق.
• يمكنك أيضًا استخدام مطلق مرجع لا يتغير إذا قمت بنسخ الصيغة إلى خلية أخرى. إذا قمت بإنشاء صيغة تشير إلى خلية باستخدام مرجع مطلق، فسيتم الإشارة إلى نفس الخلية أينما تم استخدام الصيغة.
• وأخيرا، يمكنك استخدام مختلط المرجع، وهو مزيج من الاثنين.

دعونا ننظر إلى هذه الأمور بمزيد من التفصيل.

كيفية استخدام المراجع النسبية في Excel

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

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

=SUM(E2+E12+E22)

لقد أشار هذا الآن إلى الخلايا E2 وE12 وE22 ضمن الحساب، حيث جمعها معًا. هذا مرجع نسبي – لقد أشارت الصيغة في الخلية H2 إلى الخلايا التي تقع على بعد ثلاثة أعمدة إلى اليسار (E2)، وثلاثة أعمدة إلى اليسار وعشرة صفوف إلى الأسفل (E12)، وثلاثة أعمدة إلى اليسار وعشرين صفًا إلى الأسفل (E22)، على التوالي.

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

كما ترى هنا، الصيغة في الخلية H11 هي:

=SUM(E11+E21+E31)

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

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

في هذا المثال، يريد محاسب الشركة أن يحسب المبلغ الذي يجب دفعه للموظفين بناءً على عدد الساعات التي عملوها ومعدل ثابت للأجر بالساعة.

ابدأ بالنقر فوق الخلية التي تريد حساب الإجمالي الأول فيها (في هذه الحالة، E2)، وابدأ في كتابة الصيغة المناسبة. في هذا المثال، يريد المحاسب ضرب الأجر بالساعة في الخلية A2 في إجمالي الساعات التي عملت بها جاسيندا في الخلية D2.

=SUM(A2

ومع ذلك، إذا استخدمنا مرجعًا نسبيًا هنا، فعندما نطبق الصيغة على إجماليات الموظفين الآخرين، ستشير الصيغة إلى الخلية A3 لراؤول، وA4 لـلوسي، وهكذا، مما يعني أنك لن تحصل على النتائج التي تريدها.

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

=SUM($A$2

يخبر هذا الآن Excel أنه إذا استخدمنا نفس الصيغة في مكان آخر، فسيتم دائمًا الرجوع إلى A2.

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

=SUM($A$2*D2)

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

يمكنك الآن تطبيق الصيغة على الخلايا الأخرى عن طريق نسخها ولصقها أو استخدام وظيفة التعبئة التلقائية في Excel، مع العلم أن الخلية A2 يتم الإشارة إليها بشكل مطلق.

كيفية استخدام المراجع المختلطة في Excel

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

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

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

=SUM(B2*E2)

نحتاج الآن إلى قفل العمود E. إذا أردنا التعبئة التلقائية إلى اليمين دون تعديل الصيغة، فلن تحسب بشكل صحيح الضريبة المستحقة على أرجون لعام 2022؛ في حين أنها ستلتقط بدقة إجماليات الدخل السنوي الصحيحة (لأننا لا نقفل الخلية B2)، فإن الصيغة ستشير أيضًا إلى الخلية F2. للقيام بذلك، أضف رمز الدولار قبل الإشارة إلى العمود E في الصيغة الخاصة بك:

=SUM(B2*$E2)

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

يمكننا الآن استخدام وظيفة التعبئة التلقائية لاستكمال إجمالي الضرائب المستحقة على Arjun، مع العلم أن العمود E سيبقى مقفلاً في حساباتنا، بينما ستتحرك الإجماليات لكل عام بالنسبة إلى المكان الذي تم وضع الصيغة فيه.

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

كيفية التبديل بين أنواع مختلفة من مراجع الخلايا في Excel

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

في هذه الحالة، نريد التبديل بين أنواع مرجع الخلية للخلية G11، لذا نحتاج إلى التأكد من النقر في مكان ما على مرجع الخلية أو بجواره.

ثم اضغط على F4 وسوف تشاهد تغيير نوع مرجع الخلية.

سيتم التبديل أولاً إلى مرجع مطلق:

$G$11

ثم مرجع مختلط مع الصف مقفل:

G$11

ثم مرجع مختلط مع العمود مقفل:

$G11

وأخيرًا، سيتم العودة إلى الوضع الافتراضي، وهو مرجع نسبي:

G11

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

لقد انتهيت! من كان ليتصور أن مجرد وجود رمز الدولار يمكن أن يساعدك على تنظيم مراجع الخلايا في Excel بسرعة ودقة؟