اینجا، همانجاست که power query وارد عمل میشود. power query که با نام “Get and Transform Data” نیز شناخته میشود، در درون اکسل قرار دارد و وظیفه منظم سازی داده ها را بر عهده دارد. با unpivot کردن داده های خود در اکسل با استفاده از power query مهارت های اکسل خود را یک گام بالاتر ببرید. در این مطلب، نحوه تبدیل ستون ها به ردیف در اکسل با استفاده از power query را فراخواهید گرفت.
آموزش تصویری نحوه Unpivot کردن داده ها در پاور کوئری
شما میتوانید در زیر نحوه Unpivot کردن داده های خود را به صورت تصویری مشاهده کنید.
چرا باید ستون ها را تبدیل به ردیف کنیم؟
قبل از شروع، ممکن است بپرسید چرا باید داده هایی را که به صورت ستون ساختاربندی شده اند تبدیل به ردیف کنیم؟
پاسخ کمی فنی است. اساساً، این روش زمانی مفید است که هر ردیف داده شبیه به یک رکورد اطلاعات یا یک نقطه منحصر به فرد باشد. داده های ستونی با مشخصه های متفاوتی که در بین ستون های مختلف پراکنده شده اند، مخلوط شده اند.
در مثال زیر، میتوانید مشاهده کنید که زمانی که داده های خود را از حالت ستونی به ردیف تبدیل میکنید به چه شکلی تبدیل میشود. در سمت راست، داده ها را unpivot کرده ایم و آن را تبدیل به فرمت ردیفی کرده ایم.
همچنین، اگر با pivot table کار میکنید، بهتر است که داده هایی را که میخواهید در pivot table مورد استفاده قرار دهید، از حالت ستونی تبدیل به ردیف کنید. داده هایی که به شکل ستونی هستند، به درستی در pivot table کار نمیکنند.
به صورت خلاصه، تبدیل داده ها از حالت ستونی به حالت ردیفی ، کار با آنها را راحت تر میکند. power query این کار را بسیار آسان و فقط با زدن دو کلیک انجام میدهد.
نحوه تبدیل سریع ستون ها به ردیف در اکسل( مشاهده و یادگیری)
دستورالعمل نوشتاری در این زمینه را که به صورت گام به گام توضیح داده شده است مطالعه فرمائید.
استفاده از power query اکسل
power query که در اکسل 2016 بعنوان “Get and Transform Data” نامیده شده است، ابزاری است که دو وظیفه اساسی را بر عهده دارد:
- از این ابزار میتوان برای بدست آوردن داده استفاده کرد. برای مثال از این ابزار برای استخراج داده از دیگر فایل های اکسل، پایگاه های داده، صفحات وب و غیره استفاده میشود.
- این ابزار به شما کمک میکند، داده های خود را تبدیل کنید به این معنی که آنها را سازماندهی مجدد نمایید، ستون های بیشتر اضافه کنید، یا دیگر مجموعه های داده را با آن ترکیب کنید.
power query ابزاری قدرتمند است که در درون نرم افزار اکسل قرار دارد. به جای سازماندهی مجدد داده به صورت دستی و تکرار فرایند، power query به شما امکان میدهد که چند مرحله را تنظیم کنید و یک کار را به صورت مکرر برای شما انجام میدهد.
نصب power query
power query بعنوان جزیی اصلی از ورژن اکسل 2016 قرار داده شده است ولی این ابزار را میتوان برای ورژن های قبلی اکسل نیز بعنوان یک افزونه نصب کرد. متأسفانه power query فقط برای ورژن های ویندوز اکسل قابل استفاده است.
برای نصب power query اکسل برای ویندوز (فقط برای ورژن های 2010 و 2013 اکسل ، زیرا این ابزار در ورژن 2016 اکسل به صورت ثابت قرار داده شده است)، میتوانید به سایت مایکروسافت مراجعه کنید و این ابزار را دانلود و نصب کنید.
ارسال داده به power query
power query در درون اکسل مورد استفاده قرار میگیرد، ولی در یک پنجره جدید باز میشود که در بالای اکسل قرار دارد.
برای unpivot کردن داده ها در اکسل، می بایست ابتدا اگر داده های شما به شکل جدول نیست، آن را به فرمت جدول تبدیل کنید. power query میتواند بدون دخالت شما کارهای مربوط به داده های درون جدول شما را انجام دهد.
تبدیل به جدول
برای تبدیل داده ها به جدول، میتوانید هر جایی درون داده ها کلیک کنید، سپس گزینه format as table را در ریبون اکسل پیدا کنید. میتوانید بر روی هر کدام از استایلها کلیک کنید تا سلول های شما را تبدیل به جدول دلخواه کند.
استفاده از power query
زمانی که داده های شما تبدیل به فرمت جدول شد، به پنجره Data مراجعه کنید و بر روی دکمه from table برای ارسال جدول داده های خود به power query کلیک کنید.
این کار یک پنجره جدید باز میکند که در درون اکسل و بالای پنجره آن باز میشود. این پنجره کاملاً جدید است و ویژگی های بسیار متعددی دارد.
Unpivot کردن داده ها
حال، می بایست چیزی شبیه به تصویر زیر را مشاهده کنید. این پنجره ابزارهای مورد نیاز شما را برای unpivot کردن داده ها در اختیار شما قرار میدهد.
برای unpivot کردن داده ها در اکسل، تمام ستون هایی را که میخواهید unpivot کنید هایلایت نمایید،. اساساً، power query هرکدام از ستون های انتخاب شده را تبدیل به یک ردیف میکند. در مورد داده های نمونه در اینجا، من تمام ستون های مربوط به نوع پروژه را هایلات کرده ام:
زمانی که گزینه unpivot columns را انتخاب کردید، اکسل داده های ستونی شما را تبدیل به ردیف میکند. هر ردیف، به خودی خود یک رکورد محسوب میشود و آماده وارد شدن به pivot table یا به کار گرفته شدن در شیت اکسل است.
این ویژگی شبیه به جادو است. حال داده ها را به محیط اصلی اکسل برمیگردانیم تا آنها را به کار گیریم.
Close&load
power query ویژگی های بسیار متعددی دارد که میتوانید در مطالب دیگر آنها را فرابگیرید ولی ، ما مطلب خود را با unpivot کردن داده ها به پایان میرسانیم. حال میتوانید با زدن دکمه close &load داده ها را دوباره به اکسل برگردانید.
Close&load داده ها را به اکسل برمیگرداند، و آنها را در درون صفحه اکسل قرار میدهد. همچنین میتوانید پنجره query را که ساختیم را در سمت راست مشاهده کنید.
Refresh کردن query
اگر داده های اصلی شما تغییر کند، چه باید کرد؟ اگر ردیف های بیشتری به داده های اصلی خود اضافه کنید، نیازی به تکرار تمام فرایند انجام شده نیست.
به جای این کار، میتوانید از گزینه refresh data استفاده کنید. من یک ردیف جدید برای یک مشتری جدید به داده های اضلی خود اضافه کردم:
حال به پنجره ای که داده های unpivot ما در آن بارگذاری شده است برمیگردیم. زمانی که در درون جدول کلیک میکنیم، مشاهده میکنید که منو مربوط به Workbook Queries در کنار داده های شما باز میشود. بر روی دکمه refresh درسمت راست منو کلیک کنید تا داده های شما به روز رسانی شود.
با این کار، power query به عقب باز میگردد و گام های انجام شده را به صورت اتوماتیک دوباره انجام میدهد. از آنجایی که ما ردیف جدیدی به داده های خود اضافه کرده ایم، power query این ردیف جدید را به query اضافه میکند و عمل unpivot کردن را انجام میدهد.
این ویژگی از power query را بسیار دوست دارم. ما یک ردیف داده جدید به منبع اصلی اضافه کردیم ولی به زدن یک دکمه ردیف جدید نیز مورد نظر قرار میگیرد.
یادگیری بیشتر در مورد استفاده از اکسل
در این مطلب، مشاهده کردید که تبدیل ستون به ردیف در اکسل بسیار کار آسانی است. power query ابزاری قدرتمند و انعطاف پذیر برای استخراج و سازماندهی مجدد داده های شماست. برای یادگیری بیشتر در مورد مایکروسافت اکسل میتوانید به مطالب زیر مراجعه فرمائید.
پاورکوئری در اکسل
[/vc_column_text][/vc_column][/vc_row]