ادغام خودکار فایل‌های اکسل با Power Query از یک پوشه

ادغام خودکار فایل‌های اکسل با Power Query از یک پوشه یک راهنمای عملی برای حل مسئله‌ای تکرارشونده در محیط اکسل، اکسس و اتوماسیون اداری است. هر ماه فایل‌های فروش چند شعبه با ساختار یکسان به پوشه Reports می‌رسند و هدف تهیه یک جدول تجمیعی بدون کپی و چسباندن دستی است. در این راهنما از تعریف مسئله و آماده‌سازی داده شروع می‌کنیم، اجرای نمونه را مرحله‌به‌مرحله توضیح می‌دهیم و در پایان نتیجه، خطاهای محتمل و روش انتقال به یک فرایند واقعی را بررسی می‌کنیم. هدف فقط آشنایی با نام دستور نیست؛ باید بتوانیم خروجی را اندازه بگیریم، علت اختلاف را پیدا کنیم و تغییرات بعدی را بدون از دست‌دادن کنترل انجام دهیم.

مسئله و هدف آموزشی

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

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

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

پیش‌نیازها و فرض‌های فنی

Power Query در نسخه رومیزی اکسل یک لایه تبدیل داده است؛ مراحل آن در فهرست Applied Steps ثبت می‌شوند و با Refresh دوباره روی منبع اجرا خواهند شد. بنابراین انجام یک‌باره عملیات با نوشتن یک چرخه تکرارپذیر تفاوت دارد. برای منابع حجیم، ابتدا داده غیرضروری را حذف کنید تا هم نمایش پیش‌نمایش سبک‌تر شود و هم بررسی نتیجه ساده‌تر باشد. منبع را بدون نیاز واقعی با گزینه‌های تغییر داده جایگزین نکنید.

نمونه‌های آموزشی عمداً کوچک‌اند؛ اما نباید آن‌ها را بدون کنترل به اطلاعات واقعی منتقل کرد. در داده‌های سازمانی ممکن است پرونده قفل شده باشد، ستون با نام جدید ظاهر شود، عدد به شکل متن وارد شود یا شناسه‌ای که باید یکتا باشد چندبار تکرار شود. برای هر مورد باید از قبل رفتار مشخصی تعریف کنید: رد رکورد، توقف کل عملیات یا ثبت هشدار برای اصلاح دستی. هر سه انتخاب، در شرایط مختلف می‌توانند درست باشند.

نکته دیگر، نگهداری بلندمدت است. کدی که امروز برای یک فایل جواب می‌دهد ممکن است با تغییر نام شیت، افزودن یک ستون یا ارتقای نسخه Office از کار بیفتد. بهتر است فرض‌های مهم در ابتدای آموزش و در کنار کد نوشته شوند. اگر فرض‌ها برقرار نبودند، توقف همراه با پیام روشن بهتر از ادامه دادن و تولید یک گزارش ناقص است. مستند کوتاهِ ورودی، خروجی و محدودیت‌ها معمولاً از پیچیده‌ترین ترفندهای فنی سودمندتر است.

مراحل پیاده‌سازی

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

نمونهٔ فایل‌ها را هم‌ساختار کنید

در این گام: ستون‌های نام‌دار فروش، تاریخ و کد شعبه را در همه فایل‌های نمونه یکسان کنید. نام Sheet به‌تنهایی کافی نیست؛ بهتر است داده‌ها در یک Table مشخص قرار بگیرند. این کار را ابتدا روی داده آزمایشی انجام دهید و قبل از ورود به گام بعد نتیجه را با ورودی کنترل کنید.

برای آزمون «نمونهٔ فایل‌ها را هم‌ساختار کنید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

از Data > Get Data > From Folder وارد شوید

در این گام: در اکسل به From Folder بروید و مسیر پوشه را وارد کنید. تفاوت Combine و Transform Data را بشناسید و ابتدا پیش‌نمایش نام فایل‌ها را ببینید. این کار را ابتدا روی داده آزمایشی انجام دهید و قبل از ورود به گام بعد نتیجه را با ورودی کنترل کنید.

برای آزمون «از Data > Get Data > From Folder وارد شوید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

فایل‌های موقت و مخفی را فیلتر کنید

در این گام: فایل‌های با پسوند xlsx را انتخاب کنید و نام‌های موقت با پیشوند ~$ را کنار بگذارید. بهتر است نسخه‌های پشتیبان در پوشه منبع نباشند. این کار را ابتدا روی داده آزمایشی انجام دهید و قبل از ورود به گام بعد نتیجه را با ورودی کنترل کنید.

برای آزمون «فایل‌های موقت و مخفی را فیلتر کنید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

نام Table مشخص را از فایل‌ها انتخاب کنید

در این گام: در هر Workbook به‌جای انتخاب اولین Sheet اتفاقی، جدول نام‌دار Sales را پیدا کنید. نبود جدول را به‌صورت یک وضعیت مشخص ثبت کنید. این کار را ابتدا روی داده آزمایشی انجام دهید و قبل از ورود به گام بعد نتیجه را با ورودی کنترل کنید.

برای آزمون «نام Table مشخص را از فایل‌ها انتخاب کنید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

ستون منبع و نوع داده را بررسی کنید

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

برای آزمون «ستون منبع و نوع داده را بررسی کنید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

Refresh را با فایل تازه آزمایش کنید

در این گام: یک فایل سالم جدید اضافه کنید، Refresh All را بزنید و افزایش شمار ردیف‌ها را کنترل کنید. با یک فایل خراب نیز رفتار شکست را بیازمایید. این کار را ابتدا روی داده آزمایشی انجام دهید و قبل از ورود به گام بعد نتیجه را با ورودی کنترل کنید.

برای آزمون «Refresh را با فایل تازه آزمایش کنید» یک نمونه درست و یک نمونه مرزی در نظر بگیرید. نام ستون‌ها، نوع داده و نتیجه را در کنار هم ثبت کنید. اگر این مرحله خطا داد، ابتدا فرض آن را اصلاح کنید نه اینکه خطا را نادیده بگیرید.

مثال عملی و بررسی کد

برای روشن‌شدن ساختار ادغام فایل‌های اکسل با Power Query، کد زیر یک الگوی قابل تغییر ارائه می‌کند. قبل از اجرا، نام جدول‌ها و فایل‌ها را مطابق محیط آزمایشی خود تنظیم کنید. در مثال‌های VBA، کد باید داخل ماژول استاندارد و فایل سازگار با ماکرو قرار گیرد؛ در Power Query کد M در Advanced Editor و در فرمول‌های اکسل عبارت در سلول مناسب نوشته می‌شود.

let
 Source = Folder.Files("C:\Reports"),
 Visible = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
 Added = Table.AddColumn(Visible, "SalesRows", each
   let b = Excel.Workbook([Content], true), t = Table.SelectRows(b, each [Kind] = "Table" and [Name] = "Sales")
   in if Table.IsEmpty(t) then null else t{0}[Data]),
 Valid = Table.SelectRows(Added, each [SalesRows] <> null),
 Result = Table.Combine(Valid[SalesRows])
in Result

پس از اجرای مثال، سطرهای ورودی را با خروجی مقایسه کنید. هر ماه فایل‌های فروش چند شعبه با ساختار یکسان به پوشه Reports می‌رسند و هدف تهیه یک جدول تجمیعی بدون کپی و چسباندن دستی است. در مثال‌هایی که چند دستور یا مرحله دارند، هر بخش را جداگانه آزمایش کنید؛ اگر مرحله نخست اشتباه باشد، اجرای موفق مرحله دوم به معنی درست‌بودن نتیجه نیست. برای فایل‌هایی با داده واقعی، خطاهای احتمالی را ثبت کنید و از چاپ یا ارسال اطلاعات حساس در پنجره Immediate خودداری کنید.

کنترل صحت و آزمون شرایط مرزی

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

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

