آموزش Power Query در اکسل: جادوی تمیز کردن داده‌ها در ۱۰ دقیقه

تیر 20, 1405 در تاریخ تیر ۲۰, ۱۴۰۵

آموزش Power Query در اکسل: جادوی تمیز کردن داده‌ها در ۱۰ دقیقه

فهرست مطالب :

تا حالا شده یک فایل اکسل از واحد مالی یا یک مشتری دریافت کنید و با دیدن آن، قلبتان هری بریزد؟ ستون‌هایی که ترکیبی از عدد و متن هستند، تاریخ‌های شمسی و میلادی قاطی‌پاتی، هزاران ردیف که نصفشان خالی است، و یک جدول که انگار از یک کابوس بیرون آمده. شاید یک کارمند ساده هستید که باید گزارش ماهانه را تحویل بدهد، یک دانشجو که داده‌های پایان‌نامه را جمع‌آوری کرده، یا یک خانم خانه‌دار که می‌خواهد مخارج خانواده را مرتب کند. تمیز کردن این داده‌ها با دست نه تنها ساعت‌ها وقت می‌گیرد، بلکه پر از اشتباه است. اما نگران نباشید! در این راهنما، شما را با یک ابرقهرمان به نام Power Query در اکسل آشنا می‌کنم که این فرآیند طاقت‌فرسا را به چند کلیک ساده تبدیل می‌کند. قول می‌دهم پس از ۱۰ دقیقه مطالعه و کمی تمرین، به جادوگری در تمیز کردن داده‌ها تبدیل شوید.

Power Query دقیقاً چیست؟ (به زبان ساده)

Power Query یک موتور ETL (استخراج، تبدیل، بارگذاری) است که در دل اکسل زندگی می‌کند. کارش ساده است: داده‌ها را از هر جایی (فایل اکسل، CSV، وب‌سایت، پوشه‌ای پر از فایل) دریافت می‌کند، آن‌ها را می‌شوید، اتو می‌کند، و مرتب تحویل می‌دهد.

فرض کنید یک فایل داده‌های فروش دارید که هر روز آپدیت می‌شود. شما یک‌بار مراحل تمیزکاری را روی آن اجرا می‌کنید. Power Query تمام قدم‌هایتان را مثل یک نوار ضبط می‌کند. دفعه بعد که فایل جدید رسید، فقط کافی است دکمه Refresh را بزنید. تمام آن ساعت‌ها کار تکراری، در چند ثانیه انجام می‌شود. این دقیقاً معنای جادو است!

آشنایی با محیط Power Query در اکسل ۲۰۲۶

قبل از شروع، بیایید ببینیم Power Query کجای اکسل قایم شده است. در تمام نسخه‌های Excel ۲۰۱۶ به بعد (و Microsoft ۳۶۵)، این ابزار در سربرگ Data (داده‌ها) با نام Get & Transform Data (دریافت و تبدیل داده‌ها) قرار دارد.

برای وارد شدن به ویرایشگر Power Query، کافی است پس از انتخاب منبع داده، به جای Load روی Transform Data کلیک کنید. محیط ویرایشگر شامل بخش‌های زیر است:

Ribbon (نوار ابزار): شامل سربرگ‌های Home، Transform، Add Column، View و … که تمام فرمان‌ها در آن قرار دارند.
Queries Pane (پنل کوئری‌ها): در سمت چپ، لیست تمام پرس‌وجوهایی که ساخته‌اید را نشان می‌دهد.
Data Preview (پیش‌نمایش داده‌ها): وسط صفحه، جایی که داده‌هایتان را مثل یک جدول اکسل می‌بینید.
Applied Steps (مراحل اعمال‌شده): در سمت راست، تاریخچه تمام کارهایی که روی داده‌ها انجام داده‌اید. اینجا جادوی ضبط و تکرار اتفاق می‌افتد.

یک نکته طلایی: هر مرحله‌ای که در Applied Steps می‌بینید، قابل ویرایش، حذف یا جابه‌جایی است. اگر اشتباه کردید، کافی است روی ضربدر کنار آن مرحله کلیک کنید. همه چیز به حالت قبل برمی‌گردد!

وارد کردن داده‌ها از منابع مختلف

Power Query می‌تواند داده‌ها را از منابع بسیار متنوعی دریافت کند. بیایید با رایج‌ترین آن‌ها آشنا شویم:

۱. از فایل اکسل

به تب Data بروید و From File > From Excel Workbook را انتخاب کنید.
فایل مورد نظر را انتخاب کرده و شیت حاوی داده را برگزینید.
Transform Data را بزنید تا ویرایشگر باز شود.

۲. از فایل CSV یا Text

گزینه From File > From Text/CSV را انتخاب کنید.
فایل CSV را باز کنید. Power Query معمولاً delimiter (جداکننده) را خودش تشخیص می‌دهد. اگر درست تشخیص نداد (مثلاً برای فایل‌های فارسی که جداکننده “;” است)، می‌توانید آن را در پنجره باز شده تغییر دهید.

۳. از وب‌سایت

گزینه From Other Sources > From Web را بزنید.
آدرس صفحه وب را وارد کنید. Power Query جداول موجود در صفحه را شناسایی کرده و لیست می‌کند.
جدول مورد نظر را انتخاب و بارگذاری کنید. این قابلیت برای دریافت داده‌های بورس، آب‌وهوا یا هر داده آنلاین دیگری عالی است.

۴. از یک پوشه پر از فایل

اگر هر روز یک فایل جدید در یک پوشه قرار می‌گیرد، از From File > From Folder استفاده کنید.
پوشه را انتخاب کنید. Power Query لیست فایل‌ها را نشان می‌دهد.
روی Combine Files کلیک کنید تا همه فایل‌ها ادغام شوند (در ادامه بیشتر توضیح می‌دهیم).

۶ تکنیک طلایی تمیزکاری داده‌های فارسی

حالا برسیم به اصل مطلب: تبدیل آن داده‌های آشفته به یک جدول تمیز و آماده تحلیل. داده‌های نمونه ما یک فایل فروش با این مشخصات است:

ستون «نام مشتری»: پر از فاصله‌های اضافی و غلط املایی
ستون «تاریخ»: ترکیبی از ۱۴۰۵/۰۴/۰۱ و ۲۰۲۶-۰۶-۲۲
ستون «مبلغ»: نوشته شده به صورت «۱,۵۰۰,۰۰۰ تومان»
ستون «کد ملی»: بعضی ۱۰ رقمی، بعضی با خط تیره
ردیف‌های خالی و تکراری در همه جا

۱. حذف فضای خالی ابتدا و انتها (Trim)

روی ستون «نام مشتری» کلیک راست کرده و از منوی Transform گزینه Trim را انتخاب کنید. تمام فاصله‌های بی‌فایده حذف می‌شوند. همچنین می‌توانید از Clean برای پاک کردن کاراکترهای غیرقابل چاپ استفاده کنید.

۲. یک‌دست کردن تاریخ‌های شمسی و میلادی

از آنجایی که Power Query تقویم شمسی را به‌صورت پیش‌فرض ندارد، از یک ترفند استفاده می‌کنیم:

ابتدا نوع ستون «تاریخ» را با کلیک روی آیکون سمت چپ ستون، روی Text تنظیم کنید.
از سربرگ Add Column، گزینه Column From Examples (ستون از نمونه‌ها) را انتخاب کنید.
در ستون جدید، برای اولین ردیف، فرمت صحیح را تایپ کنید (مثلاً تاریخ شمسی را به صورت ۱۴۰۵/۰۴/۰۱ بنویسید). Power Query الگو را تشخیص داده و برای بقیه پیشنهاد می‌دهد.
می‌توانید از Replace Values هم استفاده کنید: مثلاً “/” را به “-” تبدیل کنید تا همه تاریخ‌ها یک‌شکل شوند. سپس با تنظیم Locale روی “Persian” می‌توانید آن‌ها را به فرمت Date تبدیل کنید.

۳. جدا کردن عدد از متن (پاکسازی ستون مبلغ)

ستون «مبلغ» را انتخاب کنید. در سربرگ Transform روی Replace Values کلیک کنید.
در کادر “Value To Find” بنویسید تومان (با یک فاصله قبل از آن) و در “Replace With” هیچ چیز نگذارید. OK کنید.
حالا کاماهای جداکننده هزارگان را هم حذف کنید: یک Replace Values دیگر با جستجوی , و جایگزینی با هیچ.
هنوز ستون به صورت متن است. روی آیکون نوع داده کلیک کرده و Whole Number یا Decimal Number را انتخاب کنید. Power Query خودش تبدیل را انجام می‌دهد.

۴. فرمت‌دهی کد ملی و مقادیر عددی ثابت

ستون «کد ملی» را انتخاب کنید. از سربرگ Transform گزینه Replace Values را بزنید و خط تیره (-) را حذف کنید.
برای اطمینان از ۱۰ رقمی بودن، می‌توانید از Format > Add Prefix استفاده نکنید، بلکه با Column From Examples یک ستون جدید بسازید که اگر طول رشته کمتر از ۱۰ باشد، صفر اضافه کند (Power Query خودش این الگو را یاد می‌گیرد).

۵. حذف ردیف‌های تکراری

کلید Ctrl را نگه دارید و ستون‌هایی که ترکیب آن‌ها منحصربه‌فرد است (مثلاً کد ملی + تاریخ) را انتخاب کنید.
در سربرگ Home روی Remove Rows > Remove Duplicates کلیک کنید.

۶. پر کردن سلول‌های خالی یا فیلتر کردن آن‌ها

اگر می‌خواهید ردیف‌های کاملاً خالی حذف شوند: یکی از ستون‌ها را انتخاب کنید، روی فلش کوچک فیلتر کلیک کرده و Remove Empty را بزنید.
اگر می‌خواهید سلول‌های خالی با مقدار مشخصی پر شوند: ستون را انتخاب کرده، در سربرگ Transform گزینه Replace Values را بزنید. در “Value To Find” چیزی ننویسید و در “Replace With” مقدار مورد نظر (مثلاً ۰) را وارد کنید.

و تمام! حالا روی Close & Load کلیک کنید تا Power Query جدول تمیز و اتوکشیده را در یک شیت جدید تحویل دهد. هر زمان که فایل اصلی تغییر کرد، فقط کافی است در جدول خروجی راست‌کلیک کرده و Refresh را بزنید. همه مراحل دوباره اجرا می‌شود!

ادغام و یکی کردن چند فایل و جدول (Merge & Append)

این قابلیت‌ها برای حسابداران و دفترداران حکم معجزه را دارد.

Append (چسباندن ردیف‌ها زیر هم)

وقتی چند فایل با ساختار یکسان دارید (مثلاً گزارش فروش هر ماه در یک فایل جداگانه):

بعد از بارگذاری اولین فایل در Power Query، در سربرگ Home گزینه Append Queries را بزنید.
در پنجره باز شده، سایر کوئری‌ها یا فایل‌ها را انتخاب کنید. Power Query همه ردیف‌ها را زیر هم می‌چسباند.

Merge (وصل کردن جدول‌ها بر اساس یک کلید مشترک)

درست مانند VLOOKUP اما بسیار سریع‌تر و انعطاف‌پذیرتر. فرض کنید یک جدول فروش با کد کالا دارید و یک جدول اطلاعات کالاها (نام، قیمت) با همان کد. می‌خواهید نام کالا را به جدول فروش اضافه کنید:

در ویرایشگر، از سربرگ Home روی Merge Queries کلیک کنید.
در پنجره باز شده، جدول فروش (بالا) و جدول کالاها (پایین) را انتخاب کنید.
روی ستون «کد کالا» در هر دو جدول کلیک کنید تا هایلایت شوند. نوع Join را معمولاً روی Left Outer (همه ردیف‌های اولی با اطلاعات منطبق از دومی) بگذارید.
OK کنید. حالا یک ستون جدید به نام “New Column” اضافه می‌شود. روی آیکون دو فلش کنار این ستون کلیک کرده و ستون‌های مورد نظر از جدول دوم (مثلاً نام کالا) را انتخاب کنید.

جادوی Unpivot: تغییر ساختار جدول‌های غیرقابل تحلیل

یکی از رایج‌ترین فاجعه‌های دنیای اکسل، جدول‌های به‌اصطلاح «گزارشی» یا «Pivotشده» است. مثلاً یک جدول فروش دارید که در سطرها نام محصول و در ستون‌ها ماه‌های سال (فروردین، اردیبهشت، …) قرار دارد و وسط جدول هم اعداد فروش است. تحلیل این ساختار با Pivot Table یا فرمول‌ها تقریباً غیرممکن است. راه‌حل جادویی: Unpivot Columns.

همه ستون‌های ماه‌ها را با Shift انتخاب کنید.
کلیک راست کرده و Unpivot Columns را بزنید.
فوراً جدول شما به سه ستون «محصول»، «ماه» و «مقدار فروش» تبدیل می‌شود. حالا می‌توانید به‌راحتی با یک Pivot Table، فروش هر محصول در هر ماه را تحلیل کنید.

این یک تکنیک ساده اما فوق‌العاده قدرتمند است که شما را از سایر همکارانتان متمایز می‌کند.

هوش مصنوعی در Power Query: معرفی Copilot (جدید ۲۰۲۶)

از سال ۲۰۲۵، مایکروسافت دستیار هوش مصنوعی Copilot را به Power Query در Microsoft ۳۶۵ اضافه کرده است. تصور کنید دیگر نیاز نیست منوها را زیر و رو کنید. کافی است در پنل Copilot به فارسی یا انگلیسی بنویسید: «ستون تاریخ را به تاریخ شمسی تبدیل کن» یا «یک ستون جدید به اسم قیمت نهایی بساز که قیمت را ضربدر ۱.۰۹ کند». Copilot خودش قدم‌های لازم را تشخیص می‌دهد، پیش‌نمایش را نشان می‌دهد و با تأیید شما، آن را به عنوان یک مرحله به Applied Steps اضافه می‌کند.

این یعنی حتی اگر فرمول‌نویسی بلد نباشید، می‌توانید با زبان طبیعی (متن ساده)، کارهای پیچیده را انجام دهید. در سال ۲۰۲۶، این قابلیت به سرعت در حال گسترش است و یادگیری Power Query را از همیشه آسان‌تر کرده است.

سناریوهای واقعی: Power Query برای شغل شما

کارمند اداری / منشی

مشکل هر ماه: لیست حضور و غیاب از سیستم با ستون‌های اضافی و فرمت نامناسب. با Power Query ستون‌های غیرضروری را حذف می‌کنید، تاریخ‌ها را یک‌دست می‌کنید، و یک خروجی تمیز برای مدیر آماده می‌نمایید. با Refresh ماه بعد، همه چیز خودکار است.

حسابدار / دفتردار

۱۲ فایل فروش ماهانه از شعب مختلف دارید. با From Folder و Combine Files همه را یکجا ادغام می‌کنید. یک ستون از تاریخ استخراج می‌کنید، ماه و سال را جدا می‌کنید. حالا می‌توانید با یک Pivot Table ساده، فروش هر شعبه در هر ماه را گزارش دهید.

دانشجو

برای پایان‌نامه، یک دیتاست بزرگ از اینترنت دانلود کرده‌اید. پر از کاراکترهای عجیب، ستون‌های خالی و اعداد متنی است. با Power Query، داده‌ها را تمیز می‌کنید، متغیرهای جدید می‌سازید و خروجی را وارد SPSS یا Python می‌کنید. یک بار این کوئری را بسازید، برای هر دیتاست جدید فقط Refresh کنید.

معلم / استاد

نمرات دانشجویان از سامانه دانشگاه به صورت جدول عریض (هر درس یک ستون) خروجی گرفته می‌شود. با Unpivot Columns آن را به ساختار تحلیلی تبدیل می‌کنید و میانگین هر درس را محاسبه می‌نمایید.

خانم خانه‌دار / بودجه شخصی

خرج‌های روزانه را در یک فایل ساده ثبت می‌کنید. با Power Query ستون «دسته‌بندی» را تمیز می‌کنید (مثلاً جایگزینی «فست فود» با «خوراک بیرون»). سپس جدول خروجی را با یک Pivot Table تحلیل می‌کنید تا ببینید چقدر برای هر دسته هزینه کرده‌اید. با Refresh، بودجه‌تان همیشه به‌روز است.

۵ اشتباه رایج تازه‌کارها و راه‌حل آن‌ها

۱. ویرایش دستی خروجی Power Query
هرگز سلول‌های جدول خروجی را دستی تغییر ندهید. با Refresh بعدی، تمام ویرایش‌هایتان پاک می‌شود. اگر نیاز به اصلاح دارید، به ویرایشگر Power Query برگردید.

۲. فراموش کردن تنظیمات Regional (محلی)
اگر اعداد اعشاری شما با ممیز فارسی (/) نمایش داده می‌شوند و Power Query آن را متن تشخیص می‌دهد، در تنظیمات کوئری (File > Options and settings > Query Options) زبان محلی را روی Persian تنظیم کنید.

۳. تغییر نام ستون قبل از حذف ردیف‌های عنوان
اگر ردیف اول داده‌ها یک عنوان اضافی است، اول از Remove Rows > Remove Top Rows استفاده کنید، سپس از Use First Row as Headers. اگر ترتیب را برعکس کنید، نام ستون‌ها خراب می‌شود.

