كيف يمكن تحويل جدول على شكل مصفوفة إلى ثلاثة أعمدة في Excel؟
عند العمل مع البيانات في Excel، قد تصادف غالبًا جداول على شكل مصفوفة تعرض المعلومات في شبكة، حيث يُستخدم كلٌّ من الصفوف والأعمدة كعناوين. وعلى الرغم من أن هذا التنسيق بصريًا مفيد لبعض أنواع التحليل، فقد تحتاج إلى تحويل هذه المصفوفة إلى «قائمة» أو جدول مكوَّن من ثلاثة أعمدة — لأغراض مثل استيراد قواعد البيانات، أو توحيد البيانات، أو إنشاء المخططات، أو إجراء تحليل متقدم. ويتيح لك تحويل المصفوفة إلى جدول ثلاثي الأعمدة (المعروف أحيانًا باسم البيانات «غير المحورية») تصفيةً أسهل، وتجميعًا أكثر فعالية، وتكاملًا أيسر مع أدوات البيانات الأخرى. راجع المثال أدناه الذي يوضح عملية التحويل:
➤ تحويل جدول بنمط المصفوفة إلى قائمة باستخدام PivotTable
➤ تحويل جدول بنمط المصفوفة إلى قائمة باستخدام رمز VBA
➤ تحويل جدول بنمط المصفوفة إلى قائمة باستخدام Kutools لـ Excel
➤ تحويل جدول بنمط المصفوفة إلى قائمة باستخدام معادلة Excel
تحويل جدول على شكل مصفوفة إلى قائمة باستخدام الجدول المحوري
لا يحتوي Excel على أمر مدمج مباشر لتحويل جدول على شكل مصفوفة إلى جدول ثلاثي الأعمدة. ومع ذلك، يمكنك باستخداممعالج الجدول المحوريتحويل المصفوفة ذات الجداول المتقاطعة بكفاءة إلى بيانات جدولية مسطحة، جاهزة لمزيد من التحليل. هذه الطريقة مثالية لمجموعات البيانات الصغيرة والمتوسطة، وخصوصًا عندما تريد تسطيح هيكل تقرير معقد. أما في حالة مجموعات البيانات الكبيرة أو إذا لم تكن ملمًّا بعمليات الجداول المحورية، فقد تكون أقل ملاءمة.
1. افتح ورقة العمل التي تحتوي على مصفوفتك، ثم اضغطAlt + D، تليهاP لفتحمعالج الجدول المحوري والرسم البياني المحوري. في المعالج:
- ضمنأين توجد البيانات التي تريد تحليلها، اخترنطاقات توحيد متعددة.
- ضمنأي نوع من التقارير تريد إنشاءه، اخترجدولًا محوريًّا.
2. انقرالتالي. في مربع الحوارالخطوة 2أ من 3، حددسأقوم بإنشاء حقول الصفحة:
3. انقرالتالي. فيالخطوة 2ب من 3، انقر الزر
وحدد نطاق البيانات المصفوفة بالكامل، بما في ذلك رؤوس الصفوف والأعمدة. ثم انقرإضافةلإدخال النطاق في قائمةجميع النطاقات. تأكد من أن اختيار النطاق يغطي المصفوفة بأكملها.
4. فيالخطوة 3 من 3، انقرالتالي، ثم اختر ما إذا كنت تريد وضع الجدول المحوري في ورقة عمل جديدة أو في خلية محددة:
5. انقرإنهاء. سيقوم Excel بإنشاء جدول محوري يلخّص المصفوفة. وبشكل افتراضي، يعرض مجاميع مجمّعة عند نقاط التقاطع. لست بحاجة إلى تعديل هيكل الجدول المحوري لهذه المهمة—فقط انتقل إلى الخطوة التالية:
6. انقر نقرًا مزدوجًا على الخلية التي يتقاطع فيهاالمجموع الإجماليللصفوف والأعمدة (مثل الخلية F22)، وسيُنشئ Excel ورقةً جديدةً تحتوي على جدولٍ ثلاثي الأعمدة، يعرض كل صفٍ فيه مجموعةً فريدةً من رأس الصف ورأس العمود مع القيمة المقابلة.
7. لإكمال العملية، حدد الجدول الجديد، ثم انقر بزر الماوس الأيمن واخترجدول > تحويل إلى نطاق. سيؤدي ذلك إلى إزالة تنسيق الجدول، تاركًا قائمة عادية قابلة للتحرير:
ملاحظة:إذا كانت مصفوفتك تتغير بشكل متكرر، فستحتاج إلى تكرار هذه العملية لتحديث الجدول الثلاثي الأعمدة. وهذه الطريقة أنسب للبيانات الثابتة. بالإضافة إلى ذلك، إذا احتوت مصفوفتك على خلايا فارغة أو مدمجة، فقد تحتاج إلى تنظيف أولي قبل استخدام هذه التقنية.
تحويل جدول على شكل مصفوفة إلى قائمة باستخدام كود VBA
إذا كنت تفضّل الأتمتة أو كنت بحاجة إلى تطبيق هذا التحويل بشكل متكرر، فإن استخدام ماكرو VBA يُمكّنك من تحويل أي جدول على شكل مصفوفة إلى جدول منظم مكوَّن من ثلاثة أعمدة بسرعة فائقة. وتُعد هذه الطريقة مثالية لمجموعات البيانات الكبيرة أو التخطيطات المتنوعة، إذ تلغي الحاجة إلى التنسيق اليدوي تمامًا، وهي الخيار الأمثل للمستخدمين المتمرسين في تشغيل نصوص VBA.
1. اضغطAlt + F11 لفتح محررMicrosoft Visual Basic for Applications.
2. في المحرر، انقرإدراج > وحدة نمطيةلإنشاء وحدة جديدة، ثم الصق الكود التالي في نافذة الوحدة:
📜 كود VBA: تحويل المصفوفة إلى قائمة
Sub ConvertTable()
' Updated by Extendoffice
Dim Rng As Range
Dim cRng As Range
Dim rRng As Range
Dim xOutRng As Range
xTitleId = "KutoolsforExcel"
Set cRng = Application.InputBox("Select your Column labels", xTitleId, Type:=8)
Set rRng = Application.InputBox("Select Your Row Labels", xTitleId, Type:=8)
Set Rng = Application.InputBox("Select your data", xTitleId, Type:=8)
Set outRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set xWs = Rng.Worksheet
k = 1
xColumns = rRng.Column
xRow = cRng.Row
For i = Rng.Rows(1).Row To Rng.Rows(1).Row + Rng.Rows.Count - 1
For j = Rng.Columns(1).Column To Rng.Columns(1).Column + Rng.Columns.Count - 1
outRng.Cells(k, 1) = xWs.Cells(i, xColumns)
outRng.Cells(k, 2) = xWs.Cells(xRow, j)
outRng.Cells(k, 3) = xWs.Cells(i, j)
k = k + 1
Next j
Next i
End Sub3. اضغطF5 أو انقرتشغيللتنفيذ الماكرو، وستُرشدك سلسلة من المطالبات خلال الاختيارات المطلوبة:
الخطوة 1:حددتسميات الأعمدة(عادةً أعلى صف المصفوفة الخاصة بك):
الخطوة 2:حددتسميات الصفوف(عادةً العمود الأول من مصفوفتك):
الخطوة 3:حددمصفوفة نطاق البيانات الفعلية(باستثناء عناوين الصف والعمود):
الخطوة 4:اخترخلية الإخراجالتي سيبدأ عندها الجدول الثلاثي الأعمدة المحوَّل. ويُوصى باستخدام خلية فارغة أو ورقة عمل جديدة:
الخطوة 5:انقرموافق. سيتم الآن تحويل مصفوفتك إلى جدول ثلاثي الأعمدة مسطّح.
⚠️ ملاحظات ونصائح:
• تأكد من استبعاد عناوين الأعمدة والصفوف من نطاق المصفوفة أو نطاق البيانات.
• إذا كانت مصفوفتك تحتوي على خلايا مدمجة، فقم بإلغاء دمجها قبل تشغيل الماكرو لتجنب حدوث أخطاء.
• إذا واجهت أخطاءً، فراجع النطاقات التي حددتها وتأكد من توافقها الصحيح.
تحويل جدول بنمط المصفوفة إلى قائمة باستخدام Kutools لـ Excel
رغم أن الطرق المذكورة أعلاه فعّالة، فقد تبدو مملة أو محبطة للمستخدمين الأقل خبرة. إذا كنت تبحث عن حلٍّ سريع وسهل الاستخدام، فإنKutools لـ Excelيوفّر أداةً مخصصةً تسمىتحويل بُعد الجدول، صُمّمت خصيصًا لهذا الغرض.
تُعد هذه الأداة مثالية للمستخدمين الذين يحولون جداول بنمط المصفوفة بشكل متكرر أو يحتاجون إلى معالجة دفعية، إذ تحافظ—عند الحاجة—على التنسيق الأصلي مثل الخط وتعبئة اللون والمعادلات. العيب الوحيد المحتمل هو أن Kutools إضافة خارجية تتطلب التثبيت، لكنها تظل خيارًا قويًّا لأي شخص يُجري تعديلات منتظمة على بيانات Excel.
الخطوات:
1. بعد تثبيت Kutools، انتقل إلى علامة التبويبKutools، ثم انقر علىنطاق، واخترتحويل بُعد الجدول:
2. في مربع الحوارتحويل بُعد الجدول:
- (1) اخترتحويل الجدول ثنائي الأبعاد إلى جدول أحادي الأبعادضمن قسمنوع التحويل.
- (2) انقر الزر
الموجود بجانبنطاق المصدرلتحديد جدول المصفوفة الخاص بك. - (3) انقر الزر الموجود بجانبنطاق النتائجلتحديد مكان وضع الناتج.
تأكد من تحديد المصفوفة بأكملها، بما في ذلك الرؤوس والبيانات، لتجنب التحويل الجزئي أو النتائج غير الصحيحة.
3. انقر علىموافق. سيتم تحويل المصفوفة فورًا إلى قائمة ذات أعمدة، مع الحفاظ على تنسيق الخلايا الأصلي كلما أمكن:
ملاحظة:تدعم هذه الميزة أيضًا العملية العكسية—تحويل قائمة مسطحة إلى مصفوفة ثنائية الأبعاد، وهي مفيدة لإعادة بناء التقارير أو إعداد البيانات للتحليل المتقاطع. راجع: كيفية تحويل قائمة إلى مصفوفة ثنائية الأبعاد جدول ثنائي الأبعاد.
➤ اعرف المزيد عن ميزة تحويل بُعد الجدول
⏬ نزّل Kutools لـ Excel وجربه مجانًا الآن!
تحويل جدول بنمط المصفوفة إلى قائمة باستخدام معادلة Excel
إذا كنت تفضّل اتباع نهج يعتمد على المعادلات—وهو مفيدٌ بشكل خاص إذا أردت تحديث قائمتك ذات الأعمدة تلقائيًا كلما تم تعديل المصفوفة—يمكنك استخدام مجموعة من دوالINDEX،ROW،COLUMN، وCOUNTA لفك هيكلة البيانات يدويًا. ولا يتطلب هذا الحل استخدام VBA أو إضافات، وهو مثالي لمن يرغب في تجنّب الماكرو أو الأدوات الخارجية. ومع ذلك، فإنه يتطلب انتباهًا دقيقًا لمراجع المعادلات، إذ يجب عادةً إدخال المعادلات كمصفوفة أو سحبها يدويًا لأسفل/لليمين. وهو الأنسب للمصفوفات متوسطة الحجم وللحالات التي تحتاج فيها القائمة إلى البقاء نشطة ومستجيبة لأي تغييرات تطرأ على البيانات الأصلية.
لنفترض أن نطاق البيانات لديك كالتالي:
- تسميات الصفوف موجودة في الخلاياA2:A10.
- تسميات الأعمدة موجودة في الخلاياB1:J1.
- قيم المصفوفة موجودة في الخلاياB2:J10.
1. أنشئ ورقة عمل جديدة أو ابدأ في منطقة فارغة من ورقة العمل الحالية. في الخليةL2، أدخل الصيغة التالية لاستخراج تسمية الصف:
=INDEX($A$2:$A$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1)2. في الخليةM2، أدخل هذه الصيغة لاستخراج تسمية العمود المقابلة:
=INDEX($B$1:$J$1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)3. في الخليةN2، استخرج القيمة من المصفوفة باستخدام:
=INDEX($B$2:$J$10,INT((ROW(A1)-1)/COUNTA($B$1:$J$1))+1,MOD(ROW(A1)-1,COUNTA($B$1:$J$1))+1)4. حدد الخلاياL2:N2، ثم اسحب مقبض التعبئة لأسفل حتى تصل إلى الصف رقمعدد الصفوف × عدد الأعمدة (في هذا المثال، 9 صفوف × 9 أعمدة = 81 صفًا إجمالًا).
✅ نصائح:
- عدّل جميع النطاقات لتتماشى مع هيكل بياناتك الفعلي.
- استخدم
IFوISBLANKلتصفية الصفوف الفارغة عند الحاجة. - لتحقيق سلوك التوسيع التلقائي، فكّر في استخدام
OFFSETأو نطاقات مسماة ديناميكية. - هذه الطريقة أكثر عملية عندما يكون حجم المصفوفة ثابتًا وحجم البيانات معتدلًا.
ℹ️ ملاحظات إضافية:
- المزايا:تظل الصيغ نشطة وتعكس تغييرات المصفوفة تلقائيًا، ولا تتطلب VBA أو إضافات.
- العيوب:قد تؤدي إلى إبطاء أداء المصنف عند التعامل مع مصفوفات كبيرة، كما تتطلب إعدادًا دقيقًا.
عرض توضيحي: تحويل جدول بنمط المصفوفة إلى قائمة باستخدام Kutools لـ Excel
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل