لیست فایل های یک پوشه در اکسل

ایجاد یک لیست داینامیک از فایل های موجود در یک فولدر

در آموزش های قبلی نحوه ایجاد لینک بین فایل های خارج از یک فایل اکسل رو توضیح دادیم. اینکه هم میشه از ابزار Hyperlink استفاده کرد و هم از تابع Hyperlink. اما یکی از مسائلی که ممکنه بهش برخورد بکنیم، فراخوانی نام فایل های موجود در یک فولدر هست. روش های مختلفی وجود داره برای فراخوانی لیست فایل های یک پوشه. سابقا با استفاده از VBA و یا کدهای ویندوز(!) این کار انجام میشد. اما در این مقاله با استفاده از پاور کوئری به این موضوع می پردازیم. یکی از ویژگی های استفاده از پاور کوئری برای انجام این کار، قابلیت بروز رسانی هست. یعنی کافیه به محض حذف، اضافه و یا تغییر نام فایل، گزینه Refresh رو بزنیم و لیست جدید با تغییرات جدید، جایگزین لیست قبلی بشه.

همونطور که در مقالات قبلی گفتیم، ابزار پاور کوئری یک یاز ابزارهای بسیار بسیار قدرتمند اکسل هست و بصورت کلی کار اتصال به دیتابیس های مختلف، فراخوانی (Extract)، بارگذاری داده(Load)  و تمیز کردن  (Transform)داده رو انجام میده. این ابزار در آفیس 2016 به بعد با نام Get and transform شناخته میشه که در تب data قرار گرفته.

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

استفاده از پاورکوئری برای ایجاد لیست فایل های یک پوشه

برای انتخاب فولدر مورد نظر، یک فایل اکسل باز میکنیم و از مسیر زیر گزینه From Folder رو انتخاب میکنیم. (شکل 1)

Data/ Get Data/ From File/ From Folder

شکل 1- مسیر انتخاب فولدر جهت ایجاد لیست فایل های یک پوشه

از پنجره نمایش داده شده، به مسیر ذخیره فولدر مورد نظر رفته و آنرا انتخاب میکنیم. (شکل 2)

شکل 2- پاورکوئری- انتخاب فولدر مورد نظر

با زدن OK جدولی که شامل اطلاعات مختلف از فایل های موجود در فولدر انتخاب شده هست، نمایش داده میشه. همینجا هم میتونیم گزینه Load رو بزنیم و اطلاعات نمایش داده شده رو در یک شیت اکسل وارد کنیم. (شکل 3)

شکل 3- پیش نمایش داده های مربوط به فایل های موجود فولدر جهت ایجاد لیست فایل های یک پوشه

اما اگر بخوایم داده های موجود رو ویرایش کنیم و ستون های مورد نظر رو کم و زیاد کنیم، روی گزینه Edit کلیک میکنیم. با این کار وارد محیط Power Query Editor میشیم. در پنجره نمایش داده شده، میتونیم اطلاعاتی که نیاز داریم رو نگه داریم و بقیه رو حذف کنیم. برای این کار کافیه روی ستون هایی که نمیخوایم کلیک کنیم و حذف کنیم.

شکل 4- پاورکوئری-حذف ستون های اضافی

در اینجا همه ستون ها رو حذف کردیم و فقط چهار ستون (ستون نام فایل، مسیر ذخیره، پسوند و ستون مشخصات) مطابق شکل 5 نگه داشتیم.

شکل 5- پاو رکوئری- نگه داشتن اطلاعات مورد نظر از فایل های موجود در فولدر منتخب

به ستون Attributes دقت کنید این ستون، جزئیاتی راجع به هر فایل ارائه میکنیم که در یک جدول دیگه ارائه میشه (مقاله مربوط به مرج کردن جداول رو مطالعه کنید) ، با زدن علامت فلش دو طرفه، میتونیم تعیین کنیم که چه اطلاعاتی رو به ما نشون بده. با انتخاب موارد مورد نظر، اون اطلاعات هم به جدول اضافه میشه.

شکل 6- پاورکوئری- انتخاب اطلاعات مورد نظر راجع به هر فایل موجود در فولدر

با زدن OK گزینه Size و Directory به جدول فعلی اضافه میشه.

نمایش لیست فایل های یک پوشه در اکسل به صورت جدول

حالا که جدول مورد نظر رو تهیه کردیم، گام بعدی اضافه کردن این جدول به یک شیت در اکسل هست. برای این کار از تب Home روی زبانه Close & Load کلیک میکنیم و گزینه Close & Load to رو انتخاب میکنیم. از پنجره نمایش داده شده، گزینه Table رو انتخاب میکنیم و OK میزنیم.

شکل 7- پاور کوئری- بارگذاری داده های مورد نظر در یک شیت اکسل

با این کار داده های تعیین شده در قالب یک جدول و در یک شیت اکسل نمایش داده میشه که میتونیم برای کارهای بعدی (گزارشگیری و فرمول نویسی) از آن استفاده کنیم.

حالا اگر به فولدر مورد نظر بریم و تغییراتی از قبیل (حذف و اضافه فایل، تغییر نام و …) انجام بدیم. کافیه که رفرش انجام بدیم و تغییرات جدید رو در داده ها ببینیم. برای این موضوع به فولدر مورد نظر رفته و تغییراتی رو ایجاد میکنیم. در اینجا ما چهار فایل اکسل، ورد، اکسس و پاورپوینت به فولدر مورد نظر اضافه کردیم. (شکل 8)

شکل 8-پاورکوئری-تغییر داده های مرجع

حالا کافیه از تب Data و قسمت Queries & connection روی گزینه Refresh کلیک کنیم. به محض انجام این کار، اطلاعات مربوط به چهار فایل اضافه شده به فولدر مورد نظر، در جدول نهایی دیده میشوند. (شکل 9)

شکل 9- پاورکوئری- بروزرسانی دیتابیس با توجه به تغییرات داده های مرجع

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

شکل 10-پاورکوئری-ذخیره فعالیت های انجام شده در پنجره Power query editor

نکته آخر اینکه، برای دسترسی به کوئری های نوشته شده در فایل موجود، کافیه از تب Data روی گزینه Queries & Connection کلیک کنیم. سمت راست همه کوئری های نوشته شده ظاهر میشه که با نگه داشتن موس روی یکی از آنها میتونیم با زدن Edit دوباره وارد محیط پاور کوئری ادیتور بشیم و تغییرلتی رو روی کوئری مورد نظر اعمال کنیم.

شکل 11- پاور کوئری- دسترسی به  کوئری های نوشته شده و ویرایش آنها

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