Home-theater-designers
La majoria de les vegades, cercant a Microsoft Excel el full de càlcul és bastant fàcil. Si no podeu cercar-hi les files i les columnes, podeu utilitzar Ctrl + F per cercar-lo. Si esteu treballant amb un full de càlcul molt gran, podeu estalviar molt de temps fent servir una d’aquestes quatre funcions de cerca.
Un cop hàgiu sabut fer cerques a Excel mitjançant la cerca, no importa la mida que tinguin els vostres fulls de càlcul, sempre podreu trobar alguna cosa a Excel.
1. La funció VLOOKUP
Aquesta funció us permet especificar una columna i un valor i retornarà un valor de la fila corresponent d’una columna diferent (si això no té sentit, quedarà clar en un moment). Dos exemples en què podeu fer-ho són buscar el cognom d’un empleat pel seu número d’empleat o trobar un número de telèfon especificant un cognom.
Aquí teniu la sintaxi de la funció:
=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])- [valor_cerca] és la informació que ja teniu. Per exemple, si necessiteu saber en quin estat es troba una ciutat, seria el nom de la ciutat.
- [taula_array] us permet especificar les cel·les en què la funció cercarà els valors de cerca i retorn. En seleccionar l’interval, assegureu-vos que la primera columna inclosa a la vostra matriu sigui la que inclourà el vostre valor de cerca.
- [col_index_num] és el número de la columna que conté el valor de retorn.
- [range_lookup] és un argument opcional i pren 1 o 0. Si introduïu 1 o ometeu aquest argument, la funció busca el valor que heu introduït o el número més baix següent. Per tant, a la imatge següent, un VLOOKUP que busqui una puntuació SAT de 652 retornarà 646, ja que és el número més proper de la llista que és inferior a 652 i [range_lookup] per defecte és 1.
Vegem com podeu utilitzar-lo. Aquest full de càlcul conté números d’identificació, noms i cognoms, puntuacions de ciutat, estat i SAT. Suposem que voleu trobar la puntuació SAT d'una persona amb el cognom 'Winters'. VLOOKUP ho fa fàcil. Aquí teniu la fórmula que faríeu servir:
=VLOOKUP('Winters', C2:F101, 4, 0)Com que les puntuacions SAT són la quarta columna de la columna de cognoms, 4 és l'argument de l'índex de columna. Tingueu en compte que quan cerqueu text, definir [range_lookup] a 0 és una bona idea. Sense ella, podeu obtenir mals resultats.
Aquí teniu el resultat:
Va retornar 651, la puntuació SAT pertanyent a l'estudiant anomenat Kennedy Winters, que es troba a la fila 92 (que es mostra a la part superior). Hauria trigat molt més a desplaçar-se buscant el nom que escriure ràpidament la sintaxi.
Notes sobre VLOOKUP
Algunes coses són bones per recordar quan utilitzeu VLOOKUP. Assegureu-vos que la primera columna del vostre interval sigui la que inclou el vostre valor de cerca. Si no es troba a la primera columna, la funció retornarà resultats incorrectes. Si les vostres columnes estan ben organitzades, no hauria de ser un problema.
A més, tingueu en compte que VLOOKUP només retornarà un valor. Si haguéssiu utilitzat 'Geòrgia' com a valor de cerca, hauria retornat la puntuació del primer estudiant de Geòrgia i no hauria donat cap indicació que de fet hi hagi dos estudiants de Geòrgia.
2. La funció HLOOKUP
Quan VLOOKUP troba els valors corresponents en una altra columna, HLOOKUP troba els valors corresponents en una fila diferent. Com que normalment és més fàcil escanejar els encapçalaments de columna fins que trobeu el correcte i utilitzeu un filtre per trobar allò que busqueu, HLOOKUP s’utilitza millor quan teniu fulls de càlcul molt grans o si esteu treballant amb valors organitzats per temps .
Aquí teniu la sintaxi de la funció:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])- [valor_cerca] és el valor que coneixeu i voleu trobar el valor corresponent.
- [taula_array] són les cel·les en què voleu cercar.
- [fila_índex_numre] especifica la fila de la qual provindrà el valor retornat.
- [range_lookup] és el mateix que a VLOOKUP, deixeu-lo en blanc per obtenir el valor més proper quan sigui possible o introduïu 0 per cercar només coincidències exactes.
Aquest full de càlcul conté una fila per a cada estat, juntament amb una puntuació SAT durant els anys 2000-2014. Podeu utilitzar HLOOKUP per trobar la puntuació mitjana a Minnesota el 2013. A continuació us expliquem com ho farem:
=HLOOKUP(2013, A1:P51, 24)Com podeu veure a la imatge següent, es torna la puntuació:
Els Minnesotans van obtenir una mitjana de 1014 el 2013. Tingueu en compte que el 2013 no està entre cometes perquè és un número i no una cadena. A més, el 24 prové de Minnesota, situant-se a la 24a fila.
Aquí hi ha com es calcula la mitjana ponderada a Excel .
Notes sobre HLOOKUP
Igual que amb VLOOKUP, el valor de cerca ha de ser a la primera fila de la vostra matriu de taules. Poques vegades és un problema amb HLOOKUP, ja que normalment utilitzeu un títol de columna per obtenir un valor de cerca. HLOOKUP també només retorna un valor únic.
3-4. Les funcions INDEX i MATCH
INDEX i MATCH són dues funcions diferents, però quan s’utilitzen junts poden fer que la cerca d’un full de càlcul gran sigui molt més ràpida. Ambdues funcions tenen inconvenients, però en combinar-les aprofitarem els punts forts de totes dues.
En primer lloc, però, la sintaxi de les dues funcions:
=INDEX([array], [row_number], [column_number])- [matriu] és la matriu en què cercareu.
- [número_fila] i [número_columna] es pot utilitzar per restringir la cerca (ho analitzarem en un moment).
=MATCH([lookup_value], [lookup_array], [match_type])- [valor_cerca] és un terme de cerca que pot ser una cadena o un número.
- [lookup_array] és la matriu en què Microsoft Excel buscarà el terme de cerca.
- [match_type] és un argument opcional que pot ser 1, 0 o -1. 1 retornarà el valor més gran que sigui menor o igual al terme de cerca. 0 només retornarà el vostre terme exacte i -1 retornarà el valor més petit que sigui superior o igual al terme de cerca.
Pot ser que no estigui clar com utilitzarem aquestes dues funcions juntes, de manera que les exposaré aquí. MATCH pren un terme de cerca i retorna una referència de cel·la. A la imatge següent, podeu veure que en una cerca del valor 646 de la columna F, MATCH retorna 4.
INDEX, en canvi, fa el contrari: pren una referència de cel·la i hi retorna el valor. Aquí podeu veure que, quan se us demana que retorni la sisena cel·la de la columna Ciutat, INDEX retorna 'Anchorage', el valor de la fila 6.
El que farem és combinar els dos de manera que MATCH retorni una referència de cel·la i INDEX utilitzi aquesta referència per buscar el valor d'una cel·la. Suposem que recordeu que hi havia un estudiant el cognom era Waters i voleu veure quina era la puntuació d’aquest estudiant. Aquí teniu la fórmula que farem servir:
com veure canals locals a roku
=INDEX(F:F, MATCH('Waters', C:C, 0))Notareu que el tipus de coincidència està establert en 0 aquí. Quan cerqueu una cadena, és el que voldreu utilitzar. Això és el que obtenim quan executem aquesta funció:
Com podeu veure a la fitxa, Owen Waters va obtenir 1720, el número que apareix quan executem la funció. Pot ser que això no sembli tan útil quan només podeu mirar algunes columnes, però imagineu-vos quant de temps estalvieu si ho hagueu de fer 50 vegades en un full de càlcul de base de dades gran que contenia diversos centenars de columnes.
Deixeu que comencin les cerques d'Excel
Microsoft Excel en té molts funcions extremadament potents per manipular dades, i els quatre indicats anteriorment només ratllen la superfície. Aprendre a utilitzar-los us facilitarà la vida.
Si realment voleu dominar Microsoft Excel, podríeu beneficiar-vos de tenir a la mà el full de trucs Essential Excel.
Crèdit de la imatge: Cico / Shutterstock
Compartir Compartir Tweet Correu electrònic Canon vs. Nikon: quina marca de càmeres és millor?Canon i Nikon són els dos noms més importants de la indústria de les càmeres. Però, quina marca ofereix la millor gamma de càmeres i objectius?
Llegiu a continuació Temes relacionats- Productivitat
- Full de càlcul
- Microsoft Excel
- Trucs de cerca
Ian Buckley és periodista, músic, intèrpret i productor de vídeo independent que viu a Berlín, Alemanya. Quan no escriu ni a l’escenari, juga amb electrònica o codi de bricolatge amb l’esperança de convertir-se en un científic boig.
Més de Ian BuckleySubscriu-te al nostre butlletí
Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.
Feu clic aquí per subscriure-us