آموزش گزارش گیری در اکسل با ساده ترین روش موجود

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

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

جدول محوری را می توان به عنوان یک گزارش در نظر گرفت. اما برخلاف گزارش استاتیک، یک نمای تعاملی از داده ها ارائه می دهد. در واقع، شما می توانید به روش های مختلف بدون فرمول به داده ها نگاه کنید، آنها را بر اساس سال و ماه مرتب کنید، فیلتر کنید و حتی نمودار ایجاد کنید. بنابراین یک جدول محوری تجزیه و تحلیل داده ها را آسان تر و موثرتر می کند.

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

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

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

• مقایسه کل فروش محصولات مختلف

فرض کنید صفحه‌ای حاوی داده‌های فروش ماهانه برای سه محصول مختلف – product1، product2 و product3 دارید و می‌خواهید بدانید کدام محصول بیشترین درآمد را داشته است. تصور کنید صفحه فروش ماهانه شما هزاران ردیف داشته باشد. مرتب کردن همه آنها به صورت دستی می تواند یک عمر طول بکشد. با استفاده از جدول محوری می توانید تمام ارقام فروش محصول 1، محصول 2 و محصول 3 را در کمتر از یک دقیقه جمع آوری کرده و هزینه های مربوطه را محاسبه کنید.

• فروش محصول را به صورت درصدی از کل فروش نمایش دهید

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

با یک جدول محوری، می‌توانید هر ستون را طوری پیکربندی کنید که درصد ستون کل هر سه ستون را به جای کل ستون به شما نشان دهد. به عنوان مثال، اگر فروش سه محصول در مجموع 200000 و محصول 1 دارای درآمد 45000 باشد، با جدول محوری می توانید آن را ویرایش کنید تا نشان دهید که این محصول 22.5 درصد از کل فروش شرکت را داشته است.

برای نشان دادن فروش محصول به صورت درصدی از کل فروش در جدول محوری، کافیست روی سلول حاوی کل فروش کلیک راست کرده و از منوی Show Values ​​As، % Grand Total را انتخاب کنید.

5 قانون اساسی قبل از ساخت Pivot Table در اکسل

قبل از شروع به یادگیری نحوه ایجاد یک جدول محوری برای گزارش در اکسل، مهم است که این 5 قانون را بدانید:

1- مجموعه داده ها باید به صورت ردیفی مرتب شوند، به طوری که هر ردیف یک رکورد را نمایش دهد.

2- هر ستون در مجموعه داده باید دارای یک هدر منحصر به فرد باشد که فیلد را مشخص می کند.

3- هیچ سطر یا ستونی خالی نباشد.

4- تمام سلول های خالی باید حاوی صفر یا متن باشند (مانند مقدار تهی)

5- نباید هیچ سلول ادغام شده ای در مجموعه داده وجود داشته باشد.

بهتر است از داده های خام برای ایجاد جدول محوری برای تجزیه و تحلیل داده ها استفاده کنید. زیرا استفاده از جدول محوری برای داده های از پیش خلاصه شده باعث می شود نتواند برش ها و نماهای جدید و متفاوتی از داده ها تولید کند. پس بهتر است خلاصه ها را حذف کنید.

ایجاد یک Pivot Table در اکسل

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

1- با انتخاب سلول مورد نظر شروع کنید.

2- در تب Insert در قسمت Tables بر روی PivotTable کلیک کنید.

3- در پنجره باز شده بر روی پیکان Select a table or range کلیک کنید.

4- تمام سلول های مورد نظر، از جمله سلول های حاوی نام ستون را انتخاب کنید، سپس “فلش رو به پایین” را فشار دهید:

5- برای نمایش جدول در یک کاربرگ جدید، “کاربرگ جدید” را انتخاب کرده و سپس OK کنید:

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

اکنون یک صفحه گسترده جدید خواهید دید که در آن می توانید فیلدهای جدول محوری خود را انتخاب کنید:

در ادامه روش گزارش گیری و تحلیل داده ها را بر روی یک مثال نشان می دهیم.

مثال 1: کل فروش به ازای هر کارمند

بیایید ببینیم چگونه می توانید جدول محوری ایجاد کنید که کل فروش هر کارمند را نشان می دهد:

• ابتدا نام کارمندان را از قسمت Name of Employee به کادر Rows در سمت راست صفحه بکشید:

می بینید که اسامی همه کارمندان در جدول سمت چپ صفحه نشان داده شده است:

• مقدار فروش را از قسمت Sales به قسمت Values ​​بکشید.

همانطور که می بینید کل فروش (Sum of Sales) مربوط به هر کارمند (Row Labels) در جدول محوری نمایش داده می شود.

همچنین توجه داشته باشید که مبلغ کل با مجموع کل کل کارکنان در پایین جدول نشان داده شده است:

مثال 2: کل فروش بر اساس کشور

حال بیایید کل فروش را بر اساس قسمت کشور بررسی کنیم.

ابتدا باید فیلد Name of Employee را از جدول حذف کنید:

فیلد Country را به کادر Rows بکشید:

جدول محوری اکنون فروش را بر اساس کشور نشان می دهد:

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