فرمول نویسی در اکسل چگونه انجام می‌ شود؟

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

فرمول نویسی با عملگرهای اصلی در اکسل

فرمول نویسی در اکسل در واقع به معنای معادل قرار دادن محتوای یک سلول با یک فرمول است. فرمول‌ها در اکسل متشکل از عملگرها (Operators) و عملوندها (Operands) هستند. به عنوان مثال در معادله 2+2=4 ، اعداد «۲» عملوند، علامت «+» عملگر و عدد «۴» جواب فرمول است. اکسل از عملگرهای استاندارد اصلی در فرمول‌ها استفاده می‌کند. این عملگرها شامل تفریق «-»، جمع «+»، ضرب «*»، تقسیم «/» و توان «‍^» هستند.

اولین قدم در فرمول نویسی بعد از انتخاب سلول مد نظر، شروع فرمول با علامت تساوی است. در واقع با تایپ این علامت اکسل آماده دریافت یک فرمول می‌شود. اگر فرمول خود را با علامت تساوی شروع نکنید، اکسل آن را به عنوان داده متنی، عددی یا غیره می‌بیند. در ادامه با چندین مثال فرمول نویسی در اکسل را می‌آموزیم.

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

ابتدا با فرمول‌‌های بسیار ساده محاسباتی شروع می‌کنیم. فرض کنید قصد دارید حاصل ۱۰ تقسیم بر ۲ را با فرمول نویسی در اکسل به دست آورید. برای این کار، سلولی را انتخاب کنید و علامت مساوی را در آن تایپ کنید.

فرمول ساده 10/2  را بعد از علامت تساوی وارد کنید.

Enter را روی صفحه کلید بزنید تا نتیجه این معادله ساده را در سلول انتخابی ببینید.

عملوندها می‌توانند علاوه بر مقادیر عددی، آدرس سلول‌هایی باشند که حاوی مقدار عددی هستند. به عنوان مثال اعداد ۱۰ و ۲ را به ترتیب در سلول‌های A2 و A3 می‌آوریم و در سلول ‌‌B1، فرمول ‌‌A2/A3  را وارد می‌کنیم. بعد از علامت مساوی می‌توانید، تنها با کلیک بر روی سلول مد نظر، آدرس آن را در فرمول بیاورید.

با زدن Enter نتیجه معادله در سلول B2 نمایش داده می‌شود.

عملوندهای عددی و آدرس سلول‌هایی که محتوای عددی دارند، می‌توانند در کنار هم در یک فرمول نیز بیایند. به عنوان مثال می‌توان محتوای سلول A2 را مستقیماً تقسیم بر ۲ کرد.

با زدن Enter، می‌توانید نتیجه را در سلول B2 ببینید.

• اگر در اکسل هنگام استفاده از فرمول تقسیم، با خطای #DIV/0!  روبرو شدید، به این معناست که تقسیم بر عدد صفر صورت گرفته است.
• معمولا بهتر است مقادیر عددی مد نظر را در سلولی وارد کرده و آدرس آن سلول را در فرمول بیاورید. در این صورت، با تغییر محتوای سلول، فرمول نیز به طور خودکار به‌روزرسانی می‌شود.

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

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

برای مثال با استفاده از عملگر تفریق، خلوص دریافتی را محاسبه می‌کنیم. اگر درآمد را ۱۰۰۰ دلار بگیریم و در سلول A2 وارد کنیم، هزینه‌ها را ۳۰۰ دلار در نظر گرفته و در سلول B2 وارد کنیم، در C2 اختلاف این دو مقدار را با فرمول A2-B2  وارد می‌کنیم.

فرمول نویسی با عملگر ضرب در اکسل

برای مثال فرض کنید قصد دارید مقدار ۲*۴ را به دست آورید عدد ۲ را در سلول A2 و عدد ۴ را در سلول A3 وارد کنید. با تایپ معادله A2*A3  در سلول B2 و زدن Enter، نتیجه را مشاهده خواهید کرد.

