صديق ليغالبًا ما أتواصل معه بشأن بعض مشكلات العالم الحقيقي التي يواجهها عند العمل باستخدام البيانات في Excel.
في كثير من الأحيان أترجم استعلامه إلى برنامج تعليمي لبرنامج Excel على هذا الموقع لأنه قد يكون مفيدًا للقراء الآخرين أيضًا.
هذا هو واحد من هذه الدروس أيضا.
اتصل بي صديقي الأسبوع الماضي بالأسئلة التالية:
لدي بيانات عنوان في عمود في Excel وأريد تحديد / تصفية الخلايا التي تحتوي على سلاسل نصية (كلمات) متكررة في العنوان.
إليك مجموعة بيانات مماثلة حيث يريد تصفية الخلايا (الخلايا ذات الأسهم الحمراء) التي تحتوي على سلاسل نصية متكررة فيها:
ما يجعل هذا الأمر صعبًا الآن هو أن البيانات ليست متسقة.نظرًا لأن هذا تجميع لمجموعات البيانات التي تم إنشاؤها يدويًا بواسطة مندوبي المبيعات ، فقد يكون هناك تناقضات في مجموعات البيانات.
فكر في الأمر:
- يمكن تكرار أي سلسلة نصية في مجموعة البيانات هذه.على سبيل المثال ، يمكن أن يكون اسم منطقة أو اسم مدينة أو كليهما.
- الكلمات مفصولة بأحرف مسافات ، ولا يوجد تناسق فيما إذا كان اسم المدينة بعد ستة أو ثمانية أحرف.
- هناك الآلاف من هذه السجلات وتحتاج إلى تصفية تلك التي تحتوي على أي سلاسل نصية متكررة.
بعد النظر في العديد من الخيارات مثل النص إلى الأعمدة والصيغ ، قررت أخيرًا استخدام VBA للقيام بالمهمة.
لذلك قمت بإنشاء وظيفة VBA مخصصة ('IdDuplicate') لتحليل هذه الخلايا وإعطائي TRUE إذا كانت هناك كلمات مكررة في السلسلة النصية و FALSE إذا لم تكن هناك تكرارات (كما هو موضح أدناه):
تحلل هذه الوظيفة المخصصة كل كلمة في السلسلة النصية وتتحقق من عدد مرات ظهورها في النص.تُرجع TRUE إذا كان العدد أكبر من 1 ؛وإلا قم بإرجاع FALSE.
أيضًا ، تم إنشاؤه لعد الكلمات التي تحتوي على أكثر من ثلاثة أحرف فقط.
بمجرد أن أحصل على بيانات TRUE / FALSE ، يمكنني بسهولة تصفية جميع السجلات التي تكون TRUE.
الآن دعني أوضح لك كيفية القيام بذلك في Excel.
كود فبا لوظيفة مخصصة
يتم ذلك عن طريق إنشاء وظيفة مخصصة في VBA.يمكن بعد ذلك استخدام هذه الوظيفة كأي دالة ورقة عمل أخرى في Excel.
هذا هو رمز VBA الخاص به:
الدالة IdDuplicates (rng كنطاق) مثل String Dim StringtoAnalyze as Variant Dim i As Integer Dim j As Integer Const الصحيح minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value)، "") For i = UBound (StringtoAnalyze) حتى 0 الخطوة -1 إذا كان Len (StringtoAnalyze (i)) <minWordLen ثم GoTo SkipA لـ j = 0 إلى i - 1 إذا كان StringtoAnalyze (j) = StringtoAnalyze (i) ثم IdDuplicates = "TRUE" GoTo SkipB End إذا التالي j SkipA: التالي i IdDuplicates = "FALSE" SkipB: وظيفة الإنهاء
كيفية استخدام كود فبا هذا
الآن بعد أن أصبح لديك رمز VBA ، فأنت بحاجة إلى وضعه في الواجهة الخلفية لبرنامج Excel حتى يمكن أن يعمل كوظيفة عادية في ورقة العمل.
فيما يلي خطوات وضع كود VBA في الخلفية:
- انتقل إلى علامة التبويب المطور.
- انقر فوق Visual Basic (يمكنك أيضًا استخدام اختصار لوحة المفاتيح ALT + F11)
- في الواجهة الخلفية لمحرر VB المفتوح ، انقر بزر الماوس الأيمن فوق أي كائن مصنف.
- انتقل إلى "إدراج" وانقر على "وحدة".سيؤدي هذا إلى إدراج كائن الوحدة النمطية للمصنف.
- في نافذة رمز الوحدة النمطية ، انسخ والصق رمز فبا المذكور أعلاه.
بمجرد أن يكون لديك رمز VBA في الخلفية ، يمكنك استخدام الوظيفة - 'IdDuplicates' مثل أي وظيفة أخرى في ورقة العمل العادية.
تأخذ هذه الوظيفة معلمة واحدة ، مرجع الخلية للخلية حيث لديك النص.
نتيجة هذه الوظيفة هي TRUE (إذا كانت هناك كلمات مكررة فيها) أو FALSE (إذا لم يكن هناك تكرارات).بمجرد الحصول على قائمة TRUE / FALSE هذه ، يمكنك تصفية تلك التي تحتوي على TRUE للحصول على جميع الخلايا التي تحتوي على سلاسل نصية متكررة فيها.
ملاحظة: الكود الذي قمت بإنشائه هو فقط لحساب الكلمات التي يزيد طولها عن ثلاثة أحرف.وهذا يضمن أنه إذا كانت هناك كلمات في سلسلة النص تتكون من حرف واحد أو حرفين أو ثلاثة أحرف (مثل 1 A أو KGM أو LDA) ، فسيتم تجاهل هذه الكلمات عند حساب التكرارات.يمكنك بسهولة تغيير هذا في الكود إذا لزم الأمر.
هذه الميزة متاحة فقط في المصنف حيث نسخت الرمز في الوحدة النمطية.إذا كنت تريد أن يتوفر هذا الرمز في مصنفات أخرى أيضًا ، فأنت بحاجة إلى نسخ هذا الرمز ولصقه في تلك المصنفات.بدلاً من ذلك ، يمكنك إنشاء وظيفة إضافية (سيؤدي تمكين ذلك إلى إتاحة الميزة في جميع المصنفات الموجودة على نظامك).
تذكر أيضًا حفظ هذا المصنف بملحق .xlsm (لأنه يحتوي على تعليمات برمجية للماكرو).
