Podeu trobar i ressaltar les entrades duplicades entre dues columnes mitjançant la funció de format condicional de Google Sheets.
Mentre treballeu a Fulls de càlcul de Google amb grans conjunts de dades, probablement us trobeu amb un problema en què haureu de fer front a molts valors duplicats. Mentre que algunes entrades duplicades es col·loquen intencionadament, mentre que altres són errors. Això és especialment cert quan estàs col·laborant en el mateix full amb un equip.
Quan es tracta d'analitzar dades a Fulls de càlcul de Google, poder filtrar els duplicats pot ser essencial i convenient. Tot i que Google Sheets no té cap suport natiu per trobar duplicats als fulls, ofereix diverses maneres de comparar, identificar i eliminar dades duplicades a les cel·les.
De vegades, voleu comparar cada valor d'una columna amb una altra i trobar-hi si hi ha duplicats i viceversa. A Google Sheets, podeu trobar fàcilment duplicats entre dues columnes amb l'ajuda de la funció de format condicional. En aquest article, us mostrarem com comparar dues columnes a Fulls de càlcul de Google i trobar duplicats entre elles.
Trobeu entrades duplicades entre dues columnes mitjançant el format condicional
El format condicional és una característica de Google Sheets que permet a l'usuari aplicar formats específics, com ara el color de la lletra, les icones i les barres de dades a una cel·la o rang de cel·les en funció de determinades condicions.
Podeu utilitzar aquest format condicional per ressaltar les entrades duplicades entre dues columnes, ja sigui omplint les cel·les amb color o canviant el color del text. Heu de comparar cada valor d'una columna amb una altra i trobar si es repeteix algun valor. Perquè això funcioni, heu d'aplicar un format condicional a cada columna per separat. Seguiu aquests passos per fer-ho:
Obriu el full de càlcul que voleu comprovar si hi ha duplicats a Fulls de càlcul de Google. Primer, seleccioneu la primera columna (A) per comprovar amb la columna B. Podeu ressaltar tota la columna fent clic a la lletra de la columna que hi ha a sobre.
A continuació, feu clic al menú "Format" de la barra de menú i seleccioneu "Format condicional".
El menú de format condicional s'obre al costat dret dels fulls de Google. Podeu confirmar que l'interval de cel·les és el que heu seleccionat a l'opció "Aplica a l'interval". Si voleu canviar l'interval, feu clic a la "icona d'interval" i trieu un interval diferent.
A continuació, feu clic al menú desplegable a "Regles de format" i seleccioneu l'opció "La fórmula personalitzada és".
Ara, heu d'introduir una fórmula personalitzada al quadre "Valor o fórmula".
Si heu seleccionat una columna sencera (B:B), introduïu la següent fórmula COUNTIF al quadre "Valor o fórmula" a Regles de format:
=countif($B:$B,$A2)>0O,
Si heu seleccionat un rang de cel·les en una columna (per exemple, cent cel·les, A2:A30), utilitzeu aquesta fórmula:
=COMPTARSI($B$2:$B$30, $A2)>0Quan introduïu la fórmula, assegureu-vos de substituir totes les instàncies de la lletra "B" a la fórmula per la lletra de la columna que heu ressaltat. Estem afegint el signe "$" abans de les referències de cel·les per fer-les un rang absolut, de manera que no canviï, apliquem la fórmula.
A la secció Estil de format, podeu triar l'estil de format per ressaltar els elements duplicats. Per defecte, utilitzarà el color de farciment verd.
Podeu triar un dels estils de format predefinits fent clic a "Predeterminat" a les opcions "Estil de format" i, a continuació, seleccionant un dels valors predefinits.
O podeu utilitzar qualsevol de les set eines de format (Negreta, Cursiva, Subratllat, Tallat, Color del text, Color d'emplenament) a la secció "Estil de format" per ressaltar els duplicats.
Aquí, escollim un color de farciment per a les cel·les duplicades fent clic a la icona "Color d'ompliment" i seleccionant el color "groc".
Un cop hàgiu seleccionat el format, feu clic a "Fet" per ressaltar les cel·les.
La funció COUNTIF compta quantes vegades cada valor de cel·la de la "Columna A" apareix a la "Columna B". Així, si un element apareix fins i tot una vegada a la columna B, la fórmula retorna TRUE. Aleshores, aquest element es ressaltarà a la "Columna A" segons el format que hàgiu triat.
Això no ressalta els duplicats, sinó que destaca els elements que tenen duplicats a la columna B. Això vol dir que cada element destacat en groc té duplicats a la columna B.
Ara, hem d'aplicar el format condicional a la columna B utilitzant la mateixa fórmula. Per fer-ho, seleccioneu la segona columna (B2:B30), aneu al menú "Format" i seleccioneu "Format condicional".
Alternativament, feu clic al botó "Afegeix una altra regla" a la subfinestra "Regles de format condicional".
A continuació, confirmeu l'interval (B2:B30) al quadre "Aplica a l'interval".
A continuació, configureu l'opció "Format cel·les si..." a "La fórmula personalitzada és" i introduïu la fórmula següent al quadre de fórmula:
=COMPTARSI($A$2:$A$30, $B2)>0Aquí, estem utilitzant l'interval de la columna A ($A$2:$A$30) al primer argument i '$B2' al segon argument. Aquesta fórmula comprovarà el valor de la cel·la de la "columna B" amb totes les cel·les de la columna A. Si es troba una coincidència (duplicada), el format condicional elevarà aquest element a la "columna B".
A continuació, especifiqueu el format a les opcions "Estil de format" i feu clic a "Fet". Aquí, estem escollint el color taronja per a la columna B.
Això ressaltarà els elements de la columna B que tenen duplicats a la columna A. Ara, heu trobat i ressaltat els elements duplicats entre dues columnes.
Probablement us n'heu adonat, tot i que hi ha un duplicat d'"Arcelia" a la columna A, no està ressaltat. És perquè el valor de duplicat només es troba en una columna (A) no entre columnes. Per tant, no es destaca.
Ressalteu els duplicats entre dues columnes de la mateixa fila
També podeu ressaltar les files que tenen els mateixos valors (duplicats) entre dues columnes mitjançant el format condicional. La regla de format condicional pot comprovar cada fila i ressaltar les files que tenen dades coincidents a les dues columnes. Així és com ho fas:
Primer, seleccioneu les dues columnes que voleu comparar, després aneu al menú "Format" i seleccioneu "Format condicional".
Al panell Regles de format condicional, confirmeu l'interval al quadre "Aplica a l'interval" i trieu "La fórmula personalitzada és" al menú desplegable "Cel·les de la fórmula si...".
A continuació, introduïu la fórmula següent al quadre "Valor o fórmula":
=$A2=$B2Aquesta fórmula compararà les dues columnes fila per fila i ressaltarà les files que tenen valors idèntics (duplicats). Com podeu veure, la fórmula introduïda aquí només és per a la primera fila de l'interval seleccionat, però la fórmula s'aplicarà automàticament a totes les files de l'interval seleccionat mitjançant la funció de format condicional.
A continuació, especifiqueu el format a les opcions "Estil de format" i feu clic a "Fet".
Com podeu veure, només es ressaltaran les files que tinguin dades coincidents (duplicats) entre dues columnes i s'ignoraran la resta de duplicats.
Ressalteu les cel·les duplicades en diverses columnes
Quan treballeu amb fulls de càlcul més grans amb moltes columnes, és possible que vulgueu ressaltar tots els duplicats que apareixen en diverses columnes en lloc de només una o dues columnes. Encara podeu utilitzar el format condicional per ressaltar el duplicat en diverses columnes.
Primer, seleccioneu l'interval de totes les columnes i files que voleu cercar duplicats en lloc d'una o dues columnes. Podeu seleccionar columnes senceres mantenint premuda la tecla Ctrl i fent clic a la lletra de la part superior de cada columna. Alternativament, també podeu fer clic a la primera i l'última cel·la del vostre rang mentre manteniu premuda la tecla Maj per seleccionar diverses columnes alhora.
A l'exemple, estem seleccionant A2:C30.
A continuació, feu clic a l'opció "Format" al menú i seleccioneu "Format condicional".
A les Regles de format condicional, establiu les Regles de format a "La fórmula personalitzada és" i, a continuació, introduïu la fórmula següent al quadre "Valor o fórmula":
=countif($A$2:$C$30,A2)>Estem afegint el signe "$" abans de les referències de cel·les per convertir-les en columnes absolutes, de manera que no canviï, apliquem la fórmula. També podeu introduir la fórmula sense els signes '$', funciona de qualsevol manera.
A continuació, trieu el format en què voleu ressaltar les cel·les duplicades mitjançant les opcions "Estil de format". Aquí, estem escollint el color de farciment "groc". Després d'això, feu clic a "Fet".
Això ressaltarà els duplicats a totes les columnes que heu seleccionat, tal com es mostra a continuació.
Després d'aplicar el format condicional, podeu editar o suprimir la regla de format condicional quan vulgueu.
Si voleu editar la regla de format condicional actual, seleccioneu qualsevol cel·la amb format condicional, aneu a "Format" al menú i seleccioneu "Format condicional".
Això obrirà el panell "Regles de format condicional" a la dreta amb una llista de regles de format aplicades a la selecció actual. Quan passeu el ratolí per sobre de la regla, us mostrarà el botó d'eliminació, feu clic al botó d'eliminació per eliminar la regla. O, si voleu editar la regla que es mostra actualment, feu clic a la regla.
Si voleu afegir un altre format condicional a la regla actual, feu clic al botó "Afegeix una altra regla".
Compteu els duplicats entre dues columnes
De vegades, voleu comptar el nombre de vegades que es repeteix un valor d'una columna en una altra columna. Es pot fer fàcilment utilitzant la mateixa funció COUNTIF.
Per trobar el nombre de vegades que hi ha un valor a la columna A a la columna B, introduïu la fórmula següent en una cel·la d'una altra columna:
=COMPTARSI($B$2:$B$30,$A2)Introduïu aquesta fórmula a la cel·la C2. Aquesta fórmula compta el nombre de vegades que el valor de la cel·la A2 existeix a la columna (B2:B30) i retorna el recompte a la cel·la C2.
Quan escriviu la fórmula i premeu Intro, apareixerà la funció d'emplenament automàtic, feu clic a la "marca de verificació" per omplir automàticament aquesta fórmula a la resta de cel·les (C3: C30).
Si la funció d'emplenament automàtic no apareix, feu clic al quadrat blau a l'extrem inferior dret de la cel·la C2 i arrossegueu-lo cap avall per copiar la fórmula de la cel·la C2 a les cel·les C3:C30.
La columna "Comparació 1" (C) ara us mostrarà el nombre de vegades que cada valor corresponent de la columna A apareix a la columna B. Per exemple, el valor de A2 o "Franklyn" no es troba a la columna B, per tant, La funció COUNTIF retorna "0". I el valor "Loreta" (A5) es troba dues vegades a la columna B, per tant, retorna "2".
Ara, hem de repetir els mateixos passos per trobar els recomptes duplicats de la columna B. Per fer-ho, introduïu la fórmula següent a la cel·la D2 de la columna D (Comparació 2):
=COMPTARSI($A$2:$A$30,$B2)En aquesta fórmula, substituïu l'interval de "$B$2:$B$30" a "$A$2:$A$30" i "$B2" a "$A2". La funció compta el nombre de vegades que el valor de la cel·la B2 existeix a la columna A (A2:A30) i retorna el recompte a la cel·la D2.
A continuació, empleneu automàticament la fórmula a la resta de cel·les (D3:D30) a la columna D. Ara, la "Comparació 2" us mostrarà el nombre de vegades que cada valor corresponent de la columna B apareix a la columna A. Per exemple , el valor de B2 o "Stark" es troba dues vegades a la columna A, per tant, la funció COUNTIF retorna "2".
Nota: Si voleu comptar els duplicats a totes les columnes o a diverses columnes, només heu de canviar l'interval del primer argument de la funció COUNTIF a diverses columnes en lloc d'una sola columna. Per exemple, canvieu l'interval d'A2:A30 a A2:B30, que comptarà tots els duplicats en dues columnes en comptes de només una.
Això és.