تحليل الدالة غير المباشرة بالأمثلة

للوهلة الأولى (خاصة عند قراءة التعليمات) ، الوظيفة غير مباشر (غير مباشر) تبدو بسيطة وحتى غير ضرورية. جوهرها هو تحويل النص الذي يشبه الارتباط إلى ارتباط كامل. أولئك. إذا احتجنا إلى الإشارة إلى الخلية A1 ، فيمكننا عادةً إنشاء ارتباط مباشر (أدخل علامة المساواة في D1 ، وانقر فوق A1 واضغط على Enter) ، أو يمكننا استخدام غير مباشر لنفس الغرض:

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

تقولون: "حسنًا ، حسنًا". "وما الفائدة؟" 

لكن لا تحكم من خلال الانطباع الأول - إنه خادع. يمكن أن تساعدك هذه الميزة في الكثير من المواقف.

مثال 1. تبديل

كلاسيكي من هذا النوع: تحتاج إلى قلب القطر الرأسي

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

المنطق بسيط: للحصول على عنوان الخلية التالية ، نلصق الحرف "A" بالحرف الخاص "&" ورقم عمود الخلية الحالية ، التي تعطينا الوظيفة عمود (عمودي).

من الأفضل القيام بالإجراء العكسي بشكل مختلف قليلاً. نظرًا لأننا في هذا الوقت نحتاج إلى تكوين رابط للخلايا B2 و C2 و D2 وما إلى ذلك ، فمن الأنسب استخدام وضع الارتباط R1C1 بدلاً من "معركة البحر" الكلاسيكية. في هذا الوضع ، ستختلف خلايانا فقط في رقم العمود: B2 = R1C2، C2 = R1C3، D2 = R1C4 وما إلى ذلك.

هذا هو المكان الذي تأتي فيه وسيطة الوظيفة الاختيارية الثانية. غير مباشر. إذا كانت متساوية يكذب أو ملقاه (خاطئة)، ثم يمكنك تعيين عنوان الارتباط في وضع R1C1. لذلك يمكننا بسهولة إعادة النطاق الأفقي إلى الوضع الرأسي:

مثال 2. الجمع حسب الفترة

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

مثال 3. القائمة المنسدلة للجدول الذكي

في بعض الأحيان ، لا يتعامل Microsoft Excel مع أسماء الجداول الذكية والأعمدة كروابط كاملة. لذلك ، على سبيل المثال ، عند محاولة إنشاء قائمة منسدلة (tab البيانات - التحقق من صحة البيانات) على أساس العمود الموظفون من الجدول الذكي مجتمع سوف نحصل على خطأ:

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

مثال 4. روابط غير قابلة للكسر

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

إذا وضعت روابط عادية (أدخل = B2 في الخلية الخضراء الأولى وقم بنسخها لأسفل) ، فعند حذف ، على سبيل المثال ، Dasha ، سنحصل على #LINK! خطأ في الخلية الخضراء المقابلة لها. (# REF!). في حالة استخدام الوظيفة لإنشاء الروابط غير مباشر لن تكون هناك مشكلة من هذا القبيل.

مثال 5: جمع البيانات من أوراق متعددة

لنفترض أن لدينا 5 أوراق مع تقارير من نفس النوع من موظفين مختلفين (ميخائيل وإيلينا وإيفان وسيرجي وديمتري):

لنفترض أن شكل البضائع وحجمها وموضعها وتسلسلها والأشهر في جميع الجداول هي نفسها - تختلف الأرقام فقط.

يمكنك جمع البيانات من جميع الأوراق (لا تلخصها ، ولكن ضعها تحت بعضها البعض في "كومة") باستخدام صيغة واحدة فقط:

كما ترى ، الفكرة هي نفسها: نحن نلصق الرابط بالخلية المرغوبة للورقة المعينة ، و غير مباشر يحولها إلى "العيش". للراحة ، فوق الجدول ، أضفت أحرف الأعمدة (B ، C ، D) ، وعلى اليمين - أرقام الأسطر التي يجب أخذها من كل ورقة.

المزالق

إذا كنت تستخدم غير مباشر (غير مباشر) عليك أن تتذكر نقاط ضعفها:

• إذا قمت بالارتباط بملف آخر (عن طريق لصق اسم الملف بين قوسين مربعين واسم الورقة وعنوان الخلية) ، فإنه يعمل فقط أثناء فتح الملف الأصلي. إذا أغلقناه ، فسنحصل على الخطأ #LINK!
• لا يمكن أن يشير INDIRECT إلى نطاق ديناميكي مسمى. على ثابت - لا مشكلة.
• غير المباشرة هي وظيفة متطايرة أو "متقلبة" ، أي يعاد حسابها لأي تغيير في أي خلية من خلايا الصفيحة ، وليس فقط التأثير على الخلايا ، كما هو الحال في الوظائف العادية. هذا له تأثير سيء على الأداء ومن الأفضل عدم الانغماس في الجداول غير المباشرة الكبيرة.

• كيفية إنشاء نطاق ديناميكي باستخدام التحجيم التلقائي
• تلخيص عبر نافذة نطاق على ورقة مع وظيفة OFFSET