فرمول نویسی در اکسل

---

 

• چگونه در اکسل فرمول نویسی کنیم؟

از اساسی ترین مباحث در آموزش اکسل که هر کاربر باید آن را به خوبی یاد بگیرد ، فرمول نویسی است. همانطور که قبلأ اشاره کردیم ؛ محتوای سلول ها یا از مقدار تشکیل شده است یا از فرمولها (Formulas). برای انجام محاسبات در نرم افزار اکسل از قابلیت فرمول نویسی استفاده می کنیم. این محاسبات می تواند بر مبنای سلول های دیگر نیز انجام شود. در این مطلب آموزشی با نحوه کار فرمول ها و عملگرها در نرم افزار Excel آشنا خواهید شد.

• اجزاء تشکیل دهنده فرمول کدامند؟

فرمول ها معمولأ از عملگرهای ریاضی ؛ اعداد و یا آدرس سلول ها تشکیل می شوند. برای وارد کردن فرمول در سلول ابتدا بایستی علامت مساوی (=) را از روی کیبورد تایپ کنیم و سپس فرمول مورد نظر خود را طبق قواعد تعیین شده وارد کنیم. نرم افزار اکسل ؛ بر اساس فرمول وارد شده محاسبات را انجام و نتیجه را در همان سلول به نمایش در خواهد آورد. هر فرمول ممکن است دارای اجزاء زیر باشد :

• مقادیر ثابت
• آدرس سلول ها
• عملگرها
• توابع

• مقادیر ثابت

این مقادیر همانگونه که از نام آنها پیداست ؛ ثابت بوده و تغییر نمی کنند. این مقادیر ممکن است عدد یا متن باشند. در فرمول نویسی مقادیر متنی را بایستی در بین دو کوتیشن (“”) استفاده کنیم. نکته ای که بایستی به آن توجه داشته باشید این است که اگر مقادیر عددی را در بین دو کوتیشن (“”) استفاده کنیم ؛ یعنی مثلأ اگر بنویسیم “500” این عدد خاصیت عددی خود را از دست داده و حالت متن به خود می گیرد و قابلیت انجام محاسبات ریاضی بر روی آنن وجود نخواهد داشت.

• آدرس سلول ها

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

فرض کنید در سلول A1 عدد 350 را وارد می کنید ؛ حالا در سلول D1 فرمول A1= را می نویسیم ؛ در این حالت مقدار سلول D1 از آنجایی که آن را برابر با سلول A1 قرار داده ایم برابر با 350 خواهد بود ؛ حالا با تغییر مقدار سلول A1 از 350 به 550 ملاحظه خواهید نمود که مقدار سلول D1 نیز تغییر یافته و برابر با 550 خواهد شد.

---

---

---

---

• عملگرها

عملگرها از مهمترین اجزاء یک فرمول محسوب می شوند ؛ چون عملیات محاسبه ای و مقایسه ای از طریق آنها انجام می‌شود عملگرها را به چهار دسته تقسیم بندی می کنیم که عبارتند از :

• عملگرهای محاسباتی (ریاضی) : از این عملگرها در انجام محاسبات عددی استفاده می کنیم.

 

 

• عملگرهای مقایسه ای (رابطه ای) : از این نوع عملگرها برای انجام مقایسه مقادیر استفاده می کنیم ؛ نتیجه این مقایسه ممکن است مثبت (درست) یا منفی (غلط) باشد. بر مبنای این مقایسه می توان دستوراتی را جهت تصمیم گیری صادر نمود.

 

 

• عملگرهای رشته ای (متنی) : از عملگر & برای اتصال (الحاق) داده های متنی استفاده می کنیم. فرض کنید در سلول A1 کلمه Microsoft و در سلول A2 کلمه Excel را وارد کرده ایم. حالا در سلول A3 فرمول “A1&A2&”2019= را می نویسیم ؛ خروجی این فرمول بصورت MicrosoftExcel2019 خواهد بود. همانطور که ملاحظه می کنید کلمات کاملأ به هم چسبیده است که لازم است بین دو کلمه Microsoft و Excel و عدد 2019 باید یک فاصله ایجاد کنیم. برای انجام اینکار لازم است فرمول را بصورت زیر تغییر دهیم.

“A1&” “&A2&” “&”2019=

بعد از ایجاد این تغییر نتیجه بصورت Microsoft Excel 2019 خواهد بود که آن را خواناتر کرده است.

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

• توابع

توابع ؛ فرمول های از پیش تهیه شده ای هستند که نرم افزار اکسل برای سهولت در فرمول نویسی ارائه کرده است. هر تابع ممکن است از صفر تا چند ورودی داشته باشد. ورودی تابع را آرگومان می نامیم. یک تابع پس از دریافت آرگومان های لازم ؛ محاسبات تعریف شده را انجام داده و یک نتیجه را بعنوان خروجی ؛ در سلول مقصد قرار خواهد داد. فرض کنید در داخل سلول های A1 تا A20 اعدادی را وارد کرده ایم و می خواهیم تا جمع آن را محاسبه کنیم ؛ برای انجام اینکار باید از تابع Sum استفاده کنیم و آن را باید بصورت زیر در سلول A11 می نویسیم.

در این فرمول تابع Sum مقادیر سلول های A1 تا A20 را جمع بسته و نتیجه را در سلول A11 به نمایش در خواهد آورد. تغییر در هریک از اعداد سلول های A1 تا A20 سبب تغییر در مجموع آنها که در سلول A11 می باشد ؛ خواهد گردید.

در واقع از آنجایی که اولویت عملگر ضرب * از اولویت عملگر جمع + بالاتر است ؛ ابتدا عمل ضرب و سپس عمل جمع انجام خواهد شد ؛ پس بنابراین پاسخ فرمول عدد 35 خواهد بود. در چنین مواردی چنانچه هدف از این فرمول عدد 45 باشد ؛ از اولویت بالای پرانتز استفاده کرده و ابتدا عملیات جمع را در داخل پرانتز انجام می دهیم تا قبل از عملیات ضرب انجام شود ؛ سپس نتیجه را در عدد 3 که در اولویت بعدی قرار دارد ضرب می کنیم. یعنی خواهیم داشت 3*(10+5)=

• چگونه یک فرمول را کپی کنیم؟

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

---

---

در ستون E می خواهیم قیمت کل را برای هر سطر محاسبه کنیم ؛ برای انجام اینکار مطابق تصویر زیر سلول D2 را در C2 ضرب می کنیم ؛ در واقع تعداد کالا را در قیمت واحد آن ضرب می کنیم.

در گام بعدی می بایست فرمول نوشته شده در سلول E2 را که در سایر سلول های ستون E قرار واقع شده اند بنویسیم. مثلمأ در صورتی که تعداد سلول ها زیاد باشد ؛ نوشتن فرمول در تک تک آنها بصورت دستی کار درستی نخواهد بود. بدین منظور می توان از روش هایی اینکار را انجام داد که در زیر به برخی از آنها اشاره می کنیم:

• استفاده از خاصیت Auto Fill در اکسل :

  • برای استفاده از خاصیت Auto Fill در اکسل سلولی را که در سلول E2 نوشته ایم را انتخاب می کنیم ؛ سپس نشانگر موس را به پائین سمت چپ سلول حرکت می دهیم ؛ در این حالت نشانگر موس به یک علامت + تبدیل می شود ؛ کلیک چپ موس را نگه داشته و آن را به سمت پائین حرکت می دهیم ؛ ملاحظه خواهید کرد که فرمول به سایر سلول ها کپی می شود. البته در صورتی که بر روی + دابل کلیک هم انجام دهیم ؛ اکسل بصورت خودکار فرمول را در سایر سلول ها کپی خواهد کرد.

• استفاده از Paste Special :

  • روی سلول E2 کلیک و آن را انتخاب کنید ؛ با زدن کلیدهای ترکیبی Crtl+C از روی کیبورد ؛ فرمول را کپی کنید ؛ حالا مطابق تصویر زیر به تب Home بخش Clipboard رفته و روی فلش سیاه رنگ گزینه Paste کلیک کنید ؛ از منوی باز شده در انتها روی گزینه Paste Special کلیک کنید ؛ در این حالت پنجره Paste Special باز می شود در این پنجره در بخش Paste روی Formulas کلیک کنید تا انتخاب شود ؛ در انتها با کلیک بر روی دکمه Ok از پنجره مذکور خارج شوید. ملاحظه خواهید کرد که فرمول در سایر سلول ها کپی شده است.

 

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

همانگونه که پیشتر هم به آن اشاره شد ؛ توابع فرمول های از پیش تعیین شده ای هستند که برای سهولت در انجام کار توسط اکسل در اختیار کاربران گذاشته شده اند. برای استفاده از هر تابع می بایست ابتدا سلول را که قرار است نتیجه تابع در درون آن قرار گیرد انتخاب کنیم ؛ سپس با گذاشتن علامت = مساوی ؛ نام تابع را وارد کرده و آرگومان های آن را پر می کنیم ؛ ورودی ها (آرگومان ها) تابع ممکن است ؛ مقادیر ثابت ؛ آدرس یک یا مجموعه ای از سلول ها باشد. در نرم افزار اکسل توابع با توجه به کاربردشان به دسته بندی های زیر تقسیم بندی می شوند.

• توابع مالی (Financial)
• توابع تاریخ و ساعت (Data & Time)
• توابع ریاضی و مثلثاتی (Math & Trig)
• توابع آماری (Statistical)
• توابع جستجو و مرجع (Lookup & Reference)
• توابع پایگاه داده (Database)
• توابع متنی (Text)
• توابع منطقی (Logical)
• توابع اطلاعاتی (Information)
• توابع مهندسی (Engineering)

 

• ساختار یک تابع چگونه است؟

  • ساختار یک تابع بصورت (… ; آرگومان 2 ; آرگومان 1 ) تابع می باشد.

برای سهولت در وارد کردن نام یک تابع و آرگومان های آن ؛ مطابق شکل زیر در نوار فرمول روی Fx کلیک می کنیم ؛ در این حالت پنجره Insert Function به نمایش در خواهد آمد.

 

• • Search for a function : نام تابع مورد نظر در این تابع را جستجو کرده و دکمه Go را کلیک می کنیم ؛ لیستی از توابع که نام وارد شده جزئی از آنهاست ؛ در قسمت Select a Function به نمایش در می آید. در این لیست ؛ نام توابعی که نام وارد شده در توضیح آنها وجود دارد نیز نمایش داده می شود.

 

 

• • Or Select a Category : در پنجره Insert Function می توان توابع را با استفاده از گروه بندی موضوعی آنها پیدا کرد. این گروه ها در منوی کشویی Or Select a Category قرار دارد. بعد از انتخاب دسته بندی ؛ توابع مربوط به آن در بخش Select a Funnction ظاهر می شوند که میتوان آنها را بسته به نیاز خود انتخاب کرد.

 

 

• • Select a Function : در پنجره Insert Function در بخش Select Function می توان تابع مورد نظر خود را انتخاب کرد. مطابق تصویر زیر با دابل کلیک کردن روی نام تابع پنجره Function Arguments به نمایش در آمده ؛ آماده دریافت آرگومان های تابع خواهد بود.

 

در تصویر بالا تابع Sum آماده دریافت آرگومان های خود می باشد ؛ پس از معرفی آرگومان های این تابع روی دکمه Ok کلیک می کنیم تا نتیجه تابع در سلولی که تابع را در آن درج کردیم به نمایش در آید ؛ در مثال بالا با معرفی آرگومان ها که آدرس سلول های حاوی عدد بایستی باشند ،  بدلیل استفاده از تابع Sum استفاده ؛ مقادیر این سلول ها با یکدیگر جمع خواهند شد.

• معرفی برخی از توابع مهم در اکسل

  • تابع Sum (جمع)

هدف : محاسبه مجموع مقادیر عددی.

