És possible que tingueu una taula a Excel 2013 en la qual haureu de realitzar una cerca bidireccional, per la qual cosa es recupera una part de les dades de la taula de cerca basant-se en buscar un valor a la fila superior (amb els encapçalaments de columna de la taula) i un valor a la primera columna (amb els encapçalaments de fila de la taula).
La figura il·lustra una situació en què hauríeu d'utilitzar dos valors, la data de producció i el número de peça, per buscar la producció esperada. A la taula del programa de producció de 2013, les dates de producció de cada part formen els encapçalaments de columna de la primera fila de la taula i els números de part formen els encapçalaments de fila de la primera columna de la taula.
Per cercar el nombre de la part programada per produir-se en un mes determinat, cal que utilitzeu la funció COINCIDENT, que retorna la posició relativa d'un valor determinat en un rang de cel·les o matriu. La sintaxi de la funció MATCH és la següent:
COINCORDA(valor_de_cerca, matriu_de_cerca,[tipus_de_coincidència])
L' argument valor_cerca és, per descomptat, el valor la posició del qual voleu que es torni quan es trobi una coincidència, i la matriu_cerca és l'interval de cel·les o la matriu que conté els valors que voleu fer coincidir. L' argument opcional match_type és el número 1, 0 o –1, que especifica com Excel coincideix amb el valor especificat per l' argument lookup_value a l'interval especificat per l' argument lookup_array :
-
Utilitzeu match_type 1 per trobar el valor més gran que sigui menor o igual que el valor_cerca . Tingueu en compte que els valors de la matriu_cerques s'han de col·locar en ordre ascendent quan utilitzeu l' argument 1 match_type . (Excel utilitza aquest tipus de concordança quan s'omet l' argument match_type de la funció MATCH.)
-
Utilitzeu match_type 0 per trobar el primer valor que sigui exactament igual al lookup_value . Tingueu en compte que els valors de la matriu_cerques poden estar en qualsevol ordre quan utilitzeu l' argument 0 match_type .
-
Utilitzeu match_type – 1 per trobar el valor més petit que sigui més gran o igual que el valor_cerca . Tingueu en compte que els valors de la matriu_cerques s'han de col·locar en ordre descendent quan utilitzeu l' argument -1 match_type .
A més de cercar la posició de la data de producció i el número de peça als encapçalaments de columnes i files de la taula de la programació de producció, cal que utilitzeu una funció INDEX, que utilitza la posició relativa del número de fila i columna per retornar el número que s'ha de produir. de la mateixa taula.
La funció INDEX segueix dues formes de sintaxi diferents: matriu i referència. Utilitzeu el formulari de matriu quan voleu que es torni un valor de la taula (com feu en aquest exemple) i utilitzeu el formulari de referència quan voleu que es torni una referència de la taula.
La sintaxi de la forma de matriu de la funció INDEX és la següent:
INDEX(matriu,[núm_fila],[núm_col])
La sintaxi de la forma de referència de la funció INDEX és la següent:
INDEX(referència,[núm_fila],[núm_col],[núm_àrea])
La gamma argument de la forma de selecció de la funció d'índex és un rang de cel·les o una constant de matriu que desitja que l'Excel per al seu ús en les operacions de cerca. Si aquest interval o constant conté només una fila o columna, els arguments row_num o col_num corresponents són opcionals.
Si l'interval o la constant de matriu té més d'una fila o més d'una columna i especifiqueu els arguments row_num i col_num , Excel retorna el valor de l' argument de matriu que es troba a la intersecció de l' argument row_num i l' argument col_num .
Per a les funcions MATCH i INDEX de l'exemple, es van assignar els noms d'interval següents als intervals de cel·les següents:
-
dades_taula a l'interval de cel·les A2:J6 amb les dades de producció més els encapçalaments de columnes i files
-
part_list a l'interval de cel·les A2:A6 amb els encapçalaments de fila de la primera columna de la taula
-
data_list a l'interval de cel·les A2:J2 amb els encapçalaments de columna a la primera fila de la taula
-
part_lookup a la cel·la B10 que conté el nom de la part a buscar a la taula
-
date_lookup a la cel·la B11 que conté el nom de la data de producció que cal cercar a la taula
Com mostra la figura, la cel·la B12 conté una fórmula força llarga i, a primera vista, complexa que utilitza els noms d'interval descrits anteriorment i combina les funcions INDEX i MATCH:
=INDEX(dades_taula, COINCIDENCIA (cerca_part, llista_part), COINCIDENT (cerca_data, llista_data))