ساخت لیست خودکار از فایل ها
تهیه آرشیو دیجیتال از فایل های موجود در یک فولدر
گاهی اوقات داشتن یک لیست از فایل های موجود در یک فولدر که قابلیت لینک شدن به هر فایل داشته باشه، میتونه خیلی به مدیریت کارها کمک کنه. مثلا لیستی از فولدر نامه ها، فولدر فیلم و سریال، موزیک، عکس های یک پروژه و … اکسل به عنوان یک ابزار قدرتمند میتونه این امکان رو در اختیار ما قرار بده. برای این کار به چه چیزهایی نیاز داریم؟ به پاور کوئری و تابع Hyperlink
گام اول: ایجاد لیستی از نام فایل های موجود در یک فولدر
اول باید بتونیم لیست فایل های موجود در یک فولدر رو در یک اکسل داشته باشیم.برای این کار از پاورکوئری استفاده میکنیم و طبق مراحل زیر پیش میریم. اگر با پاورکوئری و نحوه دسترسی به این ابزار آشنا نیستید حتما مقاله پاورکوئری رو مطالعه کنید:
- از مسیر زیر روی گزینه From Folder کلیک میکنیم (شکل ۱)
Data/ Get & Transform/ Get Data/ From File/ From Folder
شکل ۱- مسیر دسترسی به فولدر مورد نظر
- از پنجره باز شده، فولدر مورد نظر که میخواهیم اسم فایل های آن را داشته باشیم، انتخاب کرده و ok میزنیم.
پنجره ای باز میشه که لیست اطلاعاتی که از فایل های موجود در اون فولدر جود داره داره نمایش داده میشه. ما به کدوم اطلاعات نیاز داریم؟ به اسم فایل و مسیر ذخیره. پس بهتره بقیه اطلاعات رو حذف کنیم. پس مرحله ۳ رو انجام میدیم:
- روی گزینه Transform کلیک میکنیم و وارد محیط Power query editor میشیم. حالا باید ستون هایی که نیاز نداریم رو حذف کنیم. برای این کار کافیه روی هر ستونی که نمیخوایم کلیک راست کرده و گزینهRemove رو بزنیم. ما در این مثال فقط ستون Name و Folder Path رو نگه داشتیم و بقیه رو حذف کردیم. (مطابق شکل ۲)
شکل ۲- حذف ستونهای اضافی در محیط پاورکوئری ادیتور
وقتی ستون های اضافی رو حذف کردیم باید داده ها رو وارد اکسل کنیم. برای این کار میریم سراغ مرحله ۴.
- برای وارد کردن داده ها به اکسل، از تب home روی گزینه Close & Load To کلیک کرده و از پنجره نمایش داده شده Table رو انتخاب میکنیم. مطابق شکل ۳.
شکل ۳- ورود داده ها به شیت اکسل
حالا شیتی که شامل یک جدول از اطلاعات مربوط به فایل های موجود در یک فولدر هست ایجاد شد.
گام دوم: برقراری لینک بین اسم ها و فایل ها
برای این کار باید از تابع Hyperlink استفاده کنیم. اگر با این تابع آشنایی ندارید، حتما مقاله مربوط به تابع Hyperlink رو مطالعه کنید.
در ستون سوم و در کنار داده (سول C2) ، فرمول زیر رو تایپ میکنیم:
=HYPERLINK ( [@[Folder Path]]&[@Name] , [@Name] )
شکل ۴- نوشتن فرمول Hyperlink برای داده ها
این فرمول رو نوشته و تا انتهای داده ها درگ میکنیم. با این کار با کلیک روی هر کدام، فایل مورد نظر در فولدر باز میشه. دقت داشته باشید که الگوی تابع Hyperlink به اینصورت هست که اول مسیر ذخیره فایل، بعد اسم فایل و بعد پسوند فایل باید به هم چسبیده باشند. برای همین از & استفاده کردیم که بتونیم این الگو رو ایجاد کنیم. هر قسمتی از این الگو رعایت نشه، لینک برقرار نخواهد شد.
حالا اگر فایلی در فولدر مورد نظر، اضافه یا کم بشه، کافیه که بیایم داخل فایل اکسل و روی تیبل داده ها، کلیک راست کنیم و Refresh رو بزنیم. (شکل ۵)
شکل ۵- رفرش کردن کوئری و دریافت لیست فایل ها
این فایل روی سیستم شما کار میکنه و طبیعتا برای ارسال به بقیه سیستم ها آماده نیست. اگر بخوایم فایلی باشه که لینک ها در همه سیستم ها کار کنه، باید از روش های دیگه ای استفاده کرد و طوری فرمول نویسی انجام داد که مسیر ذخیره فایل ها خودبخود بروزرسانی بشه که در آموزش های بعدی به این موضوع خواهیم پرداخت.
در این مقاله سعی کردیم با ترکیب ابزار و فرمول در اکسل، یکی از مسائل مهم و کاربردی رو به شیوه ای بهینه و جذاب حل کنیم. طبیعتا این کار به مدیریت فایل ها و نظم دهی بیشتر کمک خیلی زیادی میکنه.
این مراحل رو در قالب ویدئو زیر هم میتونید ببینید.
