Conteúdo
No momento, o Microsoft Excel tem quase quinhentas funções de planilha disponíveis através da janela Function Wizard – botão fx na barra de fórmulas. Este é um conjunto muito decente, mas, no entanto, quase todos os usuários, mais cedo ou mais tarde, encontram uma situação em que essa lista não contém a função que ele precisa - simplesmente porque não está no Excel.
Até agora, a única maneira de resolver esse problema eram as macros, ou seja, escrever sua própria função definida pelo usuário (UDF = User Defined Function) em Visual Basic, o que requer habilidades de programação apropriadas e, às vezes, não é nada fácil. No entanto, com as atualizações mais recentes do Office 365, a situação mudou para melhor – uma função especial “wrapper” foi adicionada ao Excel LAMBDA. Com sua ajuda, a tarefa de criar suas próprias funções agora é resolvida com facilidade e beleza.
Vejamos o princípio de seu uso no exemplo a seguir.
Como você provavelmente sabe, o Excel tem várias funções de análise de data que permitem determinar o número do dia, mês, semana e ano de uma determinada data. Mas por algum motivo não existe uma função que determine o número do trimestre, o que muitas vezes também é necessário, certo? Vamos corrigir essa falha e criar com LAMBDA própria nova função para resolver este problema.
Etapa 1. Escreva a fórmula
Vamos começar com o fato de que manualmente, da maneira usual, escreveremos uma fórmula em uma célula da planilha que calcula o que precisamos. No caso do número do trimestre, isso pode ser feito, por exemplo, assim:
Etapa 2. Finalizando no LAMBDA e testando
Agora é hora de aplicar a nova função LAMBDA e envolver nossa fórmula nela. A sintaxe da função é a seguinte:
=LAMBDA(Variável1; Variável2; ... Variável N ; Expressão)
onde os nomes de uma ou mais variáveis são listados primeiro e o último argumento é sempre uma fórmula ou uma expressão calculada que os utiliza. Os nomes de variáveis não devem se parecer com endereços de células e não devem conter pontos.
No nosso caso, haverá apenas uma variável – a data para a qual calculamos o número do trimestre. Vamos chamar a variável para isso, digamos, d. Em seguida, envolvendo nossa fórmula em uma função LAMBDA e substituindo o endereço da célula original A2 por um nome de variável fictício, obtemos:
Observe que, após essa transformação, nossa fórmula (na verdade, correta!) começou a produzir um erro, porque agora a data original da célula A2 não é transferida para ela. Para teste e confiança, você pode passar argumentos para ele adicionando-os após a função LAMBDA em parênteses:
Etapa 3. Crie um nome
Agora para a parte fácil e divertida. Nós abrimos Gerente de Nomes aba Fórmula (Fórmulas — Gerenciador de Nomes) e crie um novo nome com o botão Crie (Criar). Invente e insira um nome para nossa função futura (por exemplo, Nomkvartala), e no campo Ligação (Referência) copie cuidadosamente da barra de fórmulas e cole nossa função LAMBDA, apenas sem o último argumento (A2):
Tudo. Após clicar em OK a função criada pode ser usada em qualquer célula em qualquer planilha desta pasta de trabalho:
Use em outros livros
LAMBDA e arrays dinâmicos
Funções personalizadas criadas com uma função LAMBDA suportam com sucesso o trabalho com novos arrays dinâmicos e suas funções (FILTRO, ÚNICO, GRADE) adicionado ao Microsoft Excel em 2020.
Digamos que queremos criar uma nova função definida pelo usuário que compare duas listas e retorne a diferença entre elas – os elementos da primeira lista que não estão na segunda. O trabalho da vida, não é? Anteriormente, para isso, eles usavam funções a la VPR (PROCV), ou Tabelas Dinâmicas ou consultas do Power Query. Agora você pode fazer com uma fórmula:
Na versão em inglês será:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Aqui a função COUNTIF conta o número de ocorrências de cada elemento da primeira lista na segunda, e depois a função FILTRO seleciona apenas aqueles que não tiveram essas ocorrências. Ao envolver esta estrutura em LAMBDA e criando um intervalo nomeado com base nele com um nome, por exemplo, PESQUISA DE DISTRIBUIÇÃO – obteremos uma função conveniente que retorna o resultado da comparação de duas listas na forma de um array dinâmico:
Se os dados de origem não forem tabelas comuns, mas “inteligentes”, nossa função também lidará sem problemas:
Outro exemplo é dividir texto dinamicamente convertendo-o em XML e depois analisando-o célula por célula usando a função FILTER.XML que analisamos recentemente. Para não reproduzir esta complexa fórmula manualmente todas as vezes, será mais fácil envolvê-la no LAMBDA e criar uma faixa dinâmica baseada nela, ou seja, uma nova função compacta e conveniente, nomeando-a, por exemplo, RAZDTEXT:
O primeiro argumento desta função será a célula com o texto fonte, e o segundo – o caractere separador, e retornará o resultado na forma de um array dinâmico horizontal. O código da função será o seguinte:
=LAMBDA(t;d; TRANSPOSE(FILTRO.XML(“
A lista de exemplos é interminável – em qualquer situação em que você precise inserir a mesma fórmula longa e complicada, a função LAMBDA tornará a vida visivelmente mais fácil.
Enumeração recursiva de caracteres
Todos os exemplos anteriores mostraram apenas um lado, o mais óbvio, da função LAMBDA – seu uso como um “empacotador” para envolver fórmulas longas e simplificar sua entrada. Na verdade, o LAMBDA tem outro lado, muito mais profundo, que o transforma em uma linguagem de programação quase completa.
O fato é que uma característica fundamentalmente importante das funções LAMBDA é a capacidade de implementá-las em recursão – lógica de cálculos, quando no processo de cálculo a função chama a si mesma. Pelo hábito, pode parecer assustador, mas na programação, a recursão é uma coisa comum. Mesmo em macros em Visual Basic, você pode implementá-lo e agora, como você pode ver, chegou ao Excel. Vamos tentar entender essa técnica com um exemplo prático.
Suponha que queremos criar uma função definida pelo usuário que remova todos os caracteres fornecidos do texto de origem. A utilidade de tal função, eu acho, você não precisa provar – seria muito conveniente limpar dados de entrada cheios com sua ajuda, certo?
No entanto, em comparação com os exemplos anteriores, não recursivos, duas dificuldades nos aguardam.
- Teremos que criar um nome para nossa função antes de começarmos a escrever seu código, pois nele, esse nome já será usado para chamar a própria função.
- Inserir uma função tão recursiva em uma célula e depurá-la especificando argumentos entre colchetes após LAMBDA (como fizemos anteriormente) não funcionará. Você terá que criar uma função imediatamente “do zero” em Gerente de Nomes (Gerenciador de Nomes).
Vamos chamar nossa função, digamos, CLEAN e gostaríamos que ela tivesse dois argumentos – o texto a ser limpo e a lista de caracteres excluídos como uma string de texto:
Vamos criar, como fizemos anteriormente, na guia Fórmula в Gerente de nome intervalo nomeado, nomeie-o CLEAR e digite no campo Variação seguinte construção:
=LAMBDA(t;d;SE(d=””;t;CLEAR(SUBSTITUIR(t;ESQUERDA(d);””);MID(d;2;255))))
Aqui a variável t é o texto original a ser apagado e d é a lista de caracteres a serem apagados.
Tudo funciona assim:
1 iteração
O fragmento SUBSTITUTE(t;LEFT(d);””), como você pode imaginar, substitui o primeiro caractere do caractere esquerdo do conjunto d a ser excluído no texto de origem t por uma string de texto vazia, ou seja, remove o “ UMA". Como resultado intermediário, obtemos:
Vsh zkz n 125 rublos.
2 iteração
Então a função chama a si mesma e como entrada (o primeiro argumento) recebe o que resta após a limpeza no passo anterior, e o segundo argumento é a string de caracteres excluídos começando não do primeiro, mas do segundo caractere, ou seja, “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” sem a inicial “A” – isso é feito pela função MID. Como antes, a função pega o primeiro caractere da esquerda dos restantes (B) e o substitui no texto dado a ele (Zkz n 125 rublos) por uma string vazia – obtemos como resultado intermediário:
125 reais.
3 iteração
A função chama a si mesma novamente, recebendo como primeiro argumento o que resta do texto a ser apagado na iteração anterior (Bsh zkz n 125 ru.), e como segundo argumento, o conjunto de caracteres excluídos truncados por mais um caractere para à esquerda, ou seja, “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, sem a inicial “B”. Em seguida, ele novamente pega o primeiro caractere da esquerda (B) deste conjunto e o remove do texto – obtemos:
sh zkz n 125 ru.
E assim por diante – espero que tenha entendido. A cada iteração, a lista de caracteres a serem removidos será truncada à esquerda e procuraremos e substituiremos o próximo caractere do conjunto por um void.
Quando todos os caracteres acabarem, precisaremos sair do loop – esse papel é apenas desempenhado pela função IF (E SE), em que nosso design está envolvido. Se não houver mais caracteres para excluir (d=””), a função não deve mais chamar a si mesma, mas deve simplesmente retornar o texto a ser limpo (variável t) em sua forma final.
Iteração recursiva de células
Da mesma forma, você pode implementar uma enumeração recursiva de células em um determinado intervalo. Suponha que queremos criar uma função lambda chamada LISTA DE SUBSTITUIÇÃO para substituição por atacado de fragmentos no texto de origem de acordo com uma determinada lista de referência. O resultado deve ficar assim:
Aqueles. em nossa função LISTA DE SUBSTITUIÇÃO haverá três argumentos:
- célula com texto a processar (endereço de origem)
- a primeira célula de uma coluna com valores para pesquisar na pesquisa
- a primeira célula da coluna com valores de substituição da pesquisa
A função deve ir de cima para baixo no diretório e substituir sequencialmente todas as opções da coluna da esquerda Para encontrar para as opções correspondentes da coluna da direita Substituto. Você pode implementar isso com a seguinte função lambda recursiva:
Shift down em cada iteração é implementado por uma função padrão do Excel DISPOSIÇÃO (DESLOCAMENTO), que neste caso tem três argumentos – o intervalo original, deslocamento de linha (1) e deslocamento de coluna (0).
Bem, assim que chegamos ao final do diretório (n = “”), devemos encerrar a recursão – paramos de nos chamar e exibimos o que foi acumulado após todas as substituições na variável de texto fonte t.
Isso é tudo. Sem macros complicadas ou consultas do Power Query – toda a tarefa é resolvida por uma função.
- Como usar as novas funções de matriz dinâmica do Excel: FILTER, SORT, UNIC
- Substituir e limpar texto com a função SUBSTITUTE
- Criando macros e funções definidas pelo usuário (UDFs) no VBA