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

برای این منظور مسیر زیر را طی کنید:

Data>get data>from file> from workbook

سپس فایل مورد نظر خود را انتخاب کنید: (نام فایل انتخابی ما «مبالغ پرداختی» است.)

نتیجه طی کردن این مراحل، به شکل زیر خواهد بود:

در این مرحله به جای انتخاب کوئری ها، روی پوشه خود قرار گرفته و راست کلیک کنید و گزینه transform data را انتخاب نمایید:

به این ترتیب، همانطور که در تصویر زیر میبینید؛ اطلاعات کامل این فایل نمایش داده میشود:

همانطور که در تصویر بالا مشاهده میکنید، نام شیت، دیتاها، آیتم ها، نوع و پنهان بودن یا نبودن آن را مشخص میکند.

اگر همه به صورت شیت باشد، میتوانید ستون نام و دیتا را انتخاب کنید و راست کلیک نمایید و گزینه remove other columns رو انتخاب کنید:

و سپس برای خارج کردن اطلاعات از حالت تیبل، بر روی علامتی که در تصویر زیر میبینید کلیک کنید:

و از پنجره ای که باز میشود؛ تیک گزینه Use original column name as prefix را بردارید و بر روی OK کلیک کنید:

نتیجه این مراحل به شکل زیر خواهد شد:

نام ستون های خود را تغییر میدهیم: (برای این کار کافیست بر روی نام هر ستون دوبار کلیک کنید و نام مورد نظر خود را تایپ نمایید.)

دلیل تغییر نام ستون ها: به تصویر بالا نگاه کنید؛ اگر سطر اول را برای header انتخاب میکردیم و تیر به عنوان header انتخاب میشد و بعد آن را تغییر نام میدادیم، اگر در فایل خود شیت تیر نداشته باشیم با خطا مواجه خواهیم شد.

حذف مبلغ پرداختی از قسمت دیتا

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

روش اول: کافیست مبلغ پراختی را فیلتر کنید. بر روی علامت فلش کنار مبلغ پرداختی کلیک کنید:

از پنجره باز شده، تیک گزینه مبلغ پراختی را بردارید و بر روی OK کلیک نمایید:

روش دوم:

بر روی علامت فلش کنار مبلغ پرداختی کلیک نمایید:

از پنجره باز شده از قسمت گزینه Filter، گزینه does not equal…  را انتخاب نمایید:

این گزینه بیانگر این است که «آن هایی را نشان بده که برابر نیستند با..»

پنجره زیر باز خواهد شد، در کادری که در تصویر زیر مشخص شده عبارت «مبلغ پرداختی» را تایپ کنید و بر روی OK کلیک کنید:

نتیجه در تصویر زیر نمایش داده شده است:

به ترتیب تمام اعداد نمایش داده شده اند.

نمایش شیت های ترکیب شده به صورت پیوت تیبل

تا این مرحله ترکیب شیت های اکسل در پاورکوئری را آموختیم. در انتها، برای نمایش این اطلاعات به صورت پیوت تیبل مسیر زیر را طی کنید.

از منوی Home، از قسمت Close & Load گزینه Close & load to را انتخاب نمایید:

و از پنجره باز شده گزینه Pivot table را انتخاب کنید و بر روی OK کلیک کنید:

به این ترتیب Pivot table شما ساخته خواهد شد:

به این ترتیب میتوان نام افراد را در بخش Row قرار داد؛ و مبلغ پرداختی را در بخش Value قرار داد.  به این ترتیب مبلغ پرداختی آنها به ازای تمام ماه ها محاسبه میشود:

اضافه کردن یک شیت به شیت ها

در پیوت تیبلی که ایجاد شد مبلغ پرداختی را به ازای هر ماه محاسبه کنید. برای این کار کافیست «ماه» را در rows و مبلغ پرداختی را در بخش value قرار دهید.

حال به فایل خود یک شیت با نام آذر اضافه کنید و فایل جدید را با زدن کلیدهای Ctrl+s ذخیره کنید. (در اینجا ما به فایل خود دو شیت به نام های «آذر» و «شیت 10» را اضافه نموده ایم.)

سپس برای به روزرسانی پیوت تیبل و کوئری ها، از منوی  Data بر روی گزینه refresh all کلیک کنید:

به این ترتیب اطلاعات شیت های جدید که ایجاد کردیم به پیوت تیبل افزوده شدند:

حذف شیت از قسمت کوئری

برای حذف sheet10 کافیست sheet10 را از قسمت کوئری خود حذف کرد یا اسم هایی را که در نام آنها sheet وجود دارد حذف کرد:

برای این کار از بخش   Queries and conection بر روی فایل مبلغ پرداختی کلیک کنید:

نتیجه به شکل زیر خواهد شد:

از قسمت APPLIED STEPS مرحله قبل (Removed other Columns) را انتخاب کنید:

سپس بر روی گزینه Refresh Preview کلیک کنید:

نتیجه این مرحله به شکل زیر خواهد شد:

در ستون name، با انتخاب فیلتر، از قسمت text filter، گزینه does not contain را انتخاب نمایید:

و از پنجره باز شده بر روی insert کلیک نمایید:

و در پنجره باز شده بر روی کادر روبه روی does not contain کلمه Sheet را تایپ کنید. (توجه کنید که با S بزرگ نوشته شود.)

به این ترتیب هر شیتی که نام Sheet در آن باشد را حذف خواهد کرد. نتیجه را در شکل زیر ببینید:

به کوئری خود برمیگردیم؛ از قسمت Applied steps بر روی گزینه  Filtered rows کلیک کنید:

و از پنجره باز شده بر روی keep کلیک کنید تا ذخیره شود:

همانطور که در تصویر زیر مشاهده میکنید، شیت با نام Sheet10 از آن حذف شده است.

ممنون که این مطلب رو مطالعه کردید. امیدواریم این آموزش براتون مفید بوده باشه و دوباره شما رو در سایتمون ببینیم.

منابع پیشنهادی برای آشنایی با پاورکوئری

ضمناً برای دیدن آموزش های مربوط به پاورکوئری میتوانید به لینک های زیر مراجعه کنید.

پاور کوئری چیست؟ 20 قسمت آموزش رایگان پاور کوئری.

فیلم آموزشی نصب پاورکوئری در اکسل.

فیلم آموزشی ساخت کوئری از جداول اکسل.

فیلم آموزشی فراخوانی داده ها از سایر فایل های اکسل.

[/vc_column_text][/vc_column][/vc_row]