فرمول نویسی و اصول پایه در اکسل

آشنایی با مبانی فرمول نویسی در اکسل

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

ثابت‌ها در فرمول نویسی:

مقاديري هستند كه تغيير نمی‌کنند. مثلاً عدد 190 و يا عبارت «درآمد ماهانه». اگر بخواهيم مقدار عدد 200 را به‌جای 190 استفاده كنيم، بايد عدد را در فرمول بازنويسي كنيم .

آدرس خانه‌ها:

آدرس خانه‌ای است كه مقدار آن در فرمول استفاده می‌شود. مثلاً می‌توانیم مقدار عدد 200 را در خانه‌ A15 درج كنيم و از آدرس A15 در فرمول استفاده كنيم. در اين صورت به جای بازنويسي فرمول فقط كافي است كه مقدار خانه‌ای A15 را به مقدار 200 تغيير دهيم.

عملگرها:

علامت يا نشانه‌هایی هستند كه نوع محاسبات را در يك عبارت مشخص می‌کنند. عملگرها كه می‌توانید در فرمول‌ها به کار ببريد به چهار دسته تقسيم می‌شوند: عملگرهاي محاسبات رياضي ،مقایسه‌ای، منطقي و آدرس خانه‌ها.

انواع تابع‌ها در فرمول نویسی:

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

مثال ١:

می‌خواهیم مساحت يك دايره را با داشتن شعاع آن محاسبه كنيم .

مراحل انجام كار به‌صورت زير است:

1. خانه‌ی A2 را براي ورود شعاع در نظر می‌گیریم. يعني آدرس خانه‌ی A2 نشان‌دهنده‌ی شعاع دايره است.
2. در خانه‌ی ديگر مثلاً A3، فرمول زير را تايپ كنيد:

=PI()*A2ˆ2

PI تابعي است كه مقدار عدد π يعني مقدار 3.14 را برمی‌گرداند.

A2 آدرس خانه است كه محتواي آن شعاع دايره است.

2 توان استفاده‌شده در اين فرمول است.

علامت توان رساني ^{^} و علامت ضرب *، عملگرهاي محاسبات رياضي در اين فرمول هستند.

به‌عبارت‌دیگر، شعاع دايره به توان 2 رسيده و در عدد π ضرب می‌شود كه همان فرمول محاسباتي مساحت دايره است.

عملگرهای محاسبات رياضی در فرمول نویسی

اين عملگرها بر روي مقادير عددي عمل می‌کنند و به ترتيب تقدم در جدول آورده شده‌اند.

مثال	توضيحات	عملگر رياضي
%20	درصد = Percent	%
3^2	توان رساني = Exponentiation	^
3*3	ضرب = Multiplication	*
3/3	تقسيم = Division	/
3+3	جمع = Addition	+
-13-1	تفريق = Subtraction منفی‌ساز = Negation	–

در فرمول‌های پیچیده‌تر می‌توانید براي تقدم عملگرها از پرانتز استفاده كنيد. در پردازش فرمول‌ها، ابتدا عبارت‌های داخل پرانتز به ترتيب تقدم محاسبه می‌شوند. عملگرهايي كه داراي اولويت يكسان هستند از چپ به راست محاسبه می‌شوند. به‌عنوان‌مثال، فرمول %B3 * 20 = بيست درصد از محتويات خانه‌ی B3 را محاسبه می‌کند.

مثال ٢:

می‌خواهیم ترتيب مراحل انجام محاسبه‌ی فرمول را مشخص كنيم.

= B3 * 4 /(B4 ^ 2 + A2 – A5 +2)

مراحل انجام كار به‌صورت زير است:

1. ابتدا مقدار داخل پرانتز را محاسبه كنيد. مقدار A5 را از A2 كم كرده و سپس با 2 جمع می‌کند.٢. مقدار B4 را به توان 2 می‌رساند.
2. مقدار B3 را در عدد 4 ضرب می‌کند.
3. حاصل‌ضرب را تقسیم‌بر حاصل توان می‌کند .
4. حاصل تقسيم را با حاصل پرانتز جمع می‌کند.

مثال 3:

می‌خواهیم مراحل انجام اكسل كار برای محاسبه عبارت زیر فرمولی بنویسیم.

1. 
2. ابتدا خانه‌ی A1 را براي نوشتن فرمول انتخاب می‌کنیم.
3. نویسه‌ی = را تايپ می‌کنیم. قبل از تايپ فرمول، دقت كنيد كه ترتيب و اولويت اجرا بايد به‌وسیله پرانتز مشخص شود.
4. براي محاسبه‌ی توان اعداد، می‌توانیم از توان كسري استفاده كنيم.

فرمول نهایی در اکسل به شکل زیر خواهد بود.

=(5/2+9/100)/2ˆ(1/2)

نكته:
بهتر است براي خوانايي دستورات محاسباتی، از پرانتزها استفاده شود، حتي اگر بدون پرانتز نيز، نتيجه مورد نظر شما از محاسبات حاصل شده باشد.
دقت كنيد كه اگر نويسه نقطه اعشار را از منوي آفيس ،Excel Options و زبانه Advanced از علامت (.) به علامت / تبديل كرده باشيد، برای نوشتن تقسيم بايد جمله صورت را در پرانتز و جمله مخرج را نيز در پرانتز قرار دهيد. در غير اين صورت عبارت تقسيم، عدد اعشاري فرض خواهد شد.

