ادغام خودکار فایل‌های ماهانه با Power Query
یکی از وقت‌گیرترین کارهای ماهانه کپی‌کردن چند فایل در یک فایل است: گزارش فروش هر شعبه، صورت‌حساب هر ماه، خروجی هر نرم‌افزار. Power Query که در اکسل ۲۰۱۶ به بعد داخل زبانهٔ Data است، این کار را یک بار تعریف می‌کند و بعد فقط با یک کلیک تکرار می‌کند — بی‌هیچ کدی.
شرط کار: فایل‌های هم‌شکل
همهٔ فایل‌ها باید ساختار یکسان داشته باشند: همان ستون‌ها، با همان عنوان‌ها، در همان برگه. اگر یک شعبه ستون اضافه دارد یا عنوان را جور دیگری نوشته، اول همان را یکدست کن.
گام‌ها
- همهٔ فایل‌ها را در یک پوشه بگذار (و هیچ فایل دیگری در آن نباشد).
- در فایل گزارش از Data ← Get Data ← From File ← From Folder پوشه را انتخاب کن.
- در پنجرهٔ بعد Combine & Transform Data را بزن و برگه یا جدول نمونه را انتخاب کن.
- در ویرایشگر Power Query یک ستون به اسم
Source.Nameمی‌بینی: نام فایل هر ردیف. نگهش دار؛ می‌شود از آن نام شعبه یا ماه را بیرون کشید. - نوع داده‌ها را درست کن (مبلغ: عدد، تاریخ: متن یا تاریخ)، ردیف‌های خالی را حذف کن و Close & Load را بزن.
نتیجه یک جدول در اکسل است که داده‌های همهٔ فایل‌ها را زیر هم دارد.
ماه بعد
فایل تازه را در همان پوشه بینداز و روی جدول Refresh بزن. همهٔ گام‌هایی که تعریف کرده‌ای دوباره روی کل پوشه اجرا می‌شود. هر Pivot یا نموداری هم که روی این جدول ساخته‌ای با یک Refresh All به‌روز می‌شود.
Power Query یا ماکرو؟
| Power Query | ماکروی VBA | |
|---|---|---|
| یادگیری | بی کد، با کلیک | نیاز به کدنویسی |
| جمع و پاک‌سازی داده | بسیار قوی | ممکن، با کد بیشتر |
| کارهای دیگر (PDF، ایمیل، قالب‌بندی) | نه | بله |
| امنیت فایل | بی ماکرو؛ .xlsx کافی است | فایل .xlsm و تأیید ماکرو |
برای جمع‌کردن و تمیزکردن داده، Power Query تقریباً همیشه انتخاب بهتری است. وقتی بعد از جمع‌کردن باید کار دیگری هم انجام شود (چاپ، PDF، فرستادن)، ترکیب این دو بهترین نتیجه را می‌دهد.
جمع‌کردن گزارش‌ها هنوز دستی است؟
برآورد رایگان است؛ فرم را پر کن تا تماس بگیرم.