Depois de anos lidando com planilhas complexas e confusas, descobri quatro funções do Excel que me economizam horas de trabalho por semana, automatizando tarefas rotineiras que a maioria das pessoas realiza manualmente. Essas funções são indispensáveis para quem trabalha com dados regularmente, seja um analista de dados profissional ou apenas um usuário casual que busca simplificar seu trabalho.
Links Rápidos
4. XLOOKUP: Pesquisa Avançada em Planilhas
XLOOKUP É uma função de pesquisa avançada em programas de planilhas, como o Microsoft Excel e o Planilhas Google, que vai além dos recursos das funções de pesquisa tradicionais, como PROCV و PROCHDisponibilidade. XLOOKUP Maior flexibilidade, tratamento de dados mais eficiente e redução de erros comuns associados a funções legadas. XLOOKUP Uma ferramenta essencial para analistas financeiros, cientistas de dados e qualquer pessoa que trabalhe com grandes volumes de dados e precise extrair informações específicas com rapidez e precisão. Ao usar XLOOKUPVocê pode pesquisar um valor em um intervalo específico e retornar um valor correspondente de outro intervalo, independentemente da localização das colunas ou linhas. Também suporta XLOOKUP Pesquisa da direita para a esquerda e de baixo para cima, o que a torna mais versátil do que outras funções.
Adeus VLOOKUP: XLOOKUP é a solução perfeita
Parei de usar o PROCV anos atrás, quando descobri o XLOOKUP. Enquanto o PROCV pesquisa apenas para a direita e trava ao mover colunas, o XLOOKUP funciona em qualquer direção e permanece flexível. O XLOOKUP é um dos Funções do Excel que podem economizar seu tempo Encontre dados específicos em suas planilhas.
Nos meus dados de preços de componentes de computador, preciso encontrar preços específicos de GPU com base nos modelos de produto. Com o VLOOKUP, eu precisaria reestruturar a tabela inteira. Mas com o XLOOKUP, tudo o que preciso fazer é digitar:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
O XLOOKUP pesquisa toda a coluna do produto, encontra minha GPU e retorna o preço correspondente. Não importa onde a coluna de preço esteja localizada e não trava se eu adicionar mais colunas posteriormente. Eu uso isso o tempo todo para referenciar informações do produto em diferentes planilhas sem precisar reformatar nada.
A fórmula básica para XLOOKUP é:
=XLOOKUP(valor_procurado, matriz_procurada, matriz_retorno)
- valor_pesquisa: O valor que você deseja pesquisar.
- lookup_array: O lugar onde você procura valor.
- return_array: A coluna ou linha que contém o valor que você deseja retornar.
Então, no meu caso, o valor que eu queria encontrar era “GIGABYTE GeForce RTX 3060 12GB Gaming OC”. Eu queria procurar esse valor na coluna C:C e retornar o valor correspondente de D:D na mesma linha onde a correspondência foi encontrada.
Outra coisa que gosto no XLOOKUP é que, se eu adicionar ",-1" ao final da fórmula, ele pesquisa de baixo para cima, permitindo que eu encontre automaticamente o preço mais recente. Isso me poupa de ter que classificar os dados manualmente sempre que atualizo minhas planilhas.
3. Usando minhas funções SUMIFS و COUNTIFS Em planilhas
Lidando com vários padrões profissionalmente
As funções básicas SOMA e CONT. SÃO suficientes para tarefas simples, mas não são suficientes para análises práticas. Quando preciso analisar meus dados de preços sob diversas condições, costumo usar as funções SOMASES e CONT. SÃO. Elas me permitem segmentar centenas de linhas com facilidade.
Digamos que eu queira contar o número de processadores AMD disponíveis na Amazon dos EUA. Em vez de filtrar manualmente, eu digito:
=CONT.SES(F:F, "Amazon EUA", K:K, "AMD")
Isso me mostra imediatamente que há 14 processadores AMD listados na Amazon no meu conjunto de dados. O interessante é que posso compilar quantos benchmarks precisar.
Para análise de preços, a função SOMASES funciona da mesma maneira. Para calcular o valor total de todos os processadores Intel atualmente em estoque, eu uso:
=SOMA.SE(D:D, K:K, "Intel", G:G, "Em estoque")
Isso adiciona todos os preços na coluna D, onde a marca é “Intel” e o status do estoque é “Em estoque”.
A sintaxe da função SOMASES é:
=SOMA.SES(intervalo_soma, intervalo_critérios1, critério1, intervalo_critérios2, critério2...)
- intervalo_soma: A coluna que você deseja somar.
- intervalo_de_critérios1: A primeira coluna para verificar as condições.
- critério1: Primeira condição de alcance.
- intervalo_de_critérios2, critérios2: Termos e condições adicionais (opcional).
A função COUNTIFS funciona de forma semelhante, exceto que ela conta as linhas correspondentes em vez de somar os valores:
=CONT.SES(intervalo_de_critérios1, critério1, intervalo_de_critérios2, critério2...)
Prefiro usar SOMASES e CONT.SE para relatórios rápidos porque elas atualizam novos dados instantaneamente, se encaixam perfeitamente nas minhas fórmulas existentes e me permitem manter tudo alinhado sem criar uma tabela dinâmica separada. Essas ferramentas permitem uma análise de dados precisa e eficiente, economizando tempo e esforço no desenvolvimento de relatórios complexos. Usar funções como SOMASES e CONT.SE é uma habilidade essencial para todo analista de dados que busca extrair insights valiosos dos dados de forma rápida e fácil.
2. Aparar e limpar: etapas essenciais para manter a aparência
Adeus à desordem de dados
Nada estraga uma planilha mais rápido do que dados desestruturados cheios de espaços extras e caracteres ocultos. Aprendi isso da maneira mais difícil, quando minhas pesquisas falhavam constantemente devido a espaços extras no final dos nomes dos formulários.
A função TRIM remove espaços extras do início e do fim do texto, bem como quaisquer espaços extras entre palavras. Quando importo dados de fontes diferentes, os nomes dos produtos geralmente vêm com espaços inconsistentes. Em vez de limpar manualmente cada célula, crio uma coluna auxiliar e uso:
=TRIM(C2)
Em seguida, movo o ponteiro do mouse para a borda da célula até que ele se transforme em um sinal de mais (+) e, então, arrasto-o para baixo até todas as linhas nas quais desejo que a função TRIM opere.
1. TEXTBEFORE e TEXTAFTER: Uma explicação detalhada e sua importância
Extraia com precisão os dados necessários
As funções TEXTBEFORE e TEXTBAFTER estão entre as minhas funções favoritas do Excel para organizar planilhas desorganizadas. As funções de texto modernas do Excel são excelentes para extrair informações específicas de sequências de texto não estruturadas. Por exemplo, minha coluna de preços tinha entradas como "$177.52", "178.33 USD", "₱9055" e "9645.50 PHP" misturadas.
A função TEXTBEFORE extrai tudo que precede um separador especificado:
=TEXTOANTES(D2, "USD")
Dessa forma, a função extraiu “178.33” de “178.33 USD” instantaneamente.
A função TEXTAFTER funciona ao contrário, extraindo tudo após o separador:
=TEXTAFTER(C2, "AMD ")
Dessa forma, extraí a função “Ryzen 5 5700X 8-Core AM4 Processor” de “AMD Ryzen 5 5700X 8-Core AM4 Processor”.
Para extrações complexas, combino as duas funções. Para obter o preço numérico de US$ 177.52:
=TEXTOANTES(TEXTODEPOIS(D8, "$"), "USD")
A sintaxe geral das funções TEXTBEFORE e TEXTAFTER é:
=TEXTOANTES(texto, delimitador) e =TEXTODEPOIS(texto, delimitador)
A grande melhoria que essas duas funções trazem reside na sua precisão. Em vez de usar combinações complexas das funções MID, FIND e LEN, consigo obter extrações limpas usando fórmulas simples e fáceis de ler. Uso essas funções com frequência para separar números de modelo, extrair especificações de produtos e extrair dados limpos de textos importados, o que costumava exigir horas de edição manual.
Essas quatro funções resolvem alguns dos maiores desperdícios de tempo no Excel, como encontrar dados usando pesquisas flexíveis, analisar com base em múltiplos critérios, limpar textos importados confusos e extrair informações específicas de sequências de texto complexas. A maioria das pessoas realiza essas tarefas manualmente, gastando horas no que normalmente levaria apenas alguns minutos para implementar as fórmulas corretas.
Você já usou essas funções para tudo, desde análise de preços de componentes até relatórios de gerenciamento de estoque. Elas funcionam independentemente do seu setor, pois dados confusos e requisitos de pesquisa complexos são problemas universais. Depois de dominar essas funções, você se perguntará como conseguia gerenciar planilhas sem elas.
