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”.

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.

Acesse o post Como Filtrar Valores Únicos no Excel, usando o recurso de filtro avançado, ao invés de fórmulas, para filtrar itens únicos em uma planilha.

Leia a seguir:

Como gerar números aleatórios no Excel

Como criar tabela dinâmica no Excel

Como inserir o mesmo texto em várias células do Excel

Como ordenar e filtrar colunas no Excel

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.

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.

Nesse tutorial vou mostrar como gerar números aleatórios com valor entre 0 e 1, números aleatórios entre dois valores diferentes de 0 e 1 e tabela de números aleatórios. Vou usar exemplos com números inteiros e datas.

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 Aleatórios

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.

Leia a seguir

Como Inserir Datas Sequencialmente no Excel

Como Gerar Dados Geográficos no Excel

Como Contar Dias Úteis Entre Datas no Excel

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 vou mostrar como inserir o mesmo texto em várias células do Excel, usando vários recursos do Excel. Se prefere o tutorial no formato de vídeo, assista ao vídeo no final da página.

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.

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.

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.

Assista o vídeo com o passo a passo de como inserir o mesmo texto em várias células do Excel, usando os recursos de arrastar soltar, tecla de atalho e preenchimento relâmpago.

Leia a seguir

Como gerar números aleatórios no Excel

Como filtrar e contar valores distintos no Excel

Como criar tabela dinâmica no Excel

Como gerar dados geográficos no Excel

Como obter valores de células indexadas no Excel

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:

Passo a passo para fazer o gráfico no Excel

1. Na planilha onde está a tabela com os dados vá até a aba Inserir.

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 não saber qual o melhor gráfico para representar os 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.

Observe que na janela 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.

Observe na figura acima 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.

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

Como Abrir Arquivo CSV no Excel

O arquivo texto separado por vírgula ou ponto e vírgula, normalmente conhecido como arquivo com extensão CSV é bem antigo, sendo usado desde o início da computação quando se começou a falar em armazenar dados em arquivo texto, ainda é usado com frequência nos dias de hoje. 

Este tipo de arquivo armazena informações no formato de tabelas, com os dados armazenados em linhas e colunas, onde cada coluna por ser separada por algum tipo de separador, como vírgula, ponto e vírgula ou Tab, sendo os mais comuns vírgula ou ponto e vírgula.

Podemos criar os arquivos CSV em qualquer editor de texto ou em uma planilha Excel. Alguns tipos de banco de dados exportam os dados neste formato. Atualmente tanto os bancos de dados com padrão SQL ou NOSQL podem exportar os dados neste formato. No caso do NOSQL existem algumas limitações devido à estrutura dos dados.

Apesar de podermos visualizar os dados de um arquivo CSV em qualquer editor de textos como por exemplo, o Notepad, o melhor é abrir em uma planilha Excel, para visualizar os dados no formato de linhas e colunas. 

Quando os dados são somente texto a importação do arquivo CSV para o Excel ocorre sem nenhum problema, mas se os dados forem por exemplo, numéricos, como um CPF, o Excel vai importar os dados como número e nesse caso teremos problemas na conversão dos dados.

Se você tiver por exemplo, uma lista com número de documento tipo CPF, os números que começarem com o dígito 0 vão ser exportados de forma incorreta para o Excel ou seja, os números que começam com 0 vão ser exportados sem o primeiro 0 por ser um número. Veja um exemplo:

Compare o CPF do Nome2 e do Nome8 exibido no Notepad e no Excel. No Notepad observe que o CPF inicia com o dígito 0 e no Excel em ambos os casos o CPF aparece sem o dígito 0.

Para evitar que esse problema aconteça, ao invés de abrir o arquivo CSV diretamente no Excel, devemos importar os dados como se fosse um banco de dados para uma planilha Excel e definir o tipo de dados de cada "coluna", no nosso caso seria a coluna do CPF.

Como importar arquivos CSV para o Excel do Office365

1. Com o Excel aberto, selecione a guia Dados, no grupo de comandos Obter e Transformar Dados e selecione o comando De Text/CSV.

2. Selecione o arquivo CSV desejado e clique no botão Transformar Dados.

3. Na tela Power Query Editor selecione a Column2 onde estão os dados com CPF, e altere o campo Tipo de Dados para Texto. 

Quando o Excel exibir a janela Alterar Tipo de Coluna, clique no botão Substituir atual.

4. Clique em Fechar e Carregar para importar o arquivo.

5. O Excel faz a importação dos dados com o CPF no formato correto.

A importação dos dados de um arquivo CSV para o Excel existe há muito tempo desde as versões mais antigas do Excel, mas no Office 365 a forma de definir o tipo dos dados por coluna ficou um pouco diferente, não tão intuitivo como era nas versões anteriores, mas com muito mais opções de configuração e customização dos dados.