Vispārēja formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Kopsavilkums
Lai izceltu šūnas, kas satur vienu no daudzajām teksta virknēm, varat izmantot formulu, kuras pamatā ir funkcijas ISNUMBER un SEARCH, kā arī funkciju SUMPRODUCT. Parādītajā piemērā nosacītā formatēšana, kas piemērota B4: B11, ir balstīta uz šo formulu:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Paskaidrojums
Strādājot no iekšpuses, šī formulas daļa katrā B4: B11 šūnā meklē visas vērtības nosauktajā diapazonā "lietas":
--ISNUMBER(SEARCH(things,B4)
Funkcija MEKLĒŠANA atgriež vērtības pozīciju, ja tā tiek atrasta, un kļūdu #VALUE, ja tā nav atrasta. Attiecībā uz B4 rezultāti tiek atgriezti šādā masīvā:
(8;#VALUE!;#VALUE!)
Funkcija ISNUMBER maina visus rezultātus uz TRUE vai FALSE:
(TRUE;FALSE;FALSE)
Divkāršais negatīvs ISNUMBER priekšā liek TRUE / FALSE uz 1/0:
(1;0;0)
Pēc tam funkcija SUMPRODUCT saskaita rezultātus, kas tiek pārbaudīti pret nulli:
=SUMPRODUCT((1;0;0))>0
Jebkurš rezultāts, kas nav nulle, nozīmē, ka tika atrasta vismaz viena vērtība, tāpēc formula atgriež vērtību TRUE, izraisot kārtulu.
Ignorējiet tukšas lietas
Lai ignorētu tukšās šūnas nosauktajā diapazonā "lietas", varat izmēģināt šādu modificētu formulu:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Tas darbojas tik ilgi, kamēr testējamās teksta vērtībās nav virknes “FALSE”. Ja tā notiek, varat paplašināt IF funkciju, iekļaujot vērtību, ja tekstā nav zināms nepatiesa informācija (piemēram, "zzzz", "####" utt.)
Lielo un mazo burtu opcija
MEKLĒŠANA nav reģistrjutīga. Lai pārbaudītu arī reģistru, nomainiet MEKLĒT ar ATRAST, piemēram:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Nepatiesu sakritību novēršana
Viena no šīs pieejas problēmām ir tā, ka jūs varat redzēt nepatiesas atbilstības, ko izraisa apakšvirknes, 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 veiktu daļēju labojumu, varat pievienot atstarpi ap meklēšanas vārdiem (ti, "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 blakus pieturzīmēm. To var daļēji novērst, pievienojot atstarpi arī ap oriģinālo tekstu. Lai vienlaikus pievienotu vietu abu sākumam un beigām, varat izmēģināt šādu formulu:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Tomēr tas neizlabos pieturzīmju radītās problēmas.
Ja jums nepieciešams pilnīgāks risinājums, viena no iespējām ir teksta normalizēšana vispirms palīgkolonnā, pievienojot arī priekšējo un aizmugurējo atstarpi. Tad jūs varat meklēt veselus vārdus, kurus ieskauj atstarpes.
