در مقدمه باید بگویم که من پاورکوئری اکسل را بسیار قوی، مفید، کارآمد و نسبتا ساده یافتم (بر خلاف PowerPivot اکسل) و با کارشناسان سازمان‌های مختلفی توانستیم از آن بهره فروان در حل مسائل پیچیده، ادغام داده‌های حجیم و گزارش سازی ببریم. جالب است بدانید که من حدود 10 سال پیش نیاز این ابزار را احساس کرده بودم و همان موقع یک ایمیلی به مایکروسافت ارسال کردم و عنوان کردم که ای کاش ما در در اکسل می توانستیم دستورات SQL را اجرا کنیم (البته در همان موقع ابزارهایی برای اکسل به همین منظور وجود داشت که باید خریداری می شد و بر روی اکسل نصب می شد اما خود اکسل اینکار را انجام نمی داد).
مقاله‌ای «پاورکوئری چیست»، شرح مختصری از جایگاه PowerQuery در دنیای نرم افزار شرح داده شده است و اکنون در این فیلم‌های آموزشی کوشیده‌ام که مباحث مهم و کلیدی پاورکوئری در اکسل (Excel Power Query) را به شما آموزش دهم در ضمن آنکه خستی در ارائه مطالب بخرج ندهم تا این مجموعه به اولین و کامل ترین آموزش پاورکوئری تبدیل شود.
در این فیلم های آموزشی من بیشتر از «تجربه‌های عملی و واقعی» کسب شده خودم بهره برده ام و از روی کتاب و یا ویدئوی خارجی تدوین نشده است.البته برخی از مثال‌ها از کتاب M Is for (Data) Monkey انتخاب شده است و همچنین سعی کرده ام که تا حد ممکن مطالب را از نظر علمی با سایت مایکروسافت تطبیق بدهم.
در نظر داشته باشید که پاورکوئری یک زبان برنامه نویسی است (گاهی به آن Query Language گفته می شود یعنی چیزی شبیه SQL و همچنین آنرا در زمره زبانهای برنامه نویس functional می دانند چیز مانند مانند Haskell ) و این به معنای آن است که بسیار وسیع است. بنابراین انتظار نداشته باشید که کلیه توابع و دستورات آنرا در این آموزش ها ببیند.
دانلود فیلم‌های آموزشی پاورکوئری در اکسل
01- شروع کار با پاورکوئری، محیط Query Editor و ساخت کوئری (مدت: 35min | حجم: 86MB)
02- ادغام چندین فایل csv با یکدیگر، مفهوم Append در پاورکوئری (مدت: 28min | حجم: 80MB)
03- مرور فرمان‌های اولیه پاورکوئری (مدت: 27min | حجم: 83MB)
04- ادغام تمامی فایلهای یک فولدر با یکدیگر (مدت: 32min | حجم: 89MB)
05- ادغام داده‌های چندین شیت اکسل با پاورکوئری - قسمت 1 (مدت: 5min | حجم: 17MB)
06- ادغام داده‌های چندین شیت اکسل با پاورکوئری - قسمت 2 (مدت: 27min | حجم: 83MB)
07- نرمال کردن داده‌ها با ستون‌های تکراری (مدت: 15min | حجم: 40MB)
08- حل مثال محاسبه درصد فروش از کل، ابزار Group By (مدت: 28min | حجم: 79MB)
09- ادغام داده‌ها با یکدیگر با ابزار Join (مدت: 54min | حجم: 170MB)
10- نرمال سازی داده‌ها با ابزار UnPivot (مدت: 37min | حجم: 106MB)
11- بررسی توابع پاورکوئری (مدت: 22min | حجم: 60MB)
12- دستورات شرطی if then else و try otherwise (مدت: 25min | حجم: 66MB)
13- گزارش شرکت پخش داروپخش - نرمال سازی (مدت: 31min | حجم: 88MB)
14- گزارش شرکت پخش داروپخش - Cleansing & Mapping مدت: 16min | حجم: 54MB)
15- ایجاد و استفاده از پارامترها (مدت: 30min | حجم: 84MB)
16- روش تبدیل یک کوئری به Function و استفاده از آن (مدت: 21min | حجم: 56MB)
17- ترفندهای مفید - کار با لیست‌ها،ادغام فرم‌ها (مدت: 21min | حجم: 53MB)
18- ترفندهای مفید - تابع جمع تجمیعی، پاکسازی متن‌ها (مدت: 25min | حجم: 59MB)
نکته‌ها و اصلاحاتی در مورد ویدئوها:
- ویدئوهای 05 و 06 با اکسل 2016 و سایر ویدئوها با اکسل 2019 تهیه شده است. اگر از آخرین نسخه Power Query استفاده کنید، هیچ تفاوتی در دستورات و ظاهر محیط آن مشاهده نخواهید کرد. تنها تفاوت نسخه 2019 با نسخه‌های قبلی در تنوع گزینه‌هایی که هنگام Load To مشاهده می‌نمایید.
- برای ذخیره ویدئوها در مرورگر فایرفاکس روی هر یک R-Click کرده و Save link as را انتخاب کنید.
- آموزش های پاورکوئری در حال تکمیل شدن هستند. آخرین مقالات و به روزرسانی های سایت از طریق کانال تلگرام اکسل فرساران به اطلاع شما خواهید رسید.
دیدگاه‌ها
به این مطلب پاسخ دهید
باسلام و عرض ادب خدمت استاد گرامی و باتشکر از مطالب خوبتون حقیقتش میخواستم بگم هیچ منبعی ندیدم که به این راحتی و سلیسی این مبحث power queryرو توضیح داده باشند .قدردان زحمات جنابعالی هستیم. انشااله تندرست و سالم باشید.
با تشكر از تمام زحمات جنابعالي از اينكه دانش خود را صميمانه در اختيار ديگران گذاشتيد.
به اميد فرداهاي بهتر
خدا قوت بده بهتون استاد
سلام .ای یار سخت کوش مریز دست تو
تابان بود هیشه دل حق پرست تو
عالي هستند. با سپاس فراوان
درود بی کران بر وارثان کوروش کبیر . سپاسگزارم بزرگوار
سلام خدمت شما استاد عزیز
بابت آموزش ساده و روان شما خواستم تشکر کنم واقعا عالیه فقط خواهشا آموزش پاور پیوت و پاور بی ای را هم برایمان بزارید درآخر بازم ممنون از شما
با سلام، تاکنون قانع نشده‌ام که Power Pivot واقعا ابزار مفید و کاربردی باشد در ضمن اینکه فرمول نویسی DAX بسیار پیچیده است. به همین دلیل ترغیب کاربران اکسل به استفاده از آن کار مفیدی و حتی اخلاقی و علمی نمی‌دانم.
توجه داشته باشید که من از همان اوایل (حدود سال 2010) با این ابزار آشنا شدم و هرچه بیشتر در مورد آن تحقیق کردم، شک من هم به کاربردی بودن این ابزار افزایش یافت.
(البته شاید در آینده چند ویدئوی مقدماتی صرفا جهت معرفی مقدماتی این ابزار آماده کنم.)
با سلام و احترام
از بابت فایلهای آموزشی ارائه شده در سایت از شما سپاسگزارم
پیشنهاد من در خصوص فایلها اینست که چون اکسل 2019 فراگیر نشده لطفا فایلهای آموزشی در اکسل 2016 تهیه شود .
با تشکر
پاورکوئری در همه نسخه‌ها کاملا یکسان است (اگر آنرا آپدیت کرده باشید) و هیچ تفاوتی ندارند (حتی 2010 با 2019) . تنها تفاوت 2019 با نسخه‌های قبلی در پنجره‌ای است که هنگام Load To مشاهده میکنید که 2 گزینه در 2019 اضافه شده است و این دو گزینه به عملکرد پاورکوئری در اکسل مرتبط هستند نه با امکانات آن.
تصوی تفاوت پنجره Load To افزونه پاورکوئری در اکسل 2010 و 2019:
شما عالی هستید و باعث افتخار
ایکاش همه یاد بگیریم و با سخاوت منتشر کنیم
ممنون از محبت شما و سایر دوستان
اما واقعا هنگامی که به پروژه های آزاد جهانی مانند لینوکس و .. نگاه میکنم، مطمئن می‌شوم که «هیچ کاری» انجام نداده‌ام!
سلام و ارادت. دم شما گرم و خدا قوت آقا فرشید عزیز که برای اهدافت و انتقال دانش به بشریت تلاش میکنی و معتقدم جایگاه شما به خاطر صداقت و تداوم حضورتون بسیار مغتنم و محترمه... من دوست دارم شما ماندگار بشی هرچند هستی اما با افزایش این گونه ویدیو های تخصصی در سایت فرساران یا حتی سایتی مانند فرادرس همه دوستان از ثمره آموزشهای شما بهره ببرند حتی اون آدمی که توی شهرستان مرزی یا روستایی دور افتاده داره با صحنه زندگی دست و پنجه نرم میکنه این حق باید برای همه یکسان باشه و شما میتونی با امثال این آموزشهای تخصصی و متفاوتت اغنا کنی دانش انسانهای لایق و شایسته رو که در جستجوی دانشند. همیشه موفق و سربلند و عزت مند باشید استاد فرشید میدانی...
ممنون از نظر دلگرم کننده شما.
ناگفته نماند که من در حال تهیه ویدئوهایی آزاد و رایگان برای آموزش برنامه نویسی به کودکان هستم- مخصوصا بچه هایی که در شهرها و جاهای دور افتاده ای هستند - که به زودی ارائه خواهد شد. از کانال تلگرام farsaran اطلاع رسانی آن انجام خواهد شد.
معمولا من دوره های فرادرس را حتی برای مرور و آشنایی اولیه هم توصیه نمی کنم و نظری منتقدانه دارم. به نظر من فقط کسانی می توانند آموزش مجازی را خوب ارائه کنند که سالیان سال به صورت حضوری تدریس جدی و حرفه ای داشته اند و متاسفانه مشاهده من حاکی از آن است که اغلب مدرسین فرادرس کم تجربه و یا دانشجو (حتی بی تجربه) در تدریس هستن.
با سلام و عرض ادب
ممنون و یک دنیا تشکر بابت ویدئوهای بینظیرتون ،منتظر تکمیل شدن قسمت های در دست تهیه هستم .
با سلام و عرض ادب خدمت استاد عزیز و گرامی ، ممنون از زحماتی که میکشید
با سلام و تشکر از زحمات شما استاد گرامی
استاد میدانی براتون مقدور هست روی هر فایلی که کار می کنید یه نمونه از همون فایل اکسل رو هم برای دانلود بزارید که روی اون فایل تمرین کنیم ؟ و آیا ممکنه این فایل دارو پخش رو برای من بفرستید ؟ فایل اکسل منظورم هست. آدرس ایمیلم رو فرستادم براتون
ممنون
بله حتما همه فایل ها را سعی می کنم تهیه کنم و برای دانلود قرار دهم.
سلام جناب میدانی واقعا از صمیم قلب ازتون تشکر میکنم که دارین رایگان این ویدیوهارو برامون میذارید . از این هم بگذریم که تدریستون عالیه و کاملا مسلط هستین .
با عرض سلام و درود خدمت استاد گرانقدر و مجموعه فرساران
من بار دوم هست که فیلم های آموزشی پاورکوئری را می بینم و تمرین می کنم
امروز متوجه شدم چند قسمت جدید اضافه شده واقعا خوشحال شدم
روش تدریس استاد عالیه و تمام نکات حرفه ای گفته میشه
این دوره سوپرایزی رایگان و بسیار ارزشمند از فرساران هست
خیلی ممنون میشم این دوره عالی ادامه داشته باشه تا وقتی کامل بشه
موفق و شادکام باشید
سلام برنامه آموزش پاور کوئری شما عالی و جالب بود از زحماتان سپاسگزارم
باسلام امیدوارم دوستان قدر دقیقه دقیقه زحمات استاد رو بدونند و برای حمایت از ایشون همه مطالب خوبشون رو باذکر منبع نشر کنند تا قدر چنین انسان بزرگی در جای دیگه برآورده بشه.دوستان کتاب برنامه نویسی در اکسل ایشون رو پیشنهاد میکنم به همه تون من تهیه کردم واقعا عالیه....
باسلام و عرض ادب آیا تو محیط power bi همانند power query excel میتوان فایلها رو پاکسازی کرد و آیا تمامی امکانات در آن تعبیه شده چون محیط پاور بی برای طراحی داشبورد قدرتمند هست فکر میکنید مشکلی در استفاده از پاور بی به جای پاور کوئری به دلیل ساختار فونت وجود نداشته باشد؟باتشکر
بله . PowerBI از سه لایه تشکیل شده است که یکی از آنها همان چیزی است که ما در اکسل PowerQuery به آن می گوییم. لایه دوم آن همان Power Pivot است و لایه سوم آن همان المان های گرافیکی /Visual ها می باشند.
با سلام خدمت استاد گرانقدر و عزیز
باسپاس و تشکر از جناب عالی بابت مطالب بسیار عالی و آموزنده ان شااله همیشه موفق و پیروز و سالم باشید
ممنون از استاد ميداني عزيز كه هميشه در انتشار علم حرف اول رو ميزنند، خداوند پشت وپناهت مهندس
چقد باحالین شما جناب میدانی . این ویدیوها واقعا عالین چون که شما هم به بحث مسلط هستین و هم خیلی عالی تدریس میکنین تا حالا هیچ اموزشی ندیدم چه اکسل باشه یا هر چیز دیگه که مث شما تدریس کنن. خیلی عالی هستین شما دمت گرم . و یه خواهش هم داشتم جناب میدانی چند تا ویدیو توی کانال تلگرامتون قرار دادین که اونا هم فوق العاده بودن کاش از این دست ویدیوهارو هم بیشتر برامون به اشتراک بذارین . واقعا ممنونم ازتون .
با سلام و عرض تبریک خدمت شما و همکاران محترمتان
تشکر خاص دارم بابت فیلم های آموزشی power query واقعا ممنون هیچ منبعی به این خوبی در مورد وجود نداره ممنون که من و دیگران رو با چنین نرم افزاری آشنا کردید برای شمار آرزوی سلامت دارم .
با سلام خدمت استاد
به دنبال حل یک مئله ساده بودم اتفاقی با مطلب اموزش شما برخورد کردم
مسئله که چند روزه حل شده اما مائیم و پاورکوئریو
مرسی مرسی مرسی
امیدوارم به اموزشاتون ادامه بدین .مخصوصا پاور پیوت و توابع پاور کوئری
بازم مرسی.
سال خوبی داشه باشین
ضمن سلام و سپاس از زحمات شما، ممنون میشم در خصوص نحوه ی آپگرید کردن ورژن پاورکوئری در اکسل هم توضیح بدین. سپاسگزارم.
پاورکوئری دو حالت دارد یا همراه اکسل 2019 و یا 2016 نصب می شود که در این حالت باید آفیس را آپدیت کنید که معمولا اینکار باید توسط ویندوز انجام شود. یعنی آپدیت های آفیس همراه با آپدیت های ویندوز گرفته می شود. البته می توانید از سایت مایکروسافت فقط فایل آپدیت های اکسل را دانلود کنید. (در واقع اینکه آپدیت های آفیس مستقلا قابل دانلود باشد تا آنجا که به خاطر دارم مربوط به یکی از 2 لایسنس آفیس است که معمولا ما آفیسی را نصب می کنیم که قابلیت آپدیت کردن مستقل آن وجود ندارد و باید به همراه آپدیت ویندوز، آپدیت شود.)
برای نسخه های قدیمی یعنی 2013 و 2010 ، کار ساده تر است زیرا پاورکوئری یک افزونه است و کافی است که آخرین نسخه آنرا دانلود و نصب کنید.
با سلام و خسته نباشید
کیفیت آموزش بسیار مطلوب و خوب بود
چرا برای اکسل و اکسس ویدیوهای آموزشی بیشتر و مفصل تری تهیه نمی کنید و در سبد محصولات قرار بدید برای مخاطبین آموزش از طریق ویدئو بسیار راحتر از کتاب هست و اثر بخشی بیشتری دارد
با آرزوی موفقیت
برای اکسل موجود است از کانال تگرام ما را دنبال کنید تا لینک آنها و چیزهای جدیدی که در کانال و یا یوتیوب گذاشته ایم را بیابید.
با سلام و خسته نباشید
یکی از آموزش های بسیار کاربردی و مفید بود. قدرت انتقال مطالب عالی بود.
موفق و پیروز باشید
با تشکر فراوان از زحمات شما برای تهیه این ویدیو های مفید.
فقط میخواستم بپرسم اگر سوالی داشته باشیم میتونم از طریق ایمیل از شما بپرسم با خیر؟
از لطف شما سپاسگزارم واقعیت آن است که مشغله و کارهای جاری اجازه نمی دهد که پاسخ های کامل و مقنعی بدهیم و به همین خاطر از شما پوزش می خواهم.
جناب میدانی با عرض سلام.
ضمن تشکر از زحمات شما من وقتی که میخواهم دو فایل اکسل را با هم ادغام کنم در قسمت Invoke در پاور کوئری ایراد زیر را میگیرد
An error occurred in the ‘Transform File from New folder (2)’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table
من از همان فایل شما در ویدیو استفاده کردم
لطف می کنید اگر منو راهنمایی فرمایید.
با تشکر
من از این پیغام چیزی متوجه نشدم اما فکر کنم که در ویدئو هم اشاره کرده ام که تا قبل از نسخه 2019 اینکار با خطا مواجه می شد. اگر با 2 فایل کاملا ساده و شبیه هم تست کردیدو مشکل وجود داشت، به نظرم همان مشکل نسخه های قدیمی تر است.
عالی و سپاس از زحمات شما استاد ممنون که دانش خودتون را در اختیار ما هم قرار دادید مطالب واقعا مفید هستند.
سلام وقتتون بخیر
خیلی ممنون از ویدیوهای با کیفیت و مفید این قسمت
خیلی شیرین و رسا توضیح می دهید و بیننده لذت میبره از آموزش
موفق باشید
سلام باز تشکر فراوان میکنم واقعا نمی توانم چطور از شما تشکر کنم امیدوارم از خداوند هرچه آرزو دارید به شما عنایت فرماید. رامین قنبری
با سلام و خسته نباشید خدمت شما استاد محترم از مجموعه ی خوب و آموزنده اتون خواهشا فیلم های آموزش در رابطه با اسلایسرها در پاورکوئری نیز بگذارید.
باتشکر
با سلام . پاورکوئری اسلایسر ندارد. اطلاعات که در اکسل بیایند از slicer می توانید استفاده کنید. فیلم معرفی اسلایسر اکسل اینجاست:
www.farsaran.com/node/255
با سلام و عرض وقت بخیر
میخواستم از جناب فرشید میدانی که اینقدر عالی و روان توضیح دادن صمیمانه تشکر کنم . به همه کسانی که دوست دارند این مبحث رو شروع کنند توصیه میکنم که این ویدئو ها رو حتمــــــــا با دقت ببینند و اجرا کنند.
یک دنیــــــــا سپاس
با عرض سلام و ادب و تشکر بخاطر زحماتی که برای اعتلای دانش هموطنان ایران می کشید
بنده از زبان شیوا و تسلط کافی شما در ارائه و پرزینت مطلب به وجد آمدم وخواستم بدین وسیله از شما تشکر کنم و همچنین یک خواهش
اگر مقدور باشد لطف بفرمایید و از سایت کدال گزارش 3 ماهه یا 6 ماهه یک شرکت را با فرمت اکسل در پاور کوئری نرمال سازی کنید چون گزارش مذکور اطلاعت ترازنامه ،صورت سود وزیان و صورت جریان وجوه را همزمان در یک شیت دارد و نرمال سازی آن کمی برایم سخت است .
ضمناً من می خواهم اطلاعات شرکت ها را بصورت اتوماتیک از سایت کدال گرفته و ببرم داخل پاور کوئری و بعد از نرمال سازی یک پاور تیبل درست کنم و نسبت های مالی آن شرکت را محاسبه نمایم .ممنون می شوم اگر تا جایی که امکان پذیر باشد این دو درخواست فوق بعنوان یک فیلم اموزشی و تکنیک های لازم برای این موضوع آموزش داده شود . خیلی ممنون می شوم .با کمال تشکر
با سلام واحترام
من از سایت کدال صورت های مالی شرکتها را با قالب اکسل دانلود می کنم .زمانی که می خواهم فایل اکسل را باز کنم خطای تصویر زیر را می دهم ممنون می شوم راهنمایی بفرمایید
جناب میدانی بزرگوار
نمی دانم با چه زبانی از بابت تهیه این فایلهای آموزشی از شما تشکر کنم. خیلی عالی و کامل توضیح دادید. من چند تا آموزش خارجی (مثل لیندا) و ایرانی در این زمینه دیدم، متاسفانه در هیچ کدام به مفاهیم و علت وجودی ابزار اشاره نشده بود. فقط مطالب را به صورت کلی و سطحی بیان کرده بودند. می خواستم برم سراغ کتاب در زمینه power query و power pivot بگردم که خوشبختانه آموزش شما را یافتم. بابت تجربه ارزشمندی که انتقال دادید بسیار سپاسگزارم.
به امید روزی که اگر کسی آموزش تهیه کرد حتما خودش مثل شما آن مطلب را بطور کامل درک و تجربه کرده باشد.
در ضمن کتاب vba شما را هم تهیه کردم و بعد از خواندن آن بدنبال مطالب جدید از شما هستم.