ستساعدك هذه المقالة في فهم جميع أسباب أخطاء #SPILL بالإضافة إلى الحلول لإصلاحها في Excel 365.
#تسرب! هو نوع جديد من أخطاء Excel يحدث بشكل أساسي عندما تحاول الصيغة التي تنتج نتائج حسابية متعددة عرض مخرجاتها في نطاق انسكاب ولكن هذا النطاق يحتوي بالفعل على بعض البيانات الأخرى.
يمكن أن تكون بيانات الحظر أي شيء بما في ذلك القيمة النصية أو الخلايا المدمجة أو حرف مسافة عادي أو حتى في حالة عدم وجود مكان كافٍ لإرجاع النتائج. الحل بسيط ، إما مسح نطاق أي بيانات حظر أو تحديد مجموعة فارغة من الخلايا التي لا تحتوي على أي نوع من البيانات فيه.
يحدث خطأ انسكاب السوائل عادةً عند حساب صيغ الصفيف الديناميكية ، لأن صيغة الصفيف الديناميكية هي الصيغة التي تُخرج النتائج في خلايا متعددة أو صفيف. دعنا نلقي نظرة على مزيد من التفاصيل ونفهم سبب حدوث هذا الخطأ في Excel وكيفية حله.
ما الذي يسبب خطأ الانسكاب؟
منذ إطلاق المصفوفات الديناميكية في 2018 ، يمكن لصيغ Excel التعامل مع قيم متعددة في وقت واحد وإرجاع النتائج في أكثر من خلية واحدة. المصفوفات الديناميكية عبارة عن صفائف قابلة لتغيير الحجم تسمح للصيغ بإرجاع نتائج متعددة إلى نطاق من الخلايا في ورقة العمل بناءً على صيغة تم إدخالها في خلية واحدة.
عندما تُرجع صيغة صفيف ديناميكية نتائج متعددة ، تنتشر هذه النتائج تلقائيًا في الخلايا المجاورة. يسمى هذا السلوك "Spill" في Excel. ويطلق على نطاق الخلايا التي تتسرب فيها النتائج اسم "نطاق الانسكاب". سيتوسع نطاق الانسكاب أو يتقلص تلقائيًا بناءً على قيم المصدر.
إذا كانت الصيغة تحاول تعبئة نطاق انسكاب بنتائج متعددة ولكن تم حظرها بواسطة شيء ما في هذا النطاق ، فسيحدث خطأ #SPILL.
يحتوي Excel الآن على 9 وظائف تستخدم وظيفة الصفيف الديناميكي لحل المشكلات ، وتشمل هذه:
- تسلسل
- منقي
- تبديل موضع
- فرز
- صنف حسب
- رانداري
- فريدة من نوعها
- XLOOKUP
- XMATCH
تتوفر صيغ الصفيف الديناميكية فقط في "Excel 365" وهي غير مدعومة حاليًا بواسطة أي من برامج Excel غير المتصلة بالإنترنت (مثل Microsoft Excel 2016 ، 2019).
لا تنتج أخطاء الانسكاب فقط عن إعاقة البيانات ، فهناك العديد من الأسباب التي تجعلك تحصل على خطأ #Spill. دعنا نستكشف المواقف المختلفة التي قد تواجه فيها #SPILL! خطأ وكيفية اصلاحها.
نطاق الانسكاب ليس فارغًا
أحد الأسباب الرئيسية لخطأ الانسكاب هو أن نطاق الانسكاب غير فارغ. على سبيل المثال ، إذا كنت تحاول عرض 10 نتائج ، ولكن إذا كان هناك أي بيانات في أي من الخلايا في منطقة الانسكاب ، فإن الصيغة ترجع الخطأ #SPILL! خطأ.
مثال 1:
في المثال أدناه ، أدخلنا وظيفة TRANSPOSE في الخلية C2 لتحويل النطاق الرأسي للخلايا (B2: B5) إلى نطاق أفقي (C2: F2). بدلاً من تبديل العمود إلى صف ، يوضح لنا Excel الخطأ #SPILL! خطأ.
وعند النقر فوق خلية الصيغة ، سترى حدًا أزرق متقطع يشير إلى منطقة / نطاق الانسكاب (C2: F2) المطلوب لعرض النتائج كما هو موضح أدناه. ستلاحظ أيضًا علامة تحذير صفراء عليها علامة تعجب.
لفهم سبب الخطأ ، انقر فوق رمز التحذير بجوار الخطأ وشاهد الرسالة في السطر الأول مظللة باللون الرمادي. كما ترى ، تقول "نطاق الانسكاب ليس فارغًا" هنا.
تكمن المشكلة هنا في أن الخلايا الموجودة في نطاق الانسكاب D2 و E2 تحتوي على أحرف نصية (ليست فارغة) ، وبالتالي الخطأ.
المحلول:
الحل بسيط ، إما مسح البيانات (إما نقلها أو حذفها) الموجودة في نطاق الانسكاب أو نقل الصيغة إلى مكان آخر حيث لا يوجد عائق.
بمجرد حذف أو نقل الحظر ، سيقوم Excel تلقائيًا بتعبئة الخلايا بنتائج الصيغة. هنا ، عندما نقوم بمسح النص في D2 و E2 ، تنقل الصيغة العمود إلى صف على النحو المنشود.
المثال 2:
في المثال أدناه ، على الرغم من أن نطاق الانسكاب يبدو فارغًا ، إلا أن الصيغة لا تزال تعرض Spill! خطأ. يرجع السبب في ذلك إلى أن الانسكاب ليس فارغًا في الواقع ، وله حرف مسافة غير مرئي في إحدى الخلايا.
من الصعب تحديد موقع أحرف المسافات أو أي حرف آخر غير مرئي يختبئ فيما يبدو أنه خلايا فارغة. للعثور على هذه الخلايا التي تحتوي على بيانات غير مرغوب فيها ، انقر فوق عوامة الخطأ (علامة التحذير) وحدد "تحديد الخلايا المعوقة" من القائمة وسيأخذك إلى الخلية التي تحتوي على البيانات المعوقة.
كما ترى ، في لقطة الشاشة أدناه ، تحتوي الخلية E2 على حرفين مسافتين. عندما تمسح هذه البيانات ، ستحصل على المخرجات المناسبة.
في بعض الأحيان ، قد يكون الحرف غير المرئي عبارة عن نص منسق بنفس لون الخط مثل لون تعبئة الخلية أو قيمة خلية منسقة برمز الرقم ؛؛؛. عندما تقوم بتخصيص تنسيق قيمة خلية باستخدام ؛؛؛ فسوف تخفي أي شيء في تلك الخلية ، بغض النظر عن لون الخط أو لون الخلية.
يحتوي نطاق الانسكاب على خلايا مدمجة
في بعض الأحيان ، #SPILL! يحدث الخطأ عندما يحتوي نطاق الانسكاب على الخلايا المدمجة. لا تعمل صيغة الصفيف الديناميكي مع الخلايا المدمجة. لإصلاح ذلك ، كل ما عليك فعله هو إلغاء دمج الخلايا في نطاق الانسكاب أو نقل الصيغة إلى نطاق آخر لا يحتوي على خلايا مدمجة.
في المثال أدناه ، على الرغم من أن نطاق الانسكاب فارغ (C2: CC8) ، تقوم الصيغة بإرجاع خطأ Spill. هذا بسبب دمج الخلايا C4 و C5.
للتأكد من أن الخلايا المدمجة هي سبب حصولك على الخطأ ، انقر علىعلامة تحذير وتحقق من السبب - "نطاق الانسكاب به خلية مدمجة".
المحلول:
لإلغاء دمج الخلايا ، حدد الخلايا المدمجة ، ثم في علامة التبويب "الصفحة الرئيسية" ، انقر فوق الزر "دمج وتوسيط" وحدد "إلغاء دمج الخلايا".
إذا كنت تواجه صعوبة في تحديد موقع الخلايا المدمجة في جدول البيانات الكبير ، فانقر على خيار "تحديد الخلايا المعوقة" من قائمة علامة التحذير للانتقال إلى الخلايا المدمجة.
نطاق الانسكاب في الجدول
صيغ الصفيف المنسكبة غير معتمدة في جداول Excel. يجب إدخال صيغة الصفيف الديناميكية في خلية فردية واحدة فقط. إذا أدخلت صيغة صفيف مسكوب في جدول أو عندما تقع منطقة الانسكاب في جدول ، فستتلقى خطأ الانسكاب. عندما يحدث هذا ، حاول تحويل الجدول إلى نطاق عادي أو انقل الصيغة خارج الجدول.
على سبيل المثال ، عندما ندخل صيغة النطاق المنسكب التالية في جدول Excel ، فسنحصل على خطأ Spill في كل خلية من الجدول ، وليس خلية الصيغة فقط. ذلك لأن Excel ينسخ تلقائيًا أي صيغة يتم إدخالها في جدول إلى كل خلية في عمود الجدول.
أيضًا ، ستحصل على خطأ انسكاب عندما تحاول الصيغة انسكاب النتائج في جدول. في لقطة الشاشة أدناه ، تقع منطقة الانسكاب داخل الجدول الحالي ، لذلك حصلنا على خطأ انسكاب.
لتأكيد السبب وراء هذا الخطأ ، انقر فوق علامة التحذير واطلع على سبب الخطأ - "نطاق الانسكاب في الجدول"
المحلول:
لإصلاح الخطأ ، ستحتاج إلى إعادة جدول Excel إلى النطاق. للقيام بذلك ، انقر بزر الماوس الأيمن في أي مكان داخل الجدول ، وانقر على "جدول" ، ثم حدد الخيار "تحويل إلى نطاق". بدلاً من ذلك ، يمكنك النقر بزر الماوس الأيسر في أي مكان داخل الجدول ، ثم الانتقال إلى علامة التبويب "تصميم الجدول" وتحديد الخيار "تحويل إلى نطاق".
نطاق الانسكاب غير معروف
إذا لم يتمكن Excel من تحديد حجم الصفيف المنسكب ، فسيؤدي ذلك إلى ظهور خطأ Spill. في بعض الأحيان ، تتيح الصيغة لصفيف ديناميكي تغيير حجمه بين كل تمريرات حسابية. إذا استمر حجم المصفوفة الديناميكية في التغير أثناء العمليات الحسابية ولم يتوازن ، فسيؤدي ذلك إلى حدوث خطأ #SPILL! خطأ.
عادةً ما يتم تشغيل هذا النوع من أخطاء الانسكاب عند استخدام وظائف متغيرة مثل وظائف RAND و RANDARRAY و RANDBETWEEN و OFFSET و INDIRECT.
على سبيل المثال ، عندما نستخدم الصيغة التالية في الخلية B3 ، نحصل على خطأ Spill:
= SEQUENCE (RANDBETWEEN (1 ، 500))في المثال ، ترجع الدالة RANDBETWEEN عددًا صحيحًا عشوائيًا بين الرقمين 1 و 500 ، ويتغير ناتجها باستمرار. ولا تعرف الدالة SEQUENCE عدد القيم التي يجب إنتاجها في مصفوفة انسكاب. ومن ثم ، الخطأ #SPILL.
يمكنك أيضًا تأكيد سبب الخطأ بالنقر فوق علامة التحذير - "نطاق الانسكاب غير معروف".
المحلول:
لإصلاح الخطأ في هذه الصيغة ، يكون خيارك الوحيد هو استخدام صيغة مختلفة لحسابك.
نطاق الانسكاب كبير جدًا
في بعض الأحيان ، يمكنك تنفيذ صيغة تُخرج نطاقًا مسدودًا أكبر من أن تتعامل معه ورقة العمل ، وقد يمتد إلى ما وراء حواف ورقة العمل. عندما يحدث ذلك قد تحصل على #SPILL! خطأ. لإصلاح هذه المشكلة ، يمكنك محاولة الإشارة إلى نطاق معين أو خلية واحدة بدلاً من الأعمدة بأكملها أو استخدام الحرف "@" لتمكين التقاطع الضمني
في المثال أدناه ، نحاول حساب 20٪ من أرقام المبيعات في العمود A وإرجاع النتائج في العمود B ، ولكن بدلاً من ذلك ، حصلنا على خطأ Spill.
الصيغة في B3 تحسب 20٪ من القيمة في A3 ، ثم 20٪ من القيمة في A4 ، وهكذا. ينتج عنه أكثر من مليون نتيجة (1،048،576) وينسكبها جميعًا في العمود B بدءًا من الخلية B3 ، ولكنها ستصل إلى نهاية ورقة العمل. لا توجد مساحة كافية لعرض جميع المخرجات ، ونتيجة لذلك ، حصلنا على خطأ #SPILL.
كما ترى سبب هذا الخطأ هو - "نطاق الانسكاب كبير جدًا".
حلول:
لحل هذه المشكلة ، حاول تغيير العمود بأكمله بنطاق ذي صلة أو مرجع أحادي الخلية ، أو أضف عامل التشغيل @ لإجراء تقاطع ضمني.
الإصلاح 1: يمكنك محاولة إحالة النطاقات بدلاً من الأعمدة بأكملها. هنا ، نقوم بتغيير النطاق بالكامل A: A مع A3: A11 في الصيغة ، وستقوم الصيغة تلقائيًا بملء النطاق بالنتائج.
الإصلاح 2: استبدل العمود بأكمله بمرجع الخلية فقط في نفس الصف (A3) ، ثم انسخ الصيغة إلى أسفل النطاق باستخدام مقبض التعبئة.
الإصلاح 3: يمكنك أيضًا محاولة إضافة عامل التشغيل @ قبل المرجع لإجراء تقاطع ضمني. سيعرض هذا الإخراج في خلية الصيغة فقط.
ثم انسخ الصيغة من الخلية B3 إلى باقي النطاق.
ملحوظة: عندما تقوم بتحرير صيغة مسكوبة ، يمكنك فقط تحرير الخلية الأولى في منطقة / نطاق الانسكاب. يمكنك رؤية الصيغة في الخلايا الأخرى في نطاق الانسكاب ، ولكنها ستتحول إلى اللون الرمادي ولا يمكن تحديثها.
خارج الذاكرة
إذا قمت بتنفيذ صيغة صفيف متداخلة تتسبب في نفاد ذاكرة Excel ، فقد يؤدي ذلك إلى تشغيل الخطأ #SPILL. في ظل هذه الظروف ، حاول الرجوع إلى مصفوفة أو نطاق أصغر.
غير معروف / احتياطي
يمكنك أيضًا الحصول على خطأ Spill حتى عندما لا يتعرف Excel على سبب الخطأ أو يتعذر عليه التوفيق بينها. في مثل هذه الحالات ، تحقق مرة أخرى من الصيغة وتأكد من صحة جميع معلمات الوظائف.
الآن ، أنت تعرف كل أسباب وحلول #SPILL! أخطاء في Excel 365.