إجمالي التشغيل (المعروف أيضًا باسمالمبلغ التراكمي) شائع الاستخدام في كثير من الحالات.هذا مقياس يخبرك بمجموع القيم حتى الآن.
على سبيل المثال ، إذا كانت لديك بيانات مبيعات شهرية ، فسيخبرك الإجمالي الحالي بعدد المبيعات التي تم إجراؤها من اليوم الأول من الشهر إلى تاريخ محدد.
هناك حالات أخرى غالبًا ما تستخدم فيها الإجماليات ، مثل حساب الأرصدة النقدية في البيانات المصرفية / دفاتر الأستاذ ، وحساب السعرات الحرارية في خطط الوجبات ، وما إلى ذلك.
في Microsoft Excel ، توجد عدة طرق مختلفة لحساب الإجماليات الجارية.
تعتمد الطريقة التي تختارها أيضًا على بنية البيانات.
على سبيل المثال ، إذا كانت لديك بيانات جدولية بسيطة ، فيمكنك استخدام صيغة SUM بسيطة ، ولكن إذا كان لديك جدول Excel ، فمن الأفضل استخدام المراجع المنظمة.يمكنك أيضًا استخدام Power Query للقيام بذلك.
في هذا البرنامج التعليمي ، سأغطيهم جميعًااحسب المجموع الحالي في Excelطرق مختلفة.
اذا هيا بنا نبدأ!
المحتويات
حساب الإجماليات الجارية باستخدام البيانات المجدولة
إذا كانت لديك بيانات مجدولة (أي جداول في Excel لم يتم تحويلها إلى جداول Excel) ، فيمكنك استخدام بعض الصيغ البسيطة لحساب الإجماليات الجارية.
استخدم عامل الإضافة
افترض أن لديك بيانات مبيعات حسب التاريخ وتريد ذلك在 في العمود جعملية حسابيةمجموع تشغيل.
فيما يلي خطوات القيام بذلك.
الخطوة 1 - في الخلية C2 ، الخلية الأولى التي تريد تشغيل الإجمالي لها ، أدخل
= B2
هذا يحصل فقط على نفس قيمة المبيعات في الخلية B2.
الخطوة الخامسة- في الخلية C3 ، أدخل الصيغة التالية:
= ج2 + ب3
الخطوة الخامسة- تطبيق الصيغة على العمود بأكمله.يمكنك استخدام مقبض التعبئة لتحديده وسحبه ، أو ببساطة نسخ الخلية C3 ولصقها إلى جميع الخلايا المتبقية (سيؤدي ذلك إلى ضبط المرجع تلقائيًا وإعطاء النتيجة الصحيحة).
هذا سوف يعطيك النتيجة المبينة أدناه.
هذه طريقة بسيطة للغاية تعمل بشكل جيد في معظم الحالات.
المنطق بسيط - كل خلية تأخذ القيمة عليها (وهو المجموع التراكمي حتى اليوم السابق) ، وتضيف القيمة في الخلية المجاورة (وهي قيمة مبيعات اليوم الحالي).
هناك جانب سلبي واحد فقط - إذا قمت بحذف أي صف موجود في مجموعة البيانات هذه ، فستقوم جميع الخلايا أدناه بإرجاع خطأ مرجعي (#REF!)
إذا كانت مجموعة البيانات الخاصة بك تحتوي على هذا الاحتمال ، فاستخدم الطريقة التالية باستخدام صيغة SUM
استخدم SUM مع مراجع الخلايا المؤمنة جزئيًا
افترض أن لديك بيانات مبيعات حسب التاريخ ، وتريد حساب الإجمالي الحالي في العمود C.
فيما يلي صيغة SUM التي ستمنحك الإجمالي الحالي.
= SUM ($ B $ 2: B2)
اسمحوا لي أن أشرح كيف تعمل هذه الصيغة.
في صيغة SUM أعلاه ، استخدمت المرجع لإضافة $ B $ 2: B2
- $ B $ 2 - هذا مرجع مطلق ، مما يعني أن هذا المرجع لا يتغير عندما أنسخ نفس الصيغة في الخلية أدناه.لذلك عندما تنسخ الصيغة في الخلية أدناه ، ستتغير الصيغة إلى SUM ($ B $ 2: B3)
- B2 - هذا هو الجزء الثاني من المرجع ، إنه مرجع نسبي ، مما يعني أنه عندما أنسخ الصيغة لأسفل أو لليمين ، يتم ضبط هذا.لذلك عند نسخ الصيغة في الخلية أدناه ، تصبح القيمة B3
الشيء الجميل في هذا الأسلوب هو أنه إذا قمت بحذف أي صفوف في مجموعة البيانات ، فإن هذه الصيغة تعدل ولا تزال تمنحك إجمالي التشغيل الصحيح.
حساب مجموع التشغيل في ورقة Excel
عند العمل باستخدام البيانات الجدولية في Excel ، من الأفضل تحويلها إلى جدول Excel.فهو يسهل إدارة البيانات ، كما أنه يسهل استخدام أدوات مثل Power Query و Power Pivot.
يوفر استخدام جدول Excel العديد من الفوائد ، مثل المراجع المصنفة (التي تجعل من السهل جدًا الرجوع إلى البيانات في الجدول واستخدامها في الصيغ) ، والتعديل التلقائي للمراجع أثناء إضافة البيانات أو إزالتها من الجدول.
بينما لا يزال بإمكانك استخدام الصيغة أعلاه التي أظهرتها لك في ورقة Excel ، دعني أوضح لك بعض الطرق الأفضل للقيام بذلك.
افترض أن لديك جدول Excel مثل الجدول أدناه ، وتريد حساب الإجمالي الحالي في العمود C.
هذه هي الصيغة للقيام بذلك:
= SUM (SalesData [[# Headers]، [Sale]]: [@ Sale])
قد تبدو الصيغة أعلاه طويلة بعض الشيء ، لكن ليس عليك كتابتها بنفسك.ما تراه في صيغة التلخيص يسمى مرجعًا منظمًا ، وهي طريقة فعالة لبرنامج Excel للإشارة إلى نقاط بيانات محددة في جدول Excel.
على سبيل المثال ، تشير SalesData [[# Headers] ، [Sale]] إلى رأس المبيعات في جدول SalesData (SalesData هو اسم جدول Excel الذي قدمته عندما أنشأت الجدول)
بينما يشير [Sale] إلى القيمة الموجودة في خلية في نفس الصف من عمود البيع.
أنا فقط أشرح هذا هنا لفهمك ، ولكن حتى إذا كنت لا تعرف أي شيء عن المراجع المنظمة ، فلا يزال بإمكانك إنشاء هذه الصيغة بسهولة.
فيما يلي خطوات القيام بذلك:
- في الخلية C2 ، أدخل = SUM (
- حدد الخلية B1 ، وهي رأس العمود بقيمة البيع.يمكنك استخدام الماوس أو استخدام مفاتيح الأسهم.ستلاحظ أن Excel يقوم تلقائيًا بإدخال مرجع منظم إلى الخلية
- إضافة: (نقطتان)
- حدد الخلية B2.سيقوم Excel تلقائيًا بإدراج المراجع المنظمة للخلايا مرة أخرى
- أغلق القوس واضغط على Enter
ستلاحظ أيضًا أنه لا يتعين عليك نسخ عمود الصيغ بالكامل ، حيث تقوم ورقة Excel بذلك نيابةً عنك تلقائيًا.
فائدة أخرى لهذا الأسلوب هي أنه إذا أضفت سجلًا جديدًا إلى مجموعة البيانات هذه ، فإن ورقة Excel تحسب تلقائيًا الإجمالي الحالي لجميع السجلات الجديدة.
على الرغم من أننا قمنا بتضمين رؤوس الأعمدة في الصيغة ، ضع في اعتبارك أن الصيغة تتجاهل نص الرأس وتراعي البيانات الموجودة في العمود فقط
حساب الإجماليات الجارية باستخدام Power Query
Power Query هي أداة رائعة عندما يتعلق الأمر بالاتصال بقواعد البيانات واستخراج البيانات من مصادر متعددة وتحويلها قبل وضعها في Excel.
إذا كنت تستخدم Power Query بالفعل ، فمن الأفضل إضافة الإجماليات الجارية عند تحويل البيانات في Power Query Editor نفسه (بدلاً من الحصول أولاً على البيانات في Excel ثم إضافة الإجماليات الجارية باستخدام أي من الطرق المذكورة أعلاه).
على الرغم من عدم وجود وظيفة مضمنة في Power Query لإضافة الإجماليات الجارية (كنت أتمنى أن يكون هناك) ، فلا يزال بإمكانك القيام بذلك باستخدام صيغة بسيطة.
افترض أن لديك جدول Excel يبدو كالتالي ، وتريد إضافة الإجماليات الجارية إلى هذه البيانات:
فيما يلي خطوات القيام بذلك:
- حدد أي خلية في جدول Excel
- انقر فوق البيانات
- في علامة التبويب Get & Transform ، انقر فوق رمز جدول / نطاق.سيؤدي هذا إلى فتح الجدول في محرر Power Query
- [اختياري] إذا لم يكن عمود التاريخ مرتبًا بالفعل ، فانقر على أيقونة الفلتر في عمود التاريخ ، ثم انقر على فرز تصاعدي
- انقر فوق علامة التبويب إضافة عمود في محرر Power Query
- في المجموعة العامة ، انقر فوق القائمة المنسدلة عمود الفهرس (لا تنقر فوق أيقونة عمود الفهرس ، ولكن السهم الأسود المائل الصغير بجوارها لكشف المزيد من الخيارات)
- انقر فوق الخيار "من 1".سيؤدي القيام بذلك إلى إضافة عمود فهرس جديد يبدأ عند 1 وإدخال أرقام تتزايد بمقدار 1 في جميع أنحاء العمود
- انقر فوق رمز الأعمدة المخصصة (أيضًا في علامة التبويب إضافة أعمدة)
- في مربع حوار الأعمدة المخصصة الذي يفتح ، أدخل اسمًا للعمود الجديد.في هذا المثال سأستخدم الاسم "Running Total"
- في حقل صيغة العمود المخصص ، أدخل الصيغة التالية:List.Sum (List.Range (# "الفهرس المضاف" [بيع] ، 0 ، [فهرس]))
- تأكد من وجود مربع اختيار أسفل مربع الحوار نصه "لم يتم اكتشاف أخطاء في بناء الجملة"
- انقر فوق موافق.سيؤدي هذا إلى إضافة عمود إجمالي جديد قيد التشغيل
- عمود مؤشر الإسقاط
- انقر فوق علامة التبويب ملف ، ثم انقر فوق إغلاق وتحميل
ستعمل الخطوات المذكورة أعلاه على إدراج ورقة عمل جديدة في المصنف الخاص بك مع جدول بإجماليات التشغيل.
الآن ، إذا كنت تعتقد أن هذه خطوات كثيرة جدًا مقارنة بالنهج السابق باستخدام الصيغ البسيطة ، فأنت على حق.
إذا كان لديك بالفعل مجموعة بيانات وكل ما عليك فعله هو إضافة الإجماليات الجارية ، فمن الأفضل عدم استخدام Power Query.
يعتبر استخدام Power Query منطقيًا في المواقف التي يتعين عليك فيها استخراج البيانات من قاعدة بيانات أو دمج البيانات من العديد من المصنفات المختلفة وإضافة الإجماليات الجارية إليها أيضًا في العملية.
أيضًا ، بمجرد أتمتة ذلك باستخدام Power Query ، في المرة التالية التي تتغير فيها مجموعة البيانات ، لن تضطر إلى القيام بذلك مرة أخرى ، يمكنك فقط تحديث الاستعلام وسيمنحك نتائج بناءً على مجموعة البيانات الجديدة.
كيف يعمل هذا؟
الآن اسمحوا لي أن أشرح بسرعة ما يحدث مع هذا النهج.
أول شيء نقوم به في محرر Power Query هو إدراج عمود فهرس يبدأ من 1 ويزيد كلما انتقل إلى أسفل الخلية.
نقوم بذلك لأننا نحتاج إلى استخدام هذا العمود عند حساب الإجمالي الجاري في عمود آخر تم إدراجه في الخطوة التالية.
ثم نقوم بإدراج عمود مخصص واستخدام الصيغة التالية
List.Sum (List.Range (# "الفهرس المضاف" [بيع] ، 0 ، [فهرس]))
هذه صيغة List.Sum التي ستمنحك مجموع النطاق المحدد فيها.
يتم تحديد النطاق باستخدام وظيفة List.Range.
تأخذ الدالة List.Range كإخراج النطاق المحدد في عمود البيع ، ويختلف النطاق وفقًا لقيمة الفهرس.على سبيل المثال ، بالنسبة للسجل الأول ، يكون النطاق هو قيمة البيع الأولى.يتسع هذا النطاق كلما انتقلت إلى أسفل الخلية.
لذلك ، بالنسبة للخلية الأولى.List.Sum ستعطيك فقط مجموع قيمة البيع الأولى ، وبالنسبة للخلية الثانية ستعطيك مجموع قيمتي البيع الأوليين ، وهكذا.
بينما يعمل هذا الأسلوب بشكل جيد ، يمكن أن يصبح بطيئًا جدًا لمجموعات البيانات الكبيرة (آلاف الصفوف).
حساب الإجماليات الجارية على أساس المعايير
حتى الآن ، رأينا أمثلة لحساب الإجمالي الجاري لجميع القيم في عمود.
لكن في بعض الحالات ، قد ترغب في حساب الإجمالي الحالي لسجل معين.
على سبيل المثال ، لدي مجموعة بيانات أدناه حيث أريد حساب إجمالي الطابعات والماسحات الضوئية في عمودين مختلفين.
يمكن القيام بذلك باستخدام صيغة SUMIF ، التي تحسب الإجمالي الجاري مع ضمان استيفاء الشروط المحددة.
هذه هي الصيغة للقيام بذلك لعمود الطابعة:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
وبالمثل ، لحساب الإجمالي الجاري للماسح الضوئي ، استخدم الصيغة التالية:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
في الصيغة أعلاه ، استخدمت SUMIF الذي سيعطيني مبلغًا في نطاق عند استيفاء الشروط المحددة.
تأخذ الصيغة ثلاث معاملات:
- نطاق: هذا هو نطاق الشروط التي سيتم التحقق منها مقابل الشرط المحدد
- المعايير: هذا هو المعيار الذي سيتم التحقق منه فقط في حالة استيفاء هذا المعيار ، ثم القيمة في المعلمة الثالثة ، سيتم إضافة نطاق المجموع
- [مجموع_ترانج]: هذا هو نطاق مجموع القيم المراد إضافتها عند استيفاء الشرط
بالإضافة إلى ذلك ، فينطاقومجموع_راتبالمعلمة ، لقد أغلقت الجزء الثاني من المرجع بحيث يستمر النطاق في التوسع بينما نتحرك لأسفل الخلية.هذا يسمح لنا فقط بالنظر وإضافة القيم إلى هذا النطاق (ومن ثم الإجمالي الحالي).
في هذه الصيغة ، أستخدم أعمدة الرأس (الطابعات والماسحات الضوئية) كمعايير.يمكنك أيضًا ترميز المعيار إذا لم تكن عناوين الأعمدة الخاصة بك مطابقة تمامًا للنص القياسي.
إذا كنت بحاجة إلى التحقق من شروط متعددة ، يمكنك استخدام صيغة SUMIFS.
تشغيل الإجماليات في جدول محوري
إذا كنت تريد إضافة إجمالي حالي إلى نتائج PivotTable ، فيمكنك القيام بذلك بسهولة باستخدام الوظيفة المضمنة في PivotTables.
لنفترض أن لديك جدولاً محوريًا مثل الجدول أدناه مع تواريخ في عمود وقيم مبيعات في عمود آخر.
فيما يلي خطوات إضافة عمود إضافي يعرض المبيعات التراكمية حسب التاريخ:
- اسحب حقل البيع وقم بإفلاته في منطقة القيمة.
- سيؤدي هذا إلى إضافة عمود آخر بقيمة المبيعات
- انقر فوق خيار إجمالي المبيعات 2 في منطقة القيمة
- انقر فوق خيار إعدادات حقل القيمة
- في مربع الحوار إعدادات حقل القيمة ، قم بتغيير الاسم المخصص إلى الإجمالي الحالي
- انقر فوق علامة التبويب "عرض القيمة باسم"
- في القائمة المنسدلة عرض القيمة مثل ، حدد الخيار "التشغيل الإجمالي في"
- في خيارات الحقول الأساسية ، تأكد من تحديد التاريخ
- انقر فوق موافق
ستعمل الخطوات المذكورة أعلاه على تغيير عمود المبيعات الثاني إلى عمود "الإجمالي الحالي".
إذن ، هذه بعض الطرق التي يمكنك استخدامها لحساب الإجماليات الجارية في Excel.إذا كانت لديك بيانات مجدولة ، فيمكنك استخدام الصيغ البسيطة ، وإذا كانت لديك جداول Excel ، فيمكنك استخدام الصيغ التي تستخدم المراجع الهيكلية.
تناولت أيضًا كيفية حساب الإجماليات الجارية باستخدام Power Query و PivotTables.
أتمنى أن تكون قد وجدت هذا البرنامج التعليمي مفيدًا.
