Vispārēja formula
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Kopsavilkums
Apkopojot to, vai šūnās ir viena vai otra teksta virkne (ti, satur “cat” vai “žurka”), varat izmantot funkciju SUMPRODUCT kopā ar ISNUMBER + SEARCH vai FIND. Parādītajā piemērā formula šūnā F5 ir:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
kas atgriež vērtību summas C4: C8, ja šūnās B4: B8 ir vai nu "cat", vai "žurka".
Paskaidrojums
Ja summējat šūnas ar “OR” kritērijiem, jums jābūt piesardzīgam un divreiz neuzskaitīt, ja pastāv iespēja, ka abi kritēriji atgriezīsies patiesībā. Parādītajā piemērā mēs vēlamies summēt vērtības C slejā, ja B kolonnas šūnās ir vai nu “cat”, vai “žurka”. Mēs nevaram izmantot SUMIF ar diviem kritērijiem, jo SUMIFS pamatā ir AND loģika. Un, ja mēs mēģināsim izmantot divus SUMIFS (ti, SUMIFS + SUMIFS), mēs skaitīsim dubultā, jo ir šūnas, kurās ir gan "kaķis", gan "žurka".
Tā vietā mēs izmantojam šādu formulu:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Šis šīs formulas kodols ir balstīts uz šeit izskaidroto formulu, kas šūnas iekšpusē atrod tekstu ar ISNUMBER un SEARCH:
ISNUMBER(SEARCH("text",range)
Piešķirot šūnu diapazonu, šis fragments atgriezīs vērtību TRUE / FALSE masīvu, pa vienai vērtībai katrai diapazona šūnai. Šajā formulā mēs divreiz izmantojam šo fragmentu, vienu reizi - "kaķis" un vienu - "žurka", tāpēc mēs iegūsim divus masīvus. Šajā brīdī mums ir:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Pēc tam mēs pievienojam šos masīvus kopā, jo pievienošana tiek izmantota Būla algebrā OR loģikai. Matemātikas operācija TRUE un FALSE vērtības automātiski piespiež 1s un 0s, tāpēc mēs nonākam ar zemāk esošo masīvu:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Katrs skaitlis šajā masīvā ir rezultāts, pievienojot TRUE un FALSE vērtības sākotnējos divos masīvos kopā. Parādītajā piemērā masīvs izskatās šādi:
(2;0;2;1;0)
Mums šie skaitļi jāsaskaita, bet mēs nevēlamies dubultot skaitīšanu. Tāpēc mums jāpārliecinās, ka jebkura vērtība, kas lielāka par nulli, tiek skaitīta tikai vienu reizi. Lai to izdarītu, mēs piespiežam visas vērtības uz TRUE vai FALSE, pārbaudot masīvu ar "> 0". Tādējādi tiek parādīta vērtība TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Ko mēs pēc tam pārvēršam par 1/0, izmantojot dubultnegatīvu (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
un visbeidzot:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT reizina abu masīvu atbilstošos elementus kopā un summē rezultātu, atgriežot 70.
Lielo un mazo burtu opcija
Funkcija MEKLĒŠANA ignorē burtus. Ja jums ir nepieciešama sensitīva opcija, aizstājiet MEKLĒT ar funkciju Atrast.
