اقلب البيانات رأسًا على عقب في Excel (عكس ترتيب البيانات في الأعمدة/الصفوف)

في بعض الأحيان، قد تحتاج إلى قلب البيانات رأسًا على عقب في Excel، أي عكس ترتيب البيانات في مجموعة بيانات رأسية، وعكس ترتيب البيانات من اليسار إلى اليمين في مجموعة بيانات أفقية.

الآن ، إذا كنت تعتقد أنه يجب أن تكون هناك وظيفة مضمنة في Excel للقيام بذلك ، أخشى أنك ستصاب بخيبة أمل.

بينما توجد عدة طرق لقلب البيانات في Excel ، لا توجد وظائف مضمنة.ولكن يمكنك القيام بذلك بسهولة باستخدام حيل الفرز البسيطة أو الصيغ أو VBA.

سأوضح لك في هذا البرنامج التعليمي كيفية قلب البيانات في الصفوف والأعمدة والجداول رأسًا على عقب في برنامج Excel.

اذا هيا بنا نبدأ!

قلب البيانات باستخدام SORT والأعمدة المساعدة

تتمثل إحدى أسهل الطرق لعكس ترتيب البيانات في Excel في استخدام عمود مساعد ثم استخدام هذا العمود المساعد لفرز البيانات.

قلب البيانات عموديًا (ترتيب عكسي)

لنفترض أن لديك عمودًا مثل أدناهاسم مجموعة البيانات، وتريد قلب هذه البيانات:

فيما يلي خطوات قلب البيانات عموديًا:

1. في العمود المجاور ، أدخل "المساعد" كعنوان العمود
2. في عمود التعليمات ، أدخل سلسلة من الأرقام (1 ، 2 ، 3 ، إلخ).يمكنك القيام بذلك بسرعة باستخدام الطريقة الموضحة هنا
3. حدد مجموعة البيانات بأكملها ، بما في ذلك الأعمدة المساعدة
4. انقر فوق علامة التبويب البيانات
5. انقر فوق رمز الفرز
6. في مربع الحوار "فرز" ، حدد "مساعد" من القائمة المنسدلة "فرز حسب"
7. في القائمة المنسدلة للطلب ، حدد "كبير إلى صغير"
8. انقر فوق موافق

ستقوم الخطوات المذكورة أعلاه بفرز البيانات بناءً على قيم العمود المساعد، مما سيؤدي أيضًا إلى ترتيب الأسماء في البيانات بترتيب عكسي.

لا تتردد في إسقاط عمود المساعدة عند الانتهاء.

في هذا المثال ، أوضح لك كيفية قلب البيانات عندما يكون هناك عمود واحد فقط ، ولكن يمكنك استخدام نفس الأسلوب إذا كان لديك الجدول بأكمله.فقط تأكد من تحديد الجدول بأكمله ، ثم استخدم العمود المساعد لفرز البيانات بترتيب تنازلي.

قلب البيانات أفقيًا

يمكنك أيضًا قلب البيانات أفقيًا في Excel بنفس الطريقة.

يحتوي Excel على خيار فرز البيانات أفقيًا باستخدام مربع الحوار "فرز" (ميزة الفرز من اليسار إلى اليمين).

افترض أن لديك جدولًا كما هو موضح أدناه وتريد قلب هذه البيانات أفقيًا.

فيما يلي خطوات القيام بذلك:

1. في الصف أدناه ، أدخل "المساعد" كعنوان للصف
2. في صف المساعد ، أدخل سلسلة من الأرقام (1 ، 2 ، 3 ، إلخ.).
3. حدد مجموعة البيانات بأكملها ، بما في ذلك الصفوف المساعدة
4. انقر فوق علامة التبويب البيانات
5. انقر فوق رمز الفرز
6. في مربع الحوار "فرز" ، انقر فوق الزر "خيارات".
7. في مربع الحوار الذي يفتح ، انقر على فرز من اليسار إلى اليمين
8. انقر فوق موافق
9. في القائمة المنسدلة فرز حسب ، حدد الصف 3 (أو أيًا كان عمودك المساعد)
10. في القائمة المنسدلة للطلب ، حدد "كبير إلى صغير"
11. انقر فوق موافق

ستؤدي الخطوات المذكورة أعلاه إلى قلب الجدول بأكمله أفقيًا.

عند الانتهاء ، يمكنك حذف الخط المساعد.

استخدم Excel لقلب الصيغ رأسًا على عقبقلب البيانات

يحتوي Microsoft 365 على بعض الصيغ الجديدة التي تجعل من السهل جدًا عكس ترتيب العمود أو الجدول في Excel.

في هذا القسم ، سأوضح لك كيفية القيام بذلك باستخدام صيغة SORTBY (إذا كنت تستخدم Microsoft 365) أو صيغة INDEX (إذا كنت لا تستخدم Microsoft 365)

استخدم وظيفة SORTBY (متوفرة في Microsoft 365)

افترض أن لديك جدولًا يشبه هذا ، وتريد قلب البيانات في هذا الجدول:

للقيام بذلك ، أولاً ، انسخ الرؤوس وضعها في المكان الذي تريد قلب الجدول فيه

استخدم الآن الصيغة التالية أسفل خلية الرأس الموجودة في أقصى اليسار:

=SORTBY($A$2:$B$12,ROW(A2:A12),-1)

تقوم الصيغة أعلاه بفرز البيانات والفرز بناءً على نتيجة الدالة ROW.

في هذه الحالة ، ستعيد الدالة ROW صفيفًا من الأرقام التي تمثل أرقام الصفوف بين النطاق المحدد (في هذا المثال ، سلسلة من الأرقام ، مثل 2 ، 3 ، 4 ، إلخ).

نظرًا لأن المعامل الثالث لهذه الصيغة هو -1 ، فإنه يفرض على الصيغة فرز البيانات بترتيب تنازلي.

سيكون السجل الذي يحتوي على أعلى رقم صف في الأعلى وسيكون السجل الذي يحتوي على أقل رقم للقاعدة في الأسفل ، مما يؤدي إلى عكس ترتيب البيانات بشكل أساسي.

بمجرد الانتهاء ، يمكنك تحويل الصيغة إلى قيمة للحصول على جدول ثابت.

استخدم دالة الفهرس

إذا لم تتمكن من استخدام وظيفة SORTBY ، فلا داعي للقلق - يمكنك استخدام وظيفة INDEX المذهلة.

لنفترض أن لديك مجموعة بيانات من الأسماء كما هو موضح أدناه، وتريد قلب هذه البيانات.

هذه هي الصيغة للقيام بذلك:

= الفهرس ($ A $ 2: $ A $ 12 ، ROWS (A2: $ 12 $))

 كيف تعمل هذه الصيغة؟

تستخدم الصيغة أعلاه الدالة INDEX ، والتي ستعيد القيمة من الخلية بناءً على الرقم المحدد في المعلمة الثانية.

يحدث السحر الحقيقي عندما أستخدم المعامل الثاني لوظيفة ROWS.

نظرًا لأنني أغلقت الجزء الثاني من المرجع في دالة ROWS ، فستعيد في الخلية الأولى عدد الصفوف بين A2 و A12 ، وهو 11.

ولكن مع انتقاله إلى أسفل الصف ، سيتغير المرجع الأول إلى A3 ، ثم A4 ، وهكذا ، بينما سيبقى المرجع الثاني كما هو لأنني أغلقته وجعلته مطلقًا.

كلما نزلنا ، ستنخفض نتيجة الدالة ROWS بمقدار 1 ، من 11 إلى 10 إلى 9 ، وهكذا.

نظرًا لأن الدالة INDEX تُرجع قيمة بناءً على الرقم الموجود في الوسيطة الثانية ، فإن هذا ينتهي بإعطائنا البيانات بترتيب عكسي.

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

لنفترض أن لديك مجموعة بيانات تبدو كالتالي ، وتريد عكس ترتيب الجدول بأكمله:

إليك الصيغة التي ستفعل ذلك من أجلك:

=INDEX($A$2:$B$12,ROWS(A2:$A$12),COLUMNS($A$2:A2))

إليك صيغة مماثلة حيث أضفت أيضًا معلمة ثالثة تحدد رقم العمود الذي يجب أخذ القيمة منه.

لجعل هذه الصيغة ديناميكية ، استخدمت وظيفة الأعمدة ، والتي تستمر في تغيير قيمة العمود من 1 إلى 2 إلى 3 أثناء نسخها إلى اليمين.

بمجرد الانتهاء ، يمكنك تحويل الصيغة إلى قيمة لضمان نتائج ثابتة.

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

عكس البيانات باستخدام VBA

إذا كان تقليب البيانات في Excel أمرًا يتعين عليك القيام به كثيرًا ، فيمكنك أيضًا تجربة طريقة VBA.

