اگر دادههای اکسل قبل از ساخت PivotTable مرتب و قابل تحلیل نباشند، نتیجه ممکن است ناقص یا نامنظم شود. با استفاده از چهار تابع XLOOKUP، IF، TEXTSPLIT و TRIM میتوان اطلاعات را کاملتر کرد، دستهبندی ساخت، دادههای ترکیبی را تفکیک کرد و خطاهای ناشی از فاصلههای اضافی را کاهش داد.
مایکروسافت نیز تأکید میکند که دادههای منبع PivotTable باید ساختار مناسبی داشته باشند و نوع دادهها در هر ستون با یکدیگر سازگار باشد.
استفاده از XLOOKUP برای کامل کردن دادههای PivotTable
گاهی جدول فروش فقط شناسه محصول را دارد و نام محصول یا دستهبندی آن در جدول دیگری قرار گرفته است. تابع XLOOKUP میتواند اطلاعات مرتبط را بر اساس شناسه پیدا کرده و به جدول اصلی اضافه کند.
به عنوان مثال برای اضافه کردن نام محصول میتوان از این فرمول استفاده کرد:
=XLOOKUP([@ProductID],tbl_Products[ProductID],tbl_Products[ProductName])
و برای اضافه کردن دستهبندی:
=XLOOKUP([@ProductID],tbl_Products[ProductID],tbl_Products[Category])
در نتیجه میتوان در PivotTable به جای شناسههای نامفهوم، فروش را بر اساس نام محصول یا دستهبندی بررسی کرد. XLOOKUP برای جستوجوی یک مقدار و برگرداندن مقدار مرتبط از همان ردیف طراحی شده است.
دستهبندی مقادیر با تابع IF در اکسل
گاهی اعداد خام برای تحلیل PivotTable مناسب نیستند و بهتر است به چند گروه تبدیل شوند. به عنوان مثال میتوان سفارشهای حداقل ۱۰۰۰ دلاری را در گروه High Value و سایر سفارشها را در گروه Standard قرار داد.
=IF([@Amount]>=1000,"High Value","Standard")
حالا ستون OrderType را میتوان به بخش Rows یا Filters در PivotTable اضافه کرد و تعداد یا مبلغ سفارشهای هر گروه را بهسادگی مقایسه کرد. برای تعداد بیشتری از دستهها نیز میتوان از IFS یا توابع منطقی مشابه استفاده کرد.
جدا کردن اطلاعات ترکیبی با TEXTSPLIT
اگر یک سلول شامل چند نوع اطلاعات باشد، PivotTable آن را یک مقدار واحد در نظر میگیرد. به عنوان مثال عبارت Chicago | IL | Midwest برای اکسل یک مقدار متنی است و نمیتوان شهر، ایالت و منطقه را به صورت مستقل تحلیل کرد.
تابع TEXTSPLIT میتواند چنین اطلاعاتی را بر اساس جداکننده به چند بخش تقسیم کند:
=TEXTSPLIT(tbl_Sales[@Location]," | ")
این تابع برای تقسیم متن بر اساس جداکنندههای ستونی یا سطری طراحی شده است.
با تبدیل Location به ستونهای شهر و ناحیه و منطقه، میتوان در PivotTable فروش را به صورت سلسلهمراتبی بر اساس منطقه، ایالت و شهر بررسی کرد. به صورت مشابه برای تبدیل کردن اسم و سن و شهر اطراف به سه ستون مجزا، میتوان از تابع TEXTSPLIT استفاده کرد.
پاک کردن فاصلههای اضافی با TRIM
فاصلههای اضافی در دادههای متنی میتوانند باعث شوند دو نام ظاهراً یکسان در PivotTable به عنوان دو مقدار متفاوت شناسایی شوند. این مشکل در دادههایی که از سایتها یا برنامههای دیگر کپی شدهاند بیشتر دیده میشود.
برای پاکسازی نام مشتری میتوان یک ستون موقت ایجاد کرد و از فرمول زیر استفاده کرد:
=TRIM([@Customer])
پس از بررسی نتیجه، میتوان مقادیر پاکشده را به صورت Values روی ستون اصلی قرار داد. تابع TRIM فاصلههای اضافی ابتدا و انتهای متن و فاصلههای تکراری بین کلمات را حذف میکند. با این حال فاصلههای خاصی مانند Non-breaking Space ممکن است به روش دیگری مانند SUBSTITUTE نیاز داشته باشند.
قبل از ساخت PivotTable دادهها را آماده کنید
ترتیب کلی کار میتواند چنین باشد:
داده خام → تکمیل اطلاعات → دستهبندی → تفکیک دادهها → پاکسازی → PivotTable
لازم نیست همیشه از هر چهار تابع استفاده کنید. کافی است قبل از ساخت PivotTable بررسی کنید دادهها چه کمبود یا مشکلی دارند و همان بخش را اصلاح کنید. پس از آمادهسازی دادهها میتوانید از مسیر زیر PivotTable را ایجاد کنید:
Insert > PivotTable
با آمادهسازی صحیح دادهها، فیلدهای بیشتری برای Rows و Columns و Filters و Values خواهید داشت و PivotTable نیز نتایج منظمتر و قابلتحلیلتری ارائه میدهد.
گیم پلنت