اگر با حجم زیادی از دادهها در اکسل سروکار دارید، احتمالا میدانید که بررسی و تحلیل دستی اطلاعات میتواند زمانبر و پیچیده باشد. پیوت تیبل در اکسل (Pivot Table) یکی از کاربردیترین ابزارها برای خلاصهسازی، دستهبندی و تحلیل سریع دادهها است. با استفاده از پیوت تیبل میتوانید اطلاعات را بر اساس معیارهای مختلف مرتب و فیلتر کنید، محاسباتی مانند مجموع، میانگین و تعداد را انجام دهید و از دادههای خام، گزارشهای قابل فهم و کاربردی بگیرید. در این مطلب از آکادمی همراه اول، نحوه ساخت و تنظیم پیوت تیبل در اکسل را بهصورت گامبهگام بررسی میکنیم تا بتوانید از این ابزار قدرتمند برای تحلیل بهتر دادهها استفاده کنید.
خانه > آخرین مطالب > مقالات > علوم داده > پیوت تیبل در اکسل چیست؟ | آموزش ساخت Pivot Table با مثال
پیوت تیبل در اکسل چیست؟ | آموزش ساخت Pivot Table با مثال
فهرست مطالب
فهرست مطالب
پیوت تیبل در اکسل چیست و چه کاربردی دارد ؟
Pivot Table ابزاری آماری است که دادههای انتخابشده از ستونها و ردیفهای یک صفحه گسترده یا جدول پایگاه داده را خلاصه و سازماندهی مجدد میکند تا بتوان از آنها گزارش موردنظر را تهیه کرد. این ابزار به کاربران کمک میکند حجم زیادی از دادهها را به شکلی سادهتر بررسی و تحلیل کنند. پیوت تیبل در واقع دادههای موجود در صفحه گسترده یا پایگاه داده را تغییر نمیدهد و صرفاً دادهها را به اصطلاح «محوربندی» یا Pivot میکند تا بتوان اطلاعات را از زوایای مختلف مشاهده کرد.
این جدولها بهویژه زمانی کاربردی هستند که با حجم زیادی از دادهها سروکار داریم و انجام محاسبات بهصورت دستی زمان زیادی میبرد. این ابزار میتواند عملیات مختلفی جمله محاسبه مجموع، میانگین وو شناسایی متغیر پرت روی دادهها انجام دهد. سپس پیوت تیبل این اطلاعات را در قالبی ساده، منظم و قابلفهم نمایش میدهد و باعث میشود مقادیر و نکات مهم راحتتر دیده شوند.
مسعود کاویانی
آشنایی با مبانی علوم داده
رایگان
قبل از ساخت پیوت تیبل چه نکاتی را رعایت کنیم؟
برای اینکه پیوت تیبل در اکسل بهدرستی ایجاد شود و نتایج دقیق و قابل اعتمادی ارائه دهد، لازم است پیش از شروع، دادههای خود را بهدرستی آمادهسازی کنید. رعایت چند نکته ساده، مانند مرتبسازی اطلاعات در قالب سطر و ستون، اختصاص عنوان مشخص به هر ستون و حذف سطرها و ستونهای خالی، از بروز خطا در هنگام ساخت پیوت تیبل جلوگیری میکند. همچنین بهتر است دادههای مرتبط را در یک محدوده منظم و یکپارچه قرار دهید تا اکسل بتواند آنها را بهدرستی شناسایی و تحلیل کند.
آموزش ساخت پیوت تیبل در اکسل به صورت گام به گام
برای ساخت یک پیوت تیبل در اکسل، کافی است مراحل ساده زیر را دنبال کنید:
مرحله اول: آمادهسازی دادهها
پیش از ایجاد پیوت تیبل، مطمئن شوید که دادههای شما ساختار و قالببندی مناسبی دارند:
دادهها را بهصورت جدولی سازماندهی و اطلاعات را در قالب سطرها و ستونها مرتب کنید و برای هر ستون یک عنوان مشخص در نظر بگیرید. از وجود سطرها یا ستونهای خالی جلوگیری کنید. مطمئن شوید که در میان دادههای شما هیچ سطر یا ستون خالی وجود ندارد.
مرحله دوم: انتخاب دادهها
برای انتخاب دادههایی که میخواهید در پیوت تیبل استفاده شوند، روی یکی از سلولهای داخل محدوده دادهها کلیک کنید یا محدوده مشخصی از دادهها را که میخواهید در پیوت تیبل نمایش داده شوند، انتخاب کنید.

مرحله سوم: ایجاد پیوت تیبل
به تب Insert در نوار ابزار اکسل بروید و روی گزینه PivotTable کلیک کنید یا کلیدهای “Alt + N + V” در ویندوز را فشار دهید و کلیدهای “Command + Option + P” در مک را فشار دهید تا پیوت تیبل ایجاد شود. در پنجره Create PivotTable، محدوده دادههای انتخابشده را بررسی کنید تا از درستی آن مطمئن شوید و محل ایجاد پیوت تیبل را مشخص کنید:
- New Worksheet: پیوت تیبل را در یک کاربرگ جدید ایجاد میکند.
- Existing Worksheet: به شما امکان میدهد با مشخص کردن یک سلول، محل ایجاد پیوت تیبل را در کاربرگ فعلی تعیین کنید.

ساخت و سازماندهی پیوت تیبل
پس از ایجاد پیوت تیبل، پنل PivotTable Field List در سمت راست صفحه اکسل ظاهر میشود. از طریق این پنل میتوانید نحوه نمایش و سازماندهی دادهها را مشخص کنید.

برای جابهجایی و قراردادن فیلدها، عنوان ستونها را از فهرست فیلدها (Field List) بکشید و در یکی از چهار بخش زیر قرار دهید:
- Rows (سطرها): مشخص میکند کدام فیلدها بهصورت سطر در پیوت تیبل نمایش داده شوند.
- Columns (ستونها): دادهها را بر اساس فیلد انتخابشده در ستونهای مختلف دستهبندی میکند.
- Values (مقادیر): دادههای عددی را برای انجام محاسباتی مانند مجموع (Sum)، تعداد (Count) و موارد مشابه اضافه میکند.
- Filters (فیلترها): امکان فیلتر کردن دادهها را فراهم میکند تا بتوانید تحلیل دقیقتری داشته باشید.

برای سفارشیسازی و تغییر نوع محاسبه، روی یکی از مقادیر موجود در بخش Values راستکلیک و گزینه Value Field Settings را انتخاب کنید. سپس نوع محاسبه موردنظر خود را، مانند مجموع (Sum)، میانگین (Average)، تعداد (Count) و سایر موارد، انتخاب کنید.

مرحله پنجم: قالببندی و سفارشیسازی پیوت تیبل
برای اعمال سبک دلخواه، پیوت تیبل را انتخاب کنید و از مسیر Design > PivotTable Styles، یکی از قالبهای آماده را برای آن در نظر بگیرید.

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

در نهایت با افزودن اسلایسر، از مسیر Analyze> Slicer، اسلایسر ایجاد کنید تا بتوانید دادههای پیوت تیبل را بهصورت تعاملی فیلتر کنید.

مرحله ششم: بهروزرسانی پیوت تیبل
هر زمان که دادههای منبع تغییر کردند، پیوت تیبل را بهروزرسانی کنید تا اطلاعات جدید در آن نمایش داده شوند. برای این کار، مراحل زیر را انجام دهید:
- روی یکی از سلولهای داخل پیوت تیبل کلیک کنید.
- از مسیر PivotTable Analyze > Refresh، پیوت تیبل را بهروزرسانی کنید.

نحوه فیلتر و گروهبندی اطلاعات در پیوت تیبل
کاربرانی که مدت زیادی با پیوت تیبل کار کردهاند، احتمالاً بخش قدیمی Page را در پیوت تیبل به خاطر دارند. این بخش در نسخههای جدید اکسل به Filters تغییر نام داده است، اما عملکرد آن تقریباً مانند نسخههای قدیمی اکسل باقی مانده است. مایکروسافت قابلیت جدیدی نیز به این بخش اضافه کرده است که به کاربران امکان میدهد چندین گزینه را بهصورت همزمان از قسمت Filters انتخاب کنند. گرچه بخش Filters از نظر ظاهری بهاندازه Slicer جذاب و کاربردی نیست، اما همچنان قابلیت بسیار مفیدی دارد. بهخصوص زمانی که بخواهید برای هر مشتری، یک نسخه جداگانه از گزارش پیوت تیبل تهیه کنید.
تکتم خطیبی
اصول پایگاه داده مقدماتی
اعمال فیلتر از Raw Lable
پیوت تیبل نمایشدادهشده در مثال زیر، یک ابزار بسیار مناسب برای تهیه گزارشهای سریع و موردی است که میتوان آن را در اختیار مدیران ارشد قرار داد. فیلتر در پیوت تیبل روی ردیف اعمال میشود. برای فیلتر دپارتمانها و مشاهده تعداد محدودی از آنها روی علامت فلش کلیک و تیک درپارتمانهایی که نیاز دارید را فعال کنید.

در همین جدول اگر بخواهید بر اساس حقوق فیلتر را اعمال کنید و برای مثال فقط افرادی که حقوقشان بیشتر از ۱۵۰,۰۰۰ است را در جدول نگهدارید، باید از فلش کنار Row Lable گزینه value filters را باز کنید.

