Tens un llibre d'Excel amb milers de números i paraules. Hi ha d'haver múltiples del mateix nombre o paraula. Potser haureu de trobar-los. Per tant, veurem diverses maneres de trobar valors coincidents a Excel 365 .
Ens ocuparem de trobar les mateixes paraules o números en dos fulls de treball diferents i en dues columnes diferents. Veurem l'ús de les funcions EXACTE, MATCH i VLOOKUP. És possible que alguns dels mètodes que utilitzarem no funcionin a la versió web de Microsoft Excel, però tots funcionaran a la versió d'escriptori.
Taula de continguts
- Què és una funció d'Excel?
- La funció exacta
- La funció MATCH
- La funció VLOOKUP
- Com puc trobar valors coincidents en dos fulls diferents?
- De quina altra manera puc utilitzar aquestes funcions?
Què és una funció d'Excel?
Si heu utilitzat funcions abans, passeu endavant.
Una funció d'Excel és com una mini aplicació. Aplica una sèrie de passos per realitzar una única tasca. Les funcions d'Excel més utilitzades es poden trobar a la pestanya Fórmules . Aquí els veiem categoritzats per la naturalesa de la funció:
- AutoSum
- Usat recentment
- Financera
- Lògica
- Text
- Data i hora
- Cerca i referència
- Matemàtiques i Trig
- Més Funcions.
La categoria Més funcions conté les categories Estadística, Enginyeria, Cub, Informació, Compatibilitat i Web .
La funció exacta
La tasca de la funció Exact és recórrer les files de dues columnes i trobar valors coincidents a les cel·les d'Excel. Exacte vol dir exacte. Per si sola, la funció Exacta distingeix entre majúscules i minúscules. No veurà Nova York i Nova York com un partit.
A l'exemple següent, hi ha dues columnes de text: entrades i rebuts. Per només 10 conjunts de text, els podríem comparar mirant-los. Imagineu-vos si hi hagués 1.000 files o més. És llavors quan utilitzaríeu la funció Exacta.
Col·loca el cursor a la cel·la C2. A la barra de fórmules, introduïu la fórmula
=EXACTE(E2:E10;F2:F10)
E2:E10 fa referència a la primera columna de valors i F2:F10 fa referència a la columna que hi ha al costat. Un cop premem Retorn , Excel compararà els dos valors de cada fila i ens dirà si és una coincidència ( Veritable ) o no ( Fals ). Com que hem utilitzat intervals en lloc de només dues cel·les, la fórmula s'abocarà a les cel·les de sota i avaluarà totes les altres files.
Aquest mètode, però, és limitat. Només compararà dues cel·les que es troben a la mateixa fila. No compararà el que hi ha a A2 amb B3, per exemple. Com ho fem? MATCH pot ajudar.
La funció MATCH
MATCH es pot utilitzar per indicar-nos on es troba una coincidència per a un valor específic en un rang de cel·les.
Suposem que volem esbrinar en quina fila es troba una SKU (Unitat de manteniment d'estocs) específica, a l'exemple següent.
Si volem trobar en quina fila es troba AA003, utilitzaríem la fórmula:
=COINCIDENT(J1;E2:E9;0)
J1 fa referència a la cel·la amb el valor que volem fer coincidir. E2:E9 es refereix a l'interval de valors que estem cercant. El zero ( 0 ) al final de la fórmula indica a Excel que cerqui una coincidència exacta. Si fóssim números coincidents, podríem utilitzar 1 per trobar alguna cosa menor que la nostra consulta o 2 per trobar alguna cosa més gran que la nostra consulta.
Però, i si volguéssim trobar el preu de AA003?
La funció VLOOKUP
La V a VLOOKUP significa vertical. És a dir, pot cercar un valor determinat en una columna. El que també pot fer és retornar un valor a la mateixa fila que el valor trobat.
Si teniu una subscripció a l'Office 365 al canal mensual, podeu utilitzar el nou XLOOKUP . Si només teniu la subscripció semestral, estarà disponible el juliol de 2020.
Utilitzem les mateixes dades d'inventari i intentem trobar el preu d'alguna cosa.
On estàvem buscant una fila abans, introduïu la fórmula:
=CERCAV(J1;E2:G9;3;FALSE)
J1 fa referència a la cel·la amb el valor que estem fent coincidir. E2:G9 és el rang de valors amb què estem treballant. Però BUSCARV només buscarà una coincidència a la primera columna d'aquest rang. El 3 es refereix a la 3a columna des de l'inici de l'interval.
Així, quan escrivim un SKU a J1, VLOOKUP trobarà la coincidència i agafarà el valor de la cel·la 3 columnes. FALSE indica a Excel quin tipus de concordança estem buscant. FALSE significa que ha de ser una coincidència exacta on TRUE li diria que ha de ser una coincidència semblant.
Com puc trobar valors coincidents en dos fulls diferents?
Cadascuna de les funcions anteriors pot funcionar en dos fulls diferents per trobar valors coincidents a Excel. Utilitzarem la funció EXACTA per mostrar-vos com. Això es pot fer amb gairebé qualsevol funció. No només els que cobrim aquí. També hi ha altres maneres d'enllaçar cel·les entre diferents fulls i llibres de treball .
Treballant el full Holders , introduïm la fórmula
=EXACTE(D2:D10,Entrades!E2:E10)
D2:D10 és l'interval que hem seleccionat al full Holders. Un cop hem posat una coma després, podem fer clic al full d'entrades i arrossegar i seleccionar el segon rang.
Mireu com fa referència al full i al rang com a entrades! E2:E10 ? En aquest cas, cada fila coincideix, de manera que els resultats són certs.
De quina altra manera puc utilitzar aquestes funcions?
Un cop domineu aquestes funcions per fer coincidir i trobar coses, podeu començar a fer-hi moltes coses diferents. També feu una ullada a l'ús de les funcions INDEX i MATCH junts per fer alguna cosa semblant a BUSCAR V.
Teniu alguns consells interessants sobre com utilitzar les funcions d'Excel per trobar valors coincidents a Excel? Potser una pregunta sobre com fer més? Envieu-nos una nota als comentaris a continuació.
S'utilitza INDEX i MATCH en lloc de VLOOKUP
Hi ha certes limitacions amb l'ús de VLOOKUP: la funció VLOOKUP només pot buscar un valor d'esquerra a dreta. Això vol dir que la columna que conté el valor que cerqueu sempre s'ha d'ubicar a l'esquerra de la columna que conté el valor de retorn. Ara, si el vostre full de càlcul no està construït d'aquesta manera, no feu servir BUSCAR V. Utilitzeu la combinació de les funcions INDEX i MATCH.
Aquest exemple mostra una petita llista on el valor que volem cercar, Chicago, no es troba a la columna de l'esquerra. Per tant, no podem utilitzar VLOOKUP. En comptes d'això, utilitzarem la funció MATCH per trobar Chicago a l'interval B1:B11. Es troba a la fila 4. Aleshores, INDEX utilitza aquest valor com a argument de cerca i troba la població de Chicago a la quarta columna (columna D). La fórmula utilitzada es mostra a la cel·la A14.