آموزش کامل فرمول نویسی در اکسل : مبتدی تا پیشرفته

آموزش کامل فرمول نویسی در اکسل : مبتدی تا پیشرفته

برنامه اکسل (Excel) در مجموعه نرم افزار های مایکروسافت آفیس، یکی از برنامه های پرکاربرد و استفاده می باشد. یکی از ویژگی هایی که این برنامه را قوی تر می کند، قابلیت استفاده از فرمول است. اگر شما هم نیاز به استفاده از فرمول در اکسل دارید، با ما همراه باشید. امروز در انزل وب روش کامل فرمول نویسی در اکسل را آموزش خواهیم داد.

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

How to Write and Use Formulas in Microsoft Excel

قابلیت فرمول نویسی در اکسل در زمینه های مختلف بسیار کاربردی و کمک کننده است. شما می توانید از فرمول های مختلفی استفاده کنید که برخی از آن ها پیشفرض در خود اکسل وجود دارند. برای شروع فرمول نویسی در اکسل باید ابتدا = را داخل خانه ای که می خواهید نتیجه در آن نشان داده شود، تایپ کنید و بعد فرمول مورد نظر را بنویسید و اینتر کنید. به همین سادگی فرمول به اکسل اضافه می شود که در ادامه دقیق تر با فرمول نویسی در اکسل ۲۰۰۳ ، ۲۰۱۰ ، ۲۰۱۳ ، ۲۰۱۶ و..  و روش های بیشتر اضافه کردن آنها آشنا می شویم.

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

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

سپس نماد مساوی یعنی = را تایپ کنید. حال یک معادله ثابت ساده را تایپ کنید. به طور مثال از فرمول ثابت تفریق =۱۰۰-۵۰ استفاده می کنیم و پس از اتمام فرمول نویسی در اکسل، اینتر را بزنید. به همین سادگی کار انجام می شود.

اگر با خطای #VALUE در هنگام فرمول نویسی مواجه شدید، مطلب ۶ روش رفع مشکل VALUE و نوشتن فرمول در اکسل را هم مطالعه نمایید.

نحوه فرمول نویسی در اکسل برای معادله های مرجع دار (رفرنس)

بر خلاف نوعی قبلی که از مقادیر ثابت استفاده می شود، می توانیم به جای وارد کردن اعداد مستقیم، از شماره سلول به عنوان فرمول نویسی پیشرفته در اکسل استفاده کنیم. به عبارت دیگر به جای اینکه از عدد موجود در آن خانه استفاده کنیم، از شماره خانه استفاده می کنیم. مزیت این کار این است که در صورت تغییر مقادیر، دیگر نیازی به تغییر فرمول نیست. به طور مثال می خواهیم محتوای خانه B2 را از خانه A2 کم یا تفریق کنیم. برای این کار در خانه مورد نظر فرمول را به شکل زیر می نویسیم و اینتر می کنیم.

=A2-B2

نحوه فرمول نویسی در اکسل برای معادله های مرجع دار (رفرنس)

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

نحوه فرمول نویسی در اکسل برای معادله های مرجع دار (رفرنس)

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

آموزش فرمول نویسی در اکسل، ویرایش فرمول در اکسل

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

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

برای ویرایش این فرمول فقط کافیست روی باکس فرمول کلیک کنید و آن را تغییر دهید.

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

پس از ایجاد تغییرات اینتر را بزنید تا نتیجه جدید نشان داده شود.

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

فرمول نویسی در اکسل ۲۰۱۳ و.. ، کپی و پیست کردن فرمول در اکسل

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

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

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

شما می توانید برای هر سلول یا چند سلول یک نام انتخاب کنید و در فرمول های اکسل خود از آن نام استفاده کنید. ساده ترین راه این کار انتخاب سلول و وارد کردن نام در باکس Name است. به طور مثال می خواهیم نامی برای سلول A2 تعریف کنیم. آن خانه را انتخاب می کنیم و نام مورد نظر را در باکس اسم وارد می کنیم و می توانید این کار را برای گروهی از خانه ها هم انجام دهید.

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

البته این کار روش حرفه ای تری هم دارد. برای استفاده از این روش به تب Formulas بروید و از بخش Defined names group استفاده کنید. می توانید از میانبر Ctrl+F3  هم استفاده کنید. سپس نام ها را مشخص کنید. در این مثال نام سلول A2 را revenue یا درآمد گذاشته ایم و نام سلول B2 را expenses یا هزینه ها انتخاب می کنیم.

در نهایت برای نوشتن فرمولی برای محاسبه درآمد خالص ، کافیست در خانه مورد نظر برای دیدن نتیجه فرمول =revenue-expenses را تایپ کنیم.

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

به همین روش می توانید از آرگومان ها در فرمول اکسل هم استفاده کنید. به طور مثال شما نام خانه های A2:A100 یعنی از A2 تا A100 را ۲۰۱۵_sales تعیین می کنید. اگر بخواهید مجموع اعداد این خانه ها را حساب کنید، فقط کافیست تا فرمول =SUM(2015_sales) را تایپ کنید. البته همچنان می توانید این نتیجه را با فرمول =SUM(A2:A100) هم ببینید. استفاده از نام در فرمول بیشتر برای قابل درک تر کردن آن ها استفاده می شود. همچنین این موضوع در پروژه های بزرگ برای سرچ کردن بهتر می باشد.

همینطور برای آشنایی با روش های جستجو، پیشنهاد می کنیم مطلب آموزش ۵ روش جستجو ساده، پیشرفته و حرفه ای در اکسل را هم مطالعه نمایید.

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

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