در مرحله بعد گزینه Greater than را انتخاب کنید. در پنجره باز شده فیلد موردنظر را روی sum of حقوق قرار دهید. مقدار ۱۵۰,۰۰۰ را وارد کنید و گزینه ok را بزنید. در این حالت فقط ردیفهایی که مجموع حقوقشان بیشتر از ۱۵۰,۰۰۰ است نمایش داده میشود.

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

برای فیلتر کردن پیوت تیبل، کافی است روی یکی از منوهای کشویی موجود در بخش Filters کلیک کنید. این منوی کشویی همیشه در ابتدا گزینه (All) را نمایش میدهد و پس از آن، فهرست تمام مقادیر یکتای موجود در فیلد موردنظر را نشان میدهد. با انتخاب یکی از این مقادیر، پیوت تیبل فقط اطلاعات مربوط به همان گزینه را نمایش خواهد داد.

اگر تعداد زیادی فیلد به بخش Filters اضافه کنید، ممکن است بخواهید از یکی از تنظیمات کمتر شناختهشده پیوت تیبل استفاده کنید. برای این کار، طبق مراحل زیر پیش بروید:
- از تب Analyze، روی گزینه Options کلیک کنید.
- در پنجره PivotTable Options، وارد تب Layout & Format شوید.
- گزینه Report Filter Fields per Column را پیدا کنید.
- مقدار این گزینه را از ۰ به یک عدد مثبت تغییر دهید.
با انجام این کار، اکسل فیلدهای بخش Filters را در چند ستون مرتب میکند.
بابک تیمورپور
داده کاوی با پایتون
تهیه گزارش جداگانه از پیوت تیبل برای هر گزینه موجود در فیلتر
اگرچه امروزه Slicerها به یکی از قابلیتهای محبوب و پرکاربرد در گزارشگیری با پیوت تیبل تبدیل شدهاند، اما فیلترهای معمولی گزارش همچنان یک قابلیت منحصربهفرد دارند که Slicerها قادر به انجام آن نیستند.
فرض کنید گزارشی تهیه کردهاید که قرار است آن را در اختیار مدیران بخشهای مختلف یک شرکت قرار دهید. این گزارش شامل اطلاعات مشتریان، میزان درآمد و سود آنها است. شما میخواهید هر یک از مدیران، فقط اطلاعات مربوط به مشتریان حوزه مسئولیت خود را مشاهده کنند.
برای انجام این کار، میتوانید با استفاده از قابلیت Show Report Filter Pages، بهسرعت برای هر گزینه موجود در فیلتر، یک نسخه جداگانه از گزارش پیوت تیبل آماده کنید. مراحل انجام این کار به شرح زیر است:
۱. آمادهسازی قالب و قالببندی پیوت تیبل
قبل از هر کاری، مطمئن شوید که قالببندی پیوت تیبل به شکل مناسبی انجام شده است. از آنجا که قرار است چندین نسخه از پیوت تیبل گزارش بگیرید، بهتر است از همان ابتدا قالببندی تمام بخشها را بررسی کنید تا مجبور نباشید تنظیمات ظاهری هر کاربرگ را جداگانه تغییر دهید. بنابراین، قالببندی اعداد و عنوانها را پیش از شروع کار بررسی کنید.
۲. افزودن فیلد موردنظر به بخش Filters
در این فیلد حقوق را به بخش Filters منتقل کنید. در این مرحله، فیلتر حقوق باید روی گزینه (All) تنظیم شده باشد تا تمام حوزههای فعالیت در گزارش لحاظ شوند.
۳. انتخاب یک سلول از پیوت تیبل
روی یکی از سلولهای پیوت تیبل کلیک کنید تا تب Analyze در نوار ابزار اکسل نمایش داده شود.
۴. باز کردن منوی Options
در سمت چپ تب Analyze، گزینه Options را پیدا کنید. در کنار دکمه Options، یک منوی کشویی وجود دارد. توجه داشته باشید که نباید روی دکمه اصلی Options کلیک کنید؛ بلکه باید منوی کشویی کنار آن را باز کنید.
۵. انتخاب گزینه Show Report Filter Pages
از منوی بازشده، گزینه Show Report Filter Pages را انتخاب کنید. با این کار، پنجرهای با همین عنوان باز میشود که فهرستی از تمام فیلدهای موجود در بخش Filters را نمایش میدهد. از آنجا که در این مثال فقط فیلد حقوق به بخش Filters اضافه شده است، تنها همین گزینه در فهرست نمایش داده میشود.
۶. ایجاد گزارشهای جداگانه
روی دکمه OK کلیک و نتیجه را مشاهده کنید. اکسل بهصورت خودکار برای هر یک از مقادیر موجود در فیلد حقوق، یک کاربرگ جدید ایجاد میکند. در اولین کاربرگ جدید، اکسل اولین گزینه موجود در فیلد حقوق را بهعنوان مقدار فیلتر انتخاب میکند. سپس نام کاربرگ جدید را نیز متناسب با همان گزینه تغییر میدهد.
فیلتر کردن دادهها با استفاده از Slicer و Timeline
Slicerها در واقع نسخههای گرافیکی فیلدهای Report Filter هستند. در فیلترهای معمولی، زمانی که چند گزینه را انتخاب میکنید، اکسل معمولاً عبارت (Multiple Items) را نمایش میدهد و مشخص نیست کدام گزینهها انتخاب شدهاند.
اما Slicer این مشکل را برطرف میکند. زیرا بهجای نمایش یک منوی کشویی، مجموعهای از دکمههای گرافیکی را در اختیار کاربر قرار میدهد که با یک نگاه میتوان تشخیص داد کدام گزینهها انتخاب شدهاند و کدام گزینهها در حالت انتخابنشده قرار دارند. برای افزودن Slicer، مراحل زیر را انجام دهید:
- از تب Analyze، روی آیکون Insert Slicer کلیک کنید.
- پنجرهای با عنوان Insert Slicers باز میشود.
- در این پنجره، تمام فیلدهایی را که میخواهید برای آنها فیلترهای گرافیکی ایجاد کنید، انتخاب کنید.
بهصورت پیشفرض، اکسل Slicerها را با رنگهای مشابه، در قالب یک ستون و به شکل پلکانی در کنار یکدیگر قرار میدهد. البته میتوانید این تنظیمات را تغییر دهید. برای این کار، Slicer موردنظر را انتخاب کنید و از تب Slicer Tools Options در نوار ابزار اکسل استفاده کنید.
تغییر تعداد ستونهای Slicer
یکی از قابلیتهای Slicer این است که میتوانید تعداد ستونهای آن را افزایش دهید. برای مثال، فرض کنید قصد دارید ۵۰ کد اختصاری دوحرفی مربوط به ایالتهای مختلف را نمایش دهید. در چنین شرایطی، نمایش این کدها در قالب ۵ ردیف و ۱۰ ستون، بسیار مرتبتر و زیباتر از نمایش آنها در ۵۰ ردیف و یک ستون خواهد بود. برای افزایش تعداد ستونهای Slicer، مراحل زیر را انجام دهید:
- روی Slicer کلیک کنید تا تب Slicer Tools Analyze در نوار ابزار فعال شود.
- از گزینه Columns برای افزایش تعداد ستونهای Slicer استفاده کنید.
- با استفاده از دستگیرههای تغییر اندازه که در اطراف Slicer قرار دارند، میتوانید عرض یا ارتفاع آن را تغییر دهید.
- برای زیباتر شدن ظاهر گزارش، میتوانید از قسمت Slicer Styles برای هر فیلد، رنگ متفاوتی انتخاب کنید.
پس از انجام تنظیمات ظاهری Slicerها، آنها را در قسمت خالی کاربرگ مرتب کنید.
مفهوم رنگهای مختلف در Slicer
توجه داشته باشید که میتوانید عنوان Slicer و ترتیب نمایش گزینههای آن را نیز تغییر دهید. برای این کار، از آیکون Slicer Settings در تب Slicer Tools Options در نوار ابزار استفاده کنید.
همانطور که میتوانید یک سبک جدید برای پیوت تیبل ایجاد کنید، امکان ایجاد سبک جدید برای Slicer نیز وجود دارد. برای این کار، روی یکی از سبکهای موجود Slicer کلیک راست کنید و گزینه Duplicate را انتخاب کنید. سپس میتوانید تنظیماتی مانند نوع فونت، رنگها و سایر ویژگیهای ظاهری را تغییر دهید.
قابلیت جدید انتخاب چند گزینه در Slicer
در نسخه Excel 2016، یک آیکون جدید به نوار بالایی Slicer اضافه شد. این آیکون به شکل سه علامت تیک (Check Marks) نمایش داده میشود. با انتخاب این آیکون، میتوانید چندین گزینه را بهصورت همزمان از Slicer انتخاب کنید؛ بدون اینکه نیازی به نگه داشتن کلید Ctrl هنگام انتخاب گزینهها داشته باشید.
مریم امیرمزلقانی
ریاضیات در علومداده و هوش مصنوعی
استفاده از Timeline برای فیلتر کردن دادهها بر اساس تاریخ
پس از معرفی Slicer در Excel 2010، برخی کاربران اعلام کردند که استفاده از Slicer برای فیلتر کردن فیلدهای تاریخ چندان ایدهآل نیست.
برای مثال، ممکن است مجبور شوید چندین ستون به مجموعه دادههای اولیه خود اضافه کنید تا بتوانید اطلاعات را بر اساس دهههای مختلف دستهبندی و با استفاده از قابلیت Group، تاریخها را بر اساس سال، فصل و ماه گروهبندی کنید.
در نتیجه، تعداد زیادی Slicer ایجاد میشود که هرکدام تلاش میکنند یک بازه زمانی مشخص را انتخاب کنند. برای رفع این مشکل، مایکروسافت در Excel 2013 نوع جدیدی از فیلتر را با نام Timeline Slicer معرفی کرد.
نحوه استفاده از Timeline
برای استفاده از این قابلیت، مراحل زیر را انجام دهید:
- ابتدا یکی از سلولهای پیوت تیبل را انتخاب کنید.
- از تب Analyze، گزینه Insert Timeline را انتخاب کنید.
توجه داشته باشید که Timeline Slicer فقط برای فیلدهایی قابل استفاده است که حاوی مقادیر تاریخ باشند. پس از انتخاب این گزینه، اکسل فهرستی از فیلدهای تاریخ موجود در دادههای منبع را نمایش میدهد تا فیلد موردنظر خود را انتخاب کنید. البته در بیشتر موارد، فقط یک فیلد تاریخ برای انتخاب وجود دارد.
یکی از مهمترین مزایای Timeline Slicer، منوی کشویی آن است که به شما اجازه میدهد بازه زمانی موردنظر را بهراحتی تغییر دهید. به کمک این منو میتوانید دادهها را بر اساس یکی از بازههای زمانی زیر فیلتر کنید.
نکته جالب اینجاست که برای استفاده از این قابلیت، حتی نیازی نیست تاریخهای روزانه را از قبل بر اساس ماه، فصل یا سال گروهبندی کرده باشید. Timeline این امکان را فراهم میکند که مستقیماً از طریق همان فیلد تاریخ، بازه زمانی موردنظر خود را انتخاب و دادهها را فیلتر کنید.
چگونه پیوت تیبل را بهروزرسانی کنیم؟
برای بهروزرسانی پیوت تیبل پس از اعمال تغییرات در دادههای منبع، مراحل زیر را انجام دهید:
- ابتدا روی هر قسمت از پیوت تیبل کلیک کنید. برای انتخاب تمام قسمتهای پیوت تیبل، در ویندوز کلیدهای Ctrl + A و در مک کلیدهای Command + A را فشار دهید.
- از نوار ابزار اکسل، وارد تب PivotTable Analyze شوید و گزینه Refresh را انتخاب کنید.
تفاوت پیوت تیبل و پیوت چارت چیست؟
برای تحلیل و بررسی دادهها در اکسل، دو ابزار کاربردی به نامهای پیوت تیبل و پیوت چارت وجود دارند. هر دو ابزار با هدف سادهسازی فرایند تحلیل دادهها و استخراج اطلاعات مفید طراحی شدهاند، اما از نظر نحوه عملکرد و نمایش اطلاعات با یکدیگر تفاوت دارند. پیوت تیبل دادهها را در قالب جدول خلاصه و دستهبندی میکند، درحالیکه پیوت چارت همین اطلاعات را بهصورت نمودارهای گرافیکی نمایش میدهد. در جدول زیر، مهمترین تفاوتهای این دو ابزار را بررسی میکنیم.
| معیار مقایسه | پیوت تیبل (Pivot Table) | پیوت چارت (Pivot Chart) |
|---|---|---|
| تعریف | ابزاری برای خلاصهسازی، دستهبندی و تحلیل دادهها در قالب جدول است. | ابزاری برای نمایش تصویری و گرافیکی دادههای خلاصهشده در پیوت تیبل است. |
| نحوه نمایش دادهها | دادهها را در قالب ردیفها و ستونها نمایش میدهد. | دادهها را در قالب نمودارهایی مانند ستونی، خطی و دایرهای نمایش میدهد. |
| هدف اصلی | سازماندهی دادهها، انجام محاسبات و استخراج اطلاعات آماری است. | نمایش بصری اطلاعات و کمک به درک بهتر الگوها، روندها و مقایسه دادههاست. |
| امکانات تحلیلی | امکان مرتبسازی، فیلتر کردن، گروهبندی و انجام محاسباتی مانند مجموع، میانگین، حداقل و حداکثر را فراهم میکند. | امکان فیلتر کردن و تحلیل بصری دادهها را از طریق قابلیتهای مرتبط با پیوت چارت فراهم میکند. |
| ایجاد ابزار | میتوان پیوت تیبل را بهصورت مستقل ایجاد کرد. | با ایجاد پیوت چارت، پیوت تیبل متناظر نیز بهصورت خودکار ایجاد میشود. |
| ارتباط با یکدیگر | میتواند بدون وجود پیوت چارت استفاده شود. | برای نمایش و تحلیل دادهها به پیوت تیبل متناظر وابسته است. |
خطاها و مشکلات رایج در استفاده از پیوت تیبل
هنگام ایجاد یا بهروزرسانی پیوت تیبل یا Power Pivot، ممکن است کاربران گاهی با خطاهایی مواجه شوند. این خطاها معمولاً به دلایلی مانند انتخاب نادرست محدوده دادهها، وجود نداشتن عنوان برای ستونها، همپوشانی چند پیوت تیبل یا ناهماهنگ بودن دادههای منبع رخ میدهند. در ادامه رایجترین خطاها و مشکلات پیوت تیبل را با شما بهاشتراک گذاشتهایم:
خطای «Field Name Is Not Valid»
یکی دیگر از مشکلاتی که ممکن است هنگام کار با Excel Power Pivot با آن مواجه شویم، خطای «Field Name Is Not Valid» یا «نام فیلد معتبر نیست» است. این خطا معمولاً زمانی رخ میدهد که میخواهیم یک پیوت تیبل در اکسل ایجاد یا بهروزرسانی کنیم. در این حالت، پیام خطای اکسل به این موضوع اشاره میکند:
«نام فیلد پیوت تیبل معتبر نیست. برای ایجاد یک گزارش پیوت تیبل، باید از دادههایی استفاده کنیم که بهصورت یک فهرست منظم و با ستونهای دارای عنوان سازماندهی شده باشند. همچنین، برای تغییر نام یک فیلد در پیوت تیبل، باید نام جدیدی برای آن وارد کنیم.»
با این حال، وقتی دادههای منبع پیوت تیبل در اکسل را بررسی میکنیم، به نظر میرسد همهچیز درست و بدون مشکل است.
راهحل:
این نوع خطا معمولاً زمانی رخ میدهد که عنوان یک یا چند سلول در ردیف سرستونهای دادههای منبع خالی باشد. برای ایجاد یک پیوت تیبل، لازم است هر ستون حتماً یک عنوان داشته باشد. همچنین، هنگام تبدیل دادهها به یک جدول اکسل، اکسل بهطور خودکار برای ستونهایی که سلول عنوان آنها خالی است، عنوانی اختصاص میدهد.
برای جلوگیری از بروز این مشکل، میتوانیم راهکارهای زیر را به کار بگیریم:
- بررسی محدوده انتخابشده برای دادهها: در پنجره Create PivotTable، محدوده انتخابشده در قسمت Table/Range را با دقت بررسی کنید تا مطمئن شوید هیچ ستون خالی در کنار جدول دادهها بهاشتباه انتخاب نشده باشد.
- بررسی ستونهای مخفی: محدوده دادههای منبع را از نظر وجود ستونهای مخفی بررسی کنید. اگر عنوان یک یا چند ستون وجود ندارد، ابتدا آن ستونها را از حالت مخفی خارج کنید و سپس برای هرکدام یک عنوان مناسب در نظر بگیرید.
- جدا کردن سلولهای ادغامشده: اگر در ردیف عنوان ستونها سلولهای ادغامشده (Merged Cells) وجود دارد، آنها را از حالت ادغام خارج کنید و برای هر سلول بهصورت جداگانه یک عنوان در نظر بگیرید.
- بررسی محتوای سلولهای عنوان: محتوای هر یک از سلولهای عنوان را در نوار فرمول (Formula Bar) بررسی کنید. ممکن است متن عنوان یک ستون به دلیل خالی بودن سلول کناری، روی آن سلول قرار گرفته باشد و این موضوع باعث شود تصور کنیم سلول خالی است، درحالیکه متن عنوان در واقع متعلق به سلول مجاور است.
خطای «Overlapping» در پیوت تیبل اکسل
یکی دیگر از خطاهای رایجی که ممکن است هنگام کار با پیوت تیبل با آن مواجه شویم، خطای زیر است:
«A PivotTable report cannot overlap another PivotTable report» که به معنی این عبارت است: «گزارش یک پیوت تیبل نمیتواند با گزارش پیوت تیبل دیگری همپوشانی داشته باشد.»
این پیام خطا زمانی در اکسل ظاهر میشود که چند پیوت تیبل در یک کاربرگ قرار داشته باشند و فضای خالی کافی برای گسترش یکی از آنها بهمنظور نمایش دادههای جدید وجود نداشته باشد.
راهحل:
در این شرایط، معمولاً شناسایی و برطرف کردن مشکل کار چندان دشواری نیست. بااینحال، زمانی که با حجم زیادی از دادهها، تعداد زیادی پیوت تیبل و چندین منبع داده مختلف سروکار داریم، پیدا کردن علت اصلی مشکل ممکن است دشوار به نظر برسد.
برای برطرف کردن این مشکل، میتوانیم از ماکروی List All Pivot Table – Details استفاده کنیم. این ماکرو فهرستی از تمام پیوت تیبلهای موجود در فایل اکسل را در اختیارمان قرار میدهد و اطلاعاتی درباره محل قرارگیری آنها و سایر جزئیات مربوط به هر پیوت تیبل ارائه میکند.
فرض کنید منبع داده یک پیوت تیبل، فهرستی از اطلاعات یا جدولی در یکی از کاربرگهای همان فایل اکسل باشد. در این صورت، ماکرو جزئیات مربوط به آن منبع داده را نیز نمایش خواهد داد.
مشکل نمایش دادههای تکراری در پیوت تیبل
هنگام ایجاد یک پیوت تیبل، ممکن است با موارد تکراری در آن مواجه شویم. پیوت تیبل در ابتدا دادههای مشابه را در گروههای مشخصی دستهبندی و مجموع مقادیر هر گروه را محاسبه میکند. گاهی اوقات، همین موضوع میتواند باعث بروز مشکلاتی در نمایش دادهها شود.
راهحل:
برای برطرف کردن مشکل دادههای تکراری در پیوت تیبل، میتوانیم از روش پاکسازی دادهها استفاده کنیم.
تا حد زیادی میتوان این مشکل را با پاکسازی دادههای منبع برطرف کرد. بااینحال، وجود فاصلههای اضافی در انتهای متن ممکن است شناسایی دادههای مشکلدار را دشوار کند. زیرا فیلترهای موجود در جدول اکسل این فاصلهها را در نظر نمیگیرند و ممکن است دادههایی را که در ظاهر یکسان به نظر میرسند، بهدرستی تشخیص ندهیم.
در چنین شرایطی، میتوانیم از فیلترهای کشویی برای بررسی و اصلاح دادههای منبع استفاده کنیم. در ادامه، مراحل پاکسازی یکباره دادهها را توضیح میدهیم:
- مرحله ۱: ابتدا یک فیلتر اعمال کنید تا جدول فقط رکوردهایی را نمایش دهد که مربوط به کلمه تکراری هستند.
- مرحله ۲: سپس، برای جایگزین کردن مقدار فعلی ستون موردنظر، کلمه تکراری را در اولین رکورد وارد کنید.
- مرحله ۳: در مرحله بعد، سلول موردنظر را انتخاب کنید و نشانگر ماوس را روی دستگیره پرکردن که در گوشه پایین سمت راست سلول قرار دارد، ببرید.
- مرحله ۴: برای کپی کردن مقدار واردشده در تمام رکوردهای فیلترشده تا آخرین رکورد، کافی است روی دستگیره پرکردن دوبار کلیک کنید.
- مرحله ۵: در این مرحله، با پیمایش به سمت پایین جدول، مطمئن شوید که مقدار تمام رکوردها بهدرستی بهروزرسانی شده است.
- مرحله ۶: سپس، فیلتر ستون موردنظر را حذف کنید تا تمام دادههای جدول دوباره نمایش داده شوند.
- مرحله ۷: در نهایت، با بهروزرسانی پیوت تیبل (Refresh)، موارد تکراری برطرف خواهند شد؛ همانطور که در تصویر زیر مشاهده میکنید.
همانند دادههای متنی، ممکن است مقادیر عددی نیز در پیوت تیبل بهصورت تکراری نمایش داده شوند.
از آنجا که اکسل برای ذخیره و پردازش اعداد از روش محاسباتی ممیز شناور (Floating-Point Precision) استفاده میکند، گاهی ممکن است اختلافهای بسیار جزئی و نامحسوسی بین اعداد وجود داشته باشد. این اختلافها در نگاه اول قابل تشخیص نیستند، اما میتوانند باعث شوند مقادیر عددی مشابه در بخش ردیفهای پیوت تیبل (Row Area) بهصورت جداگانه نمایش داده شوند.
راهحل:
برای برطرف کردن این مشکل، میتوان از تابع ROUND استفاده کرد. این تابع تمام اعداد را تا تعداد مشخصی از رقمهای اعشار گرد میکند. با انجام این کار، اختلافهای جزئی بین اعداد که به دلیل نحوه ذخیرهسازی آنها در اکسل به وجود آمدهاند، از بین میروند و در نتیجه، مشکل نمایش مقادیر تکراری در پیوت تیبل برطرف میشود.
مسعود اسدپور
تحلیل شبکههای اجتماعی
مسیر یادگیری مهارت علم داده با آکادمی همراه اول
اگر به دنبال یادگیری علم داده و کسب مهارتهای موردنیاز این حوزه هستید، آکادمی همراه اول میتواند یکی از گزینههای مناسب برای شروع و توسعه این مسیر باشد. در بخش علوم داده این آکادمی، مجموعهای از دورهها برای آشنایی با مفاهیم و ابزارهای مختلف داده ارائه شده است و در مجموع ۳۱ دوره با ۲۳۹ ساعت آموزش در این دسته قرار دارد.
این مسیر آموزشی حوزههای مختلفی از علم داده را پوشش میدهد؛ برای مثال، در بخش تحلیل داده دورههایی مانند «مصورسازی و تحلیل داده با Tableau» و «داستانسرایی داده» به یادگیری بهتر نحوه تحلیل و ارائه نتایج دادهها کمک میکنند. همچنین برای افرادی که به حوزه مهندسی داده علاقهمند هستند، دورههایی مانند «مبانی و معماری ClickHouse» و «پیادهسازی انبار داده با استفاده از SSIS» ارائه شده است. در کنار این دورههای تخصصی، وبینارهای رایگانی مانند «معرفی مسیر شغلی تحلیلگر داده» و «معرفی مسیر شغلی مهندس داده» نیز وجود دارد که میتوانند برای آشنایی با مسیرهای شغلی این حوزه مفید باشند.
بنابراین میتوانید متناسب با هدف خود، مسیر یادگیریتان را انتخاب کنید. اگر به تحلیل و تفسیر دادهها علاقه دارید، مسیر تحلیلگر داده میتواند نقطه شروع مناسبی باشد و اگر به ساخت زیرساختهای داده، انبار داده و پردازش اطلاعات علاقهمند هستید، میتوانید به سمت مهندسی داده حرکت کنید. آشنایی با ابزارهایی مانند Tableau، ClickHouse و SSIS نیز میتواند در کنار دانش تئوری، مهارتهای کاربردی شما را برای ورود به بازار کار تقویت کند.
در نهایت، آکادمی همراه اول علاوه بر دورههای آموزشی، دورههای کارآموزی نیز برگزار میکند و هدف خود را تربیت و توانمندسازی نیروی انسانی برای اکوسیستم دیجیتال و کاهش فاصله میان آموزش آکادمیک و فضای واقعی کار عنوان کرده است.
نتیجهگیری
پیوت تیبل در اکسل یکی از ابزارهای کاربردی برای مدیریت و تحلیل حجم زیادی از دادههاست. با استفاده از این ابزار میتوانید اطلاعات خام را به گزارشهایی منظم و قابل فهم تبدیل کنید، دادهها را بر اساس معیارهای مختلف دستهبندی و فیلتر کنید و محاسباتی مانند مجموع، میانگین و تعداد را بهسرعت انجام دهید. همچنین قابلیتهایی مانند Slicer و Timeline امکان تعامل بیشتر با گزارشها و بررسی دقیقتر دادهها را فراهم میکنند.
در نهایت، اگر دادههای خود را پیش از ایجاد پیوت تیبل بهدرستی آماده کنید و با بخشهای مختلف آن آشنا باشید، میتوانید در زمان کوتاهتری به اطلاعات مهم و موردنیاز خود دست پیدا کنید و تصمیمگیریهای دقیقتری بر اساس دادهها داشته باشید.
پرسشهای متداول (FAQ)
در این بخش از مطلب به تعدادی از پرسشهای پرتکرار در مورد پیوت تیبل در اکسل پاسخ میدهیم.
۱. پیوت تیبل در اکسل چیست و چه کاربردی دارد؟
پیوت تیبل ابزاری برای خلاصهسازی، دستهبندی و تحلیل سریع حجم زیادی از دادهها در اکسل است. با استفاده از آن میتوانید اطلاعات را بر اساس معیارهای مختلف مرتب و فیلتر کنید و محاسباتی مانند مجموع، میانگین و تعداد را بدون نیاز به نوشتن فرمولهای پیچیده انجام دهید.
۲. چگونه در اکسل پیوت تیبل بسازیم؟
ابتدا دادههای خود را بهصورت منظم و در قالب سطرها و ستونها آماده کنید. سپس از تب Insert گزینه PivotTable را انتخاب کنید. پس از تعیین محدوده دادهها و محل ایجاد پیوت تیبل، روی OK کلیک کنید. در نهایت، با استفاده از پنل PivotTable Field List، فیلدهای موردنظر را در بخشهای Rows ،Columns ،Values و Filters قرار دهید.
۳. چگونه اطلاعات پیوت تیبل را پس از تغییر دادههای اصلی بهروزرسانی کنیم؟
برای بهروزرسانی اطلاعات، روی یکی از سلولهای پیوت تیبل کلیک و از تب PivotTable Analyze گزینه Refresh را انتخاب کنید. با این کار، تغییرات اعمالشده در دادههای منبع در پیوت تیبل نیز منعکس میشوند.
۴. تفاوت پیوت تیبل و پیوت چارت (Pivot Chart) در اکسل چیست؟
پیوت تیبل برای خلاصهسازی، دستهبندی و تحلیل دادهها در قالب جدول استفاده میشود. اما پیوت چارت همین اطلاعات را به شکل نمودارهای بصری نمایش میدهد. این دو ابزار میتوانند به یکدیگر متصل باشند و با تغییر فیلترها یا دادههای پیوت تیبل، نمودار نیز بهروزرسانی شود.
منابع