توابع اکسل و فرمول نویسی

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

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

در تصویر زیر، سلول A3 حاوی فرمولی است که مقدار سلول A2 را به مقدار سلول A1 اضافه می‌کند.

سلول A3 در تصویر زیر حاوی تابع SUM است که مجموع محدوده A1:A2 را محاسبه می‌کند.

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

برای وارد کردن فرمول مراحل زیر را انجام دهید:

1. یک سلول را انتخاب کنید.
2. برای اینکه اکسل بداند که می‌خواهید فرمولی را وارد کنید، علامت مساوی (=) را تایپ کنید.
3. برای مثال فرمول A1+A2 را تایپ کنید.

نکته: به جای تایپ A1 و A2، کافی است سلول A1 و سلول A2 را انتخاب کنید.

1. مقدار سلول A1 را به 3 تغییر دهید.

اکسل به طور خودکار مقدار سلول A3 را دوباره محاسبه می کند. این یکی از قدرتمندترین ویژگی های اکسل است!

ویرایش یک فرمول

هنگامی که یک سلول را انتخاب می‌کنید، اکسل مقدار یا فرمول سلول را در نوار فرمول نشان می‌دهد.

1 – برای ویرایش فرمول، روی نوار فرمول کلیک کرده و فرمول را تغییر دهید.

2 – سپس enter را کلیک کنید.

اولویت عملگرهای ریاضی

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

مثال زیر را ببینید.

 

ابتدا اکسل ضرب (A1 * A2) را انجام می‌دهد و سپس مقدار سلول A3 را به نتیجه اضافه می‌کند.

مثالی دیگر،

ابتدا اکسل قسمت داخل پرانتز (A2+A3) را محاسبه می‌کند و سپس این نتیجه را در مقدار سلول A1 ضرب می‌کند.

کپی کردن یک فرمول

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

1 – فرمول زیر را در سلول A4 وارد کنید.

2 – سلول A4 را انتخاب کرده و کلیک راست کنید و سپس روی Copy کلیک کنید (یا CTRL + c را فشار دهید)…

3 – سپس سلول B4 را انتخاب کرده و کلیک راست کنید و سپس روی Paste در قسمت Paste Options کلیک کنید (یا CTRL + V را فشار دهید).

علاوه بر این می‌توانید فرمول را به سمت سلول B4 کشیده و رها کنید. سلول A4 را انتخاب کنید، روی گوشه سمت راست پایین سلول A4 کلیک کنید و آن را به سمت سلول B4 بکشید. این بسیار ساده‌تر است و دقیقاً همان نتیجه را می‌دهد!

فرمول موجود در سلول B4 به مقادیر ستون B اشاره می‌کند و فرمول را روی مقادیر ستون B  اعمال می‌کند.

درج تابع در اکسل

هر تابع ساختار یکسانی دارد. برای مثال، SUM(A1:A4) را در نظر بگیرید.

نام این تابع SUM است، قسمت بین براکت ها (آگومان ها) به این معنی است که به اکسل محدوده A1:A4 را به عنوان ورودی می‌دهیم. این تابع مقادیر سلول‌های A1، A2، A3  و A4 را باهم جمع می‌کند. به خاطر سپردن توابع و آرگومان‌های آنها آسان نیست. خوشبختانه ویژگی  Insert Function  در اکسل به شما در این امر کمک می‌کند. برای درج یک تابع، مراحل زیر را انجام دهید :

1. یک سلول را انتخاب کنید.
2. روی دکمه Insert Function کلیک کنید.

کادر محاوره ای Insert Function ظاهر می‌شود.

3 – یک تابع را جستجو کنید یا یک تابع را از دسته بندی انتخاب کنید.به عنوان مثال، COUNTIF را از دسته آمار انتخاب کنید.

1. روی OK کلیک کنید. کادر محاوره ای Function Arguments ظاهر می‌شود.
2. در کادر Range کلیک کنید و محدوده A1:C2 را انتخاب کنید.
3. در کادر Criteria کلیک کنید و >5  را تایپ کنید.
4. روی OK کلیک کنید.

تابع Countif یک تابع آماری در اکسل است که تعداد سلول ها را در محدوده ای که معیار خاصی را برآورده می‌کند ، شمارش می‌کند.  سینتکس آن به این صورت است  =COUNTIF(range, criteria) ، که در آن “range” محدوده سلول هایی است که باید شمارش شوند و ” criteria” شرطی است که کنترل می‌کند کدام سلول ها باید شمارش شوند. در نتیجه تابع تصویر بالا تعداد سلول‌های بزرگتر از 5 را می‌شمارد.

توجه: به جای استفاده از ویژگی Insert Function، به سادگی =COUNTIF(A1:C2,”>5″) را تایپ کنید.  وقتی به    =COUNTIF( رسیدید به جای تایپ A1:C2 به سادگی محدوده A1:C2 را انتخاب کنید.

 

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

در این قسمت می‌خواهیم 10 تابع پرکاربرد اکسل را بررسی کنیم. فایل اکسل را دانلود کرده و سعی کنید این توابع را وارد کنید.

تابع COUNT

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

توجه: از COUNTA برای شمارش تمام سلول هایی که خالی نیستند می‌توانید استفاده کنید COUNTA مخفف count all است.

تابع SUM

برای جمع کردن محدوده ای از سلول ها، از تابع SUM در اکسل استفاده می‌شود. تابع SUM در تصویر زیر تمام مقادیر ستون A را جمع می‌کند.

توجه: شما همچنین می‌توانید از تابع SUM برای جمع کل یک ردیف استفاده کنید. به عنوان مثال، =SUM(5:5)   تمام مقادیر در ردیف 5 را جمع می‌کند.

تابع IF

تابع IF بررسی می‌کند که آیا یک شرط برآورده شده است یا خیر و در صورت درست بودن شرط، مقدار مشخص داده شده و اگر شرط نادرست باشد مقدار دیگری را برمی‌گرداند.

توضیح: در تصویر بالا اگر امتیاز بزرگتر یا مساوی 60 باشد، تابع IF نشان داده شده در بالا Pass را برمی‌گرداند، در غیر این صورت Fail را برمی‌گرداند. برای کپی سریع این فرمول در سلول‌های دیگر، روی گوشه سمت راست پایین سلول C2 کلیک کرده و آن را به سمت سلول C6 بکشید.

تابع AVERAGE

برای محاسبه میانگین گروهی از اعداد، از تابع AVERAGE استفاده کنید. فرمول زیر، میانگین 3 عدد بزرگتر در محدوده A1:A6 را محاسبه می‌کند.

در تصویر بالا عملکرد LARGE آرایه {20،15،10} را برمی‌گرداند.  این آرایه به عنوان آرگومان برای تابع AVERAGE استفاده می‌شود و نتیجه 15 را بر‌ می‌گرداند.

تابع  VLOOKUP

تابع VLOOKUP مقدار 53 (آرگومان اول) را در سمت چپ ترین ستون جدول قرمز (آگومان دوم) جستجو می‌کند. مقدار 4 (آگومان سوم) به تابع VLOOKUP می‌گوید که مقدار موجود در همان سطر را از ستون چهارم جدول قرمز برگرداند. سینتکس تابع به این صورت است:

VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

lookup_value : مقداری که می‌خواهید جستجو کنید که باید در ستون اول محدوده مشخص شده باشد.

table_array : محدوده سلول هایی که VLOOKUP در آنها lookup_value را جستجو می کند و مقدار آرگومان اول از درون این محدوده  جستجو می‌شود.

col_index_number : شماره ستونی (با 1 برای سمت چپ ترین ستون جدول شروع می‌شود) که می‌خواهید مقدار آن را برای شما بر گرداند. در مثال بالا ستون 4 یا salary مد نظر ماست.

range_lookup: یک آرگومان اختیاری که مشخص می‌کند آیا می‌خواهید VLOOKUP یک تطابق دقیق یا تقریبی را پیدا کند (0  یا FALSE  برای تطابق دقیق، 1 یا TRUE برای یک تطابق تقریبی).

تابع MIN

برای یافتن حداقل مقدار، از تابع MIN استفاده کنید. این تابع به همان اندازه که به نظر می‌رسد ساده است.

تابع MAX

برای یافتن حداکثر مقدار، از تابع MAX استفاده کنید.

تابع SUMPRODUCT

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

توضیح: تابع SUMPRODUCT این محاسبه را انجام می‌دهد: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500.

خلاصه و جمع‌بندی

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

 

“رباب سعادت‌دوست” دکتری سیستم‌های اطلاعاتی دارد. از علاقه‌مندی‌های او مطالعات کیفی، تکنولوژی‌های آموزش آنلاین و علم داده می‌باشد. و در حال حاضر نوشتارهای مربوط به حوزه فناوری اطلاعات را در مجله دهکده جهانی دانش و مهارت تهیه می‌کند.