مرجع ديناميكي لورقة عمل أو مصنف في Excel

إذا كانت لديك بيانات مُنظَّمة بنفس التنسيق عبر أوراق عمل أو مصنفات متعددة، وتحتاج إلى جلبها ديناميكيًّا إلى ورقة أخرى، فإن دالة INDIRECT ستُسهِّل عليك إنجاز هذه المهمة بسرعة وسلاسة.

الإشارة إلى خلايا في ورقة عمل أخرى بشكل ديناميكي
الإشارة إلى خلايا في مصنف آخر بشكل ديناميكي

الإشارة إلى خلايا في ورقة عمل أخرى بشكل ديناميكي

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

الصيغة العامة

=INDIRECT(")'«&sheet_name&»'!Cell to return data from")

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

=INDIRECT(«'»&B3&«'!C3»)

ملاحظات: في الكود:

• B3هي الخلية التي تحتوي على اسم الورقة التي ستسحب البيانات منها؛
• C3هو عنوان الخلية في ورقة العمل المحددة التي ستسحب بياناتها؛
• لتجنب إرجاع قيمة خطأ إذا كانت إما B5 (خلية اسم الورقة) أو C3 (الخلية التي ستسحب منها البيانات) فارغة، يُرجى تضمين صيغة INDIRECT داخل دالة IF كما يلي:
  =IF(OR(B3="",C3=""),"",INDIRECT($B$3&"!C3"))
• إذا لم تكن أسماء أوراقك تحتوي على مسافات، يمكنك استخدام هذه الصيغة مباشرةً
  =INDIRECT(B3&«!C3»)

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

3. استمر في سحب جميع مبيعات الأرباع الأخرى حسب حاجتك. ولا تنسَ تغيير مرجع الخلية في الصيغة.

الإشارة إلى خلايا في مصنف آخر بشكل ديناميكي

يتناول هذا القسم طريقة الإشارة ديناميكيًا إلى خلايا موجودة في مصنف آخر ضمن Excel.

الصيغة العامة

=INDIRECT(")'[« & Book name & »]« & Sheet name & »'!" & Cell address)

كما هو موضح في لقطة الشاشة أدناه، توجد البيانات التي تريد إرجاعها في العمود E من ورقة العمل«Total sales» في مصنف منفصل باسم«SalesFile». يُرجى اتباع الخطوات التالية لإتمام ذلك.

1. أولًا، قم بتعبئة معلومات المصنف (بما في ذلك اسم المصنف واسم ورقة العمل وخلايا المرجع) التي سيتم سحب البيانات بناءً عليها إلى المصنف الحالي.

2. حدد خلية فارغة، ثم انسخ الصيغة التالية إليها واضغط على مفتاحEnter.

=INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3)

ملاحظات:

• B3تحتوي على اسم المصنف الذي تريد استخراج البيانات منه؛
• C3هو اسم الورقة؛
• D3هي الخلية التي ستسحب البيانات منها؛
• ستُرجع قيمة الخطأ#REF!إذا كان المصنف المرجعي مغلقًا؛
• لتجنب ظهور قيمة الخطأ #REF!، يُرجى تضمين صيغة INDIRECT داخل دالة IFERROR كما يلي:
  =IFERROR(INDIRECT(«'[»&$B$3&«]»&$C$3&«'!»&D3),«»)

3. بعد ذلك، اسحب مقبض التعبئة لأسفل لتطبيق الصيغة على الخلايا الأخرى.

تلميح:إذا كنت لا تريد أن يتحول قيمة العودة إلى خطأ بعد إغلاق المصنف المرجعي، يمكنك تحديد اسم المصنف واسم ورقة العمل وعنوان الخلية مباشرةً في الصيغة كما يلي:
=INDIRECT(‹[SalesFile.xlxs]Total sales›!E3,«»)

دالة ذات صلة