کارم پیشتر اینگونه بود: منتظر فایلهای خام میماندم، آنها را یکییکی باز میکردم، دادهها را کپی میکردم و در یک برگهٔ اصلی میچسباندم، نام و قالب ستونها را اصلاح میکردم، ردیفهای زائد را کنار میگذاشتم، همهٔ فایلهای لازم را با هم ترکیب میکردم، فرمولها را به کار میبردم و نمودارها را بهروزرسانی میکردم. سه سال تمام، بیهیچ وقفهای، هر ماه این فرایند را تکرار کردم تا گزارش موردنیاز جلسهٔ بازبینی ماهانهمان آماده شود.
اگر در سازمانی نسبتاً بزرگ کار کنید، این فرایند ممکن است به معنای ترکیب ۱۱۴ فایل از ۱۹ محل، با سه واحد اندازهگیری و سطوح گوناگون محصول باشد. وضعیت من هم همین بود تا اینکه قابلیت Power Query در Excel به کارم آمد.
پایان چرخهٔ کپی و چسباندن
اگر هنوز هر ماه فایلها را دستی ترکیب میکنید، راه بهتری هست
آشنایی من با Power Query از جستوجو برای خودکارسازی گزارشها آغاز نشد. کاملاً اتفاقی دکمهٔ «دریافت داده» را در زبانهٔ «داده» دیدم. از سر کنجکاوی شروع به آزمودنش کردم و به این ترتیب کار با Power Query را آغاز کردم.
ویرایشگر Power Query بخش محبوب من در این ابزار است. این ویرایشگر مانند استودیوی ضبطی برای دگرگونیهای داده عمل میکند و همهٔ کارهایتان را، از حذف ستونها و پالایش ردیفها گرفته تا ادغام جدولها و تغییر نوع داده، دنبال میکند و هر عمل را بهصورت بخشی از یک دنباله در پنل «مراحل اعمالشده» ذخیره میکند. بنابراین کافی است یک دگرگونی را یک بار انجام دهید؛ Power Query آن را به خاطر میسپارد تا دوباره اجرایش کند. هرگاه بخواهید همان مراحل را روی دادههای تازه اجرا کنید، تنها باید روی «نوسازی همه» کلیک کنید.
مهمتر از همه، برای استفاده از Power Query نیازی به برنامهنویسی ندارید؛ زیرا همزمان با اجرای مراحل، کد M را در پسزمینه برایتان تولید میکند. بااینحال، اگر ترجیح میدهید اسکریپت را خودتان بنویسید، «ویرایشگر پیشرفته» امکان ویرایش کد و ساخت خودکارسازیهای اختصاصیتر را فراهم میکند.
Power Query قابلیتهای سودمند فراوانی دارد، اما از نظر من سه مورد بهویژه کاربردیاند. قابلیت «تبدیل ستونها به ردیف» جدولی عریض و خوانا برای انسان را که هفتهها یا ماهها در ستونهای آن آمدهاند، در چند ثانیه به فهرستی مرتب از ردیفهای مناسب برای پایگاه داده تبدیل میکند. گزینهٔ «از پوشه» در بخش «دریافت داده» را هم بسیار دوست دارم؛ زیرا میتوانید Power Query را به یک پوشهٔ محلی یا پوشهای در SharePoint متصل کنید، بیش از ۵۰ فایل Excel یا ۱۰۰ فایل ماهانهٔ CSV را در آن بریزید و اجازه دهید Power Query آنها را خودکار دریافت، پاکسازی و در یک جدول یکپارچه ترکیب کند.
قابلیت سودمند دیگر، ادغام پیشرفتهٔ دادهها در Power Query است که امکان ترکیب منابع ناهمگون، مانند برگههای Excel، فایلهای CSV و پایگاههای دادهٔ SQL را فراهم میکند، بیآنکه میلیونها فرمول شکننده فایل کاری را سنگین و کند کنند. از آنجا که دیگر به فرمولهای حجیم و پرمصرف نیاز ندارید، اندازهٔ فایلهایتان نیز میتواند کمتر شود.
پس از آشنایی با این قابلیتها، دیگر نمیتوانستم فقط برای تولید یک گزارش بهروز، هر ماه برگههایم را کپی و جایگذاری کنم.
از چندین فایل تا یک گزارش بهروزرسانیپذیر
یک بار تنظیم کنید، فایلهای تازه را بیفزایید و کارهای تکراری را به Excel بسپارید
پس از آشنایی بیشتر با دکمهٔ «دریافت داده» در گروه «دریافت و تبدیل داده» در زبانهٔ «داده»ی Excel، روی آن کلیک کردم و «از فایل > از پوشه» را برگزیدم. سپس مسیر دقیق پوشهای را انتخاب کردم که فایلهای گزارش ماهانهام را در آن نگه میدارم و روی «ترکیب و تبدیل داده» کلیک کردم. با این کار، Power Query همهٔ فایلهای موجود در آن پوشهٔ ویژه را میخواند و امکان پالایش، گسترش و یکپارچهسازی خودکارشان را فراهم میکند. برای آنکه دادهها به پاکیزهترین شکل وارد شوند، مطمئن شوید تمام فایلهایی که در این پوشه میگذارید ساختاری یکسان و سرستونهایی کاملاً مشابه دارند.
پس از برقراری اتصال، پنجرهٔ ویرایشگر Power Query باز میشود. اینجا فضای کاری ویژهٔ شما برای پاکسازی و شکلدهی به دادههاست. Power Query هرگز فایلهای مبدأ را تغییر نمیدهد و تنها نمای دادهها را بازآرایی میکند. هر کار پاکسازی که انجام میدهید، مانند حذف ستونهای غیرضروری، ارتقای ردیف نخست به سرستون یا تغییر نوع داده، بهطور خودکار در قالب مرحلهای جداگانه و نامگذاریشده ثبت میشود. هر زمان بخواهید میتوانید این کارها را در بخش «مراحل اعمالشده» از پنل «تنظیمات پرسوجو» ببینید، ویرایش کنید یا ترتیبشان را تغییر دهید.
از آنجا که فایلهای خام من چیدمانی عریض داشتند و هر ستون نمایندهٔ هفته یا ماهی جداگانه بود، باید آنها را به قالبی استاندارد درمیآوردم. ستون ثابتی را که میخواستم حفظ شود، مانند ستون «نام»، انتخاب کردم، روی آن راستکلیک کردم و گزینهٔ «تبدیل سایر ستونها به ردیف» را برگزیدم. Power Query بیدرنگ آن ستونهای افقی را به ردیفهای عمودی و مرتب تبدیل میکند. به این ترتیب، جدول عریض شما به ساختاری استاندارد و مناسب برای پایگاه داده درمیآید که با جدولهای محوری و فرمولها نیز بهخوبی کار میکند.
پس از آنکه دادههایتان کاملاً دگرگون شدند و ستونهای آنها از حالت محوری بیرون آمدند، در ویرایشگر به زبانه «خانه» بروید و روی «بستن و بارگذاری» کلیک کنید. با این کار، دادههای نهایی بهطور پیشفرض در قالب جدولی ساختیافته در کارپوشه Excel بارگذاری میشوند؛ البته اگر ترجیح دهید، میتوانید آنها را مستقیماً به «مدل داده» رابطهای Excel بفرستید.
مزیت این سامانه خودکار آن است که با رسیدن دادههای ماه جدید، دیگر نیازی نیست هیچیک از کارهای دستی را تکرار کنید. کافی است پرونده خام تازه را در پوشه تعیینشده قرار دهید، کارپوشه اصلی را باز کنید و در زبانه «داده» روی «بازآوری همه» کلیک کنید.
Power Query کد M را در پسزمینه اجرا میکند و تمام مراحل روند کاریتان را دوباره انجام میدهد؛ از خواندن پرونده جدید و اعمال دگرگونیها گرفته تا خارجکردن ستونها از حالت محوری و بهروزرسانی گزارش نهایی Excel، آن هم تنها در چند ثانیه.
زمان کمتر برای ساخت گزارش، فرصت بیشتر برای شنیدن حرف دادهها
با خودکارسازی ساخت گزارش در Excel، بهجای آمادهسازی دادهها میتوانید زمان بیشتری را صرف تحلیل آنها کنید. من پس از کنار هم گذاشتن چندین پرونده توانستم گلوگاههای مهم عملیاتی را سریعتر و آسانتر شناسایی کنم. برای نمونه، دریافتم نزدیک به ۵۰% سفارشهای دیرکرده در یک منطقه و در روز مشخصی از هفته متمرکز بودهاند؛ نکتهای که وقتی بیشتر وقتم صرف آمادهسازی دستی گزارش میشد، تشخیص آن بسیار دشوارتر بود.
اگر مدیر یا کارشناس منابع انسانی هستید نیز یادگیری Power Query بیفایده نخواهد بود. آموختن شیوه کار با دادهها، شما را یک گام به تبدیلشدن به دانشمند داده نزدیکتر میکند؛ و داشتن چنین مهارتی هیچگاه بد نیست.
منبع: این مطلب ترجمه و بومیسازی مقالهای از MakeUseOf به قلم Adaeze Uche است. مشاهده مقاله اصلی