Fórmula de busca de Excel con varios criterios

Ao usar unha fórmula de matriz en Excel, podemos crear unha fórmula de busca que usa varios criterios para atopar información nunha base de datos ou táboa de datos.

A fórmula matricial implica anidar a función MATCH dentro da función INDEX .

Este tutorial inclúe un exemplo paso a paso de crear unha fórmula de busca que usa varios criterios para atopar un fornecedor de widgets de titanio nunha base de datos de mostra.

Seguindo os pasos que aparecen nos temas de tutorial deba aí a través da creación e uso da fórmula que se ven na imaxe anterior.

01 de 09

Introducir os datos do titorial

O primeiro paso no tutorial é ingresar os datos nunha folla de cálculo de Excel.

Para seguir os pasos do tutorial, introduza os datos que se amosan na imaxe anterior nas seguintes celas .

• Introduce o rango superior de datos nas celas D1 a F2
• Introduce o segundo rango nas celas D5 a F11

As filas 3 e 4 quedan en branco para acomodar a fórmula matricial creada durante este tutorial.

O tutorial non inclúe o formato visto na imaxe, pero isto non afectará o funcionamento da fórmula de busca.

A información sobre opcións de formato semellantes ás vistas anteriormente están dispoñibles neste tutorial básico de formatado de Excel.

02 de 09

Comezando a función INDEX

A función INDEX é un dos poucos en Excel que ten varias formas. A función ten un formulario de matriz e un formulario de referencia .

O formulario de matriz retorna os datos reais dunha base de datos ou dunha táboa de datos, mentres que o Formulario de referencia ofrécelle a referencia ou localización das celas dos datos da táboa.

Neste tutorial utilizaremos o Array Form xa que queremos saber o nome dun fornecedor de widgets de titanio en lugar da referencia de cela a este provedor na nosa base de datos.

Cada formulario ten unha lista diferente de argumentos que deben ser seleccionados antes de comezar a función.

Pasos de titorial

1. Prema na célula F3 para facela a cela activa . Aquí é onde imos entrar na función anidada.
2. Fai clic na pestana Fórmulas do menú da cinta .
3. Escolla Busca e referencia da cinta para abrir a lista despregábel da función.
4. Fai clic en INDEX na lista para abrir o cadro de diálogo Seleccionar argumentos .
5. Escolla a opción matriz, row_num, col_num no cadro de diálogo.
6. Faga clic en Aceptar para abrir o diálogo de función INDEX.

03 de 09

Introducir o argumento Array da función INDEX

O primeiro argumento esixe o argumento Array. Este argumento especifica o alcance das celas a buscar nos datos desexados.

Para este tutorial este argumento será a nosa base de datos de mostra.

Pasos de titorial

1. No cadro de diálogo de función INDEX, prema na liña Array .
2. Destaque as celas D6 a F11 na folla de traballo para ingresar o intervalo na caixa de diálogo.

04 de 09

Comezando a función MATCH aninhada

Ao anidar unha función dentro doutro non é posible abrir a caixa de diálogo da segunda función ou aniñada para ingresar os argumentos necesarios.

A función anidada debe escribirse como un dos argumentos da primeira función.

Neste tutorial, a función MATCH anidada e os seus argumentos entraranse na segunda liña do diálogo de función INDEX - a liña Row_num .

É importante notar que ao ingresar as funcións manualmente, os argumentos da función están separados entre si por unha coma "," .

Introducir o argumento Lookup_value da función MATCH

O primeiro paso para ingresar a función MATCH anidadada é introducir o argumento Lookup_value .

O valor de busca será a referencia de localización ou de cela para o termo de busca que queremos combinar na base de datos.

Normalmente, o valor de busca acepta só un criterio de busca ou un termo. Para buscar varios criterios, debemos ampliar o valor de busca .

Isto faise mediante a concatenación ou xuntanza de dúas ou máis referencias de celas usando o símbolo " & ".

Pasos de titorial

1. No diálogo de función INDEX, fai clic na liña Row_num .
2. Escriba a coincidencia de nome de función seguida dun soporte aberto redondo " ( "
3. Prema na célula D3 para ingresar a referencia da cela na caixa de diálogo.
4. Escriba un ampersand " & " despois da referencia de célula D3 para engadir unha segunda referencia de cela.
5. Prema na célula E3 para ingresar esta segunda referencia de cela na caixa de diálogo.
6. Escriba unha coma "," despois da referencia de celas E3 para completar a entrada do argumento Lookup_value da función MATCH.
7. Deixar a caixa de diálogo de función INDEX aberta para o seguinte paso no titorial.

No último paso do tutorial os valores de busca ingresaranse nas celas D3 e E3 da folla de cálculo.

05 de 09

Engadindo o Lookup_array para a función MATCH

Este paso inclúe engadir o argumento Lookup_array para a función MATCH aninhada.

O Lookup_array é o rango de celas que a función MATCH buscará para atopar o argumento Lookup_value engadido no paso anterior do tutorial.

Xa que identificamos dous campos de busca no argumento Lookup_array , debemos facer o mesmo para Lookup_array . A función MATCH só busca unha matriz para cada termo especificado.

Para introducir varias matrices volvemos a empregar os ampersand " & " para concatenar as xuntas xuntas.

Pasos de titorial

