لا شيء يعيق سير العمل أكثر من إدخال بيانات جديدة ثم اكتشاف أن جدول Excel الخاص بك ليس كبيرًا بما يكفي. اعتدت على سحب تلك الحواف باستمرار لتوسيع الجدول يدويًا – إلى أن تعلمت هذه الحيلة البسيطة التي تجعل جداولي تتوسع تلقائيًا، مما يوفر الوقت والجهد ويضمن تحديث الجداول باستمرار.
روابط سريعة
المصفوفات الديناميكية في Excel: الطريقة المثلى لتوسيع الجداول تلقائيًا
بدلًا من إرجاع قيمة واحدة فقط، تقوم المصفوفات الديناميكية في Excel “بإخراج” نتائجها تلقائيًا عبر نطاق من الخلايا المتجاورة، دون الحاجة إلى تحديد النطاق الدقيق مسبقًا. هذه الخاصية تجعلها مثالية لإنشاء جداول ذاتية التوسّع والتحديث.
تتضمن الأمثلة الشائعة دوال مثل UNIQUE و SORT و FILTER. هذه الدوال تُرجع مصفوفات ديناميكية تتوسع أو تتقلص تلقائيًا بناءً على البيانات المصدر. عند إضافة إدخالات جديدة إلى مجموعة البيانات الخاصة بك، يتم تحديث النتائج على الفور دون أي تدخل يدوي. هذا يوفر الوقت ويقلل من الأخطاء المحتملة.
عند إدخال صيغة مصفوفة ديناميكية في خلية واحدة، يقوم Excel تلقائيًا بملء الخلايا المجاورة اللازمة لعرض جميع النتائج. ستعرف أن الخاصية تعمل بشكل صحيح عندما ترى حدًا أزرق يحيط بهذا النطاق. محاولة الكتابة في منطقة “الانسكاب” هذه ستؤدي إلى ظهور خطأ #SPILL!، وهو بمثابة آلية حماية مفيدة تمنع الكتابة فوق البيانات الديناميكية.
إن استخدام المصفوفات الديناميكية ليس مجرد ميزة مريحة، بل هو أيضًا موثوق للغاية، حيث أن إدارة الجداول يدويًا يمكن أن يؤدي إلى أخطاء، وفقدان البيانات، والإحباط. باستخدام المصفوفات الديناميكية، يمكنك تبسيط عملية إدارة البيانات في Excel وزيادة كفاءتك.
كيف أقوم بإنشاء قائمة ذاتية التوسع بالعناصر الفريدة باستخدام دالة UNIQUE في Excel؟
تُعد دالة UNIQUE في Excel من بين الدوال التي توفر عليك الكثير من الجهد. فبدلاً من الفحص اليدوي للقوائم، أستخدم هذه الدالة لاستخراج القيم المميزة من البيانات تلقائيًا، مما يسرع عملية تحليل البيانات ويقلل الأخطاء.
إليك الصيغة الأساسية للدالة UNIQUE:
المعامل array (النطاق) يحتوي على بيانات المصدر، مثل عمود لأقسام الموظفين أو أسماء العملاء. أما المعامل by_col (بواسطة_العمود) (TRUE أو FALSE) فيحدد ما إذا كنت تريد المقارنة بواسطة الأعمدة أو الصفوف، بينما يقوم المعامل exactly_once (مرة_واحدة_بالضبط) بتصفية القيم التي تظهر مرة واحدة فقط، وهو مفيد لتحديد الحالات النادرة أو الاستثنائية في البيانات.
لنفترض أنك تعمل على جدول بيانات للموظفين وتحتاج إلى قائمة نظيفة بجميع الأقسام، يمكنك ببساطة إدخال الصيغة التالية:
هنا، يحتوي العمود R على أسماء الأقسام من الصفوف 2 إلى 3004. يقوم Excel على الفور بإنشاء قائمة ديناميكية بالأقسام الفريدة التي يتم تحديثها تلقائيًا كلما انضم شخص إلى فريق جديد. هذه الطريقة تضمن لك الحصول على معلومات محدثة باستمرار دون الحاجة إلى تدخل يدوي.
هذا يتفوق أيضًا على الطريقة التقليدية لإزالة التكرارات في Excel لأن المصفوفات الديناميكية تظل متصلة ببيانات المصدر الخاصة بك. على النقيض من ذلك، فإن الإزالة اليدوية للتكرارات تنشئ قوائم ثابتة تصبح قديمة بمجرد إضافة إدخالات جديدة. باستخدام دالة UNIQUE، يمكنك التأكد من أن تحليلاتك تعتمد على أحدث البيانات المتاحة.
بالنسبة لسيناريوهات exactly_once (مرة_واحدة_بالضبط)، يمكنك استخدام الصيغة التالية للعثور على الأقسام التي لديها موظف واحد فقط. إنه يعمل بشكل جيد لتحديد الفرق التي تعاني من نقص في عدد الموظفين أو الأدوار الفريدة في مؤسستك:
يمكنني أيضاً فرز قوائمي الديناميكية تلقائياً
تتجاوز الدالة SORT في برنامج Excel مجرد إنشاء نطاقات ديناميكية، فهي تسمح بفرز البيانات تلقائياً. بالعودة إلى مثال بيانات الموظفين، بدلاً من ترتيب أسماء الموظفين أو بيانات الرواتب يدوياً، يتولى Excel عملية الفرز تلقائياً بمجرد تغيير البيانات. فيما يلي الصيغة العامة للدالة SORT:
المعامل array يمثل نطاق البيانات المراد فرزها، بينما يحدد المعامل sort_index رقم العمود الذي سيتم الفرز بناءً عليه. أما المعامل sort_order فيحدد ترتيب الفرز، حيث يشير الرقم 1 إلى الترتيب التصاعدي، والرقم -1 إلى الترتيب التنازلي. وأخيراً، يحدد المعامل by_col ما إذا كان الفرز سيتم بناءً على الأعمدة (TRUE) أو الصفوف (FALSE).
للحصول على نتائج أفضل، يمكن دمج الدالة SORT مع الدالة UNIQUE. على سبيل المثال، ستعرض الصيغة التالية قائمة بأسماء الأقسام الفريدة من سجل الموظفين، مرتبة ترتيباً أبجدياً. وعندما يضيف قسم الموارد البشرية أقساماً جديدة، ستظهر تلقائياً في مكانها الصحيح في القائمة الأبجدية.
إذا أردنا إجراء تحليل للرواتب، يمكننا استخدام الصيغة التالية:
تقوم الصيغة المذكورة أعلاه بترتيب بيانات الموظفين حسب الراتب بترتيب تنازلي. يشير العمود رقم 8 إلى الرواتب، ويشير الرقم -1 إلى الفرز من الأعلى إلى الأدنى.
تجدر الإشارة إلى أن الدالة SORT حساسة لحالة الأحرف، وتعامل الأرقام المخزنة كنصوص بشكل مختلف عن الأرقام الفعلية. لضمان دقة الفرز، يجب التأكد من أن أنواع البيانات متسقة.
يمكنك الاطلاع على دليلنا حول الدالة SORT في Excel للحصول على مزيد من الأمثلة. الهدف هو الحصول على تحديثات تلقائية، بحيث يتم تحديث القوائم التي تم فرزها على الفور دون أي تدخل يدوي.
دالة FILTER: أداتي المفضلة لإنشاء تقارير ديناميكية في Excel
إذا لم يسبق لك استخدام دالة FILTER في برنامج Excel، فأنت تفوت فرصة عظيمة. تعتبر دالة FILTER من أقوى الأدوات لإنشاء تقارير ديناميكية متقدمة. يمكنك استخدامها لعرض البيانات التي تطابق معايير محددة تلقائيًا، مما يغنيك عن إنشاء نسخ ثابتة من البيانات التي سرعان ما تصبح قديمة وغير دقيقة.
الصيغة العامة لدالة FILTER هي:
يشير مصطلح array إلى نطاق البيانات الكامل الذي تريد تصفحته، بينما تحدد include المعايير التي يجب أن تستوفيها البيانات المراد عرضها. أما if_empty، فيتيح لك عرض رسالة مخصصة في حال عدم وجود أي نتائج تطابق المعايير المحددة.
أستخدم هذه الدالة باستمرار في إعداد تقارير متنوعة. على سبيل المثال، إذا أردنا عرض قائمة بجميع أعضاء فريق المبيعات من قاعدة بيانات الموظفين، يمكننا استخدام الدالة FILTER بالطريقة التالية:
عندما ينتقل موظف ما إلى قسم المبيعات، سيظهر تلقائيًا في النتائج التي تم تصفيتها. وبالمثل، لتحليل الرواتب، يمكننا استخدام الصيغة التالية لعرض الموظفين الذين يكسبون أكثر من 50,000 دولار:
إذا حصل موظف ما على ترقية وزيادة في الراتب، فسيظهر فجأة في تقرير أصحاب الدخول المرتفعة هذا. بالإضافة إلى ذلك، يمكننا أيضًا دمج معايير متعددة:
تعرض الصيغة أعلاه أعضاء فريق المبيعات الذين يكسبون أكثر من 50,000 دولار. تعمل علامة النجمة (*) كعامل “AND”، مما يعني أنه يجب استيفاء كلا الشرطين لكي يتم عرض النتيجة.
ترجع دالة FILTER الخطأ #CALC! إذا لم تتطابق أي نتائج مع المعايير المحددة. استخدم المعامل if_empty لعرض الرسالة “لم يتم العثور على نتائج” بدلاً من ذلك.
تثبت المصفوفات الديناميكية فعاليتها لأنها تلغي الحاجة إلى تحديثات الجداول اليدوية وتقضي على إمكانية فقدان بعض الإدخالات. يصبح Excel أكثر سهولة وبديهية بمجرد البدء في استخدام دوال UNIQUE و SORT و FILTER معًا.
