آموزش قدر مطلق در اکسل به زبان ساده (گام به گام)

رتبه: 5 ار 3 رای SSSSS

نویسنده: تیم تولید محتوا زمان مطالعه 7 دقیقه

بسیاری از افراد در صفحات اکسل روی اعداد کار می کنند. انواع مختلفی از اعداد در اکسل وجود دارد، مانند: اعداد مثبت، منفی، اعشار، تاریخ/زمان و …. تبدیل فرمت اعداد به یکدیگر یکی از کارهای رایج می باشد، مانند حذف علامت منفی عدد یا تبدیل عدد منفی به مثبت در محاسبات. برای انجام این عملیات از تابع ABS استفاده می شود.

در این آموزش درباره تابع ABS در اکسل صحبت می کنیم و ساختار این تایع و روش های استفاده از آن را روی مثال های مختلف توضیح خواهیم داد. در انتها نیز روش های دیگری را برای حذف علامت منفی ارائه می دهیم.

ویدیوی آموزشی

اگر به توضیحات پیشرفته تر نیاز دارید به خواندن ادامه دهید:

تابع ABS در اکسل

تابع ABS (یا ABSOLUTE) در اکسل، مقدار قدر مطلق یک عدد را محاسبه می کند، یعنی علامت عدد منفی را حذف کرده و یک عدد مثبت برمی گرداند.

این تابع روی اعداد مثبت اثری ندارد.

فرمول کلی:

ABS(number)

در این فرمول، number یک عدد یا آدرس سلولی است که می خواهید مقدار قدر مطلق آن را محاسبه کنید.

در مثال زیر، تابع ABS روی سلول های سمت چپ اعمال شده است،و نتیجه را در سلول های سمت راست مشاهده می کنید:

تابع ABS در اکسل

۱٫ روی عدد مثبت ۴۵ تاثیری نداشته است.

۲٫ برای اعداد منفی -۸۹۰, -۶۷, -۷۴ مقادیر ۸۹۰, ۶۷, ۷۴ را برگردانده است.

نکته: اعمال تابع ABS روی مقادیر غیر عددی ممکنه خطای !VALUE# را نشان دهد.

نکته: تابع ABS روی محدوده سلول ها کار نمی کند.

به عنوان مثال برای فرمول

“=SUM(ABS(A1:A10))”

احتمالا خطای !VALUE# نشان داده می شود یا مقدار درست را محاسبه نمی کند، چون A1:A10 یک محدوده است. بنابراین CTRL+ SHIFT+ ENTER را روی فرمول اعمال می کنیم تا فرمول زیر ساخته می شود:

{=SUM(ABS(A1:A10))}

نتیجه مورد نظر را برمی گرداند.

مثال تابع ABS در اکسل

محاسبه اختلاف بین دو عدد

در مثال زیر فرض کنید می خواهیم اختلاف امتیاز بین ستون های B و D محاسبه کنیم.

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

E2 =ABS(B2-D2)

Cell B2=4 and cell D2=7

محاسبه اختلاف بین دو تاریخ

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

تابع TODAY در اکسل، به طور خودکار تاریخ روز جاری را برمی گرداند. (تاریخ امروز در سلول B2 نشان داده شده است.)

محاسبه اختلاف بین دو تاریخ

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

=ABS(TODAY()-A3)

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

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

می توانید تابع ABS را با توابع دیگری مانند SUM، MAX، MIN، AVERAGE و … ترکیب کنید.

می توانید تابع ABS را با توابع دیگری مانند SUM، MAX، MIN، AVERAGE و ... ترکیب کنید.

۱-SUM و ABS

فرمول های آرایه ای در اکسل کمک می کنند تا محاسبات مختلفی روی یک محدوده (آرایه) یا ستون انجام دهیم. می توانیم از تابع آرایه ای SUM با تابع ABS برای بدست آوردن مجموع مقادیر مطلق اعداد در ستون یا ردیف استفاده کنیم.

فرض کنید در مثال زیر می خواهیم مجموع مقدار مطلق اعداد را در محدوده A1:A6 محاسبه کنیم.

