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

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

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

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

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

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

=INDIRECT("'"&اسم_الورقة&"'!الخلية المستهدفة")

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

=INDIRECT("'"&B3&"'!C3")

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

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

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

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

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

هذا القسم يتحدث عن الإشارة الديناميكية إلى خلايا في مصنف آخر في Excel.

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

=INDIRECT("'[" & اسم_المصنف & "]" & اسم_الورقة & "'!" & عنوان_الخلية)

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

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

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

=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,"")

الوظائف ذات الصلة