گزارش گیری در اکسل می تواند کمک شایانی به روند تحلیل نمودار و جداول کند. در آموزش های پیشین نحوه رسم جدول اکسل و همچنین طراحی نمودار در اکسل را به صورت مفصل تشریح کردیم. در پروسه های حسابداری گزارش گیری از اکسل بسیار کاربردی است. در این راهنما علاوه بر آموزش تب data در اکسل به بررسی مهمترین ابزار در سربرگ data در اکسل نیز خواهیم پرداخت. پس از پایان آموزش با نحوه آنالیز در اکسل، کاربرد data analysis و فعال سازی data analysis در اکسل و نحوه گزارش گیری نموداری در اکسل آشنا خواهید شد.
?
زبانه DATE حاوی امکانات و ابزارهایی برای وارد کردن اطلاعات به کاربرگ، تعیین نوع اطلاعات قابل وارد شدن به سلول، گزارش گیری در اکسل و … میباشد. در تصویر زیر این زبانه و ابزارهای دورن آن را مشاهده میکنید.
?
بخش CONNECTIONS برای وارد کردن اطلاعات و ایجاد لینک با سایر بانک داده ها میباشد. در تصویر زیر این بخش و ابزارهای آن را مشاهده میکنید.
?
ابزار GET EXTERNAL DATA برای وارد کردن اطلاعات و داده ها از بیرون از اکسل میباشد. اطلاعات میتواند از فایل اکسس، وب و … وارد اکسل شود. فرض کنید میخواهیم فروش ماهانه یک شرکت که در یک فایل متنی همانند زیر وجود دارد را به محیط اکسل منتقل کنیم.
?
برای این کار با کلیک بر ابزار GET EXTERNAL DATA ? ابزارهای درون این گزینه همانند تصویر زیر نمایش داده میشوند. بعد از آموزش get external data قادر به تحلیل انواع داده ها خواهید بود.
?
چون مبالغ فروش در فایل متنی قرار دارد، گزینه FROM TEXT را انتخاب میکنیم. در کادر باز شده محل فایل را در کامپیوتر مشخص کرده و در پایان روی گزینه IMPORT کلیک میکنیم.
?
پس در این مرحله از آموزش get external dataمرحله از با کادری همانند تصویر زیر مواجه میشویم. همانند تصویر زیر اگر فایل دارای متن فارسی باشد، در کادر مقابل FILE ORIGIN گزینه ۱۲۵۶:ARABIC(WINDOWS) را انتخاب و سپس روی NEXT کلیک میکنیم.
?
در کادر بعدی هم گزینه NEXT را انتخاب میکنیم.
?
در کادر بعدی گزارش گیری در قسمت COLUMN DATA FORMAT نوع فرمت متن و اعداد را انتخاب میکنیم . بهترین گزینه برای این مثال، گزینه GENERAL است. در پایان گزینه FINISH را انتخاب میکنیم.
?
در کادر بعدی اولین سلول برای ایجاد این جدول را مشخص و گزینه OK را انتخاب میکنیم.
?
پس از کلیک بر گزینه OK جدول بصورت زیر تشکیل خواهد شد.
?
حال اگر فایل متنی را تغییر داده باشد و بخواهید اعداد تغییر پیدا کرده در محیط اکسل نیز تغییر کنند، روی ابزار REFRESH ? کلیک کرده و دوباره همانند قبل فایل را انتخاب و گزینه IMPORT را انتخاب میکنیم.
اگر بخواهیم فایل اکسل بصورت خودکار و با فواصل زمانی معین، فایل متنی را بررسی کند و در صورت تغییر در آن ،جدول موجود در اکسل را همانند فایل متنی تغییر دهد، از گزینه PROPERTIES ? را انتخاب میکنیم و همانند تصویر زیر در مقابل کادر REFRESH EVERY مدت هر بررسی را وارد و در نهایت روی گزینه OK کلیک میکنیم.
?
بخوانید : آموزش فعال سازی ماکرو در اکسل و نحوه ساخت دکمه ماکرو
آنالیز در اکسل
در بخش DATA TOOLS ابزارهایی برای تجزیه و تحلیل اطلاعات، گزارش گیری از داده ها و همچنین مشخص نمودن نوع داده ها و محدوده آنها وجود دارد. در تصویر زیر ابزارهای این بخش را مشاهده میکنید.
?
TEXT TO COLUMNS ابزار : فرض کنید در سلول A1 همانند تصویر زیر یک عبارت نوشته شده باشد .میخواهیم هر کلمه از عبارت را در یک سلول بصورت جداگانه داشته باشیم.
?
برای این کار ابتدا این سلول را فعال میکنیم و سپس TEXT TO COUUMNS ? را انتخاب میکنیم. در کادر اول این ابزار، اگر میخواهید جداسازی برا اساس فاصله، ویرگول و … باشد، گزینه DELIMITED را انتخاب کنید و اگر میخواهید بر اساس فاطه داده شده، متن جداسازی شود، گزینه FIXED WIDTH را انتخاب میکنیم. در این مثال گزینه FIXED WIDTH را انتخاب کرده و گزینه NEXT را انتخاب میکنیم.
?
در کادر بعدی با جابجا کردن خطوط با ماوس، محل جدا کردن هر کلمه از کلمه قبلی و بعدی را مشخص و در نهایت گزینه NEXT را انتخاب میکنیم.
?
در کادر جدید نوع داده ها و محل سلولهای جدید را انتخاب ودر نهایت گزینه FINISH را انتخاب میکنیم.
?
تصویر حاصل بصورت زیر است.
?
ابزار REMOVE DUPLICATES برای حذف داده های تکراری دریک منطقه میباشند. فرض کنید در تصویر چند عدد را در یک محدوده داریم. میخواهیم اعداد تکراری را حذف کنیم.
?
برای این کار ابتدا محدوده مورد نظر را با ماوس انتخاب میکنیم و سپس ابزار REMOVE DUPLICATES ? را انتخاب میکنیم. در کادر باز شده این نرم افزار گزینه OK را انتخاب میکنیم.
?
اعداد حاصل بصورت زیر خواهد بود.
?
ابزار DATA VALIDATION برای تهیه لیست ورود داده ها و همچنین محدود کردن دامنه مورد داده ها در یک سلول بکار میرود . فرض کنید میخواهید در یک سلول لیستی داشته باشید و با کلیک بر آن سلول لیست باز شود و شما مقدار یا عبارتی را انتخاب کنید. برای این کار روی ابزار DATA VALIDATION ? کلیک میکنیم .در کادر ALLOW گزینه LIST را انتخاب میکنیم.
در کادر SOURCE عناوین لیست را وارد میکنیم. برای ایجاد فاصله بین عناوین از علامت “,” استفاده کنید .
فرض کنید میخواهیم در لیست ۳ مورد صنعتی، تجاری و اداری برای انتخاب وجود داشته باشند. برای این کار در این کادر عبارت “صنعتی,تجاری,اداری” را مینویسیم و در نهایت روی OK کلیک میکنیم.
?
حال اگر روی سلول مورد نظر کلیک کنیم، همانند تصویر زیر لیستی باز میشود که میتوانیم مورد دلخواه را از آن لیست انتخاب کرد.
?
مثال در محیط اکسل همانند تصویر زیر اطلاعات یک دارایی وارد شده است.
?
میخواهیم در سلول B5 لیستی داشته باشیم که بتوان از بین دو روش محاسبه استهلاک مجموع سنوات و مستقیم، یکی را انتخاب کنیم. برای این کار ابزار DATA VALIDATION را باز کرده و در قسمت ALLOW گزینه LIST را انتخاب میکنیم و در قسمت SOURCE عبارت “مستقیم,مجموع سنوات” را مینویسیم و OK را انتخاب میکنیم.
?
?
میخواهیم در مقابل سلول “دوره مدنظر” در سلول B6، لیستی داشته باشیم که شماره سال مورد نظر برای محاسبه استهلاک در آن وجود داشته باشد. برای این کار در این سلول ابزار DATA VALIDATION را انتخاب کرده و در قسمت ALLOW گزینه LIST را انتخاب میکنیم. در کادر SOURCE نیز عبارت “۱,۲,۳,۴,۵” را وارد کرده و گزینه OK را انتخاب میکنیم.
?
حالا میخواهیم در سلول B7 فرمولی بنویسیم که هزینه استهلاک طبق روش موجود در سلول B5 و برای سال موجود در سلول B6 را محاسبه کند. برای این کار در این سلول تابع IF را فرامیخوانیم. در قسمت LOGICAL_TEST باید عبارت شرط را بنویسیم. شرط را بر این میگذاریم که “اگر سلول B5 حاوی عبارت خط مستقیم باشد” در قسمت VALUE_IF_TRUE از تابع SLN و در صورت برقرار نبودن شرط در کادر VALUE_IF_FALSE از تابع SYD استفاده شود. برای این کار در کادر اول همانند تصویر زیر شرط را مینویسیم.
?
در کادر دوم (VALUE_IF_TRUE) تابع SLN (تابع محاسبه استهلاک به روش خط مستقیم) را فرا میخوانیم و اطلاعات آن را همانند تصویر زیر وارد میکنیم.
?
حالا قبل از کلیک بر گزینه OK در نوار فرمول یک بار همانند تصویر زیر روی تابع IF کلیک میکنمی تا دوباره به این تابع بازگردیم.
?
حالا در کادر سوم تابع VALUE_IF_FALSE( IF) تابع SYD را فرامیخوانیم و اطلاعات آن را همانند تصویر زیر وارد میکنیم.
?
در پایان روی گزینه OK کلیک میکنیم. فرمول موجود در سلول B7 بصورت زیر است.
=IF(B5=”خط مستقیم”,SLN(B1,B2,B3),SYD(B1,B2,B3,B6))
حالا چون سلول های B5 و B6 خالی هستند، در سلول B7 خطای #NUM! نمایش داده میشود. حالا اگر در لیست سلول B5 روش مجموع سنوات و در لیست سلول B6 سال سوم را انتخاب کنیم، هزینه استهلاک این سال نمایش در سلول B7 داده میشود.
?
گاهی اوقات لازم است از ورود اعداد اعشاری در سلول ها جلوگیری شود. برای نمونه در مثال قبل اگر در دوره مدنظر برای محاسبه استهلاک عدد اعشاری وارد شود، نتیجه نادرستی نمایش داده میشود و عدد دوره مورد نظر برای محاسبه استهلاک همیشه عدد صحیح است. برای اینکه به یک سلول فرمان بدهیم فقط اعداد صحیح را قبول کند، ابزار DATA VALIDATION را باز کرده و از لیست ALLOE گزینه WHOLE NUMBER را انتخاب میکنیم.
?
پس از انتخاب این گزینه در ادامه همین کادر، کادری به نام DATA ایجاد میشود که در آن باید محدوده اطلاعات را مشخص کنیم. اگر بخواهیم در یک سلول اعدادی بین ۱ تا ۵ وارد شوند، از لیست DATA گزینه
BETWEEN را انتخاب میکنیم.
?
حالا در کادر MINIMUM حد پایین محدوده که در این مثال ۱ است و در کادر MAXIMUM حد بالای محدوده اعداد که در این مثال ۵ است را وارد میکنیم.
?
حالا برای اینکه در صورت ورود اعداد خارج از محدوده اکسل هشداری را به کاربر نمایش دهد، در همین کادر روی زبانه ERROR ALERT کلیک میکنیم.
?
در کادر STYLE نوع هشدار را مشخص میکنیم. در این کادر سه نوع هشدار به نامهای STOP، WARNING و INFORMATION وجود دارد که بترتیب سه درجه سختی هشدار را نشان میدهند .هشدار STOP به هیچ عناون اجازه ورود داده خارج از محدوده را نمیدهد، هشدار WARNING، اگر کاربر ورود داده خارج از محدوده را تایید کند، اجازخ ورود میدهد و گزینه INFORMATION فقط در مورد ورود داده خارج از محدوده به کاربر اطلاع میدهد.
در این مثال گزینه STOP را اتخاب میکنیم و در کادر ERROR MESSAGE متن هشدار را وارد میکنیم .
در کادر TITLE نیز عنوان هشدار را وارد میکنیم.
?
حالا پس از کلیک روی گزینه OK اگر در سلول مورد نظر عددی خارج از محدوده ۱ تا ۵ وارد کنیم، شاهد تصویر زیر خواهیم بود.
?
با کلیک بر گزینه RETRY میتوان عدد وارد شده را اصلاح کرد و با کلیک بر گزینه CANCEL همان عدد قبلی وارد میشود.
حال اگر پس از کلیک بر گزینه RETRY عدد ۴ را وارد کنیم، خواهیم دید سلول این عدد را میپذیرد.
?
برای حذف این تنظیمات از یک سلول ابتدا آن را فعال کرده و سپس در ابزار DATA VALIDATION گزینه CLEAR ALL را انتخاب و روی گزینه OK کلیک کنید.
?
ابزار SCENARIO MANAGER برای گزارشگیری از یک فرمول بکار میرود. فرض کنید اطلاعات یک سرمایه گذاری و عواید چهار ساله آن در محیط اکسل بصورت زیر در دسترس است.
?
میخواهیم در سلول B8 خالص ارزش روز این پروژه را محاسبه کنیم. برای این کار تابع NPV را فرامیخوانیم و همانند تصویر زیر اطلاعات را در این تابع وارد میکنیم.
?
پس از کلیک بر OK خالص ارزش روز این پروژه محاسبه میشود.
?
حال میخواهیم جدولی تهیه کنیم که علاوه بر نرخ بازده ۱۵ درصد، مقدار NPV را برای نرخهای ۱۳، ۱۴، ۱۶ و ۱۷ درصد نیز نشان دهد.
برای این کار ابتدا روی سلول B8 کلیک کرده و سپس ابزار WHAT_IF ANALYSIS را انتخاب کرده و از لیست آن گزینه SCENARIO MANAGER را انتخاب میکنیم.
?
در کادر باز شده این ابزار همانند تصویر زیر گزینه ADD را انتخاب میکنیم.
?
پس از کلیک بر ADD در کادر جدید همانند تصویر زیر در کادر SCENARIO NAME نام جدول و یا نام هر نام دلخواه دیگری را وارد میکنیم. در کادر CHANGING CELLS روی سلولی که میخواهیم آن را تغییر دهیم )نرخ بازده ،B7( کلیک میکنیم و در پایان روی گزینه OK کلیک میکنیم.
?در کادر ظاهر شده بعدی، مقدار جدید سلولی را که باید تغییر کند (نرخ بازده) را وارد میکنیم.
?
با کلیک بر گزینه OK به صفحه اول SCENARIO MANAGER هدایت میشویم.
?
مراحل فوق را برای سایر نرخهای بازده (۱۴، ۱۶ و ۱۷ درصد) نیز انجام میدهیم. تا در نهایت ابزار SCENARIO MANAGER بصورت زیر تغییر یابد.
?
حالا بر گزینه SUMMARY کلیک میکنیم. در کادر جدید از ما خواسته میشود سلولی را که تغیرات آن باید نمایش داده شود را مشخص کنیم. در این مثال میخواهیم تغییرات سلول B8 )خالص ارزش روز( را مشاهده کنیم. برای این کار روی این سلول کلیک کرده و گزینه OK را انتخاب میکنیم.
?
پس از کلیک روی OK در یک کاربرگ جدید جدولی همانند تصویر زیر ایجاد میشود که مقدار NPV برای نرخ بازده های مختلف را نشان میدهد.
?
ابزار GOAL SEEK این ابزار که در بخش ابزارهای WHAT_IF ANALYSIS قرار دارد به نوعی برای آزمون و خطا در محاسبات کاربرد دارد. مثال قبل را در نظر بگیرید.
?
میخواهیم بدانیم با چه نرخ بازدهای میزان NPV صفر میشود )نرخ بازده داخلی(. برای این کار میتوانیم از تابع IRR استفاده کنیم. اما در این بخش میتوانیم از ابزار GOAL SEEK نیز استفاده کنیم. برای این کار ابتدا روی سلول B8 کلیک کرده و سپس همانند تصویر زیر این ابزار را باز میکنیم.
?
در کادر باز شده این ابزار، در کادر TO VALUE مقدار مورد نظر برای تابع NPV که در این مثال صفر است را وارد میکنیم. در کادر BY CHANGING CELL سلولی را که برای رسیدن به خالص ارزش روز صفر باید تغییر کند را انتخاب میکنیم. در این مثال میخواهیم با تغییر نرخ بازده مقدار NPV تغییر کند، به همین منظور در این کادر روی سلول B7 کلیک کرده و در پایان OK را انتخاب میکنیم.
?
پس از این مرحله کادر دیگری باز میشود که در آن محاسبات لازم انجام شده و نرخ بازده جدید نمایش داده میشود. در پایان روی OK کلیک کنید.
?
نرخ بازده مورد نیاز برای صفر شدن NPV در تصویر زیر نمایش داده شده است.
?
مثال: سررسید یک وام ۵ ساله است و در طی ۵ قسط در پایان هر سال بازپرداخت میشود. نرخ بهره سالانه ۱۶ درصد است. چقدر وام بگیریم تا مبلغ هر قسط ۶۰۱۱۱۰۱۱۱ ریال شود؟ برای این کار ابتدا اطلاعات را همانند تصویر زیر در محیط اکسل وارد میکنیم.
?
سپس در سلول B4 تابع PMT برای محاسبه مبلغ هر قسط را نوشتهایم. چون فعلا مبلغ وام وارد نشده است، مبلغ هر قسط هم صفر نمایش داده میشود. حالا ابتدا سلول B4 را فعال کرده و سپس ابزار GOAL SEEK را فرامیخوانیم و کادرهای آن را همانند تصویر زیر پر میکنیم.
?
چون پرداخت قسط باعث خروج وجه نقد میشود، آن را با علامت منفی وارد میکنیم. پس از کلیک بر گزینه OK مبلغ وام بصورت زیر نمایش داده میشود.
?
در پایان روی OK کلیک میکنیم.
SOLVE ابزار
این ابزار در حالت عادی در زبانه DATA وجود ندارد و به عنوان یک ADD-IN باید ابتدا فعال شود. با کمک این ابزار میتوان مسائل مربوط به برنامه ریزی خطی را حل نمود. این ابزار با توجه به شرطها و محدودیتهایی که در آن وارد میشود، حداقل یا حدکثر مقدار یک تابع که تابع هدف باشد را مشخص میکند. برای فعال کردن این ابزار روی زبانه FILE کلیک کرده و گزینه OPTIONS را انتخاب میکنیم. در کادر باز شده همانند تصویر زیر از بخش ADD-INS روی گزینه GO کلیک میکنیم.
?
در کادر باز شده جدید مانند تصویر زیر گزینه SOLVER ADD-IN را تیکدار کرده و روی گزینه OK کلیک میکنیم.
?
حال اگر به زبانه DATA مراجعه کنید این ابزار را خواهید دید.
?
برای توضیح بیشتر این ابزار به طرح مثالی میپردازیم.
مثال: یک شرکت دو محصول X و Y را تولید میکند. فروش هر واحد محصول X زیانی معادل ۲ واحد پولی و فروش هر واحد محصول Y سودی معادل ۵ واحد پولی را برای شرکت در پی دارد. به دلیل رضایت مشتری و حفظ بازار، حداقل ۱۱۱ محصول X و ۸۱ محصول Y در روز باید تولید شود و حداکثر تولید این محصولات هم بترتیب ۲۱۱ و ۱۷۱ واحد است. از طرفی در روز حداقل باید ۲۱۱ محصول در شرکت تولید شود. مطلوبست محاسبه تعداد تولید محصول X و Y برای دستیابی به حداکثر سود در روز؟ همانطور که در درس پژوهش عملیاتی آموختهایم، تابع هدف این مثال بصورت زیر است:
Z 2X Y5
برای شروع حل این مسئله در اکسل، سلول B1 و B2 را مقدار تولید محصولات X و Y در نظر میگیریم.
?
حالا در سلول B3 تابع هدف Z را مینویسیم. تابع هدف در این مثال با فرمول نویسی نسبی بصورت زیر است:
=(-۲*B1)+(5*B2)
?
شرط های این مثال بصورت زیر است:
X>=100
Y>=80
X<=200
Y<=170
X+Y>=200
برای شروع کار از زبانه DATA از بخش ANALYSIS ابزار SOLVE را انتخاب میکنیم تا کادرآن بصورت زیر باز شود.
?
در کادر SET OBJECTIVE باید سلولی را که حاوی تابع Z است را وارد کنیم. در این مثال در این کادر سلول B3 را انتخاب میکنیم.
?
در مقابل گزینه TO سه انتخاب وجود دارد. در این مثال چون هدف حداکثر کردن سود است، گزینه MAX را انتخاب میکنیم.
?
در کادر BY CHANGING VARIABLE CELLS باید آدرس سلولهای متغیرهای مثال را وارد کنیم. در این مثال دو متغیر X و Y داریم که در سلول B1 و B2 قرار دارند.
?
در کادر SUBJECT TO THE CONTSRAINTS باید تمام شرطها و محدودیت های گزارش گیری در اکسل را وارد کنیم.
?
با کلیک بر گزینه ADD کادری همانند تصویر زیر برای وارد کردن محدودیت اول باز میشود. محدودیت اول تعداد تولید محصول X میباشد که حداقل باید ۱۰۰واحد باشد ( X>=100(. این محدودیت را همانند زیر وارد میکنیم و در پایان بر OK کلیک میکنیم (برای مقدار X آدرس سلول آن را وارد میکنیم.)
?
?
برای وارد کردن محدودیت دوم دوباره گزینه ADD را انتخاب میکنیم. در محدودیت دوم حداقل تعداد تولید محصول Y که ۸۰ واحد است را وارد میکنیم.
?
?
شرط سوم و چهارم که مربوط به حداکثر تعداد تولید دو محصول است را نیز به همین صورت وارد میکنیم.
?
برای وارد کردن شرط آخر گزارش گیری در اکسل که مربوط به جمع تولید محصول X و Y است ابتدا یک سلول جدید همانند تصویر زیر ایجاد میکنیم و فرمول =B1+B2 را در آن وارد نیکنیم تا جمع تولید دو محصول در آن نوشته شود.
?
حالا همانند شروط قبل بر گزینه ADD کلیک میکنیم و این شرط را نیز وارد میکنیم.
?
حالا پس از کلیک بر گزینه OK در کادر اصلی این ابزار، در مقابل گزینه SELECT A SOLVING METHOD گزینه SIMPLEX LP را انتخاب میکنیم و در پایان گزینه SOLVE را انتخاب میکنیم.
?
خواهیم دید تعداد سلول های X و Y بترتیب به مقادیر ۱۰۰ و ۱۷۰ تغییر پیدا کردهاند و حداکثر مقدار سود ممکن ۶۵۰ واحد است.
?
مثال: تابع هدف یک مساله بصورت ۳۴X+36Y است. میخواهیم حداکثر مقدار تابع هدف را با توجه به محدودیت های زیر محاسبه کنیم.
۹X+7Y<=1260
۷X+9Y<=1300
X>=0
Y>=0
برای حل این مساله ابتدا اطلاعات را بصورت زیر وارد اکسل میکنیم. فرمول تابع هدف و محدودیتها در تصویر زیر مشخص شده اند.
?
حالا برای گزارش گیری در اکسل ابزار SLOVE را فرامیخوانیم و آدرس سلول تابع هدف را در کادر SET OBJECTIVE و آدرس سلول متغیرها را در کادر BY CHANGING VARIABLE CELLS وارد میکنیم. چون تابع ما حداکثر است، گزینه MAX را انتخاب میکنیم.
?
حالا در کادر SUBJECT TO THE CONSTRAINTS محدودیت ها را وارد میکنیم. برای این کار روی گزینه ADD کلیک میکنیم و در کادر جدید باز شده شرط اول را بصورت زیر وارد میکنیم.
?
سایر محدودیت ها را نیز بصورت زیر وارد میکنیم.
?
??
تا اینجا کادر ابزار SOLVE بصورت زیر است.
?
حالا کافیست جهت گزارش گیری در اکسل روی گزینه SOLVE کلیک کنید تا جواب بهینه نمایش داده شود.
