اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
[email protected]

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
  • درباره ما
    • Cart

      0

Power Query

  • خانه
  • بلاگ
  • Power Query
  • لیست فایل های یک پوشه در اکسل

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

  • ارسال شده توسط سامان چراغی
  • دسته بندی Power Query
  • تاریخ ۲۵ تیر ۱۳۹۸
  • نظرات ۱ نظر
لیست فایل های یک پوشه
5 / 5 ( 1 امتیاز )

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

در آموزش های قبلی نحوه ایجاد لینک بین فایل های خارج از یک فایل اکسل رو توضیح دادیم. اینکه هم میشه از ابزار 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 نگه داشتیم.

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

 

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

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

حتما بخوانید:  ابزار Unpivot ابزاری کاربردی در Power Query

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

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

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

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

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

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

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

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

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

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

حتما بخوانید:  فعال کردن Power Query در ورژن های مختلف اکسل

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

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

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

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

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

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

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

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

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

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

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

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

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

مطلب قبلی

ترکیب جداول در اکسل با استفاده از Power Query
۲۵ تیر ۱۳۹۸

مطلب بعدی

رنگی کردن سلول های پروتکت شده
۲۵ تیر ۱۳۹۸

ممکن است همچنین دوست داشته باشید

Power-Query-Applications-Cover
چند مثال کاربردی از ابزار Power Query
۱۵ اسفند, ۱۳۹۹
Install-Power-Query-Cover
فعال کردن Power Query در ورژن های مختلف اکسل
۷ اسفند, ۱۳۹۹
Unpivot Data Cover
ابزار Unpivot ابزاری کاربردی در Power Query
۷ مرداد, ۱۳۹۹

    1 نظر

  1. profile avatar
    sevda
    ۲۶ آبان ۱۳۹۹
    پاسخ

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

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

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

آخرین مطالب

مقادیر متمایز در اکسل
تفاوت داده Distinct و Unique چیست؟ چطور میشه مشخص کرد
۱۰فروردین۱۴۰۰
استفاده از چندین تابع IF اکسل در هم
توابع IF تو در تو و نکات مهمی که باید بدانید
۲۷اسفند۱۳۹۹
تابع Trim
تابع Trim در اکسل
۲۱اسفند۱۳۹۹
کاربرد Power Query
چند مثال کاربردی از ابزار Power Query
۱۵اسفند۱۳۹۹

وجود یک منبع جامع و به روز که بدونی همیشه پشتیبانته برای یادگیری ضروریه. اکسل پدیا رو برای همین به وجود آوردیم.

پاسخ سوالات شما

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

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

[email protected]

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما