Você sabe como criar funções no VBA para usar no Excel? Hoje vamos criar a função ÍNDICE CORRESP com dois ou mais critérios!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso Canal do YouTube para mais vídeos!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
Fala Impressionadores! Nessa aula eu vou te mostrar inicialmente como construir a função ÍNDICE e CORRESP no Excel e depois nós vamos partir para o VBA onde vamos aprender como criar funções no VBA!
Vamos utilizar a VBA Function, que nada mais é do que uma função dentro do VBA para criarmos uma função dentro do Excel.
Você pode criar diversas funções personalizas que o Excel não tenha para facilitar o seu trabalho no dia a dia!
E aí, vamos aprender a criar a função ÍNDICE e CORRESP com mais critérios dentro do VBA?
Função ÍNDICE e CORRESP no Excel, como funciona?
Essas funções se complementam no Excel, trazendo um resultado muito semelhante ao da função PROCV.
Existem algumas vantagens em usar o ÍNDICE e CORRESP principalmente em relação à posição do objeto procurado na planilha.
Antes de entrar no ambiente VBA vamos explicar como esta função funciona no Excel:
Nesta tabela temos diversas aulas de diversos cursos, aulas classificadas como Curta ou Longa.
Nosso objetivo é escrever o nome da aula e a tabela retornar se ela é Curta ou Longa de forma automática.
Função ÍNDICE
=índice(Argumento1; Argumento2)
1º Argumento – matriz -> onde temos a resposta que procuramos, neste caso o tamanho da aula -> coluna D.
2º Argumento – núm_linha = Qual o número da linha onde está a resposta?
No caso do 2º Argumento nós não temos essa resposta, porque primeiro precisamos encontrar o nome do curso, ver qual o número da linha onde está o nome e retornar o resultado na coluna ao lado, onde está a resposta -> se a aula é curta ou longa.
Parra isso vamos usar a função CORRESP, para gerar essa correspondência e completar o 2º argumento da função ÍNDICE.
Então no segundo argumento da função ÍNDICE vamos abrir a função CORRESP:
Função CORRESP
1º Argumento – valor procurado -> Neste caso estamos procurando o nome do curso, vamos selecionar a célula onde buscamos o nome.
2º Argumento – matriz_procurada -> Onde está o nome das aulas, ou seja, na coluna C.
3º Argumento – correspondência -> Vamos optar pela correspondência exata -> 0
Em resumo o ÍNDICE precisa do valor procurado e do número da linha onde está este valor, o CORRESP retorna justamente o número da linha como resposta, assim, uma função complementa a outra.
Se quiser uma explicação mais passo a passo sobre Função ÍNDICE e CORRESP no Excel, acesse o link da aula!
ÍNDICE-CORRESP com mais critérios!
Observe que a aula SOMASES que estamos procurando existe em duplicata na nossa tabela
Nossa função retornou à 1ª que encontrou, mas, e se quiséssemos a 2ª?
Neste caso temos que acrescentar critérios, a 1ª aula é do professor João e a 2ª do professor Alfredo, esses são critérios que podemos usar para diferenciar.
Vamos juntar o nome do professor com o nome da aula em uma coluna auxiliar
Feito isso, dê um duplo clique no canto inferior direito da célula para replicar para as demais células.
Agora podemos usar esta coluna auxiliar e obter um novo resultado.
Feito isso já podemos buscar o resultado considerando não somente a aula, mas também o professor.
Claro que para conseguir esse resultado nós tivemos que inventar uma coluna com os nomes concatenados e alterar a formulação, mas:
Será que podemos construir uma função específica no VBA?
Vamos apagar a coluna auxiliar e retornar a tabela inicial. Feito isso vamos usar o atalho Alt + F11 para entrar no ambiente VBA.
Se você estiver com o arquivo desta aula, vai ver que temos uma função pronta no VBA, esta função foi da aula anterior -> Como fazer PROCV no VBA.
Como a base é muito parecida com a que vamos construir hoje, vamos aproveitar a estrutura para fazer a nossa função indiceCorrespVBA().
A partir do momento que criamos esta Function, se você escrever o nome dela no Excel =indiceCorrespVBA vai perceber que a função já vai existir no Excel, você pode inclusive ir testando conforme a aula for evoluindo.
Agora precisamos descrever os argumentos da nossa função, por exemplo, como resposta essa função vai nos dar um texto? Um número? Qual o comportamento desta função?
Vamos começar acrescentando o As Variant e um nome para os argumentos:
Nomeando o critério e acrescentando o intervalo:
Feito isso, vamos verificar se cada célula daquela linha é igual ao nosso valor procurado, se não for, passamos para a próxima linha.
A estrutura que vamos usar é a For each:
Precisamos primeiro verificar se o valor analisado é igual ao valor procurado, para isso vamos usar a estrutura If.
Nesta estrutura se a função encontra o valor -> vai armazenar, responder Achei e sair da função, caso contrário, vai passar para o próximo indiceCorrespVBA e responder “Não encontrei esse valor procurado”.
Agora o que falta para completar a função?
Precisamos colocar mais um intervalo, teremos o intervalo de procura e o intervalo de resposta
Já definimos que o intervalo de procura é a coluna onde temos os nomes dos cursos certo?
O intervalo de resposta será a coluna onde temos o tamanho das aulas, se são curtas ou longas.
Nossa função está verificando em uma coluna cada célula até achar a que estamos procurando, quando encontrar a função traz como resposta o valor da célula ao lado como no PROCV.
Para o código entender qual célula da coluna de resposta deve ser mostrada teremos que criar um contador.
Contador
Imagine que queremos encontrar a aula CALCULATE, nossa função vai verificar na célula 1, na 2, e vai encontrar na 3.
Se ele encontrou na célula 3 da coluna de busca ele deve retornar como resposta a célula 3 da coluna de resposta.
Então o contado vai indicar o número da célula correta para a coluna de resposta.
Vamos então declarar a nossa variável -> Long (número muito grande), começando pelo valor 1 e recebendo cont + 1 a cada iteração.
Na imagem acima também acrescentamos um critério, teremos a coluna para procurar o valor que será -> intervalo_procura e a coluna para encontrar a resposta -> intervalo_resposta.
Agora que a contagem está funcionando e criamos o critério de intervalo_resposta, vamos ter que indicar para a função onde ela irá buscar a resposta.
Para isso, vamos acrescentar o novo critério:
Entre parênteses vamos colocar o const para especificar que critério queremos buscar.
Resultado:
Agora nossa função já está funcionando como o ÍNDICE CORRESP, nós indicamos o valor procurado, a coluna onde deve procurar e a coluna que deve retornar.
Se a função encontrar o valor do intervalo ela para de procurar de imediato, se não encontrar ela irá para o próximo Next e vai exibir a mensagem que não encontrou o valor.
ÍNDICE CORRESP + Critérios
Como vamos ter mais de um intervalo -> intervalo_procura1 e mais de um valor procurado -> valor_procurado1 vamos primeiro mudar a ordem dos critérios na nossa função, colocando para o final os critérios que podem receber mais de um valor.
Depois acrescentar o 1 nos critérios que estão no corpo da função, para não ter erros na execução:
Nossa função passará a ter critérios obrigatórios e critérios opcionais, que vamos acrescentar agora.
Atenção, o valor retornado não tem opção 2 de critério!
O valor retornado vem sempre da mesma coluna onde temos o resultado, portanto, não necessita de opção2.
No campo dos critérios, nós temos para avaliar a resposta, mais de uma possibilidade, um argumento obrigatório e um opcional.
Precisamos considerar esses critérios opcionais na nossa função, para isso vamos alterar a lógica que usamos.
Agora não basta encontrar o valor correto para o primeiro critério, após encontrar esse valor a função deve verificar se o critério2 também está sendo atendido, se estiver, então ok, se não, a procura continua.
Como fazemos isso?
Resultado:
Vamos utilizar a função IsMissing para avaliar se o segundo argumento foi ou não preenchido.
Então se o primeiro critério é verdadeiro -> a função verifica se o segundo critério foi preenchido -> FALSO, ou seja, o segundo argumento não foi inserido pelo usuário) -> Saio da função.
Ou
O primeiro critério é verdadeiro -> O segundo critério está preenchido -> a função passa a verificar se o segundo critério é verdadeiro, se sim -> saio da função.
Ou
Se o segundo critério não for verdadeiro, o valor do segundo critério foi colocado pelo usuário, mas não foi encontrado na tabela -> Next -> “Não encontrei esse valor procurado”.
Agora podemos usar a nossa nova função, com até 2 critérios sempre que precisarmos fazer este tipo de análise no Excel.
Seguindo esta mesma linha de raciocínio você pode acrescentar 3 ou mais critérios apenas copiando os blocos de códigos já feitos e adicionando mais critérios a sua função!
Na aula de hoje trouxe a explicação de diversos temas importantes sobre Como Criar Funções no VBA!
Falamos sobre como as funções ÍNDICE e CORRESP funcionam, como utilizar as variáveis, IsMissing, estrutura de repetição, teste lógico, etc.
Com este passo a passo você conseguirá criar no ambiente VBA uma função para seu uso que não existe no Excel!
Esta aula foi uma sugestão de um de nossos alunos, para aprender a criar funções que não existem no Excel dentro do VBA…
Espero que tenham gostado desta aula mais detalhada.
Aproveite a aula! Até a próxima!
Para acessar outras publicações de VBA, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador