تطبيق تحليل ABC في Microsoft Excel

واحدة من الطرق الرئيسية للإدارة والخدمات اللوجستية هي تحليل ABC. باستخدامه ، يمكنك تصنيف موارد المؤسسة ، والمنتجات ، والعملاء ، إلخ. حسب درجة الأهمية. في الوقت نفسه ، ووفقًا لمستوى الأهمية ، يتم تعيين كل واحدة من الوحدات المذكورة أعلاه في واحدة من ثلاث فئات: A أو B أو C. يوجد Excel في أدوات الأمتعة الخاصة به التي تسهّل تنفيذ هذا النوع من التحليل. دعونا معرفة كيفية استخدامها ، وما هو تحليل ABC.

استخدام تحليل ABC

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

• الفئة ( أ) - تحتوي العناصر على أكثر من 80٪ من الثقل النوعي ؛
• الفئة B - العناصر ، التي يتراوح مجموعها من 5٪ إلى 15٪ من الثقل النوعي ؛
• الفئة C - العناصر المتبقية ، مجموعها الإجمالي هو 5 ٪ وأقل من الوزن المحدد.

تطبق بعض الشركات تقنيات أكثر تقدمًا وتفكك العناصر ليس في 3 ولكن في 4 أو 5 مجموعات ، لكننا سنعتمد على مخطط التحليل ABC الكلاسيكي.

الطريقة 1: التحليل عن طريق الفرز

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

لدينا جدول بقائمة السلع التي تبيعها الشركة ، والمبلغ المقابل من العائد من بيعها لفترة معينة من الزمن. في الجزء السفلي من الجدول ، يتم تحقيق إجمالي الأرباح لجميع أسماء المنتجات. ومن الجدير بالمهمة ، باستخدام تحليل ABC ، ​​لكسر هذه السلع في مجموعات من خلال أهميتها للمشروع.

1. حدد الجدول مع المؤشر المحدد ، مع الاستمرار على زر الماوس الأيسر ، باستثناء الرأس والخط النهائي. انتقل إلى علامة التبويب "البيانات" . انقر فوق الزر "فرز" الموجود في مربع الأداة "فرز وتصفية" على الشريط.

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

2. عند تطبيق أي من الإجراءات المذكورة أعلاه ، يتم تشغيل نافذة الفرز. دعنا نرى أن الخيار "My data contains the headers" قد تم تحديده. في حالة غيابه ، نؤسس.

   في حقل "العمود" ، نشير إلى اسم العمود الذي يحتوي على بيانات حول الأرباح.

   في حقل "التصنيف" ، تحتاج إلى تحديد معايير محددة للفرز. نترك إعدادات الضبط المسبق - "القيم" .

   في حقل "الطلب" ، قمنا بتعيين الموضع "تنازلي" .

   بعد إجراء الإعدادات المحددة ، انقر فوق الزر "موافق" في أسفل النافذة.

3. بعد تنفيذ الإجراء المحدد ، تم فرز جميع العناصر حسب العائدات من الأكبر إلى الأصغر.
4. الآن نحن بحاجة إلى حساب الوزن المحدد لكل عنصر من العناصر لإجمالي الكلي. نقوم بإنشاء عمود إضافي لهذه الأغراض ، والذي سنطلق عليه "الوزن الخاص" . في الخلية الأولى من هذا العمود ، نضع العلامة "=" ، والتي بعدها نشير إلى الرابط إلى الخلية التي يوجد فيها مبلغ الإيرادات من بيع المنتج المطابق. بعد ذلك ، قمنا بتعيين علامة القسمة ( "/" ). بعد ذلك ، حدد إحداثيات الخلية ، التي تحتوي على إجمالي كمية مبيعات البضائع في جميع أنحاء المؤسسة.

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

   بعد ذلك ، لإجراء الحسابات ، اضغط على الزر Enter .

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

   لذا ، في السطر الأول ، قم بنقل المؤشر إلى عمود "حصة متراكمة" من عمود "الوزن المحدد" .

9. بعد ذلك ، نضع المؤشر في الخلية الثانية من العمود "المشاركة المتراكمة" . هنا علينا تطبيق الصيغة. نضع علامة "متساوية" ونضيف محتويات خلية "الوزن النوعي" للصف نفسه ومحتويات خلية "التراكمية" من الخط أعلاه. يتم ترك جميع المراجع النسبية ، وهذا هو ، نحن لا التلاعب بها. بعد ذلك ، نضغط على زر Enter لعرض النتيجة النهائية.
10. الآن تحتاج إلى نسخ هذه الصيغة إلى خلايا هذا العمود ، والتي يتم وضعها أدناه. للقيام بذلك ، قمنا بتطبيق علامة التعبئة ، التي لجأنا إليها بالفعل عند نسخ الصيغة في العمود "الوزن المحدد" . في هذه الحالة ، لا يكون السطر "الإجمالي" ضروريًا لالتقاط ، لأن النتيجة المتراكمة في 100٪ سيتم عرضها على المنتج الأخير من القائمة. كما ترون ، تم ملء جميع عناصر العمود الخاص بنا بعد ذلك.
11. بعد ذلك ، أنشئ عمود "المجموعة" . سنحتاج إلى تجميع السلع حسب الفئات ( أ) و ( ب) و ( ج) وفقاً للحصة المتراكمة. كما نذكر ، يتم تجميع جميع العناصر وفقًا للمخطط التالي:
    • أ - تصل إلى 80 ٪ .
    • ب - 15٪ التالية ؛
    • ج - ما تبقى من 5 ٪ .

    وبالتالي ، فإننا نحدد الفئة ( أ) لجميع السلع ، والحصة المتراكمة من الوزن المحدد والتي تدخل الحدود إلى 80 ٪ . يتم تعيين السلع ذات الوزن النوعي المتراكم من 80٪ إلى 95٪ للفئة ب. المجموعة الباقية من البضائع التي تزيد قيمتها عن 95٪ من الوزن النوعي المتراكم يتم تعيينها للفئة جيم.

