ربط النص حسب الشرط

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

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

بمعنى آخر ، نحتاج إلى أداة تلصق (تربط) النص وفقًا للحالة - تناظرية للوظيفة سوميسلي (سوميف)، ولكن للنص.

الطريقة 0. الصيغة

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

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

يمكنك الآن تصفية تلك العناصر ونسخ لصق العنوان الضروري لمزيد من الاستخدام.

الطريقة 1. الوظيفة الكلية للالتصاق بشرط واحد

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

وظيفة MergeIf (TextRange كنطاق ، SearchRange كنطاق ، شرط كسلسلة) Dim Delimeter كسلسلة ، i As Long Delimeter = "،" gluings غير متساوية مع بعضها البعض - نغادر مع خطأ إذا SearchRange.Count <> TextRange.Count ثم MergeIf = CVErr (xlErrRef) قم بإنهاء وظيفة نهاية إذا 'انتقل من خلال جميع الخلايا ، تحقق من الشرط وجمع النص في المتغير OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells (i) Like Condition ثم OutText = OutText & TextRange.Cells (i) & Delimeter Next i 'عرض النتائج بدون آخر محدد MergeIf = Left (OutText، Len (OutText) - Len (Delimeter)) End وظيفة  

إذا عدت الآن إلى Microsoft Excel ، ثم في قائمة الوظائف (زر fx في شريط الصيغة أو علامة التبويب الصيغ - إدراج دالة) سيكون من الممكن العثور على وظيفتنا دمج في الفئة تعريف المستخدم (تعريف المستخدم). فيما يلي حجج الوظيفة:

الطريقة الثانية: ربط النص حسب شرط غير دقيق

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

يتم دعم أحرف البدل القياسية:

• علامة النجمة (*) - تشير إلى أي عدد من أي أحرف (بما في ذلك غيابهم)
• علامة الاستفهام (؟) - تعني أي حرف واحد
• علامة الجنيه (#) - تعني أي رقم واحد (0-9)

بشكل افتراضي ، يكون عامل التشغيل Like حساسًا لحالة الأحرف ، أي يفهم ، على سبيل المثال ، "Orion" و "Orion" كشركات مختلفة. لتجاهل الحالة ، يمكنك إضافة السطر في بداية الوحدة النمطية في محرر Visual Basic خيار مقارنة النص، والتي ستبدل "أعجبني" لتكون غير حساسة لحالة الأحرف.

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

• ؟ 1 ## ؟؟ 777RUS - اختيار جميع لوحات تسجيل منطقة 777 ، بدءًا من 1
• LLC * - جميع الشركات التي يبدأ اسمها بـ LLC
• ## 7 ## - جميع المنتجات ذات الرمز الرقمي المكون من خمسة أرقام ، حيث يكون الرقم الثالث هو 7
• ؟؟؟؟؟ - جميع الأسماء المكونة من خمسة أحرف ، إلخ.
  

الطريقة الثالثة. وظيفة ماكرو للصق النص تحت حالتين

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

وظيفة MergeIfs (TextRange كنطاق ، SearchRange1 كنطاق ، Condition1 كسلسلة ، SearchRange2 كنطاق ، Condition2 كسلسلة) Dim Delimeter كسلسلة ، i As Long Delimeter = "،" "أحرف محدد (يمكن استبدالها بمسافة أو ؛ إلخ.) e.) 'إذا كانت نطاقات التحقق من الصحة والالتصاق غير متساوية مع بعضها البعض ، فاخرج بخطأ إذا كان SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count ثم MergeIfs = CVErr (xlErrRef) قم بإنهاء الوظيفة إذا انتقل من خلال جميع الخلايا ، وتحقق من جميع الشروط وجمع النص في المتغير OutText لـ i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells (i) = Condition1 And SearchRange2.Cells (i) = Condition2 ثم OutText = OutText & TextRange.Cells (i) & Delimeter End If Next i 'عرض النتائج بدون المحدد الأخير MergeIfs = Left (OutText، Len (OutText) - Len (Delimeter)) وظيفة النهاية  

سيتم تطبيقه بنفس الطريقة تمامًا - فقط الوسائط الآن بحاجة إلى تحديد أكثر:

الطريقة الرابعة. التجميع واللصق في Power Query

يمكنك حل المشكلة بدون البرمجة في VBA ، إذا كنت تستخدم وظيفة Power Query الإضافية المجانية. بالنسبة لبرنامج Excel 2010-2013 ، يمكن تنزيله هنا ، وفي Excel 2016 يكون مضمّنًا بالفعل بشكل افتراضي. سيكون تسلسل الإجراءات على النحو التالي:

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

لنقم الآن بتحميل جدولنا في الوظيفة الإضافية Power Query. للقيام بذلك ، في علامة التبويب البيانات (إذا كان لديك Excel 2016) أو في علامة التبويب Power Query (إذا كان لديك Excel 2010-2013) ، فانقر فوق من الطاولة (بيانات - من جدول):

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

انقر فوق "موافق" وسوف نحصل على جدول صغير للقيم المجمعة لكل شركة. تظهر محتويات الجداول بوضوح إذا نقرت بزر الماوس الأيسر على الخلفية البيضاء للخلايا (وليس على النص!) في العمود الناتج:

الآن دعنا نضيف عمودًا آخر ، حيث ، باستخدام الوظيفة ، نلصق محتويات أعمدة العنوان في كل من الجداول المصغرة ، مفصولة بفواصل. للقيام بذلك ، في علامة التبويب إضافة عمود نضغط عمود مخصص (إضافة عمود - عمود مخصص) وفي النافذة التي تظهر ، أدخل اسم العمود الجديد وصيغة الاقتران بلغة M المضمنة في Power Query:

لاحظ أن جميع وظائف M حساسة لحالة الأحرف (على عكس Excel). بعد الضغط على OK نحصل على عمود جديد بعناوين ملصقة:

يبقى لإزالة العمود غير الضروري بالفعل الجدول (انقر بزر الماوس الأيمن على العنوان) حذف العمود) وقم بتحميل النتائج على الورقة من خلال النقر فوق علامة التبويب الصفحة الرئيسية - إغلاق وتنزيل (الصفحة الرئيسية - إغلاق وتحميل):

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

• كيفية تقسيم سلسلة نصية طويلة إلى أجزاء
• عدة طرق للصق النص من خلايا مختلفة في واحدة
• استخدام عامل Like لاختبار النص مقابل قناع