جایگزینی متن انبوه با فرمول

فرض کنید فهرستی دارید که در آن، با درجات مختلفی از "صراحت"، داده های اولیه نوشته شده است - به عنوان مثال، آدرس ها یا نام شرکت ها:

به وضوح مشاهده می شود که همان شهر یا شرکت در انواع مختلف در اینجا حضور دارد که بدیهی است در آینده هنگام کار با این میزها مشکلات زیادی ایجاد خواهد کرد. و اگر کمی فکر کنید، می توانید نمونه های زیادی از کارهای مشابه را از مناطق دیگر پیدا کنید.

حالا تصور کنید که چنین داده‌های نادرستی مرتباً به شما می‌آیند، یعنی این یک داستان یکباره «به صورت دستی درستش کنید، فراموشش کنید» نیست، بلکه یک مشکل به طور منظم و در تعداد زیادی سلول است.

چه باید کرد؟ از طریق کادر «یافتن و جایگزینی» یا با کلیک کردن، 100500 بار متن کج را با متن صحیح جایگزین نکنید. کلیدهای Ctrl+H?

اولین چیزی که در چنین شرایطی به ذهن می رسد این است که یک جایگزین انبوه بر اساس یک کتاب مرجع از پیش تدوین شده تطبیق گزینه های نادرست و صحیح انجام دهید - مانند این:

متأسفانه، با رواج آشکار چنین کاری، مایکروسافت اکسل روش های داخلی ساده ای برای حل آن ندارد. برای شروع، بیایید دریابیم که چگونه این کار را با فرمول ها انجام دهیم، بدون اینکه "توپخانه سنگین" را به شکل ماکرو در VBA یا Power Query درگیر کنیم.

مورد 1. عمده تعویض کامل

بیایید با یک مورد نسبتاً ساده شروع کنیم - وضعیتی که باید متن کج قدیمی را با متن جدید جایگزین کنید. کاملا.

فرض کنید دو جدول داریم:

در اول - نام های متنوع اصلی شرکت ها. در دوم - کتاب مرجع مکاتبات. اگر در نام شرکت در جدول اول هر کلمه ای از ستون را پیدا کنیم برای پیدا کردن، سپس باید این نام کج را کاملاً با نام صحیح - از ستون جایگزین کنید جایگزین جدول جستجوی دوم

برای آسودگی:

• هر دو جدول با استفاده از میانبر صفحه کلید به پویا ("هوشمند") تبدیل می شوند کلیدهای Ctrl+T یا تیم درج - جدول (درج - جدول).
  
• در برگه ای که ظاهر می شود سازنده (طرح) اولین جدول به نام داده هاو جدول مرجع دوم – تعویض.

برای توضیح منطق فرمول، کمی از راه دور برویم.

با در نظر گرفتن شرکت اول از سلول A2 به عنوان مثال و به طور موقت فراموش کردن بقیه شرکت ها، بیایید سعی کنیم کدام گزینه را از ستون تعیین کنیم. برای پیدا کردن آنجا ملاقات می کند. برای انجام این کار، هر سلول خالی را در قسمت آزاد برگه انتخاب کنید و تابع را در آنجا وارد کنید برای پیدا کردن (پیدا کردن):

این تابع تعیین می کند که آیا زیررشته داده شده گنجانده شده است (آگومان اول تمام مقادیر ستون است برای پیدا کردن) در متن مبدأ (اولین شرکت از جدول داده ها) و باید شماره ترتیبی نویسه ای را که متن از آن پیدا شده است، یا در صورت پیدا نشدن رشته فرعی یک خطا را خروجی می دهد.

ترفند در اینجا این است که از آنجایی که ما نه یک، بلکه چندین مقدار را به عنوان آرگومان اول مشخص کردیم، این تابع نیز در نتیجه نه یک مقدار، بلکه آرایه ای از 3 عنصر را برمی گرداند. اگر آخرین نسخه آفیس 365 را که از آرایه های پویا پشتیبانی می کند ندارید، پس از وارد کردن این فرمول و کلیک بر روی وارد این آرایه را دقیقاً روی برگه خواهید دید:

اگر نسخه های قبلی اکسل را دارید، پس از کلیک بر روی وارد ما فقط اولین مقدار را از آرایه نتیجه خواهیم دید، یعنی خطای #VALUE! (#ارزش!).

شما نباید بترسید 🙂 در واقع، فرمول ما کار می کند و اگر تابع وارد شده را در نوار فرمول انتخاب کنید و کلید را فشار دهید، همچنان می توانید کل آرایه نتایج را ببینید. F9(فقط فراموش نکنید که فشار دهید Esc رابرای بازگشت به فرمول):

آرایه حاصل از نتایج به این معنی است که در نام اصلی شرکت کج (GK Morozko OAO) از تمام مقادیر در یک ستون برای پیدا کردن فقط دومی را پیدا کرد (مروزکو)، و از چهارمین کاراکتر متوالی شروع می شود.

حالا بیایید یک تابع به فرمول خود اضافه کنیم VIEW(جستجو):

این تابع سه آرگومان دارد:

1. ارزش مورد نظر - می توانید از هر عدد به اندازه کافی بزرگ استفاده کنید (نکته اصلی این است که از طول هر متنی در داده های منبع فراتر رود)
   
2. مشاهده_بردار - محدوده یا آرایه ای که در آن به دنبال مقدار مورد نظر هستیم. در اینجا تابع معرفی شده قبلی است برای پیدا کردن، که یک آرایه {#VALUE!:4:#VALUE!} را برمی گرداند.
   
3. بردار_نتایج – محدوده ای که در صورت یافتن مقدار مورد نظر در سلول مربوطه، می خواهیم مقدار را از آن برگردانیم. در اینجا نام های صحیح از ستون آمده است جایگزین جدول مرجع ما
   

ویژگی اصلی و غیر مشهود در اینجا این است که تابع VIEW اگر مطابقت دقیقی وجود نداشته باشد، همیشه به دنبال کوچکترین مقدار (قبلی) می گردد. بنابراین، با تعیین هر عدد سنگین (مثلا 9999) به عنوان مقدار مورد نظر، مجبور می شویم VIEW سلولی با کوچکترین عدد (4) را در آرایه {#VALUE!:4:#VALUE!} پیدا کنید و مقدار مربوطه را از بردار نتیجه، یعنی نام شرکت را درست از ستون برگردانید. جایگزین.

نکته دوم این است که، از نظر فنی، فرمول ما یک فرمول آرایه است، زیرا تابع برای پیدا کردن نه یک، بلکه آرایه ای از سه مقدار را به عنوان نتیجه برمی گرداند. اما از آنجایی که تابع VIEW آرایه های خارج از جعبه را پشتیبانی می کند، پس مجبور نیستیم این فرمول را به عنوان یک فرمول آرایه کلاسیک وارد کنیم - با استفاده از میانبر صفحه کلید کلیدهای Ctrl+تغییر+وارد. یک مورد ساده کافی است وارد.

همین. امیدوارم منطق رو متوجه بشی

باقی مانده است که فرمول نهایی را به اولین سلول B2 ستون منتقل کنید درست شد - و تکلیف ما حل شد!

البته، با جداول معمولی (نه هوشمند)، این فرمول نیز عالی عمل می کند (فقط کلید را فراموش نکنید F4 و رفع لینک های مربوطه):

مورد 2. تعویض جزئی فله

این مورد کمی پیچیده تر است. دوباره دو جدول "هوشمند" داریم:

اولین جدول با آدرس های کج نوشته شده که باید اصلاح شود (من به آن زنگ زدم Data2). جدول دوم یک کتاب مرجع است که طبق آن باید یک زیررشته داخل آدرس را جایگزین کنید (من این جدول را صدا کردم تعویض ها 2).

تفاوت اساسی در اینجا این است که شما باید تنها بخشی از داده های اصلی را جایگزین کنید - برای مثال، آدرس اول نادرست است. «سنت پترزبورگ” در سمت راست «سنت پترزبورگ”، بقیه آدرس (کد پستی، خیابان، خانه) را همانطور که هست بگذارید.

فرمول تمام شده به این شکل خواهد بود (برای سهولت درک، آن را به چند خط تقسیم کردم دگرساز+وارد):

کار اصلی در اینجا توسط تابع متن استاندارد Excel انجام می شود جایگزین (جایگزین)که دارای 3 آرگومان است:

1. متن منبع – اولین آدرس کج از ستون آدرس
2. آنچه ما به دنبال آن هستیم - در اینجا از ترفند با تابع استفاده می کنیم VIEW (جستجو)از روش قبلی برای بیرون کشیدن مقدار از ستون برای پیدا کردن، که به عنوان یک قطعه در یک آدرس منحنی گنجانده شده است.
3. چه چیزی را جایگزین کنیم - به همان ترتیب ما مقدار صحیح مربوط به آن را از ستون پیدا می کنیم جایگزین.

این فرمول را با وارد کنید کلیدهای Ctrl+تغییر+وارد در اینجا نیز مورد نیاز نیست، اگرچه در واقع یک فرمول آرایه است.

و به وضوح مشاهده می شود (به #خطاهای N/A در تصویر قبلی مراجعه کنید) که چنین فرمولی، با همه ظرافتش، چند اشکال دارد:

• عملکرد SUBSTITUTE به حروف کوچک و بزرگ حساس است، بنابراین "Spb" در خط ماقبل آخر در جدول جایگزین یافت نشد. برای حل این مشکل، می توانید از تابع استفاده کنید زامنیت (جایگزین کردن)، یا ابتدا هر دو جدول را به یک ثبات بیاورید.
• اگر متن در ابتدا صحیح یا در آن باشد هیچ قطعه ای برای جایگزینی وجود ندارد (خط آخر)، سپس فرمول ما خطا می دهد. این لحظه را می توان با رهگیری و جایگزینی خطاها با استفاده از تابع خنثی کرد IFERROR (IFERROR):

  

• اگر متن اصلی حاوی چندین قطعه از دایرکتوری به طور همزمان، سپس فرمول ما فقط آخرین مورد را جایگزین می کند (در خط 8 ، Ligovsky «خیابان« تغییر یافته است به "pr-t"، ولی "S-Pb" on «سنت پترزبورگ” دیگر نه، زیرا S-Pb” در فهرست بالاتر است). این مشکل را می توان با اجرای مجدد فرمول خودمان، اما از قبل در امتداد ستون، حل کرد درست شد:

  
  

در جاهایی کامل و دست و پا گیر نیست، اما خیلی بهتر از همان تعویض دستی است، درست است؟ 🙂

PS

در مقاله بعدی نحوه اجرای چنین جایگزینی انبوه را با استفاده از ماکروها و Power Query خواهیم فهمید.

• نحوه عملکرد تابع SUBSTITUTE برای جایگزینی متن
  
• یافتن تطابق متن دقیق با استفاده از تابع EXACT
• جستجو و جایگزینی حساس به حروف بزرگ و کوچک (VLOOKUP حساس به حروف کوچک و بزرگ)