كيفية تنسيق صفيف مسكوب في Excel

ملخص

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

• تتيح قواعد التنسيق المشروطة القائمة على الصيغة تعديلات التنسيق التلقائي عندما تتغير معلمات البيانات.

• يتيح لك وضع رمز بالدولار قبل مرجع العمود تطبيق القواعد على جميع الصفوف في البيانات.

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

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

للمتابعة كما تقرأ ، تنزيل نسخة من مصنف Excel هذا. بعد النقر فوق الرابط ، ستجد زر التنزيل في الزاوية العلوية اليمنى من الشاشة.

نظرًا لأن وظيفة Pivotby لا تتضمن وسيطة تتيح لك تنسيق النتيجة ، فإن التمييز بين صفوف الرأس ، وصفوف البيانات ، والصفوف الفرعية ، والصف الكلي الكبير أمر صعب.

في هذه المرحلة ، قد يتم إغراء تطبيق التنسيق المباشر – مجموعة الخط من علامة التبويب Home على الشريط – لفصل الأنواع المختلفة من الصفوف المختلفة بصريًا في البيانات.

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

بدلاً من ذلك ، يجب عليك استخدام التنسيق الشرطي ، والذي يسمح لك بتنسيق الخلايا والصفوف وفقًا لقيمها.

حدد جميع البيانات – بالإضافة إلى بعض الصفوف الإضافية في الأسفل للسماح بالنمو الرأسي – وفي علامة التبويب المنزل على الشريط ، انقر فوق تنسيق مشروط> إدارة القواعد.

بعد ذلك ، في مربع الحوار “مدير قواعد التنسيق المشروط” ، انقر فوق “قاعدة جديدة”.

لكل قاعدة من القواعد التي ستقوم بإنشائها لتنسيق صفيفك المسكوب ، ستحتاج إلى استخدام صيغة. لذلك ، في منطقة تحديد نوع القاعدة في مربع الحوار قاعدة التنسيق الجديد ، حدد الخيار النهائي ، “استخدم صيغة لتحديد الخلايا التي يجب تنسيقها.”

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

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

=ISTEXT($G1)

نظرًا لأن وظيفة iStext تعامل الخلايا الفارغة والخلايا التي تحتوي على نص كقيمة نصية ، فإن قاعدة التنسيق الشرطية ستنظر في الخلايا G1 إلى G3 على أنها نص ، مع الخلايا المتبقية في عمود G تحتوي على قيم الأرقام.

الأهم من ذلك ، أن إضافة علامة الدولار ($) قبل مرجع العمود – المعروف أيضًا باسم المرجع المختلط – يضم التنسيق الشرطي لهذا العمود ، مع السماح لـ Excel بتطبيق القاعدة على الصفوف المتبقية.

بعد كتابة مرجع خلية ، بدلاً من كتابة علامة الدولار يدويًا ، اضغط على F4 للتبديل بين المراجع المطلقة (على سبيل المثال ، $ g $) ، والمراجع المختلطة (على سبيل المثال ، $ G1 أو G $ 1) ، والمراجع النسبية (على سبيل المثال ، G1).

أخيرًا ، نظرًا لأنك حددت في البداية البيانات في الأعمدة A إلى G ، سيتم تطبيق التنسيق الشرطي على الصف بالكامل حيث يتم استيفاء الشرط.

الآن ، انقر فوق “تنسيق” لتحديد التنسيق لصفوف الرأس. في هذه الحالة ، تريد أن تكون رمادية ملونة. ثم ، انقر فوق “موافق” في خلايا التنسيق وتحرير مربعات الحوار قاعدة التنسيق.

عند النقر فوق “تطبيق” في مربع حوار Manager لقواعد التنسيق الشرطي ، سترى أن الصفوف التي تحتوي فيها العمود G فقط على خلايا أو نص فارغ – بمعنى آخر ، صفوف الرأس – باللون الرمادي.

بعد ذلك ، تريد تنسيق الصفوف الفرعية بحيث يتبنى حشوة خضراء فاتحة.

مرة أخرى ، مراجعة البيانات بعناية لمعرفة الشروط التي يمكنك استخدامها لتطبيق التنسيق على هذه الصفوف فقط. في هذه الحالة ، تحتوي الصفوف الفرعية على نص في العمود A ولكن لا شيء في العمود B. أيضًا ، نظرًا لأن الصف الكلي الكبير يفي أيضًا بهذه المعايير ، فأنت بحاجة إلى استبعاد أي خلايا في العمود A تحتوي على عبارة “Total Grand”.

مع استمرار فتح مربع حوار Manager لقواعد التنسيق الشرطي ، انقر فوق “قاعدة جديدة” ، وحدد الخيار الذي يتيح لك استخدام صيغة لتنسيق الخلايا. هذه المرة ، في حقل الصيغة ، اكتب:

=AND($A1<>"",$B1="",$A1<>"Grand Total")

أين

• تتيح لك الوظيفة تحديد أكثر من حالة واحدة داخل الأقواس ،

• $ A1<> “” يخبر Excel بالبحث عن خلايا في العمود A لا تحتوي على خلايا فارغة () (“”) ،
• $ B1 = “” يخبر Excel بالبحث عن الخلايا في العمود B الذي يحتوي على (=) خلايا فارغة (“”) ، و
• $ A1<> “الكلي الكبير” يطلب من Excel استبعاد () أي خلايا في العمود A تحتوي على نص “Grand Total”.

كما هو الحال مع القاعدة السابقة ، تذكر إدراج علامة $ قبل مراجع العمود للسماح لـ Excel بتطبيق نفس القاعدة لأسفل جميع الصفوف المحددة.

الآن ، انقر فوق “تنسيق” لاختيار لون التعبئة الخضراء الخفيفة ، وبعد إغلاق خلايا التنسيق وتحرير مربعات الحوار قاعدة التنسيق ، انقر فوق “تطبيق” لرؤية الصفوف الفرعية المملوءة باللون الأخضر الخفيف.

أخيرًا ، تريد أن تمتلئ الخلايا في الصف الكلي الكبير بالأخضر الأكثر جرأة.

نظرًا لأن الصف الكلي الكبير هو الصف الوحيد الذي يحتوي على عبارة “Total Grand” في العمود A ، فهذا هو المعيار الذي يمكنك استخدامه للتنسيق المشروط. في مربع حوار Manager لتنسيق قواعد التنسيق ، انقر فوق “قاعدة جديدة” ، وحدد الخيار النهائي في قائمة تحديد نوع القاعدة. الآن ، في حقل الصيغة ، اكتب:

=$A1="Grand Total"

بعد ذلك ، انقر فوق “تنسيق” ، وحدد لون تعبئة أخضر غامق لتطبيقه على الخلايا التي تتطابق مع هذا المعيار. الآن ، عندما تغلق خلايا التنسيق وتحرير مربعات الحوار قاعدة التنسيق ، وانقر فوق “تطبيق” في مربع الحوار “مدير قواعد التنسيق” ، سترى أن الصف الكلي الكبير الخاص بك قد اعتمد هذا التنسيق.

الآن بعد أن قمت بتطبيق جميع قواعدك ، انقر فوق “إغلاق” في مربع حوار Manager لقواعد التنسيق المشروط. بعد ذلك ، اضبط بعض بياناتك في الجدول الأصلي ، وشاهد النتيجة المنسكبة وتحديث تنسيقها وفقًا لذلك.

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

---

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