Como criar um calendário dinamicamente em mudança no Excel

neste artigo, vamos mostrar-lhe como criar um calendário dinamicamente em mudança no Excel.

Download Workbook

Populate variables

First step is creating the variables to be used in the formulas. Estas variáveis precisam fornecer a informação atual para que o calendário possa atualizar automaticamente. Aqui está a lista de variáveis:

• Presente data
• O número de presentes dia do mês
• é O número do mês atual
• ano
• A data do primeiro dia do mês atual
• O número de presentes dia da semana
• O número da presente semana do ano

Vamos usar o HOJE, DIA, MÊS, ANO, DATA, dia da SEMANA, e WEEKNUM funções. Resumidamente, enquanto hoje a função retorna a data do dia, Dia, Mês, Ano, Dia de semana e weeknum funções analisam os respectivos valores de data. A função DATE devolve o valor da data do ano, mês e dia indicados.

na imagem a seguir, você pode ver os nomes que demos e as fórmulas que foram geradas em 20 de novembro de 2020.

we named each variable to make our job easy when creating formulas. O próximo passo para criar um calendário no Excel é criar um esboço para a interface do calendário.

contorno para o calendário no Excel

Antes de criar as fórmulas reais que geram os dias do mês, primeiro precisamos colocar um contorno para ajudar a criar fórmulas e também fornecer referência visual.

os dias serão colocados numa tabela de 7 colunas e 6 linhas. Precisamos aumentar estes números em 2 para títulos e células auxiliares também. Enquanto os títulos da coluna e linha incluem dias de semana para colunas e números da semana por linhas, as células auxiliares serão números consecutivos a partir de 1.

o calendário pode parecer o seguinte.

Use borders or background colors as you like. O ponto importante é usar um número de semana dinâmica no título. Consulte a função WEEKNUM para a primeira semana, e adicione 1 para cada semana consecutiva.

Vamos continuar a criar um calendário no Excel por preencher os números de dias.

dias geradores

se estiver a utilizar um layout semelhante, e não quiser preocupar-se com os detalhes da fórmula, sinta-se à vontade para copiar a fórmula abaixo. Em seguida, colá-lo na célula superior-esquerda na área do dia do seu calendário, e preencher para as células restantes. As células G2 e E4 referem-se às primeiras células das colunas auxiliares. Assim, você pode precisar ajustar estas referências, a menos que a primeira célula seja G4.

Agora, vamos dar uma olhada mais de perto a fórmula. Primeiro, a fórmula usa a função DATA, que retorna uma data pelo ano, mês e dia dado. Os dois primeiros argumentos são fornecidos pelas variáveis número de anos e número de meses que já foram calculados.

a parte diária da fórmula tem como objetivo gerar números de dia em uma base de 7, uma vez que há 7 dias em uma semana. 7 * ($E4-1) parte da fórmula verificar o número da semana na coluna auxiliar e, e gera uma base, começando com 0 e aumentando por 7 de cada vez.

Podemos aumentar ou diminuir este número, tanto quanto a diferença entre o dia da semana do primeiro dia do mês por dia da semana número. Em outras palavras, estamos recebendo o primeiro dia do mês presente como ponto de âncora. Por exemplo, é o sétimo dia da semana (de segunda a base de domingo) para 1 de novembro de 2020.Se a semana do nosso calendário assumir que a segunda-feira é o primeiro dia da semana, a segunda-feira deve ser 6 dias antes (=1-7) do primeiro dia do mês. Assim, temos números como abaixo.

Adicionar esses valores 1 para mudar os números. Assim, o domingo pode tornar-se o primeiro dia como no seguinte exemplo.

Como resultado, temos 2 matrizes para 2 dimensões. Para criar uma tabela, precisamos adicionar ambas as matrizes juntas.

estes números representam o dia do mês presente. Há números inferiores a 1 ou superiores a 31, O que obviamente não pode ser Dias do mês.

a função DATA lida com estes tipos de números gerando uma data no mês anterior ou no próximo. Por exemplo, -2 torna-se dois dias antes do primeiro dia do mês dado.

finalmente, se você usar estes números na função de data, você pode obter as datas reais do mês atual.

formatação

números de Dia

agora, vamos povoar os dias. Você pode alterar a formatação dos valores sem alterar o valor real. Este recurso é chamado de formatação de Números. Siga os passos para ajustar a formatação do número para mostrar apenas os dias.

1. Selecionar quaisquer das datas, no dia em área
2. Pressione as teclas Ctrl + 1 para abrir o diálogo Formatar Células
3. Activar separador Número, se já não estiver aberta
4. Selecione personalizar na lista Categoria
5. Digite a letra d na caixa Tipo
6. Clique em OK para salvar
   

Agora, o layout de calendário é feito.

se você quiser remover o preenchimento para datas de um único dígito, você pode usar dd em vez de um único D. Para saber mais sobre a formatação de números: formatação de números no Excel – tudo o que você precisa saber

realçando o dia atual

finalmente, vamos usar a formatação condicional para definir diferentes cores para os dias que não estão no mês presente, e destacar o dia presente.

para adicionar estas opções de formatação condicional;

1. Escolha o dia de intervalo
2. Clique em Formatação Condicional ícone na guia página Inicial da faixa de opções
3. Selecione Nova Regra
   
4. Selecione Usar uma fórmula para determinar quais células devem ser formatadas
5. Inserir uma fórmula para definir a regra para destacar o dia atual
   =G4=HOJE()
   G4: Célula superior esquerda do intervalo inclui dias. Use uma referência relativa (não use $).
6. carregue no botão Formato na janela de antevisão da janela de formatação das células
   
7. na janela, seleccione as opções de formatação que deseja ver na célula actual
   usámos um fundo laranja neste exemplo.
8. Clique em OK para aplicar
   
9. caixa de pré-Visualização irá mostrar as suas preferências
10. Clique em OK para aplicar a formatação condicional
    

Esta é a forma como ele se parece:

A fórmula retorna um valor Booleano. Se o valor da célula (G4) for igual ao resultado da função de hoje, que devolve o dia presente num formato de data, a fórmula devolve verdadeiro. Caso contrário, falso. Se o resultado for verdadeiro, o Excel aplica a formatação na célula.Há dois pontos importantes aqui.:

1. você precisa usar uma referência relativa, por exemplo, G4, a menos que você não queira que o Excel preencha a referência ao longo da Gama. Considere como as fórmulas mudam quando você as copia em outro lugar. O Excel aplica o mesmo para fórmulas de formatação condicional também. Para mais informações, consulte: Como criar uma referência absoluta do Excel e uma referência relativa
2. as células do dia devem retornar os valores reais da data. É por isso que estamos usando a formatação de números para mostrar as porções do dia.