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

مثال کاربرد پاورکوئری در اکسل

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

 

همان‌طور که در شکل فوق مشاهده می‌شود، در این فایل، اطلاعات هرروز به‌صورت مجزا از اطلاعات روز بعد نگهداری می‌شود و بین فاکتورهای امروز با روز بعد، یک ردیف خالی قرار داده می‌شود. از سوی دیگر، در حالتی که در یک روز بیش از یک‌بار فروش داشته باشیم، تاریخ مربوط به آن روز، تنها در اولین رکورد ثبت می‌شود و در کنار بعضی از کدهای خریدار از علامت * (ستاره) استفاده‌شده است و همچنین در کنار اعداد ستون D، بعضاً از واژه “عدد” استفاده‌شده است که منجر به تبدیل این مقادیر به حالت متن  می‌شود.

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

 

۱-   تبدیل داده ها به جدول

ابتدا محدوده داده‌ها (از سلول A1 تا سلول D32) را انتخاب نموده و از تب Insert بر روی دستور Table کلیک کرده و پیغام نمایش داده‌شده را تائید نموده تا داده‌ها به‌صورت جدول نمایش داده شوند.

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

۲-   فراخوانی داده ها در پاورکوئری

درحالی‌که یکی از سلول‌های جدول ایجادشده، انتخاب‌شده است از تب Data، قسمت Get & Transform Data دستور From Table/Range را کرده تا پنجره ویرایشگر پاورکوئری  مشابه شکل ‏ذیل ظاهر شود و داده‌ها درون آن فراخوانی شوند.

 

در این پنجره می‌توان از دستورات موجود در پاورکوئری جهت اصلاح ساختار داده‌ها استفاده نمود.

۳-   فیلتر کردن سلولهای خالی در پاورکوئری

به‌منظور حذف ردیف‌های خالی بین داده‌های هر روز، کافی است بر روی علامت فیلتر (فلش رو به پایین) در کنار ستون “کد خریدار” کلیک کرده و از بین لیست کدهای خریدار، مانند شکل ، گزینه null را غیرفعال نموده.

 

۴-   دستور جایگزین کردن متن در پاورکوئری

به‌منظور حذف کاراکتر ستاره در ستون “کد خریدار”، با راست کلیک کردن بر روی آن ستون و انتخاب دستور Replace Value، پنجره جایگزین کردن مقادیر نمایان می‌شود، سپس مطابق با شکل ذیل در قسمت Value to Find این پنجره، عبارت * (ستاره) را ثبت نموده و قسمت Replace With را خالی گذاشته و تائید نموده

 

۵-   جایگزین کردن عبارت عددی در پاور کوئری

به‌طور مشابه با گام قبل، به‌منظور حذف عبارت عدد، در کنار مقادیر ثبت‌شده در ستون “تعداد خریداری شده”، بر روی این ستون راست کلیک می‌کنیم، اما مطابق با شکل ذیل برخلاف گام ۴، دستور Replace Value جهت انتخاب در بین دستورات پیشنهادی این ستون نمی‌باشد

 

علت نبودن دستور Replace Value در فهرست دستورات مربوط به ستون “تعداد خریداری شده” فرمت آن ستون می‌باشد، به‌عبارت‌دیگر دستور Replace، تنها برای ستون‌هایی با فرمت Text قابل‌استفاده می‌باشد درحالی‌که فرمت ستون “تعداد خریداری شده” بر روی Any می‌باشد. (با انتخاب این ستون، مشاهده می‌شود در قسمت Transform از تب Home، حالت Data Type بر روی Any قرار داده دارد.) ازاین‌رو ابتدا باید بر روی این ستون راست کلیک نموده و از قسمت Change Type، حالت Text را انتخاب نموده تا فرمت این ستون به Text تغییر کند.

سپس مطابق با گام ۴، بر روی ستون “تعداد خریداری شده” راست کلیک نموده و دستور Replace Value را انتخاب نموده و در پنجره بازشده در قسمت Value to Find عبارت “عدد” را ثبت نموده و قسمت Replace With را خالی گذاشته و تائید نموده تا عبارت “عدد” از تمام سلول‌های این ستون حذف شود.

درنهایت به‌منظور تبدیل فرمت مقادیر این ستون به عدد، کافی است بر روی این ستون راست کلیک نموده و از قسمت Change Type، حالت Number را برگزیده.

 

 

۶-   استفاده از دستور Fill در پاورکوئری

در این گام به‌منظور تکرار تاریخ برای رکوردهای هر روز (جایگزین کردن مقدار Null در هر سلول با تاریخ سلول قبلی) کافی است دبر روی ستون تاریخ راست کلیک نموده و از لیست دستورات، دستور Fill حالت Down را انتخاب نموده تا مقدار هر سلول Null در این ستون، با مقدار سلول قبلی جایگزین شود. مطابق با شکل ذیل.

 

 

۷-   نمایش نتیجه پاورکوئری در اکسل

درنهایت در این مرحله کافی است از تب Home در پنجره ویرایشگر پاورکوئری، دستور Close & Apply را برگزیده تا نتیجه گام‌های فوق به‌صورت جدول داده‌ها درون یک شیت جدید از فایل اکسل به‌صورت شکل ذیل بارگذاری  شود.

 

 

 

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