تابع GETPIVOTDATA در اکسل — معرفی و کاربردهای عملی

GETPIVOTDATA یکی از توابع کاربردی اکسل برای استخراج مقادیر از جدول محوری (PivotTable) است. این تابع به شما اجازه می‌دهد به‌صورت پویا و پایدار به مقادیر خلاصه‌شده در PivotTable دسترسی پیدا کنید، حتی اگر ساختار یا مرتب‌سازی جدول تغییر کند.

چرا از GETPIVOTDATA استفاده کنیم؟

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

سینتکس تابع

شکل کلی تابع به صورت زیر است:

=GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], ...)

توضیح پارامترها:

مثال‌های عملی

فرض کنید PivotTable شما در سلول A3 شروع می‌شود و فیلدهای Region (منطقه) و Product (محصول) دارید و مقدار خلاصه شده Sum of Sales است.

=GETPIVOTDATA("Sum of Sales",$A$3,"Region","East","Product","Tea")

این فرمول مجموع فروش محصول “Tea” در منطقه “East” را از PivotTable که شامل آن فیلدها است برمی‌گرداند. اگر نام فیلد دقیقاً با چیزی که اکسل نمایش می‌دهد متفاوت باشد، پاسخ خطا خواهد بود.

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

=GETPIVOTDATA("Sum of Sales",$A$3,"Region",B2,"Product","Tea")

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

نمونه جمع چند آیتم

اگر بخواهید جمع فروش دو محصول را از یک منطقه بگیرید، می‌توانید چند GETPIVOTDATA را با هم جمع کنید:

=GETPIVOTDATA("Sum of Sales",$A$3,"Region","East","Product","Tea")
+GETPIVOTDATA("Sum of Sales",$A$3,"Region","East","Product","Coffee")

یا برای خوانایی بیشتر:

=SUM(
 GETPIVOTDATA("Sum of Sales",$A$3,"Region","East","Product","Tea"),
 GETPIVOTDATA("Sum of Sales",$A$3,"Region","East","Product","Coffee")
)

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

ترکیب با IFERROR و پیشگیری از #REF!

گاهی ممکن است آیتمی در PivotTable وجود نداشته باشد یا فیلد تغییر کند. برای جلوگیری از نمایش خطا:

=IFERROR(GETPIVOTDATA("Sum of Sales",$A$3,"Region",B2,"Product",C2),0)

این فرمول در صورت بروز خطا مقدار 0 را برمی‌گرداند و ظاهر گزارش را تمیز نگه می‌دارد.

نکات پیشرفته و بهترین روش‌ها

• دقت در نوشتن نام فیلد: بعضی اوقات PivotTable نام فیلد مقدار را به صورت “Sum of Sales” نمایش می‌دهد. برای جلوگیری از اشتباه، نام دقیق را از هدر مقدار کپی کنید.
• استفاده از ارجاع به سلول PivotTable: به‌جای ارجاع به یک سلول ثابت، می‌توانید نام جدول محوری یا آدرس سلول بالای PivotTable را استفاده کنید تا تابع بداند از کدام Pivot مقدار را بگیرد.
• غیرفعال کردن تولید خودکار GETPIVOTDATA: اگر هنگام تایپ مساوی و انتخاب یک سلول از PivotTable صرفاً می‌خواهید آدرس سلول را، نه GETPIVOTDATA را، استفاده کنید می‌توانید این گزینه را خاموش کنید: PivotTable Analyze (یا Options) → Generate GetPivotData (بردارید تیک).
• هرچند GETPIVOTDATA پایدار است، اما در صورت تغییر نام فیلدها یا حذف کامل فیلدها باید فرمول‌ها را بازنگری کنید.

اشتباهات رایج و راه‌حل‌ها

• خطای نام فیلد: مطمئن شوید نام فیلد مقدار دقیقاً همان است که Pivot نمایش می‌دهد. کپی/پیست بهترین روش است.
• استفاده از مرجع اشتباه برای pivot_table: اگر ارجاع به سلول خارج از PivotTable داده شود، تابع با خطا مواجه می‌شود.
• انتظار از wildcard: GETPIVOTDATA از الگوهای wildcard پشتیبانی نمی‌کند؛ برای جستجوی مبتنی بر بخش‌هایی از متن باید از راهکارهای کمکی (مانند MATCH یا INDEX روی داده‌های منبع) استفاده کنید.

مثال ترکیبی با SUMPRODUCT برای گزارش‌های پیچیده

فرض کنید می‌خواهید مجموع فروش چند منطقه مشخص را در یک سلول نشان دهید ولی نمی‌خواهید تک‌تک مقادیر را جمع بزنید؛ می‌توانید در یک ستون کمکی لیست مناطق قرار دهید و سپس با SUMPRODUCT و GETPIVOTDATA جمع بگیرید:

=SUMPRODUCT(
 N(GETPIVOTDATA("Sum of Sales",$A$3,"Region",E2:E5))
)

در اینجا E2:E5 لیستی از مناطق است. تابع N برای تبدیل مقادیر بدست‌آمده به عدد استفاده شده تا SUMPRODUCT بتواند جمع را انجام دهد. توجه کنید که این فرمول به عنوان فرمول آرایه‌ای یا در نسخه‌های جدید اکسل به‌طور عادی کار می‌کند.

جمع‌بندی و توصیه‌های عملی

GETPIVOTDATA ابزاری قدرتمند برای استخراج دقیق و پایدار داده‌ها از PivotTable است و مخصوصاً برای داشبوردها و گزارش‌هایی که نیاز به اتوماسیون و ثبات دارند توصیه می‌شود. اما باید به نام دقیق فیلدها، ارجاعات صحیح و مدیریت خطاها توجه کنید. برای گزارش‌های سریع و موقتی ممکن است استفاده از ارجاعات مستقیم سلولی ساده‌تر باشد، اما اگر می‌خواهید گزارش شما در مقابل تغییرات ساختار مقاوم بماند، GETPIVOTDATA انتخاب بهتری است.

در صورت تمایل می‌توان مثال‌های فایل‌محور‌تر (با داده و PivotTable نمونه) یا فرمول‌های داینامیک‌تری (با استفاده از نام‌های تعریف‌شده و Slicers) تهیه کرد.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال