اگر از اکسل برای تحلیل داده‌ها استفاده می‌کنید، به احتمال زیاد از تابع SUM برای جمع‌بندی داده‌ها استفاده کرده‌اید؛ اما اگر با حجم زیادی از داده‌ها سروکار دارید، احتمالاً نمی‌خواهید کل مجموع یک محدوده از سلول‌ها را داشته باشید، بلکه به‌جای آن، مجموع یک مقدار انتخابی را می‌خواهید. تابع SUMIF انجام این کار را به‌راحتی امکان‌پذیر می‌کند. در این مقاله، به‌طور کامل به آموزش فرمول SUMIF در اکسل می‌پردازیم و به شما نشان می‌دهیم که چگونه از این تابع استفاده کنید.
در این مطلب می‌خوانید
تابع SUMIF چیست و برای چه کاری استفاده می‌شود؟
همان‌طور که در مقاله فرمول نویسی در اکسل در وبسایت ۲۴ وب لرن گفته شده است، برای محاسبه مجموع (کل) یک محدوده سلول‌ها معمولاً از تابع SUM در مایکروسافت اکسل استفاده می‌شود. بااین‌حال، SUM یک تابع بسیار ابتدایی است که به کاربر اجازه نمی‌دهد تعیین کند کدام سلول‌ها را باید در فرمول انتخاب کند؛ بلکه به‌سادگی همه را شامل می‌شود.
این موضوع باعث می‌شود که SUM یک تابع کمترمفید برای تحلیل داده‌های پیچیده باشد. برای حل این مشکل می‌توانید به‌جای SUM از تابع SUMIF استفاده کنید. SUMIF به شما اجازه می‌دهد از یک عبارت منطقی (در این مورد، یک عبارت IF) استفاده کنید تا به اکسل کمک کند قبل‌از محاسبه مجموع تصمیم بگیرد کدام سلول‌ها را درنظر بگیرد.
برای مثال، اگر لیستی از دانش‌آموزانی دارید که در حال جمع‌آوری پول هستند و سعی می‌کنید مجموع را براساس اینکه در چه تیمی هستند (مثلاً تیم قرمز یا آبی) تعیین کنید، می‌توانید از SUMIF استفاده کنید که در آن تابع مجموع کمک‌های مالی را در صورتی که دانش‌آموزان عضو تیم قرمز باشند، محاسبه می‌کند.
محدودیت‌های یک عبارت IF به این معنی است که اگر از تابع SUMIF استفاده کنید، فقط می‌توانید یک معیار داشته باشید (مثلاً: محدوده سلول‌ها شامل «مرد» باشد).
اگر به معیارهای بیشتری برای عبارت منطقی خود نیاز دارید، طبق آنچه که در سایت microsoft.com منتشر شده است،
باید از تابع جایگزینی مانند SUMIFS استفاده کنید که به شما اجازه می‌دهد ترکیبی از IF و AND را در یک فرمول به کار ببرید.
تابع SUMIF چگونه در مایکروسافت اکسل کار می‌کند؟
در ادامه آموزش SUMIF در اکسل، به این موضوع می‌رسیم که این تابع چگونه کار می‌کند. معیارهای تعیین اینکه آیا یک سلول باید در فرمول SUMIF گنجانده شود یا خیر، می‌تواند عددی (مثلاً اینکه آیا سلول‌ها شامل عدد ۱۲ هستند)، یک رشته متنی (مثلاً سلول‌هایی که شامل کلمه «بله» هستند) یا شامل یک عملگر ریاضی (مثلاً مقادیری که بیشتر از ۱۲ هستند) باشد.
ساختار فرمول به‌صورت زیر است:
=SUMIF(range, criteria, sum_range)
در فرمول فوق، range به محدوده سلولی اشاره دارد که می‌خواهید به‌عنوان بخشی از بررسی معیارها استفاده کنید (مثلاً A2:C1). آرگومان criteria، همان‌طور که از نامش پیدا است، به معیار IF تکی اشاره دارد (مثلاً بررسی یک رشته متنی که حاوی کلمه “today” است).
آرگومان اختیاری sum_range به شما اجازه می‌دهد یک محدوده سلولی دوم را برای محاسبه SUMIF تعیین کنید. اگر بخواهید آرگومان range اولیه را برای عملیات منطقی خود به کار ببرید؛ اما بخواهید مجموع را از یک مجموعه سلول متفاوت محاسبه کنید (مثلاً اگر X برابر Y باشد، Z را جمع بزن)، این آرگومان می‌تواند مفید باشد.
نکاتی که قبل‌از استفاده از SUMIF در اکسل باید درنظر داشته باشید
تابع SUMIF محدودیت‌های کمی دارد؛ اما این محدودیت‌ها وجود دارند؛ بنابراین باید به آن‌ها توجه کنید. این محدودیت‌ها عبارت‌اند از:
- بدون آرگومان sum_range، محدوده سلولی که در آرگومان اولیه range ذکر شده است، برای محاسبه SUMIF هم استفاده خواهد شد.
- استفاده از علامت آمپرسند (ampersand) یا (&) امکان الحاق اولیه را در فرمول SUMIF فراهم می‌کند و به شما اجازه می‌دهد از داده‌های سلول‌های دیگر به‌عنوان بخشی از آرگومان معیار SUMIF استفاده کنید. برای مثال، =SUMIF(A2:D1, “<“&A9) به‌دنبال سلول‌هایی در محدوده A2 تا D1 می‌گردد که کمتر از مقدار سلول A9 باشند.
- الحاق با استفاده از آمپرسند می‌تواند با توابع دیگر، مانند تابع DATE، برای ایجاد معیارهای پیچیده‌تر هم استفاده شود.
- اگر معیار شما از رشته متنی استفاده می‌کند، می‌توانید از وایلدکارت‌ها (wildcards) برای جستجوی تطبیق‌های جزئی استفاده کنید (مانند ? برای جستجوی کاراکترها براساس طول یا * برای کاراکترهای متوالی). اگر می‌خواهید این کاراکترهای خاص را جستجو کنید (به‌جای استفاده از آن‌ها به‌عنوان بخشی از معیار خود)، یک علامت مد (~) را قبل‌از آن‌ها قرار دهید (مثلاً ~*).
- رشته‌های متنی که به‌عنوان بخشی از آرگومان معیار استفاده می‌شوند، می‌توانند حداکثر ۲۵۵ کاراکتر طول داشته باشند. رشته‌های طولانی‌تر پیام خطای #VALUE! را برمی‌گردانند.
آموزش SUMIF در اکسل: راهنمای گام‌به‌گام
در این بخش از آموزش SUMIF در اکسل به بررسی نحوه استفاده از این فرمول می‌پردازیم. اگر می‌خواهید از تابع SUMIF در یک کاربرگ اکسل استفاده کنید، باید مراحل زیر را دنبال کنید.
مرحله ۱: انتخاب یک سلول خالی
شما می‌توانید با بازکردن یک صفحه گسترده اکسل و انتخاب یک سلول خالی شروع کنید. با انتخاب سلول، روی نوار فرمول در نوار ابزار کلیک کنید تا روی آن متمرکز شوید.
مرحله ۲: تعیین محدوده اولیه سلول‌ها
با فعال‌شدن نوار فرمول باید شروع به نوشتن فرمول SUMIF خود با ساختار و نحوه نگارش ذکرشده کنید. با تایپ‌کردن تابع زیر در نوار ریبون شروع کنید:
=SUMIF)
سپس باید محدوده سلولی اولیه را انتخاب کنید. اگر از آرگومان sum_range در انتهای فرمول خود استفاده نمی‌کنید، این محدوده شامل داده‌هایی خواهد بود که تابع SUMIF برای محاسبه مجموع آن‌ها استفاده می‌کند. در غیر این صورت، این داده‌ها فقط برای بررسی معیارها استفاده خواهند شد.
برای این کار، محدوده سلولی خود را با استفاده از اولین و آخرین سلول در محدوده تایپ کنید یا سلول‌ها را با موس یا تاچ‌پد انتخاب کنید. به‌عنوان‌مثال، از یک محدوده سلولی بین A2 و A9 به‌صورت زیر استفاده کنید:
=SUMIF(A2:A9
مرحله ۳: تعیین معیار SUMIF
پس‌از انتخاب محدوده اولیه باید معیار SUMIF را تعیین کنید. این معیار آزمون منطقی IF است که فرمول برای تعیین اینکه کدام سلول‌ها محاسبه و کدام یک نادیده گرفته شوند، استفاده می‌کند. می‌توانید از اعداد، رشته‌های متنی، عملگرهای ریاضی، کاراکترهای جایگزین و سایر مراجع سلول به‌عنوان بخشی از معیار خود استفاده کنید.
نمونه‌هایی از معیارهای مختلف SUMIF شامل موارد زیر هستند:
- استفاده از عملگرهای کمتر یا بیشتر از:
=SUMIF(B2:B9, “<25”)
- مطابقت با یک رشته متنی:
=SUMIF(A2:A9, “L*”, B2:B9)
- استفاده از کاراکتر جایگزین برای مطابقت جزئی با یک رشته:
=SUMIF(A2:A9, “to*”)
- استفاده از کاراکتر جایگزین برای پیداکردن سلول‌هایی با طول کاراکتر مشخص، براساس تعداد علامت سؤال‌های استفاده‌شده:
=SUMIF(A2:A9, “??”, B2:B9)
- تمام سلول‌های داخل محدوده که خالی هستند:
=SUMIF(A2:A10, “”, B2:B10)
- سلول‌هایی که معیار را برآورده نمی‌کنند:
=SUMIF(A2:A9, “<>Toby”, B2:B9)
- تمام سلول‌های داخل محدوده که خالی نیستند:
=SUMIF(A2:A10, “<>”, B2:B10)
- استفاده از اتصال برای افزودن داده‌ها از سلول‌های دیگر:
=SUMIF(B2:B9, “<“&B13)
- استفاده از اتصال برای به‌کارگیری صحیح تاریخ‌ها:
=SUMIF(C2:C9, “>”&DATE(2020, 12, 1), B2:B9)
این مثال‌ها یک فهرست جامع نیستند و می‌توانند با توجه به نیازهای شما اصلاح شوند. اگر قصد دارید از محدوده سلولی اولیه خود به‌عنوان بخشی از محاسبه استفاده کنید، باید فرمول را با یک پرانتز بسته ببندید تا نتیجه خروجی را ببینید، مثل:
=SUMIF(A2:A9, “<25”)
مرحله ۴ (اختیاری): تعیین معیار sum_range
آرگومان نهایی فرمول SUMIF (sum_range) اختیاری است و می‌تواند برای ایجاد آزمون‌های منطقی پیچیده‌تر استفاده شود.
اگر می‌خواهید یک محدوده سلول را آزمون کنید و سپس مجموع محدوده دوم را محاسبه کنید، باید معیار sum_range را قبل‌از بستن فرمول اضافه کنید.
مانند معیار محدوده اولیه، اولین و آخرین سلول‌های محدوده خود را مشخص کنید و سپس فرمول را با یک پرانتز بسته ببندید.
برای مثال، فرمول زیر:
=SUMIF(A2:A9, “L*”, B2:B9)
سلول‌های بین A2 و A9 را بررسی می‌کند تا ببیند آیا شامل رشته‌های متنی با حرف “L” هستند یا نه. اگر این‌گونه باشد، اعداد مربوطه در سلول‌های B2 تا B9 با استفاده از تابع SUM محاسبه خواهند شد.
در صورتی که محدوده‌های سلولی یا معیارهای شما نادرست باشند، اکسل یک پیام خطای #VALUE! یا مشابه آن را بازمی‌گرداند.
چگونه صفر تا صد اکسل را در کمترین زمان ممکن یاد بگیرید؟
آیا به‌دنبال دوره‌ای هستید که اکسل را از پایه تا سطح پیشرفته به شما آموزش دهد و شما را به یک کاربر حرفه‌ای در این نرم‌افزار قدرتمند تبدیل کند؟ دوره آموزش اکسل از مبتدی تا پیشرفته ۲۴ وب لرن دقیقاً همان چیزی است که به‌دنبال آن هستید! این دوره جامع با تدریس استاد پدرام تقی‌زاده، با بیش‌از ۹ ساعت محتوای آموزشی در ۱۸ ویدیو، تمام آنچه را برای تسلط بر اکسل نیاز دارید، به شما ارائه می‌دهد.
برای موفقیت در هر زمینه‌ای باید مهارت‌هایتان را تقویت کنید. این دوره به شما کمک می‌کند تا داده‌ها را به‌طور حرفه‌ای دستکاری کنید، یافته‌هایتان را به‌درستی ارائه دهید و محاسبات پیچیده را به‌سادگی انجام دهید. استفاده از اکسل در بسیاری از مشاغل کلیدی شامل مدیریت پروژه، حسابداری و حسابرسی، تحلیل‌گری مالی، مدیریت بودجه و تحلیل‌گری بازار ضروری است. بنابراین با تبدیل‌شدن به یک متخصص اکسل نه‌تنها می‌توانید فرصت‌های شغلی متعددی پیدا کنید، بلکه می‌توانید گام‌های بلندتری برای کسب موفقیت در شغل فعلی خود بردارید.
دوره آموزش جامع اکسل یکی از پرطرفدارترین دوره‌های آموزشی ۲۴ وب لرن است که اکنون با یک تخفیف ویژه ۶۰ درصدی به‌جای قیمت اصلی ۴۹۹,۰۰۰ تومان، فقط با ۱۹۹,۰۰۰ تومان در دسترس شما قرار دارد. با شرکت در این دوره، علاوه‌بر یادگیری از سطح مقدماتی تا پیشرفته، به مزایای زیر هم دسترسی خواهید داشت:
- دسترسی مادام‌العمر به تمامی فایل‌ها و بروزرسانی‌ها؛
- پشتیبانی دائم از سوی تیم آموزشی ۲۴ وب لرن؛
- آموزش مباحث پیشرفته مانند PivotTables، ماکرو نویسی، فرمول نویسی پیشرفته و بسیاری موارد دیگر.
همین حالا از صفحه محصول دوره جامع اکسل ۲۴ وب لرن دیدن کنید و این فرصت طلایی را از دست ندهید.
سوالات متداول
- چگونه می‌توانم از تابع SUMIF برای کار با تاریخ‌ها استفاده کنم؟
برای کار با تاریخ‌ها در تابع SUMIF می‌توانید از تابع DATE در ترکیب با معیارهای خود استفاده کنید. به‌عنوان‌مثال، فرمول =SUMIF(C2:C9, “>“&DATE(2023,1,1), B2:B9) تمام مقادیر در محدوده B2:B9 را جمع می‌کند که تاریخ‌های مربوط به آن‌ها در محدوده C2:C9 بعد از اول ژانویه ۲۰۲۳ باشد.
- چگونه می‌توانم در تابع SUMIF از معیارهای متنی استفاده کنم؟
برای استفاده از معیارهای متنی می‌توانید از رشته‌های متنی داخل علامت نقل قول (“”) استفاده کنید. همچنین می‌توانید از کاراکترهای wildcard مانند * و ? برای جستجوی تطابق‌های جزئی استفاده کنید. به‌عنوان‌مثال، فرمول =SUMIF(A2:A9, “T*”) تمام سلول‌هایی را که با حرف “T” شروع می‌شوند، پیدا می‌کند و مجموع آن‌ها را محاسبه می‌کند.
- اگر بخواهم بیش‌از یک معیار را اعمال کنم، چه باید بکنم؟
اگر نیاز دارید بیش‌از یک معیار را در فرمول خود اعمال کنید، باید از تابع SUMIFS استفاده کنید که به شما امکان می‌دهد چند معیار را هم‌زمان اعمال کنید. این تابع مشابه SUMIF است؛ اما انعطاف‌پذیری بیشتری دارد و می‌تواند برای تحلیل‌های پیچیده‌تر مورد استفاده قرار گیرد.
سخن پایانی
با آموزش SUMIF در اکسل می‌توانید به‌سادگی داده‌های خود را تحلیل کنید و مجموع‌های خاصی را با معیارهای مشخص محاسبه کنید. این مهارت برای هر کاربر اکسل ضروری است و می‌تواند بهره‌وری شما را به‌طور چشم‌گیری افزایش دهد.
سلام خیلی مطلب خوبی بود اما سوالی که اینجا برای من پیش اومده اینه که برای از اونجایی که فرمول های زیادی توی اکسل وجود داره آیا مقاله ای هست بشه مهمترین فرمل های اکسل رو یاد بگیریم. مخصوصا برای من که توی شرکت کار می کنم. ؟