طرق فعّالة لاستخدام دالة INDIRECT في Excel

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

قد تكون معتادًا على استخدام مراجع الخلايا في Excel، ولكن هل تعلم أنه يمكنك الذهاب إلى خطوة أبعد وإنشاء مراجع خلايا خاصة بك؟ هنا تأتي فائدة الدالة INDIRECT.

ما الذي تفعله الدالة INDIRECT؟

تحول الدالة INDIRECT في Excel سلسلة نصية إلى مرجع. تتمتع هذه الدالة بالعديد من الفوائد العملية:

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

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

بنية الدالة INDIRECT

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

يُعرف هذا الترتيب المعكوس باسم نمط المرجع R1C1، حيث يشير الحرف “R” إلى صف، ويشير الحرف “C” إلى عمود. ولهذا السبب، تحتوي الدالة INDIRECT على وسيطتين. وفيما يلي بناء الجملة:

=INDIRECT(x,y)

حيث x هو نص المرجع، وy (اختياري) هو نمط المرجع.

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

y (نمط المرجع) هو وسيطة اختيارية. إذا اخترت عدم تضمين هذه الوسيطة، فسيستخدم Excel تلقائيًا مرجعًا بنمط A1 الافتراضي، على الرغم من أنه يمكنك أيضًا كتابة TRUE هنا لإجبار Excel على استخدام نمط المرجع هذا. سيؤدي كتابة FALSE إلى إخبار Excel باستخدام نمط المرجع R1C1.

ملاحظة

في هذه المقالة، سأستخدم مراجع بنمط A1 لإظهار كيفية عمل الدالة INDIRECT. بمجرد فهمك لمفاهيمها، يمكنك التجربة باستخدام مراجع بنمط R1C1.

تكون نتيجة الدالة INDIRECT هي مرجع.

الأشياء التي يجب وضعها في الاعتبار قبل استخدام الدالة INDIRECT

هناك بعض الأشياء التي يجب أن تعرفها قبل أن تبدأ العمل مع الدالة INDIRECT في جدول البيانات الخاص بك:

• هذه دالة متقلبة، مما يعني أنها تحاول التحديث باستمرار. قد يؤدي استخدام هذه الدالة في جدول بيانات كبير إلى بطئها أو حتى تعطلها.
• للاستفادة الكاملة من الدالة INDIRECT، ستستفيد أولاً من معرفة كيفية إنشاء مراجع مسماة في Excel.
• يعد عامل علامة الجمع (&) طريقة رائعة لإنشاء سلسلة نصية يمكن استخدامها كمرجع.

كيفية استخدام الدالة INDIRECT

حتى تتمكن من فهم كيفية استخدام الدالة INDIRECT في الممارسة العملية، دعنا نرى كيف تعمل في أبسط أشكالها.

أمثلة أساسية

في هذا المثال، الكتابة

=INDIRECT(A1)

في الخلية B2، تتحول الخلية A1 إلى مرجع للخلية D1. ولهذا السبب تكون النتيجة 5.

في هذا المثال، الكتابة

=SUM(INDIRECT(A2))

في الخلية B2، يتم تحويل الخلية A2 أولاً إلى مرجع خلية، ثم يتم جمع الخلايا في هذا المرجع. وذلك لأن الدالة INDIRECT مضمنة داخل الدالة SUM.

الآن، دعنا نلقي نظرة على استخدام الدالة INDIRECT للإشارة إلى خلية في ورقة أخرى. تحتوي الخلية A1 من الورقة 2 على الرقم 100، ونريد إنشاء مرجع INDIRECT لتلك الخلية في الورقة الحالية. أولاً، نحتاج إلى كتابة Sheet2!A1 في الخلية A3، ثم نحتاج إلى تحويل هذا إلى مرجع باستخدام الدالة INDIRECT عن طريق كتابة

=INDIRECT(A3)

في الخلية B3.

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

مثال 1 من العالم الحقيقي