فرمول نویسی با سلول‌های نامگذاری شده

عملوندها در فرمول اکسل، علاوه بر مقادیر عددی و آدرس یک سلول، می‌توانند نامی از ستون یا ردیف سلول‌ها باشند. برای این کار ابتدا نیاز است ستون یا ردیف مد نظر را نامگذاری کنیم و آن نام را به عنوان عملوند در فرمول بیاوریم.

به‌عنوان مثال، می‌توان فرمول خالص دریافتی را به صورت «درآمد منهای هزینه» نوشت. ابتدا ستون A را به‌عنوان درآمد و ستون B را به عنوان هزینه نامگذاری می‌کنیم. برای نامگذاری A، روی «A» بالای ستون کلیک کنید. در خانه‌ای که در تصویر زیر نمایش داده شده عبارت «Revenue» را تایپ کنید. به همین ترتیب، ستون B را «Expenses» نامگذاری کنید.

حال در سلول C2 فرمول Revenue-Expenses  را وارد می‌کنیم. با زدن Enter، نتیجه در سلول نمایش داده می‌شود.

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

از عملگرهای محاسباتی جمع، تفریق، ضرب و تقسیم، می‌توانید در کنار یکدیگر در یک فرمول استفاده کنید. نکته مهم در استفاده ترکیبی از این عملگرها، در نظر گرفتن ترتیب محاسبه (PEMDAS) است. اکسل یک معادله ریاضی را به ترتیب زیر انجام می‌دهد:

1. پرانتز «()» (Parenthesis)
2. توان«^» (Exponents)
3. ضرب و تقسیم به ترتیب از چپ به راست «/»، «*» (Multiplication & Division)
4. جمع و تفریق «-»، «+» (Addition & Subtraction)

برای مثال معادله 2+4*6/(3-2) به ترتیب زیر در اکسل انجام می‌شود و نتیجه نهایی در سلول نمایش داده می‌شود:

1. 2+4*6/(1)
2. 2+24/1
3. 2+24
4. 26

تابع جمع SUM

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

فیلم آموزش فرمول نویسی در گوگل شیت Google Sheets – مقدماتی + گواهینامه در فرادرس

کلیک کنید

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

استفاده از فرمول SUM برای جمع زدن دو سلول

فرض کنید قصد جمع زدن دو سلول A2 و A3 را دارید. با استفاده از فرمول SUM می‌توان نتیجه این جمع را با فرمول SUM(A2,A3)  در سلول B2 وارد کرد.

استفاده فرمول SUM برای جمع زدن تعدادی سلول پشت سر هم

فرض کنید قصد جمع زدن ستونی از سلول‌ها را داشته باشید که از ‌‌A2 شروع شده و تا A5 ادامه پیدا کرده باشند. برای این کار با وارد کردن فرمول SUM(A2:A10) ، مقادیر موجود در سلول‌های A2 تا A10 با هم جمع زده می‌شوند.

استفاده از فرمول SUM برای جمع زدن داده‌هایی در چند ستون

اگر داده‌هایی که قصد جمع زدن آنها را دارید، در چند ستون مختلف آمده باشند، می‌توانید هر ستون را با «, » جدا کرده و به کمک تابع SUM جمع بزنید. برای مثال در تصویر، تابع از سه آرگمان تشکیل شده است. آرگمان اول سلول‌های A2 تا A5، آرگمان دوم سلول‌های B2 تا B8 و آرگمان سوم سلول‌های C2 تا C10 را آدرس دهی می‌کند.

نتیجه این تابع در سلول D2 نمایش داده شده است.

تابع ضرب PRODUCT

تابع PRODUCT با سینتکسی مشابه تابع SUM مقادیر را در هم ضرب می‌کند برای مثال، در تصویر زیر مقادیر موجود در سلول‌های A2 تا A10، به‌وسیله فرمول PRODUCT، در سلول B2 در هم ضرب شدند.

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

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

