پیوند متن بر اساس شرط

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

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

به عبارت دیگر، ما به ابزاری نیاز داریم که متن را با توجه به شرایط بچسباند (پیوند می دهد) - آنالوگ تابع. SUMMESLI (SUMIF)، اما برای متن

روش 0. فرمول

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

معایب این روش واضح است: از تمام سلول های ستون اضافی به دست آمده، ما فقط به آخرین ها برای هر شرکت نیاز داریم (زرد). اگر لیست بزرگ است، برای انتخاب سریع آنها، باید ستون دیگری را با استفاده از تابع اضافه کنید DLSTR (LEN)، بررسی طول رشته های انباشته شده:

اکنون می‌توانید آن‌ها را فیلتر کرده و چسب آدرس لازم را برای استفاده بیشتر کپی کنید.

روش 1. ماکروعملکرد چسباندن با یک شرط

اگر لیست اصلی بر اساس شرکت مرتب نشده باشد، پس فرمول ساده بالا کار نمی کند، اما می توانید به راحتی با یک تابع سفارشی کوچک در VBA به اطراف بپردازید. ویرایشگر ویژوال بیسیک را با فشار دادن میانبر صفحه کلید باز کنید Alt + F11 یا با استفاده از دکمه ویژوال بیسیک برگ توسعه دهنده (توسعه دهنده). در پنجره باز شده، یک ماژول خالی جدید را از طریق منو وارد کنید درج - ماژول و متن تابع ما را در آنجا کپی کنید:

تابع MergeIf(TextRange به عنوان محدوده، SearchRange به عنوان Range، Condition As String) Dim Delimeter به عنوان رشته، i As Long Delimeter = "، " چسب ها با یکدیگر برابر نیستند - اگر SearchRange.Count <> TextRange.Count با خطا خارج می شویم سپس MergeIf = CVERr(xlErrRef) خروج از تابع پایان اگر از تمام سلول ها عبور کنید، شرایط را بررسی کنید و متن را در متغیر OutText برای i = 1 به SearchRange جمع آوری کنید. Cells.Count If SearchRange.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter بعد من نتایج را بدون آخرین جداکننده نمایش می دهم MergeIf = Left(OutText، Len(OutText) - Len(Delimeter)) پایان تابع  

اگر اکنون به مایکروسافت اکسل بازگشته اید، در لیست توابع (دکمه fx در نوار یا برگه فرمول فرمول ها - تابع درج) امکان یافتن عملکرد ما وجود خواهد داشت MergeIf در رده تعریف شده توسط کاربر (تعریف شده توسط کاربر). آرگومان های تابع به شرح زیر است:

روش 2. متن را با شرایط غیر دقیق الحاق کنید

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

حروف عام استاندارد پشتیبانی می شوند:

• ستاره (*) - نشان دهنده هر تعداد از هر کاراکتر (از جمله عدم وجود آنها) است.
• علامت سوال (؟) - مخفف هر کاراکتری است
• علامت پوند (#) - مخفف هر یک رقم (0-9)

به طور پیش فرض، اپراتور Like به حروف بزرگ و کوچک حساس است، به عنوان مثال، Orion و Orion را به عنوان شرکت های مختلف درک می کند. برای نادیده گرفتن حروف بزرگ، می توانید خط را در همان ابتدای ماژول در ویرایشگر ویژوال بیسیک اضافه کنید گزینه مقایسه متن، که Like را تغییر می دهد تا به حروف بزرگ و کوچک حساس باشد.

به این ترتیب، می توانید ماسک های بسیار پیچیده ای برای بررسی شرایط بسازید، به عنوان مثال:

• ?1##??777RUS – انتخاب تمامی پلاک های منطقه 777 با 1 شروع می شود
• LLC* – همه شرکت هایی که نام آنها با LLC شروع می شود
• ##7## – همه محصولات با کد دیجیتال پنج رقمی که رقم سوم آن 7 است
• ????? - تمام نام های پنج حرف و غیره
  

روش 3. تابع ماکرو برای چسباندن متن تحت دو شرایط

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

تابع MergeIfs(TextRange به عنوان محدوده، SearchRange1 به عنوان Range، Condition1 As String، Search Range2 به عنوان Range، Condition2 as String) Dim Delimeter به عنوان رشته، i As Long Delimeter = "، " 'نویسه های جداکننده (می توان با فاصله یا غیره جایگزین کرد) ه.) 'اگر محدوده اعتبارسنجی و چسباندن با یکدیگر برابر نیستند، با یک خطا خارج شوید If SearchRange1.Count <> TextRange.Count یا SearchRange2.Count <> TextRange.Count سپس MergeIfs = CVERr(xlErrRef) خروج از تابع پایان اگر از تمام سلول ها عبور کنید، همه شرایط را بررسی کنید و متن را در متغیر OutText جمع آوری کنید برای i = 1 به SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 و SearchRange2.Cells(i) = Condition2 سپس OutText = OutText و TextRange.Cells(i) & Delimeter End If Next من نتایج را بدون آخرین جداکننده نمایش می دهم MergeIfs = Left(OutText، Len(OutText) - Len(Delimeter)) تابع پایان  

دقیقاً به همان روش اعمال خواهد شد - اکنون فقط آرگومان ها باید بیشتر مشخص شوند:

روش 4. گروه بندی و چسباندن در Power Query

اگر از افزونه رایگان Power Query استفاده کنید، می توانید بدون برنامه نویسی در VBA مشکل را حل کنید. برای اکسل 2010-2013 می توان آن را از اینجا دانلود کرد و در اکسل 2016 قبلاً به طور پیش فرض ساخته شده است. ترتیب اقدامات به شرح زیر خواهد بود:

Power Query نمی داند چگونه با جداول معمولی کار کند، بنابراین اولین قدم این است که جدول خود را به یک جدول "هوشمند" تبدیل کنیم. برای انجام این کار، آن را انتخاب کرده و ترکیب را فشار دهید کلیدهای Ctrl+T یا از برگه انتخاب کنید صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول). در برگه ای که ظاهر می شود سازنده (طرح) می توانید نام جدول را تنظیم کنید (من استاندارد را ترک کردم جدول 1):

حالا بیایید جدول خود را در افزونه Power Query بارگذاری کنیم. برای انجام این کار، در برگه داده ها (اگر اکسل 2016 دارید) یا روی تب Power Query (اگر اکسل 2010-2013 دارید) کلیک کنید از روی میز (داده ها - از جدول):

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

روی OK کلیک کنید و یک جدول کوچک از مقادیر گروه بندی شده برای هر شرکت دریافت می کنیم. اگر روی پس‌زمینه سفید سلول‌ها (نه روی متن!) در ستون حاصل کلیک چپ کنید، محتویات جداول به وضوح قابل مشاهده است:

حالا بیایید یک ستون دیگر اضافه کنیم، جایی که با استفاده از تابع، محتویات ستون‌های Address را در هر یک از جدول‌های کوچک با کاما از هم جدا می‌کنیم. برای انجام این کار، در برگه ستون را اضافه کنید فشار می دهیم ستون سفارشی (افزودن ستون - ستون سفارشی) و در پنجره ای که ظاهر می شود، نام ستون جدید و فرمول کوپلینگ را به زبان M تعبیه شده در Power Query وارد کنید:

توجه داشته باشید که همه توابع M به حروف بزرگ و کوچک حساس هستند (برخلاف اکسل). پس از کلیک بر روی OK یک ستون جدید با آدرس های چسبانده شده دریافت می کنیم:

باقی مانده است که ستون غیر ضروری را حذف کنید آدرس های جدول (روی عنوان کلیک راست کنید) حذف ستون) و با کلیک بر روی برگه، نتایج را در برگه آپلود کنید صفحه اصلی — بستن و دانلود کنید (صفحه اصلی - بستن و بارگیری):

نکته مهم: برخلاف روش های قبلی (توابع)، جداول Power Query به طور خودکار به روز نمی شوند. اگر در آینده تغییراتی در داده های منبع ایجاد شود، باید در هر نقطه از جدول نتایج کلیک راست کرده و دستور را انتخاب کنید. به روز رسانی و ذخیره (تازه کردن).

• چگونه یک رشته متن طولانی را به قطعات تقسیم کنیم
• چندین روش برای چسباندن متن از سلول های مختلف به یک
• استفاده از عملگر Like برای تست متن در برابر یک ماسک