إزالة الصفوف والأعمدة الفارغة من البيانات

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

لنلق نظرة على عدة طرق لحل هذه المشكلة.

الطريقة الأولى. ابحث عن الخلايا الفارغة

قد لا يكون هذا هو الأكثر ملاءمة ، ولكن بالتأكيد أسهل طريقة جديرة بالذكر.

لنفترض أننا نتعامل مع مثل هذا الجدول الذي يحتوي على العديد من الصفوف والأعمدة الفارغة بداخله (مظللة للتوضيح):

لنفترض أننا على يقين من أن العمود الأول من جدولنا (العمود B) يحتوي دائمًا على اسم مدينة. ثم ستكون الخلايا الفارغة في هذا العمود علامة على وجود صفوف فارغة غير ضرورية. لإزالتها جميعًا بسرعة ، قم بما يلي:

1. اختر النطاق مع المدن (B2: B26)
2. اضغط على المفتاح F5 ثم اضغط تسليط الضوء (اذهب إلى خاص) أو حدد في علامة التبويب الصفحة الرئيسية - بحث وتحديد - حدد مجموعة من الخلايا (الصفحة الرئيسية - بحث وتحديد - انتقال إلى خاص).
3. في النافذة التي تفتح ، حدد الخيار خلايا فارغة (فراغات) والصحافة OK - يجب تحديد جميع الخلايا الفارغة في العمود الأول من جدولنا.
4. الآن حدد في علامة التبويب الصفحة الرئيسية أمر حذف - حذف الصفوف من الورقة (حذف - حذف الصفوف) أو اضغط على اختصار لوحة المفاتيح CTRL+ناقص - وتحل مهمتنا.

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

الطريقة 2: البحث عن الصفوف الفارغة

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

ألق نظرة على الجدول التالي ، على سبيل المثال ، لمثل هذه الحالة فقط:

هنا سيكون النهج أصعب قليلاً:

1. أدخل الوظيفة في الخلية A2 بالإحصاء (كونتا)، والتي ستحسب عدد الخلايا المملوءة في الصف إلى اليمين ونسخ هذه الصيغة لأسفل إلى الجدول بأكمله:
2. حدد الخلية A2 وقم بتشغيل عامل التصفية باستخدام الأمر البيانات - تصفية (بيانات - تصفية) أو اختصار لوحة المفاتيح CTRL+تغير+L.
3. دعنا نصفي الأصفار حسب العمود المحسوب ، أي جميع الصفوف التي لا توجد بها بيانات.
4. يبقى تحديد الخطوط التي تمت تصفيتها وحذفها باستخدام الأمر الصفحة الرئيسية - حذف - "حذف الصفوف من الورقة (الصفحة الرئيسية - حذف - حذف الصفوف) أو اختصار لوحة المفاتيح CTRL+ناقص.
5. نقوم بإيقاف تشغيل الفلتر والحصول على بياناتنا بدون أسطر فارغة.

لسوء الحظ ، لم يعد من الممكن تنفيذ هذه الحيلة باستخدام الأعمدة - لم يتعلم Excel بعد كيفية التصفية حسب الأعمدة.

الطريقة الثالثة. ماكرو لإزالة كافة الصفوف والأعمدة الفارغة من الورقة

يمكنك أيضًا استخدام ماكرو بسيط لأتمتة هذه المهمة. اضغط على اختصار لوحة المفاتيح قديم+F11 أو حدد من علامة التبويب المطور - البصرية الأساسية (المطور - محرر Visual Basic). إذا كانت علامات التبويب المطور غير مرئي ، يمكنك تمكينه من خلال ملف - خيارات - إعداد الشريط (ملف - خيارات - تخصيص الشريط).

