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

اکسل یک ابزار قدرتمند برای گزارشگری است. یکی از ساده ترین راه های ساخت گزارش در اکسل استفاده از پیوت تیبل (pivot table) است که به شما امکان می دهد داده های خود را به سادگی با کشیدن و رها کردن فیلدها مرتب سازی، گروه بندی و خلاصه کنید.

آموزش ویدیویی

اگر ویدیوی بالا راضی کننده نبود به خواندن ادامه دهید:

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

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

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

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

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

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

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

فرض کنید یک صفحه شامل داده های فروش ماهانه برای سه محصول مختلف دارید- محصول۱، محصول۲ و محصول۳- می خواهید بفهمید کدام محصول بیشترین درآمد را داشته است. تصور کنید که صفحه فروش ماهانه شما هزاران ردیف داشته باشد. مرتب سازی دستی همه آنها می تواند یک عمر طول بکشد. با استفاده از یک پیوت تیبل می توانید در کمتر از یک دقیقه تمام رقم های فروش محصول۱، محصول۲ و محصول۳ را جمع کنید و هزینه های مربوطه را محاسبه کنید.

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

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

با یک پیوت تیبل می توانید هر ستون را پیکربندی کنید تا درصد ستون از مجموع هر سه ستون را به جای مجموع ستون به شما نشان دهد. به طور مثال اگر فروش سه محصول در مجموع ۲۰۰۰۰۰ باشد و محصول۱، درآمد ۴۵۰۰۰ داشته باشد، با پیوت تیبل می توانید آن را طوری ویرایش کنید که نشان دهد این محصول ۲۲٫۵ درصد از کل فروش شرکت را داشته است.

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

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

قبل از شروع به یادگیری ساخت یک پیوت تیبل برای گزارشگیری در اکسل، شناخت این ۵ قانون مهم است:

۱- مجموعه داده ها باید در ردیف ها سازماندهی شده باشند، به صورتیکه هر ردیف یک رکورد را نمایش دهد.

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

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

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

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

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

۴۰ کلید میانبر در اکسل که سرعت کارتان را دو برابر می کنند

ساخت Pivot Table در اکسل

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

۱- با انتخاب یک سلول دلخواه شروع کنید.

۲- در تب Insert در بخش Tables روی PivotTable کلیک کنید.

۳- در پنجره باز شده، روی پیکان قسمت Select a table or range کلیک کنید.

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

۵- برای نمایش جدول در یک صفحه کار جدید،‘New Worksheet’ را انتخاب و سپس OK کنید:

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

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

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

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

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

۱- ابتدا نام کارمندان را از فیلد Name of Employee به جعبه Rows در سمت راست صفحه بکشید:

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

۲- میزان فروش را از فیلد Sales به جعیه Values بکشید.

همانطور که مشاهده می کنید کل فروش (Sum of Sales) مربوط به هر یک از کارمندان (Row Labels) در پیوت تیبل نمایش داده شده است.

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

لیست ۵۰ تا از کاربردی ترین فرمول های اکسل (توابع مهم)

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

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

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

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

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

دانلود رایگان آموزش صفر تا صد اکسل (فیلم فارسی+PDF)