جمع آوری جداول از فایل های مختلف اکسل با Power Query

فرمول بندی مسئله

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

فرض کنید پوشه زیر را داریم که حاوی چندین فایل با داده های شهرهای شعبه است:

تعداد فایل ها مهم نیست و ممکن است در آینده تغییر کند. هر فایل یک برگه به ​​نام دارد حراجیجایی که جدول داده ها قرار دارد:

البته تعداد ردیف ها (ترتیب ها) در جداول متفاوت است، اما مجموعه ستون ها در همه جا استاندارد است.

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

ما سلاح ها را انتخاب می کنیم

برای راه حل، به آخرین نسخه اکسل 2016 (عملکردهای لازم قبلاً به طور پیش فرض در آن تعبیه شده است) یا نسخه های قبلی اکسل 2010-2013 با افزونه رایگان نصب شده نیاز داریم. پرس و جو برق از مایکروسافت (آن را از اینجا دانلود کنید). Power Query ابزاری فوق العاده انعطاف پذیر و فوق العاده قدرتمند برای بارگیری داده ها در اکسل از دنیای خارج و سپس حذف و پردازش آن است. Power Query تقریباً از تمام منابع داده موجود پشتیبانی می کند - از فایل های متنی گرفته تا SQL و حتی Facebook 🙂

اگر اکسل 2013 یا 2016 ندارید، نمی توانید ادامه مطلب را بخوانید (شوخی می کنم). در نسخه های قدیمی اکسل، چنین کاری را می توان تنها با برنامه نویسی یک ماکرو در ویژوال بیسیک (که برای مبتدیان بسیار دشوار است) یا با کپی دستی یکنواخت (که زمان زیادی طول می کشد و خطا ایجاد می کند) انجام داد.

مرحله 1. یک فایل را به عنوان نمونه وارد کنید

ابتدا، بیایید داده‌ها را از یک کتاب کار به عنوان مثال وارد کنیم، تا اکسل "ایده را انتخاب کند". برای انجام این کار، یک Workbook خالی جدید ایجاد کنید و…

• اگر اکسل 2016 دارید، تب را باز کنید داده ها و پس از آن ایجاد Query – From File – From Book (داده ها - پرسش جدید - از فایل - از اکسل)
• اگر اکسل 2010-2013 با افزونه Power Query نصب شده دارید، برگه را باز کنید پرس و جو برق و روی آن انتخاب کنید از فایل – از کتاب (از فایل - از اکسل)

سپس در پنجره ای که باز می شود، به پوشه گزارش های ما بروید و هر کدام از فایل های شهر را انتخاب کنید (مهم نیست کدام یک هستند، زیرا همه آنها معمولی هستند). پس از چند ثانیه، پنجره Navigator ظاهر می شود، جایی که باید برگه مورد نیاز خود (Sales) را در سمت چپ انتخاب کنید و محتوای آن در سمت راست نمایش داده می شود:

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

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

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

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

یک ستون جدید باید با نام متن ماه برای هر ردیف تشکیل شود. با دوبار کلیک کردن روی عنوان ستون، می توانید نام آن را از آن تغییر دهید تاریخ کپی به راحتی بیشتر ماه، به عنوان مثال.

اگر در برخی از ستون ها برنامه به درستی نوع داده را تشخیص نداده است، می توانید با کلیک کردن بر روی نماد قالب در سمت چپ هر ستون به آن کمک کنید:

با استفاده از یک فیلتر ساده می توانید خطوط دارای خطا یا خطوط خالی و همچنین مدیران یا مشتریان غیر ضروری را حذف کنید:

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

سبک و ظریف، اینطور نیست؟

مرحله 2. بیایید درخواست خود را به یک تابع تبدیل کنیم

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

در ویرایشگر Query، به تب View بروید و روی دکمه کلیک کنید ویرایشگر پیشرفته (مشاهده - ویرایشگر پیشرفته). پنجره ای باید باز شود که در آن تمام اقدامات قبلی ما به صورت کد در زبان M نوشته می شود. لطفاً توجه داشته باشید که مسیر فایلی که برای مثال وارد کردیم در کد هاردکد شده است:

حالا بیایید چند تنظیم را انجام دهیم:

معنی آنها ساده است: خط اول (مسیر فایل)=> رویه ما را با آرگومان به یک تابع تبدیل می کند مسیر فایل، و در زیر مسیر ثابت را به مقدار این متغیر تغییر می دهیم. 

همه. را کلیک کنید پایان و باید این را ببیند:

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

مرحله 3. جمع آوری تمام فایل ها

همه سخت ترین قسمت ها پشت سر است، بخش دلپذیر و آسان باقی می ماند. به برگه بروید داده – ایجاد پرس و جو – از فایل – از پوشه (داده ها - پرسش جدید - از فایل - از پوشه) یا، اگر اکسل 2010-2013 دارید، مشابه تب پرس و جو برق. در پنجره ای که ظاهر می شود، پوشه ای را که تمام فایل های شهر منبع ما در آن قرار دارند را مشخص کرده و کلیک کنید OK. در مرحله بعدی باید پنجره ای باز شود که در آن تمام فایل های اکسل موجود در این پوشه (و زیرپوشه های آن) و جزئیات هر یک از آنها لیست می شود:

کلیک کنید تغییر دادن (ویرایش) و دوباره وارد پنجره ویرایشگر query آشنا می شویم.

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

پس از کلیک روی OK ستون ایجاد شده باید به جدول ما در سمت راست اضافه شود.

حالا بیایید تمام ستون های غیر ضروری را حذف کنیم (مانند اکسل، با استفاده از دکمه سمت راست ماوس - برداشتن، فقط ستون اضافه شده و ستون با نام فایل باقی می ماند، زیرا این نام (به طور دقیق تر، شهر) در کل داده های هر ردیف مفید خواهد بود.

و اکنون "wow moment" - روی نماد با فلش های خاص خود در گوشه سمت راست بالای ستون اضافه شده با عملکرد ما کلیک کنید:

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

برای زیبایی کامل، می‌توانید پسوندهای xlsx. را از ستون اول با نام فایل‌ها با جایگزینی استاندارد با «nothing» حذف کنید (روی سرصفحه ستون راست کلیک کنید. جایگزین) و نام این ستون را به شهر:. و همچنین فرمت داده ها را در ستون با تاریخ تصحیح کنید.

همه! را کلیک کنید صفحه اصلی - بستن و بارگذاری (صفحه اصلی - بستن و بارگیری). تمام داده های جمع آوری شده توسط پرس و جو برای همه شهرها در برگه اکسل فعلی در قالب "جدول هوشمند" آپلود می شود:

اتصال ایجاد شده و عملکرد اسمبلی ما به هیچ وجه نیازی به ذخیره جداگانه ندارند - آنها همراه با فایل فعلی به روش معمول ذخیره می شوند.

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

PS

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

1. را انتخاب کنید ایجاد درخواست – از فایل – از پوشه – انتخاب پوشه – OK
2. پس از ظاهر شدن لیست فایل ها، فشار دهید تغییر دادن
3. در پنجره Query Editor، ستون Binary را با یک پیکان دوتایی گسترش دهید و نام برگه را انتخاب کنید که از هر فایل گرفته شود.

و بس! ترانه!

• طراحی مجدد صفحه متقاطع به یک صفحه مسطح مناسب برای ساخت میزهای محوری
• ساخت نمودار حباب متحرک در Power View
• ماکرو برای جمع آوری ورق از فایل های اکسل مختلف در یک