نمایش متن در فیلد Value پیوت تیبل

نمایش متن در فیلد Value پیوت تیبل بجای محاسبات مثل جمع، شمارش و …

گاهی اوقات پیش میاد که ما نیاز داشته باشیم در پیوت تیبل و در قسمت Value داده ای رو قرار بدیم که متنش نمایش داده بشه نه محاسبات مربوط به اون داده در واقع قصد داریم نحوه نمایش متن در فیلد Value پیوت تیبل رو در این آموزش یاد بگیریم. در واقع نمیخوایم مثلا تعداد اون رکورد رو بشماره یا … میخوایم خود داده در قسمت محاسبات نمایش داده بشه.

مثلا دیتابیسی داریم که در اون حضور افراد در شعب مختلف، در سه شیفت تعیین شده (شکل ۱). حالا میخوایم همین اطلاعات رو در قالب یک ماتریس نمایش بدیم.

شکل ۱ – دیتابیس داده ها

حالا میخوایم این داده ها رو در قالب یک ماتریس نشون بدیم که بتونه افراد رو در شیفت های مختلف نشون بده. شکل ۲، پیوت تیبل ساده رو نشون میده که فقط میتونه ماتریس رو تشکیل بده و نهایتا محاسباتی رو روی تعداد داده های موجود در هر فیلد انجام بده. در حالیکه هدف ما این هست که اسامی افراد داخل ماتریس نمایش داده بشه، نه تعداد اونها.

شکل ۲- ماتریس مورد نظر برای نمایش داده ها

ما برای این مسئله چند روش حل ارائه میکنیم که در ادامه به اونها خواهیم پرداخت:

روش اول: استفاده از Pivottable و فرمول نویسی DAX:

برای استفاده از پیوت تیبل برای حل این مسئله طبق مراحل زیر عمل میکنیم:

1. داده ها رو انتخاب کرده و از تب Insert رو گزینه Pivottable کلیک میکنیم.
2. از پنجره نمایش داده شده تیک گزینه Add this data to the data model و بعد Ok رو میزنیم.

شکل ۳- اضافه کردن داده ها به دیتا مدل

3. حالا ماتریس مورد نظر رو ایجاد میکنیم به اینصورت که فیلد شعبه در Column و فیلد شیفت رو در Row قرار میدیم.

شکل ۴ – ایجاد ماتریس مورد نظر برای تهیه گزارش نهایی

4. در این مرحله باید کاری کنیم که نام هر کارمند در قسمت Value نمایش داده بشه. اما اگر کارمند رو به Value اضافه کنیم، نتیجه چیزی جز تعداد کارمندان نیست. در واقع Count فیلد کارمند محاسبه میشه. در حالیکه ما میخوایم اسم کارمندها نمایش داده بشه نه تعداد اونها. پس باید برای این کار از فرمول نویسی DAX استفاده کنیم و Measure مورد نظرمون رو به گزارش اضافه کنیم. برای این کار روی Table6 کلیک راست کرده و Add measure رو میزنیم.

شکل ۵ – مسیر اضافه کردن Measure به پیوت تیبل

در پنجره نمایش داده شده از فرمول Concatenate استفاده میکنیم و Measure مورد نظر رو مینویسیم:

شکل ۶- نوشتن فرمول DAX در Measure مورد نظر

5. بعد از OK کردن یک فیلد به لیست فیلدهای پیوت تیبل اضافه میشه. حالا کافیه employeeName و به قسمت Value اضافه کنیم.

شکل ۷ – اضافه کردن Measure مورد نظر به Value

با این کار اسم کارمندها در ماتریس مورد نظر نمایش داده میشه (شکل ۸). در واقع ما با این فرمول، گفتیم که اسم کارمندها رو با , به هم بچسبون.

شکل ۸ – نمایش اسم کارمندان در ماتریس مورد نظر

