می‌خواهيم كاري كنيم كه در يك ستون (يا هر محدوده ديگري) از كاربرگ هيچ عدد تكراري را نتوان وارد نمود و در صورتيكه اين اتفاق افتاد اكسل پيغام بروز خطا را نمايش دهد.

تابع OFFSET جهت ايجاد يك مرجع به محدوده‌اي كه به تعداد رديف و ستون خاصي از يك سل يا محدوده‌اي از سل‌ها فاصله دارد، استفاده مي‌شود. در ادامه مطلب در خصوص اين تابع توضيحات بيشتري وجود دارد.

برخي از اشخاص معتقدند كه تابع IF مفيدترين فرمول در اكسل است. فرمولهاي اين تابع اجازه مي‌دهد تست‌هاي شرطي در مقادير و فرمولها انجام دهيد و تاحدي بتوانيد منطق بكا رفته در زبانهاي برنامه نويسي را تقليد نمائيد. يك فرمول IF با يك شرط شروع شده و اگر شرط صحيح باشد، تابع يك مقدار را برگردانده و اگر شرط برقرار نبود تابه مقدار دومي را كه برايش تعيين شده برمي‌گرداند. براي بهتر شناختن اين تابع بر روي ادامه مطلب كليك نمائيد.

در این پست قصد دارم تا شما عزیزان را با بحث Sensitivity Analysis در اكسل 2007 آشنا كنم. قبل از پرداختن به اصل مسئله با يك مثال بحث را آغاز مي‌كنم. فرض كنيد مي‌خواهيم در يك مركز خريد مغازه‌اي را اجاره كرده و در آن اقدام به فروش ساندويچ نمائيم. قبل از افتتاح مغازه مي‌خواهيم بدانيم كه ميزان سود، درآمد و هزينه‌هاي متغير، چگونه به فروش يك ساندويچ و هزينه هر ساندويچ بستگي دارد.

بر اساس فرضيه‌هاي ورودي مي‌توانيم خروجيهاي مورد نياز را محاسبه كنيم. همان طور كه گفته شد اين خروجيها مي‌تواند مواردي مانند سود، درآمد و هزينه متغير باشد. ممكن است حدس ما در خصوص هزينه متغير توليد يك ساندويچ برابر با ۴ دلار باشد، اما ممكن است اين فرضيه اشتباه باشد. تحليل حساسيت، چگونگي تغيير خروجيها در ارتباط با تغييرات ورودي‌ها را تعيين مي‌كند. بطور مثال مي‌خواهيم بدانيم كه چگونه تغيير در قيمت محصول بر سود، درآمد و هزينه متغير سال اثر مي‌گذارد.

در این پست قصد دارم شما را با چگونگی استفاده از توابع Text اكسل آشنا كنم. براي اين كار ابتدا هر يك از توابع Text را توضيح داده و سپس آنها را با هم تركيب كرده و كارهاي پيچيده‌اي را انجام خواهيم داد.

•  تابع INDEX

این تابع در محدوده اعلام شده به تعداد سطر و ستون اعلام شده شمارش کرده و داده موجود در سلی که در محل تقاطع سطر و ستون قرار دارد را نتیجه می دهد

=INDEX(array;row_num;column_num)

در اين فرمول array آرايه مورد نظر، row_num شماره سطر و column_num شماره ستون مي‌باشد.

به مثال زير توجه كنيد.

براي يافتن داده موجود در سطر دوم و ستون سوم از فرمول زير استفاده مي‌شود.

=INDEX(A1:D8;2;3)

در اينجا جواب برابر است با 15.000

• تابع VLOOKUP

این تابع برای یافتن یک مقدار از یک جدول به کار می رود، به این ترتیب که مقدار گفته شده را از ستون اول یافته سپس در همان ردیف و در ستون گفته شده داده مورد نظر را به دست می آورد. دستور این تابع به صورت زير است.

=VLOOKUP(lookup_value;table_array;col_index_num;range_lookup)

در lookup_value مقداری را که می‌خواهیم از ستون اول جدول پیدا کنیم وارد می‌کنیم. در table_array جدول مرجع وارد می‌شود. در قسمت col_index_num شماره ستونی که می‌خواهیم جواب از آن ستون باشد را وارد می‌کنیم. در قسمت range_lookup هم تعیین می‌شود که اگر تابع مقدار مورد نظر را پیدا نکرد چه کند، آیا نزدیکترین مقدار را به ترتیب نزولی نتیجه دهد یا اینکه دقیقاً همان مورد را جستجو کند، (مقادیر FALSE و TRUE برای این کار استفاده می شود).

به یک مثال توجه کنید. برای جستجوی معادل عدد 20 از ستون دوم:

