ده فرمول اکسل که کار روزمره را ساده می‌کند

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

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

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

قبل از شروع، سه نکته

  • هر فرمول با علامت = شروع می‌شود. بدون آن، اکسل نوشته‌ی شما را متن معمولی حساب می‌کند.
  • اگر بعد از نوشتن فرمول خطا گرفتید، شاید اکسل شما به جای ویرگول (,) از نقطه‌ویرگول (;) استفاده می‌کند. در این حالت، در مثال‌ها هر ویرگول انگلیسی را با نقطه‌ویرگول عوض کنید.
  • B2 یعنی ستون B و ردیف ۲، و B2:B10 یعنی همه‌ی خانه‌های B2 تا B10. بیشتر این فرمول‌ها در گوگل شیتس هم به همین شکل کار می‌کنند.

SUM: جمع اعداد

برای جمع چند خانه، از SUM استفاده کنید. فرمول زیر جمع خانه‌های B2 تا B10 را حساب می‌کند:

=SUM(B2:B10)

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

AVERAGE: میانگین

میانگین عددها را همین‌طور به دست می‌آورید:

=AVERAGE(B2:B10)

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

درصد: بدون هیچ تابعی

برای درصد، فرمول خاصی لازم نیست؛ فقط تقسیم می‌کنید. اگر فروش واقعی در B2 و هدف فروش در C2 باشد، فرمول زیر نسبت آن‌ها را می‌دهد:

=B2/C2

بعد خانه را با CtrlShift% روی قالب درصد بگذارید تا به جای ۰٫۸ عدد ۸۰٪ را ببینید. چند حساب دیگر هم با همین روش انجام می‌شود. بیست درصد یک عدد:

=B2*20%

افزایش یا کاهش درصدی از قیمت قدیمی (B2) به قیمت جدید (C2):

=(C2-B2)/B2

قیمت با مالیات، وقتی نرخ مالیات در خانه‌ی E1 نوشته شده است:

=B2*(1+E1)

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

IF: اگر این بود، آن

IF بر اساس یک شرط، یکی از دو جواب را می‌نویسد. فرض کنید نمره در B2 است و ۱۰ و بالاتر قبول است:

=IF(B2>=10,"قبول","مردود")

متن داخل فرمول را بین دو علامت گیومه‌ی انگلیسی (") بنویسید. برای شرط‌های چندتایی، شرط‌ها را داخل AND بگذارید. مثلاً این فرمول وقتی «قبول» می‌نویسد که هر دو نمره ۱۰ یا بیشتر باشند:

=IF(AND(B2>=10,C2>=10),"قبول","مردود")

COUNTIF: شمارش با شرط

می‌خواهید بدانید چند نفر نمره‌ی ۱۰ یا بیشتر دارند؟ COUNTIF فقط خانه‌هایی را می‌شمارد که شرط را دارند:

=COUNTIF(B2:B30,">=10")

شرط می‌تواند متن هم باشد. این فرمول می‌شمارد که در ستون C چند بار کلمه‌ی «قبول» آمده است:

=COUNTIF(C2:C30,"قبول")

SUMIF: جمع با شرط

SUMIF فقط عددهایی را جمع می‌کند که کنارشان شرط برقرار است. اگر نام کالا در ستون A و مبلغ در ستون B باشد، این فرمول جمع مبلغ فقط برای ردیف‌های «نان» را می‌دهد:

=SUMIF(A2:A30,"نان",B2:B30)

ترتیب آن همیشه این است: اول خانه‌هایی که شرط روی آن‌ها بررسی می‌شود، بعد شرط، بعد خانه‌هایی که جمع می‌شوند.

VLOOKUP و XLOOKUP: پیدا کردن مقدار

کد کالا را می‌نویسید و می‌خواهید قیمتش از جدول بیاید. فرض کنید جدول در A2:C100 است، کد کالا در ستون اول آن است و قیمت در ستون سوم:

=VLOOKUP(E2,A2:C100,3,FALSE)

یعنی مقدار E2 را در ستون اول جدول پیدا کن و از همان ردیف، ستون سوم را بده. FALSE یعنی فقط جواب دقیق قبول است. اگر اکسل شما جدید است (نسخه‌ی ۲۰۲۱ یا ۳۶۵)، XLOOKUP ساده‌تر است و مشخص می‌کند اگر چیزی پیدا نشد چه بنویسد:

=XLOOKUP(E2,A2:A100,C2:C100,"پیدا نشد")

ROUND: گرد کردن

برای گرد کردن به عدد صحیح، رقم دوم را صفر بدهید. برای دو رقم اعشار، عدد ۲ را بنویسید:

=ROUND(B2,0)

اگر رقم دوم را منفی بدهید، به جای اعشار، رقم‌های سمت چپ گرد می‌شوند. مثلاً اگر رقم دوم منفی سه باشد، قیمت به نزدیک‌ترین هزار گرد می‌شود و ۱۲۳۴۵ به ۱۲۰۰۰ تبدیل می‌شود. فرمولش این است:

=ROUND(B2,-3)

TRIM و SUBSTITUTE: تمیز کردن متن

متنی که از وب یا نرم‌افزارهای قدیمی کپی شده، معمولاً فاصله‌ی اضافی دارد. TRIM فاصله‌های ابتدا و انتهای متن را پاک می‌کند و فاصله‌های پشت‌سرهم را یکی می‌کند:

=TRIM(A2)

یک مشکل مخصوص فارسی هم هست: گاهی متن به جای «ی» و «ک» فارسی، «ي» و «ك» عربی دارد. جلوی چشم فرقی نمی‌کنند، ولی جست‌وجو، مرتب‌سازی و COUNTIF آن‌ها را دو حرف متفاوت می‌دانند. این فرمول هر دو را درست می‌کند. حرف اول هر جفت، همان حرف عربی است:

=SUBSTITUTE(SUBSTITUTE(A2,"ي","ی"),"ك","ک")

برای متن‌های بلند، اصلاح متن فارسی افزارگاه به سادگی این کار را انجام می‌دهد. همچنین نیم‌فاصله و فاصله‌های اضافی را یکجا درست می‌کند.

تاریخ: اختلاف دو تاریخ و سن

اکسل تاریخ را عدد می‌داند، برای همین تفاضل ساده کار می‌کند. اگر تاریخ شروع در A2 و پایان در B2 باشد، فرمول زیر تعداد روزها را می‌دهد:

=B2-A2

اگر خانه به جای عدد یک تاریخ نشان داد، قالب آن را روی «General» یا «Number» بگذارید. برای سال یا ماه کامل از DATEDIF استفاده کنید. حرف آخر مشخص می‌کند چه چیزی شمرده شود: "y" سال، "m" ماه و "d" روز. با این فرمول سن را از روی تاریخ تولد در A2 حساب می‌کنید:

=DATEDIF(A2,TODAY(),"y")

DATEDIF در فهرست پیشنهادهای اکسل دیده نمی‌شود، ولی کار می‌کند. یک نکته‌ی مهم هم هست: اکسل تاریخ را میلادی می‌فهمد و تاریخ شمسی را معمولاً متن حساب می‌کند. اگر تاریخ‌هایتان شمسی است، اول با اختلاف دو تاریخ و روزشمار افزارگاه فاصله‌شان را بسنجید، یا آن‌ها را به میلادی تبدیل کنید.

وقتی اکسل خطا می‌دهد

خطاها بد نیستند؛ می‌گویند کجای فرمول مشکل دارد:

  • #NAME? یعنی اسم فرمول را اشتباه نوشته‌اید، یا متن را بدون گیومه گذاشته‌اید.
  • #DIV/0! یعنی عددی را بر صفر یا بر یک خانه‌ی خالی تقسیم کرده‌اید.
  • #N/A یعنی VLOOKUP یا XLOOKUP مقداری را که دنبالش بودید در جدول پیدا نکرد.
  • #VALUE! یعنی در جایی که عدد لازم است، متن گذاشته‌اید.

آخرین نکته: وقتی فرمولی را برای ردیف‌های بعدی پایین می‌کشید، آدرس‌ها هم یکی‌یکی پایین می‌آیند. اگر می‌خواهید آدرسی ثابت بماند، مثل خانه‌ی نرخ مالیات، بعد از نوشتن آدرس F4 را بزنید تا علامت $ به آن اضافه شود. در بعضی لپ‌تاپ‌ها باید همزمان کلید Fn هم زده شود.

ابزارهای مرتبط در افزارگاه

محاسبه درصد

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

ماشین‌حساب‌ها

اصلاح متن فارسی

«ی» و «ک» عربی، نیم‌فاصله و فاصله‌های اضافی را یکجا درست کنید.

ابزارهای متن

مقالات دیگر

معرفی سایت و ابزار

پنج ابزار رایگان برای رسم فلوچارت و نمودار

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

۴ دقیقه مطالعه

هوش مصنوعی

کدام دستیار هوش مصنوعی برای چه کاری؟

دوازده دستیار هوش مصنوعی با پلن رایگان. هر کدام به چه کاری می‌آید؟ چطور بهتر سؤال بپرسیم؟ و چه چیزهایی را نباید برایشان بنویسیم؟

۵ دقیقه مطالعه

همه‌ی مقالات