همواره یکی از ابزارهای بسیار پر کاربرد در تحلیل داده‌ها برای مدیران نمودار می‌باشد که اگر به درستی با آن ارتباط برقرار کنیم، اطلاعات آماری پر کاربردی از آن استخراج می‌شود. در این نوشنار قصد داریم یکی از پر کاربردترین نمودارهای تحلیلی را به شما معرفی و نحوه استخراج اطلاعات از آن را بیان نماییم.
نمودار scatter : می‌تواند تغییرات چند متغییر را در کنار یکدیگر و تاثیر پذیری آن‌ها بر روی همدیگر را هم بصورت نقطه به نقطه و هم بصورت خطی به ما نشان دهد.
یکی از مهمترین مباحث مدیریتی در اکسل مبحث آنالیز داده‌ها می‌باشد که بسیار پر کاربرد است، در این پست قصد داریم به بیان ساده با ذکر چند مثال کاربردی به بررسی موضوع مذکور و ابزارهای مرتبط با آنالیز داده‌ها بپردازیم.
در اکسل از آنالیز با عنوان What -If یاد می‌شود به بیان عامیانه یعنی برای رسیدن به هدف مطلوب، چه تغییری بر روی داده‌ها انجام گیرد، در واقع آنالیز داده‌ها بر روی نتیجه یا جواب مسئله تغییراتی انجام می‌دهد و با تغییر نتیجه، متغییر مورد نظر در تابع را تغییر می‌دهد. به عنوان مثال فرض کنید شما اگر روزی 4 ساعت درس بخوانید در پایان هفته 200 صفحه از کتاب مورد نظرتان را به پایان می‌رسانید، حال می‌خواهید بدانید که اگر بخواهید تا پایان هفته 400 صفحه از کتاب تمام شود باید روزی چند صفحه درس بخوانید. این مثال ساده بیان کننده کاربرد what - if در برنامه اکسل می‌باشد.
در ادامه به بررسی ابزارهای مرتبط با آنالیز داده‌ها آشنا خواهیم شد.
یکی از ابزارهای پرکاربرد آماری در اکسل که کاربردهای فراوانی نیز برای آن در حسابداری می‌توان یافت، ابزار Data Analysis می‌باشد. در این پست قصد داریم به بررسی ابزار مذکور و کاربردهای آن در حسابداری بپردازیم.
نمودار زیر که مربوط به سود یک شرکت تولیدی می‌باشد و تابع متناظر با آن در اختیار قرار دارد.
حال به کمک تابع مذکور می‌خواهیم نقطه سر به سر (نقطه‌ای که هزینه‌ها با درآمدها برابر می‌شود) و همچنین بیشترین و کمترین نقطه سوددهی شرکت را بدست آوریم. چگونه باید عمل نماییم؟
ابتدا تابع مذکور را به شکل زیر در یک sheet جدید وارد نمایید.
توجه: N14 آدرس سلولی بقلی است یعنی جایی که به عنوان متغییر باید تغییر نماید یا به عبارت جای X است
حال تابع مذکور به فرمتی که قابل فهم توسط اکسل است در می‌آید و می‌توان از آن برای تجزیه و تحلیل مورد نظر استفاده کرد، از اینجای کار به بعد نیازمند چند ابزار کاربردی هستیم.
میزان ذخیره مورد انتظار در رابطه با کل محصولات شرکت برادران کامیاب با توجه به اطلاعات سال مالی 93 با توجه به برآورد هزینه گارانتی که در زیر در اختیار قرار گرفته است را محاسبه نمایید.
همچنین ثبت سند مرتبط با آن را بنویسید.
هزینه گارانتی 20 درصد از محصولات 4000000 ریال
هزینه گارانتی 60 درصد از محصولات 65000000 ریال
هزینه گارانتی 10 درصد از محصولات 70000000 ریال
هزینه گارانتی 10 درصد از محصولات 0 ریال
ج : برای انجام پروژه مذکور ابتدا دو فیلد در دو ستون با عنوان هزینه گارانتی و درصد ایجاد می‌نمایید و هر یک از مقادیر مذکور را در قسمت مرتبط با آن درج می‌نماییم.
حال با استفاده از تابع SUMPRODUCT آرایه‌های مربوطه را در آرگومان مرتبط با آن قرار می‌دهیم.
توجه داشته باشید در هر آرگومان باید کل فیلدهای مرتبط با آن انتخاب شود.
به ترتیب زیر عمل می‌نماییم.
فرض کنید مطابق جدول زیر برای 13 دانشجو نمرات دروس حسابداری دولتی و نمرات درس مبانی کامپیوتر در حسابداری در اختیار قرار دارد.
پیش از این با یکی از توابع پر کاربرد اکسل در رابطه با جستجو در جداول به نام DGET آشنا شدیم. حال در این پست قصد داریم به بررسی یکی دیگر از توابع پرکاربرد اکسل در رابطه با جستجو را مورد به نام VLOOKUP را مورد بررسی قرار دهیم.
همانطور که از نام این تابع مشخص است جستجو بصورت افقی را مورد بررسی قرار می‌دهد، یعنی با توجه به یک پارامتر (مثلا شماره پرسنلی) مقادیر متناظر با آن پارامتر را در سلول مورد نظر که موازی و در همان سطر (بصورت افقی) با آن پارامتر قرار دارد را برای ما بازیابی می‌نماید.
تابع VLOOKUP دارای 4 آرگومان به شرح زیر می‌باشد.
یک شرکت تولید بستنی را در نظر بگیرید که در فصل تابستان 93 با مشخصات زیر اقدام به فروش 3 نوع بستنی نموده است. حال می‌خواهیم با انتخاب هر شماره فاکتور که یکتا می‌باشد. کلیه مشخصات تک تک فیلدهای آن فاکتور (که در رکورد متناظر با آن شماره فاکتور قرار دارد) در اختیار ما قرار گیرد.
همانطور که قبلا قول دادیم در این قسمت قصد داریم، صرفا به بررسی توابع پرکاربرد اکسل با تمرکز بر مفاهیم و استانداردهای حسابداری بپردازیم. اگر با نحوه فرمول نویسی در اکسل آشنایی ندارید، ابتدا پست‌های قبلی مرتبط با اکسل را مطالعه نمایید. سپس به ادامه مطالعه این نوشتار بپردازید. همانطور که می‌دانید توابع از تجمیع چند فرمول تشکیل شده و بر حسب نیاز کاربر تحت عنوان یک نام در اکسل ذخیره می‌شود.
در این پست قصد داریم به بررسی و کاربرد تابع SUMPRODUCT در حسابداری بپردازیم، دامنه کاربرد این تابع در علم آمار، ریاضی، حسابداری و مدیریت بسیار زیاد می‌باشد. در آمار و ریاضی کاربرد آن برای بدست آوردن" امید ریاضی" و در حسابداری و مدیریت مالی در رابطه با " ارزش مورد انتظار" می‌باشد. یکی از پرکاربردترین موارد استفاده این تابع در حسابداری مرتبط با مبحث ذخایر می‌باشد( به استاندارد شماره 4 حسابداری بند 36 مراجعه نمایید) در این بخش با ذکر یک مثال از خود استاندارد مربوطه به روش محاسبه ارزش مورد انتظار خواهیم پرداخت.
مثال : واحد تجاری محصولات خود را همراه با ضمانت شش ماهه بابت رفع هرگونه عیب و نقص ناشی از تولید به فروش می‌رساند. چنانچه عیب و نقص کشف شده در تمام محصولات فروش رفته جزئی باشد، هزینه رفع آن معادل یک میلیون ریال خواهد بود و اگر عیب و نقص کشف شده در تمام محصولات فروش رفته عمده باشد، هزینه رفع آن معادل چهار میلیون ریال خواهد بود. تجربیات گذشته و انتظارات آتی واحد تجاری حاکی از آن است که در سال آینده، 75 درصد محصولات فروش رفته فاقد عیب و نقص، 20 درصد دارای عیب و نقص جزئی و 5 درصد نیز دارای عیب و نقص عمده خواهد بود. براساس الزامات بند 21، جاری احتمال خروج منافع اقتصادی بابت تعهدات مربوط به ضمانت محصولات را به طور کلی ارزیابی می‌کند.
ارزش مورد انتظار هزینه رفع عیب و نقص محصولات به شرح زیر است:
4 /0= (4 میلیون * 5%) + (1 میلیون 20%) + (صفر * 75%)
برای محاسبه مورد مذکور در اکسل به شرح زیر اقدام می‌نماییم.
گاها پیش می‌آید که در یک جدول دنبال پارامتر یا پامترهای خاصی هستیم و فقط می‌خواهیم جمع مقادیر یا مبالغ متناظر با آن پارامتر را بدانیم؛ در این صورت قطع و یقین جمع معمولی کارآیی ندارید و ما نیازمند جمعی هستیم که مشروط به شرایط باشد که ما انتظار آن را داریم؛ مثلا می‌خواهیم جمع مالیات حقوق پرسنلی را بدانیم که در سال 93 در شرکت استخدام شدند: یعنی جمع حقوق مشروط به استخدام سال 93؛ یعنی جمع شزطی،
جمع شرطی در اکسل یعنی SUMIF؛ اما چگونه و در چه مواقع از این تابع استفاده نماییم. ابتدا با آرگومان‌های این تابع آشنا می‌شویم.
یکی دیگر از توابع بسیار پر کاربرد در اکسل که شباهت زیادی با تابع VLOOKUP دارد، ولی از این تابع کارایی مطلوب تری بخصوص در قبال بروزسانی داده‌ها دارد، ولی بدلیل عادت به استفاده از VLOOKUP در بین حسابداران و دانشجویان حسابداری کمتر مورد توجه واقع شده تابع DGET می‌باشد. که در این نوشتار قصد داریم به معرفی و کاربرد آن بپردازیم.
قبل از توضیح کاربرد این تابع، خاطر نشان می‌نمایم که هرگاه قبل از نام یک تابع از کلمه D استفاده شد به معنی DATA می‌باشد. تابع DGET یک جدول از داده‌ها را در اختیار می‌گیرد و با توجه به معیاری که بصورت یکتا برای یک رکود مشخص شده از بین کل رکودهای جدول، مقادیر متناظر با آن معیار را در اختیار کاربر قرار می‌دهد.
اما معیار یا criteria در این تابع به چه معنی است و شامل چه مواردی می‌شود؟
فرض کنید صرفا شماره دانشجویی یک دانشجو را در اختیار داریم و می‌خواهیم از بین کل رکوردهای تمام دانشجویان دنبال یک فیلد یا تمام فیلدهای مرتبط با رکورد اطلاعات یک دانشجو، مانند نام، نام خانوادگی، وضعیت انتخاب واحد، تعداد دروس ...مرتبط با او بگردیم، فرض کنید در یک شرکت با داشتن شماره پرسنلی یک کارمند می‌خواهیم یک بخش یا تمام بخش‌های حقوق آن کارمند از جمله، حقوق پایه، اضافه کار، بهره وری و.... را با انتخاب شماره پرسنلی در اختیار داشته باشیم، در این صورت بهترین و کارا ترین روش استفاده از تابع DGET می‌باشد.
اما چگونه از این تابع استفاده نماییم؟ آرگومان‌های تابع DGET به شرح زیر می‌باشد.
Database: آدرس جدولی از داده‌ها که جستجو و تحلیل بر روی آن انجام می‌گیرد.
Field: آدرس فیلدی که بر روی آن جستجو و آنالیز باید صورت گیرد.
Criiteria: آدرس دو سلولی که نام معیار و مقدار معیار در آن قرار دارد ( شامل دو سلول زیر هم می‌شود)
نکته بسیار مهم در رابطه با مقدار دهی در criteria این است که حتما باید از مقدار دهی مطلق با به کار بردن "$" استفاده نماییم.
برای حل یک نمونه تمرین کاربردی در رابطه با تابع DGET به این آدرس مراجعه نمایید: پروژه مربوط به تابع dget
فرض کنید وامی معادل 10000000 با بازپرداخت 10 ماه و نرخ بهره سالانه 24% در اختیار قرار دارد و می‌خواهیم برای آن جدول اقساط ایجاد نماییم. در این شرایط بهترین ابزار در اکسل تابع PMT می‌باشد.
این تابع دارای 5 آرگومان است که به شرح زیر می‌باشد.
یکی از نیازهای یک مجموعه بعد از تاسیس و شروع به کار، استخدام نیروی کار می‌باشد، واحد منابع انسانی یک سازمان پس از بررسی رزومه متقاضیان کار و مصاحبه استخدامی اقدام به جذب نیرو می‌نماید و با توجه به نوع شرکت (دولتی، خصوصی) اقدام به بستن قرارداد با شخص مستخدم می‌نماید. این قرارداد بر حسب نوع سازمان از قانون خدمات کشوری (برای شرکت‌های دولتی) یا قانون کار مربوط به تامین اجتماعی (برای شرکت‌های خصوصی) تبعیت می‌نماید، هر سال نیز اگر تغییراتی ایجاد شود طبق بخشنامه جدید که از طرف وزارت تعاون، کار و رفاه اجتماعی ابلاغ می‌شود. در ادامه می‌توانید بخشنامه مربوط به سال 94 را ملاحظه نمایید.
بخشنامه شورای عالی کار سال 94 در رابطه با حقوق و دستمزد
بنا به درخواست دوستان فایل مروبط به محاسبات حقوق و دستمزد سال 94 به همراه درج تمام ملاحظات قانونی و نرخ‌های سال 94 به همراه فیش حقوقی در اختیار قرار می‌گیرد
فایل مربوط به حقوق و دستمزد سال 94
یکی از کسورات قانونی که باید محاسبه و پرداخت شود، مالیات حقوق می‌باشد، همه ساله تغییراتی در مورد معافیت‌ها و گاها نرخ معافیت ها اعمال می‌شود و که در محاسبات تاثیر گذار می‌باشد، در ادامه فایل مربوط به محاسبات مالیات حقوق سال 94 در اختیار دوستان قرار می‌گیرد.
فایل مربوط به محاسبه مالیات حقوق سال 94
یکی دیگر از کسوراتی که بعضی از شرکت‌ها، از حقوق کارکنان کسر می‌نمایند (علاوه بر بیمه و مالیات) گاها وام نیز می‌باشد، در ادامه فایل مربوط به نحوه محاسبه وام و اقساط مربوط و نحوه محاسبه اصل و فرع وام، در اختیار دوستان قرار می‌گیرد.
فایل مربوط به محاسبه وام و اقسط مربوطه
توابعی که در فایل های اکسل استفاده شده عبارت است از (vlookup، choose، if-to-if,pmt, ppmt ,ipmt ,sum) .
(پسورد فایل‌ها: هنگامی که می‌خواهیم شرح سطرهای یک سند را در سیستم مالی نوسا به یکباره تغییر دهیم از کلید ctrl بعلاوه یک کلید دیگر صفحه کلید تحت عنوان ... استفاده می‌نمایم آن یک کلید یا حرف چیست ؟)
یک کارنامه تهیه نمایید که شامل: نام درس، تعداد واحد و نمره هر درس و معدل کل باشد.
ج: برای رسیدن به خواسته منظور، ابتدا یک sheet که شامل موارد زیر است تهیه می‌نماییم.
فرمول نویسی در اکسل:
یکی از مهمترین دلایل محبوبیت اکسل این است که می‌توان حجم وسیعی از داده ها را جمع آوری، تجزیه و تحلیل نمود. این مهم زمانی امکان پذیر است که از فرمول نویسی در اکسل استفاده نماییم.
اکسل برای نوشتن فرمول ابزاری را در اختیار قرار می‌دهد به نام نوار فرمول که در ادامه با این ابزار و نحوه استفاده از آن بیشتر آشنا می‌شویم.
فرمولی که در این نوار ابزار نوشته می‌شود بر روی سلولی که فعال است و روی آن قرار داریم اعمال می‌شود، همانطور که می‌دانیم در سلول انواع آیتم‌ها نظیر عکس، عدد، متن و ... می‌توان اعمال نمود. اما زمانی که از علامت مساوی (=) استفاده می‌نماییم بدین معنی می‌باشد که با فرمول سرو کار داریم.
انواع فرمول نویسی در اکسل:
الف - فرمول نویسی مطلق:
زمانی که با اعداد سروکار داریم این نوع فرمول نویسی کاربرد دارد، در واقع این نوع فرمول نویسی همانند ماشین حساب رفتار می‌کند و یک نوع عملیات ریاضی را محاسبه می‌نماید. مثلا اگر در سلول A1 بنویسیم 5*1.5= و کلید ENTER را فشار دهیم در این سلول عدد 7.5 نمایش داده می‌شود.
باید توجه داشت که این نوع فرمول نویسی کمتر مورد استفاده قرار می‌گیرد.
ب - فرمول نویسی نسبی: در این نوع فرمول نویسی دیگر با اعداد سروکار نداریم بلکه با خود سلول ها سروکار داریم، یعنی بجای اعداد نام سلول ها را در فرمول وارد می‌نماییم. بطور مثال در سلول A1 عدد 5 و در سلول B1 عدد 1.5 را داریم و می‌خواهیم حاصلضرب این دو عدد در سلول C1 نمایش داده شود، برای این کار، سلول C1 را فعال می‌نماییم و فرمول (B1*A1=) در نوار فرمول می‌نویسیم ENTER می‌نماییم.
