المحتويات
تهانينا! لقد وصلت إلى اليوم الأخير من الماراثون 30 وظيفة Excel في 30 يومًا. لقد كانت رحلة طويلة ومثيرة للاهتمام تعلمت خلالها العديد من الأشياء المفيدة حول وظائف Excel.
في اليوم الثالث والعشرين من الماراثون ، سنخصص دراسة الوظيفة غير مباشر (غير مباشر) ، الذي يعرض الرابط المحدد بواسطة السلسلة النصية. باستخدام هذه الوظيفة ، يمكنك إنشاء قوائم منسدلة تابعة. على سبيل المثال ، عند اختيار بلد من القائمة المنسدلة ، يحدد الخيارات التي ستظهر في القائمة المنسدلة للمدينة.
لذا ، دعونا نلقي نظرة فاحصة على الجزء النظري من الدالة غير مباشر (غير مباشر) واستكشف أمثلة عملية لتطبيقه. إذا كانت لديك معلومات أو أمثلة إضافية ، فيرجى مشاركتها في التعليقات.
عمل 30: غير مباشر
المسمى الوظيفي غير مباشر (INDIRECT) ترجع الارتباط المحدد بواسطة السلسلة النصية.
كيف يمكنك استخدام الوظيفة غير المباشرة؟
منذ الوظيفة غير مباشر (غير مباشر):
- قم بإنشاء ارتباط أولي غير متحرك.
- قم بإنشاء مرجع إلى نطاق مسمى ثابت.
- قم بإنشاء ارتباط باستخدام معلومات الورقة والصف والعمود.
- قم بإنشاء مصفوفة غير متغيرة من الأرقام.
النحو غير المباشر (غير مباشر)
المسمى الوظيفي غير مباشر (غير مباشر) له الصيغة التالية:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) هو نص الرابط.
- a1 - إذا كانت تساوي TRUE (TRUE) أو لم يتم تحديدها ، فسيتم استخدام نمط الارتباط A1؛ وإذا كان FALSE (FALSE) ، ثم النمط R1C1.
الفخاخ غير المباشرة (غير مباشر)
- المسمى الوظيفي غير مباشر (غير مباشر) يتم إعادة حسابه عندما تتغير القيم الموجودة في ورقة عمل Excel. يمكن أن يؤدي هذا إلى إبطاء المصنف بشكل كبير إذا تم استخدام الوظيفة في العديد من الصيغ.
- إذا كانت الوظيفة غير مباشر (غير مباشر) ينشئ ارتباطًا بمصنف Excel آخر ، ويجب أن يكون هذا المصنف مفتوحًا وإلا ستبلغ الصيغة عن خطأ #REF! (#حلقة الوصل!).
- إذا كانت الوظيفة غير مباشر (غير مباشر) يشير إلى نطاق يتجاوز حد الصفوف والأعمدة ، وستقوم الصيغة بالإبلاغ عن خطأ #REF! (#حلقة الوصل!).
- المسمى الوظيفي غير مباشر (INDIRECT) لا يمكنه الإشارة إلى نطاق ديناميكي مسمى.
مثال 1: إنشاء ارتباط أولي غير متحرك
في المثال الأول ، يحتوي العمودان C و E على نفس الأرقام ، ويتم حساب مجموعهما باستخدام الوظيفة مجموع (SUM) هي نفسها أيضًا. ومع ذلك ، فإن الصيغ مختلفة قليلاً. الصيغة في الخلية C8 هي:
=SUM(C2:C7)
=СУММ(C2:C7)
في الخلية E8 ، الدالة غير مباشر (غير مباشر) ينشئ ارتباطًا بخلية البداية E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
إذا أدخلت صفًا في أعلى الورقة وأضفت القيمة لشهر يناير (يناير) ، فلن يتغير المبلغ الموجود في العمود ج. ستتغير الصيغة ، استجابةً لإضافة سطر:
=SUM(C3:C8)
=СУММ(C3:C8)
ومع ذلك ، فإن الوظيفة غير مباشر تعمل (غير مباشر) على إصلاح E2 باعتبارها خلية البداية ، لذلك يتم تضمين شهر يناير تلقائيًا في حساب إجماليات العمود E. تم تغيير خلية النهاية ، لكن خلية البداية لم تتأثر.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
مثال 2: ارتباط إلى نطاق مسمى ثابت
المسمى الوظيفي غير مباشر يمكن (غير مباشر) إنشاء مرجع إلى نطاق مسمى. في هذا المثال ، تشكل الخلايا الزرقاء النطاق NumList. بالإضافة إلى ذلك ، يتم أيضًا إنشاء نطاق ديناميكي من القيم الموجودة في العمود B NumListDyn، حسب عدد الأرقام في هذا العمود.
يمكن حساب مجموع كلا النطاقين ببساطة عن طريق إعطاء اسمه كوسيطة للدالة مجموع (SUM) ، كما ترى في الخليتين E3 و E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
بدلاً من كتابة اسم النطاق في دالة مجموع (SUM) ، يمكنك الرجوع إلى الاسم المكتوب في إحدى خلايا ورقة العمل. على سبيل المثال ، إذا كان الاسم NumList مكتوب في الخلية D7 ، ستكون الصيغة في الخلية E7 كما يلي:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
للأسف الوظيفة غير مباشر (غير مباشر) لا يمكنه إنشاء مرجع نطاق ديناميكي ، لذلك عند نسخ هذه الصيغة لأسفل في الخلية E8 ، ستحصل على خطأ #REF! (#حلقة الوصل!).
مثال 3: قم بإنشاء ارتباط باستخدام معلومات الورقة والصف والعمود
يمكنك بسهولة إنشاء ارتباط بناءً على أرقام الصفوف والأعمدة ، وكذلك استخدام القيمة FALSE (FALSE) لوسيطة الوظيفة الثانية غير مباشر (غير مباشر). هذه هي الطريقة التي يتم بها إنشاء ارتباط النمط R1C1. في هذا المثال ، أضفنا أيضًا اسم الورقة إلى الارتباط - "MyLinks"! R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
مثال 4: إنشاء مصفوفة أرقام غير متغيرة
تحتاج أحيانًا إلى استخدام صفيف من الأرقام في صيغ Excel. في المثال التالي ، نريد حساب متوسط أكبر 3 أرقام في العمود B. يمكن إدخال الأرقام في صيغة ، كما هو الحال في الخلية D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
إذا كنت بحاجة إلى صفيف أكبر ، فمن غير المحتمل أن ترغب في إدخال جميع الأرقام في الصيغة. الخيار الثاني هو استخدام الوظيفة صف (ROW) ، كما هو الحال في صيغة الصفيف التي تم إدخالها في الخلية D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
الخيار الثالث هو استخدام الوظيفة صف (رافق غير مباشر (غير مباشر) ، كما هو الحال مع صيغة الصفيف في الخلية D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
ستكون نتيجة كل الصيغ الثلاثة هي نفسها:
ومع ذلك ، إذا تم إدراج صفوف في الجزء العلوي من الورقة ، فإن الصيغة الثانية سترجع نتيجة غير صحيحة بسبب حقيقة أن المراجع في الصيغة ستتغير مع إزاحة الصف. الآن ، بدلاً من متوسط أكبر ثلاثة أرقام ، تُرجع الصيغة متوسط الأرقام الثالثة والرابعة والخامسة من حيث الحجم.
باستخدام الوظائف غير مباشر (غير مباشر) ، تحتفظ الصيغة الثالثة بمراجع الصف الصحيحة وتستمر في إظهار النتيجة الصحيحة.