إتقان Goal Seek في Excel – دليل كامل مع أمثلة

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

• المثال الأول: تعديل خطة سداد القرض
• المثال الثاني: تحديد الدرجة الدنيا للنجاح في الاختبار
• المثال الثالث: حساب الأصوات المطلوبة للفوز بالانتخابات
• المثال الرابع: تحقيق الربح المستهدف في الأعمال التجارية

ما هو Goal Seek في Excel؟

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

• تعديل متغير واحد:
  يمكن لـ Goal Seek فقط تغيير قيمة خلية إدخال واحدة في كل مرة.
• يتطلب وجود صيغة:
  عند تطبيق ميزة Goal Seek، يجب أن تحتوي الخلية المستهدفة على صيغة.
• الحفاظ على سلامة الصيغة:
  لا تقوم أداة Goal Seek بتغيير الصيغة في الخلية المستهدفة؛ بل تقوم بتغيير القيمة في خلية الإدخال التي تعتمد عليها الصيغة.

اكتشف سحر Excel مع Kutools AI

• التنفيذ الذكي: قم بعمليات الخلية، وتحليل البيانات، وإنشاء المخططات - كل ذلك يتم من خلال أوامر بسيطة.
• الصيغ المخصصة: قم بإنشاء صيغ مصممة خصيصًا لتسهيل سير عملك.
• برمجة VBA: كتابة وتطبيق أكواد VBA بسهولة.
• تفسير الصيغ: فهم الصيغ المعقدة بسهولة.
• ترجمة النصوص: تجاوز الحواجز اللغوية داخل جداول البيانات الخاصة بك.

عزز قدرات Excel لديك باستخدام أدوات تعمل بالذكاء الاصطناعي. قم بالتنزيل الآن وعش تجربة الكفاءة كما لم يحدث من قبل!

الوصول إلى أداة Goal Seek في Excel

للوصول إلى ميزة Goal Seek، انتقل إلى تبويب البيانات، انقر فوق تحليل ما-إذا > Goal Seek. شاهد لقطة الشاشة:

ثم سيظهر مربع حوار Goal Seek. إليكم شرح الخيارات الثلاثة في مربع حوار Goal Seek الخاص بـ Excel:

• تحديد الخلية: الخلية المستهدفة التي تحتوي على الصيغة التي تريد تحقيقها. يجب أن تحتوي هذه الخلية على صيغة، وسيقوم Goal Seek بضبط القيم في خلية أخرى ذات صلة لجعل قيمة هذه الخلية تصل إلى الهدف الذي حددته.
• القيمة المستهدفة: القيمة المستهدفة التي تريد أن تصل إليها "تحديد الخلية".
• عن طريق تغيير الخلية: الخلية المدخلة التي تريد من Goal Seek ضبطها. سيتم تغيير القيمة في هذه الخلية لضمان أن القيمة في "تحديد الخلية" تحقق الهدف "القيمة المستهدفة".
  
  

في القسم التالي، سنستكشف كيفية استخدام ميزة Goal Seek في Excel من خلال أمثلة مفصلة.

استخدام Goal Seek مع الأمثلة

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

المثال الأول: تعديل خطة سداد القرض

السيناريو: شخص يخطط للحصول على قرض بقيمة 20,000 دولار ويسعى لسداده خلال 5 سنوات بمعدل فائدة سنوي قدره 5%. القسط الشهري المطلوب هو 377.42 دولارًا. لاحقًا، يدرك أنه يستطيع زيادة القسط الشهري إلى 500 دولار. بالنظر إلى أن إجمالي مبلغ القرض ومعدل الفائدة السنوي يبقى كما هو، كم عدد السنوات التي سيستغرقها الآن لسداد القرض؟

كما هو موضح في الجدول التالي:

• تحتوي الخلية B1 على مبلغ القرض وهو 20,000 دولار.
• تحتوي الخلية B2 على مدة القرض وهي 5 سنوات.
• تحتوي الخلية B3 على معدل الفائدة السنوي وهو 5%.
• تحتوي الخلية B5 على القسط الشهري الذي يتم حسابه باستخدام الصيغة =PMT(B3/12,B2*12,B1).
  
  

هنا سأوضح لك كيفية استخدام ميزة Goal Seek لإكمال هذه المهمة.

الخطوة الأولى: تفعيل ميزة Goal Seek

انتقل إلى تبويب البيانات، انقر فوق تحليل ما-إذا > Goal Seek.

الخطوة الثانية: تكوين إعدادات Goal Seek

1. في مربع تحديد الخلية، حدد الخلية التي تحتوي على الصيغة B5.
2. في مربع القيمة المستهدفة، أدخل مبلغ القسط الشهري المجدول -500 (رقم سالب يمثل دفعة).
3. في مربع عن طريق تغيير الخلية، حدد الخلية B2 التي تريد تعديلها.
4. انقر موافق.
   
   

النتيجة

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

الآن، سيستغرقه حوالي 3.7 سنوات لسداد القرض.

المثال الثاني: تحديد الدرجة الدنيا للنجاح في الاختبار

السيناريو: طالب يحتاج إلى اجتياز 5 اختبارات، لكل منها نفس الوزن. للنجاح، يجب أن يحقق الطالب متوسط درجات بنسبة 70% عبر جميع الاختبارات. بعد الانتهاء من أول 4 اختبارات ومعرفة نتائجها، يحتاج الطالب الآن إلى حساب الدرجة الدنيا المطلوبة في الاختبار الخامس لضمان أن يصل المتوسط العام إلى 70% أو يتجاوزه.

كما هو موضح في لقطة الشاشة أدناه:

• تحتوي الخلية B1 على درجة الاختبار الأول.
• تحتوي الخلية B2 على درجة الاختبار الثاني.
• تحتوي الخلية B3 على درجة الاختبار الثالث.
• تحتوي الخلية B4 على درجة الاختبار الرابع.
• ستحتوي الخلية B5 على درجة الاختبار الخامس.
• B7 هو متوسط النجاح الذي يتم حسابه باستخدام الصيغة =(B1+B2+B3+B4+B5)/5.
  
  

لتحقيق هذا الهدف، يمكنك تطبيق ميزة Goal Seek على النحو التالي:

الخطوة الأولى: تفعيل ميزة Goal Seek

انتقل إلى تبويب البيانات، انقر فوق تحليل ما-إذا > Goal Seek.

الخطوة الثانية: تكوين إعدادات Goal Seek

1. في مربع تحديد الخلية، حدد الخلية التي تحتوي على الصيغة B7.
2. في مربع القيمة المستهدفة، أدخل متوسط النجاح 70%.
3. في مربع عن طريق تغيير الخلية، حدد الخلية (B5) التي تريد تعديلها.
4. انقر موافق.
   
   

النتيجة

سيظهر بعد ذلك مربع حوار حالة Goal Seek، مشيراً إلى أن الحل قد تم العثور عليه. وفي الوقت نفسه، سيتم إدخال الخلية B7 التي حددتها في مربع "عن طريق تغيير الخلية" تلقائيًا بقيمة متطابقة، وستحصل الخلية التي تحتوي على الصيغة على القيمة المستهدفة بناءً على التغيير المتغير. انقر موافق لقبول الحل.

إذن، لتحقيق المتوسط العام المطلوب وللنجاح في جميع الاختبارات، يحتاج الطالب إلى الحصول على درجة لا تقل عن 71% في الاختبار الأخير.

المثال الثالث: حساب الأصوات المطلوبة للفوز بالانتخابات

السيناريو: في انتخابات، يحتاج مرشح إلى حساب الحد الأدنى من الأصوات المطلوبة لتأمين الأغلبية. مع العلم بإجمالي عدد الأصوات المدلى بها، ما هو الحد الأدنى من الأصوات التي يجب أن يحصل عليها المرشح لتحقيق أكثر من 50% ويفوز؟

كما هو موضح في لقطة الشاشة أدناه:

• تحتوي الخلية B2 على إجمالي عدد الأصوات المدلى بها في الانتخابات.
• تحتوي الخلية B3 على عدد الأصوات الحالية التي حصل عليها المرشح.
• تحتوي الخلية B4 على نسبة الأغلبية (%) من الأصوات التي يهدف المرشح إلى تحقيقها، والتي يتم حسابها باستخدام الصيغة =B2/B1.
  
  

لتحقيق هذا الهدف، يمكنك تطبيق ميزة Goal Seek على النحو التالي:

الخطوة الأولى: تفعيل ميزة Goal Seek

انتقل إلى تبويب البيانات، انقر فوق تحليل ما-إذا > Goal Seek.

الخطوة الثانية: تكوين إعدادات Goal Seek

1. في مربع تحديد الخلية، حدد الخلية التي تحتوي على الصيغة B4.
2. في مربع القيمة المستهدفة، أدخل النسبة المئوية المطلوبة للأغلبية. في هذه الحالة، للفوز بالانتخابات، يجب أن يحصل المرشح على أكثر من نصف (أي أكثر من 50%) من إجمالي الأصوات الصحيحة، لذلك أدخلت 51%.
3. في مربع عن طريق تغيير الخلية، حدد الخلية (B2) التي تريد تعديلها.
4. انقر موافق.
   
   

النتيجة

سيظهر بعد ذلك مربع حوار حالة Goal Seek، مشيراً إلى أن الحل قد تم العثور عليه. وفي الوقت نفسه، سيتم استبدال الخلية B2 التي حددتها في مربع "عن طريق تغيير الخلية" تلقائيًا بالقيمة الجديدة، وستحصل الخلية التي تحتوي على الصيغة على القيمة المستهدفة بناءً على التغيير المتغير. انقر موافق لتطبيق التغييرات.

إذن، يحتاج المرشح إلى 5100 صوت على الأقل للفوز بالانتخابات.

المثال الرابع: تحقيق الربح المستهدف في الأعمال التجارية

السيناريو: تهدف شركة إلى تحقيق هدف ربح محدد قدره 150,000 دولار للسنة الحالية. بالنظر إلى التكاليف الثابتة والمتغيرة، يحتاجون إلى تحديد الإيرادات المطلوبة من المبيعات. كم عدد الوحدات المباعة المطلوبة لتحقيق هذا الهدف من الربح؟

كما هو موضح في لقطة الشاشة أدناه:

• تحتوي الخلية B1 على التكاليف الثابتة.
• تحتوي الخلية B2 على التكاليف المتغيرة لكل وحدة.
• تحتوي الخلية B3 على سعر الوحدة.
• تحتوي الخلية B4 على عدد الوحدات المباعة.
• B6 هو إجمالي الإيرادات، والذي يتم حسابه باستخدام الصيغة =B3*B4.
• B7 هو إجمالي التكاليف، والذي يتم حسابه باستخدام الصيغة =B1+(B2*B4).
• B9 هو الربح الحالي من المبيعات، والذي يتم حسابه باستخدام الصيغة =B6-B7.
  
  

للحصول على إجمالي وحدات المبيعات بناءً على الربح المستهدف، يمكنك تطبيق ميزة Goal Seek على النحو التالي.

الخطوة الأولى: تفعيل ميزة Goal Seek

انتقل إلى تبويب البيانات، انقر فوق تحليل ما-إذا > Goal Seek.

الخطوة الثانية: تكوين إعدادات Goal Seek

1. في مربع تحديد الخلية، حدد الخلية التي تحتوي على الصيغة التي تعيد الربح. هنا الخلية B9.
2. في مربع القيمة المستهدفة، أدخل الربح المستهدف 150000.
3. في مربع عن طريق تغيير الخلية، حدد الخلية (B4) التي تريد تعديلها.
4. انقر موافق.
   
   

النتيجة

سيظهر بعد ذلك مربع حوار حالة Goal Seek، مشيراً إلى أن الحل قد تم العثور عليه. وفي الوقت نفسه، سيتم استبدال الخلية B4 التي حددتها في مربع "عن طريق تغيير الخلية" تلقائيًا بقيمة جديدة، وستحصل الخلية التي تحتوي على الصيغة على القيمة المستهدفة بناءً على التغيير المتغير. انقر موافق لقبول التغييرات.

نتيجة لذلك، تحتاج الشركة إلى بيع 6666 وحدة على الأقل لتحقيق هدف الربح البالغ 150,000 دولار.

القضايا الشائعة والحلول الخاصة بـ Goal Seek

يعرض هذا القسم بعض القضايا الشائعة والحلول المقابلة لها لـ Goal Seek.

1. عدم قدرة Goal Seek على العثور على حل

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

2. بطء سرعة الحساب

• السبب: يمكن أن يكون Goal Seek بطيئًا مع مجموعات بيانات كبيرة، صيغ معقدة، أو عندما تكون القيمة الأولية بعيدة عن الحل، مما يتطلب المزيد من التكرارات.
• الحل: قم بتبسيط الصيغ حيثما كان ذلك ممكنًا وتقليل حجم البيانات. تأكد من أن القيمة الأولية قريبة قدر الإمكان من الحل المتوقع لتقليل عدد التكرارات اللازمة.

3. مشكلات الدقة

• السبب: عند استخدام Goal Seek، قد تلاحظ أنه في بعض الأحيان يقول Excel إنه وجد حلاً، لكنه ليس بالضبط ما تريده - قريب ولكنه ليس قريبًا بما فيه الكفاية. قد لا توفر Goal Seek دائمًا نتيجة دقيقة للغاية بسبب دقة الحساب في Excel والتقريب.
  
  على سبيل المثال، لحساب الأس المحدد لرقم واستخدام Goal Seek للعثور على الأساس الذي يحقق النتيجة المرجوة. هنا سأقوم بتغيير قيمة الإدخال في الخلية A1 (الأساس) بحيث تتطابق الخلية A3 (نتيجة الأس) مع النتيجة المستهدفة 25.
  
  كما ترى في لقطة الشاشة أدناه، توفر Goal Seek قيمة تقريبية بدلاً من الجذر الدقيق.
  
• الحل: قم بزيادة عدد المنازل العشرية المعروضة في خيارات الحساب في Excel للحصول على دقة أكبر ثم أعد تشغيل ميزة Goal Seek. الرجاء القيام بما يلي.
  
  1. انقر فوق ملف > خيارات لفتح نافذة خيارات Excel.
  2. حدد الصيغ في الجزء الأيسر، وفي قسم خيارات الحساب، قم بزيادة عدد المنازل العشرية في مربع التغيير الأقصى. هنا أقوم بتغيير 0.001 إلى 0.000000001 والنقر فوق موافق.
     
  3. أعد تشغيل Goal Seek وستحصل على الجذر الدقيق كما هو موضح في لقطة الشاشة أدناه.
     

4. القيود والطرق البديلة

• السبب: يمكن لـ Goal Seek تعديل قيمة إدخال واحدة فقط وقد لا تكون مناسبة للمعادلات التي تتطلب تعديلات متزامنة لمتغيرات متعددة.
• الحل: بالنسبة للسيناريوهات التي تحتاج إلى تعديلات لمتغيرات متعددة، استخدم أداة Solver في Excel، وهي أكثر قوة وتسمح بوضع القيود.

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