في Microsoft Excel ، من المهم الرجوع إليهاإلى الخلايا في أوراق العمل الأخرى أو حتى في ملفات Excel مختلفة. في البداية ، قد يبدو هذا أمرًا مخيفًا ومربكًا بعض الشيء ، ولكن بمجرد فهم كيفية عمله ، فإنه ليس بالأمر الصعب.
في هذه المقالة ، سننظر في كيفية الرجوع إليهاورقة أخرى في نفس ملف Excel وكيفية الرجوع إلى ملف Excel آخر. سنغطي أيضًا أشياء مثل كيفية الرجوع إلى نطاق خلايا في دالة ، وكيفية جعل الأمور أكثر بساطة مع أسماء محددة ، وكيفية استخدام VLOOKUP للمراجع الديناميكية.
كيفية الرجوع إلى ورقة أخرى في ملف Excel نفسه
تتم كتابة مرجع الخلية الأساسية كحرف العمود متبوعًا برقم الصف.
لذلك يشير مرجع الخلية B3 إلى الخلية عند تقاطع العمود B والصف 3.
عند الإشارة إلى الخلايا الموجودة على أوراق أخرى ، يسبق مرجع الخلية هذا اسم الورقة الأخرى. على سبيل المثال ، أدناه عبارة عن إشارة إلى الخلية B3 على اسم ورقة "يناير".
=January!B3
علامة التعجب (!) تفصل اسم الورقة من عنوان الخلية.
إذا كان اسم الورقة يحتوي على مسافات ، فعليك إحاطة الاسم بعلامات اقتباس مفردة في المرجع.
='January Sales'!B3
لإنشاء هذه المراجع ، يمكنك كتابتها مباشرة في الخلية. ومع ذلك ، فمن الأسهل والأكثر موثوقية السماح لـ Excel بكتابة المرجع لك.
اكتب علامة المساواة (=) في خلية ، وانقر فوق علامة التبويب "ورقة" ، ثم انقر فوق الخلية التي تريد الإسناد التبادلي لها.
أثناء القيام بذلك ، يكتب Excel المرجع لك في شريط الصيغة.
اضغط على Enter لإكمال الصيغة.
كيفية الرجوع ملف Excel آخر
يمكنك الرجوع إلى خلايا مصنف آخر باستخدام نفس الأسلوب. فقط تأكد من فتح ملف Excel الآخر قبل البدء في كتابة الصيغة.
اكتب علامة المساواة (=) ، قم بالتبديل إلى الملف الآخر ، ثم انقر فوق الخلية الموجودة في الملف الذي تريد الرجوع إليه. اضغط على Enter عندما تنتهي.
يحتوي المرجع التبادلي المكتمل على اسم المصنف الآخر المحاط بأقواس مربعة ، متبوعًا باسم الورقة ورقم الخلية.
=[Chicago.xlsx]January!B3
إذا كان اسم الملف أو الورقة يحتوي على مسافات ، فستحتاج إلى إحاطة مرجع الملف (بما في ذلك الأقواس المربعة) بعلامات اقتباس مفردة.
='[New York.xlsx]January'!B3
في هذا المثال ، يمكنك رؤية علامات الدولار ($) بين عنوان الخلية. هذا مرجع للخلية المطلقة (تعرف على المزيد حول مراجع الخلايا المطلقة).
عند الرجوع إلى الخلايا والنطاقات على ملفات Excel مختلفة ، تكون المراجع مطلقة بشكل افتراضي. يمكنك تغيير هذا إلى مرجع نسبي إذا لزم الأمر.
إذا نظرت إلى الصيغة عند إغلاق المصنف المشار إليه ، فسيحتوي المسار بأكمله على هذا الملف.
على الرغم من إنشاء إشارات إلى المصنفات الأخرىهو واضح ، فهي أكثر عرضة للقضايا. يمكن للمستخدمين الذين يقومون بإنشاء أو إعادة تسمية المجلدات ونقل الملفات كسر هذه المراجع والتسبب في حدوث أخطاء.
الاحتفاظ بالبيانات في مصنف واحد ، إن أمكن ، أكثر موثوقية.
كيفية تقاطع مرجع نطاق الخلية في دالة
الرجوع إلى خلية واحدة مفيد بما فيه الكفاية. لكن قد ترغب في كتابة دالة (مثل SUM) تشير إلى نطاق من الخلايا في ورقة عمل أو مصنف آخر.
ابدأ الوظيفة كالمعتاد ثم انقر على الورقة ونطاق الخلايا — بنفس الطريقة التي اتبعتها في الأمثلة السابقة.
في المثال التالي ، تقوم دالة SUM بتجميع القيم من النطاق B2: B6 في ورقة عمل باسم المبيعات.
=SUM(Sales!B2:B6)
كيفية استخدام الأسماء المحددة لمراجع الصليب البسيطة
في Excel ، يمكنك تعيين اسم لخلية أومجموعة من الخلايا. هذا هو أكثر أهمية من عنوان الخلية أو النطاق عندما تنظر إليها. إذا كنت تستخدم الكثير من المراجع في جدول البيانات الخاص بك ، فإن تسمية هذه المراجع يمكن أن يسهل عليك رؤية ما قمت به.
والأفضل من ذلك ، أن هذا الاسم فريد لكل أوراق العمل في ملف Excel هذا.
على سبيل المثال ، يمكننا تسمية خلية "ChicagoTotal" ثم نص المرجع التبادلي كما يلي:
=ChicagoTotal
هذا بديل أكثر أهمية لمرجع قياسي مثل هذا:
=Sales!B2
من السهل إنشاء اسم محدد. ابدأ بتحديد الخلية أو نطاق الخلايا التي تريد تسميتها.
انقر في "مربع الاسم" في الزاوية العلوية اليسرى ، واكتب الاسم الذي تريد تعيينه ، ثم اضغط على "إدخال".
عند إنشاء أسماء محددة ، لا يمكنك استخدامهاالمسافات. لذلك ، في هذا المثال ، تم ضم الكلمات بالاسم وفصلها بحرف كبير. يمكنك أيضًا فصل كلمات بأحرف مثل الواصلة (-) أو الشرطة السفلية (_).
يحتوي Excel أيضًا على "مدير الأسماء"مراقبة هذه الأسماء في المستقبل سهلة. انقر فوق الصيغ> مدير الاسم. في نافذة إدارة الأسماء ، يمكنك رؤية قائمة بجميع الأسماء المعرفة في المصنف ، وأين توجد ، وما هي القيم التي تخزنها حاليًا.
يمكنك بعد ذلك استخدام الأزرار الموجودة في الأعلى لتعديل وحذف هذه الأسماء المعرفة.
كيفية تنسيق البيانات كجدول
عند العمل مع قائمة واسعة من البيانات ذات الصلة ، يمكن أن يؤدي استخدام ميزة "تنسيق Excel كجدول" في Excel إلى تبسيط طريقة الرجوع إلى البيانات فيه.
خذ الجدول البسيط التالي.
يمكن تنسيق هذا كجدول.
انقر فوق خلية في القائمة ، وانتقل إلى علامة التبويب "الصفحة الرئيسية" ، وانقر فوق الزر "تنسيق كجدول" ، ثم حدد نمطًا.
تأكد من أن نطاق الخلايا صحيح وأن الجدول يحتوي على رؤوس.
يمكنك بعد ذلك تعيين اسم ذي معنى لجدولك من علامة التبويب "تصميم".
بعد ذلك ، إذا احتجنا إلى جمع مبيعات شيكاغو ، فيمكننا الرجوع إلى الجدول باسمه (من أي ورقة) ، متبوعًا بقوس مربعة ([) لرؤية قائمة بأعمدة الجدول.
حدد العمود بالنقر فوقه نقرًا مزدوجًا في القائمة وأدخل قوسًا مربعًا مغلقًا. ستبدو الصيغة الناتجة مثل هذا:
=SUM(Sales[Chicago])
يمكنك معرفة كيف يمكن للجداول أن تجعل البيانات المرجعية لوظائف التجميع مثل SUM و AVERAGE أسهل من مراجع الورقة القياسية.
هذا الجدول صغير لأغراض العرض. كلما زاد حجم الجدول والمزيد من الأوراق التي لديك في مصنف ، زادت الفوائد التي تراها.
كيفية استخدام وظيفة VLOOKUP للمراجع الديناميكية
تم تثبيت المراجع المستخدمة في الأمثلة حتى الآن في خلية أو مجموعة محددة من الخلايا. هذا رائع وغالبًا ما يكفي لاحتياجاتك.
ومع ذلك ، ماذا لو كانت الخلية التي تشير إليها لديها القدرة على التغيير عند إدراج صفوف جديدة ، أو قيام شخص ما بفرز القائمة؟
في هذه السيناريوهات ، لا يمكنك ضمان أن القيمة التي تريدها ستظل في نفس الخلية التي أشرت إليها في البداية.
بديل في هذه السيناريوهات هو استخدام دالة بحث داخل Excel للبحث عن القيمة في قائمة. هذا يجعلها أكثر دواما ضد التغييرات على ورقة.
في المثال التالي ، نستخدم وظيفة VLOOKUP للبحث عن موظف في ورقة أخرى بواسطة معرف الموظف الخاص بهم ثم إرجاع تاريخ البدء.
فيما يلي مثال لقائمة الموظفين.
تنظر وظيفة VLOOKUP إلى أسفل العمود الأول من الجدول ، ثم تُرجع المعلومات من عمود محدد إلى اليمين.
تبحث وظيفة VLOOKUP التالية عن معرف الموظف الذي تم إدخاله في الخلية A2 في القائمة الموضحة أعلاه وتُرجع التاريخ المنضم إلى العمود 4 (العمود الرابع من الجدول).
=VLOOKUP(A2,Employees!A:E,4,FALSE)
فيما يلي توضيح لكيفية بحث هذه الصيغة في القائمة وإرجاع المعلومات الصحيحة.
إن الشيء العظيم في VLOOKUP على الأمثلة السابقة هو أنه سيتم العثور على الموظف حتى لو تغيرت القائمة بالترتيب.
ملحوظة: VLOOKUP هي صيغة مفيدة بشكل لا يصدق ، وقد خدشنا سطح قيمته في هذه المقالة فقط. يمكنك معرفة المزيد حول كيفية استخدام VLOOKUP من مقالتنا حول هذا الموضوع.
في هذه المقالة ، نظرنا في طرق متعددةالإسناد الترافقي بين جداول بيانات Excel والمصنفات. اختر الطريقة التي تناسب مهمتك في متناول اليد ، والتي تشعر بالراحة في العمل معها.