در M، ترتیب مراحل و نوع هر ستون اهمیت دارد. متنی که ظاهر عددی دارد لزوماً Number نیست و تاریخ‌های محلی نیز ممکن است با فرهنگ اشتباه تفسیر شوند. بهتر است تبدیل نوع داده پس از تمیزکردن متن و در نقطه‌ای انجام شود که بتوانید خطاهای تبدیل را ببینید. شمار ردیف‌های دارای Error را کنترل کنید؛ حذف همه خطاها بدون تحلیل می‌تواند مقدار مالی معتبر را از گزارش کنار بگذارد.

خطاهای رایج

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

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

بهترین روش‌ها و نگهداری

Power Query فرمولی نیست که با تغییر هر سلول فوراً همه خروجی‌ها را محاسبه کند. باید Refresh اجرا شود و در برخی محیط‌ها تنظیمات بارگذاری یا اجازه دسترسی به فایل‌ها هم اهمیت دارد. اگر کاربر فایل نهایی را جابه‌جا کند، مسیر پوشه در کوئری ممکن است بشکند. قبل از تحویل گزارش، مسیر داده و نسخه پشتیبان را ثبت کنید و روی یک فایل تازه، فرایند بازیابی را آزمایش کنید.

نکته دیگر، نگهداری بلندمدت است. کدی که امروز برای یک فایل جواب می‌دهد ممکن است با تغییر نام شیت، افزودن یک ستون یا ارتقای نسخه Office از کار بیفتد. بهتر است فرض‌های مهم در ابتدای آموزش و در کنار کد نوشته شوند. اگر فرض‌ها برقرار نبودند، توقف همراه با پیام روشن بهتر از ادامه دادن و تولید یک گزارش ناقص است. مستند کوتاهِ ورودی، خروجی و محدودیت‌ها معمولاً از پیچیده‌ترین ترفندهای فنی سودمندتر است.

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

تمرین پیشنهادی

یک نسخه کوچک از مسئله «ادغام خودکار فایل‌های اکسل با Power Query از یک پوشه» برای تمرین بسازید. سه ورودی تازه اضافه کنید: یک رکورد معتبر، یک رکورد تکراری و یک رکورد دارای مقدار گمشده. پیش از اجرا، خروجی مورد انتظار هر سه را در جدول جداگانه بنویسید. سپس فرایند را اجرا کنید و تفاوت‌ها را کنار هم ثبت کنید. پس از موفقیت، نام یکی از ستون‌ها یا مسیر یکی از فایل‌ها را عمداً تغییر دهید تا رفتار خطا را هم ببینید. در پایان، مراحل لازم برای بازگرداندن وضعیت صحیح را مستند کنید.

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

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

موضوعات مرتبط

برای یادگیری پیوسته، درس‌های مرتبط زیر را در کنار این مقاله بررسی کنید. پیوندهای برنامه‌ریزی‌شده پس از انتشار مقصد قابل فعال‌سازی‌اند.

• چگونه چند شیت اکسل را با VBA در یک شیت ادغام کنیم
• پاک‌سازی و تغییر شکل داده‌ها در Power Query با Unpivot و Refresh
• آموزش XLOOKUP در اکسل با جست‌وجوی دقیق و مدیریت خطا
• آموزش FILTER و UNIQUE و SORT در اکسل با آرایه‌های پویا

جمع‌بندی

ادغام فایل‌های اکسل با Power Query زمانی واقعاً مفید است که نتیجه قابل کنترل و تکرار باشد. هر ماه فایل‌های فروش چند شعبه با ساختار یکسان به پوشه Reports می‌رسند و هدف تهیه یک جدول تجمیعی بدون کپی و چسباندن دستی است. با اجرای مرحله‌ای، بررسی داده‌های مرزی و ثبت فرض‌ها می‌توان یک مثال ساده را به ابزاری قابل اتکا تبدیل کرد. پیش از استفاده از فایل روی اطلاعات اصلی، آزمون‌ها را در نسخه پشتیبان تکرار و محدودیت‌های نسخه نرم‌افزار و ساختار داده را کنترل کنید.