پاسخ : ترفند های اکسل [h=2]معرفی ابتدایی توابع مالی در اکسل [/h]توابع مالي در اكسل را می توان به شرح زیر نام برد : .SLN تابع محاسبه هزینه استهلاک به روش خط مستقیم . SYD تابع محاسبه هزینه استهلاک به روش مجموع سنوات . DB تابع محاسبه هزینه استهلاک نزولی در مدت معین . DDB تابع محاسبه هزینه استهلاک به روش نزولی مضاعف در مدت معین . VDB تابع محاسبه دوره خاص هزینه استهلاک به روش نزولی . FV تابع محاسبه ارزش آتی (آینده) سرمایه گذاریها . PV تابع محاسبه ارزش فعلی خالص سرمایه گذاری (اقساط مساوی) . NPV تابع محاسبه ارزش فعلی خالص سرمایه گذاری . XNPV تابع محاسبه ارزش فعلی خالص سرمایه گذاری بر حسب تاریخ . PMT تابع محاسبه اقساط وام . PPMT تابع محاسبه اقساط مربوط به اصل وام . IPMT تابع محاسبه اقساط مربوط به بهره . Nper تابع محاسبه تعداد دوره های مورد نیاز برای سرمایه گذاری . Rate تابع محاسبه نرخ بهره . IRR تابع محاسبه نرخ بازده داخلی سرمایه گذاری . XIRR تابع محاسبه نرخ بازده داخلی سرمایه گذاری بر حسب تاریخ . MIRR تابع محاسبه نرخ داخلی کارکرد سرمایه . ACCRINT تابع محاسبه بهره متعلقه اوراق قرضه از زمان صدور تا بازخرید اوراق . ACCRINTM تابع محاسبه بهره متعلقه اوراق قرضه از زمان صدور تا بازخرید اوراق . CUMIPMT تابع فرع انباشته اقساط متوالی یک وام
پاسخ : ترفند های اکسل [h=2]مهارتهای اکسل (تابع Sumproduct) [/h]فرمول کاربردی و مفید Sumproduct فرض کنید دو لیست از اعداد دارید .به صورت بسیار ساده می توان با استفاده از تابع Sumproduct زیگمای A*B را به صورت نمایش داده شده محاسبه نمود . در این مرحله ممکن است به صورت یک تابع تقریبا بی فایده به نظر برسد اما با مطالعه ادامه مطلب خواهید دید که همه چیز تغییر خواهد کرد و شما نیز به مفید و کاربردی بودن این تابع پی خواهید برد .فرض کنید شما یک لیست از اطلاعات فروش شامل ستون های نام و نام خانوادگی ، منطقه ، محصول و فروش دارید.میخواهید بدانید که چه تعداد کالا به آقای Luke فروخته شده است؟ ساده است، شما با استفاده از فرمول SUMIF براحتی میتوانید این مسئله را حل کنید .اما صبر کنید ، اگر بخواهید بدانید چه تعداد کالا به آقای Luke در منطقه غرب «west» به فروخته شده است چه خواهید کرد ؟ما به شما استفاده از تابع کاربردی Sumproduct را پیشنهاد می کنیم.هرچند راه های دیگری از قبیل استفاده از تابع Sumifs که در آفیس 2007 به بعد گنجانده شده وجود دارد اما در اینجا قصد آموزش تابع Sumproduct را داریم.فرض کنید داده ها در محدوده A1:A10 قرار دارد .(نام و نام خانوادگی در ستون A ، منطقه در ستون B ، محصول در ستون C و فروش در ستون D )فرمول به صورت زیر خواهد بود .=SUMPRODUCT((A1:A10="Luke Skywalker"),(B1:B10="West"),D1d10)یک دقیقه وقت دارید تا بفهمید که چه انجام شده است ؟همانطور که احتمالا متوجه شده اید تحلیل فرمول فوق به شکل زیر خواهد بود .
پاسخ : ترفند های اکسل [h=2]قفل کردن سلول های دلخواه در اکسل [/h]علاوه بر به کار بردن کلمه رمز برای حفاظت از پرونده هایتان . اکسل ویژگی هایی را برای حفاظت از کار - یعنی حفاظت از کتابچه های کار . ساختار کتابچه های کار . خانه های کاربرگ . به صورت انفرادی . اشیا گرافیکی . نمودارها . سناریوها و پنجره ها و غیره و...... که مانع دسترسی و یا ویرایش آنها توسط افراد غیر مجاز می گردد ارائه می هد . و نیز این امکان را فراهم می سازد که بتوانید عملیات خاص ویرایش را بر روی برگه های حفاظت شده انجام دهید . بنا به طور پیش فرض . اکسل از کلیه خانه های کاربرگ و نمودار ها حفاظت می کند . ولی این حفاظت غیر فعال می باشد . تصویر زیر را در نظر بگیرید : Click here to view the original image of 762x226px. همانطور که ملاحظه می کنید تصویر بالا قسمتی از یک لیست حقوق و دستمزد میباشد . که در آن برای محاسبه اقلامی مانند مزد ماهانه . و یا جمع حقوق و مزایا از فرمول استفاده شده است . حال اگر شما قصد داشته باشید که از این سلولها حفاظت کنید بدین معنی که فرمول های استفاده شده در این کاربرگ نمایش داده نشوند . و کاربران با کلیک بر روی سلول هایی که در آنها از فرمول استفاده شده است قادر نباشند فرمول استفاده شده را ببینند و یا آنها را ویرایش کنند . برای انجام این کار به ترتیب زیر عمل کنید : 1- ابتدا با استفاده از Ctrl+A تمام سلول های کاربرگ را انتخاب کنید . 2- سپس بر روی کاربرگ کلیک راست کنید . و گزینه Format Cells را انتخاب نمایید . 3- در پنجره Format Cells بر روی سربرگ Protection کلیک کنید و تیک گزینه Locked را غیر فعال کنید و بر روی OK کلیک نمایید . 4- سلولهایی که حاوی فرمول هستند را انتخاب کنید و دوباره مرحله 3 را انجام دهید . ولی در این مرحله تیک گزینه Locked را فعال کنید . 5- بر روی تب Review کلیک کنید . و بر روی گزینه Protect Sheet کلیک کنید . 6- در پنجره Protect sheet به جز گزینه ُSelect Lock Cells تیک تمام گزینه ها را فعال کنید و رمز عبور خود را وارد نمایید . 7- در پنجره بعدی هم رمز عبور خود را دوباره وارد کنید و بر روی Ok کلیک کنید . بعد از انجام این مراحل خواهید دید که قادر نخواهید بود سلولهایی که حاوی فرمول هست را انتخاب کنید .
پاسخ : ترفند های اکسل [h=2]کارتکس محاسبه کارکرد و اضافه کاری در اکسل[/h] اگر شما در شرکتی مشغول کار هستید و باید ورود و خروج پرسنل رو به طور دستی وارد کنید و در آخر ماه هم اضافه کار پرسنل . میزان مرخصی ساعتی . مرخصی استحقاقی . رو به طور دستی محاسبه کنید . می توانید از این برنامه که در اکسل طراحی شده است استفاده کنید . برنامه ای که الان دانلود می کنید به طور آزمایشی میباشد . و فقط شما می توانید اطلاعات ورود و خروج رو در 3 ماه اول سال وارد کنید .در زیر شما می توانید تصاویر از این برنامه را مشاهده کنید . Click here to view the original image of 670x518px. ویژگیهای این برنامه : 1- ثبت 3 ورودی و خروجی برای پرسنل . 2- محاسبه مرخصی ساعتی . 3- محاسبه مرخصی روزانه . 4- محاسبه مرخصی استعلاجی . 5- محاسبه اضافه کار ماهانه . راهنمای استفاده : 1- ابتدا برنامه را از لینک زیر دانلود کنید . دانلود 2- قبل از باز کردن فایل در برنامه اکسل تنظیمات زیر را انجام دهید : 1- از منوی File بر روی گزینه Options کلیک کنید . 2- در پنجره Excel Options و در سمت راست بر روی گزینه Trust Center کلیک کنید . 3- در سمت چپ بر روی گزینه Trust Center Setting کلیک کنید . 4- در پنجره Trust Center بر روی گزینه Macro Setting کلیک نمایید و تیک گزینه Enable all macro را فعال کرده و بر روی ok کلیک نمایید . 1- بعد از وارد شدن به محیط برنامه بر روی گزینه ثبت اطلاعات کلیک نمایید . 2- در پنجره باز شده اطلاعات مورد نیاز را وارد نمایید . نکته 1 : فیلد وضعیت شیفت اختیاری میباشد و اثری بر عملکرد محاسبات نخواهد گذاشت . نکته 2 : فیلد کارکرد الزامی میباشد و شما میزان کارکرد عادی روزانه پرسنل مورد نظر خود را باید در این فیا وارد نمایید . مثلا پرسنل اداری از ساعت 7 تا 15 بنابراین کارکرد عادی 8:00 میباشد . 3- سپس بر روی ثبت اطلاعات کلیک نمایید . با انجام این کار اطلاعات از فرم به ماه مورد نظر شما منقل خواهند شد . 4- در قمست ماه شما می توانید با انتخاب گزینه عادی از ستون وضعیت مربوط به هر ماه ساعت کارکرد را همانند تصویر زیر وارد نمایید . اگر پرسنل شما از مرخصی ساعتی استفاده کرده است می توانید مقدار مرخصی ساعتی را در روز مورد نظر در ستون مرخصی ساعتی وارد نمایید .
پاسخ : ترفند های اکسل [h=2]جمع اعداد یک محدوده حاوی خطا[/h] شاید این موضوع را متوجه شده باشید که اگر به هر تابعی در اکسل یک خطا به عنوان ورودی بدهیم ، خروجی آن تابع خطا خواهد شد . برای رفع این مشکل می توان از فرمول های برداری به طریقه زیر بهره گرفت . نکته : پس از نوشتن فرمول به جای اینتر ، Ctrl + Shift+ Enter را همزمان بزنید =SUM(IFERROR(D2d5);"")) در ضمن تابع IFFERROR که در بالا استفاده شده در اکسل 2007 و بالاتر موجود است اگر از اکسل 2003 استفاده می کنید باید از فرمول زیر استفاده نمایید .(حتما در انتها کلید های Ctrl+Shift+Enter را بزنید) =SUM(IF(ISERROR(D2d5),"",D2d5))در صورتی که در فهم این مطلب با مشکل مواجه شدید در قسمت نظرات مطرح کنید . همچنین اگر روش های دیگری برای اینکار مد نظرتان است در قسمت نظرات با دیگران به اشتراک بگذارید.
پاسخ : ترفند های اکسل [h=2]تبدیل واحدهای اندازه گیری در اکسل [/h]تابع Convert واقعا تابع فوق العاده ای است . تعجب بر انگیز نیست که این تابع تبدیل های مختلفی را انجام می دهد و مخصوصا مقیاس ها را تبدیل می کند . تعداد مقیاس هایی که این تابع آنها را تبدیل می کند واقعا قابل بیان نیست . این تابع فوت را به اینچ . فارنهایت را به سلسیوس . پینت را به لیتر . اسب بخار را به وات تبدیل می کند . و بسیاری از نبدیل های دیگر را انجام می دهد . در واقع . 10 مقوله وجود دارد که هر یک در برگیرنده ده ها واحد اندازه گیری است که تبدیل از آنها و به آنها انجام می شود . این مقوله ها عبارتند از : 1- وزن و جرم 2 - فاصله 3- زمان 4 -فشار 5 -نیرو 6 - انرژی 7 - توان 8 - مغناطیس 9 -دما 10 - مقیاس مایع این تابع سه آرگومان می گیرد : مقدار . واحد اندازه گیری که تبدیل باید از آن انجام شود . و و حد اندازه گیری که تبدیل باید به آن انجام شود . مثلا در این جا . تابع کار تبدیل 10 گالن را به لیتر انجام می دهد : =Convert(10,"gal","l") مثال : برای اینکه بدانیم 150 فوت چند متر میباشد می توان از فرمول زیر استفاده کرد : =CONVERT(150,"ft","m")
پاسخ : ترفند های اکسل [h=2]اکسل سخنگو [/h] امروز میخام یه قابلیت جالب از اکسل رو براتون بگم. که با استفاده از اون میتونین کاری کنین که اکسل براتون حرف بزنه. اگه همین الان نرم افزار اکسل رو باز کنین بهتره. از قسمت Quick Access Toolbar گزینه More Command رو کلیک کنین. حالا از لیست Choose commandsfrom گزینه All commands رو انتخاب کنین. حالا از توی لیست به سمت پایین حرکت کنین تا برسین به Speak Cells ، از این گزینه تا گزینه Speak Cell on Enter رو توسط دکمه Add انتخاب و از پنجره خارج بشین. حالا توی نوار ابزار Quick Access این ابزارها رو میبینین. با کلیک روی هر گزینه ازش استفاده کرده و حالشو ببرین. البته این سخنگو فقط اعداد و حروف لاتین رو میخونه ... فارسی بلد نیست .!
پاسخ : ترفند های اکسل [h=2] پیدا کردن آخرین تاریخ [/h]همه دوستان میدونن که با استفاده از تابع LOOKUP میشه مقداری رو بر اساس آیتم مشخص شده پیدا کرد. مثلا فرض کنید یک جدولی دارین که توی اون روزهایی که یک کالای مشخص فروش رفته رو مشخص کردین، حالا میخاین بفهمین که آخرین روزی که فروش کالای مشخصی رو داشتین کی بوده... به جدول زیر توجه کنین با استفاده از فرمول زیر میشه این کارو براحتی انجام داد. (خاصیت تابع LOOKUP خیلی مهمه !)=LOOKUP("y";B2:E2;$B$1:$E$1)این فرمول آخرین تاریخی که در جدول با علامت x به ثبت رسیده رو نشون میده...
پاسخ : ترفند های اکسل بعضی وقتا پیش میاد که شما میخاین در یک ستون از اکسل همیشه آخرین مقدار وارد شده رو مشخص کنین. بسیار خوب با استفاده از فرمولهای اکسل میشه این کارو انجام داد. (البته اینم بگم که ستون مورد نظر باید پیوسته باشه و همه سلولهاش پر باشه ...) سپس از تابع INDEX به صورت زیر استفاده میکنیم. ستون اول A و ستون دوم B است. =INDEX(B:B;COUNT(B:B)) با این کار همیشه آخرین مقدار وارد شده در ستون مورد نظر در سلولی که این تابع نوشته شده نمایش مییابد.
پاسخ : ترفند های اکسل [h=2] اعداد تکراری [/h]اگر بخواهید بفهمید که آیا در یک ستون شامل اعداد، عدد تکراری وجود دارد یا نه به روش زیر عمل کنید. فرض کنید اعداد در ستون a از a1 تا a10 وارد شده است. از فرمول زیر استفاده کنید: =if(countif($a$1:$a$10;mode($a$1:$a$10))>1;"تکر اری ندارد";"تکراری دارد") به همین راحتی ... تابع mode عددی رو پیدا میکنه که بیشترین فراوانی رو داره و تابع countif هم تعداد اون عدد رو در محدوده انتخاب شده میشماره بنابراین اگه نتیجه بزرگتر از 1 شد پس ما عدد تکراری داریم.
