دسته بندي: تحقیق و پروژه رایگان,پروژه و تحقیق رایگان,دانش آموزی,تحقیق دانشجویی,

تحقیق درباره Excel 2003

توابع کاربردی و روزمره

§ آشنایی و درک توابع ریاضی

§ آشنایی و درک توابع اطلاعاتی

§ آشنایی و درک توابع متنی

§ آشنایی و درک توابع جستجو و مرجع

§ آشنایی و درک توابع منطقی

در این فصل به شرح توابع سودمتر اکس می‌پردازیم و برای مختصر کردن بحث توابع فقی به شرح توابع منتخب می‌پردازیم. لذا این فصل ( و فصل‌های 15 ، 16، 17 را می‌توانید به عنوان یک مرجع منظور دارید. ولی در ضمیمه B «مرجع توابع» هر تابع موجود در اکسل همراه با اطلاعات پایه‌ای که برای استفاده از آنها لازم دارید، با انضمام مراجع متقابل به کلیه اطلاعات موجود در این فصل و فصل‌های دیگر ارائه می شود. برای مطالعه شرح کامل توابعی که در این فصل شرح داده نشده اند به اطلاعات توصیفی ارائه شده در کادر محاوره‌ای Insert Function و سیستم Help Online رجوع کنید.

آشنایی و درک توابع ریاضی

بیشتر کارهایی که در اکسل انجام می‌شوند حداقل یک یا چند تابع ریاضضی سر و کار دارند. و با وجودی که تقریبا اکسل قادر به محاسبه هر چیزی می‌باشد، تابع جمع sum بیش از سایر توابع ریاضی به کار برده می شود. در این بخش بعضی توابعی ریاضی که در اکسل بیشتر مورد استفاده قرار گیرند و سودمندتر می باشند شرح داده می‌شوند.

استفاده از تابع sum

در تابع sum یک سری اعداد جمع‌ زده می‌شوند. این تابع به فرم = sum (number 1 , number 2 , …) می‌باشد که Number همان آرگومنتها هستند که یک سری از 30 ورودی را تشکیل می‌دهند، این آرگومنت می‌توانند عدد، آرگومنت‌هایی را که به مقادیر متنی، مقدارای منطقی یا به خانه‌های خالی کاربرگ ارجاع داده می‌شود نادیده می‌گیرد.

نکته: توان بخشیدن بیشتر به تابع جمع

شما می‌توانید فرمول‌ههای شرطی قدرتمندی برای sum با استفاده از ابزارهای افزودنی ایجاد کنید. به « استفاده از برنامه‌های راهنمای Conditinal sum و look up نگاه کنید.

دکمه AutoSum

از آنجایی که تابع sum در حد گسترده ای مورد استفاده قرار می‌گیرد، اکسل دکمه AutoSum را روی نوار ابزار standard خود قرار داده است. اگر خانه ای از کاربرگ را انتخاب کرده و روی دکمه AutoSum کلیک کنید، اکسل یک فرمول sum ایجاد کرده و خود حدس می زند که کدام خانه‌ها را می‌خواهید با هم جمع بزنید، برای وارد کردن فرمول‌های sum در دامنه‌ای از خانه های کاربرگ قبل از کلیک روی دکمه AutoSum خانه‌های کاربرگ را انتخاب کنید.

گسترش خودکار دامنه

ز زمان ساخت اولین برنامه صفحه گسترده، یکی از متداولترین مشکلات این برنامه، وارد کردن خانه‌های کاربرگ در پایین یا سمت راست دامنه‌ای که در فرمول به آن ارجاع شده است می‌باشد. برای مثال، تصور کنید فرمول = sum (Al ; A4 ) را درخانه A5 کاربرگ وارد کرده‌اید و سپس ردیف 5 را انتخاب کرده و یک ردیف جدید به کاربرگ اضافه نموده‌‌اید. رددیف جدید در بالای ردیف انتخابی قرار می گیرد. از این رو فرمول sum به خانه C6 رانده می شود. هر عددی که در خانه جدید وارد شده C5 شد. اکنون می‌توانید خانه‌هایی را در پایین یا در سمت سارت یک دامنه ارجاع داده شده در یک فرمول وارد کنید و اکسل قرار دادن این خانه‌ها به مجرد وارد کردن مقدار در این خانه‌های جدید وارد شده بخش 5ک ساخت فرمول‌ها و تحلیل اجرای داده‌ها) فصل 14: توابع کاربردی و روزمره

فرمول خود: فرمول را تنظیم خواهد کرد.

این تنظیم در صورتی که انجام خاهد شد که خانه‌ها را بلافاصله در سمت راست، یا زیر یک دامنه ارجاع داده شده وارد نمایید. وارد کردن خانه‌ها در بالا یا در سمت چپ یک دامنه ارجاع شده نیاز به ویرایش دستی فرمول‌های مرجع ‌دهی خواهد داشت.

استفاده از توابع انتخاب شده ریاضی

اکسل دارای 58 تابع ریاضی و مثلثاتی می‌باشد که اسامی همه آنها در فهرست ضمیمه B ل مرجع توابع» ارائه شده است. در این بخش، فقط چند تابع سودمندتر و توابعی که به درستی درک نشده‌اند شرح داده می‌شوند.

توابع SUMPRODUCT , PRODUCT

در تابع product کلیه آرگومنتها که می تواند بالغ بر 30 آرگومنت اعم از آرگومنتها متنی با منطقی باشند در یکدیگر ضرب می‌شوند و خانه‌های کاربرگ نادیده گرفته می شوند. در تابع SUMPRODUCT مقدار هر خانه در یک دامنه در خانه متناظر در دامنه دیگر که هم اندازه دامنه اولی است ضرب شده و سپس نتیجه حاصل ضربها با یکدیگر جمع زده می شوند. در این تابع می‌توان حداکثر 30 بردار را به عنوان آرگومنت به کار برد ولی ابعاد همه بردارها باید یکسان باشند، (ورودیهای غیر عددی معادل صفر تلقی خواهند شد) برای مثال، فرمول‌های زیر در اصل یکی می‌باشند.

= SUMPRODUCT 9 Al : A4 , B1: B4)

{ = SUM ( Al : A4^*B1: B4)}

تنها تفاوت این دو فرمول در این است که فرمول sum باید با فشار دادن Ctrl + Shift + Enter به عنوان یک بردار وارد شود.

برای کسب اطلاعات بیشتر درباره بردارها، به «استفاده از بردارها» نگاه کنید.

تابع MOD

تابع MOD باقیمانده یک عمل تقسیم را برمی‌گرداند. این تابع دارای دو آرگومنت (divisor و number یا مقسوم‌ و مقسوم الیه ) می‌باشد. نتیجه را محاسبه تابع Mod باقی‌مانده حاصل از تقسیم آرگومنت Number بر آرگومنت divisor می‌باشد. برای مثال، نتیجه تابع = Mod ( 9, 4) عدد 1 می‌باشد که باقیمانده است که از تقسیم 9 بر 4 به دست آمده است.

یک مثال Mod

استفاده عملی از تابع Mod به شرح زیر است:

1- انتخاب یک خانه از کاربرگ و انتخاب Conditional formatting , Format

