Vispārēja formula
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Kopsavilkums
Lai pārbaudītu šūnu vienā no vairākām virknēm un atgrieztu pielāgoto rezultātu pirmajai atrastajai atbilstībai, varat izmantot INDEX / MATCH formulu, kuras pamatā ir funkcija MEKLĒT. Parādītajā piemērā formula C5 ir:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kur lietas (E5: E8) un rezultātus (F5: F8) sauc par diapazoniem.
Šī ir masīva formula, un tā jāievada ar Control + Shift + Enter.
Paskaidrojums
Šajā formulā tiek izmantoti divi nosauktie diapazoni: lietas un rezultāti . Ja jūs tieši portējat šo formulu, noteikti izmantojiet nosauktos diapazonus ar vienādiem nosaukumiem (kas definēti, pamatojoties uz jūsu datiem). Ja nevēlaties izmantot nosauktos diapazonus, tā vietā izmantojiet absolūtās atsauces.
Šīs formulas kodols ir šis fragments:
ISNUMBER(SEARCH(things,B5)
Tas ir balstīts uz citu formulu (šeit detalizēti paskaidrots), kas pārbauda šūnu vienā apakšvirsrakstā. Ja šūnā ir apakšvirkne, formula atgriež vērtību TRUE. Ja nē, formula atgriež FALSE.
Tā kā funkcijai MEKLĒT dodam vairāk nekā vienu lietu, kas jāmeklē, nosauktajā diapazona lietās tas mums vairāk dos vienu rezultātu masīvā, kas izskatās šādi:
(#VALUE!;9;#VALUE!;#VALUE!)
Skaitļi apzīmē sakritības lietās , kļūdas - vienumus, kas netika atrasti.
Lai vienkāršotu masīvu, mēs izmantojam funkciju ISNUMBER, lai visus masīva vienumus pārveidotu par TRUE vai FALSE. Jebkurš derīgs skaitlis kļūst PATIESA, un jebkura kļūda (ti, lieta, kas nav atrasta) kļūst FALSE. Rezultāts ir šāds masīvs:
(FALSE;TRUE;FALSE;FALSE)
kas tiek ievietots funkcijā MATCH kā parametrs lookup_array, ar parametra TRUE parametru lookup_value:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Tad MATCH atgriež pirmās atrastās TRUE pozīciju, šajā gadījumā - 2.
Visbeidzot, mēs izmantojam funkciju INDEX, lai izgūtu rezultātu no nosauktajiem diapazona rezultātiem tajā pašā pozīcijā:
=INDEX(results,2) // returns "found red"
Rezultātu diapazonu varat pielāgot ar jebkādām vērtībām, kas ir jēgas jūsu lietošanas gadījumā.
Nepatiesu sakritību novēršana
Viena no šīs pieejas problēmām, kas saistīta ar pieeju ISNUMBER + SEARCH, ir tas, ka jūs varat iegūt nepatiesas atbilstības no daļējas atbilstības garāku vārdu iekšpusē. Piemēram, ja mēģināt saskaņot “dr”, jūs varat atrast arī “Andrea”, “dzēris”, “pilēt” utt., Jo šajos vārdos parādās “dr”. Tas notiek tāpēc, ka SEARCH automātiski veic atbilstību "satur-tips".
Lai ātri atrisinātu problēmu, meklēšanas vārdus varat ietīt atstarpes rakstzīmēs (ti, "dr" vai "dr"), lai izvairītos no vārda "dr" atrašanas citā vārdā. Bet tas neizdosies, ja šūnā pirmais vai pēdējais parādās “dr”.
Ja jums nepieciešams stabilāks risinājums, viena no iespējām ir vispirms normalizēt tekstu palīga kolonnā un pievienot priekšējo un aizmugurējo atstarpi. Pēc tam izmantojiet šīs lapas formulu palīga slejā esošajā tekstā, nevis sākotnējā teksta.
