Mostrando postagens com marcador Excel. Mostrar todas as postagens
Mostrando postagens com marcador Excel. Mostrar todas as postagens

29 de novembro de 2021

Como criar tabela dinâmica no Excel

A tabela dinâmica é uma tabela onde são exibidas informações organizadas de forma resumida e com cálculos realizados automaticamente, quando modificados dados em uma tabela. O Excel cria uma tabela dinâmica a partir de dados existentes em planilhas. 

Vamos ver como criar uma tabela dinâmica, juntamente com o respectivo gráfico dinâmico, que totaliza as vendas por funcionário, a partir de uma planilha com as informações de vendas.

Especificação da tabela dinâmica

A tabela de origem possui o nome da loja, dos funcionários e as vendas realizadas por cada funcionário. Existem duas tabelas dinâmicas, como exemplo na figura, a tabela 1, mostra a tabela dinâmica com a soma de vendas por funcionários e a tabela 2 mostra a tabela dinâmica com a soma de vendas por loja.


Antes de gerar a tabela dinâmica certifique-se de colocar um nome descritivo para cada coluna e verificar se não existem células vazias ou com dados duplicados. Essa verificação é importante para obter uma tabela dinâmica de fácil interpretação e com os valores dos cálculos corretos. Não queremos obter, por exemplo, uma contagem de valores que inclui uma célula que não possui dados.

A especificação de tabela que usamos é uma representação de dados em linhas e colunas. Na planilha do Excel nos referimos da mesma forma, os dados estão em linhas e colunas. Quando trabalhamos com tabela dinâmica, para facilitar o entendimento e o uso dos dados, vamos chamar cada coluna como “campo”.

No nosso exemplo as colunas Lojas, Funcionários e Vendas são os campos "Lojas", "Funcionários" e "Vendas".

Ao iniciar a criação da tabela dinâmica é mais fácil começar pelos campos de valores numéricos. No nosso exemplo, o campo de valores é “Vendas”.


Criando a tabela dinâmica

Abra a planilha Excel com a tabela de origem, vá até a guia “Inserir”, clique em “Tabela Dinâmica” e escolha “Da Tabela/Intervalo”.

O Excel abre a janela “Tabela Dinâmica da tabela ou intervalo”. 


No campo “Selecione uma tabela ou um intervalo” preencha com o intervalo onde estão os dados. A forma mais fácil é selecionar todas as células da tabela com os dados, para que o Excel preencha o campo com o intervalo correto dos dados.

No campo “Escolha onde você deseja colocar a tabela dinâmica” existem duas opções:

  • Nova Planilha: O Excel cria a tabela dinâmica em uma planilha nova, localizada à esquerda da planilha que contém a tabela com a origem dos dados.
  • Planilha Existente: O Excel cria a tabela dinâmica em uma planilha já existente na Pasta de Trabalho. Se selecionar esta opção, deve definir a célula da planilha para criar a tabela dinâmica, preenchendo o campo “Local”.

No nosso exemplo vamos selecionar a opção “Planilha Existente”. 

O Excel vai exibir na planilha uma área a partir da célula selecionada, com o espaço para os dados da tabela dinâmica e o painel “Campos da Tabela Dinâmica”, para definirmos o formato da nossa tabela dinâmica.


Vamos montar a tabela dinâmica para exibir a soma de vendas por funcionário. A forma mais fácil de começar é através de um campo numérico.

Normalmente os campos não numéricos são adicionados a “Linhas”, as hierarquias de data e hora são adicionadas a “Colunas” e os campos numéricos são adicionados a “Valores”.

No painel “Campos de Tabela Dinâmica” arraste o campo “Vendas” para a área do campo “Valores” e o campo “Funcionários” para a área do campo “Linhas”.


A tabela com a soma de vendas por funcionário fica dessa forma:


Ao invés de obter a soma das vendas podemos obter a contagem de vendas por funcionário, simplesmente alterando as configurações do campo de valor. 

No painel “Campos da Tabela Dinâmica” clique em “Soma de Vendas” e “Configurações do Campo de Valor”.

Na janela “Configurações do Campo de Valor”, na aba “Resumir campo de valor por” escolha “Contagem”.


Se quiser fazer a tabela dinâmica com a soma de vendas por loja ao invés de por funcionário, siga o mesmo procedimento anterior, só que ao invés de usar o campo “Funcionários” no momento de definir a tabela dinâmica, use o campo “Lojas”.

Para facilitar a visualização dos dados podemos criar um Gráfico Dinâmico a partir dos dados da tabela dinâmica criada.

Criando o gráfico dinâmico

Selecione qualquer célula dentro da tabela dinâmica para exibir a aba “Tabela Dinâmica Analyze” e clique em “Gráfico Dinâmico”.


Na janela “Inserir Gráfico” escolha o formato do gráfico que melhor representa seus dados. Para o nosso exemplo, vamos selecionar o gráfico de barras.


Resultado de nossa tabela dinâmica com o gráfico dinâmico na mesma planilha.


Atualizando os dados da tabela dinâmica

Uma vez que a tabela dinâmica está pronta, pode alterar os dados ou inserir novas informações na tabela de origem. Quando fizer qualquer tipo de alteração é necessário atualizar a tabela dinâmica, para visualizar as informações atualizadas.

Selecione qualquer célula dentro da tabela dinâmica para exibir a aba “Tabela Dinâmica Analyze” e clique em “Atualizar”.

No nosso exemplo optamos em criar a tabela e o gráfico dinâmico na mesma planilha, mas se a tabela de origem tiver uma quantia grande de dados é melhor criar a tabela dinâmica em uma nova planilha, para ter espaço e trabalhar com inserção de mais dados.

Assista ao vídeo com o passo a passo de como criar uma tabela dinâmica no Excel.

Leia a seguir

13 de maio de 2021

Como Gerar Dados Geográficos no Excel

No Excel você pode gerar uma tabela com dados geográficos usando a ferramenta de conversão de tipos de dados do próprio Excel. As informações são obtidas de uma fonte de dados online, dessa forma você vai obter dados atualizados, por exemplo, de área, fusos horários, latitude, longitude, população.

Veja neste post um exemplo de uso de dados geográficos, através da geração de uma tabela com algumas Localidades do Brasil, contendo os dados geográficos de cada Localidade. A única informação que você precisa inserir é o nome da Localidade, a partir daí podemos obter as demais informações geográficas.

Vou partir de uma tabela com o nome das Localidades e as colunas Área, Latitude, Longitude e População.


Gerando Dados Geográficos

Na planilha com a tabela de Localidades, selecione todas as Localidades, vá para a guia “Dados”, clique em “Geografia”, no grupo “Tipo de Dados”. Se o tipo de dados “Geografia” não estiver visível, clique na seta “Mais” para ver mais opções.


Após o Excel converter o nome das Localidades em tipo Geografia, você vai ver um ícone do lado esquerdo do nome da Localidade (1), e quando clicar na Localidade vai ver o botão “Adicionar Coluna” (2).

Com todas as Localidades selecionadas, clique no botão “Adicionar Coluna”, e selecione o dado que deseja inserir, por exemplo, Área. Como todas as Localidades estão selecionadas, o Excel preenche a informação em todas as Localidades. Para cada dado que deseja inserir, repita o mesmo procedimento. Veja o resultado da tabela formatada, com os dados preenchidos.

Como os dados são vinculados de fontes da internet, pode acontecer do Excel não conseguir obter todos os dados, como é o caso da Localidade de Minas Gerais, que não tem as informações de latitude e longitude. Isso pode acontecer por alguns motivos como, o campo referenciado ser protegido por atribuição, estar em um formato que o Excel não conseguiu ler ou o campo referenciado não se aplica ao tipo de dados. No caso da tabela, o Excel usou no momento da conversão os dados da Cidade de São Paulo, da Cidade do Rio de Janeiro e do Estado de Minas Gerais

A quantidade de dados disponíveis pode variar, de acordo com o dado de origem. Se utilizar por exemplo País, ao invés de Estado ou Cidade, o Excel exibe uma lista bem maior de dados disponíveis.


