الأخبار التكنولوجية والاستعراضات والنصائح!

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel

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

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

كيفية الرجوع إلى ورقة أخرى في نفس ملف Excel

تتم كتابة مرجع الخلية الأساسي كحرف عمود متبوعًا برقم الصف.

لذلك يشير مرجع الخلية B3 إلى الخلية عند تقاطع العمود B والصف 3.

عندما تشير إلى خلايا في أوراق أخرى ، فإن مرجع الخلية هذا مسبوق باسم الورقة الأخرى. على سبيل المثال ، يوجد أدناه مرجع إلى الخلية B3 على ورقة تسمى “يناير”.

= يناير! B3

تفصل علامة التعجب (!) اسم الورقة عن عنوان الخلية.

إذا كان اسم الورقة يحتوي على مسافات ، فيجب إحاطة الاسم بعلامات اقتباس مفردة في المرجع.

= “مبيعات يناير”! B3

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

اكتب علامة المساواة (=) في خلية ، وانقر فوق علامة التبويب ورقة ، ثم انقر فوق الخلية التي تريد الإسناد الترافقي إليها.

عند القيام بذلك ، يكتب Excel المرجع لك في شريط الصيغة.

اضغط على Enter لإكمال الصيغة.

كيفية الرجوع إلى ملف Excel آخر

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

اكتب علامة المساواة (=) ، وقم بالتبديل إلى الملف الآخر ، ثم انقر فوق الخلية الموجودة في الملف الذي تريد الرجوع إليه. اضغط على رجوع عند الانتهاء.

يحتوي المرجع الترافقي المكتمل على اسم المصنف الآخر بين قوسين مربعين ، متبوعًا باسم الورقة ورقم الخلية.

=[Chicago.xlsx]يناير! B3

إذا كان اسم الملف أو الورقة يحتوي على مسافات ، فيجب إحاطة مرجع الملف (بما في ذلك الأقواس المربعة) بعلامات اقتباس فردية.

= ‘[New York.xlsx]يناير ‘! B3

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 1

في هذا المثال ، يمكنك رؤية علامة الدولار ($) بين عنوان الخلية. هذا مرجع خلية مطلق (اقرأ المزيد حول مراجع الخلايا المطلقة).

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

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

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 2

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

يعد الاحتفاظ بالبيانات في مصنف ، إن أمكن ، أكثر موثوقية.

كيفية الإسناد الترافقي إلى نطاق من الخلايا في دالة

تعتبر الإشارة إلى خلية واحدة مفيدة بدرجة كافية. ولكن قد ترغب في كتابة دالة (مثل SUM) تشير إلى نطاق من الخلايا في ورقة عمل أو مصنف آخر.

ابدأ الوظيفة كالمعتاد ، ثم انقر فوق الورقة ونطاق الخلية – تمامًا كما فعلت في الأمثلة السابقة.

في المثال التالي ، تجمع دالة SUM القيم من النطاق B2: B6 في ورقة عمل تسمى المبيعات.

= SUM (Sales! B2: B6)

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 3

كيفية استخدام الأسماء المحددة للإحالات المرجعية البسيطة

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

والأفضل من ذلك ، أن هذا الاسم فريد لجميع أوراق العمل في ملف Excel هذا.

على سبيل المثال ، يمكننا تسمية خلية “ChicagoTotal” ثم يصبح الإسناد الترافقي كالتالي:

= ChicagoTotal

هذا بديل أكثر وضوحا لمرجع افتراضي مثل هذا:

= المبيعات!

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

انقر فوق مربع الاسم في الزاوية العلوية اليسرى ، واكتب الاسم الذي تريد تعيينه ، ثم اضغط على Enter.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 4

عند إنشاء أسماء محددة ، لا يمكنك استخدام مسافات. لذلك ، في هذا المثال ، تم ربط الكلمات في الاسم وفصلها بحرف كبير. يمكنك أيضًا فصل الكلمات بأحرف مثل الواصلة (-) أو الشرطة السفلية (_).

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

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 5

يمكنك بعد ذلك استخدام الأزرار الموجودة في الجزء العلوي لتحرير وحذف هذه الأسماء المعرفة.

كيفية تنسيق البيانات كجدول

عندما تعمل مع قائمة واسعة من البيانات ذات الصلة ، يمكن لميزة تنسيق Excel كجدول تبسيط كيفية الرجوع إلى البيانات الموجودة فيه.

خذ الجدول البسيط التالي.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 6

يمكن تنسيق هذا كجدول.

انقر فوق خلية في القائمة ، وقم بالتبديل إلى علامة التبويب “الصفحة الرئيسية” ، وانقر فوق الزر “تنسيق كجدول” ، ثم اختر نمطًا.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 7

تأكد من صحة نطاق الخلايا وأن جدولك يحتوي على رؤوس.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 8

يمكنك بعد ذلك تعيين اسم ذي معنى لجدولك من علامة التبويب “التصميم”.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 9

بعد ذلك ، إذا احتجنا إلى جمع مبيعات شيكاغو ، فيمكننا الرجوع إلى الجدول باسمه (من أي ورقة) ، متبوعًا بقوس مربع ([) to see a list of the table’s columns.

كيفية عبور الخلايا المرجعية بين جداول بيانات Microsoft Excel 10

Select the column by double-clicking it in the list and enter a closing square bracket. The resulting formula would look something like this:

=SUM(Sales[Chicago])

يمكنك معرفة كيف يمكن للجداول أن تجعل البيانات المرجعية لوظائف التجميع مثل SUM و AVERAGE أسهل من المراجع الطرفية القياسية.

هذا الجدول صغير لأغراض العرض التوضيحي. كلما زاد حجم الجدول وزاد عدد الأوراق الموجودة في المصنف ، زادت الفوائد التي ستلاحظها.

كيفية استخدام دالة VLOOKUP للمراجع الديناميكية

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

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