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

آموزش Pivot Table در اکسل

How to Make a Pivot Table in Excel

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

کاربرد آموزش صفر تا صد Pivot Table

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

با این اطلاعات می توانید به شناسایی و پاسخگویی سوالات پیش بینی نشده پیرامون داده ها کمک کنید. در ادامه ۷ سناریو که در آنها Pivot Table مفید واقع می شود لیست شده است:

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

نمایش فروش محصول به عنوان درصدی از کل فروش: جداول محوری به طور ذاتی مجموع هر سطر یا ستون را هنگام ایجاد نشان می دهند. با این حال، این تنها رقمی نیست که می توانید به طور خودکار تولید کنید. با یک جدول محوری، به جای فقط کل ستون، می توانید هر ستون را طوری پیکربندی کنید که درصد ستون از کل ستون ها را به شما بدهد. برای نشان دادن فروش محصول به صورت درصدی از کل فروش در جدول محوری، کافی است روی سلولی که کل فروش را نشان می دهد کلیک راست کرده و Show Values ​​As > % of Grand Total را انتخاب کنید.

ترکیب داده های تکراری: به جای جستجوی دستی و ترکیب تمام معیارهای موارد تکراری، می توانید داده های خود را از طریق جدول محوری ترکیب کنید.

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

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

Pivot Table در اکسل چیست و چرا مهم است؟

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

فرض کنید که شما یک مجموعه داده مانند تصویر زیر دارید.

این داده ها در مورد فروش است و از ۱۰۰۰ سطر تشکیل شده است.

داده ها فروش بر اساس منطقه، نوع خرده فروش و مشتری است.

حالا ممکن است رئیس شما بخواهد از این داده ها چند چیز را درک کند.

مجموع فروش در منطقه جنوب در سال ۲۰۱۶ چقدر بوده است؟

۵ خرده فروش که فروش بالایی داشته اند کدام ها بوده اند؟

عملکرد The Home Depot در مقایسه با سایر خرده فروشان در جنوب چگونه بوده است؟

شما می توانید از توابع اکسل برای پاسخ دادن به این سوالات استفاده کنید اما اگر ناگهان رئیس شما لیستی از ۵ سوال دیگر را ارائه دهد چه؟

هر بار که تغییری ایجاد می شود باید به داده ها برگردید و فرمول های جدیدی را استفاده کنید.

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

در عرض چند ثانیه، یک جدول محوری یا Pivot Table به همه این سؤالات پاسخ می دهد. امیدواریم با این مثال ساده در مورد کاربرد Pivot Table در اکسل درکی پیدا کرده باشید.

نحوه ساخت Pivot Table در اکسل

در ادامه مراحل ساخت Pivot Table در اکسل را توضیح خواهیم داد. در اینجا از داده های جدول بالا استفاده شده است.

در جایی از دیتاشیت خود کلیک کنید.

به مسیر Insert –> Tables –> Pivot Table بروید.

در صفحه Create Pivot Table گزینه های پیش فرض در بیشتر موارد به خوبی کار می کنند:

Table/Range به طور پیش فرض بر اساس مجموعه داده یا دیتا شیت شما پر می شود. اگر داده های شما سطر یا ستون خالی نداشته باشد اکسل به طور خودکار محدوده صحیح را شناسایی می کند. در صورت نیاز می توانید این مورد را به صورت دستی تغییر دهید.

اگر می خواهید پیوت تیبل را در یک مکان خاص ایجاد کنید در زیر گزینه Choose where you want the PivotTable report to be placed مکان را مشخص کنید. در غیر این صورت، یک ورک شیت جدید با Pivot Table ایجاد می شود.

روی OK کلیک کنید.

به محض اینکه روی OK کلیک کنید یک ورک شیت جدید با Pivot Table ایجاد می شود.

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

نحوه کار با کامپوننت ها بعد از ایجاد Pivot Table در اکسل

برای استفاده کارآمد از Pivot Table درک کامپوننت ها یا اجزای سازنده یک پیوت تیبل بسیار مهم است. در این بخش با موارد زیر آشنا خواهید شد:

• Pivot Cache
• Values Area
• Rows Area
• Columns Area
• Filters Area

Pivot Cache

به محض ایجاد یک Pivot Table با استفاده از داده ها، اکسل یک اسنپ شات از داده ها می گیرد و در حافظه خود ذخیره می کند. این اسنپ شات Pivot Cache نام دارد.

هنگامی که دیدهای (View های) مختلف را با استفاده از Pivot Table ایجاد می کنید، اکسل به منبع داده باز نمی گردد، بلکه از Pivot Cache برای تجزیه و تحلیل سریع داده ها و ارائه خلاصه/نتایج به شما استفاده می کند.

دلیل ایجاد حافظه کش Pivot Cache، بهینه سازی عملکرد جدول محوری یا Pivot Table است. حتی زمانی که هزاران سطر داده دارید، جدول محوری در خلاصه کردن داده ها بسیار سریع است. می‌توانید آیتم ها را به باکس های Rows/Columns/Values/Filters بکشید و رها کنید، نتایج فورا بروزرسانی می شود.

