اكسل

قم بإنشاء مرجع ديناميكي لورقة العمل

Create Dynamic Reference Worksheet

تم تضمين ورقة عمل الممارسة مع تدريب بالفيديو عبر الإنترنت .

في هذا الفيديو ، سنلقي نظرة على كيفية إنشاء مرجع ديناميكي لورقة عمل في صيغة.



لنلقي نظرة.

كيفية جعل الصيغة في التفوق إذا

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





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

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



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

بعد ذلك ، سوف أنسخ هذه الصيغة مرتين (باستخدام الاختصار Control + الفاصلة العليا لمنع المرجع من التغيير) والعودة وتغيير أسماء الوظائف.

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

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

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

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

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

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

كيفية استخدام وظيفة pmt في Excel

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

الآن عندما أكتب اسمًا مختلفًا للورقة في C5 ، فإن الصيغ تسحب المعلومات من تلك الورقة.

أخيرًا ، لنجعل C5 قائمة منسدلة ، بحيث يسهل تحديد ورقة عمل.

حصلنا على خطأ #REF في الأسبوع 1 لأنه لا تزال هناك مسافة في اسم الورقة. عندما أخرج ذلك ، كل شيء يعمل.



^