آموزش تابع ABS در اکسل

معرفی تابع ABS

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

برای مثال، اگر مقدار سلول A2 برابر با منفی ۲۵۰ باشد، فرمول زیر مقدار ۲۵۰ را برمی‌گرداند:

=ABS(A2)

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

کاربردهای اصلی تابع ABS

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

ساختار (Syntax)

ساختار اصلی تابع ABS به شکل زیر است:

=ABS(number)

نمایش مفهومی ساختار به زبان فارسی:

=ABS(عدد)

تابع ABS برخلاف بعضی توابع اکسل فقط یک آرگومان می‌پذیرد و نمی‌توان چند عدد را به‌صورت مستقیم به آن داد.

آرگومان‌ها

number یا عدد / آرگومان اول

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

مثال با یک عدد ثابت:

=ABS(-75)

نتیجه این فرمول برابر با ۷۵ است.

آرگومان دوم

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

=ABS(A2:A10)

در Excel ۳۶۵ و نسخه‌های دارای آرایه پویا، نتایج این فرمول در چند سلول نمایش داده می‌شوند.

مثال‌های ساده و پایه

تبدیل عدد منفی به مثبت

اگر سلول A2 شامل عدد منفی ۴۸۰ باشد، فرمول زیر عدد ۴۸۰ را برمی‌گرداند:

=ABS(A2)

استفاده از عدد مثبت

اگر عدد ورودی مثبت باشد، مقدار آن تغییر نمی‌کند:

=ABS(120)

نتیجه برابر با ۱۲۰ است.

استفاده از عدد صفر

=ABS(0)

قدر مطلق صفر برابر با صفر است.

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

فرض کنید مقدار هدف در سلول B2 برابر با ۱۰۰۰ و مقدار واقعی در سلول C2 برابر با ۱۱۸۰ است. برای محاسبه اندازه اختلاف از فرمول زیر استفاده کنید:

=ABS(B2-C2)

نتیجه برابر با ۱۸۰ خواهد بود. اگر ترتیب سلول‌ها را نیز جابه‌جا کنید، نتیجه همچنان ۱۸۰ است.

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

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

=ABS(B2-A2)

این روش حتی در صورت جابه‌جا وارد شدن تاریخ شروع و پایان، نتیجه‌ای مثبت نمایش می‌دهد.

مثال‌های کاربردی و واقعی

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

اگر بودجه در B2 و هزینه واقعی در C2 ثبت شده باشد، اندازه اختلاف با فرمول زیر به دست می‌آید:

=ABS(B2-C2)

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

بررسی محدوده مجاز خطا با IF

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

=IF(ABS(B2-C2)<=5;"مجاز";"نیازمند بررسی")

اگر فاصله دو مقدار حداکثر ۵ باشد، عبارت «مجاز» نمایش داده می‌شود.

بررسی هم‌زمان اختلاف و وضعیت تأیید با AND

در این مثال، اختلاف باید حداکثر ۵ باشد و سلول D2 نیز وضعیت تأیید را داشته باشد:

=AND(ABS(B2-C2)<=5;D2="تأیید")

تنها زمانی نتیجه TRUE خواهد بود که هر دو شرط برقرار باشند.

شناسایی وضعیت فوری با OR

فرمول زیر زمانی TRUE برمی‌گرداند که اختلاف بیشتر از ۱۰۰ باشد یا سلول E2 دارای وضعیت فوری باشد:

=OR(ABS(B2-C2)>100;E2="فوری")

شمارش اختلاف‌های بزرگ با COUNTIF

تابع COUNTIF نمی‌تواند در همه نسخه‌های اکسل، تابع ABS را مستقیماً روی محدوده ورودی خود اجرا کند. روش ساده این است که ابتدا در ستون کمکی C قدر مطلق مقادیر ستون B را محاسبه کنید:

=ABS(B2)

پس از کپی‌کردن فرمول تا انتهای داده‌ها، تعداد مقادیر بیشتر از ۱۰ را با فرمول زیر بشمارید:

=COUNTIF(C2:C20,">10")

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

فرض کنید شناسه موردنظر در E2 قرار دارد، شناسه‌ها در ستون A، مقدار پیش‌بینی‌شده در ستون C و مقدار واقعی در ستون D هستند. فرمول زیر اختلاف مطلق دو مقدار را برای همان شناسه محاسبه می‌کند:

=ABS(XLOOKUP(E2,A2:A100,C2:C100)-XLOOKUP(E2,A2:A100,D2:D100))

ترکیب تابع ABS با فرمول‌های دیگر

• ترکیب با IF برای دسته‌بندی اختلاف‌ها به مجاز و غیرمجاز
• ترکیب با SUM برای جمع‌کردن قدر مطلق مقادیر
• ترکیب با SUMPRODUCT برای محاسبات آرایه‌ای سازگارتر
• ترکیب با AND برای بررسی چند شرط به‌صورت هم‌زمان
• ترکیب با OR برای پذیرش یکی از چند شرط
• ترکیب با COUNTIF و ستون کمکی برای شمارش اختلاف‌ها
• ترکیب با XLOOKUP برای محاسبه اختلاف داده‌های بازیابی‌شده
• ترکیب با ROUND برای گرد کردن نتیجه اختلاف
• ترکیب با IFERROR برای مدیریت خطاهای احتمالی

جمع قدر مطلق چند مقدار

در Excel ۳۶۵ و نسخه‌های دارای پشتیبانی مناسب از آرایه‌ها می‌توانید از فرمول زیر استفاده کنید:

=SUM(ABS(B2:B10))

در نسخه‌های قدیمی ممکن است این فرمول به ثبت آرایه‌ای نیاز داشته باشد. روش سازگارتر برای بسیاری از نسخه‌ها استفاده از SUMPRODUCT است:

=SUMPRODUCT(ABS(B2:B10))

تفاوت قدر مطلق مجموع با مجموع قدر مطلق‌ها

فرمول زیر ابتدا مقادیر را جمع می‌کند و سپس قدر مطلق نتیجه نهایی را به دست می‌آورد:

=ABS(SUM(B2:B10))

اما فرمول زیر ابتدا قدر مطلق تک‌تک مقادیر را محاسبه کرده و سپس آن‌ها را جمع می‌کند:

=SUMPRODUCT(ABS(B2:B10))

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

گرد کردن اختلاف

=ROUND(ABS(B2-C2),2)

این فرمول اختلاف مطلق را تا دو رقم اعشار گرد می‌کند.

مدیریت خطا در عملیات جست‌وجو

=IFERROR(ABS(XLOOKUP(E2;A2:A100;C2:C100));"یافت نشد")

اگر شناسه موردنظر پیدا نشود یا محاسبه با خطا روبه‌رو شود، عبارت «یافت نشد» نمایش داده خواهد شد.

خطاهای رایج و روش رفع آنها

۱. خطای VALUE به دلیل وجود متن

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

۲. خطای NAME به دلیل تایپ اشتباه نام تابع

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

۳. استفاده از چند آرگومان

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

=ABS(B2-C2)

۴. اشتباه گرفتن تغییر مطلق با درصد تغییر

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

=ABS((C2-B2)/B2)

سپس قالب سلول را روی Percentage قرار دهید. اگر مقدار اولیه صفر باشد، باید احتمال خطای تقسیم بر صفر را مدیریت کنید.

۵. خنثی شدن اعداد مثبت و منفی در SUM

اگر ابتدا اعداد مثبت و منفی را جمع کرده و سپس ABS را اجرا کنید، برخی مقادیر یکدیگر را خنثی می‌کنند. اگر هدف جمع اندازه تمام مقادیر است، از SUMPRODUCT همراه با ABS استفاده کنید.

=SUMPRODUCT(ABS(B2:B20))

۶. خطای SPILL در نسخه‌های جدید

اگر ABS را روی یک محدوده اجرا کنید، Excel ۳۶۵ تلاش می‌کند نتایج را در چند سلول نمایش دهد. در صورت اشغال بودن سلول‌های مقصد، خطای SPILL ایجاد می‌شود. محتویات سلول‌های مسیر خروجی را پاک کنید یا فرمول را به محل دیگری انتقال دهید.

۷. اشتباه در جداکننده آرگومان‌ها

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

۸. انتظار تغییر مقدار سلول اصلی

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

نکات حرفه‌ای و ترفندهای مهم

• برای خوانایی بهتر، از نام‌گذاری محدوده‌ها و ستون‌های جدول استفاده کنید.
• پیش از استفاده از ABS مشخص کنید که آیا جهت تغییر نیز برای تحلیل اهمیت دارد یا خیر.
• برای مقایسه با دامنه مجاز، ABS را همراه با IF، AND یا OR به کار ببرید.
• برای جمع قدر مطلق یک محدوده در نسخه‌های مختلف، SUMPRODUCT معمولاً انتخاب مطمئن‌تری است.
• در Excel ۳۶۵ می‌توانید ABS را مستقیماً روی یک محدوده اجرا کنید و نتایج آرایه‌ای دریافت کنید.
• برای جلوگیری از نمایش اعشار طولانی، نتیجه را با ROUND ترکیب کنید.
• برای مدیریت خطاهای جست‌وجو یا داده‌های ناقص، از IFERROR استفاده کنید.
• برای تحلیل حرفه‌ای، مقدار دارای علامت و قدر مطلق آن را در دو ستون جدا نگه دارید.
• به قالب سلول توجه کنید؛ ABS قالب عدد، درصد، تاریخ یا واحد پول را به‌صورت خودکار اصلاح نمی‌کند.
• استفاده از ABS روی تاریخ‌ها اختلاف روز را نشان می‌دهد، زیرا تاریخ‌ها در اکسل به‌صورت عدد سریال ذخیره می‌شوند.

تفاوت تابع ABS با توابع مشابه

تفاوت ABS با SIGN

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

=SIGN(A2)

تفاوت ABS با تبدیل ساده علامت

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

=A2*-1

تفاوت ABS با MAX

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

=MAX(A2:A10)

تفاوت ABS با SQRT و توان دوم

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

=SQRT(A2^2)

تفاوت ABS با ROUND

ABS علامت منفی را حذف می‌کند، در حالی که ROUND تعداد رقم‌های اعشار را کاهش می‌دهد. برای انجام هر دو کار باید این توابع را با یکدیگر ترکیب کنید.

=ROUND(ABS(A2),2)

سازگاری با نسخه‌های مختلف اکسل

تابع ABS از توابع قدیمی و پایه اکسل است و تقریباً در تمام نسخه‌های رایج پشتیبانی می‌شود.

• Excel ۲۰۰۷ و نسخه‌های جدیدتر ویندوز
• Excel ۲۰۱۰، ۲۰۱۳، ۲۰۱۶ و ۲۰۱۹
• Excel ۲۰۲۱ و Excel ۲۰۲۴
• Microsoft 365
• Excel for Mac
• Excel for the Web
• نسخه‌های موبایل اکسل

استفاده ساده از ABS در همه این نسخه‌ها یکسان است. با این حال، اجرای آن روی یک محدوده و نمایش خودکار چند نتیجه به قابلیت آرایه‌های پویا نیاز دارد که بیشتر در Microsoft ۳۶۵ و نسخه‌های جدید موجود است.

تابع XLOOKUP نیز در نسخه‌های قدیمی مانند Excel ۲۰۱۶ و ۲۰۱۹ در دسترس نیست. در این نسخه‌ها می‌توانید از VLOOKUP یا ترکیب INDEX و MATCH استفاده کنید.

سؤالات پرتکرار درباره تابع ABS

آیا ABS همه اعداد را مثبت می‌کند؟

بله، عدد منفی را مثبت می‌کند، عدد مثبت را بدون تغییر نگه می‌دارد و برای صفر نیز مقدار صفر را برمی‌گرداند.

آیا ABS علامت سلول اصلی را تغییر می‌دهد؟

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

آیا می‌توان چند عدد را مستقیماً به ABS داد؟

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

چگونه اختلاف دو عدد را همیشه مثبت نمایش دهیم؟

تفریق دو مقدار را داخل تابع ABS قرار دهید:

=ABS(A2-B2)

آیا ABS مقدار را گرد می‌کند؟

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

آیا ABS برای درصدها قابل استفاده است؟

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

چگونه اختلاف درصدی را بدون علامت منفی محاسبه کنیم؟

=ABS((B2-A2)/A2)

در این فرمول A2 مقدار اولیه و B2 مقدار جدید است.

چرا نتیجه ABS با انتظار من متفاوت است؟

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

جمع‌بندی و پیشنهاد یادگیری بعدی

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

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

پس از یادگیری ABS، پیشنهاد می‌شود توابع IF، SUMPRODUCT، ROUND، SIGN، AND، OR، IFERROR و XLOOKUP را یاد بگیرید. ترکیب این توابع به شما کمک می‌کند گزارش‌های دقیق‌تر، کنترل خطای حرفه‌ای‌تر و تحلیل‌های کاربردی‌تری در اکسل ایجاد کنید.