المحتويات
الطريقة الثالثة. الصيغ
لنبدأ ، للتسخين ، بأبسط خيار - الصيغ. إذا كان لدينا جدول صغير مرتبة حسب التاريخ كإدخال ، ثم لحساب الإجمالي الحالي في عمود منفصل ، نحتاج إلى صيغة أولية:
الميزة الرئيسية هنا هي التثبيت الصعب للنطاق داخل دالة SUM - الإشارة إلى بداية النطاق تكون مطلقة (بعلامات الدولار) ، وإلى النهاية - نسبي (بدون دولارات). وفقًا لذلك ، عند نسخ الصيغة إلى العمود بأكمله ، نحصل على نطاق موسع ، نحسب مجموعه.
عيوب هذا النهج واضحة:
- يجب فرز الجدول حسب التاريخ.
- عند إضافة صفوف جديدة بالبيانات ، يجب تمديد الصيغة يدويًا.
الطريقة الرابعة. الجدول المحوري
هذه الطريقة أكثر تعقيدًا بعض الشيء ، لكنها أكثر إمتاعًا. وللتفاقم ، دعنا نفكر في مشكلة أكثر خطورة - جدول 2000 صف من البيانات ، حيث لا يوجد فرز حسب عمود التاريخ ، ولكن هناك تكرارات (على سبيل المثال ، يمكننا البيع عدة مرات في نفس اليوم):
نقوم بتحويل جدولنا الأصلي إلى اختصار لوحة مفاتيح "ذكي" (ديناميكي) CTRL+T أو فريق الصفحة الرئيسية - تنسيق كجدول (الصفحة الرئيسية - تنسيق كجدول)، ثم نقوم ببناء جدول محوري عليه باستخدام الأمر إدراج - PivotTable (إدراج - جدول محوري). نضع التاريخ في منطقة الصفوف في الملخص ، وعدد البضائع المباعة في منطقة القيم:
يرجى ملاحظة أنه إذا لم يكن لديك إصدار قديم من Excel ، فسيتم تجميع التواريخ تلقائيًا حسب السنوات والأرباع والأشهر. إذا كنت بحاجة إلى مجموعة مختلفة (أو لا تحتاجها على الإطلاق) ، فيمكنك إصلاحها عن طريق النقر بزر الماوس الأيمن على أي تاريخ وتحديد الأوامر المجموعة / فك التجميع (مجموعة / فك المجموعة).
إذا كنت تريد رؤية كل من الإجماليات الناتجة حسب الفترات والإجمالي الجاري في عمود منفصل ، فمن المنطقي إذن إلقاء الحقل في منطقة القيمة غير متاح مرة أخرى للحصول على نسخة مكررة من الحقل - سنقوم فيه بتشغيل عرض الإجماليات الجارية. للقيام بذلك ، انقر بزر الماوس الأيمن فوق الحقل وحدد الأمر حسابات إضافية - الإجمالي التراكمي (إظهار القيم كـ - الإجماليات الجارية):
هناك يمكنك أيضًا تحديد خيار زيادة الإجماليات كنسبة مئوية ، وفي النافذة التالية تحتاج إلى تحديد الحقل الذي سيذهب التراكم من أجله - في حالتنا ، هذا هو حقل التاريخ:
مزايا هذا النهج:
- تتم قراءة كمية كبيرة من البيانات بسرعة.
- لا يلزم إدخال الصيغ يدويًا.
- عند تغيير بيانات المصدر ، يكفي تحديث الملخص بزر الفأرة الأيمن أو باستخدام الأمر Data - Refresh All.
تنبع العيوب من حقيقة أن هذا ملخص ، مما يعني أنه لا يمكنك فعل ما تريد فيه (إدراج الأسطر ، كتابة الصيغ ، إنشاء أي رسوم بيانية ، وما إلى ذلك) لن يعمل بعد الآن.
الطريقة الثالثة: Power Query
لنقم بتحميل جدولنا "الذكي" ببيانات المصدر في محرر استعلام Power Query باستخدام الأمر البيانات - من جدول / نطاق (بيانات - من جدول / نطاق). بالمناسبة ، في أحدث إصدارات Excel ، تمت إعادة تسميته - الآن يطلق عليه بأوراق الشجر (من الورقة):
ثم نقوم بالخطوات التالية:
1. قم بفرز الجدول بترتيب تصاعدي حسب عمود التاريخ باستخدام الأمر فرز تصاعدي في قائمة التصفية المنسدلة في رأس الجدول.
2. بعد ذلك بقليل ، لحساب الإجمالي الجاري ، نحتاج إلى عمود إضافي برقم الصف الترتيبي. دعنا نضيفه مع الأمر إضافة عمود - عمود الفهرس - من 1 (إضافة عمود - عمود الفهرس - من 1).
3. أيضًا ، لحساب الإجمالي الحالي ، نحتاج إلى مرجع للعمود غير متاح حيث تكمن بياناتنا الملخصة. في Power Query ، تسمى الأعمدة أيضًا قوائم (قائمة) وللحصول على ارتباط لها ، انقر بزر الماوس الأيمن فوق رأس العمود وحدد الأمر تفصيل (أظهر تفاصيل). سيظهر التعبير الذي نحتاجه في شريط الصيغة ، الذي يتكون من اسم الخطوة السابقة # "تمت إضافة الفهرس"، حيث نأخذ الجدول واسم العمود [مبيعات] من هذا الجدول بين قوسين مربعين:
انسخ هذا التعبير إلى الحافظة لمزيد من الاستخدام.
4. حذف المزيد من الخطوة الأخيرة غير الضرورية غير متاح وأضف بدلاً من ذلك عمودًا محسوبًا لحساب الإجمالي الجاري باستخدام الأمر إضافة عمود - عمود مخصص (إضافة عمود - عمود مخصص). ستبدو الصيغة التي نحتاجها كما يلي:
هنا الوظيفة قائمة يأخذ القائمة الأصلية (العمود [مبيعات]) ويستخرج العناصر منه ، بدءًا من الأول (في الصيغة ، هذا هو 0 ، لأن الترقيم في Power Query يبدأ من الصفر). عدد العناصر المراد استردادها هو رقم الصف الذي نأخذه من العمود [فِهرِس]. لذا فإن هذه الوظيفة للصف الأول ترجع فقط خلية أولى واحدة من العمود غير متاح . بالنسبة للسطر الثاني - أول خليتين بالفعل ، للثالثة - الثلاثة الأولى ، إلخ.
حسنًا ، ثم الوظيفة قائمة جمع القيم المستخرجة ونحصل في كل صف على مجموع جميع العناصر السابقة ، أي المجموع التراكمي:
يبقى حذف عمود الفهرس الذي لم نعد بحاجة إليه وتحميل النتائج مرة أخرى إلى Excel باستخدام الأمر Home - Close & Load to.
حلت المشكلة.
سريع وغاضب
من حيث المبدأ ، كان من الممكن إيقاف هذا ، لكن هناك ذبابة صغيرة في المرهم - الطلب الذي أنشأناه يعمل بسرعة السلحفاة. على سبيل المثال ، على جهاز ليس أضعف جهاز كمبيوتر لدي ، تتم معالجة جدول 2000 صف فقط في 17 ثانية. ماذا لو كان هناك المزيد من البيانات؟
للإسراع ، يمكنك استخدام التخزين المؤقت باستخدام وظيفة List.Buffer الخاصة ، والتي تقوم بتحميل القائمة (القائمة) المعطاة لها كوسيطة في ذاكرة الوصول العشوائي ، مما يؤدي إلى تسريع الوصول إليها بشكل كبير في المستقبل. في حالتنا ، من المنطقي تخزين قائمة # "الفهرس المضاف" [مباع] ، والتي يجب على Power Query الوصول إليها عند حساب الإجمالي الجاري في كل صف من جدول 2000 صف.
للقيام بذلك ، في محرر Power Query في علامة التبويب الرئيسية ، انقر فوق الزر Advanced Editor (الصفحة الرئيسية - محرر متقدم) لفتح التعليمات البرمجية المصدر لاستعلامنا بلغة M المضمنة في Power Query:
ثم أضف خطًا به متغير هناك قائمتي، يتم إرجاع قيمته بواسطة وظيفة التخزين المؤقت ، وفي الخطوة التالية نستبدل استدعاء القائمة بهذا المتغير:
بعد إجراء هذه التغييرات ، سيصبح استعلامنا أسرع بشكل ملحوظ وسوف يتعامل مع جدول مكون من 2000 صف في 0.3 ثانية فقط!
شيء آخر ، أليس كذلك؟ 🙂
- مخطط باريتو (80/20) وكيفية بنائه في Excel
- البحث عن الكلمات الرئيسية في النص والاستعلام عن التخزين المؤقت في Power Query