ساختار : (…;SUM(Number1; Number2 (هر آرگومان مشخص کننده یک مقدار عددی است).

مثال1 : محاسبه مجموع مقادیر موجود در سلول های A1 , B4 و C8 (سلول های غیر مجاور) : 

(SUM(A1;B4;C8

مثال2 : محاسبه مجموع مقادیر موجود در سلول های A1 تا B12 (سلول های مجاور) : 

(SUM(A1:B12

---

• • تابع SumIF (جمع تک شرطی)

هدف : محاسبه مجموع مقادیر عددی دارای یک شرط خاص

ساختار : ([SUMIF(range, criteria, [sum_range

range : محدوده ای که باید شرط در داخل آن جستجو شود

criteria : شرطی که باید جستجو براساس آن انجام شود (این شرط می تواند بصورت دستی وارد شود و هم میتوان آن را به یک سلول ارجاء داد)(تذکر در صورتی که شرط شما بصورت متن است و آنرا بصورت دستی وارد می کنید ؛ متن را در بین دو دابل کوتیشن “” قرار دهید)

[sum_range] : محدوده ای که عملیات جمع روی آن انجام می شود

مثال : محاسبه مجموع مقادیر فروش در مرداد ماه (شرط در سلول B1)

([SUMIF(A1:A100, B1, [C1:C100

---

• • تابع SumIFS (جمع چند شرطی)

هدف : محاسبه مجموع مقادیر عددی دارای چند شرط

ساختار : (…[SUMIFS (sum_range, range1, criteria1, [range2], [criteria2

sum_range : محدوده ای که باید عملیات جمع روی آن انجام شود

range1 : محدوده ای که باید شرط اول در آن جستجو شود

criteria1 : شرط اول

[sum_range] : محدوده ای که عملیات جمع روی آن انجام می شود

[range2] : محدوده ای که باید شرط دوم در آن جستجو شود

[criteria2] : شرط دوم

نکته : با استفاده از این تابع به تعدادی که می خواهید می توانید دامنه جستجو و شرط تعیین کنید تا عملیات جمع بر ساس آنها انجام گردد.

مثال1 : محاسبه مجموع مقادیر فروش در مرداد ماه مربوط به کارشناس فروش 1 (شرط در سلول E1) (شرط دوم در سلول E2)

(SUMIFS(A2:A100;B2:B100;E1;C2:C100;E2

---

• • تابع Average (میانگین)

هدف : محاسبه میانگین مقادیر عددی.

ساختار : (…;AVERAGE(Number1; Number2 (هر آرگومان مشخص کننده یک مقدار عددی است).

مثال1 : محاسبه میانگین مقادیر موجود در سلول های A1 , B4 و C8 (سلول های غیر مجاور) : 

(AVERAGE(A1;B4;C8

مثال2 : محاسبه میانگین مقادیر موجود در سلول های A1 تا B12 (سلول های مجاور) : 

(AVERAGE(A1:B12

---

• • تابع Count (شمارش)

هدف : شمارش سلول های حاوی مقادیر عددی ( سلول های دارای مقادیر متنی و سلول های خالی شمارش نمی شوند).

ساختار : (…;COUNT(Value1; Value2 (هر آرگومان مشخص کننده یک مقدار عددی یا غیر عددی است).

مثال1 : تعیین تعداد سلول های حاوی مقادیر عددی که در سلول های A1 , B3 و C6 (سلول های غیر مجاور) : 

(COUNT(A2;B3;C6

مثال2 : تعیین تعداد سلول های حاوی مقادیر عددی در محدوده A1 تا A12 (سلول های مجاور) : 

(COUNT(A1:A12

---

• • تابع COUNTIF (شمارش تک شرطی)

هدف : شمارش سلول هایی که دارای یک شرط خاص هستند.

ساختار : (COUNTIF(Range;Criteria

Range : دامنه ارزیابی

Criteria : شرط مورد نظر

سلول های موجود در دامنه ارزیابی ؛ با شرط موردنظر ارزیابی شده و در صورتیکه حائز شرط باشند ؛ شمارش می شوند.

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

(“COUNTIF(A1:A10;”>20

---

• • تابع COUNTIFS (شمارش چند شرطی)

هدف : شمارش سلول هایی که دارای چند شرط خاص هستند.

ساختار :([…COUNTIFS (range1, criteria1, [range2], [criteria2

Range1 : دامنه ارزیابی اول  (دامنه ای که شرط اول در داخل آن دامنه جستجو می شود)

Criteria1 : شرط اول

[Range2] : دامنه ارزیابی دوم  (دامنه ای که شرط اول در داخل آن دامنه جستجو می شود)

[Criteria2] : شرط دوم

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

مثال : شمارش تعداد سلول هایی که در دامنه ستون A دارای شرط سلول F1 ؛ در دامنه سلول B دارای شرط سلول F2 هستند.

(COUNTIFS(A:A;F1;B:B;F2

---

• • تابع Max (بیشرین مقدار)

هدف : تعیین بیشترین مقدار بین اعداد (هر آرگومان مشخص کننده یک مقدار عددی است).

ساختار : (…;MAX(Number1; Number2

مثال1 : تعیین بیشترین مقدار موجود در سلول های A1 , B4 و C8 (سلول های غیر مجاور) : 

(MAX(A1;B4;C8

مثال2 : تعیین بیشترین مقدار موجود در سلول های A1 تا B12 (سلول های مجاور) : 

(MAX(A1:B12

---

• • تابع Min (کمترین مقدار)

هدف : تعیین کمترین مقدار بین اعداد (هر آرگومان مشخص کننده یک مقدار عددی است).

ساختار : (…;MIN(Number1; Number2 

مثال1 : تعیین کمترین مقدار موجود در سلول های A1 , B4 و C8 (سلول های غیر مجاور) : 

(MIN(A1;B4;C8

مثال2 : تعیین کمترین مقدار موجود در سلول های A1 تا B12 (سلول های مجاور) : 

(MIN(A1:B12

---

• • تابع ROUND (گرد کردن)

هدف : گرد کردن عدد اعشار ؛ با تعداد رقم اعشار دلخواه

ساختار : (…;ROUND(Number;Num_digits 

Number : عدد اعشاری مورد نظر یا آدرس آن

Num_digits : تعداد رقم اعشار که بعد از گرد کردن مورد نظر است.

مثال : گرد کردن عدد اعشاری موجود در سلول A1 (که بیش از 2 رقم اعشار دارد) با 2 رقم اعشار

(ROUND(A1:2

---

• • تابع INT (جزء صحیح)

هدف : تعیین جزء صحیح عدد اعشاری

ساختار : (INT(Number  (آرگومان این تابع (Number) مشخص کننده یک عدد اعشاری است.

مثال : تعیین جزء صحیح عدد اعشاری موجود در سلول A1

(INT(A1

---

• • تابع IF (شرط)

هدف : تعیین مقدار سلول در صورت برقرار بودن شرط خاص (شرایط با استفاده از عملگرهای مقایسه ای بررسی می شوند).

ساختار :(IF(Logical_test;Value_if_true;Value_if_false

Logical_test : شرط موردنظر

Value_if_true : مقدار سلول در صورت برقرار بودن شرط

Value_if_true : مقدار سلول در صورت برقرار نبودن شرط

مثال : در سلول C1 اگر مقدار B1 بزرگتر یا مساوی 50 باشد ؛ مقدار “OK” و در غیر اینصورت مقدار “NOTOK” قرار گیرد.

(“C1=IF(B1>=50;”OK”;”NOTOK

---

• • تابع RANDBETWEEN (ایجاد اعداد صحیح تصادفی)

هدف : ایجاد اعداد تصادفی (رندوم) در بین یک بازه که آن را خودمان تعیین میکنیم کاربرد دارد

ساختار : (RANDBETWEEN(bottom;top

bottom : کمترین مقدار بازه ای که قصد داریم عدد رندوم در آن ایجاد شود

top : بیشترین مقدار بازه ای که قصد داریم عدد رندوم در آن ایجاد شود

برای تولید اعداد تصادفی کافی است تا کلید F9 را بصورت مکرر فشار دهیم ؛ در هر بار فشردن این کلید یک عدد تصادفی در بازه ای که از قبل برای تابع تعریف کردیم ایجاد خواهد شد.

مثال : ایجاد اعداد تصادفی صحیح بین عدد 100 و 300

(RANDBETWEEN(100;300

---