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

تعرف على أنواع مراجع الخلايا في Excel: النسبية والمطلقة والمختلطة

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

سنقوم الآن بالتعرف على ثلاثة أشكال متميزة من مراجع الخلايا في Excel:

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

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

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

سيتم استخدام مثال بسيط لشرح مفهوم مراجع الخلايا النسبية في Excel. لنفترض أن لديك مجموعة المعلومات التالية:

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

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

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

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

7.2 ما هي مراجع الخلايا المطلقة في Excel؟

على عكس مراجع الخلايا النسبية، لا تتغير مقارنات الخلايا المطلقة عند نقل الصيغة إلى خلايا إضافية. بافتراض أنك قمت بجمع البيانات المقدمة أدناه، ستحتاج إلى حساب عمولة الدخل الإجمالي لكل عنصر. تبلغ العمولة 20٪، وهي مرئية في الخلية G1.

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

=D2*$G$1

ركز على علامات الدولار ($) في مرجع خلية العمولة – $G$2.

ماذا يفعل علامة الدولار ($) في مراجع الخلايا في Excel؟

يؤدي وضع علامة دولار أمام رقم الصف أو العمود إلى جعله مطلقًا.

تتغير الصيغة من =D2*$G$1 إلى =D3*$G$1 عندما تقوم بتكرارها من الخلية E2 إلى الخلية E3. تجدر الإشارة إلى أنه، حتى عندما يصبح D2 هو D3، تظل $G$1 كما هي. لن يتغير مرجع الخلية عندما تكرره لأنك أضفت علامة دولار أمام ‘G’ و ‘1’ في G1. اتصال الخلية الآن مطلق.

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

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

7.3 ما هي مراجع الخلايا المختلطة في Excel؟

تعتبر مراجع الخلايا المختلطة أكثر صعوبة من مقارنات الخلايا المطلقة والنسبية. هناك نوعان من مراجع الخلايا المختلطة:

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

ألقِ نظرة على موقف لفهم كيفية عمله. يجب استخدام قيم النسبة المئوية في الخلايا E2 و F2 و G2 في مجموعة البيانات أدناه لحساب مستويات العمولة الثلاثة.

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

=$B4*$C4*E$2

تتضمن الصيغة أعلاه كلا النموذجين من مراجع الخلايا في Excel المختلطة (أحدهما يثبت فيه الصف والآخر الذي يتم فيه قفل العمود). ألقِ نظرة على كيفية عمل كل مرجع خلية:

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

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

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

لتغيير العلاقة من نسبي إلى مطلق، يجب عليك وضع علامة الدولار قبل تدوين العمود ورقم الصف. نظرًا لأن A1 هو اتصال خلية نسبي، فإن تغييره إلى $A$1 يجعله مطلقًا.

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

فكر في الحالة التي تحتوي فيها الخلية على العلاقة =A1.

يحدث هذا عندما تختار علاقة وتضغط على مفتاح F4.

افعل ما يلي بعد الضغط على مفتاح F4:

• من “نسبي” إلى “مطلق”، تتغير علاقة الخلية من A1 إلى $A$1.
• لتغيير علاقة الخلية من A1 إلى A$1، اضغط على مفتاح F4 مرتين (يتغير إلى مرجع مختلط حيث الصف مقفل).
• اضغط على مفتاح F4 ثلاث مرات: كفهرس الخلية، يصبح A1 هو $A1 (يتغير إلى مرجع مختلط حيث يتم قفل العمود).

لتغيير علاقة الخلية مرة أخرى إلى A1، اضغط على مفتاح F4 أربع مرات.

7.5 جداول الضرب في Excel و Google Sheets

إعداد البيانات

أدخل الأرقام 1-10 في الخلايا A2 إلى A11 ومرة أخرى في الخلايا B1 إلى K1 لإعداد البيانات.

يمكنك أيضًا وضع الأرقام في الخلايا بطريقة مثيرة للاهتمام باستخدام ميزة TRANSPOSE:

• املأ الأرقام 1-10 في الخلايا A2 إلى A11.
• للنطاق B1: K1، اكتب الصيغة التالية في شريط الصيغة.
• نظرًا لأن هذه صيغة صفيف، استخدم Ctrl + Shift + Enter لإكمالها.
• =TRANSPOSE (A2:A11)

