سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

نمایش متن در فیلد Value پیوت تیبل
۵/۵ - (۱ امتیاز)

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

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

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

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

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

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

ماتریس مورد نظر برای نمایش داده ها

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

اضافه کردن Measure مورد نظر به Value

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

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

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

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

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

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

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

استفاده از پاورکوئری جهت نمایش متن در فیلد 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  در پاورکوئری

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

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

ایجاد ماتریس یا پیوت کردن داده ها در پاور کوئری

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

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

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

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

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

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

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

نکته:
توجه داشته باشید در این روش داده تکرای نداریم. در واقع برای نمایش داده تکراری باید با زبان M کوئری ایجاد شده رو ویرایش کنیم.

 

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

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

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

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

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

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

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

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

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

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

مدیرت خطا با IFerror

نکته:
برای ثبت فرمول آرایه ای باید از کلید ترکیبی Ctrl+shift+Enter رو همزمان فشار بدیم. جهت مطالعه بیشتر راجع به فرمول نویسی آرایه ای مقاله مربوطه رو مطالعه کنید.

 

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

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

آواتار
127

فارغ التحصیل لیسانس مهندسی صنایع، ارشد مدیریت صنعتی از دانشگاه تربیت مدرس و عاشق اکسل هستم. از سال 1388 که ترم 2 لیسانس بودم، به توصیه استاد مشاورم شروع به خوندن اکسل بصورت حرفه ای کردم و همچنان در حال مطالعه و یادگیری و البته آموزش به بقیه هستم.

دیدگاه کاربران
  • شاهین ۵ بهمن ۱۴۰۰ / ۱۰:۲۲ ق٫ظ

    ظاهرا تصویر ۵ فقط از ورژن ۲۰۱۶ به بالا امکان پذیر هست
    یک سوال اینکه
    در این روش مقادیر حرفی مساوی نیستند و فرق میکنند و اکسل هم با گذاشتن ویرگول همه مقادیر را در value می اورد
    چطور میشه فرمولی بدیم که فقط یک مورد رو به طور شانسی بیاره؟

  • شاهین ۵ بهمن ۱۴۰۰ / ۹:۵۳ ق٫ظ

    با سلام در تصویر ۵ گزینه add Measure وجود ندارد

  • خشایار ۲۲ مهر ۱۳۹۹ / ۶:۱۱ ب٫ظ

    با سلام…
    عکسی که زیر قسمت ۵ هست،
    اکسل من تب all و active و اون گزینه table 6 رو نداره

    • آواتار
      حسنا خاکزاد ۲۷ مهر ۱۳۹۹ / ۷:۱۰ ب٫ظ

      درود
      احتمالا تیک data model رو نزدید
      در نهایت ورژن رو چک کنید
      ۲۰۱۳ به بالا

  • الهام صفايي ۴ تیر ۱۳۹۹ / ۱۱:۱۷ ق٫ظ

    با سلام
    متسفانه هیچ کدوم از فایلها باز نمیشن

    • آواتار
      حسنا خاکزاد ۴ تیر ۱۳۹۹ / ۱۲:۲۴ ب٫ظ

      درود
      فایل ها مشکلی ندارن
      نرم افزار winrar/winzip رو به اخرین نسخه اپدیت کنید

  • آواتار
    محمد خجسته ۴ اردیبهشت ۱۳۹۹ / ۹:۲۴ ب٫ظ

    با سلام و خدا قوت
    خیلی ممنون بابت آموزش کاربردی و عالی شما
    موفق باشید

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد.

توسط
تومان