فرمول نویسی در اکسل با توابع و با روش Function Wizard

در این روش برای آشنایی با فرمولهای اکسل با مثال، ابتدا به تب Formulas بروید و در Function Library group گزینه Insert Function را بزنید. می توانید از دسته بندی های نشان داده شده هم استفاده کنید و فرمول مورد نظر را انتخاب کنید.

فرمول نویسی در اکسل با توابع و با روش Function Wizard

به طور مثال از دسته بندی های موجود در Auto Sum می توانید تابع های عادی و پر استفاده ریاضی مانند فرمول جمع اکسل، فرمول میانگین در اکسل ، ترکیب دو فرمول در اکسل و.. را پیدا کنید.

فرمول نویسی در اکسل با توابع و با روش Function Wizard

البته قابل ذکر است در روشی دیگر بعد از زدن علامت = می توانید در منوی کشویی Functions در سمت چپ صفحه یکی از ده فرمول استفاده شده را انتخاب کنید یا با زدن  More Functions… به همان لیست فرمول اکسل اشاره شده انتقال داده شوید.

فرمول نویسی در اکسل با توابع و با روش Function Wizard

زمانی که Function wizard نشان داده می شود، اگر نام تابع را می دانید، آن را تایپ کرده و روی GO کلیک کنید تا نتایج جستجو و نام های مشابه نشان داده شود. در غیر اینصورت باید دسته مورد نظر را انتخاب کنید. در لیستی که تابع ها نشان داده می شود، تابع مورد نظر را انتخاب کرده و روی OK کلیک کنید. با کلیک روی هر فرمول، توضیحاتی در مورد آن نشان داده می شود.

فرمول نویسی در اکسل با توابع و با روش Function Wizard

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

فرمول نویسی در اکسل با توابع و با روش Function Wizard

پس از تکمیل اطلاعات، با زدن اینتر یا OK تابع شما اضافه می شود.

فرمول نویسی در اکسل با توابع و با روش Function Wizard

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

در این روش هم ابتدا = را تایپ کنید و نام تابع را وارد کنید. اگر دقیق نام تابع را نمی دانید، کافیست تا حروف اولیه را تایپ کنید تا اکسل خودش تابع ها را جستجو کنید و به شما نشان دهد.

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

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

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

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

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

فرمول های پرکاربرد در اکسل

در اکسل فرمول های زیادی وجود دارد. برخی از آن ها ممکن است برای شما بیشتر استفاده داشته باشند. به همین منظور لیستی از پر کاربرد ترین فرمول های اکسل را در ادامه می بینید.

  • فرمول محاسبه روز ها: برای محاسبه تعداد روز های بین دو تاریخ استفاده می شود. مثال: =DAYS(A30,A29)
  • فرمول محاسبه روز های کاری: تعداد روز های کاری (۵ روز در هفته) را محاسبه می کند. مثال: =NETWORKDAYS(A33,A34)
  • فرمول TRIM: فضا های اضافی در خانه ها را حذف می کند. مثال: =TRIM+آدرس خانه های مورد نظر

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

نظرات و پیشنهادات شما؟

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

حمایت مالی

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

مطالب مرتبط

کامل ترین راهنما مرتب سازی بر اساس دو ستون در اکسل (تصویری)
کامل ترین راهنما مرتب سازی بر اساس دو ستون در اکسل (تصویری)
آموزش تمام روش های ایجاد چک باکس در اکسل به صورت تصویری
آموزش تمام روش های ایجاد چک باکس در اکسل به صورت تصویری
آموزش بهترین و سریع ترین روش های مقایسه دو ستون در اکسل
آموزش بهترین و سریع ترین روش های مقایسه دو ستون در اکسل
11 نرم افزار جایگزین اکسل (رایگان و پولی)
۱۰ نرم افزار جایگزین اکسل (رایگان و پولی)

نظرات

  • Musa says:

    ممنونم مفید بود.

  • لگزی says:

    با سلام خانم خاکزاد
    من از بوکان باهاتون تماس می گیرم اگر امکان دارد آدرس تلگرام خودتونو برام ارسال کنید
    در ضمن وقتی عددی تحت فرمول بدست آمده چگونه می توان در اکسل ثابت کرد یعنی ازحالت فرمول خارج شوند
    مثال ما درخانه A3 جمع خانه های A1 و A2 را انجام داده ایم اگر خانه A3 را به غیر از استفاده از = و کشیدن از گزینه کپی استفاده شود عدد جمع دوخانه دیگر را به ما نشان می دهد برای ثابت کردن اعدادی که عملیات روی آنها انجام شده من هر بار اعداد را وارد ورد می کنم و بعداً آنرا دوباره وارد اکسل می کند با این کار عدد ثابت می شود آیا تابعی هست که بتواند این کار را در اکسل همزمان انجام دهد ممنون و سپاسگذارم

  • حامد says:

    سلام
    من فرمولی در اکسل نیاز دارم که بتواند مقدار سلول را که عدد مقدار مصرف می باشد را بصورت ۵ واحد ۱۰۰ تایی در ۵ نرخ پایه ضرب کند و باقی مانده بیش از ۵۰۰ واحد را در نرخ پایه ثابت ضرب کند.
    چگونه می توان عدد مقدار مصرف را بصورت ۵ واحد ۱۰۰ تایی جدا کرد.؟؟
    اعداد مقدار مصرف می تواند ۲۸۵ یا ۵۴۷ یا ۹۸۵ یا بیشتر باشد
    ممنون میشم کمک کنید
    سپاس