عند العمل مع جداول البيانات، من الشائع جدًا معرفة النتيجة المرجوة ولكن لا نعرف الرقم الذي يجب إدخاله في الخلية لتحقيقها. بدلًا من تجربة القيم يدويًا كما لو كنا نخمن، يمكنك ترك برنامج Excel وبرامج جداول البيانات الأخرى تقوم بالعمل نيابةً عنك باستخدام أداة البحث عن الهدف.
هذه الوظيفة أشبه بإخبار برنامج الجداول الإلكترونية: " أريد هذه النتيجة النهائية ؛ ابحث عن القيمة التي يجب أن تحتويها هذه الخلية لتحقيقها". إنها طريقة ملائمة للغاية لحل المعادلات ذات المجهول الواحد، وهي قابلة للتطبيق على سيناريوهات واقعية مثل القروض، ودرجات الامتحانات، والأرباح، والميزانيات، أو تحليلات الأعمال.
ما هو بالضبط البحث عن الهدف في جداول البيانات؟
تُعدّ أداة " البحث عن الهدف " جزءًا من ميزات تحليل "ماذا لو..." المضمنة في برنامج Excel وبرامج تحرير أخرى مثل ONLYOFFICE. على عكس الصيغ العادية التي تحسب النتيجة من بيانات الإدخال، تعمل أداة "البحث عن الهدف" بشكل عكسي : فأنت تحدد النتيجة المطلوبة، ويقوم برنامج الجداول الإلكترونية بحساب القيمة التي يجب أن تحتويها الخلية لكي تحقق الصيغة هذا الشرط.
بمعنى آخر، يسمح هذا الأسلوب للصيغة الحالية باعتماد قيمة محددة عن طريق تعديل متغير واحد تلقائيًا. وهذا يتجنب أسلوب التجربة والخطأ التقليدي ، حيث تقوم بتغيير رقم، ثم تنظر إلى النتيجة، ثم تغيره مرة أخرى، وهكذا حتى تحصل على النتيجة الصحيحة.
المفاهيم الأساسية: الخلية المستهدفة والخلية المتغيرة
لاستخدام خاصية البحث عن الهدف دون أي لبس، من المهم فهم عنصرين أساسيين: الخلية المستهدفة والخلية التي تتغير . إن فهم هذين المفهومين جيداً سيجنبك الكثير من المتاعب.
الخلية المستهدفة هي الخلية التي تحتوي على الصيغة التي تريد تحديد نتيجتها . وهي ليست أي خلية، بل يجب أن تحتوي دائمًا على صيغة: متوسط، أو دالة مالية، أو دالة SUMPRODUCT، أو حساب ربح، وما إلى ذلك. إنها المكان الذي تريد أن تظهر فيه القيمة النهائية التي تختارها، مثل الحد الأدنى لمعدل الدرجات، أو القسط الشهري، أو الربح المطلوب، أو معدل فائدة محدد.
الخلية التي تتغير هي الخلية المجهولة ، وهي الخلية التي ستُعدّل الأداة قيمتها لتحقيق النتيجة المطلوبة. يجب أن تكون هذه الخلية جزءًا من الصيغة الموجودة في الخلية المستهدفة؛ فإذا لم تُشر الصيغة إليها، فلن تتمكن أداة البحث عن الهدف من التأثير على النتيجة.
وأخيرًا، ستحتاج إلى تحديد القيمة المطلوبة ، وهي الرقم المحدد الذي تريد تحقيقه في الخلية المستهدفة: درجة نهائية قدرها 50، ومتوسط قدره 70، واحتمالات قدرها -900، وربح قدره 50.000، وما إلى ذلك. أنت تحدد هذه القيمة، وهي محور التحليل بأكمله.
كيفية استخدام ميزة البحث عن الهدف: نظرة عامة خطوة بخطوة
قبل الخوض في أمثلة محددة، يجدر بنا إلقاء نظرة على التدفق العام ، لأنه يتكرر بنفس الطريقة تقريبًا في أي موقف، سواء في Excel أو في ONLYOFFICE والمحررات المتوافقة الأخرى.
بشكل عام، يتبع الاستخدام النموذجي لـ Goal Seek هذا التسلسل: أولاً تقوم بإعداد الورقة والصيغ ، ثم تفتح الأداة، وتحدد الخلية المستهدفة، والقيمة المراد الوصول إليها، والخلية التي ستتغير، وتترك البرنامج يجد الحل.
سيكون المسار المعتاد كالتالي:
- إعداد البياناتتقوم بإدخال الخلايا بالقيم التي تعرفها مسبقًا (مبلغ القرض، عدد الأشهر، السندات القائمة، التكاليف، الأسعار، إلخ)، وإذا لزم الأمر، يمكنك إزالة التكرارات.
- أنشئ الصيغة اللازمة للنتيجة: تكتب في الخلية المستهدفة الصيغة التي تحسب النتيجة التي تريد التحكم فيها (المتوسط، PAY/PMT، الربح، SUMPRODUCT، إلخ).
- افتح البحث عن الهدفمن علامة التبويب "البيانات"، ضمن مجموعة "تحليل الفرضيات" أو "تحليل النتائج"، يمكنك الوصول إلى "البحث عن الهدف".
- املأ المعايير الثلاثة: أنت تحدد الخلية المستهدفة، والقيمة المراد تحقيقها، والخلية التي تتغير.
- اركض واقبليقوم البرنامج بحساب التقريبات المتتالية حتى يتم العثور على حل، ويعرض لك مربعًا بحالة البحث، ويمكنك قبول ذلك بحيث تبقى القيم الجديدة في الورقة.
يتكرر هذا النمط في جميع السيناريوهات: الأشياء الوحيدة التي تتغير هي الصيغ المستخدمة والخلية التي تقرر استخدامها كمجهول.
تحديد الأهداف من خلال الدرجات: معرفة الدرجة التي تحتاج إلى الحصول عليها
من الاستخدامات الشائعة الأخرى لخاصية "البحث عن الهدف" حساب الدرجة المطلوبة في امتحان مُقبل لتحقيق معدل نهائي مُحدد. يُعد هذا مثالًا توضيحيًا بسيطًا ومثاليًا لفهم كيفية عمل الأداة في الاتجاه المعاكس.
الفكرة بسيطة: لديك بالفعل بعض الدرجات، وينقصك اختبار أخير، وتريد أن تعرف الحد الأدنى للنتيجة التي تحتاجها لكي يصل معدلك النهائي إلى قيمة معينة، سواء كان المعدل بسيطًا أو مرجحًا.
المعدل البسيط لعدة درجات
تخيل أن لديك أربعة اختبارات في مادة معينة. لقد أكملت ثلاثة منها، وحصلت على درجاتها، ولم يتبق سوى الاختبار النهائي. أنت تعلم أنك تحتاج إلى درجة نهائية قدرها 50 لاجتياز المادة، وتريد من برنامج إكسل أن يخبرك بالدرجة التي تحتاجها في ذلك الاختبار الأخير.
أولًا، أنشئ جدولًا بالدرجات المعروفة واترك خلية واحدة فارغة للدرجة المفقودة. في خلية أخرى، احسب الدرجة النهائية كمتوسط بسيط، على سبيل المثال باستخدام =AVERAGE(grade_range) أو =AVERAGE إذا كنت تستخدم دالة اللغة الإنجليزية. ستكون خلية المتوسط هذه هي الخلية المستهدفة.
بعد ذلك، افتح "البحث عن الهدف" من "بيانات" > "تحليل ماذا لو" > "البحث عن الهدف" وقم بضبط المعلمات. في "تعيين الخلية" ، حدد الخلية التي تحتوي على المتوسط (على سبيل المثال، C6). ثم، في " إلى القيمة" ، أدخل القيمة المستهدفة، والتي ستكون 50 في هذه الحالة. في "تغيير الخلية" ، اختر الخلية التي تحتوي على الدرجة المتميزة، على سبيل المثال، C5، وهي الخلية التي سيقوم Excel بتعديلها تلقائيًا.
بعد النقر على "موافق"، تقوم الأداة بحساب درجة الامتحان اللازمة لتحقيق معدل 50 بالضبط. سيعرض برنامج Excel في مربع حالة البحث ما إذا تم العثور على حل، وإذا كان الأمر كذلك، فسيكتب الحد الأدنى للدرجة التي تحتاجها في خلية الدرجة المعلقة، مثل 60%.
الدرجات المرجحة باستخدام SUMPRODUCT
إذا أردتَ تطوير الأمر أكثر، يمكنك تطبيق منهجية البحث عن الهدف على نظام التقييم الموزون ، حيث يكون لكل تقييم وزن مختلف (على سبيل المثال، الواجبات، والاختبارات القصيرة، والامتحان النهائي، إلخ). في هذه الحالة، لا تكون الدرجة النهائية مجرد متوسط بسيط، بل متوسط مرجح.
يتم ذلك عادةً باستخدام دالة SUMPRODUCT ، التي تضرب كل درجة في وزنها وتجمع النواتج. بقسمة الناتج على مجموع الأوزان، نحصل على متوسط الدرجات المرجح النهائي. هذه هي الصيغة التي ستضعها في الخلية المستهدفة.
تكون العملية كالتالي: في عمود، تُدرج الأنشطة المختلفة (الاختبار 1، الاختبار 2، الواجب، الامتحان، إلخ)، وفي عمود آخر تُدرج الدرجات، وفي عمود ثالث تُدرج النسب المئوية أو الأوزان. يمكن أن تكون صيغة الدرجة النهائية على النحو التالي: =SUMPRODUCT(grade_range;weight_range)/SUM(weight_range) . الخلية التي تحتوي على SUMPRODUCT وSUM هي الخلية المستهدفة.
عند فتح "البحث عن الهدف"، في خانة "تعيين الخلية"، اختر الخلية التي تحتوي على الدرجة النهائية المرجحة؛ وفي خانة "القيمة المطلوبة"، أدخل الحد الأدنى الذي ترغب في تحقيقه (على سبيل المثال، 50)؛ وفي خانة "تغيير الخلية"، حدد درجة الامتحان مرة أخرى ، وهي القيمة الوحيدة غير المعروفة. بعد تشغيل البحث، سيخبرك البرنامج بالدرجة التي يجب أن يحصل عليها الطالب في الامتحان النهائي، على سبيل المثال، 57,5% ، حتى يصل المتوسط العام إلى 50.
استخدام خاصية البحث عن الهدف في برنامج ONLYOFFICE Docs 8.0
ليست شركة Excel هي الوحيدة التي قامت بدمج هذه الأداة؛ فقد أضافت برامج التحرير مثل ONLYOFFICE Docs أيضًا وظيفة البحث عن الهدف إلى جداول البيانات الخاصة بها، مع وظائف مماثلة للغاية، وهي مثالية إذا كنت تعمل في بيئات تعاونية أو سحابية.
تتشابه آلية العمل في برنامج ONLYOFFICE إلى حد كبير: أولًا، تُدخل بياناتك والصيغة التي تربط هذه البيانات بالنتيجة التي تريد تتبعها، ويمكنك استخدام ميزات مفيدة مثل الإكمال التلقائي . على سبيل المثال، إذا كنت ترغب في حساب الحد الأدنى لدرجة النجاح، يمكنك إدخال الدرجات في نطاق واستخدام الدالة =AVERAGE(B2:B5) في خلية النتيجة، ولتكن B7. ستكون هذه الخلية، B7، هي الخلية المستهدفة.
بعد ذلك، انتقل إلى علامة التبويب "بيانات" وانقر على أيقونة " البحث عن الهدف" . سيظهر مربع حوار يحتوي على ثلاثة حقول مشابهة لتلك الموجودة في برنامج Excel. في حقل "تعيين الخلية" ، اختر الخلية التي تحتوي على الصيغة (B7)؛ في حقل "القيمة" ، أدخل المتوسط المطلوب (على سبيل المثال، 70)؛ وفي حقل " عند تغيير الخلية"، حدد الخلية التي تحتوي على الدرجة المفقودة (على سبيل المثال، B5)، وهي الخلية التي سيتم تحديثها في جدول البيانات.
عند النقر على "موافق"، يقوم برنامج ONLYOFFICE بحساب القيمة المطلوبة للدرجة المجهولة حتى يصل المعدل إلى 70. ستعرض نافذة "البحث عن الهدف" النتيجة؛ إذا قبلتها، فسيتم تحديث الخلايا ذات الصلة. في المثال الموضح، قد يُظهر النظام أنك تحتاج إلى 85 في الاختبار الأخير لتحقيق هذا المعدل الأدنى.
ينطبق هذا المبدأ نفسه على حالات أخرى: سداد القروض، وتخطيط الاستثمار، والميزانيات الثابتة التي ترغب في تعديل فئة معينة فيها، أو تحليل نقطة التعادل في المبيعات. طالما لديك متغير مجهول واحد ومعادلة تربطه بهدفك، فإنّ "البحث عن الهدف" مناسب تمامًا.
متى تستخدم خاصية البحث عن الهدف ومتى تنتقل إلى خاصية الحل؟
على الرغم من أن خاصية البحث عن الهدف مفيدة للغاية، إلا أنها تعاني من قصور جوهري: فهي لا تسمح إلا بتعديل خلية متغيرة واحدة لتحقيق النتيجة. إذا كانت مشكلتك تتضمن عدة متغيرات مجهولة أو قيودًا معقدة، فلن تكون هذه الخاصية كافية، وستحتاج إلى استخدام أدوات أكثر تقدمًا مثل أداة الحل.
Solver هو إضافة لبرنامج Excel (وغيره من البرامج) تُوسّع نطاق تحليل "ماذا لو" من خلال السماح لك بزيادة أو تقليل قيمة خلية مستهدفة، وتعديل خلايا متعددة في آن واحد، وإضافة قيود (حدود دنيا، حدود قصوى، علاقات بين الخلايا، إلخ). وهو مثالي لتحسين الربحية، وتخصيص الموارد، أو البرمجة الخطية.
على سبيل المثال، إذا أردتَ معرفة الدرجات التي تحتاجها في اختبارين مختلفين (اللغة الإنجليزية والكيمياء) للحصول على معدل 84، فلن تُجدي خاصية "البحث عن الهدف" نفعًا لوجود متغيرين. أما خاصية "الحل" فتتيح لك تحديد خلية المعدل (بقيمة 84) كهدف، وتغيير قيم الخلايا لكلا المادتين، ثم إيجاد توليفة ممكنة، مثل الحصول على 87 في كل منهما.
في سيناريو الأعمال، يمكنك استخدام أداة Solver لتحقيق أقصى ربح عبر خطوط خدمات متعددة، حيث يجب عليك في الوقت نفسه تحقيق الحد الأدنى من أهداف المبيعات لخدمة معينة، والحد الأقصى لأهداف المبيعات لخدمة أخرى، بالإضافة إلى حد أقصى إجمالي للمبيعات. في هذه الحالات، ستحدد إجمالي الربح كهدف، وتختار الكميات المباعة كمتغيرات، وتحدد القيود، تاركًا لأداة Solver مهمة إيجاد التركيبة المثلى.
يُعد البحث عن الهدف مثاليًا للمسائل ذات المتغير الواحد (خلية واحدة متغيرة) والصيغ البسيطة نسبيًا، بينما يتولى Solver المهمة عندما يكون هناك متغيرات متعددة وقيود متزامنة.
المشاكل الشائعة: لماذا قد لا ينجح البحث عن الهدف بشكل جيد
على الرغم من أن الأداة تعمل عادةً بكفاءة عالية، إلا أن هناك أوقاتًا يبدو فيها أن خاصية البحث عن الهدف "تفشل" أو تعجز عن إيجاد حل. لا تكمن المشكلة عادةً في البرنامج نفسه، بل في الإعدادات أو الصيغة.
من أولى الأمور التي يجب التحقق منها إعدادات التكرار في المصنف . على سبيل المثال، يحدّ برنامج إكسل من الحد الأقصى لعدد التكرارات والحد الأقصى للتغيير المسموح به بين التقريبات لمنع الحسابات اللانهائية. إذا كان عدد التكرارات منخفضًا جدًا أو كان الحد الأقصى للتغيير كبيرًا جدًا، فقد تتوقف الأداة قبل الأوان أو تفشل في تحقيق الدقة المطلوبة.
في برنامج إكسل، يمكنك مراجعة هذه المعلمات من خلال ملف > خيارات > الصيغ . هناك، يمكنك زيادة الحد الأقصى لعدد التكرارات للسماح بمزيد من محاولات البحث، أو تقليل الحد الأقصى للتغيير لزيادة الدقة. غالبًا ما يؤدي تعديل هذه القيم إلى تحسين النتائج في المسائل الأكثر تعقيدًا.
من الأخطاء الشائعة الأخرى عدم تضمين صيغة الخلية المستهدفة للخلية المراد تغييرها. فإذا لم تكن الخلية المراد تغييرها جزءًا من الصيغة، فلن يؤثر ذلك على النتيجة مهما تم تدويرها في جدول البيانات. كما يجب التأكد من كتابة الصيغة بشكل صحيح وتجنب إنشاء مراجع دائرية ، لأن ذلك قد يعيق الحساب أو يتسبب في فشل عملية البحث عن الهدف.
إن إتقان خاصية البحث عن الهدف في برنامج Excel وجداول البيانات الأخرى هو طريقة سريعة للانتقال من الحسابات التقريبية إلى التحليل العددي الأكثر جدية دون الخوض في الرياضيات المتقدمة: ببساطة افهم صيغة النتيجة، وحدد المتغير الذي تريد تعديله، واستخدم الأداة للقيام بالعمل الشاق نيابة عنك، سواء كان ذلك لأموالك الشخصية أو دراستك أو الإدارة اليومية لعملك.
