جایگزینی متن انبوه در Power Query با تابع List.Acumulate

نحوه جایگزینی سریع و انبوه متن طبق لیست مرجع با فرمول ها - قبلاً آن را مرتب کرده ایم. حالا بیایید سعی کنیم آن را در Power Query انجام دهیم.

همانطور که اغلب اتفاق می افتد انجام دادن این کار بسیار ساده تر از توضیح دادن است چرا کار می کند، اما بیایید سعی کنیم هر دو را انجام دهیم 🙂

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

میز اول را صدا زدم داده ها، جدول دوم – فهرست راهنمابا استفاده از زمینه نام جدول (نام جدول) برگ سازنده (طرح).

وظیفه: آدرس های موجود در جدول را جایگزین کنید داده ها همه رخدادها از یک ستون برای پیدا کردن کتاب راهنما به همتایان صحیح مربوطه خود از ستون جایگزین. بقیه متن در سلول ها باید دست نخورده باقی بماند.

مرحله 1. دایرکتوری را در Power Query بارگذاری کنید و آن را به یک لیست تبدیل کنید

پس از تنظیم سلول فعال در هر مکانی از جدول مرجع، روی برگه کلیک کنید داده ها (تاریخ)یا روی برگه پرس و جو برق (اگر نسخه قدیمی اکسل دارید و Power Query را به عنوان افزونه در یک برگه جداگانه نصب کرده اید) روی دکمه از جدول / محدوده (از جدول/محدوده).

جدول مرجع در ویرایشگر Power Query بارگذاری می شود:

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

حال برای انجام تغییرات و جایگزینی های بیشتر، باید این جدول را به یک لیست (فهرست) تبدیل کنیم.

انحراف غزلی

• جدول یک آرایه دو بعدی است که از چندین ردیف و ستون تشکیل شده است.
• رکورد (رکورد) - رشته آرایه یک بعدی، متشکل از چندین فیلد-عنصر با نام، به عنوان مثال [نام = "ماشا"، جنسیت = "f"، سن = 25]
  
• فهرست - یک آرایه-ستون یک بعدی که از چندین عنصر تشکیل شده است، برای مثال {1، 2، 3، 10، 42} or { "ایمان عشق امید" }
  

برای حل مشکل ما در درجه اول به نوع علاقه مند خواهیم بود فهرست.

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

