سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

لیست فایل های یک پوشه
۵/۵ - (۱ امتیاز)

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

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

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

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

استفاده از پاورکوئری

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

Data/ Get Data/ From File/ From Folder

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

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

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

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

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

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

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

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

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

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

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

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

نکته:
اینکه چه ستونی رو نگه داریم و چه ستونی رو حذف کنیم بستگی به موضوعی داره که میخوایم از این داده ها استفاده کنیم. فرض کنید ما میخواهیم از این اطلاعات در تابع Hyperlink استفاده کنیم. همونطور که میدونیم، مسیر ذخیره، اسم فایل و پسوند از ملزومات تابع Hyperlink هستند.

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

126

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • هادی ۱۷ خرداد ۱۴۰۱ / ۹:۳۲ ق٫ظ

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

  • sevda ۲۶ آبان ۱۳۹۹ / ۱۲:۵۹ ب٫ظ

    ممنونم از اطلاعات واضح ودقیق
    خدا به علمتون برکت بده و عاقبت بخیر باشین.

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان