راهنمای جامع Unpivot کردن دادهها در اکسل با Power Query
مشاهده ویدیوی تحلیل داده unpivot
(تحلیل حرفهای دادهها برای حسابداران و مدیران مالی)**
1. مقدمه: چرا Unpivot در پاور کوئری اهمیت دارد؟
در بسیاری از پروژههای حسابداری، مالی و گزارشگری، دادههای خام معمولاً در قالبهای «عریض» (Wide Format) ارائه میشوند. برای مثال، فروش ماهانه مشتریان، هزینههای مراکز هزینه، یا گزارشهای بودجهای ممکن است در ستونهای متعدد پخش شده باشند.
اما برای تحلیل حرفهای، مدلسازی دادهها، ساخت داشبورد و حتی ایجاد نسبتهای مدیریتی، معمولاً باید دادهها را به فرمت ستونی استاندارد (Long Format) تبدیل کنیم.
این تبدیل دقیقاً همان کاری است که ابزار Unpivot در Power Query برای ما انجام میدهد.
Power Query داده را «ذوب» میکند و از حالت گسترده به حالت تحلیلی تبدیل میکند، بدون نیاز به فرمولنویسی، کدنویسی یا ماکرو.
2. Unpivot چیست و چه زمانی استفاده میشود؟
Unpivot فرآیندی است که طی آن Power Query ستونهای متعدد را به دو ستون تبدیل میکند:
یک ستون شامل نام فیلد (Attribute)
یک ستون شامل مقدار (Value)
موارد استفاده رایج برای حسابداران و مالیها
تبدیل فروش ماهانه (فروردین تا اسفند) به فرمت Pivot-friendly
تبدیل گزارش بودجه سالانه به فرمت دیتابیس
تحلیل هزینهها به تفکیک مرکز هزینه
تبدیل دادههای چندستونه سیستمهای قدیمی به قالب استاندارد برای Power Pivot یا داشبوردها
آمادهسازی گزارشات برای استانداردهای IFRS یا صورتهای مالی تحلیلی
3. مراحل انجام Unpivot در Power Query (گامبهگام)
گام ۱: ورود دادهها به Power Query
از مسیر زیر، دادههای خود را وارد کنید:
Data → Get & Transform → From Table/Range
نکته حرفهای:
قبل از ارسال داده به Power Query، مطمئن شوید محدوده شما Table باشد (Ctrl + T). این کار باعث میشود هر تغییری در دادهها بهطور پویا به Power Query منتقل شود.
گام ۲: شناسایی ستونهای ثابت و ستونهای قابل Unpivot
در هر دیتاست، معمولاً یک یا چند ستون ثابت وجود دارد؛ مانند:
نام مشتری
کد کالا
مرکز هزینه
شماره حساب دفتر کل
و در کنار آنها ستونهایی مانند ماهها، دورهها یا سنجهها وجود دارند که باید Unpivot شوند.
گام ۳: انتخاب ستونهای مورد نیاز برای Unpivot
در Power Query دو روش دارید:
روش ۱: انتخاب ستونهایی که باید Unpivot شوند
Select → راستکلیک → Unpivot Columns
روش ۲: انتخاب ستونهایی که باید ثابت بمانند
Select → راستکلیک → Unpivot Other Columns
روش دوم معمولاً استانداردتر و مطمئنتر است، مخصوصاً وقتی ستونهای دورهای همواره در حال افزایش هستند.
4. نتیجه Unpivot: ساختار تحلیلپذیر
پس از انجام Unpivot، Power Query دو ستون جدید تولید میکند:
Attribute → شامل نام ستونهای قبلی (مثلاً ماهها)
Value → شامل مقدار هر دوره
شما میتوانید نام ستونها را به صورت زیر تغییر دهید:
Attribute → «ماه» یا «دوره»
Value → «مبلغ»
این ساختار بهترین قالب برای تهیه PivotTable، داشبورد، مدل مالی و Power Pivot است.
5. مثال کاربردی برای حسابداران
فرض کنید گزارش فروش شامل ستونهای زیر باشد:
| مشتری | فروردین | اردیبهشت | خرداد | ... |
با Unpivot، خروجی تبدیل میشود به:
| مشتری | ماه | مبلغ |
این ساختار امکان تحلیلهای زیر را فراهم میکند:
محاسبه سهم فروش هرماه
تحلیل روند فروش
تهیه داشبورد بر اساس Timeline
ترکیب داده با سایر جداول (مثلاً جدول کالاها یا مناطق فروش)
6. نکات حرفهای و Best Practices
۱. دادههای خالی (Null) را مدیریت کنید
میتوانید در Power Query از مسیر زیر مقادیر Null را حذف کنید:
Home → Remove Rows → Remove Blank Rows
۲. دقت کنید که ستونهای غیرعددی اشتباهاً Unpivot نشوند
مثلاً ستون توضیحات، تاریخ ثبت، یا وضعیت تأیید.
۳. برای گزارشات بودجه، همیشه ستون ماه را بهصورت نوع داده تاریخ یا متن استاندارد تنظیم کنید.
۴. از ترکیب Unpivot + Merge برای ساخت Data Model حرفهای استفاده کنید.
۵. Power Query Case Sensitive است؛ نام ستونها دقیقاً باید یکسان باشد.
7. جمعبندی
ابزار Unpivot در Power Query یکی از توانمندترین ابزارهای تبدیل ساختار داده برای حسابداران، تحلیلگران مالی و مدیران است. این ابزار، دادههای عریض و غیرقابل تحلیل را به یک دیتابیس استاندارد تبدیل میکند و زمینه را برای ساخت داشبوردهای پویا، گزارشات مدیریتی و تحلیلهای حرفهای فراهم میسازد.
با استفاده از Unpivot:
سرعت پردازش داده بیشتر میشود
احتمال خطای انسانی کاهش مییابد
گزارشها قابل اتکا، پویا و استاندارد میشوند
ویدیوهای آموزشی شما کاملاً کاربردیتر و حرفهایتر خواهند شد
