Transcrições
1. Introdução — o índice e as fórmulas de combinação: Olá a todos. Sou Jeremy, ou como alguns me chamam Big Dog. Bem-vindo ao meu curso
Skillshare sobre as fórmulas de índice e correspondência. Antes de saltarmos para o conteúdo, deixe-me dar um segundo
para me apresentar. Estou no mundo do
trabalho há quase sete anos e
todo esse tempo foi gasto no Excel para o meu tempo na faculdade para minha função
atual hoje, Excel faz parte
do meu kit de ferramentas diário. Desenvolvi uma paixão por análises e
interesses em
descobrir as soluções mais eficientes e um amor pela automação. Meu objetivo para esta
série de vídeos é fazer de você um especialista em fórmulas de índice
e correspondência. As
fórmulas de índice e correspondência, quando combinadas, fornecem uma
função Lookup que
supera as
pesquisas VLookup e H e garante que seus cálculos não
sejam alterados com base em
linhas adicionais ou colunas, a correspondência de índice pode aumentar
a eficácia de planilhas
ad hoc e modelos de custo
complexos. Junte-se a mim nesta série enquanto
desbloqueamos o próximo
nível em seu
potencial celular e deixamos as outras
funções de pesquisa na poeira. Vamos atrás disso.
2. Noções básicas de índices e combinações — parte 1: correspondência exata: Olá a todos, Bem-vindo ao
primeiro vídeo da série. Nos próximos vídeos, abordaremos as fórmulas de
índice e correspondência. A fórmula de índice nos faz definir um intervalo de células e nos
permite retornar o valor de uma célula dentro desse intervalo, dadas as coordenadas
das células. A fórmula de correspondência solicita um valor de pesquisa e
o intervalo definido, ou o valor de pesquisa aparece, a fórmula de correspondência
retornará o número da coluna ou da
linha no intervalo definido ou na pesquisa valor é encontrado. Embora essas fórmulas possam
ser usadas por conta própria, o caso de uso mais comum
é quando elas são combinadas. Nos próximos vídeos,
abordaremos alguns exemplos das diferentes maneiras pelas quais essas
duas fórmulas podem ser usadas. Vamos mergulhar. Neste exemplo,
temos uma tabela de faturas para março que está
faltando a cidade do cliente. Gostaríamos de puxar
a cidade do cliente
da tabela de pesquisa para
a mesa de trabalho. Podemos usar as fórmulas combinadas de índice e correspondência para nos ajudar. A fórmula de índice
começa pedindo o raio IRB que tem
nossos dados desejados, que é B3 a B6
na tabela de pesquisa. Observe que isso será definido como uma referência absoluta para que o intervalo não mude à medida que
copiamos a fórmula para outras células. Em seguida, a fórmula de índice quer
o número da linha na matriz que acabamos de definir
da célula onde os dados
desejados residem. Porque o número de
linha específico para a cidade que queremos devolver
mudará com base no cliente. Precisaremos que o
número da linha seja dinâmico. Para isso, usaremos
a fórmula de correspondência. A fórmula de correspondência primeiro
pede o valor de pesquisa um dez. Em seguida, ele nos pede para
inserir a matriz de pesquisa, que será A3 a seis. Assim como a matriz de índice acima, isso também será definido como
uma referência absoluta. Finalmente, ele
pede o tipo de correspondência, que neste caso
será 0 porque queremos uma correspondência exata menor
que e
maior que as correspondências serão
explicadas em vídeos posteriores. Mas saiba que eles normalmente
são usados
apenas para números correspondentes. A fórmula de índice tem um número de coluna de
entrada opcional final que não
usaremos neste exemplo, mas vamos dar uma
olhada mais tarde. Certo, terminamos
a fórmula. Vamos copiá-lo para baixo e ver se estamos recebendo as cidades
corretas. Fantástico. Agora, como
essa combinação de fórmulas pode ser um pouco confusa, vamos revisar rapidamente
o que aconteceu. Primeiro, usamos a
fórmula de índice para obter o intervalo de células onde as informações
da cidade do cliente que queríamos estavam localizadas. Como não queríamos
inserir manualmente o número da linha da cidade
do cliente para
cada linha da fatura, usamos a fórmula de correspondência para retornar
dinamicamente
o número da linha. A fórmula de correspondência retorna o número da linha
do cliente na tabela de pesquisa com base
no valor de pesquisa que
fornecemos da tabela de trabalho, pudemos usar
o número da linha que o fórmula de correspondência fornecida como uma entrada na fórmula do índice para depois retornar a cidade do
cliente. Tenha em mente que as fórmulas de
índice ou o ray e a matriz
de pesquisa de fórmulas de correspondência devem ser os mesmos valores de linha. No nosso caso, as linhas de
três a seis, para que as
fórmulas
funcionem adequadamente juntas. Tudo bem, vamos passar para
outro exemplo para solidificar
nosso entendimento.
3. Noções básicas de índices e combinações — parte 2: menos do que de correspondência: Olá a todos, Bem-vindo ao
segundo vídeo da série. Neste vídeo,
vamos construir a combinação das
fórmulas de índice e correspondência e nos
aprofundar no que a fórmula de correspondência pode
fazer. Vamos entrar nisso. Neste exemplo,
temos uma lista de clientes e as
vendas anuais alcançadas por cada um. Para cada cliente, queremos determinar quais
descontos devem ser baseados nas
lágrimas de desconto associadas
às metas de vendas anuais
na tabela de pesquisa. Como no vídeo anterior, a fórmula de índice começa
com a matriz
dos dados desejados, B3 a B8. Em seguida, para que a fórmula de índice seja o
número da linha Input Dynamic, temos que usar a fórmula de
correspondência. No exemplo de vídeo anterior, usamos o cliente como
o valor de pesquisa e 0 uma correspondência exata
como o tipo de correspondência. Neste exemplo,
usaremos um número de vendas
anuais como o valor de
pesquisa em um menos do que o tipo de correspondência. Vamos passar por isso. O tipo de correspondência
será de vendas anuais entre o array de pesquisa
será de três a oito, e o tipo de correspondência será um. Vamos passar rapidamente pelo que
está acontecendo na fórmula de partida. Desta vez, o
colchão primeiro pegou o valor anual de
pesquisa de vendas e combinando-o com as
vendas anuais na matriz de pesquisa. Como inserimos
menos do que como o tipo de correspondência, a fórmula de correspondência encontrará o valor mais próximo
na matriz de pesquisa
menor que o nosso valor de pesquisa. Em seguida, ele retornará
o número da linha do menor que a correspondência
mais próxima e será alimentado
na fórmula do índice como o número da linha dinâmica. Já tínhamos dado
a fórmula do índice, a matriz de dados. E agora, com um
número de linha dinâmico da fórmula de correspondência, ele retornará o
conteúdo da célula
na posição de números de linha dinâmica dentro da matriz. Vamos copiar a
fórmula para baixo para ver se obtivemos os
resultados que esperávamos. Impressionante, tudo parece bom. Vamos avançar
para o próximo exemplo.
4. Noções básicas de índices e combinações — parte 3: correspondência de correspondência de índices: Olá a todos, Bem-vindo ao
terceiro vídeo da série. Neste vídeo,
vamos adicionar
uma camada
adicional à combinação
das
fórmulas de índice e correspondência e fórmula de
correspondência adicional. Nos dois últimos exemplos, analisamos retornar
apenas a linha número dois, a fórmula de índice para
obter os dados desejados. Neste exemplo,
vamos usar para combinar fórmulas para retornar os números de
linha e coluna. Isso nos permitirá retornar
uma gama mais ampla de dados com apenas um pouco mais de trabalho
inicial. Aqui vamos nós. Neste exemplo,
temos uma tabela de faturas para março que
está faltando os clientes, cidade, estado e CEP. Usaremos as informações
da tabela de pesquisa para
preencher nossa tabela de trabalho. Ao contrário dos outros
dois vídeos de exemplo, começaremos com uma matriz de
índices que inclui linhas e colunas, B3 a D6. A matriz de índice terá linhas
e colunas bloqueadas. Uma nota importante aqui,
ao longo desta fórmula, será muito importante
prestar atenção
aos tipos de referência das células. Algumas células terão as
linhas e colunas bloqueadas em algumas, só teremos
linhas ou colunas bloqueadas. Se o tipo de
referência de célula errado for usado, a fórmula não
retornará o que pretendemos. Em seguida, usaremos a fórmula da primeira partida para retornar
dinamicamente
o número da linha. O valor de pesquisa de
fórmulas de primeira correspondência será o cliente um dez. Esse valor de pesquisa
terá a coluna bloqueada, mas a linha dez aberta, a matriz de pesquisa
será A3 a A6 com linhas
e colunas bloqueadas. Finalmente, o tipo de correspondência será 0 porque queremos
uma correspondência exata. Para que a segunda fórmula de correspondência
retorne o número da coluna. O valor da pesquisa será
a cidade do cabeçalho da coluna. Na célula C9. Esta célula terá
a linha nove bloqueada, mas as colunas C abrirão. A matriz de pesquisa
será de B2 a D2, com as linhas
e as colunas bloqueadas. Por fim, o tipo de correspondência será novamente 0 porque
queremos uma correspondência exata. Vamos copiar essa fórmula para
o resto das células na coluna
da cidade e para as colunas de estado e
CEP também. Doce, tudo está
funcionando como esperado. Vamos fazer backup um pouco e falar sobre como essa
fórmula está funcionando. Começamos a
fórmula de correspondência de índice indicando o intervalo de células contendo os dados que queremos retornar
eventualmente. Uma nota rápida, queremos ter certeza que esse intervalo de
células inclui apenas dados que queremos
retornados e não inclui valores de pesquisa que
usaremos para obter os dados. Certo? Agora que temos
o intervalo de células contendo nossos dados de
retorno para encontrar, a fórmula de índice agora precisa número da linha e do número da coluna de
onde os dados são armazenados. Em vez de dar
números estáticos, como a linha um e a coluna dois, para retornar Ohio, usaremos fórmulas de
correspondência para
retornar esses valores dinamicamente. Não precisamos inserir as
coordenadas manualmente. A
fórmula da primeira partida retornará
dinamicamente
o número da linha. Damos a fórmula,
o valor da pesquisa, o cliente no
intervalo de células onde o
valor da pesquisa do cliente está contido. E ele retorna a linha
do valor de pesquisa do cliente. Como cada cliente
na tabela de pesquisa tem informações de cidade,
estado e código postal
correspondentes . Os dados que queremos, retornando o
número da linha de onde a fórmula de correspondência foi encontrada. O cliente também nos fornece o número da linha dos dados
que queremos devolver. A segunda
fórmula de correspondência retornará
dinamicamente
o número da coluna. Damos a fórmula,
o valor de pesquisa, o cabeçalho da coluna e o intervalo de células ao qual o
cabeçalho da coluna corresponde. E ele retornará a localização
do cabeçalho da coluna como um número dado ambas as coordenadas dos dois valores
de correspondência, a fórmula de índice agora é capaz retornar o valor da
célula que estávamos atrás. Isso foi um pouco
de informação para absorver, mas você a
alimentou. Como este é o último
vídeo da série, gostaria de
agradecer por assistir. Se você gosta deste curso,
por favor, tome um minuto para
conferir meus outros cursos. E, como sempre, se
você está se sentindo preso, sinta-se à vontade para parar
e fazer uma pergunta. Obrigado.
Jeremy Schilling, Microsoft Excel Expert
