نحوه استفاده از Power Query در اکسل

اگرچه ابزارهای فناوری طیف گسترده ای از راه حل های مدیریت داده را ارائه می دهند، ادغام اطلاعات از منابع متعدد همچنان یک چالش است. درک اطلاعات برگه های متفاوت اغلب به تلاش دستی متکی است.

اینجاست که power query می تواند با بحث ، داده ها از مبداهای مختلف در یک نمای یکپارچه کمک کند.

به عنوان یک مشاور بازاریابی، من با چندین تیم در سراسر تجارت مشتری کار می کنم. به عنوان مثال، برای دریافت تصویر واقعی از آنچه در قیف درآمد یک شرکت SaaS در حال انجام است، معمولاً به داده‌هایی از بازاریابی، فروش، موفقیت مشتری و محصول نیاز دارم.

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

بنابراین، power query در طول سال‌ها برای من بسیار مفید بوده است و آنقدرها هم که فکر می‌کنید پیچیده نیست.

با power query می‌توانید مجموعه‌های داده را از چندین منبع وارد، پاکسازی، تبدیل و ادغام کنید. هنگامی که نحوه استفاده از آن را بدانید، جمع آوری و تفسیر داده ها از منابع مختلف بسیار آسان تر می شود.

پاور کوئری چیست؟

Power Query اساساً یک فناوری است که می توانید در اکسل برای اتصال مجموعه داده ها به یکدیگر در صفحه گسترده اکسل استفاده کنید. می‌توانید از power query برای استخراج داده‌ها از منابع مختلف، از جمله صفحات وب، پایگاه‌های داده، سایر صفحات گسترده و چندین نوع فایل استفاده کنید.

هنگامی که داده ها وارد می شوند، می توانید از ویرایشگر power query نیز برای تمیز ، و تبدیل داده ها استفاده کنید. در نهایت، می توانید داده های تبدیل شده را به صفحه گسترده موجود خود وارد کنید.

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

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

با هر ویژگی پیشرفته در اکسل، کمی منحنی یادگیری وجود دارد. اما در حالی که ممکن است برای آشنایی با پاور کوئری چند بار تلاش کنید، زمانی که بدانید همه چیز کجاست، رابط کاربری بسیار کاربرپسندی دارد.

نحوه استفاده از Power Query در اکسل

هنگام یادگیری نحوه استفاده از چیزی مانند power query، مفیدترین روش با یک مثال است. مراحل زیر را می توان برای هر نوع مجموعه داده اعمال کرد و عملکردهای برجسته شده را می توان با روش های دیگری برای استفاده از پرس و جوها در صورت نیاز جایگزین کرد.

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

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

مرحله 1. Excel را باز کنید و به power query دسترسی پیدا کنید.

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

در نوار اصلی، روی «داده» کلیک کنید. “دریافت داده (Power Query)” را به عنوان اولین مورد در نوار داده خواهید دید.

مرحله 2. داده های خود را وارد کنید.

برای دسترسی به گزینه های power query خود روی منوی کشویی کلیک کنید.

در این مرحله اول، می‌خواهیم «دریافت داده» را بزنیم، بنابراین می‌توانیم با استفاده از داده‌های صفحه‌گسترده فروش بریتانیا، پرس و جو را شروع کنیم.

با کلیک بر روی این گزینه پنجره ای باز می شود که می توانید منبع داده را انتخاب کنید. همانطور که خواهید دید، امکان استخراج داده ها از طیف گسترده ای از منابع، از جمله فایل های SharePoint به اشتراک گذاشته شده است.

در این مورد، ما “Excel workbook” را برای دسترسی به فایلی که به صورت محلی در رایانه من ذخیره شده است انتخاب می کنیم. پس از انجام این انتخاب، شما به سادگی فایل های خود را برای صفحه گسترده مناسب مرور کرده و روی «Get Data» کلیک کنید.

سپس در صفحه زیر روی «Next» کلیک کنید. از اینجا می توانید برگه های خاصی را که می خواهید وارد کنید انتخاب کنید. می‌توانید پیش‌نمایش داده‌ها را قبل از زدن «بارگیری» برای نهایی ، واردات مشاهده کنید.

اگر در این مرحله “بارگذاری” را انتخاب کنید، power query داده ها را به یک برگه جدید در صفحه گسترده موجود شما وارد می کند.

از آنجا، می‌توانید «Data» و «Launch Power Query Editor» را برای تمیز ، انبوه یا به‌روزرسانی داده‌های وارد شده انتخاب کنید.

مرحله 3. پاک ، داده های وارد شده.

اگر ترجیح می دهید داده های خود را قبل از وارد ، پاک کنید، می توانید این کار را در مرحله واردات انجام دهید.

به جای انتخاب «بارگیری» در طول فرآیند واردات، می‌توانید «Transform data» را انتخاب کنید، که ویرایشگر Power Query را درست مانند مرحله قبل باز می‌کند.

به عنوان مثال، برگه فروش بریتانیا حاوی همان داده‌های برگه ایالات متحده است، اما به درستی قالب‌بندی نشده است. قالب ارز برای سلول ارز اعمال نمی شود.

بنابراین، در Power Query Editor، می‌توانم تب Transform را برای ویرایش داده‌ها قبل از وارد ، انتخاب کنم (اگر ابتدا داده‌ها را وارد کرده و سپس ویرایشگر Power Query را باز کنم، می‌توانم این کار را انجام دهم).

همانطور که می بینید، گزینه های زیادی در اینجا وجود دارد. در این مثال، من ستون ارز را انتخاب می کنم و “نوع داده” را انتخاب می کنم.

از آنجا می توانم گزینه Currency را انتخاب کرده و مقدار را به USD تغییر دهم.

مرحله 4. داده های تبدیل شده خود را بارگیری کنید.

مرحله آخر اضافه ، این داده های پاک شده و تبدیل شده به صفحه گسترده موجود من است. در ویرایشگر Power Query، به برگه “Home” برگردید و روی “Close & load” کلیک کنید.

اکسل اکنون این پرس و جو را در یک برگه جدید در صفحه گسترده شما بارگذاری می کند.

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

با این حال، موارد استفاده برای power query به دلیل تنوع گسترده داده‌هایی که می‌توانید وارد و تمیز کنید، و انواع تبدیل‌ها و دستکاری داده‌ها که می‌توانید انجام دهید، تقریباً بی‌پایان است.

بنابراین، بیایید به چند نمونه از کارهایی که می‌توانید از power query برای انجام آن استفاده کنید نگاهی بیندازیم.

مثال های پاور کوئری

ادغام داده ها از صفحات گسترده چندگانه

چه در حال کار با چندین کتاب کار خود هستید یا فایل‌هایی که از افراد دیگر ارسال می‌شوند، نیاز به ادغام داده‌ها در یک صفحه یک مشکل نسبتاً رایج است.

Power query این کار را بسیار ساده می کند.

ابتدا مطمئن شوید که تمام کتاب‌هایی که می‌خواهید ادغام کنید در یک پوشه ذخیره شده باشند. در صورت امکان، مطمئن شوید که همه آنها از نظر داشتن عناوین یکسان ستون مطابقت دارند.

اکسل را در یک Workbook خالی باز کنید و با گزینه “Get Data” به Power Query دسترسی پیدا کنید.

منبع داده خود را به عنوان “کتاب کار اکسل” انتخاب کنید و اولین فایل خود را انتخاب کنید. اگر فرمت برگه‌های شما یکسان است، نیازی به انجام کار دیگری در اینجا نخواهید داشت. به سادگی “بارگیری” را فشار دهید.

سپس، دیگر صفحات گسترده خود را وارد می‌کنید. اما، به جای انجام این مرحله اول دقیقاً به همان روش، می‌خواهید برگه‌های دیگر را به این کوئری موجود که به تازگی ایجاد کرده‌اید، اضافه کنید.

در برگه، دوباره power query را باز کنید و هنگام وارد ، یک فایل، Workbook دوم را از لیست خود انتخاب کنید. به جای زدن «بارگذاری»، گزینه «تبدیل داده» را انتخاب کنید.

اکنون، باید هر دو عبارت را در ویرایشگر Power Query در پانل سمت چپ مشاهده کنید.

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

از منوی کشویی، «Append quries as new» را انتخاب کنید. جداول را همانطور که می خواهید یکی به انتهای دیگری اضافه کنید انتخاب کنید و “Ok” را بزنید.

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

پاک ، داده ها قبل از وارد ،

گاهی اوقات، ممکن است با یک صفحه گسترده کثیف سر و کار داشته باشید. قالب بندی و استایل در همه جا وجود دارد، و شما می خواهید قبل از وارد ، مطمئن شوید که تا حد ممکن تمیز و مرتب است.

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

یک مثال ساده صفحه‌گسترده‌ای است که در آن برخی از متن‌ها با حروف بزرگ نوشته می‌شوند، و برخی دیگر نه. بنابراین، هدف این است که همه چیز را قبل از اضافه ، آن به برگه موجود خود به حالت جمله تبدیل کنید.

فرض کنید تیم فروش در فرانسه گزارش فروش خود را برای ما ارسال کرده است. عالی به نظر می رسد، اما برخی از متن ها در ستون شناسه کشور با حروف بزرگ نوشته شده اند و برخی دیگر اینطور نیستند. به‌علاوه، فضاهای تصادفی در نام کشور ظاهر شده‌اند که باید اصلاح شوند، در غیر این صورت ما برای جمع‌آوری صحیح داده‌ها در جدول‌های محوری و سایر گزارش‌ها تلاش خواهیم کرد.

در برگه خود، power query را باز کنید و فایل را با داده های آشفته وارد کنید. به جای وارد ، فوراً روی «Transform data» کلیک کنید.

ابتدا ستونی را با حروف بزرگ و فاصله های اضافی پیدا کنید. روی ستون کلیک راست کرده و روی «Transform column»، سپس «Text transforms» و در نهایت «Trim» کلیک کنید. این کار هر فاصله اضافی را که می تواند در دستکاری داده ها اختلال ایجاد کند، حذف می کند.

دوباره روی ستون کلیک راست کنید و این بار از بین گزینه ها روی “Capitalize Every Word” کلیک کنید. ممکن است لازم باشد ابتدا «حروف کوچک» را انتخاب کنید تا کلمات تمام حروف را تصحیح کنید و سپس از گزینه «حرف بزرگ ، هر کلمه» استفاده کنید.

اکنون می‌توانید روی «بارگذاری» کلیک کنید تا داده‌های پاک‌شده‌تان به صفحه اکسل وارد شوند.

برداشتن/محور ، داده ها برای بهبود قابلیت های تحلیل

یکی دیگر از چالش‌های رایج در اکسل دریافت داده‌ها به گونه‌ای است که دستکاری و تجزیه و تحلیل را دشوار می‌کند، به‌ویژه زمانی که می‌خواهید ستون‌ها و ردیف‌ها را تغییر دهید – یا داده‌ها را “محور” کنید.

در این مثال، داده‌های نظرسنجی داریم که در آن ستون‌ها توسط پاسخ‌دهندگان تجزیه می‌شوند. در حالت ایده آل، ما می خواهیم آن را با ستون ها به عنوان سوالات نظرسنجی تجزیه و تحلیل کنیم.

ابتدا یک جدول اکسل خالی باز کنید و برگه خود را با استفاده از power query وارد کنید.

مانند مثال قبلی، به جای کلیک ، روی «بارگیری»، ابتدا باید روی «تبدیل داده» کلیک کنیم.

اکنون که ویرایشگر Power Query باز است، به تب Transform در نوار بروید. تمام ستون‌های سؤال نظرسنجی را با هم انتخاب کنید و روی «Unpivot columns» کلیک کنید و سپس «Unpivot only selected columns» را انتخاب کنید.

Power query ستون های انتخاب شده را می گیرد و داده ها را با ویژگی ها و مقادیر سوالات تجزیه می کند. به برگه «صفحه اصلی» بروید و «بستن و بارگیری» را بزنید تا از داده‌های بدون محور در صفحه‌گسترده خود استفاده کنید.

با Power Query تجربه کنید

در طرح بزرگ ابزارها، اکسل در دسته بندی، تمیز ، و تغییر نوع داده هایی که در عملیات روزمره تجاری در چندین بخش و تیم مورد استفاده قرار می گیرد، یکی از قدرتمندترین ها است.

اگرچه ممکن است کمی طول بکشد تا با تمام ویژگی‌ها و عملکردهای چیزی مانند power query آشنا شوید، اما ارزش تلاش را دارد.

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