محاسبه مجموع خریدهای مواد غذاییام با یک SUMIF و تقریباً ده ثانیه انجام شد. مجموعهای شرطی از جمله قابلیتهای اکسلی هستند که هر روز به آنها تکیه میکنم، بنابراین آن فرمول در برگه ردیابی هزینهام بسیار به کار میرود.
بعد میخواستم سهم کارت اعتباری را از همان مجموع خریدهای مواد غذایی جدا کنم، و اینجا SUMIF کم آورد. این تابع یک بازه معیار، یک معیار و یک بازه جمع اختیاری میپذیرد؛ تمام ماجرا همین است. SUMIFS از اکسل ۲۰۰۷ به بعد از چند شرط پشتیبانی میکند، و تاریخها جایی هستند که فاصله میان این دو تابع بسیار بیشتر میشود.
SUMIFS چند معیار را در یک فرمول واحد میپذیرد
دستهبندی و روش پرداخت در یک خط، بدون ستون کمکی
این تفاوت تنها به یک اصلاح نیاز دارد و ارزش دارد پیش از هر چیز دیگری به آن اشاره کنیم. SUMIF بازه جمع را در پایان میگذارد و آن را اختیاری در نظر میگیرد. SUMIFS بازه جمع را در ابتدا میگذارد و آن را الزامی میکند. هر آنچه پس از این آرگومان نخست میآید، یک بازه معیار همراه با یک معیار است، و اکسل تا ۱۲۷ جفت از این موارد را میپذیرد.
برگه ردیابی هزینهام پنج ستون در ۲۴ ردیف دارد که تاریخ، دستهبندی، روش پرداخت، فروشگاه و مبلغ تمام آنچه از ژانویه تا می ۲۰۲۶ خرج کردهام را پوشش میدهد. تنها خرید مواد غذایی به ۶۴۳٫۷۰ دلار میرسد. اینگونه آن رقم را بر اساس روش پرداخت تفکیک کردم:
- سلولی را که میخواهید مجموع در آن قرار گیرد انتخاب کنید و نام تابع را بنویسید.
- بازه جمع را روی ستون Amount تنظیم کنید، زیرا SUMIFS ابتدا اعدادی را که میخواهید با هم جمع کنید نیاز دارد و سپس شرایط را.
- جفت اول را اضافه کنید: ستون Category و سپس «Groceries».
- جفت دوم را اضافه کنید: ستون Payment Method و سپس «Credit card».
- پرانتزها را ببندید و نتیجه را در برابر نمای فیلترشده با همان دو شرط بررسی کنید.
فرمول نهایی به شکل =SUMIFS(E2:E25,B2:B25,"Groceries",C2:C25,"Credit card") است و عدد ۳۰۴٫۹۰ دلار را برمیگرداند. این مقدار کمی کمتر از نیمی از هزینه مواد غذاییام است، و بدون ستون کمکی یا فرمول تودرتو به این نتیجه رسیدم.
محدود کردن بیشتر آن تنها یک ویرگول هزینه دارد. افزودن ستون Store و «Corner Grocer» بهعنوان جفت سوم، همان مجموع را به ۸۸٫۷۰ دلار کاهش میدهد. هر نوع معیاری که در SUMIF میشناسید بدون تغییر قابل استفاده است، از جمله متن ساده، اعداد، عملگرهای مقایسهای بهصورت متنی، نویسههای جایگزین و ارجاعات سلولی که با علامت & به هم پیوند داده شدهاند.
SUMIFS نیاز دارد هر بازه معیار از نظر اندازه با بازه جمع مطابقت داشته باشد. عدم تطابق به جای یک عدد نادرست، خطای #VALUE! برمیگرداند، و همین امر کشف آن را نسبت به اشتباه معادل در SUMIF آسانتر میکند.
بازههای تاریخ جایی هستند که SUMIF هیچ پاسخی ندارد
دو شرط روی یک ستون، دیواری است که بالاخره به آن میرسید
هزینههای فصلی پرسشی بود که این موضوع را برای من روشن کرد. یک فصل تاریخ شروع و تاریخ پایان دارد، یعنی دو شرط روی یک ستون اعمال میشوند. SUMIF تنها یک معیار میپذیرد، بنابراین این پرسش اساساً خارج از توان آن است، نه صرفاً دشوار.
SUMIFS با جفتکردن ستون Date دو بار این مسئله را حل میکند. فرمول =SUMIFS(E2:E25,A2:A25,">="&DATE(۲۰۲۶,۱,۱),A2:A25,"<="&DATE(۲۰۲۶,۳,۳۱)) عدد ۱۰۰۳٫۸۵ دلار را برای سهماهه نخست امسال برمیگرداند. قرار دادن هر مرز در DATE به جای تایپ آن بهصورت متنی، باعث میشود فرمول بدون توجه به قالببندی تاریخ سیستم شما درست کار کند.
افزودن یک جفت دستهبندی به آن، تفکیکی را به من میدهد که واقعاً میخواهم. هزینه مواد غذایی سهماهه نخست به ۳۷۹٫۷۵ دلار و آبوبرق به ۲۵۱٫۴۰ دلار میرسد، هر کدام با یک فرمول.
خطایی پیدا کردید؟ آن را به info@www.makeuseof.com بفرستید تا اصلاح شود.
اگر اصرار داشته باشید میتوانید با SUMIF هم به آن اعداد برسید. یک ستون کمکی که فصل را مشخص کند کار میکند، تفریق یک مجموع تجمعی از دیگری هم کار میکند، و SUMPRODUCT هم کار میکند. هر سه راه شما را با چیزی اضافی برای نگهداری باقی میگذارند، و این معمولاً نشانه آن است که فرمولی از کارایی افتاده است.
ترتیب آرگومانها جای دیگری هم سود میرساند. AVERAGEIFS و MAXIFS و MINIFS همگی بازه تجمیع را در ابتدا میگذارند و همان جفتهای معیار را میپذیرند، و COUNTIFS جفتها را بهتنهایی میگیرد. الگو را یکبار یاد بگیرید، و کل مجموعه را در اختیار دارید.
حتی وقتی تنها یک شرط وجود دارد هم سراغ SUMIFS میروم
نگهداشتن دو تابع در ذهن، هزینهاش بیشتر از آن آرگومان اضافه است
تحلیل بهندرت در نخستین پرسش متوقف میشود. ابتدا مجموع یک دستهبندی را میخواهم، بلافاصله میخواهم بر اساس روش پرداخت تفکیک شود یا به یک ماه محدود گردد، و بازنویسی SUMIF در آن نقطه یعنی وارد کردن آرگومانها به ترتیبی دیگر. شروع کردن با SUMIFS یعنی شرط بعدی فقط یک ویرگول فاصله دارد.
SUMIF همچنان دو مزیت کوچک دارد. محدوده جمع آن اختیاری است، پس جمعکردن یک ستون در برابر مقادیر خودش یک آرگومان کمتر نیاز دارد، و تفاوت اندازه محدوده جمع با محدوده معیار را میپذیرد و شکل را از سلول بالا-چپ تشخیص میدهد. در محاسبهی سریع مجموع در فایل شخص دیگری، این اختصار واقعاً مفید است.
هیچکدام از این سهولتها از بررسی دقیق سربلند بیرون نمیآیند. تحملپذیری تفاوت اندازه بهویژه بیشتر شبیه تله است، چون سلولهایی را که هرگز انتخاب نکردهاید جمع میزند و عددی منطقینما بدون هیچ هشداری میدهد. آرگومان اضافه در SUMIFS سختگیری میخرد، و سختگیری همان چیزی است که میخواهم در فرمولها نگه دارم. پس SUMIF را از واژگانم حذف نکردهام، اما دیگر از آنجا شروع نمیکنم؛ این دو چیز متفاوتاند، و ارزش دارد پیش از آنکه با صفحهگستردهای مهم روبهرو شوید، این عادت را در خود ایجاد کنید. همین عادت باعث میشود توابع جدیدتر اکسل را بیازمایم تا برایشان جایی در یک فایل کاری پیدا کنم.
مجموعهای شرطی نقطهی ورود هستند
پرسشی که سرانجام مرا به جای دیگری میفرستد
SUMIFS در هر سلول به یک پرسش پاسخ میدهد؛ اما وقتی میخواهم هر دستهبندی به تفکیک هر روش پرداخت شکسته شود، باید شبکهای از فرمولها را بنویسم و نگه دارم. دقیقاً همانجاست که GROUPBY و PIVOTBY کل کار را در یک فرمول انجام میدهند.
کار بعدی که میخواهم در این ردیاب امتحان کنم، دادن معیارهای SUMIFS از سلولها بهجای تایپ دستیشان است تا دو منوی کشویی با یک فرمول، گزارش تعاملی کوچکی بسازند. AVERAGEIFS یا COUNTIFS را جایگزین کنید و همان تنظیمات پاسخ میدهد که یک سفر معمولی چقدر هزینه دارد یا هر چند وقت یکبار به آنجا میروم.
منبع: این مطلب ترجمه و بومیسازی مقالهای از MakeUseOf به قلم Yasir Mahmood است. مشاهده مقاله اصلی