7.5 جدول الضرب باستخدام المراجع المختلطة

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

=$A2*B$1

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

• Ctrl + C لنسخ الخلية B2.
• اختر مجموعة B2: K11.
• Ctrl + V ستقوم بلصق الصيغة.

7.6 جدول الضرب باستخدام صيغة المصفوفة

في الواقع، طريقة صيغة المصفوفة بسيطة. ما عليك سوى تحديد المجموعة B1:K1، وكتابة الصيغة في شريط الصيغة، والضغط على Ctrl + Shift + Enter لإدخال = A2:A11 * B1:K1.

سيتم استخدام هذه الطريقة لإنشاء جدول الضرب بأكمله.

7.7 إنشاء جدول الضرب في جداول بيانات Google

في كل من جداول بيانات Google و Excel، تعمل طريقة إنشاء جدول الضرب بنفس الطريقة:

7.8 الترقيم في Excel

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

كيفية إضافة رقم تسلسلي تلقائيًا في Excel؟

هناك العديد من التقنيات لتقدير عدد الصفوف في Excel.

1. باستخدام مقبض التعبئة
2. الاستفادة من سلسلة التعبئة
3. باستخدام دالة ROW

باستخدام Fill Handle

يتعرف هذا بسرعة على نمط في عدد قليل من الخلايا المعبأة مسبقًا ويطبقه في جميع أنحاء العمود. تحقق أدناه من بعض الأمثلة.

اتبع الخطوات التالية:

1. املأ الفراغات في الخلايا A3 و A4 بواحد.
2. كما هو موضح في الصورة أدناه، حدد جميع الخلايا.
3. يحتوي Excel على مربع صغير محاط باللون الأحمر بعنوان “مقبض التعبئة”، كما هو موضح في الصورة أعلاه.
4. باستخدام مؤشر الماوس على هذا المربع، انقر نقرًا مزدوجًا على مقبض التعبئة.
5. قبل الانتهاء من مجموعة البيانات، فإنه سيملأ جميع الخلايا.
6. انظر إلى التوضيح أدناه.

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

الاستفادة من Full Series

تمنحك هذه الميزة تحكمًا أكبر في كيفية إدخال الأرقام التسلسلية في Excel. افترض أنك أحرزت درجة أقل من متوسط ​​جميع الطلاب في كل موضوع.

اتبع الخطوات أدناه لملء سلسلة في Excel: اكتب 1 في الخلية A3.

• حدد زر HOME. انتقل إلى منطقة التحرير وحدد خيار التعبئة، كما هو موضح في الصورة أدناه.
• اختر Fill من القائمة المنسدلة.
• هناك العديد من الخيارات. حدد Series، كما هو موضح في لقطة الشاشة أدناه.

1. سيظهر مربع حوار جديد، كما هو موضح أدناه.
2. في القسم، اضغط على الأعمدة أسفل السلسلة.
3. اكتب القيمة في حقل “Stop Value”. يوجد حد أقصى يبلغ عشرة سجلات ؛ أدخل 10.
4. لن يعمل خيار Fill Series إذا تركت هذه القيمة فارغة.
5. ثم اضغط على زر “موافق”. نتيجة لذلك، سيتم “ملء” صفوف الأرقام التسلسلية التي تتراوح من 1 إلى 10. انظر إلى التوضيح أدناه.

باستخدام دالة ROW

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

تعيد ميزة ROW إجمالي صف Excel للصف الحالي. اطرح واحدًا إذا بدأت البيانات في الصف الثاني. انظر إلى التوضيح أدناه. =ROW () – 3 هي صيغة يمكنها حساب عدد الصفوف في صف.

اسحب الصيغة فوق الصفوف المتبقية، والنتيجة كالتالي:

نصائح حول الترقيم في Excel

• يتم تثبيت جميع إعدادات Fill Handle و Fill Series.
• سواء قمت بتمرير سجل أو حذفه أو صف في مجموعة البيانات، يظل رقم الصف كما هو.

يمكن أن تمنحك أداة ROW في Excel عمليات حسابية دقيقة إذا قمت بقص ونسخ البيانات.