Vispārēja formula
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Kopsavilkums
Lai klasificētu tekstu, izmantojot atslēgvārdus ar atbilstību "satur", varat izmantot funkciju MEKLĒŠANA, izmantojot INDEX un MATCH palīdzību. Parādītajā piemērā formula C5 ir:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kur atslēgvārdi ir nosauktais diapazons E5: E14, un kategorijas ir nosauktais diapazons F5: F14.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Paskaidrojums
Būtībā tā ir INDEX un MATCH funkcija.
Funkcijā MATCH mēs izmantojam funkciju MEKLĒT, lai B kolonnā meklētu šūnas katram uzskaitītajam atslēgvārdam nosauktajā diapazona atslēgvārdos (E5: E14):
SEARCH(keywords,B5)
Tā kā mēs meklējam vairākus vienumus (nosauktajos diapazona atslēgvārdos ), mēs iegūsim vairākus šādus rezultātus:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Vērtība! kļūda rodas, ja SEARCH nevar atrast tekstu. Kad SEARCH atrod atbilstību, tas atgriež skaitli, kas atbilst teksta pozīcijai šūnā.
Lai mainītu šos rezultātus izmantojamākā formātā, mēs izmantojam funkciju ISNUMBER, kas visas vērtības pārveido par TRUE / FALSE šādi:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Šis masīvs nonāk funkcijā MATCH kā lookup_array, bet lookup_value ir iestatīts kā TRUE. MATCH atgriež pirmās TRUE pozīciju, ko tā atrod masīvā (šajā gadījumā 7), kas funkcijai INDEX tiek piegādāta kā row_num:
=INDEX(categories,7)
INDEX gala rezultātā atgriež 7. vienumu kategorijās "Auto".
Ar XLOOKUP
Izmantojot funkciju XLOOKUP, šo formulu var nedaudz vienkāršot. XLOOKUP var izmantot to pašu loģiku, kas izmantota iepriekšējās MATCH funkcijas iekšpusē, tāpēc ekvivalenta formula ir:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP atrod masīvā pirmo TRUE un atgriež atbilstošo vērtību no kategorijām .
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 utt.
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. Tad jūs varat meklēt veselus vārdus, kurus ieskauj atstarpes.
