اخرین حرف

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

بیایید به چندین روش سنتی برای انتخاب نگاه کنیم: فرمول ها، ماکروها و از طریق Power Query.

روش 1. فرمول ها

برای سهولت در درک ماهیت و مکانیزم فرمول، اجازه دهید کمی از دور شروع کنیم. ابتدا بیایید تعداد فاصله های بین کلمات را در متن منبع خود به عنوان مثال به 20 قطعه افزایش دهیم. شما می توانید این کار را با تابع جایگزین انجام دهید. جایگزین (جایگزین) و تابع تکرار یک کاراکتر معین N-بار – تکرار (REPT):

حالا با استفاده از تابع 20 کاراکتر از انتهای متن به دست آمده را قطع می کنیم RIGHT (درست):

گرم تر می شود، درست است؟ باقی مانده است که فضاهای اضافی را با استفاده از عملکرد حذف کنید TRIM (TRIM) و مشکل حل خواهد شد:

در نسخه انگلیسی، فرمول ما به این صورت خواهد بود:

=TRIM(RIGHT(SUBSTITUTE(A1;» «;REPT(» «;20));20))

امیدوارم واضح باشد که اصولاً لازم نیست دقیقاً 20 فاصله درج شود - هر عددی تا زمانی که از طول طولانی‌ترین کلمه در متن منبع بیشتر باشد انجام می‌شود.

و اگر متن مبدأ باید نه با فاصله، بلکه با یک کاراکتر جداکننده دیگر (به عنوان مثال، با کاما) تقسیم شود، فرمول ما باید کمی اصلاح شود:

روش 2. تابع ماکرو

وظیفه استخراج آخرین کلمه یا قطعه از متن را نیز می توان با استفاده از ماکروها حل کرد، یعنی نوشتن یک تابع جستجوی معکوس در ویژوال بیسیک که آنچه ما نیاز داریم را انجام می دهد - جستجوی یک زیر رشته داده شده در یک رشته در جهت مخالف - از پایان به آغاز

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

 تابع LastWord(txt به عنوان رشته، delim اختیاری به عنوان رشته = " "، n اختیاری به عنوان عدد صحیح = 1) به عنوان رشته arFragments = Split(txt، delim) LastWord = arFragments(UBound(arFragments) - n + 1) تابع پایان  

اکنون می توانید کتاب کار را ذخیره کنید (در قالب ماکرو فعال!) و از تابع ایجاد شده در دستور زیر استفاده کنید:

=LastWord(txt ; delim ; n)

جایی که

• کلیپ برد چند منظوره - سلول با متن منبع
• گول زدن - کاراکتر جداکننده (پیش‌فرض - فاصله)
• n – چه کلمه ای باید از آخر استخراج شود (به طور پیش فرض – اول از آخر)

با هر تغییری در متن مبدأ در آینده، عملکرد ماکرو ما در همان لحظه، مانند هر تابع صفحه استاندارد اکسل، دوباره محاسبه می‌شود.

روش 3. Power Query

پرس و جو برق یک افزونه رایگان از مایکروسافت برای وارد کردن داده ها به اکسل تقریباً از هر منبع و سپس تبدیل داده های دانلود شده به هر شکلی است. قدرت و خنکی این افزونه به قدری عالی است که مایکروسافت تمام ویژگی های آن را به صورت پیش فرض در اکسل 2016 قرار داده است. برای Excel 2010-2013 Power Query را می توان به صورت رایگان از اینجا دانلود کرد.

وظیفه ما برای جدا کردن آخرین کلمه یا قطعه از طریق جداکننده داده شده با استفاده از Power Query بسیار آسان است.

ابتدا، بیایید جدول داده های خود را با استفاده از میانبرهای صفحه کلید به یک جدول هوشمند تبدیل کنیم. کلیدهای Ctrl+T یا دستورات صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول):

سپس «جدول هوشمند» ایجاد شده را با استفاده از دستور در Power Query بارگذاری می کنیم از جدول / محدوده (از جدول/محدوده) برگ داده ها (اگر اکسل 2016 دارید) یا روی برگه پرس و جو برق (اگر اکسل 2010-2013 دارید):

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

پس از کلیک روی OK کلمه آخر به یک ستون جدید جدا می شود. ستون اول غیر ضروری را می توان با کلیک راست روی سرصفحه و انتخاب آن حذف کرد برداشتن (حذف). همچنین می توانید نام ستون باقیمانده در سرفصل جدول را تغییر دهید.

نتایج را می توان با استفاده از دستور به برگه بارگذاری کرد صفحه اصلی — بستن و بارگذاری — بستن و بارگذاری برای … (صفحه اصلی — بستن و بارگذاری — بستن و بارگیری به…):

و در نتیجه می گیریم:

مانند این - ارزان و شاد، بدون فرمول و ماکرو، تقریبا بدون لمس صفحه کلید 🙂

اگر لیست اصلی در آینده تغییر کند، کلیک راست یا استفاده از میانبر صفحه کلید کافی است کلیدهای Ctrl+دگرساز+F5 درخواست ما را به روز کنید

• تقسیم متن چسبنده به ستون
• تجزیه و تحلیل متن با عبارات منظم
• استخراج اولین کلمات از متن با تابع SUBSTITUTE