المحتويات
غالبًا ما يواجه المستخدمون الحاجة إلى تثبيت خلية في صيغة. على سبيل المثال ، يحدث هذا في المواقف التي تريد فيها نسخ صيغة ، ولكن بحيث لا يتحرك الارتباط لأعلى ولأسفل بنفس عدد الخلايا التي تم نسخها من موقعها الأصلي.
في هذه الحالة ، يمكنك إصلاح مرجع الخلية في Excel. ويمكن القيام بذلك بعدة طرق في وقت واحد. دعونا نلقي نظرة فاحصة على كيفية تحقيق هذا الهدف.
ما هو ارتباط Excel
تتكون الورقة من خلايا. كل واحد منهم يحتوي على معلومات محددة. يمكن للخلايا الأخرى استخدامها في العمليات الحسابية. لكن كيف يفهمون من أين يحصلون على البيانات؟ يساعدهم في إنشاء الروابط.
يعين كل ارتباط خلية بحرف واحد ورقم واحد. يمثل الحرف عمودًا ويمثل الرقم صفًا.
هناك ثلاثة أنواع من الروابط: مطلقة ونسبية ومختلطة. يتم تعيين الثانية بشكل افتراضي. المرجع المطلق هو المرجع الذي يحتوي على عنوان ثابت لكل من العمود والعمود. وفقًا لذلك ، يكون مختلط هو المكان الذي يتم فيه إصلاح عمود منفصل أو صف.
طريقة 1
لحفظ عناوين الأعمدة والصفوف ، اتبع الخطوات التالية:
- انقر فوق الخلية التي تحتوي على الصيغة.
- انقر فوق شريط الصيغة للخلية التي نحتاجها.
- اضغط على F4.
نتيجة لذلك ، سيتغير مرجع الخلية إلى مطلق. يمكن التعرف عليه بعلامة الدولار المميزة. على سبيل المثال ، إذا نقرت على الخلية B2 ثم نقرت على F4 ، فسيبدو الرابط كالتالي: $ B $ 2.
ماذا تعني علامة الدولار قبل جزء العنوان لكل خلية؟
- إذا تم وضعه أمام حرف ، فهذا يشير إلى أن مرجع العمود يظل كما هو ، بغض النظر عن مكان نقل الصيغة.
- إذا كانت علامة الدولار أمام الرقم ، فإنها تشير إلى أن السلسلة مثبتة.
طريقة 2
هذه الطريقة هي نفس الطريقة السابقة تقريبًا ، ما عليك سوى الضغط على F4 مرتين. على سبيل المثال ، إذا كانت لدينا الخلية B2 ، فستصبح بعد ذلك B $ 2. بكلمات بسيطة ، بهذه الطريقة تمكنا من إصلاح الخط. في هذه الحالة ، سيتغير حرف العمود.
إنه ملائم للغاية ، على سبيل المثال ، في الجداول حيث تحتاج إلى عرض محتويات الخلية الثانية من الأعلى في الخلية السفلية. بدلاً من القيام بهذه الصيغة عدة مرات ، يكفي إصلاح الصف وترك العمود يتغير.
طريقة 3
هذه هي الطريقة السابقة تمامًا ، ما عليك سوى الضغط على المفتاح F4 ثلاث مرات. عندئذٍ ستكون الإشارة إلى العمود فقط مطلقة ، وسيظل الصف ثابتًا.
طريقة 4
لنفترض أن لدينا مرجعًا مطلقًا لخلية ، ولكن كان من الضروري هنا جعلها نسبية. للقيام بذلك ، اضغط على المفتاح F4 مرات عديدة بحيث لا توجد علامات $ في الارتباط. بعد ذلك سيصبح نسبيًا ، وعند نقل الصيغة أو نسخها ، سيتغير كل من عنوان العمود وعنوان الصف.
تثبيت الخلايا لنطاق كبير
نرى أن الطرق المذكورة أعلاه لا تقدم أي صعوبة على الإطلاق في الأداء. لكن المهام محددة. وعلى سبيل المثال ، ماذا نفعل إذا كان لدينا عشرات الصيغ في وقت واحد ، الروابط التي يجب تحويلها إلى روابط مطلقة.
لسوء الحظ ، لن تحقق طرق Excel القياسية هذا الهدف. للقيام بذلك ، تحتاج إلى استخدام ملحق خاص يسمى VBA-Excel. يحتوي على العديد من الميزات الإضافية التي تسمح لك بأداء المهام الشائعة باستخدام Excel بشكل أسرع.
يتضمن أكثر من مائة وظيفة محددة من قبل المستخدم و 25 وحدة ماكرو مختلفة ، ويتم تحديثها أيضًا بانتظام. يسمح لك بتحسين العمل مع أي جانب تقريبًا:
- الخلايا.
- الماكرو.
- وظائف من أنواع مختلفة.
- الروابط والمصفوفات.
على وجه الخصوص ، تسمح لك هذه الوظيفة الإضافية بإصلاح الروابط في عدد كبير من الصيغ مرة واحدة. للقيام بذلك ، يجب عليك تنفيذ الخطوات التالية:
- حدد النطاق.
- افتح علامة التبويب VBA-Excel التي ستظهر بعد التثبيت.
- افتح قائمة "الوظائف" ، حيث يوجد خيار "تأمين الصيغ".
6 - بعد ذلك ، سيظهر مربع حوار تحتاج فيه إلى تحديد المعلمة المطلوبة. تسمح لك هذه الوظيفة الإضافية بتثبيت عمود وعمود بشكل منفصل ، معًا ، وكذلك إزالة التثبيت الموجود بالفعل مع الحزمة. بعد تحديد المعلمة المطلوبة باستخدام زر الاختيار المقابل ، تحتاج إلى تأكيد إجراءاتك بالنقر فوق "موافق".
مثال
لنأخذ مثالاً لجعله أكثر وضوحًا. لنفترض أن لدينا معلومات تصف تكلفة البضائع وكميتها الإجمالية وإيرادات المبيعات. ونحن نواجه مهمة جعل الجدول ، بناءً على الكمية والتكلفة ، يحدد تلقائيًا مقدار الأموال التي تمكنا من كسبها دون خصم الخسائر.
في مثالنا ، لهذا تحتاج إلى إدخال الصيغة =B2 * C2. الأمر بسيط للغاية ، كما ترى. من السهل جدًا استخدام مثالها لوصف كيفية إصلاح عنوان خلية أو عمودها أو صفها الفردي.
بالطبع ، في هذا المثال ، يمكنك محاولة سحب الصيغة لأسفل باستخدام علامة الملء التلقائي ، ولكن في هذه الحالة ، سيتم تغيير الخلايا تلقائيًا. لذلك ، في الخلية D3 ستكون هناك صيغة أخرى ، حيث سيتم استبدال الأرقام ، على التوالي ، بـ 3. علاوة على ذلك ، وفقًا للمخطط - D4 - ستأخذ الصيغة الشكل = B4 * C4 ، D5 - بالمثل ، ولكن مع رقم 5 وهلم جرا.
إذا كان ذلك ضروريًا (اتضح في معظم الحالات) ، فلا توجد مشاكل. ولكن إذا كنت بحاجة إلى إصلاح الصيغة في خلية واحدة بحيث لا تتغير عند السحب ، فسيكون ذلك أكثر صعوبة إلى حد ما.
لنفترض أننا بحاجة إلى تحديد عائدات الدولار. دعنا نضعها في الخلية B7. دعنا نشعر بالحنين قليلاً ونشير إلى تكلفة 35 روبل لكل دولار. وفقًا لذلك ، لتحديد الإيرادات بالدولار ، من الضروري تقسيم المبلغ بالروبل على سعر صرف الدولار.
هذا ما يبدو عليه في مثالنا.
إذا حاولنا ، على غرار الإصدار السابق ، وصف صيغة ، فسنفشل. وبالمثل ، ستتغير الصيغة إلى الصيغة المناسبة. في مثالنا ، سيكون مثل هذا: = E3 * B8. من هنا يمكننا أن نرى. أن الجزء الأول من الصيغة قد تحول إلى E3 ، وقمنا بتعيين أنفسنا هذه المهمة ، لكننا لا نحتاج إلى تغيير الجزء الثاني من الصيغة إلى B8. لذلك ، نحتاج إلى تحويل المرجع إلى مرجع مطلق. يمكنك القيام بذلك دون الضغط على المفتاح F4 ، فقط عن طريق وضع علامة الدولار.
بعد أن قمنا بتحويل المرجع إلى الخلية الثانية إلى خلية مطلقة ، أصبحت محمية من التغييرات. يمكنك الآن سحبها بأمان باستخدام مقبض الملء التلقائي. ستبقى جميع البيانات الثابتة كما هي ، بغض النظر عن موضع الصيغة ، وستتغير البيانات غير الملتزم بها بمرونة. في جميع الخلايا ، سيتم تقسيم الإيرادات بالروبل الموصوفة في هذا السطر على نفس سعر صرف الدولار.
ستبدو الصيغة نفسها كما يلي:
= D2 / $ B $ 7
انتباه! لقد أشرنا إلى علامتي دولار. بهذه الطريقة ، نظهر للبرنامج أن كلا من العمود والصف يحتاجان إلى الإصلاح.
مراجع الخلايا في وحدات الماكرو
الماكرو هو روتين فرعي يسمح لك بأتمتة الإجراءات. على عكس الوظيفة القياسية لبرنامج Excel ، يسمح لك الماكرو بتعيين خلية معينة على الفور وتنفيذ إجراءات معينة في بضعة أسطر من التعليمات البرمجية. مفيد للمعالجة المجمعة للمعلومات ، على سبيل المثال ، إذا لم تكن هناك طريقة لتثبيت الوظائف الإضافية (على سبيل المثال ، يتم استخدام كمبيوتر الشركة ، وليس الكمبيوتر الشخصي).
تحتاج أولاً إلى فهم أن المفهوم الأساسي للماكرو هو كائنات يمكن أن تحتوي على كائنات أخرى. كائن المصنفات مسؤول عن الكتاب الإلكتروني (أي المستند). يتضمن كائن جداول البيانات ، وهو عبارة عن مجموعة من جميع أوراق المستند المفتوح.
وفقًا لذلك ، تعتبر الخلايا كائن خلايا. يحتوي على جميع خلايا ورقة معينة.
كل كائن مؤهل بحجج بين قوسين. في حالة الخلايا ، تتم الإشارة إليها بهذا الترتيب. يتم إدراج رقم الصف أولاً ، متبوعًا برقم العمود أو الحرف (كلا التنسيقين مقبولان).
على سبيل المثال ، سيبدو سطر التعليمات البرمجية الذي يحتوي على مرجع إلى الخلية C5 كما يلي:
مصنفات (“Book2.xlsm”). أوراق (“List2”). خلايا (5 ، 3)
مصنفات (“Book2.xlsm”). أوراق (“List2”). خلايا (5 ، “C”)
يمكنك أيضًا الوصول إلى خلية باستخدام كائن أنيق - مرتب. بشكل عام ، الغرض منه هو إعطاء مرجع إلى نطاق (يمكن أن تكون عناصره ، بالمناسبة ، مطلقة أو نسبية) ، ولكن يمكنك ببساطة إعطاء اسم خلية ، بنفس التنسيق كما في مستند Excel.
في هذه الحالة ، سيبدو الخط هكذا.
مصنفات ("Book2.xlsm"). أوراق ("List2"). النطاق ("C5")
قد يبدو أن هذا الخيار أكثر ملاءمة ، لكن ميزة الخيارين الأولين هي أنه يمكنك استخدام المتغيرات بين قوسين وإعطاء رابط لم يعد مطلقًا ، ولكنه يشبه الارتباط النسبي ، والذي سيعتمد على نتائج الحسابات.
وبالتالي ، يمكن استخدام وحدات الماكرو بشكل فعال في البرامج. في الواقع ، ستكون جميع الإشارات إلى الخلايا أو النطاقات هنا مطلقة ، وبالتالي يمكن أيضًا إصلاحها بها. صحيح أنها ليست مريحة للغاية. يمكن أن يكون استخدام وحدات الماكرو مفيدًا عند كتابة برامج معقدة بعدد كبير من الخطوات في الخوارزمية. بشكل عام ، الطريقة القياسية لاستخدام المراجع المطلقة أو النسبية أكثر ملاءمة.
استنتاجات
لقد اكتشفنا ماهية مرجع الخلية ، وكيف يعمل ، وما الغرض منه. لقد فهمنا الفرق بين المراجع المطلقة والنسبية وتوصلنا إلى ما يجب القيام به لتحويل نوع إلى آخر (بكلمات بسيطة ، أو إصلاح العنوان أو إلغاء تثبيته). لقد اكتشفنا كيف يمكنك القيام بذلك على الفور بعدد كبير من القيم. الآن لديك المرونة لاستخدام هذه الميزة في المواقف المناسبة.