Excel

Få den første match celle indeholder

Get First Match Cell Contains

Excel-formel: Få den første matchcelle, der indeholderGenerisk formel
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}
Resumé

For at kontrollere en celle for en af ​​flere ting og returnere det første match, der findes på listen, kan du bruge en INDEX / MATCH-formel, der bruger SEARCH eller FIND til at finde et match. I det viste eksempel er formlen i C5:

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

hvor 'ting' er navngivet rækkevidde E5: E9.



hvad betyder pv i excel

Bemærk: dette er en matrixformel og skal indtastes med Control + Shift + Enter.



Forklaring

I dette eksempel har vi en liste over farver i et navngivet område kaldet 'ting' (E5: E9). Vi vil kontrollere teksten i kolonne B for at se, om den indeholder nogen af ​​disse farver. I så fald vil vi returnere navnet på den første fundet farve.

Arbejder indefra og ud, bruger denne formel ISNUMBER-funktion og SØG-funktion at søge i teksten i B5 for hver farve, der er anført i 'ting' som denne:



 
 ISNUMBER ( SEARCH (things,B5)

Dette udtryk er baseret på en formel ( forklaret i detaljer her ) der kontrollerer en celle for et enkelt underlag. Hvis cellen indeholder understrengen, returnerer udtrykket SAND. Hvis ikke, returnerer udtrykket FALSK.

Når vi giver denne SØGNING en liste over ting (i stedet for en ting) vil give os en række resultater tilbage. Hver fundet farve genererer en numerisk position, og farver, der ikke findes, genererer en fejl:

 
{#VALUE!#VALUE!20#VALUE!#VALUE!}

ISNUMBER-funktionen konverterer derefter resultater til SAND / FALSK-værdier. Ethvert tal bliver SAND, og ​​enhver fejl (ikke fundet) bliver FALSK. Resultatet er en matrix som denne:



 
{FALSEFALSETRUEFALSEFALSE}

Denne matrix returneres til MATCH-funktion som array-argumentet. Opslagsværdien er SAND, og ​​matchtypen er sat til nul for at tvinge et nøjagtigt match. Når der er en matchende farve, returnerer MATCH den position, der blev fundet først SAND. Denne værdi føres ind i INDEX-funktion som række nummer, med det navngivne interval 'ting' angivet som array. Når der er mindst et match, returnerer INDEX farven på den position. Når der ikke findes noget match, returnerer denne formel fejlen # N / A.

Med hårdkodede værdier

Hvis du ikke ønsker at oprette et eksternt navngivet område som 'ting' i dette eksempel, kan du hårdkode værdier i formlen som 'arraykonstanter' som denne:

hvordan man laver et datakort i Excel
 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

Få første kamp i cellen

Sprog her er ret forvirrende, men formlen ovenfor returnerer den første kamp, ​​der findes i liste over ting at se efter . Hvis du i stedet vil returnere den første kamp, ​​der blev fundet i cellen, der testes , kan du prøve en formel som denne:

 
= INDEX (things, MATCH ( AGGREGATE (15,6, SEARCH (things,A1),1), SEARCH (things,A1),0))

I denne version af formlen er MATCH-funktionen indstillet til at lede efter resultatet af dette uddrag:

 
 AGGREGATE (15,6, SEARCH (things,A1),1) // get min value

der bruger AGGREGATE-funktion for at få minimumsværdien i de resultater, der returneres af SEARCH. Vi har brug for AGGREGATE her, fordi det indgående array sandsynligvis vil indeholde fejl (returneres af SEARCH, når ting ikke findes), og vi har brug for en funktion, der ignorerer disse fejl og stadig giver os den mindste numeriske værdi.

Resultatet fra AGGREGATE returneres direkte til MATCH som opslagsværdi sammen med det samme array returneret af SEARCH. Det endelige resultat er den første kamp, ​​der findes i cellen, ikke den første kamp, ​​der findes i listen over ting.

Forfatter Dave Bruns


^