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.
A estatística é parte importante da vida de um analista de dados.
Com ela, podemos fazer inferências e previsões com base em evidências, testar hipóteses, medir a confiabilidade e a precisão dos resultados, e comunicar informações de forma clara e objetiva.
Vamos utilizar duas ferramentas, o R e o Excel, para ilustrar conceitos básicos de estatística.
Dada uma série de dados, como a abaixo, no R podemos obter um sumário de informações com apenas alguns comandos.
É como se fosse o centro de gravidade da distribuição.
A mediana é menos sensível a outliers. Por exemplo, se a série acima fosse a renda das pessoas, e tivesse uma amostra a mais, o Bill Gates, a média iria subir enormemente, porém a mediana seria pouco ou nada afetada.
Primeiro e Terceiro Quartis
Se a mediana dividiu a série em duas, os quartis dividem em 4 pedaços.
Podemos usar a fórmula “=QUARTIL.INC(RANGE)”.
– O primeiro quartil é maior do que 25% da série.
– O segundo quartil é maior do que 50% da série – também é chamado de mediana.
– O terceiro quartil é maior do que 75% da série.
Há também a fórmula “=QUARTIL.EXC(RANGE)”. A diferença é que o QUARTIL.INC considera o ponto de 0% e 100% inclusos na lista, ao passo que o QUARTIL.EXC não considera.
Como são muito semelhantes, ficaremos com o primeiro, para fins práticos.
Desvio padrão
É dado por “=DESVPAD.A(RANGE)”. É uma medida de dispersão, mostra o quão “concentrados” ou “espalhados” os dados estão em relação à média.
Uma distribuição com desvio padrão pequeno vai estar próxima à média. Já um desvio padrão grande, vai estar mais distribuído, como no exemplo abaixo.
Há uma diferença entre “DESVPAD.A” e “DESVPAD.P”. A primeira é quando os dados são de uma amostra da população. O segundo, quando temos a população inteira do estudo.
No caso de uma variável aleatória normal, o intervalo entre (média – 1 desvio) e (média + 1 desvio) compreende 68% dos casos.
Uma boa recomendação é o livro a seguir, que explica conceitos de forma clara, sem fórmula alguma.
As funções SOMA.SES e CONT.SES são funções avançadas do Excel que permitem somar ou contar valores que atendem a critérios específicos em vários intervalos de dados.
Ambas são bastante úteis para a análise de dados.
Suponha que você tenha a tabela a seguir. Para cada carregamento de madeira que entrou na fábrica de papel, qual o período, a espécie e o volume carregado.
Queremos calcular o número de carregamentos por mês e espécie, e a soma de volume calculado por mês e espécie.
Como podemos proceder?
A função CONT.SES conta o número de células em um intervalo de dados que atendem a critérios específicos. A sintaxe da função é a seguinte:
Exemplo. No caso acima, o primeiro intervalo é o “Mês”, que vai C3 a C34.
O segundo intervalo é “Espécie”, que vai de D3 A D34.
A fórmula “=CONT.SES($C$3:$C$34; “JAN”; $D$3:$D$34; “Eucalipto”)” vai contar quantas ocorrências de eucalipto ocorreram em JAN, ou seja, vai resultar em 6.
Podemos aplicar a fórmula de modo sistemático.
Primeiro, criar uma tabela, com os meses nas linhas e as espécies nas colunas.
Na fómula, aplicar exatamente o CONT.SES acima, porém com duas diferenças: travando a coluna no primeiro critério, e a linha no segundo critério.
=CONT.SES($C$3:$C$34;$I3;$D$3:$D$34;J$2)
Dessa forma, podemos escrever a fórmula na célula “J3” e arrastar a mesma para preencher a tabela toda corretamente.
Outra alternativa é utilizar uma tabela dinâmica, também funciona bem.
O Excel fornece algumas ferramentas estatísticas para cálculos simples. Uma delas é a de correlação linear, que indica o quão correlacionadas estão duas séries (ou seja, o quanto o comportamento de uma influi na outra).
Digamos que eu tenha duas séries. Qual a correlação entre elas?
Para habililitar o suplemento de análise de dados, ir em Arquivo -> Mais… -> Opções
Depois, em Suplementos -> Suplementos do Excel
Habilitar “Ferramentas de Análise”
Em Dados -> Análise de dados, escolher correlação
Na caixinha que surgir, escolha o range de dados a analisar.
A correlação entre a Coluna 1 e a Coluna 2 é bem alta, de 0,999534
Isso fica bem evidente se plotarmos ambas as séries.
Sobre os tipos de correlação. Quanto mais próximo de 1, maior a correlação. Se for próximo de –1, é uma forte correlação negativa (o aumento de uma variável implica diminuição de outra). Se a correlação for próxima de 0, não há correlação linear (mas pode haver alguma relação não-linear entre variáveis).
Sabendo correlação entre séries, podemos criar modelos explicativos e de previsão de comportamentos, para basear nossas decisões.
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.
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.
Ferramentas em Excel-Vba 
Você precisa fazer login para comentar.