معرفی تابع در اکسل

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

فرمول نویسی با استفاده از Wizard توابع

اگر در استفاده از توابع در اکسل مهارت زیادی نداشته باشید ویزارد «Insert Function» به شما کمک خواهد کرد. کافی‌ست در سربرگ «Formulas»، بخش «Insert Function» را باز کنید. (هم چنین می‌توانید از آیکون «Fx» در کنار نوار فرمول استفاده کنید یا به سادگی با تایپ علامت تساوی در یک سلول، از گزینه «More Functions» در لیست میانبر توابع استفاده کنید.)

در تصویر زیر لیست میانبر توابع را مشاهده می‌کنید.

با زدن دکمه Insert Function پنجره‌ای به همین نام باز می‌شود. در بخش بالایی این پنجره، می‌توانید تابع مد نظر خود را جستجو کنید. در قسمت میانی، لیستی از تمام توابع موجود در اکسل نمایش داده می‌شود. برای مثال، از تابع میانگین AVERAGE استفاده می‌کنیم.

هر یک از توابع موجود در لیست، سینتکس و کاربرد خاص خود را دارند و می‌توانند بسیار پیچیده باشند. در این مطلب تنها به کلیت استفاده از توابع و فرمول نویسی اشاره می‌کنیم.

سپس بعدی ویزارد، آرگومان‌های تابع را به ترتیب «Number1»، «Number2» و غیره به شما نشان می‌دهد. در این مثال برای استفاده از تابع میانگین، دو لیست قیمت میوه در دو صفحه جدا داریم. آرگمان اول تابع، قیمت میوه‌ها از سلول B2 تا B5 در شیت ۲۰۱۴ و آرگمان دوم قیمت میوه‌ها از سلول B2 تا B5 در شیت ۲۰۱۵ است. به کمک دکمه‌های موجود در کنار نوار آرگمان Number 1 و Number2، لیست قیمت‌ها را انتخاب می‌کنیم. بعد از انتخاب، آدرس هر لیست در نوار مربوط نوشته می‌شود.

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

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

استفاده از ویزارد Insert Function شاید گاهی کار را آسان‌تر کند، اما اگر تابع مورد نیاز خود را بشناسید، استفاده از این ویزارد تنها کار را طولانی می‌کند.

اگر نام و سینتکس تابع مدنظر خود را از پیش بشناسید، می‌توانید به سادگی پس از انتخاب سلول مورد نظر و تایپ علامت تساوی، تابع را وارد کنید. در این روش نیز برای مثال، از تابع میانگین AVERAGE استفاده می‌کنیم. بعد از علامت تساوی با تایپ ابتدای نام تابع، اکسل لیستی از تمام توابعی نمایش می‌دهد که نام آن‌ها با حروف مشابه آغاز می‌شود. در این لیست می‌توانید به سادگی تابع AVERAGE را پیدا کنید. می‌توانید نام تابع را کاملاً به طور دستی نیز وارد کنید.

بعد از وارد کردن تابع، اکسل سینتکس آن را به شما نشان می‌دهد. طبق این سینتکس، می‌توانید بعد از پرانتز مقادیر یا آدرس سلول‌های مد نظر را وارد کنید. در این مثال لیستی از قیمت میوه‌ها در دو صفحه جداگانه داریم. می‌توانیم آدرس هر لیست را به صورت دستی وارد کنیم یا به سادگی با نشانگر موس لیست را انتخاب کنیم. همان‌طور که ملاحظه می‌کنید، آرگومان‌ها با علامت «, » از یکدیگر جدا شدند. نام صفحه بین دو علامت «' » آمده است و با یک علامت «! » ، از آدرس سلول‌های B1:B5  جدا شده است.

بعد از تکمیل فرمول پرانتز را ببندید و Enter را بزنید تا نتیجه را مشاهده کنید.