كيفية استخدام Goal Seek في Microsoft Excel لسيناريوهات What-If

يسمح لك تحليل What-If في Excel بمعرفة كيف يمكن أن يؤثر تغيير خلية على ناتج الصيغة. يمكنك استخدام أدوات Excel لهذا الغرض لحساب تأثير تغيير قيمة خلية في صيغة.

يحتوي Excel على ثلاثة أنواع من أدوات تحليل What-If: Scenario Manager و Goal Seek و Data Table. باستخدام Goal Seek ، يمكنك تحديد المدخلات التي تحتاجها لتحويل الصيغة للخلف إلى ناتج معين.

ميزة Goal Seek في Excel هي التجربة والخطأ ، لذلك إذا لم تنتج ما تريد ، فإن Excel يعمل على تحسين هذه القيمة حتى يتم ذلك.

ما هي صيغ البحث عن هدف Excel؟

ينقسم Goal Seek في Excel بشكل أساسي إلى ثلاثة مكونات رئيسية:

  1. تعيين الخلية : الخلية التي تريد استخدامها كهدف لك.
  2. إلى القيمة : القيمة التي تريدها كهدفك.
  3. من خلال تغيير الخلية : الخلية التي ترغب في تغييرها للوصول إلى قيمة الهدف.

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

إذا كان كل هذا يبدو محيرًا ، فستمنحك الأمثلة أدناه فكرة جيدة عن كيفية عمل Goal Seek.

نموذج البحث عن الهدف 1

على سبيل المثال ، لنفترض أن لديك خليتان (A & B) وخلية ثالثة تحسب متوسط ​​هاتين الخليتين.

لنفترض الآن أن لديك قيمة ثابتة لـ A ، وترغب في رفع المستوى المتوسط ​​عن طريق تغيير قيمة B. باستخدام Goal Seek ، يمكنك حساب قيمة B التي ستنتج المتوسط ​​الذي تريده.

ذات صلة: نصائح لتوفير الوقت في Microsoft Excel

كيفية استخدام Goal Seek في Excel

  1. في Excel ، انقر فوق الخلية C1 .
  2. في شريط الصيغة ، أدخل الرمز التالي:
     =AVERAGE(A1:B1)

    ستجعل Excel هذه الصيغة يحسب متوسط ​​القيم في الخليتين A1 و B1 ويخرج ذلك في الخلية C1 . يمكنك التأكد من أن الصيغة تعمل عن طريق إدخال الأرقام في A1 و B1. يجب أن تظهر C1 متوسطها.

  3. في هذا المثال ، قم بتغيير قيمة الخلية A1 إلى 16 .
  4. حدد الخلية C1 ، ثم من الشريط ، انتقل إلى علامة التبويب البيانات .
  5. ضمن علامة التبويب البيانات ، حدد تحليل ماذا إذا ثم البحث عن الهدف . سيظهر هذا نافذة Goal Seek.
  6. في نافذة Goal Seek ، اضبط خلية التعيين على C1 . ستكون هذه خلية هدفك. بعد تمييز C1 قبل فتح نافذة Goal Seek ، سيتم تعيينه تلقائيًا في Set cell .
  7. في إلى القيمة ، أدخل قيمة الهدف التي تريدها. في هذا المثال ، نريد أن يكون المتوسط ​​26 ، لذا ستكون قيمة الهدف 26 أيضًا.
  8. أخيرًا ، في الخلية عن طريق تغييرها ، حدد الخلية التي تريد تغييرها للوصول إلى الهدف. في هذا المثال ، ستكون الخلية B2 .
  9. انقر فوق " موافق" لجعل Goal Seek يعمل بسحره.

بمجرد النقر فوق "موافق" ، سيظهر مربع لإعلامك بأن Goal Seek قد وجد حلاً.

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

الموضوعات ذات الصلة: صيغ Excel المجنونة التي تقوم بأشياء مذهلة

مثال البحث عن الهدف 2

يمكن أن يكون Goal Seek مفيدًا عند استخدامه في سيناريوهات الحياة الواقعية. ومع ذلك ، لاستخدام Goal Seek بكامل إمكاناته ، يجب أن يكون لديك صيغة مناسبة ، لتبدأ بها.

في هذا المثال ، لنأخذ حالة مصرفية صغيرة. افترض أن لديك حسابًا مصرفيًا يمنحك فائدة سنوية بنسبة 4٪ على الأموال الموجودة في حسابك.

باستخدام تحليل What-If و Goal Seek ، يمكنك حساب مقدار الأموال التي تحتاجها في حسابك للحصول على مبلغ معين من مدفوعات الفائدة الشهرية.

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

  1. في الخلية A1 ، اكتب التوازن .
  2. في الخلية A2 اكتب معدل سنوي .
  3. في الخلية A4 ، اكتب الربح الشهري .
  4. في الخلية B2 ، اكتب .
  5. حدد الخلية B4 وفي شريط الصيغة ، أدخل الصيغة أدناه:
     =B1*B2/12

    سيساوي الربح الشهري رصيد الحساب مضروبًا في المعدل السنوي ثم مقسومًا على عدد الأشهر في السنة ، 12 شهرًا.

  6. انتقل إلى علامة التبويب البيانات ، وانقر فوق تحليل ماذا إذا ، ثم حدد Goal Seek .
  7. في نافذة Goal Seek ، اكتب B4 في Set cell .
  8. اكتب 350 في خلية القيمة إلى . (هذا هو الربح الشهري الذي تريد الحصول عليه)
  9. اكتب B1 في عن طريق تغيير الخلية . (سيؤدي هذا إلى تغيير الرصيد إلى قيمة ستنتج 350 دولارًا شهريًا)
  10. انقر فوق " موافق" . سيظهر مربع حوار حالة البحث عن الهدف .

في مربع الحوار "حالة البحث عن الهدف" ، سترى أن "البحث عن الهدف" قد وجد حلاً في الخلية B4. في الخلية B1 ، يمكنك رؤية الحل الذي يجب أن يكون 105000.

متطلبات البحث عن الهدف

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

في Excel ، لا يزال بإمكانك حل أكثر من متغير واحد ، لكنك ستحتاج إلى استخدام أداة أخرى تسمى Solver. يمكنك قراءة المزيد حول برنامج Excel's Solver من خلال قراءة مقالتنا حول كيفية استخدام برنامج Excel's Solver .

تحديد الهدف

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

  1. في الخلية A1 ، اكتب 3 .
  2. حدد الخلية C1 ، ثم في شريط الصيغة ، أدخل الصيغة أدناه:
     =1/(A1-5)

    ستقسم هذه الصيغة واحدًا على A1-5 ، وإذا كانت A1-5 تساوي 0 ، فستكون النتيجة قيمة غير محددة.

  3. انتقل إلى علامة التبويب البيانات ، وانقر فوق تحليل ماذا إذا ، ثم حدد Goal Seek .
  4. في تعيين الخلية ، اكتب C1 .
  5. في القيمة إلى ، اكتب 1 .
  6. أخيرًا ، في عن طريق تغيير نوع الخلية A1 . (سيؤدي هذا إلى تغيير قيمة A1 لتصل إلى 1 في C1)
  7. انقر فوق " موافق" .

سيخبرك مربع الحوار Goal Seek Status أنه ربما لم يعثر على حل ، ويمكنك التحقق مرة أخرى من أن قيمة A1 بعيدة المنال.

الآن ، حل هذا الهدف سيكون ببساطة الحصول على 6 في A1. سيعطي هذا 1/1 ، وهو ما يساوي 1. ولكن في مرحلة ما أثناء عملية التجربة والتحسين ، حاول Excel 5 في A1 ، مما يعطي 1/0 ، وهو غير محدد ، مما يؤدي إلى قتل العملية.

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

على سبيل المثال ، إذا قمت بتغيير A1 إلى أي رقم أكبر من 5 ثم كررت نفس الخطوات إلى Goal Seek for 1 ، فيجب أن تحصل على النتيجة الصحيحة.

اجعل العمليات الحسابية أكثر كفاءة مع البحث عن الهدف

يمكن لتحليل What-If و Goal Seek تسريع العمليات الحسابية باستخدام الصيغ الخاصة بك إلى حد كبير. يعد تعلم الصيغ الصحيحة خطوة مهمة أخرى نحو أن تصبح خبيرًا في Excel.