Hack de vida do Excel para aqueles envolvidos em relatórios e processamento de dados

2024 Autor: Malcolm Clapton | [email protected]. Última modificação: 2024-01-13 02:44

Neste post, Renat Shagabutdinov, Diretor Geral Adjunto da Mann, Ivanov e Ferber Publishing House, compartilha alguns hacks legais da vida do Excel. Essas dicas serão úteis para qualquer pessoa envolvida em vários relatórios, processamento de dados e criação de apresentações.

Este artigo contém técnicas simples para simplificar seu trabalho no Excel. Eles são especialmente úteis para aqueles que estão envolvidos em relatórios de gestão, preparam vários relatórios analíticos com base em downloads de 1C e outros relatórios, formam apresentações e diagramas deles para a gestão. Não pretendo ser uma novidade absoluta - de uma forma ou de outra, essas técnicas provavelmente foram discutidas nos fóruns ou mencionadas em artigos.

Alternativas simples para VLOOKUP e HLOOKUP, caso os valores desejados não estejam na primeira coluna da tabela: LOOKUP, INDEX + SEARCH

As funções VLOOKUP e HLOOKUP funcionam apenas se os valores desejados estiverem na primeira coluna ou linha da tabela da qual você planeja obter dados.

Caso contrário, existem duas opções:

1. Use a função LOOKUP.

   Possui a seguinte sintaxe: LOOKUP (valor_procurado; vetor_procurado; vetor_resultado). Mas para que funcione corretamente, os valores do intervalo view_vector devem ser classificados em ordem crescente:

   

2. Use uma combinação das funções MATCH e INDEX.

   A função MATCH retorna o número ordinal de um elemento no array (com a sua ajuda você pode encontrar em qual linha da tabela está o elemento procurado), e a função INDEX retorna um elemento do array com um dado número (que nós descobriremos usando a função MATCH).

   

   Sintaxe da função:

   • SEARCH (search_value; search_array; match_type) - para o nosso caso, precisamos de um tipo de correspondência "correspondência exata", que corresponde ao número 0.

   • INDEX (matriz; número_linha; [número_coluna]). Nesse caso, você não precisa especificar o número da coluna, pois a matriz consiste em uma linha.

Como preencher rapidamente células vazias em uma lista

A tarefa é preencher as células da coluna com os valores no topo (de forma que o tópico fique em cada linha da tabela, e não apenas na primeira linha do bloco de livros sobre o assunto):

Selecione a coluna "Assunto", clique na faixa de opções no grupo "Home", o botão "Localizar e Selecionar" → "Selecione um grupo de células" → "Células em branco" e comece a inserir a fórmula (ou seja, coloque um igual) e consulte a célula na parte superior, simplesmente clicando na seta para cima no teclado. Depois disso, pressione Ctrl + Enter. Depois disso, você pode salvar os dados recebidos como valores, já que as fórmulas não são mais necessárias:

Como encontrar erros em uma fórmula

Cálculo de uma parte separada de uma fórmula

Para entender uma fórmula complexa (na qual outras funções são usadas como argumentos de função, ou seja, algumas funções são aninhadas em outras) ou para encontrar a fonte de erros nela, geralmente você precisa calcular parte dela. Existem duas maneiras fáceis:

1. Para calcular parte de uma fórmula diretamente na barra de fórmulas, selecione essa parte e pressione F9:

   

   Neste exemplo, houve um problema com a função SEARCH - os argumentos foram trocados nela. É importante lembrar que se você não cancelar o cálculo da parte da função e pressionar Enter, a parte calculada permanecerá um número.

2. Clique no botão Calcular Fórmula no grupo Fórmulas na faixa de opções:

   

   Na janela que aparece, você pode calcular a fórmula passo a passo e determinar em que estágio e em qual função ocorre um erro (se houver):

   

Como determinar do que uma fórmula depende ou se refere

Para determinar de quais células uma fórmula depende, no grupo Fórmulas da faixa de opções, clique no botão Células Afetantes:

Aparecem setas para indicar do que depende o resultado do cálculo.

Se o símbolo destacado na imagem em vermelho for exibido, a fórmula depende das células em outras planilhas ou em outros livros:

Ao clicar nele, podemos ver exatamente onde as células ou intervalos de influência estão localizados:

Ao lado do botão "Células Influenciadoras" está o botão "Células Dependentes", que funciona da mesma forma: exibe setas da célula ativa com uma fórmula para as células que dela dependem.

O botão "Remover setas", localizado no mesmo bloco, permite remover setas para células influentes, setas para células dependentes ou os dois tipos de setas ao mesmo tempo:

Como encontrar a soma (número, média) dos valores das células em várias planilhas

Digamos que você tenha várias planilhas do mesmo tipo com dados que deseja adicionar, contar ou processar de alguma outra maneira:

Para fazer isso, na célula em que você deseja ver o resultado, insira uma fórmula padrão, por exemplo SOMA (SOMA), e especifique o nome da primeira e da última planilha da lista das planilhas que você precisa processar em o argumento, separado por dois pontos:

Você receberá a soma das células com o endereço B3 das planilhas "Data1", "Data2", "Data3":

Este endereçamento funciona para folhas localizadas consistentemente … A sintaxe é a seguinte: = FUNCTION (first_list: last_list! Range reference).

Como construir frases de modelo automaticamente

Usando os princípios básicos de trabalho com texto no Excel e algumas funções simples, você pode preparar frases de modelo para relatórios. Vários princípios de trabalho com texto:

• Nós concatenamos o texto usando o sinal & (você pode substituí-lo pela função CONCATENAR, mas isso não faz muito sentido).
• O texto é sempre escrito entre aspas, as referências a células com texto estão sempre sem.
• Para obter o caractere de serviço "aspas", use a função CHAR com o argumento 32.

Um exemplo de criação de um modelo de frase usando fórmulas:

Resultado:

Neste caso, além da função CHAR (para exibir cotações), é usada a função IF, que permite alterar o texto dependendo se há uma tendência de vendas positiva, e a função TEXT, que permite exibir o número em qualquer formato. Sua sintaxe é descrita abaixo:

TEXTO (valor; formato)

O formato é especificado entre aspas, como se você estivesse inserindo um formato personalizado na janela Formatar células.

Textos mais complexos também podem ser automatizados. Na minha prática, havia a automação de comentários longos, mas rotineiros para relatórios gerenciais no formato “INDICADOR caiu / aumentou em XX em relação ao plano, principalmente devido ao crescimento / queda de FACTOR1 em XX, crescimento / queda de FACTOR2 em YY …”com uma lista de fatores variáveis. Se você escreve esses comentários com frequência e o processo de escrevê-los pode ser algoritmizado, vale a pena criar uma fórmula ou macro que lhe poupe pelo menos parte do trabalho.

Como armazenar dados em cada célula após a concatenação

Quando você mescla células, apenas um valor é retido. O Excel avisa sobre isso ao tentar mesclar células:

Da mesma forma, se você tivesse uma fórmula dependendo de cada célula, ela parará de funcionar após combiná-las (erro # N / A nas linhas 3-4 do exemplo):

Para mesclar células e ainda preservar os dados em cada uma delas (talvez você tenha uma fórmula como neste exemplo abstrato; talvez você queira mesclar células, mas manter todos os dados para o futuro ou ocultá-los intencionalmente), mescle todas as células na planilha, selecione-os e use o comando Pincel de formatação para transferir a formatação para as células que você precisa combinar:

Como construir um pivô a partir de várias fontes de dados

Se você precisar construir um pivô a partir de várias fontes de dados ao mesmo tempo, terá que adicionar o "Assistente de Tabela Dinâmica e Gráfico" à faixa de opções ou ao painel de acesso rápido, que tem essa opção.

Você pode fazer isso da seguinte forma: "Arquivo" → "Opções" → "Barra de ferramentas de acesso rápido" → "Todos os comandos" → "Assistente de tabela dinâmica e gráfico" → "Adicionar":

Depois disso, um ícone correspondente aparecerá na faixa de opções, clicando em que chama o mesmo assistente:

Quando você clica nele, uma caixa de diálogo aparece:

Nele, é necessário selecionar o item “Em diversos intervalos de consolidação” e clicar em “Avançar”. Na próxima etapa, você pode selecionar "Criar um campo de página" ou "Criar campos de página". Se você quiser criar de forma independente um nome para cada uma das fontes de dados, selecione o segundo item:

Na próxima janela, adicione todos os intervalos com base nos quais o pivô será construído e dê nomes a eles:

Depois disso, na última caixa de diálogo, especifique onde o relatório da tabela dinâmica será colocado - em uma folha existente ou nova:

O relatório da tabela dinâmica está pronto. No filtro "Página 1", você pode selecionar apenas uma das fontes de dados, se necessário:

Como calcular o número de ocorrências do texto A no texto B ("tarifa MTS SuperMTS" - duas ocorrências da abreviatura MTS)

Neste exemplo, a coluna A contém várias linhas de texto, e nossa tarefa é descobrir quantas vezes cada uma delas contém o texto de pesquisa localizado na célula E1:

Para resolver esse problema, você pode usar uma fórmula complexa que consiste nas seguintes funções:

1. DLSTR (LEN) - calcula o comprimento do texto, o único argumento é o texto. Exemplo: DLSTR ("máquina") = 6.
2. SUBSTITUTE - substitui um texto específico em uma string de texto por outro. Sintaxe: SUBSTITUTE (texto; antigo_texto; novo_texto). Exemplo: SUBSTITUTE (“carro”; “auto”; “”) = “móvel”.
3. UPPER - substitui todos os caracteres em uma string por maiúsculas. O único argumento é o texto. Exemplo: SUPERIOR (“máquina”) = “CARRO”. Precisamos dessa função para fazer pesquisas que não diferenciam maiúsculas de minúsculas. Afinal, SUPERIOR ("carro") = SUPERIOR ("Máquina")

Para encontrar a ocorrência de uma determinada sequência de texto em outra, você precisa excluir todas as suas ocorrências no original e comparar o comprimento da sequência resultante com o original:

DLSTR (“Tariff MTS Super MTS”) - DLSTR (“Tariff Super”) = 6

Em seguida, divida essa diferença pelo comprimento da string que procurávamos:

6 / DLSTR (“MTS”) = 2

São exatamente duas vezes que a linha "MTS" é incluída na original.

Resta escrever este algoritmo na linguagem das fórmulas (vamos denotar por “texto” o texto em que procuramos ocorrências, e por “procurado” - aquele cujo número de ocorrências nos interessa):

= (DLSTR (texto) -LSTR (SUBSTITUTE (UPPER (text); UPPER (search), ""))) / DLSTR (search)

Em nosso exemplo, a fórmula se parece com isto:

= (DLSTR (A2) -LSTR (SUBSTITUIR (SUPERIOR (A2), SUPERIOR ($ E $ 1), “”))) / DLSTR ($ E $ 1)