كيف تُنشئ عمودًا أو رسمًا بيانيًّا شريطيًّا مجمَّعًا ومكدَّسًا في Excel؟

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

---

إنشاء رسم بياني عمودي متجمع مكدّس في Excel

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

✅ ما يجب أن تعرفه أولًا:

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

دعنا نستعرض العملية خطوة بخطوة باستخدام بيانات مبيعات نموذجية لمنتجات عبر أرباع متعددة.

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

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 لفتح مربع حوار الماكرو. اخترCreateStackedClusteredChart، ثم انقرتشغيل.

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

📝 نصائح:

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

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

---

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

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

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

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

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

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. بعد اكتمال نطاق التحويل الخاص بك (بما في ذلك المكدسات والتجميعات)، حدد هذا النطاق الجديد وأنشئ مخططك العمودي المكدس بالطريقة الأصلية المذكورة سابقًا ()Insert>Stacked Column). وسيُظهر المخطط تلقائيًّا أي تغييرات تُجريها على جدول البيانات الأصلي.

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

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

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

---

مقالات أخرى ذات صلة بالمخططات: