Contidos
Na segunda parte do noso tutorial sobre a función VPR (LOOKUP) en Excel, analizaremos algúns exemplos que che axudarán a dirixir todo o poder VPR para resolver as tarefas máis ambiciosas de Excel. Os exemplos supoñen que xa tes un coñecemento básico de como funciona esta función. Se non, pode estar interesado en comezar coa primeira parte deste titorial, que explica a sintaxe e o uso básico. VPR. Ben, imos comezar.
Busca en Excel por varios criterios
función VPR en Excel é unha ferramenta realmente poderosa para realizar buscas dun determinado valor nunha base de datos. Non obstante, hai unha limitación significativa: a súa sintaxe permítelle buscar só un valor. E se queres buscar por varias condicións? A continuación atoparás a solución.
Exemplo 1: busca por 2 criterios diferentes
Supoñamos que temos unha lista de pedidos e queremos atopala Cantidade de mercadorías (Cant.), en base a dous criterios: Nome do cliente (Cliente) e Nome do produto (Produto). O asunto complícase polo feito de que cada un dos compradores pediu varios tipos de mercadorías, como se pode ver na seguinte táboa:
función regular VPR non funcionará neste escenario porque devolverá o primeiro valor que atope que coincida co valor de busca indicado. Por exemplo, se quere saber a cantidade dun artigo doces'ordenado polo comprador Jeremy Hill, escribe a seguinte fórmula:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– esta fórmula devolverá o resultado 15correspondente ao produto mazás, porque é o primeiro valor que coincide.
Hai unha solución sinxela: crea unha columna adicional na que combinar todos os criterios desexados. No noso exemplo, estas son as columnas Nome do cliente (Cliente) e Nome do produto (Produto). Non esquezas que a columna combinada sempre debe ser a columna máis á esquerda do intervalo de busca, xa que é a columna da esquerda onde a función VPR mira cara arriba cando busca un valor.
Entón, engade unha columna auxiliar á táboa e copia a seguinte fórmula en todas as súas celas: =B2&C2. Se queres que a cadea sexa máis lexible, podes separar os valores combinados cun espazo: =B2&» «&C2. Despois diso, pode usar a seguinte fórmula:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Onde está a cela B1 contén o valor concatenado do argumento valor_valor (valor_busca) e 4 – Argumento col_index_num (número_columna), é dicir, o número da columna que contén os datos que se van recuperar.
Exemplo 2: BUSCAR V por dous criterios coa táboa que se ve noutra folla
Se precisa actualizar a táboa principal (Táboa principal) engadindo datos da segunda táboa (Táboa de busca), que se atopa noutra folla ou noutro libro de Excel, pode recoller o valor desexado directamente na fórmula que insira. na mesa principal.
Como no exemplo anterior, necesitará unha columna auxiliar na táboa de busca cos valores combinados. Esta columna debe ser a columna máis á esquerda do intervalo de busca.
Entón a fórmula con VPR podería ser así:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Aquí, as columnas B e C conteñen os nomes dos clientes e dos produtos, respectivamente, e a ligazón Pedidos!$A&$2:$D$2 define unha táboa para buscar noutra folla.
Para facer a fórmula máis lexible, pode darlle un nome ao intervalo de visualización e, a continuación, a fórmula parecerá moito máis sinxela:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Para que a fórmula funcione, os valores da columna máis esquerda da táboa que estás mirando deben combinarse exactamente do mesmo xeito que nos criterios de busca. Na figura anterior, combinamos os valores u2bu2band poñendo un espazo entre eles, do mesmo xeito que cómpre facer no primeiro argumento da función (BXNUMX& "" & CXNUMX).
Lembrade! función VPR limitado a 255 caracteres, non pode buscar un valor que teña máis de 255 caracteres. Teña isto presente e asegúrese de que a lonxitude do valor desexado non supere este límite.
Estou de acordo en que engadir unha columna auxiliar non é a solución máis elegante e non sempre aceptable. Podes facer o mesmo sen a columna auxiliar, pero iso requiriría unha fórmula moito máis complexa cunha combinación de funcións Index (ÍNDICE) e XOGO (MÁIS EXPOÑA).
Extraemos os valores 2º, 3º, etc. usando VLOOKUP
Xa o sabes VPR pode devolver só un valor coincidente, máis precisamente, o primeiro atopado. Pero que pasa se este valor repítese varias veces na matriz vista e queres extraer o 2º ou 3º deles? E se todos os valores? O problema parece complicado, pero a solución existe!
Supoñamos que unha columna da táboa contén os nomes dos clientes (Nome do cliente) e que a outra columna contén os produtos (Produto) que compraron. Tentemos atopar o 2º, 3º e 4º artigos adquiridos por un determinado cliente.
O xeito máis sinxelo é engadir unha columna auxiliar antes da columna nome do cliente e enche-lo con nomes de clientes co número de repetición de cada nome, por exemplo, John Doe 1, John Doe 2 etc. Faremos o truco coa numeración mediante a función COUNTIF (COUNTIF), dado que os nomes dos clientes están na columna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Despois diso, pode usar a función normal VPRpara atopar a orde requirida. Por exemplo:
- Buscar 2º artigo solicitado polo cliente Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Buscar 3º artigo solicitado polo cliente Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
De feito, pode introducir unha referencia de cela como valor de busca en lugar de texto, como se mostra na seguinte figura:
Se só buscas 2-e repetición, pode facelo sen a columna auxiliar creando unha fórmula máis complexa:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Nesta fórmula:
- $F $2 – unha cela que contén o nome do comprador (non se modifica, teña en conta: a ligazón é absoluta);
- $ B $ - columna nome do cliente;
- Táboa4 – A túa mesa (este lugar tamén pode ser un rango normal);
- 16 $ – a cela final da súa táboa ou rango.
Esta fórmula só atopa o segundo valor coincidente. Se precisa extraer as repeticións restantes, use a solución anterior.
Se precisa unha lista de todas as coincidencias, a función VPR este non é un axudante, xa que só devolve un valor á vez: período. Pero Excel ten unha función Index (ÍNDICE), que pode facer fronte facilmente a esta tarefa. Como se verá esa fórmula, aprenderá no seguinte exemplo.
Recuperar todas as repeticións do valor desexado
Como se mencionou anteriormente VPR non pode extraer todos os valores duplicados do intervalo dixitalizado. Para iso, necesitas unha fórmula un pouco máis complexa, composta por varias funcións de Excel, como Index (ÍNDICE), SMALL (PEQUENO) e Row (LIÑA)
Por exemplo, a fórmula seguinte atopa todas as repeticións do valor da cela F2 no intervalo B2:B16 e devolve o resultado das mesmas filas da columna C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Introduza esta fórmula matricial en varias celas adxacentes, como as celas F4: F8como se mostra na figura seguinte. O número de celas debe ser igual ou superior ao número máximo posible de repeticións do valor buscado. Non esquezas facer clic Ctrl + Maiús + Entrarpara introducir a fórmula matricial correctamente.
Se estás interesado en entender como funciona, profundicemos un pouco nos detalles da fórmula:
1 parte:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – compare o valor da cela F2 con cada un dos valores do intervalo B2:B16. Se se atopa unha coincidencia, entón a expresión STRING(C2:C16)-1 devolve o número da liña correspondente (valor -1 permítelle non incluír a liña de cabeceira). Se non hai coincidencias, a función IF (IF) devolve unha cadea baleira.
Resultado da función IF (IF) haberá unha matriz horizontal como: {1,"",3,"",5,"","","","","","",12,"","",""}
2 parte:
ROW()-3
СТРОКА()-3
Aquí a función Row (LINE) actúa como contador adicional. Dado que a fórmula se copia nas celas F4:F9, restamos o número 3 do resultado da función para obter o valor 1 na cela F4 (liña 4, resta 3) para obter 2 na cela F5 (liña 5, resta 3) etc.
3 parte:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
función SMALL (PEQUENO) volve n-oh o valor máis pequeno da matriz de datos. No noso caso, que posición (da máis pequena) a devolver está determinada pola función Row (LIÑA) (ver a parte 2). Entón, para unha célula F4 función PEQUENO({matriz},1) Volta 1º elemento de matriz (menor), é dicir 1. Para celular F5 Volta 2º o elemento máis pequeno da matriz, é dicir 3, Etc
4 parte:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
función Index (INDEX) simplemente devolve o valor dunha cela específica nunha matriz C2:C16. Para celular F4 función ÍNDICE($C$2:$C$16) volverá mazáspara F5 función ÍNDICE($C$2:$C$16) volverá doces' e así por diante.
5 parte:
IFERROR()
ЕСЛИОШИБКА()
Finalmente, poñemos a fórmula dentro da función SE ERRO (IFERROR), porque é pouco probable que estea satisfeito coa mensaxe de erro #AT (#N/A) se o número de celas nas que se copia a fórmula é menor que o número de valores duplicados no intervalo que se está a ver.
XNUMXD busca por fila e columna coñecidas
Realizar unha busca XNUMXD en Excel implica buscar un valor por un número de fila e columna coñecida. Noutras palabras, estás extraendo o valor da cela na intersección dunha fila e columna determinadas.
Entón, imos á nosa táboa e escribamos unha fórmula cunha función VPR, que atopará información sobre o custo dos limóns vendidos en marzo.
Hai varias formas de realizar unha busca XNUMXD. Consulta as opcións e escolle a que máis che conveña.
Funcións VLOOKUP e MATCH
Podes usar unha morea de funcións VPR (BUSCAR V) e MÁIS EXPOÑA (COINCER) para atopar o valor na intersección dos campos Nome do produto (cadea) e Mes (columna) da matriz en cuestión:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
A fórmula anterior é unha función regular VPR, que busca unha coincidencia exacta do valor "Limóns" nas celas A2 a A9. Pero como non sabes en que columna están as vendas de marzo, non poderás establecer o número de columna para o terceiro argumento da función. VPR. En cambio, úsase a función MÁIS EXPOÑApara definir esta columna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Traducida á linguaxe humana, esta fórmula significa:
- Buscamos os personaxes “Mar” – argumento valor_valor (valor_busca);
- Buscando nas celas de A1 a I1 – argumento matriz_busca (matriz_busca);
- Devolvendo coincidencia exacta - argumento tipo_concordancia (tipo de coincidencia).
Uso 0 no terceiro argumento, di funcións MÁIS EXPOÑA busque o primeiro valor que coincida exactamente co valor que busca. Isto é equivalente ao valor FALSE (FALSO) para o cuarto argumento VPR.
Así é como podes crear unha fórmula de busca bidireccional en Excel, tamén coñecida como busca bidimensional ou busca bidireccional.
Función SUMPRODUCT
función SUMPRODUCT (SUMPRODUCT) devolve a suma dos produtos das matrices seleccionadas:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funcións INDEX e MATCH
No seguinte artigo explicarei detalladamente estas funcións, polo que de momento podes copiar esta fórmula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Rangos nomeados e operador de intersección
Se non che gustan todas esas fórmulas complexas de Excel, pode que che guste esta forma visual e memorable:
- Seleccione a táboa, abra a pestana Fórmulas (Fórmulas) e prema Crear a partir de Selección (Crear a partir da selección).
- Marque as caixas Fila superior (na liña anterior) e Columna esquerda (na columna da esquerda). Microsoft Excel asignará nomes aos intervalos dos valores da fila superior e da columna esquerda da folla de cálculo. Agora podes buscar usando estes nomes directamente sen crear fórmulas.
- En calquera cela baleira, escribe =nome_fila nome_columna, por exemplo así:
= Limóns Mar
... ou viceversa:
=Mar Limóns
Lembra que os nomes das filas e das columnas deben estar separados por un espazo, que neste caso funciona como o operador de intersección.
Cando introduza un nome, Microsoft Excel mostrará unha información cunha lista de nomes coincidentes, igual que cando introduce unha fórmula.
- prensa Entrar e comprobar o resultado
En xeral, calquera dos métodos anteriores que elixas, o resultado dunha busca bidimensional será o mesmo:
Usando varias VLOOKUPs nunha fórmula
Acontece que a táboa principal e a táboa de busca non teñen unha soa columna en común, e isto impide que use a función habitual VPR. Non obstante, hai outra táboa que non contén a información que nos interesa, senón que ten unha columna común coa táboa principal e coa táboa de busca.
Vexamos o seguinte exemplo. Temos unha táboa principal cunha columna SKU (novo), onde quere engadir unha columna cos prezos correspondentes doutra táboa. Ademais, temos 2 táboas de busca. O primeiro (táboa de consulta 1) contén números actualizados SKU (novo) e nomes de produtos, e o segundo (táboa de consulta 2) - nomes de produtos e números antigos SKU (antigo).
Para engadir prezos da segunda táboa de busca á táboa principal, debes realizar unha acción coñecida como dobre VPR ou aniñado VPR.
- Escribe unha función VPR, que atopa o nome do produto na táboa Táboa de consulta 1uso SKU, como o valor desexado:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Aquí Novo_SKU - rango nomeado $A:$B na táboa Táboa de consulta 1, 2 - esta é a columna B, que contén os nomes dos bens (ver a imaxe superior)
- Escribe unha fórmula para inserir prezos dunha táboa Táboa de consulta 2 baseado en nomes de produtos coñecidos. Para iso, pega a fórmula que creou anteriormente como o valor de busca para a nova función VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Aquí prezo - rango nomeado $A:$C na táboa Táboa de consulta 2, 3 é a columna C que contén os prezos.
A seguinte figura mostra o resultado devolto pola fórmula que creamos:
Substitución dinámica de datos de diferentes táboas mediante VLOOKUP e INDIRECTO
En primeiro lugar, imos aclarar o que queremos dicir coa expresión “Substitución dinámica de datos de diferentes táboas” para asegurarnos de que nos entendemos correctamente.
Hai situacións nas que hai varias follas con datos do mesmo formato, e é necesario extraer a información necesaria dunha determinada folla, dependendo do valor que se introduza nunha determinada cela. Creo que é máis doado explicalo cun exemplo.
Imaxina que tes informes de vendas para varias rexións cos mesmos produtos e no mesmo formato. Quere atopar cifras de vendas para unha rexión específica:
Se só tes dous informes deste tipo, podes usar unha fórmula vergonzosamente sinxela con funcións VPR и IF (IF) para seleccionar o informe desexado para buscar:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Onde:
- $D$2 é unha cela que contén o nome do produto. Teña en conta que aquí usamos referencias absolutas para evitar cambiar o valor de busca ao copiar a fórmula noutras celas.
- $D3 é unha cela co nome da rexión. Estamos a usar unha referencia de columna absoluta e unha referencia de fila relativa porque pensamos copiar a fórmula noutras celas da mesma columna.
- FL_Sales и CA_Vendas – os nomes das táboas (ou rangos denominados) que conteñen os correspondentes informes de vendas. Por suposto, podes usar os nomes habituais das follas e as referencias de intervalos de celas, por exemplo "Folla FL"!$A$3:$B$10, pero os intervalos con nome son moito máis cómodos.
Non obstante, cando hai moitas táboas deste tipo, a función IF non é a mellor solución. Pola contra, pode usar a función INDIRECTO (INDIRECT) para devolver o intervalo de busca desexado.
Como probablemente sabes, a función INDIRECTO úsase para devolver unha ligazón dada por unha cadea de texto, que é exactamente o que necesitamos agora. Entón, substitúe con coraxe na fórmula anterior a expresión pola función IF para vincular coa función INDIRECTO. Aquí tes unha combinación VPR и INDIRECTO funciona moi ben con:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Onde:
- $D$2 – esta é unha cela co nome do produto, non se modifica debido á ligazón absoluta.
- $D3 é a cela que contén a primeira parte do nome da rexión. No noso exemplo, isto FL.
- _Vendas – a parte común do nome de todos os rangos ou táboas nomeados. Cando se combina co valor da cela D3, forma o nome completo do intervalo necesario. Abaixo amósanse algúns detalles para aqueles que son novos na función INDIRECTO.
Como funcionan INDIRECTO e VLOOKUP
Primeiro, permíteme recordarche a sintaxe da función INDIRECTO (INDIRECTO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
O primeiro argumento pode ser unha referencia de cela (estilo A1 ou R1C1), un nome de intervalo ou unha cadea de texto. O segundo argumento determina que estilo de ligazón está contido no primeiro argumento:
- A1se o argumento é CÓDIGO VERDADEIRO (VERDADEIRO) ou non especificado;
- R1C1Se FAS E (FALSO).
No noso caso, a ligazón ten o estilo A1, polo que podes deixar fóra o segundo argumento e centrarte no primeiro.
Entón, volvamos aos nosos informes de vendas. Se o recordas, cada informe é unha táboa separada situada nunha folla separada. Para que a fórmula funcione correctamente, debes nomear as túas táboas (ou rangos) e todos os nomes deben ter unha parte común. Por exemplo, así: CA_Vendas, FL_Vendas, TX_Vendas etcétera. Como podes ver, "_Sales" está presente en todos os nomes.
función INDIRECTO conecta o valor da columna D e a cadea de texto "_Sales", indicando así VPR en que táboa buscar. Se a cela D3 contén o valor "FL", a fórmula buscará na táboa FL_Vendas, se "CA" - na táboa CA_Vendas e así por diante.
O resultado das funcións VPR и INDIRECTO serán as seguintes:
Se os datos están situados en diferentes libros de Excel, entón cómpre engadir o nome do libro antes do intervalo nomeado, por exemplo:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Se a función INDIRECTO refírese a outro libro de traballo, ese libro debe estar aberto. Se está pechada, a función informará dun erro. # Ref! (#SSIL!).