آموزش pivot table در اکسل

یکی از قابلیت‌های پرکاربرد و محبوب در اکسل، Pivot Table است. شاید بتوان گفت این ابزار سریع‌ترین و آسان‌ترین راه برای آنالیز داده‌ها و اطلاعات محسوب می‌شود. با این‌حال هنوز بسیاری از کاربران اکسل نمی‌دانند چطور باید با آن کار کنند یا چون فکر می‌کنند ابزار پیچیده‌ای است قید آموزش آن را زده‌اند. در حالی‌که در ابزار Pivot Table با کمترین تلاش، می‌توان به نتایج ارزشمندی رسید.

در این مقاله قصد داریم به انواع کاربرد Pivot Table در اکسل و آموزش استفاده از آن بپردازیم اما پیش از شروع بیایید ببینیم که:

pivot table چیست؟

اگر حجم بالای اطلاعات در یک فایل اکسل حسابی گیجتان کرده‌است یا برای ارائه آن‌ها به دنبال یک راه حل مناسب هستید pivot table به کمکتان می‌آید. این ابزار به خلاصه کردن، نظم بخشیدن و محاسبات کمک می‌کند و برای مقایسه اطلاعات و پیدا کردن الگوها نیز کاربرد دارد. Pivot به معنای محور است بنابراین می‌توان گفتPivot Table یعنی جدول محوری. یعنی جدولی که محورهای آن می‌توانند بچرخند و جابجا شوند. بگذارید به جای توضیح بیشتر، به آموزش pivot table بپردازیم و این ابزار را در عمل بشناسیم.

آموزش pivot table در اکسل

رسم پیوت تیبل چند مرحله دارد که اگر آن‌ها را به‌درستی طی کنید بعد از چند بار تمرین می‌توانید در رسم این جدول‌ها به مهارت برسید. ممکن است در شروع کار احساس سردرگمی کنید اما نگران نباشید، بعد از رسم چند جدول کاملا مهارت پیدا می‌کنید. بیایید آموزش تصویری Pivot Table در اکسل را با مثال اول شروع کنیم:

اولین تمرین Pivot Table در اکسل

فرض کنید اطلاعات مربوط به 20 سریال برتر از دید IMDB در اختیار ما قرار دارد. این داده‌ها شامل نام سریال، سال شروع و پایان، تعداد قسمت‌ها و رتبه IMDB است. حالا قصد داریم جدول محوری آن را رسم کنیم:

1. تنظیم داده‌ها

در قدم اول برای تنظیم داده‌ها، آن‌ها را در یک جدول منظم می‌کنیم. این مرحله بسیار مهم است. داده‌های شلخته و نامنظم سردرگمی شما را بیشتر و کار رسم Pivot Table را سخت‌تر می‌کند.

2.تعیین محدوده

حالا از منوی بالا، وارد تب Insert می‌شویم و از قسمت Tables، گزینه Pivot Table را انتخاب می‌کنیم. در این قسمت باید محدوده جدول مورد نظر را انتخاب کنیم. برای انتخاب این محدوده می‌توانید روی جدول، از سلول اول بکشید و تا سلول انتهایی ماوس را جلو ببرید. به این شکل چارچوب جدول مورد نظر انتخاب می‌شود. همچنین می‌توانید از فرمول زیر در قسمت Table/Range استفاده کنید:

'(نام صفحه)'!$(ستون سلول اول)$(ردیف سلول اول):$(ستون سلول آخر)$(ردیف سلول آخر)

که در مثال مورد بررسی ما به این شکل خواهد بود:

'20 سریال برتر'!$C$1:$G$21

3. تعیین صفحه

حالا باید تعیین کنیم که می‌خواهیم جدول مورد نظر در همین صفحه ساخته شود (Existing Worksheet) یا در یک صفحه جدید(New Worksheet)؟ پیشنهاد ما، ایجاد یک صفحه جدید است. در نهایت دکمه تایید را می‌زنیم.

باز شدن صفحه جدید یعنی تازه کار ما شروع شده است! حالا باید Pivot Table را رسم کنیم. در حال حاضر صفحه اکسل به دو قسمت تقسیم شده است که به بررسی آن‌ها می‌پردازیم. قسمتی که در شکل زیر به رنگ زرد مشخص شده، یعنی قسمت سمت راست صفحه، از دو بخش بالایی و پایینی تشکیل شده است. بخش بالایی تیترهایی هستند که در جدول اصلی آن‌ها را Select کردیم. حالا به‌صورت زمینه‌های مختلف برای دسته‌بندی در این قسمت قرار گرفته‌اند و می‌توانیم از بین آن‌ها یک یا چند مورد را انتخاب کنیم.

