أحب استخدام طاولات Excel ، لكني أتمنى أن تكون Microsoft قد تم إصلاح مشكلة رئيسية واحدة

لقد كتبت عدة مرات حول العديد من فوائد تنسيق بياناتك كجدول منظم في Microsoft Excel. ومع ذلك ، على الرغم من ذلك ، هناك قضية واحدة رئيسية تستمر في إلقاء مفتاح على الأعمال – لا تتوافق جداول Excel مع صيغ الصفيف الديناميكية.

المشكلة: صيغ الصفيف الديناميكية لا تعمل في طاولات Excel

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

متعلق ب

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

لا يستحق البكاء على المراجع المنسكبة.

على سبيل المثال ، الكتابة:

=UNIQUE(A2:A21)

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

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

متعلق ب

كل ما تحتاج لمعرفته حول جداول Excel (ولماذا يجب عليك دائمًا استخدامها)

هذا يمكن أن يغير تماما كيف تعمل في Excel.

كل من جداول Excel وصيغ الصفيف الديناميكي هي حاويات لتوسيع البيانات تلقائيًا. لذلك ، إذا حاولت وضع شيء واحد يتوسع تلقائيًا (مثل صيغة صفيف ديناميكية) داخل شيء آخر يوسع تلقائيًا (مثل جدول Excel) ، لا يمكن لـ Excel أن يحدد أي من الاثنين يجب أن يملي حجم مجموعة البيانات.

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

الحل 1: قم بتحويل الجدول إلى نطاق غير مهذب

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

الآن ، نظرًا لأن البيانات لم تعد في جدول Excel منظم ، يمكنك استخدام صيغ الصفيف الديناميكية لمحتوى قلبك.

ومع ذلك ، على الرغم من أنه يمكنك إعادة تنشيط أزرار التصفية عن طريق الضغط على CTRL+Shift+L ، فإن النطاقات غير المطوبة ليس لديها نفس القدرات مثل جداول Excel. على سبيل المثال ، لا يمكنك الرجوع إلى رؤوس الأعمدة في الصيغ ، ولا تملك النطاقات العادية نفس السعة المتوسطة التلقائية كجداول Excel.

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

متعلق ب

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

لا تطبق التنسيق المباشر.

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

الحل 2: استخدم وظائف بديلة

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

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

توليد قائمة ديناميكية مرقمة

في هذا المثال ، الكتابة:

=SEQUENCE(COUNTA(B:B)-1,1,1)

في الخلية A2 يحسب عدد الخلايا غير الخاضعة للخلايا في العمود B ، ناقص واحد لحساب صف الرأس ، وإنشاء عمود واحد من الأرقام المتسلسلة ، بدءا من 1. ما هو أكثر ، إذا تمت إضافة البيانات أو إزالتها من العمود ب ، سيتم تحديث العدد في العمود أ تلقائيا.

متعلق ب

لا تنشئ قوائم مرقمة يدويًا في Excel: استخدم التسلسل و Counta بدلاً من ذلك

هذه الصيغة البسيطة ستوفر لك الكثير من الوقت.

للحصول على نفس النتيجة في جدول Excel دون استخدام صيغة صفيف ديناميكية ، في الخلية A2 ، النوع:

=ROW()-ROW(TableX((#Headers),(Name)))

إنشاء مجموعة من الأرقام العشوائية

في هذا النطاق غير المطاف ، الكتابة:

=RANDARRAY(20,1,50,100,TRUE)

في الخلية A2 إرجاع قائمة عشوائية من الأرقام الكاملة بين 50 و 100 في الخلايا A2 إلى A21.

متعلق ب

كيفية إنشاء أرقام عشوائية في Microsoft Excel

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

لفعل الشيء نفسه في جدول منسق ، في الخلية A2 ، النوع:

=RANDBETWEEN(50,100)

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

تقسيم خلية إلى أعمدة منفصلة

في هذا المثال ، يأخذ Sextsplit الاسم في الخلية A2 ، ويسرب النسخة المقسمة من كل اسم إلى خلايا B2 و C2 ، مع سلسلة الفضاء الفاصلة كحدد. بعد ذلك ، يطبق تحديد الخلية B2 والنقر المزدوج على مقبض التعبئة صيغة الصفيف الديناميكية على الخلايا المتبقية في النطاق.

=TEXTSPLIT(A2,", ")

عند العمل مع جداول Excel ، بدلاً من استخدام صيغة الصفيف الديناميكية هذه ، يمكنك استخدام TextBefore و Textafter.

في الخلية B2 ، اكتب:

=TEXTBEFORE((@Name),",")

لاستخراج النص قبل الفاصلة من عمود الاسم.

ثم ، في الخلية C2 ، اكتب:

=TEXTAFTER((@Name)," ")

لاستخراج النص بعد الفضاء.

متعلق ب

4 طرق لتقسيم البيانات إلى أعمدة متعددة في Microsoft Excel

أنت مدلل للاختيار!

في هذا النطاق العادي ، تسرد هذه الصيغة الفريدة جميع القيم الفريدة في الخلايا A2 إلى A50.

=UNIQUE(A2:.A50)

متعلق ب

كيفية إدراج وفرز القيم والنص الفريد في Microsoft Excel

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

لتحقيق نتيجة مماثلة في جدول Excel ، في الخلية B2 ، النوع:

=INDEX(UNIQUE($A$1:$A$50),ROW(A2))

أين

• فِهرِس() إرجاع قيمة ،
• فريدة من نوعها ($ 1 $: $ A 50 $)، وهي الوسيطة الأولى لصيغة الفهرس ، تجد قيمًا فريدة في الخلايا A1 إلى A50 ، و
• صف (A2)، وهي الوسيطة الثانية لصيغة الفهرس ، تُرجع نالقيمة الفريدة ، حيث ن هو رقم الصف للخلية النشطة.

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

ثم ، في خلية منفصلة ، اكتب:

=COUNTA(UNIQUE($A$2:.$A$50))

لحساب القيم الفريدة في نطاق المصدر.

الآن ، إذا لم يتطابق العدد في الصف الإجمالي للجدول مع العدد في خلية Counta-Unique ، فأنت تعلم أنك بحاجة إلى توسيع حجم الجدول الخاص بك أو تقليله وفقًا لذلك.

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