گزارش گیری در اکسل – ساخت انواع گزارش Excel از صفر تا صد

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

انواع روش‌های گزارش گیری در اکسل

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

۱. گزارش گیری ساده

روش‌های گزارش گیری ساده در اکسل عبارت‌اند از:

• فیلتر کردن داده‌ها
• نمودارهای ساده
• توابع پایه

۲. گزارش‌گیری حرفه‌ای

در گزارش‌گیری حرفه‌ای، از روش‌های زیر برای تهیه گزارش استفاده می‌کنیم:

• استفاده از PivotTable یا جداول محوری
• نمودارهای پیشرفته
• توابع پیشرفته
• داشبورد مدیریتی

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

گزارش گیری ساده در اکسل

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

1. فیلتر کردن داده‌ها
2. نمودارهای ساده
3. توابع پایه اکسل

در ادامه بحث هر یک از این موارد را بررسی می‌کنیم.

گزارش گیری با فیلتر کردن داده‌ها

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

مرحله ۱

۱. ابتدا تیتر اصلی جدول را انتخاب می‌کنیم، سپس از زبانه «Data» روی باکس «Filter» کلیک می‌کنیم. بعد از اعمال فیلتر، کنار تیتر اصلی یا هدر جدول علامت فلش ظاهر می‌شود که با کلیک روی آن دسته‌بندی‌های مختلف موجود در آن ستون مشخص هستند.

مرحله ۲

۲. با حذف علامت هر یک از دسته‌بندی‌ها فقط گروه دلخواه خود برای مشاهده در گزارش را انتخاب می‌کنیم.

مرحله ۳

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

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

گزارش گیری با رسم نمودار

رسم نمودار یکی از ابزارهای گرافیکی برای گزارش گیری در اکسل است. از آنجا که تصویرسازی داده‌ها تحلیل و تصمیم‌گیری را ساده می‌کند، این نوع گزارش برای مدیران بسیار جذاب است. برای این کار کافی است از زبانه «Insert» بخش «Charts» نمودار دلخواه خود را انتخاب کنیم.

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

گزارش گیری در اکسل با توابع پایه اکسل

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

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

مسیر یادگیری سریع گزارش گیری در اکسل در فرادرس

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

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

• فیلم آموزش داشبورد مدیریتی و گزارش‌گیری حرفه‌ای در اکسل در فرادرس
• فیلم آموزش استفاده از توابع و فرمول‌نویسی در اکسل در فرادرس
• فیلم آموزش رسم انواع نمودار در اکسل در فرادرس
• فیلم آموزش ابزارهای کاربردی اکسل در فرادرس

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

• مجموعه آموزش اکسل مقدماتی تا پیشرفته در اکسل در فرادرس
• مجموعه آموزش رسم انواع نمودار در اکسل در فرادرس
• مجموعه آموزش اکسل برای کسب و کار در فرادرس

گزارش گیری حرفه‌ای در اکسل

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

در ادامه این بخش ابزارهای مورد نیاز برای گزارش‌گیری حرفه‌ای به شرح زیر را بررسی می‌کنیم.

1. ابزار PivotTable
2. نمودارهای پیشرفته
3. توابع پیشرفته
4. داشبورد مدیریتی

حال در ادامه بحث نحوه کار با هر یک را به‌طور کامل توضیح می‌دهیم.

گزارش گیری در اکسل با PivotTable

«جدول محوری یا پیوت تیبل» (PivotTable) یکی از بهترین ابزارهای حرفه‌ای گزارش گیری در اکسل است. با این ابزار بدون نیاز به فرمول‌نویسی می‌توانیم انواع گزارش‌های خلاصه و تحلیلی را با سرعت تهیه کنیم.

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

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

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

۱. مشخص کردن محدوده گزارش‌گیری

در این مرحله بعد از انتخاب جدول داده‌ها، در زبانه «Insert» روی دکمه «PivotTable» و سپس روی «From Table/Range» کلیک می‌کنیم.

بعد از باز شدن پنجره، همان‌طور که در تصویر مشخص است، محدوده انتخاب شده جدول به‌صورت خودکار در قسمت «Table/Range» درج می‌شود. در این مرحله روی بخش «New Worksheet» و دکمه «Ok» کلیک می‌کنیم.