توجه: یکی از نقاط ضعف حافظه Pivot Cache این است که اندازه ورک بوک شما را افزایش می دهد. از آنجایی که این یک کپی از داده های منبع است، هنگامی که یک جدول محوری ایجاد می کنید، یک کپی از آن داده ها در Pivot Cache ذخیره می شود.

Values Area

Values Area چیزی است که محاسبات / مقادیر را نگه می دارد. بر اساس مجموعه داده های نشان داده شده در ابتدای این مطلب، اگر می خواهید به سرعت کل فروش را بر اساس منطقه در ماه محاسبه کنید می توانید یک جدول محوری مطابق شکل زیر دریافت کنید.

ناحیه ای که با رنگ نارنجی مشخص شده است Values Area است.

در این مثال Pivot Table در اکسل ، کل فروش هر ماه برای ۴ منطقه نشان داده شده است.

Rows Area

Rows Area بخش سمت چپ Value Area  را تشکیل می دهد. در مثال زیر، Rows Area شامل بخش هایی است که با کادر قرمز مشخص شده است.

Columns Area

Columns Area بالای Value Area  را تشکیل می دهد. در مثال زیر، Columns Area شامل ماه ها است.

Filters Area

Filters Area یک فیلتر اختیاری است که می توانید از آن برای بررسی بیشتر مجموعه داده های خود استفاده کنید. به عنوان مثال اگر می خواهید فقط فروش خرده فروشان را مشاهده کنید می توانید این گزینه را از منوی کشویی انتخاب کنید و بروزرسانی Pivot Table در اکسل یا داده ها را فقط برای خرده فروشان Multiline انجام دهید.

تجزیه و تحلیل و محاسبات در Pivot Table

حالا اجازه دهید با استفاده از Pivot Table که ایجاد کردیم به سوالات پاسخ دهیم.

برای دانلود نمونه داده ها اینجا کلیک کنید و ادامه دهید.

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

در بخش Pivot Table Fields، شما Fields و Areas را دارید (همانطور که در زیر مشخص شده است):

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

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

حالا بیایید به سوالاتی که در ابتدای مقاله مطرح کردیم پاسخ دهیم:

کل فروش در منطقه جنوب چقدر است؟

فیلد Region را در فیلد Rows و فیلد Revenue را به Values area بکشید. به طور خودکار جدول محوری شما به روز می شود.

توجه داشته باشید که به محض اینکه فیلد Revenue را در Values area رها می کنید تبدیل به Sum of Revenue می شود. به طور پیش فرض، اکسل تمام مقادیر یک منطقه معین را جمع می کند و مقدار کل را نشان می دهد. اگر بخواهید می توانید این مقدار را به میانگین یا سایر معیارهای آماری تغییر دهید. پاسخ این سوال ۲۱۲۲۵۸۰۰ خواهد بود.

۵ خرده فروش که فروش بالاتری داشتند کدامند؟

فیلد Customer را در Row area و فیلد Revenue را در Values Area بکشید. در صورتی که فیلدهای دیگری در قسمت area وجود دارد و می خواهید آن را حذف کنید، کافی است آن را انتخاب کرده و به بیرون بکشید و رها کنید. شما یک Pivot Table مانند شکل زیر دریافت خواهید کرد:

توجه داشته باشید که به طور پیش فرض، آیتم ها (در این مورد مشتریان) به ترتیب حروف الفبا مرتب شده اند.

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

روی یکی از سلول های ناحیه Values Area کلیک راست کنید.

برای مرتب کردن داده ها در اکسل به مسیر Sort –> Sort Largest to Smallest بروید.

با این کار شما یک لیست مرتب شده بر اساس فروش کلی دریافت خواهید کرد.

عملکرد The Home Depot در مقایسه با سایر خرده فروشان در جنوب چگونه بود؟

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

Region Field را در Rows area بکشید. حالا قسمت Customer را در Rows area زیر قسمت Region بکشید. وقتی این کار را انجام می‌دهید، اکسل متوجه می‌شود که می‌خواهید داده‌های خود را ابتدا بر اساس منطقه و سپس بر اساس مشتریان در منطقه دسته‌بندی کنید. چیزی مطابق شکل زیر خواهید داشت:

حالا فیلد Revenue را در Values area ​​بکشید و فروش هر مشتری (و همچنین منطقه کلی) را خواهید داشت.

با دنبال کردن مراحل زیر می توانید خرده فروشان را بر اساس ارقام فروش مرتب کنید:

روی سلولی که دارای مقدار فروش برای هر خرده فروش است کلیک راست کنید و به مسیر Sort –> Sort Largest to Smallest بروید.

با این کار فورا تمام خرده فروشان بر اساس مقدار فروش مرتب خواهند شود.

حالا می توانید به سرعت از طریق منطقه جنوب مقادیر را چک کنید و تشخیص دهید که فروش The Home Depot معادل ۳۰۰۴۶۰۰ بوده است و بهتر از چهار خرده فروش در منطقه جنوبی بوده است.

همچنین می توانید قسمت Filter را بر اساس منطقه فیلتر کنید.

نظرات و پیشنهادات شما؟

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