Excel

Få første match celle indeholder

Get First Match Cell Contains

Excel -formel: Hent første matchcelle indeholderGenerisk formel | _+_ | 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,A1)),0))}

hvor 'tingene' er navngivet område 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 farve, der blev fundet.

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



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

Dette udtryk er baseret på en formel ( forklaret detaljeret her ), der kontrollerer en celle for en enkelt delstreng. Hvis cellen indeholder delstrengen, returnerer udtrykket SAND. Hvis ikke, returnerer udtrykket FALSKT.

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

 
 ISNUMBER ( SEARCH (things,B5)

ISNUMBER -funktionen konverterer derefter resultaterne til SAND / FALSK værdier. Ethvert tal bliver SAND, og ​​enhver fejl (ikke fundet) bliver FALSK. Resultatet er et array som dette:

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

Denne matrix returneres til MATCH -funktion som array -argumentet. Opslagningsværdien er SAND, og ​​matchtypen sættes til nul for at tvinge en nøjagtig matchning. Når der er en matchende farve, returnerer MATCH den position, der først blev fundet SAND. Denne værdi indføres i INDEX -funktion som rækkenummer, med det navngivne område 'ting' angivet som array. Når der er mindst én match, returnerer INDEX farven på den position. Når der ikke findes nogen match, returnerer denne formel fejlen #N/A.

Med hårdkodede værdier

Hvis du ikke vil konfigurere et eksternt navngivet område som 'ting' i dette eksempel, kan du hard-kode værdier i formlen som 'array-konstanter' som dette:

hvordan man laver et datakort i Excel
 
{FALSEFALSETRUEFALSEFALSE}

Få den første kamp i cellen

Sproget her er ret forvirrende, men formlen ovenfor vil returnere det første match, der findes i liste over ting at kigge efter . Hvis du i stedet vil returnere det første match, der blev fundet i cellen, der testes , kan du prøve en formel som denne:

 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

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

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

som bruger AGGREGATE funktion for at få den mindste værdi i resultaterne returneret af SØG. Vi har brug for AGGREGATE her, fordi det indgående array sandsynligvis vil indeholde fejl (returneres af SØG, 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 det første match, der findes i cellen, ikke det første match, der findes på listen over ting.

Forfatter Dave Bruns


^