{

    [ یافتن = «St. پترزبورگ، جایگزین = «سنت. پترزبورگ"] ,

    [ یافتن = «St. پترزبورگ، جایگزین = «سنت. پترزبورگ"] ,

    [ Find = "Peter", Replace = "St. پترزبورگ"] ,

و غیره.

}

چنین تبدیلی با استفاده از یک تابع خاص از زبان M ساخته شده در Power Query انجام می شود - Table.ToRecords. برای اعمال مستقیم آن در نوار فرمول، این تابع را به کد مرحله در آنجا اضافه کنید منبع.

بود:

بعد از:

پس از افزودن تابع Table.ToRecords، ظاهر جدول ما تغییر می کند - به لیستی از رکوردها تبدیل می شود. محتویات رکوردهای فردی را می توان با کلیک بر روی پس زمینه سلول در کنار هر کلمه ای در پایین صفحه نمایش مشاهده کرد. رکورد (اما نه در یک کلمه!)

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

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

این کار آماده سازی کتاب راهنما را تکمیل می کند.

باقی مانده است که روی آن کلیک کنید صفحه اصلی – بستن و بارگذاری – بستن و بارگیری برای… (صفحه اصلی — بستن و بارگذاری — بستن و بارگذاری به..)، یک گزینه را انتخاب کنید فقط یک اتصال ایجاد کنید (فقط ایجاد اتصال) و به اکسل برگردید.

مرحله 2. بارگذاری جدول داده ها

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

هیچ اقدام تدارکاتی خاصی لازم نیست که با آن انجام شود و ما به مهمترین چیز می رویم.

مرحله 3. با استفاده از تابع List.Acumulate جایگزینی را انجام دهید

بیایید با استفاده از دستور یک ستون محاسبه شده به جدول داده های خود اضافه کنیم اضافه کردن یک ستون - ستون سفارشی (افزودن ستون - ستون سفارشی): و نام ستون اضافه شده را در پنجره باز شده وارد کنید (به عنوان مثال، آدرس تصحیح شد) و عملکرد جادویی ما List.Acumulate:

باقی مانده است که روی آن کلیک کنید OK – و یک ستون با جایگزین های انجام شده دریافت می کنیم:

توجه داشته باشید که:

• از آنجایی که Power Query به حروف کوچک و بزرگ حساس است، هیچ جایگزینی در خط ماقبل آخر وجود نداشت، زیرا در دایرکتوری ما "SPb" داریم، نه "SPb".
• اگر چندین رشته فرعی برای جایگزینی همزمان در داده های منبع وجود داشته باشد (به عنوان مثال، در خط 7 شما باید هر دو "S-Pb" و "Prospectus" را جایگزین کنید)، این کار هیچ مشکلی ایجاد نمی کند (برخلاف جایگزینی با فرمول های روش قبلی).
• اگر چیزی برای جایگزینی در متن منبع (خط نهم) وجود نداشته باشد، هیچ خطایی رخ نمی دهد (بر خلاف جایگزینی با فرمول ها).

سرعت چنین درخواستی بسیار بسیار مناسب است. به عنوان مثال، برای جدول داده های اولیه با اندازه 5000 ردیف، این پرس و جو در کمتر از یک ثانیه به روز شد (به هر حال، بدون بافر، حدود 3 ثانیه!)

نحوه عملکرد تابع List.Acumulate

در اصل، این می تواند پایان این مقاله باشد (برای من که بنویسم و ​​شما بخوانید). اگر می خواهید نه تنها بتوانید، بلکه درک کنید که چگونه "زیر کاپوت" کار می کند، باید کمی عمیق تر در سوراخ خرگوش شیرجه بزنید و با عملکرد List.Acumulate مقابله کنید، که تمام جایگزینی عمده را انجام داد. برای ما کار کن

سینتکس این تابع به صورت زیر است:

=List.Acumulate(فهرست, دانه, باتری)

جایی که

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

به طور کلی، نحو برای نوشتن توابع در Power Query به شکل زیر است:

(argument1, argument2, … argumentN) => برخی از اقدامات با آرگومان ها

به عنوان مثال، تابع جمع می تواند به صورت زیر نمایش داده شود:

(a, b) => a + b

برای List.Accumulate، این تابع انباشته دارای دو آرگومان مورد نیاز است (آنها را می توان هر چیزی نامید، اما نام های معمول عبارتند از بود и جاری، مانند راهنمای رسمی این تابع، که در آن:

• بود - متغیری که در آن نتیجه جمع می شود (مقدار اولیه آن همان است که در بالا ذکر شد دانه)
  
• جاری - مقدار تکرار شده بعدی از لیست فهرست
  

برای مثال، بیایید نگاهی به مراحل منطق ساخت زیر بیندازیم:

=List.Acumulate({3، 2، 5}, 10, (وضعیت، فعلی) => حالت + جریان)

این آخرین انباشته است بود مقدار List. Accumulate تابع و خروجی های ما است در نتیجه:

اگر کمی خیال پردازی کنید، سپس با استفاده از تابع List.Accumulate، می توانید برای مثال، تابع Excel CONCATENATE را شبیه سازی کنید (در Power Query، آنالوگ آن نامیده می شود. متن.ترکیب) با استفاده از عبارت:

یا حتی حداکثر مقدار را جستجو کنید (تقلید از تابع MAX اکسل که در Power Query نامیده می شود لیست. حداکثر):

با این حال، ویژگی اصلی List.Acumulate توانایی پردازش نه تنها متن ساده یا لیست های عددی به عنوان آرگومان است، بلکه اشیاء پیچیده تر را نیز پردازش می کند - به عنوان مثال، لیست ها از لیست ها یا لیست ها از رکوردها (سلام، دایرکتوری!)

بیایید دوباره به ساختاری نگاه کنیم که جایگزینی را در مشکل ما انجام داد:

List.Acumulate(فهرست راهنما, [نشانی], (وضعیت، فعلی) => Text.Replace(وضعیت، فعلی[یافتن]، فعلی[جایگزین]) )

واقعا اینجا چه خبر است؟

1. به عنوان مقدار اولیه (دانه) اولین متن ناشیانه را از ستون می گیریم [نشانی] جدول ما: 199034، سنت پترزبورگ، خ. برینگا، د. 1
2. سپس List.Accumulate روی عناصر لیست یک به یک تکرار می شود – کتاب راهنما. هر عنصر از این لیست یک رکورد متشکل از یک جفت فیلد "What to find – What to جایگزین با" یا به عبارت دیگر خط بعدی در فهرست است.
3. تابع accumulator در یک متغیر قرار می دهد بود مقدار اولیه (آدرس اول 199034، سنت پترزبورگ، خ. برینگا، د. 1) و یک عملکرد انباشت کننده را روی آن انجام می دهد - عملیات جایگزینی با استفاده از تابع استاندارد M متن. جایگزین کنید (مشابه با تابع SUBSTITUTE اکسل). نحو آن این است:

   Text.Replace (متن اصلی، آنچه ما به دنبال آن هستیم، چیزی که با آن جایگزین می کنیم)

   و در اینجا داریم:

• بود آدرس کثیف ماست که در آن نهفته است بود (رسیدن به آنجا از دانه)
  
• فعلی[جستجو] - مقدار فیلد برای پیدا کردن از ورودی تکراری بعدی لیست فهرست راهنما، که در متغیر قرار دارد جاری
• فعلی[جایگزین] - مقدار فیلد جایگزین از ورودی تکراری بعدی لیست فهرست راهنمادراز کشیده در جاری

بنابراین، برای هر آدرس، یک چرخه کامل از شمارش تمام خطوط در دایرکتوری هر بار اجرا می شود و متن فیلد [Find] را با مقدار فیلد [Replace] جایگزین می کند.

امیدوارم ایده گرفته باشید 🙂

• با استفاده از فرمول ها، متن را در لیست جایگزین انبوه کنید
  
• عبارات منظم (RegExp) در Power Query