Além dos dados existem também imagens. Veja a imagem para a Cidade de São Paulo.


No momento de fazer a conversão do tipo de dados, o Excel faz uma busca na internet para procurar informações sobre o dado. Se você selecionar somente uma linha da Localidade, o Excel pode exibir a janela Seletor de Dados. Por exemplo, para a Localidade de São Paulo, o Excel vai exibir a janela “Seletor de Dados”, com opções referentes a palavra São Paulo. Você deve escolher qual dos resultados corresponde a sua pesquisa. No exemplo abaixo a diferença é que na primeira opção são as informações da Cidade de São Paulo, e na segunda são as informações do Estado de São Paulo.


Com a conversão de tipo de dados do Excel, fica fácil preencher dados geográficos de informações tipo cidade, estado ou país. Além do tipo de dados geográfico, o Excel também possui outros tipos de conversão de dados. Alguns estão disponíveis somente com a assinatura do Microsoft 365. Você pode ver todas as opções disponíveis, acessando a guia “Dados”, no grupo “Tipo de Dados”.

30 de março de 2021

Como Filtrar e Contar Valores Distintos no Excel

Apesar do Excel ser mais comum como uma planilha de cálculo, podemos também usá-lo para armazenar e obter informações estatísticas dos dados. Veja nesse tutorial como filtrar e contar o número de vezes que aparecem os nomes distintos de uma lista, com vários nomes repetidos.

Usando fórmulas do Excel você vai ver como filtrar os nomes distintos, ou seja, somente os nomes únicos, gerar uma lista com esses nomes e contar quantas vezes cada nome aparece na lista original. Vamos partir de uma lista com 19 nomes, sendo que vários deles aparecem mais de uma vez. 

A planilha inicial apresenta a lista de nomes com vários nomes repetidos. A partir dessa lista vamos filtrar e contar os nomes distintos.

Filtrando Itens Distintos

Para filtrar os itens distintos digite a seguinte fórmula na célula “B2” da barra de fórmulas, e pressione ENTER.

=SEERRO(ÍNDICE($A$2:$A$20;CORRESP(0;CONT.SE($B$1:B1;$A$2:$A$20);0));"")

A célula “B2” exibe o primeiro nome distinto. Selecione a célula “B2”, clique no quadrado verde no canto inferior direito da célula e arraste até a célula “B20”.


Você vai obter a lista de nomes distintos na coluna "B" da planilha.

Contando Itens Distintos

Para contar os itens distintos digite a seguinte fórmula na célula “C2” da barra de fórmulas, e pressione ENTER.

=SOMARPRODUTO(CONT.SE($A$2:$A$20;B2))

A célula “C2” exibe o número de vezes que aparece o nome “Adriana”. Selecione a célula “C2”, clique no quadrado verde no canto inferior direito da célula e arraste até a célula “C20”.


Você vai obter a contagem de cada nome distinto na coluna "B" da planilha.

Veja o resultado completo do filtro e contagem dos nomes distintos a partir de uma lista de nomes. A coluna “A” possui a lista de nomes de origem, a coluna “B” a lista de nomes distintos, e a coluna “C” a contagem do número de vezes que o nome da coluna “B” aparece na coluna ‘A”.


Usando fórmulas você pode facilmente filtrar e contar itens distintos de listas armazenadas no Excel.

19 de março de 2021

Como Traduzir Funções do Excel

O Excel possui várias fórmulas para executar cálculos, automatizar planilhas, tratar dados e muitos outros recursos que o tornam uma ferramenta útil para armazenar e tratar dados.

Existem muitas fórmulas com diferentes graus de dificuldade, e quando precisamos usar alguma fórmula, nem sempre localizamos em português. Na internet existem muitos tutoriais sobre o Excel, com exemplos utilizando as fórmulas com seus nomes originais em inglês.

Se você estiver usando o Excel em português vai precisar do nome das funções nas fórmulas em português. Para resolver esse problema a Microsoft desenvolveu um suplemento chamado Tradutor de Funções, que traduz o nome das funções de um idioma para outro.