=VLOOKUP(20;A1:E4;2)

چون پارامتر چهارم وارد نشده است پس TRUE در نظر گرفته می شود، پس عدد 20 یا کوچکتر از آن نیز قابل قبول است. معادل عدد 20 در ستون دوم عدد 21 است.

برای جستجوی معادل عدد 25 از ستون دوم:

=VLOOKUP(25;A1:E4;2)

چون عدد 25 در بین اعداد ستون اول نیست پس نزدیکترین عدد به آن که البته کوچکتر از آن نیز باشد را در نظر می گیریم یعنی عدد 20 و جواب برابر با 21 است.

در صورتیکه در تابع بالا پارامتر FALSE را وارد کنیم، این تابع نمی‌تواند عددی معادل آن پیدا کند و در نتیجه پیغام خطای #N/A را خواهد داد. همچنین در صورتیکه پارامتر col_index_num کوچکتر از 1 بـاشد، پیغام #VALUE! و در صورتیکه این پارامتر بزرگتر از تعداد ستونهای جدول باشد پیغام #REF! را نتیجه می دهد.

• ایجاد ماکرو

هدف اصلی از ایجاد یک ماکرو آسانتر کردن کارها و سهولت دسترسی به بخش های مختلف برنامه است. اکسل به شما اجازه می دهد تا ماکروهایی ایجاد کنید تا کارهای ناخوشایند و تکراری را تنها با یک کلیک و یا چند کلید انجام دهید.

یک ماکرو مجموعه ای از فرمانها، کلیدها و کلیکهای موس است که می توانید آنها را ذخیره کرده و هر موقع نیاز بود آنرا اجرا کنید. برای ایجاد یک ماکرو دو راه وجود دارد.

1. ضبط ضربه کلیدها و کلیک ها در حین انجام کار
2. نوشتن یک ماکرو از ابتدا

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

به مثال زیر توجه کنید.

فرض کنید می‌خواهیم معتبرسازی مقادیر را به صورت یک ماکرو ضبط کنیم. برای این کار از نوار Developer و گــروه Code بر روی Record Macro کلیک می‌کنیم. در کادر محاوره‌ای ظاهر شده باید یک نام را به ماکرو اختصاص دهیم «بطور مثال نام Validate را وارد می‌کنیم». می‌توان برای اجرای ماکرو کلید میانبر نیز تعریف نمود که همراه با کلید Ctrl باعث اجرای ماکرو خواهد شد.

پس از کلیک بر روی OK هر عملی که انجام دهید در ماکرو ضبط خواهد شد، بنابراین کلیه اعمالی که برای معتبرسازي داده‌هاي ورودي بايد انجام داد را انجام داده و پس از اتمام عملیات بر روی گزینه Stop Recording کلیک می کنیم.

حال ماکرویی داریم که عملیات معتبرسازی داده ها را با شرط مورد نظر ما در خود ضبط کرده و از این به بعد در هر جائی می‌توان از این ماکرو استفاده کرد.

برای استفاده از ماکرو، پس از انتخاب محدوده مورد نظر و کلیک بر روی گزینه Macros در نوار Developer کادر محاوره ای شامل کلیه ماکروهای ساخته شده ظاهر می‌گردد که پس از انتخاب ماکروی مورد نظر، کلیک بر روی Run باعث اجرای ماکرو در محدوده انتخاب شده خواهد شد.

توابع جستجو و آدرس:

• تابع HLOOKUP

این تابع دارای چهار پارامتر می باشد. این تابع برای جستجو در محدوده ای خاص برای پیدا کردن عبارتی در سطر اول جدول است که پس از پیدا کردن ستون مربوطه داده موجود در سطر n ام را به عنوان نتیجه اعلام می کند. دستور این تابع به صورت زير است.

 =HLOOKUP(lookup_value;table_array;row_index_num;range_lookup)

مقداری که تابع در سطر اول جدول به دنبال آن می گردد، lookup_value، محدوده مورد جستجو table_array می باشد که شامل سطر اول نیز می شود. row_index_num شماره سطری است که می‌خواهید پس از پیدا کردن ستون مورد نظر داده آن سطر به عنوان جواب اعلام گردد. range_lookup مقداری اختیاری است که دو مقدار TRUE یا FALSE را می‌پذیرد. TRUE یعنی مقادیری که در سطر اول قـــــــرار می‌گیرند باید به صورت مرتب شده بــاشند، در غیــر اینصورت ممکن است تابع جواب درستی ندهد. اگر FALSE را به کار بریم نیازی به مرتب سازی نداریم و جواب دقیقی ارائه خواهد شد.

طريقه گذاشتن تصوير در پنجره ويندوز

