نحوه تبدیل ستون ها به سطرها در مایکروسافت اکسل با Power Query

چگونه می توانیم در مایکروسافت اکسل ستون های از داده ها را به سطرها تبدیل کنیم؟ این آموزش را هم اکنون از فراز سافت مطالعه کنید.

نحوه تبدیل ستون ها به سطرها در مایکروسافت اکسل با Power Query

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

از دیگر ویژگی هی مایکروسافت اکسل Power Query است. Power Query یا دریافت و تبدیل داده ها راهی است که به راحتی شما می توانید داده های خود را پردازش و تحلیل کنید. یکی از محبوب ترین مراحل پردازش داده های این است که داده ها را در ستون های بیشتری پخش کنیم و سپس آن ها را به سطرهایی تبدیل کنیم.

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

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

چرا ستون ها به سطرها تبدیل می کنیم؟

قبل از این که این آموزش را شروع کنیم، ممکن است این سؤال برای شما ایجاد شود که چرا داده هایی را که در ستون های اکسل ایجاد شده است را به سطرها تبدیل می کنیم؟

پاسخ این سؤال کمی فنی است. اساساً، بهتر است هر ردیف از داده مانند یک record، یک داده تک و منحصر به فرد باشد. داده های ستونی ترکیبی از چندین ویژگی و خواص بر طبق ستون هستند.

در تصویر زیر شما مثالی را می بینید که در آن داده های ستونی به داده های سطری در مایکروسافت اکسل تبدیل شده اند. در تصویر سمت چپ، لیستی از مشتری ها با پروژه های مختلف در ستون نشان داده شده است و در سمت راست همان داده ها می باشند که به سطر تبدیل شده اند.

چرا ستون ها به سطرها تبدیل می کنیم؟

همچنن اگر شما از Pivot Table استفاده می کنید، بسیار مهم است که شما از سطرها به جای ستون ها برای ایجاد Pivot Table استفاده کنید.

Power Query در مایکروسافت اکسل

Power Query، در مایکروسافت اکسل ۲۰۱۶، Get and Transform Data نامیده می شود. این ابزار دارای دو عملکرد اصلی است:

۱-‌ شما می توانید از آن برای گرفتن اطلاعات مانند اشاره کردن به فایل های دیگر اکسل، پایگاه داده ها و حتی صفحات وب برای قراردادن آن در صفحات گسترده استفاده کنید.

۲-‌ به شما کمک می کند تا داده های خود را تغییر دهید، که به معنی افزودن سلول های دیگر یا ادغام آن با مجموعه ای دیگر از داده ها است.

Power Query در مایکروسافت اکسل

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

نحوه نصب Power Query در مایکروسافت اکسل

Power Query برای اولین بار در مایکروسافت اکسل قرار داده شده است، اما شما می توانید آن را در سایر نسخه های مایکروسافت اکسل به عنوان یک افزودنی نصب کنید. متاسفانه، Power Query تنها برای اکسل بر روی ویندوز قابل استفاده است.

برای نصب Power Query برای اکسل بر ویندوز، به وب سایت مایکروسافت بروید و Power Query دانلود و نصب کنید.

همچنین بخوانید: آموزش جامع و کاربردی تبدیل صفحات گسترده اکسل به Google Sheets

فرستادن داده ها به Power Query

Power Query در داخل مایکروسافت اکسل باز می شود، اما در یک پنجره جدید باز خواهد شد که در بالای مایکروسافت اکسل قرار دارد.

برای جداسازی داده در اکسل، نخست شما نیاز دارید که داده های خود را به یک جدول تبدیل کنید، البته اگر داده های خود را از قبل در این فرمت قرار نداده اید. شما می توانید یک جدول را به راحتی به Power Query منتقل کنید.

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

برای تبدیل داده ها به یک جدول، بر روی یکی از ستون ها کلیک کنید و سپس در تب Home گزینه Format as Table را پیدا و بر روی منو کشویی آن کلیک کنید. شما می توانید بر روی هر کدام از طرح های مورد نظر خود کلیک کرده تا داده های خود را به یک جدول تبدیل کنید.

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

انتقال جدول داده ها به Power Query

هنگامی که داده های خود را به جدول تبدیل کردید، به تب Data در نوار ابزار اکسل بروید و بر روی From Table کلیک کنید تا داده ها به Power Query منتقل و ارسال شوند.

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

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

Unpivot Data

در این مرحله باید چیزی شبیه تصویر زیر را مشاهده کنید. این پنجره دارای ابزارهایی است که شما به آن نیاز دارید.

برای انجام دادن unpivot داده های خود، ابتدا داده های خود را انتخاب و برجسته کنید. اساساً، Power Query هر یک از ستون ها را به سطرها تبدیل می کند. برای نمونه ما در مثال زیر هر یک از ستون ها را انتخاب کرده ایم.

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

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

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

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

بستن و بارگذاری داده ها برای ارسال به مایکروسافت اکسل

پنجره Power Query دارای ویژگی های بسیار مفید و سودمند است که می توانید از آن برای سایر آموزش ها استفاده کنید. خب حالا بر روی Close & Load کلیک کنید تا داده ها را به داخل مایکروسافت اکسل انتقال دهیم.

بستن و بارگذاری داده ها برای ارسال به مایکروسافت اکسل

با کلیک بر روی Close and Load، داده ها را به مایکروسافت اکسل انتقال می یابند و آن را در یک صفحه گسترده قرار می دهند. شما در سمت راست پنجره queries را مشاهد خواهید کرد که نشان می دهد، Power Query و unpivot data انجام شده است.

Refresh the Query

اگر ما بخواهیم اطلاعات اصلی را تغییر دهیم باید چه کاری را انجام دهیم؟ اگر شما می خواهید سطرهای بیشتری را به را به داده های اصلی اضافه کنید، لزومی ندارد که کل فرآیند Power Query را تکرار کنید.

به جای تکرار مراحل Power Query، شما می توانید از گزینه Refresh Data استفاده کنید. در تصویر زیر ما یک ردیف کاملاً جدید را به داده های اصلی client اضافه کرده ایم:

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

خب حالا اجازه دهید به تب بر گردیم که داده های شما در آن بارگذاری شده اند. وقتی که شما بر روی جدول داده ها کلیک می کنیم، منوی Workbook Queries خواهید دید. در سمت راست منو بر روی دکمه Refresh کلیک کنید تا Power Query را رفرش شود.  

با انجام این عمل مراحل Power Query دوباره اجرا و انجام می شود. از آنجایی که ما ردیف های جدیدی را به داده های اصلی اضافه کرده ایم، Power Query این ردیف های جدید را به query اضافه می کند و آن ها را اجرا می کند.

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

یکی از ویژگی های مورد علاقه کاربرارن در Power Query، رفرش کردن یا تازه سازی کردن داده ها است. ما ابتدا داده های جدید خود را به حدول اصلی اضافه کردیم و بدون آنکه مراحل Power Query را انجام دهیم، با یک کلیک ساده داده ها به query اضافه شده اند.

با یکی دیگر از آموزش های سایت فراز سافت همراه شما بودیم. در این آموزش به شما نحوه تبدیل ستون ها به سطرها را در مایکروسافت اکسل با استفاده از Power Query آموزش دادیم. امیدواریم از آن نهایت استفاده را ببرید و به دیگر آموزش های ما در فراز سافت همراه باشید.