آموزش Pivot Table در اکسل همراه با 1 نکته طلایی
Pivot Table در اکسل
در این آموزش می خواهیم با توجه به جدولی که در زیر مشاهده می کنید گزارشی مبنی بر آنکه در هر ماه و در هر منطقه ما از چه کارشناس هایی فروشی داریم .
راه حلی که به ذهن خیلی ها میرسد استفاده ازPivot Table می باشد و ما نیز می خواهیم از روش پیوت تیبل استفاده کنیم اما یک محدودیت داریم که این محدودیت و راه حل آن را در این آموزش به شما خواهیم گفت.
قدم اول :ساخت پیوت تیبل (pivot table)
از تبInsert گزینه‌یPivot Table ، را می زنیم .
در پنجره باز شده همانطور که در تصویر زیر به آن اشاره شده است سوالی مبنی بر اینکه نتیجه کار در کجا نمایش داده شود وجود دارد که ما گزینه یExisting Worksheet یعنی در همین شیت میخواهیم بگذاریم را انتخاب کرده وOk می کنیم.
با انتخاب گزینه Ok در سمت راست پنجره ای که محیط Pivot Table می باشد نمایش داده می شود.
حال گزینه منطقه را در قسمتRows
ماه را در قسمت Columns
و کارشناس را در قسمت Value قرار می دهیم
و گزینه Ok را میزنیم و نتیجه همانطور که در تصویر مشاهده می کنید به دست می آید اما مشکلی که با آن مواجه هستیم این است که وقتی کارشناس را در قسمت Value قرار می دهیم تعداد کارشناسها را به ما نشان می دهد در صورتی که ما نام کارشناسها را در کنار هم میخواهیم.
راه حلی که ممکن است خیلی‌ها از آن استفاده کنند این است که همه ی فیلدها را در قسمت Rows قرار دهند که در این صورت نیز همانطور که در تصویر مشاهده می کنید جواب خواهیم گرفت.
این راه حل می تواند یک حالت از جواب باشد اما ما می خواهیم نام کارشناسها را کنار هم در یک سلول داشته باشیم.
پس این راه حل برای ما قابل استفاده نیست. حال با استفاده از یک روش دیگر این محدودیت را برطرف می کنیم تا بتوانیم داده های متنی را کنار هم بگذاریم.
نکته Pivot Table
ابتدا باید جدولمان را تبدیل به Table کنیم.
از تبHome گزینه‌ی Format as Table یکی از فرمت‌ها را انتخاب می کنیم یا کلیدCtrl + T را میزنیم و در پنجره باز شده Ok را می زنیم.
نام این جدول را میتوانیم تغییر داده و یا با همین نام پیش فرض ادامه می دهیم.
حال در تب Insert گزینه‌ی Pivot Table را می زنیم و مشخص میکنیم که نتیجه‌ی کار در کجا قرار بگیرد، این بار حتماً همانطور که در تصویر مشخص شده است دقت کنید تا تیک Add this data to Data Model را فعال کنید
حال منطقه را در قسمت سطر، ماه را در قسمت ستون واگر کارشناس را در قسمت Values قرار دهیم همچنان آن محدودیت را خواهیم داشت!!!
اما روش برطرف شدن محدودیت به این صورت است که بر روی نام جدول کلیک راست میکنیم یک Measure یا یک واحد دیگر اینجا اضافه میکنیم.
در پنجره باز شده در قسمت Measure Name نام این واحد را فرضاً SalesPerson یا کارشناس فروش میگذاریم.(شماره 1 در تصویر)
در قسمت Formula باید از تابعی به نام تابع CONCATENATEX استفاده کنیم.
یکی از توابع اکسلی CONCATENATE می باشد که احتمالا شما در اکسل با این تابع آشنایی دارید اما اینجا توابع مربوط به DAX هست و با اکسل متفاوت می باشد.
CONCATENATEX، پرانتز باز نام جدول را تایپ میکنیم کهTable1 بود کاما، قسمت بعدی باید ستون مورد نظر خود را انتخاب کنیم.
اول نام جدول را مینویسیم Table1 و به دنبال ستون مورد نظر که نام کارشناس هست در Table1 ، دابل کلیک میکنیم در قسمت بعدی باید جداکننده‌ی خود را مشخص کنیم و جداکننده را حتما داخل دابل کوتیشن بگذاریم، به اینصورت که دابل کوتیشن، اسپیس، کاما، اسپیس، دابل کوتیشن و پرانتز را می بندیم .( شماره 2 در تصویر )
حال گزینه‌ی Check DAX Formula را میتوانیم بزنیم تا ببینیم که فرمول را درست وارد کردیم یا خیر که در اینجا همانطور که در قسمت شماره 3 تصویر مشاهده می کنید تأیید داده و مشخص است که فرمول خود را درست وارد کردیم و Ok را میزنیم.
با انتخاب گزینه Ok یک فیلد بهPivot Table ما به نام SalesPerson اضافه میشود که این فیلد را خیلی راحت میتوانیم به قسمت Values اضافه کنیم
و همان طور که در تصویر زیرمیبینید، ما در اینجا نام کارشناس فروش ها را کنار هم در یک سلول داریم.به طور مثال برای منطقه جنوب، در خرداد ماه فرضاً 3 تا کارشناس داشتیم و در اردیبهشت ماه هیچ کارشناسی نداشتیم.
در انتها ستون جمع چون به کار نمی آید به راحتی می توانیم بر روی Grand Total کلیک راست کنیم و Remove Grand Total را میزنیم.
جهت مشاهده آموزش ویدئویی پیوت تیبل در اکسل بر روی لینک زیر کیلیک کرده و آن را داخل پیج ایسنتاگرام مطالب کمیاب در اکسل و حسابداری ( softwaretrain ) مشاهده فرمایید .
دیدگاهتان را بنویسید