Estes pasos van ser inseridos despois da entrada introducida no paso anterior na liña Row_num no diálogo de función INDEX.

1. Prema na liña Row_num despois da coma para colocar o punto de inserción ao final da entrada actual.
2. Destaque as celas D6 a D11 na folla de traballo para ingresar ao intervalo. Esta é a primeira matriz que a función é buscar.
3. Escriba un ampersand " & " despois das referencias celulares D6: D11 porque queremos que a función busque dúas matrices.
4. Destaque as celas E6 a E11 na folla de traballo para ingresar ao intervalo. Esta é a segunda matriz que a función é buscar.
5. Escriba unha coma "," despois da referencia de celas E3 para completar a entrada do argumento Lookup_array da función MATCH.
6. Deixar a caixa de diálogo de función INDEX aberta para o seguinte paso no titorial.

06 de 09

Engadiuse o tipo de coincidencia e Completando a función MATCH

O terceiro e último argumento da función MATCH é o argumento Match_type.

Este argumento di a Excel como combinar o valor Lookup con valores no Lookup_array. As opcións son: 1, 0 ou -1.

Este argumento é opcional. Se se omite a función usa o valor predeterminado de 1.

• se o Match_type = 1 ou se omite: MATCH atopa o maior valor que é inferior ou igual ao valor_Vista. Os datos de Lookup_array deben clasificarse en orde crecente.
• se o match_type = 0: MATCH atopa o primeiro valor que é exactamente igual ao valor de busca. Os datos de Lookup_array poden clasificarse en calquera orde.
• se o Match_type = -1: MATCH atopa o valor máis pequeno que é maior ou igual ao valor de busca. Os datos de Lookup_array deben ordenarse en orde descendente.

Pasos de titorial

Estes pasos van ser inseridos despois da entrada introducida no paso anterior na liña Row_num no diálogo de función INDEX.

1. Seguindo a coma na liña Row_num , escribe un cero " 0 " xa que queremos que a función anidada devolva coincidencias exactas aos termos que introducimos nas celas D3 e E3.
2. Escriba un soporte de rolda de peche " ) " para completar a función MATCH.
3. Deixar a caixa de diálogo de función INDEX aberta para o seguinte paso no titorial.

07 de 09

Volver á función INDEX

Agora que a función MATCH está feita, pasaremos á terceira liña do cadro de diálogo aberto e introduciremos o último argumento para a función INDEX.

Este terceiro e último argumento é o argumento Column_num que indica a Excel o número de columna no intervalo D6 a F11 onde atopará a información que queremos que devance a función. Neste caso, un provedor de widgets de titanio .

Pasos de titorial

1. Fai clic na liña Column_num no cadro de diálogo.
2. Introduza o número tres " 3 " (sen comiñas) nesta liña xa que estamos a buscar datos na terceira columna do intervalo D6 a F11.
3. Non prema en Aceptar ou pecha o diálogo de función INDEX. Debe permanecer aberto para o seguinte paso no tutorial - creando a fórmula matricial .

08 de 09

Creación da fórmula de matriz

Antes de pechar o cadro de diálogo necesitamos converter a nosa función anidada nunha fórmula matricial .

Unha fórmula matricial é o que permite buscar varios termos na táboa de datos. Neste tutorial buscamos coincidir dous termos: Widgets da columna 1 e do titanio da columna 2.

A creación dunha fórmula matricial en Excel faise presionando CTRL , SHIFT e ENTER nas teclas ao mesmo tempo.

O efecto de presionar estas teclas é envolver a función con chaves: {} indicando que agora é unha fórmula matricial.

Pasos de titorial

1. Coa caixa de diálogo completa aínda aberta desde o paso previo deste tutorial, manteña premido as teclas CTRL e SHIFT no teclado e, a continuación, prema e solte a tecla ENTER .
2. Se se realiza correctamente, a caixa de diálogo pechará e aparecerá un erro de # N / A na cela F3: a cela onde ingresamos a función.
3. O erro N / A aparece na cela F3 porque as celas D3 e E3 están en branco. D3 e E3 son as celas onde contámoslle a función para atopar os Valores de busca no paso 5 do tutorial. Unha vez que se engaden datos a estas dúas celas, o erro substitúese por información da base de datos .

09 de 09

Engadir os criterios de busca

O último paso no tutorial é engadir os termos de busca á nosa folla de cálculo.

Como se mencionou no paso anterior, buscamos emparejar os termos Widgets da columna 1 e Titanium da columna 2.

Se, e só se, a nosa fórmula atopa unha coincidencia para ambos os termos nas columnas axeitadas da base de datos, devolverá o valor desde a terceira columna.

Pasos de titorial

1. Fai clic na cela D3.
2. Escribe Widgets e presiona a tecla Enter no teclado.
3. Fai clic na celda E3.
4. Teclee Titanium e prema a tecla Intro no teclado.
5. O nome do provedor Widgets Inc. debería aparecer na cela F3 - a localización da función xa que é o único provedor que vende os widgets de Titanium.
6. Cando fai clic na célula F3 a función completa
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   aparece na barra de fórmulas arriba da folla de cálculo .

Nota: no noso exemplo só había un fornecedor de widgets de titanio. Se houbese máis de un provedor, o fornecedor listado primeiro na base de datos devolve a función.