فرمولنويسي و كار با تابعها

فصل سوم
فرمولنويسي و كار با تابعها
فرمولها و فرمولنويسي از مهمترين قابليتهاي اكسل هستند. فرمولها، عبارات محاسباتي يا
مقايسهاي هستند كه روي مقادير عددي يا رشتهاي با كمك عملگرها، عملياتي را انجام ميدهند.
تابع يكي از امكانات مهم اكسل است و انجام عمليات را در فرمولها آسان ميكند.
پس از آموزش اين فصل ، هنرجو ميتواند :
فرمول مورد نظر را در خانه درج كند.
انواع آدرسدهي را تعريف كند و از آنها استفاده كند.
ناحيهاي از كاربرگها را نامگذاري كند.
تابعها را تعريف كرده و از آن استفاده كند.
انواع تابعها را بشناسد و با تابعهاي عمومي كار كند.
انواع خطاها را شناسايي كرده و آنها را رفع كند.
١-٣ فرمولها و فرمولنويسي
فرمولها، عبارتهاي محاسباتي يا مقايسهاي هستند كه روي انواع دادهها ــ كه در فصل قبل
معرفي شدند ــ به كمك عملگرها، عملياتي را انجام ميدهند. در اكسل فرمولها با عالمت
مساوي شروع ميشوند. هر فرمول )عبارت( ميتواند يك يا چند قسمت داشته باشد. اجزاي
1 و انواع تابعها. در ادامه اين اجزا
فرمول عبارتاند از : ثابتها، آدرس خانهها )متغيرها(، عملگرها
را به اختصار بررسي ميكنيم:
ــ ثابتها:
مقاديري هستند كه تغيير نمي ً كنند. مثال عدد 190 و يا عبارت »درآمد ماهانه«. اگر بخواهيم
1 .با مفهوم اين كلمات در درس برنامهسازي1 آشنا شدهايد.
156
مقدار عدد 200 را بهجاي 190 استفاده كنيم، بايد عدد را در فرمول بازنويسي كنيم.
ــ آدرس خانهها:
آدرس خانهاي است كه مقدار آن در فرمول استفاده مي ً شود. مثال ميتوانيم مقدار عدد 200
را در خانهي A15 درج كنيم و از آدرس A15 در فرمول استفاده كنيم. در اين صورت بهجاي
بازنويسي فرمول فقط كافي است كه مقدار خانهي A15 را به مقدار 200 تغيير دهيم.
ــ عملگرها:
عالمت يا نشانههايي هستند كه نوع محاسبات را در يك عبارت مشخص ميكنند. عملگرها كه
ميتوانيد در فرمولها بهكار ببريد به چهار دسته تقسيم ميشوند: عملگرهاي محاسبات رياضي،
مقايسهاي، منطقي و آدرس خانهها.
ــ انواع تابعها:
تابعها، فرمول از پيش نوشته شدهاي است كه يك، هيچ يا چند مقدار را به عنوان ورودي
ميگيرد، عملياتي را انجام داده و يك، هيچ يا چند مقدار را بر ميگرداند. با استفاده از تابعها
فرمولها سادهتر ميشوند، بهخصوص كه تابعهاي از پيشآمادهي زيادي در اكسل وجود
دارند.
مثال 1-3
ميخواهيم مساحت يك دايره را با داشتن شعاع آن محاسبه كنيم.
مراحل انجام كار بهصورت زير است :
١ .خانهي A2 را براي ورود شعاع در نظر ميگيريم. يعني آدرس خانهي A2 نشاندهندهي شعاع
دايره است.
٢ .در خانه ً ي ديگر مثال A3 ،فرمول زير را تايپ كنيد:
) ( PI تابعي است كه مقدار عدد π يعني مقدار …14/3 را بر ميگرداند.
A2 آدرس خانه است كه محتواي آن شعاع دايره است.
2 ثابت استفاده شده در اين فرمول است.
عالمت توانرساني ^ و عالمت ضرب *، عملگرهاي محاسبات رياضي در اين فرمول
هستند.
فرمولنویسی و کار با تابعها
157
به عبارت ديگر، شعاع دايره به توان 2 رسيده و در عدد π ضرب ميشود كه همان فرمول
محاسباتي مساحت دايره است.
١-١-٣ عملگرهاي محاسبات رياضي
اين عملگرها بر روي مقادير عددي عمل ميكنند و به ترتيب تقدم در جدول 1-3 آورده
شدهاند.
جدول 1-3 عملگرهاي محاسبات رياضي
عملگر رياضي توضيحات مثال
% Percent = درصد 20%
^ Exponentiation = توانرساني 2^3
* Multiplication = ضرب 3*3
3 / /Division = تقسيم 3
+ Addition = جمع 3+3
–
Subtraction = تفريق
Negation = منفيساز
1 -1-3
در فرمولهاي پيچيدهتر ميتوانيد براي تقدم عملگرها از پرانتز استفاده كنيد. در پردازش
فرمولها، ابتدا عبارتهاي داخل پرانتز بهترتيب تقدم محاسبه ميشوند. عملگرهايي كه داراي
اولويت يكسان هستند از چپ به راست محاسبه ميشوند. به عنوان مثال، فرمول %20 * B3 =
بيست درصد از محتويات خانهي B3 را محاسبه ميكند.
مثال 2-3
ميخواهيم ترتيب مراحل انجام محاسبهي فرمول )2 +A5 – A2 + (2 ^ B4 / 4 * B3 = را مشخص
كنيم.
مراحل انجام كار بهصورت زير است:
١ .ابتدا مقدار داخل پرانتز را محاسبه كنيد. مقدار A5 را از A2 كم كرده و سپس با 2 جمع ميكند.
فرمولها و فرمولنویسی
158
2 .مقدار B4 را به توان 2 ميرساند.
3 .مقدار B3 را در عدد 4 ضرب ميكند.
4 .حاصلضرب را تقسيم بر حاصل توان ميكند.
5 .حاصل تقسيم را با حاصل پرانتز جمع ميكند.
مثال 3-3
فرمولي بنويسيم.
+
9 2
100 5
2
ميخواهيم در اكسل براي محاسبهي عبارت
مراحل انجام كار به صورت زير است:
١ .ابتدا خانهي A1 را براي نوشتن فرمول انتخاب ميكنيم.
2 .نويسهی = را تايپ ميكنيم. قبل از تايپ فرمول، دقت كنيد كه ترتيب و اولويت اجرا بايد
بهوسيله پرانتز مشخص شود. براي محاسبهي توان اعداد، ميتوانيم از توان كسري استفاده كنيم.
براي محاسبهي توان دوم ميتوان از تابع SQRT نيز استفاده كرد. ما از توان كسري استفاده
1 .
2
ميكنيم. 2 يعني 2 به توان
3 .فرمول را به صورت شكل 1-3 تايپ ميكنيم. اگر پاسخ را با يك ماشين حساب بهدست آوريم،
جواب 34/3 است، ولي فرمول رديف1 عدد 46/6 را بر ميگرداند كه مقدار مورد نظر نيست.
پس اولويت اجرا بايد با استفاده از پرانتزها مشخص شود. در شكل1-3 ،فرمولها و پاسخهاي
حالتهاي مختلف استفاده از پرانتز آورده شده است. فقط رديفهاي 6 ،7 و 8 پاسخ صحيح
را توليد كردهاند.
شكل 1-3 فرمولهاي محاسبهي عبارت مثال
فرمولنویسی و کار با تابعها
159
نكته
بهتر است براي خوانايي دستورات محاسباتي، از پرانتزها استفاده شود، حتي اگر
بدون پرانتز نيز، نتيجهي مورد نظر شما از محاسبات حاصل شده باشد.
دقت كنيد كه اگر نويسهي نقطه اعشار را از منوي آفيس، Options Excel و زبانهی
Advanced از عالمت . به عالمت / تبديل كرده باشيد، براي نوشتن تقسيم بايد
جملهي صورت را در پرانتز و جملهي مخرج را نيز در پرانتز قرار دهيد. در غير
اينصورت عبارت تقسيم، عدد اعشاري فرض خواهد شد.
٢-١-٣ عملگرهاي رشتهاي
تنها عملگر رشتهاي، عملگر & است )جدول 2-٣ .)از اين عملگر براي تركيب رشتهها استفاده
ميشود. اگر بخواهيد در فرمول از مقدارهاي رشتهاي استفاده كنيد بايد آنها را بين دو عالمت
گيومه )” “( قرار دهيد. بهعنوان مثال اگر در خانهي B1 كلمهي Microsoft وارد شده باشد، حاصل
فرمول تايپ شده در C1( شكل 2-3 ،)در شكل 3-3 قابل مشاهده است.
جدول 2-3 عملگرهاي رشتهاي
عملگر توضيحات
تركيب چند رشتهی متني و تبديل آن به يك رشته است. 1&
توابع بسياري در اكسل وجود دارند كه ميتوانند بهجاي برخي عملگرها استفاده شوند. تنها
عملگر رشتهاي & است، درحاليكه تابعهاي متعددي هستند كه بر روي دادههاي رشتهاي )متني(
كار ميكنند. اين تابعها را در قسمت آشنايي با برخي تابعها خواهيم ديد.
شكل 2-3 فرمول داراي عملگر
شكل 3-3 نتيجهي استفاده از عملگر & در فرمول
فرمولها و فرمولنویسی
Ampersand. 1
160
مثال 4-3
دو رشتهي »هفته« و »اول« را با هم تركيب كنيد بهطوريكه نتيجه »هفته اول« شود. همينطور
رشتهي التين Week First را از تركيب دو رشتهی Week و First ايجاد كنيد.
مراحل انجام كار به صورت زير است:
١ .فرمولهاي شكل 4-3 را تايپ كنيد. دقت كنيد كه قسمتهايي كه با عالمت ــ مشخص شده
است، نشاندهندهي نويسهي فضاي خالي است كه با دكمهي Space صفحهكليد ايجاد ميشود.
شكل 4-3 فرمول تركيب رشته
٢ .نتيجهي فرمولهاي نوشته شده براي تركيب رشتهی فارسي و التين را در شكل 5-3 مشاهده
ميكنيد. نتيجهي فرمول خانهي A1 مقدار »هفته اول« است كه مورد نظر ما نبوده است. تفاوت
فرمولهاي رديف اول با فرمولهاي رديف دوم و سوم، استفاده از نويسهي فضاي خالي است.
در انتهاي رشتهي اول در فرمول رديف دوم و يا در ابتداي رشتهی دوم در فرمول رديف سوم،
نويسهي فضاي خالي را با فشردن دكمهي Space در متن درج كردهايم.
شكل 5-3 نتيجهي فرمول تركيب رشته
٣ .دقت كنيد كه در تركيب دو رشتهي انگليسي بدون نويسهي فضاي خالي، حروف بههم
چسبيده نميشوند ولي همانطور كه مشاهده ميكنيد، نتيجهي فرمول رديف اول با فرمولهاي
رديف دوم و سوم متفاوت است.
فرمولنویسی و کار با تابعها
161
نكته
دقت شود كه در حالت متن فارسي، فرمول تركيب رشته از راست به چپ نمايش
داده عمل ميشود.
٣-١-٣ عملگرهاي مقايسهاي
اين عملگرها امكان مقايسهي دو مقدار را فراهم ميكنند و حاصل آنها درست/ Trueيا غلط/
False است. اولويت اجراي اين عملگرها، پس از عملگرهاي محاسبات رياضي و عملگر رشتهاي
است. عملگرهاي مقايسهاي در جدول 3-3 نشان داده شده است.
جدول 3-3 عملگرهاي مقايسهاي
عملگر توضيحات مثال
= مساوي با B1 = A1
< بزرگتر از B1 > A1
> كوچكتر از B1 < A1 =< بزرگتر از يا مساوي با B1 >= A1
=> كوچكتر از يا مساوي با B1 <= A1 <> نامساوي با B1 <> A1
٤-١-٣ عملگرهاي آدرس خانهها
براي تعيين محدودهاي از خانهها، از عملگر آدرس استفاده ميشود. اولويت عملگرهاي آدرس از
تمام عملگرهاي ديگر باالتر است. عملگر آدرس تركيبي از سه عملگر جدول 4-3 است.
سه حالت آدرسدهي محدوده در اكسل به شرح زير است:
آدرسدهي محدودهاي از خانهها: به عنوان مثال عبارت E7:C3 به خانههاي بين و خود
خانههاي C3 و E7 اشاره ميكند.
آدرس دهي ستوني: در آدرسدهي ستوني، نام جزء اول آدرس ثابت است و شمارهي پس
از نام ستون تغيير ميكند؛ مانند F10:F1.
آدرسدهي سطري: در آدرسدهي سطري، نام جزء اول متفاوت است و شمارهي پس از
نام ستونها ثابت است؛ مانند F4:A4.
فرمولها و فرمولنویسی
162
جدول 4-3 عملگرهاي آدرس
عملگر توضيحات مثال
عملگر ناحيه است كه يك آدرس به تمام خانههاي بين دو :
آدرس اشاره ميكند و شامل دو آدرس نيز ميباشد.
B15:B5
عملگر جمع مجموعهها/ تركيب دو ناحيه است كه به تمام ,
خانههاي هر دو ناحيه اشاره ميكند.
(D15:D5,B15:B5(SUM
عملگر اشتراك مجموعهها است كه به تمام خانههاي )فضاي خالي(
مشترك دو ناحيه اشاره ميكند.
C8:C6 D7:B7
نكته
برنامهي اكسل بهطور پيشفرض در خانههايي كه داراي فرمول هستند، نتيجه را
نمايش ميدهد و متن فرمول با انتخاب خانه در نوار فرمول قابل مشاهده است. براي
مشاهدهي فرمول در خانههاي كاربرگ بهجاي نتيجهي محاسبه، كليد ميانبر `+Ctrl
را فشار دهيد. با مجدد `+Ctrl نتيجه نمايش داده خواهد شد.
٥-١-٣ اولويت عملگرها
همانطور كه در مثال 3-3 ديديم، اجراي عملگرها به ترتيب و بر حسب اولويت آنها اجرا
ميشود و در نتيجهي محاسبات تأثير دارد. ترتيب اجرا، به اولويت عملگرها بستگي دارد. اگر دو
عملگر داراي اولويت يكسان باشند، به ترتيب از چپ به راست اجرا ميشوند )جدول ٥-٣.)
جدول 5-3 اولويت اجراي عملگرها
عملگر توضيحات عملگر توضيحات
:
عملگرهاي آدرس / و * ضرب و تقسيم , )فضاي خالي(
– منفيساز – و + جمع و تفريق
% درصد & تركيب دو رشتهی متني
^ توانرساني
=
< >
=>
=< <>
عملگرهاي مقايسهاي
فرمولنویسی و کار با تابعها
163
دقت كنيد كه استفاده از پرانتزها، اولويت اجرا را تغيير ميدهد و داخليترين پرانتز اول
اجرا ميشود.
مثال 5-3
نتيجهي دو عبارت 3*5+2 =و 3(*5+2 =)را با توجه به جدول 5-3 بررسي كنيد.
مراحل انجام كار به صورت زير است:
1 .در فرمول بدون استفاده از پرانتز، ابتدا ضرب بين 5 و 3 اجرا ميشود كه نتيجهي آن 15 است،
سپس نتيجهي آن با عدد 2 جمع ميشود و بنابراين نتيجهي نهايي 17 است.
2 .در فرمول با استفاده از پرانتز، ابتدا عبارت داخل پرانتز اجرا ميشود. داخل پرانتز فقط عملگر
جمع است و نتيجهي جمع دو عدد 2 و 5 ،عدد 7 خواهد شد، سپس نتيجهي آن در عدد 3
ضرب مي شود و بنابراين جواب نهايي 21 است.
كنجكاوي
آيا پنهان كردن خانهها،بر فرمولي كه از دادههاي آن خانهها استفاده ميكنند تغييري
ايجاد ميكند؟
2-3 آدرس دهي در فرمول
آدرس، يك خانه يا محدودهاي از خانههاي كاربرگ را مشخص ميكند. آدرس ميگويد داده و
مقدار كدام خانهها در فرمول استفاده شوند. با استفاده از آدرسدهي ميتوانيد از دادههاي بخشي
از كاربرگ در يك فرمول استفاده كنيد و يا از مقدار يك خانه، در چندين فرمول استفاده كنيد.
ميتوانيد از خانههاي كاربرگهاي ديگر فايل اكسل و يا از خانههاي كاربرگهاي فايلهاي اكسل
١ يا آدرسدهي خارجي٢ ناميده ميشود.
ديگر نيز استفاده كنيد. آدرسدهي به فايلهاي ديگر، پيوند
1-2-3 آدرسدهي سطر و ستون
استفاده از تركيب حروف براي نمايش ستون و عدد رديف براي نمايش سطر است. مثل اولين
خانه A1 .حروف از A شروع و تا XFD است يعني تعداد 384/16 ستون است. اعداد سطر نيز از 1
تا 576/048/1 است. مثالهايي از آدرسدهي سطر و ستون در جدول6-3 آورده شده است.
فرمولها و فرمولنویسی
References External. 2 Link. 1
164
جدول 6-3 مثالهايي از آدرسدهي خانهها و محدودهها
براي آدرسدهي به مثال
خانهي ستون A و سطر 10 A10
محدودهي خانههاي ستون A ،از سطر10ام تا سطر20ام A20:A10
محدودهي خانههاي سطر 15ام، از ستون B تا ستون E E15:B15
5:5 تمام خانههاي سطر 5ام
10:5 تمام خانههاي سطرهاي 5ام تا 10ام شامل تمام ستونها
تمام خانههاي ستون H H:H
تمام خانههاي ستونهاي H تا J شامل تمام سطرها J:H
محدودهي خانههاي ستونهاي A تا E و سطرهاي 10ام تا 20ام E20:A10
2-2-3 آدرسدهي به كاربرگهاي ديگر
همانطور كه در مثال زير ميبينيد، از كاربرگ Sheet2 ،محدودهي خانههاي B10:B1 آدرسدهي
شده است. عالمت جداساز بين نام كاربرگ و آدرس محدودهی خانههاي آن، عالمت تعجب
»!« است.
2 و تركيبي از آنها
1 ،مطلق
3-2-3 تفاوت آدرسدهي نسبي
در اكسل دو نوع آدرسدهي وجود دارد: نسبي، مطلق و میتوان ترکیبی از آنها را نیز استفاده کرد.
آدرسدهي نسبي :
به وضعيت نسبي خانهي آدرسدهي شده با خانهي داراي فرمول، وابسته است. در آدرسدهي
ً نسبي نام آدرس، حروف و اعداد بدون عالمت خاص است مثال A1 .اگر با استفاده از پر كردن
Absolute. 2 Relative. 1
فرمولنویسی و کار با تابعها
165
خودكار، فرمول را به خانههاي ديگر كپي كنيم، آدرسدهي فرمول نيز تغيير ميكند و آدرس
خانهها متناسب با خانهی انتقاليافته بهطور خودكار تنظيم مي ً شود. مثال اگر خانهيC1 كه داراي
فرمول B1 + A1 = است را به خانهي C2 كپي نماييم، فرمول به تناسب تغيير ميكند و فرمول
خانهي C2 ،B2+A2 = مي ً شود. يا مثال اگر خانهيC1 كه داراي فرمول B10 + A2 = است را به
خانهي C3 كپي نماييم، فرمول خانهي C3 ،B12+A4 = ميشود.
آدرسدهي مطلق :
هميشه به خانهی مشخص شده اشاره دارد مثل 1$A .$اگر خانه داراي فرمول كپي شود، آدرس
مطلق ثابت باقي مي ً ماند. مثال اگر خانهيC1 كه داراي فرمول 10$A =$است را به خانهي C2 كپي
نماييم، آدرسدهي مطلق ثابت ميماند و فرمول خانهي C2 نيز همان آدرس و 10$A =$ميشود.
آدرسدهي تركيبي :
داراي آدرسدهي مطلق و نسبي است. اگر خانه داراي فرمول جابهجا شود، آدرس مطلق ثابت
باقي ميماند و آدرسدهي نسبي به تناسب تغيير مي ً كند. مثال اگر خانهيC1 كه داراي فرمول
B1 + 1$A = $است را به خانهي C2 كپي نماييم، آدرسدهي مطلق فرمول ثابت ميماند ولي
آدرسدهي نسبي به تناسب تغيير ميكند. فرمول خانهي C2 ،B2+1$A = $ميشود.
نكته
در زمان استفاده از آدرس خانه در فرمول، با فشردن دكمهي F4 آدرسدهي از
مطلق به نسبی تغییر می کند.
تمرين 1-3
فرمول سادهاي بنويسيد كه به آدرس =10$A ، 10$A =$و A10 =$اشاره كند. اين
فرمولها را به ترتيب در خانههاي B2، B1 و B3 و همچنين در خانههاي H1، G1 و
I1 تايپ كنيد. سه رديف B1 تا B3 را انتخاب و دو ستون به سمت راست كپي كنيد.
سه ستون G1 تا I1 را انتخاب و دو رديف پايين كپي كنيد.
نتيجه در شكل 6-3 قابل مشاهده است.
آدرسدهی در فرمول
166
شكل 6-3 نتيجهي كپي فرمول با عالمت $
با فشردن دكمهي `+ Ctrl ،بهجاي نتيجهي فرمول، فرمولها نمايش داده ميشود )شكل 7-3.)
بررسي كنيد، در هنگام كپي فرمول خانههاي B1 تا B3 در ستونهاي D، C و E ،براي هر
يك از سه حالت آدرسدهي مطلق )$( چه اتفاقي افتاده است؟ در هنگام كپي فرمول خانههاي
G1 تا I1 در رديفهاي 2 ،3 و 4 ،براي هر يك از سه حالت آدرسدهي مطلق )$( چه اتفاقي
افتاده است؟
فرمولنویسی و کار با تابعها
مطالعهی آزاد
روش آدرسدهي سه بُعدي
اگر بخواهيم دادههاي يك خانه يا محدودهاي از خانههاي چند كاربرگ را استفاده
ُعدي استفاده مي ً كنيم. مثال براي جمع دادههاي خانهی B5
كنيم، از آدرسدهي سهب
در هفت كاربرگ، فرمول )B5!Sheet8:Sheet2(SUM =استفاده ميشود. و فرمول
(G10:B5!Sheet10:Sheet1 (SUM ،=دادههاي محدودهي G10:B5 در ده كاربرگ
را جمع ميكند.
شكل 7-3 نتيجهي كپي فرمول با عالمت $
167
٤-2-3 اسمگذاري و استفاده از names
اسامي، محدودهاي از خانهها، فرمولها، مقدارهاي ثابت يا جدولهاي اكسل2007 هستند.
ميتوانيم روي يك محدوده، نام بگذاريم و از اين نام در فرمولها استفاده كنيم. بدينترتيب
توضيح بيشتري از آن محدوده خواهيم داشت. همچنين با تعريف محدودهي جديد براي آن
نام، تغييرات به تمام فرمولهايي كه از آن استفاده ميكنند، اعمال خواهد شد. دو نوع تعريف نام
وجود دارد : name Defined و name Table.
name Defined : اين نوع اسمگذاري، يك خانه، محدودهاي از خانهها، فرمول يا مقدار
ثابت را نشان ميدهد.
name Table : مجموعه دادههاي در مورد يك موضوع خاص است كه در سطرها و
1
ستونها ذخيره شده است.
مثال 6-3
ميخواهيم يك اسم NamedData براي محدودهي C9:A2 ايجاد كنيم.
مراحل انجام كار به صورت زير است:
1 .ابتدا محدودهي مورد نظر C9:A2 را انتخاب ميكنيم.
2 .از نوار فرمول،كادر Box Name را كليك مي ً كنيم و نام مورد نظر مثال NamedData را تايپ
كرده و Enter ميكنيم )شكل 8-3.)
آدرسدهی در فرمول
1 .اگر با مفهوم بانكداده آشنا باشيد، سطرها، ركوردهاي جدول و ستونها، فيلدهاي جدول هستند.
شكل 8-3 كادر Box Name نوار فرمول
168
3 .روش ديگر آن است كه پس از انتخاب محدوده، كليك راست كرده و از منوي بازشده
گزينهي …Range a Name را انتخاب كنيم )شكل ٩-3.)
شكل ٩-3 منوي كليك راست براي اسمگذاري يك محدوده
شكل ١٠-3 پنجرهي ايجاد نام
فرمولنویسی و کار با تابعها
4 .در پنجرهي Name New( شكل ١٠-3 ،)اسم را در قسمت Name ،دامنهي مجاز استفاده را در
قسمت Scope ،در قسمت Comment توضيحات بيشتر و در قسمت to Refers آدرس محدوده
نمايش داده ميشود كه ميتوانيد آن را در همين جا تغيير دهيد.
تمرين 2-3
اسامي محدودههايي با عنوان StudentsNames_List و NamedRange3 را در هر
محدودهی خانهی دلخواه ايجاد كنيد.
با كليك عالمت فلش كوچك كنار كادر Box Name روي نوار فرمول، ليست اسامي
محدودههايي كه ايجاد كردهايد، نمايش داده ميشود )شكل 1١-3.)
169
شكل 1١-3 ليست اسامي محدودههاي ايجاد شده
آدرسدهی در فرمول
1
٣-٣ تابعها و استفاده از آنها
همان ً طور كه قبال گفته شد، تابعها يكي از اجزاي عبارت در فرمولها هستند. هر تابع دارای نام
میباشد و ممکن است ورودی نداشته باشد. هر تابع یک عملیات را انجام میدهد. به طور كلي
در اكسل تابع به دو صورت وجود دارد:
2
1 .تابعهاي دروني
3
2 .تابعهاي تعريف شده بهوسيلهي كاربر
مزيت اكسل آن است كه تابعهاي دروني متنوعي وجود دارد و كاربردهاي فراواني دارند
كه نياز به تعريف تابع بهوسيلهي كاربر را كم ميكند.
انواع تابعهاي اكسل در گروههايي دستهبندي شدهاند تا جستجو و كار با آنها را آسانتر
نمايد. اين گروهها عبارتاند از:
5
4 ٤ .تابعهای آماري
1 .تابعهای مالي
7
6 ٥ .تابعهای جستجو و مرجع
2 .تابعهای تاريخ و زمان
9
8 ٦ .تابعهای بانك اطالعاتي
3 .تابعهای رياضي و مثلثاتي
٧ .تابعهای رشتهاي١٠ 9 .تابعهای بررسي اطالعات١١
٨ .تابعهای منطقي١٢ 10 .تابعهای هوشتجاري١٣ و 11 .تابعهای مهندسي١٤
در بعضي موارد براي انجام يك عمل خاص، هم ميتوان از عملگر و هم از تابع استفاده كرد،
ولي استفاده از تابعها كار را سادهتر ميكند. فرض كنيد ميخواهيم خانههاي A1 تا A7 را با هم جمع
1 .برخي تابعها را ميتوان بهعنوان پارامتر ساير تابعها استفاده كرد و در اين صورت به تابع درون تابع ديگر )Functions Nested )گويند.
Financial. 4 Defined User. 3 in Built. 2
Reference & Lookup. 7 Time & Date. 6 Statistical. 5
Text. 10 Database. 9 Trig & Math. 8
Engineering. 14 Cube. 13 Logical. 12 Information. 11
170
كنيم. با استفاده از عملگر + ميتوانيم در خانهي A8 جمع را به صورت زير بهدست آوريم:
A7 + A6 + A5 + A4 + A3 + A2 + A1=
بديهي است كه تايپ آدرس خانههاي متعدد، خستهكننده و دشوار است. محاسبهي فوق
را ميتوانيم با استفاده از تابع SUM بهصورت )A7:A1 (SUM =بهدست آوريم.
نكته
براي جمع خانههاي A1 تا A7 ميتوان ابتدا آنها را انتخاب كرد و سپس روي
در زبانهي Home در ريبون و گروه Editing كليك كرد.
1-3-3 اجزاي تابع
هر تابع داراي دو قسمت اصلي است: 1 .نام تابع ، 2.آرگومانهاي تابع
نام تابع كليد واژهاي است كه نوع عمليات را نشان ميدهد و در مواردي ميتواند مخفف
كلماتي باشد. آرگومان تابع نيز ورودي يا وروديهاي تابع است. آرگومانها با سميكولن );( از
1
هم جدا ميشوند.
انواع آرگومانهاي تابع به سه دسته تقسيم ميشوند:
1 .فاقد آرگومان.
2 .داراي تعدادي آرگومان مشخص.
3 .داراي حداكثر 255 آرگومان.
1-1-3-3 تابع فاقد آرگومان
ً پرانتز باز و بسته پس از نام تابع آورده شود. به
تابعي است كه هيچ ورودي ندارد. اما بايد حتما
/3 و )( Rand یک عدد تصادفی
عنوان مثال، ميتوان تابع )(PI و )( Rand را نام برد. ))(PI عدد 14
بین صفر و یک را تولید می کند.(
كنجكاوي
تابع ()Rand را در خانههاي B1 و B2 تايپ كنيد. آيا نتيجهها برابرند؟ چه نتيجهاي
ميگيريد؟
فرمولنویسی و کار با تابعها
1 .در برخي نسخههاي اكسل، آرگومان با ”,“ از هم جدا ميشوند.
171
2-1-3-3 تابع داراي آرگومان مشخص
اين نوع تابعها، تعدادي ثابت از ورودي ميگيرند و كم و زياد كردن تعداد وروديها ميتواند
ً داراي دو آرگومان است و باقيماندهي
سبب بروز خطا شود. به عنوان مثال، تابع )( MOD دقيقا
تقسيم عدد اول بر عدد دوم را به عنوان نتيجه توليد ميكند.
نتيجه مثال عملكرد نام تابع
باقيماندهي تقسيم n بر m را (2;7(MOD = 1
توليد ميكند.
(m;n(MOD
3-1-3-3 تابع داراي حداكثر 255 آرگومان
تابعي مانند SUM ميتواند حداكثر 255 ورودي داشته باشد. به عنوان مثال، براي جمع مقدار
خانههاي A1 تا A7 ميتوان حالتهاي زير را در نظر گرفت:
تابع دو آرگومان دارد. (A7:A4;A3:A1 (SUM) الف
تابع يك آرگومان دارد. (A7:A1 (SUM) ب
تابع سه آرگومان دارد. (A7:A6; A5:A3; A2:A1 (SUM) ج
نكته
آرگومان ميتواند داراي يك خانه يا محدودهاي از خانهها باشد؛ بنابراين 255
آرگومان به معني 255 ً خانه نيست؛ مثال
(A10 ; A8:A1 (sum=
تمرين 3-3
تابع ROUND را بررسي كنيد.
راهنمايي: فرمولهاي خانههاي A2 تا A6( شكل 1٢-3 )را در خانههاي B2 تا B6
تايپ كرده و پاسخ را بررسي كنيد.
نوعدادهي خانههاي B2 تا B6 از نوع عددي با دو رقم اعشار انتخاب شده است.
تابعها و استفاده از آنها
آرگومان دوم آرگومان اول
172
شكل 12-3 تمرين آرگومانهاي مختلف تابع ROUND
شكل 1٣-3 گزينهي Function Insert از زبانهي Formulas ريبون
فرمولنویسی و کار با تابعها
نكته
براي نمايش عبارت فرمول خانههاي A2 تا A6 در شكل 12-3 نوعدادهي Text براي
آن خانهها انتخاب شده است، بنابراين با تايپ فرمولها كه با = شروع شده است،
محاسبهي فرمول انجام نميشود و خود عبارت فرمولها نمايش داده شدهاند.
٢-٣-٣ روش استفاده از تابعها
تابعها را ميتوان به دو روش استفاده كرد: 1 .تايپ تابع ، 2 .استفاده از Function Insert.
١ .تايپ تابع :
تايپ تابع مستلزم دانستن امالي تابع و تعداد آرگومانهاي موردنياز تابع است. هرچند اكسل2007
براي آسان كردن تايپ تابع، قابليت Complete Auto را دارد كه در ادامه خواهيم ديد.
٢ .استفاده از Function Insert:
از پنجرهي Function Insert از زبانهي Formulas در دسترس است )شكل 1٣-3 .)پس از
انتخاب گزينه، پنجرهي واردكردن تابع نمايش داده ميشود )شكل 1٤-3.)
173
با انتخاب هر كدام از تابعها از قسمت function a Select اين پنجره، توضيحي در مورد
تابع و هر يك از آرگومانهاي آن، و همچنين نتيجهي خروجي تابع در قسمت زيرين نشان داده
ميشود. همانطور كه در پنجره مالحظه ميكنيد برنامهي اكسل با توجه به نوع كاربرد تابعها، آنها
را دسته ً بندي كرده است كه قبال آنها را ديدهايم و در قسمت category a select or اين پنجره قابل
مشاهده است. با انتخاب هر دسته، زيرمجموعهي مرتبط در ليست انتخاب تابع قابل دسترس است.
تحقيق
تابعهاي هر دسته را بررسي كنيد.
براي آسانتر كردن ايجاد و تغيير فرمولها و كاهش تايپ كردن كامل و همچنين
پيشگيري از اشتباهات تايپي و كاهش خطاهاي قواعد زباني، بهتر است گزينهي AutoComplete
را فعال كنيم.
تابعها و استفاده از آنها
شكل 1٤-3 پنجرهي Function Insert
174
مثال 7-3
ميخواهيم AutoComplete را فعال و عملكرد آن را بررسي كنيم.
مراحل انجام كار به صورت زير است:
١ .براي اين منظور از منوي آفيس گزينهي Options Excel را انتخاب كرده، در زبانهي �Ad
vanced ،قسمت options Editing ،گزينهي values cell for AutoComplete Enable را فعال
يا غيرفعال كنيد. با فعال كردن اين گزينه، پس از تايپ عالمت مساوي و تايپ اولين حرف،
در زير خانه ليستي باز ميشود )شكل 1٥-3 )كه تابعهايي كه با آن حرف شروع ميشوند را
دربر دارد.
شكل 15-3 ليست AutoComplete محدود به تابعهايي كه با حرف r شروع ميشوند.
شكل 1٦-3 ليست محدود به تابعهايي كه باحرف ro شروع ميشوند.
فرمولنویسی و کار با تابعها
٢ .با تايپ نويسههاي بيشتر، ليست محدودتر ميشود )شكلهاي 1٦-3 و 1٧-3.)
175
٣ .به محض حركت روي ليست با كليدهاي جهتدار باال و پايين صفحهكليد، توضيحي در مورد
آن تابع نيز در كنار ليست نمايش داده ميشود )شكل 1٨-3.)
٤ .پس از تايپ كامل تابع، ابزار توضيح تابع )شكل 1٩-3 )كه راهنماي كاملتر و دقيقتري از
تابع است، نشان داده ميشود. با كمك ابزار توضيح، تعداد و آرگومانهاي تابع را ميتوان
تشخيص داد.
شكل 1٧-3 ليست محدود به تابعهايي كه باحرف rou شروع ميشوند و نمايش توضيحي از تابع اول
تابعها و استفاده از آنها
شكل 1٨-3 ليست AutoComplete و توضيحي از تابع ROUNDUP
٣-٣-٣ آشنايي با برخي انواع تابعها
در ادامه با تعدادي از تابعها آشنا خواهيم شد. همان ً طور كه قبال گفته شد، آرگومانها ميتوانند
آدرس خانهها باشند، ولي در مثالهاي هر تابع از ثابتها استفاده شده است.
١-٣-٣-٣ تابع SUMIF:
قالب: )SumRange; Criteria; Range Check (SUMIF
تعداد آرگومان: 3
عملكرد: اگر بخواهيم مجموع خانههايي را در صورت تحقق شرطي بهدست
شكل 1٩-3 ابزار توضيح تابع ــ تابع ROUND
176
آوريم، از اين تابع استفاده ميكنيم. آرگومان اول آدرس محدودهاي است كه
محتواي آن مورد بررسي واقع ميشود. آرگومان دوم، شرط است و آرگومان سوم،
آدرس محدودهاي است كه مقادير از آن انتخاب و در صورت احراز شرط، جمع
ميشوند.
تمرين 4-3
با توجه به مقادير دادهشده در شكل ٢٠-3 ،جمع كاركرد آقاي علوي چقدر است؟
شكل ٢٠-3
فرمولنویسی و کار با تابعها
٢-٣-٣-٣ تابع MIN:
قالب: )… ;Number2; Number1 (MIN
تعداد آرگومان: حداكثر 255
عملكرد: كوچكترين مقدار آرگومانهاي دادهشده را بر ميگرداند.
مثال 8-3
نتيجه تابع
12) 13 ; 12 (MIN
177
نكته
تابع MAX بزرگترين مقدار آرگومانهاي دادهشده را بر ميگرداند.
٣-٣-٣-٣ تابع LOG:
قالب: )base; number (LOG
تعداد آرگومان: 2
عملكرد: لگاريتم عدد مورد نظر را در مبناي مشخصشده بر ميگرداند.
نكته
اگر مبنا ذكر نشود، مبناي 10 در نظر گرفته ميشود.
مثال 9-3
نتيجه تابع
1) 10 (LOG
4) 2 ; 16 ( LOG
٤-٣-٣-٣ تابع ISNUMBER:
قالب: )value (ISNUMBER
تعداد آرگومان: 1
عملكرد: اگر مقدار ورودي، عدد باشد TRUE بر ميگرداند.
مثال 10-3
نتيجه تابع
FALSE) a (ISNUMBER
TRUE) 1384(ISNUMBER
كنجكاوي
عملكرد تابع ISTEXT ر بررسي كنيد.
تابعها و استفاده از آنها
178
٥-٣-٣-٣ تابع CONCATENATE:
قالب: )… ; 2 text ; text1 (CONCATENATE
تعداد آرگومان: حداكثر 255
عملكرد: وروديها را با هم تركيب ميكند. اين تابع مشابه عملگر & است.
مثال 11-3
نتيجه تابع
ALIREZA”) REZA” ; “ALI ( “CONCATENATE
٦-٣-٣-٣ تابع AVERAGE:
قالب: )… ; number2 ; number1 (AVERAGE
تعداد آرگومان: حداكثر 255
عملكرد: ميانگين حسابي آرگومانها را محاسبه ميكند.
مثال 12-3
نتيجه تابع
12) 14; 12; 10 ( AVERAGE
٧-٣-٣-٣ تابع ABS:
قالب: )number (ABS
تعداد آرگومان: 1
عملكرد: قدر مطلق ورودي را بر ميگرداند.
مثال 13-3
نتيجه تابع
0) 0 (ABS
1) 1 (-ABS
7) 7 (ABS
فرمولنویسی و کار با تابعها
179
٨-٣-٣-٣ تابع SIN:
قالب: )number (SIN
تعداد آرگومان: 1
عملكرد: سينوس زاويهي تعيينشده را محاسبه ميكند.
مثال 14-3
نتيجه تابع
1) 2()/PI ( SIN
5.0) 180() / PI * 30 (SIN
نكته
آرگومان بر حسب راديان در نظر گرفته ميشود. اگر ورودي بر حسب درجه باشد،
ميتوانيم براي محاسبهي سينوس آن، ابتدا آن را در 180() / PI ضرب كنيم.
٩-٣-٣-٣ تابع INT:
قالب: )number (INT
تعداد آرگومان: 1
عملكرد: نزديكترين عدد صحيح كوچكتر يا مساوري ورودي را بر ميگرداند
)تابع جزء صحيح(.
مثال 15-3
نتيجه تابع
8) 5.8 (INT
9) -5.8 (-INT
١٠-٣-٣-٣ تابع COUNT:
قالب: )… ; value2 ; value1 (COUNT
تابعها و استفاده از آنها
180
تعداد آرگومان: حداكثر 255
عملكرد: تعداد »اعداد« موجود در آرگومانها را حساب ميكند.
١١-٣-٣-٣ تابع COUNTA:
قالب: )… ; value2 ; value1 (COUNTA
تعداد آرگومان: حداكثر 255
عملكرد: تعداد خانههاي حاوي مقدار در آرگومانها را حساب ميكند.
١٢-٣-٣-٣ تابع COUNTBLANK:
قالب: )range (COUNTBLANK
تعداد آرگومان: 1
عملكرد: تعداد خانههاي خالي يك محدوده را حساب ميكند.
١٣-٣-٣-٣ تابع COUNTIF:
قالب: )criteria ; range (COUNTIF
تعداد آرگومان: 2
عملكرد: تعداد خانههايي كه شرط مورد نظر را دارند محاسبه ميكند. براي درك
بهتر تابعهاي COUNT به مثال زير توجه كنيد.
مثال 16-3
نمرات و مشخصات سه هنرجو در كاربرگ )شكل 2١-3 )آمده است:
الف( چند هنرجو شمارهي هنرجويي ندارند؟
ب( چند نمرهي مربوط به هنرجوي اول، پر شده است؟
ج( چند نمرهي هنرجوي دوم، باالتر از 14 است؟
د( چند خانهي بين A1 تا F4 شامل مقدار است؟
شكل 2١-3
فرمولنویسی و کار با تابعها
181
پاسخ پرسشهاي باال به ترتيب برابر است با )شکل ٢٢-٣:)
تابعها و استفاده از آنها
تمرين ٥-3
در مثال 16-3 ،اگر بخواهيم تعداد خانههايي را كه عدد دارند محاسبه كنيم، بايد
چه فرمولي بنويسيم؟
كنجكاوي
براي قسمت »ب« مثال 16-3 ،فرمول ديگري ارايه كنيد.
مطالعهی آزاد
تابع MMULT:
قالب: )array2 ; array1 (MMULT
تعداد آرگومان: 2
عملكرد: حاصلضرب دو ماتريس را محاسبه ميكند.
مثال
حاصل ضرب ماتريسهاي
B
    =  
8 5
A و 6 1
    =  
5 2
4 7
را به دست آوريد.
شكل 2٢-3
182
مراحل انجام اين كار به صورت زير است:
١ .ابتدا، اعداد باال را در خانههاي كاربرگ وارد كنيد. ماتريس A از خانههاي A1
تا B2 و ماتريس B از خانههاي C1 تا D2.
٢ .در خانهي A4 فرمول
)D2:C1 ; B2:A1 ( MMULT=
را وارد كنيد.
٣ .پس از تأييد فرمول )فشار دادن Enter )عدد 15 ظاهر ميشود كه درايهي اول
ماتريس جواب است.
٤ .براي مشاهدهي ساير درايهها، محدودهي ماتريس جواب را انتخاب كنيد ) يعني
خانههاي A4 تا B5 ،زيرا حاصل ضرب دو ماتريس 2×2 ،يك ماتريس 2×2 ديگر
است(. سپس در خط فرمول كليك كنيد )در خانهي A4 بهجاي 15 ،فرمول
ظاهر ميشود( و پس از آن كليدهاي Ctrl ، Shift و Enter را با هم بزنيد. جواب
ماتريس يعني
در خانههاي A4 تا B5 ظاهر ميشود.
نكته
در اكسل، نتيجهي تابعها دو حالت دارد:
١ .تنها يك خانه، پاسخ تابع است، مانند: SUM ،INT و … كه پس از تايپ تابع نتيجه
با فشار دادن Enter قابل مشاهده است.
٢ .تعدادي خانه، پاسخ تابع است. مانند MMULT.
در حالتي كه پاسخ تابع بيش از يك خانه است مراحل نظير تابع MMULT
صورت ميگيرد.
فرمولنویسی و کار با تابعها
آدرس ماتريس دوم آدرس ماتريس اول
 
   
46 15
80 39
183
تابع MINVERSE:
قالب: )array (MINVERSE
تعداد آرگومان: 1
عملكرد: معكوس ماتريس ورودي را بر ميگرداند.
مثال
معكوس ماتريس A در مثال 17-3 را بهدست آوريد.
مراحل انجام اين كار به صورت زير است:
١ .ابتدا، در خانههاي A7 كليك كرده، فرمول )B2:A1 (MINVERSE = را تايپ
ميكنيم و Enter را ميزنيم.
٢ .محدودهي ماتريس جواب يعني خانههاي A7 تا B8 را انتخاب ميكنيم.
٣ .در خط فرمول كليك ميكنيم و Enter + Shift + Ctrl را فشار ميدهيم.
٤ .پاسخ عبارت است از
تابع TRANSPOSE:
قالب: )array (TRANSPOSE
تعداد آرگومان: 1
عملكرد: ترانهادهي ماتريس ورودي را برمي‌ گرداند )جاي سطرها و ستونها عوض
ميشود(.
تمرين
ترانهادهي ماتريسهاي A و B را با توجه به چند جوابي بودن تابع بهدست آوريد.
تمرين
تابع MDETERM دترمينان ماتريس ورودي را محاسبه ميكند. دترمينان ماتريس
را بهدست آوريد.
 
   
7 4
5 6
تابعها و استفاده از آنها
/ /
/ /
−  
   
185185 0 14815 0
07407 0 259259 0
184
مثال
دستگاه سه معادلهي سه مجهولي زير را حل كنيد :
شكل ماتريسي دستگاه به صورت زير است:
كافي است معكوس ماتريس
x
y
z
 
 
 
   
براي بهدست آوردن
=A
 
  −  
   
451
21 1
113
B ضرب كنيم. با استفاده از تابع MINVERSE معكوس ماتريس A
 
  =  
   
0
4
2
را در
عبارت است از :
با استفاده از MMULT ،1-A را در B ضرب ميكنيم. نتيجهي دستگاه عبارت است
از :
فرمولنویسی و کار با تابعها
yz x
yz x
z xy
 + += 
=+ − 

= ++ 
0 45
4 2
3 3
x
y
z
   
= −    
   
0 451
4 21 1
2 113
/ //
// / A
// /
−
−  
− =  
− −  
1
02 04 02
085714 0 31429 0 057143 0
371429 0 028571 0 08571 0
x
y
z
=
− =
=
1
1
1
185
١٤-٣-٣-٣ تابع LEN:
قالب: )text (LEN
تعداد آرگومان: 1
عملكرد: تعداد نويسههاي )كاراكترهاي( ورودي را بر ميگرداند.
مثال 1٧-3
نتيجه تابع
3”) ALI (“LEN
2) 18 (LEN
١٥-٣-٣-٣ تابع SQRT:
قالب: )number (SQRT
تعداد آرگومان: 1
عملكرد: ريشهي دوم عدد ورودي را محاسبه ميكند.
مثال 18-3
نتيجه تابع
4) 16 (SQRT
162.3) 10 (SQRT
نكته
براي محاسبهي ريشه ميتوانيم از عملگر توان )^( نيز استفاده كنيم، بهعنوان مثال،
فرمول 5.0 ^ 16 = نيز ريشهي دوم عدد 16 را محاسبه ميكند.
١6-٣-٣-٣ تابع ROWS:
قالب: )array (ROWS
تعداد آرگومان: 1
عملكرد: تعداد سطرهاي آرايههاي موجود در آرگومان را بر ميگرداند.
تابعها و استفاده از آنها
186
مثال 19-3
نتيجه تابع
10) F10:A1 (ROWS
تمرين ٦-3
تابع COLUMNS را بررسي كنيد.
17-٣-٣-٣ تابع IF:
قالب: )false-if-value ; true-if-value ; test-logical (IF
تعداد آرگومان: 3
عملكرد: شرطي را بررسي ميكند. در صورت درست بودن، آرگومان دوم تابع
اجرا ميشود و در غير اين صورت آرگومان سوم تابع در نظر گرفته ميشود )شكل
2٣-3.)
فرمولنویسی و کار با تابعها
شكل 2٣-3
true-if-value false-if-value
Y N test logical
تمرين ٧-3
در خانهي A2 فرمول
(“12 equal Not” ; “12 Equal ; “12 = A1 ( IF=
را تايپ كنيد. حال در خانهي A1 مقادير 14 ،12 و 8 را تايپ كنيد و نتايج را
تحليل كنيد.
187
٤-٣-٣ تركيب تابعها )تابعهاي درون تابعي ديگر(
گاهي الزم است كه از نتيجهي ساير تابعها، در يك تابع به عنوان ورودي استفاده كنيم. به اين
ً تابعهاي مورد نياز جداگانه انجام شوند، بلكه ميتوانيم از آنها به
ترتيب، الزم نيست كه حتما
عنوان آرگومان در تابع استفاده كنيم.
فرمول (16 ; 18) ; 18;4(SUM ( MAX =را در نظر بگيريد. تابع MAX داراي سه آرگومان
است كه آرگومان اول، خود يك تابع است. ابتدا تابع SUM عمل كرده، نتيجهي 22 بهعنوان
اولين آرگومان MAX منظور ميشود. پس از آن تابع MAX بين اعداد 22 ،18 و 16 ،عدد 22 را
ــ كه بزرگترين مقدار است ــ به عنوان خروجي توليد ميكند.
در حاالت خاص، آرگومانهاي يك تابع ميتوانند نتيجه ً ي يك تابع ديگر باشند. مثال در
فرمول زير، تابع AVERAGE درون تابع IF استفاده شده است كه محاسبهي تابع SUM درون تابع
IF را كنترل ميكند.
نكته
در مثال (0);G5:G2(SUM;50)>F5:F2(AVERAGE(IF ،دو تابع AVERAGE و
SUM هر دو در يك سطح ــ سطح دوم ــ قرار دارند، يعني هر دو آرگومان يك
تابع )IF )هستند. اگر تابعي بهجاي آرگومان تابعAVERAGE يا SUM قرار داده
شود، سطح سوم خواهد شد.
نكته
نتيجهي تابع استفاده شده بهجاي آرگومان يك تابع، بايد نوع دادهاي متناظر داشته
باشد. در غير اينصورت خطاي !VALUE #رخ ميدهد.
تمرين ٨-3
استفاده از تابعB بهجاي آرگومان تابعA و استفاده از تابعC بهجاي آرگومان تابعB
و بههمين ترتيب، حداكثر تا چند سطح مجاز است؟
راهنمايي: از عبارت جستجو Limits Level Nesting در پنجرهي راهنما استفاده كنيد.
تابعها و استفاده از آنها
188
٥-٣-٣ استفاده از تابعها در حل مسایل
با تركيب تابعها و استفاده از فرمولهاي مناسب، ميتوان مسايل مختلف را در اكسل حل كرد. در
زير به عنوان مثال، به دو مورد اشاره شده است.
مثال 20-3
ميخواهيم از بين 49 هنرجو با شمارههاي صحيح 1 تا 49 ،يك نفر را به تصادف انتخاب كنيم.
با استفاده از تابعها، فرمولي مناسب ارايه دهيد.
با استفاده از تابعهاي )( RAND و)( INT پاسخ عبارت است از :
(()RAND * 49+ 1 (INT=
زيرا
1() < RAND ≤ 0 49() < RAND * 49 ≤ 0 50() < RAND * 49 ≤ 1 تابع INT نيز سبب ميشود اعداد صحيح بين 1 تا 49 انتخاب شوند. نكته براي توليد عدد تصادفي صحيح بين a تا b از رابطهي زير استفاده ميشود: ( b < a() ) ( RAND) * 1 + a – b + (a ( INT ٤-٣ يافتن و رفع خطاهاي فرمول نوشته شده زماني كه فرمولها پيچيده باشند، با يك اشتباه كوچك بهجاي نمايش نتيجهي فرمول با پيغام خطا مواجه خواهيم شد. برنامهي اكسل براي محاسبهي فرمول، ابتدا آن را بررسي ميكند و در صورتي كه اشكالي در آن باشد و نتواند نتيجهي فرمول را محاسبه و نمايش دهد، با توجه به نوع خطا، يكي از عاليم جدول ٧-٣ را نشان ميدهد كه ميتوانيد با توجه به توضيحات جدول و علت مربوطه، آن خطا را رفع كنيد. فرمولنویسی و کار با تابعها 189 جدول 7-3 جدول ليست خطاها پيام خطا علت روش رفع خطا ###### عرض ستون از طول عدد يا تاريخي كه در يك خانه درج ميشود، كوچكتر است. پهناي ستون مربوط به آن خانه را افزايش دهيد. اگر نتيجهی فرمول از نوع تاريخ و يا زمان، مقدار منفي شود. خطاي تقسيم يك عدد بر صفر رخ داده است. !0/DIV# يا اگر يك عدد بر محتواي خالي يك خانه تقسيم شده باشد. آدرسها و مقدارهايي را كه در مخرج قرار دارند بررسي كنيد. ?NAME# اشاره به محدودهي نامگذاري شده باشد كه ايجاد نشده است. نام تابع يا آدرس مورد نظر را يافته و تصحيح كنيد. ً استفاده از تابعي كه وجود ندارد، مثال عنوان آن صحيح تايپ نشده باشد. استفاده از نوع برچسبها، زمانيكه استفاده از آنها مجاز نباشد. استفاده از رشتهی متني كه درون عالمت » بسته نشده باشد. استفاده از محدودهي غير مجاز آدرس. اشاره به كاربرگ ديگري كه وجود ندارد. !REF# اشاره به خانهاي كه محتواي آن پاك شده باشد. آدرسها و محتواي خانههاي آنها را بررسي كنيد. اشاره به خانهاي كه ممكن است حذف شده باشد. استفاده از پيوند كه موجود نباشد )Exchange Data Dynamic Link) !VALUE# نوع اطالعات خانهاي كه در فرمول به آن ارجاع شده است، ً اشتباه است. مثال يك تابع، يك مقدار عددي را به عنوان ورودي ميگيرد ولي رشتهی متني به آن ارسال شده باشد. آدرسي كه در فرمول به آن ارجاع شده است يا محتواي آن را تغيير دهيد. A/N# استفاده از تابعهاي VLOOKUP ،HLOOKUP ،MATCH وقتي كه نتواند مقايسه را انجام دهد )يا ليست ورودي آنها مرتب نشده باشد(.* تصحيح تابع استفاده از تابع نوشته شده بهوسيلهي كاربر كه مجاز به استفاده از آن در كاربرگ نباشيم. استفاده از تابع بدون آن كه تمامي پارامترهاي ورودي موردنياز به آن ارسال شده باشد. استفاده از تابع ()NA* یافتن و رفع خطاهای فرمول نوشته شده *. این مطالب جنبهی تکمیلی دارد و در آزمونها از این مباحث سؤال طرح نشود. 190 پيام خطا علت روش رفع خطا استفادهی نامناسب از جداساز آدرس محدوده !NULL# زماني كه دو محدودهي آدرس، اشتراك نداشته باشند. اصالح آدرسها !NUM# ارسال وروديهاي اشتباه به يك تابع اصالح داده يا آرگومانهاي ارسال شده به يك تابع استفاده از تابعي كه به علت اشتباه دستورات آن، خاتمه نيابد. ً )مثال IRR يا RATE*) عدد نتيجه بسيار بزرگ باشد يا به اندازهاي كوچك باشد كه قابل محاسبه نباشد. *. این مطالب جنبهی تکمیلی دارد و در آزمونها از این مباحث سؤال طرح نشود. يكي ديگر از انواع خطاها، خطاي حلقهي بازگشتي )Reference Circular )است. اين خطا هنگامي رخ ميدهد كه آدرس خانهاي كه فرمول در آن قرار دارد، در سمت راست تساوي فرمول ذكر شود. به عنوان مثال در خانهي A4 فرمول زير قرار گيرد : A4 + A2 + A1= به اين ترتيب پيغام خطا مطابق شكل 2٤-3 ظاهر ميشود. شكل ٢٤-3 فرمولنویسی و کار با تابعها وقتي در خانهاي خطايي رخ دهد، يك مثلث سبزرنگ در گوشهي سمت چپ باالي خانه ظاهر ميشود كه با انتخاب آن خانه، نشانهي Checking Error را در كنار آن ميبينيد. با قرار دادن اشارهگر ماوس روي آن، دليل بروز خطا مشاهده ميشود. ميتوانيد با استفاده از گزينههاي منو، خطاي رخ داده را بررسي كنيد و با استفاده از راهنماي برنامه و ارزيابي مراحل محاسبهي فرمول، اشكال را برطرف كنيد. كنجكاوي عملكرد هر يك از گزينههاي نشانهي Checking Error را مشخص كنيد. جدول 7-3( ادامه( 191 براي آنکه سريع به خانهي داراي خطا برويم، از زبانهي Home ريبون، قسمت Editting )شكل 2٥-3 ،)گزينهی Select&Find را انتخاب، از منوي بازشده )شكل 2٦-3 )گزينهي ...Special To Go را كليك ميكنيم. از پنجرهي Special To Go( شكل 2٧-3 ،)گزينهي Formulas و از گزينههاي آن Errors را انتخاب ميكنيم. با زدن دكمهي OK به اولين خانه داراي خطا منتقل ميشويم. یافتن و رفع خطاهای فرمول نوشته شده شكل ٢٥ -3 پيغام خطاي حلقهي بازگشتي شكل ٢٦-3 شكل ٢٧-3 192 ١-٤-٣ پيشگيري از برخي خطاها و نمايش پيغام مناسب در برخي خطاها با استفاده از تابع )(ISERROR ميتوانيم بهجاي نوع خطاي پيشفرض كه در جدول 7-3 مشاهده كرديد، پيغام مناسب و مورد نظر خود را نمايش دهيد )شکل ٢٨-٣ .) ٢-٤-٣ خوانايي بيشتر با رفع خطاها و قالببندي مناسب با رفع خطاهاي يك كاربرگ و مشكالت قالببندي، خوانايي كاربرگ بيشتر ميشود. شکلهای ٢٩-٣ و ٣٠-٣ اطالعات مواد اوليه يك شركت را در يك كاربرگ نشان ميدهد. شكل 30-٣ ، همان كاربرگ شكل 29-٣ ،پس از قالببندي و رفع خطاها است. خوانايي كداميك بيشتر است؟ براي قالببندي ميتوانيد از امكانات و قابليتهايي كه در فصلهاي قبلي ياد گرفتهايد و در فصل بعدي نيز برخي از موارد را خواهيم ديد، بهره ببريد. ــ براي خوانايي بايد نوع دادهي عددي به طور مناسب تنظيم شوند. ــ فونتها و رنگ فونتها تنظيم گردند. ــ اندازهي رديفها و ستونها تنظيم گردند. ــ همترازي متن و مقدار خانهها تنظيم گردند. ــ خطوط مرزي، رنگ زمينه و الگوي زمينه خانهها تنظيم گردند. فرمولنویسی و کار با تابعها شكل 2٨-3 یافتن و رفع خطاهای فرمول نوشته شده 193 شكل 2٩-3 شكل ٣٠-3 يك كاربرگ داراي خطاها و مشكالت قالببندي 194 خالصهی فصل يكي از قابليتهاي مهم اكسل، فرمولها است. فرمولها عبارتهاي محاسباتي يا مقايسهاي هستند كه عملياتي را بر روي دادهها انجام ميدهند. فرمول ميتواند شامل ثابتها، عملگرها، انواع تابعها و آدرس خانهها باشد. تابع، فرمول از پيشنوشتهشدهاي است كه يك يا چند مقدار را به عنوان ورودي ميگيرد، عملياتي را انجام داده و يك يا چند مقدار را بر ميگرداند. با استفاده از تابعها، فرمولها سادهتر ميشوند. عملگرها، عالمت يا نشانههايي هستند كه نوع محاسبات را در يك عبارت مشخص ميكنند. عملگرها شامل عملگرهاي محاسبات رياضي، مقايسهاي، منطقي و آدرس خانهها هستند. در اكسل دو نوع آدرسدهي وجود دارد: نسبي و مطلق. »آدرسدهي نسبي« حروف و ً اعداد بدون عالمت خاص است مثال A1 .كه با كپي كردن در خانههاي ديگر، آدرسدهي فرمول نيز تغيير ميكند. »آدرسدهي مطلق« هميشه به خانهي مشخصي اشاره دارد و اگر كپي شود، آدرس مطلق ثابت باقي ميماند. تابعها، از نظر ورودي به سه نوع تقسيم ميشوند: فاقد آرگومان، داراي آرگومان مشخص ثابت، داراي تعداد آرگومان متغير. در اكسل تابعهاي مختلفي وجود دارد كه در دستههايي گروهبندي شدهاند. از جمله : تابعهاي رياضي، مالي، مثلثاتي، و ... كه هر يك كاركرد مربوط به خود را دارد. از نظر نتيجه نيز، پاسخ برخي تابعها يك خانه است و پاسخ تابعهايي مانند MMULT بيش از يك خانه است. براي مشاهدهي پاسخ تابعهايي مانند MMULT ،پس از انتخاب محدودهي جواب و كليك روي خط فرمول، دكمههاي Enter + Shift + Ctrl را ميزنيم. فرمولنویسی و کار با تابعها خودآزمایی 195 خودآزمايي ١ .حاصل عبارتهاي رياضي زير را با استفاده از تابعها در اكسل بهدست آوريد: )ج 3 2 4 5( +الف + 7 8 13 9 )ب + 3 1 5 + 2 1( د 2 2 .نمرهها و واحدهاي چهار درس يك هنرجو در كاربرگ مشخص است. معدل وي را حساب كنيد و اگر بيشتر از 17 است، كاري كنيد كه پيغام مناسب در خانهي E2 قرار گيرد. ٣ .تابع از نظر تعداد ورودي و نتيجه چند نوع است؟ مثال بزنيد. ٤ .انواع تابعها را نام ببرید. ٥ .حاصل قسمتهاي زير را بهدست آوريد: ((4;2(MIN; 18); 12;4 (MAX ( SUM) الف ((“Setayesh(“LEN; 16); 3(FACT ( SUM) ب ٦ .در مورد عملكرد تابعهاي ROUNDDOWN و ROUNDUP تحقيق كنيد. ٧ .آيا استفاده از تابع AVERAGE براي محاسبهي ميانگين وزني مناسب است؟ ٨ .ميخواهيم از بين 100 كارمند با شمارههاي 710 تا 809 ، يك كارمند را به صورت تصادفي انتخاب كنيم. فرمول مناسب در اكسل را بيان كنيد. ٩ .با استفاده از تابع مناسب، مقدارهاي موجود در ستونهاي A و B را در ستون C به هم ملحق كنيد. 196 ١٠ .براي تابع ISNUMBER چند كاربرد مناسب ذكر كنيد. فرمولنویسی و کار با تابعها