فرض کنید در یک جدول که دارای ردیف، شماره سفارش، نام کالا، تعداد، عرض و طول کالا است. قصد دارید شماره سفارش را در یک سلول انتخاب کنیم و سپس سایر جزئیات آن نیز نمایش داده شود. در مسائل ریاضی به مقدارهایی که تغییر می کنند متغییر گفته می شود. در پاور کوئری به این مقدارهای متغییر، پارامتر (parameter) نامیده می شود. پارامترها می توانن از مقدار یک سلول ساخته شوند یا در پاور کوئری یا پارامتر ساخت. در اینجا نشان خواهیم داد چگونه از مقدار یک سلول متغییر در پاور کوئری بسازید.

تعریف پارامتر در پاور کوئری

اگر با پاور کوئری آشنا نیستید 23 قسمت فیلم رایگان آموزش پاور کوئری را ببینید.

ابتدا جدول مورد نظر خود را تشکیل داده و به صورت table در آورید.

برای ساخت یک کوئری از جدول بالا در اکسل 2016 و یا 2019 کافی است از تب Data گزینه From Table/Range را انتخاب کنید.

با این کار کوئری جدول مورد نظر ساخته می‌شود.

فیلتر کردن جدول براساس مقدار انتخاب شده

برای مثال A91 را در ستون شماره سفارش فیلتر میکینم. برای این کار زبانه کنار شماره سفارش را زده گزینه A91 را انتخاب میکنیم.

در این مرحله ستون شماره سفارش را حذف میکنیم. برای این کار رو ستون راست کلیک کرده و گزینه Remove را انتخاب کرده و یا از صفحه کلید Delete را انتخاب میکنیم.

سپس با استفاده از گزینه close & load to این جدول ساخته شده را به صورت یک table در شیت های موجود و در سلول مورد نظر نمایش دهید. برای انتخاب سلول تیک گزینه Existing worksheet در Import Data را زده و سلول خود را انتخاب و OK کنید.

مطابق شکل زیر جدول کوئری ایجاد می‌شود که کالاهای مربوط به سفارش A91 را نمایش می‌دهد.

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

برای اینکه با استفاده از مقدار یک سلول کوئری خود را فیلتر کنیم، نام سلول A92 را تغیر داده و پس از کلیک بر روی سلول در قسمت namebox مقدار SaleNumber که در شکل مشخص شده مینویسید.

سپس سلول A92 را انتخاب کرده و از از تب Data گزینه From Table/Range را انتخاب کنید. پاور کوئری این بازه را به یک جدول تبدیل نمیکند زیرا برای این سلول یک نام انتخاب شده است. پاور کوئری از این نام برای ساخت کوئری خود استفاده می کند.

اگر در کوئری عکس بالا دقت کنید، مقدار سلول به صورت عنوان ستون در آمده است. برای اینکه بتوانیم از این مقدار به عنوان پارامتر استفاده کنیم بایست مراحلی که پاور کوئری به صورت اتوماتیک در ساخت کوئری اضافه کرده است را حذف کنیم. در اینجا دو مرحله اضافه داریم. بعد از SOURCE که مقدار سلول را در کوئری قرار داده است، مراحل PROMOTED HEADER که مقدار سلول را به صورت عنوان تبدیل کرده است و CHANGE TYPE که نوع دیتای ستون را تعیین کرده است به صورت اتوماتیک توسط پاور کوئری ساخته شده است. با زدن علامت x که در کنار هر مرحله قرار دارد این مراحل را حذف می کنیم.

چون میخواهید فقط مقدار سلول نمایش داده شود و به صورت جدول نباشد (در اینجا مقدار A92) روی آن راست کلیک کرده و Drill Down را انتخاب کنید تا فقط مقدار A92 را به نمایش بگذارد.

حال اگر در قسمت چپ Queries را باز کنید می‌بینید که SaleNumber مقدار text است و A92 در آن قرار دارد.

پاور کوئری به صورت اتوماتیک از نام جدول و یا بازه برای نامگذاری کوئری های خود استفاده می کند و در اینجا از نام SALENUMBER به عنوان نام کوئری استفاده کرده است.

در پنجره کوئری ها جدول قبلی خود (در اینجا Table1) را انتخاب کنید. سپس مانند شکل بر روی گزینه  Filtered Rows (مرحله ای که جدول را فیلتر کردیم) بزنید و در نوار فرمول به جای “A91” اسم کوئری SaleNumber را نوشته و enter بزنید.

همانطور که میبینید محصولات A92 فیلتر شدند. دقت داشته باشید که SaleNumeber نیاز به لود شدن ندارد درنتیجه گزینه close & load to را زده وonly Create Connection را انتخاب کنید تا فقط یک کانکشن برایتان بسازد.

حال اگر در اکسل در سلول SaleNumber مقدار A91 را وارد کنید و Enter بزنید و سپس از تب Data گزینه Refresh All را بزنید، تغییرات بر روی کوئری ها ایجاد شده و جدول بر اساس مقدار جدید فیلتر می شود. به همین روش شماره سفارش های دیگر را نیز انتخاب و رفرش کنید محصولات آنها را نمایش میدهد.

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