با يک مثال طريقه گذاشتن تصوير در پنجره ويندوز را آموزش مي‌دهيم. ما در اين مثال مي‌خواهيم تصوير پنجره درايو D را تغيير دهيم.
برنامه نوت پت را باز کنيد و متن زير را درون آن بنويسيد

[{BE098140-A513-11D0-A3A4-00C04FD706EC}]
iconarea_image=D:\photo.jpg

سپس آن را با نام Desktop.ini در درايو D ذخيره کنيد.
سپس عکس مورد نظرتان را در مسير D:\photo.jpg قرار دهيد. 

• آدرس نسبی سل‌ها

در حالت عادی، نحوه آدرس گذاری Excel در فرمول‌ها به صورت «نسبی» می‌باشد. به اين معنی که با کپی کردن، جابجا کردن يا گسترش دادن محتويات سل به مکان جديد، آدرس‌های فرمول موجود در سل نيز به طور منطقی تغيير می‌کند.

در مثال زير فرمول (هزينه - فروش= سود) يا (E6=G6-F6) در سل E6 درج شده است. با کشيدن محتويات اين سل روی سل‌هـــای E7 تا  E9 (خاصيت AutoFill)، فرمول اين سـل به G7-F7 در سل E7 و G8-F8 در سل E8 و G9-F9 در سل E9  تغيير پيدا می‌کند. يعنی تمام سل‌هايی که حاوی اين فرمول باشند، برای محاسبات هميشه از دو سل سمت چپ خود استفاده می‌کنند.

همچنين با Copy & Paste کردن محتويات سل E6 به مکان F14، فرمول اين سل به H14-G14 تغيير می‌يابد. (به همين دليل مقدار صفر در آن نشان داده می‌شود).

آدرس دهي مطلق را در ادامه مطلب ببينيد.

• تابع RATE

این تابع نرخ بهره را برای یک قسط از وام مشخص، تعداد قسط و مبلغ مشخص نتیجه می دهد. دستور این تابع به صورت زیر است.

 =RATE(nper;pmt;pv;fv;type)

بطور مثال فرمول محاسبه نرخ بهره برای یک قسط از  وام 3.000.000 تومانی با اقساط 36 ماهه و مبلغ هر قسط 106.958 تومان به صورت زیر می‌باشد.

=RATE(36;-106958;3000000)

 که نتیجه برابر است با 1.4 درصد که مربوط به یک قسط می باشد و اگر این تابع در عدد 12 ضرب شود بهره یک ساله را نتیجه می دهد که برابر با 17 درصد می باشد.

• توابع مالی برای وام ها

معمولا آرگومانهایی که در این گونه توابع استفاده می‌شوند به شرح زیر است:

1. نرخ بهره در هر دوره: Rate
2. دوره ای که بهره برای آن محاسبه می شود: Per
3. تعداد کل اقساط پرداختی: Nper
4. ارزش فعلی سرمایه: Pv
5. ارزش آینده یا تعادل پولی که می خواهید بعد از پرداخت آخرین قسط به آن دست یابید: Fv
6. زمان پرداخت که می تواند در ابتدای دوره یا در انتهای دوره باشد: Type (عدد صفر پرداخت در انتهای دوره و عدد یک پرداخت در ابتدای دوره می باشد)

• استفاده از قالب بندی‌های شرطی

يکي از روشهای مفيد برای نمايش بهتر داده‌ها قالب بندی آنها براساس محتويات سل‌ها مي‌باشد. به عنوان مثال می‌توان اعداد منفی را با رنگ قرمز از ساير اعداد متمايز نمود و يا سل‌هايي را که کلمه خاصي در آنها درج شده با فونت خاصی مشخص کرد.

1. محدوده‌ای را که می‌خواهيم دارای قالب بندی شرطی شود انتخاب می‌کنيم
2. از نوار Home گزينه Conditional Formatting را كليك می‌کنيم
3. در منوي ظاهر شده تعدادي از شرطهاي از قبل آماده را مي‌توان انتخاب كرد. بطور مثال مي‌توان اين قانون را گذاشت كه در صورتي كه در محدوده انتخاب شده عددي كمتر از 100 وجود داشت، سل مربوط به آن به رنگ مشخصي ظاهر گردد.
4. پس از ايجاد شرط مورد نظر، مي‌توان از قسمت With نحوه مشخص شدن سلي كه داراي شرط نيست را تعيين كرد.
5. هر كدام از شرطهاي از قبل آماده شده داراي دكمه‌اي تحت عنوان More Rules نيز مي‌باشند كه از طريق آن مي‌توان قالب بندي‌هاي ديگري را براي شروط بنا نهاد.