املأ الخلايا الفارغة حتى القيمة التالية في Excel (3 طرق سهلة)

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

كما هو مبين أدناه:

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

من السهل إصلاحه!

في هذا البرنامج التعليمي سوف أريكم كيفتعبئة الخلايا بسرعة حتى قيمة التعبئة التالية في Excel.

يمكنك القيام بذلك بسهولة باستخدام أساليب حوار Go-To الخاصة البسيطة أو VBA أو Power Query.

اذا هيا بنا نبدأ!

الطريقة الأولى - املأ باستخدام صيغة Go To Special +

افترض أن لديك مجموعة بيانات كما هو موضح أدناه ، وتريد ملء البيانات في العمودين A و B.

في العمود B ، الهدف هو ملء "printer" حتى آخر خلية فارغة تحتها ، ثم عند بدء "scanner" ، ثم ملء "scanner" في الخلية أدناه حتى تصبح الخلية فارغة.

انتقل إلى الخطوات التالية المستخدمة في الخطوات الخاصة لتحديد جميع الخلايا الفارغة واملأها بصيغة بسيطة:

1. حدد البيانات المراد تعبئتها (A1: D21 في مثالنا)
2. انتقل إلى علامة التبويب "الصفحة الرئيسية"

3. في مجموعة التحرير ، انقر فوق رمز "بحث وتحديد" (سيكشف هذا عن المزيد من الخيارات في القائمة المنسدلة)
4. انقر فوق خيار "Go"

5. في مربع الحوار "الانتقال إلى" الذي يفتح ، انقر فوق الزر "خاص".سيؤدي هذا إلى فتح مربع الحوار "الانتقال إلى خاص"

6. في مربع الحوار "الانتقال إلى خاص" ، انقر فوق "فارغ"

7. انقر فوق موافق

ستحدد الخطوات المذكورة أعلاه جميع الخلايا الفارغة في مجموعة البيانات (الموضحة أدناه).

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

لا تقلق بشأن موضع الخلية في التحديد ، حيث ستعمل طريقتنا في أي حال.

الآن ، فيما يلي خطوات ملء البيانات في الخلايا الفارغة المحددة:

1. انقر فوق مفتاح علامة المساواة (=) على لوحة المفاتيح.سيؤدي هذا إلى إدراج علامة المساواة في الخلية النشطة
2. اضغط على مفتاح السهم لأعلى.سيؤدي هذا إلى إدراج مرجع الخلية للخلية أعلى الخلية النشطة.على سبيل المثال ، في حالتنا ، B3 هي الخلية النشطة ، وعندما نقوم بهاتين الخطوتين ، فإنها تدخل = B2 في الخلية
3. اضغط مع الاستمرار على مفتاح التحكم واضغط على Enter

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

على الرغم من أن هذا قد يبدو كخطوات كثيرة جدًا ، فبمجرد الانتهاء من ذلك ، ستتمكن من ملء البيانات بسرعة في Excel في ثوانٍ.

الآن ، هناك شيئان يجب أن تكون على دراية بهما عند استخدام هذه الطريقة.

تحويل الصيغة إلى قيمة

الأول هو التأكد من تحويل الصيغ إلى قيم (بحيث يكون لديك قيم ثابتة ولا تفسد عند تغيير البيانات في المستقبل).

تغيير تنسيق التاريخ

إذا كنت تستخدم التواريخ في بياناتك (كما فعلت في مثال البيانات) ، فستلاحظ أن القيم التي تم ملؤها في عمود التاريخ هي أرقام بدلاً من التواريخ.

إذا كان الإخراج في عمود التاريخ بتنسيق التاريخ المطلوب ، فأنت جيد ولا تحتاج إلى فعل أي شيء آخر.

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

فيما يلي خطوات القيام بذلك:

1. حدد الأعمدة التي تحتوي على التواريخ
2. انقر فوق علامة التبويب الصفحة الرئيسية
3. في مجموعة الأرقام ، انقر على القائمة المنسدلة تنسيق وحدد تنسيق التاريخ.

إذا كنت بحاجة إلى القيام بهذه المساحة المتروكة من وقت لآخر ، فإنني أوصي بتقنية Go-To الخاصة والصيغة.

على الرغم من أنه يحتوي على بضع خطوات ، إلا أنه بسيط ويمنحك نتائج في مجموعة البيانات.

ولكن إذا كان عليك القيام بذلك كثيرًا ، فأقترح عليك إلقاء نظرة على VBA وطريقة Power Query الموضحة بعد ذلك

الطريقة الثانية - املأ برمز VBA البسيط

يمكنك استخدام رمز ماكرو VBA بسيط للغاية لملء الخلايا بسرعة في Excel حتى آخر قيمة فارغة.

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

إليك رمز VBA الذي سيتكرر خلال كل خلية في التحديد ويملأ أي خلايا فارغة:

Sub FillDown () لكل خلية في التحديد إذا كانت الخلية = "" ثم cell.FillDown End If Next End Sub

يستخدم الكود أعلاه حلقة For للتكرار خلال كل خلية في التحديد.

في حلقة For ، أستخدم شرط If-Then للتحقق مما إذا كانت الخلية فارغة.

إذا كانت الخلية فارغة ، يتم ملؤها بالقيمة من الخلية أعلاه ، وإذا لم يكن الأمر كذلك ، فإن حلقة for تتجاهل الخلية وتنتقل إلى الخلية التالية.

الآن بعد أن أصبح لديك رمز VBA ، دعني أوضح لك مكان وضع هذا الرمز في Excel:

1. حدد البيانات المراد تعبئتها
2. انقر فوق علامة التبويب المطور

3. انقر فوق رمز Visual Basic (أو يمكنك استخدام اختصار لوحة المفاتيح ALT + F11).سيؤدي هذا إلى فتح محرر Visual Basic في Excel

4. في محرر Visual Basic على اليسار ، سيكون لديك Project Explorer.إذا كنت لا ترى ذلك ، فانقر فوق الخيار عرض في القائمة ، ثم انقر فوق Project Explorer

5. إذا كان لديك العديد من مصنفات Excel المفتوحة ، فسيعرض Project Explorer جميع أسماء المصنفات.ابحث عن اسم المصنف الذي توجد به البيانات.
6. انقر بزر الماوس الأيمن فوق أي كائن في المصنف ، وانتقل إلى "إدراج" ، ثم انقر فوق "وحدة نمطية".سيؤدي هذا إلى إدراج وحدة نمطية جديدة لهذا المصنف

7. انقر نقرًا مزدوجًا فوق "الوحدة النمطية" التي أدخلتها للتو في الخطوات أعلاه.سيفتح نافذة الكود لتلك الوحدة

8. انسخ والصق رمز فبا في نافذة التعليمات البرمجية
9. ضع المؤشر في أي مكان في الكود وقم بتشغيل كود الماكرو بالنقر فوق الزر الأخضر في شريط الأدوات أو باستخدام اختصار لوحة المفاتيح F5

ستعمل الخطوات المذكورة أعلاه على تشغيل رمز VBA وسيتم ملء بياناتك.

إذا كنت تريد استخدام رمز VBA هذا مرة أخرى لاحقًا ، فستحتاج إلى حفظ هذا الملف كمصنف Excel ممكّن بماكرو (ملحق. XLSM)

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

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

يمكنك أيضًا إضافة هذا الماكرو إلى مصنف الماكرو الشخصي الخاص بك ثم استخدامه في أي مصنف على نظامك.

انقر هنا لقراءة المزيد عن مصنف الماكرو الشخصي وكيفية إضافة رمز إليه.

الطريقة الثالثة - التعبئة باستخدام Power Query

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

من المستحسن عندما لا تزال تستخدم Power Query لتحويل البيانات أو دمج البيانات من أوراق عمل متعددة أو مصنفات متعددة.

كجزء من سير عمل موجود ، يمكنك تعبئة الخلايا الفارغة بسرعة باستخدام خيار التعبئة في Power Query.

لاستخدام Power Query ، يوصى بأن تكون بياناتك بتنسيق جدول Excel.إذا لم تتمكن من تحويل البيانات إلى جدول Excel ، فيجب عليك إنشاء نطاق مسمى للبيانات ثم استخدام هذا النطاق المسمى في Power Query.

يوجد أدناه مجموعة بيانات تم تحويلها إلى جدول Excel.يمكنك القيام بذلك عن طريق تحديد مجموعة البيانات ، والانتقال إلى علامة التبويب "إدراج" ، والنقر فوق رمز الجدول.

فيما يلي خطوات استخدام Power Query لملء البيانات حتى القيمة التالية:

1. حدد أي خلية في مجموعة البيانات
2. انقر فوق علامة التبويب البيانات

3. في المجموعة الحصول على البيانات وتحويلها ، انقر فوق من ورقة العمل.سيؤدي هذا إلى فتح محرر Power Query.لاحظ أن الخلايا الفارغة ستعرض القيمة "فارغة" في Power Query

4. في محرر Power Query ، حدد العمود الذي تريد تعبئته.للقيام بذلك ، اضغط باستمرار على مفتاح التحكم وانقر فوق رأس العمود الذي تريد تحديده.في مثالنا سيكون التاريخ وعمود المنتج.
5. انقر بزر الماوس الأيمن على أي عنوان محدد

6. انتقل إلى خيار Fill وانقر فوق Down.سيؤدي هذا إلى ملء جميع الخلايا الفارغة بالبيانات

7. انقر فوق علامة التبويب ملف ، ثم انقر فوق إغلاق وتحميل.سيؤدي هذا إلى إدراج ورقة عمل جديدة مع جدول النتائج في المصنف

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

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

بينما تعمل طريقة Power Query بشكل جيد ، فمن الأفضل استخدامها عندما تستخدم بالفعل Power Query في سير عملك.

إذا كان لديك مجموعة بيانات واحدة فقط بها خلايا فارغة لتعبئتها ، فمن الأنسب استخدام طريقة Go To الخاصة أو طريقة VBA (الموضحة أعلاه).

لذلك يمكنك استخدام هذه الطرق الثلاث البسيطة لاملأ الخلايا الفارغة حتى القيمة التالية في Excel.

أتمنى أن تكون قد وجدت هذا البرنامج التعليمي مفيدًا.