2- انتخاب گزینه Formula is از فهرست اول شروط، و سپس تایپ فرمول = Mod (Row () , 2 _ = 0 در کادر سمت راست.

3- کلیک روی دکمه Format و انتخاب یک رنگ تحت کلید Parrerns برای ساختن فرمتی که رنگ منتخب را در هر ردیف دیگر اعمال می‌کند. توجه داشته باشید که اگر خانه های انتخابی در ردیف‌هایی به شماره‌های فرد قرار داشته باشند، به نظر می‌رسد که هیچ اتفاقی رخ نداده است ولی اگر فرمت را کپی کرده و یا در ردیف‌‌های دیگر به کار ببرید نتیجه را مشاهده خواهید کرد. این فرمول شماره ردیف‌ جاری را با استفاده از تابع Row بازبین می‌‌نماید و آن را بر 2 بخش‌کرده، و اگر باقیمانده‌ای داشت، مقدار Fals را برمی‌گرداند، زیرا فرمول در عین حال حاوی تست شرطی =0 می‌باشد. اگر تابع Mod هر چیزی جز صفر را برگرداند، تست‌های شرطی fals می‌شوند. لذا قالب‌بندی فقط هنگامی اعمال می شود که فرمول مقدار True را ( در ردیف‌های زوج) برگرداند. برای کسب اطلاعات بیشتر درباره قالب‌بندی « با استفاده از فرمت‌های شرطی» نگاه کنید.

تابع COMBIN

تابعCombin تعداد ممکن ترکیبات، یا گروه‌هایی را که در یک روی هم گذاری منابع می توان تشکیل داد. تعیین می کند. این تابع دارای دو آرگومنت (number, number-chosen) می باشد که number تعداد جمع الام در منبع و number –chosen تعاد اقلامی است که شما می خواهید در هر ترکیبی گروه بندی شود. برای مثال برای تعیین تعداد تیم های فوتبال 12 نفره مختلف که از یک گروه 17 نفره بازیکن می توان تشکیل داد، فرمول =combin (17,12) را باید تایپ کنید. نتیجه محاسبه نشان می دهد که با این تعداد بازیکن 6188 تیم می توان تشکیل داد.

نکته : شانس خود را امتحان کنید.

تابع combin می تواند امکان اینکه مثلا در یک بازی چقدر شانس آورید را برآورد کند. تعداد ترکیبات بازی طبق فرمول =combin (52,5) محاسبه می شود که نشان می دهد با یک دست کارت 52 تایی ، 960/598/2 حالت امکان ترکیب 5 کارت با یکدیگر وجود دارد.

در یک لاتاری برای بردن باید حالت های انتخاب 6 شماره از 49 شماره را در نظر بگیرید. شانس برد شما در این بازی حاصل فرمول = combine (49,6) می باشد که نتیجه محاسبه این فرمول یعنی تعداد ترکیبات ممکن آن 816/983/12 می باشد.

تابع Rand و تابع Randbetween

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

تابع randbetween که با نصب برنامه افزودنی analysis toolpak در دسترس قرار می گیرد کنترل بیشتری از rand را ارائه می دهد. با تابع randbetween می توانید دامنه ای از اعداد که در آنها مقدارهای صحیح اتفاقی تولید می شوند را تعیین کنید.

آرگومنت (bottom, top) نشان دهنده کوچکترین و بزرگترین عدد صحیحی است که تابع باید به کار ببرد مقدار این آرگومنتها نیز در نظر گرفته می شوند. برای مثال فرمول =randbewween(123, 456) می تواند هر عدد صحیح بین 123 به بالا تا خود 456 را برگرداند.

استفاده از توابع روند کننده

اکسل دارای چند تابع مختص کارهای روند کردن اعداد به مقدار تعیین شده می باشد.

توابع round, rounddown, roundup

تابع round هر عددی را به عددی با تعداد ارقام مشخص اعشاری ( با صفر قرار دادن اعداد کوچکتر از 5 و افزودن عدد 1 به رقم ماقبل آخر در صورتی که رقم آخر بزرگتر از 5 باشد روند می کند. این تابع دارای دو آرگومنت (number, number,-digits) می باشد اگر آرگومنت number-digits یک عدد مثبت باشد در این صورت مقدار number به تعداد تعیین شده ارقام اعشاری روند می شود و اگر num-digits یک مقدار منفی باشد عدد تا سمت چپ نقطه اعشاری روند می شود. اگر num-digit صفر باشد تابع به نزدیکترین عدد صحیح روند می شود. برای مثال فرمول =round (123, 4567, -2) عدد 100 را بر می گرداند و فرمول =round (123.4567, 3) عدد 123.457 را بر می گرداند. توابع rounddown, roundup شبیه تابع round عمل می کنند و همان طور که از اسم دو تابع استنباط می شود ترتیب روند کردن عدد در آنها به ترتیب همیشه در جهت روند سازی به عدد کمتر یا بیشتر می باشد.

توابع even, odd

تابع even عدد را به نزدیکترین عدد زوج بعدی روند می کند تابع odd عدد را به نزدیکترین عدد فرد بعدی روند می کند. اعداد منفی به طور متناسب به عدد قبلی روند می شوند. برای مثال فرمول =even (23,4) عدد 24 را بر می گرداند و فرمول =odd(-4) عدد -5 را بر می گرداند.

توابع floor و ceiling

تابع floor‌ عدد را به نزدیکترین مضرب داده بعدی و ع دد ceiling عدد را به نزدیکترین مضرب داده قبلی روند می کند. این توابع دارای دو آرگومنت (number, multiple) می باشد برای مثال فرمول =floor (23.4, 0.5) عدد 23 را بر می گرداند و فرمول =ceiling (5, 1.5) عدد 6 را بر می گرداند.

استفاده از تابع انعطاف پذیر Mround

تصویر کنید برای روند کردن یک عدد به هر مضربی جز -10 برای مثال روند کردن اعداد به شانزدهمین به نحوی که عدد به صورت کسری قالب بندی شود هیچ وقت مخرج بزرگتر از 16 نداشته باشد. تابع به نحوی که عدد به صورت کسری قالب بندی شود هیچ وقت مخرج بزرگتر از 16 نداشته باشد. تابع mround که در analysis toolpak قرار دارد هر عددی را به مضربی که تعیین کنید روند می کند.

این تابع به صورت =mround (number, multiple) می باشد. براث مثال با تایپ کردن فرمول =mround (a1,0625) عدد نمایش داده شده در خانه A1 افزایش 16/1 خواهد داشت. این تابع در صورتی روند می شود که باقیمانده پس از تقسیم عدد بر مضرب حداقل به مقدار نصب مضرب باشد اگر می خواهید این را در یک فرمول موجود به کار ببرید فقط در این فرمول mround مقدار A1 ( در این مثال) را با فرمول خود جایگزین کنید.

تابع INT

تابع INT اعداد را به نزدیکترین عدد صحیح قبلی روند می‌کند. برای مثال هر دو فرمول

=INT (100.01)

= INT ( 100 . 99999999)

با وجودی مقدار که عدد 100 . 99999999 عملا 101 می‌باشد هر دو فرمول عدد 100 را برمی‌گردانند. اگر عدد منفی باشد، INT آن عدد را نیز به عدد صحیح قبلی روند می‌کند. اگر همه اعداد در مثالها منفی باشند. حاصل مقدار روند شده نیز منفی خواهد شد. مثل INT= ( 100 . 99999999) که معادل 101- می شود.

تابع TRUNC

تابع TRUNC اعداد سمت راست نقطه اعشار را بدون توجه به علامت عدد حذف می‌ند. این تابع دارای آرگومنتهای (numcer , num , digits) می باشد. اگر مقدار آرگومنت num , digits مشخص نشده باشد این مقدار معادل صفر قرار داده می‌شود. در غیر این صورت تابع TRUNC هر عددی را بعد از تعداد ارقام تعیین شده در num , digits بعد از نقطه اعشار حذف می‌کند. براای مثال، فرمول(13, 978) TRUNC= مقدار 13 برمی‌گرداند و فرمول (13, TRUNC 978 , 1) = مقدار 13.9 را بر می‌گرداند.

تابع AVERAGE در مقابل تابع AVG

در سایر برنامه‌های صفحه گسترده مانند لوتوس 1و 2و 3 از تابع آماری AVG برای محاسبه میانگین‌ها استفاده می شود. در بعضی از نسخه‌های قبلی اکسل، تایپ کردن فرمول AVG( 2,4,5,8)= ممکن بود منجر به دریافت مقدار خطای # Name گردد. ولی اکنون اکسل تابع AVG را با وجود باز شدن یک کادر محاوره‌ای هنگام تایپ کردن تابع و پرسیدن این که آیا مایلید AVG را با تابع AVERAGE تبدیل نمی‌کند این است که هدفع یادگیری استفاده از نام صحیح تابع توسط کاربر می باشد.

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

برای کسب اطلاعات بیشتر به ضمیمه B «مرجع توابع» نگاه کنید.

آشنایی و درک توابع متنی

توابع متنی در اکسل، یکی از سودمندترین ابزارهای واژه‌پردازی و مدیریت داده‌ها می‌باشند که در هر جایی به چشم نمی‌خورند- این توابع قادر به انجام کارهایی هستند که یک واژه‌پرداز قادر به انجام آن نمی‌باشد. برای مثال می‌تانید توابع Zlean , Trim برای حذف فضاهای زاید و کاراکترهای غیر چاپی که برای پاک کردن داده‌های وارد شده بسیار خوب می‌باشند کاری که انجام آن با استفاده از جستجو و جایگزینی سخت و غیر ممکن است را انجام دهند.

توابع PROPER , LOWER , UPPER و حروف بزرگ و کوچک کلمه‌ها، جمله‌ها و پاراگرافها را بدون تایپ کردن مجدد آنها تغییر می‌دهند. شما برای متن‌هایی که از اسناد دیگر به اکسل کپی می‌کنید نیز می‌توانید این تابع را به کار ببرید. سپس Paste Special , Edit را برای تبدیل فرمول‌ها به مقدارهای حاصل و برگرداندن متن به سند اصلی انتخاب کنید.

برای کسب اطلاعات بیشتر درباره توابع انفرادی، به ضمیمه B «مرجع توابع» نگاه کنید.

استفاده از توابع انتخاب شده متنی

توابع متنی، ورودی‌های متنی عددی را به اعداد و ورودی‌های عددی را به رشته‌های متنی می کنند.

تابع TEXT

تابع TEXT یک عدد را به یک رشته متنی با فرمت خاص تبدیل می‌کند و آرگومنتهای این تابع عبارتند از Value نشان دهده هر عددع فرمول یا مرجع به خانه کاربرگ و format text نشان دهنده فرمت رشته حاصل می باشد. برای مثال، فرمول = TEXT ( 98/4 "0.00") رشته متنی 25.50 را برمی‌گرداند.

شما می‌توانید از هر نماد قالب‌بندی در اکسل به جز علامت ستاره & برای تعیین فرمت دلخواه در این تابع استفاده کنید، ولی مجاز به استفاده از فرمت General نمی باشید.

تابع DOLLAR

همانند تابع TEXT، تابع DOLLAR نیز عدد را به یک رشته تبدیل می‌کند. ولی تابع DOLLAR رشته حاصل را به صورت پولی با تعداد مشخصی عدد اعششار قالب‌بندی می‌کند. آرگومنتهای Number , Decimals) فرمول = DOLLAR ( 45, 899 , 2) رشته متنی $ 45.90 را برمی‌گرداند. توجه داشته باشید که اکسل در صورت لزوم عدد را روند می‌کند.

در صورتی که آرگومنت Decimal را حذف کنید، اکسل دو رقم اعشاری برای نتیجه حاصل در نظر می‌گیرد. اگر بعد از اولین آرگومنت، کاما قرار دهید ولی آرگومنت دوم را حذف می‌‌کنید، اکسل هیچ رقم اعشاری را درج نخواهد کرد. اگر برای Decimal عدد منفی تایپ کنید، اکسل عدد را تا سمت چپ نقطه اعشار روند می‌کند.

تابع LEN

تابع LEN تعداد کاراکترهای ورودی را برمی‌گرداند. تنها آرگومنت این تابع می‌‌توان یک عدد، یک رشته قرار گرفته در داخل دو کوتشین یا مرجع به یک خانه کاربرگ باشد. صفرهای دنباله نادیده گرفته می‌شوند. برای مثال فرمول = LEN ("Test") عدد 4 را برمی‌گرداند.

تابع LEN طول متن یا مقدار نمایش داده شدهع نه طول متن یا مقدار زیرین را برمی‌گرداند برای مثال، فرض کنید خانه A10 دارای فرمول = A1 +A2+A3+A4+A5+A6+A7+A8 می‌باشد و نتیجه محاسبه این فرمول مقدار 25 می باشد. فرمول = LEN ( A10) مقدار 2 را برمی‌گرداند نشان دهنده طول مقدار محاسبه شده 25 می‌باشد. مرجع خانه کاربرگ به منزله آرگومنت تابع LEN خود می‌توانند شام تابع رشته‌ای دیگری باشد. برای مثال، اگر خانه A1 حاوی تابع = Rept ( "-*" , 75) باشد که دو کاراکتر خط فاصله و ستاره ، *، را 75 بار در یک خانه کاربرگ تکرار می‌کند، فرمول = LEN (A1) مقدار 150 را برمی‌گرداند.

توابع Code , Char : ACLL

درهر کامپیوتری از کدهای عددی برای نمایش کاراکترها استفاده می‌شود. معروف‌ترین سیستم عددی American Standard Code for Inter Notional Interchange (ASCLL) می باشد. در این سیستم اعداد 0 تا 127 نشان دهنده همه اعداد، حروف و نمادها می‌باشند.

توابع Char و Code در رابطه با این کدهای ASCLL به کار می‌روند. تابع Code کاراکتری را برمی‌گرداند که مربوط به یک عدد که ASCLL می باشد. تابع Code عدد که ASCLL را برای اولین کاراکتر آرگومنت خود بر می‌گرداند. برای مثال فرمول = Char (8) متن S را بر می‌گرداند. فرمول = CODE ( "S") کد ASCLL 83 را بر می گرداند. اگر به عنوان آرگومنت متنی یک کاراکتر حرفی را تایپ کنید باید حتما این کاراکتر را دو کوتیشن قرار دهید درغیر این صورت اکسل مقدار خطای ? NAME # را برمی‌گرداند.

نکته : نمادپردازی

اگر اغلب از نمادهای خاص ASCLL استفاده می‌‌کنید، شماره کد ASCLL را با تابع Char برای ایجاد سریع یک نماد بدون استفاده از فرمان Symbol , Insert به کار ببرید. برای مثال، برای کپی کردن یک نماد ثبت کپی رایت، یعنی نماد ^® فرمول Char (174) = را تایپ کنید.

توابع پاکسازی CLEAN , TAIM

کاراکترهای خالی قبل و بعد، اغلب مرتب‌سازی ورودی‌های یک کاربرگ یا یک پایگاه داده‌ها می گردد. اگر از توابع رشته‌ای برای دستکاری متن در کتابچه‌ کار خود استفاده می‌کنید، فاصله‌های اضافی می‌توانند مانع کارکرد صحیح فرمول گردند. تابع Trim کاراکترهای خالی قبل، بعد و اضافی یک رشته را پاک می‌کند و فقط تک فاصله بین کلمه ها را حفظ می کند.

تابع CLEAN شبیه تابع TRIM می باشد، با این تفاوت که این تابع فقط برای کاراکترهای غیر قابل چاپ مانند کدهای خاص برنامه و کلیدها به کار می‌رود. تابع CLEAN به خصوص، اگر داده‌ها را از برنامه دیگری یا یک سیستم عامل دیگر وارد کرده باشید سودمندی خود را نشان می‌دهد، زیرا اغلب در پروسه تبدیل، کاراکترهای غیر قابل چاپ را که به صورت نماید یا در کادر رؤیت می شوند مشخص می‌سازد. از تابع CLEAN می‌توانید برای پاک کردن این کاراکترها از داده‌ها استفاده کنید.

تابع EXACT

تابع EXACT یک تابع شرطی است که مشخص می سازد که آیا دو رشته کاملا با یکدیگر تطاابق دارند یا نه. در این تابع قالب‌بندی نادیده گرفته می‌شود، ولی این تابع بین حروف بزرگ و کوچک تفاوت می گذارد. اگر دو رشته مقایسه شده کاملا با یکدیگر جور باشند نتیجه برگشتی این تابع True می باشد.

هر دو آرگومنت این تابع باید رشته‌های عددی قرار رگفته در علامت کوتیشن، مرجع به خانه‌هایی که حاوی متن بوده، مقدارهای عددی، یا فرمول‌هایی که مقدارهای عددی را ارزیابی می‌کنند باشند. برای مثال، اگر هر دو خانه A6 , A5 از کاربرگ حاوی متن Totals باشند فرمول = EXACT ( A5 , A6) مقدار True را بر می‌گرداند.

توابع حروف بزرگ و کوچک PROPER , LOWER, UPPER

سه تابع موضوع کاراکترها در رشته‌های منی را تغییر می‌دهند. توابع Lower , Upper کاراکتر اول در هر کلمه را به کاراکتر بزرگ تبدیل می کند. و هر حرفی که در یک رشته متنی به دنبال آن حرف دیگری قرار نگیرد را نیز به کاراکتر بزرگ تبدیل می‌کندو بقیه حروف را به حروف تبدیل می‌کند. برای مثالع اگر خانه A1 حاوی متن Mark Dodge باشد، شما می‌توانید فرمول = Upper (A1) برگرداندن مقدار MARK DODGE و یا فرمول = LOWER (A1) را برای برگرداندن Mark Dodge به کار ببرید.

ولی در صورتی که متن حاوی نشانه‌های نقطه‌گذاری باشد نتیجه قابل پیش‌بینی نخواهد بود. برای مثال اگر خانه A1 کاربرگ حاوی متن it was't bad باشد تابع Proper آن را به it was Bad تبدیل خواهد کرد.

استفاده از توابع زیر رشته متنی

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

توابع SEARCH , FIND

از توابع serch , find برای مشخص کردن محل یک زیر رشته از یک رشته استفاده می شود. هر دو تابع محل رشته از کاراکتری را که ذکر کرده‌اید مشخص می سازند. ( اکسل فاصله‌های خالی و نشانه‌های نقطه‌گذاری را نیز به عنوان یک کاراکتر شمارش می کند.) عملکرد این دو تابع شبیه یکدیگر می باشد با این تفاوت که تابع find نسبت به حروف بزرگ و کوچک حساس می باشد و بین آنها تفاوت می گذارد و در SEARCH استفاده از کاراکترهای wildcard مجاز است. هر دو تابع دارای یک نوع آرگومنت ( find . text , within, text , start, num) می باشند.

آرگومنت اختیاری serch – num هنگامی کارایی دارای که آرگومنت within, text بیشتر از یک بار به ازاء text find روی دهد. اگر شما آرگومنت start, num را حذف کنید، اکسل فقط اولین موردی را که با متن find . text جود درآید. گزارش می کند. برای مثال برای پیدا کردن محل p در رشتهOperaA Night at the ، شما باید فرمول = FIND ( "P" , "A" Night at the Opera را تایپ کنید. فرمول مقدار 17 را برمی‌گرداند زیرا p هفدهمین کاراکتر در این رشته است.

اگر از ترتیب کاراکتری که در جستجوی آن هستید اطمینان ندارید می‌توانید از تابع serch با اضافه کردن wildcard ها در رشته find . text استفاده کنید.

فرض کنید اسامی Smyth , Smith را در کاربرگ خود به کار برده‌اید. برای تعیین این که کدام یک از این اسامی در خانه A1 قرار دارند فرمول = SEARCH( " smith" , A1) را تایپ کنید. اگر خانه A1 کاربرگ حاوی John Smith یا John Smyth باشد تابع SEARCHمقدار 6 را برمی‌گرداند که نقطه شروع رشته Smith می باشد. ولی اگر از تعداد کاراکترها مطمئن نیستید از کاراکترwildcard ستاره (*) استفاده کنید. برای مثال، برای یافتن محل Allan یا Alan در متنی ذخیره شده در خانه A1 فرمول SEARCH ( "A* an " , A1 ) = را تایپ کنید.

توابع LEFT , RIGHT

تابع Right سری منتهی الیه سمت راست کاراکترها در یک رشته ذکر شده را برمی‌گرداند. تابع Left سری منتهی‌الیه سمت چپ این کاراکترها را برمی‌گرداند. آرگومنتهای این دو تابع یکسان می‌باشند. آرگومنت num chars تعداد کاراکترها برای استخراج از آرگومنت‌ Text را نشان می دهد.

در این توابع، فاصله‌های خالی در آرگومنت متن به عنوان کاراکتر شمرده می‌شوند. اگر متن دارای کاراکترهای خالی در قبل از رشته یا در دنباله خود داشته باشد باید از تابع Trim همراه با تابع Right یا Left استفاده کنید تا نتیجه محاسبه تابع غیر قابل پیش‌بینی نباشد. برای مثال فرض کنید در خانه A1 کاربرگ خود رشته Test is a Test را تایپ کرده اید. فرمول = Right ( A1 , 4) کلمه Test را برمی‌گرداند.

تابع MID

از تابع MID می‌توان برای استخراج یک سری کاراکترهای از رشته متنی استفاده کرد. این تابع دارای آرگومنتها می باشد. برای مثال اگر خانه A1 کاربرگ حاوی متن This is a long text باشد، شما می‌توانید فرمول = MID ( A1 , 11 , 9) را برای استخراج کاراکتر long text از ورودی خانه A1 به کار ببرید.

اشکال‌یابی و اشکال زدایی

توابع SUBSTITUTE , REPLACE

توابع REPLACE و SUBSTITUTE متن جدید را جایگزین متن قدیمی می‌کنند. تابع Replace رشته‌ای از کاراکترها را جایگزین رشته دیگری از کاراکترها کرده و دارای آرگومنتهای unm – chars , new , text می باشد. فرض کنید خانه A1 دارای متن Eric Miller , Geo می باشد. برای جایگزین کردن چهار کاراکتر اول این رشته با Geof فرمول = REPLACE (A1 , 1 , 4 , " Geof") را تایپ کنید نتیجه Geof Miller , Geo خواهد شد.

در تابع SUBSTITUTE متنی را که باید جایگزین گردد مشخص می سازید. این تابع دارای‌ آرگومنتهای text , old text , new , text , instance text) می‌باشد. فرض کنید خانه A1 حاوی متن Mandy می باشد که می‌خواهید آن را در خانه A2 قرار دهید ولی باید آن را به Randy تبدیل کنید. برای این منظور فرمول = SUBSTITUTE (A1 ,. "M" , "R" ) را در خانه A2 تایپ کنید.

آرگومنت- numinstance به طور اختیاری فقط رخداد تعیین شده Old – text را جایگزین می‌کند برای مثال، اگر خانه A1 حاوی متن "Through the Hoope" باشد عدد 4 در فرمول = SUBSTITUTE (A1 , 4 , "1" , "h") از اکسل می خواهد تا یک را به جای چهارمین h پیدا شده در خانه A1 قرار دهد. در صورت حذف آرگومنت num- instance اکسل کلیه رخدادهای old text را به new text تغییر می‌دهد.

نکته : شمارش رخدادها

در این مورد فکر کنید شما می‌توانید یک فرمول برداری را با استفاده از تابع Substitute برای شمارش تعداد رخدادهای یک رشته متنی در یک دامنه از خانه‌های کاربرگ به کار ببرید. فرمول = Sum ( LEN (< Range>) LEN (SUBSTITUTE ( < Range> . "text" , " " ))) LEN ( "text") برای شمردن تعداد فقره‌هایی که Lext "" در < Range> تکرار می شود به کار ببرید. این فرمول را تایپ کرد، و دکمه Ctrl + Shift+ Enter را فشار دهید.

تابع CONCATINATE

برای سر هم کرد رشته ای از 30 رشته کوچکتر یا مراجع تابع Concatinte تابع مناسب این کار بوده که همان کار کاراکتر & را انجام می دهد. برای مثال اگر خانه B4 کاربرگ حاوی متن Pacfic با یک کارکت خالی بعد از آن می‌باشد، فرمول = CONCATINATE ( B4 , "Musucal Instruments") مقدار Pacific Musical Instruments را برمی‌گرداند.

اشکال یابی و اشکال زدایی

تبدیل تاریخ‌های به هم الحاق شده به اعداد سریالی

اگر در صدد الحاق محتویات یک خانه فرمت شده به صورت تاریخ بربیایید، نتیجه آنها که انتظار دارید نخواهد بود. زیرا یک تاریخ در اکسل فقط یک عدد سریالی است و آن چه که شما به طور معمول مشاهده می کنید نمایش قالب‌بندی شده تاریخ می باشد ولی در صورتی که محتویات یک خانه قالب‌بندی تاریخ را به اطلاعات دیگر ملحق سازید نسخه فرمت نشده تاریخ به اطلاعات دیگر ملحق می‌شود. برای جلوگیری از این رخداد از تابع text برای تبدیل این عدد سریالی به یک قابل سازماندهی مجدد استفاده کنید. برای مثال، فرض کنید خانه A1 کاربرگ حاوی متن Today Date is"" و خانه A2 کاربرگ حاوی فرمول = Now() می باشد و به نحوی قالب‌بندی شده است که تاریخ را به صورت dd/mm/ yyy نشان دهد. با وجود این فرمول = CONCATINTE (A1 , "" A2 ) باعث می شود که مقدار sDate is 38050 نمایش داده شود. برای رفع این مشکل ، تابع Text با اجرای تابع مقدار Today Date is 03/04/2004 ( یا هر تاریخ دیگری) برگشت داده می شود. توجه داشته باشید که این فرمول دارای یک کاراکتر فاصله به عنوان یک آرگومنت جدا ("") بین و آرگومنتهای مرجع خانه کاربرگ می باشد.

آشنایی و درک توابع منطقی

از توابع منطقی برای تست کردن شرایط خاص استفاده می‌شود. این توابع اغلب در مبحث منطق بولی، عملگرهای منطق Logical Operators نامیده می شوند که به افتخار Grorge Boole ریاضی دان فرانسوی به این نام خوانده می‌شود. عملگرهای بولی در تئوری تنظیم و گاهی اوقات در آموزش مفاهیم منطقی در مدارس به کار برده می شوند از این عملگرها برای گرفتن دو نتیجه Fals , True استفاده می‌‌شود.

در این تست فقط به شرح توابع منطقی سودمندتر می‌پردازیم.

استفاده از توابع انتخاب شده منطقی

اکسل دارای مجموعه‌ای غنی از توابع، منجمله توابعی که در برنامه افزودنی Analysis Toolpak می باشند است. در بیشتر توابع منطقی، از تست‌های شرطی برای تعیین Fals , True بودن یک شرط ذکر شده است می شود.

فرمول های افزایش دهنده کارایی مورد استفاده در تابع SUMIF

اگراغلب از تابع If برای اجرای تست‌های شریط در روی ردیف‌ها یا ستون های انفرادی استفاده کرده و سپس برای تهیه جمع ستون / ردیف از Sum استفاده می‌کنید. تابع SUMIF کار شما را تا اندازه‌ای تسهیل خواهد بخشید. با استفاده از Sum if می‌توانید مقدارهای خاصی را به یک دامنه مبتنی بر معیاری که ارائه می دهید اضافه کنید. برای مثال فرمول = SUMIF ( C 12; V27 , "Yes" , A12 ; A27) را برای پیدا کردن جمع کلیه اعداد در A12; A27 که در آن خامه در همین ردیف در ستون C حاوی لغت Yes می باشد تایپ کنید. این فرمول کلیه محاسباتی را که لازم دارید برایتان انام می‌دهد و لزوم ساخت یک ستون ار فرمول های if را منتفی می‌سازد.

تابع IF

تابع if مقدار را بر اساس تست‌های شرطی تهیه شده برمی‌گرداند. این تابع دارای آرگومنتهای (logical – text , value , if true , value , if false) می باشد. باری مثال این فرمول اگر مقدار در خانه A6 کوچکتر از 22 باشد مقدار 5 را برمی‌گرداند. و در غیر این صورت مقدار از 10 برگشت داده می‌شود. توابع دیگر If را می‌توان به صورت توابع تو در تو به کار برد. برای مثالع فرمول، = If ( SUM (A1 : A10) > Sum ( A1 ; A10 ) , 0 ) مقدار جمع A1 تا A10 را در صورتی که حاصل جمع بزرگتر از صفر باشد برمی‌گرداند و در غیر این صورت مقدار صفر برگشت داده می شود.

در عین حال شما می‌توانید از آرگومنهای متنی برای برگرداندن هیچ چیزی سفر، در صورتی که نتیجه False باشد استفاده کنید. آرگومنت Logical – Test نیز می‌تواند متنی باشد. دو ورودی متن دقیقا جز برای نمونه، باید با همدیگر تطابق داشته و یکسان باشند.

توابع NOT , OR , AND

سه تابع NOT , OR , AND شما را در انجام تست‌های شرطی مرکب یاری می‌دهند. این سه تابع با توابع منطقی = < > , < = , >= , <, > , کار می‌کنند. توابع OR , AND می‌توانند تا 30 آرگومنت منطقی داشته باشند. تابع NOT فقط دارای یک آرگومنت است . آرگومنت‌ها می‌توانند تست‌های شریط مراجع به خانه‌هایی از کاربرگ که حاوی مقدارهای منطقی هستند باشند.

تصور کنید که می‌خواهید اکسل متن Pass را فقط در صورتی که متوسط معدل دانشجو بالای 75 با در نظر گرفتن تعداد غیبت‌ها کمتر از5 چلسه است ارائه ده.

شکل 1-14 ما فرمول: = If ( AND ( G4 < 5 , F4 > 75 ) "Pass" , Fail" را تایپ کرده‌ایم. این فرمول دانشجویان ردیف 5 را نادیده می‌گیرد زیرا همه آنها بیشتر از 5 جلسه غایب بوده‌اند. اگر به جای تابع AND تابع OR را در فرمول نشان داده شده در شکل 1-14 به کار ببرید، کلیه دانشجویان قبول خواهند شد.

تابع OR مقدار منطقی True را در صورتی که هر یک از تست‌های شرطی درست باشند برمی‌گرداند. ولی تابع AND مقدار True را فقط وقتی برمی‌گرداند که کلیه تست های شرطی درست باشند.

از آنجایی که تابع NOT یک عمل را نفی می کند معمولا با توابع دیگر به کار می‌رود. تابع NOT اکسل را راهنمای می‌کند تا در صورتی که آرگومنت غلط باشد و مقدار True یا در صورت درست بودن آرگومنت مقدار منطقی False را برگرداند.

توابع تو در توی IF

گاهی اوقات، نمی‌توان یک مشکل منطقی را با استفاده از فقط عملگرهای منطقی و توابع NOT , OR , AND حل کرد. در این نوع مواردع می‌توانید با قرار دادن توابع if در داخل یک دیگر سلسله از تست‌ها را انجام دهید.

عدم استفاده از If های متعدد

شما می‌توانید فرمول هایی که حاوی 7 فرمول If تو در تو را بسازید. فرمول‌های تو در توی If تست‌های شرطی متداول را نشان می‌دهند ولی محدودیت هفت تابعی میتواند منجر به بروز مشکلاتی گردد، به خصوص اگر شما داده‌ها را از یک برنامه‌ای که در آن می‌توان تعداد بیشتری توابع تو در تو را به کار برد، مانند لوتوس 1 و 2 و 3 تبدیل می‌کنید. در این صورت یا باید فرمول‌های طولانی را شکسته و آنها را در دو خانه کاربرگ قرار دهید یا با رهیافتی دیگر این پروسه را انجان دهید مثلا با استفاده از توابع جستجو (Look up)

موارد دیگر استفاده از توابع شرطی

می‌توان کلیه توابع شرطی شرح داده شده در این فصل را به صورت فرمول‌های جدا گانه به کار برد. با وجودی که شما معمولا از توابعی مانند ISREF , ISNA , ISERROR , NOT , OR , AND در یک تابع IF استفاده می‌کنید ولی فرمولهایی مانند = AND (A1 > A2, A2< A3 ) را نیز برای انجام تست‌های شرطی ساده به کار ببرید. این فرمول مقدار منطقی True را اگر مقدار در A1 بزرگتر از مقدار در A2 باشد و مقدار در A2 کمتر از مقدار در A3 باشد را برمی‌گرداند. شما می‌توانید این نوع فرمول را برای تحلیل دادن مقدارهای False یا True در دامنه‌ای از خانه‌های عددی پایگاه داده‌ها شرایط False و Trueرا به عنوان معیار انتخاب برای چاپ یک گزارش ویژه به کار ببرید.

آشنایی و درک توابع اطلاعاتی

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

در این بخش درباره سودمندترین این توابع اطلاعاتی توضیح می‌دهیم.

استفاده از توابع انتخاب شده اطلاعاتی

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

توابع ERROR , TYPE و TYPE

تابع TYPE تعیین می‌کند که آیا یک خانه کاربرگ حاوی، متن، یک عدد، یک مقدار منطقی، یک بردار یا یک مقدار خطا می‌باشد. نتیجه این محاسبه کدی برای نوع ورودی در خانه ارجاع داده شده می‌گردد: که 1 برای بردار، 2 برای متن، 4 باری یک مقدار منطقی، 16 برای یک مقدار خطا، و 64 برای یک بردار به کار می‌رود.

تابع ERROR , TYPE نیز مانند TYPE محتویات یک خانه از کاربرگ را ارزیابی می‌کند با این تفاوت که در این تابع انواع مختلف مقدارهای خطا پیگیریمی‌شوند. نتیجه ارائه یک کد برای مقدار خطا در خانه ارجاع شده می‌باشد. برای خطا # Null! 2 برای خطای # Value ، 4 برای # Ref 5 برای خطای #Name ، 6 برای # NUM! و 7خطای # N/A هر مقدار دیگر در خانه ارجاع شده مقدار خطای # N/ A را برمی‌گرداند.

تابع COUNTER BLANK

تابع COUNTER BLANK تعداد خانه‌های خالی کاربرگ در دامنه تعیین شده را از طریق تنها ارگومنت خود می شمارد. این تابع تا اندازه‌ای گیج کننده است، زیرا فرمول‌هایی که رشته‌‌های متنی خالی مانند = "" یا صفر را ارزیابی می‌کنند، شاید به نظر خالی برسند، در حالی که این فرمول‌ها خالی نیستند، لذاغ نباید به عنوان خانه‌ةای خالی شمرده شوند.

استفاده از توابع اطلاعاتی IS

برای تعیین این که خانه های ارجاع داده شده دارای نوع مربوطه از مقدارها می باشند یا نه می‌توان از توابع IS TEXT , ISREF . IS NUMBER , IS NONTEXT , IS NA, IS LOGICAL , استفاده کنید و نیز در صورت نصب کردن Analysis Toolpak می‌توان از دو تابع IS EVEN و IS ODD نیز استفاده کرد.

کلیه توابع IS دارای فقط یک آرگومنت می‌باشند. برای مثال تابع، IS BLANK به فرم = IS BLANK ( Value) می‌باشد. آرگومنت Vakue یک مرجع به یک خانه کاربرگ می باشد. اگر Value به یک خانه خالی ارجاع داده شود، تابع مقدار منطقی True را بر می‌گرداند و در غیر این صورت مقدار False برگشت داده می شود.

مواظب نابهنجاری‌ها و چیزهای غیر عادی در توابع IS باشید.

با وجودی که به جاییک خانه تکی کاربرگ می‌توانید از یک دامنه خانه در کابرگ به عنوان آرگومنت در هر تابع IS استفاده کنید، نتیجه می‌تواند آن چه که مد نظرتان است نباشد. برای مثال، تصور می کنید که تابع در صورتی که دامنه ارجاع داده می‌شود درعوض رفتار آن بستگی به محل دامنه نسبت به خانه حاوی فرمول خواهد داشت. اگر این آرگومنت عطف به دامنه‌ای شود که ستون یا ردیف حاوی فرمول را قطع کند، BlankIS از تقاطع غیر صریح برای رسیدن به نتیجه استفاده خواهد کرد. به عبارت دیگر، این تابع فقط به یک خانه کاربرگ در دامه ارجاع داده شده نگاه می کند و فقط اگر این خانه در همان ردیف یا ستون خانه ای قرار گیرد که حاوی تابع باشد. در این حالت تابع بقیه دامنه را نادیده می گیرد. اما اگر این دامنه در هیچ ردیف یا ستونی با فرمول تسهیل نتیجه همیشه False خواهد شد.

یک نمونه ISERR

شما می‌توانید ISERR را برای نگرفتن پیام خطا به عنوان نتیجه فرمول به کار ببرید. برای مثال فرض کنید که می‌خواهیدد خانه‌هایی از کاربرگ را که حاوی یک رشته خاص کاراکتر، مانند 12A می باشند و منجر به نمایش کلمه Yes در خانه خالی باقی بماند.و می‌توانید توابع Find , If را برای انجام این منظور به کار ببرید، ولی اگر این مقدار پیدا نشود ، به جای ی خانه خالی، یکخانه با مقدار خطار # Value برگشت داده می شود. برای رفع این مشکل، تابع Iserr را به انی فرمول اضافه کنید. تابع Find محلی را که یک زیر رشته در یک رشته بزرگتر پیدا شده برمی‌گرداند. اگر زیر رشته در آن جا نیست، تابع Find مقدار #Value را برمی گرداند. راه حل این مشکل اضافه کردن یک تبع Iseer مانند IF ( ISERR ( FIND ( "12A . A 1 )) , "", Yes") می‌باشد. از آن جایی که شما علاقمند به دانستن نوع خطا نیستید که فقط حاصل محاسبه تابع است، این فرمول خطا را به دام انداخته و فقط نتیجه مورد نظر را نمایش می‌دهد.

آشنایی و درک توابع LOOKUP REEERENCE

توابع جستجو و مرجع به شما کمک می کنند تا از جدول های کاربرگ خود استفاده و آنها را به عنوان منابع اطلاعاتی جهت استفاده در جاهای دیگر در فرمول ها به کار ببرید. می‌توانید سه تابع اولیه زیر را برای جستجوی اطلاعات ذخیره شده در یک فهرست یا جدول برای دستکاری مراجع به کار ببرید. تابع LOOKUP ، تابع VLOLKUP ، و تابع HLOOKUP

علاوه بر این سه تابع جستجو، توابع قدرتمند دیگری نیز برای جستجو وجود دارند و ما تعدادی از آنها را در این بخش شرح می‌دهیم.

استفاده از توابع انتخاب شده REFERENCE , LOOKUP

توابع VLOLKUP و HLOOKUP تقریبا توابعی توابع یکسانی هستند که اطلاعات ذخیره شده در جدول‌هایی را که ساخته جستجو می‌کنند. HLOOKUP و VLOLKUP یا در جهت عمودی یا در جهت افقی به جستجو می‌پردازند ولی تابع LOOKUP از هر دو سو اطلاعات مورد نظر را جستجو می‌کند.

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

ساخت فرمول‌های خودکارسازی شده

می‌توانید فرمول‌های قدرتمند برای جستجو را با استفاده از ابزارهای افزودنی به وجود آورید.

جدول 1-14 فهرست آرگومنت‌های تابع LOOKUP و شرح آنها می‌باششد. تابع LOOKUP در دو فرم موسوم به Vector Form و Array Form به صورت زیر ارائه می شود.

جدول 1-14 آرگومنتهای تابع LOOKUP
آرگومنت	شرح
LOOKUP- Value	مقدار، مرجع خانه کاربرگ یا متن ( قرار داده شده در علامت کوتیشن) که می‌خواهید آن را در یک جدول یا دامنه پیدا کنید.
Table – arry	یک دامنه یا نام جدولی که می‌خواهید آن را جستجو کنید.
Row – index num Col-index- num	تعداد ردیف یا ستونی از جدول که از آن جدول می‌خواهید نتیجه را انتخاب کنید که نسبت به جدول شمرده می شود.
Rang - LOOKUP	یک مقدار منطقی که تعیین می‌کند آیا این تابع دقیقا با Vector–LOOKUP تطابق دارد، یا نه ، False را برای تطبقی دقیق Vector–LOOKUP تایپ کنید. مقدار پیش فرض این آرگومنت True می‌باشد که در طبق آن نزدیکترین مقدار هماهنگ در جدول پیدا می‌شود.
Vector- LOOKUP	یک دامنه یک ردیفی یا یک ستونی حاوی اعداد، متن یا مقدارهای منطقی
Result – Vector	یک دامنه یک ردیفی یا یک ستونی که باید به همان اندازه Vector- LOOKUP
Arry	دامنه‌ای حاوی اعداد، متن یا مقدارهای منطقی برای مقایسه با Vector- LOOKUP

تفاوت بین توابع LOOKUP در نوع جدولی است که هر تابع به کار می‌برد: VLOLKUP فقط با جدولهای برداری عموی کار می‌کند. HLOOKUP را می‌توان برای جدول‌های افقی به کار برد. فرم‌برداری Array From از تابع LOOKUP را می‌توان برای جدول‌های افقی یا برای جدول‌های تنظیم شده به صورت عمودی استفاده کرد. و- From Vector فقط برای ردیف‌های تکی یا ستونی‌های تکی داده‌ها به کار می‌رود.

Array From تابع LOOKUP تعیین کننده جستجو به نحو افقی یا عمودی بر اساس شکل جدول تعریف شده در آرگومنت Array می باشد. اگر تعداد ستون‌ها بیشتر از ردیف‌ها باشد LOOKUP اولین ردیف را به دنبال LOOKUP- Value می‌گردد. و اگر تعداد ردیف‌ها بیشتر ازستون‌ها باشد LOOKUP در اولین ستون به دنبال LOOKUP- Value خواهد گشت. تابع LOOKUP در اولین ستون به دنبال LOOKUP- Value خواهد گشت. تابع LOOKUP همیشه آخرین مقدار در ردیف یا ستون حاوی LOOKUP- Value را برمی‌گرداند و می‌توایند با استفاده از VLOLKUP یا HLOOKUP شماره یک ردیف یا یک ستون را برای جستجو ذکر کنید.

توابع HLOOKUP و VLOLKUP

در توابع VLOLKUP و HLOOKUP اعم از این که یک جدولی باید به صورت افقی یا عمودی در نظر گرفته شود بستگی به این امر دارد که مقدارهای مقایسه‌ای در کجا قرار دارد. اگراین ردیف جدول قرار داشته باشند، جدول افقی می‌باشد. مقدارهای مقایسه‌ای می‌توانند عدد یا متن باشند ولی در هر حال باید به ترتیب از بزرگ به کوچک تنظیم شده باشند. هیچ مقدار مقایسه‌ای نباید بیشتر از یک بار در جدول به کار رفته باشد.

آرگومنت index num دومین ایندکس را ارائه می‌دهد و به اکسل می‌گوید که تابع جستجو به کدام ستون یا ردیف از جدول برای نتیجه تابع نگاه کند. اولین ستون یا ردیف جدول دارای شماره ایندکس 1 می‌باشد که هیچ وقت نباید بزرگتر از تعداد ردیف‌ها یا ستون‌های جدول باشد. برای مثال اگر یک جدول عمودی دارای سر ستون باشد عدد ایندکس نمی‌تواند بزرگتر از 3 باشد. اگر هیچ مقداری با این وضعیت هماهنگ نباشد تابع مقدار خطا را برمی‌گرداند.

به خاطر بسپارید که این توابع جستجو به طور عادی دنبال بزرگترین مقدار مقایسه‌ای که کمتر از یا مساوی با مقدار LOOKUP- Value می‌باشند می‌گردند، نه به دنبال مقدار دقیق بین مقدار مقایسه‌ای و مقدار جستجو. اگر کلیه مقدارهای مقایسه‌ای در اولین ردیف یا ستون از دامنه جدول بزرگتر از مقدار جستجو باششند. تابع مقدار خطار # N/ A را برمی‌گرداند. ولی اگر کلیه مقدارهای مقایسه‌ای کمتر از مقدار مد نظر شما نباشد ، برمی‌گرداند. اگر به دنبال یک تطابق هستید، در آرگومنت Range LOOKUP مقدار Flase را تایپ کنید.

کاربرگ نمایش داده شده در شکل 3- 14 نمونه‌ای از یک جدول افقی است که در تابع HLOOKUP مورد استفاده قرار گرفته است.

تابع LOOKUP

تابع LOOKUP دارای دو فرم Vector عمودی و Arry افقی می‌باشد. هر دو فرم این تابع شبیه VLOLKUP و HLOOKUP بوده و تابع همان قوانین این دو تابع می باشند فقط در دو فرم عمودی و افقی با آرگومنتهای توصیف شده در جدول 1- 14 در اختیار کاربر قرار می‌گیرند.

مانند توابع HLOOKUP و VLOLKUP، در فرم عمودی تابع LOOKUP بزرگترین مقدار مقایسه که بزرگتر از مقدار جستجو نیست جستجو می‌شود. Vector- LOOKUP result Vector اغلب دامنه‌‌های همجوار هستند، ولی در صورت استفاده از تابع HLOOKUP الزامی به همجواری آنان نیست. آنها می‌توانند در ناحیه‌‌های جدا از هم در کاربرگ قرار داشته باشند و یک دامنه می تواند افقی و دامنه دیگر عمودی باشد. تنها پیش‌نیازز این است که تعداد عنصرهای هر دو باید یکسان باشد.

برای مثال، کاربرگ موجود در شکل 4-14 یعنی دامنه‌های غیر موازی را در نظر بگیرید. هر دو آرگومنت Vector- LOOKUP یعنی A1 ; A5 و آرگومنت result Vector یعنی D6 : H 6 دارای 5 عنصر می‌باشند. مقدار LOOKUP- Value می‌‌باشند. مقدار LOOKUP- Value 3 می‌باشد و با ورودی در سومین خانه Vector- LOOKUP تطابق دارد، و باعث می‌شود که نتیجه حاصل از فرمول در سومین خانه در دامنه نتیجه معادل 300 گردد.

فرم افقی Array From تابع مشابه تابع VLOLKUP و HLOOKUP است، ولی با یک جدول یا افقی یا عمودی سر و کار دارد و با در نظر گرفتن ابعاد جدول محل مقدارهای مقایسه را پیدا می‌کند. اگر در جدور ردیف‌های بیشتری از ستون‌ها وجود داشته باشد یا جدول از نظر تعداد ردیف و ستون یکسان باشدع این تابع جدول را به صورت یک جدول عمودی در نظر گرفته و فرض می‌کند که مقدارهای مقایسه در آخرین ستون سمت چپ قرار دارند. ولی اگر جدول دارای ستون‌های بیشتری نسبت به تعداد ردیف‌های مقایسه در اولین ردیف جدول قرار دارند. نتیجه همیشه در آخرین ردیف یا ستون جدول تعیین شده قرار داده می شود و شما نمی‌توانید تعداد ستون‌ها و ردیف را قید کنید.

از آنجایی که HLOOKUP و VLOLKUP بیشتر قابل پیش‌بینی و کنترل می‌باشد، شما معمولا استفاده از آنها را بر تابع LOOKUP ترجیح خواهید داد.

تابع Address

تابع Address یک روش ساده برای ساختن مرجع از اعداد را ارائه می دهد. این تابع دارای آرگومنتهای (row num – column num – abs num , A1 , sheet text) می‌باشد.

تابع CHOOSE

از تابع CHOOSE برای نشان داده یک فقره از مقدارهای یک لیست استفاده می شود. این تابع دارای آرگومنتهای (index num , Value 1 , Value 2 ) می‌باشد و تا 29 آرگومنت Value می‌تواند داشته باشد. آرگومنت index num مکانی در این لیست است که می‌خواهید به آن برگردید. این مقدار باید مثبت بوده و از تعداد عنصرهای لیست بیشتر نباشد. تابع مقدار این عنصر در لیست را که در این محل قرار دارد و با index num نشان داده می شود، برمی‌گرداند. شما می توانید مراجع خانه انفرادی را در این لیست به کار ببرید، ولی نمی‌توانید دامنه‌ها را قید کنید، شاید وسوسه شوید که تابعی مانند = CHOOSE ( A10 , C1 : C5) را برای گرفتن جای بیشتر برای یک تابع طولانی تر مانند تابع ذکر شده در مثال فوق بسازید. ولی در این صورت نتیجه کار یک خطای # Value خواهد بود.

تابع MATCH

تابع MATCH تا حد زیادی به تابع CHOOSE مربوط می‌شود. ولی در حالی که تابع CHOOSE فقره‌ای را که محل ذکر شده در آرگومنت index num را اشغال کرده برمی‌گرداند. تابع Match محل این فقره در لیست را که بیشتر از همه با مقدار جستجو تطابق دارد برمی‌گرداند.

این تابع دارای آرگومنتهای (Lookup Value – Lookup array . Match type ) می باشد که Lookup Value و فقره‌ها در Lookup array می‌توانند مقدارهای عددی یا رشته‌های متنی باشند، و type–Match قواعد برای جستجو را مطابق جدول 2- 14 ارائه می‌دهد.

جدول 2- 14 آرگومنتهای تابع Match
نوع Match	شرح
1 ( یا حذف شده)	این آرگومنت‌ بزرگترین مقدار در دامنه ذکر شده ( که باید به ترتیب صعودی ذخیره شده باشد) را پیدا می‌کند که این مقدار کوچکتر یا مساوی Lookup Value می‌باشد. اگر هیچ یک از فقره‌های موجود در دامنه ذکر شده با این معیار تطابق نداشته باشند، تابع مقدار خطاری # N/ A را برمی‌گرداند.
0	این آرگومنت اولین مقدار در دامنه ذکر شده ( بدون نیاز به مرتب‌سازی مقدارها در دامنه) را پیدا می‌کند که معادل مقدار Lookup Value می باشد. اگر هیچ یک از فقره‌ها در این دامنه با مقدار Lookup Value یکسان نباشد ، این تابع مقدار #N/ A را برمی‌گرداند.
-1	این آرگومنت، کوچکترین مقدار در دامنه ذکر شده ( که باید به ترتیب نزولی مرتب شده باشد) را پیدا می‌کند که بزرگتر یا مساوی با مقدار Lookup Value می باشد. اگر هیچ یک از فقره‌ها در دامنه ذکر شده با این مقدار تطابق نداشته باشند تابع مقدار #N/ A را برمی‌گرداند.

با استفاده از Match برای پیدا کرد یک رشته متنی شما باید یک آرگومنت typeMatch به مقدار 0 را به کار ببرید. سپس می‌توانید از کاراکترهای Wildcard را در آرگومنت Lookup Value استفاده کنید.

تابع INDEX

تابع INDEX دارای دور فرم می‌باشد. یک فرم برداری که یک مقدار را برمی‌گرداند و یک فرم مرجع که یک مرجع به خانه جدول را برمی‌گرداند.

تذکر: می توانید فرمول‌های قدرتمند جستجو را با استفاده از ابزارهای افزودنی که در آنها از تابع INDEX استفاده می شود، بسازید. به «استفاده از برنامه‌های راهنمای Lookup , Conditional . Sum » نگاه کنید.

فرم افقی / Array این تابع فقط با آرگومنتهای برداری سر و کار دارد. این تابع نه مرجع به خانه کاربرگ بلکه نتیجه حاصل را برمی‌گرداند. نتیجه، مقدار در برداری است که در colunmn num , row num مشخص شده است.

تذکر : هر یک از دو فرم تابع INDEX دارای ویژگی‌های سودمندی می‌باشند. با استفاده از فرم مرجع این تابع می‌توانید چند ناحیه غیر همجوار کاربرگ را به صورت مرجع دامنه جستجو به کار ببرید. با استفاده از فرم برداری این تابع می‌توایند دامنه‌ای از خانه‌های جدول را به جای یک خانه به عنوان نتیجه حاصل در اختیار داشته باشید.

فرم مرجع (Reference) این تابع یک آدرس خانه کاربرگ از یک مقدار را برمی‌گرداند که هنگامی که می‌خواهید عملیاتی را روی یک خانه به جای انجام عملیات روی مقدار این خانه انجام دهید، می‌توانید بسیار مفید واقع شود. ولی این تابع می‌تواند باعث سردرگمی نیز بشود، زیرا اگر تابع INDEX در داخل یک تابع دیگرقرار گرفته باشد، آن تابع می‌تواند مقدار این خانه از کاربر را که آدرس آن توسط تابع INDEX برگردانده شده به کار ببرید.

به علاوه فرم مرجع تابع INDEX، نتیجه خود را به صورت یک آدرس ارائه نمی‌دهد، بلکه مقدارهای موجود در آدرس را نشان می‌دهد. به خاطر بسپارید همچنین نتیجه در هر حال اگر حتی شبیه یک آدرس به نظر نرسد یک آدرس می‌باشد.

رهنمودهای زیر را هنگام کار با تابع INDEX به خاطر بسپارید:

· اگر برای آرگومنت colomn – num یا row num مقدار صفر را تایپ کنید. تابع INDEX برای کل ردیف یا ستون مرجعی را برمی‌گرداند.

· آرگومنت مرجع می‌تواند یک یا چند دامنه باشد که «ناحیه» خوانده می‌شوند. هر ناحیه باید مستطیل بوده و شامل اعداد، متن یا فرمول‌ها باشد. اگر این ناحیه‌ها همجوار نباشند، باید آرگومنت مرجع را در پرانتز قرار دهید.

· آرگومنت area num فقط در صورتی که مورد نیازز می‌باشد که بیشتر از یک ناحیه در مرجع قرار داده شود. این آرگومنت ناحیه‌ای را که آرگومنت‌های colomn – num یا row num در آنها به کار می روند را تعیین می‌کند. اولین ناحیه در مرجع قید می شود و اولین ناحیه 1 و دومین ناحیه 2 و الی آخر نامیده می ‌شوند.

با استفاده از کاربرگ ارائه شده در شکل 5-14 فرمول = INDEX ( C3 : F6 , 0 , 2) مقدار خطای # Value را نشان می‌دهد، زیرا آرگومنت row num از ناحیه 0، مرجع به کلیه ستون های ذکر شده با آرگومنت column num از ناحیه 2 یا دامنه .D3 : D 6 را برمی‌گرداند. اکسل نمی تواند به عنوان نتیجه یک دامنه را برگرداند. ولی می‌تواند این فرمول را در تابع دیگر قرار دهد مثل = SUM (INDEX ( C3: E6 , 0 , 2)) که نتیجه آن 2600 یعنی جمع مقدارها در دامنه D3: D6 می باشد. این مطلب فایده به دست آوردن یک مرجع به منزله یک نتیجه را نشان می‌دهد.

اکنون نحوه کار تابع INDEX را در دامنه‌های چندگانه در آرگومنت مرجع بررسی می کنیم ( هنگامی که بیشتر از یک دامنه به کار می‌رود، شما باید آرگومنت را در پرانتز قرار دهید). ناحیه A1:C5 و ناحیه D6 ; F10 آرگومنت area num (2) به تابع index می‌گوید تا روی ناحیه دوم از این ناحیه‌ها کار کند. این فرمول آدرس D6 را برمی‌گرداند. که این خانه کاربرگ در اولین ستون و اولین ردیف دامنه D6:F10 قرار دارد. نتیجه نمایش داده شده، مقدار در آن خانه می باشد.

تابع INDIRECT

تابع INDIRECT محتویات یک خانه را با استفاده از مرجع آن برمی‌گرداند. این تابع دارای آرگومنتهای (ref text , A1) می‌باشد که ref text یک A1 Style یا مرجع R1 C1 Style یا یک نام خانه کاربرگ ممی باشد. اگر A1 به صورت True باشد یا حذف شود اکسل ref text را به فرمت R1 C1 تفسیر خواهد کرد. اگر A1 به صورت True باشد یا حذف شود اکسل ref text را به فرمت A1 تفسیر خواهد کرد. اگر کاربرگ شما حاوی مقدار متنی B3 و خانه B3 حاوی مقدار 2.888 باشد، فرمول = indir را برمی‌گرداند. اگر کاربرگ شما به نحوی تنظیم شده است که مراجع R1 C1 Style را نشان می‌دهد و خانه R6 C3 حاوی مرجع متنی R3 C2 و خانه R3 C2 حوی مقدار 2.888 فرمول = INDEX نیز مقدار 2.888 را برمی‌گرداند.

توابع COLUMN , ROW

توابع ROW و COLUMN تعداد ردیف یا ستون از خانه یا دامنه عطف شده به آرگومنت تکی تابع می باشد. اگر آرگومنت حذف شده باشد این نتیجه شماره ردیف یا ستون از خانه‌‌ای است که حاوی تابع می‌باشد. اگر آرگومنت یک دامنه یا یک نام دامنه باشد و تابع به صورت برداری با فشار Ctrl + Shift+ Enter نتیجه تابع برداری است که شامل ارقام ستون یا ردیف از هر ستون و ردیفی در دامنه باشد.

توابع COLUMN و ROWS

توابع ROWS و COLUMN تعداد ردیف‌ها و ستون‌های ارجاع داده شده تنها آرگومنت تابع در یک مرجع یا یک بردار را برمی‌گرداند. آرگومنت این توابع می‌تواند یک مقدار ثابت برداری یک مرجع دامنه یا نام یک دامنه باشد. فرمول = ROW ( A 1 : A10 ) مقدار 10 را برمی‌گرداند زیرا دامنه A1: A10 شامل 10 ردیف می‌باشد و فرمول = COLUMN (A1: C10) مقدار 3 را برمی‌گرداندع زیرا دامنه A1: C10 شامل 3 ستون می باشد.

تابع AREAS

شما می‌توانید تابع AREAS را برای تعیین تعداد ناحیه‌ها در یک مرجع به کار ببرید. AREAS عطف به مراجع خانه انفرادی یا دامنه‌ای از کاربرگ‌ به منطقه‌ها می‌شوند. تنها آرگومنت این تابع می‌تواند یک مرجع به خانه کاربرگ، یک مرجع دامنه یا مراجعی به چند دامنه باشد. اگر چندین مرجع به دامنه‌‌های مختلف کاربرگ را در این تابع به کار می‌برید، باید آنها را در یک مجموعه از پرانتزها قرار دهید تا اکسل کاماهایی را که دامنه‌ها را از همدیگر جدا می‌سازد به غلط تفسیر نکند. ( با وجودی که این تابع فقط دارای یک آرگومنت می باشد، اکسل کاماهای بسته نشده را به عنوان جداسازی‌های آرگومنت تلقی می‌کند.)

تابع TRANSPOSE

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

شما باید تابع TRANSPOSE را به عنوان یک فرمول برداری در دامنه‌ای که دارای تعداد ردیف‌ و ستون برابر با ردیف و ستون آرگومنت Array می‌باشد به کار ببرید.

تذکر : برای جابه جایی سریع و آسان، دامنه ای را که می‌خواهید جا به جا کنید انتخاب کرده و دکمه Ctrl + C را برای کپی کردن دامنه فشار دهید. روی خانه‌ای از کاربرگ که می‌خواهید شروع گوشه چپ فوقانی در ناحیه جا به جا شده باشد کلیک کرده Paste special , Edit انتخاب نمایید و سپس روی گزینه TRANSPOSE (جا به جایی ) کلیک کنید.

فهرست مطالب

توابع کاربردی و روزمره1

آشنایی و درک توابع ریاضی.. 1

استفاده از تابع sum... 2

دکمه AutoSum... 2

گسترش خودکار دامنه. 3

استفاده از توابع انتخاب شده ریاضی.. 4

توابع SUMPRODUCT , PRODUCT.. 4

تابع MOD.. 5

تابع COMBIN.. 6

تابع Rand و تابع Randbetween. 7

استفاده از توابع روند کننده8