انواع روش های جمع در اکسل – آموزش ۱۳ روش + مثال

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

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

چگونه می‌شود چند سلول را در اکسل جمع کرد؟

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

در اولین مثال، نحوه استفاده از AutoSum را برای جمع زدن اتوماتیک در اکسل می‌بینیم. برای کار با ویژگی AutoSum ابتدا اولین سلول خالی زیر محدوده مورد نظر را انتخاب می‌کنیم، در این مثال، سلول A5 را برگزیده‌ایم:

سپس در زبانه Home دکمه AutoSum را می‌زنیم یا دو کلید Alt + =  را روی کیبورد فشار می‌دهیم:

به این ترتیب، فرمول تابع SUM همراه با ارجاعاتی به سلول‌های محدوده، در سلول نتیجه نشان داده می‌شود. در مثال زیر، فرمول ما به شکل =SUM(A1:A4) است. توجه داشته باشید که اگر سلول‌ها به طور خودکار درون فرمول افزوده نشده‌اند، می‌توانید آن‌ها را به طور دستی .وارد کنید:

در نهایت دکمه Enter را فشار می‌دهیم تا فرمول کامل و اجرا شود.

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

تنظیمات تابع SUM

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

1SUM(number1, [number2],...)

وارد کردن اولین آرگومان این فرمول (Number1)، ضروری است اما باقی اختیاری هستند. همچنین می‌توانید از ارجاعات سلولی نیز به جای آرگومان‌های این تابع استفاده کنید. مثلا در مثال بالا (=SUM(A1:A4) ) آرگومان A1:A4 به شکل ارجاع سلولی نوشته شده است.

1. چگونه یک ستون اکسل را جمع کنیم؟

یکی از روش‌های ساده جمع زدن ستون در اکسل، استفاده از تابع SUM است. این تابع در کمترین زمان تمام مقادیر را با هم جمع می‌زند. برای کار با تابع Sum، روی سلولی که می‌خواهید نتیجه را در آن ببینید، کلیک کنید. سپس بعد از نوشتن علامت مساوی (=)، قسمت آغازین فرمول SUM را در آن بنویسید. بعد به کمک ماوس، ستون مورد نظر را انتخاب کنید و دکمه Enter را بزنید. تابع SUM کار خود را به سرعت انجام خواهد داد.

2. انواع روش های جمع در اکسل برای محاسبه جمع کل محدوده‌ای از سلول‌ها

با یک حرکت سریع می‌توانید جمع ستون در اکسل را محاسبه کنید و مجموع کلی داده‌های موجود در آن را به دست بیاورید. برای انجام این کار، مراحل زیر را انجام مي‌دهیم:

• تمام محدوده مورد نظر را همراه با یک ستون خالی در سمت راست و یک ردیف خالی زیر آن‌ها، انتخاب می‌کنیم. (سلول‌های A1:D5 در مثال زیر):

• سپس روی دکمه AUTOSUM در زبانه HOME بزنید. فرمول تابع SUM به طور خودکار برای ستون‌ها و ردیف‌های این محدوده اعمال خواهد شد و جمع کل آن‌ها محاسبه می‌شود.

3. انواع روش های جمع در اکسل برای محاسبه مجموع کل جاری

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

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

برای محاسبه مجموع جاری محدوده‌ای از سلول‌ها (که به عنوان جدول قالب بندی نشده‌اند)، مراحل زیر را دنبال کنید. در مثال زیر، مقادیر را در ستون C وارد کرده‌ایم و مجموع را در ستون D محاسبه می‌کنیم:

• در ابتدا، فرمول زیر را در سلول D2 وارد می‌کنیم:

1=SUM(C$2:C2)

در این فرمول از علامت $ برای نشان دادن مرجع مطلق استفاده شده است.

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

روش کار فرمول

این فرمول، ردیف 2 را به عنوان نقطه شروع در نظر می‌گیرد (C$2) و آن را تا انتها به عنوان مرجع حفظ خواهد کرد (C2). این کار اطمینان حاصل می‌کند که وقتی فرمول را به ردیف‌های پایینی گسترش می‌دهید و کپی می‌کنید، نقطه شروع تغییری نکند. در مثال زیر، فرمول در سلول D6 نوشته شده است. همانطور که می‌بینید، نقظه شروع تغییری نکرده و نقطه پایان در ردیف فعلی است (C6).

محاسبه مجموع جاری در جدول اکسل

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

مشکل فرمول محاسبه مجموع جاری

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

اما به محض اینکه یک ردیف جدید در پایین جدول ایجاد کنید، فرمول آخرین ردیف تغییر خواهد کرد. قبل از افزودن ردیف جدید، فرمول سلول D6 به شکل زیر بود:

1= SUM(C$2:C6)

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

به همین ترتیب، هر باری که ردیف جدید اضافه کنیم، فرمول آخرین ردیف تغییر می‌کند تا عدد موجود در ردیف آخر را نمایش دهد. به همین دلیل نمی‌شود از این روش برای جداول اکسل استفاده کرد.

فرمول مجموع جاری در جداول اکسل

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

• فرمول زیر را در سلول D2 وارد می‌کنیم:

1=SUM(C$1:[@واحد])

• فرمول به طور خودکار تا سلول D6 گسترش پیدا می‌کند و تمام سلول‌های ستون D فرمولی یکسان دارند.

روش کار فرمول

این فرمول برخلاف قبلی، نقطه شروع و مرجع مطلق را، سرتیتر یا هدینگ جدول در نظر می‌گیرد (C$1). به این ترتیب، اگر ردیف داده‌ای جدیدی در بالای جدول ایجاد شود، نقطه شروع در هدینگ، ثابت می‌ماند. همچنین چون مقدار این سلول هدینگ، متن است، به عنوان صفر در نظر گرفته می‌شود و تاثیری در محاسبه مجموع نخواهد داشت.

برای نقطه پایان نیز از مرجع جدولی ساختار یافته ([واحد@]) استفاده شده است. این مرجع به ستون «واحد» در ردیف جاری، اشاره دارد. به کمک این مرجع سلولی، در صورت اضافه شدن ردیف به این ستون نیز فرمول بهم نمی‌ریزد. مثال زیر نشان می‌دهد که حتی با اضافه شدن یک ردیف جدید، سلول C6 فرمول درست را نشان می‌دهد و نتیجه مجموع جاری، صحیح خواهد بود:

4. استفاده از تابع OFFSET برای جمع محدوده

اگر در مثال قبل، یک سلول جدید را مستقیما بالای تابع SUM اضافه کنید، ردیف جدید در محاسبه جمع وارد نخواهد شد. فرمول همچنان سلول‌های A1:A4 را جمع می‌زند و A5 را نادیده خواهد گرفت. برای حصول اطمینان از اینکه ردیف‌های جدید نیز در محاسبه جمع دخیل هستند، مي‌شود از تابع OFFSET به عنوان یکی دیگر از انواع روش های جمع در اکسل استفاده کرد. برای کار با این تابع، مانند مراحل زیر عمل می‌کنیم:

• سلول A5 را برمی‌گزینیم و فرمول زیر را در آن می‌نویسیم:

1   =SUM(A1:OFFSET(A5,-1,0))

• برای اجرای فرمول، دکمه Enter را می‌زنیم. سپس ردیف جدیدی در بالای ردیف 5 اضافه می‌کنیم. حالا اگر عددی در سلول A5 بنویسیم، خواهیم دید که مقدار جمع عوض می‌شود.

5. جمع شرطی با تابع SUMIF

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

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

کلیک کنید

• مطابقت دقیق با شرط.
• مطابقت در رشته متنی.
• مطابقت با استفاده از عملگرها.

تنظیمات تابع SUMIF در اکسل

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

1 SUMIF(range, criteria, [sum_range])

در این تابع، آرگومان‌های ضروری به شرح زیر هستند:

• Range: نشان‌دهنده محدوده سلول‌ها برای اعمال کردن شرط.
• Criteria: مقداری که به عنوان شرط استفاده می‌شود.

این تابع یک آرگومان دیگر نیز دارد که اختیاری است:

• [Sum_range]: سلول‌هایی که قرار است جمع شوند. اگر جای این آرگومان خالی باشد، سلول‌های Range مورد استفاده قرار می‌گیرند.

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

جمع شرطی با مطابقت کامل

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

• ابتدا سلولی را برای نمایش نتیجه انتخاب و علامت مساوی (=) را درون آن وارد می‌کنیم. سپس فرمول SUMIF را می‌نویسیم (SUMIF( ).
• در ادامه سلول‌هایی را که حاوی عبارت شرط هستند، انتخاب می‌کنیم. در این مثال شرط ما در محدوده A2:A10 قرار دارد. سپس علامت «کاما» (,) را برای جداسازی آرگومان‌ها وارد می‌کنیم.
• در مرحله بعد باید عبارت شرط را بنویسیم. در این مثال، شرط ما «مداد» است و چون متن محسوب می‌شود، آن را درون «""» قرار داده‌ایم.
• بعد دوباره کاما می‌گذاریم و در ادامه، محدوده‌ای که می‌خواهیم جمع شود را در فرمول می‌آوریم. در این مثال، جمع در محدوده B2:B10 انجام خواهد شد.
• فرمول نهایی به شکل زیر می‌شود:

1=SUMIF(A2:A10,"مداد",B2:B10)

• در نهایت، دکمه Enter را می‌زنیم تا فرمول کامل شود.

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

1=SUMIF(A2:A10,"A2",B2:B10)

جمع شرطی با مطابقت در رشته متنی

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

• ابتدا سلول نتیجه را انتخاب و تابع SUMIF را در آن می‌نویسیم.
• سپس به عنوان آرگومان اول، سلول‌های مورد نظر برای بررسی شرط را وارد می‌کنیم. در این مثال می‌شود محدوده A2:A10.
• در مرحله بعد، پس از نوشتن علامت «کاما» (,)، عبارت شرط را خواهیم نوشت. چون شرط ما متن است، آن را درون علامت نقل قول ("") قرار می‌دهیم. همچنین برای تحقق شرط مطابقت جزئی، از یک یا دو علامت ستاره (*) در اطراف شرط استفاده می‌کنیم. به این ترتیب می‌توانیم تمام سلول‌هایی که بخشی از محتوای آن‌ها «مداد» است را بیابیم.
• سپس محدوده سلول‌هایی که می‌خواهیم جمع شوند را نیز می‌نویسیم که در این مثال، B2:B10 است. در نهایت فرمول کلی به شکل زیر می‌شود:

1=SUMIF(A2:A10,"*مداد*",B2:B10)

• با زدن دکمه Enter، نتیجه نهایی مانند تصویر زیر، نشان داده خواهد شد:

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

1 =SUMIF(A2:A10,"*" & A2 & "*",B2:B10)

جمع شرطی با عملگرهای مختلف

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

مثال1: استفاده از عملگر بزرگ‌تر (<) در فرمول SUMIF

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

• ابتدا سلول نتیجه را انتخاب و قسمت اول فرمول و سپس محدوده سلول‌های مورد نظر برای جستجوی شرط (در این مثال B2:B10) را در آن وارد می‌کنیم.
• بعد از کاما، شرط را می‌نویسیم که در این مثال، مربوط به مقادیر بزرگ‌تر یا مساوی 10 است. برای این کار از عملگر «=<» استفاده خواهیم کرد. همچنین کل عبارت شرط باید درون علامت نقل قول باشد.
• سپس دوباره پس از نوشتن کاما، محدوده سلول‌های مورد نظر برای جمع (در این مثال C2:C10) را وارد می‌کنیم. فرمول کلی به شکل زیر است:

1  =SUMIF(B2:B10,">=10",C2:C10)

• بعد از فشردن دکمه Enter روی کیبورد، نتیجه مانند تصویر زیر، نشان داده می‌شود:

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

1  =SUMIF(B2:B10,">=" & D1,C2:C10)

همان‌طور که دیدید، برای برقرای ارتباط بین ارجاع سلولی و عملگر منطقی، از علامت «&» استفاده کرده‌ایم.

مثال2: محاسبه مجموع سالانه جاری با تابع SUMIF

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

• ابتدا اولین سلولی که می‌خواهیم مجموع سالانه در آن محاسبه شود را انتخاب می‌کنیم. در این مثال از سلول C2 استفاده شده است. سپس فرمول زیر را درون آن وارد می‌کنیم و دکمه Enter را می‌زنیم:

1=SUMIF(A$2:A2,">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$2:B2)

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

این فرمول تاریخ‌های ستون A را از ردیف دوم (A$2) تا ردیف جاری (A2) بررسی می‌کند:

1=SUMIF(A$2:A2

سپس تابع DATE، تاریخ 11 ماه قبل از تاریخ فعلی را محاسبه خواهد کرد:

1DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))
2

بعد عملگر «=<» تاریخ‌های بزرگ‌تر یا مساوی این تاریخ را چک می‌کند:

1">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2))
2

در نهایت فرمول، مقدار متناظر ستون B را برای ردیف‌هایی که این شرط را برآورده می‌کنند، با یک‌دیگر جمع خواهد کرد. این کار از ردیف دوم (B$2) شروع می‌شود و تا ردیف جاری (B2) ادامه دارد.

6. انواع روش های جمع در اکسل برای جمع کردن سلول‌ها با چند شرط مختلف

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

تنظیمات تابع SUMIFS در اکسل

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

1SUMIFS(range, criteria, [sum_range])

تابع SUMIFS سه آرگومان ضروری دارد:

• Sum-Range: نشان‌ دهنده محدوده سلول‌ها برای بررسی شرط.
• Criteria_range1: محدوده مورد نظر برای جستجوی اولین شرط.
• Criteria1: شرط اول.

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

• مطالب پیشنهادی برای مطالعه:
  • ساخت شجره نامه در اکسل – آموزش تصویری و ساده
  • سرچ در اکسل – آموزش تصویری و کامل روش‌ های مختلف
  • ساخت تابع در اکسل – آموزش ایجاد دستور سفارشی
  • نحوه و دستور گذاشتن عکس در اکسل – راهنمای تصویری کامل
  • تابع Count در اکسل – نحوه استفاده به زبان ساده

مطابقت با معیارهای متعدد تابع SUMIFS

در این مثال، تابع SUMIFS، مقادیر موجود در ستون D را بر اساس مطابقت با دو شرط زیر با یکدیگر جمع می‌کند:

• وضعیت در ستون B برابر «تحویل» باشد.
• تعداد واحدها در ستون C، بزرگ‌تر یا مساوی 10 باشد.

همان‌طور که در تصویر زیر می‌بینید، این دو شرط را در سلول‌های F6 (تحویل)‌ و G6 (عدد 10) نوشته‌ایم.

برای محاسبه جمع با دو شرط، باید مراحل زیر را طی کنیم:‌

• ابتدا سلول نتیجه را انتخاب و قسمت اول فرمول را در آن وارد می‌کنیم. سپس محدوده سلول‌هایی که می‌خواهیم جمع شوند را به عنوان آرگومان اول می‌نویسیم (در این مثال، D3:D10).
• بعد از نوشتن کاما، محدوده سلول‌هایی را برمی‌گزینیم که شرط اول روی آن‌ها اعمال می‌شود (در این مثال، محدوده B3:B10).
• سپس اولین شرط را که در این مثال عبارت «تحویل» در سلول F6 است، وارد می‌کنیم.
• بعد از آن باید محدوده سلول‌های مورد جستجو برای شرط دوم را بنویسیم (در این مثال محدوده C3:C10). سپس شرط دوم را همراه با عملگر بزرگ‌تر مساوی (=<) وارد می‌کنیم. فرمول نهایی مانند زیر است:

1=SUMIFS(D3:D10,B3:B10,F6,C3:C10,">=" & G6)

 7. استفاده از تابع SUMIFS با چند شرط AND/OR

در مثال زیر، از تابع SUMIFS برای جمع کردن ردیف‌هایی استفاده می‌کنیم که در آن‌ها:

• «شهر»، «شیراز» باشد.
  • و
• «دسته‌بندی»، «کیک‌» باشد.

برای انجام این کار، فرمول زیر را در سلول G9 وارد می‌کنیم:

1=SUMIFS(C2:C13,A2:A13,E3,B2:B13,E4)

فرمول با چند شرط برای AND/OR

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

• «شهر»، «تهران» و «دسته‌بندی»، «بیسکوییت» باشد.
  • یا
• «شهر»، «تهران» و «دسته‌بندی»، «کیک» باشد.

دو دسته‌بندی مورد نظر در سلول‌های G6:G7 نوشته شده‌اند.

فرمول با ورودی آرایه

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

• ابتدا سلولی که می‌خواهیم مجموع را در آن ببینیم (در این مثال سلول A15)، انتخاب می‌کنیم. سپس عبارت =SUM(SUMIFS(  را برای شروع می‌نویسیم.
• در ادامه، سلول‌هایی مورد نظر برای جمع را برمی‌گزینیم (در این مثال، محدودهC2:C13).
• بعد از قرار دادن کاما، سلول‌های مورد نظر برای جستجوی شرط اول را انتخاب می‌کنیم (در این مثال، محدوده A2:A13). سپس برای انتخاب اولین شرط، روی سلول حاوی آن (E3) کلیک خواهیم کرد.
• در این مرحله باید سلول‌های مورد نظر برای جستجوی شرط دوم (در این مثال محدوده B2:B13) را وارد کنیم. بعد از آن، روی سلولی که شرط دوم درون آن است (E4) کلیک می‌کنیم.
• انتهای فرمول را با دو پرانتز می‌بندیم. فرمول نهایی به شکل زیر است:

1=SUM(SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6:G7))

• چون ورودی فرمول، آرایه بوده است باید کلیدهای Ctrl+Shift+Enter را به طور همزمان روی کیبورد فشار دهیم. به این ترتیب، کل فرمول درون دو آکولاد ({}) قرار می‌گیرد تا مشخص کند که ورودی‌ها آرایه هستند. توجه داشته باشید که نباید این آکولادها را به طور دستی وارد کنیم.

8. انواع روش های جمع در اکسل : جمع چند شرطی با تابع SUMPRODUCT

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

در ادامه به کمک یک مثال، نحوه عملکرد تابع SUMPRODUCT را می‌بینیم. در این مثال، تنها ردیف‌هایی در جمع محاسبه می‌شوند که «وضعیت» آن‌ها «فعال» و «بازدید» آن‌ها بیشتر یا مساوی 10 باشد.

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

فیلم آموزش اکسل Microsoft Office Excel 2016 در فرادرس

کلیک کنید

• ابتدا سلول نتیجه را انتخاب و قسمت اول فرمول را درون آن می‌نویسیم (همراه با عملگر =). سپس به عنوان اولین آرگومان، سلول‌هایی را انتخاب می‌کنیم که اولین شرط، روی آن‌ها جستجو خواهد شد (در این مثال، محدوده A2:A6).
• بعد از گذاشتن علامت مساوی (=)، اولین شرط که عبارت «فعال» است را همراه با علامت نقل قول ("") می‌نویسیم و پرانتز اول را می‌بندیم.
• به عنوان آرگومان بعدی باید سلول‌هایی را بنویسیم که می‌خواهیم شرط دوم را بینشان پیدا کنیم (در این مثال، محدوده B2:B6). سپس شرط دوم، یعنی «10=<» را می‌نویسیم.
• در نهایت سلول‌هایی که می‌خواهیم با هم جمع شوند را وارد می‌کنیم ( در این مثال، محدوده C2:C6) و دو پرانتز باقی مانده را مي‌بندیم. فرمول نهایی مانند زیر خواهد شد:

1=SUMPRODUCT(--(A2:A6="فعال"), --(B2:B6>=10),--(C2:C6))

• با زدن دکمه Enter فرمول اجرا شده و خروجی مانند تصویر زیر، نمایش داده می‌شود.

9. جمع پنج عدد بالاتر لیست

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

نسخه1، چند عدد بزرگ‌تر

در صورتی که بخواهید چند عدد بزرگ‌تر یک محدوده را با هم جمع کنید، مثلا 3 عدد بزرگ‌تر، بهتر است اعداد را درون فرمول بنویسید، مانند زیر:

1=SUM(LARGE(A1:A7,{1,2,3}))  
2

بر اساس جدول مثال زیر، نتیجه این فرمول می‌شود: 70+60+50. توجه کنید که تنها یک عدد 50 در جمع حساب شده است و عدد 50 دوم با اینکه جایگاه سوم را دارد اما مورد استفاده قرار نگرفته است.

نسخه2، چندین عدد بالاتر

اگر قصد محاسبه جمع چندین عدد بالاتر را داشته باشید، بهتر است از ترکیب تابع SUM با تابع INDIRECT کمک بگیرید. به این ترتیب باید در تابع INDIRECT، ردیف‌هایی را وارد کنید که شامل اعداد مورد نظر هستند. در این مثال از ردیف‌های 1:10 استفاده کرده‌ایم، چون 10 عدد بالاتر لیست، درون این محدوده قرار دارند. برای محاسبه جمع با کمک تابع INDIRECT مانند مراحل زیر پیش می‌رویم:

• ابتدا فرمول زیر را در سلول نتیجه می‌نویسیم:

1  =SUM(LARGE(A1:A50,ROW(INDIRECT("1:10"))))

• از آن‌جایی که ورودی این تابع آرایه است، برای اجرا شدن آن، کلیدهای Ctrl+Shift+Enter را به صورت همزمان می‌زنیم.

نسخه3، اعداد بزرگ‌تر متغیر

برای محاسبه اعداد بزرگ‌تر در شرایط متغیر، همچنان می‌توانید از ترکیب تابع INDIRECT ،LARGE و SUM استفاده کنید. درست مانند مثال بالا اما باید به سلولی که حاوی متغیر مورد نظر است، ارجاع دهید. برای انجام این کار، مانند زیر پیش می‌رویم:

• ابتدا در سلول C1، تعداد سلول‌های بالاتر را می‌نویسیم (در این مثال، 10).
• سپس فرمول زیر را در سلول نتیجه وارد می‌کنیم:

1  =SUM(LARGE(A1:A7,ROW(INDIRECT("1:"&C1))))

• در نهایت، از آن‌جایی که این تابع ورودی آرایه دارد، برای اجرا شدن آن سه دکمه Ctrl+Shift+Enter را به طور همزمان فشار می‌دهیم.

10. محاسبه مقادیر موجود در محدوده‌های زمانی

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

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

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

می‌خواهیم از تابع SUMIFS برای محاسبه مجموع تمام واحدها استفاده کنیم، به شرطی که تاریخ:

• در روز شروع یا بعد از آن باشد.
  • و
• در روز پایان یا قبل از آن باشد.

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

1=SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$2, $A$2:$A$9,"<=" & $E$2)

• در این مثال، آرگومان اول ($B$2:$B$9 )، محدوده‌ای است که عددهای مورد نظر برای جمع را در خود دارد.
• آرگومان دوم ($A$2:$A$9 )، محدوده‌ای است که شرط اول در آن جستجو خواهد شد.
• سومین آرگومان (">=" & $D$2 )، محدوده‌ای را نشان می‌دهد که شرط اول قرار است در آن بررسی شود (تاریخ شروع) به همراه عملگری که برای شرط داریم، یعنی علامت بزرگ‌تر یا مساوی (=<).
• چهارمین آرگومان ($A$2:$A$9 )، محدوده مورد نظر برای جستجوی شرط دوم است.
• پنجمین آرگومان ("<=" & $E$2 )، محدوده‌ای حاوی مقدار شرط دوم (تاریخ پایان) و عملگر مورد استفاده در آن (کمتر یا مساوی >=) است.

در این مثال، نتیجه محدوده زمانی انتخابی، مجموع فروش 494 واحد شده است. برای تایید صحت این مقدار، می‌توانید اعداد محدوده B3:B6 را به صورت دستی جمع بزنید. همچنین می‌توانید برای محاسبه مجموع فروش یک محدوده زمانی دیگر، تاریخ شروع و پایان را در سلول‌های D2 و E2 تغییر دهید. به همین ترتیب می‌توانیم جمع ساعت در اکسل را نیز محاسبه کنیم. فقط مهم است که از قبل، قالب‌ندی سلول‌ها را به درستی پیاده‌سازی کرده باشیم.

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

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

• در روز شروع یا بعد از آن است.

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

• بعد از تاریخ پایان.

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

1=SUMIF($A$2:$A$9,">=" &$D$2,$B$2:$B$9)
2- SUMIF($A$2:$A$9,">" &$E$2,$B$2:$B$9)

• محدوده $A$2:$A$9 ، شامل اعدادی است که می‌خواهیم جمع کنیم.
• شرط ">=" & $D$2 ، محدوده‌ای است که تاریخ شروع و عملگر بزرگ‌تر مساوی را شامل می‌شود.
• محدوده $B$2:$B$9 ، محدوده مورد نظر برای جستجوی تاریخ است.
• و در نهایت شرط ">" & $E$2 ، محدوده‌ای را نشان می‌دهد که تاریخ پایان همراه با عملگر بزرگ‌تر، در آن قرار دارد.

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

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

11. جمع کردن فهرست فیلتر شده با تابع SUBTOTAL

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

• ابتدا به کمک دکمه Filter در زبانه Home یا سایر روش‌ها، لیست داده را فیلتر می‌کنیم. حداقل یکی از ردیف‌ها باید فیلتر شده باشد. در این مثال، ستون اول را به شکلی فیلتر کرده‌ایم که فقط ردیف‌های مربوط به عبارت «پوشه» نشان داده شوند.
• سپس اولین سلول خالی زیر ستونی که می‌خواهیم جمع کنیم را برمی‌گزینیم و روی دکمه AUTOSUM در زبانه HOME، می‌زنیم. (اگر می‌خواهید تابع SUMTOTAL را برای سلول دیگری غیر از اولین سلول خالی زیر ستون به کار ببرید، می‌توانید فرمول را به طور دستی درون آن بنویسید).
• پس از آن، فرمول تابع SUBTOTAL به طور خودکار درون سلول نوشته می‌شود که جمع مقادیر قابل مشاهده ستون را انجام می‌دهد. (اولین آرگومان این تابع یک عدد تابعی است که مشخص می‌کند چه عملیاتی روی اعداد مورد نظر انجام شود. اینجا عدد 9 را می‌بینیم که به معنی انجام عمل جمع است. می‌توانیم از سایر اعداد مانند 1 برای محاسبه میانگین و 3 برای COUNTA استفاده کنیم).
• در نهایت دکمه Enter را فشار می‌دهیم تا فرمول کامل و اجرا شود. البته توجه داشته باشید که در نسخه اکسل 2003 و بالاتر می‌توانید از فرمول زیر نیز برای انجام همین کار استفاده کنید:

1    =SUBTOTAL(109,B2:B9)

اعداد تابعی SUBTOTAL

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

گروه دوم نیز تابع‌های 101 تا 111 هستند.

12. محاسبه مجموع یک لیست فیلتر شده با تابع AGGREGATE

تابع AGGREGATE که از نسخه اکسل 2010 معرفی شده است، مانند تابع SUBTOTAL کار می‌کند. با این تفاوت که توابع بیشتری دارد و می‌تواند خطاهای مقداری، مانند ردیف‌های داده پنهان را نادیده بگیرد. در ادامه با چند مثال، این تابع را یاد می‌گیریم.

درست مانند تابع SUBTOTAL، تابع AGGREGATE نیز ردیف‌های پنهان را نادیده می‌گیرد و چندین عدد تابعی مختلف مانند SUM و AVERAGE دارد. البته برخلاف SUBTOTAL، این تابع، 19 عدد تابعی دارد.

برخلاف تابع SUBTOTAL، تابع AGGREGATE را می‌شود برای نادیده گرفتن خطاها و ردیف‌های پنهان و همچنین توابع AGGREGATE و SUBTOTAL تو در تو، تنظیم کرد.

برای جمع فیلتر و نادیده گرفتن ردیف‌های پنهان به کمک تابع AGGREGATE، مراحل زیر را دنبال می‌کنیم:

• ابتدا سلول نتیجه را انتخاب و قسمت اول فرمول را در آن وارد می‌کنیم. سپس در لیست اعداد تابعی، روی عدد 9 می‌زنیم تا عملیات SUM انجام شود.
• بعد از گذاشتن «کاما» (,)، در بین گزینه‌ها، عدد 3 یا 7 را انتخاب می‌کنیم. در این مثال، عدد 3 می‌شود دومین آرگومان و در نتیجه، ردیف‌های پنهان، خطاها و توابع تو در توی SUBTOTAL و AGGREGATE، نادیده گرفته می‌شوند.
• سپس محدوده‌ای از سلول‌ها که داده در آن قرار دارد را برمی‌گزینیم (در این مثال، محدوده D2:D7).
• در نهایت بعد از وارد کردن پرانتز بسته، دکمه Enter را می‌زنیم که فرمول اجرا شود. فرمول نهایی به شکل زیر است:

1=AGGREGATE(9,3,D2:D7)

13. فرمول‌هایی با ارجاع جدولی

ممکن است در مواقعی که از ارجاع جدولی یا Table Reference استفاده می‌کنید، هنگام کپی کردن فرمول به ستون‌های مجاور به مشکل بخورید. در ادامه روش‌های جلوگیری از بروز این مسئله را یاد می‌گیریم.

مشکل Table Reference در اکسل

در این خلاصه فروش، در سلول C5 از تابع SUMIFS استفاده کرده‌ایم که مجموع فروش محصول «Bar» در شعبه «West» را به درستی نشان می‌دهد و فرمول آن به شکل زیر است:

1=SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4)

اما اگر دسته Fill Handle این سلول را بگیرید و به سمت راست بکشید، نتیجه‌ای که در سلول D5 نشان می‌دهد، نادرست است.

اگربه فرمول موجود در سلول D5 نگاه کنیم، متوجه می‌شویم که تمام ارجاعات جدولی، به یک ستون به سمت راست منتقل شده اند، چون فرمول را یک ستون به سمت راست بردیم:

1=SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Product],D$4)

به همین دلیل، فرمول به جای Quantity، جمع ستون «Total Cost» را حساب می‌کند. همچنین به جای جستجوی عبارت « East»، در ستون «Region»، در ستون «Category» جستجو می‌کند. در انتها نیز به جای گشتن به دنبال عبارت «Cookies» در ستون «Category»، در ستون « Product» جستجو می‌کند. از آن‌جایی که هیچ‌کدام از شروط محقق نمی‌شود، نتیجه صفر در خروجی نشان داده خواهد شد.

برای جلوگیری از بروز این مشکل کافی است از دسته Fill Handle استفاده نکنید. به جای آن می‌توانید یکی از دو روش زیر را به کار ببرید.

استفاده از روش Fill Right

ابتدا سلول حاوی فرمول و سلول‌های سمت راست آن که می‌خواهیم همین فرمول را داشته باشند، انتخاب می‌کنیم. سپس دو کلید Ctrl+R را روی کیبورد فشار می‌دهیم تا فرمول به سلول‌های سمت راست منقل شود.

استفاده از روش کپی و پیست

ابتدا سلول حاوی فرمول را انتخاب و روی آن راست‌کلیک می‌کنیم. از منوی باز شده، گزینه Copy را می‌زنیم. سپس تمام سلول‌های دیگری که می‌خواهیم فرمول را در آن‌ها اضافه کنیم، انتخاب می‌کنیم. در نهایت دو کلید Ctrl+V را روی کیبورد می‌زنیم تا فرمول پیست (Paste) شود.

رفع مشکل عدم جمع شدن اعداد در اکسل

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

رفع مشکل در صورتی که مقادیر مانند عدد به نظر می‌رسند اما عدد نیستند

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

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

در تصویر بالا، مقادیر موجود در ستون C، شبیه به عدد هستند اما با یکدیگر جمع نمی‌شوند. در این محدوده، 3 سلول حاوی تابع داریم:

• تابع SUM: در سلول C7 می‌بینیم که به جای پاسخ صحیح، مجموع فرمول جمع، صفر شده است.
• تابع COUNTA: در سمت راست و درون سلول F4 از تابع COUNTA برای شمردن سلول‌هایی که خالی نیستند، استفاده شده است. نتیجه این فرمول عدد 4 است که نشان می‌دهد در محدوده سلولی C3:C6، چهار ورودی وجود دارد. از این تابع برای شمارش سلول ها در اکسل استفاده می‌شود.
• تابع COUNT: تابع COUNT برای شمارش سلول‌های دارای عدد است. در سلول F5 از این تابع استفاده شده و نتیجه آن صفر شده است. این یعنی هیچ‌یک از سلول‌های این محدوده به عنوان عدد در نظر گرفته نشده‌اند.

تبدیل متن به عدد با ویژگی Paste Special

برخی از عددهای متنی را می‌شود به کمک Paste Special درست کرد. برای انجام این کار، مرحل زیر را دنبال کنید:

• روی یک سلول خالی راست‌کلیک و گزینه COPY را انتخاب کنید. سپس سلول‌های حاوی عددهای متنی را برگزینید. روی یکی از آن‌ها کلیک‌راست کنید و روی گزینه Paste Special بزنید.
• در ادامه، پنجره مربوط به Paste Special باز می‌شود. در قسمت Paste، گزینه Values را برگزینید و در قسمت Operation، دکمه Add را بزنید و در نهایت، OK کنید.

بعد از اینکه عددها درست شدند، می‌توانید قالب بندی مناسب را روی آن‌ها اعمال کنید. برای این کار باید در زبانه Home و منوی کشویی Format، گزینه Number را برگزینید.

تبدیل متن به عدد به کمک VBA

اگر به طوز مکرر نیاز به تغییر قالب بندی سلول‌ها دارید، بهتر است از ماکروهای VBA استفاده کنید. برای انجام این کار ابتدا دو کلید Alt+F11 را روی کیبورد فشار دهید تا پنجره کدنویسی VBA باز شود. سپس روی شیت مورد نظر راست کلیک و گزینه‌های Insert > Module را انتخاب کنید. در صفحه باز شده، کد زیر را بنویسید:

1Sub ConvertToNumbers()
2Dim rng As Range
3
4'get constants in selected range
5On Error Resume Next
6Set rng = Selection _
7  .SpecialCells(xlCellTypeConstants, 23)
8On Error GoTo errHandler
9
10If Not rng Is Nothing Then
11  'copy blank cell outside used range
12  Cells.SpecialCells(xlCellTypeLastCell) _
13      .Offset(0, 1).Copy
14  
15  'add to selected cells
16   rng.PasteSpecial Paste:=xlPasteValues, _
17       Operation:=xlPasteSpecialOperationAdd
18Else
19  MsgBox "Could not find Constants in selection"
20End If
21
22exitHandler:
23  Application.CutCopyMode = False
24  Set rng = Nothing
25  Exit Sub
26errHandler:
27  MsgBox "Could not change text to numbers"
28  Resume exitHandler
29End Sub 

سپس از محیط VBA خارج شوید. از این پس می‌توانید به کمک تابع ConvertToNumbers()  متن را به عدد تبدیل کنید.

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

سخن نهایی

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

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