Vispārēja formula
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Kopsavilkums
Lai pārbaudītu šūnu vienā no vairākām lietām un atgrieztu pirmo sarakstā atrasto atbilstību, varat izmantot INDEX / MATCH formulu, kas atbilst meklēšanai izmanto SEARCH vai FIND. Parādītajā piemērā formula C5 ir:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kur "lietas" ir nosauktais diapazons E5: E9.
Piezīme: šī ir masīva formula, un tā jāievada ar Control + Shift + Enter.
Paskaidrojums
Šajā piemērā mums ir krāsu saraksts nosauktajā diapazonā, ko sauc par "lietām" (E5: E9). Mēs vēlamies pārbaudīt B slejas tekstu, lai redzētu, vai tajā ir kāda no šīm krāsām. Ja tā, mēs vēlamies atgriezt pirmās atrastās krāsas nosaukumu.
Strādājot no iekšpuses, šī formula izmanto funkciju ISNUMBER un SEARCH, lai B5 tekstā meklētu katru krāsu, kas uzskaitīta šādās lietās:
ISNUMBER(SEARCH(things,B5)
Šī izteiksme ir balstīta uz formulu (šeit detalizēti paskaidrota), kas pārbauda šūnu vienā apakšvirsrakstā. Ja šūna satur apakšvirkni, izteiksme atgriež vērtību TRUE. Ja nē, izteiksme atgriež FALSE.
Kad mēs šai MEKLĒŠANAI sniegsim lietu sarakstu (vienas lietas vietā), tas mums atgriezīs virkni rezultātu. Katra atrasta krāsa ģenerēs skaitlisku pozīciju, un krāsas, kas nav atrastas, radīs kļūdu:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkcija ISNUMBER pēc tam pārvērš rezultātus TRUE / FALSE vērtībās. Jebkurš skaitlis kļūst PATIESA, un jebkura kļūda (nav atrasta) kļūst FALSE. Rezultāts ir šāds masīvs:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Šis masīvs tiek atgriezts funkcijā MATCH kā masīva arguments. Uzmeklēšanas vērtība ir PATIESA, un atbilstības veids ir iestatīts uz nulli, lai piespiestu precīzu atbilstību. Ja ir atbilstoša krāsa, MATCH atgriež pirmo atrasto TRUE pozīciju. Šī vērtība tiek ievadīta INDEX funkcijā kā rindas numurs, ar masīvu norādot nosaukto diapazonu "lietas". Ja ir vismaz viena atbilstība, INDEX atgriež krāsu šajā pozīcijā. Ja atbilstība nav atrasta, šī formula atgriež kļūdu # N / A.
Ar grūti kodētām vērtībām
Ja šajā piemērā nevēlaties iestatīt ārēju nosaukto diapazonu, piemēram, "lietas", formulas vērtības var kodēt kā "masīva konstantes" šādi:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Iegūstiet pirmo maču šūnā
Valoda šeit ir diezgan mulsinoša, taču iepriekš minētā formula atgriezīs pirmo meklēto lietu sarakstā atrasto atbilstību. Ja tā vietā vēlaties atgriezt pirmo pārbaudāmajā šūnā atrasto atbilstību, varat izmēģināt šādu formulu:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Šajā formulas versijā funkcija MATCH ir iestatīta, lai meklētu šī fragmenta rezultātu:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
kas izmanto funkciju AGGREGATE, lai iegūtu SEARCH atgriezto rezultātu minimālo vērtību. Mums šeit ir nepieciešams AGGREGATE, jo ienākošajā masīvā, visticamāk, būs kļūdas (ko SEARCH atgriezīs, kad lietas netiks atrastas), un mums ir nepieciešama funkcija, kas ignorēs šīs kļūdas un tomēr mums piešķir minimālo skaitlisko vērtību.
Rezultāts no AGGREGATE tiek atgriezts tieši MATCH kā uzmeklēšanas vērtība kopā ar to pašu masīvu, ko atgriezusi SEARCH. Gala rezultāts ir pirmais šūnā atrastais mačs, nevis pirmais, kas atrasts lietu sarakstā.
