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)

 

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:

 

Como fazer uma validação de dados que permita apenas números únicos

Como fazer uma validação de dados que permita apenas números únicos

Fala acadêmicos! Prontos para mais um treino?

Hoje iremos ver como criar uma validação de dados, que permita apenas números únicos

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

Em nossa planilha, temos uma coluna que serão alimentadas as notas fiscais.

Desejamos que seja preenchido, apenas números únicos, portanto, textos e números repetidos não serão aceitos.

Passo 1: Criar uma fórmula que conte se o número é único

Iremos utilizar a função Cont.se

Fórmula: =CONT.SE($C$3:C3;C3)

Passo 2: Criar uma fórmula que garanta que a informação digitada seja número.

Para isso iremos utilizar a função E, que garante que dois argumentos sejam verdadeiros dentro da função.

Antes da função cont.se, iremos inserir a função E.

O cont.se será o primeiro teste lógico da função, se essa contagem for igual a 1, significa que é a primeira vez que o número aparece.

O segundo teste lógico irá testar se a célula é um número, para isso iremos utilizar a função ÉNUM.

Podemos digitar ou buscar essa informação na guia Fórmulas, mais funções, informações, ÉNÚM

Abrirá uma janela onde devo digitar a célula que será testada, nesse caso a célula C3.

Feche o parênteses da função ÉNUM e da função E

Fórmula: =E(CONT.SE($C$3:C3;C3)=1;ÉNÚM(C3))

Para as células que possuem o número único e que realmente são números, são testadas como verdadeiro. As células com resultado falso, possui valor duplicado ou texto.

Selecione a coluna de notas fiscais,  copie a fórmula e cole na validação de dados.

Clique em Dados, validação de dados

Escolha o formato personalizado e cole a fórmula no espaço destinado.

Confirme clicando em ok.

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

 

 

PROCV ou ÍNDICE CORRESP? Saiba qual utilizar

PROCV ou ÍNDICE CORRESP? Saiba qual utilizar

Faaaala acadêmicos! Prontos para mais um treino?

Muitos usuários fiquem em dúvida quando escolher a função PROCV e quando escolher as funções ÍNDICE e CORRESP. Nesse post esclareceremos todas suas dúvidas 😉

 

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

Nossa planilha contêm os nomes dos vendedores e os valores de suas vendas.

Iremos iniciar utilizando a função PROCV. Temos alguns posts sobre essa função, caso não tenha visto ainda, clique abaixo:

A função PROCV realiza a procura na vertical.

Fórmula: =PROCV(G3;B3:C7;2;FALSO)

Função ÍNDICE+CORRESP

Não iremos detalhar a sintaxe de cada função, temos um post sobre isso:

Desejamos encontrar o valor da venda de acordo com o nome do vendedor digitado, porém o mês correspondente a pesquisa, queremos sempre alterar, não ser fixo.

A função PROCV por si só não consegue realizar, mas as funções ÍNDICE+CORRESP sim

Função ÍNDICE

Fórmula: =ÍNDICE(Tabela2[[JAN]:[DEZ]];5;12)

FUNÇÃO CORRESP

Fórmula: =CORRESP(B11;Tabela2[NOME];0)

O resultado 5, significa que a Rita está na linha 5.

Se alterarmos para Rodrigo, o resultado será 2:

Podemos utilizar a função CORRESP para colunas, nesse caso retornará a localização dos meses.

Fórmula: =CORRESP(C10;Tabela2[[#Cabeçalhos];[JAN]:[DEZ]];0)

Basta copiar as funções e substituir pelas linhas e colunas da ÍNDICE

Fórmula: =ÍNDICE(Tabela2[[JAN]:[DEZ]];CORRESP(B11;Tabela2[NOME];0);CORRESP(C10;Tabela2[[#Cabeçalhos];[JAN]:[DEZ]];0))

Considerações finais

Qual função devo utilizar?

Use a que mais tenha facilidade, mas se atente ao resultado final, muitas vezes somente uma função não irá te atender e terá que unir a outras funções.

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

Remover dados duplicados – um dos sete truques que poucas pessoas conhecem

Remover dados duplicados – um dos sete truques que poucas pessoas conhecem

Fala acadêmicos! Bora para mais um treino?

Remover dados duplicados, podem ser feitos de várias maneiras, nesse post trago uma delas

 

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

Nossa planilha contêm o ID, nomes, sobrenomes e vendas.

Desejamos saber se existem lançamentos duplicados.

Se eu clicar em Dados, remover duplicadas, ele exibe as colunas, mas não encontra nenhum dados duplicado.

Isso acontece por causa da coluna ID, pois todas as identificações estão diferentes.

O que fazer? Clique novamente na opção remover duplicadas e desmarque a coluna ID.

Mas desejamos que exiba somente uma vez o nome e sobrenome dos vendedores

Clique novamente na opção remover duplicadas e desmarque as colunas ID e Vendas.

Se quisermos remover os nomes duplicados, fazemos o mesmo processo

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?