Nesse tutorial você vai aprender como instalar, configurar e usar o tradutor de funções do Excel.

Como Instalar o Tradutor de Funções

Para instalar o Tradutor de Funções abra o Excel, vá até a guia "Inserir", selecione "Suplementos" e depois "Obter Suplementos".


Na janela "Suplementos do Office" escolha "Produtividade" no painel do lado esquerdo, e depois clique no botão "Adicionar" do item "Functions Translator".


O Excel instala o suplemento que vai aparecer na guia "Página Inicial". 

Como Configurar o Tradutor de Funções

Para configurar os idiomas no Tradutor de Funções vá até a guia "Página Inicial", selecione "Tradutor" no grupo "Tradutor de Funções".


No painel Tradutor de Funções selecione o ícone de configurações localizado no rodapé do painel.


No painel de "Preferências" você pode configurar o idioma de origem, e depois para qual idioma deseja traduzir. Para traduzir de inglês para português selecione "Inglês" na lista suspensa "De" e "Português" na lista suspensa "Para".


Como Usar o Tradutor de Funções

Para usar o Tradutor de Funções vá até a guia "Página Inicial", selecione "Tradutor" no grupo "Tradutor de Funções".
  1. No painel "Tradutor de Funções" abra a guia "Tradutor".
  2. Copie ou digite a fórmula que deseja traduzir no primeiro campo.
  3. Clique no botão com a seta para baixo para fazer a tradução. 
  4. Observe no segundo campo a fórmula traduzida.


Agora é só usar a fórmula traduzida em sua planilha.

No painel Tradutor de Funções além da guia "Tradutor" tem a guia "Referência" e "Dicionário".

A guia "Referência" exibe as funções por categoria. Você pode selecionar a categoria desejada e obter uma listagem com as funções nos dois idiomas configurados, no caso inglês e português. 

Para acessar a lista de categorias vá até a aba "Referência" no painel "Tradutor de Funções", selecione a categoria desejada na lista suspensa "Categoria da Função". Veja logo abaixo a listagem das funções da categoria selecionada, nos dois idiomas.


A guia "Dicionário" faz a busca da função a partir de qualquer parte do nome da função. 

Para acessar o Dicionário vá até a aba "Dicionário", digite a função a ser pesquisada no campo "Nome da função". No exemplo abaixo, buscamos a função CONCATENATE. Veja logo abaixo a função localizada. A busca da função pode ser realizada em ambos os idiomas definido na configuração do Tradutor de Funções.


Com o suplemento Tradutor de Funções fica fácil localizar e traduzir as funções no Excel.

9 de março de 2021

Como Gerar Números Aleatórios no Excel

A Planilha Excel possui várias funções para gerar números aleatórios. Existem funções para gerar números simples, números entre uma faixa de valores e até mesmo tabelas. Com pequenas alterações de formatação dos números aleatórios é possível obter conjuntos de dados de vários tipos como datas, porcentagens, valores financeiros. Esses conjuntos de dados com números aleatórios podem ser usados, por exemplo, em testes de fórmulas e gráficos.

Gerar Números Aleatórios

A função "ALEATÓRIO()" gera números aleatórios com valores maiores e igual a 0 e menores do que 1. Para usar a função digite na barra de fórmulas: 

=ALEATÓRIO()

Observe que o Excel gera um número com o valor entre 0 e 1.


Você pode exibir os números em vários formatos como por exemplo, moeda ou porcentagem. Para formatar o número selecione o grupo "Células", na guia "Página Inicial", e clique em "Formatar". No menu drop-down selecione "Formatar Células".


Na janela "Formatar Células" selecione "Moeda" na opção Categoria, e na opção "Números Negativos" selecione o segundo item e clique em "Ok".


A célula apresenta o valor R$0,19. Se selecionar a Categoria "Porcentagem" a célula apresentará o valor 18,90%.

Gerar Números Aleatórios Entre Dois Valores

A função "ALEATÓRIOENTRE(inferior, superior)" permite gerar números aleatórios e definir quais os limites inferior e superior do número. 

