كل ما تحتاج لمعرفته حول الانسكاب في Excel

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

التعاريف التي يجب أن تعرفها أولاً

قبل أن أعرض لك أمثلة على السكب أثناء العمل، إليك تعريفات بعض مصطلحات Excel ذات الصلة في هذا الدليل:

• سفك: عندما تقوم صيغ Excel بإرجاع مجموعة من القيم (المعروفة أيضًا باسم المصفوفة).
• صفيف: مجموعة من البيانات ضمن نطاق من الخلايا. على سبيل المثال، إذا كانت الخلايا من A1 إلى A20 تحتوي على بيانات، فإن A1:A20 هو النطاق، والبيانات الموجودة ضمن هذا النطاق هي الصفيف.
• وظيفة: صيغة محددة مسبقًا تقوم بإجراء عملية حسابية عند استخدامها في صيغة.
• صيغة: مجموعة من الوظائف ومراجع الخلايا والقيم وعوامل التشغيل والأسماء التي عند استخدامها معًا بعد علامة التساوي (=) تؤدي إلى إرجاع نتيجة
• صيغة الصفيف الديناميكي: صيغة قادرة على إرجاع صفيف.
• صيغة الصفيف الممتد: صيغة تقوم حاليًا بإرجاع صفيف ممتد.

أمثلة على الانسكاب

أولاً، دعونا نلقي نظرة على مثال مباشر للتسرب في برنامج Excel. يحتوي جدول بيانات Excel هذا على ستة فرق اختبار ونتائجهم للأسابيع من الأول إلى الخامس.

لأن

=B2:B7

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

ومع ذلك، أريد الآن الاستفادة بشكل عملي أكثر من هذا النوع من النتائج من خلال باستخدام وظيفة الأوفست. في هذه الحالة، أريد من برنامج Excel أن يخبرني بما سجله كل فريق في أسبوع معين، اعتمادًا على الرقم الذي أكتبه في الخلية I1.

للقيام بذلك، سأكتب في الخلية I2

=OFFSET(A2,0,I1,6,1)

لأنني أريد أن يبدأ برنامج Excel من الخلية A2، وأن يظل في نفس الصف، ويتحرك عبر عدد الأعمدة التي كتبتها في الخلية I1، ويعيد نتيجة تكون على بعد 6 صفوف لأسفل وعمود واحد عرضيًا. نظرًا لأن Excel يُرجع مصفوفة يبلغ ارتفاعها 6 صفوف، على الرغم من أنني كتبت الصيغة في الخلية I2 فقط، فإن النتيجة تنتقل من الخلايا I1 إلى I7.

لترتيب جدول استرجاع البيانات الخاص بي، أريد أيضًا إضافة أسماء الفرق إلى العمود H. وللقيام بذلك، سأكتب

=A2:A7

في الخلية H2، والتي تنتج أيضًا مصفوفة ممتدة. لقد قمت أيضًا بتطبيق بعض التنسيق لتحسين إمكانية قراءة جدول البيانات.

في المثال الأخير، سأستخدم الدالة XLOOKUP لإنتاج مصفوفة ممتدة. هدفي هو عرض مجموعة النتائج الخاصة بفريق معين على مدار فترة الخمسة أسابيع بأكملها، مع اعتماد الفريق المعني على القيمة الموجودة في الخلية I1. أولاً، سأكتب صيغة المصفوفة الديناميكية

=B1:F1

في الخلية H3 لإنتاج المصفوفة الممتدة لأرقام الأسابيع في الخلايا من H3 إلى L3.

الآن، سأقوم بإنشاء قائمة منسدلة في الخلية I1 تحتوي على أسماء الفرق المدرجة في الخلايا من A2 إلى A7. للقيام بذلك، سأقوم بالنقر فوق البيانات > التحقق من صحة البيانات، واختيار “قائمة” في الحقل “السماح”، وتحديد الخلايا من A2 إلى A7 لتحديد مصدر القائمة، قبل النقر فوق “موافق”.

وأخيرًا، يمكنني استخدام وظيفة XLOOKUP لإرجاع النتائج لفريقي المحدد خلال الأسابيع الخمسة. في الخلية H3 سأكتب:

=XLOOKUP(I1,A2:A7,B2:F7)

لأنني أريد أن يأخذ Excel اسم الفريق في الخلية I1، ويطابقه مع اسم فريق في النطاق من A2 إلى A7، ويعيد المصفوفة المقابلة من الأعمدة B إلى F. وبما أن النتيجة بعرض خمسة أعمدة، يتم عرضها كـ المصفوفة الممتدة عبر الخلايا من H3 إلى L3. في هذه الحالة، قمت باختيار فريق Quizpicable Me من القائمة المنسدلة في الخلية I1.

تتضمن الوظائف الأخرى التي عادةً ما تؤدي إلى مصفوفة ممتدة FILTER، وSORT، وUNQUE، وRANDARRAY.

الإشارة إلى النطاق المسكوب

للإشارة إلى نطاق ممتد، استخدم عامل تشغيل النطاق الممتد (#) بعد النطاق داخل صيغة الصفيف الديناميكي. باستخدام نفس المثال المذكور أعلاه، أريد عرض النتيجة الإجمالية لفريق معين بناءً على المصفوفة الممتدة في الخلايا من H3 إلى L3.

لذلك، الكتابة

=SUM(H3#)

في الخلية L4 يخبر Excel بجمع المصفوفة في النطاق الممتد بدءًا من H3.

إصلاح #SPILL المشترك! أخطاء

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

إذا كانت صيغة الصفيف الديناميكي تشير إلى عمود كامل، فيمكنك استخدام الدالة TRIMRANGE لإخبار Excel بخصم الصفوف الفارغة، مما يعني بشكل أساسي قطع البيانات لتشمل الخلايا الضرورية فقط.