Vincular Excel com arquivo TXT

Vincular Excel com arquivo TXT

Fala acadêmicos! Bora para mais um treino?

No treino de hoje é especialmente para você que trabalha com sistemas em uma empresa e precisa exportar arquivos para o Excel. Muitos sistemas exibem os dados em formato TXT em um bloco de notas.

Clique aqui e inscreva no novo canal!

Desse modo ao alterar um dado do arquivo TXT, os dados do Excel serão atualizados.

Muitos usuários copiam os dados do bloco de notas e cola no Excel, erroneamente, pois essas informações ficam apenas em uma célula. Colando os dados utilizando o Ctrl+V, até funciona, mas sempre terá que atualizar.

Vinculando os dados

Clique na célula onde deseja que inicie as informações, exemplo: B2.

Na guia Dados, no grupo obter e transformar dados, clique na opção: De Text/CSV

Importante: como nosso exemplo o arquivo é de texto, iremos utilizar essa opção, caso precise vincular outro tipo de arquivo, clique na opção correspondente.

Abrirá uma janela onde se deve selecionar o arquivo a ser vinculado.

Selecione o arquivo e clique em importar.

Abrirá uma janela, que por padrão vem selecionado a origem do arquivo como 1252-Europeu Ocidental (Windows)

Esse tipo de formato desconfigura os textos. Iremos alterar clicando na setinha e escolha a opção de nenhum.

Delimitador: normalmente o Excel já reconhece o padrão do arquivo a ser exportado, mas caso não seja identificado, basta selecionar a opção desejada.

Caso não tenha o formato desejado, clique em personalizado e escolha o caracter que necessite.

Após a escolha, clique em carregar.

Ao lado será exibido o arquivo txt que está vinculado com a planilha.

Toda vez que precisar alterar o arquivo txt, salve as alterações e atualize a planilha, clica no menu Dados, Atualizar tudo.

Importante: Os arquivos devem estar salvos na mesma pasta e não pode ser alterado o nome do arquivo a ser exportado, em nosso exemplo o arquivo txt.

E aí, gostou do treino? Comente abaixo:

 

Utilizando o Somases para automatizar um fluxo de caixa

Utilizando o Somases para automatizar um fluxo de caixa

Fala acadêmicos, bora para mais um treino?

Ontem vimos como criar um fluxo de caixa diário e dinâmico a partir do mês e ano selecionado, se você ainda não viu esse post, clique no link abaixo:

E hoje iremos gerar o relatório, automatizando esse fluxo de caixa, utilizando a função Somases

 

Os dados a serem utilizados para gerar o fluxo de caixa diário, estão na guia de Entrada.

Iniciando a função

Na guia fluxo de caixa diário, na coluna de entrada, célula D4, inicie a função.

O primeiro argumento da função SOMASES é o intervalo da soma, coluna no qual estão as informações que irão somar de acordo com o critério que precisamos.

Como desejamos somar os valores de entrada, iremos clicar na guia de lançamentos e selecionar essa coluna.

Fórmula: =SOMASES(Tabela1[Valor]

O próximo argumento será o intervalo de critério, que será a coluna de data.

Fórmula: =SOMASES(Tabela1[Valor];Tabela1[Data]

O critério da data, está na guia fluxo de caixa diário, o dia que fizemos a função no post anterior.

Fórmula: =SOMASES(Tabela1[Valor];Tabela1[Data];’FC Diário’!B4

Agora precisamos informar para o Excel, que desejamos somar as entradas dessa data, nosso segundo intervalo de critério será a coluna onde está essa informação, se o valor será de Entrada ou Saída.

Fórmula: =SOMASES(Tabela1[Valor];Tabela1[Data];’FC Diário’!B4;Tabela1[Tipo]

E o critério2 será Entrada

Antes de pressionarmos a tecla enter, iremos iniciar os “travamentos” das células.

Vamos travar a célula B4, para que ela altere somente a coluna. A célula D3 para travar somente a linha e os títulos da tabela que está na guia de lançamentos.

Fórmula: =SOMASES(Tabela1[[Valor]:[Valor]];Tabela1[[Tipo]:[Tipo]];’FC Diário’!D$3;Tabela1[[Data]:[Data]];$B4)

Arraste a fórmula para baixo e para o lado, para calcular as demais entradas e saídas.

Calculando o Saldo Inicial

A princípio iremos referenciar a célula N8.

Calculando o Saldo Final

O saldo final será o valor inserido no saldo inicial, somando as entradas e subtraindo as saídas.

Fórmula: =C4+D4-E4

O saldo inicial do dia 02, será sempre o saldo final do dia anterior.

Fórmula: =F4

Arraste essa fórmula para baixo.

Agora precisamos fazer as mesmas fórmulas para a tabela ao lado. Copie a fórmula realizada nas Entradas.

Altere somente a referência B4 para H4 (que são os dias)

Fórmula: =SOMASES(Tabela1[[Valor]:[Valor]];Tabela1[[Tipo]:[Tipo]];’FC Diário’!J$3;Tabela1[[Data]:[Data]];$H4)

Repita o mesmo processo com a coluna de Saídas.

Fórmula: =SOMASES(Tabela1[[Valor]:[Valor]];Tabela1[[Tipo]:[Tipo]];’FC Diário’!K$3;Tabela1[[Data]:[Data]];$H4)

O saldo inicial do dia 16, será o saldo final do dia 15.

Fórmula: =F18

E o saldo final é a mesma, copie e cole, alterando as referências.

Fórmula: =I4+J4-K4

O saldo inicial do dia 17, será o saldo final do dia 16.

Fórmula: =L4

Precisamos corrigir a fórmula, quando ocorrer a mudança de meses, para acompanhar os dias.

Para isso, iremos utilizar a função SE

Fórmula: =SE(H17=””;””;L16)

Realizamos a função do dia 29, pois se a data acima estiver vazia, que retorne vazio, caso contrário retorne o saldo final doa dia 28, presente na célula L16.

Arraste a fórmula para baixo. Replique a mesma lógica para as demais colunas (entradas, saídas e saldo final).

Fórmula Entrada

Fórmula: =SE(H17=””;””;SOMASES(Tabela1[[Valor]:[Valor]];Tabela1[[Tipo]:[Tipo]];’FC Diário’!J$3;Tabela1[[Data]:[Data]];$H17))

Fórmula Saída

Fórmula: =SE(H17=””;””;SOMASES(Tabela1[[Valor]:[Valor]];Tabela1[[Tipo]:[Tipo]];’FC Diário’!K$3;Tabela1[[Data]:[Data]];$H17))

Fórmula Saldo Final

Fórmula: =SE(H17=””;””;I17+J17-K17)

 

Utilizando o Somases para automatizar um fluxo de caixa

Como fazer mágica utilizando as funções de Data

Fala galera! Bora para mais um treino?

Hoje iremos ver como as funções de data, lhe ajudarão a criar relatórios incríveis para sua planilhas!

Nós iremos aprender a criar um fluxo de caixa diário e ele será dinâmico, a partir do ano e mês selecionado.

Primeiro ponto a se observar é o formato dos dia da tabela, eles estão no formato geral.

Para facilitar o uso da função SOMASES onde iremos gerar os relatórios, iremos alterar esse formato de data.

Digite a  data 01/01/2019.

Altere esse formato, para que apareça somente os dias:

Na Página Inicial clique em mais formatos de número…

Escolha a opção Personalizado e digite dd, que corresponderá dois dígitos para os dias.

Arraste para baixo a formatação. Desejamos que esses dias se alterem de acordo com o mês escolhido, ao digitar fevereiro, ele se altere para as datas de fevereiro e assim por diante.

Para isso, iremos utilizar a função Data, referenciando o dia, mês e ano. Para que os meses sejam dinâmicos, precisamos inserir em algum lugar essa informação.

Digite em uma guia auxiliar uma tabela com os nomes dos meses e sua ID.

Vamos utilizar a função PROCV, para que ao escolher o mês desejado, traga essa ID automaticamente.

Fórmula: =PROCV(O5

O valor procurado será a célula O5, onde estão os meses.

Fórmula: =PROCV(O5;Tabela11;

A matriz tabela será a tabelinha que criamos nos dados auxiliares, onde estão os meses e sua ID.

Fórmula: =PROCV(O5;Tabela11;2;0)

O número índice coluna será o 2, onde estão as ID dos meses na matriz tabela e utilizaremos o número 0, para correspondência exata.

Para automatizar o resultado e ele retorne o dia, mês e ano exato, iremos utilizar a função DATA

Fórmula: =DATA(O4;O6;1)

Referenciamos o ano, mês e o dia digitamos 1.

Para os próximos dias, referencie a célula acima e some mais 1.

Fórmula: =B4+1

Arraste até o final da tabela, o dia 15. Repita esse processo para a tabela ao lado.

Fórmula: =B18+1

Repita o processo referenciando a célula acima para o restante da tabela.

Fórmula: =H4+1

Sabemos que os meses não tem a mesma quantidade de dias, alguns 30, outros 31 e fevereiro 28 ou 29 dependendo do ano.

Sendo assim, precisamos realizar uma fórmula que corrija e pare no último dia do mês correspondente.

Vamos utilizar a função Se e a função FIMMÊS

Fórmula: =SE(H16<FIMMÊS(H16;0);H16+1;””)

Se a célula acima for menor que o fim do mês dessa mesma célula, ela somará mais um dia, se não for, ela retornará vazia. Arraste essa fórmula até a linha 5.

Note que as células abaixo tivemos um erro, para isso iremos tratar com a função SEERRO

Fórmula: =SEERRO(SE(H4<FIMMÊS(H4;0);H4+1;””);””)

E aí, o que achou do treino de hoje? Comente abaixo:

 

 

 

 

Macete para arrastar fórmulas até determinada linha

Macete para arrastar fórmulas até determinada linha

Fala acadêmicos! Bora para mais um treino?

Hoje iremos ver como arrastar fórmulas até determinada linha.

 

Curtindo nosso conteúdo?
Que tal se aperfeiçoar fazendo parte de nosso time de alunos?💚
Saiba mais clicando nesse link

Temos uma planilha que está com uma fórmula na linha 11, coluna K no qual nomeamos de Status.

Desejamos arrastar essa fórmula, até a linha 10.000

Muitos usuários utilizam a alça de preenchimento e arrastam manualmente essa fórmula.

Passo 1: Utilize a caixa de nome.

Clique na caixa de nome e digite o endereço de uma célula, por exemplo: H10000

Passo 2: Digite um caracter na célula.

Desejamos arrastar fórmula da coluna Status, que está na coluna K, insira um caracter nessa célula, por exemplo: a letra X

Volte ao topo da planilha, pressione as teclas: Ctrl+seta para cima.

Passo 3: Arraste a fórmula.

Posicione o mouse na parte inferior esquerda da célula, o cursor irá alterar para uma cruz preta, dê dois cliques rápidos. A fórmula será arrastada até a linha 9999.

Resultado:

E aí, o que achou do treino de hoje? Comente abaixo:

 

Gráfico surpreendente: masculino x feminino

Gráfico surpreendente: masculino x feminino

Teste

Insira o seu endereço de e-mail abaixo para receber grátis as atualizações do blog!>

Fique tranquilo, seu e-mail está completamente SEGURO conosco!

Fala acadêmicos! Prontos para mais um treino?

Hoje iremos criar um gráfico personalizado

 

 

 

Curtindo nosso conteúdo?
Que tal se aperfeiçoar fazendo parte de nosso time de alunos?💚
Saiba mais clicando nesse link

Passo 1: Digite as informações abaixo.

Selecione as células acima e pressione as teclas: Ctrl+Shift+5

Digite esses dados para exemplo:

Formate a tabela como preferir

Passo 2: Crie um gráfico de colunas.

Selecione toda a tabela, clique na guia Inserir, gráfico de colunas e escolha a primeira opção.

Passo 3: Insira os ícones.

Iremos inserir os ícones de homem e mulher. Se você tem o office 365, esses ícones estão disponíveis.

Clique no menu Inserir, ilustrações, ícones.

Pesquise por pessoas e selecione os dois ícones.

Caso não tenha essa versão do office, pesquise na internet, temos como sugestão o site theounproject.com

Pesquise os ícones em inglês, são gratuitos!

Selecione as imagens e duplique, pode ser pressionando as teclas: Ctrl+D ou segurando a tecla Ctrl e arrastando.

Precisamos de um ícone que tenha apenas o contorno e esteja vazio e o outro que esteja preenchido.

Selecione os dois últimos ícones (clique em um, pressione a tecla Ctrl e após clique no outro). Na barra superior, clique em gráfico de formato, preenchimento de gráficos e escolha a opção: sem preenchimento.

Após, clique em contorno de gráficos e escolha a cor vermelha.

Clique somente no homem e escolha o contorno azul.

Altere a cor de preenchimento, mulher para vermelho, homem para azul.

Passo 4: Insira os ícones nos gráficos.

Copie o ícone do homem (Ctrl+C), clique duas vezes na primeira coluna e cole (Ctrl+V)

Faça o mesmo com o ícone da mulher.

Desejamos que os ícones fiquem do mesmo tamanho.

Clique nos ícones preenchidos, botão direito do mouse, formatar série de dados…

A opção que procuramos não está disponível com os dois ícones selecionados, dê dois cliques no primeiro ícone.

Em preenchimento, escolha a opção: empilhar e dimensionar com

Repita o mesmo para o ícone da mulher. Importante: a medida da unidade deve ser sempre 1, para que fique configurado.

Agora vamos unir os ícones. Clique fora do gráfico, após clique novamente no ícone preenchido.

Na última opção altere a sobreposição de séries para 100% e a largura do espaçamento para 5%.

Apague as linhas de grade (clique sobre ela e pressione a tecla delete). Após clique no eixo vertical e com o botão direito, escolha a opção: Formatar Eixo…

Em opções de eixo, altere o limite para mínimo: 0,0máximo: 1,0

Exclua o título, eixo vertical e legendas, após clique na área de plotagem e diminua os ícones.

Vamos inserir a porcentagem nos gráficos, utilizando caixa de texto.

Clique no gráfico, menu Inserir, texto, caixa de texto, clique e arraste próximo ao ícone do homem.

 

Referencie a porcentagem, clique na barra de fórmula, pressione o sinal de igualdade e clique no primeiro percentual masculino.

Resultado:

Duplique a caixa de texto (Ctrl+D) e referencie o feminino.

Selecione as duas caixas de texto, na Página Inicial formate o alinhamento, tamanho e negrite as fontes.

Vamos colocar o título do gráfico com forma de retângulo

Clique e arraste o retângulo, altere o preenchimento da forma para cinza.

Digite o texto: Percentual de clientes

Apague os ícones que inserimos, clique e pressione a tecla delete.

E aí, o que achou do treino de hoje? Comente abaixo:

 

Remover duplicatas com o Colar Transpor

Remover duplicatas com o Colar Transpor

Fala acadêmicos! Bora para mais um treino?

Sabemos que o recurso de remover duplicatas no Excel, pode ser realizado de diversas formas, vai depender muito da sua base de dados e do que deseja remover.

Hoje veremos, como remover esses dados, utilizando o recurso de colar transpor!

Curtindo nosso conteúdo?
Que tal se aperfeiçoar fazendo parte de nosso time de alunos?💚
Saiba mais clicando nesse link

Nossa planilha possui dados dos vendedores na vertical e desejamos que os dados fiquem na horizontal, porém que os nomes dos vendedores não devem se repetir.

Passo 1: Selecione sua base de dados e pressione as teclas Ctrl+C para copiar.

Passo 2: Clique na célula ao lado e pressione as teclas Ctrl+V para colar.

Passo 3: Remova os dados duplicados.

Selecione a coluna que colou os dados e pressione as teclas: Alt+S+M

Abrirá a janela exibindo os dados a serem removidos, estão na coluna D, clique em Ok.

Será exibida essa mensagem informando a quantidade de valores duplicados.

Passo 4: Copie os valores exclusivos (Ctrl+C)

Passo 5: Clique na célula onde os dados devam estar.

Cole pressionando as teclas: Atl+C+V+T

E aí, o que achou do treino de hoje? Comente abaixo:

DESAFIO EXCEL 7D

DASHBOARDS PROFISSIONAIS
EM 7 DIAS

OU SEU DINHEIRO DE VOLTA!

Crie sua conta :)

Na próxima tela você irá completar seu cadastro e transformar seu negócio.

Precisa de ajuda?
💬 Precisa de ajuda?
Olá 👋🏽
Como podemos te ajudar?