في بعض الأحيان في Excel ، قد ترغب في ذلكإخفاءالقيم الصفرية في مجموعة البيانات وعرض هذه الخلايا على أنها فارغة.
لنفترض أن لديك مجموعة بيانات مثل المجموعة أدناه ، وتريد إخفاء القيمة 0 في كل تلك الخلايا (أو تريد استبدالها بشيء مثل شرطة أو النص "غير متوفر").
بينما يمكنك القيام بذلك يدويًا باستخدام مجموعات بيانات صغيرة ، فإنك تحتاج إلى نهج أفضل عند العمل مع مجموعات البيانات الكبيرة.
في هذا البرنامج التعليمي ، سأوضح لك عدة طرق لإخفاء الأصفار في Excel ، وطريقة لتحديد جميع الأصفار وإزالتها من مجموعة البيانات.
提示 提示: عندما تخفي 0 في خلية باستخدام الطريقة الموضحة في هذا البرنامج التعليمي ، فإنها تخفي 0 فقط دون إزالتها.على الرغم من أن الخلايا قد تظهر فارغة ، إلا أن هذه الخلايا لا تزال تحتوي على أصفار.إذا كنت تستخدم هذه الخلايا (الأصفار المخفية) في أي صيغة ، فسيتم استخدام هذه الخلايا في الصيغة.
لنبدأ العمل على هذه الأساليب!
المحتويات
إخفاء تلقائي للقيم الصفرية في الخلايا
يحتوي Excel على ميزة مضمنة تسمح لك بإخفاء جميع القيم الصفرية لورقة العمل بأكملها تلقائيًا.
كل ما عليك فعله هو إلغاء تحديد مربع في خيارات Excel وسيتم تطبيق التغييرات على ورقة العمل بأكملها.
افترض أن لديك مجموعة بيانات المبيعات الموضحة أدناه ، وتريد إخفاء جميع القيم الصفرية وإظهار خلية فارغة.
فيما يلي خطوات إخفاء الأصفار في كل الخلايا في مصنف Excel:
- افتح المصنف حيث تريد إخفاء كل الأصفار
- انقر فوق علامة التبويب ملف
- انقر فوق الخيار
- في مربع الحوار "خيارات Excel" الذي يفتح ، انقر فوق الخيار "خيارات متقدمة" في الجزء الأيمن
- مرر لأسفل إلى قسم "خيارات العرض لهذه الورقة" وحدد الورقة التي تريد إخفاء الأصفار فيها.
- قم بإلغاء تحديد الخيار "عرض الأصفار في الخلايا ذات القيم الصفرية"
- انقر فوق موافق
ستخفي الخطوات المذكورة أعلاه الأصفار في جميع الخلايا في ورقة العمل المحددة مرة واحدة.
ينطبق هذا التغيير أيضًا على الخلايا التي تكون فيها نتيجة الصيغة صفرًا.
تذكر أن هذه الطريقة تخفي فقط القيم 0 في الخلية ، ولا تحذفها.0 لا يزال موجودًا ، إنه مخفي فقط.
هذه هي الطريقة الأفضل والأسرع لإخفاء القيم الصفرية في Excel.لكن هذا مفيد فقط إذا كنت تريد إخفاء القيم الصفرية في جميع أنحاء الورقة.إذا كنت تريد فقط إخفاء القيم الصفرية في نطاق معين من الخلايا ، فمن الأفضل استخدام الطرق الأخرى الموضحة لاحقًا في هذا البرنامج التعليمي.
إخفاء القيم الصفرية في الخلايا باستخدام التنسيق الشرطي
في حين أن الطريقة المذكورة أعلاه هي الأكثر ملاءمة ، إلا أنها لا تسمح لك بإخفاء الأصفار فقط في نطاق معين (فهي تخفي القيم الصفرية عبر ورقة العمل بأكملها).
إذا كنت تريد إخفاء الأصفار لمجموعة بيانات معينة (أو مجموعات بيانات متعددة) ، يمكنك استخدام التنسيق الشرطي.
باستخدام التنسيق الشرطي ، يمكنك تطبيق تنسيق معين بناءً على القيمة الموجودة في الخلية.لذلك إذا كانت بعض الخلايا لها قيمة 0 ، فيمكنك ببساطة كتابة تنسيق شرطي لإخفائه (أو حتى تمييزه إذا لزم الأمر).
لنفترض أن لديك مجموعة بيانات مثل أدناه وتريد إخفاء الأصفار في الخلايا.
فيما يلي خطوات إخفاء الأصفار في Excel باستخدام التنسيق الشرطي:
- اختر مجموعة بيانات
- انقر فوق علامة التبويب الصفحة الرئيسية
- في مجموعة النمط ، انقر فوق تنسيق شرطي.
- ضع المؤشر على خيار "تمييز قواعد الخلية" وانقر على خيار "يساوي" في الخيارات المعروضة.
- في مربع حوار يساوي ، أدخل "0" في الحقل الأيسر.
- حدد القائمة المنسدلة تنسيق وانقر فوق تنسيق مخصص.
- في مربع الحوار تنسيق الخلايا ، حدد علامة التبويب الخط.
- من القائمة المنسدلة اللون ، حدد الأبيض.
- انقر فوق موافق.
- انقر فوق موافق.
تعمل الخطوات المذكورة أعلاه على تغيير لون خط الخلايا ذات الأصفار إلى الأبيض ، مما يجعل هذه الخلايا تبدو فارغة.
لن يعمل هذا الحل إلا إذا كانت لديك خلفية بيضاء في الورقة.إذا كان هناك أي لون آخر للخلفية في الخلية ، فقد يظل اللون الأبيض يجعل الصفر مرئيًا.
إذا كنت تريد إخفاء الأصفار بالفعل في الخلية ، فستبدو الخلية فارغة بغض النظر عن لون خلفية الخلية ، يمكنك استخدام الخطوات التالية.
- اختر مجموعة بيانات
- انقر فوق علامة التبويب الصفحة الرئيسية
- في مجموعة النمط ، انقر فوق تنسيق شرطي.
- ضع المؤشر على خيار "تمييز قواعد الخلية" وانقر على خيار "يساوي" في الخيارات المعروضة.
- في مربع حوار يساوي ، أدخل 0 في الحقل الأيسر.
- حدد القائمة المنسدلة تنسيق وانقر فوق تنسيق مخصص.
- في مربع الحوار تنسيق الخلايا ، حدد علامة التبويب الأرقام ، ثم انقر فوق الخيار "مخصص" في الجزء الأيمن.
- في خيارات التنسيق المخصصة ، أدخل ما يلي في حقل النوع:؛؛؛ (ثلاث فواصل منقوطة ، بدون مسافات)
- انقر فوق موافق.
- انقر فوق موافق.
تعمل الخطوات المذكورة أعلاه على تغيير التنسيق المخصص للخلايا ذات القيمة 0.
عندما تستخدم ثلاث فواصل منقوطة كتنسيق ، فإنها تخفي كل شيء في الخلية (سواء كانت أرقامًا أو نصًا).ونظرًا لأن هذا التنسيق ينطبق فقط على الخلايا ذات القيمة 0 ، فإنه يخفي فقط الأصفار وليس باقي مجموعة البيانات.
يمكنك أيضًا استخدام هذه الطريقة لتمييز هذه الخلايا بلون مختلف ، مثل الأحمر أو الأصفر.للقيام بذلك ، ما عليك سوى إضافة خطوة أخرى (بعد الخطوة 8) حيث تحتاج إلى تحديد علامة التبويب تعبئة في مربع حوار تنسيق الخلايا واختيار اللون الذي تريد تمييز الخلايا به.
تعمل الطريقتان المذكورتان أعلاه أيضًا عندما يكون 0 هو نتيجة الصيغة في الخلية.
ملاحظة: كلتا الطريقتين الموصوفتين في هذا القسم تخفيان القيم الصفرية فقط.لا يزيل تلك الأصفار.هذا يعني أنه إذا استخدمت هذه الخلايا في عملية حسابية ، فسيتم أيضًا استخدام جميع الأصفار.
إخفاء القيم الصفرية بالتنسيق المخصص
هناك طريقة أخرى لإخفاء القيم الصفرية من مجموعة البيانات وهي إنشاء تنسيق مخصص يخفي القيم في الخلايا التي تحتوي على 0 ، بينما تظهر أي قيم أخرى كما هو متوقع.
فيما يلي خطوات إخفاء الأصفار في الخلايا بتنسيق مخصص:
- حدد مجموعة البيانات بأكملها لتطبيق هذا التنسيق عليها.حدد كل الخلايا إذا كنت تريد هذا الخيار للورقة بأكملها
- مع تحديد الخلية ، انقر فوق علامة التبويب الصفحة الرئيسية
- في المجموعة خلايا ، انقر فوق خيارات التنسيق
- من خيارات القائمة المنسدلة ، انقر فوق تنسيق الخلايا.سيؤدي هذا إلى فتح مربع حوار تنسيق الخلايا
- في مربع الحوار تنسيق الخلايا ، حدد علامة التبويب الأرقام
- في الخيارات الموجودة في الجزء الأيمن ، انقر فوق "خيارات مخصصة".
- في الخيارات الموجودة على اليمين ، أدخل التنسيق التالي في حقل النوع:0؛ -0 ؛؛ @
- انقر فوق موافق
ستطبق الخطوات المذكورة أعلاه تنسيقًا مخصصًا على الخلايا المحددة ، حيث يتم تنسيق الخلايا ذات القيمة 0 فقط لإخفاء القيمة ، بينما تعرض الخلايا المتبقية البيانات كما هو متوقع.
كيف يعمل هذا التنسيق المخصص؟
في Excel ، يمكنك تحديد أربعة أنواع من تنسيقات البيانات:
- رقم إيجابي
- عدد السلبي
- 零
- نص
لكل نوع من أنواع البيانات هذه ، يمكنك تحديد تنسيق.
في معظم الحالات ، يوجد تنسيق افتراضي - عام ، مما يجعل كل أنواع البيانات هذه مرئية.لكن لديك المرونة لإنشاء تنسيقات محددة لكل نوع بيانات.
إليك بنية التنسيق المخصصة التي تحتاج إلى اتباعها في حالة تطبيق تنسيق مخصص على كل نوع من أنواع البيانات التالية:
؛؛؛
في الطريقة المعروضة أعلاه ، حددت التنسيق على النحو التالي:
0؛ -0 ؛؛ @
يقوم التنسيق أعلاه بما يلي:
- يتم عرض الأرقام الموجبة كما هي
- يتم عرض الأرقام السالبة بعلامة ناقص
- الأصفار مخفية (لأنه لم يتم تحديد تنسيق)
- يتم عرض النص كما هو
استبدل الأصفار بشرطة أو بنص معين
في بعض الحالات ، قد لا ترغب فقط في إخفاء الأصفار في الخلايا ، ولكن أيضًا استبدالها بشيء أكثر وضوحًا.
على سبيل المثال ، قد ترغب في إزالة الأصفار واستبدالها بشرطة أو نص مثل "غير متوفر" أو "البيانات غير متوفرة بعد"
أصبح هذا ممكنًا مرة أخرى باستخدام تنسيق أرقام مخصص ، حيث يمكنك تحديد ما يجب أن تعرضه الخلية بدلاً من الصفر.
فيما يلي خطوات تحويل 0 إلى شرطة في Excel:
- حدد مجموعة البيانات بأكملها لتطبيق هذا التنسيق عليها.حدد كل الخلايا إذا كنت تريد هذا الخيار للورقة بأكملها
- مع تحديد الخلية ، انقر فوق علامة التبويب الصفحة الرئيسية
- في المجموعة خلايا ، انقر فوق خيارات التنسيق
- من خيارات القائمة المنسدلة ، انقر فوق تنسيق الخلايا.سيؤدي هذا إلى فتح مربع حوار تنسيق الخلايا
- في مربع الحوار تنسيق الخلايا ، حدد علامة التبويب الأرقام
- في الخيارات الموجودة في الجزء الأيمن ، انقر فوق "خيارات مخصصة".
- في الخيارات الموجودة على اليمين ، أدخل التنسيق التالي في حقل النوع:0؛ -0؛ -؛ @
- انقر فوق موافق
ستترك الخطوات المذكورة أعلاه جميع الخلايا الأخرى دون تغيير وتغير الخلايا بقيمة 0 لعرض شرطة (-).
注意: في هذا المثال اخترت استبدال الأصفار بشرطة ، ولكن يمكنك استخدام أي نص.على سبيل المثال ، إذا كنت تريد عرض NA في خلية ، فيمكنك استخدام التنسيق التالي:0؛ -0؛ "NA"؛ @
نصائح احترافية: يمكنك أيضًا تحديد بعض ألوان النص بتنسيق أرقام مخصص.على سبيل المثال ، إذا أردت أن تعرض الخلايا ذات الأصفار النص NA باللون الأحمر ، فيمكنك استخدام التنسيق التالي:0؛ -0؛ [أحمر] "NA"؛ @
إخفاء القيم الصفرية في الجدول المحوري
هناك سيناريوهان محتملان يعرض فيهما الجدول المحوري القيمة 0:
- تحتوي خلايا البيانات المصدر المُلخصة في الجدول المحوري على 0 قيم
- خلايا البيانات المصدر للملخص في الجدول المحوري فارغة ، وقد تم تنسيق الجدول المحوري لعرض الخلايا الفارغة كأصفار
دعونا نرى كيفية إخفاء الأصفار في الجدول المحوري في كل حالة:
عندما تكون خلية البيانات المصدر 0
يوجد أدناه نموذج لمجموعة البيانات التي استخدمتها لإنشاء الجدول المحوري.لاحظ أن جميع أرقام الكمية / الإيرادات / الربح المسجلة لـ "الغرب" هي 0.
يبدو الجدول المحوري الذي تم إنشاؤه باستخدام مجموعة البيانات هذه كما يلي:
نظرًا لأن جميع السجلات في المنطقة الغربية بها أصفار في مجموعة البيانات ، فسترى أصفارًا في الجدول المحوري الغربي هنا.
إذا كنت تريد إخفاء الأصفار في هذا الجدول المحوري ، فيمكنك استخدام التنسيق الشرطي للقيام بذلك.
ومع ذلك ، فإن التنسيق الشرطي في Excel PivotTables يعمل بشكل مختلف قليلاً عن البيانات العادية.
فيما يلي خطوات تطبيق التنسيق الشرطي على جدول محوري لإخفاء الأصفار:
- حدد أي خلية في العمود تحتوي على 0 في ملخص PivotTable (لا تحدد كل الخلايا ، واحدة فقط)
- انقر فوق علامة التبويب الصفحة الرئيسية
- في مجموعة النمط ، انقر فوق تنسيق شرطي.
- ضع المؤشر على خيار "تمييز قواعد الخلية" وانقر على خيار "يساوي" في الخيارات المعروضة.
- في مربع حوار يساوي ، أدخل 0 في الحقل الأيسر.
- حدد القائمة المنسدلة تنسيق وانقر فوق تنسيق مخصص.
- في مربع الحوار تنسيق الخلايا ، حدد علامة التبويب الأرقام ، ثم انقر فوق الخيار "مخصص" في الجزء الأيمن.
- في خيارات التنسيق المخصصة ، أدخل ما يلي في حقل النوع:؛؛؛ (ثلاث فواصل منقوطة ، بدون مسافات)
- انقر فوق موافق.سترى أيقونات صغيرة لخيارات التنسيق على يمين الخلايا المحددة.
- انقر فوق رمز خيارات التنسيق هذا
- من بين الخيارات المتاحة ، حدد - كل الخلايا التي تعرض مجموع قيمة الإيرادات للمنطقة.
تخفي الخطوات المذكورة أعلاه الأصفار في الجدول المحوري ، إذا قمت بتغيير هيكل الجدول المحوري (بإضافة المزيد من رؤوس الصفوف / الأعمدة إليه ، سيستمر إخفاء الأصفار).
إذا كنت تريد معرفة المزيد حول كيفية عملها ، فإليك برنامج تعليمي مفصل يشرح ذلكتطبيق التنسيق الشرطي على جدول محوريبالطريقة الصحيحة.
عندما تحتوي خلية البيانات المصدر على خلايا فارغة
يوجد أدناه مثال حيث لدي مجموعة بيانات بها خلايا فارغة لجميع سجلات West.
إذا قمت بإنشاء جدول محوري بهذه البيانات ، فسأحصل على نتائج مثل أدناه.
بشكل افتراضي ، يعرض الجدول المحوري خلايا فارغة عندما تكون جميع الخلايا في بيانات المصدر فارغة.ومع ذلك ، إذا كنت لا تزال ترى الرقم 0 في هذه الحالة ، فاتبع هذه الخطوات لإخفاء الصفر وإظهار الخلية الفارغة:
- انقر بزر الماوس الأيمن على أي خلية في الجدول المحوري
- انقر فوق خيارات PivotTable
- في مربع الحوار خيارات PivotTable ، انقر فوق علامة التبويب التخطيط والتنسيق
- في خيارات التنسيق ، حدد الخيار و "العرض: للخلايا الفارغة" واتركه فارغًا.
- انقر فوق موافق.
ستعمل الخطوات المذكورة أعلاه على إخفاء الأصفار في الجدول المحوري وإظهار خلية فارغة.
يمكنك في الخطوة 0 تحديد ما إذا كنت تريد أن يعرض الجدول المحوري شيئًا ما بدلاً من 4.على سبيل المثال ، إذا كنت تريد عرض النص - "البيانات غير متوفرة" ، بدلاً من 0 ، في الحقل (الموضح أدناه).
البحث عن وإزالة الأصفار في Excel
في جميع الطرق المذكورة أعلاه ، لقد أوضحت لك كيفية إخفاء القيمة 0 في الخلية ، لكن القيمة تبقى في الخلية.
إذا كنت تريد إزالة القيم الصفرية من مجموعة البيانات (والتي ستترك لك خلايا فارغة) ، فاستخدم الخطوات التالية:
- حدد مجموعة بيانات (أو ورقة عمل كاملة)
- انقر فوق علامة التبويب الصفحة الرئيسية
- في مجموعة التحرير ، انقر فوق "بحث وتحديد"
- من الخيارات التي تظهر في القائمة المنسدلة ، انقر فوق "استبدال"
- في مربع الحوار "بحث واستبدال" ، قم بإجراء التغييرات التالية:
- في البحث عن الحقل ، أدخل 0
- في حقل "استبدال بـ" ، أدخل لا شيء (اتركه فارغًا)
- انقر فوق الزر "خيارات" وحدد الخيار "مطابقة محتويات الخلية بالكامل"
- انقر فوق استبدال الكل
ستحل الخطوات المذكورة أعلاه محل جميع الخلايا على الفور بقيمة صفر بفراغات.
إذا كنت لا تريد إزالة الأصفار فورًا وتحديد الخلايا أولاً ، فاستخدم الخطوات التالية:
- حدد مجموعة بيانات (أو ورقة عمل كاملة)
- انقر فوق علامة التبويب الصفحة الرئيسية
- في مجموعة التحرير ، انقر فوق "بحث وتحديد"
- من الخيارات التي تظهر في القائمة المنسدلة ، انقر فوق "استبدال"
- في مربع الحوار "بحث واستبدال" ، قم بإجراء التغييرات التالية:
- في البحث عن الحقل ، أدخل 0
- في حقل "استبدال بـ" ، أدخل لا شيء (اتركه فارغًا)
- انقر فوق الزر "خيارات" وحدد الخيار "مطابقة محتويات الخلية بالكامل"
- انقر فوق الزر "بحث عن الكل".سيعطيك هذا قائمة بجميع الخلايا التي تحتوي على 0
- اضغط على Control-A (اضغط باستمرار على مفتاح التحكم واضغط على A).سيؤدي هذا إلى تحديد جميع الخلايا التي تحتوي على 0 بداخلها.
الآن بعد أن حددت جميع خلايا 0 ، يمكنك حذفها أو استبدالها بشيء آخر أو تغيير اللون وتمييزها.
إخفاء الصفر إزالة الأصفار
عند التعامل مع الخلايا التي تحتوي على 0s ، تحتاج إلى معرفة الفرق بين إخفاءها وحذفها.
عندما تقوم بإخفاء قيمة صفرية في خلية ، فإنها تكون غير مرئية بالنسبة لك ، ولكنها تظل في الخلية.هذا يعني أنه إذا كان لديك مجموعة بيانات واستخدمتها لإجراء العمليات الحسابية ، فسيتم أيضًا استخدام هذه القيمة الصفرية في العمليات الحسابية.
على العكس من ذلك ، عندما يكون لديك خلية فارغة ، فإن استخدامها في صيغة قد يعني أن Excel يتجاهل هذه الخلايا تلقائيًا (اعتمادًا على الصيغة التي تستخدمها).
على سبيل المثال ، هذا ما أحصل عليه عندما أستخدم صيغة AVERAGE للحصول على متوسط عمودين (النتيجة في الصف 14).تم إخفاء القيم 0 في خلايا العمود A (لكنها لا تزال موجودة في الخلايا) ، وتمت إزالة القيم 0 في خلايا العمود B.
على الرغم من أن مجموعتي البيانات لكلا مجموعتي البيانات تبدو متشابهة ، يمكنك ملاحظة الاختلاف في النتائج.
هذا لأن صيغة AVERAGE تتجاهل الخلايا الفارغة في العمود B ، لكنها تستخدم خلايا في العمود A (لأن الخلايا في العمود A لا تزال تحتوي على القيمة 0).