الآن بعد أن فهمنا كيفية عمل الدالة INDIRECT، فلنستكشف كيف يمكننا استخدامها لجعل أوراق العمل الخاصة بنا تعمل بشكل صحيح في العالم الحقيقي.

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

هنا، نتتبع عدد الأهداف التي يسجلها فريق كرة قدم في كل مباراة، ونريد حساب المتوسط ​​للمباريات الثلاث السابقة.

أولاً، سنستخدم الدالة COUNT لمعرفة عدد المباريات التي لعبها الفريق حتى الآن.

=COUNT(B:B)

في الخلية E1 سيتم حساب العدد الإجمالي للخلايا في العمود B التي تحتوي على أرقام، وبالتالي إخبارنا بعدد الألعاب التي تم لعبها.

نصيحة
إذا كنت تريد حساب النص بالإضافة إلى الأرقام، فاستخدم الدالة COUNTA بدلاً من ذلك.

الآن، نحن مستعدون لاستخدام الدالة INDIRECT لتحويل البيانات في الخلية E1 إلى مرجع. في هذه المرحلة، هدفنا هو إيجاد متوسط ​​القيم في الخلايا B6 إلى B8. وإليك كيف ستساعدنا الدالة INDIRECT في القيام بذلك. ابدأ بكتابة

=AVERAGE(INDIRECT

في الخلية E2، لأننا نخبر Excel بأننا نريد في النهاية إيجاد متوسط، ونستخدم أيضًا الدالة INDIRECT لإخبار Excel بالمكان الذي يجب أن يبحث فيه لحساب هذا المتوسط.

الخلية الأولى التي سنشير إليها في حساب المتوسط ​​هي B8، لذا سنضيف

=AVERAGE(INDIRECT("B"&E1)

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

الخلية التالية التي سنشير إليها في حساب المتوسط ​​هي B7، وهي خلية واحدة أعلى من B8. لذا، فإن إضافة

=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1)

بالنسبة لصيغتنا، يعني هذا أن Excel سوف ينشئ مرجع خلية يبدأ في العمود B، ثم يطرح 1 من القيمة الموجودة في E1.

وأخيرًا، نريد الإشارة إلى الخلية B6، التي تقع على بعد خليتين من B8 في حساب المتوسط، ثم نضيف قوس الإغلاق:

=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1),INDIRECT("B"&E1-2))

يقوم هذا بحساب متوسط ​​3 و1 و1 بشكل صحيح، وهو إجمالي عدد الأهداف المسجلة في آخر ثلاث مباريات.

الآن، عندما نضيف المزيد من البيانات إلى العمود B، ستزداد القيمة في الخلية E1، حيث تتعرف دالة COUNT على وجود المزيد من القيم في العمود B، وستقوم المراجع غير المباشرة بالتحديث وفقًا لذلك لالتقاط آخر ثلاث قيم في صيغة AVERAGE دائمًا. يحدث كل هذا دون الحاجة إلى تغيير الصيغة التي كتبناها في الخلية E2.

مثال 2 من العالم الحقيقي

لنلق نظرة على مثال آخر. هذه المرة، سنستخدم مراجع مسماة حتى نتمكن من استخدام INDIRECT مع دالة VLOOKUP في Excel.

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

للبدء، نحتاج إلى تسمية الخلايا من B1 إلى C5 باسم “week_1″، والخلايا من B7 إلى B11 باسم “week_2”. ثم في الخلية G2، سنكتب

=VLOOKUP(E2,INDIRECT("week_"&F2),2,0)

لأننا نريد أن يبحث Excel عن قيمة توم في النطاق الذي أطلقنا عليه اسم “week_1″، ويعيد القيمة المقابلة في العمود الثاني من هذا النطاق كمطابقة دقيقة.

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

بعد ذلك، سيتم تطبيق أي تغييرات على القيم في العمودين C أو F تلقائيًا على الصيغة التي كتبناها للتو.

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

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