محاسبه مجموع خرکی خریدهایم با یک 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 تنها یک معیار میپذیرد، پس این پرسش از اساس و نه به واسطه نValidators رسیدن به آن، خارج از دسترس است.
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 است. مشاهده مقاله اصلی