عملگرهای رشته‌ای

تنها عملگر رشته‌اي، عملگر & است. از اين عملگر براي تركيب رشته‌ها استفاده می‌شود. اگر بخواهيد در فرمول از مقدارهاي رشته‌اي استفاده كنيد بايد آن‌ها را بين دو علامت گيومه (“ ”) قرار دهيد.
توابع بسياري در اكسل وجود دارند كه می‌توانند به جاي برخي عملگرها استفاده شوند. تنها عملگر رشته‌اي & است، درحالي كه تابع‌های متعددي هستند كه بر روي داده‌هاي رشته‌اي متني كار مي‌كنند. اين تابع‌ها را در قسمت آشنايي با برخي تابع‌ها خواهيم ديد.

مثال 4:

دو رشته «هفته» و «اول» را با هم تركيب كنيد به طوري كه نتيجه «هفته اول» شود. همين طور رشته ي لاتين First Week را از تركيب دو رشته Week و First ايجاد كنيد.

مراحل انجام كار به‌صورت زير است:

1. دقت كنيد كه قسمت‌هايي كه با علامت ــ مشخص شده است، نشان دهنده نويسه فضاي خالي است كه با دكمه Space صفحه كليد ايجاد می‌شود.
2. نتيجه فرمول‌های نوشته‌شده براي تركيب رشته فارسي و لاتين را مشاهده مي‌كنيد. نتيجه فرمول خانه‌ A1 مقدار «هفته اول» است كه مورد نظر ما نبوده است. تفاوت فرمول‌های رديف اول با فرمول‌های رديف دوم و سوم، استفاده از نويسه فضاي خالي است. در انتهاي رشته اول در فرمول رديف دوم و يا در ابتدای رشته دوم در فرمول رديف سوم ،نويسه فضاي خالي را با فشردن دكمه Space در متن درج كرده‌ايم.
3. دقت كنيد كه در تركيب دو رشته انگليسي بدون نويسه فضاي خالي، حروف به هم چسبيده نمی‌شوند ولی همان طور كه مشاهده می‌كنيد، نتيجه فرمول رديف اول با فرمول‌های رديف دوم و سوم متفاوت است.

نكته:
دقت شود كه در حالت متن فارسی، فرمول تركيب رشته از راست به چپ نمايش داده عمل می‌شود.

عملگرهاي مقایسه‌ای

اين عملگرها امكان مقايسه دو مقدار را فراهم می‌کنند و حاصل آن‌ها درست /True يا غلط/False است. اولويت اجراي اين عملگرها، پس از عملگرهای محاسبات رياضي و عملگر رشته‌ای است. عملگرهای مقایسه‌ای در جدول نشان داده شده است.

جدول عملگرهای مقایسه‌ای
=	مساوی با	A1 = B1
<	بزرگ تر از	A1 > B1
>	كوچك تر از	A1 < B1
=<	بزرگ تر از يا مساوي با	A1 >= B1
=>	كوچك تر از يا مساوي با	A1 <= B1
<>	نامساوي با	A1 <> B1

عملگرهای آدرس خانه‌ها

براي تعيين محدوده‌اي از خانه‌ها، از عملگر آدرس استفاده می‌شود. اولويت عملگرهاي آدرس از تمام عملگرهاي ديگر بالاتر است.
آدرس‌دهی محدوده‌اي از خانه‌ها: به‌عنوان‌ مثال عبارتC3:E7 به خانه‌هاي بين و خود خانه‌هاي C3 و E7 اشاره مي‌كند.
آدرس‌دهی ستونی: در آدرس‌دهی ستونی، نام جزء اول آدرس ثابت است و شماره پس از نام ستون تغيير می‌کند؛ مانند F1:F10.
آدرس‌دهی سطری: در آدرس‌دهی سطری، نام جزء اول متفاوت است و شماره پس از نام ستون‌ها ثابت است؛ مانند A4:F4.

عملگر (:): عملگر ناحيه است كه يك آدرس به تمام خانه‌ها بين دوآدرس اشاره می‌کند و شامل دو آدرس نيز می‌باشد.
مثال: B5:B15

عملگر (،): عملگر جمع مجموعه‌ها/ تركيب دو ناحيه است كه به تمام خانه‌های هر دو ناحيه اشاره می‌کند.
مثال: SUM(B1:B5,D1:D5)

[supsystic-tables id=1]

نكته:
برنامه اكسل به طور پيش فرض در خانه‌هايی كه داراي فرمول هستند، نتيجه را نمايش می‌دهد و متن فرمول با انتخاب خانه در نوار فرمول قابل مشاهده است. براي مشاهده فرمول در خانه‌های کاربرگ به جای نتيجه محاسبه، كليد ميانبر ~+Ctrl را فشار دهيد. با مجدد ~+Ctrl نتيجه نمايش داده خواهد شد.