Vispārēja formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Kopsavilkums
Lai pārbaudītu šūnu, vai tajā ir viena no daudzajām virknēm, varat izmantot formulu, kuras pamatā ir funkcijas MEKLĒŠANA, ISNUMBER un SUMPRODUCT. C5 formula, kas nokopēta uz leju, ir:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kur lietas ir nosauktais diapazons E5: E9.
Paskaidrojums
Mēs vēlamies pārbaudīt katru šūnu B5: B11, lai noskaidrotu, vai tajā ir kāda no virknēm nosauktajās diapazona lietās (E5: E9). Lejupielādētā formula, kuru izmantojam C5, ir šāda:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Šī formula ir balstīta uz formulu (paskaidrota šeit), kas pārbauda šūnu vienā apakšvirsrakstā. Ja šūnā ir apakšvirkne, formula atgriež vērtību TRUE. Ja nē, formula atgriež FALSE:
ISNUMBER(SEARCH(things,B5))
Tomēr šajā gadījumā meklēšanai mēs meklējam virkņu sarakstu. Tā kā lietās ir 5 virknes , SEARCH atgriež 5 rezultātus tādā masīvā:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kad SEARCH atrod virkni, tā atgriež šīs virknes pozīciju. Ja MEKLĒŠANA neatrod virkni, tā atgriež vērtību #VALUE! kļūda. Tā kā “dzeltenais” parādās kā pirmais vārds B5, mēs redzam 1. Tā kā pārējās virknes nav atrastas, pārējie 4 vienumi ir kļūdas.
Šis masīvs tiek tieši atgriezts funkcijā ISNUMBER. Pēc tam ISNUMBER atgriež TRUE / FALSE vērtību masīvu:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Ja masīvā mums ir pat viena PATIESĪBA, mēs zinām, ka šūna satur vismaz vienu no meklētajām virknēm. Vieglākais veids, kā pārbaudīt PATIESĪBU, ir visu vērtību pievienošana kopā. Mēs to varam izdarīt, izmantojot SUMPRODUCT, bet vispirms mums ir jāpiespiež TRUE / FALSE vērtības uz 1s un 0s ar dubultu negatīvu (-) šādi:
--ISNUMBER(SEARCH(things,B5))
Tādējādi tiek iegūts jauns masīvs, kas satur tikai 1s un 0s:
(1;0;0;0;0)
piegādāts tieši SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Tikai apstrādājot vienu masīvu, SUMPRODUCT pievieno masīvā esošos vienumus un atgriež rezultātu. Jebkurš rezultāts, kas nav nulle, nozīmē, ka mums ir "trāpījums", tāpēc mēs pievienojam> 0, lai piespiestu patiesu vai nepatiesu gala rezultātu:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Ar grūti kodētu sarakstu
Meklējamo virkņu sarakstam nav nepieciešams izmantot diapazonu. Varat arī izmantot masīva konstanti. Piemēram, lai pārbaudītu, vai nav “sarkans”, “zils” un “zaļš”, varat izmantot šādu formulu:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Nepatiesu sakritību novēršana
Viena no šīs pieejas problēmām ir tā, ka jūs varat iegūt nepatiesas atbilstības no apakšvirsrakstiem, kas parādās garākos vārdos. Piemēram, ja jūs mēģināt saskaņot “dr”, jūs varat atrast arī “Andrea”, “dzēriens”, “sauss” utt., Jo šajos vārdos parādās “dr”. Tas notiek tāpēc, ka SEARCH automātiski veic atbilstību "satur".
Lai ātri uzlauztu, varat pievienot atstarpi ap meklēšanas vārdiem (piemēram, "dr" vai "dr"), lai izvairītos no "dr" aizķeršanās ar citu vārdu. Bet tas neizdosies, ja “dr” šūnā parādās pirmais vai pēdējais vai parādās ar pieturzīmēm.
Ja jums nepieciešams precīzāks risinājums, viena no iespējām ir teksta normalizēšana vispirms palīgkolonnā, pievienojot arī priekšējo un aizmugurējo atstarpi. Pēc tam izmantojiet šīs lapas formulu iegūtajam tekstam.
