بحث هذه المدونة الإلكترونية

الأحد، 2 أبريل، 2017

اضافة خاصية الاجمالي الفرعي Subtotal

اضافة خاصية الاجمالي الفرعي Subtotal




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

كيفية اضافة خاصية الاجمالي الفرعي Subtotal:

أولاً عليك أن تحدد العمود (أو الاعمدة التي تريد تجميع بياناتك حسبها). يُفضّل أن تجعل العمود الذي تريد التجميع حسبه أولاً من جهة اليسار للنسخة الإنكليزية من البرنامج (اليمين للنسخة العربية) ثم تتبعه بعمود الشرط الثاني ثم الثالث… وذلك في حال اردت التجميع وفقاً لشرطين أو اكثر. لاحظ أنني قلت أن هذا مفضل وليس إلزامياً أي أن اكسل سيقبل التجميع حسب اي عمود بغض النظر عن ترتيبه ولكن جعل اعمدة الشروط أولاً يساعد على تسهيل قراءة وتنسيق التقرير,
في المثال التالي لدي مجموعة بيانات زيارات مرضى وفيها جنس المريض, العمر, الاسم, مدة الزيارة وبدل الزيارة. اريد تجيمع بيانات هذا الجدول حسب جنس المريض ومن ثم تجميع بيانات كل جنس حسب العمر. لاحظ أنني جعلت عمود الشرط الأول ترتيبه الأول والشرط الثاني ترتيبه الثاني.
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

الخطوة الثانية هي الزامية وليست اختيارية, وتتمثل بفرز Sort الجدول كاملاً حسب عمود الشرط الأول وعمود  الشرط الثاني معاً (وليس الفرز حسب الشرط الاول ثم الفرز حسب الشرط الثاني). لتنفيذ ذلك, حدد الجدول كاملاً, اذهب الى تبويب بيانات – Data واختر فرز – Sort  (او بالضغط على اختصار لوحة المفاتيح Ctrl+D ثم S),

الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

يمكن لك تجاوز خطوة تحديد كامل الجدول, حدد اي خلية ضمن الجدول ثم اذهب مباشرةً الى ايقونة الفرز – Sort لان اكسل سيقوم بتحديد كامل الجدول عنك.

الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

الخطوات كالتالي,
  1. تأكد من تفعيل هذا الخيار لتخبر اكسل ان جدولك يحوي في سطره الأول عناوين الأعمدة Headers.
  2. اختر عنوان عمود الشرط الأول بواسطة عنوانه
  3. اختر الخيار قيمة Values ليتم الفرز حسب قيم العمود
  4. حدد طريقة الفرز, تصاعدياً أو تنازلياً (حسب حاجتك)
  5. اضغط على زر اضافة مستوى جديد Add Level وذلك ليتم اضافة مستوى فرز ثاني
  6. كرر الخطوات 2 و 3 و 4 اعلاه حسب متطلبات الشرط الثاني ثم الخطوة 9 للتنفيذ.
النتيجة يجب أن تكون كالتالي,
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

لاحظ أن الجدول تم فرزه (ترتيبه) حسب العمود الأول فأسطر الاناث اصبحت أولاً ثم اسطر الذكور, ثم ضمن كل مجموعة, تم فرز (ترتيب) البيانات حسب العمر.
الآن, حدد جدول البيانات مجدداً, اذهب الى التبويب بيانات – Data واختر ايقونة الاجمالي الفرعي – Subtotal,
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

مجدداً, يمكن لك تجاوز خطوة تحديد كامل الجدول, حدد اي خلية ضمن الجدول ثم اذهب مباشرةً الى ايقونة الاجمالي الفرعي Subtotal لان اكسل سيقوم بتحديد كامل الجدول عنك.

اجعل اعداداتك كالتالي,
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

  1. نختار من هذه القائمة عمود الشرط الأول.
  2. يتيح لنا اكسل تنفيذ عمليات حسابية بسيطة عند تجميع بياناتنا, هذه العمليات يتم تطبيقها على عمود أو اكثر حسب اختيارك
    • معادلة مجموع Sum لاعطاء المجموع لقيم رقمية
    • معادلة عدد Count لاعطاء عدد القيم
    • معادلة الحد الأقصى Max لاعطاء اعلى قيمة
    • معادلة الحد الأدنى Min لاعطاء ادنى قيمة
    • معادلة حاصل الضرب Product لاعطاء حاصل ضرب جميع القيم بعضها ببعض
  3. في حال اردت تطبيق المعادلة المحددة اعلاه على عمود أو اكثر, حدده هنا
  4. ابق الخيارات الثلاثة كما هي دون تغيير ثم نفذ
