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

قدر مطلق در اکسل چیست؟

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

تابع ABS مخفف عبارت Absolute Value است و ماموریت آن محاسبه قدر مطلق عدد ورودی است.
فرقی نمی‌کند عدد شما مثبت، منفی یا حتی حاصل یک فرمول پیچیده باشد؛ تابع ABS همیشه مقدار مثبت آن را برمی‌گرداند.

نظر مطالعه: آموزش کار با اکسل

ساختار و دستور قدر مطلق در اکسل

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

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

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

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

فکر کنید ستون A شامل چند عدد منفی و مثبت است و می‌خواهید در ستون B، قدر مطلق همه این اعداد را نمایش دهید. مرحله های کار به این صورت است:

در سلول B1، فرمول زیر را داخل کنید:

سپس کلید Enter را بزنید تا نتیجه نمایش داده شود. اکنون نشانگر موس را روی گوشه پایین سمت راست سلول B1 ببرید تا علامت کشیدن ظاهر شود. با کشیدن این علامت به سمت پایین، فرمول برای همه سلول‌های ستون B کپی می‌شود.

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

پینشهاد مطالعه: تبدیل pdf به اکسل

کاربردهای تابع قدر مطلق در اکسل

تابع ABS فقط برای حذف علامت منفی از اعداد نیست. این تابع در سناریوهای واقعی و حرفه ای اکسل کاربردهای مهمی دارد. در ادامه، رایج ترین کاربردهای تابع قدر مطلق در اکسل را به همراه مثال بازدید می‌کنیم.

تشخیص انحراف از مقدار مقصد در تحلیل کارکرد

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

مثال:

در این فرمول، مقدار A1 می‌تواند مقدار واقعی و B1 مقدار مقصد باشد. خروجی، مقدار انحراف از مقصد را به طور عدد مثبت نمایش می‌دهد.

منفعت گیری از قدر مطلق برای رتبه‌بندی بر پایه مقدار اختلاف

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

مثال:

=RANK(ABS(A1-B1), C:C)

=RANK(ABS(A1-B1), C:C)

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

محاسبه مجموع نوسان داده‌ها با SUM و ABS

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

مثال:

=SUM(ABS(A1:A10))

=SUM(ABS(A1:A10))

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

کنترل اغاز اشتباه با منفعت گیری از ABS و IF

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

مثال:

=IF(ABS(A1-B1)>10, "خارج از محدوده", "مجاز")

=IF(ABS(A1-B1)>۱۰, "خارج از محدوده", "مجاز")

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

محاسبه مجموع شرطی بدون دقت به علامت با SUMIF و ABS

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

مثال:

ابتدا در ستون کمکی، قدر مطلق داده‌ها محاسبه می‌شود:

سپس مجموع شرطی با SUMIF محاسبه می‌شود:

=SUMIF(B:B, "فروش", C:C)

=SUMIF(B:B, "فروش", C:C)

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

نظر مطالعه: آموزش تابع iferror در اکسل

منفعت گیری از قدر مطلق در فرمول‌های آرایه‌ای و تحلیلی

در فرمول های تحلیلی پیشرفته، تابع ABS زیاد تر در کنار توابعی همانند SUM، AVERAGE یا MAX منفعت گیری می‌شود تا تحلیل دقیق‌تری روی داده‌ها انجام شود.

مثال:

=MAX(ABS(A1:A10-B1))

=MAX(ABS(A1:A10-B1))

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

شناسایی داده‌های پرت در مجموعه داده‌ها

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

مثال:

=ABS(A1-AVERAGE(A:A))

=ABS(A1-AVERAGE(A:A))

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

نظر مطالعه: آموزش میانگین گرفتن در اکسل

تحلیل شرطی پیشرفته با SUMPRODUCT و ABS

ترکیب SUMPRODUCT و ABS برای تحلیل‌های حرفه‌ای و چند شرطی منفعت گیری می‌شود. این روش نیازی به ستون کمکی ندارد و همه محاسبات در یک فرمول انجام می‌شود. فکر کنید جدولی با سه ستون مبلغ تراکنش (A)، نوع تراکنش (B) و حالت (C) داریم. برای این که بخواهیم مجموع مبلغ فروش‌های تایید شده را محاسبه کنیم از فرمول زیر منفعت گیری می‌کنیم:

مثال:

=SUMPRODUCT(ABS(A:A)*(B:B="فروش")*(C:C="تایید"))

در این فرمول، فقط تراکنش‌هایی که شرایط اشکار شده (تایید) را دارند در محاسبه نهایی لحاظ خواهد شد و مقادیر منفی اثری در نتیجه ندارند. این کاربرد در گزارش‌های مالی، داشبوردهای مدیریتی و تحلیل داده‌های پیچیده زیاد رایج است.

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

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

ضرب در عدد ۱-

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

مثال:

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

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

در این روش، علامت منفی از مقدار عدد حذف می شود. این کار طبق معمول با منفعت گیری از ابزار Find and Replace با پشتیبانی کلید‌های ترکیبی Ctrl+F انجام می‌گیرد و زیاد تر برای داده‌های متنی کاربرد دارد.

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

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

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

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

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

مرحله اول: باز کردن محیط VBA

ابتدا فایل اکسل خود را باز کنید. سپس کلیدهای Alt + F11 را همزمان سختی دهید. با این کار، پنجره VBA Editor باز می شود.

مرحله دوم: ساخت یک ماژول تازه

سپس از باز شدن محیط VBA، از منوی بالا روی گزینه Insert کلیک کنید. سپس گزینه Module را انتخاب کنید. با این کار، یک ماژول تازه ساخته می شود که محل نوشتن کد ماکرو است.

مرحله سوم: داخل کردن کد ماکرو

در پنجره ماژول باز شده، کد زیر را کپی و قرار دهید:

Sub RemoveNegativeSign()
    Dim cell As Range

    For Each cell In Selection
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Value = Abs(cell.Value)
        End If
    Next cell
End Sub

Sub RemoveNegativeSign()
    Dim cell As Range

    For Each cell In Selection
        If IsNumeric(cell.Value) And cell.Value < ۰ Then
            cell.Value = Abs(cell.Value)
        End If
    Next cell
End Sub

سپس از داخل کردن کد، فایل را ذخیره کنید.

مسئله مهم این است که فایل باید با فرمت Excel Macro-Enabled Workbook (.xlsm) ذخیره شود تا ماکرو قابل اجرا باشد.

مرحله چهارم: اجرای ماکرو

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

سپس کلیدهای Alt + F8 را بزنید، نام ماکرو را انتخاب کنید و روی گزینه Run کلیک کنید. با این کار، ماکرو اجرا می‌شود و اعداد منفی به طور دائمی به مثبت تبدیل خواهند شد.

منفعت گیری از Flash Fill

Flash Fill یکی از قابلیت‌های هوشمند اکسل است که می‌تواند الگوهای ساده را تشخیص دهد. اگر نحوه تبدیل عدد منفی به مثبت را در چند سلول اول انجام دهید، اکسل می‌تواند این الگو را برای بقیه داده‌ها نظر کند.

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

سؤالات متداول درمورد تابع قدرمطلق در اکسل