اکسل پدیا، دانشنامه پارسی اکسل

021-47625755
info@excelpedia.net

ورود یا ثبت نام

  • دانشنامه اکسل
    • اکسل چیست؟
    • اکسل مقدماتی
    • توابع اکسل
    • نمودار ها
    • وی بی – ماکرو
  • آکادمی اکسل
    • ویدئوهای آموزشی
      • رسم نمودارهای حرفه ای
      • اکسل و شروع حرفه ای
      • اکسل نینجا (اکسل پیشرفته)
      • صفر تا صد Pivot Table
      • برنامه نویسی وی بی (VBA)
    • کتاب ها
      • کتاب های فارسی
      • کتاب های انگلیسی
  • داشبورد های مدیریتی
    • آموزش ساخت داشبورد
    • دانلود داشبورد نمونه
  • افزونه های کاربردی
  • دوره های حضوری
    • اکسل نینجا
    • برنامه نویسی وی بی (VBA)
    • Cart

      0

مسائل کاربردی

  • خانه
  • بلاگ
  • مسائل کاربردی
  • نمایش متن در فیلد Value پیوت تیبل

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

  • ارسال شده توسط حسنا خاکزاد
  • دسته بندی مسائل کاربردی
  • تاریخ ۳ اردیبهشت ۱۳۹۹
  • نظرات ۵ دیدگاه ها
نمایش متن در فیلد Value پیوت تیبل
5 / 5 ( 1 امتیاز )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

حتما بخوانید:  ترکیب توابع با تابع Vlookup در اکسل

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

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

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

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

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

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

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

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

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

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

مدیرت خطا با IFerror

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

 

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

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

با عضویت در سایت به صورت مستقیم دانلود کنید

  • ارسال به ایمیل

Des

دریافت فایل این آموزشبرای دانلود فایل کلیک کنید

برچسب:آرایه ای, تابع Iferror, تابع Index, تابع Match

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

مطلب قبلی

این عدد حاصل جمع چه اعدادی است؟
۳ اردیبهشت ۱۳۹۹

مطلب بعدی

ایجاد فرم در گوگل شیت
۳ اردیبهشت ۱۳۹۹

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

Match Nth Large Cover
حل مسئله رتبه بندی داده های تکراری
۱۱ آذر, ۱۳۹۹
Sort-Dates-Cover
مرتب سازی انواع تاریخ در اکسل
۲ شهریور, ۱۳۹۹
Two Dates Cover
محاسبات در یک بازه تاریخی
۱۴ تیر, ۱۳۹۹

    5 نظر

  1. profile avatar
    خشایار
    ۲۲ مهر ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۲۷ مهر ۱۳۹۹
      پاسخ

      درود
      احتمالا تیک data model رو نزدید
      در نهایت ورژن رو چک کنید
      2013 به بالا

  2. profile avatar
    الهام صفايي
    ۴ تیر ۱۳۹۹
    پاسخ

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

    • profile avatar
      حسنا خاکزاد
      ۴ تیر ۱۳۹۹
      پاسخ

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

  3. profile avatar
    محمد خجسته
    ۴ اردیبهشت ۱۳۹۹
    پاسخ

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

نظر بدهید لغو پاسخ

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

-- بارگیری کد امنیتی --

زودتر از دیگران با خبر بشید

از طریق ایمیل، مطالب آموزشی درباره اکسل و برنامه نویسی وی بی و سایر رویدادهای مهم را دریافت خواهید کرد.
  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

آخرین مطالب

حل مسئله رتبه بندی داده های تکراری
۱۱آذر۱۳۹۹
ابزار Mail Merge
ترکیب Mail Merge ورد و اکسل
۲۳آبان۱۳۹۹
سورت تاریخ
مرتب سازی انواع تاریخ در اکسل
۰۲شهریور۱۳۹۹
توابع متنی: تابع Proper، تابع Lower، تابع Upper در اکسل
توابع ویرایش متن انگلیسی در اکسل
۱۷مرداد۱۳۹۹

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

پاسخ سوالات شما

اگر سوالات اکسلی دارید تو تالار گفتمان اکسل پدیا میتونید مطرح کنید. اساتید بسیاری هستند که به شما کمک میکنند.

کانال و تالار گفتمان اکسل پدیا

ورود به تالار گفتگو تلگرام

عضویت در خبرنامه
با عضویت در خبرنامه، ایمیل‌های آموزشی درباره اکسل و برنامه نویسی وی بی را دریافت خواهید کرد.

  • این فیلد برای اعتبار سنجی است و باید بدون تغییر باقی بماند .

021-47625755

info@excelpedia.net

آدرس: تهران، میدان دوم صادقیه، برج گلدیس، طبقه 7 واحد 721

دانلود اپ اکسل پدیادانلود از کافه بازار

تمامی حقوق برای اکسل پدیا محفوظ است.

  • اکسل از راه دور
  • شرایط و قوانین
  • درباره ما
  • تماس با ما