سلول A7 را انتخاب کرده و فرمول

“=SUM(ABS(A2:A6))”

را وارد می کنیم. همانطور که گفتیم، تابع ABS با محدوه سلول ها کار نمی کند، پس باید بعد از وارد کردن فرمول، کلیدهای Ctrl + Shift + Enter را فشار دهیم. سپس فرمول همراه با براکت{} در اطراف آن در نوار فرمول نشان داده می شود.

SUM و ABS

مقدار ۴۴ را در سلول A7 برمی گرداند که برابر با مجموع مقدار مطلق اعداد در سلول های A1:A6 است.

۲- SUMIF و ABS

تابع SUMIF مجموع اعداد را در یک محدوده براساس شرایط خاصی محاسبه می کند.

SUMIF و ABS

در مثال زیر می خواهیم اعداد منفی در ستون B را از اعداد مثبت در ستون A کم کنیم و نتیجه نیز یک عدد مطلق باشد. از تابع ABS همراه با تابع SUMIF استفاده می کنیم:

از تابع ABS همراه با تابع SUMIF استفاده می کنیم

نتیجه ۷۹ است.

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

دو تابع SUMIF براساس شرط “بزرگتر از صفر” اعدد مثبت در ستون A (اعداد ۱۵ و ۶) و براساس شرط “کوچکتر از صفر” اعداد منفی در ستون B (عدد ۱۰۰) را با هم جمع می کند. نتایج جمع دو تابع برابر با ۷۹- است که با استفاده از تابع ABS مقدار ۷۹ برگردانده می شود.

۳- SUMPRODUCT و ABS

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

اگر فقط از تابع SUMPRODUCT استفاده کنیم، یک عدد منفی بدست خواهیم آورد

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

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

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

چند روش ساده دیگر برای تبدیل اعداد منفی به مثبت در اکسل (حذف علامت منفی) وجود دارد.

۱- ضرب در عدد ۱-

می توانید با ضرب اعداد منفی در عدد ۱-، به راحتی آنها را به اعداد مثبت تبدیل کنید.

اگر محدوده مورد نظرتان ترکیبی از اعداد مثبت و منفی است، باید مطمئن شوید که فقط اعداد منفی را ضرب می کنید.

مثال زیر را در نظر بگیرید. با استفاده از فرمول زیر می توانید یک عدد منفی را به یک عدد مثبت تبدیل کنید:

=IF(A2>0,A2,-A2)

Formula to convert negative numbers to positive numbers

تابع IF ابتدا بررسی می کند که آیا عدد مثبت است یا خیر. اگر مثبت باشد، علامت عدد تغییر نمی کند و اگر منفی باشد، در ۱- ضرب می شود و در نهایت یک عدد مثبت برمی گرداند.

۲- جایگزینی علامت منفی

می توانید از قابلیت Replace در اکسل استفاده کنید.

ابتدا باید تمام سلول های عددی را انتخاب کنید.
مسیر Home >> Editing >> Find & Select >> Replace را دنبال کنید یا کلیدهای میانبر Ctrl + H را فشار دهید.
در فیلد Find what، کاراکتر “-” را وارد کنید و در فیلد Replace with چیزی ننویسید.

روی دکمه Replace All کلیک کنید.

روی دکمه Replace All کلیک کنید.

در نهایت تمام علامت های منفی حذف می شوند.

جایگزینی علامت منفی

۳- حذف علامت منفی با ماکرو

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

ماکرو:

Sub RemoveNegativeSign()

Selection.Replace What:=”-“, Replacement:=””, LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

End Sub

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

۴- استفاده از Flash Fill

Flash Fill به شما امکان می دهد به سرعت الگوها را شناسایی کرده و سپس جایی که الگو اعمال شده، نتیجه را نشان می دهد.

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

به همین ترتیب می توانید از آن برای حذف سریع علامت منفی از یک عدد استفاده کنید در حالی که مقادیر مثبت بدون تغییر باقی بمانند.

در مثال زیر می خواهیم اعداد منفی را به اعداد مثبت تغییر دهیم.

استفاده از Flash Fill