Excel

La cèl·lula conté una de moltes coses

Cell Contains One Many Things

Fórmula Excel: la cel·la conté una de moltes cosesFórmula genèrica
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0
Resum

Per comprovar si una cel·la conté una de moltes cadenes, podeu utilitzar una fórmula basada en CERCA , NÚMERO i SUMPRODUCT funcions. La fórmula en C5, copiada, és:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

on coses és el rang anomenat E5: E7.

Explicació

L'objectiu d'aquest exemple és provar cada cel·la de B5: B14 per veure si conté alguna de les cadenes del fitxer rang anomenat coses (E5: E7). Aquestes cadenes poden aparèixer on sigui a la cel·la, de manera que es tracta d’un problema literal que conté. La fórmula en C5, copiada, és:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Aquesta fórmula es basa en una altra fórmula que comprova una cel·la per a una sola subcadena. Si la cel·la conté la subcadena, la fórmula retorna TRUE. Si no, la fórmula retorna FALS:

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Quan el Funció SEARCH troba una cadena, torna la posició d'aquesta cadena com a número. Si CERCA no ho fa cerca una cadena, torna un #VALUE. error. Això significa que ISNUMBER retornarà CERT si hi ha una coincidència i FALS si no.



En aquest exemple, l'objectiu és comprovar si hi ha més d'una cadena, de manera que donem a la funció SEARCH una llista de cadenes al rang anomenat coses . Com que hi ha 3 cordes a coses ('vermell', 'verd' i 'blau'), SEARCH retorna 3 resultats en un matriu com això:

com fer mitjanes en Excel
 
{#VALUE!#VALUE!23}

Com que no es troben 'vermell' i 'blau', la SEARCH retorna un #VALOR. error. Tanmateix, com que 'verd' apareix a prop del final del text a la cel·la B5, SEARCH retorna 23 (és a dir, 'verd' comença pel 23è caràcter).

Aquesta matriu es retorna directament al fitxer Funció ISNUMBER , que converteix els elements de la matriu a TRUE o FALSE:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Lògicament, si tenim fins i tot un TRUE a la matriu, sabem que una cel·la conté almenys una de les cadenes que cerquem. La forma més senzilla de comprovar si és TRUE és sumar tots els valors. Ho podem fer amb el Funció SUMPRODUCT , però primer hem de coaccionar els valors TRUE / FALSE a 1s i 0s a doble negatiu (--) com això:

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

Això genera una nova matriu que conté només 1s i 0s:

com fer parcel·les de caixes en excel
 
{001}

que es lliura directament a SUMPRODUCT:

 
= SUMPRODUCT ({001}) // returns 1

Amb només una matriu per processar, SUMPRODUCT suma els elements de la matriu i retorna un resultat. Qualsevol resultat diferent de zero significa que tenim un 'hit', de manera que afegim> 0 per forçar un resultat final de TRUE o FALSE:

 
= SUMPRODUCT ({001})>0 // returns TRUE

Tingueu en compte que qualsevol combinació de coincidències retornarà un nombre superior a zero i farà que la fórmula torni a TRUE.

Amb una llista codificada

No és necessari utilitzar un interval per cercar la llista de cadenes. També podeu utilitzar un fitxer constant de matriu . Per exemple, per comprovar si hi ha 'vermell', 'blau' o 'verd', podeu utilitzar una fórmula com aquesta:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Funció SUM

Històricament, SUMPRODUCT sovint apareix a fórmules de matriu , perquè pot gestionar matrius de forma nativa, sense control + majúscula + entrada . Això fa que la fórmula sigui 'més amigable' per a la majoria dels usuaris. En Excel 365 , que gestiona matrius de forma nativa , el Funció SUM es pot utilitzar en lloc de SUMPRODUCT sense control + shift + enter:

com separar els números del text en Excel
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>0

Evitar coincidències falses

Un problema amb aquest enfocament és que podeu obtenir coincidències falses a partir de subcadenes que apareixen dins de paraules més llargues. Per exemple, si intenteu fer coincidir 'dr' també podeu trobar 'Andrea', 'beure', 'sec', etc. ja que 'dr' apareix dins d'aquestes paraules. Això passa perquè SEARCH fa una coincidència 'conté' automàticament.

Per fer un hack ràpid, podeu afegir espai al voltant de les paraules de cerca (és a dir, 'dr' o 'dr') per evitar atrapar 'dr' en una altra paraula. Però això fallarà si apareix 'dr' primer o darrer en una cel·la o apareix amb puntuació.

Si necessiteu una solució més precisa, podeu fer-ho normalitzar el text primer a columna auxiliar , tenint cura d'afegir també un espai principal i final. A continuació, utilitzeu la fórmula d'aquesta pàgina al text resultant.

Arxius adjunts Dossier cel·la conté una de moltes coses.xlsx Autor Dave Bruns


^