Para gerar um número entre 1 e 1000 digite na barra de fórmulas:

=ALEATÓRIOENTRE(1;1000)

onde, o número 1 é o limite inferior e o número 1000 o limite superior.


Com essa mesma função é possível criar também datas aleatórias, já que o Excel usa número para armazenar os valores de data.

Para gerar uma data entre 05/01/2021 e 20/02/2021 digite na barra de fórmulas:

=ALEATÓRIOENTRE(DATA(2021;1;5);DATA(2021;2;20))

Observe que o Excel gera um número e não uma data.


Para exibir a data é necessário fazer a formatação do número.  Para formatar o número selecione o grupo "Células", na guia "Página Inicial" e clique em "Formatar". No menu drop-down selecione "Formatar Células".


Na janela "Formatar Células" selecione "Data" na opção Categoria, e na opção "Tipo" selecione a formatação da data desejada e clique em "Ok".


 A célula apresenta a data no formato selecionado.


Gerar Tabela de Números Aleatorios

A função "MATRIZALEATÓRIA([linhas];[colunas];[min];[max];[inteiro])" cria uma tabela de números aleatórios. Essa função possui vários parâmetros opcionais para customizar os valores gerados na matriz. Os parâmetros são:
  • linhas: número de linhas da tabela.
  • colunas: número de colunas da tabela.
  • min: valor mínimo do número gerado.
  • max: valor máximo do número gerado.
  • inteiro: booleano que define se o número deve ser inteiro ou ponto flutuante.
Para gerar uma tabela com 3 linhas por 5 colunas, com valores entre 1 e 500 inteiros, digite na barra de fórmulas:

=MATRIZALEATÓRIA(3;5;1;500;VERDADEIRO)


Em todos os exemplos com geração de números aleatórios observe que os valores dos números mudam quando altera algum valor na planilha ou salva a planilha. Esse é o comportamento quando o cálculo automático está habilitado, o que é o padrão nas planilhas Excel.

Para alterar o comportamento do cálculo de uma planilha selecione o grupo "Cálculo", na guia "Fórmulas" e a opção "Manual".


Mas lembre-se se a planilha estiver configurada para fazer os cálculos de forma "Manual", é necessário clicar em "Calcular Agora" (1) sempre que quiser fazer algum cálculo na planilha.

Com as funções "ALEATÓRIO", "ALEATORIOENTRE" e "MATRIZALEATÓRIA" podemos criar conjuntos de dados com valores fictícios bem variados, que nos ajuda a aprender a fazer gráficos ou trabalhar com formatação.

Para ver como gerar números aleatórios no Planilhas Google ao invés do Excel, acesse o tutorial Como Gerar Números Aleatórios no Planilhas Google.

13 de agosto de 2020

Como Inserir o Mesmo Texto em Várias Células do Excel

Existem algumas situações, em que precisamos preencher uma planilha do Excel com alguns dados repetidos, ao invés de digitar várias vezes a mesma informação, podemos usar o recurso de arrastar e soltar, usar algumas teclas do teclado conhecido como teclas de atalho ou o comando de Preenchimento Relâmpago do Excel.

Neste post você vai ver como inserir o mesmo texto em várias células do Excel, usando vários recursos do Excel.

Inserir o mesmo texto usando o recurso arrastar e soltar

O recurso de arrastar e soltar acredito que é o mais conhecido no Excel. Para inserir o mesmo texto em várias células do Excel, usando o recurso de arrastar e soltar:
  • Digite o texto desejado na célula da planilha;
  • Selecione a célula e arraste o símbolo +, que aparece no canto inferior esquerdo da célula selecionada, até a célula que deseja preencher com o mesmo texto. Por exemplo, para inserir a palavra "blog" em 10 células, digite a palavra "blog" na primeira célula;
  • Arraste o texto até a última célula.
Arrastar e soltar texto na célula do Excel

Inserir o mesmo texto usando o recurso de tecla de atalho

