راهنمایی، مثال و اطلاعاتی درباره فرمولهای اکسل
فرمول ها صفحات گسترده (اکسل) رو محبوبتر میکنند. با ایجاد فرمولها، شما میتونید محاسبات رو سریعتر انجام بدید حتی اگر اطلاعات در سلولهای مرتبط با فرمولها تغییر کنند. به عنوان مثال، شما میتونید مجموع مقادیر سلولهای یک ستون رو باهم جمع کنید.
مفاهیم اولیه و پایه
- همه فرمولهای اکسل با یک علامت (=) شروع میشن.
- بعد از علامت مساوی، نام یک سلول یا توابع فرمولی وارد میشن. توابع نوع فرمولهارو در اکسل معین میکنن.
- اگر یک تابع ریاضی رو اجرا میکنید، فرمول ریاضی اون در پرانتز قرار میگیره.
- استفاده از کولن (:) به شما امکان انتخاب تعداد زیادی از سلولهارو برای قرار دادن در فرمول میده. به عنوان مثال، A1:A10 یعنی سلولهای A1 تا A10.
- فرمولها به صورت پیشفرض با استفاده از سلول مرجع ایجاد میشن، یعنی اگر شما یک علامت ساین ($) در مقابل نام سطر یا ستون اضافه کنید اون به عنوان سلول مرجع در نظر گرفته میشه.
وارد کردن فرمول در صفحات گسترده اکسل
در پایین یک تصویر متحرک از نحوه اضافه کردن فرمولها به صفحات گسترده اکسل قرار دادیم. در فرمول اولمون، داخل سلول “D1” به صورت دستی فرمول SUM= رو وارد کردیم تا اعداد (۱+۲) رو که در سلولهای A1 و B2 قرار دارند با هم جمع کنه و حاصل جمع اونهارو که عدد ۳ هست رو در سلول D1 نمایش بده.
در مثال بعد، ما با استفاده از ماوس سلولهای A2 تا D2 رو مشخص و هایلایت کردیم و بعد به جای تایپ کردن دستی فرمول، از دکمه فرمول که در نوار منو سربرگ Home قرار داره و به صورت پیش فرض روی فرمول SUM تنظیم شده استفاده میکنیم و این گزینه به صورت خودکار فرمول رو اعمال میکنه.
بعد ما نشون میدیم که چطور شما میتونید به صورت دستی یک فرمول رو وارد کنید و سپس با کمک ماوس، سلول و مقدار اون رو انتخاب و دریافت کنید. (همچنین شما میتونید با انتخاب چند سلول مقادیر یک محدوده رو دریافت و فرمول رو روی اونها اعمال کنید). در آخر، به صورت دستی یکبار فرمول (*) رو در سلول D4 با استفاده از تابع SUM برای پیدا کردن مقدارحاصل ضرب ۱۰۰*۵ که در سلولهای A4 و B4 قرار دارند وارد میکنیم.
نمونههایی از فرمولها
توجه: توابعی که در پایین ذکر شدند ممکنه در همه زبانهای مایکروسافت اکسل یکسان نباشند. همه این مثالها در نسخه انگلیسی مایکروسافت اکسل انجام میشه.
نکته: مثالهای پایین به ترتیب حروف الفبا ذکر شدند، اگر شما میخواید با رایجترین فرمول شروع کنید، پیشنهاد میکنیم با فرمول SUM= شروع کنید.
- =
- AVERAGE
- COUNT
- COUNTA
- COUNTIF
- IF
- INDIRECT
- MEDIAN
- MIN AND MAX
- PRODUCT
- RAND
- RANDBETWEEN
- ROUND
- SUM
- SUMIF
- TODAY
- TREND
- VLOOKUP
=
=
بااستفاده از فرمول = (مساوی) شما یک سلولرو در سلول دیگری قرار میدید. برای مثال،” A1 in B1= ” یعنی هرچیزی در A1 بود به طور اتوماتیک در B1 قرار میگیره. همینطور شما میتونید فرمولی ایجاد کنید که یک سلول برابر با بیش از یک مقدار باشه. برای مثال اگر شما در سلول A1 یک نام داشته باشید و در سلول B1 هم نام دیگری، با نوشتن این فرمول A2 =A1&” “&B1، سلول A1 در B1 با یک فاصله قرار میگیرد. همینطور میتونید از یک فرمول تلفیقی برای ترکیب مقادیر سلول استفاده کنید.
AVERAGE
=AVERAGE(X:X)
مقدار میانگین مقادیر بین چند سلولرو نمایش میده. برای مثال، اگر خواستید برای سلولهای A1 تا A30 میانگین بگیرید،باید این عبارت رو تایپ کنید: (AVERAGE(A1:A30=
COUNT
=COUNT(X:X)
تعداد سلولهایی که فقط دارای مقدار عددی هستند را میشمارد. برای مثال شما با استفاده از فرمول (COUNT(A1:A15= میتونید تعداد سلولهایی که بین A1 و A15 حاوی مقدار عددی هستند رو پیدا کنید. اگر تنها سلول A1 و A5 دارای مقدار عددی باشند، مقدار سلولی که حاوی این تابع هست برابر ۲ میباشد.
COUNTA
=COUNTA(X:X)
این تابع در یک محدوده تعداد سلولهایی که شامل هر محتوایی هستند و خالی نیستند (متن و اعداد، نه فقط اعداد) رو میشماره. برای مثال، شما با استفاده از فرمول (COUNTA(A1:A20= میتونید در محدوده A1 تا A20 تعداد سلولهایی که شامل محتوا هستند رو بشمارید. اگر تعداد ۷ سلول خالی بودند عدد “۱۳” نمایش داده میشه.
COUNTIF
=COUNTIF(X:X,”*”)
این تابع سلولهایی که دارای مقادیر خاص هستند رو میشماره. برای مثال، اگر (“COUNTIF(A1:A10,”TEST= رو در سلول A11 وارد کنید، هرسلولی بین A1 تا A10 شامل عبارت “test” هست رو میشماره و تعداد رو برمیگردونه. بنابراین، اگر شما پنج سلول در این محدوده داشته باشید که حاوی لغت “test”میشن، مقدار سلول A11 برابر با ۵ خواهد بود.
IF
(*)IF=
نحوه بیان و استفاده از این تابع به این شکل هست:
=IF(CELL=”VALUE” ,”PRINT OR DO THIS”,”ELSE PRINT OR DO THIS”)
در هر جمله شرطی ۳ قسمت وجود داره، در ابتدا یک مقایسه انجام میشه، قسمت دوم جملات شرطی به ما میگه که اگر پاسخ مقایسه درست بود چه اتفاقی بیفته و قسمت سوم جملات شرطی میگه اگر پاسخ مقایسه درست نبود چه اتفاقی باید بیفته. برای مثال، فرمول (“IF(A1=””,”BLANK”,”NOT BLANK= یعنی اگر در سلول A1 مقداری وجود نداشت عبارت “BLANK” (خالی) رو نمایش بده در غیر اینصورت اگر حاوی محتوایی بود عبارت “NOT BLANK” (خالی نیست) رو برگردون. تابع IF استفادههای بیشتری داره اما به طور معمول به ساختار بالا محدود میشه.
استفاده از IF میتونه برای مواقعی که میخواهید مقدار یک سلول رو محاسبه کنید هم مفید باشه، اما اون سلول باید حتما حاوی مقدار باشه. برای مثال ممکنه شما بخواهید مقدارهای بین دو سلول رو تقسیم کنید. اگر در سلولها چیزی وجود نداشته باشه خطای DIV/0! error# رو دریافت میکنید. با استفاده از دستور IF شما فقط میتونید تنها یک سلول رو که حاوی مقدار هست محاسبه کنید. برای مثال، اگر شما بخواهید فقط یک عمل تقسیم انجام بدید، و اگر سلول A1 حاوی مقداری هست، میتونید این عبارت رو بنویسید:
=IF(A1=””,””,SUM(B1/A1))
که اگر A1 حاوی مقدار یا محتوایی باشه سلول B1 بر A1 تقسیم میشه، در غیر اینصورت سلول خالی هست.
INDIRECT
=INDIRECT(“A”&”2”)
این فرمول مرجعی معین که توسط رشته متنی مشخص شده رو برمیگردونه. در مثال بالا، فرمول مقدار نام سلولی که در A2 موجود هست رو برمیگردونه.
=INDIRECT(“A”&RANDBETWEEN(1,10))
با استفاده از توابع indirect و randbetween مقدار تصادفی یک سلول بین A1 و A2 رو برمیگردنه.
MEDIAN
=MEDIAN(A1:A7)
MIN AND MAX
متوسط مقادیر سلولهای A1 تا A7 رو پیدا می کنه. برای مثال، عدد ۴ متوسطی برای ۱، ۲، ۳، ۴، ۵، ۶، ۷ هست.
=MIN/MAX(X:X)
Min و Max نشان دهنده حداقل و حداکثر مقداری که در سلول ها هستند میباشد. برای مثال، اگر میخواهید حداقل مقدار بین سلولهای A1 و A30 رو بدست بیارید باید این عبارترو قرار بدید: (MIN(A1:A30= یا اگر حداکثر مقدار بین این سلولها رو میخواهید از این عبارت استفاده کنید: (MAX(A1:A30=
PRODUCT
=PRODUCT(X:X)
این تابع چند سلول رو باهم ضرب میکنه. برای مثال (Product(A1:A30= همه سلولهارو باهم ضرب میکنه، بنابراین A1 * A2 * A3 و …
RAND
=RAND()
یک عدد تصادفی بزرگتر از صفر اما کمتر از یک رو تولید می کنه، برای مثال، “۰.۶۸۱۳۵۹۱۸۷” میتونه یک عدد تصادفی تولید شده باشه که در سلول فرمول قرار داده شده است.
RANDBETWEEN
=RANDBETWEEN(1,100)
یک عدد تصادفی بین دو مقدار ایجاد میکنه. درمثال بالا، فرمول عدد تصادفی که بین ۱ تا ۱۰۰ هسترو ایجاد میکنه.
ROUND
=ROUND(X,Y)
یک عدد رو تا تعداد مشخصی از مکانهای اعشار گرد میکنه. X سلول اکسل حاوی عددی هست که باید گرد بشه. Y تعداد مکانهای اعشار رو نشون میده. در پایین تعدادی مثال قرار دادیم:
=ROUND(A2,2)
عددی که در سلول A2 هست رو تا دو رقم اعشار گرد میکنه. اگر عدد ۴.۷۳۶۹ باشه، مثال بالا عدد رو به ۴.۷۴ گرد میکنه. اما اگر عدد ۴.۷۶۱۴ باشه، اون به ۴.۷۶ گرد میشه. برای فهم اینکه اعداد چه وقت به سمت بالا یا پایین گرد میشن باید منطق این تابع رو بدونید، اگر ارقام اعشاری که قرار هست گرد بشه از عدد ۵ کوچکتر بود، به سمت پایین گرد میشه. اگر ارقام اعشاری که باید گرد بشن خود عدد ۵ یا از ۵ بزرگتر بودند به سمت بالا رند میشه (این حالت برای قسمت صحیح اعداد هم برقرار هست).
=ROUND(A2,0)
عددی که در سلول A2 هست رو تا صفر رقم اعشار گرد میکنه یا اون رو به نزدیکترین عدد کامل میبره. اگر عدد ۴.۷۳۶ باشه، مثال بالا اون رو تا عدد ۵. گرد میکنه. اگر عدد ۴.۳۶۷ باشه، به عدد ۴ گرد میشه.
SUM
=SUM(X:X)
این تابع رایجترین تابعی هست که برای جمع، تفریق، ضرب یا تقسیم مقادیر سلولها استفاده میشه. در پایین تعدادی مثال قرار دادیم.
=SUM(A1+A2)
سلولهای A1 و A2 رو باهم جمع میکنه.
=SUM(A1:A5)
سلولهای A1 تا A5 رو باهم جمع میکنه.
=SUM(A1,A2,A5)
سلولهای A1 و A2 و A5 رو باهم جمع میکنه.
=SUM(A2-A1)
سلول A1 رو از A2 کم میکنه.
=SUM(A1*A2)
سلولهای A1 و A2 رو در هم ضرب میکنه.
=SUM(A1/A2)
سلول A1 رو بر A2 تقسیم میکنه.
SUMIF
=SUMIF(X:X,”*”X:X)
در این عبارت تابع SUM برای جمع زدن مقادیر مختلف که دارای یک معیار خاص هستند به کار میره. همونطور که مشاهده میکنید این تابع سه پارامتر داره، پارامتر اول محدوده سلولهایی هست که باید درمورد شرط مورد ارزیابی قرار بگیرن برای مثال A1:A6.
پارامتر دوم شرطی که باید وجود داشته باشه.
پارامتر سوم سلولهایی که در صورت برقراری شرط باید باهم جمع شوند.عبارت (SUMIF(A1:A6,”TEST”,B1:B6= مثالی از این تابع هست که مقادیر موجود در سلولهای B1:B6 رو اگر کلمه “test” بین سلولهای A1:A6 قرار داشت با هم جمع میکنه. بنابراین اگر شما کلمه “TEST” رو (به بزرگی و کوچکی حروف لاتین حساس نیست) در A1 قرار بدید، اما اعداد در سلولهای B1 تا B6 باشند، تنها مقداری که در B1 هست جمع میشه چون کلمه TEST در A1 هست.
TODAY
=TODAY()
این تابع تاریخ جاری رو در سلول وارد شده چاپ میکنه. هربار که اکسل خودتون رو باز میکنید، این مقدار تغییر خواهد کرد تا تاریخ جاری رو نمایش بده.اگر میخواهید تاریخیرو وارد کنید که تغییر نکنه، کلید کنترل”Ctrl” و سمی کالن”؛” رو نگه دارید.
TREND
=TREND(X:X)
این تابع مقدار رایج و یا خاص یک سلول رو پیدا میکنه. برای مثال، اگر سلولهای A1 تا A6 دارای مقادیر ۲، ۴، ۶، ۸، ۱۰، ۱۲ باشند و شما در سلول دیگری این فرمول رو قرار بدید: (TREND(A1:A6=، مقدار ۲ رو دریافت خواهید کرد، چون اعداد در اون محدوده به هر عدد ۲ واحد اضافه شده اضافه شده.
VLOOKUP
=VLOOKUP(X,X:X,X,X)
فرمول lookup، hlookup، یا vlookup به شما امکان جست و جو و پیدا کردن مقادیر مرتبط برای نتایج بازگشتی رو میده.