۴. نادیده گرفتن نوع داده (Data Type)
حتماً پس از تمیزکاری، نوع داده هر ستون را بررسی کنید. عددها را Whole Number یا Decimal Number تنظیم کنید، تاریخ‌ها را Date، و متن‌ها را Text. در غیر این صورت، فرمول‌ها و Pivot Tableهای بعدی به درستی کار نمی‌کنند.

۵. بارگذاری بی‌رویه داده‌های اضافی
اگر فقط به بخشی از داده‌ها نیاز دارید، پیش از بارگذاری نهایی، ستون‌های اضافی را Remove Columns کنید و فیلترهای لازم را اعمال کنید. این کار حجم فایل و سرعت Refresh را به شدت کاهش می‌دهد.

سوالات متداول (FAQ)

۱. Power Query در کدام نسخه‌های اکسل وجود دارد؟
در Excel ۲۰۱۶، ۲۰۱۹، ۲۰۲۱ و Microsoft ۳۶۵ به صورت داخلی. برای Excel ۲۰۱۰ و ۲۰۱۳ افزونه رایگان آن قابل دانلود است.

۲. تفاوت Power Query و Power Pivot چیست؟
Power Query برای تمیز کردن و شکل دادن به داده‌ها (ETL) است. Power Pivot برای تحلیل و مدل‌سازی داده‌های تمیز، ایجاد روابط و فرمول‌های پیچیده (DAX) به کار می‌رود. معمولاً Power Query اول می‌آید، سپس Power Pivot.

۳. آیا می‌توان مراحل Power Query را ویرایش یا حذف کرد؟
بله، پنل Applied Steps در سمت راست ویرایشگر تمام مراحل را نشان می‌دهد. می‌توانید روی هر مرحله کلیک کنید، آن را ویرایش کنید، حذف کنید (با ضربدر کنار آن)، یا مراحل جدید را بین آن‌ها اضافه کنید.

۴. آیا Power Query جایگزین VLOOKUP می‌شود؟
برای ادغام جدول‌ها، Merge Queries بسیار قدرتمندتر و انعطاف‌پذیرتر از VLOOKUP است. اما برای کارهای ساده در یک جدول کوچک، VLOOKUP هنوز هم سریع و مفید است. Power Query بیشتر برای پاک‌سازی و آماده‌سازی داده‌های بزرگ طراحی شده است.

۵. چطور می‌توانم کوئری Power Query را به فایل دیگری منتقل کنم؟
کافی است در پنجره Queries & Connections (تب Data)، روی کوئری راست‌کلیک کرده و Copy را بزنید. سپس در فایل مقصد، در همان پنجره، راست‌کلیک کرده و Paste کنید.

جمع‌بندی و گام‌های بعدی برای حرفه‌ای شدن

شما حالا با ابزاری آشنا شده‌اید که می‌تواند ساعت‌ها زمان شما را ذخیره کند و خطاهایتان را به صفر برساند. Power Query نه‌تنها یک ابزار، بلکه یک طرز فکر جدید برای کار با داده‌هاست: «یک بار بساز، همیشه استفاده کن». از تمیز کردن یک فایل ساده گرفته تا ادغام صدها فایل و ساخت داشبوردهای مدیریتی، همه چیز با این مهارت ممکن می‌شود.

اما این تازه اول راه است. ترکیب Power Query با Power Pivot و Pivot Table، شما را به یک تحلیل‌گر داده حرفه‌ای تبدیل می‌کند که می‌تواند از دل انبوه داده‌های خام، اطلاعات ارزشمند برای تصمیم‌گیری‌های سازمانی استخراج کند. این مهارت‌ها دقیقاً همان چیزهایی هستند که در آگهی‌های استخدام با عناوین «تحلیل‌گر داده»، «کارشناس هوش تجاری» یا «مدیر گزارش‌ها» به دنبال آن می‌گردند.

اگر به دنبال یادگیری عمیق‌تر و تسلط کامل بر Power Query، Power Pivot و دیگر ابزارهای حرفه‌ای اکسل هستید، دوره‌های تخصصی و پروژه‌محور می‌توانند این مسیر را برای شما هموار کنند. در این دوره‌ها، با مثال‌های واقعی از صنایع مختلف، از یک کاربر معمولی به یک متخصص تبدیل می‌شوید که می‌تواند هرج و مرج داده‌ها را به گزارش‌های درخشان و حقوق بالاتر تبدیل کند.

همین امروز قدم اول را بردارید و یادگیری حرفه‌ای را آغاز کنید—دنیای تحلیل داده منتظر شماست.