مقدمه

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

تابع SUMIF چیست؟

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

فرمول کلی تابع SUMIF به این صورت است:

=SUMIF(range, criteria, [sum_range])

در این فرمول:

• range ناحیه ‌ای است که شرط بر اساس آن بررسی می‌ شود.
• criteria همان شرطی است که باید بررسی شود (مثلاً “برابر با علی”، “>10” و غیره).
• sum_range ناحیه‌ ای است که مقادیر آن در صورت برقراری شرط جمع زده می ‌شوند. این بخش اختیاری است و اگر نوشته نشود، اکسل همان range را برای جمع در نظر می ‌گیرد.

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

نحوه استفاده از تابع SUMIF

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

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

• =SUMIF (A2:A5, “لپتاپ”, B2:B5)
• در این فرمول:
• A2:A5 ناحیه‌ ای است که اکسل در آن دنبال شرط می ‌گردد.
• “لپتاپ” شرط مورد نظر است.
• B2:B5 محدوده‌ ای است که در صورت برقراری شرط، مقادیر آن جمع می ‌شود.
• نتیجه: مقدار خروجی این تابع برابر است با 3000 که حاصل جمع 1000 و 2000 است.

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

مثال از تابع SUMIF در اکسل

جدولی از فروش روزانه چندین محصول را در اختیار داریم:

قصد داریم تعداد کل فروش محصول لپ تاپ را  محاسبه کنیم. از این رو با درج تابع SUMIF محدوده A1:A10 را به عنوان محدوده جستجو، درج خواهیم نمود:

سپس معیار جستجو (محصول لپ تاپ) را فی مابین ” “لحاظ خواهیم نمود:

در پایان محدوده تعداد فروش(C1:C10) را به عنوان محدوده محاسبات(جمع)، تعیین خواهیم نمود:

با فشردن کلید Enter از صفحه‌کلید، جمع فروش محصول لپ تاپ برابر است با 21 عدد:

در ادامه بهتر است مقدار آرگومان criteria در تابع SUMIF اکسل را از یک سلول فراخوانی کنیم تا دیگر نیاز به نوشتن معیار جستجو  فی ما بین ” ” وجود نداشته باشد:

به عنوان مثال، جمع فروش محصول کیبورد (معیار جستجو در سلول E3 درج گردیده است):

اکنون تا به این بخش از مقاله معیارهای برابر، همچون”برابر با لپ تاپ” و “برابر با کیبورد” مورد بحث قرار گرفتند. در ادامه به تشریح عملگرهای منطقی در اکسل و کاربرد آن در تابع SUMIF خواهیم پرداخت.

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

عملگرهای منطقی در اکسل، به شرح زیر است:

• برابر (=)
• نابرابر (<>)
• بزرگتر (>)
• بزرگتر مساوی (>=)
• کوچکتر (<)
• کوچکتر مساوی (<=)

با استفاده از عملگر منطقی نابرابر (<>)، قادر به محاسبه جمع فروش تمام محصولات به جز کیبورد خواهیم بود:

و یا پرتفوی یک سهامدار را تصور کنید، تعداد سهام‌هایی که قیمت هر سهم آن بیشتر از 50 تومان ارزش دارد برابر است با 3089 عدد:

البته استفاده از عملگرهای منطقی در تابع SUMIF بصورت زیر نیز صحیح است:

نکته: در صورتی که آرگومان [sum_range] تعیین نگردد، آرگومان range به عنوان محدوده محاسباتی لحاظ خواهد شد:

همانطور که در تصویر فوق نیز مشاهده می‌کنید مجموع تعداد فروش‌های بزرگتر از 10 عدد، برابر است با 37 عدد.

تابع SUMIFS چیست؟

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

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

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

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

نحوه استفاده از تابع SUMIFS

تابع SUMIFS زمانی به کار می ‌رود که بخواهید فقط مقادیری را جمع بزنید که چند شرط هم ‌زمان را داشته باشند. این تابع بسیار مناسب گزارش ‌هایی است که باید با دقت بالا و بر اساس معیارهای ترکیبی ساخته شوند. فرض کنید می ‌خواهید مجموع فروش لپتاپ‌ ها را فقط در منطقه “شمال” محاسبه کنید. داده ‌ها به این صورت هستند:

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

• =SUMIFS (C2:C5, A2:A5, “لپتاپ”, B2:B5, “شمال”)
• در اینجا:
• C2:C5 محدوده ‌ای است که باید مقادیرش جمع شود.
• A2:A5 و B2:B5 محدوده‌ هایی هستند که شروط در آن‌ ها بررسی می ‌شوند.
• شرط اول “لپتاپ”، شرط دوم “شمال” است.
• نتیجه: 1000 + 2000 = 3000

آموزش تابع SUMIFS در اکسل به شما کمک می ‌کند داده ‌ها را به‌ صورت هوشمندانه و هدفمند تحلیل کنید؛ خصوصاً در پروژه ‌های مالی و مدیریتی.

تفاوت دو تابع SUMIF و SUMIFS در اکسل

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

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

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

در آموزش توابع SUMIF و SUMIFS در اکسل تأکید می شود که SUMIFS انعطاف پذیری بسیار بیشتری دارد و برای گزارش گیری های مدیریتی پیچیده تر مناسب است. با این حال، برای محاسبات ساده و تک شرطی، استفاده از SUMIF می تواند کارایی بیشتری داشته باشد. انتخاب بین این دو تابع باید بر اساس پیچیدگی تحلیل مورد نیاز و ساختار داده های شما انجام شود.

کاربردهای عملی توابع SUMIF و SUMIFS

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

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

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

مثال های کاربردی برای آموزش توابع SUMIF و SUMIFS در اکسل

فرض کنید لیست حقوق کارمندان را دارید و می خواهید مجموع حقوق بخش فروش (SUMIF) و مجموع حقوق بخش فروش با سابقه بیش از ۵ سال (SUMIFS) را به دست آورید.

• =SUMIF (B2:B10, “فروش”, C2:C10) → جمع حقوق بخش فروش
• =SUMIFS (C2:C10, B2:B10, “فروش”, D2:D10, “>5”) → جمع حقوق فروش با سابقه >5 سال

استفاده از شرط ‌های متنی و مقایسه ‌ای

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

برای شرط‌ های عددی، می ‌توان از علائمی مانند >, <, >=, <= و <> استفاده کرد. برای مثال، اگر بخواهید مجموع فروش ‌هایی که بیشتر از 1000 واحد هستند را محاسبه کنید، می ‌توانید از فرمولی مانند SUMIF(B2:B10, ">1000") استفاده کنید. همچنین، اگر بخواهید فقط فروش ‌هایی که کمتر از یا مساوی با 500 هستند را جمع بزنید، کافی است شرط را به شکل <=500 در فرمول قرار دهید. در مورد داده‌ های متنی نیز می ‌توانید از کاراکترهای جایگزین مانند * (برای جایگزینی چند حرف) و ? (برای یک حرف) استفاده کنید. به عنوان نمونه، اگر بخواهید فقط ردیف ‌هایی که در ستون محصول، شامل عبارت “کفش” هستند را بررسی کنید، از شرط "*کفش*" استفاده می ‌کنید. این شرط باعث می‌ شود تمام سلول‌ هایی که واژه کفش را در هر جای متن دارند (مثلاً “کفش اسپرت” یا “کفش مردانه”) انتخاب شوند.

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

در این قسمت از آموزش توابع SUMIF و SUMIFS در اکسل، چند ترفند حرفه ‌ای معرفی می‌ کنیم:

• استفاده از ارجاع به سلول
• استفاده از نام محدوده‌ ها برای خوانایی بیشتر
• ترکیب SUMIFS با TODAY() برای فیلتر تاریخ روز

اشتباهات رایج هنگام استفاده از SUMIF و SUMIFS

در ادامه آموزش توابع SUMIF و SUMIFS در اکسل، شناخت اشتباهات رایج می ‌تواند از بروز خطاهای محاسباتی جلوگیری کند. یکی از شایع ‌ترین اشتباهات، عدم هماهنگی اندازه محدوده ‌ها است؛ به‌ ویژه در SUMIFS که تمام محدوده‌ های شرط باید دقیقاً هم ‌اندازه با محدوده جمع باشند. در غیر این صورت، فرمول نتیجه ‌ای اشتباه یا خطای #VALUE! خواهد داد.

اشتباه دوم، استفاده نادرست از رشته ‌ها است. فراموش کردن علامت نقل‌قول (” “) در نوشتن شرط ‌های متنی، مانند "محصول" یا ">1000" باعث می‌ شود اکسل شرط را نپذیرد یا آن را نادرست تفسیر کند.

اشتباه سوم مربوط به ترتیب آرگومان‌ ها در تابع SUMIFS است. برخلاف SUMIF که ابتدا محدوده شرط نوشته می ‌شود، در SUMIFS ابتدا باید محدوده جمع (sum\_range) قرار بگیرد. رعایت نکردن این ترتیب باعث می‌ شود خروجی فرمول نادرست باشد یا اصلاً کار نکند.

جمع‌ بندی نهایی برای آموزش توابع SUMIF و SUMIFS در اکسل

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

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