باستخدام التعليمات البرمجية للماكرو لـ VBA ، يمكنك نسخها ولصقها مرة واحدة في مصنف في محرر VBA ، ثم إعادة استخدامها مرارًا وتكرارًا في نفس المصنف.

يمكنك أيضًا حفظ الكود في مصنف ماكرو شخصي أو كوظيفة إضافية في Excel وتكون قادرًا على استخدامه في أي مصنف على نظامك.

يوجد أدناه رمز VBA لقلب البيانات المحددة عموديًا في ورقة العمل.

قلب فرعي ()

خافت TopRow كمتغير
خافت LastRow كمتغير
خافت StartNum كـ عدد صحيح
خافت EndNum كعدد صحيح

Application.ScreenUpdating = خطأ

رقم البداية = 1
EndNum = التحديد. الصفوف. العد

افعل أثناء StartNum <EndNum
TopRow = Selection.Rows (StartNum)
LastRow = Selection.Rows (EndNum)
Selection.Rows (EndNum) = TopRow
Selection.Rows (StartNum) = LastRow
رقم البداية = رقم البداية + 1
EndNum = EndNum - 1
أنشوطة

Application.ScreenUpdating = ترو
نهاية الفرعية

لاستخدام هذا الرمز ، تحتاج أولاً إلى تحديد مجموعة البيانات التي تريد عكسها (باستثناء الرؤوس) ، ثم تشغيل هذا الرمز.

كيف يعمل هذا الرمز؟

يحسب الكود أعلاه أولاً العدد الإجمالي للصفوف في مجموعة البيانات ويخصصه لمتغير EndNum.

ثم يستخدم حلقة Do while لفرز البيانات.

الطريقة التي يفرز بها هذه البيانات هي بأخذ الصف الأول والأخير وتبديلهما.ثم ينتقل إلى الصف الثاني من الصف قبل الأخير ثم يقوم بتبديلها.ثم ينتقل إلى الصف الثالث من الصف الثالث إلى الصف الأخير ، وهكذا.

تنتهي الحلقة عند اكتمال كل عمليات الفرز.

كما أنه يستخدم خاصية Application.ScreenUpdating ويضبطها على FALSE عند تشغيل الكود ، ثم يعيدها إلى TRUE عند انتهاء تشغيل الكود.

يضمن ذلك عدم ظهور التغييرات التي تحدث على الشاشة في الوقت الفعلي ، كما يعمل على تسريع العملية.

كيف تستخدم الكود؟

انسخ والصق هذا الرمز في محرر VB كما يلي:

1. افتح ملف Excel حيث تريد إضافة رمز VBA
2. اضغط مع الاستمرار على ALT واضغط على F-11 (يمكنك أيضًا الانتقال إلى علامة تبويب المطور والنقر فوق رمز Visual Basic)
3. في محرر Visual Basic الذي يفتح ، سيكون هناك Project Explorer على الجانب الأيسر من محرر VBA.إذا كنت لا ترى ذلك ، فانقر فوق علامة التبويب عرض ، ثم انقر فوق مستكشف المشاريع
4. انقر بزر الماوس الأيمن فوق أي عنصر في المصنف حيث تريد إضافة رمز
5. انتقل إلى خيار الإدراج وانقر على الوحدة النمطية.سيؤدي هذا إلى إضافة وحدة نمطية جديدة إلى المصنف
6. انقر نقرًا مزدوجًا فوق رمز الوحدة النمطية في Project Explorer.سيؤدي هذا إلى فتح نافذة التعليمات البرمجية لتلك الوحدة
7. انسخ والصق رمز فبا أعلاه في نافذة الكود

لتشغيل التعليمات البرمجية للماكرو لـ VBA ، حدد أولاً مجموعة البيانات لقلبها (باستثناء الرأس).

مع تحديد البيانات ، انتقل إلى محرر VB وانقر فوق زر التشغيل الأخضر في شريط الأدوات ، أو حدد أي سطر في الكود واضغط على F5

إذن ، هذه بعض الطرق التي يمكنك بها قلب البيانات في Excel (على سبيل المثال ، عكس ترتيب مجموعة البيانات).

يمكن استخدام جميع الطرق التي غطتها في هذا البرنامج التعليمي (الصيغ ووظائف SORT و VBA) لقلب البيانات رأسياً وأفقياً (يجب عليك ضبط الصيغ ورمز VBA وفقًا لتقليب البيانات الأفقي).

أتمنى أن تكون قد وجدت هذا البرنامج التعليمي مفيدًا.