3 بهترین روش گزارش گیری در اکسل (تبدیل داده های خام به جدول مرتب)
یک ابزار قدرتمند برای گزارشگری است. یکی از ساده ترین راه های ساخت گزارش در اکسل استفاده از پیوت تیبل (pivot table) است که به شما امکان می دهد داده های خود را به سادگی با کشیدن و رها کردن فیلدها مرتب سازی، گروه بندی و خلاصه کنید.
به زبان ساده تر گزارشگیری در اکسل یعنی اینکه اطلاعات و دادههای خام و بههمریخته را تبدیل کنید به یک جدول یا گزارش تمیز، مرتب و قابلفهم که مدیران یا خود شما بتوانید با یک نگاه، وضعیت کار را درک کنید و برای آینده تصمیم بگیرید.
ابتدا آموزش سریع آسان
۱. قبل از هر چیز مواظب باشید که اطلاعات خود را درست وارد کنید (جدولسازی)
قبل از اینکه سراغ گزارشهای پیچیده بروید، باید دادههایتان را درست در اکسل وارد کنید:
-
ستونها و ردیفها: اطلاعات هر بخش باید در ستون خودش باشد (مثلاً ستون تاریخ، ستون نام مشتری، ستون مقدار فروش و...).
-
ردیف هدر (Header): ردیف اول جدول همیشه باید عنوان ستونها باشد (مثلاً: ردیف، تاریخ، نام کالا، تعداد، قیمت).
-
تبدیل به جدول (Table): روی یکی از سلولهای اطلاعاتتان کلیک کنید و کلیدهای میانبر
Ctrl + Tرا فشار دهید. با این کار دادههای شما تبدیل به یک جدول زیبا و مهندسیشده میشوند که فیلتر هم دارند.
۲. ابزار جادویی گزارشگیری: پیوت تیبل (Pivot Table)
پیوت تیبل (Pivot Table) قلب تپنده گزارشگیری در اکسل است. با این ابزار میتوانید در چند ثانیه، هزاران سطر اطلاعات را خلاصه کنید (مثلاً بفهمید در هر ماه چقدر فروش داشتهاید یا کدام کالا بیشتر فروخته شده).
چطور از آن استفاده کنیم؟
-
روی جدول دادههای خود کلیک کنید.
-
از نوار بالای صفحه به تب Insert بروید.
-
روی گزینه PivotTable کلیک کنید و در پنجره باز شده
OKرا بزنید تا یک شیت جدید برای گزارش شما باز شود. -
در صفحه جدید، در سمت راست، کادری میبینید که نام ستونهای جدولتان در آن قرار دارد. حالا کافیست:
-
ستونهایی که میخواهید دستهبندی شوند (مثل ماهها یا نام افراد) را در کادر Rows بیندازید.
-
ستونی که اعداد و مبالغ در آن است (مثل مبلغ فروش) را در کادر Values بیندازید.
-
به همین راحتی! اکسل در چند ثانیه یک گزارش جمعبندیشده و دقیق به شما تحویل میدهد.
۳. استفاده از نمودارها (Charts) برای گزارشهای بصری
هیچکس دوست ندارد به اعداد و ارقام زیاد خیره شود. ساخت یک نمودار ساده، گزارش شما را صد برابر جذابتر و قابلفهمتر میکند.
-
جدول یا خروجی پیوت تیبل خود را انتخاب کنید.
-
به تب Insert بروید.
-
در بخش Charts، روی یک مدل نمودار (مثلاً ستونی یا دایرهای - Column یا Pie) کلیک کنید.
-
نمودار شما آماده است و با تغییر اعداد جدول، شکل آن هم بهصورت خودکار تغییر میکند.
۴. مرتبسازی و فیلتر کردن (Sort & Filter)
گاهی گزارش شما فقط نیاز به مرتب بودن دارد:
-
با استفاده از ابزار Filter (که در تب Data قرار دارد)، میتوانید فقط اطلاعات یک شخص، یک شهر یا یک ماه خاص را نمایش دهید و بقیه را موقتاً مخفی کنید.
-
با استفاده از Sort میتوانید اطلاعات را از بیشترین به کمترین (مثلاً پرفروشترین کالاها) مرتب کنید.
پس برای شروع برای اینکه اولین گزارش خود را بسازید، نیازی به فرمولهای سخت ندارید. کافی است اطلاعات را مرتب وارد کنید، با کلید Ctrl + T آن را جدول کنید و اگر دادههای حجمی دارید، از Pivot Table کمک بگیرید. تمرین روی یک فایل کوچک، تمام این مراحل را برایتان ملکه ذهن میکند.
آموزش ویدیویی
اگر ویدیوی بالا راضی کننده نبود به خواندن ادامه دهید:
Pivot Table در اکسل چیست؟
Pivot Table یکی از ابزارهای بسیار قدرتمند و مفید در اکسل است که برای کاوش و خلاصه سازی داده ها، تجزیه و تحلیل و تهیه گزارش از مجموعه داده های بزرگ به کار می رود.
یک پیوت تیبل را می توان به عنوان یک گزارش در نظر گرفت. اما برخلاف گزارش ثابت، یک نمای تعاملی از داده ها فراهم می کند. در واقع می توانید داده ها را بدون فرمول از جهات مختلف بررسی کنید، آنها را براساس سال و ماه دسته بندی کنید، فیلتر کنید و حتی نمودار بسازید. بنابراین یک پیوت تیبل، تجزیه و تحلیل داده ها را آسان تر و موثرتر می کند.
با ساخت پیوت تیبل، داده ها کم یا زیاد نمی شوند و یا تغییر نمی کنند. بلکه، دوباره سازماندهی می شوند تا بتوانید اطلاعات مفیدی به دست آورید.
نحوه استفاده از Pivot Table در اکسل
اگر در مورد کار و شیوه گزارش گیری از پیوت تیبل ها گیج شده اید، نگران نباشید. وقتی به صورت عملی آن را ببینید و به کار ببرید، درک آن بسیار راحت تر خواهد شد. در اینجا دو سناریوی فرضی برای استفاده از یک پیوت تیبل آورده شده است.
- مقایسه مجموع فروش محصولات مختلف
فرض کنید یک صفحه شامل داده های فروش ماهانه برای سه محصول مختلف دارید- محصول۱، محصول۲ و محصول۳- می خواهید بفهمید کدام محصول بیشترین درآمد را داشته است. تصور کنید که صفحه فروش ماهانه شما هزاران ردیف داشته باشد. مرتب سازی دستی همه آنها می تواند یک عمر طول بکشد. با استفاده از یک پیوت تیبل می توانید در کمتر از یک دقیقه تمام رقم های فروش محصول۱، محصول۲ و محصول۳ را جمع کنید و هزینه های مربوطه را محاسبه کنید.
- نمایش فروش محصولات به عنوان درصد از کل فروش
فرض کنید فروش سه ماهه سه محصول را در یک صفحه اکسل وارد و این داده ها را به یک جدول پیوت تیبل کرده اید. جدول به طور خودکار سه جمع در پایین هر ستون به شما نشان می دهد – که مجموع فروش سه ماهه هر محصول می باشد. اما اگر بخواهید درصد فروش یک محصول در کل فروش شرکت و نه فقط فروش کل آن محصول را پیدا کنید، چه می کنید؟
با یک پیوت تیبل می توانید هر ستون را پیکربندی کنید تا درصد ستون از مجموع هر سه ستون را به جای مجموع ستون به شما نشان دهد. به طور مثال اگر فروش سه محصول در مجموع ۲۰۰۰۰۰ باشد و محصول۱، درآمد ۴۵۰۰۰ داشته باشد، با پیوت تیبل می توانید آن را طوری ویرایش کنید که نشان دهد این محصول ۲۲٫۵ درصد از کل فروش شرکت را داشته است.
برای نشان دادن فروش محصولات به عنوان درصد کل فروش در یک پیوت تیبل، کافیست روی سلول حاوی مجموع فروش راست کلیک کرده و از منوی Show Values As گزینه % of Grand Total را انتخاب کنید.
۵ قانون اساسی قبل از ساخت یک Pivot Table در اکسل
قبل از شروع به یادگیری ساخت یک پیوت تیبل برای گزارشگیری در اکسل، شناخت این ۵ قانون مهم است:
۱- مجموعه داده ها باید در ردیف ها سازماندهی شده باشند، به صورتیکه هر ردیف یک رکورد را نمایش دهد.
۲- هر ستون در مجموعه داده ها باید یک هدر یا سرآیند منحصر به فرد داشته باشد که فیلد را مشخص می کند.
پایگاه داده SQL Server رو قورت بده! بدون کلاس، سرعت 2 برابر، ماندگاری 3 برابر، پولسازی بلافاصله ... دانلود:
۳- هیچ ردیف و ستونی نباید خالی باشد.
۴- تمام سلول های خالی باید حاوی عدد صفر یا متن باشند (مانند مقدار نال)
۵- در مجموعه داده ها نباید هیچ سلولی ادغام شده باشد.
بهتر است از داده های خام برای ایجاد پیوت تیبل برای تجزیه و تحلیل داده ها استفاده کنید. زیرا استفاده از پیوت تیبل برای داده های از قبل خلاصه شده باعث می شود تا نتواند برش ها و نماهای مختلف و جدیدی از داده ها تولید کند. بنابراین بهتر است، خلاصه ها را حذف کنید.
ساخت 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 بکشید:
اکنون پیوت تیبل فروش را بر اساس کشور نشان می دهد:
خیلی عالی بود ممنونم
پاسخسلام خسته نباشید اگر سه sheet داشته باشیم یکی به عنوان اصلی یکی هم استخدانی و دیگری ضمن خدمت اگر بخواهیم بر حسب کدپرسنلی اطلاعات دو شیت در شیت اصلی ذخیره شود و همه را یکجا گزارش بگیریم می توان از پیوت تیبل استفاده کرد
پاسخعالی بود. ممنونم
پاسخسلام و عرض خسته نباشید
پاسخچطور می تونم از اکسل برای آنالیز کابینت استفاده کنم
مثلا برا اینکه یک یونیت با ارتفاع 72 و عرض 60 و عمق 55 رو خرد کنم برای قسمت برشکاری چکار باید بکنم
سلام؛من میخواهم از یک خط تولید گزارشگیری کنم،تمام اطلاعات از plc در یک جدول در هر یک ثانیه در حال بروز رسانی می باشند، من میخواهم مدیر شرکت متوجه شود فلان محصول در فلان تاریخ چقدر تولید شده است.لطفا من را راهنمایی کنید(اطلاعات دائم در حال بروز رسانی می باشند(
پاسخعالی
پاسخو بسیار ممنونم
سلام وقت بخیر با تشکر از آموزش شما
پاسخمن یک فایل اکسل جهت انبارداری طراحی کردم که با فروش کالاهای مختلف که در جدول 1 ثبت می شوند مانده موجود انبار در جدول 2 نمایش داده شود. اما متاسفانه بعد از تغییر و اضافه کردن اطلاعات در جدول 1 ، اعداد pivot table تغییر نمی کنند و حتما باید رفرش کنم تا مانده صحیح رو نمایش بده. آیا می تونید برای رفع این مشکل راهنمایی کنید؟ با تشکر
این رفتار در Pivot Table طبیعی است، چون بهصورت پیشفرض بعد از تغییر دادههای منبع، بهروزرسانی خودکار انجام نمیدهد و باید Refresh شود. سادهترین راه این است که روی Pivot Table کلیک کنید، وارد PivotTable Options شوید و در تب Data گزینه Refresh data when opening the file را فعال کنید. همچنین اگر دادههای جدول ۱ را به حالت Excel Table (با Ctrl+T) تبدیل کنید، محدوده دادهها بهصورت پویا به Pivot متصل میشود و در صورت اضافه شدن ردیفهای جدید، ساختار منبع درستتر عمل میکند.
اگر میخواهید Pivot بلافاصله بعد از هر تغییر در جدول ۱ آپدیت شود، میتوان از یک ماکرو VBA استفاده کرد که با هر تغییر، دستور Refresh را اجرا کند (مثلاً با رویداد Worksheet_Change). این روش کاملاً خودکار است اما نیاز به فعال بودن ماکروها در فایل دارد.