سبد خرید
0

هیچ محصولی در سبد خرید نیست.

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

تابع GetPivotData در اکسل و نحوه کار با آن

پیوت تیبل در اکسل
۵/۵ - (۳ امتیاز)

ابزار Pivot Table و تابع GetPivotData

همونطور که میدونیم یکی از روش های گزارشگیری استفاده از ابزار پیوت تیبل هست. این ابزار فوق العاده به ما کمک میکنه که گزارش های متنوعی رو بگیریم. اما خیلی وقت ها پیش میاد که به قسمت های خاصی از گزارش نیاز داریم. مثلا میخوایم جمع یک فیلد که در گزارش محاسبه شده رو در شیت نهایی و یا داشبورد ببینیم. فرض کنید گزارشی مطابق با شکل ۱ داریم که میخوایم میزان اشتغال دستگاه اجرایی مسکن رو فراخوانی کنیم. برای این کار اگه توی یک سلول بطور مستقیم و بدون کلیک کردن بنویسیم =B9 (اگر به جای این کار کلیک کنید تابع GetPivotData تو فرمول ظاهر میشه) میزان اشتغال ایجاد شده در دستگاه اجرایی مسکن رو میبینیم.

تابع Getpivotdata – فراخوانی قسمتی از یک گزارش بصورت مستقیم و بدون تابع

شکل ۱- تابع Getpivotdata – فراخوانی قسمتی از یک گزارش بصورت مستقیم و بدون تابع

اما مسئله ای که هست اینه که اگر گزارش با هر روشی فیلتر بشه (فیلتر یا Slicer)، ممکنه تعداد اشتغال مسکن دیگه در سلول B7 نباشه. در شکل ۲ میبینیم با فیلتر کردن سال روی سال ۸۹، سلول تعداد اشتغال برای مسکن، به سلول B7 جابجا شده. اما فرمول نوشته همجنان به سلول B9 اشاره میکنه که در این گزارش نمایش دهنده جمع تعداد اشتغال برای دستگاه اجرایی های سال ۸۹ هست.

تابع Getpivotdata – فیلتر کردن گزارش

شکل ۲- تابع Getpivotdata – فیلتر کردن گزارش

اینجاست که ما باید دنبال راهی باشیم که تعداد اشتغال مسکن رو فارغ از آدرس سلول پیدا کنیم که در صورت جابجا شدن مکان آیتم ها، خروجی هم متناسب با اون تغییر کنه. این کار رو تابع Getpivotdata برای ما انجام میده. در واقع این تابع فارغ از آدرس سلول، و با استفاده از نام فیلد و عنوان آیتم ها، خروجی مورد نظر رو پیدا میکنه. مثلا میگه در فیلد دستگاه اجرایی، آیتم مسکن رو برای تعداد اشتغال، پیدا کن، حالا هر جایی که هست.(چیزی شبیه آدرس دهی در Table در اکسل)

آرگومان های تابع Getpivotdata به شرح زیر است:

Data_Field: فیلدی هست که محاسبات روی اون انجام میشه که در واقع در قسمت Value در پیوت تیبل قرار گرفته. در مثال بالا، فیلد تعداد اشتغال، در این قسمت قرار میگیره.

Pivot_Table: سلول مبدا پیوت تیبل هست. در واقع با توجه به اینکه در هر شیت میتونیم چند پیوت تیبل داشته باشیم، در این آرگومان تعیین میکنیم که منظورمون کدوم گزارش هست. در مثال بالا A3 سلولی هست که گزارش از اون شروع شده.

[Field1,Item1]: نام فیلد و آیتم مورد نظر که میخواهیم فراخوانی بشه در این قسمت قرار میگیره. این آرگومان اختیاری هست و به این معنی هست که اگر حذف بشه، Grandtotal در گزارش نمایش داده میشه.

حالا برگردیم به مثال قبلی. برای استفاده از این تابع راحت ترین کار اینه که بعد از زدن = روی سلول مورد نظر کلیک کنیم. با این کار مستقیما تابع Getpivotdata ظاهر میشه. به تصویر زیر دقت کنید. با کلیک کردن روی سلول B9 تابع Getpivotdata ظاهر میشه که داده مربوط به تعداد اشتغال رو از گزارشی که از سلول A3 شروع میشه و در فیلد “دستگاه اجرایی” و آیتم “مسکن” قرار گرفته رو نشون میده.

=Getpivotdata(“تعداد اشتغال”,$A$3,”دستگاه اجرایی”,”مسکن”)

نحوه اعمال فیلتر در پیوت تیبل

همونطو که در تصویر نمایش داده میشه، در صورت فیلتر کردن سال بر روی آیتم ۸۹، نتیجه فرمول Getpivotdata آپدیت میشه و عدد ۱۵ که این بار در سلول B7 قرار گرفته رو نشون میده. پس میبینیم که این تابع فارغ از آدرس سلول، از طریق فیلد و آیتم به مورد مورد نظر ارجاع میده.

نکته:
در صورتی که با کلیک کردن روی سلولی از پیوت تیبل، تابع Getpivotdata فعال نشد. باید از مسیر زیر، تیک Generate Getpivotdata رو بزنیم.

Analyze/ Options/ Generate Getpivotdata

 

حالا مثال دیگری از این تابع بزنیم که فیلدها و آیتم های بیشتری داره. فرض میزان تعداد اشتغال و مبلغ درخواستی رو به تفکیک بخش و دستگاه اجرایی ایجاد کردیم.حالا میخوایم جمع مبلغ درخواستی رو برای اداره صنایع و معادن و در بخش کشاورزی فراخوانی کنیم. با کلیک روی سلول G7 فرمول زیر نمایش داده میشه.

=GETPIVOTDATA(“Sum of مبلغ درخواستی”,$A$3,”دستگاه اجرایی”,”اداره صنایع و معادن”,”بخش”,”کشاورزی”)

تابع getpivotdata- فراخوانی داده از گزارش ماتریسی

شکل ۳- تابع getpivotdata- فراخوانی داده از گزارش ماتریسی

همونطور که در فرمول بالا می بینیم، آرگومان اول که نشون دهنده فیلد محاسباتی هست، مبلغ درخواستی تعیین شده (نه تعداد اشتغال) و در ادامه، فیلد “دستگاه اجرایی” و آیتم “اداره صنایع و معادن” و بعد شرط دوم، یعنی فیلد “بخش” و آیتم “کشاورزی”.

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

اینکار رو بصورت انجام میدیم:

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

=GETPIVOTDATA(“Sum of مبلغ درخواستی”,$A$3,”دستگاه اجرایی”,F15,”بخش”,C15)

تابع getpivotdata- پویا کردن تابع با استفاده از لیست های فروریز

شکل ۴- تابع getpivotdata- پویا کردن تابع با استفاده از لیست های فروریز

با این کار، با تغییر سلول های C15 و F15 نتیجه فرمول تغییر میکنه. به تصویر زیر دقت کنید.

ارجاع به سلول های grand total

تا الان باید نحوه ارجاع به سلول های Grand Total رو باید حدس زده باشید. برای ارجاع به این مقادیر، نیازی به فیلد و آیتم نیست و فقط دو آرگومان اول تابع رو تعیین میکنیم که فیلد محاسباتی و مکان شروع گزارش رو تعیین میکنه. مثلا فرض کنید میخواهیم کل اشتغال ایجاد شده رو فراخوانی کنیم. برای این کار فرمول رو مطابق شکل زیر می نویسیم:

=GETPIVOTDATA(“Sum of تعداد اشتغال”,$A$3)

با استفاده از این تابع میتونیم اجزای مختلفی از گزارش های ایجاد شده رو در داشبوردها فراخوانی کنیم و با استفاده از slicer ها و سلول های متغیر، نتایج رو پویا کنیم.

پیشنهاد میکنم حتما سری آموزش های پیوت تیبل رو هم ببین.

کلیدواژه : تابع GetPivotData
133

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

دیدگاه کاربران
  • احمد ۱۹ مهر ۱۳۹۸ / ۱۱:۱۷ ب٫ظ

    فوق العاده بود دستتون درد نکنه

  • mmahdih ۸ مهر ۱۳۹۸ / ۱۲:۴۸ ب٫ظ

    سلام
    جنب چراغی عزیز اگر فایل نمونه بزارید عالی میشه

    با تشکر

ارسال دیدگاه

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

توسط
تومان