Goal Seek هي إحدى أدوات تحليل What-if لبرنامج Excel والتي تساعدك في العثور على قيمة الإدخال الصحيحة للصيغة للحصول على المخرجات المطلوبة. يُظهر كيف تؤثر قيمة واحدة في صيغة على أخرى. بمعنى آخر ، إذا كانت لديك قيمة مستهدفة تريد تحقيقها ، فيمكنك استخدام أداة البحث عن الهدف للعثور على قيمة الإدخال الصحيحة للحصول عليها.
على سبيل المثال ، لنفترض أنك حصلت على إجمالي 75 درجة في مادة ما وتحتاج إلى 90 درجة على الأقل للحصول على درجة S في هذا الموضوع. لحسن الحظ ، لديك اختبار أخير قد يساعدك في رفع متوسط درجاتك. يمكنك استخدام Goal Seek لمعرفة مقدار الدرجة التي تحتاجها في هذا الاختبار النهائي للحصول على درجة S.
يستخدم Goal Seek طريقة التجربة والخطأ لتتبع المشكلة من خلال إدخال التخمينات حتى تصل إلى النتيجة الصحيحة. يمكن لـ Goal Seek العثور على أفضل قيمة إدخال للصيغة في غضون ثوانٍ والتي كان من الممكن أن تستغرق وقتًا طويلاً لاكتشافها يدويًا. في هذه المقالة ، سنوضح لك كيفية استخدام أداة Goal Seek في Excel مع بعض الأمثلة.
مكونات وظيفة البحث عن الهدف
تتكون وظيفة Goal Seek من ثلاث معلمات ، بمعنى:
- تعيين الخلية - هذه هي الخلية التي يتم فيها إدخال الصيغة. تحدد الخلية التي تريد الإخراج المطلوب فيها.
- إلى قيمة - هذه هي القيمة المستهدفة / المرغوبة التي تريدها كنتيجة لعملية البحث عن الهدف.
- عن طريق تغيير الخلية - يحدد هذا الخلية التي يجب تعديل قيمتها للحصول على الإخراج المطلوب.
كيفية استخدام Goal Seek في Excel: مثال 1
لتوضيح كيفية عملها في مثال بسيط ، تخيل أنك تدير كشك فواكه. في لقطة الشاشة أدناه ، تعرض الخلية B11 (أدناه) تكلفة شراء الفاكهة لكشكك وتعرض الخلية B12 إجمالي إيراداتك من بيع تلك الفاكهة. وتوضح الخلية B13 النسبة المئوية لأرباحك (20٪).
إذا كنت ترغب في زيادة أرباحك إلى "30٪" ، لكنك غير قادر على زيادة استثمارك ، لذلك يجب عليك زيادة إيراداتك لتحقيق الربح. لكن إلى أي مدى؟ سيساعدك البحث عن الهدف في العثور عليه.
يمكنك استخدام الصيغة التالية في الخلية B13 لحساب نسبة الربح:
= (B12-B11) / B12الآن ، تريد حساب هامش الربح بحيث تكون نسبة ربحك 30٪. يمكنك القيام بذلك باستخدام Goal Seek في Excel.
أول شيء يجب فعله هو تحديد الخلية التي تريد تعديل قيمتها. في كل مرة تستخدم فيها Goal Seek ، يجب عليك تحديد خلية تحتوي على صيغة أو دالة. في حالتنا ، سنحدد الخلية B13 لأنها تحتوي على الصيغة لحساب النسبة المئوية.
ثم انتقل إلى علامة التبويب "البيانات" ، وانقر فوق الزر "ماذا لو التحليل" في مجموعة التوقعات ، وحدد "البحث عن الهدف".
سيظهر مربع الحوار Goal Seek مع 3 حقول:
- تعيين الخلية - أدخل مرجع الخلية الذي يحتوي على الصيغة (B13). هذه هي الخلية التي ستحصل على الإخراج المطلوب.
- إلى قيمة - أدخل النتيجة المرغوبة التي تحاول تحقيقها (30٪).
- عن طريق تغيير الخلية - أدخل مرجع الخلية لقيمة الإدخال التي تريد تغييرها (B12) للوصول إلى النتيجة المرجوة. ما عليك سوى النقر فوق الخلية أو إدخال مرجع الخلية يدويًا. عند تحديد الخلية ، سيضيف Excel علامة "$" قبل حرف العمود ورقم الصف لجعلها خلية مطلقة.
عند الانتهاء ، انقر فوق "موافق" لاختبارها.
ثم سيظهر مربع حوار "Goal Seek Status" ويخبرك إذا وجد أي حل كما هو موضح أدناه. إذا تم العثور على حل ، فسيتم تعديل قيمة الإدخال في "الخلية المتغيرة (B12)" إلى قيمة جديدة. هذا ما نريده. لذلك في هذا المثال ، حدد التحليل أنه لكي تصل إلى هدف الربح بنسبة 30٪ ، فإنك تحتاج إلى تحقيق عائد قدره 1635.5 دولارًا (B12).
انقر فوق "موافق" وسيقوم Excel بتغيير قيم الخلية أو النقر فوق "إلغاء" لتجاهل الحل واستعادة القيمة الأصلية.
قيمة الإدخال (1635.5) في الخلية B12 هي ما اكتشفناه باستخدام Goal Seek من أجل تحقيق هدفنا (30٪).
أشياء يجب تذكرها عند استخدام Goal Seek
- يجب أن تحتوي الخلية المحددة دائمًا على صيغة تعتمد على الخلية "عن طريق تغيير الخلية".
- يمكنك فقط استخدام Goal Seek على قيمة إدخال خلية واحدة في المرة الواحدة
- يجب أن تحتوي "عن طريق تغيير الخلية" على قيمة وليست صيغة.
- إذا لم يتمكن Goal Seek من العثور على الحل الصحيح ، فإنه يعرض أقرب قيمة يمكن أن ينتجها ويخبرك أن رسالة "Goal-calling ربما لم تجد حلاً".
ماذا تفعل عندما لا يعمل برنامج Excel Goal Seek
ومع ذلك ، إذا كنت متأكدًا من وجود حل ، فهناك بعض الأشياء التي يمكنك محاولة إصلاحها.
تحقق من معلمات وقيم Goal Seek
هناك شيئان يجب عليك التحقق منهما: أولاً ، تحقق مما إذا كانت معلمة "تعيين الخلية" تشير إلى خلية الصيغة. ثانيًا ، تأكد من أن خلية الصيغة (تعيين الخلية) تعتمد ، بشكل مباشر أو غير مباشر ، على الخلية المتغيرة.
ضبط إعدادات التكرار
في إعدادات Excel ، يمكنك تغيير عدد المحاولات الممكنة التي يمكن أن يقوم بها Excel للعثور على الحل الصحيح بالإضافة إلى دقته.
لتغيير إعدادات حساب التكرار ، انقر فوق "ملف" من قائمة علامات التبويب. ثم ، انقر على "خيارات" في الجزء السفلي.
في نافذة خيارات Excel ، انقر فوق "الصيغ" في الجزء الأيمن.
ضمن قسم "خيارات الحساب" ، قم بتغيير هذه الإعدادات:
- الحد الأقصى للتكرارات - يشير إلى عدد الحلول الممكنة التي سيحسبها التفوق ؛ كلما زاد الرقم زادت التكرارات التي يمكنه إجراؤها. على سبيل المثال ، إذا قمت بتعيين "الحد الأقصى للتكرار" على 150 ، يختبر Excel 150 حلاً ممكنًا.
- أقصى تغيير - يشير إلى دقة النتائج ؛ يعطي الرقم الأصغر دقة أعلى. على سبيل المثال ، إذا كانت قيمة خلية الإدخال تساوي "0" ولكن توقف Goal Seek عن الحساب عند "0.001" ، فإن تغيير هذا إلى "0.0001" يجب أن يحل المشكلة.
قم بزيادة قيمة "الحد الأقصى للتكرارات" إذا كنت تريد أن يقوم Excel باختبار المزيد من الحلول الممكنة وتقليل قيمة "الحد الأقصى للتغيير" إذا كنت تريد نتيجة أكثر دقة.
توضح لقطة الشاشة أدناه القيمة الافتراضية:
لا مراجع دائرية
عندما تشير صيغة ما إلى خليتها الخاصة ، يطلق عليها اسم مرجع دائري. إذا كنت تريد أن يعمل Excel Goal Seek بشكل صحيح ، فلا يجب أن تستخدم الصيغ مرجعًا معادًا.
مثال البحث عن هدف Excel 2
لنفترض أنك تقترض مبلغ "25000 دولار" من شخص ما. إنهم يقرضونك المال بمعدل فائدة 7٪ شهريًا لمدة 20 شهرًا ، مما يجعل السداد "1327 دولارًا" شهريًا. ولكن يمكنك فقط دفع "1000 دولار" شهريًا لمدة 20 شهرًا بفائدة 7٪. يمكن أن يساعدك Goal Seek في العثور على مبلغ القرض الذي ينتج عنه دفعة شهرية (EMI) تبلغ "1000 دولار".
أدخل متغيرات الإدخال الثلاثة التي ستحتاجها لحساب حساب PMT الخاص بك ، أي معدل الفائدة بنسبة 7 ٪ ، ومدة 20 شهرًا ، والمبلغ الأساسي 25000 دولار.
أدخل صيغة PMT التالية في الخلية B5 والتي ستمنحك مبلغ EMI يبلغ 1،327.97 دولارًا.
صيغة الدالة PMT:
= PMT (معدل الفائدة / 12 ، المدة ، المبلغ الرئيسي)الصيغة:
= PMT (B3 / 12، B4، -B2)
حدد الخلية B5 (خلية الصيغة) وانتقل إلى البيانات -> ماذا لو التحليل -> البحث عن الهدف.
استخدم هذه المعلمات في نافذة "Goal Seek":
- تعيين الخلية - B5 (الخلية التي تحتوي على الصيغة التي تحسب EMI)
- إلى قيمة - 1000 (نتيجة الصيغة / الهدف الذي تبحث عنه)
- عن طريق تغيير الخلية - B2 (هذا هو مبلغ القرض الذي تريد تغييره لتحقيق القيمة المستهدفة)
ثم اضغط على "موافق" للاحتفاظ بالحل الذي تم العثور عليه أو "إلغاء" لتجاهله.
يخبرك التحليل أن الحد الأقصى للمبلغ الذي يمكنك اقتراضه هو 18825 دولارًا إذا كنت تريد تجاوز ميزانيتك.
هذه هي الطريقة التي تستخدم بها Goal Seek في Excel.