شما سالها از مبلغ استفاده کرده اید. این تعداد را اضافه می کند ، کار خود را انجام می دهد ، و شما احتمالاً دو بار در مورد آن فکر نمی کنید. اما در حالی که در حال کلیک بر روی فیلترها و ساخت ستون های یاور کثیف هستید ، اکسل برای شما نگه داشته است.
شما سالها از مبلغ استفاده کرده اید. این تعداد را اضافه می کند ، کار خود را انجام می دهد ، و شما احتمالاً دو بار در مورد آن فکر نمی کنید. اما در حالی که در حال کلیک بر روی فیلترها و ساخت ستون های یاور کثیف هستید ، اکسل برای شما نگه داشته است.
یک عملکرد باهوش تر وجود دارد که در دید ساده پنهان شده است ، که می تواند وزنه برداری سنگین را برای شما انجام دهد.
چرا جمع به تنهایی کافی نیست
جمع معادل صفحه گسترده یک دعوت باز است: این همه چیز را در کل استقبال می کند ، خواه در آنجا تعلق داشته باشد یا نه. این عالی است که شما فقط به یک ستون سریع نیاز دارید ، اما در کار در دنیای واقعی ، به ندرت همه چیز را اضافه می کنید.
بیایید تصور کنیم که شما یک مدیر مدرسه هستید که به یک برگه عظیم اکسل از هزاران سوابق دانشجویی خیره شده اید. شما می خواهید نمره کل برای یک دانش آموز مجرد ، مثلاً جان ، در هر آزمون. با جمع تنها ، این بدان معناست که به صورت دستی هر ردیف را در جایی که جان ظاهر می شود ، پیدا کنید و نمرات خود را یک به یک اضافه کنید و به این امید که سلول را از دست ندهید.
= جمع (C2 ، C4 ، C6 ، …)
این خسته کننده است ، مستعد خطا است و هرچه مجموعه داده شما بزرگتر شود ، بدتر می شود.
جمع نمی داند که یک ستون یک نام ، یک بخش یا یک خط تولید است. این فقط با خوشحالی هر شماره ای را که به آن پرتاب می کنید اضافه می کند. این برای لیست های ساده خوب است ، اما لحظه ای که می خواهید نتایج جان را از سارا متمایز کنید ، یا همه چیز را به جز یک بخش فیلتر کنید ، جمع می شود.
در مقابل ، Sumifs برای این کار طراحی شده است. به جای اینکه همه چیز را در دید قرار دهید ، از شما می پرسد که تعداد آنها در کل متعلق است. یک فرمول sumifs به این شکل است:
= sumifs (C2: C10 ، A2: A10 ، “جان”)
به زبان انگلیسی ساده ، این به اکسل می گوید که اسامی موجود در ستون A را جستجو کنید ، هر ردیف را که می گوید “جان” را پیدا کنید و سپس نمرات تطبیق را از ستون C. اضافه کنید. شما نیازی به فیلتر ، اسکن دستی یا ستون های یاور ندارید – فقط یک فرمول.
و این فقط برای داده های دانشجویی نیست. تصور کنید هزینه های پروژه را توسط بخش ، تجزیه و تحلیل فروش بر اساس منطقه یا محاسبه کمیسیون ها توسط نماینده فروش و قلمرو تصور کنید. مبلغ شما را به سمت راه حل های کثیف وادار می کند ، اما Sumifs بر اساس شرایطی که مشخص می کنید به شما می دهد.
حال به این فکر کنید که معیارهای شما تغییر می کند ، که آنها همیشه انجام می دهند. شاید شما دیگر نمی خواهید که این سه ماهه باشد. شما به ماه گذشته نیاز دارید. شاید شما می خواهید یک خط محصول خاص را حذف کنید ، یا فقط سفارشات را با یک مقدار خاص درج کنید. با جمع ، دوباره به فیلتر کردن و انتخاب مجدد دوباره باز می گردید. با SUMIFS ، شما فقط شرایط را تغییر می دهید و فرمول به طور خودکار تنظیم می شود.
sumifs برای کار واقعی باهوش تر است
نحو برای عملکرد SUMIFS به گونه ای طراحی شده است که چندین شرایط را به طور همزمان اداره کند و فقط مقادیر مربوط به همه آنها را برآورده می کند:
= sumifs (sum_range ، معیارها_RANGE1 ، معیارها 1 ، معیارها_ورژ 2 ، معیارها 2 ، [معیارها_range3 ، معیارها 3] ،…)
در نگاه اول ، کمی ارعاب کننده به نظر می رسد. اما هنگامی که آن را تجزیه می کنید ، واقعاً ساده است: شما از اکسل می خواهید که شماره های مشخص شده خود را اضافه کنید ، اما فقط در مواردی که معیارها در معیارها صحیح است ، و در جایی که معیار 2 در معیارهای_range2 و غیره صادق است.
همچنین SUMIF وجود دارد ، که دقیقاً به همان روش کار می کند اما فقط یک شرایط واحد را امکان پذیر می کند. من تمایل دارم که حتی برای یک شرط ، با SUMIF بچسبم ، زیرا اگر بعداً نیاز به اضافه کردن دیگری داشته باشم ، لازم نیست فرمول را بازنویسی کنم.
در اینجا یک نمونه عملی از SUMIFS با داده های فروش آورده شده است:
= sumifs (درآمد ، منطقه ، “شرق” ، کانال فروش ، “آنلاین” ، “> 500”) = sumifs (L2: L100 ، A2: A100 ، “Asia” ، D2: D100 ، “آنلاین” ، J2: J100 ، “> 400”)
در اصل ، این فرمول به اکسل می گوید که ستون درآمد (در L2: L100) را طی کند و مقادیر را اضافه کند ، اما تنها اگر منطقه (در A2: A100) آسیا باشد ، کانال فروش (در D2: D100) بصورت آنلاین است و قیمت (در J2: J100) بیشتر از 500 دلار است.
شما می توانید در شرایط بیشتر – شهر ، تاریخ ، تاریخ فروش ، اولویت سفارش ، خط تولید – و اکسل هر یک را بررسی کنید. فقط ردیف هایی که هر شرایط را برآورده می کنند در کل گنجانده شده اند. Sumifs می تواند تا 127 معیار را جمع کند ، که حتی برای پیچیده ترین گزارش ها نیز بیش از حد کافی است.
بهترین بخش این است که فرمول استاتیک نیست. اگر داده های شما تغییر می کند ، بگویید یک ردیف اکنون شرایط را برآورده می کند ، فرمول به طور خودکار به روز می شود. این امر باعث می شود که برای ردیابی فروش بر اساس منطقه ، خلاصه کردن هزینه ها بر اساس طبقه بندی یا نظارت بر موجودی بر اساس نوع محصول ، بسیار ارزشمند باشد.
و فقط در صورت تعجب ، تاریخ ها برای sumifs مشکلی ندارند. شما می توانید از آن برای محاسبه کل تاریخ (یا قبل) تاریخ خاص با یک فرمول ساده استفاده کنید ، مانند این:
= sumifs (درآمد ، تاریخ سفارش ، “> = 1/1/2016”) = sumifs (N2: N100 ، F2: F100 ، “> = 1/1/2016”)
تنها جزئیاتی که باید به آن توجه کنید این است که وقتی معیارهای شما شماره ای مانند تاریخ یا متن نیست – باید آن را در علائم نقل قول بپیچید.
Sumifs در تمام نسخه های مدرن اکسل ، با شروع اکسل 2007 کار می کند ، و به طور مداوم اجرا می شود که آیا شما از برنامه دسک تاپ ، اکسل آنلاین یا حتی برنامه تلفن همراه استفاده می کنید. اگر آن را بیش از Excel ، چه در وب و چه در برنامه تلفن همراه آن ترجیح می دهید ، به طور کامل در Google Sheets پشتیبانی می شود.
مهم نیست که در کجا کار می کنید ، Sumifs یک ارتقا قابل اعتماد از مبلغ قدیمی قدیمی است.