پاسخ : ترفند های اکسل [h=2]تابع CHOOSE [/h]تابع Choose یکی از توابع بسیار مفید اکسل است که در نحوه استفاده از آن را با ذکر یک مثال توضیح میدهم.فرض کنیم که جدولی به شرح ذیل داریم که خریدهای یک ماهه شرکتی را نشان میدهد.با نوشتن تابعی جمع کل اقلام مصرفی، نیمه مصرفی و سرمایه ای را مشخص کنید.برای بدست آوردن جمع تفکیکی هزینه ها از تابع CHOOSE که ساختار کلی آن به شرح ذیل می باشد استفاده می کنیم. =(CHOOSE(Index Number, value 1, value 2, … ) Index number یک عدد می باشد. Value 1 ,Value 2, … مقادیر هستند.اگر Index Number برابر 1 باشد آنگاه مقدار Value 1 برگردانده میشود. اگر Index Number برابر 2 باشد آنگاه مقدار Value 2 برگردانده میشود. و به همین شکل می توان تا 255 مقدار در این دستور استفاده نمود.در این مثال چون هدف جمع مقادیر می باشد پس تابع CHOOSE و SUM را به شکل زیر با هم ترکیب می کنیم. =SUM((CHOOSE(Index Number, value 1, value 2, … )) در شکل سلول C16 را به جای Index Number و محدوده C4:C13 را به عنوان Value 1 و محدوده D4:.D13 را به عنوان Value 2 انتخاب نموده ایم.با وارد کردن عدد 1 جمع مبالغ ستون "اقلام مصرفی"، با وارد کردن عدد 2 جمع مبالغ ستون "اقلام نیمه مصرفی " و با وارد کردن عدد 3 جمع مبالغ ستون "اقلام سرمایه ای" به دست می آید.
پاسخ : ترفند های اکسل [h=2]حل معادله درجه دوم به کمک اکسل [/h]در این پست قصد دارم معادله درجه دوم که در دوره دبیرستان آن را دیده اید به کمک اکسل و با استفاده از تابع IF حل کنم.شکل کلی این معادله به صورت 0=ax[SUP]2[/SUP]+bx+c می باشد که در آن a≠0 فرض شده است.برای حل این معادله باید ابتدا دلتا را که از رابطه b[SUP]2[/SUP]-4ac بدست آوریم که سه حالت ممکن است اتفاق بیفتد.اگر مقدار دلتا عددی مثبت باشد آنگاه معادله دارای دو جواب می باشد که از رابطه بدست می آید.اگر دلتا مساوی صفر باشد آنگاه معادله دارای ریشه مضاعف (تکراری) می باشد که از رابطه به دست می آید.اگر دلتا کوچکتر از صفر باشد (عدد منفی) آنگاه معادله در دامنه اعداد حقیقی جواب ندارد. چون دلتا زیر رادیکال با فرجه زوج قرار می گیرد پس نمی تواند عددی منفی باشد. برای حل این معادله به کمک اکسل ابتدا باید مقدار دلتا را با توجه به ضرایب a, b, c محاسبه کنیم.چون مقدار دلتا برابر است با: b[SUP]2[/SUP]-4ac آن را در اکسل باید به این صورت بنویسیم: b^2-4*a*c . در اکسل برای به توان رساندن از علامت ^ و برای علامت ضرب از * استفاده می کنیم.با توجه به شکل مقدار عبارت b^2-4*a*c Delta= ابتدا حساب می کنیم.در این مثال ضرایب معادله را a=1 , b=-1 , c=-6 در نظر گرفته ایم. پس مقدارDelta=(-1)^2-4*(1)*(-6)=25چون مقدار Delta عددی مثبت است پس معادله دارای دو جواب خواهد بود که عبارتند از x1=3 و x2=-2برای محاسبه جوابها در اکسل فرمولها را به شکل زیر می نویسیم. =IF(C4>=0,”x1=”, “ “) توضیح: در این فرمول سلول C4 مقدار Delta می باشد. اگر مقدار سلول C4 بزرگتر یا مساوی صفر باشد آنگاه عبارت =x1 نوشته می شود و اگر Delta یعنی مقدار C4 کوچکتر از صفر باشد در این سلول عبارتی نوشته نمی شود.ریشه های معادله را (در صورت وجود) همانطور که در شکل می بینید به صورت زیر بدست می آوریم:X1=IF(C4>=0,(-B3+C4^0.5)/(2*C3),””)X2=IF(C4>=0,(-B3-C4^0.5)/(2*C3),””)در سلول D7 فرمول=IF(C4<0,”Delta<0”, “ “)در صورتی که مقدار Delta کوچکتر از صفر باشد عبارت Delta<0 را نمایش میدهد و در غیر این صورت در این سلول نوشته ای دیده نمی شود.در سلول C7 در صورتی که مقدار Delta کوچکتر از صفر باشد عبارت " معادله ریشه حقیقی ندارد" نمایش داده می شود و در غیر این صورت در این سلول نوشته ای دیده نمی شود. Click here to view the original image of 730x361px. تذکر: عبارت C4^.5 یعنی مقدار عددی سلول C4 که آن را به توان 0.5 رسانده ایم. به عبارت دیگر در این شکل با قرار دادن a=1 , b=-4 , c=4 مقدار دلتا برابر صفر و در نتیجه معادله دارای ریشه مضاعف (تکراری) 2 می باشد. در این شکل با قرار دادن a=1 , b=3 , c=4 مقدار دلتا عددی منفی شده و در نتیجه معادله در دامنه اعداد حقیقی جواب ندارد.
پاسخ : ترفند های اکسل [h=2]جمع شرطی در اکسل- قسمت دوم sumifs [/h]در پست قبلی تابع SUMIF را بررسی کردیم و گفتیم که این تابع برای جمع زدن یک ناحیه از اعداد با اعمال شرط خاصی به کار می رود. این تابع فقط یک شرط را می پذیرد. حال اگر بخواهیم جمع را با اعمال بیش از یک شرط داشته باشیم باید از تابع SUMIFS استفاده نماییم.شکل کلی این تابع به صورتSUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]در این تابع بر خلاف SUMIF اول باید محدوده ای را که قصد جمع زدن داده های آن را داریم (داده های نوع عدیی) وارد نماییم و سپس محدوده های شرط را تعیین کنیم.در مثال شکل زیر نحوه انجام کار مشخص شده است.می خواهیم جمع کل مبلغ فروش فروشنده ای به اسم "علی" را به شرطی که تعداد کل فروشها بیشتر از 10 عدد باشد بدست آوریم.همانطور که ملاحظه میگردد، در این مثال باید دو شرط را با هم داشته باشیم . شرط اول نام فروشنده که باید "علی" باشد و شرط دوم اینکه تعداد فروش از 10 عدد بیشتر باشد.همانطور که در جدول دیده می شود فرمول باید جمع کل فروش ردیف های 4 و 13 جدول را نمایش دهد.فرمول را به صورت زیر وارد می کنیم. ( "R2:R14,">10,"علی=",SUMIFS(T2:T14,Q2:Q14= حال اگر فرمول فوق را به صورت ( "R2:R14,">=10,"علی=",SUMIFS(T2:T14,Q2:Q14= وارد کنیم مبلغ ردیف 1 نیز به جمع اضافه خواهد شد.
پاسخ : ترفند های اکسل [h=2]جابجا کردن سلولهای / سطر/ ستون در excel با کلید میانبر [/h]اگر شما بخواهید چند سطر و یا ستون را در اکسل جابجا کنید حتما با CUT اینکار را انجام می دهید و اگر جای خالی باشد شاید با استفاده از Drag کردن کادر دور سلولابتدا سطر / ستون / سلول مورد نظر خود را انتخاب کنید.سپس کادر سیاه رنگ دور قسمتی را که انتخاب کرده اید را کلیک کنید و (رها نکنید*آنرا به هر جایی که می خواهید انتقال یابد Drag کنید و کلید Shift را هم نگه داریدمی بینید که سلول excel به سادگی Move می شود
پاسخ : ترفند های اکسل [h=2]پیدا کردن تکراریها در Excel [/h]برای یافتن مقادیر تکراری در یک ستون از Excel ۱- ابتدا ستون یا محدوده مورد نظر خود را انتخاب کنید. ۲- در سربرگ Home از نوار ابزار Excel (به این نوار در Excel 2007 به بعد Ribbon میگویند) گزینه Conditiona Formatting را بزنید. 3- در لیست باز شده، گزینه Highlight Cell Ruls را بزنید. 4- در این لیست گزینه Duplicate Value را بزنید. 5 – در پنجره آخر، از شما پرسیده میشود که سلولهای تکراری چه رنگی شوند. چگونه در Excel از ورود اعداد یا مقادیر تکراری توسط کاربر جلوگیری کنیم؟همانطور که شاید از قبل بدانید ، شما میتوانید روی سلولهای Excel ابزار Validation را فعال کنید، ابزار Excel Validation مقادیری را که شما در سلول وارد میکنید چک میکند و در صورتیکه که معتبر باشد آنرا قبول مینماید.معتبر بودن با شرطی که شما قبلا روی این سلول گذاشته اید سنجیده میشود.ابتدا ستون مورد نظر را انتخاب نمایید.1- دقت نماید که آدرس سلول فعال شما چیست، مثلادر شکل زیر سلول فعال A1 است.2- از Ribbon به سربرگ Data بروید و سپس گزینه Validation را انتخاب کنید.3- مطابق با شکل تنظمات را انجام دهید. تابع COUNTIF(A:A,A1) در اکسل مشخص می کند که در ستون A ، چند بار مقادر سلول A1 تکرار شده است و هدف ما این است که این مقدار تکراری نباشد، بنابراین باید این تعداد برابر با یک باشد ، که تکراری نشود.
پاسخ : ترفند های اکسل [h=2]فایلهای Excel شما هرگز از بین نمی رود ! [/h]کجا می توانید فایلهای Excel خود ذخیره کنیدکه هرگز از بین نروند؟فضای وب ابزار بسیار را در اختیار کاربران حرفه ای قرار می دهد زیرا کاربران رایانه مایل هستند که بسیاری از فایلهای خود را همواره در دسترس داشته باشند و بتوانند آنها را در محل مطمئنی که احتمال از بین رفتن فایها نباشد نگهداری کنند.شرکت Microsoft برای کاربران خود یک فضای با حجم به صورت رایگان در نظر گرفته است و در محصول Office 2010 ، مانند نرم افزار Excel , Word , Powerpoint در منوی File گزینه Share شما می توانید گزینه Save to SkyDrive را مشاهده نمایید. برای استفاده از این سرویس به سایت www.skydrive.com رفته و در آنجا ثبت نام نمایید توجه داشته باشید که ایمیل شما بعنوان نام کاربری در نظر گرفته می شود و لازم نیست که حتما ایمیل شما از Hotmail باشد.بعد از ثبت نام و ورود به فضای اختصاصی خود می توانید فایلهای Excel و ... را در این فضا ذخیره کنید و در نظر داشته باشید که پشتیبانی و Backup فایلهای شما توسط شرکت Microsoft انجام می شود و شاید بتوان گفت که "فایلهای Excel , Word , … شما هرگز از بین نمی رود.مهمترین ویژگی این محصول در مقایسه با سایر محصولات مانند Google Docs در این است که میانبر SkyDrive در منوی فایل Microsoft Office Excel 2010 قرار داده شده است. Click here to view the original image of 657x487px.
پاسخ : ترفند های اکسل معرفی Google Docs : یکی از خدمات شرکت گوگل Google Docs است که توسط آن می توان فایلها Documents(word) ، SpreadSheets (Excel) ،Presentation(PowerPoint) را ایجاد کرد. از جمله امکانات دیگر این تکنولوژی رایگان شرکت گوگل می توان به موارد : [*=center]ایجاد فایلها : به سادگی می توان کارهای اولیه مانند بالت، مرتب سازی، ساخت جدول، فرمول نویسی و عوض کردن فونت را انجام داد البته به صورت مجانی . [*=center]آپلود فایها: Google Docs انواع فایهای متداول و معمول مانند : Doc , XLS , RTF , CSV , PPT , ODT , ODS و ... را قبول می کند ، می توانید این فایلها را آپلود کنید. [*=center]سادگی کار: مانند سایر نرم افزارها با Toolbar ها کار می کنید . [*=center]Share کردن فایلها و کار با آنها: [*=center]می توانید به صورت همزمان روی فایلهایتان با سایر همکارانتان کار کنید، آنها را دعوت کنید تا فایلتان را مشاهده کنند. [*=center]می توانید تنظیم کنید که چه کسانی اجازه دیدن داردند و چه کسانی اجازه ویرایش [*=center]می توانید همزمان روی فایهایتان کار کنید، و در ضمن نسخه های قدیمی فایل نیز موجود است و از Chat آنلاین گوگل نیز استفاده کنید. روی لینک زیر برای دیدن نمونه فایلی که به اشتراک گذاشته شده است و برای عموم قابل دیدن است کلیک کنید Published File(اگر من که بوجود آورنده این فایل هستم به شما اجازه بدهم می توانید این فایل را ویرایش کنید) نمونه فایل آموزش به صورت اکسل (این فایل بر روی سرور گوگل است و یک فایل داینامیک است یعنی اگر من فایل ویرایش کنم شما با ویرایش جدید فایل را دانلود می کنید بدون اینکه مجبور باشم لینک را آپدیت کنم) [*=center]دسترسی و امینت: فایلهای شما بر روی سرور گوگل هستند و از هر نقطه دنیا به صورت Web base قابل دسترس و هر زمان از طریق اینترنت و بدون نیاز به هیچ نرم افزاری قابل استفاده می باشند.فایها را به سادگی کپی و Export می کنید.به سادگی آنها را بر روی وب قرار می دهیدشروع کنید: [*=center]اگر در Google شما Account ندارید ، ایجاد کنید . (منظور همان ایمیل است J) [*=center]Login کنید . (منظور این است که وارد ایمیل خود شوید) [*=center]روی لینک Documents کلیک کنید. (این لینک در بالای صفحه ایمیل شما نشان داده شده است.) [*=center]خوب ادامه دهید ، با کمی دقت و حوصله خودتان متوجه خواهید شد که تا چه اندازه کار اینجا ساده است . به عکس زیر هم دقت کنید.)
پاسخ : ترفند های اکسل [h=2]چگونگی بازیابی فایل های خراب شده در Excel [/h] قطعا برای شما هم پیش آمده است که هنگام باز کردن یک فایل Excel با پیغام خطا مواجه شوید. مثلا: Excel cannot open the file xyz.xlsx because the file format or file extension in not valid. Verify that the file has not been corrupted and that file extension matches the format of the file. و فایل excel شما باز نمی شود. برای بازیابی و باز کردن اینگونه فایل ها، در اینجا برخی از تجربیات عملی فرساران را با شما به اشتراک می گذاریم. 1- نام فایل و مسیر فایل را تغییر دهید. دلیلش را نپرسید، اما این مورد در بسیاری از موارد کار کرده است. هم اسم فایل را تغییر دهید و هم فولدر آنرا . 2- از Open & Recover خود Excel استفاده کنید. در Excel هنگام Open کردن یک فایل می توانید مانند شکل زیر، از Recover استفاده کنید. در خیلی از موارد این گزینه می تواند فایل را باز کند. 3- محاسبات را خاموش کنید. این گزینه بسیار جالب است. اگر با گزینه بالا فایل باز نشد و بازهم پیغام خطا می داد، ابتدا در Excel Options محاسبات Excel را خاموش کنید و سپس از طریق روش شماره 2 اینکار را انجام دهید.
پاسخ : ترفند های اکسل [h=2]توابع کار با رشتهها و دادهها (25 تابع) [/h] [TABLE="class: cms_table, width: 659, align: center"] [TR] [TD="align: center"]ACS [/TD] [TD="align: center"]رشتهای را به half-width (single-byte)تبدیل میکند. [/TD] [TD="align: center"]=ASC("EXCEL") equals "EXCEL" [/TD] [/TR] [TR] [TD="align: center"]BAHTTEXT [/TD] [TD="align: center"]عددی را به عدد کشور تایلند تبدیل میکند. [/TD] [TD="align: center"] [/TD] [/TR] [TR] [TD="align: center"]CHAR [/TD] [TD="align: center"]عددی را به کاراکتر متناظرش در ANSIتبدیل میکند. [/TD] [TD="align: center"]=CHAR(33) à ! [/TD] [/TR] [TR] [TD="align: center"]CLEAN(text) [/TD] [TD="align: center"]برای حذف کاراکترهای غیر چاپی( از کامپیوترهای قدیمی) [/TD] [TD="align: center"] [/TD] [/TR] [TR] [TD="align: center"]CODE [/TD] [TD="align: center"]کاراکتری به عدد متناظرش در ANSIتبدیل میکند. [/TD] [TD="align: center"]=CODE(!) à 33 [/TD] [/TR] [TR] [TD="align: center"]CONCATENATE [/TD] [TD="align: center"]چند متن را بهم میچسباند. (مانند &عمل میکند.) [/TD] [TD="align: center"]="FAR"&" "&123 à FAR 123 [/TD] [/TR] [TR] [TD="align: center"]DOLLAR [/TD] [TD="align: center"]عددی را به فرمت Currency و متن تبدیل میکند. [/TD] [TD="align: center"] [/TD] [/TR] [TR] [TD="align: center"]EXACT [/TD] [TD="align: center"]دو متن را با هم مقایسه میکند که آیا برابرند. [/TD] [TD="align: center"]=EXACT("FAR";"Far")àFalse [/TD] [/TR] [TR] [TD="align: center"]FIND [/TD] [TD="align: center"]میتواند محل شروع یک رشته را در رشتهای دیگر جستجو کند. ( FAR#Far ) [/TD] [TD="align: center"]=FIND("S";"ARASH")à4 [/TD] [/TR] [TR] [TD="align: center"]SEARCH [/TD] [TD="align: center"]همانند FIND است با این تفاوت کهFAR=Far [/TD] [TD="align: center"] [/TD] [/TR] [TR] [TD="align: center"]FIXED [/TD] [TD="align: center"]عدد اعشاری را روند کرده و آنرا به متنتبدیل میکند. [/TD] [TD="align: center"]=FIXED(1234.223;2)à1,234.22 [/TD] [/TR] [TR] [TD="align: center"]LEFT [/TD] [TD="align: center"]از سمت چپ کاراکتر(ها) را جدا میکند. [/TD] [TD="align: center"]=LEFT("ARASH";2)à AR [/TD] [/TR] [TR] [TD="align: center"]LEN [/TD] [TD="align: center"]تعداد کاراکترهای یک ورودی را میشمارد. [/TD] [TD="align: center"]=LEN("SALE PRICE,")à11 [/TD] [/TR] [TR] [TD="align: center"]LOWER [/TD] [TD="align: center"]ورودی را تبدیل به حروف کوچک میکند. [/TD] [TD="align: center"]=LOWER("ARASH")à arash [/TD] [/TR] [TR] [TD="align: center"]MID [/TD] [TD="align: center"]به تعداد n کاراکتر از محل mام یک رشته را بر میگرداند. [/TD] [TD="align: center"]=MID("yahoo.com";7;3)àcom [/TD] [/TR] [TR] [TD="align: center"]PROPER [/TD] [TD="align: center"]حروف اول یک رشته را به حرف بزرگ و بقیه حروف را به حروف کوچک تبدیل میکند. [/TD] [TD="align: center"]=PROPER("THIS is a tes") à This Is A Test 123 [/TD] [/TR] [TR] [TD="align: center"]REPLACE [/TD] [TD="align: center"]جایگزینی یک متن در متن دیگر با شرایط تعیین محل و تعداد کاراکتر. [/TD] [TD="align: center"]=REPLACE("farzad";4;3;"id")à Farid [/TD] [/TR] [TR] [TD="align: center"]REPT [/TD] [TD="align: center"]کاراکتری را به دفعات تکرار میکند. [/TD] [TD="align: center"]=REPT("*";5)à ***** [/TD] [/TR] [TR] [TD="align: center"]RIGHT [/TD] [TD="align: center"]از سمت راست کاراکتر(ها) را جدا میکند. [/TD] [TD="align: center"]=RIGHT("ARASH";2) à SH [/TD] [/TR] [TR] [TD="align: center"]SUBSTITUTE [/TD] [TD="align: center"]رشتهای را با رشتهی جدیدی در یک متن جایگزین میکند. [/TD] [TD="align: center"]=SUBSTITUTE(A2, "Sales", "Cost") [/TD] [/TR] [TR] [TD="align: center"]T [/TD] [TD="align: center"]اگر ورودی آن یک رشته باشد ، همان رشته را بر میگرداند. (برای کار با سایر برنامههای صفحه گسترده) [/TD] [TD="align: center"] [/TD] [/TR] [TR] [TD="align: center"]TEXT [/TD] [TD="align: center"]یک عدد را به یک متن با فرمتی دلخواه تبدیل میکند. [/TD] [TD="align: center"]=TEXT(123.533;"#.## $")à123.53 $ [/TD] [/TR] [TR] [TD="align: center"]TRIM [/TD] [TD="align: center"]SPACEهای اضافی اول و بین متنها را حذف میکند. (برای کار با سایر برنامههای صفحه گسترده) [/TD] [TD="align: center"]=TRIM(" THIS IS A TEST 123") à THIS IS A TEST 123 [/TD] [/TR] [TR] [TD="align: center"]UPPER [/TD] [TD="align: center"]حروف کوچک را به حروف بزرگ تبدیل میکند. [/TD] [TD="align: center"]=UPPER("Far")à FAR [/TD] [/TR] [TR] [TD="align: center"]VALUE [/TD] [TD="align: center"]متنی را به مقدار عدد متناظرش تبدیل میکند. [/TD] [TD="align: center"]=VALUE("$1,000") à 1000 =VALUE("16:48:00")-VALUE("12:00:00") à0.2[/TD] [/TR] [/TABLE]
پاسخ : ترفند های اکسل [h=2] محاسبه سود تسهیلات تا سقف ۶۰ قسط با نرم افزار تحت اکسل [/h] موارد کاربرد: 1-محاسبه سود تسهیلات 2-محاسبه هر قسط به تفکیک اصل و بهره 3-محاسبه سود تحقق یافته و تحقق نیافته جهت استفاده حسابداران 4-تصمیم گیری جهت تسویه تسهیلات و یا ادامه آن تا آخرین قسط نوع فایل : xlsx حجم فایل : 38 KB فرمولی که برای محاسبه یک قسط استفاده شده دقیقا فرمول بانک می باشد و بعد از اینکه مبلغ یک قسط محاسبه شد مبلغ یک قسط در تعداد اقساط ضرب و نتیجه را از اصل وام کم میکنیم،سود کل تسیلات بدست می آید اما برای تفکیک اصل و بهره هر قسط نرخ بهره موثر در مانده اصل تسهیلات که در اختیار وام گیرنده است بهره قسط محاسبه و پس از کسر بهره قسط از مبلغ قسط اصل قسط بدست می آیدA=(C*R*(1+R)^N)/(((1+R)^N)-1) A=مبلغ هر قسط C=اصل تسهیلات R=نرخ بهره موثر N=تعداد اقساط R=r/12*k r=نرخ بهره اسمی سالانه k=فاصله اقساط مثلا اگر اقساط ماهانه باشه عدد ۱ و چنانچه اقساط سه ماهه باشه عدد ۳ سود کل تسهیلات=(N*A)-C
