Preenchimento automático no Excel

O preenchimento automático no Excel é uma funcionalidade útil que permite preencher rapidamente células com dados baseados em padrões ou séries. 

Preenchimento de Série: O Excel pode prever automaticamente séries de números, datas, texto e outros tipos de dados com base em um padrão reconhecido. Por exemplo, se você digitar “3” em uma célula e “6” na próxima, então selecione essas duas células e arraste a alça de preenchimento (o pequeno quadrado no canto inferior direito da célula selecionada) para baixo, o Excel automaticamente preencherá as células seguintes com a série numérica “9”, “12”, “15” e assim por diante.

Exemplo: Arrastar para baixo a seleção ou dar dois cliques no canto inferior direito vai preencher automaticamente as células abaixo.

Resultado:

Atenção, porque às vezes o Excel erra, ou melhor, extrapola o prenchimento com um padrão diferente do esperado.

No caso abaixo, a intenção era preencher com “a”, “b”, “c”, “d”, e ele repetiu “a”, “b”, “a”, “b”.

O Preenchimento Relâmpago é outra funcionalidade inteligente. Digamos que eu tenha a tabela a seguir, e vou escrevendo a concatenação na terceira coluna (não funciona com fórmula, seria escrevendo mesmo).

Podemos disparar o preenchimento relâmpago selecionando o range a ser preenchido e utilizando CTRL + E, ou indo pelo menu Página Inicial -> Edição -> Preenchimento Relâmpago.

O resultado será o padrão preenchido para o range selecionado.

Como habilitar o solver no Excel

Como esta é uma dúvida frequente, destaco neste post.

Se o solver não estiver habilitado, ir em Arquivos -> Opções -> Suplementos

Ir… em Suplementos do Excel, e habilitar o Solver.

O Solver estará na aba Dados do Excel.

Crie fórmulas além da imaginação com a função Lambda

As versões mais atuais do Excel (o 365) possibilitam o uso do Lambda, o que transforma o Excel comum em praticamente uma linguagem de programação.

Exemplo 1. Um exemplo bem simples, com uma variável:

=LAMBDA(x; x^2)(5)

– Primeiro argumento: a variável (x)

– Segundo argumento: a fórmula (x^2)

– Terceiro argumento: o valor de x

O valor dá 25, que é o valor de x^2. Entretanto, para essa fórmula simples, não há ganho algum em utilizar o Lambda. Podemos fazer exemplos mais complexos.

Exemplo 2:

=LAMBDA(x; y; x*y)(5;3)

Agora estamos com duas variáveis (x;y), a fórmula é x*y, e os valores são x = 5 e y = 3.

A função fica bem mais divertida quando criamos um nome

Exemplo 3. Fórmula do volume de uma esfera.

Criamos um nome (VolEsfera), passando a função LAMBDA com a fórmula do volume, e com o parâmetro raio.

=LAMBDA(raio;4/3*PI()*raio^3)

Podemos chamar essa fórmula na planilha, como se fosse uma função comum qualquer do Excel.

Vamos fazer um último exemplo, mais complexo. Criar uma função para juntar intervalos.

Exemplo 4.

=LAMBDA(range1;range2;LET(a;range1;b;range2;r;SEQUÊNCIA(LINS(a)+LINS(b));c;SEQUÊNCIA(1;COLS(a));SE(r<=LINS(a);ÍNDICE(a;r;c);ÍNDICE(b;r-LINS(a);c))))

Para usar a função, basta passar os intervalos a serem juntados na fórmula.

=JuntarIntervalos(A2:B5;D2:E3)

Com o LAMBDA, é possível criar fórmulas extremamente poderosas, usando somente o Excel.

Vide arquivos em anexo.

Agradecimentos ao @Mauricio de Carvalho Cota pelos exemplos e pela dica.

Como fazer um gráfico empilhado?

Digamos que eu tenha a seguinte tabela.

Selecionando a tabela, ir em Inserir -> Gráficos.

Há várias opções. As principais são colunas agrupadas e colunas empilhadas.

A de colunas agrupadas, gera o gráfico a seguir.

A de colunas empilhadas, gera uma informação em cima da outra.

Planilhas bloqueadas pelo Windows

De vez em quando, baixamos algumas planilhas da internet (digamos, Github), e esta vem automaticamente bloqueada.

Quando confiamos na fonte que gerou a mesma, podemos fazer o seguinte:

– Clicar no arquivo com o botão direito do mouse, em propriedades

– Desbloquear o arquivo, conforme mostrado abaixo

É uma dica que pode ser útil em algum momento.

Cores complementares

Não é muito fácil escolher cores de formas harmônicas, mas conheci uma ferramenta que pode ajudar nisto.

Quando falamos de cores complementares, o Adobe Color Wheel é uma boa ferramenta para consulta. Tem o código RGB e hexadecimal, para uso nos nossos trabalhos.

https://color.adobe.com/pt/create/color-wheel

Link acima.

Tabela dinâmica com superpoderes

As tabelas dinâmicas se mostram como ótimas ferramentas para analisar informações e obter insights sobre uma determinada base de dados. Neste tutorial, vamos demonstrar como extrair mais valor deste recurso, realizando filtros e cálculos mais complexos do que simples médias, somas e contagem de valores.