۲. تعیین نحوه نمایش داده‌ها در جدول

با انجام مرحله اول، مطابق محدوده انتخاب شده از جدول داده‌ها، یک پیوت تیبل در کاربرگ جدید ایجاد می‌شود. حال لازم است چیدمان یا نحوه نمایش داده‌ها در گزارش خود را مشخص کنیم. به‌عنوان مثال اگر بخواهیم میزان فروش در شعبه‌های مختلف بر حسب محصول را در گزارش نهایی نمایش دهیم، از باکس «Pivot Table Fields»، سه بخش «Branch»، «Product» و «Total Sales» را علامت می‌زنیم.

سپس با توجه به نوع گزارش، هر یک را در بخش‌های مربوط قرار می‌دهیم. معمولا در جداول محوری، داده‌های که می‌خواهیم گزارش بر اساس آن گروه‌بندی شود، در باکس «Rows» و داده‌های متناظر با آن در قسمت «Column» قرار می‌گیرند. همچنین داده‌هایی از جدول را که می‌خواهیم به‌عنوان نتیجه نهایی محاسبه شوند، در قسمت «Values» قرار می‌دهیم. در مثال مد نظر، داده‌های مربوط به شعبه‌ها در قسمت «Rows»، داده‌های متناظر آن یعنی میزان فروش محصولات در قسمت «Columns» و محاسبه مجموع فروش در قسمت «Values» قرار می‌گیرند. برای این کار کافی است با نگاه داشتن دکمه سمت چپ ماوس، قسمت‌های علامت خورده را به سمت بخش مربوط درگ (Drag) کنیم تا انتقال داده‌ها به شکل خودکار انجام شود.

۳. تنظیمات نمایش داده‌ها در گزارش

در این مرحله، نوع نمایش جدول در گزارش از لحاظ فشرده بودن سطرها یا ستون‌ها و تکرار هدر جدول در هر صفحه را مشخص می‌کنیم. برای انجام این تنظیمات ابتدا در زبانه «Design» روی بخش «Report Layout» می‌رویم. در منوی آبشاری باز شده پنج حالت نمایش برای گزارش به شرح زیر وجود دارد که آن‌ها را در یک مثال دیگر از لحاظ فرمت ظاهری جدول بررسی می‌کنیم.

• 

  حالت Show in Compact Form: در این حالت همه فیلدهای بخش Row در یک ستون قرار می‌گیرند و جدول به شکل فشرده و مرتب‌تر است. حالت فشرده برای گزارش‌های ساده و کوتاه و زمانی‌که نیازی به دیدن عنوان هر کادر نداریم، به کار می‌رود.

• حالت Show in Outline Form: در این نوع گزارش‌گیری، همه کادر‌های بخش Row در ستون‌های جداگانه نمایش داده می‌شوند و داده‌ها خواناتر هستند. از این حالت برای گزارش‌های تحلیلی و چاپ کامل همه تیترهای اصلی جدول استفاده می‌کنیم.

• حالت Tabular Form: این حالت جدول در فرم کلاسیک کاربرگ اکسل همراه خطوط نمایش داده می‌شوند. فیلتر کردن داده‌ها در حالت «Tabular» راحت‌تر است و از آن برای گزارش‌گیری‌های رسمی که در جدول داده‌های مختلف وجود دارند استفاده می‌کنیم.

همچنین با انتخاب بخش «Repeat All items Lables» عنوان ستون‌ها در هر ردیف تکرار می‌شود.

۴. فیلتر کردن داده‌ها

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

از قسمت «Pivot Table Field» ستون دلخواهی را که می‌خواهیم فقط در خروجی گزارش باشد با درگ کردن به بخش «Field» منتقل می‌کنیم. به‌عنوان مثال اگر گزارش فروش محصولات در یک استان خاص را نیاز داشته باشیم، بخش «State» را به این قسمت انتقال می‌دهیم. در این حالت یک ردیف اضافی در بالای جدول ایجاد می‌شود.

