تقرأ =INDIRECT(E2) الخلية التي عنوانها مكتوب كنص في E2. إذا كانت E2 تقول C4، تعيد الصيغة القيمة الموجودة في C4. ويمكن أيضًا بناء العنوان من أجزاء: تقرأ =INDIRECT("C"&E3) العمود C في رقم الصف الموجود في E3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Address | Value | |
| 2 | Apple | Fruit | $1.20 | C4 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | 6 | $1.10 | |
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
تقرأ F2 الخلية C4، سعر Carrot، $0.80. غيّر E2 إلى C3 أو B5 فتتبعها F2. وتجمع F3 الحرف "C" والرقم 6 الموجود في E3 في العنوان C6 وتعيد $1.10. غيّر E3 إلى 2 لسعر Apple.
صيغة دالة INDIRECT
=INDIRECT(ref_text, [a1])
ref_text: نص يكتب مرجعًا:"C4"،"B2:B6"،"Prices!A2"،"'Price list'!A2:B9".a1:TRUEأو محذوف لعناوين نمط A1. وFALSEيقرأ نمط R1C1، حيث تعني"R4C3"الصف 4 والعمود 3، وهذا يناسب صفًا وعمودًا كلاهما رقم.
إذا لم يكن النص عنوانًا صالحًا، تكون النتيجة #REF!. تعيد INDIRECT مرجعًا حقيقيًا، لذا تعمل داخل SUM وCOUNTIF وVLOOKUP وكل دالة تأخذ نطاقًا.
بناء نطاق من أرقام
يمكن أن يكون العنوان نطاقًا كاملًا. وإدخال رقم فيه يعطي نطاقًا يأتي حجمه من خلية.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Rows | Total | ||
| 2 | Jan | 4,200 | 3 | 12,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 |
مع 3 في E2 يصبح النص B2:B4، وتجمع F2 من Jan إلى Mar: 12,900. اضبط E2 على 6 لنصف السنة، 27,900. توجد 1+E2 لأن البيانات تبدأ في الصف 2. ويمكن كتابة المجموع نفسه دون INDIRECT، =SUM(B2:INDEX(B2:B7,E2))، وهي ليست متقلبة؛ تقارن صفحة OFFSET بين الخيارات.
الإشارة إلى ورقة مسماة في خلية
يمكن أن يأتي اسم الورقة من خلية أيضًا. وهذا يحوّل صيغة ملخص واحدة إلى بحث عبر الأوراق: يقرأ كل صف الورقة المسماة في العمود A. وتُبقي علامتا الاقتباس المفردتان حول الاسم الصيغة عاملة مع الأسماء التي فيها مسافات.
| A | B | |
|---|---|---|
| 1 | Month | Total |
| 2 | Jan | 12,500 |
| 3 | Feb | 12,200 |
| 4 | Mar | 13,700 |
تبني B2 النص 'Jan'!B2:B4 وتجمعه: 12,500. وB3 وB4 هما الصيغة نفسها منسوخة إلى الأسفل، لذا تقرآن Feb (12,200) وMar (13,700). افتح علامة التبويب Feb وغيّر رقمًا: يتبعه الملخص. اكتب Feb فوق Jan في A2 فتجمع B2 الآن Feb. والنطاق B2:B4 داخل علامات الاقتباس نص، لذا لا يتغير عند نسخ الصيغة إلى الأسفل؛ وحده المرجع A2 يتغير.
القوائم المنسدلة التابعة
القائمة المنسدلة الثانية التي تعتمد عناصرها على الأولى هي المهمة الكلاسيكية لـ INDIRECT. والإعداد المعتاد في الاكسل:
- ضع عناصر كل فئة في عمود وسمِّ كل نطاق باسم فئته: حدد الأعمدة مع عناوينها واستخدم الصيغ > إنشاء من التحديد > الصف العلوي. ينشئ ذلك الأسماء
FruitوVegetableوDairy. - أعطِ A2 قائمة بالفئات: البيانات > التحقق من صحة البيانات > السماح: قائمة، والمصدر
Fruit,Vegetable,Dairy. - أعطِ B2 قائمة مصدرها
=INDIRECT(A2). عندما تقول A2 إنها Fruit، تقرأ القائمة النطاق المسمى Fruit.
تبني الورقة أدناه الشيء نفسه بورقة لكل فئة بدلًا من نطاق مسمى. تستخدم D2 الدالة INDIRECT لمد عناصر الورقة المسماة في A2، وتقرأ قائمة B2 النطاق D2:D4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Category | Item | Items for the category | |
| 2 | Fruit | Apple | Apple | |
| 3 | Pear | |||
| 4 | Plum |
اختر Dairy في A2: يتحول D2:D4 إلى Milk وButter وCheese، وكذلك خيارات B2. تحتفظ B2 بقيمتها القديمة حتى تختار قيمة جديدة؛ ويتصرف الاكسل بالطريقة نفسها، ولهذا كثيرًا ما تضيف النماذج فحصًا مثل =COUNTIF(D2:D4,B2)>0 بجانب العنصر. في Excel 365 يمكنك الاستغناء عن النطاقات المسماة وتوجيه القائمة الثانية إلى صيغة ممتدة، مثل =INDIRECT("'"&A2&"'!A2:A4") في خلية مساعدة و=D2# كمصدر. وفي صفحة القائمة المنسدلة بقية الإعداد.
INDIRECT متقلبة، وتتجاهل الصفوف المدرجة
ينتج أثران جانبيان عن قراءة INDIRECT لنص بدلًا من مرجع:
- تُعاد حسابها عند كل تغيير. لا يستطيع الاكسل أن يعرف إلى أي خلايا سيشير نص ما، لذا يعيد حساب كل INDIRECT بعد أي تعديل في أي مكان من المصنف. بضع عشرات لا تضر؛ أما عشرات الآلاف فتجعل كل ضغطة مفتاح بطيئة. وتعطي INDEX مع رقم صف (
=INDEX(C:C,E3)) النتيجة نفسها التي تعطيها=INDIRECT("C"&E3)ولا يُعاد حسابها إلا عندما تتغير مدخلاتها. - العنوان لا يتحرك. أدرج صفًا فوق الصف 4 فتصبح
=C4هي=C5، لكن=INDIRECT("C4")تبقى تقرأ C4، التي صارت صفًا مختلفًا. أحيانًا يكون هذا هو المقصود، أي مرجع يجب أن يبقى على خلية ثابتة مهما حدث للورقة. وفي أغلب الأحيان يكون خللًا ينتظر من يُدرج صفًا.
لا تعمل INDIRECT إلى مصنف آخر إلا ما دام ذلك المصنف مفتوحًا؛ وعندما يكون مغلقًا، تعيد #REF!.
تمرين: سعر من رقم صف
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Row | Price | |
| 2 | Apple | Fruit | $1.20 | 5 | ||
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
دورك: في F2، استخدم INDIRECT لإرجاع السعر في العمود C في رقم الصف المكتوب في E2.
الأسئلة الشائعة
ماذا تفعل INDIRECT في الاكسل؟
تحوّل النص إلى مرجع. تعيد =INDIRECT("C4") قيمة C4، وتعيد =INDIRECT(E2) قيمة أي عنوان خلية مكتوب في E2. ويمكن بناء العنوان بالعلامة &، لذا تقرأ =INDIRECT("C"&E2) العمود C في رقم الصف الموجود في E2.
كيف أشير إلى ورقة أخرى اسمها موجود في خلية؟
ابنِ العنوان مع اسم الورقة بين علامتي اقتباس مفردتين: =INDIRECT("'"&A2&"'!B2"). تُبقي علامتا الاقتباس الصيغة عاملة مع الأسماء التي فيها مسافات. وتجمع =SUM(INDIRECT("'"&A2&"'!B2:B4")) نطاقًا في تلك الورقة.
لماذا تعيد INDIRECT الخطأ #REF!؟
لأن النص ليس عنوانًا صالحًا، أو يسمّي ورقة غير موجودة، أو يشير إلى مصنف آخر مغلق. تحقق من النص الذي تبنيه الصيغة بوضع التعبير نفسه في خلية وحده، دون INDIRECT.
هل INDIRECT دالة متقلبة؟
نعم. يعيد الاكسل حساب كل INDIRECT عند كل تغيير في أي مكان من المصنف، لأنه لا يستطيع أن يعرف مسبقًا إلى أي خلايا سيشير النص. بضع منها لا تضر؛ أما الآلاف فتبطئ المصنف. وكثيرًا ما تستطيع INDEX أداء المهمة نفسها دون أن تكون متقلبة.
