Excel

Klasificējiet tekstu ar atslēgvārdiem

Categorize Text With Keywords

Excel formula: klasificējiet tekstu ar atslēgvārdiemVispārīgā formula | _+_ | Kopsavilkums

Lai kategorizētu tekstu, izmantojot atslēgvārdus ar atbilstību “satur”, varat izmantot funkciju MEKLĒT ar INDEX un MATCH palīdzību. Parādītajā piemērā C5 formula ir šāda:





kā meklēt kolonnā
{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,text)),0))}

kur atslēgvārdus ir nosaukts diapazons E5: E14 un kategorijām ir nosauktais diapazons F5: F14.

Piezīme: tas ir masīva formula un jāievada, izmantojot taustiņu kombināciju control + shift + enter.





Skaidrojums

Pamatā tas ir es NDEX un MATCH funkcija .

Funkcijas MATCH iekšpusē mēs izmantojam MEKLĒŠANAS funkcija lai meklētu šūnas B slejā katram sarakstā iekļautajam atslēgvārdam nosaukts diapazons atslēgvārdus (E5: E14):



 
{= INDEX (categories, MATCH (TRUE, ISNUMBER ( SEARCH (keywords,B5)),0))}

Tā kā mēs meklējam vairākus vienumus (nosauktajā diapazonā atslēgvārdus ), mēs saņemsim vairākus šādus rezultātus:

 
 SEARCH (keywords,B5)

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 Funkcija ISNUMBER , kas visas vērtības pārvērš par TRUE/FALSE šādi:

 
{#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!24#VALUE!#VALUE!#VALUE!}

Šis masīvs iekļaujas MATCH funkcijā kā lookup_array , Ar lookup_value iestatīts kā TRUE. MATCH atgriež pirmās TRUE pozīciju, ko tā atrod masīvā (šajā gadījumā 7), kas tiek sniegta Funkcija INDEXrindas_numurs :

 
{FALSEFALSEFALSEFALSEFALSEFALSETRUEFALSEFALSEFALSE}

INDEX atgriež 7. vienumu kategorijām , “Auto”, kā gala rezultāts.

Ar XLOOKUP

Ar XLOOKUP funkcija , šo formulu var nedaudz vienkāršot. XLOOKUP var izmantot to pašu loģiku, kas izmantota iepriekš MATCH funkcijā, tāpēc līdzvērtīga formula ir:

kā ievadīt šūnā
 
= INDEX (categories,7)

XLOOKUP atrod masīvā pirmo TRUE un atgriež atbilstošo vērtību no kategorijām .

Dinamiskās masīva formulas ir pieejami Office 365 tikai.

Nepatiesu atbilstību novēršana

Viena no šīs pieejas problēmām ir tāda, ka jūs varat iegūt nepatiesas atbilstības no apakšvirknēm, kas parādās garākos vārdos. Piemēram, ja jūs mēģināt saskaņot vārdu “dr”, iespējams, atradīsit arī “Andrea”, “dzēriens”, “sauss” utt., Jo šajos vārdos parādās “dr”. Tas notiek tāpēc, ka SEARCH automātiski atbilst “satur”.

Lai ātri uzlauztu, varat pievienot atstarpi ap meklēšanas vārdiem (piemēram, “dr” vai “dr”), lai izvairītos no “dr” saķeršanas citā vārdā. Bet tas neizdosies, ja šūnā “dr” parādās pirmais vai pēdējais, vai ar pieturzīmēm utt.

Ja jums ir nepieciešams precīzāks risinājums, viena no iespējām ir normalizēt tekstu vispirms a palīgu kolonna , rūpējoties, lai tiktu pievienota arī vadošā un beigu vieta. Tad jūs varat meklēt veselus vārdus, ko ieskauj atstarpes.

Autors Deivs Bruns


^