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

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

ملاحة المدرسة

1. لماذا تحتاج الصيغ والوظائف?
2. تعريف وخلق صيغة
3. مرجع الخلية النسبية والمطلقة ، والتنسيق
4. وظائف مفيدة يجب أن تعرفها
5. عمليات البحث والرسوم البيانية والإحصائيات والجداول المحورية

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

ما هو مرجع الخلية?

"مرجع الخلية" يعني الخلية التي تشير إليها خلية أخرى. على سبيل المثال ، إذا كان في الخلية A1 لديك = A2. ثم يشير A1 إلى A2.

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

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

أول خلية في جدول البيانات هي A1 ، مما يعني أن العمود A ، الصف 1 ، B3 يشير إلى الخلية الموجودة في العمود الثاني والصف الثالث ، وهكذا.

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

أنواع مراجع الخلية

هناك ثلاثة أنواع من مراجع الخلايا.

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

النسبية - تعني الإشارة النسبية أن عنوان الخلية يتغير كلما قمت بنسخه أو نقله ؛ أي أن مرجع الخلية يتعلق بموقعه.

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

المراجع النسبية

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

لنفترض الآن أننا نحتاج إلى توفير مساحة في جدول البيانات لمزيد من البيانات. نحتاج إلى إضافة أعمدة أعلى والصفوف إلى اليسار ، لذا يتعين علينا نقل الخلية إلى الأسفل وإلى اليمين لإفساح المجال.

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

بالاستمرار في مثالنا ، والنظر إلى الرسم أدناه ، إذا قمت بنسخ محتويات الخلية A1 إلى اليمين وأربعة لأسفل ، فقد قمت بنقلها إلى الخلية C5.

لقد نسخت الخلية عمودين إلى اليمين وأربعة لأسفل. هذا يعني أننا قمنا بتغيير الخلية التي تشير إلى اثنين عبر وأربعة إلى الأسفل. A1 = A2 الآن هو C5 = C6. بدلاً من الإشارة إلى A2 ، تشير الخلية C5 الآن إلى الخلية C6.

القيمة المعروضة هي 0 لأن الخلية C6 فارغة. في الخلية C6 ، نكتب "أنا C6" والآن يعرض C5 "I C6".

مثال: صيغة النص

دعونا نحاول مثال آخر. تذكر من الدرس 2 حيث كان علينا تقسيم الاسم الكامل إلى الاسم الأول واسم العائلة؟ ماذا يحدث عند نسخ هذه الصيغة?

اكتب الصيغة = RIGHT (A3، LEN (A3) - FIND ("،"، A3) - 1) أو انسخ النص إلى الخلية C3. لا تقم بنسخ الخلية الفعلية ، فقط النص ، قم بنسخ النص ، وإلا فسيتم تحديث المرجع.

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

الآن لدينا:

لا شيء معقد ، لقد كتبنا للتو صيغة جديدة في الخلية C3. الآن نسخ C3 إلى الخلايا C2 و C4. مراقبة النتائج أدناه:

الآن لدينا الأسماء الأولى لألكسندر هاملتون وتوماس جفرسون.

استخدم المؤشر لتمييز الخلايا C2 و C3 و C4. قم بتوجيه المؤشر إلى الخلية B2 وقم بلصق المحتويات. انظر إلى ما حدث - نحصل على خطأ: "#REF". لماذا هذا?

عندما قمنا بنسخ الخلايا من العمود C إلى العمود B قام بتحديث المرجع عمود واحد إلى اليسار = RIGHT (A2، LEN (A2) - FIND ("،"، A2) - 1).

لقد غيرت كل إشارة إلى A2 إلى العمود على يسار A ، ولكن لا يوجد عمود إلى يسار العمود A. لذا لا يعرف الكمبيوتر ما تقصده.

