Les funcions de cerca d'Excel 2016 més populars són les funcions HLOOKUP (per a la cerca horitzontal) i VLOOKUP (per a la cerca vertical). Aquestes funcions es troben al menú desplegable Cerca i referència a la pestanya Fórmules de la cinta, així com a la categoria Cerca i referència al quadre de diàleg Insereix funció. Formen part d'un grup potent de funcions que poden retornar valors buscant-los a les taules de dades.
La funció VLOOKUP cerca verticalment (de dalt a baix) la columna més a l'esquerra d'una taula de cerca fins que el programa localitza un valor que coincideixi o superi el que esteu buscant. La funció HLOOKUP cerca horitzontalment (d'esquerra a dreta) la fila superior d'una taula de cerca fins que localitza un valor que coincideixi o superi el que esteu buscant.
La funció BUSCARV utilitza la sintaxi següent:
BUSCAR V(valor_cerca, matriu_taula, num_índex_col,[cerca_interval])
La funció HLOOKUP segueix la sintaxi gairebé idèntica:
CERC_HL(valor_cerca, matriu_taula,núm_índex_fila,[cerca_interval])
En ambdues funcions, l' argument valor_de_cerca és el valor que voleu cercar a la taula de cerca, i matriu_taula és l'interval de cel·les o el nom de la taula de cerca que conté tant el valor a cercar com el valor relacionat a retornar.
L' argument col_index_num designa la columna de la taula de cerca que conté els valors que retorna la funció VLOOKUP basant-se en la concordança del valor de l' argument lookup_value amb els de l'argument table_array. Determineu l' argument col_index_num comptant quantes columnes hi ha aquesta columna a la dreta des de la primera columna de la taula de cerca vertical i incloeu la primera columna de la taula de cerca en aquest recompte.
L' argument row_index_num designa la fila que conté els valors que retorna la funció HLOOKUP en una taula horitzontal. Determineu l' argument row_index_num comptant quantes files hi ha aquesta fila cap avall de la fila superior de la taula de cerca horitzontal. De nou, incloeu la fila superior de la taula de cerca en aquest recompte.
Quan introduïu els arguments col_index_num o row_index_num a les funcions BUSCARV i BUSCAR HL , el valor que introduïu no pot superar el nombre total de columnes o files de la taula de cerca.
L' argument range_lookup opcional tant a les funcions BUSCARV com a BUSCAR HL és el TRUE o FALSE lògic que especifica si voleu que Excel trobi una coincidència exacta o aproximada per al valor_cerca a la matriu_taula. Quan especifiqueu TRUE o ometeu l' argument range_lookup a la funció BUSCAR V o BUSCAR HL , Excel troba una coincidència aproximada. Quan especifiqueu FALSE com a argument range_lookup , Excel només troba coincidències exactes.
La cerca de coincidències aproximades només s'aplica quan cerqueu entrades numèriques (en lloc de text) a la primera columna o fila de la taula de cerca vertical o horitzontal. Quan Excel no troba una coincidència exacta en aquesta columna o fila de cerca, localitza el següent valor més alt que no superi l' argument search_value i després retorna el valor a la columna o fila designada pels arguments col_index_num o row_index_num .
Quan utilitzeu les funcions BUSCAR V i BUSCAR HL, el text o les entrades numèriques de la columna o fila de cerca (és a dir, la columna més a l'esquerra d'una taula de cerca vertical o la fila superior d'una taula de cerca horitzontal) han de ser úniques. Aquestes entrades també s'han d'ordenar o ordenar en ordre ascendent; és a dir, l'ordre alfabètic de les entrades de text i l'ordre de menor a major per a les entrades numèriques.
La figura mostra un exemple d'ús de la funció BUSCAR V per retornar una propina del 15% o del 20% d'una taula de propina, depenent del total abans d'impostos del xec. La cel·la F3 conté la funció BUSCAR V:
=BUSCARV(Total_pretax, Taula_de propina, SI(Percentatge_de propina=0,15,2,3))
Aquesta fórmula retorna l'import de la propina en funció del percentatge de propina a la cel·la F1 i l'import abans d'impostos del xec a la cel·la F2.
Utilitzant la funció BUSCARV per retornar la quantitat de la propina que cal afegir d'una taula de cerca.
Per utilitzar aquesta taula de propina, introduïu el percentatge de la propina (15% o 20%) a la cel·la F1 (anomenada Tip_Percentage) i l'import del xec abans d'impostos a la cel·la F2 (anomenada Pretax_Total). Aleshores, Excel cerca el valor que introduïu a la cel·la Pretax_Total de la primera columna de la taula de cerca, que inclou l'interval de cel·les A2:C101 i s'anomena Tip_Table.
Llavors, Excel baixa els valors de la primera columna de Tip_Table fins que troba una coincidència, i després el programa utilitza l' argument col_index_num a la funció VLOOKUP per determinar quina quantitat de propina d'aquesta fila de la taula ha de tornar a la cel·la F3. Si Excel troba que el valor introduït a la cel·la Pretax_Total ($16,50 en aquest exemple) no coincideix exactament amb un dels valors de la primera columna de Tip_Table, el programa continua buscant l'interval de comparació fins que trobi el primer valor que supera. el total abans d'impostos (17,00 a la cel·la A19 en aquest exemple). A continuació, Excel torna a pujar a la fila anterior de la taula i retorna el valor de la columna que coincideix amb l' argument col_index_num de la funció BUSCAR V. (Això és perquè l'opció range_lookups'ha omès l'argument de la funció.)
Tingueu en compte que l'exemple de la taula de suggeriments de la figura utilitza una funció SI per determinar l' argument col_index_num per a la funció BUSCARV a la cel·la F3. La funció SI determina el número de columna que s'utilitzarà a la taula de suggeriments fent coincidir el percentatge introduït a Tip_Percentage (cel·la F1) amb 0,15. Si coincideixen, la funció retorna 2 com a argument col_index_num i la funció BUSCARV retorna un valor de la segona columna (la columna B del 15%) a l'interval Tip_Table. En cas contrari, la funció SI retorna 3 com a argument col_index_num i la funció BUSCARV retorna un valor de la tercera columna (la columna del 20% C) a l'interval Tip_Table.
La figura següent mostra un exemple que utilitza la funció HLOOKUP per cercar el preu de cada article de fleca emmagatzemat en una taula de cerca de preus independent i després per tornar aquest preu a la columna Preu/Doz de la llista de vendes diàries. La cel·la F3 conté la fórmula original amb la funció HLOOKUP que després es copia a la columna F:
Utilitzant la funció HLOOKUP per retornar el preu d'un article de fleca des d'una taula de cerca.
=BUSCARH(element,taula_preus,2,FALSE)
En aquesta funció HLOOKUP, el nom d'interval Element que es dóna a la columna Element a l'interval C3:C62 es defineix com l' argument valor_cerca i la taula de preus del nom de l'interval de cel·les que es dóna a l'interval de cel·les I1:M2 és l' argument table_array . L' argument row_index_num és 2 perquè voleu que Excel torni els preus a la segona fila de la taula de cerca de preus, i l' argument opcional range_lookup és FAL perquè el nom de l'article a la llista de vendes diàries ha de coincidir exactament amb el nom de l'article a la taula de cerca de preus. .
En fer que la funció HLOOKUP utilitzi l'interval de la taula de preus per introduir el preu per dotzena de cada article de fleca a la llista de vendes diàries, podeu actualitzar qualsevol de les vendes de la llista. Tot el que heu de fer és canviar el seu cost Preu/Doz en aquest rang, i la funció HLOOKUP actualitza immediatament el nou preu a la llista de vendes diàries allà on es ven l'article.