همانطور که میدانید، اکسل مجموعه ای از توابع را برای انجام محاسبات مختلف بر روی داده ها ارائه کرده است. در مقاله های قبل در مورد COUNTIFS و COUNTIF صحبت کردیم که برای شمارش سلول ها به صورت مشروط طراحی شده بودند. در هفته قبل در مورد تابع SUMIF که عمل جمع کردن مقادیر بر اساس یک شرط را انجام میدهد صحبت شد. حال وقت آن است که به سراغ نسخه پیشرفته تر تابع SUMIF برویم. تابع SUMIFS مقادیر مختلف در سلول های مختلف را بر اساس شرط های چندگانه با هم جمع میکند.
اگر با تابع sumif آشنا نیستید پیشنهاد میکنیم این مطلب آموزشی را مطالعه کنید:
کسانی که با تابع SUMIF آشنایی دارند ممکن است تصور کنند که برای تبدیل این تابع به تابع SUMIFS فقط کافیست یک S اضافه کنند و چند شرط اضافه کنند. این تصور کاملاً منطقی است ولی وقتی با مایکروسافت کار میکنید همیشه منطقی بودن کافی نیست.
فیلم آموزشی تابع sumifs در اکسل
توی قسمت قبل راجع به sumif گفتیم، توی این قسمت قراره راجع به #sumifs صحبت کنیم. این دو فرمول بسیار شبیه به هم هستن و تنها تفاوتشون در اینه که در #sumif شما یک شرط داشتید و #sumifs شما میتونید به تعداد دلخواه شرط تعریف کنید.
دانلود فایل اکسل ارائه شده در این قسمت:
در زیر فیلم آموزشی sumifs را مشاهده کنید:
تابع SUMIFS اکسل – ترکیب و کاربرد
از این تابع برای بدست آوردن مجموع چند سلول بر اساس شرط های چندگانه استفاده میشود. تابع SUMIFS در اکسل 2007 معرفی شد در نتیجه میتوانید در تمام ورژن های 2007 به بعد اکسل از این تابع استفاده کنید.
در مقایسه با SUMIF، تایع SUMIFS دارای ترکیبی پیچیده تر است:
=SUMIFS(sum_range, criteria_range1, criteria1,[criteria_range2, criteria2],...)
سه پارامتر اول ضروری هستند و دیگر پارامتر ها و شرط های مربوط به آنها اختیاری هستند.
sum_range : یک یا چند سلول که می بایست با یکدیگر جمع شوند. این مقدار میتواند یک سلول ، مجموعه ای از سلول ها یا نام یک محدوده باشد. تنها سلول هایی که دارای عدد هستند با هم جمع میشوند و سلول های خالی یا دارای مقدار متنی مورد محاسبه قرار نمی گیرد. (ضروری)
criteria_range1: محدوده اولی که می بایست بوسیله شرط مربوط به خود مورد بررسی قرار گیرد(ضروری)
criteria1 : اولین شرطی که می بایست بررسی شود(ضروری). شرط شما میتواند به شکل عدد، عبارت منطقی، آدرس سلول، متن یا دیگر توابع اکسل باشد. برای مثال میتوانید از شرط هایی مثل 10،”>=10″, A1، “گیلاس” ، TODAY() استفاده کنید.
criteria_range2, criteria2: اینها محدوده های اضافی و شرط های مربوط به آنهاست. شما میتوانید تا 127 جفت محدوده و شرط در این تابع استفاده کنید.
مهم: تابع SUMIFS با منطق AND کار میکند به این صورت که اگر تمام شرط ها صحیح باشد سلول های موجود در sum_range با هم جمع میشوند.
حال به نمونه ای از فرمول SUMIFS با دو شرط توجه کنید. فرض کنید لیستی از محموله های میوه از فروشندگان مختلف در اختیار دارید. نام میوه ها در ستون A ، نام فروشنده در ستون B و مقدار در ستون C ذکر شده است و شما میخواهید مجموع مقدار را برای هر میوه و هر فروشنده مشخص کنید. برای مثال تمام سیب های عرضه شده توسط “امیر”.
زمانی که در حال یادگیری چیز جدید هستید بهتر است با نمونه های ساده شروع کنید. بنابراین برای شروع پارامتر های تابع SUMIFS را برای مسئله خود مشخص میکنیم:
- sum_range – C2:C9
- criteria_range1 – A2:A9
- criteria1 – “سیب”
- criteria_range2 – B2:B9
- criteria2 – “امیر”
حال پارامترهای بالا را مونتاژ میکنیم و به فرمول زیر می رسیم:
=SUMIFS(C2:C9, A2:A9, "سیب", B2:B9, "امیر")
برای پالایش بیشتر فرمول خود میتوانیم به جای نام میوه و عرضه کننده آن از آدرس سلول های آنان در فرمول استفاده کنیم:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
استفاده از SUMIFS و SUMIF در اکسل
از آنجایی که در این مقاله به دنبال یافتن تمام راههایی هستیم که میتوان از طریق آن مجموع سلول ها را بر اساس چند شرط بدست آورد، در مورد مثال های فرمولی از هر دو تابع SUMIFS و SUMIF بحث خواهیم کرد. برای استفاده صحیح از این دو تابع می بایست شباهت ها و تفاوت های این دو تابع را به خوبی درک کنید.
اگرچه نقاط مشترک این دو تابع واضح و روشن است (پارامتر ها و اهداف تابع) ولی تفاوت های آنان اینگونه نیستند، هرچند این تفاوت ها بسیار مهم اند.
- ترتیب پارامتر ها
در توابع SUMIFS و SUMIF ترتیب پارامترها متفاوت است. به صورت مشخص، Sum_range در SUMIF پارامتر سوم و در SUMIFS پارامتر اول است.
در نگاه اول، ممکن است فکر کنید که مایکروسافت به صورت عمدی منحنی یادگیری را برای کاربران خود پیچیده کرده است ولی اگر به دقت به این تفاوت ها دقت کنید خواهید فهمید که دلایلی در پشت این پیچیدگی ها نهفته است. نکته مهم اینجاست که sum_range در SUMIF اختیاری است و در صورتی که آن را حذف کنید، هیچ مشکلی پیش نخواهد آمد و فرمول SUMIF شما مجموع مقادیر در محدوده را بدست خواهد آورد.
در تابع SUMIFS مقدار sum_range بسیار مهم و ضروری است و به همین دلیل نیز در ابتدا آمده است. احتمالاً متخصصان مایکروسافت تصور کرده اند که بعد از اضافه کردن محدوده و شرط دهم یا صدم ، ممکن است بعضی افراد فراموش کنند که محدوده مورد نظر را برای جمع کردن به فرمول اضافه کنند.
خلاصه اینکه اگر این فرمول ها را کپی یا اصلاح میکنید، به یاد داشته باشید که ترتیب آنها را رعایت کنید.
2. اندازه پارامتر های sum_range و criteria_range
در تابع SUMIF تا زمانی که بالاترین سلول سمت چپ درست انتخاب شده باشد، حتماً نباید که اندازه محدوده sum_range با criteria_range یکسان باشد. اما در تابع SUMIFS هر criteria_range می بایست به اندازه sum_range ردیف و ستون داشته باشد.
برای مثال،
=SUMIF(A2:A9,F1,C2:C18)
نتیجه صحیح را ارائه میکند زیرا اکسل فقط بالاترین سلول در سمت چپ را در sum range در نظر میگیرد(سلول C2 در این مثال) و سپس تعداد ردیف و ستونی را که در پارامتر range مشخص شده است به آن اضافه میکند.
فرمول
=SUMIFS(C2:C9, A2:A9, "سیب", B2:B10, "امیر")
خطای #value! را برمیگرداند زیرا criteria_range2 (B2:B10) اندازه برابری با criteria_range1 (A2:A9) و sum_range (C2:C9) ندارد.
خوب، صحبت در مورد تئوری کافیست وقت آن است که به صورت عملی به این توابع بپردازیم.
نحوه استفاده از تابع SUMIFS در اکسل – مثال های فرمولی
در سطرهای بالا در مورد فرمول ساده SUMIFS با دو شرط صحبت کردیم. با همین روش، میتوانید از فرمول های SUMIFS همراه با شرط های چندگانه به اشکال عدد، تاریخ، عبارت های منطقی و دیگر توابع اکسل استفاده کنید.
مثال 1: تابع SUMIFS با عملگرهای مقایسه ای
در مثال جدول عرضه کننده میوه، فرض کنید که میخواهید مجموع تمام تحویل هایی که توسط علی صورت گرفته و مقدار آن 200 و بیشتر است را بدست آورید. برای انجام این کار، می بایست از عملگر مقایسه ای” بزرگتر یا مساوی” در شرط دوم استفاده کنید تا فرمول زیر حاصل شود:
=SUMIFS(C2:C9,B2:B9,"علی",C2:C9,">=200")
نکته: لطفاً توجه داشته باشید که در فرمول های SUMIFS اکسل، عبارت های منطقی که دارای عملگرهای مقایسه ای هستند حتماً می بایست در درون “” قرار گیرند.
ما در مطلبی که در مورد تابع SUMIF عنوان کردیم به صورت مفصل در مورد تمام عملگرهای مقایسه ای صحبت کردیم. در تابع SUMIFS نیز میتوان از همان عملگرها و به همان صورت استفاده کرد. برای مثال فرمول زیر مقدار مجموع تمام سلول های محدوده C2:C9 را که بزرگتر یا مساوی 200 و کوچکتر و مساوی 300 هستند را نشان میدهند.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
مثال2: استفاده از تابع SUMIFS همراه با تاریخ
در مواردی که میخواهید از تابع SUMIFS همراه با تاریخ و بر اساس تاریخ فعلی استفاده کنید، می بایست تابع today()در قسمت شرط تابع SUMIFS خود بکارگیرید. فرمول زیر مجموع مقادیر ستون D را به شرطی که مقدار متناظر آن در ستون C در 7 روز گذشته قرار گیرد بدست می آورد (امروز نیز جزیی از این 7 روز است):
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
توجه: زمانی که از تابع دیگری همراه با عملگر مقایسه ای بعنوان شرط استفاده میکنید، می بایست از علامت & برای متصل کردن رشته استفاده کنید. برای مثال،
"<="&TODAY()
با همین روش میتوانید از تابع SUMIF نیز برای بدست آوردن مجموع در یک تاریخ مشخص استفاده کنید. برای مثال، فرمول های SUMIFS زیر مجموع C2:C9 را به شرطی که تاریخ متناظر آنان در ستون B بین 1 اکتبر تا 31 اکتبر 2014 قرار گیرد بدست می آورد:
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
مثال3: تابع SUMIFS همراه با سلول های خالی و غیر خالی
زمانی که گزارش ها یا دیگر داده ها را تحلیل میکنیم، ممکن است لازم باشد مقادیر متناظر با سلول های خالی و غیر خالی را نیز بدست آوریم.
شرط | توضیحات | مثال فرمولی | |
سلول های خالی | “=” | مجموع متناظر با سلول های خالی که مطلقاً خالی هستند و درون آنها هیچ جیز نیست- نه فرمول، نه رشته با طول صفر |
=SUMIFS(C2:C10,A2:A10,"=",B2:B10, "=") مقادیر محدوده C2 تا C10 را جمع میکند به شرطی که مقدار متناظر آنها در ستون A و B مطلقا خالی باشند. |
“” | مقادیری را جمع میکند که متناظر با سلول هایی است که ظاهراً خالی هستند ولی دارای رشته های با طول صفر اند که توسط دیگر توابع برگردانده شده است. برای مثال سلول هایی که دارای فرمولی مثل =”” باشند. |
=SUMIFS(C2:C10,A2:A10,"",B2:B10, "") با همان شرط فرمول بالا مقادیر C2:C10 را جمع میکند ولی شامل رشته های خالی(با طول صفر) نیز میشود. |
|
سلول های غیر خالی | “<>” | مقادیر متناظر با سلول های غیر خالی از جمله سلول های دارای رشته با طول صفر را جمع میکند . |
=SUMIFS(C2:C10,A2:A10,"=",B2:B10, "=") مقادیر محدوده C2 تا C10 را جمع میکند به شرطی که مقدار متناظر آنها در ستون A و B خالی نباشد، به این معنی که رشته های خالی نیز نداشته باشند. |
SUM-SUMIF یا SUM / LEN | مقادیر متناظر با سلول های غیر خالی را جمع میکند. سلول های دارای رشته با طول صفر را شامل نمیشود. |
=SUM(C2:C10)-SUMIFS(C2:C10, A2:A10, "", B2:B10, "") =SUM((C2:C10)*(LEN(A2:A10)>0)*(LEN(B2:B10)>0)) مقادیر محدوده C2 تا C10 را جمع میکند به شرطی که مقدار متناظر آنها در ستون A و B خالی نباشند.این فرمول سلول های دارای رشته خالی را شامل نمیشود. |
حال به بررسی نحوه استفاده از فرمول های SUMIFS در مورد سلول های خالی و غیر خالی با داده های واقعی میپردازیم.
فرض کنید، تاریخ سفارش در ستون B، تاریخ تحویل در ستون C قرار دارد و تعداد کالا نیز در ستون D قرار دارد. چگونه میتوان تعداد کل محصولاتی که تحویل داده نشده است را بدست آورد؟ به این معنی که شما می بایست مقادیر متناظر با سلول های غیر خالی در ستون B و سلول های خالی در ستون C را بدست آورید.
راه حل این است که از فرمول SUMIFS همراه با دو شرط استفاده کنیم:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
استفاده از تابع SUMIF همراه با چند شرط OR
همانطور که در ابتدای این مطلب اشاره شد، تابع SUMIFS با منطق AND طراحی شده است، ولی اگر لازم باشد مقادیری را بر اساس چند شرط OR بررسی کنید چطور؟ به این معنی که حداقل یکی از شرط ها صادق باشد.
مثال1: SUMIF+ SUMIF
ساده ترین راه حل این است که مقادیر برگردانده شده توسط چند تابع SUMIF را باهم جمع کنیم. برای مثال، فرمول زیر مجموع مقدار محصولاتی که توسط محمد و علی تحویل داده شده است را نشان میدهد:
=SUMIF(C2:C9,"محمد",D2:D9) + SUMIF(C2:C9,"علی",D2:D9)
همانطور که می بینید، تابع SUMIF اول مقدار مربوط به علی را محاسبه میکند و تابع دیگر، مقدار مربوط به محمد را برمیگرداند و سپس مجموع این دو مقدار را بدست می آورید.
مثال2. توابع SUMIF و SUMIFS همراه با پارامتر آرایه
شیوه ای که در بالا توضیح داده شد بسیار ساده است و در زمانی که فقط دو شرط وجود دارد میتواند بسیار کاربردی باشد. ولی اگر بخواهید چندین شرط را مورد بررسی قرار دهید ممکن است SUMIF+ SUMIF بسیار طولانی شود. در این موارد، شیوه بهتر این است که از یک شرط آرایه ای در تابع SUMIF استفاده کنید.
ابتدا شرط های خود را لیست کنید و آنها را با استفاده از کاما از یکدیگر تفکیک کنید سپس تمام آنها را در میان{} قرار دهید. به این مجموعه به لحاظ فنی یک آرایه گفته میشود.
در مثال قبل، در صورتی که بخواهید مجموع مربوط به محمد، علی و امیر را بدست آورید. شرط آرایه شما شبیه به
{“محمد”,”علی”,”امیر”}
خواهد شد. و تابع کامل SUMIF به صورت زیر میشود:
=SUMIF(B2:B9, {"محمد","علی","امیر"} ,C2:C9)
همانطور که مشاهده میکنید یک فرمول آرایه بسیار خلاصه تر از فرمول SUMIF+ SUMIF است و به شما اجازه میدهد مقادیر بسیار زیادی را در یک آرایه وارد کنید.
این رویکرد را میتوان هم برای اعداد و هم برای مقادیر متنی به کار گرفت. برای مثال، اگر به جای نام عرضه کننده در ستون B شما ID مربوط به عرضه کنندگان را مثل 1,2,3 یا غیره را داشتید، فرمول شما شبیه به فرمول زیر میشد:
=SUM(SUMIF(B2:B9, {1,2,3} , C2:C9))
برخلاف متن، اعداد نیازی به قرارگرفتن در “” ندارند.
مثال3. توابع SUMPRODUCT & SUMIF
در بعضی موارد ممکن است بخواهید شرط های خود را ب جای قرار دادن در فرمول، آنها را در سلول قرار دهید. برای این کار میتوانید از ترکیب SUMPRODUCT & SUMIF که مقادیر درون آرایه را در هم ضرب میکند استفاده کنید و مقادیر مجموع محصولات مورد نظر را برگردانید:
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
که G2:G4 سلول هایی هستند که شرط شما در آن قرار دارد – نام عرضه کننده در مثال ما – صورت عملی این فرمول در تصویر زیر نشان داده شده است.
البته، اگر راغب هستید میتوانید از روش آرایه برای انجام این کار استفاده کنید و نتیجه هر دو روش تفاوتی با هم ندارد:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
تابع SUMIFs همراه با شرط های چندگانه or
در صورتی که به جای استفاده ساده از شرط های or، میخواهید از چندین مجموعه از شرطها استفاده کنید، می بایست به جای SUMIF از SUMIFs استفاده کنید. فرمول های این تابع نیز با مورد بالا تفاوتی چندانی ندارد.
مثل همیشه، ارائه یک مثال میتواند درک روشنتری از مسئله ایجاد کند. در جدول عرضه کنندگان میوه، تاریخ تحویل (ُستون d) را اضافه کنید و مقدار کل محصولی را که توسط mike,john و pete تحویل داده شده است، بدست آورید.
مثال1: SUMIFs+ SUMIFs
فرمولی که به این شیوه تولید میشود تکراری به نظر میرسد و طولانی است ولی فهم آن اسان است و مهمتر اینکه جواب صحیحی را برمیگرداند:
=SUMIFS(D2:D9,C2:C9, "Mike", E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014") +SUMIFS(D2:D9, C2:C9, "John", E2:E9, ">=10/1/2014", E2:E9, "<=10/31/2014") +SUMIFS(D2:D9, C2:C9, "Pete", E2:E9, ">=10/1/2014" ,E2:E9, "<=10/31/2014")
همانطور که مشاهده میکنید توابع جداگانه SUMIFS را برای هرکدام از عرضه کنندگان نوشتیم که هرکدام دارای دو شرط هستند. بزرگتر یا مساوی 1 اکتبر و کوچکتر یا مساوی 31 اکتبر 2014. وسپس جواب ها را با هم جمع میکنیم.
مثال2. توابع SUMIFS و SUM با یک پارامتر آرایه
در مثال قبل تلاش کردیم تا ماهیت این روش را روشن کنیم حال میتوان فقط فرمول ها را کپی کرد، ترتیب پارامترها را تغییر داد(اگر به یاد داشته باشید ترتیب پارامترها در SUMIFS و SUMIF با هم متفاوت بود) و سپس شرط اضافی را به فرمول افزود. فرمولی که ایجاد میشود بسیار خلاصه تر از SUMIFS+ SUMIFS است:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
نتیجه ای که این فرمول برمیگرداند دقیقا شبیه به چیزی است که در تصویر بالا نشان داده شده است.
مثال3. SUMPRODUCT & SUMIFS
اگر به یاد داشته باشید، روش استفاده از sumproduct با دو روشی که بررسی کردیم متفاوت بود به این صورت که در روش sumproduct نیازی به وارد کردن شرطها در فرمول نبود و آنها را در سلول وارد میکردیم. زمانی که چند مجموعه از شرط ها را داشته باشیم، استفاده از sumproduct به تنهایی کافی نیست و شما می بایست از isnumber و match نیز استفاده کنید.
بنابراین با فرض اینکه نام عرضه کنندگان در سلول های h1:h3 قرار دارد، تاریخ شروع در سلول h4 و تاریخ پایان در سلول h5 قراردارد، فرمول sumproduct ما به شکل زیر میشود:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
سوال بسیاری از افراد این است که چرا از — در فرمول SUMPRODUCT استفاده میکنیم.نکته اینجاست که فرمول SUMPRODUCT فقط اعداد را مورد محاسبه قرار میدهد و زمانی که عملگرهای مقایسه ای در فرمول ما مقادیر true/false را برمیگرداند، که مقادیری غیر عددی هستند، می بایست برای تبدیل آنها به مقادیر 0 و 1 از علامت –- استفاده کرد که به زبان فنی double unary operator گفته میشوند. علامت اول true/false را تبدیل به -1 و 0 میکند و علامت دوم منفی 1 را به مثبت تبدیل میکند و در نتیجه تبدیل به 0 و 1 می شوند که برای تابع SUMPRODUCT نیز قابل خواندن است.
امیدواریم که توضیحات بالا این قضیه را روشن کرده باشد.اگر هنوز برایتان روشن نشده کافیست این قانون ساده را به یاد داشته باشید که زمانی که در فرمول SUMPRODUCT خود از عملگرهای مقایسه ای استفاده میکنید می بایست از علامت –استفاده کنید.
استفاده از تابع sum در فرمول های آرایه
اگر به یاد داشته باشید، مایکروسافت در ورژن 2007 خود از تابع sumifs استفاده کرد. اگر کسی هنوزه از ورژن های 2003 یا 2000 یا قبلتر استفاده کند، می بایست برای بدست آوردن مجموع با چندین شرط از تایع Sum به صورت آرایه استفاده کنید. طبیعتا این شیوه در ورژن های مدرن اکسل نیز کارایی دارد، و میتوان آن را یک رقیب سنتی برای تابع sumifs در نظر گرفت.
در فرمول Sumif که در بالا توضیح داده شد، از پارامتر آرایه ای استفاده کردید، ولی فرمول آرایه چیزی متفاوت است.
مثال1. تابع Sum با شرط های چندگانه and در ورژن های 2003 و ماقبل
به اولین مثال این مطلب برمیگردیم که درآن مجموع مقادیر مربوط به هر میوه و عرضه کننده را بدست آوردیم.
همانطور که تاکنون متوجه شده اید، این کار را میتوان به سادگی با استفاده از یک تابع Sumifانجام داد:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
حال ببینیم که چگونه میتوان این کار را با استفاده از ورژن های قدیمی اکسل انجام داد:
ابتدا تمام شرط هایی که می بایست بررسی شود را به صورت range=”condition” می نویسیم. در این مثال، دو مورد وجود دارد:
Condition 1: A2:A9="apples"
Condition 2: B2:B9="Pete"
آنگاه یک فرمول sum مینوسیم که تمام محدوده وشرط های شما را در هم ضرب کندو هر کدام را درون پرانتز قرار میدهیم. آخرین عنصری که ضرب میشود sum range است.
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
همانطور که در تصویر زیر نشان داده شده است، این فرمول به خوبی در آخرین ورژن اکسل نیز کار میکند.
توجه: زمانی که هر فرمول آرایه ای را وارد میکنید، می بایست حتما Ctrl + Shift + Enter را فشار دهید. زمانی که این کار را انجام می دهید، فرمول شما در درون {} قرار میگیرد که این تصویر به این معنی است که فرمول آرایه شما به درستی وارد شده است. اگر سعی کنید {} به صورت دستی وارد کنید، فرمول شما تبدیل به یک رشته متن خواهد شد و کار نخواهد کرد.
مثال2. تابع Sum و فرمول های آرایه در ورژن مدرن اکسل
حتی در ورژن های جدید اکسل نیز قدرت تابع sum مورد غفلت قرار نمیگیرد. تابع sum به صورت آرایه ای فقط یک تمرین ذهنی نیست و در عمل کاربرد های بسیار زیادی دارد که یکی از این کاربردها را در مثال بعدی ارائه کرده ایم.
فرض کنید، دو ستون b و c را در اختیار دارید و میخواهید بدانید در صورتی که مقدار ستون C بزرگتر یا مساوی 10 باشد، مقدار ستون C چند برابر ستون b است .راه حل سریعی که به ذهن می رسد استفاده از فرمول آرایه Sum است.
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
اگر فکر میکنید فرمول بالا استفاده کاربردی ندارد از یک زاویه دیگر به آن نگاه کنید J
فرض کنید یک لیست سفارش به شکلی که در تصویر زیر آمده دارید و میخواهید بدانید چند نوع کالا در تاریخ مشخص شده به صورت کامل تحویل داده نشده است. با تبدیل این خواسته ها به زبان اکسل ، شرط های زیر را خواهیم داشت.
شرط1: مقدار در ستون b(کالاهای سفارش داده شده) بزرگتر از صفر است.
شرط2: مقدار موجود در ستونC (تحویل داده شده)کمتر از مقدار موجود در ستون b است.
شرط 3: تاریخ در ستون d(تاریخ سررسید) کمتر از 11/1/2014 است.
با ترکیب این 3 محدوده و شرط ، به فرمول زیر خواهید رسید:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
فرمول ها و مثال هایی که در بالا توضیح داده شد فقط پوسته ای از توابع SUMIFS و SUMIF را مشخص کردند. ولی خوشبختانه این مثال های شما را در مسیری درست قرار داده اند و حال میتوانید در کاربرگ خود مقادیر را با هم جمع کنید حتی اگر از پیچیده ترین شرط ها هم استفاده شده باشد.
با سلام
من میخوام با استفاده از فرمول سام ایفز همزمان داده های موجود در یک سطر و یک ستون را از دیتا بیس جستجو کنم
مشکلی که دارم اینه که نمیشه همزمان سلول های سطر یا ستون را آدرس دهی مطلق کرد .
یعنی هر وقت میخوام سلول های ستون را آدرس دهی مطلق کردم ، باید مجددا فمول سلول های سطر را ویرایش کنم برای آدرس دهی مطلق .
ممنون میشم نمونه فایل ماکرو برام معرفی بفرمائید .
با تشکر
منظورتون رو درست متوجه نشدم ولی به صورت کلی برای داینامیک کردن بازه ها می تونید از ترکیب تابع offset و match استفاده کنید.
تابع offset در اکسل
تابع match در اکسل
سلام
یک تابع شرطی میخوام بنویسم
که مثلا اگر تولید کننده بتن فلان بود و رده مقومت فلان بود و عدد مقاومت کمتر از فلان بود مقطع بتن ریزی رو به من بدهد
مشکل اینجاست که محل بتن ریزی چند کاراکتری هست و من فقط نوشته های فارسی درون سلول رو میخوام برام بیاره و نه همه اطلاعات سلول
برای اینکه فرمول نویسی ساده تر بشه اول باید دیتا رو درست و اصولی ذخیره کنید و گرته نوشتن این مدل فرمول ها ساده نیست و نیاز داره توی فرمول نویسی خیلی حرفه ای باشید.
سلام وقت بخیر
ممکنه یه فایل بفرستم سوالم و بپرسم؟
سلام
توی تلگرام می تونید بفرستید. شماره رو از داخل این لینک بردارید.
VPN ندارم ایمیل میشه؟
بله ایمیل کنید.
الان توضیحتون رو برا دوستان خوندم ، ول یتو فرمول نویسی بهم خطا میده. دو تا ستون p و o دارم که قراره مقادیرش با هم جمع بشه به شرطی که در ستون N دارای کد مثلا ۴۰۱۱۱ باشه. من این فرمول رو نوشتم: این اطلاعات در شیت بدهکاری هست قراره در شیت دیگه جمع رو برام بده. ضمنا همه سلول ها هم پر نیست سلول خالی هم دارم بدون کد هم دارم . ممکنه ایرادش برا خاطر خالی بودنشون باشه؟
=sumproduct(‘بدهکاری ها’!(o8:o44+p8:p44)*(sumifs(o8:o44,n8:n44,”40111″)*(sumifs(p8:p44,n8:n44,”40111″))
ضمن تشکر فراوان از آموزش خوبتون ،لطفا اگر فرمول رو برای شیت دیگه بخوایم بنویسیم چطوری میشه؟ مثلا همین جدول اطلاعات تو یه شیت هست ولی ما جمع رو میخوایم تو یه شیت دیگه بدست بیاریم.
پیشاپیش از پاسختون سپاسگزارم
توی فرمول نویسی اکسل می تونید به شیت یا فایل دیگه ارجاع بدید. هنگام نوشتن فرمول، هر کجا لازم هست اول شیت رو انتخاب و بعد سلول ها رو انتخاب می کنید. توی فرمول خودش به صورت اتوماتیک نام شیت رو هم اضافه می کنه.
سلام امیر جان خسته نباشی
یک تابع نوشتم که در بازه زمانی خاص مقدار جنس فروخته شده رو حساب کنه! تابع کار می کنه و هیچ مشکلی نیست ولی حالا می خوام بگم در همین بازه زمان مشتری خاص چقدر از همین جنس خریده
=SUMIFS(Table1[مقدار];Table1[تاريخ];”>=”&L2;Table1[تاريخ];”<=”&M2;Table1[كالا];L4)
سلام
همون طور که شرط کالا رو مشخص کردید، توی آرگومان های بعدی ستون مشتری و نام مشتری رو اضافه کنید.
مثلا:
امیر جان هر چی تشکر کنم کمه! هر وقت سوال کردم با آقایی و بزرگ منشی جواب دادی! ان شاء الله همیشه سالم و تندرست باشی در پناه حق
موفق باشید.
با سلام و درود چطوری میتونم در قسمت sum rang جمع دو ستون رو اضافه کنم ایا امکان داره ؟
ممنون
سلام
امکانش نیست. باید دو فرمول بنویسید و با هم جمع کنید.
=sumifs(°°°)+sumifs(∆∆∆)
یا اینکه از تابع sumproduct استفاده کنید.
=sumproduct((a1:a10+b1:b10)*(coneition1)*(condition2))
ممنون از لطفتون
سلام
ایا میتونیم با توجه به اینکه اطلاعات در شیت 1 هست در شیت 2 sumifs بزنیم ؟
سلام
بعد از اینکه تابع رو نوشتید و پارانتز باز کردید می تونید روی شیت اول کلیک کنید و محدوده مورد نظر انتخاب کنید و بعد از گذاشتن جدا کننده مجدد به شیتی که در حال فرمول نویسی هستید برگردید.
خدا قوت
ميخوام اعدادي كه با استفاده از vlookup توي ستون هاي مختلف جمع اوري كردم رو با sumif يا sumifs توي هر سطر جمع كنم ولي گاهي درست جمع ميكنه و گاهي اشتباه
=SUMIFS(J5:AJ5; $J$2:$AJ$2; $G$4)
ميشه لطفا راهنمايي كنيد
سلام
سوالتون اشتباه هست. تابع sumifs برای جمع شرطی استفاده میشه. در ضمن تابع vlookupفقط یک مقدار رو بر می گردونه چی رو دقیقا میخواید جمع کنید؟
سلام
اگر چند شرط از چند ستون داشتیم بازهم باید از آرایه برای هرکدوم استفاده کنیم؟ مثلا مجموع خرید میوه های هلو و سیب از مغازه های محمدی و رضایی
برای این مدل مسائل بهتر هست از تابع sumproduct (+) استفاده کنید.
ببخشید میشه با دکر مثال توضیح بدید؟ چون سام پروداکت هم میزنم جواب نمیده خیلی برام مهمه ممنون میشم اگه بلدید واضحتر توضیح بدید
هزینه پاسخ به این سوال 20هزارتومن میشه. می تونید از لینک زیر پرداخت کنید و در قسمت توضیحات سفارش سوالاتون قرار بدید تا بعد از پاسخ به صورت یک فایل اکسل به ایمیلتون ارسال بشه.
اگر میخواید که فیلم آموزشی توضیحات در اختیارتون قرار بگیره 50 هزارتومان واریز کنید.
https://skillpro.ir/product/q/
چطور پاسخ سوالات قبلی رو رایگان دادید اونوقت به ما که رسید پولی شد؟ ضمنا من که پکیج آموزشیتون رو نخاستم فقط یک سوال پرسیدم اگه واقعا برای جواب یک سوال هم میخاید پول بگیرید که اوکی موفق باشید 《زکات علم نشر آن است حضرت علی ع》
سلام
ما به اندازه کافی زکات علممون دادیم و بیش از 500 آموزش به صورت رایگان منتشر کردیم. می تونید مطالب سایت مطالعه کنید، لینک رو هم پاسخ قبلی ارسال کردیم. شما علمی ندارید که نشرش بدید؟ سایتی؟ اینستاگرامی؟ تلگرامی؟
زمانی که قرار بذاریم برامون ارزشمند هست و نمی تونیم همیشه لقمه آماده در اختیار دوستان قرار بدیم.
موفق و پیروز باشید.
من در محل کارم چه نرم افزارهای عمومی مثل اکسل و چه نرم افزارهای تخصصی کارم مثل جم کام رو به راحتی و رایگان یاد همکاران میدم ضمنا بازم میگم من که کل آموزش رو نخواستم فقط یه سوال داشتم و جوابشو که تابع DSUM هست رو از کسی که میخاست زکات علمشو بده گرفتم اوکی موفق باشید
وقت بذارید و آموزش ها رو توی آپارات منتشر کنید و زکات علمتون بدید.
بله حتما
مثلا فرض کنیم از A1 تا A5 شامل میوه های هلو، سیب، پرتقال، انار و گلابی است
از ستون B1 تا B5 شامل نام های علی، عباس،رضا،حسن و حسین است ستون C1 تا C5 شامل درآمدها هست اگه بخایم مجموع درآمد هلو و پرتقال را از فروشندگان عباس و حسن حساب کنیم چکار باید بکنیم؟
ممنون میشم با فرمول نشون بدید