برای تعیین استان خاص از منوی آبشاری، مورد دلخواه خود (Virginia) و سپس گزینه «Select Multiple items» را انتخاب می‌کنیم.

بنابراین هنگام گزارش‌گیری فقط داده‌های مربوط به «Virginia» در خروجی خواهند بود.

در پیوت تیبل فیلتر کردن داده‌ها را به شکل دیگری نیز می‌توانیم انجام دهیم. در این روش با استفاده از ابزار «Slicer» ظاهر گرافیکی جذاب‌تری به گزارش می‌دهیم و قسمت‌های فیلتر شده در گزارش کاملا مشخص هستند. برای این کار بعد از انتخاب یک سلول دلخواه در جدول، از زبانه «Pivot Table Analyze» قسمت «Insert Slicer» را انتخاب می‌کنیم.

سپس در پنجره باز شده بخش‌هایی که می‌خواهیم در گزارش نهایی نمایش داده شوند را انتخاب می‌کنیم.

همان‌طور که در تصویر مشخص است، دو جدول جداگانه با توجه به نوع فیلتر نمایش داده می‌شوند. با کلیک روی هر از دکمه‌های جدول، زیر مجموعه مورد نظر از هر بخش را انتخاب می‌کنیم. البته برای نمایش هم زمان چند مورد، دکمه «Alt+S» را نگاه می‌داریم و سپس موارد دلخواه خود را انتخاب می‌کنیم.

دکمه «Slicer» یک ابزار گرافیکی تعاملی است که نسبت به فیلتر معمولی در اکسل، ساده‌تر و سریع‌تر است.

۵. گزارش‌گیری در کاربرگ‌های جداگانه

برخی مواقع می‌خواهیم بخش‌های فیلتر شده در کاربرگ‌های جداگانه نمایش داده شوند. برای این نوع گزارش‌ گیری با پیوت تیبل، ابتدا روی قسمت‌ دلخواه فیلتر شده دارای منوی آبشاری کلیک می‌کنیم. سپس از زبانه «PivotTable Analyze» مسیر «Options» و «Show Report Filter Pages» را دنبال می‌کنیم.

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

بعد از کلیک روی دکمه «Ok» دو کاربرگ جداگانه خواهیم داشت.

۶. گزارش‌گیری با رسم نمودار

رسم نمودار یکی از روش‌های حرفه‌ای گزارش گیری در اکسل است که در پیوت تیبل نیز به‌راحتی می‌توانیم آن را انجام دهیم. برای این کار از زبانه «PivotTable Analyze» و منوی آبشاری بخش «Tools» روی قسمت «PivotCharts» کلیک می‌کنیم.

در پنجره باز شده، نمودار دلخواه خود را انتخاب می‌کنیم.

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

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

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

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

برای یادگیری اصول گزارش‌گیری حرفه‌ای با این نمودارها پیشنهاد می‌کنیم فیلم آموزش رسم نمودارهای پیشرفته در اکسل در فرادرس را مشاهده کنید.

گزارش گیری با توابع پیشرفته اکسل

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

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

گزارش گیری در اکسل با داشبورد مدیریتی

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

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

ویژگی‌های گزارش‌گیری با داشبورد مدیریتی برای کسب و کارها

اما داشبوردهای اکسل ویژگی‌هایی به شرح زیر در گزارش‌گیری دارند که کار با آن‌ها را به اولویت کسب و کارها تبدیل می‌کند.

1. مشاهده عملکرد
2. تصویرسازی گرافیکی
3. به‌روز‌رسانی خودکار
4. فیلتر کردن داده‌ها
5. تحلیل پیشرفته
6. بهبود تصمیم‌گیری

در نرم افزار اکسل داشبورد مدیریتی مجموعه‌ای از یک یا چند «PivotTable» همراه توابع پیشرفته اکسل برای تحلیل و خلاصه‌سازی داده‌ها، «Pivot chart» و سایر نمودارها برای ارزیابی تصویری عملکرد، ابزار «Slicer» برای فیلتر کردن داده‌ها و «Power Query» برای جمع‌‌آوری و به‌روزرسانی داده‌ها است.

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

جمع‌بندی پایانی

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