كيفية إنشاء مخطط أعمدة / شريط مكدس ومجمّع في Excel؟

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

إنشاء مخطط أعمدة مكدس ومجمّع في Excel

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

✅ ما تحتاج لمعرفته أولاً:

• لا يقدم Excel نوع "مخطط الأعمدة المكدسة والمجمّعة" مدمجًا. يتم إنشاء النتيجة من خلال الحيل في ترتيب البيانات.
• يجب إعادة هيكلة بيانات المصدر لمحاكاة تجميع المجموعات.
• تُضاف صفوف فارغة بين مجموعات الفئات لفصل كل مجموعة بصريًا.

لنستعرض العملية خطوة بخطوة باستخدام بيانات مبيعات عينة للمنتجات على مدار عدة فصول.

1. تنظيم بياناتك الخام: في هذا المثال، لدينا أسماء المنتجات في العمود A وبيانات المبيعات (مثل Actual vs Target للفصل الأول والثاني) في الأعمدة المجاورة. الهدف هو تجميع بيانات كل منتج جنبًا إلى جنب وعرض القيم الفعلية/المستهدفة مكدسة داخل كل مجموعة.

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

3. إنشاء المخطط: حدد بياناتك المنظمة حديثًا. ثم انتقل إلى إدراج > عمود أو مخطط شريطي > عمود مكدس.

4. تنسيق السلسلة: انقر بزر الماوس الأيمن على أي عمود في المخطط، وحدد تنسيق سلسلة البيانات.

5. تقليل عرض الفجوة: في جزء تنسيق سلسلة البيانات، انتقل إلى خيارات السلسلة، وقم بتعيين عرض الفجوة = 0% لضغط كل مجموعة بصريًا في مجموعة مكدسة واحدة.

6. تعديل واجهة العرض والموقع: انقر بزر الماوس الأيمن على واجهة العرض > تنسيق واجهة العرض.

7. تحديد موقع واجهة العرض: ضمن جزء تنسيق واجهة العرض، تحت خيارات الواجهة، حدد موقع الواجهة المفضل (يمين، أعلى، يسار، أو أسفل) لتناسب تخطيط المخطط الخاص بك وتجنب التداخل مع البيانات.

✅ النتيجة: لديك الآن مخطط أعمدة مكدس ومجمّع حيث يتم تجميع بيانات كل منتج فعلية/مستهدفة ومكدسة جنبًا إلى جنب للمقارنة السريعة.

⚠️ القيد: تعمل هذه التقنية بشكل جيد مع مجموعات البيانات الصغيرة. لكن بالنسبة لمجموعات البيانات الكبيرة أو البيانات التي تتغير بشكل متكرر، يمكن أن تكون إعادة الهيكلة يدويًا عرضة للأخطاء. راجع الأقسام التالية لحلول VBA والصيغ لأتمتة هذه العملية.

كود VBA – أتمتة إعادة هيكلة البيانات وإنشاء المخطط

عند التعامل مع مجموعات بيانات كبيرة أو التقارير التي تتغير بشكل متكرر، يمكن أن يكون إعادة تنظيم البيانات يدويًا لإنشاء مخططات الأعمدة المكدسة والمجمعة أمرًا مملًا وعرضة للأخطاء. باستخدام VBA (Visual Basic for Applications)، يمكنك أتمتة العملية بالكامل - من إعادة هيكلة جدول المصدر إلى إدراج المخطط - مما يوفر لك الوقت والجهد.

هذه الطريقة هي الأنسب للمستخدمين المألوفين ببرامح Excel أو البيئات التي يُسمح فيها باستخدام VBA. فهي فعالة بشكل خاص إذا كان هيكل المخطط الخاص بك يبقى كما هو ولكن البيانات تُحدث بانتظام. إليك كيفية تنفيذه:

الخطوة 1: اضغط على Alt + F11 لفتح محرر VBA. في المحرر، انقر فوق إدراج > وحدة.

الخطوة 2: انسخ الكود التالي لـ VBA في نافذة الوحدة:

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

الخطوة 3: اضغط على Alt + F8 لفتح مربع الحوار Macro. حدد CreateStackedClusteredChart وانقر فوق تشغيل.

الخطوة 4: عند المطالبة، حدد مجموعة بياناتك الأصلية (مع الرؤوس). ستقوم الماكرو بإنشاء ورقة عمل جديدة مع إدخال صفوف فارغة وإنشاء مخطط الأعمدة المكدسة والمجمّعة تلقائيًا.