الصيغة الجديدة في B2 على سبيل المثال ، هي = RIGHT (#REF!، LEN (#REF!) - FIND ("،"، # REF!) - 1) والنتيجة هي #REF:

نسخ صيغة إلى نطاق من الخلايا

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

نقصد بـ "المدى" أكثر من خلية واحدة. على سبيل المثال ، يعني (C1: C10) كافة الخلايا من الخلية C1 إلى الخلية C10. لذلك هو عمود من الخلايا. مثال آخر (A1: AZ1) هو الصف العلوي من العمود A إلى العمود AZ.

إذا كان النطاق يتخطى خمسة أعمدة و 10 صفوف ، فأنت تشير إلى النطاق بكتابة الخلية العلوية اليمنى والسفلى لليمين ، على سبيل المثال ، A1: E10. هذه منطقة مربعة تعبر الصفوف والأعمدة وليست مجرد جزء من عمود أو جزء من صف.

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

الآن نسخ الصيغة في الخلية C3 (= B3 + C2) إلى باقي العمود لإعطاء رصيد قيد التشغيل لميزانيتنا. يقوم Excel بتحديث مرجع الخلية أثناء نسخه. وتظهر النتيجة أدناه:

كما ترى ، يتم تحديث كل خلية جديدة نسبيا إلى الموقع الجديد ، بحيث تقوم الخلية C4 بتحديث صيغتها إلى = B4 + C3:

تحديثات الخلية C5 إلى = B5 + C4 ، وما إلى ذلك:

مراجع مطلقة

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

على سبيل المثال ، أدخل الصيغة = $ A $ 1 في أي خلية. لا يعني تغيير قيمة $ أمام العمود A تغيير العمود ، يعني $ أمام الصف 1 عدم تغيير العمود عند نسخ الخلية أو نقلها إلى أي خلية أخرى.

كما ترى في المثال أدناه ، في الخلية B1 لدينا مرجع نسبي = A1.عندما نقوم بنسخ B1 إلى الخلايا الأربعة أسفله ، يتغير المرجع النسبي = A1 إلى الخلية إلى اليسار ، بحيث تصبح B2 A2 و B3 من الواضح أن هذه الخلايا ليس لها قيمة مدخلة ، وبالتالي فإن الناتج هو صفر.

ومع ذلك ، إذا استخدمنا = $ A1 $ 1 ، كما هو الحال في C1 ونقوم بنسخها إلى الخلايا الأربع أدناه ، فإن المرجع مطلق ، وبالتالي لا يتغير أبدًا ويكون المخرجات دائمًا مساوية للقيمة في الخلية A1.

لنفترض أنك تتعقب اهتمامك ، كما هو موضح في المثال أدناه. الصيغة في C4 = B4 * B1 هي "نسبة الفائدة" * "الرصيد" = "الفائدة في السنة".

الآن ، لقد قمت بتغيير ميزانيتك وحفظت مبلغًا إضافيًا قدره 2،000 دولار لشراء صندوق مشترك. لنفرض أنه صندوق ذو سعر ثابت ويدفع سعر الفائدة نفسه. أدخل الحساب الجديد والتوازن في جدول البيانات ثم انسخ الصيغة = B4 * B1 من الخلية C4 إلى الخلية C5.

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

الصندوق المشترك الجديد يكسب 0 دولار في الفائدة سنويا ، وهو ما لا يمكن أن يكون صحيحا لأن سعر الفائدة هو 5 في المئة بشكل واضح.

يسلط Excel الضوء على الخلايا التي تشير إليها الصيغة. يمكنك أن ترى أعلاه أن الإشارة إلى معدل الفائدة (B1) يتم نقلها إلى الخلية الفارغة B2. كان ينبغي لنا أن نجعل الإشارة إلى B1 مطلقة من خلال كتابة $ B $ 1 باستخدام علامة الدولار لترسيخ مرجع الصف والعمود.

أعد كتابة الحساب الأول في C4 للقراءة = B4 * $ B $ 1 كما هو موضح أدناه:

ثم نسخ تلك الصيغة من C4 إلى C5. يبدو الآن جدول البيانات كالتالي:

ونظرًا لأننا نسخنا الخلية إلى أسفل الخلية ، أي زيادة الصف بمقدار واحد ، تكون الصيغة الجديدة = B5 * $ B $ 1. يتم حساب معدل فائدة الصناديق المشتركة بشكل صحيح الآن ، لأن سعر الفائدة مرتبط بالخلية B1.

هذا مثال جيد على الوقت الذي يمكنك فيه استخدام "اسم" للإشارة إلى خلية. الاسم هو المرجع المطلق. على سبيل المثال ، لتعيين الاسم "سعر الفائدة" إلى الخلية B1 ، انقر بزر الماوس الأيمن فوق الخلية ثم حدد "define name".

يمكن أن تشير الأسماء إلى خلية واحدة أو نطاق ، ويمكنك استخدام اسم في صيغة ، على سبيل المثال = interest_rate * 8 هو نفس الشيء مثل الكتابة = $ B $ 1 * 8.

مراجع مختلطة

المراجع المختلطة هي متى إما الصف أو العمود هو الراسية.

على سبيل المثال ، لنفترض أنك أحد المزارعين يضع ميزانية. أنت تمتلك أيضًا متجرًا لبيع الخلاصات وتبيع البذور. أنت ذاهب لزرع الذرة وفول الصويا والبرسيم. يعرض جدول البيانات أدناه التكلفة لكل فدان. "التكلفة لكل فدان" = "سعر لكل رطل" * "رطل من البذور لكل فدان" - هذا ما سيكلفك زراعة فدان.

أدخل التكلفة لكل فدان كـ = $ B2 * C2 في الخلية D2. أنت تقول أنك تريد إرساء السعر لكل عمود باوند. ثم انسخ تلك الصيغة إلى الصفوف الأخرى في نفس العمود:

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

نضيف عمودين: "رطل البذرة في المخزون" ثم "قيمة المخزون". الآن ، قم بنسخ الخلية من D2 إلى F4 ولاحظ أنه تم تحديث مرجع الصف في الجزء الأول من الصيغة الأصلية ($ B2) إلى الصف 4 ولكن العمود يظل ثابتًا نظرًا لأن $ يثبت به إلى "ب".

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

المراجع الدائرية

المرجع المعاد هو عندما تشير صيغة إلى نفسها.

على سبيل المثال ، لا يمكنك كتابة c3 = c3 + 1. يسمى هذا النوع من الحسابات بـ "التكرار" بمعنى أنه يعيد نفسه. لا يدعم Excel التكرار لأنه يحسب كل شيء مرة واحدة فقط.

إذا حاولت القيام بذلك بكتابة SUM (B1: B5) في الخلية B5:

تظهر شاشة تحذير:

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

إذا كان لديك مرجع معاد ، في كل مرة تفتح فيها جدول البيانات ، سيخبرك Excel بهذه النافذة المنبثقة التي لديك مرجع دائري.

مراجع لورقات العمل الأخرى

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

تسمى أوراق العمل بشكل افتراضي Sheet1 و Sheet2 وما. يمكنك إنشاء واحدة جديدة بالنقر فوق "+" في الجزء السفلي من شاشة Excel.

يمكنك تغيير اسم ورقة العمل إلى شيء مفيد مثل "قرض" أو "ميزانية" عن طريق النقر بزر الماوس الأيمن على علامة تبويب ورقة العمل المعروضة في الجزء السفلي من شاشة برنامج Excel ، واختيار إعادة التسمية ، وكتابة اسم جديد.

أو يمكنك ببساطة النقر المزدوج على علامة التبويب وإعادة تسميتها.

بناء الجملة لمرجع ورقة العمل هو = ورقة عمل! يمكنك استخدام هذا النوع من المرجع عند استخدام نفس القيمة في صحيفتي عمل ، وقد تكون أمثلة على ذلك:

• تاريخ اليوم
• سعر تحويل العملة من الدولار إلى اليورو
• أي شيء له علاقة بجميع أوراق العمل في المصنف

في ما يلي مثال على ورقة العمل "الاهتمام" التي تشير إلى ورقة العمل "قرض" ، الخلية B1.

إذا نظرنا إلى ورقة العمل "القرض" ، يمكننا أن نرى الإشارة إلى مبلغ القرض:

التالي…

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

هذا هو الدرس اليوم ، في الدرس 4 ، سنناقش بعض الوظائف المفيدة التي قد ترغب في معرفتها لاستخدام Excel اليومي.