في نافذة محرر Visual Basic التي تفتح ، حدد أمر القائمة إدراج - وحدة وفي الوحدة الفارغة التي تظهر ، انسخ والصق الأسطر التالية:

   Sub DeleteEmpty () Dim r طويل ، rng كنطاق 'удаляем пустые строки لـ r = 1 إلى ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA (Rows (r)) = 0 ثم إذا rng ليس شيئًا ، ثم عيّن rng = Rows (r) Else Set rng = Union (rng ، Rows (r)) End If Next r إذا لم يكن rng شيئًا ، ثم rng.Delete 'удаляем пустые столбцы تعيين rng = لا شيء لـ r = 1 إلى ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA (Columns (r)) = 0 ثم إذا كان rng لا شيء ، فقم بتعيين rng = Columns (r) Else Set rng = Union (rng، Columns ( r)) End If Next r If Not rng Is Nothing ثم rng.Delete End Sub  

أغلق المحرر وارجع إلى Excel. 

الآن ضرب الجمع قديم+F8 أو زر وحدات الماكرو علامة التبويب المطور. ستعرض النافذة التي تفتح قائمة بجميع وحدات الماكرو المتاحة حاليًا للتشغيل ، بما في ذلك الماكرو الذي أنشأته للتو. حذف فارغ. حدده وانقر فوق الزر يجري (يجري) - سيتم حذف جميع الصفوف والأعمدة الفارغة من الورقة على الفور.

الطريقة الثالثة: Power Query

هناك طريقة أخرى لحل مشكلتنا والسيناريو الشائع جدًا وهو إزالة الصفوف والأعمدة الفارغة في Power Query.

أولاً ، لنقم بتحميل جدولنا في محرر Power Query Query. يمكنك تحويله إلى "ذكي" ديناميكي باستخدام اختصار لوحة المفاتيح Ctrl + T أو تحديد نطاق البيانات لدينا ومنحه اسمًا (على سبيل المثال البيانات) في شريط الصيغة ، التحويل إلى الاسم:

نستخدم الآن بيانات الأمر - الحصول على البيانات - من الجدول / النطاق (البيانات - الحصول على البيانات - من الجدول / النطاق) وتحميل كل شيء في Power Query:

ثم كل شيء بسيط:

1. نحذف الأسطر الفارغة باستخدام الأمر Home - تقليل الأسطر - حذف الأسطر - حذف الأسطر الفارغة (الصفحة الرئيسية - إزالة الصفوف - إزالة الصفوف الفارغة).
2. انقر بزر الماوس الأيمن فوق عنوان عمود المدينة الأول وحدد أمر Unpivot Other Columns من قائمة السياق. سيكون طاولتنا ، كما يطلق عليها تقنيًا بشكل صحيح ، تطبيع - تم تحويلها إلى ثلاثة أعمدة: المدينة والشهر والقيمة من تقاطع المدينة والشهر من الجدول الأصلي. تكمن خصوصية هذه العملية في Power Query في أنها تتخطى الخلايا الفارغة في بيانات المصدر ، وهو ما نحتاج إليه:
3. نقوم الآن بإجراء العملية العكسية - نعيد الجدول الناتج إلى جدول ثنائي الأبعاد لإعادته إلى شكله الأصلي. حدد العمود مع الأشهر وعلى علامة التبويب تحول اختر فريق العمود المحوري (تحويل - عمود محوري). في النافذة التي تفتح ، كعمود من القيم ، حدد (القيمة) الأخيرة ، وفي الخيارات المتقدمة - العملية لا تجمع (لا تجمع):
4. يبقى تحميل النتيجة مرة أخرى إلى Excel باستخدام الأمر الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل ... (الصفحة الرئيسية - إغلاق وتحميل - إغلاق وتحميل إلى ...)

• ما هو الماكرو ، كيف يعمل ، مكان نسخ نص الماكرو ، كيفيه تشغيل ماكرو؟
• تعبئة جميع الخلايا الفارغة في القائمة بقيم الخلايا الأصل
• إزالة جميع الخلايا الفارغة من نطاق معين
• إزالة جميع الصفوف الفارغة في ورقة العمل باستخدام الوظيفة الإضافية PLEX