12. من أجل الوضوح ، يمكنك ملء هذه المجموعات بألوان مختلفة. لكن هذا في الإرادة.

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

الدرس: الفرز والتصفية في Excel

الطريقة 2: استخدام صيغة معقدة

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

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

   =ВЫБОР(Номер_индекса;Значение1;Значение2;…)

   مهمة هذه الوظيفة هي إخراج إحدى القيم المحددة ، اعتمادًا على رقم الفهرس. يمكن أن يصل عدد القيم إلى 254 ، ولكننا نحتاج إلى ثلاثة أسماء فقط تتوافق مع فئات تحليل ABC: A ، B ، C. يمكننا الدخول فوراً في الحقل "Value1" الرمز "A" ، في الحقل "Value2" - "B" ، في الحقل "Value3" - "C" .

5. ولكن مع حجة "الرقم القياسي" يجب أن تكون مفهومة تماما ، بعد أن بنيت في ذلك عدد قليل من المشغلين إضافية. اضبط المؤشر في حقل "رقم الفهرس" . بعد ذلك ، انقر فوق الرمز الذي يشبه المثلث ، على يسار الزر "Insert function" . يتم عرض قائمة بالعوامل المستخدمة حديثًا. نحن بحاجة إلى وظيفة MATCH . وبما أن القائمة لا ، فننقر على النقش "وظائف أخرى ..." .
6. يتم تشغيل معالج نافذة الوظائف مرة أخرى. مرة أخرى ، انتقل إلى الفئة "المراجع والمصفوفات" . نجد هناك موقف "SEARCH" ، حدده وانقر على زر "موافق" .
7. يفتح نافذة حجج OPERATOR . تركيبه كما يلي:

   =ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления)

   الغرض من هذه الوظيفة هو تحديد رقم الموضع للعنصر المحدد. وهذا هو ، ما نحتاجه لحقل "رقم الفهرس" للدالة SELECT .

   في الحقل "مجموعة ممسوحة ضوئيًا" ، يمكنك على الفور تعيين التعبير التالي:

   {0:0,8:0,95}

   يجب أن يكون في أقواس معقوفة ، مثل صيغة مصفوفة. ليس من الصعب تخمين أن هذه الأرقام ( 0 ، 0 ، 8 ؛ 0،95 ) تدل على حدود الحصة المتراكمة بين المجموعات.

   يعد حقل "نوع المطابقة" اختياريًا وفي هذه الحالة لن نقوم بملئه.

   في الحقل "قيمة البحث" ، نقوم بتعيين المؤشر. ثم مرة أخرى من خلال المثلث المصورة أعلاه ، ننتقل إلى معالج الدالة .

8. هذه المرة في معالج الدالة ننتقل إلى الفئة "رياضيات" . نختار الاسم "SUMMER" ونضغط على زر "OK" .
9. يتم تشغيل نافذة الوسائط الخاصة بوظيفة SUMIFER . يلخص المشغل المحدد الخلايا التي تتوافق مع حالة معينة. بناء الجملة هو:

   =СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)

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

   في حقل "المعايير" ، نحتاج إلى تحديد شرط. نحن ندخل التعبير التالي:

   ">"&

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

   بعد ذلك ، لا تنقر على زر "موافق" ، ولكن انقر على اسم الدالة MATCH في شريط الصيغة.

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

    بعد ذلك ، نأخذ محتويات الحقل بالكامل "القيمة المطلوبة" بين قوسين ، وبعد ذلك نضع علامة القسمة ( "/" ). ثم مرة أخرى من خلال رمز المثلث اذهب إلى نافذة اختيار الوظيفة.

11. كما هو الحال في المرة الأخيرة في معالج المهام التي تم إطلاقها ، فإننا نبحث عن المشغل المطلوب في الفئة "رياضيات" . هذه المرة تسمى الوظيفة المطلوبة "SUMM" . حدده وانقر على زر "موافق" .
12. يفتح نافذة الحجج لمشغل SUM . هدفه الرئيسي هو تلخيص البيانات في الخلايا. إن بنية هذا المشغل بسيطة للغاية:

    =СУММ(Число1;Число2;…)

    لأغراضنا فقط ، حقل "Number1" مطلوب . أدخل إحداثيات نطاق العمود "الإيرادات" ، باستثناء الخلية التي تحتوي على الإجماليات. لقد أجرينا بالفعل مثل هذه العملية في حقل "المدى" في دالة SUMMER . وكذلك في ذلك الوقت ، تكون إحداثيات النطاق مطلقة ، وتحديدها ، والضغط على المفتاح F4 .

    بعد ذلك ، انقر على زر "موافق" في أسفل النافذة.

13. كما ترى ، قامت مجموعة وظائف الإدخال بحساب وإخراج النتيجة إلى الخلية الأولى لعمود "المجموعة" . تم تعيين المنتج الأول لمجموعة "A" . الصيغة الكاملة التي قدمناها لهذا الحساب هي كما يلي:

    =ВЫБОР(ПОИСКПОЗ((СУММЕСЛИ($B$2:$B$27;">"&$B2)+$B2)/СУММ($B$2:$B$27);{0:0,8:0,95});"A";"B";"C")

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

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

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

الدرس: معالج الدالة في Excel

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

• ← باستخدام الدالة SELECT في Microsoft Excel
• تعيين يوم من الأسبوع حسب التاريخ في Microsoft Excel →