فهرست
یکی از پربازدیدترین ویدیوها در کانال یوتیوب من، ویدیویی درباره Flash Fill در مایکروسافت اکسل است. ماهیت این ابزار این است که اگر نیاز دارید به نحوی داده های منبع خود را تغییر دهید، فقط باید نتیجه ای را که می خواهید در ستون مجاور به دست آورید تایپ کنید. پس از چندین سلول تایپ شده به صورت دستی (معمولاً 2-3 کافی است)، اکسل منطق تحولات مورد نیاز شما را "درک" می کند و به طور خودکار آنچه را تایپ کرده اید ادامه می دهد و تمام کارهای یکنواخت را برای شما تکمیل می کند:
اصل کارایی. دکمه جادویی «آن را درست انجام بده» که همه ما خیلی دوستش داریم، درست است؟
در واقع، یک آنالوگ از چنین ابزاری در Power Query وجود دارد - در آنجا نامیده می شود ستون از نمونه ها (ستون از نمونه ها). در واقع، این یک هوش مصنوعی کوچک است که در Power Query تعبیه شده است که می تواند به سرعت از داده های شما یاد بگیرد و سپس آن را تغییر دهد. بیایید نگاهی دقیق تر به قابلیت های آن در چندین سناریو عملی بیندازیم تا بفهمیم کجا می تواند در کارهای واقعی برای ما مفید باشد.
مثال 1. چسباندن/برش متن
بیایید بگوییم که چنین جدول "هوشمندی" در اکسل با داده های مربوط به کارمندان داریم:
آن را به روش استاندارد در Power Query بارگیری کنید - با دکمه از جدول/محدوده برگ داده ها (داده ها - از جدول/محدوده).
فرض کنید باید برای هر کارمند یک ستون با نام خانوادگی و حروف اول اضافه کنیم (Ivanov SV برای اولین کارمند و غیره). برای حل این مشکل می توانید از یکی از دو روش زیر استفاده کنید:
- روی عنوان ستون با داده های منبع راست کلیک کرده و دستور را انتخاب کنید از نمونه ها ستون اضافه کنید (اضافه کردن ستون از نمونه ها);
- یک یا چند ستون را با داده و در برگه انتخاب کنید اضافه کردن یک ستون یک تیم انتخاب کنید ستون از نمونه ها. در اینجا، در لیست کشویی، می توانید تعیین کنید که آیا تمام یا فقط ستون های انتخاب شده نیاز به تجزیه و تحلیل دارند.
سپس همه چیز ساده است - در ستونی که در سمت راست ظاهر می شود، شروع به وارد کردن نمونه هایی از نتایج مورد نظر می کنیم، و هوش مصنوعی ساخته شده در Power Query سعی می کند منطق تبدیل ما را درک کند و به تنهایی ادامه دهد:
ضمناً در هر خانه ای از این ستون می توانید گزینه های صحیح را وارد کنید، یعنی نه لزوماً از بالا به پایین و پشت سر هم. همچنین، می‌توانید بعداً با استفاده از چک باکس‌های موجود در نوار عنوان، به راحتی ستون‌هایی را از تجزیه و تحلیل اضافه یا حذف کنید.
به فرمول بالای پنجره توجه کنید - این همان چیزی است که Power Query هوشمند برای به دست آوردن نتایج مورد نیاز ما ایجاد می کند. به هر حال، این تفاوت اساسی بین این ابزار و پر کردن فوری در اکسل پر کردن فوری مانند یک "جعبه سیاه" عمل می کند - آنها منطق تحولات را به ما نشان نمی دهند، بلکه به سادگی نتایج آماده ای را ارائه می دهند و ما آنها را بدیهی می گیریم. در اینجا همه چیز شفاف است و همیشه می توانید کاملاً به وضوح بفهمید که دقیقاً با داده ها چه اتفاقی می افتد.
اگر می‌بینید که Power Query "این ایده را گرفته است"، می‌توانید با خیال راحت دکمه را فشار دهید OK یا میانبر صفحه کلید کلیدهای Ctrl+وارد - یک ستون سفارشی با فرمول اختراع شده توسط Power Query ایجاد می شود. به هر حال، بعداً می توان آن را به راحتی به عنوان یک ستون معمولی ایجاد شده به صورت دستی (با دستور اضافه کردن یک ستون - ستون سفارشی) با کلیک بر روی نماد چرخ دنده در سمت راست نام مرحله:
مثال 2: حروف مانند در جملات
اگر روی عنوان ستون با متن راست کلیک کرده و دستور را انتخاب کنید دگرگونی (تبدیل)، سپس می توانید سه دستور مسئول تغییر رجیستر را مشاهده کنید:
راحت و جالب است، اما در این لیست، برای مثال، من شخصاً همیشه یک گزینه دیگر نداشتم - موردی مانند جملات، زمانی که حروف بزرگ (بزرگ) نه حرف اول هر کلمه، بلکه فقط حرف اول در سلول می شود، و بقیه متن زمانی که This با حروف کوچک (کوچک) نمایش داده می شود.
این ویژگی گم شده با هوش مصنوعی به راحتی قابل پیاده سازی است ستون هایی از نمونه ها - کافی است چند گزینه را برای Power Query وارد کنید تا با همان روحیه ادامه دهید:
به عنوان یک فرمول در اینجا، Power Query از دسته ای از توابع استفاده می کند متن.بالا и متن. پایین، تبدیل متن به حروف بزرگ و کوچک به ترتیب و توابع متن.شروع и متن. اواسط - آنالوگ توابع Excel LEFT و PSTR، قادر به استخراج زیر رشته از متن از سمت چپ و از وسط.
مثال 3. جایگشت کلمات
گاهی اوقات، هنگام پردازش داده های دریافتی، لازم است که کلمات موجود در سلول ها را در یک دنباله معین بازآرایی کنیم. البته می‌توانید ستون را با جداکننده به ستون‌های کلمه جداگانه تقسیم کنید و سپس به ترتیب مشخص شده (فراموش نکنید که فاصله اضافه کنید) آن را دوباره بچسبانید، اما به کمک ابزار ستون از نمونه ها همه چیز بسیار ساده تر خواهد بود:
مثال 4: فقط اعداد
یکی دیگر از وظایف بسیار حیاتی این است که فقط اعداد (اعداد) را از محتویات سلول بیرون بکشید. مانند قبل، پس از بارگذاری داده ها در Power Query، به تب بروید اضافه کردن یک ستون - ستون از نمونه ها و چند سلول را به صورت دستی پر کنید تا برنامه بفهمد دقیقاً چه چیزی می خواهیم دریافت کنیم:
یکنوع بازی شبیه لوتو!
مجدداً، ارزش آن را دارد که به بالای پنجره نگاه کنید تا مطمئن شوید که Query فرمول را به درستی تولید کرده است - در این مورد حاوی یک تابع است. متن انتخاب کنید، که همانطور که ممکن است حدس بزنید، کاراکترهای داده شده را از متن منبع مطابق لیست استخراج می کند. متعاقباً، این فهرست، البته، در صورت لزوم به راحتی در نوار فرمول قابل ویرایش است.
مثال 5: فقط متن
مشابه مثال قبلی، می‌توانید بیرون بکشید و برعکس - فقط متن، حذف همه اعداد، علائم نگارشی و غیره.
در این مورد، از تابعی استفاده می‌شود که از قبل از نظر معنی مخالف است - Text.Remove، که کاراکترها را از رشته اصلی طبق یک لیست مشخص حذف می‌کند.
مثال 6: استخراج داده ها از یک فرنی الفبایی
Power Query همچنین می تواند در موارد دشوارتر کمک کند، زمانی که شما نیاز به استخراج اطلاعات مفید از حروف الفبای عددی در یک سلول دارید، به عنوان مثال، شماره حساب را از شرح هدف پرداخت در صورت حساب بانکی دریافت کنید:
توجه داشته باشید که فرمول تبدیل Power Query می تواند بسیار پیچیده باشد:
برای سهولت در خواندن و درک، می توان آن را با استفاده از یک سرویس آنلاین رایگان به شکل بسیار منطقی تر تبدیل کرد. Power Query Formatter:
چیز بسیار مفید - احترام به سازندگان!
مثال 7: تبدیل تاریخ
ابزار ستون از نمونه ها را می توان برای ستون های تاریخ یا تاریخ نیز اعمال کرد. وقتی اولین ارقام یک تاریخ را وارد می‌کنید، Power Query فهرستی از تمام گزینه‌های تبدیل ممکن را نمایش می‌دهد:
بنابراین می توانید به راحتی تاریخ اصلی را به هر فرمت عجیب و غریب مانند "سال-ماه-روز" تبدیل کنید:
مثال 8: دسته بندی
اگر از ابزار استفاده کنیم ستون از نمونه ها به ستونی با داده های عددی، متفاوت عمل می کند. فرض کنید نتایج آزمون کارکنان را در Power Query بارگذاری کرده ایم (نمرات شرطی در محدوده 0-100) و از درجه بندی شرطی زیر استفاده می کنیم:
- کارشناسی ارشد - کسانی که بیش از 90 امتیاز گرفتند
- خبرگان - از 70 تا 90 امتیاز گرفتند
- کاربران - از 30 تا 70
- مبتدیان - کسانی که امتیاز کمتر از 30 را کسب کردند
اگر ستونی از مثال‌ها را به لیست اضافه کنیم و مرتب کردن این درجه‌بندی‌ها را به صورت دستی شروع کنیم، خیلی زود Power Query ایده ما را برمی‌دارد و ستونی با فرمول اضافه می‌کند، جایی که عملگرها در یکدیگر قرار می‌گیرند. if منطق پیاده سازی خواهد شد، بسیار شبیه به آنچه ما نیاز داریم:
باز هم نمی توانید وضعیت را تا انتها فشار دهید، بلکه روی آن کلیک کنید OK و سپس مقادیر آستانه را از قبل در فرمول اصلاح کنید - به این ترتیب سریعتر است:
نتیجه گیری
مطمئنا یک ابزار ستون از نمونه ها یک "قرص جادویی" نیست و دیر یا زود، موقعیت های غیر استاندارد یا موارد نادیده گرفته شده "مزرعه جمعی" در داده ها وجود خواهد داشت، زمانی که Power Query با شکست مواجه می شود و نمی تواند آنچه را که می خواهیم انجام دهد. به درستی برای ما با این حال، به عنوان یک ابزار کمکی، بسیار خوب است. به علاوه، با مطالعه فرمول هایی که او تولید کرده است، می توانید دانش خود را در مورد عملکردهای زبان M گسترش دهید، که همیشه در آینده مفید خواهد بود.
- تجزیه متن با عبارات منظم (RegExp) در Power Query
- جستجوی متن فازی در Power Query
- مایکروسافت اکسل را با فلش پر کنید