فهم آلية عمل اكسل يفيد المستخدم جداً لتوقع النتيجة قبل التنفيذ مما يؤدي الى تجنب اضاعة الوقت بخطوات لاداعي لها وتجنب اخطاء التطبيق والتي تنشأ بأغلب الأوقات بسبب نسيان تنفيذ خطوة ما أو عدم تنفيذها بشكل صحيح. يمكن لك التفكير بآلية عمل الاجمالي الفرعي Subtotal كالتالي, اكسل سيقرأ قيمة أول خلية من الاعلى في عمود الشرط الذي اضفته ويحتفظ بها في ذاكرته ثم يقرأ قيمة الخلية التي تليها في الجدول فإن كانت مطابقة للقيمة المحفوظة, استمر في قراءة الخلية التالية حتى يجد قيمة لا تطابق مافي ذاكرته, حينها سيقوم بتجميع جميع الأسطر التي تم قراءتها منذ البداية وحتى القيمة الجديدة ضمن مجموعة واحدة مع تطبيق المعادلة التي طلبتها منه قبل الانتقال للبحث عن المجموعة التالية بذات الطريقة. هذا يؤدي بنا إلى أن شرط تنفيذ التجميع هو تغيّر القيم في عمود الشرط. أي انه لو كانت اول ثلاثة قيم متاشبهة وليكن الرقم واحد, ثم رقم مغاير وليكن 2 ومن ثم مجدداً يتكرر الرقم واحد, اكسل سيقوم بتجميع أول ثلاثة ارقام متساوية متجاورة كمجموعة اولى, ثم السطر التالي المحتوي على الرقم 2 كمجموعة ثانية ومن ثم يقوم بتجميع التالي من الاسطر ذات الرقم واحد كمجموعة ثالثة مستقلة عن المجموعة الأولى والثانية وهذا هو سبب انني اشرت الى ان فرز الجدول تصاعدياً او تنازلياً يعتبر الزامياً ومهماً جداً للحصول على خاصية الاجمالي الفرعي بشكل صحيح 

النتيجة ستكون كالتالي, قام اكسل بتجميع الأسطر التي تحوي نوع الجنس انثى كمجموعة واحدة واضاف لنا مجموع عمود المبالغ لكل هذه الأسطر (المجموع 2,106) ومن ثم قام بنفس العملية للأسطر التي تحوي نوع الجنس ذكر.
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

هل يمكننا تطبيق الاجمالي الفرعي Subtotal اكثر من مرة (باستخدام اكثر من شرط) على نفس الجدول؟

قمت بتجميع عرض بياناتي حسب نوع جنس المريض بشكل صحيح, الآن, هل يمكنني عمل مجموعات فرعية اخرى ضمن مجموعة الإناث Female وكذلك ضمن مجموعة الذكور Male؟ مثلاً اريد ان اعرف مجموعات اعمار المرضى من كل جنس, المدينة, التشخيص …. الجواب هو نعم, يمكنك ذلك, ببساطة, حدد الجدول وكرر الخطوات التي اتبعتها اول مرة مع اي شرط ثاني, انا سأقوم بتنفيذ التجيمع الثاني باعتبار شرط العمر مع استخدام معادلة المتوسط الحسابي Average على قيم مدة الزيارة (أي انني اريد ان ارى متوسط مدة الزيارة لكل فئة عمرية للاناث والذكور كل على حدة),
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

لاحظ التالي, في الخطوة الرابعة وفي حال كنت تنفذ هذا التجميع لثاني او ثالث مستوى … يجب عليك التّأكد من عدم تحديد اول خيار “Replace current subtotals” أو “استبدال الاجماليات الفرعية الحالية” حيث أنها ستقوم بحذف التجميعات القديمة واحلال الجديدة محلها وهو مالا تريده طبعاً.
بالمتابعة, نلاحظ أن اكسل قد اضاف مستوى تجميع رابع كما انه اضافة قيم المتوسطات لتجميعات اعمار المرضى لكل جنس على حدة كما أردنا.
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

وبالتالي, النقر على مستوى العرض 4 في مثالنا, سيظهر كامل الجدول, اما الرقم واحد فسيظهر فقط سطرين, مجموع مبالغ فئة الإناث ومجموع مبالغ فئة الذكور (مع متوسط اعمار الجميع وقيمة المبالغ للجميع كأرقام نهائية اسفل الجدول)
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

كل المطلوب منك الآن هو تنسيق جدولك بما يناسب استخداماتك.

طريقة حذف الاجمالي الفرعي Subtotal:

في حال اردت حذف التجميعات ايّاً كان السبب, كل المطلوب هو تحديد الجدول, اذهب الى ذات ايقونة اضافة الاجمالي الفرعي Subtotal كما لو كنت تضيف شرط تجميع جديد مع الفرق انك ستضغط هنا على زر إزالة الكل – Remove All ليزيل اكسل كافة شروط التجميع ويعيد الجدول كحالته أول مرة.
الاجمالي الفرعي Subtotal
الاجمالي الفرعي Subtotal

المصدر
viaexcel
إرسال تعليق