Transcrições
1. Introdução: Bem-vindo a esta
aula em datas e
horários no Microsoft Excel. Meu nome é Julian e tenho
trabalhado e ensinando excel nos últimos dez
anos como profissional de marketing digital. Compreender como o
Excel trata os valores de data e hora e como você
pode formatar, analisar e trabalhar com datas e horários
no Excel é, na minha opinião, uma das
coisas mais importantes a aprender para quem quiser
para trabalhar com o Excel. Isso ocorre porque datas
e horas são quase impossíveis de evitar
quando se trabalha no Excel. Se você está trabalhando com dados de
vendas ou um plano de projeto, por exemplo, você
estará trabalhando com datas ou horários de uma
forma ou de outra. Nesta aula, abordaremos os fundamentos de como
o Excel trata os valores de data
e hora, abordagens de formatação de
data e hora e melhores práticas, funções
populares de data e hora e como usá-las. Um exemplo, casos de uso e dicas para trabalhar com datas
e horas no Excel. No final desta aula, você poderá
trabalhar com datas e
horários no excel de forma mais eficiente, ter menos probabilidade de cometer erros ao trabalhar com datas
e horários e se destacar. E ser capaz de usar funções de data
e hora no Excel para uma análise mais
detalhada. Depois de terminar a aula, você poderá
testar o que
aprendeu com o projeto da classe. Obrigado por se inscrever nesta
aula e vamos começar.
2. Fundamentos das datas e horários no Excel: Nesta lição, vamos
começar analisando os fundamentos de trabalhar com datas e horários no Excel. Temos uma lista de
exemplos de datas,
datas e horas aqui. Temos primeiro de janeiro de 1901 de janeiro de 1903 horas
e, em seguida, mais abaixo, temos algumas
datas e horários mais recentes, dizendo que
temos 20 de fevereiro de
2022, por exemplo. Agora é importante entender
que o que vemos aqui, obviamente nossas datas e horários. Mas, abaixo de todos esses valores, excel tem um sistema de números de série que basicamente dá significado
a todas essas datas e horários e permite que cálculos sejam feitos ao trabalhar com datas
e vezes no Excel. Para deixar tudo isso um
pouco mais claro, o que vou fazer é
copiar essas datas e horários, copiá-los para
essas células aqui. Agora, obviamente, a formatação
dessas datas e horas está em um formato típico de data e hora. O que vou fazer é
destacar as células e depois vou mudar a
formatação muito geral. E você notará que
todas as datas e
horários agora
trocaram para um número. Agora, a razão pela qual incluí o
primeiro de janeiro e 1700 é porque no Excel é sistema de números de
série
para datas e horários. Isso realmente representa
o primeiro dia a partir do qual todas as datas e horas
são calculadas. Então, se formos apenas dizer três
horas no mesmo dia, obtemos 1,625 porque não
vai avançar o dia inteiro. Acabamos de avançar parte
de um dia. Se formos para 15
de janeiro de 1900, fomos 14 dias para
a frente desde o primeiro dia. Temos 15 e, obviamente, todos esses dias somam. Então, se olharmos
para datas e horários muito recentes, temos números muito maiores. Então, aqui está um exemplo muito
específico. Temos o 20
de fevereiro de 2020 a dez minutos após o
meio-dia e 15 segundos, e isso se transforma em um
número de série de 400461.5072, 12. Agora, tudo isso pode
parecer um pouco confuso e abstrato
para começar, mas vale a pena estar ciente
desse sistema de números de série que Excel tem subjacentes ou
datas e horas no Excel, porque é o que
todos os cálculos são baseados em quando você está
trabalhando com datas e horários, e é como as funções funcionam com
datas e horas no Excel. Então, vai
ficar muito mais
claro sobre o motivo pelo qual é
importante em lições futuras. Mas só para
começar, sim, essa é uma introdução muito básica em como Excel funciona com
datas e horários.
3. Como formatar datas e horários no Excel: Nesta lição,
veremos as diferentes
maneiras pelas quais você pode formatar datas
e horários no Excel. Temos data e hora
de décimo de janeiro de 202213 minutos e 42 segundos depois das dez horas da noite, escritas cinco vezes aqui. Agora o que vamos fazer é copiar essa data e
hora para essas células aqui. E vamos formatar cinco maneiras diferentes apenas de
executar alguns exemplos para você. Então, vou abrir o menu Formatar usando
o Control 1, o atalho do
teclado. E então vou
para a categoria de data. Vale a pena apontar, você pode alterar as configurações de localização. O meu está atualmente
definido para inglês, Reino Unido, mas vale a pena
estar ciente disso. Vou começar
mudando para este
primeiro aqui. O que você notará
é que o tempo desapareceu
e agora
acabamos de receber o décimo de janeiro de 2022. Mas é importante notar que o tempo ainda
permanece nesta célula. Ainda o temos aqui
na barra de fórmulas. Então, na verdade, esta ainda é
a mesma data e
hora desta. Mas como
mudamos a formatação para não mostrar a hora,
ela não está lá. Agora vou fazer
outro exemplo. Então, faremos isso aqui e iremos para a categoria de
data novamente. Vamos seguir
este segundo exemplo. Digite aqui. Selecione, OK? E desta vez temos o
mês escrito como texto, então é janeiro
em vez de ninguém. Novamente, o
valor subjacente real permanece o mesmo. Então, ainda temos esses dados de
tempo lá dentro, mas não estão sendo exibidos devido
à formatação
que escolhemos. Farei mais uma na
categoria de data. Apenas selecionaremos aquele com pontos em vez de barras
para frente. Novamente, o valor subjacente
permanece o mesmo, mas a formatação
mudou para que agora temos os dois pontos. Para esses dois últimos,
vamos dar
uma olhada na formatação personalizada. Então, vou controlar um
para abrir o menu. E então, na formatação personalizada, o que vou fazer
é, na verdade remover
os segundos aqui. Temos todas as
mesmas informações mostrando, mas não segundos. Você verá que o
segundo acabou de desaparecer. Mas, novamente, ainda há, eles ainda não
foram
excluídos dos dados em si. Então, para este último, vou alterar a ordem de data e hora e
para a formatação. Então vou cortar as horas, minutos e segundos,
e colá-los antes. Selecione. Certo? E você notará
que a hora é agora no início e a
data está no final. Então, todos esses parecem
bem diferentes. Mas, novamente, é importante
notar que eles não mudaram em termos
dos valores subjacentes. E podemos mostrar
isso novamente por if, alterando o
formato para geral. Então, obtemos o
número de série XL e você
notará , obviamente, que eles são
exatamente iguais. Só vou
voltar para mostrar esses exemplos de
formatação diferentes. E, no entanto, isso é apenas uma
rápida análise de como você pode formatar datas
e horas no Excel.
4. Funções de data e hora que você precisa saber: Nesta lição,
veremos uma variedade de funções de data e
hora. Então, temos uma data e
hora escritas aqui, no dia 20 de abril de
2021101342 segundos. As primeiras funções pelas quais
vamos executar são bastante simples
e autoexplicativas, então vou passar por
elas rapidamente. Mas o que eles vão fazer é extrair diferentes partes
da data e hora. Começaremos com o segundo. E como o nome sugere, isso extrairá os segundos, então nos dará 42. Movendo-se para minutos. Podemos usar isso e
ele extrairá 13. Então nosso isso nos dará
o 22 por dez horas. E agora passando
para a data. A função day
retorna o dia, então esse é o dia 20. E, em seguida, mês para uso
que receberá os quatro. E, por último, temos 2021. Essas são essas funções.
Agora, as próximas funções são um pouco mais complexas. Temos dia da semana agora o que
isso faz é basicamente atribuir um número para os
diferentes dias da semana, o que nos dirá em que dia da semana um determinado dia estava. Então, vamos mergulhar
em um exemplo. Então, selecionamos nossa data
como antes. E o tipo de retorno é um campo
opcional aqui, mas vale a pena passar para ele. Então, sabemos exatamente qual sistema de
numeração está sendo usado. Então, se formos com a opção um, semana começa em um domingo, modo que seria atribuído o
número um
até sábado, que seria
atribuído o número sete. Então segunda-feira seria a
terça seria três, etc. Vamos com a opção dois. A semana começa em uma segunda-feira
com este sistema de numeração. Dado o número um, até sete, que é domingo, como você pode ver, há várias opções
diferentes. Poderíamos até começar nossas semanas, dias de
semana com um 0 para segunda-feira. Mas, por enquanto,
vamos com a opção dois, aperte enter e
chegamos ao que isso
significa que é baseado no
sistema de numeração que escolhemos. Esta data é uma terça-feira, 20 de abril de 2021. Isso não foi tão terça. Agora, um fraco, isso
é bastante semelhante. Basicamente o que ele
faz, ele lhe dirá qual número semana por ano, uma semana, uma determinada data
cai dentro do ano. Por isso, funciona da
mesma forma que a função do dia da semana. Então, selecionamos a data
e, em seguida, podemos especificar o tipo de devolução para que possamos
começar nossas semanas em um domingo. Vamos com dois no entanto. Então, basicamente, a contagem
de semanas começa às segundas-feiras. Pressione Enter depois de
selecionarmos dois e obtemos 17. Então o que isso significa é que, com base no
sistema de numeração que escolhemos, essa data cai na 17ª, 17ª semana do ano. Agora vamos
olhar para o número da semana ISO agora, semanas
ISO, o certo
tipo de sistema de numeração. Eles
seguem a mesma regra naquela semana começa
em uma segunda-feira com a primeira semana de qualquer ano
tem que incluir uma quinta-feira. Agora, esse tipo de sistema de
numeração é comumente usado em empresas
e organizações. Portanto, é uma função útil para
lembrar e estar ciente. Portanto, não precisamos especificar
um tipo de retorno porque as regras já estão definidas
com base no sistema ISO. Então, vamos selecionar a data e
a hora
novamente e obtemos 16. Então, como você pode ver, embora a regra seja aquela semana
começando na segunda-feira, porque temos
essa regra adicional. A primeira semana do ano
tem que conter uma quinta-feira. Na verdade, obtemos 16
em vez de 17. Então, as duas últimas funções que
veremos quais estão nesta seção
separada aqui, ou o que é chamado de funções
voláteis. E o que isso significa
é que a saída
dessas funções pode mudar toda vez que a célula
é recalculada. Ou se você fosse
salvar sua pasta de trabalho, fechá-la e reabri-la, você receberá uma nova resposta se
o valor for alterado. Então, vamos começar
com a função now. E isso é bem simples. Basicamente, apenas
nos diz a data e a
hora exatas que temos agora. Então, uma vez que eu aperte Enter e você
notará que eu não faço
referência a nenhuma célula. Eu só tenho que fechar
a função
assim com colchetes abertos
e fechados . Depois de apertar Enter, recebo
o dia 3 de março de 202217, minutos e 50 segundos
depois das nove horas, que é
exatamente o momento em estou gravando
esta lição. Hoje funciona de forma
muito semelhante. Tudo o que faz diferente
é que não nos
dá o tempo real, apenas nos diz a data. Então, uma vez que apertamos Enter
lá e novamente, você não faz referência a nenhuma célula, basta fechar
o fechamento dos colchetes desse jeito. Clique em Enter e
teremos 3 de março de 2022. E você pode ver como
a função NOW acima recalculou novamente
porque é uma função volátil. Vou
entrar nisso de novo. Você pode ver como os
segundos mudarão. Então subimos três
segundos lá na última vez que entrei nele. Esse é um intervalo de funções de data
e hora. E nas próximas lições,
veremos alguns
dos casos de uso práticos e aplicações
dessas funções.
5. Como usar a função DE TEXTO com datas e horários: Nesta lição, vamos dar
uma olhada rápida
na função de texto. Agora, embora isso não seja uma função de
data ou hora, você pode usá-la ao trabalhar
com datas e horas. Portanto, é outra função útil para estar ciente e
ter em seu kit de ferramentas. Temos a data
e a hora aqui. 20 de abril de 202113 minutos e 42 segundos depois das dez horas. Agora, o que podemos fazer é usar a função de texto para converter um valor dois textos em um formato numérico
específico. Então o que vou fazer é fazer referência a essa
célula aqui, célula C3. E então vou usar a formatação
do ddd, que realmente
colocará essa data no formato
do dia nos formatos de data
abreviados. Então, vou apertar Enter
e chegaremos na terça. E isso porque
este dia aqui, 20, abril de
2021 é uma terça-feira. Se eu quisesse que isso mostrasse
a palavra completa terça-feira, posso adicionar mais um
d lá e o Excel
interpretará isso e nos
fornecerá a palavra completa terça-feira. Outra coisa que posso fazer, posso colocar o mês, para que eu possa fazer o MMM, que nos dará o mês
abreviado, abril. E, novamente, como antes,
posso adicionar mais um lá e recebemos o mês inteiro de
abril escrito assim. E outra coisa que eu
poderia fazer é ano ano, o que nos dará 21. Fiz três y's. Eu recebo o ano inteiro
de 2021 também. Podemos usar funções de data
e hora para fazer coisas semelhantes e também brincar
com a formatação. Mas vale a pena estar ciente
dessa função de texto também. Você pode vê-lo sendo usado em algumas pastas de trabalho que você
encontra para esse propósito. Vale a pena tê-lo em seu kit de ferramentas ao trabalhar com datas
e horários também.
6. Exemplo: insights com datas e horários: Nesta lição, vamos procurar
aplicar algumas das funções e técnicas que aprendemos até agora. Temos alguns dados de vendas aqui. São vendas em dólares e
funcionam para todo o ano de 2021. Então,
primeiro de janeiro de 2021, até
31 de dezembro de 2021. E vamos adicionar
alguns dados para mostrar as caxumba, semanas e dias
nessas colunas aqui. Então, para começar, faremos a coluna Mês, coluna C. E vou
usar as técnicas de reformatação. Vou copiar
todas essas células, depois colá-las aqui. Abra o
menu de formatação do controle um e vá para Personalizado. E eu quero que essas datas sejam exibidas no formato de mês de
texto abreviado. Então Jan, FEB, manhã, etc. Vou destacar
lá e apenas fazer MMM. Você verá que obtemos a amostra de Jan, que é o que eu quero. Selecione, Ok, e agora,
de relance, podemos
ver com mais facilidade em que mês esses números de
vendas se relacionam. Então, nós os
escrevemos em texto assim. Em seguida, vamos
preencher a coluna da semana. E para fazer isso,
vou usar a função entorpecimento da semana
ISO. Lá vamos nós. Eu disse semana núm, e então vou especificar
a data como
vir aqui, clicar em Enter, e obtemos todos os
nossos números da semana ISO agora preenchidos. Agora você notará que nos primeiros três dias
do ano de 2021, número da semana
ISO 53, eles realmente correspondem à
semana que atravessou dois mil e vinte e dois mil
e vinte e um. E nossa primeira
semana inteira nesse ano realmente começa
no dia 4 de janeiro. Por fim, vamos
entrar no que dia real
da semana cada uma dessas
datas se relaciona. Porque, no
momento, à primeira vista, não
sabemos se
é segunda ou
terça ou quarta, quinta, sexta,
sábado ou domingo. Isso vai nos
ajudar com isso. Então, vou usar a função de
texto para isso. Portanto, é igual a texto. Vou especificar
a data e
vou colocá-la no formato
abreviado. Então, o DDD aperte enter
e, em seguida, temos todos os nossos dias escritos
nesse formato abreviado. Então frito sentou Sandman, etc. Agora temos essa informação
extra. O que vamos fazer é apenas
trazer uma tabela dinâmica. As tabelas dinâmicas são um recurso
no Excel que
permite analisar dados rapidamente
e eles são realmente úteis. E para trazer uma tabela dinâmica de
dicas, precisamos ter certeza de que
estamos na guia Inserir. Portanto, este aqui
ao lado da guia Início, certifique-se de que estamos selecionados em
qualquer lugar dos dados. Em seguida, vou
selecionar Tabela Dinâmica, planilha
existente e, em seguida,
vou especificar o local. Vou apenas com a
célula H2, selecione. Certo? E então temos nossas opções de
tabela dinâmica chegando. Você pode ver que temos os campos de vendas, mês, dia da semana e
data todos lá. O que vou fazer é
descobrir qual dia
da semana normalmente
vê mais vendas. Vou arrastar este campo de dia para baixo
para a seção de linha. E então as vendas
que vou
colocar na seção de valor. Então temos todos
os nossos dados aqui. Vou clicar com o botão direito do mouse em
Classificar Maior para menor. Então, agora podemos ver que segunda-feira,
de alguma forma, é o dia que recebe mais vendas
em todo o 2021. O mais baixo é, na verdade, domingo. Portanto, isso é algo
que podemos fazer porque adicionamos essas informações para que possamos fazer
isso de
forma rápida e fácil. Agora temos essa
informação lá dentro. Se pudéssemos remover o dia e
pudermos adicionar o número da semana, por exemplo,
obteremos todos os nossos dados e dados detalhados pelos números
da semana. Então, da mesma forma,
também poderíamos fazer isso com o mês e tê-lo
mostrando rapidamente assim. Então Jan, FEB, ma, et cetera, todo o
caminho lá embaixo. Em termos de apenas poder ver rapidamente mais informações
sobre as datas. Como outra coisa realmente
útil
da aplicação dessas lições
que aprendemos até agora. E também em termos
de poder
analisá-lo rapidamente com tabelas dinâmicas
em um formato diferente. É outro benefício de ter esse conhecimento das funções de data e
hora no Excel.
7. Exemplo: temporizações de projetos: Nesta lição,
veremos algumas funções e fórmulas para
calcular o número de dias entre duas datas. Temos uma data de
início do projeto ou data início do projeto
fictício
de 4 de abril de 2022. Para o nosso projeto, temos um prazo de
19 de agosto de 2022. Então, há alguns meses
entre essas duas datas. Mas o que queremos fazer é
calcular
quantos dias há
entre essas duas datas. Então, o que vou fazer nesta
cela aqui, verei oito. Vou colocar iguais
e depois vou selecionar
a célula de prazo C5. Então, a partir disso, vou
tirar a data de início do
projeto, que é o quarto
de abril de 2022, acerte enter e obtemos 137 dias. Assim, podemos ver que
entre nossas duas datas, temos 137 dias agora, isso é bastante útil
para nós sabermos, nos
dá uma ideia de
quanto tempo temos. Mas há uma função
chamada Network Days, que vamos usar em seguida, que é ainda mais útil. E o que isso faz é basicamente fatores nos fins de semana
e deduz aqueles do número de dias úteis que você tem para lhe dar uma imagem mais realista de quanto tempo você
faria na verdade, tem. Se abrirmos a função
Network Days. Vou selecionar a data de
início primeiro desta vez. Em seguida, vou
selecionar a data de término. Em seguida, temos ambas
as datas selecionadas. Agora, você tem esse campo
opcional de feriados, o que faremos
no próximo exemplo. Mas, por enquanto, vou
apertar Enter e
temos 100 dias úteis agora. Então, agora já falamos nos fins de semana,
eles foram retirados, então temos uma imagem mais
realista de quanto
tempo de trabalho
temos entre esses dois dias. Agora, obviamente em
uma organização, as pessoas vão tirar férias. E para este próximo vamos usar
esse campo de feriados. Se eu abrir a função
Network Days, selecione a data de início, 4 de abril de 2020 até o momento. Assim como da última vez. Desta vez, vou fazer outra vírgula para passar para
a opção de feriados. E o que você
precisa fazer é selecionar as células onde suas
férias estão listadas, então você precisa delas
listadas assim. Vou destacar
todas essas células. Então, a célula B14
até B22 Acerte Enter. Agora recebemos 91 dias. Então, o que podemos ver
é
que temos o mesmo cálculo
acontecendo
do número de dias
menos os fins de semana, mas também fatoramos
nesses nove feriados. Então, passamos de 100
dias úteis para 91 dias úteis. Portanto, são três fórmulas e
funções realmente
úteis no Excel quando você está calculando quanto tempo você pode ter entre duas datas onde quer que seja para gerenciamento de
projetos ou
apenas organização geral. Outra função
a ser ciente é a função network days
dot INT L, que é efetivamente uma versão um
pouco mais avançada da função
de dias de rede. Então, vou
passar por um exemplo agora. E funciona muito parecido. Então, selecionamos a data de início, selecionamos a data de término. Mas também temos a opção especificar o tipo de fim de semana
que queremos ter. Hipoteticamente
falando,
digamos que temos funcionários
trabalhando aos sábados. E isso significa que podemos ir com
o fim de semana apenas de domingo. Como você pode ver, existem
várias opções diferentes. Você pode ter
fins de semana ou, digamos, segunda e terça ou
terça e quarta, que
afetará potencialmente o número de dias
úteis que você tem dependendo da data de
início do projeto e data de término do
projeto, o prazo, várias opções
diferentes aqui, mas vou usar o
domingo apenas como exemplo. Como antes, podemos
entrar nos feriados. Então, vou
entrar nos feriados, destacar todos esses
e, em seguida, clicar em Enter. E agora temos 110, o que obviamente é diferente da figura que
tínhamos quatro de 91. Também vale a pena estar ciente
desta função inAcol de
pontos de rede, o que lhe dá um pouco
mais flexibilidade ao calcular o número
de dias entre duas datas.
8. Exemplo: como rastrear um orçamento: Nesta lição, veremos a função de frack Year e vamos
aplicá-la a um exemplo específico que analisa o orçamento e orçamento para dar um exemplo melhor de
como ela pode ser usada. O que temos aqui é o
início do ano, que é o primeiro
de janeiro de 2022. E o que queremos fazer
é nesta célula aqui, coloque ontem, a
data de ontem. Agora, poderíamos inserir
isso manualmente, mas uma maneira mais rápida de
ter ontem lá dentro, supondo que você
queira que esta seja uma planilha que
você está atualizando regularmente é usar o
função hoje e depois
deduzir uma delas. Então, o que isso significa é que toda vez que
abrimos essa pasta de trabalho, essa célula será atualizada
automaticamente com a data correta para
ontem e
não precisaremos continuar
atualizando-a manualmente. Agora queremos calcular
a porcentagem do ano concluído. E para isso vamos
usar a função Year frack. Portanto, é bem simples. Precisamos selecionar
a data de início. Então, primeiro de janeiro de 2022. Em seguida, selecionamos a data de término, que é a
data de ontem que acabamos de colocar. Então, no próximo passo,
precisamos selecionar a base. Caso contrário, ele apenas
reverterá para a opção 0. E eu recomendo usar um. E o que isso faz é calcular a
fração do ano com base
no número real
de dias dividido pelo número real
de dias no ano. Existem outras
opções se você quiser passar 360 dias no ano ou 365. Mas ao usar esses fatores em coisas como
anos bissextos também, feche isso, pressione Enter. E podemos ver que estamos 16,2% do caminho ao longo do ano. Agora, em seguida,
veremos o orçamento de publicidade para 2022. Temos um
orçamento publicitário de US $80 mil. Agora, digamos que
verifiquemos nossas despesas para o aumento até o
final de ontem e gastamos US $17.542,34. Agora, para descobrir a
porcentagem de orçamento gasto, esta é apenas uma divisão simples, então vamos pegar esse valor
gasto e dividi-lo pelo orçamento de publicidade e
obtemos um valor de 21,9%. Instantaneamente. Você pode
ver que estamos 16,2% do caminho ao
longo do ano, mas já gastamos 21,9%. Portanto, sem fazer
mais cálculos, podemos ver que estamos andando à frente do orçamento. Mas o que podemos fazer é um cálculo
rápido aqui, apenas para nos dar uma porcentagem exata de
quanto somos uma cabeça. Vou selecionar a célula C8, depois dividi-la pela célula C4 para a porcentagem do ano completo e
, em seguida, fazer menos um. E agora isso nos
mostra que estavam 35,7% à frente do orçamento com
base em onde estamos no ano. Agora, se por exemplo, tivéssemos gasto apenas US$70 mil até agora, você verá que esse número
muda para menos 45,9%. Então, estamos muito
abaixo do orçamento. Portanto, essa ferramenta simples agora só permite que você
acompanhe seu orçamento. E sim, é um
ótimo exemplo de como a função Year frack no
Excel pode ser usada e como você pode achar útil ao calcular coisas diferentes para fazer com orçamentos e
colar, por exemplo.
9. Próximos passos: Obrigado por fazer esta aula e espero que você tenha achado útil. Deixe-me saber se você tiver alguma
dúvida e encontrar as instruções
do projeto de aula e ficar abaixo para testar
o que aprendeu. Você quer saber
mais sobre o Excel, confira minhas outras
aulas e
siga-me para atualizações sobre
minhas novas aulas. Mais uma vez, obrigado por participar da aula e espero
vê-lo na próxima.
Excel Classes, Excel teacher
