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

توسط Svetlana Cheusheva ، به روز شده در 14 دسامبر 2022

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

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

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

اصول فرمول های اکسل

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

• فرمول عبارتی است که مقادیر موجود در یک سلول یا در طیف وسیعی از سلول ها را محاسبه می کند.

   

به عنوان مثال ، به جای مشخص کردن هر مقدار که باید مانند فرمول فوق خلاصه شود ، می توانید از عملکرد SUM برای اضافه کردن طیف وسیعی از سلول ها استفاده کنید: = SUM (A2: A4)

می توانید تمام توابع اکسل موجود را در کتابخانه عملکرد در برگه Formulas پیدا کنید:

400+ توابع در اکسل وجود دارد ، و تعداد آنها توسط نسخه به نسخه در حال رشد است. البته ، یادآوری همه آنها غیرممکن است ، و شما در واقع نیازی به آن ندارید. جادوگر عملکرد به شما کمک می کند تا عملکردی را که برای یک کار خاص مناسب است پیدا کنید ، در حالی که Excel Formula Intellisense به محض تایپ کردن نام عملکرد پیش از یک علامت مساوی در یک سلول ، نحو و آرگومان های عملکرد را سریع می کند:

با کلیک بر روی نام عملکرد ، آن را به یک لینک آبی تبدیل می کند ، که موضوع راهنما را برای آن عملکرد باز می کند.

نکته. لزوماً لازم نیست یک نام عملکرد را در تمام کلاه ها تایپ کنید ، Microsoft Excel پس از اتمام تایپ فرمول ، به طور خودکار از آن استفاده می کند و برای تکمیل آن کلید Enter را فشار می دهد.

10 عملکرد اصلی اکسل که قطعاً باید بدانید

آنچه در زیر آمده است ، لیستی از 10 کارکرد ساده و در عین حال واقعاً مفید است که یک مهارت لازم برای همه کسانی است که مایل هستند از یک تازه کار اکسل به یک حرفه ای اکسل تبدیل شوند.

اولین عملکرد اکسل که باید با آن آشنا شوید ، عملکردی است که عملکرد اصلی حساب اضافی را انجام می دهد:

در نحو کلیه توابع اکسل ، آرگومان محصور در [براکت های مربع] اختیاری است ، سایر آرگومان ها لازم است. به این معنی که فرمول جمع شما باید حداقل 1 عدد ، مراجعه به سلول یا طیف وسیعی از سلول ها را شامل شود. مثلا:

= SUM (B2: B6) - مقادیر را در سلولهای B2 از طریق B6 اضافه می کند.

= جمع (B2 ، B6) - مقادیر را در سلولهای B2 و B6 اضافه می کند.

در صورت لزوم ، می توانید محاسبات دیگری را در یک فرمول واحد انجام دهید ، به عنوان مثال ، مقادیر را در سلولهای B2 از طریق B6 اضافه کنید ، و سپس مبلغ را به 5 تقسیم کنید:

به طور خلاصه با شرایط ، از عملکرد SUMIF استفاده کنید: در آرگومان 1 ، شما وارد طیف سلولهایی می شوید که در برابر معیارها (A2: A6) آزمایش می شوند ، در آرگومان 2 - خود معیارها (D2) و در آخرین آرگومان- سلول ها به جمع (B2: B6):

= sumif (a2: a6 ، d2 ، b2: b6)

در برگه های اکسل شما ، فرمول ها ممکن است چیزی شبیه به این باشند:

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

منابع مفید:

• - فرمول های کل یک ستون ، ردیف ها ، فقط سلولهای فیلتر شده (قابل مشاهده) یا جمع در ورق ها.- سریعترین راه برای جمع آوری ستون یا ردیف اعداد.- نمونه های فرمول برای جمع بندی سلولهای مشروط.- نمونه های فرمول برای جمع کردن سلولها بر اساس معیارهای متعدد.

میانگین

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

با نگاهی دقیق تر به فرمول از بخش قبلی (= جمع (B2: B6)/5) ، در واقع چه کاری انجام می دهد؟مقادیر سلولهای B2 تا B6 را مبالغ می کند ، و سپس نتیجه را با 5 تقسیم می کند و شما با اضافه کردن گروهی از اعداد و سپس تقسیم مبلغ به تعداد آن اعداد ، چه می نامید؟بله ، به طور متوسط!

عملکرد متوسط اکسل این محاسبات را در پشت صحنه انجام می دهد. بنابراین ، به جای تقسیم مبلغ براساس تعداد ، می توانید این فرمول را در یک سلول قرار دهید:

به طور متوسط سلولهای مبتنی بر شرایط ، از فرمول زیر استفاده کنید ، جایی که A2: A6 دامنه معیارها است ، D3 معیارها است و B2: B6 سلول ها به طور متوسط هستند:

= متوسط (A2: A6 ، D3 ، B2: B6)

منابع مفید:

• - سلولهای متوسط با تعداد.- به طور متوسط سلولها با هرگونه داده (اعداد ، مقادیر بولی و متن) پیدا کنید.- سلولهای متوسط بر اساس یک معیار.- سلولهای متوسط بر اساس معیارهای متعدد.

حداکثر و دقیقه

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

= حداقل (B2: B6)

منابع مفید:

• - بالاترین مقدار را پیدا کنید.- با شرایط بیشترین تعداد را دریافت کنید.- بر اساس معیارهای متعدد ، بزرگترین مقدار را دریافت کنید.- کوچکترین مقدار را در یک مجموعه داده برگردانید.- کمترین تعداد را بر اساس یک یا چندین شرط پیدا کنید.

شمارش و شمارش

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

در حالی که عملکرد شمارش فقط با سلولهای حاوی اعداد سروکار دارد ، عملکرد COUNTA تمام سلولهایی را که خالی نیستند ، شمارش می کند ، خواه شامل اعداد ، تاریخ ، زمان ، متن ، مقادیر منطقی واقعی و نادرست ، خطاها یا رشته های متن خالی باشد ("" "):

به عنوان مثال ، برای یافتن اینکه چه تعداد سلول در ستون B حاوی اعداد است ، از این فرمول استفاده کنید:

برای شمارش تمام سلولهای غیر خالی در ستون B ، با این یکی بروید:

در هر دو فرمول ، شما از به اصطلاح "مرجع ستون کل" استفاده می کنید (B: B) که به تمام سلولهای موجود در ستون B اشاره دارد.

تصویر زیر تفاوت را نشان می دهد: در حالی که شمارش فقط اعداد را انجام می دهد ، Counta تعداد کل سلولهای غیر خالی را در ستون B ، از جمله مقدار متن در هدر ستون ، خروجی می کند.

منابع مفید:

• - یک روش سریع برای شمارش سلول ها با اعداد.- سلولها را با هر مقدار (سلولهای غیر خالی) بشمارید.- سلولهایی را که با یک شرایط روبرو هستند ، بشمارید.- سلول ها را با چندین معیار بشمارید.

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

به عنوان مثال ، در صورت تکمیل سفارش در صورت تکمیل سفارش (یعنی مقدار در ستون C) یا خیر. برای آزمایش اینکه آیا یک سلول خالی نیست ، شما از اپراتور "برابر نیست" استفاده می کنید (<>) در ترکیب با یک رشته خالی (""). در نتیجه ، اگر سلول C2 خالی نباشد ، فرمول "بله" را برمی گرداند ، در غیر این صورت "نه":

= if (c2<>""، "بله خیر")

منابع مفید:

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

روش های مختلفی برای حذف فضاهای ناخواسته در اکسل وجود دارد که عملکرد تر و تمیز ساده ترین آن است:

به عنوان مثال ، برای برش فضاهای اضافی در ستون A ، فرمول زیر را در سلول A1 وارد کنید و سپس آن را در ستون کپی کنید:

این همه فضاهای اضافی در سلول ها را از بین می برد اما یک شخصیت فضایی واحد بین کلمات:

منابع مفید:

هر زمان که می خواهید تعداد کاراکترها را در یک سلول خاص بدانید ، LEN تابعی است که باید استفاده کنید:

آیا می خواهید دریابید که چند شخصیت در سلول A2 وجود دارد؟فقط فرمول زیر را در سلول دیگری تایپ کنید:

لطفاً بخاطر داشته باشید که عملکرد اکسل لن کاملاً همه شخصیت ها از جمله فضاها را به حساب می آورد:

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

منابع مفید:

و یا

این دو کارکرد منطقی محبوب برای بررسی معیارهای متعدد هستند. تفاوت در این است که چگونه آنها این کار را انجام می دهند:

• و اگر همه شرایط برآورده شود ، در غیر این صورت نادرست است.
• یا در صورت برآورده شدن هر شرط ، صحیح است ، در غیر این صورت نادرست است.

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

به عنوان مثال ، برای بررسی نتایج آزمون در ستون های B و C و بازگشت "Pass" اگر هر دو بیشتر از 60 هستند ، "در غیر این صورت شکست" ، از فرمول زیر استفاده کنید اگر فرمول با یک تعبیه شده و بیانیه:

=IF(AND(B2>60, B2>60) ، "پاس" ، "شکست")

اگر کافی است فقط یک نمره آزمون بیشتر از 60 (یا آزمون 1 یا آزمون 2) داشته باشید ، یا بیانیه را جاسازی کنید:

=IF(OR(B2>60, B2>60) ، "پاس" ، "شکست")

منابع مفید:

هماهنگ کردن

در صورتی که می خواهید از دو یا چند سلول مقادیر بگیرید و آنها را در یک سلول ترکیب کنید ، از اپراتور Concatenate (&) یا عملکرد Concatenate استفاده کنید:

به عنوان مثال ، برای ترکیب مقادیر سلول های A2 و B2 ، فقط فرمول زیر را در یک سلول متفاوت وارد کنید:

برای جدا کردن مقادیر ترکیبی با یک فضا ، شخصیت فضایی ("") را در لیست آرگومان ها تایپ کنید:

= concatenate (a2 ، "" ، b2)

منابع مفید:

• - نمونه های فرمول برای ترکیب رشته های متنی ، سلول ها و ستون ها.- عملکرد جدیدتر و بهبود یافته برای ترکیب محتویات چندین سلول در یک سلول.

امروز و اکنون

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

= امروز () برای وارد کردن تاریخ امروز در یک سلول.

= اکنون () برای وارد کردن تاریخ و زمان فعلی در یک سلول.

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

منابع مفید:

• - روش های مختلف برای وارد کردن تاریخ و زمان فعلی در اکسل: به عنوان یک تمبر زمان غیرقابل تغییر یا تاریخ و زمان به روز به روز.- فرمول هایی برای تبدیل تاریخ به متن و برعکس ، استخراج یک روز ، ماه یا سال از تاریخ ، تفاوت بین دو تاریخ و موارد دیگر را محاسبه کنید.

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

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

اعداد را در نقل قول های مضاعف محصور نکنید

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

به عنوان مثال ، برای بررسی مقدار در سلول B2 و بازگشت 1 برای "گذشت" ، 0 در غیر این صورت ، فرمول زیر را ، مثلاً در C2 قرار دهید:

فرمول را روی سلولهای دیگر کپی کنید و ستونی از 1 و 0 را خواهید داشت که می توان بدون هیچ مشکلی محاسبه کرد.

حال ، ببینید اگر شماره ها را دو برابر کنید ، چه اتفاقی می افتد:

در نگاه اول ، خروجی طبیعی است - همان ستون 1 و 0. با این حال ، با توجه به نگاه دقیق تر ، متوجه خواهید شد که مقادیر حاصل به طور پیش فرض در سلول ها به سمت چپ تراز شده اند ، به این معنی که این رشته ها هستند ، نه اعداد! اگر بعداً کسی سعی کند آن 1 و 0 را محاسبه کند ، ممکن است در نهایت موهای خود را بیرون بکشد و سعی کند بفهمد چرا یک مبلغ 100 ٪ صحیح یا فرمول شمارش چیزی جز صفر باز نمی گرداند.

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

لطفاً این قانون ساده را بخاطر بسپارید: اعدادی که به فرمول های اکسل شما ارائه می شود باید بدون هیچ گونه قالب بندی مانند جداکننده اعشاری یا علامت دلار وارد شوند. در آمریکای شمالی و برخی دیگر از کشورها ، کاما جداکننده استدلال پیش فرض است و از علامت دلار ($) برای ارجاع سلول های مطلق استفاده می شود. استفاده از آن شخصیت ها در اعداد فقط ممکن است اکسل شما را دیوانه کند :) بنابراین ، به جای تایپ 2000 دلار ، به سادگی 2000 را تایپ کنید ، و سپس با تنظیم یک فرمت شماره اکسل ، مقدار خروجی را به دلخواه خود قالب دهید.