O recurso de usar tecla de atalho, acho bem útil no caso em que não queremos usar o mousepad do notebook ou um mouse. Para inserir o mesmo texto em várias células do Excel, usando o recurso de tecla de atalho:
  • Selecione todas as células que deseja preencher com o mesmo texto. Como exemplo vou usar a mesma palavra "blog" do método anterior;
  • Digite a palavra "blog", na primeira célula;
  • Tecle CTRL + ENTER.
Preencher texto na célula do Excel

Inserir o mesmo texto usando o recurso de preenchimento relâmpago

O recurso de Preenchimento Relâmpago do Excel é útil em situações em que você já possui uma planilha, com alguns dados preenchidos, e precisa preencher uma coluna com informações repetidas ou seguindo um padrão, por exemplo, sequencial. A diferença desse formato de preenchimento é que ele só funciona se a planilha já possuir dados preenchidos, em pelo menos uma coluna, para que o Excel use de exemplo, para fazer o preenchimento da coluna nova.

Para mostrar como usar o preenchimento relâmpago, vou usar uma planilha com uma coluna de números sequenciais. Na próxima coluna vou preencher com a palavra “blog”, todas as 10 linhas numeradas.


Para preencher todas as linhas usando o recurso Preenchimento Relâmpago do Excel, selecione a célula com a palavra “blog”, vá para a guia “Dados”, clique no comando “Preenchimento Relâmpago”, no grupo “Ferramenta de Dados”.


O Excel preenche todas as 10 linhas da coluna B com a palavra “blog”.



Com estes recursos, você tem a opção de escolher dentre vários recursos, para inserir o mesmo texto em várias células do Excel, o mais fácil e adequado para sua planilha, facilitando dessa forma o preenchimento de dados.

Leia a seguir


23 de junho de 2020

Como Fazer Gráfico no Excel



O Excel é um aplicativo de produtividade muito usado para armazenar, organizar e manipular dados em uma planilha. Dependendo dos dados fica difícil analisar a informação, principalmente quando queremos analisar por exemplo, tendências ao longo do tempo, comparar valores, mostrar proporções ou distribuição de dados. Nesses casos a melhor forma de análise é através da representação dos dados por um gráfico. 

Veja nesse post como fazer gráfico no Excel a partir de uma tabela com informações de número de visualizações de uma página web exibidas por mês.

Vou usar a tabela abaixo como base para o gráfico:

Tabela mês versus visualizações

Passo a passo para fazer o gráfico no Excel

1. Na planilha onde está a tabela selecione a aba Inserir.

Aba Inserir do Excel

No grupo de comandos Gráficos existem vários comandos para fazer um gráfico, cada comando é representado por um ícone indicando qual o tipo de gráfico. Tem gráfico de colunas, de linhas, de pizza, de dispersão, de radar, de combinação e dinâmico. Para facilitar a geração do gráfico tem o comando Gráficos Recomendados, que é útil no caso de você não saber qual o melhor gráfico para representar os seus dados. A escolha do gráfico deve ser baseada no tipo de dados que você quer exibir. Para a tabela acima vou usar o comando Gráficos Recomendados.

2. Com todos os dados da tabela selecionados escolha o comando Gráficos Recomendados na aba Inserir.

Inserir gráfico

Observe que na tela Inserir Gráficos o Excel exibe as opções de gráficos para o tipo de dados selecionados. Para a tabela acima vou escolher o gráfico de linhas porque quero exibir a tendência das visualizações ao longo do tempo.

3. Selecione a opção gráfico de linhas que é a primeira opção dos gráficos e o botão Ok.

Gráfico de linha

Observe que o estilo do gráfico está diferente do exibido na tela Inserir Gráfico. Após inserir o gráfico optei em colocar um estilo diferente do padrão. Você pode mudar o layout, estilo, dados, tipo e local do gráfico através da aba Design do Gráfico.

Para exibir a aba Design do Gráfico simplesmente selecione o gráfico e escolha o estilo preferido.

design do gráfico

O seu gráfico está pronto. Agora você pode personalizar a aparência do gráfico ou testar outras opções de gráficos para ver qual representa melhor os dados.