4. انتقال داده‌ها به بخش خودشان

با تیک زدن هر یک از موارد بخش بالایی، تیتر به یکی از چهار قسمت پایینی (1، 2، 3 یا 4) منتقل می‌شود. هر کدام از این قسمت‌ها کاربرد خاصی در جدول نهایی خواهند داشت:

با توجه به توضیحات قبلی، ما متغیرهای «نام اثر»، «رتبه IMDB» و «تعداد قسمت‌ها» را انتخاب می‌کنیم. برای انتخاب کردن هر کدام از موارد بخش بالایی می‌توانیم کنار آن تیک بزنیم یا آن را بکشیم و در فیلد مناسب رها کنیم. راه سوم این است که روی مورد کلیک‌راست کنیم و از گزینه‌های Add to … مورد مناسب را انتخاب نماییم. در صورتی‌که کنار موارد تیک بزنیم اکسل به‌صورت خودکار آن‌ها را به فیلد پیشنهادی خود منتقل می‌کند.

نام اثر یک داده غیرعددی است. بنابراین بهترین جایگاه برای آن قسمت ردیف‌ها است. تعداد قسمت‌ها داده‌ای عددی است و می‌خواهیم بر روی آن اعمال ریاضی انجام بدهیم. بنابراین به قسمت Value آن را منتقل می‌کنیم. به‌عنوان متغیر سوم برای سنجش، رتبه IMDB را به ستون‌ها می‌بریم.

در این قسمت گزینه‌ای برای انتخاب وجود دارد. Defer Layout Update به معنی به تاخیر انداختن اعمال تغییرات است. با جابجایی هر کدام از داده‌ها به هر یک از چهار قسمت بخش پایینی، جدول سمت چپ اکسل به‌روزرسانی می‌شود. با تیک زدن این گزینه می‌توانید به‌روزرسانی را معلق کنید و بعد از آن‌که فیلدها را به‌صورت دلخواه پر کردید دوباره این گزینه را فعال کنید.

5. و تمام

جدول مورد نظر ما ساخته شده است!

همان‌طور که می‌بینید اکسل به زیبایی داده‌ها را طبقه‌بندی کرده است. می‌توانید به‌صورت متمایز ببینید که کدام سریال‌ها رتبه 9.2 در IMDB گرفته‌اند و چند قسمت مجموعا با این رتبه وجود دارد. شاید بگویید که ما پیشتر در مقاله‌های آموزش تابع Sumif و آموزش تابع Countif به همین اطلاعات دست پیدا کرده بودیم! البته درست است اما به کمک جدول پیوت می‌توانیم چندین تابع را به‌طور هم‌زمان در قسمت‌های مختلفی از جدول به کار ببریم و نتیجه بسیار بهتر، جامع‌تر و قابل فهم‌تری داشته باشیم.

تا به اینجا آموزش Pivot Table به زبان ساده را با هم خواندیم، حالا یک مثال سخت‌تر مطرح می‌کنیم:

دومین تمرین Pivot Table در اکسل

برای آموزش حرفه ای Pivot Table، این بار اطلاعاتی گسترده‌تر با داده‌های عددی بیشتر را به جدول تبدیل می‌کنیم.

1.آماده‌سازی جدول

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

2.انتخاب Pivot Table

حالا باید مانند مثال قبلی، از تب Insert گزینه Pivot Table را بزنیم. اما پیش از آن می‌خواهیم ببینیم جدول پیشنهادی اکسل برای این مجموع داده‌ها چیست؟ بنابراین بر روی گزینه کناری، یعنی Recommended Pivot Table کلیک می‌کنیم. اکسل بازه مورد نظر را می‌پرسد و می‌توانیم آن را با کلیک کردن و کشیدن ماوس انتخاب کنیم. یادتان نرود که حتما تیترها در این سلول‌ها قرار گرفته باشند. سپس کلید ok را می‌زنیم.

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

جدول Pivot پیشنهادی اکسل به این‌شکل است.

همان‌طور که می‌بینید «میزان خرید»، «هزینه اضافه» و «میزان فروش» به‌صورت خودکار در قسمت Values قرار گرفته‌اند زیرا داده‌های عددی قابل محاسبه هستند. فصل نیز در قسمت ردیف‌ها (Rows) قرار دارد زیرا مقداری متنی و غیرعددی است. این جدول مجموع فروش، خرید و مازاد خرید در چهار فصل را به‌صورت خلاصه نمایش می‌دهد. باورکردنی نیست که تنها با چند کلیک توانسته‌ایم از حجم داده‌های نامنظم به چنین جدول خلاصه و زیبایی دست پیدا کنیم! اینطور نیست؟

3.تکمیل و ویرایش جدول

برای اینکه جدول کامل‌تری داشته باشیم می‌خواهیم یک متغیر غیرعددی دیگر به آن اضافه کنیم. بنابراین متغیر «نوع لباس» را انتخاب می‌کنیم. برای اینکه جدول خیلی پهن نشود و به همه قسمت‌های آن دسترسی داشته باشیم متغیرهای ستون و ردیف را جابجا کردیم. شکل نهایی جدول به این صورت است:

چون اطلاعات ما به زبان فارسی است بهتر است نوشتار بعضی از قسمت‌های جدول را تغییر بدهیم. مثلا با دو بار کلیک کردن بر روی سلول A4 می‌توانیم نام آن را از «Sum of میزان فروش» به «مجموع میزان فروش» تبدیل کنیم.

همچنین می‌توانیم به کمک ابزار Replace، تمامی «Sum of»ها را به «مجموع» تبدیل کنیم.

قسمت‌های دیگر که هنوز به زبان انگلیسی هستند را هم می‌توانیم برای تکمیل کردن جدول تغییر بدهیم. در نهایت تمامی داده‌ها را انتخاب می‌کنیم و فونت را نیز تغییر می‌دهیم. Pivot Table به شکل زیر است:

چه زمانی از pivot table استفاده کنیم؟

به‌طور کلی 4 موقعیت وجود دارد که استفاده از Pivot Table کمک‌کننده است. البته نمی‌توان موارد استفاده از این جدول کاربردی را به همین موارد محدود کرد اما صرفا برای اینکه کاربردهای Pivot Table بهتر و بیشتر شناخته شوند به این دسته‌بندی می‌پردازیم:

1. وقتی می‌خواهیم فروش چند محصول مختلف را مقایسه کنیم. این کار کمک می‌کند برای تخصیص منابع تصمیمات بهتری گرفته‌شود و محصولات پرطرفدار و کم‌مصرف از یکدیگر تمیز داده شوند. بعد از آن‌که در Pivot Table محصولات را بر اساس میزان فروش دسته‌بندی کردیم می‌توانیم از قسمت Sort آن‌ها را از پرفروش به کم‌فروش مرتب کنیم. محصولات بالاتر قابلیت این را دارند که در تعداد بیشتری تولید شوند و محصولات پایین‌تر نیازمند تجدید نظر هستند.
2. وقتی می‌خواهیم ببینیم هر محصولی چند درصد از فروش کل را داشته است. به‌طور کلی قابلیت مشاهده به صورت درصد یکی از ویژگی‌های کاربردی Pivot Table است. برای یک محصول خاص می‌توان مشخص کرد که چه درصدی از فروش فصلی، سالیانه، فروش کل یا حتی درصد فروش در یک دسته‌بندی خاص را به خود اختصاص داده است.
3. وقتی نیاز به تقسیم‌بندی بر اساس داده‌های تکراری داریم. در مثال دوم همین مقاله، فروش لباس‌ها در فصل‌های مختلف را دیدیم. در جدول اصلی بارها کلمات «زمستان» و «پالتو» آورده شده‌بود. با این‌حال در Pivot Table تمام داده‌های یکسان در یک دسته‌بندی خلاصه شدند.
4. وقتی داده‌های جدول مدام به‌روزرسانی می‌شوند. همان‌طور که گفتیم پیش از اقدام به ساخت Pivot Table، داده‌ها را در یک جدول اولیه منظم می‌کنیم. سپس بر اساس این جدول به ساخت پیوت تیبل می‌پردازیم. هرگاه داده‌های جدول اولیه تغییر کنند یا به تعداد آن‌ها افزوده یا از آن کم شود Pivot Table بر اساس داده‌های جدید به‌روزرسانی خواهد شد.

جمع‌بندی

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