مهمة النقل في Excel. إيجاد أفضل وسيلة للنقل من البائع إلى المشتري

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

وصف مهمة النقل

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

1. مغلق. في هذه الحالة ، يكون العرض والطلب متوازنين.
2. فتح. لا يوجد مساواة بين العرض والطلب هنا. للحصول على حل لهذه المشكلة ، يجب عليك أولاً إحضارها إلى النوع الأول ، معادلة العرض والطلب. للقيام بذلك ، تحتاج إلى تقديم مؤشر إضافي - وجود مشترٍ أو بائع مشروط. بالإضافة إلى ذلك ، تحتاج إلى إجراء بعض التغييرات على جدول التكلفة.

كيفية تمكين ميزة البحث عن الحل في Excel

لحل مشاكل النقل في Excel ، هناك وظيفة خاصة تسمى "البحث عن حل". لا يتم تمكينه افتراضيًا ، لذلك عليك القيام بالخطوات التالية:

1. افتح قائمة "ملف" الموجودة في الزاوية اليسرى العليا من نافذة البرنامج.
2. بعد ذلك ، انقر فوق الزر الذي يحتوي على المعلمات.
3. بعد ذلك ، نجد قسم "الإعدادات" الفرعي وانتقل إلى قائمة إدارة الوظائف الإضافية. هذه برامج صغيرة يتم تشغيلها ضمن بيئة Microsoft Excel. نرى أننا في البداية نقرنا على قائمة "الوظائف الإضافية" ، ثم في الجزء الأيمن السفلي قمنا بتعيين عنصر "وظائف Excel الإضافية" وقمنا بالنقر فوق الزر "انتقال". يتم تمييز جميع الإجراءات اللازمة باستخدام مستطيلات وأسهم حمراء.
4. بعد ذلك ، قم بتشغيل الوظيفة الإضافية "البحث عن حل" ، وبعد ذلك نؤكد إجراءاتنا بالضغط على الزر "موافق". استنادًا إلى وصف الإعداد ، يمكننا أن نرى أنه مصمم لتحليل البيانات المعقدة ، مثل البيانات العلمية والمالية.
5. بعد ذلك ، انتقل إلى علامة التبويب "البيانات" ، حيث نرى زرًا جديدًا يسمى نفس الوظيفة الإضافية. يمكن العثور عليها في مجموعة أدوات التحليل.

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

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

بالإضافة إلى مهام النقل ، تُستخدم هذه الوظيفة الإضافية أيضًا للأغراض التالية:

1. تطوير خطة الإنتاج. أي عدد وحدات المنتج التي يجب إنتاجها لتحقيق أقصى دخل.
2. ابحث عن توزيع العمالة لأنواع مختلفة من العمل بحيث تكون التكلفة الإجمالية لإنتاج منتج أو خدمة هي الأصغر.
3. اضبط الحد الأدنى من الوقت الذي يستغرقه إكمال كل العمل.

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

من المهم ملاحظة أن تحسين مجموعة القيم يتم بشكل حصري على مؤشر واحد ، وهو ما يسمى الوظيفة الموضوعية.

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

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

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

مثال على حل مشكلة النقل في Excel

من أجل توضيح كيفية حل مشاكل النقل عمليًا في Excel ، دعنا نعطي مثالاً.

مهام الشروط

افترض أن لدينا 6 بائعين و 7 مشترين. يتم توزيع العرض والطلب بينهما على التوالي بالطريقة التالية: 36 و 51 و 32 و 44 و 35 و 38 وحدة بائعون و 33 و 48 و 30 و 36 و 33 و 24 و 32 وحدة مشترون. إذا جمعت كل هذه القيم ، فستجد أن العرض والطلب متوازنان. لذلك ، هذه المشكلة من النوع المغلق ، والتي يتم حلها بكل بساطة.

بالإضافة إلى ذلك ، لدينا معلومات حول المبلغ الذي تحتاج إلى إنفاقه على النقل من النقطة A إلى النقطة B (تم تمييزها بالخلايا الصفراء في المثال).

الحل - خوارزمية خطوة بخطوة

الآن ، بعد أن تعرفنا على الجداول بالبيانات الأولية ، يمكننا استخدام الخوارزمية التالية لحل هذه المشكلة:

1. أولاً ، نصنع جدولاً يتكون من 6 صفوف و 7 أعمدة.
2. بعد ذلك ، ننتقل إلى أي خلية لا تحتوي على أي قيم وفي نفس الوقت تقع خارج الجدول الذي تم إنشاؤه حديثًا وإدراج الوظيفة. للقيام بذلك ، انقر فوق الزر fx الموجود على يسار سطر إدخال الوظيفة.
3. لدينا نافذة نحتاج فيها إلى تحديد فئة "الرياضيات". ما الوظيفة التي نهتم بها؟ الذي تم إبرازه في لقطة الشاشة هذه. دور SUMPRODUCT تضرب النطاقات أو المصفوفات فيما بينها وتجمعها. فقط ما نحتاجه. بعد ذلك ، اضغط على مفتاح OK.
4. بعد ذلك ، ستظهر نافذة على الشاشة تحتاج فيها إلى تحديد معلمات الوظيفة. هم كالتالي:
   1. المصفوفة 1. هذه هي الوسيطة الأولى التي نكتب فيها النطاق المميز باللون الأصفر. يمكنك ضبط معلمات الوظيفة إما باستخدام لوحة المفاتيح أو عن طريق تحديد المنطقة المناسبة باستخدام زر الماوس الأيسر.
   2. الصفيف 2. هذه هي الوسيطة الثانية ، وهي الجدول الذي تم إنشاؤه حديثًا. يتم تنفيذ الإجراءات بنفس الطريقة.

قم بتأكيد الإجراء الخاص بك عن طريق الضغط على زر موافق.

1. بعد ذلك ، نقوم بالنقر بزر الماوس الأيسر على الخلية التي تعمل كأعلى اليسار في الجدول الذي تم إنشاؤه حديثًا. الآن انقر فوق زر إدراج الوظيفة مرة أخرى.
2. نختار نفس الفئة كما في الحالة السابقة. لكن هذه المرة نحن مهتمون بالوظيفة مجموع.
3. الآن تأتي مرحلة ملء الحجج. كحجة أولى ، نكتب الصف العلوي من الجدول الذي أنشأناه في البداية. بالطريقة نفسها كما في السابق ، يمكن القيام بذلك عن طريق تحديد هذه الخلايا على الورقة ، أو يدويًا. نؤكد أفعالنا بالضغط على زر موافق.
4. سنرى النتائج في الخلية مع الوظيفة. في هذه الحالة ، إنها صفر. بعد ذلك ، انقل المؤشر إلى الزاوية اليمنى السفلية ، وبعد ذلك ستظهر علامة الإكمال التلقائي. يبدو وكأنه قطيفة سوداء صغيرة. إذا ظهرت ، فاضغط مع الاستمرار على زر الماوس الأيسر وحرك المؤشر إلى الخلية الأخيرة في جدولنا.
5. يمنحنا هذا الفرصة لنقل الصيغة إلى جميع الخلايا الأخرى والحصول على النتائج الصحيحة دون الحاجة إلى إجراء حسابات إضافية.
6. الخطوة التالية هي تحديد الخلية اليسرى العلوية ولصق الوظيفة مجموع بداخلها. بعد ذلك ، ندخل الوسائط ونستخدم علامة الإكمال التلقائي لملء جميع الخلايا المتبقية.
7. بعد ذلك ، ننتقل مباشرة إلى حل المشكلة. للقيام بذلك ، سوف نستخدم الوظيفة الإضافية التي قمنا بتضمينها سابقًا. انتقل إلى علامة التبويب "البيانات" ، وهناك نجد أداة "البحث عن حل". نضغط على هذا الزر.
8. الآن ظهرت نافذة أمام أعيننا ، يمكنك من خلالها تكوين معلمات إضافتنا. دعنا نلقي نظرة على كل من هذه الخيارات:
   1. تحسين وظيفة الهدف. هنا نحتاج إلى تحديد الخلية التي تحتوي على الوظيفة SUMPRODUCT. نرى أن هذا الخيار يجعل من الممكن تحديد وظيفة سيتم البحث عن حل لها.
   2. قبل. هنا قمنا بتعيين خيار "الحد الأدنى".
   3. عن طريق تغيير خلايا المتغيرات. نشير هنا إلى النطاق المقابل للجدول الذي أنشأناه في البداية (باستثناء صف وعمود التلخيص).
   4. تخضع للقيود. هنا نحتاج إلى إضافة قيود عن طريق النقر فوق الزر "إضافة".
   5. نتذكر نوع القيد الذي نحتاج إلى إنشائه - يجب أن يكون مجموع قيم طلبات المشترين وعروض البائعين هو نفسه.
9. يتم تنفيذ مهمة القيود على النحو التالي:
   1. ارتباط بالخلايا. هنا ندخل نطاق الجدول للحسابات.
   2. شروط. هذه عملية حسابية يتم على أساسها التحقق من النطاق المحدد في حقل الإدخال الأول.
   3. قيمة الشرط أو القيد. هنا ندخل العمود المناسب في الجدول المصدر.
   4. بعد اكتمال جميع الخطوات ، انقر فوق الزر "موافق" ، وبالتالي تأكيد إجراءاتنا.

نقوم بإجراء نفس العمليات بالضبط للصفوف العليا ، مع تحديد الشرط التالي: يجب أن تكون متساوية.

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

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

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

وفي الختام

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