جمع در اکسل

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

اگر می‌خواهید سریع از سلول‌های خاصی را در اکسل جمع بکنید، می‌توانید آن سلول‌ها را به سادگی انتخاب کنید و به نوار وضعیت در گوشه‌ی پایین سمت راست پنجره‌ی اکسل خود نگاه کنید:

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

• نحوه‌ی جمع کردن در اکسل با استفاده از یک محاسبه‌ی حسابی ساده
• تابع SUM اکسل
• جمع خودکار (AutoSum) در اکسل
• نحوه‌ی جمع یک ستون در اکسل
• نحوه‌ی جمع کردن ردیف‌ها در اکسل
• Total Row اکسل برای جمع داده‌ها در یک جدول
• نحوه‌ی جمع تنها سلول‌های فیلترشده (قابل رؤیت)
• نحوه‌ی محاسبه‌ی جمع تجمعی
• نحوه‌ی جمع کردن میان شیت‌ها
• جمع شرطی اکسل
• جمع اکسل کار نمی‌کند – رفع و راه حل

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

در صورت نیاز به جمع سریع چندین سلول، می‌توانید از مایکروسافت به عنوان ماشین حساب کوچک استفاده کنید. تنها از یک عملگر علامت به‌علاوه (+)مانند یک عملیات حسابی معمولی استفاده کنید. مثلاً:

=1+2+3

یا

=A1+C1+D1

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

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

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

اولین آرگومان اجباری است، سایر اعداد اختیاری هستند و می‌توانید تا 255 عدد را در یک فرمول واحد ارائه کنید.

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

=SUM(A1:A100)

=SUM(A1, A2, A5)

=SUM(1,5,-2)

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

=SUM(A2:A4, A8:A9)

=SUM(A2:A6, A9, 10)

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

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

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

=IF(AND($B2<“”, $C2<>””, $D2<>””), SUM($B2:$D2), “Value missing”)

نحوه‌ی جمع خودکار (AutoSum) در اکسل

در صورت نیاز به جمع کردن بازه‌ای از اعداد، اعم از ستون، سطر یا چندین ستون یا سطر مجاور، می‌توانید به مایکروسافت اکسل اجازه دهید یک فرمول SUM مناسب برای شما بنویسد.
به سادگی یک سلول را در کنار اعدادی که می‌خواهید اضافه کنید، انتخاب کنید، روی AutoSum در تب Home، در گروه group، کلیک کنید و کلید Enter را فشار دهید؛ فرمول Sum به طور خودکار درج خواهد شد:

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

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

برای جمع کردن اعداد در یک ستون خاص، می‌توانید از تابع SUM یا ویژگی AutoSum استفاده کنید.

به عنوان مثال، برای جمع کردن مقادیر در ستون B، مثلاً در سلول‌های B2 تا B8، فرمول SUM زیر را وارد کنید:

=SUM(B2:B8)

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

اگر ستونی را که می‌خواهید جمع کنید تعداد متغیری ردیف دارد (یعنی سلول‌های جدید می‌توانند اضافه شوند و سلولهای موجود در هر زمان حذف شوند)، می‌توانید کل ستون را با ارجاع به یک ستون جمع کنید، بدون مشخص کردن یک حد پایین یا بالا. مثلاً:

=SUM(B:B)
توجه مهم! به هیچ وجه نباید فرمول «جمع یک ستون» خود را در همان ستون مورد نظر قرار دهید زیرا این امر یک ارجاع سلول دایره‌وار ایجاد می‌کند (یعنی جمع جمع بازگشتی بی‌پایان) و فرمول جمع شما 0 را بازمی‌گرداند.

جمع ستون به جز عنوان (header) یا به جز چند ردیف اول

معمولاً، با ارجاع به یک ستون در فرمول Sum اکسل، جمع کل ستون را بدون در نظر گرفتن عنوان (header) محاسبه می‌کند همان‌طور که تصویر بالا نشان می‌دهد. اما در بعضی موارد، عنوان ستون مورد نظر شما که می‌خواهید جمع کنید، می‌تواند در واقع عددی در آن داشته باشد. یا، ممکن است بخواهید چند ردیف اول با اعدادی را که به داده‌هایی که می‌خواهید جمع کنید مرتبط نیستند، استثنا کنید.

متأسفانه، مایکروسافت اکسل یک فرمول SUM مختلط با یک مرز واضح پایین اما بدون حد بالایی را مانند =SUM(B2:B) که در Google Sheets خوب کار می‌کند، نمی‌پذیرد. برای استثنا کردن چند ردیف اول از جمع کردن، می‌توانید از یکی از راه حل‌های زیر استفاده کنید.

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

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

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

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

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

  =SUM(B2:B1048576)

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

  =SUM(B2:B655366)

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

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

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

=SUM(B2:D2)

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

برای جمع کردن مقادیر در هر سطر به صورت جداگانه، کافی است فرمول Sum خود را بکشید. نکته‌ی اصلی استفاده از ارجاع‌های سلول نسبی (بدون $) یا مختلط است (جایی که علامت $ فقط ستون‌ها را برطرف می‌کند). مثلاً:

=SUM($B2:$D2)

برای جمع کردن مقادیر در یک بازه‌ی دارای چندین ردیف، به سادگی بازه‌ی مورد نظر را در فرمول Sum مشخص کنید. مثلاً:

=SUM(B2:D6) – مقادیر ردیف‌های 3 تا 6 را جمع می‌کند.

=SUM(B2:D3, B5:D6) – مقادیر ردیف‌های 2، 3، 5 و 6 را جمع می‌کند.

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

برای جمع کردن کل ردیف با تعداد نامحدودی از ستون‌ها، یک ارجاع به کل ردیف به فرمول Sum خود ارائه دهید، به عنوان مثال:

=SUM(2:2)

لطفاً به یاد داشته باشید که برای جلوگیری از ایجاد یک ارجاع دایره‌وار، نباید آن فرمول “مجموع یک ردیف” را در سلولی از همان سطر وارد کنید ، زیرا این امر در صورت وجود یک نتیجه با محاسبه‌ی اشتباه می‌دهد:

برای جمع کردن سطرها به استثنای ستون(های) خاص، کل ردیف را جمع کنید و سپس ستون‌های بی‌ربط را تفریق کنید. به عنوان مثال، برای جمع کردن ردیف 2 به جز 2 ستون اول، از فرمول زیر استفاده کنید

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

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

اگر داده‌های شما در یک جدول اکسل سازماندهی شده‌است، می‌توانید از ویژگی ویژه Total Row بهره ببرید که می‌تواند به سرعت داده‌های موجود در جدول شما را جمع کند و جمع بندی‌ها را در ردیف آخر نشان دهد.

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

برای تبدیل دامنه‌ی معمولی از سلول‌ها به یک جدول، آن را انتخاب کرده و میانبر Ctrl + T را فشار دهید (یا روی Table در تب Insert کلیک کنید)

نحوه‌ی اضافه کردن total row در جداول اکسل

پس از تنظیم داده‌های خود در یک جدول، می‌توانید total row را به این روش وارد کنید:

1. برای نمایش تب Table Tools با Design، در نقطه‌ای از جدول کلیک کنید.
2. در تب Design، در گروه Table Style Options، تیک Total Row را بزنید:
   

روش دیگر برای اضافه کردن total row در اکسل، کلیک راست بر روی سلولی درون جدول، کیک بر روی Table سپس Totals Row است.

نحوه‌ی جمع داده‌ها در جدول

وقتی total row در انتهای جدول ظاهر می‌شود، اکسل تمام تلاش خود را می‌کند تا تعیین کند که چگونه می‌خواهید داده‌ها را در جدول محاسبه کنید.

در جدول نمونه‌ی ما، مقادیر ستون D (سمت راست ستون) به طور خودکار جمع شده‌اند و مبلغ در Total Row نمایش داده می‌شود:

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

اگر می‌خواهید محاسبه‌ی دیگری انجام دهید، تابع مربوطه را از لیست کشویی مانند Average، Count، Max، Min و غیره انتخاب کنید.

اگر total row به طور خودکار برای ستونی که به آن نیاز ندارد نمایش داده شده، لیست کشویی را برای آن ستون باز کنید و None را انتخاب کنید.

توجه: هنگام استفاده از ویژگی Total Row برای جمع کردن یک ستون، اکسل با درج تابع SUBTOTAL با اولین آرگومان تنظیم‌شده روی 109، تنها مقادیر ردیف‌های قابل مشاهده را جمع می‌کند. توضیحات مفصل این عملکرد را در بخش بعدی مشاهده خواهید کرد.

اگر می‌خواهید داده‌ها را در ردیف‌های قابل مشاهده و نامرئی جمع کنید، ردیف کل را اضافه نکنید و به جای آن از یک تابع معمولی SUM استفاده کنید:

نحوه‌ی جمع تنها سلول‌های فیلترشده (مرئی) در اکسل

بعضی اوقات، برای تجزیه و تحلیل موثرتر، ممکن است نیاز به فیلتر یا پنهان کردن برخی داده‌ها در ورک‌شیت خود داشته باشید. یک فرمول معمولی Sum در این حالت کار نخواهد کرد زیرا تابع SUM تمام مقادیر موجود در بازه‌ی مشخص‌شده از جمله ردیف‌های پنهان (فیلترشده) را جمع می‌کند.

اگر می‌خواهید فقط سلول‌های قابل مشاهده را در یک لیست فیلترشده جمع کنید، سریع‌ترین راه این است که داده‌های خود را در یک جدول اکسل سازماندهی کرده و سپس ویژگی Total Row اکسل را فعال کنید. همان‌طور که در مثال قبلی نشان داده شد، انتخاب Sum در total row جدول، تابع SUBTOTAL را که سلول‌های پنهان را نادیده می‌گیرد، وارد می‌کند.

روش دیگر برای جمع سلول‌های فیلترشده در اکسل، اعمال یک AutoFilter به صورت دستی با کلیک دکمه‌ی Filter در تب Data می‌باشد؛ و سپس، خودتان یک فرمول Subtotal بنویسید.

تابع SUBTOTAL دارای سینتکس زیر است:

که در آن:

• Function_num– عددی از 1 تا 11 یا از 101 تا 111 که مشخص می‌کند کدام تابع را برای جمع جزء استفاده کند.

لیست کامل توابع را می‌توانید در این‌جا پیدا کنید. در حال حاضر، ما فقط به تابع SUM علاقه‌مندیم، که با شماره‌های 9 و 109 تعریف شده‌است. هر دو عدد ردیف‌های فیلتر شده را در نظر نمی‌گیرند. تفاوت این است که 9 سلول شامل سلول‌های مخفی‌شده به صورت دستی (یعنی راست کلیک کردن > Hide) است، در حالی که 109 آن‌ها را در نظر نمی‌گیرد.

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

• Ref1, Ref2, … – سلول‌ها یا بازه‌هایی که می‌خواهید زیرجلسه شوند. اولین آرگومان Ref اجباری، سایر موارد (حداکثر 254) اختیاری است.

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

=SUBTOTAL(109, B2:B14)

و حالا ، بیایید فقط سطرهای “Banana” را فیلتر کنیم و مطمئن شویم که فرمول Subtotal ما فقط سلولهای قابل مشاهده را جمع می کند:

نکته: می‌توانید از ویژگی AutoSum اکسل استفاده کنید تا فرمول Subtotal را برای شما به صورت خودکار وارد کند. فقط کافی است داده‌های خود را در جدول (Ctrl + T) سازماندهی کرده یا با کلیک بر روی دکمه‌ی Filter داده‌ها را به روشی که می‌خواهید فیلتر کنید. پس از آن سلولی را که بلافاصله در زیر ستون مورد نظرتان است انتخاب کنید و بر روی دکمه‌ی AutoSum روی روبین کلیک کنید. یک فرمول SUBTOTAL درج خواهد شد، که فقط سلول‌های قابل مشاهده در ستون را جمع می‌کند.

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

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

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

=SUM($B$2:B2)

نحوه‌ی جمع کردن میان شیت‌ها

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

به اصطلاح یک ارجاع 3بعدی همان چیزی است که این کار را انجام می‌دهد:

=SUM(Jan:Apr!B6)

یا

=SUM(Jan:Apr!B2:B5)
فرمول اول مقادیر موجود در سلول B6 را جمع می‌کند، در حالی که فرمول دوم بازه‌ی B2:B5 را در کلیه ورک‌شیت‌های واقع بین دو شیت مرزی که مشخص می‌کنید جمع می‌کند (Jan و Apr در این مثال):

جمع شرطی اکسل

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

به عنوان مثال، فرمول SUMIF زیر فقط آن مقادیری را در ستون B جمع می‌کند که در ستون C وضعیت “Completed” دارند:

=SUMIF(C:C,”completed”,B:B )

برای محاسبه جمع شرطی با معیارهای متعدد، از تابع SUMIFS استفاده کنید. در مثال بالا، برای دریافت کل سفارشات “Completed” با مبلغی بیش از 200 دلار، از فرمول SUMIFS زیر استفاده کنید:

=SUMIFS(B:B,C:C,”completed”,B:B, “>200” )

توضیحات بیشتر درباره‌ی سینتکسSUMIF و SUMIFS و مثال‌های بیشتر را می‌توانید در این آموزش‌ها بیابید:

• تابع SUMIF در اکسل
• تابع SUMIFS در اکسل

جمع اکسل کار نمی‌کند – دلایل و راه حل‌ها

Are you trying to add a few values or total a column in your Excel sheet, but a simple SUM formula doesn’t compute? Well, if the Excel SUM function is not working, it’s most likely because of the following reasons.

1. خطای #Name به جای نتیجه‌ی مورد انتظار ظاهر می‌شود

این ساده‌ترین خطا برای رفع است. در 99 از 100 مورد، خطای #Name بیانگر غلط نوشتن تابع SUM است.

2. بعضی از اعداد جمع نمی‌شوند

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

برای رفع این مشکل، تمام سلولهای مشکل‌ساز را انتخاب کنید، روی علامت هشدار کلیک کنید و سپس بر روی Convert to Number کلیک کنید.

3. تابع SUM مقدار 0 را برمی‌گرداند

جدا از اعدادی که به صورت متنی قالب‌بندی شده‌اند، یک ارجاع دایره‌وار منبع رایج مشکل در فرمول‌های Sum است، به خصوص هنگامی که می‌خواهید یک ستون را در اکسل جمع کنید؛ بنابراین، اگر اعداد شما به صورت عدد قالب بندی شده‌اند، اما فرمول Sum شما هنوز صفر را برمی‌گرداند، ارجاع‌های دایره‌وار موجود در شیت خود را ردیابی و رفع کنید (تب Formula > سپس Error Checking > حال Circular Reference).

فرمول Sum عدد بالاتری از آن‌چه که انتظار می‌رود برمی‌گرداند

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

5. فرمول SUM اکسل به روز نمی‌شود

هنگامی که یک فرمول SUM در اکسل حتی پس از اینکه مقادیر موجود در سلولهای وابسته را به روز کردید، به نمایش جمع قدیمی می‌پردازد، به احتمال زیاد حالت محاسبه روی Manual تنظیم شده‌است. برای رفع این مشکل، به تب Formulas بروید، روی فلش کشویی کنار گزینه‌ی Calculation Options کلیک کنید و روی Automatic کلیک کنید.

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