استبدال النص المجمع بالصيغ

لنفترض أن لديك قائمة يتم فيها ، بدرجات متفاوتة من "الاستقامة" ، كتابة البيانات الأولية - على سبيل المثال ، العناوين أو أسماء الشركات:

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

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

ماذا أفعل؟ لا تستبدل النص الملتوي يدويًا 100500 مرة بالنص الصحيح من خلال مربع "بحث واستبدال" أو بالنقر CTRL+H?

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

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

حالة 1. استبدال كامل بالجملة

لنبدأ بحالة بسيطة نسبيًا - وهي حالة تحتاج فيها إلى استبدال النص القديم الملتوي بآخر جديد. تماما.

لنفترض أن لدينا جدولين:

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

للراحة:

• يتم تحويل كلا الجدولين إلى ديناميكي ("ذكي") باستخدام اختصار لوحة المفاتيح CTRL+T أو فريق إدراج - جدول (إدراج - جدول).
  
• في علامة التبويب التي تظهر منشئ (التصميم) الجدول الأول اسمه البياناتوالجدول المرجعي الثاني - بدائل.

لشرح منطق الصيغة ، دعنا ننتقل قليلاً من بعيد.

بأخذ الشركة الأولى من الخلية A2 كمثال ونسيان مؤقتًا باقي الشركات ، دعنا نحاول تحديد الخيار من العمود لايجاد يلتقي هناك. للقيام بذلك ، حدد أي خلية فارغة في الجزء الحر من الورقة وأدخل الوظيفة هناك لايجاد (تجد):

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

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

إذا كان لديك إصدارات سابقة من Excel ، فبعد النقر فوق أدخل سنرى فقط القيمة الأولى من مصفوفة النتيجة ، أي الخطأ #VALUE! (#القيمة!).

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

مصفوفة النتائج الناتجة تعني ذلك في اسم الشركة الأصلي الملتوي (حارس مرمى موروزكو OAO) لجميع القيم في عمود لايجاد وجدت فقط الثانية (موروزكو)، وبدءًا من الحرف الرابع على التوالي.

لنضيف الآن دالة إلى صيغتنا أنظر إلى(ابحث عن):

هذه الوظيفة لها ثلاث حجج:

1. القيمة المطلوبة - يمكنك استخدام أي رقم كبير بدرجة كافية (الشيء الرئيسي أنه يتجاوز طول أي نص في البيانات المصدر)
   
2. ناقل_العرض - النطاق أو المصفوفة حيث نبحث عن القيمة المطلوبة. هذه هي الوظيفة التي تم تقديمها مسبقًا لايجاد، والتي تُرجع مصفوفة {#VALUE!: 4: #VALUE!}
   
3. المتجه_النتائج - النطاق الذي نريد إرجاع القيمة منه إذا تم العثور على القيمة المطلوبة في الخلية المقابلة. فيما يلي الأسماء الصحيحة من العمود استبدل جدولنا المرجعي.
   

الميزة الرئيسية وغير الواضحة هنا هي أن الوظيفة أنظر إلى إذا لم يكن هناك تطابق تام ، فابحث دائمًا عن أقرب قيمة أصغر (سابقة). لذلك ، من خلال تحديد أي رقم ضخم (على سبيل المثال ، 9999) كقيمة مرغوبة ، سوف نفرض أنظر إلى ابحث عن الخلية التي تحتوي على أقرب رقم أصغر (4) في المصفوفة {#VALUE!: 4: #VALUE!} وقم بإرجاع القيمة المقابلة من متجه النتيجة ، أي اسم الشركة الصحيح من العمود استبدل.

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

هذا كل شئ. أتمنى أن تحصل على المنطق.

يبقى نقل الصيغة النهائية إلى الخلية الأولى B2 من العمود ثابت - وتحل مهمتنا!

بالطبع ، مع الجداول العادية (غير الذكية) ، تعمل هذه الصيغة أيضًا بشكل رائع (فقط لا تنس المفتاح F4 وتحديد الروابط ذات الصلة):

الحالة 2. استبدال جزئي بالجملة

هذه الحالة أصعب قليلاً. مرة أخرى لدينا جدولين "ذكيين":

الجدول الأول الذي يحتوي على عناوين مكتوبة بشكل ملتوي يحتاج إلى تصحيح (أسميته Data2). الجدول الثاني عبارة عن كتاب مرجعي ، والذي بموجبه تحتاج إلى إجراء استبدال جزئي لسلسلة فرعية داخل العنوان (سميت هذا الجدول البدائل 2).

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

ستبدو الصيغة النهائية على هذا النحو (لسهولة الإدراك ، قسمتها إلى عدد الخطوط المستخدمة قديم+أدخل):

يتم تنفيذ العمل الرئيسي هنا بواسطة وظيفة نص Excel القياسية استبدل (بديل)، الذي يحتوي على 3 حجج:

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

أدخل هذه الصيغة مع CTRL+تغير+أدخل ليست هناك حاجة هنا أيضًا ، على الرغم من أنها ، في الواقع ، صيغة صفيف.

ومن الواضح (انظر أخطاء # N / A في الصورة السابقة) أن هذه الصيغة ، بكل أناقتها ، لها عيبان:

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

  

• إذا كان النص الأصلي يحتوي على عدة أجزاء من الدليل في وقت واحد، ثم تحل صيغتنا محل الصيغة الأخيرة فقط (في السطر الثامن ، Ligovsky «رقم الجادة « تغير إلى "pr-t"، لكن "S-Pb" on "شارع. بطرسبورغ " لم يعد بسبب "S-Pb"أعلى في الدليل). يمكن حل هذه المشكلة عن طريق إعادة تشغيل الصيغة الخاصة بنا ، ولكن بالفعل على طول العمود ثابت:

  
  

ليست مثالية ومرهقة في الأماكن ، ولكنها أفضل بكثير من نفس الاستبدال اليدوي ، أليس كذلك؟ 🙂

PS

في المقالة التالية ، سنكتشف كيفية تنفيذ مثل هذا الاستبدال المجمع باستخدام وحدات الماكرو و Power Query.

• كيف تعمل وظيفة الاستبدال لاستبدال النص
  
• البحث عن تطابقات نصية تامة باستخدام دالة EXACT
• بحث واستبدال حساس لحالة الأحرف (VLOOKUP حساس لحالة الأحرف)