Генеричка формула
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Резиме
Да бисте сумирали горњих н вредности у критеријумима који се подударају, можете користити формулу засновану на функцији ЛАРГЕ, умотаној у функцију СУМПРОДУЦТ. У генеричком облику формуле (горе), опсег представља опсег ћелија које се упоређују са критеријумима , вредности представљају нумеричке вредности из којих се преузимају горње вредности, а Н представља идеју Н-те вредности.
У примеру, активна ћелија садржи ову формулу:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Где је боја именовани опсег Б5: Б12, а вредност именовани опсег Ц5: Ц12.
Објашњење
У свом најједноставнијем облику, ЛАРГЕ враћа вредност „Н-тог највећег“ у опсегу са овом конструкцијом:
=LARGE (range,N)
Тако, на пример:
=LARGE (C5:C12,2)
вратиће 2. највећу вредност у опсегу Ц5: Ц12, што је 12 у приказаном примеру.
Међутим, ако као други аргумент наведете „ВЕЛИКУ константу“ (нпр. Константу у облику (1,2,3)), ЛАРГЕ ће вратити низ резултата уместо једног резултата. Дакле, формула:
=LARGE (C5:C12, (1,2,3))
вратиће 1., 2. и 3. највећу вредност Ц5: Ц12 у низу попут овог: (12,12,10)
Дакле, трик овде је филтрирање вредности на основу боје пре него што се покрене ЛАРГЕ. То радимо са изразом:
(color=E5)
Што резултира низом ТРУЕ / ФАЛСЕ вредности. Током операције множења, ове вредности се присиљавају на јединице и нуле:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Дакле, коначни резултат је да само вредности повезане са бојом „црвена“ преживе операцију:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
а остале вредности су присиљене на нулу.
Напомена: ова формула неће обрађивати текст у опсегу вредности. Види доле.
Руковање текстом у вредностима
Ако негде у опсегу вредности имате текст, функција ЛАРГЕ ће избацити грешку #ВАЛУЕ и зауставити рад формуле.
Да бисте руковали текстом у опсегу вредности, можете додати функцију ИФЕРРОР овако:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Овде заглављујемо грешке ЛАРГЕ узроковане текстуалним вредностима и замењујемо нулом. Коришћење ИФ унутар ЛАРГЕ захтева да се формула унесе са цонтрол + схифт + ентер, па прелазимо на СУМ уместо на СУМПРОДУЦТ.
Напомена: Налетео сам на ову формулу коју је објавио невероватни Барри Хоудини на стацковерфлов.
