فرمول های کاربردی در اکسل را بشناسید
اکسل همواره به عنوان یکی از مهمترین نرمافزارها در سری آفیس شناخته شده است؛ زیرا میتوان از آن به منظور انجام محاسبات بسیار پیچیده و دقیق کمک گرفت. یادگیری فرمول های کاربردی در اکسل به شما اجازه میدهد از این ابزار کاربردی به بهترین شکل ممکن استفاده کنید و از آن برای دستیابی به اهداف مدنظرتان کمک بگیرید.
این فرمولها شاکله اصلی اکسل را تشکیل میدهند و به همین دلیل از اهمیت بسیار زیادی برخوردار هستند. تنها افرادی که با آنها آشنایی کاملی دارند میتوانند اعمالی همچون جمع، مشخص کردن بزرگترین عدد، نتیجهگیری شرطی و موارد مشابه را به نحو احسن انجام دهند. تا پایان متن با ما همراه باشید تا برخی از مهمترین فرمولهای اکسل را به شما عزیزان معرفی کنیم.
۱) فرمول مخصوص جمع
اولین مورد از فرمول های کاربردی که باید به آن اشاره کنیم، SUM است که به منظور جمع کردن مقادیر موجود درون سلولها به کار گرفته میشود.
به جرئت میتوان بیان کرد که SUM مهمترین و پراستفادهترین گزینه برای انجام محاسبات ابتدایی به شمار میرود.
۲) فرمول اکسل برای میانگین
در پارهای از موارد، نیاز داریم در یک محدوده مشخص، میانگین اعداد موجود درون اکسلها را مشخص کنیم.
این کار با استفاده از فرمول AVERAGE (معادل میانگین در زبان انگلیسی) انجام میگیرد.
تنها کافی است پس از وارد کردن این عبارت، محدوده مدنظر خود را تعیین کنید تا میانگینگیری انجام شود.
۳) فرمول شمارش سلولها در شیت اکسل
یکی دیگر از فرمول های کاربردی در اکسل که باید از آن یاد کرد، فرمول COUNT است.
همانطور که از این عبارت میتوان دریافت، چنین فرمولی به منظور شمارش تعداد سلولها مورد استفاده قرار میگیرد.
توجه داشته باشید که COUNT تعداد سلولها را میشمارد و برای جمع کردن ارقام درون آنها مناسب نیست.
۴) فرمول مشخص کردن بزرگترین عدد
چنانچه به دنبال مشخص کردن بزرگترین عدد یا MAXIMUM هستید، باید از فرمولی استفاده کنید که دقیقاً با توجه به همین کلمه معرفی شده است.
فرمول MAX به شما این اجازه را میدهد به سادگی از بین هزاران عدد، بزرگترین مورد از آنها را مشخص کنید و این کار در کسری از ثانیه انجام میگیرد.
۵) فرمول مشخص کردن کوچکترین عدد
گاهی نیاز به تعیین کردن کوچکترین عدد به جای بزرگترین عدد حس میشود.
همانطور که احتمالاً تاکنون حدس زدهاید، در چنین شرایطی لازم است به جای MAXIMUM از عبارت MINIMUM و فرمول MIN کمک بگیریم.
این فرمول به سادگی کوچکترین عدد را برای ما مشخص خواهد کرد.
۶) فرمول نتیجه شرطی
وقتی صحبت از فرمول های کاربردی در اکسل به میان میآید، بسیاری از افراد به یاد IF میافتند.
این فرمول که یک نوع تابع به شمار میآيد به ما کمک میکند تا نتایج شرطی را مورد استفاده قرار دهیم.
به عنوان مثال، میتوان با کمک IF تعیین کرد که اگر نتیجه نهایی یک عدد خاص نبود، عبارت معینی را به ما نمایش دهد.
۷) فرمول جستجو در اکسل
برای جستجو کردن مقادیر خاص در سلولهای اکسل بر اساس چند پارامتر یا عامل مشخص باید به سراغ فرمولی بروید که دقیقاً جهت انجام همین امر شکل گرفته است.
این فرمول که VLOOKUP است کل سلولهای محدوده مدنظر شما را جستجو میکند و سپس مقادیر مربوطه را بر میگرداند.
۸) فرمول شمارش کاراکترها
بدون استفاده از فرمول های کاربردی در اکسل، شمردن تعداد کاراکترهایی که درون یک رشته متنی قرار دارند امری فوقالعاده دشوار است که زمان زیادی را از شما ميگیرد.
بهتر است از فرمول LEN که برای همین موضوع ساخته شده است کمک بگیرید. به عنوان مثال میتوانید در یک سلول (LEN(B17)= را تایپ کنید.
۹) فرمول حذف فضای اضافی در اکسل
در اکثر موارد، سلولهای ما به نحوی چیده شدهاند که بین آنها فضای اضافی غیرضروری مشاهده میشود.
به منظور نظم دادن به این محیط یا دی – کلاتر کردن آن میتوان از فرمول TRIM کمک گرفت.
این فرمول موجب میشود که فضای اضافی پیش و پس از یک رشته به خصوص کاملاً حذف شود.
۱۱) فرمول تاریخ در اکسل
به دنبال آن هستید که در یک سلول به خصوص از شیت اکسل، تاریخ مدنظر خود را وارد کنید؟
حتی برای این کار نیز یک فرمول تعبیه شده است که میتوانید به سراغ آن بروید.
کافی است در سلول مدنظرتان عبارت =(TODAY) را وارد کنید تا این فرمول تاریخ را به صورت میلادی برایتان ثبت کند.
۱۲) فرمول تفاوت تاریخ در اکسل
به منظور مشخص کردن فاصله زمانی بین دو تاریخ گوناگون، باید به دنبال استفاده از فرمول های کاربردی در اکسل باشید.
یکی از این فرمولها، DATEDIF نام دارد که میتواند فاصله تعیین شده بین دو دوره تاریخی مشخص را برایتان محاسبه کند و به سادگی آن را به نمایش بگذارد.
۱۳) فرمول انحراف معیار در اکسل
به جرئت میتوان بیان کرد که بخش مهمی از فرمولهای اکسل مربوط به توابع آماری هستند؛ زیرا این موارد در سازمانهای گوناگونی به کار گرفته میشوند.
از بین این دسته فرمولها میتوان به STEDV اشاره کرد که به شما در تعیین انحراف معیار کمک میکند. البته باید محدوده مدنظر خود را به درستی مشخص کنید.
۱۴) فرمول برای مشخص کردن واریانس در اکسل
از بین فرمول های کاربردی در اکسل باید به گزینهای اشاره کرد که به کمک آن میتوان واریانس مقادیر متعدد را بلافاصله تشخیص داد.
این فرمول VARIANCE است و تنها با قرار دادن آن در یک سلول، میتوانید واریانس ارقام مدنظر خود را تعیین کنید. چنین موردی در دسته توابع آماری جای دارد.
سخن پایانی
افرادی که به دنبال پیشرفت در زمینه یادگیری مجموعه آفیس هستند، باید به خوبی با «اکسل» آشنایی داشته باشند. «فرمولها» یکی از مهمترین بخشهای این نرم افزار به شمار میآیند که انجام فعالیتهای گوناگونی را ممکن میکنند. تنها با شناخت کامل این موارد میتوانید محاسبات گوناگون را به خوبی انجام دهید.
از جمله فرمول های کاربردی در اکسل میتوان به مواردی همچون SUM، AVERAGE، COUNT و… اشاره کرد. بسیار مهم است که موارد گفته شده را به صورت صحیح وارد سلولهای اکسل کنید؛ زیرا هرگونه اشتباه منجر به برهمخوردن ترکیب محاسباتی شما میشود.
با شرکت در دورههای آموزشی ما میتوانید علاوه بر این فرمولها با سایر بخشهای مجموعه آفیس آشنا شوید.
12 دیدگاه در “فرمول های کاربردی در اکسل را بشناسید”
سلام. مقاله عالی بود. من از تابع SUM زیاد استفاده میکنم، اما گاهی نیاز دارم فقط سلولهایی را جمع بزنم که شرایط خاصی دارند (مثلاً فقط فروش یک محصول خاص). آیا تابعی برای این کار هست؟
سلام مریم عزیز. بله دقیقاً! برای جمعزنی شرطی، دو تابع بسیار قدرتمند وجود دارد:
SUMIF: زمانی که فقط یک شرط دارید. مثلاً جمع کل فروشهای محصول «الف».
=SUMIF(range, criteria, [sum_range])
SUMIFS: زمانی که چندین شرط دارید. مثلاً جمع فروشهای محصول «الف» در منطقه «تهران» در ماه «فروردین».
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
این توابع از پایهایترین و کاربردیترین ابزارهای تحلیل داده در اکسل هستند که شما را از فیلتر کردن و جمعزدن دستی بینیاز میکنند.
من برای برنامهریزی جلسات در اکسل از تاریخ استفاده میکنم. آیا راهی وجود دارد که با وارد کردن تاریخ، به طور خودکار نام روز هفته (مثل یکشنبه) هم در سلول کناری ظاهر شود؟
سلام پدرام. حتماً. این کار با تابع TEXT به راحتی انجام میشود. اگر تاریخ شما در سلول A2 است، از فرمول زیر استفاده کنید:
=TEXT(A2, “dddd”)
این فرمول نام کامل روز (مثل Saturday) را برمیگرداند. اگر بخواهید نام کوتاهتر (مثل Sat) باشد، از “ddd” استفاده کنید. همچنین میتوانید آن را با تابع TODAY ترکیب کنید (=TEXT(TODAY(), “dddd”)) تا همیشه روز جاری نمایش داده شود. این یک ترفند ساده اما فوقالعاده کاربردی برای داشبوردها و برنامهریزیهاست.
با تابع IF آشنا هستم، اما گاهی شرایط پیچیدهتر است. مقاله اشاره کردید که توابع IFS و AND/OR هم هستند. لطفاً یک مثال کاربردی از زمان استفاده از هر کدام بزنید.
سلام علی. سوال بسیار خوبی است. اجازه دهید با یک مثال درجهبندی نمره (A2 حاوی نمره است) تفاوت را نشان دهیم:
IF ساده (یک تصمیم دوگانه): =IF(A2>=۱۰, “قبول”, “مردود”)
IF تو در تو (چند تصمیم پیچیده): =IF(A2>=۱۸, “عالی”, IF(A2>=۱۴, “خوب”, IF(A2>=۱۰, “متوسط”, “ضعیف”))) – این روش ممکن است گیجکننده شود.
IFS (راهحل مدرنتر و خوانا برای چند شرط): =IFS(A2>=۱۸, “عالی”, A2>=۱۴, “خوب”, A2>=۱۰, “متوسط”, TRUE, “ضعیف”) – بسیار تمیزتر است.
ترکیب IF با AND/OR (چند شرط برای یک خروجی): =IF(AND(A2>=۱۰, A2<=20), "محدوده مجاز", "خارج از محدوده") - این فرمول تنها زمانی "محدوده مجاز" را نشان میدهد که هر دو شرط (>=۱۰ و <=20) برقرار باشند.
من گزارشات ماهانه هر بخش را در شیتهای جداگانه دارم. برای تهیه گزارش کلی، چطور میتوانم دادههای مشخصی (مثل جمع فروش نهایی) را از همه این شیتها به یک شیت خلاصه «ارجاع» دهم؟
سلام سمانه. این یک چالش رایج و مهم است. سادهترین راه، استفاده از مراجع سهبعدی (3D Reference) با تابع SUM است.
فرض کنید شیتهای شما به ترتیب فروردین، اردیبهشت و خرداد نام دارند و میخواهید سلول B10 (جمع فروش) از هر سه را در شیت خلاصه جمع بزنید. فرمول در شیت خلاصه به این شکل خواهد بود:
=SUM(فروردین:خرداد!B10)
با این کار اکسل به طور خودکار سلول B10 را از همه شیتهای بین فروردین تا خرداد جمع میزند. نکته کلیدی این است که ساختار همه شیتها (موقعیت سلول مورد نظر) باید یکسان باشد. این روش برای توابع دیگر مانند AVERAGE نیز کار میکند.
برای تهیه لیست ایمیل یا آدرسهای کامل، نیاز دارم نام، نامخانوادگی و شهر را که در ستونهای مجزا هستند، در یک سلول کنار هم بیاورم. آیا راهی غیر از تایپ دستی وجود دارد؟
سلام رضا. بله، دو روش اصلی وجود دارد:
عملگر «&» (Ampersand): سادهترین راه برای ترکیب مستقیم. اگر نام در A2، نامخانوادگی در B2 و شهر در C2 باشد:
=A2 & ” ” & B2 & ” – ” & C2
نتیجه: علی رضایی – تهران
تابع TEXTJOIN (نسخههای جدیدتر اکسل): این تابع قدرتمندتر و انعطافپذیرتر است، مخصوصاً وقتی سلولهای خالی در بین دادهها دارید.
=TEXTJOIN(” – “, TRUE, A2, B2, C2)
آرگومان اول جداکننده (مثلاً فاصله یا خط تیره)، آرگومان دوم نادیده گرفتن سلولهای خالی (TRUE) و سپس سلولها را مشخص میکنید. این تابع کنترل بهتری روی خروجی نهایی میدهد.
هنگام وارد کردن دادههای بزرگ (مثل کد ملی یا شماره سفارش) نگران تکراری بودن آنها هستم. آیا اکسل میتواند به طور خودکار موارد تکراری را قبل از تایید نهایی به من هشدار دهد؟
سلام حسین. بله، شما میتوانید از اعتبارسنجی داده (Data Validation) به همراه یک فرمول شرطی برای این کار استفاده کنید. این یک روش پیشگیرانه و حرفهای است:
محدوده مورد نظر (مثلاً ستون کدها) را انتخاب کنید.
به Data > Data Validation بروید.
در زبانه Settings، از منوی Allow گزینه Custom را انتخاب کنید.
در کادر Formula، فرمول زیر را وارد کنید:
=COUNTIF($A$2:$A$100, A2)=1
(فرض کنید دادههای شما از A2 تا A100 است).
در زبانه Error Alert یک پیام هشدار مناسب بنویسید.
حالا اگر کاربر شمارهای را تایپ کند که قبلاً در محدوده وجود داشته باشد، اکسل اجازه ورود نمیدهد و پیام خطای شما را نشان میدهد. همچنین میتوانید از Conditional Formatting برای رنگی کردن موارد تکراری پس از ورود استفاده کنید.