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

۳۴۳ بازدید
آخرین به‌روزرسانی: ۲۶ دی ۱۴۰۲
زمان مطالعه: ۷ دقیقه
فرمول نویسی در اکسل چگونه انجام می‌ شود؟

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

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

فرمول نویسی در اکسل در واقع به معنای معادل قرار دادن محتوای یک سلول با یک فرمول است. فرمول‌ها در اکسل متشکل از عملگرها (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 کاری مشابه عملگر جمع انجام می‌دهد.

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

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

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

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

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

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

استفاده از فرمول sum در اکسل
برای مشاهده تصویر در ابعاد بزرگتر، روی آن کلیک کنید.

استفاده از فرمول 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 را بزنید تا نتیجه را مشاهده کنید.

بر اساس رای ۱ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
AblebitsGCF GlobalSpreadSheeto
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *