Pivot Table in Excel- جدول محوری در اکسل به همراه مثال کاربردی

Pivot Table in Excel
به همراه مثال کاربردی

مقدمه

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

قدم 1: آماده‌سازی داده‌ها

قبل از ایجاد جدول محوری، باید داده‌های مورد نظر را در اکسل آماده کنیم. اطمینان حاصل کنید که داده‌هایتان در یک جدول یا محدوده قرار دارند و هر ستون یک متغیر یا فیلد مختلف را نمایندگی می‌کند.

قدم 2: ایجاد جدول محوری

1. انتخاب داده‌ها: انتخاب کنید که کدام بخش از داده‌ها می‌خواهید در جدول محوری استفاده کنید.
2. Insert (درج): در منوی “Insert” روی “Pivot Table” کلیک کنید.
3. تعیین منبع داده: اگر داده‌ها در یک محدوده قرار دارند، محدوده را انتخاب کنید و سپس روی OK کلیک کنید.

قدم 3: تنظیمات جدول محوری

1. مکان‌یابی متغیرها: متغیرهایی که می‌خواهید در جدول محوری استفاده کنید را به بخش‌های Rows (سطرها) و Columns (ستون‌ها) بکشید.
2. محاسبات و توابع: اگر نیاز به محاسبات خاص دارید، می‌توانید از توابع مانند Sum، Count، Average و … استفاده کنید.

قدم 4: تحلیل داده‌ها

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

Pivot Table در اکسل

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

چطور Pivot Table را ساده و سریع یاد بگیریم؟

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

Pivot Table به چه معناست؟

قبل از هر چیز خوب است که بدانیم Pivot Table به چه معناست. Pivot Table به معنای جدول چرخشی هست که دلیل این نامگذاری این هست که به راحتی و با Drag کردن موس می‌توانید طراحی جدول مورد نظرتان را بچرخانید. بله دقیقاً بچرخانید!

این موضوع بعد از ارائه یک مثال کاملاً روشن خواهد شد.

چطور یک Pivot Table ایجاد کنیم؟

داده‌های زیر را در نظر بگیرید که در یک Table ذخیره شده‌اند. ذخیره کردن داده‌ها در Table پیش نیازی اصولی برای ایجاد یک Pivot Table است.

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

خب! جدولی داریم که از ۳ ستون به شرح زیر تشکیل شده است:

• ستون فروشنده
• ستون مبلغ فروش، و
• ستون فصل

قصد داریم که از این جدول یک پیوت تیبل تهیه کنیم. برای ایجاد پیوت تیبل گام‌های زیر را طی کنید.

گام اول ایجاد پیوت تیبل

هر کجای این جدول که دوست دارید کلیک کنید. و سپس در تب Insert گزینه Pivot Table را کلیک کنید.

گام دوم ایجاد پیوت تیبل

در گام دوم، پنجره زیر باز می‌شود.

این پنجره نشان می‌دهد جدولی به نام Table1 انتخاب شده، و قرار است در یک New Worksheet یا شیت جدید، یک Pivot Table از آن ایجاد شود. فقط کافی است که OK را بزنید.

گام سوم ایجاد پیوت تیبل

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

راهنمای Fields Pane

• شماره ۱) به این پنجره Fields Pane یا صفحه فیلدها گفته می‌شود.
• شماره ۲) به این بخش از پنجره، Fields List یا لیست فیلدها گفته می‌شود.

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

• شماره ۳ تا ۶) به این بخش‌ها، Fields Areas یا ناحیه فیلدها گفته می‌شود.

فیلدهای موجود در لیست فیلدها به این بخش‌ها منتقل می‌شوند، تا یک Pivot Table ایجاد شود. چهار ناحیه که به ترتیب شامل: ناحیه Filters، ناحیه Rows، ناحیه Columns، و ناحیه Values است. از بین این ناحیه‌ها، 3 مورد آخر بسیار پر استفاده‌ تر است.

• شماره ۷) به این دکمه Tool گفته می‌شود که برای تغییر ترتیب نمایش بخش‌های همین پنجره استفاده می‌شود.

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

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

شماره ۹) اگر تیک گزینه Defer Layout Update (معوق کردن بروز رسانی چیدمان یا طرح بندی) را بزنید، بعد از هر تغییر در چیدمان فیلدها باید، دکمه Update را که روشن می‌شود، بزنید تا اعمال آن بر Pivot Table لحاظ شود.

حال که با این پنجره آشنا شدید، گام‌های بعدی ایجاد پیوت تیبل را طی می‌کنیم.

گام چهارم ایجاد پیوت تیبل

در این گام باید فیلدهای موجود در Fields List را انتخاب، و به Fields Areas منتقل کنید. اما چطور؟

برای این مرحله ۲ روش را معرفی می‌کنیم:

• روش اول: کشیدن یک فیلد از Fields List و رها کردن در یکی از بخش‌های Fields Areas
• روش دوم: تیک کردن یک فیلد در Fields List

روش کشیدن و رها کردن یک فیلد

در این روش شما دقیقاً می‌دانید که هر فیلد باید در چه ناحیه‌ای از جدول قرار گیرد.

به عنوان مثال: ناحیه Rows، ناحیه‌ای است که اگر فیلد فروشنده را در این بخش قرار دهید، نام هر فروشنده در یک سطر از Pivot Table شما قرار می‌گیرد.

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

روش تیک کردن یک فیلد

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

به عنوان مثال: اگر نوع داده فیلد انتخابی شما متنی باشد (مثل نام فروشنده یا فصل) باشد. آن را در ناحیه Rows قرار می‌دهد. اما اگر فیلد انتخابی عددی باشد، آن را در قسمت Values قرار می‌دهد.

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

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

توابع Pivot Table در ناحیه Values

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

همانطور که در تصویر بالا می‌بینید، امکان تغییر محاسبه با توابع زیر وجود دارد:

• تابع Count یا شمارش/تعداد
• تابع AVERAGE یا میانگین
• تابع MAX یا بزرگترین
• تابع Min با کوچکترین
• تابع Product یا ضرب
• تابع Count Number یا شمارش/تعداد اعداد
• و توابع واریانس (VAR) و انحراف معیار (STD)

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

به عنوان مثال: اگر تابع Count انتخاب شود. تعداد دفعاتی که مبلغ فروش هر فروشنده، دارای یک مقدار مبلغ (و نه خالی) برای یک فروشنده استخراج می‌کند و در مقابل نام همان فروشنده قرار می‌دهد.

استفاده همزمان ناحیه Rows و Columns 

مثال قبل را در نظر بگیرید، اگر فروش هر فروشنده به تفکیک فصل را داشته باشیم، باید فیلد فصل را در ستون قرار دهیم. یعنی همزمان با فیلد فروشنده در ردیف! به تصویر زیر توجه کنید:

همانطور که در تصویر بالا می‌بینید، با اضافه کردن فیلد فصل به ناحیه Columns، بلافاصله گزارش موردنظر ایجاد می‌شود. به این نوع از گزارش که همزمان از سطرها و ستون‌ها برای گزارش اعداد استفاده می‌شود، گزارش ماتریسی گفته می‌شود.

گزارش خود را بچرخانید: مفهوم Pivot

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

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

سادگی Pivot Table

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

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

نتیجه‌گیری

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

همچنین بیشتر بخوانید:

1. پیش‌بینی آینده با تابع FORECAST در اکسل 2021
2. کار با تابع AVERAGEIFS در اکسل
3. تابع AVERAGEIF و عملکرد آن در اکسل
4. کاربرد تابع Average میانگین در اکسل 2019
5. ابزارهای گروه Style و کارکرد آنها در اکسل