У овом чланку ћемо научити како се користи функција ИФ уместо функција СУМПРОДУЦТ и СУМИФС у програму Екцел.
Сценариј:
Једноставним речима, када радимо са дуго раштрканим скупом података, понекад морамо пронаћи збир бројева са неким критеријумима изнад њега. На пример, проналажење збира плата у одређеном одељењу или постављање више критеријума по датуму, именима, одељењима или чак бројевима података попут плата испод вредности или количине изнад вредности. За ово обично користите функцију СУМПРОДУЦТ или СУМИФС. Али не бисте веровали, исту функцију обављате са Екцел основном функцијом ИФ функцијом.
Како решити проблем?
Мора да размишљате како је ово могуће, да изведете логичке операције над низовима табела користећи ИФ функцију. ИФ функција у екцелу је врло корисна, провест ће вас кроз неке тешке задатке у Екцелу или било којем другом језику за кодирање. ИФ функција тестира услове на низу који одговарају захтеваним вредностима и враћа резултат као низ који одговара Труе условима као 1 и Фалсе као 0.
За овај проблем користићемо следеће функције:
- СУМ функција
- ИФ функција
Биће нам потребне ове горе наведене функције и неки основни смисао за рад са подацима. логички услови на низовима могу се применити помоћу логичких оператора. Ови логички оператори раде и на тексту и на бројевима. Испод је генеричка формула. { } витичасте заграде су чаробни алат за извођење формула низа са функцијом ИФ.
Општа формула:
| { = СУМ (ИФ ((логичко_1) * (логичко_2) * … * (логичко_н), збир_марица)) } |
Напомена: За увијене заграде ( { } ) Употреба Цтрл + Схифт + Ентер при раду са низовима или опсезима у Екцелу. Ово ће подразумевано генерисати увијене заграде на формули. НЕ покушавајте да кодирате знакове уврнутих заграда.
Логичко 1: тестира услов 1 на низу 1
Логично 2: тестира услов 2 на низу 2 итд
сум_арраи: низ, врши се збир операција
Пример:
Све ово може бити збуњујуће за разумевање. Дакле, хајде да тестирамо ову формулу тако што ћемо је покренути на примеру приказаном испод. Овде имамо податке о испорученим производима у различите градове заједно са одговарајућим пољима категорија и количинама. Овде имамо податке и морамо да пронађемо количину колачића послатих у Бостон где је количина већа од 40.
Табела података и табела критеријума приказани су на горњој слици. Ради разумевања, користили смо именоване опсеге за коришћене низове. Именовани опсези су наведени испод.
Овде:
Град дефинисан за низ А2: А17.
Категорија дефинисана за низ Б2: А17.
Количина дефинисана за низ Ц2: Ц17.
Сада сте спремни да добијете жељени резултат помоћу формуле испод.
Користите формулу:
| { = СУМ (ИФ ((Град = "Бостон") * (Категорија = "Колачићи") * (Количина> 40), Количина)) } |
Објашњење:
- Цити = "Бостон": проверава вредности у опсегу града да се подударају са "Бостон".
- Категорија = "Колачићи": проверава да ли се вредности у опсегу категорија подударају са "колачићима".
- Количина> 40: проверава вредности у опсегу количина на ма
- Количина је низ где је потребан збир.
- ИФ функција проверава све критеријуме и звездица цхар (*) множи све резултате низа.
= СУМ (ИФ ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Сада ИФ функција враћа само количине које одговарају 1с, а остатак се занемарује.
- Функција СУМ враћа СУМ.
Сада се количина која одговара 1 додаје само да би се добио резултат.
Као што видите, количина 43 се враћа, али постоје три наруџбине колачића испоручене у „Бостон“ у количинама 38, 36 и 43. Требао нам је збир количине где је количина већа од 40. Дакле, формула враћа само 43. Сада користите друге критеријуме да бисте добили СУМ Количина за Град: "Лос Ангелес" & Категорија: "Барови" & Количина буде мања од 50.
Користите формулу
| { = СУМ (ИФ ((Град = "Лос Ангелес") * (Категорија = "Шипке") * (Количина <50), Количина)) } |
Као што видите, формула као резултат враћа вредности 86. Што је збир 2 налога који задовољавају услове који имају количину 44 и 42. Овај чланак илуструје како заменити угнежђену формулу ИФ са једним ИФ у формули низа. Ово се може користити за смањење сложености сложених формула. Међутим, овај проблем се лако може решити помоћу функције СУМИФС или СУМПРОДУЦТ.
Употреба функције СУМПРОДУЦТ:
Функција СУМПРОДУЦТ враћа збир одговарајућих вредности у низу. Тако ћемо добити низ који враћа 1с а Труе вредности вредности и 0с вредностима Фалсе израза. Дакле, последњи збир ће одговарати тамо где све изјаве стоје Тачно.
Користите формулу:
| = СУМПРОДУЦТ ( - (Град = "Бостон"), - (Категорија = "Колачићи"), - (Количина> 40), Количина) |
-: операција која се користи за претварање свих ТРУЕ у 1с и Фалсе у 0.
Функција СУМПРОДУЦТ поново проверава СУМ количине коју враћају горе објашњена функција СУМ и ИФ.
Слично, за други пример резултат остаје исти.
Као што видите, функција СУМПРОДУЦТ може обавити исти задатак.
Ево свих опсервационих напомена у вези са употребом формуле.
Напомене:
- Поље_сум у формули ради само са бројевима.
- Ако формула враћа грешку #ВАЛУЕ, проверите да ли завојите заграде морају бити присутне у формули као што је приказано у примерима у чланку.
- Негација (-) цхар мења вредности, ТРУЕ или 1с у ФАЛСЕ или 0с и ФАЛСЕс или 0с у ТРУЕс или 1с.
- Операције попут једнаке ( = ), мање од једнаког ( <= ), веће од ( > ) или није једнако () може се извести унутар примијењене формуле, само с бројевима.
Надамо се да је овај чланак о томе како користити функцију ИФ уместо функција СУМПРОДУЦТ и СУМИФС у Екцелу објашњен. Више чланака о формулама сумирања потражите овде. Ако су вам се допали наши блогови, поделите их са својим почетницима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште
Како се користи функција СУМПРОДУЦТ у програму Екцел: Враћа СУМ након множења вредности у више низа у Екцелу.
ЗБИРАЈТЕ ако је датум између : Враћа ЗБОР вредности између датих датума или периода у Екцелу.
Зброј ако је датум већи од датума: Враћа ЗБОР вредности након датог датума или тачке у Екцелу.
2 начина збрајања по месецима у Екцелу: Враћа ЗБОР вредности у датом месецу у Екцелу.
Како збрајати више колона са условом: Враћа СУМ вредности у више колона са условом у екцелу.
Популарни чланци:
50 Екцел пречица за повећање ваше продуктивности : Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.