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

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

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

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

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

تابع ABS در اکسل

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

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

فرمول کلی:

ABS(number)

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

در مثال زیر، تابع 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)

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

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

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

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

=ABS(TODAY()-A3)

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

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

۱-SUM و ABS

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

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

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

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

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

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

۲- SUMIF و ABS

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

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

نتیجه ۷۹ است.

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

۳- SUMPRODUCT و ABS

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• روی دکمه 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:

۱- در سلول مجاور داده مورد نظر، عدد را به صورت دستی وارد کنید. در این مثال در سلول B1، عدد ۳۴ نوشته شده است.

۲- در سلول زیر آن نیز دوباره عدد هدف را وارد می کنیم، در این مثال در سلول B2، عدد ۶۷۷ نوشته شده است..

۳- دو سلول را انتخاب کنید. (B1, B2)

۴- مکان نما را در قسمت سمت راست پایین قرار دهید تا اشاره گر به یک نماد جمع تبدیل شود.

۵- کلیک کنید و بکشید تا سلول های ستون پر شوند (یا دابل کلیک کنید)

۶- روی آیکن AutoFill Options کلیک کنید.

۷- گزینه Flash Fill را انتخاب کنید.

نکته ای که باید به خاطر بسپارید این است که باید به اکسل نشان دهید که در حال تبدیل یک عدد منفی به مثبت هستید.

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

   دانلود فیلم های آموزش صفر تا صد اکسل +جزوه

   شروع به کار نرم افزار

  همه منو/تب های اکسل در نوار ریبون

   ۴۰ کلید میانبر

   نحوه تایپ شدن اتوماتیک اعداد