جستجوی کلمات کلیدی در متن منبع یکی از رایج ترین کارها هنگام کار با داده است. بیایید با استفاده از مثال زیر به راه حل آن از چند جهت نگاه کنیم:
فرض کنید من و شما لیستی از کلمات کلیدی – نام برندهای خودرو – و یک جدول بزرگ از انواع قطعات یدکی داریم که در آن توضیحات می‌تواند گاهی شامل یک یا چند برند در یک زمان باشد، اگر قطعه یدکی با بیش از یک مورد مطابقت داشته باشد. مارک ماشین وظیفه ما یافتن و نمایش تمام کلمات کلیدی شناسایی شده در سلول های همسایه از طریق یک کاراکتر جداکننده (مثلاً یک کاما) است.
روش 1. Power Query
البته، ابتدا جداول خود را با استفاده از میانبر صفحه کلید به پویا ("هوشمند") تبدیل می کنیم کلیدهای Ctrl+T یا دستورات صفحه اصلی – قالب بندی به صورت جدول (صفحه اصلی - قالب بندی به عنوان جدول)، به آنها اسامی بدهید (مثلا تمبرи لوازم یدکی) و با انتخاب روی تب یکی یکی در ویرایشگر Power Query بارگیری کنید داده ها - از جدول / محدوده (داده ها - از جدول/محدوده). اگر نسخه های قدیمی اکسل 2010-2013 دارید که در آن Power Query به عنوان یک افزونه جداگانه نصب شده است، دکمه مورد نظر در برگه خواهد بود. پرس و جو برق. اگر نسخه کاملاً جدید اکسل 365 دارید، دکمه از جدول/محدوده الان آنجا تماس گرفت با برگ (از برگه).
پس از بارگذاری هر جدول در Power Query، با دستور به اکسل برمی گردیم صفحه اصلی — بستن و بارگیری — بستن و بارگیری در… — فقط اتصال ایجاد کنید (صفحه اصلی — بستن و بارگذاری — بستن و بارگیری به… — فقط ایجاد اتصال).
حالا بیایید یک درخواست تکراری ایجاد کنیم لوازم یدکیبا کلیک راست روی آن و انتخاب درخواست تکراری (پرس و جو تکراری)، سپس نام درخواست کپی به دست آمده را به تغییر نام دهید نتایج و ما به همکاری با او ادامه خواهیم داد.
منطق اعمال به شرح زیر است:
- در برگه Advanced اضافه کردن یک ستون یک تیم انتخاب کنید ستون سفارشی (افزودن ستون - ستون سفارشی) و فرمول را وارد کنید = برندها. پس از کلیک بر روی OK ما یک ستون جدید خواهیم داشت که در هر سلول یک جدول تودرتو با لیستی از کلمات کلیدی ما وجود دارد - مارک های خودروساز:
- از دکمه ای با فلش های دوتایی در هدر ستون اضافه شده برای گسترش همه جداول تودرتو استفاده کنید. در همان زمان، خطوط با توضیحات قطعات یدکی در مضربی از تعداد مارک ها ضرب می شوند و ما همه جفت-ترکیب های ممکن "مارک قطعات یدکی" را دریافت خواهیم کرد:
- در برگه Advanced اضافه کردن یک ستون یک تیم انتخاب کنید ستون مشروط (ستون شرطی) و شرطی را برای بررسی وقوع یک کلمه کلیدی (برند) در متن منبع (شرح قسمت) تنظیم کنید:
- برای غیر حساس کردن حروف جستجو، آرگومان سوم را به صورت دستی در نوار فرمول اضافه کنید Compare.OrdinalIgnoreCase به تابع بررسی وقوع متن. حاوی (اگر نوار فرمول قابل مشاهده نیست، می توان آن را در برگه فعال کرد مرور):
- جدول به دست آمده را فیلتر می کنیم و تنها یکی را در آخرین ستون باقی می گذاریم، یعنی مطابقت دارد و ستون غیر ضروری را حذف می کنیم. ظهور.
- گروه بندی توضیحات یکسان با دستور دسته بندی بر اساس برگ دگرگونی (تبدیل - گروه بندی بر اساس). به عنوان یک عملیات تجمیع، انتخاب کنید همه خطوط (همه ردیف ها). در خروجی، ستونی با جداول دریافت می کنیم که شامل تمام جزئیات برای هر قطعه یدکی، از جمله مارک های خودروسازان مورد نیاز ما است:
- برای استخراج نمرات برای هر قسمت، ستون محاسبه شده دیگری را در برگه اضافه کنید اضافه کردن یک ستون - ستون سفارشی (افزودن ستون - ستون سفارشی) و از فرمولی متشکل از یک جدول استفاده کنید (آنها در ستون ما قرار دارند جزئیات) و نام ستون استخراج شده:
- روی دکمه با فلش های دوتایی در هدر ستون حاصل کلیک می کنیم و دستور را انتخاب می کنیم استخراج مقادیر (استخراج مقادیر)برای خروجی تمبر با هر کاراکتر جداکننده که می خواهید:
- حذف یک ستون غیر ضروری جزئیات.
- برای افزودن قطعاتی که از آن ناپدید شده‌اند، جایی که هیچ مارکی در توضیحات یافت نشد، به جدول حاصل، رویه‌ای را برای ترکیب پرس و جو انجام می‌دهیم. نتیجه با درخواست اصلی لوازم یدکی دکمه ترکیب کردن برگ صفحه اصلی (صفحه اصلی - ادغام سوالات). نوع اتصال - خارجی Join Right (اتصال بیرونی سمت راست):
- تنها چیزی که باقی می ماند حذف ستون های اضافی و تغییر نام و جابجایی ستون های باقی مانده است - و وظیفه ما حل می شود:
روش 2. فرمول ها
اگر نسخه ای از اکسل 2016 یا جدیدتر دارید، مشکل ما را می توان به روشی بسیار فشرده و ظریف با استفاده از عملکرد جدید حل کرد. آمیختن (TEXTJOIN):
منطق پشت این فرمول ساده است:
- عملکرد جستجو (پیدا کردن) وقوع هر مارک را به نوبه خود در توضیحات فعلی قطعه جستجو می کند و یا شماره سریال نمادی را که برند از آنجا پیدا شده است یا خطای #VALUE را برمی گرداند! اگر نام تجاری در توضیحات موجود نیست.
- سپس با استفاده از تابع IF (اگر) и EOSHIBKA (ISERROR) خطاها را با یک رشته متن خالی «» و اعداد ترتیبی کاراکترها را با نام های تجاری خود جایگزین می کنیم.
- آرایه حاصل از سلول های خالی و مارک های یافت شده در یک رشته واحد از طریق یک کاراکتر جداکننده داده شده با استفاده از تابع مونتاژ می شود. آمیختن (TEXTJOIN).
مقایسه عملکرد و بافر پرس و جوی نیرو برای افزایش سرعت
برای تست عملکرد، اجازه دهید جدولی از 100 توضیحات قطعات یدکی را به عنوان داده های اولیه در نظر بگیریم. بر روی آن نتایج زیر را دریافت می کنیم:
- زمان محاسبه مجدد با فرمول (روش 2) - 9 ثانیه. هنگامی که برای اولین بار فرمول را در کل ستون و 2 ثانیه کپی می کنید. در مکرر (احتمالاً بافر تأثیر می گذارد).
- زمان به روز رسانی کوئری Power Query (روش 1) بسیار بدتر است - 110 ثانیه.
البته خیلی به سخت افزار یک کامپیوتر شخصی خاص و نسخه نصب شده آفیس و آپدیت ها بستگی دارد، اما تصویر کلی به نظر من واضح است.
برای سرعت بخشیدن به یک کوئری Power Query، بیایید جدول جستجو را بافر کنیم تمبر، زیرا در فرآیند اجرای پرس و جو تغییر نمی کند و نیازی به محاسبه مجدد آن نیست (همانطور که Power Query به طور واقعی انجام می دهد). برای این کار از تابع استفاده می کنیم جدول.بافر از زبان داخلی Power Query M.
برای انجام این کار، یک کوئری باز کنید نتایج و روی زبانه مرور دکمه را فشار دهید ویرایشگر پیشرفته (مشاهده - ویرایشگر پیشرفته). در پنجره ای که باز می شود، یک خط با یک متغیر جدید اضافه کنید مارکی 2، که یک نسخه بافر از دایرکتوری automaker ما خواهد بود و بعداً از این متغیر جدید در دستور query زیر استفاده کنید:
پس از چنین اصلاحاتی، سرعت به روز رسانی درخواست ما تقریباً 7 برابر افزایش می یابد - تا 15 ثانیه. یه چیز کاملا متفاوت 🙂
- جستجوی متن فازی در Power Query
- جایگزینی متن انبوه با فرمول
- جایگزینی متن انبوه در Power Query با تابع List.Acumulate