أساليب عنونة المطلقة في Microsoft Excel
كما تعلم ، في جداول Excel هناك نوعان من العناوين: نسبي ومطلق. في الحالة الأولى ، يتغير الارتباط في اتجاه النسخ حسب المقدار النسبي للتحول ، بينما في الحالة الثانية يتم إصلاحه ويبقى دون تغيير أثناء النسخ. لكن بشكل افتراضي جميع العناوين في Excel مطلقة. في نفس الوقت ، غالباً ما تكون هناك حاجة لاستخدام عنونة مطلقة (ثابتة). دعونا نعرف ما هي الطرق التي يمكن القيام بها.
تطبيق عنونة المطلقة
قد تكون هناك حاجة لعناوين مطلقة ، على سبيل المثال ، عندما نقوم بنسخ صيغة ، يتكون جزء منها من متغير معروض في سلسلة من الأرقام ، والثاني له قيمة ثابتة. وهذا يعني أن هذا الرقم يلعب دور معامل غير متغير ، والذي تحتاج إليه لإجراء عملية معينة (الضرب والقسمة وما إلى ذلك) إلى سلسلة كاملة من الأرقام المتغيرة.
في Excel ، هناك طريقتان لتحديد عنونة ثابتة: من خلال تشكيل مرجع مطلق واستخدام الدالة INDIRECT. دعونا ننظر في كل من هذه الأساليب بالتفصيل.
الطريقة 1: المرجع المطلق
وبطبيعة الحال ، فإن الطريقة الأكثر شهرة والتي تستخدم في الغالب لإنشاء عنونة مطلقة هي استخدام المراجع المطلقة. الروابط المطلقة لها اختلاف ليس فقط وظيفيًا ، بل أيضًا متشابكًا. يحتوي العنوان النسبي على بناء الجملة هذا:
=A1
في عنوان ثابت ، يتم تعيين علامة الدولار قبل قيمة الإحداثيات:
=$A$1
يمكن إدخال علامة الدولار يدويا. للقيام بذلك ، يجب وضع المؤشر قبل القيمة الأولى لإحداثيات العنوان (أفقيًا) في الخلية أو في شريط الصيغة. علاوة على ذلك ، في تخطيط لوحة المفاتيح الإنجليزية ، انقر فوق المفتاح "4" في الحالة العليا (مع الضغط على مفتاح "Shift" ). هناك يوجد رمز الدولار. ثم تحتاج إلى القيام بنفس الإجراء مع الإحداثيات الرأسية.
هناك طريقة أسرع. يجب وضع المؤشر في الخلية حيث يوجد العنوان ، وانقر فوق مفتاح الدالة F4. بعد ذلك ، ستظهر علامة الدولار فورًا أمام الإحداثيات الأفقية والرأسية للعنوان المعطى.
الآن دعونا ننظر في كيفية استخدام عنونة مطلقة في الممارسة عن طريق استخدام المراجع المطلقة.
خذ الجدول الذي يحسب أجور الموظفين. يتم الحساب بضرب قيمة راتبهم الشخصي بنسبة ثابتة ، وهي نفس النسبة لجميع الموظفين. يقع المعامل نفسه في خلية ورقة منفصلة. نحن نواجه مهمة حساب أجور جميع العمال بأقصى سرعة ممكنة.
- لذا ، في الخلية الأولى من عمود "الأجور" ، ندخل الصيغة لضرب معدل الموظف المقابل بالمعامِل. في حالتنا ، هذه الصيغة لها الشكل التالي:
=C4*G3 - لحساب النتيجة النهائية ، انقر فوق مفتاح Enter على لوحة المفاتيح. يتم عرض النتيجة في الخلية التي تحتوي على الصيغة.
- حسبنا الراتب للموظف الأول. الآن نحن بحاجة للقيام بذلك لجميع الخطوط الأخرى. بالطبع ، يمكن كتابة العملية إلى كل خلية من عمود "الأجور" يدويًا ، عن طريق إدخال نفس الصيغة مع تصحيح الإزاحة ، ولكن لدينا مهمة إجراء الحسابات بأسرع ما يمكن ، وسيستغرق الإدخال اليدوي الكثير من الوقت. ولماذا تهدر الجهد على الإدخال اليدوي ، إذا كان يمكن ببساطة نسخ الصيغة إلى خلايا أخرى؟
لنسخ الصيغة ، نستخدم أداة مثل علامة التعبئة. يصبح المؤشر في الزاوية اليمنى السفلى من الخلية حيث يتم احتوائه. في هذه الحالة ، يجب تحويل المؤشر نفسه إلى علامة التعبئة نفسها على شكل صليب. ثبت زر الماوس الأيسر واسحب المؤشر لأسفل حتى نهاية الجدول.
- ولكن ، كما نرى ، حصلنا على أصفار واحد بدلاً من الحساب الصحيح للأجور للموظفين الآخرين.
- نحن ننظر ، في سبب سبب هذه النتيجة. للقيام بذلك ، حدد الخلية الثانية في عمود "الأجور" . يتم عرض التعبير المقابل للخلية في شريط الصيغة. كما ترى ، فإن المضاعف الأول ( C5 ) يتوافق مع معدل الموظف الذي نتوقع رواتبه. ترجع إزاحة الإحداثيات مقارنة بالخلية السابقة إلى خاصية النسبية. ومع ذلك ، في هذه الحالة بالذات ، هذا هو ما نحتاجه. وبفضل هذا ، كان المضاعف الأول هو معدل الموظف المعين الذي نحتاجه. لكن تحول الإحداثي حدث أيضًا مع العامل الثاني. والآن لا يشير عنوانه إلى المعامل ( 1،28 ) ، بل إلى الخلية الفارغة أدناه.
![الصيغة المنسوخة في Microsoft Excel]()
هذا ما تسبب في حساب الأجور للموظفين لاحقة من القائمة تبين أن تكون غير صحيحة.
- لتصحيح الوضع ، نحتاج إلى تغيير عنوان العامل الثاني من نسبي إلى ثابت. للقيام بذلك ، ارجع إلى الخلية الأولى من عمود "الأجور" ، وحددها. بعد ذلك ، ننتقل إلى شريط الصيغة ، حيث يتم عرض التعبير الذي نحتاجه. حدد العامل الثاني ( G3 ) مع المؤشر واضغط على مفتاح الوظيفة على لوحة المفاتيح.
- كما ترون ، حول إحداثيات المضاعف الثاني ظهر علامة الدولار ، وهذا ، كما نتذكر ، هو سمة من سمات معالجة المطلقة. لعرض النتيجة على الشاشة ، اضغط على Enter .
- الآن ، كما كان من قبل ، قمنا باستدعاء علامة التعبئة بوضع المؤشر في الركن السفلي الأيمن من العنصر الأول في عمود "الأجور" . قم بضغط زر الماوس الأيسر واسحبه لأسفل.
- كما نلاحظ ، في هذه الحالة تم إجراء الحساب بشكل صحيح وتم حساب مبلغ الأجور لجميع العاملين في المؤسسة بشكل صحيح.
- دعونا نتحقق من كيفية نسخ الصيغة. للقيام بذلك ، حدد العنصر الثاني من عمود "الأجور" . انظر إلى التعبير في شريط الصيغة. وكما ترى ، فإن إحداثيات العامل الأول ( C5 ) ، والتي لا تزال نسبية ، قد حولت نقطة رأسية عمودية واحدة مقارنة بالخلية السابقة. لكن العامل الثاني ( $ G $ 3 ) ، الذي حددناه وعيناؤه ، لم يتغير.
في Excel ، يتم استخدام ما يسمى بالعنوان المختلط أيضًا. في هذه الحالة ، يتم إصلاح العمود أو الصف في عنوان العنصر. يتم تحقيق ذلك بطريقة يتم بها وضع علامة الدولار قبل أحد إحداثيات العنوان. في ما يلي مثال على رابط مختلط نموذجي:
=A$1
يعتبر هذا العنوان أيضًا مختلطًا:
=$A1
بمعنى ، يتم استخدام عنونة المطلق في الارتباط المختلط فقط من أجل واحدة من قيم إحداثي الاثنين.
دعونا نرى كيف يمكن تطبيق مثل هذا الارتباط المختلط في الممارسة مع مثال على جدول المرتبات نفسه لموظفي المؤسسة.
- كما ترون ، في وقت سابق فعلنا ذلك أن جميع الإحداثيات من المضاعف الثاني لديها عنونة المطلقة. ولكن دعونا نرى ، هل من الضروري في هذه الحالة ، يجب أن يتم إصلاح كل القيم؟ كما ترى ، عندما يحدث النسخ على إزاحة رأسية ، وتبقى الإحداثيات بدون تغيير. لذلك ، من الممكن تطبيق العنوان المطلق فقط على إحداثيات السلسلة ، وترك إحداثيات العمود كما هي افتراضياً - نسبي.
حدد العنصر الأول من عمود "الأجور" وقم بتنفيذ المعالجة المذكورة أعلاه في سطر الصيغة. نحصل على الصيغة التالية:
=C4*G$3كما ترى ، يتم تطبيق العنونة الثابتة في المضاعف الثاني فقط فيما يتعلق بإحداثيات السلسلة. لعرض النتيجة في خلية ، انقر فوق الزر Enter .
- ثم ، باستخدام علامة التعبئة ، قم بنسخ هذه الصيغة إلى نطاق الخلايا الموجودة أدناه. كما ترون ، يتم تنفيذ كشوف المرتبات لجميع الموظفين بشكل صحيح.
- انظر كيف يتم عرض الصيغة المنسوخة في الخلية الثانية للعمود الذي قمنا بتنفيذ التلاعب فوقه. كما يمكنك أن ترى في سطر الصيغة ، بعد تحديد هذا العنصر من الورقة ، على الرغم من حقيقة أن العنونة المطلقة للمضاعف الثاني ليس لها سوى إحداثيات الخطوط ، فإن إحداثيات إحداثيات العمود لم تحدث. هذا يرجع إلى حقيقة أننا نسخنا لا أفقيا ، ولكن عموديا. إذا قمنا بنسخ أفقيا ، في الحالة المشابهة ، على العكس ، سيكون علينا القيام بالعنونة الثابتة لإحداثيات العمود ، وبالنسبة للسلسلة ، سيكون هذا الإجراء اختياريًا.
الدرس: مراجع مطلقة ونسبية في Excel
الطريقة 2: وظيفة Alpha
الطريقة الثانية لتنظيم العنونة المطلقة في جدول بيانات Excel هي استخدام عامل التشغيل INDEX . تنتمي الوظيفة المحددة إلى مجموعة عوامل التشغيل المضمنة "المراجع والمصفوفات" . وتتمثل مهمتها في تشكيل مرجع للخلية المحددة مع إخراج النتيجة إلى هذا العنصر في الورقة التي يقع فيها المشغل. في هذه الحالة ، يكون الرابط مرتبطًا بالإحداثيات أقوى من استخدام علامة الدولار. لذلك ، من المعتاد في بعض الأحيان الإشارة إلى الارتباطات التي تستخدم DVSSYL كـ "superabsolute". يحتوي هذا البيان على بناء الجملة التالي:
=ДВССЫЛ(ссылка_на_ячейку;[a1])
تحتوي الدالة على وسيطتين ، أولهما إلزامي ، والثاني ليس كذلك.
تعتبر الوسيطة "ارتباط إلى خلية" ارتباطًا إلى عنصر ورقة Excel في نموذج نصي. بمعنى ، أنها مرجع عادي ، ولكن محاطة بعلامات اقتباس. هذا هو ما يجعل من الممكن توفير خصائص عنونة المطلقة.
تعتبر الحجة "a1" اختيارية ويتم استخدامها في حالات نادرة. ولا يكون استخدامه ضروريًا إلا عندما يختار المستخدم خيارًا بديلًا للعنونة ، بدلاً من الاستخدام المعتاد لنوع الإحداثيات "A1" (للأعمدة تحديد أبجدي ، والخطوط لها خطوط رقمية). ويتمثل البديل في استخدام نمط R1C1 ، حيث تشير الأعمدة ، مثل الصفوف ، إلى أرقام. قم بالتبديل إلى هذا النمط من العمليات من خلال نافذة خيارات Excel. ثم ، باستخدام عامل التشغيل INDEX ، استخدم القيمة "FALSE" كوسيطة "a1 " . إذا كنت تعمل في وضع العرض العادي للارتباط ، مثل معظم المستخدمين الآخرين ، فيمكنك تحديد القيمة "TRUE" كوسيطة "a1 " . ومع ذلك ، يتم تضمين هذه القيمة بشكل افتراضي ، لذا من الأسهل بكثير عدم تحديد الوسيطة "a1" في هذه الحالة.
دعونا ننظر في كيفية معالجة المطلق ، المنظمة بمساعدة من وظيفة DVSSYL ، سوف تعمل على مثال جدول الأجور لدينا.
- نختار العنصر الأول من عمود "الأجور" . ضع العلامة "=" . كما تذكر ، يجب أن يتم تمثيل العامل الأول في الصيغة أعلاه لحساب المرتب بواسطة عنوان نسبي. لذلك ، ببساطة انقر فوق الخلية التي تحتوي على القيمة المقابلة للمرتب ( C4 ). بعد كيف تم عرض عنوانه في العنصر لإخراج النتيجة ، انقر فوق زر "مضاعفة" ( * ) على لوحة المفاتيح. ثم نحتاج إلى الانتقال إلى استخدام المشغل الفردي . انقر فوق رمز "إدراج وظيفة" .
- في النافذة المفتوحة من معالجات الدوال انتقل إلى الفئة "المراجع والمصفوفات" . من بين قائمة الأسماء المعروضة ، نميز اسم "DVSSYL" . ثم انقر على زر "موافق" .
- يتم تنفيذ تفعيل نافذة الوسيطة للمشغل DVSSYL . يتكون من حقلين يتوافقان مع وسيطات هذه الوظيفة.
ضع المؤشر في حقل "مرجع الخلية" . انقر فقط فوق هذا العنصر من الورقة التي يوجد بها المعامل لحساب الراتب ( G3 ). سيظهر العنوان فورًا في حقل نافذة الحجج. إذا كنا نتعامل مع وظيفة عادية ، فيمكن اعتبار إدخال العنوان كاملاً ، لكننا نستخدم الدالة " ALGORITHM" . كما نتذكر ، يجب أن تحتوي العناوين الموجودة فيه على شكل نص. لذلك ، نقوم بحفظ الإحداثيات الموجودة في حقل النافذة بعلامات اقتباس.
نظرًا لأننا نعمل في وضع العرض القياسي للتنسيق ، يتم ترك الحقل "A1" فارغًا. نضغط على زر "موافق" .
- يحسب التطبيق ويعرض النتيجة في عنصر ورقة يحتوي على الصيغة.
- الآن نقوم بنسخ هذه الصيغة إلى جميع الخلايا الأخرى في عمود "الأجور" باستخدام علامة التعبئة ، كما فعلنا من قبل. كما ترى ، تم حساب جميع النتائج بشكل صحيح.
- دعونا نرى كيف يتم عرض الصيغة في واحدة من الخلايا التي تم نسخها. حدد العنصر الثاني من العمود وانظر إلى صف الصيغة. كما ترى ، قام المضاعف الأول ، وهو مرجع نسبي ، بتغيير إحداثياته. في الوقت نفسه ، ظلت حجة العامل الثاني ، والتي تتمثل في وظيفة ALGORITHM ، دون تغيير. في هذه الحالة ، تم استخدام تقنية العنونة الثابتة.
الدرس: مشغل للصناعة في Excel
يمكن توفير العنونة المطلقة في جداول Excel بطريقتين: استخدام وظيفة INDEX واستخدام المراجع المطلقة. في هذه الحالة ، توفر الوظيفة ربطًا أكثر صرامة بالعنوان. يمكن أيضًا استخدام العنونة المطلقة جزئيًا عند استخدام الروابط المختلطة.