📝 نصائح:

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

✅ المزايا: يوفر الوقت، التخطيط الدقيق، مثالي للتقارير المتكررة.
⚠️ العيوب: يتطلب Excel ممكّن الماكرو ومعرفة أساسية بـ VBA.

صيغة Excel – إعادة هيكلة البيانات ديناميكيًا لمخططات المكدس والمجمّع

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

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

إليك مثال على كيفية إعداد هذا:

• لنفترض أن بياناتك الأصلية في A1:D7 (مع A1 كرأس في الزاوية اليسرى العلوية)، منظمة كمنطقة/فئة في العمود A وقيم الفرعية (مثل Q1، Q2، Q3) في الأعمدة B، C، D.
• تريد عرض كل فئة كمجموعة مع القيم Q المكدسة، باستخدام صفوف فارغة لفصل المجموعات.

1. في ورقة العمل الجديدة أو المنطقة المجاورة، قم بإنشاء هيكل مساعد لاستخراج كل مجموعة وإدراج صفوف فارغة. على سبيل المثال، لنسخ الصف الأول من البيانات إلى E2:G2:

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

اسحب هذه الصيغة لأسفل حسب الحاجة. لإدراج صفوف فارغة بين المجموعات، قم بإعداد صيغة IF لإرجاع فارغ ("") على الأسطر البديلة:

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

استخدم مزيجًا من هذه الصيغ مع المراجع ذات الهيكل الجيد لعرض بياناتك المعاد هيكلتها، بما في ذلك صفوف فارغة بفاصل زمني منتظم.

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

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

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

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

مقالات الرسم البياني الأكثر ارتباطًا:

---

أفضل أدوات إنتاجية لمكتب العمل

Kutools لـ Excel يحل معظم مشاكلك ويزيد من إنتاجيتك بنسبة 80%

• شريط الصيغة الفائق (تحرير سهل للنصوص والصيغ المتعددة الأسطر); تخطيط القراءة (قراءة وتحرير سهل للخلايا الكبيرة العدد); لصق إلى النطاق المُرشح...
• دمج الخلايا/الصفوف/الأعمدة مع الاحتفاظ بالبيانات; تقسيم محتوى الخلايا; دمج الصفوف المكررة والمجموع/المتوسط... منع الخلايا المكررة; مقارنة النطاقات...
• تحديد الصفوف المكررة أو الفريدة; تحديد الصفوف الفارغة (جميع الخلايا فارغة); البحث المتقدم والبحث الضبابي في العديد من المصنفات; اختيار عشوائي...
• نسخ دقيق للخلايا المتعددة دون تغيير مرجع الصيغة; إنشاء تلقائي للمراجع إلى أوراق متعددة; إدراج رموز تعداد نقطي، مربعات اختيار وأكثر...
• تفضيل وإدراج سريع للصيغ، النطاقات، الرسوم البيانية والصور; تشفير الخلايا بكلمة مرور; إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني...
• استخراج النص، إضافة نص، إزالة الأحرف في موضع محدد، إزالة المسافات; إنشاء وطباعة إحصاءات صفحة البيانات; التحويل بين محتويات الخلايا والتعليقات...
• مرشح متقدم (حفظ وتطبيق مخططات التصفية على أوراق أخرى); ترتيب متقدم حسب الشهر/الأسبوع/اليوم، التكرار وأكثر; تصفية خاصة حسب العريض، المائل...
• تجميع المصنفات وأوراق العمل; دمج الجداول بناءً على الأعمدة الرئيسية; تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls، xlsx وPDF...
• تجميع جدول محوري حسب رقم الأسبوع، يوم الأسبوع وأكثر... عرض الخلايا غير المؤمنة، المؤمنة بألوان مختلفة; تمييز الخلايا التي تحتوي على صيغة/اسم...

اقرأ المزيد... تجربة مجانية لمدة 30 يومًا بكامل الميزات... شراء... 

---

Office Tab - يجلب واجهة التبويب إلى Office، ويجعل عملك أسهل بكثير

• تمكين التحرير والقراءة بتبويب في Word، Excel، PowerPoint، Publisher، Access، Visio وProject.
• فتح وإنشاء مستندات متعددة في تبويبات جديدة لنفس النافذة، بدلاً من النوافذ الجديدة.
• يزيد من إنتاجيتك بنسبة 50%، ويقلل من مئات النقرات بالماوس لك كل يوم!

اقرأ المزيد... تجربة مجانية لمدة 30 يومًا بكامل الميزات... شراء...