Excel

Om cellen innehåller en av många saker

If Cell Contains One Many Things

Excel -formel: Om cellen innehåller en av många sakerGenerisk formel | _+_ | Sammanfattning

För att testa en cell för en av flera strängar och returnera ett anpassat resultat för den första matchningen som hittats kan du använda en INDEX / MATCH -formel baserat på SÖK -funktion . I exemplet som visas är formeln i C5:



vilka av följande karaktärer är jokertecken i excel? (markera alla som gäller.)
{= INDEX (results, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}

var saker (E5: E8) och resultat (F5: F8) är namngivna intervall .

Detta är en matrisformel och måste anges med Control + Shift + Enter.





Förklaring

Denna formel använder två namngivna intervall : saker , och resultat . Om du skickar den här formeln direkt ska du använda namngivna intervall med samma namn (definierade utifrån dina data). Om du inte vill använda namngivna intervall, använd absoluta referenser istället.

Kärnan i denna formel är detta utdrag:



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

Detta är baserat på en annan formel ( förklaras i detalj här ) som kontrollerar en cell för en enda delsträng. Om cellen innehåller delsträngen returnerar formeln TRUE. Om inte, returnerar formeln FALSE.

För vi ger SÖK -funktion mer än en sak att leta efter, i det angivna intervallet saker , det kommer att ge oss mer det enda resultatet, i en array som ser ut så här:

 
 ISNUMBER ( SEARCH (things,B5)

Siffror representerar matchningar i saker , fel representerar objekt som inte hittades.

För att förenkla matrisen använder vi ISNUMBER -funktion för att konvertera alla objekt i matrisen till antingen SANT eller FALSKT. Vilket giltigt tal som helst blir SANT, och alla fel (dvs. en sak hittades inte) blir FALSK. Resultatet är en matris så här:

 
{#VALUE!9#VALUE!#VALUE!}

som går in i MATCH -funktionen som lookup_array argument, med en uppslagningsvärde av SANT:

 
{FALSETRUEFALSEFALSE}

MATCH returnerar sedan positionen för första SANT -hittade, 2 i detta fall.

Slutligen använder vi INDEX -funktion för att hämta ett resultat från det angivna intervallet resultat på samma position:

 
 MATCH (TRUE,{FALSETRUEFALSEFALSE},0) // returns 2

Du kan anpassa resultat intervall med vilka värden som helst som är vettiga i ditt användningsfall.

excel count-celler med viss text

Förhindra falska matchningar

Ett problem med detta tillvägagångssätt med ISNUMBER + SEARCH -tillvägagångssättet är att du kan få falska matchningar från partiella matchningar i längre ord. Till exempel, om du försöker matcha 'dr' kan du också hitta 'Andrea', 'drack', 'droppa' etc. eftersom 'dr' förekommer i dessa ord. Detta händer eftersom SEARCH automatiskt gör en 'innehåller-typ' -matchning.

För en snabb lösning kan du slå in sökord i mellanslagstecken (dvs. 'dr' eller 'dr') för att undvika att hitta 'dr' i ett annat ord. Men detta kommer att misslyckas om 'dr' visas först eller sist i en cell.

Om du behöver en mer robust lösning är ett alternativ att normalisera texten först i a hjälparpelare och lägg till ett ledande och efterföljande utrymme. Använd sedan formeln på denna sida i texten i hjälpkolumnen, istället för originaltexten.

Författare Dave Bruns


^