Considere a seguinte base de dados fictícia:

Logo de cara, podemos identificamos que existem diversas células vazias. Mas quantos processos sem resultado nós temos, exatamente? Quantas unidades fabris existem para cada tipo de negócio? Como podemos determinar os quartis para o valor final de execução, também para cada tipo de negócio?

O primeiro passo é criar uma tabela dinâmica com esta base. E, para transformar a base num Modelo de Dados, selecionando a opção “Adicionar estes dados ao Modelo de Dados”:

Agora, clicando com o botão direito sobre “Intervalo” (que não estaria disponível caso a tabela não fosse adicionada ao Modelo de Dados) e em seguida, em “Adicionar Medida”:

Será aberta uma nova janela onde pode-se criar fórmulas personalizadas que nos possibilitam criar campos mais complexos para nossa tabela dinâmica.

Para determinar a contagem de valores faltantes na coluna “Resultado final para Klabin”, podemos utilizar a função “COUNTBLANK”, como mostrado a seguir:

Para determinar a quantidade de unidades fabris distintas, pode-se utilizar a função “DISTINCTCOUNT”, como na imagem abaixo:

Por fim, com objetivo de responder a última das perguntas que fizemos inicialmente, pode-se utilizar a função “PERCENTILE.INC” para determinar os percentis 25% e 75% (1º e 3º quartil, respectivamente) para os valores finais de execução:

Agora, com os campos personalizados já criados, podemos finalmente criar nossa tabela dinâmica (com os campos indicados a seguir), e obter facilmente as respostas para nossas perguntas:

Utilizando a técnica descrita, é possível ter a facilidade de uma tabela dinâmica com todo o poder do DAX.

Correlações no Excel

O Excel tem uma ferramenta de análise que ajuda a calcular correlações.

Primeiro, ir em Arquivo -> Opções do Excel -> Suplementos -> Suplementos do Excel

Marcar a opção “Ferramentas de Análise” e dar OK.

Digamos que eu tenha três colunas de dados como abaixo.

Ir em Dados -> Análise de Dados -> Correlação

A tela seguinte pedirá o intervalo de entrada dos dados, e outras opções como a indicação de dados nas linhas ou colunas.

O resultado: há correlação positiva entre Col 1 e Col 2, e negativa entre Col 2 e Col3

Essas correlações ficam bem evidentes se plotarmos os gráficos dos dados.

Fica a dica.

Dados de PDF para o Excel

 Trazer dados de PDF para o Excel é sempre complicado, porque a formação de uma tabela pode vir toda errada.

Para aumentar as chances de sucesso, utilize Dados -> De Arquivo -> De PDF

Vão aparecer algumas opções:

Resultado:

Veja também:

https://ideiasesquecidas.com/

Aprenda o travamento de células, um dos recursos mais úteis do Excel

O travamento de células no Excel é um recurso que permite bloquear a referência de uma célula em uma fórmula, para que ela não se altere ao arrastar a fórmula para outras células.

Isso pode ser útil quando se quer usar uma mesma célula como referência fixa em vários cálculos.

Para travar uma célula no Excel, você pode usar o símbolo $ na referência da célula dentro da fórmula.

• $A$1: trava a coluna e a linha (travamento completo)
• A$1: trava somente a linha (travamento de linha)
• $A1: trava somente a coluna (travamento de coluna)
• A1: não trava nada (referência relativa)

Vamos fazer 5 exemplos.

1) Não travar nada

Digamos que eu queira calcular o aumento para todos os itens da lista. Como o fazer?

Se eu escrever a fórmula =C8*C2, e arrastar, sem travar…

Vai dar um resultado errado, porque a fórmula vai deslocar ambas as células para baixo (de C8 * C2 para C9 * C3, para C10 * C4 e assim sucessivamente até C13 * C7)

2) Travamento completo

Ao invés de C8 * C2,

Fazemos C8 * $C$2 na fórmula em D8, e arrasto para demais células. Estou travando completamente a célula C2, para qualquer tipo de arraste da fórmula.

3) Trava somente a linha

Na célula E7, ao escrever a fórmula = D7 * E$6, estou travando apenas a linha. Portanto, se eu arrastar a célula para baixo, cada linha vai referenciar a linha 6 da coluna correspondente (no caso, E).

4) Trava somente a coluna

Na célula E7, ao escrever a fórmula = $D7 * E6, estou travando apenas a coluna. Portanto, se eu arrastar a célula para a direita, cada célula vai multiplicar a coluna D.

5) Travar linhas e colunas corretas

Qual será a fórmula a usar, para aplicar os percentuais de aumento nas colunas E, F, G aos itens da linha 7 a 12, conforme tabela abaixo?

 Acertou quem pensou = $D7 * E$6

Travo a coluna D (útil quando arrastar para direita)

E travo a linha 6 (útil quando arrastar para baixo)

Com isso, posso escrever essa fórmula na célula E7 e arrastar para a planilha toda!

Você também pode usar a tecla F4 com o cursor na referência a ser travada. Isso vai inserir os símbolos $ automaticamente. Você pode pressionar F4 várias vezes para alternar entre os tipos de travamento.

Bônus: Imagem inspirada numa noite estrelada, gerada pela IA Bing Create.

Veja também:

https://ideiasesquecidas.com/