كيفية دمج البيانات Consolidate من مصنفات متعددة في مصنّف واحد في Microsoft Excel


Huda AlMashta

لقد غطينا في دروس سابقة كيفية تلخيص البيانات باستخدام المجاميع الفرعية، والجداول المحورية. وسنتعلّم اليوم كيفية تلخيص البيانات باستخدام خاصية الدمج.

excel-consolidate.png

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

يمكن دمج البيانات بطريقتين رئيسيتين:

  • حسب الموضع
  • حسب الفئة

وسنشرح كيفية الدمج بالطريقتين في هذا الدرس.

دمج البيانات حسب الموضع

تُستخدم هذه الطريقة إذا كانت البيانات الموزّعة على مصنفات متعددة (أو أوراق متعددة) لها نفس الترتيب ونفس أسماء الأعمدة والصفوف في جميع المصنفات أو أوراق العمل.

مثال: لدينا هنا 4 مصنفات منفصلة، ويحتوي كل مصنف على المبيعات الخاصة بكل ربع. وكما نلاحظ، البيانات في جميع المصنفات لها نفس الترتيب ونفس عناوين الأعمدة والصفوف. سنقوم باستخدام خاصية الدمج لإيجاد مجموع المبيعات السنوي في مصنّف منفصل (لاحظ أنّ جميع الجداول تبدأ بالخلية C5 وتنتهي بالخلية F11):

1-دمج المصنفات.png

أولًا: نفتح جميع المصنفات الأربعة بالإضافة إلى مصنف خامس جديد (نسميّه الملخص) لإيجاد مجموع المبيعات السنوي. في المصنف الجديد، نقوم بإنشاء جدول بنفس البنية، يحتوي على نفس عدد الصفوف والأعمدة ونفس عناوينها.

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

2-مجموع المبيعات.png

ما سنقوم به هو جمع عدد الوحدات المباعة للربع الأول + الثاني+ الثالث+ الرابع، لكل منتج ومن ثم نجد على ضوئها المبيعات لكل منتج، ثم مجموع المبيعات الكلي.

ثانيًا: نحدد نطاق الخلايا الذي نريد عرض البيانات المدمجة فيه، وهو عمود "عدد الوحدات المباعة" في مصنّف الملخّص (E6:E11). من جديد أكرر، يجب أن تكون البيانات التي نريد دمجها موجودة في نفس نطاق الخلايا في مصنفات المصدر:

3-تحديد البيانات.png

ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate:

4-دمج.png

من مربّع الحوار Consolidate، نحدد الدالة التي نريد استخدامها. هناك العديد من الخيارات، لكن ما يهمنا هنا هو دالة الجمع SUM لأننا نريد إيجاد مجموع "عدد الوحدات المباعة" في كل الأرباع:

5-دالة الجمع.png

بعد ذلك، ننقر على زر السهم الصغير بجانب حقل مرجع Reference ثم نذهب إلى المصنف الأول (مبيعات الربع الأول) ونحدد نطاق الخلايا E6:E11 فيه:

6-تحديد المرجع.png

لاحظ الصيغة في حقل المرجع؛ يشير اكسل أولًا إلى اسم المصنّف، ثم اسم ورقة العمل، ثم نطاق الخلايا.

انقر على زر Add لإضافة البيانات من المصنّف الثاني (مبيعات الربع الثاني)، وكرر نفس الخطوات على المصنفين الآخرين، وكما موضّح في الصورة أدناه:

7-دمج البيانات-4 مصنفات.gif

نتأكّد من تأشير مرّبع الخيار Create links to source data لإنشاء ارتباط مع مصدر البيانات، وهذا لأنّ بياناتنا موجودة على مصنفات مختلفة. أي لا حاجة لتأشير الخيار إذا كنت تريد دمج البيانات من أوراق عمل مختلفة في ورقة على نفس المصنّف.

ننقر على OK، وسيقوم البرنامج بجمع كافة بيانات "عدد الوحدات المباعة" عبر جميع المصنفات. وإذا أردنا معرفة البيانات التفصيلية لكل رقم دون الرجوع إلى كل مصنّف، يوفّر لنا اكسل على مخطط تفصيلي على جانب المصنّف يمكننا استخدامه لإظهار (+) أو إخفاء البيانات (-) التي تم دمجها لنحصل على الرقم النهائي في كل خلية. وعند النقر على الرقم سيظهر في شريط الصيغة امتداد يمكننا الاستدلال به إلى اسم المصنف المصدر واسم ورقة العمل:

8-مخطط تفصيلي.png

دمج البيانات حسب الفئة

تُستخدم هذه الطريقة إذا كانت البيانات التي تريد دمجها غير مرتبة بنفس الترتيب عبر جميع المصنفات أو أوراق العمل كأن يكون عدد عناصر الجدول أو ترتيبها مختلفًا. مع ذلك يجب أن يكون لها نفس أسماء الصفوف والأعمدة.

مثال: لدينا هذه المرة 4 أوراق عمل (ويمكن أن تكون مصنفات أيضًا) تحتوي على 4 جداول. لكن كل جدول يحتوي على عدد/ترتيب مختلف من الفئات (المنتجات)، وقد توجد نفس الفئة مكررة في أكثر من جدول مع اختلاف بياناتها. ما نريده هو إيجاد مجموع المبيعات لكل منتج (أي لكل فئة) في ورقة عمل تمثّل الملخص.

9-دمج البيانات حسب الفئة.png

أولًا: نقوم بإنشاء ورقة عمل جديدة، ثم ننشئ جدول يبدأ من نفس الصف ونفس العمود لبقية الجداول في أوراق العمل المصدر (أي الخلية C5 في مثالنا):

10-ورقة عمل-جدول بيانات جديد.png

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

11-تحديد نطاق الجدول.png

ثالثًا: نذهب إلى تبويب بيانات Data وننقر على أمر دمج Consolidate. سيظهر لنا نفس مربع الحوار الذي ظهر في الطريقة السابقة. نحدد الدالة التي نريد استخدامها من حقل Function ثم ننقر على زر السهم الصغير في حقل Reference:

12-تحديد الدالة.png

نذهب إلى الورقة الأولى ونحدد نطاق البيانات الأول (C6:F11 في مثالنا)، ثم الورقة الثانية ونحدد نطاق البيانات الثاني (C6:F11 في مثالنا) وهكذا:

13-دمج البيانات حسب الفئة.gif

بعد ذلك نحدد أين توجد الفئات التي نريد دمجها سواء كانت في الصف العلوي Top Raw أو في العمود الأيمن Right Columnأو في كليهما. سنحدد العمود الأيمن لأنّ المنتجات تقع في العمود الأيمن في هذا المثال ثم ننقر على OK:

15-دمج حسب الفئة-مثال.png

سيتم إيجاد مجموع المبيعات لكل منتج في كل الأرباع حتّى إن كانت مواقعها مختلفة عبر أوراق العمل.



1 شخص أعجب بهذا


تفاعل الأعضاء


لا توجد أيّة تعليقات بعد



يجب أن تكون عضوًا لدينا لتتمكّن من التعليق

انشاء حساب جديد

يستغرق التسجيل بضع ثوان فقط


سجّل حسابًا جديدًا

تسجيل الدخول

تملك حسابا مسجّلا بالفعل؟


سجّل دخولك الآن