تابع sum در اکسل

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

در صورتی که میخواهید خیلی سریع مجموع چند سلول را بدست آورید میتوانید همه آنها را انتخاب کنید و به قسمت STATUS BAR در قسمت پایین سمت راست پنجره اکسل نگاه کنید.

برای به دست آوردن نتایج پایدارتر میتوانید از تابعSUM استفاده کنید. استفاده از این تابع بسیار ساده است. بنابراین اگر مبتدی هم باشید، در فهم مثال هایی که در زیر آمده است؛ نباید مشکلی داشته باشید.

چگونه با استفاده از یک محاسبه ریاضی ساده مجموع را بدست آوریم

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

برای مثال

=1+2+3

یا

=A1+B1+C1

با این حال، اگر شما بخواهید چند ده یا چند صد ردیف را با هم جمع کنید، یا در فرمول نام هر یک از سلول ها را بنویسید؛ ایده خوبی به نظر نمیرسد. در این شرایط، شما میتوانید از تابع SUM اکسل که برای جمع کردن یک مجموعه مشخص از اعداد طراحی شده است؛ استفاده کنید.

نحوه استفاده از تابع SUM در اکسل

این تابع، تابعی ریاضی است که مقادیر را با هم جمع میکند. ترکیب تابع SUM به شکل زیر است:

SUM(number1, [number2] ,…)

آرگومان اول ضروری است و دیگر پارامترها اختیاری هستند و میتوانید در یک فرمول 255 عدد را جمع کنید.

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

=sum(a1:a100)

=sum(a1,a2,a5)

=sum(1,5,-2)

این تابع برای جمع کردن مقادیر مختلف در محدوده های مختلف و یا ترکیب مقادیر عددی، مرجع سلول ها و … مفید است. برای مثال:

=sum(a2:a4,a8:a9)

=sum(a2:A6,A9,A10)

تصویر زیر نمونه های بیشتری از فرمول SUM را نشان میدهد:

در کاربرگ های واقعی فرمول SUM معمولاً اجزای بیشتری را شامل میشود و قسمتی از یک فرمول بزرگتر است.

برای مثال، شما میتوانید SUM را بعنوان value_if_true در فرمول IF به کار ببرید تا در صورتی که هر سه سلول در یک ردیف دارای مقدار باشند، اعدادی را به ستون های B،C،D اضافه کند و در صورتی که هرکدام از سلول ها خالی باشد هشدار دهد:

=IF(AND($B2<"",$C2<>""),SUM($B2:$C2),"مقداری از دست رفته")

برای آشنایی با تابع sumfi، تابع sumifs، تابع countif و تابع countifs مطالب زیر را مطالعه کنید:

چگونگی استفاده از SUMIF در اکسل، مثال های فرمولی برای جمع زدن مشروط سلول ها

نحوه استفاده از توابع SUMIFS و SUMIF با شرط های چندگانه – مثال های فرمولی

تابع sumif در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع sumifs در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع COUNTIF در اکسل – شمارش به شرط خالی نبودن، بزرگتر بودن، تکراری بودن یا منحصر به فرد بودن

تابع countif در اکسل – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

نحوه استفاده از توابع COUNTIF و COUNTIFS با شرط های چندگانه

تابع countifs در اکسل-قسمت اول – دوره فرمول های اساسی در اکسل (فیلم آموزشی)

تابع countifs در اکسل -قسمت دوم– دوره فرمول های اساسی در اکسل (فیلم آموزشی)

 

نحوه استفاده از Autosum در اکسل

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

در سلول بعد از اعدادی که میخواهید با هم جمع کنید، کلیک کنید، در پنجره home بر روی autosum کلیک کنید.enter را بزنید و اکسل به صورت اتوماتیک فرمول Sum را برای شما خواهد نوشت.

همانطور که در تصویر می بینید، خاصیت autosum اکسل نه تنها فرمول را برای شما مینویسد، بلکه محدوده ای را که میخواهید با هم جمع کنید را نیز انتخاب میکند. در 90 درصد موارد اکسل محدوده را به درستی انتخاب میکند. در غیر این صورت میتوانید به صورت دستی محدوده را اصلاح کنید و سپس کلید enter را بزنید.

نکته: برای استفاده سریعتر از AUTOSUM میتوانید از میانبر ALT+= استفاده کنید. فقط کافیست این دو کلید را نگه دارید و ENTER را بزنید تا به صورت اتوماتیک فرمول SUM  اجرا شود.

همچنین میتوانید از AUTOSUM برای گرفتن میانگین، شمارش، ماکزیمم ، مینیمم و … استفاده کنید.

نحوه جمع زدن یک ستون با تابع sum در اکسل

برای جمع زدن مقادیر در یک ستون هم میتوانید از تابع Sum و هم autosum استفاده کنید.

برای مثل برای جمع زدن مقادیر b2:b8 ستون b فرمول زیر را وارد کنید:

=sum(b2:b8)

مجموع کل ستون با تعداد نامحدودی از ردیف

در صورتی که ستون مورد نظر شما دارای مقادیر مختلفی از ردیف است( به این معنی که ردیف های آن حذف و ردیف های جدید اضافه میشود) میتوانید بدون مشخص کردن بالا و پایین ستون کل آن را مورد محاسبه قرار دهید. به صورت زیر:

=sum(B:B)

نکته مهم: هیچ وقت نباید مجموع یک ستون را در همان ستون قرار داد زیرا این کار باعث بوجود آمدن حلقه (circular refrence) خواهد شد و مجموع شما 0 میشود.

مجموع ستون به جز HEADER یا تفریق کردن چند ردیف

عموماً، با اضافه کردن یک ستون به عنوان مرجع به یک فرمول SUM، همانطور که در تصویر نشان داده شده است، کل آن ستون به جز heading در نظر گرفته خواهد شد. ولی در بعضی از موارد، در بعضی از heading ها اعدادی وجود دارد، یا ممکن است بخواهید تعدادی از ردیف ها را کم کنید. متأسفانه اکسل ترکیب sum  با حد پایین مشخص و حد بالای نامشخص شبیه به =sum(B1:B3)  را قبول نخواهد کرد. برای کم کردن چند ردیف اول میتوانید یکی از موارد زیر را انجام دهید.

1. کل ستون را جمع کنید و سپس مواردی را که نمیخواهید از آن کم کنید ( سلول های B1 تا B3) در مورد

=sum(B:B)-SUM(B1:B3)

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

برای مثال، برای جمع زدن ستون B بدون HEADER میتوانید از یکی از فرمول های زیر استفاده کنید:

در اکسل 2007، اکسل 2010، اکسل2013 و اکسل 2016:

=SUM(B2:B1048576)

در اکسل 2003  و پایینتر:

=SUM(B2:B655366)

نحوه جمع زدن ردیف ها با تابع sum در اکسل

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

برای مثال میتوانید برای جمع زدن سلول های B2 تا D2 از فرمول زیر استفاده کنید:

=SUM(B2:D2)

نحوه جمع زدن ردیف های چندگانه در اکسل

برای اضافه کردن تک به تک اعداد در ردیف ها میتوانید فرمول Sum را پایین بکشید. نکته کلیدی استفاده از محدوده های نسبی( بدون علامت $) یا ثابت کردن سول مرجع (وقتی که علامت $ فقط ستون را ثابت میکند). برای مثال:

=sum($B2:$D2)

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

=SUM(B2:D6)

مقادیر ردیف های 2 تا 6 را جمع میزند.

=SUM(B2:D3,B5:D6)

مقادیر ردیف های 2، 3، 5 و  6 را جمع میزند.

چگونگی جمع زدن کل یک ردیف

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

برای جمع زدن ردیف ها و استثناء کردن چند ستون میتوانید کل ردیف را جمع بزنید و سپس بعضی از ستون ها را از آن کم کنید. برای مثال، برای جمع زدن ردیف 2 و استثناء کردن دو ستون اول میتوانید از این فرمول استفاده کنید:

=SUM(2:2)-SUM(A2:B2)

استفاده از تمام ردیف های اکسل برای جمع زدن داده های یک جدول

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

برای انتقال یک محدوده معمول از اطلاعات به یک جدول، آن را انتخاب کنید و CTRL+T را بزنید.( یا بر روی TABLE در پنجره INSERT کلیک کنید).

نحوه استفاده از مجموع ردیف ها در جدول های اکسل

زمانی که داده های شما در جدول سازماندهی شده است، میتوانید به این شیوه از مجموع ردیف استفاده کنید:

1. در نقطه ای از جدول کلیک کنید تا TABLE TOOL باز شود و وارد قسمت DESIGN شوید.
2. در قسمت TABLE STYLE OPTION GROUP بر روی TOTAL ROW کلیک کنید.

شیوه دیگری که میتوانید از این خاصیت اسنفاده کنید این است که بر روی یکی از سلول ها کلیک راست کنید و بر روی TABLE>TOTAL ROW کلیک کنید.

چگونگی جمع زدن داده ها در یک جدول

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

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

برای جمع زدن مقادیر موجود در دیگر ستون ها، کافیست یک سلول مربوط به ردیف TOTAL ROW را انتخاب کنید.بر روی لیست کلیک کنید و گزینه Sum  را انتخاب کنید.

در صورتی که میخواهید محاسبات دیگری نیز انجام دهید، میتوانید عملیات مورد نظر خود را از لیست موجود انتخاب کنید.

در صورتی که total row برای ستونی فعال شده است که نیازی به آن ندارید فقط کافیست بر روی لیست کلیک کرده و گزینه none را انتخاب کنید.

توجه: زمانی که از ویژگی total row برای یک ستون استفاده میکنید، اکسل فقط اطلاعات موجود در ردیف های قابل مشاهده را نمایش میدهد، که آرگومان ابتدای آن 109 مشخص شده است، اطلاعات بیشتر در این مورد در قسمت بعد ارائه شده است.

در صورتی که میخواهید داده های موجود در ردیف های قابل مشاهده و غیر قابل مشاهده را محاسبه کنید، می بایست از تابع معمولی sum استفاده کنید و از total row  استفاده نکنید.

چگونگی استفاده از تابع Sum برای سلول های قابل مشاهده (visible) در اکسل

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

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

روش دیگر استفاده از Autofilter است. برای این کار میبایست بر روی دکمه filter در پنجره data کلیک کنید. و سپس یک فرمول subtotal بنویسید:

Subtotal(function_num,num,ref1,[ref2],…)

در این فرمول:

:function_num عددی بین 1 تا 11 یا 101 تا 111 که نشان میدهد برای subtotal از کدام یک از تابع ها استفاده شود.

شما میتوانید لیست کامل تابع ها را از support.office.com بدست آورید. فعلا ما صرفا به تابع Sum میپردازیم. که توسط شماره های 9 و 109 تعریف شده است. هر دو شماره، ردیف های فیلتر شده را استثناء میکند. تفاوت این دو شماره این است که 9  سلول هایی را شامل میشود که به صورت دستی مخفی شده اند .  (راست کلیک> hidden) ولی 109 آنها را در نظر نمیگیرد.

Ref1, ref2,…  سلول یا محدوه هایی است که میخواهید جمع بزنید. اولین آرگومان ضروری است ولی آرگومان های بعدی اختیاری هستند.

در این مثال، محدوده b2:b14 را با استفاده از فرمول در زیر آمده جمع میکنیم:

=SUBTOTAL(109,B2:B14)

اکنون فقط ردیف های مربوط به banana را فیلترکنید و مطمئن شوید که تابع Subtotal فقط سلول های قابل مشاهده را محاسبه میکند.

=subtotal(109,b2:b14)

نکته:

میتوانید از ویژگی Auto sum اکسل برای جمع زدن زیر مجموعه های خود استفاده کنید. داده های خود را به صورت جدول درآورید. یا با استفاده از دکمه filter آنها را فیلتر کنید. پس از آن، روی سلولی که بعد از محدوده مورد نظر شما قرار دارد کلیک کنید، و دکمه Auto sum را در ribbon بزنید. فرمول Subtotal ایجاد خواهد شد که فقط ستون های قابل مشاهده را جمع خواهد زد.

نحوه بدست آوردن مجموع متغیر در اکسل (مجموع تراکمی)

برای بدست آوردن یک مجموع متغیر در اکسل، از یک تابع ساده Sum و کاربرد هوشمندانه آدرس نسبی و مطلق استفاده کنید.

برای مثال در صورتی که میخواهید مجموع انباشته را در ستون B  بدست آورید، در سلول B2 این فرمول را وارد کنید و به دیگر سلول ها گسترش دهید.

=SUM($B$2:B2)

نحوه بدست آوردن تابع sum در کاربرگ های مختلف

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

برای این کار میتوانید از شیوه معروف به 3D REFFRENCE استفاده کنید.

=SUM(تیر:فروردین!B6)

یا

=SUM(تیر:فروردین!B2:B5)

 فرمول اول مقادیر را در سلول B6 با هم جمع میکند ولی فرمول دوم محدوده های B2:B6 را در کاربرگ های مختلف که در اینجا فروردین و اردیبهشت نامگذاری شده است با هم جمع میکند.

sum شرطی در اکسل

در صورتی که میبایست مقادیری را جمع کرد که دارای یک شرط خاص یا مجموعی از شرط ها باشد میتوان به ترتیب از فرمول های SUMIF و SUMIFS استفاده کرد.

برای مثال، فرمول SUMIF زیر فقط مقادیری را جمع میکند که در ستون C دارای عبارت “completed” باشند.

=SUMIF(C:C,”کامل شده”,B:B)

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

=SUMIFS(B:B,C:C,”کامل شده”,B:B,”>200000″)

توجه: تابع sumif در تمام وژن های 2003 به بعد اکسل موجود است ولی تابع Sumifs از اکسل 2007 به بعد ایجاد شده است.

دلایل کارنکردن تابع sum در اکسل | دلایل و راه حل ها

آیا در استفاده از تابع sum با مشکل مواجه شده اید؟ در صورتی که تابع sum کار نمیکند احتمالا میتواند به یکی از دلایل  زیر باشد:

1. به جای نتایج #NAME ERROR نشان داده میشود.

این خطا به سادگی حل میشود. در 99 درصد موارد این خطا  به دلیل غلط املایی در نوشتن تابع SUM ایجاد میشود.

2. تعدادی از ارقام اضافه نشده است

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

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

برای حل این مسئله، سلول های دارای مشکل را انتخاب کنید، و سپس بر روی convert to number کلیک کنید.

3. تابع sum در اکسل صفر را برمیگرداند

به غیر از اعدادی که فرمت آنها متن است، یکی دیگر از مشکلات رایج در این تابع بوجود آمدن حلقه است، به خصوص زمانی که تلاش میکنید یک ستون را در اکسل جمع بزنید. بنابراین، در صورتی که اعداد شما با فرمت عدد هستند و دوباره تابع صفر را برمیگرداند، می بایست به دنبال حلقه های بوجود آمده باشید.(formula tab>error checking> circular reffrence)

4. تابع sum در اکسل مقداری بیش از مقدار مورد انتظار را برمیگرداند

در صورتی که عدد برگردانده شده بیش از انتظار است، به یاد داشته باشید که تابع sum  علاوه بر مقادیر قابل مشاهده ، سلول های غیر قابل مشاهده را نیز مورد محاسبه قرار میدهد. همانطور که در بالا توضیح داده شد میتوانید از تابع subtotal به جای Sum استفاده کنید.

5. تابع sum در اکسل به روز رسانی نمیشود

اگر تابع شما با وجود اضافه یا کم شدن سلول ها هنوز هم همان مقدار قدیمی را نشان میدهد، به احتمال زیاد حالت محاسبه بر روی manual نتظیم شده است. برای حل این مسئله به پنجره formula بروید و بر روی فلش کنار calculate option  کلیک کنید و آن را روی حالت automatic قرار دهید.