ترفند های اکسل

[M o R i S]

تابع GCD

تابع GCD

شکل کلی تابعGCD(number1,number2, …)این تابع بزرگترین مقسوم علیه مشترک اعداد داده شده را محاسبه می کند. تعداد آرگومانهای این تابع می تواند تا 29 آرایه باشد.
مثال

GCD(12,15)=حاصل عدد 3می باشد.
GCD(96,450,300)=حاصل عدد6 می باشد.

​

 

[M o R i S]

تابع LCM

تابع LCM

شکل کلی تابعLCM(number1,number2, …)این تابع کوچکترین مضرب مشترک اعداد داده شده را محاسبه می کند. تعداد آرگومانهای این تابع می تواند تا 29 آرایه باشد.
مثال

LCM(12,15)=حاصل عدد 60می باشد.
LCM(96,450,300)=حاصل عدد7200 می باشد.
​

 

[M o R i S]

تابع POWER

تابع POWER

شکل کلی تابعPOWER(number,power)این تابع برای محاسبه یک عدد به توان عدد دیگر مورد استفاده قرار میگیرد.
مثال

POWER(2, 5)=حاصل عدد 32می باشد.
POWER(3,5)=حاصل عدد243 می باشد.
POWER(4,5/4)=حاصل عدد5.658654 می باشد.

​

 

[M o R i S]

تابع PRODUCT

تابع PRODUCT

شکل کلی تابعPRODUCT(number1,number2, …)این تابع بین 1 تا 30 عدد را در هم ضرب می کند.
مثال

PRODUCT(2, 5)=حاصل عدد 10می باشد.
POWER(2,5,7)=حاصل عدد70 می باشد.

​

 

[M o R i S]

تابع RAND

تابع RAND

شکل کلی تابع()RANDاین تابع یک مقدار تصادفی بین صفر و یک را تولید می کند. برای تولید یک عدد تصادفی بین دو عدد a و b از فرمول زیر استفاده می کنیم. RAND()*(b-a)+a
مثال

RAND()*(6- 4)+4=یک مقدار تصادفی بین 4 و 7 تولید می کند.​

 

[M o R i S]

تابع RANDBETWEEN

تابع RANDBETWEEN

شکل کلی تابع(RANDBETWEEN(bottom,topاین تابع یک مقدار تصادفی بین حد پایین و حد بالای مشخص شده را نمایش می دهد.

مثال

RANDBETWEEN(4,7)=یک مقدار تصادفی بین 4 و 7 تولید می کند.​

 

[M o R i S]

تابع QUOTIENT

تابع QUOTIENT

شکل کلی تابع(QUOTIENT(numerator,denumeratorاین تابع قسمت صحیح خارج قسمت تقسیم دو عدد را محاسبه می نماید. آرگومان اول مقسوم و آرگومان دوم مقسوم علیه می باشد.
مثال

QUOTIENT (6,4)=قسمت صحیح تقسیم عدد 6 بر 4 برابر 1 می باشد.

​

 

[M o R i S]

تابع SIGN

تابع SIGN

شکل کلی تابع(SIGN(numberاین تابع را تابع علامت می گویند. اگر عدد داخل پرانتز مبت باشد جواب برابر 1 و اگر عدد داده شده منفی باشد جواب برابر 1- و چنانچه این عدد صفر باشد پاسخ برابر صفر خواهد بود. مثال

(SIGN (-12=حاصل برابر -1می باشد.
SIGN 10=حاصل برابر 1 می باشد.
(SIGN (0=حاصل برابر 0 می باشد.

​

 

[M o R i S]

تابع SQRT

تابع SQRT

شکل کلی تابع(SQRT(numberاین تابع ریشه دوم (جذر) عدد داده شده را محاسبه می کند.
مثال
(SQRT 144=حاصل برابر 12 می باشد.​

 

[M o R i S]

تابع SQRTPI

تابع SQRTPI

شکل کلی تابع(SQRT(numberاین تابع ابتدا عدد داده شده را در عدد پی (3.1415) ضرب کرده و سپس جذر آن را محاسبه می کند.


مثال(SQRTPI(3=حاصل برابر 3.06998 می باشد.​

 

[M o R i S]

تابع MOD

تابع MOD

شکل کلی تابع(MOD(number,Divisorاین تابع باقیمانده تقسیم عدد اول بر عدد دوم را محاسبه می کند.
مثال

(MOD=( 12,5حاصل برابر 2 می باشد.

​

 

[M o R i S]

تابع لگاریتم نپر LN

تابع لگاریتم نپر LN

شکل کلی تابع(LN(numberاین تابع لگاریتم طبیعی عدد مورد نظر را محاسبه می کند. این تابع معکوس تابع EXP می باشد. پایه این لگاریتم عدد نپر (e) می باشد. مثال

(LN(12=حاصل برابر 2.484907 می باشد.​

 

[M o R i S]

تابع لگاریتم در مبنای دلخواه

تابع لگاریتم در مبنای دلخواه

شکل کلی تابع([LOG(number, [baseاین تابع لگاریتم در مبنای 10 را محاسبه می کند. عدد مبنا در این تابع اختیاری بوده و در صورتی که ذکر نشود فرض بر این است که مبنا عدد 10 می ابشد.مثال

(LOG (12,3)حاصل برابر 2.26186 می باشد.
(LOG (5=حاصل برابر 0.69897 می باشد.

​

 

[M o R i S]

تابع لگاریتم در مبنای ده LOG 10

تابع لگاریتم در مبنای ده LOG 10

شکل کلی تابعLOG10 (number)این تابع لگاریتم یک عدد در مبنای 10 را محاسبه می کند.

مثال

(LOG10 (12=حاصل برابر 1.079181 می باشد.​

 

[M o R i S]

چگونه در اکسل تابع مورد نیاز خود را ایجاد کنیم؟

چگونه در اکسل تابع مورد نیاز خود را ایجاد کنیم؟

برنامه نویسی در اکسل به کمک نوعی از زبان ویژوال بیسیک انجام می شود که به اختصار به آن VBA (Visual Basic for Applications) گفته می شود. این امکان همزمان با نصب برنامه های آفیس نصب می شود و نیاز به نصب جداگانه ندارد.برای آشنایی مختصر با این برنامه ذکر چند اصطلاح ضروری است.سابروتین (subroutine) : برنامه های بزرگ نوشته شده در محیط VBA از قسمتهای کوچکتری به نام سابروتین (رویه) تشکیل می شوند. هر سابروتین با کلمه کلیدی Sub شروع می شود و با کلمه کلیدی End Sub پایان می یابد. در واقع می توان گفت که سابروتین کوچکترین واحد یک برنامه است که دستورات داخل این قسمت باعث انجام کاری می شوند. کلمات روتین – پراسیجر – متد و فانکشن نیز نامهای دیگری برای سابروتین می باشند.ماژول: تعدادی از سابروتین ها با هم تشکیل یک ماژول را می دهند.برای نوشتن یک برنامه به شرح ذیل عمل می کنیم.

پس از باز کردن اکسل با زدن کلیدهای Alt+F11 به محیط برنامه نویسی (VBA) وارد می شویم.از پنجره ظاهر شده و از منوی Insert گزینه Module را انتخاب میکنیم تا یک ماژول جدید ایجاد شود. مطابق شکل پنجره project explorer ظاهر می شود. اگر این پنجره را نمی بینید می توانید از گزینه View آن را نمایش دهید یا از Ctrl+R برای نمایش آن استفاده نمایید.)برای بار دوم از منوی insert گزینه Procedure را انتخاب نمایید تا پنجره add procedure باز شود. در قسمت name نام دلخواه و در قسمت type گزینه function را انتخاب نمایید و کلید Ok را فشار دهید. در این حالت عبارت public function name () در خط بالا و End function در خط پایین ظاهر می شود. در این مثال ما به جای name از کلمه دلخواه addnum استفاده کرده ایم. و چون می خواهیم در هنگام اجرای تابع آدرس دو سلول دلخواه را وارد نماییم از آرگومان (ورودی) num1 و num2 در داخل پرانتز استفاده کرده ایم که ورودی ها با علامت کاما از همدیگر جدا شده اند.

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

حال سئوال این است: با وجودی که اکسل دارای دستورات پیشرفته برای جمع می باشد چه نیازی به این تابع وجود دارد؟در واقع این یک نمونه است و نشان میدهد که اگر توابعی که مورد نیاز ما بوده و در اکسل موجود نیستند و یا می خواهیم به روش ساده تری کاری را انجام دهیم، می توانیم با استفاده از امکانات برنامه نویسی این قابلیت ها را به برنامه اکسل اضافه کنیم.حال برای استفاده از تابعی که ایجاد کرده ایم به محیط اکسل باز میگردیم. در یک سلول علامت = را تایپ می کنیم و نام تابعی که ایجاد کرده ایم را وارد می کنیم (در اینجا addnum) ملاحظه میشود با تایپ چند حرف اول از نام تابع اکسل تابع ما را تشخیص داده و نام کامل آن را برای ما نمایش می دهد.در واقع توابعی که توسط کاربر ایجاد می شوند در قسمتی به نام User Define قرار می گیرند که از لیست Insert function قابل دسترسی هستند.برای ذخیره صفحه حاوی برنامه باید آن را به صورت Enabled Workbook -Excel Macro ذخیره نماییم. در غیر این صورت تابع کار نخواهد کرد.

اکسل به طور پیش فرض برنامه های موجود در یک فایل را غیر فعال (Disable) می کند چونکه ممکن است این برنامه یک نوع ویروس باشد. بعد از باز کردن فایل اکسل که شامل برنامه VBA می باشد با کلیک بر روی گزینه Enable Content که در بالای صفحه ظاهر می شود، برنامه را فعال کنید در غیر این صورت به برنامه دسترسی نخواهد داشت.

 

[M o R i S]

ایجاد لیست های پایین افتادنی (Drop-Down list) در اکسل.1

ایجاد لیست های پایین افتادنی (Drop-Down list) در اکسل.1

Drop-Down لیست ها در اکسل کار ورود داده ها را ساده تر کرده و از اشتباهات احتمالی در هنگام ورود اطلاعات جلوگیری می کنند.در این روش به جای ورود اطلاعات از صفحه کلید، اطلاعات مورد نیاز از یک لیست انتخاب می شود. این لیست باید قبلاً توسط کاربر ایجاد شده باشد. یک روش ایجاد لیست با ذکر یک مثال توضیح داده میشود.فرض کنید تعدادی از استانها با میزان بارندگی سالیانه آنها را بخواهیم به یک لیست تبدیل کنیم. ابتدا نام استانها را در یک ستون، مثلاً ستون A و بدون فاصله وارد می کنیم. از سربرگ Data گزینه Data validation را انتخاب می کنیم. از پنجره ظاهر شده و از قسمت Allow از کادر پایین افتادنی گزینه List را انتخاب می کنیم. از قسمت پایین همین پنجره و در قسمت Source آدرس محدوده ای را که اسامی استانها در آن قرار گرفته را وارد می کنیم. (به جای آدرس محدوده، اگر این محدوده دارای اسم باشد می توان از اسم به جای آدرس محدوده استفاده نمود.)می توان در قسمت Input Massage پیام دلخواه را تایپ کنیم تا هنگام انتخاب سلول پیام مناسب نمایش داده شود.در قسمت Error Alert نیز می توان پیامی درج کرد تا در صورت انتخاب نادرست، (مثلاً تایپ نامی که در لیست وجود ندارد) این پیام ظاهر شودمراحل اجرای کار در تصویرهای زیر توضیح داده شده اند.

 

[M o R i S]

ایجاد لیست های پایین افتادنی (Drop-Down list) در اکسل.2

ایجاد لیست های پایین افتادنی (Drop-Down list) در اکسل.2

تذکر: در شکلهای زیر یک بار محدوده نام استان به لیست تبدیل شده و یک بار نیز محدوده میزان بارندگی به لیست تبدیل شده است (دو لیست مجزا). هر دو محدوده را نمی توان همزمان به یک لیست تبدیل کرد.
تذکر: اطلاعات برای تبدیل به لیست باید در یک محدوده پیوسته (بدون سلول خالی در بین داده ها) وارد شوند.
تذکر: اطلاعاتی که به لیست تبدیل می شوند، می توانند در کاربرگ جاری یا یک کاربرگ دیگر (مثلاً Sheet2،Sheet3 و ... قرار داشته باشند)
تذکر: با قراردادن کرسر موس در یک سلول و انجام عملیات فوق علامت انتخاب لیست در همان سلول ظاهر خواهد شد. (با خروج از سلول علامت انتخاب ناپدید می شود. پس سلول مورد نظر را به خاطر بسپارید!)

 

[M o R i S]

آدرسهای نسبی و مطلق در اکسل

آدرسهای نسبی و مطلق در اکسل

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

میخواهیم قیمت کل هر کدام از اقلام را حساب کنیم. در ستون قیمت کل و در سلول E3 اولین فرمول را به شکل C3*D3= وارد می کنیم. (مقدار x قیمت واحد). فرمول را تا ردیف E10 کپی می کنیم. همانطور که مشاهده می شود، هنگام کپی فرمول، مرجع سلولها نیز جابجا می شود. یعنی عبارت C3*D3= به شکل زیر تغییر می کند


C4*D4=

C5*D5=

C5*D5=

C6*D6=

C7*D7=

C8*D8=

C9*D9=

C10*D10=


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

اما در بسیاری از موارد لازم است که مرجع سلول تغییر نکند. به عبارت دیگر آدرس سلول به صورت مطلق وارد شود.
در مثال بالا فرض کنید که فروشنده 95% قیمت کالا را از شما دریافت کند به عبارت دیگر مبلغ 5% قیمت کل کالا را به شما تخفیف دهد. حال می خواهید که مبلغ کل هر کالا را در 95% ضرب کنید. چون عدد 95% فقط در یک سلول قرار دارد، اگر بخواهید اولین قیمت کل را در سلول F2 (95%) ضرب کرده و فرمول را به بقیه سلولها کپی کنید، مشاهده می شود اعدادی که بدست می آیند بطور آشکاری نامعقول هستند.

حال اگر بر روی این اعداد دابل کلیک کنید، متوجه می شوید که مرجع فرمول تغییر کرده است. برای آنکه در هنگام ک‍‍پی کردن فرمولها، مرجع فرمول ثابت بماند بایستی آن را به صورت مطلق تعریف کنیم. برای این کار از علامت $ قبل از آدرس سلول استفاده می کنیم. در مثال بالا آدرس سلول F2 بایستی به صورت مطلق تعریف شود. در این مثال چون هنگام کپی کردن F2 به F5,F4,F3 و ... تغییر میکند یعنی ستون ثابت مانده و ردیف تغییر کرده است برای مطلق تعریف کردن آن علامت $ را قبل از عدد 2 قرار دهیم یعنی F$2. علامت $ باعث ثابت شدن آدرس سلول شده و هنگام کپی شدن فرمول در ردیفهای پائینی مرجع این فرمول ثابت می ماند.

در این مثال چون فرمول در ستونهای مختلف کپی نمی شود لذا فیکس کردن ستون (F) ضروری نیست. اما اگر خواسته باشیم که فرمول را در ستونهای مختلف یا در سطرهای مختلف کپی کنیم بایستی عنوان ستون نیز ثابت در نظر گرفته شود و آدرس سلول به شکل ۲$F$
در فرمول قرار داده شود تا در هنگام کپی شدن آدرس سلول مورد نظر ثابت بماند.

تذکر: اگر پس از انتخاب آدرس سلول کلید F4 را یک بار فشار دهیم سطر و ستون به آدرس مطلق تبدیل می شوند. اگر همین کلید را برای بار دوم فشار دهیم فقط ردیف به حالت مطلق در می آید و اگر برای بار سوم نیز این کلید را فشار دهیم سطر از حالت مطلق خارج شده و ستون به حالت مطلق در می آید.

 

[M o R i S]

تابع Match

تابع Match

در بسیاری از موارد لازم است تا دو لیست از داده ها را با هم مقایسه کرده و اختلاف آنها را مشخص کنیم. در این مورد استفاده از تابع match که ساختار ساده ای دارد بسیار کارساز خواهد بود.در اینجا با ذکر یک مثال ساده کاربرد این تابع را بررسی می کنیم.فرض کنید شما مسئول کنترل یک انبار هستید و می خواهید بدانید که آیا در لیست ورود این هفته با هفته قبل که کالاهای مشابهی را دریافت کرده اید کالای تکراری وجود دارد یا خیر.

برای این منظور مطابق شکل لیست هفته قبل و لیست این هفته را در ستونهای B و C وارد کرده و در ستون D فرمول

=match(C3,$B$3:$B$21,0)

​

را وارد کنید و آن را تا سلول D21 کپی کنید.همانطور که در شکل می بینید در بعضی از سلول ها #N/A (Not Available – در دسترس نیست) نوشته شده است و به معنی این است که این یک کالای جدید می باشد که در هفته قبل وارد نشده است. حال اگر در این سلول ها اعدادی نوشته شوند مانند 11 یعنی اینکه این یک کالایی می باشد که در هفته قبل وارد شده و در ردیف شماره 11 قرار دارد.بدین ترتیب به راحتی می توان داده های تکراری را مشخص نمود.

​

=match(C3,$B$3:$B$21,0)

​

حال توضیح کوتاهی در مورد تابع داده می شود.کلمه match به معنی انطباق می باشد. C3 در این مثال یعنی آدرس سلول اولین قلم کالای این هفته و $B$3:$B$21 آدرس محدوده کالاهای هفته گذشته می باشدکه به صورت مطلق تعریف شده است (در مورد آدرسهای نسبی و مطلق در آینده مقاله جداگانه ای تهیه خواهد شد). عدد صفر نیز به معنی یک انطباق دقیق می باشد.البته تابع match به تنهایی کارایی زیادی ندارد . اما اگر با تابع Index ترکیب شود حلال بسیاری از مشکلات خواهد بود.موفق باشید

​

 

[M o R i S]

جلوگیری از باز شدن فایل اکسل توسط افراد غیر مجاز

جلوگیری از باز شدن فایل اکسل توسط افراد غیر مجاز

اگر در محیط اکسل فایلی را ایجاد کرده اید که اطلاعات آن محرمانه هستند یا اطلاعات مهمی را در آن ذخیره کرده اید و نمی خواهید توسط دیگران باز شود و یا در صورت باز شدن نتوان تغییراتی روی آن اعمال کرد راه حل بسیار ساده می باشد.روی دکمه File کلیک کنید و در فهرستی که باز می شود روی Save As… کلیک کنید. پنجره ذخیره فایل مانند تصویر باز می شود.

در پنجره Save As قسمت 1 باید محل ذخیره فایل را مشخص نمایید. در قسمت 2 نام فایل و در قسمت 3 نوع فایل را تعیین کنید. قبل از کلیک بر روی دکمه Save بر روی دکمه Tools کلیک کنید. از فهرستی که باز می شود بر روی General Options کلیک کنید. تا پنجره دیگری مطابق شکل باز شود.

در این پنجره دو نوع رمز را می توان وارد کرد. اولی برای باز کردن فایل و دومی برای ویرایش کردن فایل می باشد.
اگر مقابل گزینه Password to open: رمز را وارد کنید، کاربر برای بازکردن فایل باید حتماً رمز را وارد کند. اما اگر در مقابل گزینه Password to modify: رمز را وارد کنید، کاربر می تواند فایل را باز کند اما نمی تواند تغییراتی را که روی فایل انجام داده است را ذخیره کند

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

اگر فایل را به صورت فقط خواندنی (Read only) باز کرده باشیم و پس از ایجاد تغییرات بخواهیم آن را ذخیره کنیم، پنجره شکل زیر ظاهر شده و به ما هشدار می دهد که: فایل (اسم فایل باز شده) به صورت فقط خواندنی باز شده و امکان ثبت تغییرات بر روی آن امکان پذیر نمی باشد، برای ثبت تغییرات یا باید فایل به اسم دیگری ذخیره شود یا با همین اسم موجود اما در مکان دیگری.
بدینسان فایل اصلی همیشه بدون تغییر حفظ خواهد شد.

 

[M o R i S]

if های تو در تو در اکسل

if های تو در تو در اکسل

در توابع شرطی اگر بیش از چند شرط داشته باشیم چگونه عمل خواهد شد
مثلا محاسباتی داریم که برای هر محدوده از اعداد فاکتوری در نظر گرفته میشود مثلا اگه عدد کوچکتر از 5 باشد فاکتور 0 باشد و اگر بین 5 تا 5.5 باشد فاکتور 1 اگر بین 5.5 تا 6 باشد فاکتور 2 و الا آخر که جمعا 14 شرط داریم چگونه باید عمل کردپاسخ:با تشکر از این دوست عزیز:می توانید از تابع IF تو در تو به شکل زیر استفاده کنید.


=IF(A1<5,0,IF(A1>=5,IF(A1<=5.5,2,IF(A1>5.5,IF(A1<=6,3,IF(A1>6,IF(A1<=6.5,4,IF(A1>6.5,IF(A1<=7,5,IF(A1>7,IF(A1<=7.5,6,IF(A1>7.5,IF(A1<=8,7,IF(A1>8,IF(A1<=8.5,8,IF(A1>8.5,IF(A1<=9,9,IF(A1>9,IF(A1<=9.5,10,IF(A1>9.5,IF(A1<=10,11,IF(A1>10,IF(A1<=10.5,12,IF(A1>10.5,13))))))))))))))))))))))))

 

[M o R i S]

حفاظت کاربرگهای اکسل

حفاظت کاربرگهای اکسل

کاربرگهای اکسل می توانند در برابر تغییرات حفاظت شوند. اگر در یک کاربرگ اطلاعاتی را وارد کرده اید و غیر از شما سایر همکارانتان نیز از این فایل استفاده می کنند، برای جلوگیری از تغییرات ناخواسته لازم است که کاربرگ را حفاظت کنید. و برای اعمال تغییرات مجدد روی کاربرگ حفاظت شده می توان با وارد کردن پسورد این کار را انجام داد.اکسل در حالت پیش فرض گزینه Lock را برای تمام سلولهای کاربرگ فعال کرده است. اما تا زمانی که از سربرگ Review گزینه Protect Sheet را انتخاب نکنید، حفاظتی روی کاربرگ اعمال نخواهد شد.برای حفاظت کاربرگ به روش زیر عمل می کنیم.ابتدا مثلث کوچک بین ستون A و سطر 1 را انتخاب می کنیم. در این حالت کل کاربرگ به حالت انتخاب در می آید. (می توان با Ctrl+A کل کاربرگ را انتخاب کرد)بر روی ناحیه انتخاب شده راست کلیک کرده و از پنجره ظاهر شده Format Cells… را انتخاب می کنیم. پنجره دیگری ظاهر می شود که در سربرگ Protection تیک کنار گزینه Locked را بر میداریم.اکنون کل کاربرگ به صورت حفاظت نشده در می آید. حال سلولهایی که می خواهیم قفل کنیم (مثلاً دارای فرمول هستند و می خواهیم که فرمولها دستکاری نشوند) را انتخاب کرده و همانند روش بالا در سربرگ Protection تیک کنار گزینه Locked را فعال می کنیم. در این حالت اگر در سربرگ Review گزینه Protect sheet را انتخاب کرده و پسورد مورد نظر را وارد کنیم (وارد کردن پسورد اختیاری می باشد)، سلول ها به حالت قفل شده درآمده و برای تغییرات روی آنها باید از سربرگ Review گزینه Unprotect Sheet را انتخاب نمایئم و در صورت داشتن پسورد آن را وارد نماییم.در تصویرهای زیر مراحل کار تشریح شده است.

 

[M o R i S]

تبدیل عدد به حروف

تبدیل عدد به حروف

در این پست فایل تبدیل عدد به حروف فارسی را برای دانلود قرار داده ام . امیدوارم مفید واقع گردد.تذکر: این فایل حداکثر یک عدد ۱۲ رقمی را تبدیل می کند.

دانلود تبدیل عدد به حروف

دانلود جدول اشتال در قالب اکسل

 

[M o R i S]

جلوگیری از ورود داده های تکراری در اکسل

جلوگیری از ورود داده های تکراری در اکسل

در بسیاری از مواقع و هنگام وارد کردن حجم زیادی از اطلاعات در صفحه گسترده اکسل، ممکن است بعضی از داده ها به صورت تکراری وارد شوند.در اکسل ابزار Data validation ورود اطلاعات را با شرایطی که شما تعیین کرده اید چک کرده و در صورت معتبر بودن داده ورودی آن را قبول کرده و در غیر این صورت از ورود داده نامعتبر جلوگیری می کند.برای اینکه از ورود داده های تکراری جلوگیری نمائیم، ابتدا محدوده ای را که قصد ورود اطلاعات در آن را داریم انتخاب می کنیم. در این مثال قصد داریم اطلاعاتی را در ستون N وارد نمایئم.ابتدا بر روی ستون N کلیک می کنیم تا انتخاب شود. از نوار ریبون و از سربرگ Data گزینه Data Validation را انتخاب می کنیم. مطابق شکل های زیر تنظیمات را انجام میدهیم.

همانطور که در شکل می بینید فرمول را به صورت COUNTIF(N:N,N1)=1 نوشته ایم. در این فرمول COUNTIF کار شمارش شرطی را انجام میدهد. عبارت N:N به معنی کل ستون N می باشد و N1 سلول فعال است. تابع فوق یعنی COUNTIF(N:N,N1) مشخص می کند که تعداد تکرار در ستون N چند بار اتفاق افتاده و اگر آن را مساوی یک قرار دهیم، فقط یک بار اجازه ورود هر گونه اطلاعات (عدد – متن و ...) داده می شود.
در سربرگ Error Alert از پنجره Data Validation نیز عبارت مناسب جهت آگاهی از ورود داده های تکراری را نوشته ایم (این قسمت اختیاری می باشد و می توان آن را خالی گذاشت.)

 

[M o R i S]

توابع مالی در اکسل

توابع مالی در اکسل

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

Loan principle	اصل پول
Interest Rate	نرخ بهره
Pay-Period	دوره پرداخت
Duration of the loan	طول دوره پرداخت
Payment	پرداخت در هر دوره

تذکر: 1
اکسل نرخ بهره را به ازاء دوره پرداخت می گیرد (مثلاً پرداخت ماهانه) اما پاسخها را بر اساس سالانه برمیگرداند و برای تبدیل به ماهانه باید آن را بر 12 تقسیم کنیم.
تذکر 2: مقدار پرداختهایی که در فرمول وارد می شوند بایستی به صورت عدد منفی وارد شوند.
تابع PMT
مثال1: فرض کنیم از یک موسسه مالی یک وام به مبلغ پنج میلیون تومان با نرخ بهره 14% و دوره بازپرداخت 3 ساله گرفته ایم. مبلغ قسط ماهانه آن چقدر است؟
از فرمول به شرح زیر برای مشخص کردن میزان اقساط ماهانه استفاده می کنیم.
=PMT(نرخ بهره ماهانه,تعداد دوره پرداخت,اصل وام دریافتی)
همانطور که در شکل می بینید نرخ بهره سالانه یعنی 14% را بر عدد 12 تقسیم کرده ایم تا نرخ بهره مانه را بدست آوریم.

تذکر: قسط پرداختی هر ماه شامل دو قسمت است. قسمتی از آن مربوط به اصل پول گرفته شده و قسمتی نیز مربوط به پرداخت سود است.
مثال 2: فرض کنیم در مثال 1 تعداد 15 قسط را تا کنون پرداخت کرده باشید. چه مقدار از قسط آخر (قسط 15) مربوط به اصل پول و چه مقدار آن مربوط به سود می باشد؟
برای این منظور از تابع IPMT به شکل زیر استفاده می کنیم.
=IPMT(rate(نرخ بهره),per(دوره پرداخت مورد نظر),nper(تعداد کل دوره های پرداخت),pv(اصل پول)
=IPMT(B2/12,B5,B3,B1)
همانطور که در شکل می بینید، مقدار محاسبه شده برابر 34488.07 می باشد. این عدد مبلغ سود پول را نشان می دهد و با کم کردن آن از مبلغ کل قسط مبلغ اصل پول نیز که برابر 132400.08 می باشد نیز مشخص میگردد.

تابع NPERشکل کلی تابع:

=NPER(rate,pmt,pv)
​

مثال 3: من قصد دارم یک وام به مبلغ پنج میلیون تومان بگیرم. نرخ بهره آن 15% و توان پرداخت ماهی 250000 تومان را دارم. تسویه این وام در چند ماه به پایان می رسد؟

=NPER(.15/12,-250000,5000000)

شما می توانید در طی 23 ماه این مبلغ را تسویه نمایید.تابع Rateشکل کلی تابع
=rate(nper(تعداد کل دوره های پرداخت),pmt(پرداخت ماهانه),pv(اصل پول)

مثال 4:میخواهم وسیله ای بخرم به قیمت 700000 تومان. پیشنهاد فروشنده اقساط 24 ماهه با پرداخت ماهانه 35000 تومان است. چگونه میتوان نرخ واقعی بهره را بدست آورد.نرخ بهره را با استفاده از فرمول فوق محاسبه می کنیم.

=RATE(24,-35000,700000)

​

جواب این فرمول 2% می باشد (نرخ بهره ماهانه) و با ضرب آن در عدد 12 ماه بهره 24% خواهد شد. پس بهتر است این پول را از بانکی که نرخ بهره اش 16% وام بگیرم.تابع PVشکل کلی تابع

=PV(rate(نرخ بهره),nper(تعداد کل دوره های پرداخت,pmt(پرداخت ماهانه)


​

​

مثال 5: نرخ بهره بانک 16% است. اگر بتوانم در طی سال آینده ماهی 50000 تومان قسط بدهم، چقدر می توانم از بانک وام بگیرم؟

=PV(.16/12,12,-50000)

​

اسخ 551080 تومان یعنی تقریباً مبلغ 550000 تومان

تابع FVشکل کلی تابع

=FV(rate(نرخ بهره),nper(تعداد کل دوره های پرداخت,pmt(پرداخت ماهانه),pv(اصل پول)


​

مثال 6: با مبلغ 100000 تومان در بانک حسابی باز کرده ام و ماهی 50000 تومان به آن اضافه می کنم. بهره پرداختی بانک .515% است. در پایان دو سال چقدر پس انداز دارم.

=FV(0.155/12,24,-50000,-100000)

​

جواب: مبلغ 1532413 تومان می باشد.




تذکر: تمام مبلغ پرداختی به صورت عدد منفی در فرمولها وارد شده اند.

 

[M o R i S]

تابع VLOOKUP

تابع VLOOKUP

کاربرد تابع VLOOKUPتابع VLOOKUP در سری توابع LOOKUP & REFERENCE اکسل قرار دارد.شکل کلی این تابع به صورت:

=VLOOKUP(LOOKUP_Value,table_array,col_index_mum, [range_Lookup]))


​

می باشد.به زبان ساده معنی عبارت بالا این است: Lookup_Valueبگرد به دنبال مقداری که من در سلول ... وارد کرده ام. (آدرس سلول به جای Lookup نوشته شود)table_array جدولی که مقدار وارد شده در بالا در آنجا قرار دارد.col_index_num شماره ستونی که مقدار آن باید مقابل Lookup_Value نوشته شود.Range_Lookup (مقدار 0 و 1) دقت جستجو را مشخص می کند. مقدار صفر جستجوی دقیق و مقدار یک جستجوی تقریبی را فراهم می آورد.در این پست کاربرد این تابع را با ذکر یک مثال توضیح میدهم.دوستان عزیزی که مسئولیت تهیه صورت وضعیت یک شرکت را به عهده دارند به خوبی می دانند که بدون استفاده از تابع VLOOKUP، وارد کردن شرح، واحد و قیمت آیتمهای فهرست بهاء کاری طاقت فرسا و همراه با اشتباهات زیاد خواهد بود.اما با استفاده از این تابع و فقط با وارد کردن ردیف آیتم مورد نظر، سایر پارامترهای دیگر از قبیل شرح آیتم، واحد، قیمت واحد به صورت اتوماتیک درج خواهند شد.در این مثال فرض ما بر این است که کاربرگهایی که ما روی آنها کار می کنیم (مانند برگ ریز متره، خلاصه متره و ...) دارای جهت راست به چپ هستند (از سربرگ PAGE LAYOUT) گزینه Sheet Right – to- Left را فعال می کنیم.)در این مثال من یک کاربرگ را به عنوان DATA BASE که نمونه ای از فهرست بهای رشته برق می باشد در قالب اکسل تهیه کرده ام.همانطور که در شکل می بینید این نمونه در محدوده A2:.D10 قرار گرفته است. ابتدا بهتر است که این محدوده را نامگذاری کنیم. برای این کار از سربرگ FORMULAS آیتم Name Manager را انتخاب کرده و از پنجره باز شده New را کلیک می کنیم. پنجره New Name نمایان می شود. در قسمت Name نام دلخواه (فارسی یا انگلیسی) را بدون فاصله تایپ می کنیم. (برای نامهای طولانی می توان از _ بین کلمات استفاده نمود) و سپس در قسمت Refers to: با کلیک بر روی فلش کوچک قرمز رنگ کناری محدوده ای را که قصد نامگذاری آن را داریم انتخاب کرده و سپس بر روی OK کلیک می کنیم.شکلهای زیر نحوه کار را نماش می دهند.

در این مثال من دو کاربرگ را تهیه کرده ام. همانطور که قبلاً اشاره شده کاربرگ DATA BASE را به اسم "برق" و کاربرگ دیگر را به نام "صورت وضعیت" نامگذاری کرده ام.
حال میخواهم در کاربرگ صورت وضعیت از تابع VLOOKUP استفاده کرده و با درج شماره ردیف فهرست از کاربرگ برق، شرح، واحد و قیمت آیتمها به صورت خودکار در کاربرگ "صورت وضعیت قرار بگیرند.
در قدم اول قصد دارم شرح آیتم در برگ صورت وضعیت در زیر عنوان "شرح" قرار بگیرد. برای این منظور در سلول B8 کاربرگ "صورت وضعیت" فرمول را به شرح زیر وارد می کنم.
=VLOOKUP(A8,فهرست,2,0)
A8: محلی که باید جستجو شود.
فهرست: جدولی که مقدار مورد جستجو در آنجا قرار دارد
2: ستون دوم از جدول فهرست
0: مقدار جستجو دقیق باشد. شرح نزدیک به آیتم قرار داده نشود.
برای آنکه مقدار واحد نیز درج شود عیناً فرمول فوق را تکرار کرده و فقط به جای عدد "2" باید عدد "3" را وارد کنیم. چونکه شرح واحد در ستون سوم (از سمت راست) جدول "فهرست (کاربرگ برق) قرار دارد.
شکل زیر فرمول و نتیجه کار را نشان می دهد.

تذکر: اگر مقدار LOOKUP (در این مثال یعنی ستون A) یافت نشود آنوقت خطای

#NA (Not Available)

نمایش داده می شود.تذکر: در ردیفهای فهرست قبل از بعضی از ردیفها مقدار "0" درج شده است. اما اکسل آن را نادیده میگیرد. اگر شماره آیتم درست وارد شود و دوباره خطای #NA نمایش داده شود بهتر است که از Format Cell نوع داده سلول را Text در نظر بیگیریم.تذکر: تابع HLOOKUP نیز مانند VLOOKUP عمل می کند . اما به جای جستجوی عمودی، جستجوی افقی را انجام میدهد.​

 

[M o R i S]

تابع CHOOSE

تابع CHOOSE

تابع 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 جمع مبالغ ستون "اقلام سرمایه ای" به دست می آید.

 

[M o R i S]

حل معادله درجه دوم به کمک اکسل

حل معادله درجه دوم به کمک اکسل

در این پست قصد دارم معادله درجه دوم که در دوره دبیرستان آن را دیده اید به کمک اکسل و با استفاده از تابع 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 کوچکتر از صفر باشد عبارت " معادله ریشه حقیقی ندارد" نمایش داده می شود و در غیر این صورت در این سلول نوشته ای دیده نمی شود.

تذکر: عبارت C4^.5 یعنی مقدار عددی سلول C4 که آن را به توان 0.5 رسانده ایم. به عبارت دیگر

در این شکل با قرار دادن a=1 , b=-4 , c=4 مقدار دلتا برابر صفر و در نتیجه معادله دارای ریشه مضاعف (تکراری) 2 می باشد.

در این شکل با قرار دادن a=1 , b=3 , c=4 مقدار دلتا عددی منفی شده و در نتیجه معادله در دامنه اعداد حقیقی جواب ندارد.

​

​

 

[M o R i S]

جمع شرطی در اکسل- قسمت دوم sumifs

جمع شرطی در اکسل- قسمت دوم sumifs

در پست قبلی تابع 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 نیز به جمع اضافه خواهد شد.

​

 

[M o R i S]

جابجا کردن سلولهای / سطر/ ستون در excel با کلید میانبر

جابجا کردن سلولهای / سطر/ ستون در excel با کلید میانبر

اگر شما بخواهید چند سطر و یا ستون را در اکسل جابجا کنید حتما با CUT اینکار را انجام می دهید و اگر جای خالی باشد شاید با استفاده از Drag کردن کادر دور

سلولابتدا سطر / ستون / سلول مورد نظر خود را انتخاب کنید.سپس کادر سیاه رنگ دور قسمتی را که انتخاب کرده اید را کلیک کنید و (رها نکنید*آنرا به هر جایی که

می خواهید انتقال یابد Drag کنید و کلید Shift را هم نگه داریدمی بینید که سلول excel به سادگی Move می شود​