ربما تكون معتادًا على استخدام مراجع الخلايا في Excel، ولكن هل تعلم أنه يمكنك المضي قدمًا وإنشاء مراجع الخلايا الخاصة بك؟ هذا هو المكان الذي تكون فيه الدالة INDIRECT مفيدة.
ماذا تفعل الدالة غير المباشرة INDIRECTشرح ؟
تعمل الدالة غير المباشرة في Excel على تحويل سلسلة نصية إلى مرجع. هذه الوظيفة لها العديد من الفوائد العملية:
- يسمح لك بإنشاء مراجع ديناميكية. على سبيل المثال، يمكنك تغيير مرجع إلى خلية داخل صيغة دون تغيير الصيغة نفسها.
- تعد الدالة INDIRECT طريقة رائعة لإنشاء روابط بين أوراق العمل داخل المصنف.
- يمكنك استخدام المعلومات الموجودة لديك بالفعل في جدول البيانات الخاص بك لإنشاء مرجع.
- سيظل المرجع الذي تم إنشاؤه باستخدام الدالة INDIRECT كما هو، حتى إذا تغيرت بنية الورقة.
بناء الجملة غير المباشر
بشكل افتراضي، تستخدم المراجع في Excel نمط المرجع A1، مما يعني أنه يشير إلى العمود أولاً، ثم إلى رقم الصف. ومع ذلك، تمكنك الدالة INDIRECT من تبديل هذه العناصر.
يُعرف هذا الترتيب المعكوس بالنمط المرجعي R1C1، حيث يشير “R” إلى صف، ويشير “C” إلى عمود. لهذا السبب، تحتوي الدالة INDIRECT على وسيطتين. هنا هو بناء الجملة:
=INDIRECT(x,y)
أين س هو النص المعي، و ذ (اختياري) هو النمط المرجعي.
وبشكل أكثر تحديدا، س (النص المرجعي) يمكن أن يكون مرجعًا بنمط A1، أو مرجعًا بنمط R1C1، أو اسمًا محددًا مسبقًا، أو مرجع خلية. لو س يشير إلى مصنف آخر، فيجب أن يكون المصنف الثاني مفتوحًا. تجدر الإشارة أيضًا إلى أنه لا يمكنك الرجوع إلى مصنف آخر إذا كنت تستخدم Excel للويب.
ذ (النمط المرجعي) هو وسيطة اختيارية. إذا اخترت عدم تضمين هذه الوسيطة، فسيستخدم Excel تلقائيًا مرجع نمط A1 الافتراضي، على الرغم من أنه يمكنك الكتابة أيضًا حقيقي هنا لإجبار Excel على استخدام هذا النمط المرجعي. الكتابة خطأ شنيع سيخبر Excel باستخدام النمط المرجعي R1C1.
في هذه المقالة، سأستخدم مراجع نمط A1 لتوضيح كيفية عمل الدالة غير المباشرة. بمجرد استيعابك لمفاهيمه، يمكنك تجربة استخدام مراجع نمط R1C1.
تعتبر نتيجة الدالة INDIRECT مرجعًا.
الأشياء التي يجب وضعها في الاعتبار قبل استخدام الدالة غير المباشرة
هناك بعض الأشياء التي يجب أن تعرفها قبل البدء في العمل باستخدام INDIRECT في جدول البيانات الخاص بك:
- هذا هو وظيفة متقلبة، مما يعني أنه يحاول التحديث باستمرار. قد يؤدي استخدام هذه الوظيفة في جدول بيانات كبير إلى بطئها أو حتى اختلالها.
- للاستفادة الكاملة من الدالة INDIRECT، ستستفيد أولاً من معرفة كيفية إنشاء مراجع مسماة في Excel.
- يعد عامل علامة الضم (&) طريقة رائعة لإنشاء سلسلة نصية يمكن استخدامها كمرجع.
كيفية استخدام الدالة غير المباشرة
حتى تتمكن من التعرف على كيفية استخدام INDIRECT في الممارسة العملية، دعونا نرى كيف يعمل في أبسط أشكاله.
أمثلة أساسية
في هذا المثال، الكتابة
=INDIRECT(A1)
في الخلية B2 يحول الخلية A1 إلى مرجع للخلية D1. ولهذا السبب كانت النتيجة 5
في هذا المثال، الكتابة
=SUM(INDIRECT(A2))
في الخلية B2، يقوم أولاً بتحويل A2 إلى مرجع خلية، ثم يجمع الخلايا الموجودة في ذلك المرجع. وذلك لأن الدالة INDIRECT مضمنة في الدالة SUM.
الآن، دعونا نلقي نظرة على استخدام الدالة INDIRECT للإشارة إلى خلية في ورقة أخرى. تحتوي الخلية A1 في الورقة2 على الرقم 100، ونريد إنشاء مرجع غير مباشر لتلك الخلية في الورقة الحالية. أولا، نحن بحاجة إلى الكتابة الورقة2!أ1 في الخلية A3، ثم نحتاج إلى تحويل هذا إلى مرجع باستخدام الدالة INDIRECT عن طريق الكتابة
=INDIRECT(A3)
في الخلية B3.
ومع ذلك، يمكننا أن نجعل ذلك أسهل بكثير باستخدام مرجع مسمى. على سبيل المثال، إذا أردنا إعادة تسمية الخلية A1 من الورقة 2 المجموع، يمكننا استخدام هذا ضمن مرجعنا غير المباشر. يعد هذا مفيدًا بشكل خاص إذا كنت تقوم بإنشاء مرجع إلى ورقة أخرى، حيث يساعدك على تجنب الاضطرار إلى تذكر علامة التعجب في بناء الجملة. وأيضًا، إذا تغير اسم الورقة، فلن يتكيف المرجع غير المباشر مع هذا التغيير، لذا فإن استخدام مرجع مسمى يعد بديلاً أكثر أمانًا ودائمًا.
مثال 1
الآن بعد أن فهمنا كيفية عمل INDIRECT، دعونا نستكشف كيف يمكننا استخدامه لجعل أوراق العمل الخاصة بنا مميزة في العالم الحقيقي.
كما هو الحال غالبًا في برنامج Excel، هناك أكثر من طريقة لتحقيق نفس النتيجة. على سبيل المثال، يمكنك تضمين الدالة TAKE لجعل العملية التالية أكثر ديناميكية. ومع ذلك، هدفنا هنا هو توضيح استخدام الدالة INDIRECT، بحيث يمكنك بعد ذلك المضي قدمًا واستخدامها بطريقتك الخاصة.
نحن هنا نتتبع عدد الأهداف التي يسجلها فريق كرة القدم في كل مباراة، ونريد حساب المتوسط للمباريات الثلاث السابقة.
أولاً، سوف نستخدم الدالة COUNT لمعرفة عدد المباريات التي لعبها الفريق حتى الآن. الكتابة
=COUNT(B:B)
في الخلية E1، سيتم حساب العدد الإجمالي للخلايا في العمود B الذي يحتوي على أرقام، وبالتالي يخبرنا بعدد الألعاب التي تم لعبها.
نحن الآن جاهزون لاستخدام الدالة 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، وسيتم تحديث المراجع غير المباشرة وفقًا لذلك لالتقاط القيم الثلاث الأخيرة دائمًا في المتوسط صيغة. كل هذا يحدث دون الحاجة إلى تغيير الصيغة التي كتبناها في الخلية E2.
مثال 2
دعونا نلقي نظرة على مثال آخر. هذه المرة، سوف نستخدم مراجع مسماة حتى نتمكن من استخدام INDIRECT مع وظيفة VLOOKUP الخاصة ببرنامج Excel.
نريد من برنامج Excel أن يخبرنا بمقدار الأموال التي كسبها كل فرد من الأفراد الأربعة في الأسبوع الأول أو الأسبوع الثاني، مع خيار تغيير رقم الأسبوع هذا إذا لزم الأمر.
للبدء، نحتاج إلى تسمية الخلايا من B1 إلى C5 باسم “week_1″، والخلايا من B7 إلى B11 باسم “week_2”. ثم، في الخلية G2، سوف نكتب
=VLOOKUP(E2,INDIRECT("week_"&F2),2,0)لأننا نريد أن يبحث Excel عن قيمة Tom في النطاق الذي أطلقنا عليه اسم “week_1″، ويعيد القيمة المقابلة في العمود الثاني من هذا النطاق كمطابقة تامة.
يمكننا بعد ذلك استخدام مقبض التعبئة لإكمال البيانات للأفراد الثلاثة الآخرين، مع العلم أن المراجع التي أنشأناها مرتبطة بالصفوف الخاصة بها وآمنة أيضًا بسبب استخدامنا للمراجع المسماة.
بعد ذلك، سيتم تطبيق أي تغييرات على القيم الموجودة في العمودين C أو F تلقائيًا على الصيغة التي كتبناها للتو.
لإكمال عملية جعل جدول البيانات الخاص بك ديناميكيًا ومرتبًا بالكامل، يمكنك إضافة قوائم منسدلة باستخدام أداة التحقق من صحة البيانات في Excel. في المثال أعلاه، يمكنك الوصول إلى أسماء الأفراد من خلال القائمة المنسدلة، بحيث يشغل جدول البحث صفًا واحدًا فقط.