توجه کنید اگر در یک شعبه و در یک شیفت، دو کارمند وجود داشته باشند (یعنی در شیفت C و شعبه ۹ دو کارمند ۲۱ و ۲۲ وجود داشته باشند)، نتیجه به صورت شکل ۹ نمایش داده خواهد شد.

شکل ۹- نمایش داده تکراری در ماتریس پیوت تیبل

استفاده از پاورکوئری جهت نمایش متن در فیلد Value پیوت تیبل:

اگر با پاورکوئری و نحوه استفاده (یا فعالسازی) از این ابزار آشنا نیستید حتما مقاله مربوط به پاورها رو مطالعه کنید.

برای استفاده از این روش، ابتدا باید داده ها رو به Query Editor اضافه کنیم. پس:

1. داده ها رو انتخاب کرده و از تب Data و قسمت Get & Transform data روی گزینهFrom Table/Range کلیک میکنیم  و وارد محیط Query Editor میشیم.
2. حالا باید داده ها رو بر اساس ماتریسی که میخوایم تغییر بدیم. در کوئری امکانی وجود داره به نام Pivot که میاد داده ها رو بصورت ماتریسی مرتب میکنه. پس اول فیلدی که میخوایم در ردیف قرار بگیره رو انتخاب میکنیم و از تب Transform روی گزینه column Pivot کلیک میکنیم.
3. از پنجره نمایش داده شده و در قسمت محاسبات یعنی Value Column، فیلد کارمند رو انتخاب میکنیم، و عملیات مورد نظر رو از قسمت Advanced Options گزینه Don’t aggregate رو انتخاب میکنیم که بجای محاسباتی مثل شمارش و … خود کلمه نمایش داده بشه.

شکل ۱۰- تنظیمات مربوط به pivot column در پاورکوئری

4. بعد از زدن OK داده ها بصورت شکل ۱۱ نمایش داده میشوند.

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

5. حالا کافیه نتیجه کار رو به اکسل منتقل کنیم. برای این کار از تب Home و قسمت Close گزینه Close & Load to رو میزنیم و از پنجره نمایش داده شده گزینه Table رو انتخاب میکنیم.

شکل ۱۲- انتخاب انتقال ماتریس ایجاد شده به اکسل

6. بعد از زدن OK، ماتریس ایجاد شده به یک شیت در فایل اکسل منتقل می شه.

شکل ۱۳- انتقال ماتریس ایجاد شده به شیت در اکسل

استفاده از فرمول نویسی برای نمایش متن در فیلد Value پیوت تیبل:

برای اینکه بتونیم ببینیم چه کسی در هر شعبه و شیفت قرار گرفته. اول باید جای اونها رو پیدا کنیم. یعنی ببینیم شیفت A و شعبه ۱ در چه ردیفی از دیتابیس قرار گرفته و اونو فراخوانی کنیم.

=IFERROR(INDEX(DataBase2[کارمند],MATCH(F$1&$E2,DataBase2[شعبه]&DataBase2[شیفت],۰)),””)

شکل ۱۴- ایجاد ماتریس با استفاده از فرمول نویسی

این فرمول چطور کار میکنه؟

این فرمول از دو بخش اصلی تشکیل شده. اول تابع Match که مکان متغیرها رو پیدا میکنه و به عنوان آرگومان row در تابع index قرار میگیره و نام کارمند مربوط به اون ردیف رو نمایش میده. پس:

تابع Match مکان متغیرها رو در دیتابیس پیدا میکنه. اما نکته مهمی که وجود داره، این هست که با توجه به اینکه دو متغیر داریم، منطق فرمول بصورت آرایه ای خواهد بود.

این فرمول میاد متغیرها رو به هم میچسبونه و مقدار به هم چسبیده را در تیبل مورد نظر جستجو میکنه و مکانشو برمیگردونه.

در نهایت هم با تابع Iferror متغیرهایی که داده ندارند و با خطا مواجه میشن رو مدیریت میکنیم.

دانلود فایل اکسل این آموزش

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

ثبت ایمیل جهت دانلود فایل

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...