مطابقت با همه افتتاحیه و بسته شدن پرانتز

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

به جای تایپ مجدد ، همان فرمول را در سلولهای دیگر کپی کنید

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

توجه داشته باشید. پس از کپی کردن فرمول ، اطمینان حاصل کنید که تمام منابع سلول صحیح است. منابع سلولی بسته به اینکه مطلق هستند (تغییر نمی کنند) یا نسبی (تغییر) ممکن است تغییر کنند.

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

نحوه حذف فرمول ، اما مقدار محاسبه شده را نگه دارید

هنگامی که فرمول را با فشار دادن کلید حذف حذف می کنید ، یک مقدار محاسبه شده نیز حذف می شود. با این حال ، شما می توانید فقط فرمول را حذف کرده و مقدار حاصل را در سلول نگه دارید. در اینجا چگونه:

• تمام سلول ها را با فرمول های خود انتخاب کنید.
• برای کپی کردن سلولهای انتخاب شده ، Ctrl + C را فشار دهید.
• Right-click the selection, and then click Paste Values>مقادیر برای چسباندن مقادیر محاسبه شده به سلولهای انتخاب شده. یا ، میانبر ویژه خمیر را فشار دهید: Shift+F10 و سپس v.

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

اطمینان حاصل کنید که گزینه های محاسبه به صورت خودکار تنظیم شده است

If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the Calculation Options somehow switched to Manual . To fix this, go to the Formulas tab>گروه محاسبه ، روی دکمه گزینه های محاسبه کلیک کنید و Automatic را انتخاب کنید.

اگر این کمکی نمی کند ، این مراحل عیب یابی را بررسی کنید: فرمول های اکسل کار نمی کنند: رفع و راه حل.

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