راهنمای قطعی ابزار Power Query در اکسل

استفاده از اکسل برای انجام وظایف، مهارتی است که بسیاری از متخصصان داده برای کار و زندگی شخصی خود از آن استفاده می کنند. یکی از عملکردهایی که اکسل برای کمک به وارد کردن داده‌ها به صورت مؤثرتر ارائه می‌کند، پرس و جوی انرژی است. دانستن اینکه پاور کوئری چیست و چگونه از آن استفاده کنید می تواند مهارت های کامپیوتری شما را افزایش دهد، فرآیندهای کاری شما را سرعت بخشد و به مدیریت بهتر داده ها کمک کند.

در این مقاله از ایوسی، ما در مورد چیستی پاور کوئری، توابعی که در اختیار اکسل قرار می‌دهد، فازهایی که استفاده می‌کند و نمونه‌ای از نحوه عملکرد آن صحبت می‌کنیم.

مرتبط: مهارت های پیشرفته اکسل: تعریف و مثال

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

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

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

توابع یک power query در اکسل

ابزار power query می تواند به هر تابعی که اکسل استفاده می کند دسترسی داشته باشد. یکی از متداول ترین توابعی که استفاده می کند VLOOKUP است که می توانید از آن برای جستجوی یک نقطه داده خاص در محدوده مشخصی از سلول ها مانند سلول های A1 تا Z26 استفاده کنید. این روش ارجاع به سلول در اکسل معمولاً از علامت “نام سلول: نام سلول” استفاده می کند که به برنامه می گوید کدام سلول ها را در تابع قرار دهد. توابع رایج دیگر یک پرس و جو، توابع ریاضی SUM، AVERAGE، SubTRACT و PRODUCT هستند.

مطالب مرتبط: نحوه استفاده از VLOOKUP در اکسل

مراحل یک کوئری قدرت در اکسل

هنگامی که از ابزار power query در اکسل استفاده می کنید، چهار مرحله را از ورودی کاربر اجرا می کند. شرح کارهایی که هر مرحله از یک Power Query انجام می دهد و آنچه شما وارد می کنید عبارتند از:

۱. اتصال

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

1. تب “داده” را از نوار بالای برنامه اکسل انتخاب کنید.

2. گزینه کشویی «دریافت داده» را از برگه «دریافت و تبدیل داده» انتخاب کنید.

3. ماوس را روی گزینه «از فایل» نگه دارید تا منوی دیگری باز شود.

4. مکانی را که می‌خواهید داده‌ها را از آن دریافت کنید، انتخاب کنید، مانند «From Folder»، «From Workbook» یا «From Text/CSV».

5. برای جستجوی فایل مورد نظر روی “مرور” کلیک کنید.

6. “OK” را انتخاب کنید.

پس از انتخاب “OK”، یک کادر محاوره ای ظاهر می شود که در آن می توانید نحوه تعامل با داده ها را انتخاب کنید. سه گزینه ای که برای انتخاب شما در اختیار دارد عبارتند از:

• “Combine & Transform Data”: این گزینه به شما امکان می دهد داده ها را به یک نسخه تمیزتر و سازماندهی شده تبدیل کنید و اگر نیاز دارید که داده ها با منبع اصلی متفاوت به نظر برسند، می تواند مفید باشد.

• “Combine & Load”: این گزینه داده ها را مستقیماً از اولین فایل به اکسل وارد می کند و اگر قبلاً داده ها را همانطور که می خواهید سازماندهی کرده باشید می تواند مفید باشد.

• “Combine & Load To…”: این گزینه به شما امکان می دهد داده ها را از منبع اصلی خود صادر کرده و از کتاب کار اکسل به مکان دیگری وارد کنید.

۲. تبدیل

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

1. “Transform Sample File” را انتخاب کنید.

2. تنظیمات خود را روی داده ها انجام دهید.

3. روی “اعمال” کلیک کنید تا داده ها توسط پرس و جو تغییر کنند.

مطالب مرتبط: ۲۵ بهترین نکته اکسل برای بهینه سازی استفاده

۳. ترکیب کنید

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

۴. بارگذاری کنید

پس از نوشتن پرس و جو و وارد کردن داده های خود، می توانید پرس و جو و کتاب کار را ذخیره کنید. این به شما امکان می دهد بعداً به تغییراتی که در داده ها و Power Query ایجاد کرده اید دسترسی داشته باشید. این مرحله همچنین مرحله ای است که می توانید تصمیم بگیرید که هنگام وارد کردن داده ها کجا بارگیری شوند. مراحل زیر ممکن است به شما کمک کند داده ها را در یک نمودار خاص بارگیری کنید:

1. “Close & Load To…” را از سمت چپ بالای نوار اکسل انتخاب کنید.

2. مکانی را برای انتقال داده های جدید انتخاب کنید.

3. روی “OK” کلیک کنید تا اکسل داده ها را به آن مکان وارد کند.

مثالی از استفاده از power query در اکسل

در زیر نمونه ای از استفاده از power query در اکسل برای اتصال، تبدیل، ترکیب و بارگذاری داده های مربوط به شرکت های تابعه در سال های ۲۰۱۸، ۲۰۱۹ و ۲۰۲۰ است که در فایل های مختلف ثبت شده است:

۱. به فایل های داده متصل شوید

ابتدا با استفاده از میانبر روبان اکسل برای وارد کردن داده ها از تب داده، به فایل های داده متصل شوید. پس از باز کردن کادر محاوره ای، می توانید فایل ها را انتخاب کنید. به عنوان مثال، اگر ۲۰۱۸ را به عنوان یک فایل CSV ذخیره کرده اید، و فایل های ۲۰۱۹ و ۲۰۲۰ را به عنوان کتاب کار اکسل ذخیره کرده اید، می توانید همه فایل ها را انتخاب کنید. مراحل زیر نمونه ای از اتصال به فایل ها است:

1. به تب “داده” در نوار اکسل بروید.

2. “دریافت داده” را انتخاب کنید.

3. “از پوشه” را انتخاب کنید.

4. به پوشه ای که فایل های داده را در آن ذخیره کرده اید بروید.

5. برای اتصال به داده ها “OK” را انتخاب کنید.

۲. داده ها را تغییر دهید

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

۳. داده ها را با هم ترکیب کنید

هنگامی که تبدیل به داده ها را انجام دادید، می توانید هر یک از فایل های داده را در کتاب کاری که استفاده می کنید ترکیب کنید. در نسخه های جدیدتر اکسل، زمانی که به فایل های داده متصل می شوید، این مرحله به طور خودکار انجام می شود. این مفید است زیرا مجبور نیستید برای بار دوم فایل ها را انتخاب کنید. اگر می‌خواهید فایل چهارمی مانند داده‌های سال ۲۰۱۷ را وارد کنید، می‌توانید آن فایل را در این مرحله انتخاب کنید و اکسل آن را در جدول ترکیبی گنجانده است.

۴. داده ها را بارگذاری کنید

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

1. “Close & Load To…” را از سمت چپ بالای نوار اکسل انتخاب کنید

2. “Pivot Table” را انتخاب کنید

3. روی “OK” کلیک کنید

هنگامی که روی «OK» کلیک می‌کنید، اکسل یک جدول محوری را با داده‌هایی که در مراحل اتصال و ترکیب قرار داده‌اید پر می‌کند و یک جدول یکپارچه ایجاد می‌کند که می‌توانید به آن دسترسی داشته باشید یا داده‌های جدیدی را به آن اضافه کنید، مانند داده‌های ۲۰۲۱ پس از دریافت آن.

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