Crie um calendário no Excel com uma fórmula usando uma fórmula inserida em matriz.
Olhe para esta figura:
Essa fórmula,, =Coolé a mesma fórmula em todas as células de B5: H10! Veja:
Foi inserido na matriz assim que B5: H10 foi selecionado pela primeira vez. Neste artigo, você verá o que está por trás da fórmula.
A propósito, há uma célula que ainda não foi mostrada que é o mês a ser mostrado. Ou seja, a célula J1 contém =TODAY(), (e estou escrevendo isso em dezembro), mas se você alterá-la para 08/05/2012, verá:
Estamos em maio de 2012. OK, definitivamente legal! Comece do início e vá até essa fórmula no calendário e veja como ela funciona.
Além disso, suponha que hoje seja 8 de maio de 2012.
Primeiro, olhe para esta figura:
A fórmula realmente não faz sentido. Seria, se estivesse cercado por =SUM, mas você quer ver o que está por trás da fórmula, então você vai expandi-la selecionando-a e pressionando a tecla F9.
A figura acima torna-se a figura abaixo quando a tecla F9 é pressionada.
Observe que há um ponto-e-vírgula após o 3 - isso indica uma nova linha. Novas colunas são representadas por uma vírgula. Então você vai tirar vantagem disso.
O número de semanas em um mês varia, mas nenhum calendário precisa de mais de seis linhas para representar qualquer mês e, claro, todos eles têm sete dias. Olhe para esta figura:
Insira manualmente os valores de 1 a 42 em B5: H10, e se você inserir =B5:H10em uma célula e, em seguida, expandir a barra de fórmula, você verá o que é mostrado aqui:
Observe a colocação dos pontos-e-vírgulas - após cada múltiplo de 7 - indicando uma nova linha. Este é o início da fórmula, mas em vez de uma fórmula tão longa, você pode usar esta fórmula mais curta. Selecione B5: H10. Tipo
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
como a fórmula, mas não pressione Enter.
Para dizer ao Excel que esta é uma fórmula de matriz, você deve manter pressionado Ctrl + Shift com a mão esquerda. Enquanto segura Ctrl + Shift, pressione Enter com a mão direita. Em seguida, solte Ctrl + Shift. No restante deste artigo, esse conjunto de pressionamentos de tecla será denominado Ctrl + Shift + Enter.
Se você fez Ctrl + Shift + Enter corretamente, as chaves aparecerão ao redor da fórmula na barra de fórmulas e os números de 1 a 42 aparecerão em B5: H10 conforme mostrado aqui:
Observe que você está pegando os números de 0 a 5 separados por ponto-e-vírgula (nova linha para cada) e multiplicando-os por 7, dando efetivamente o seguinte:
A orientação vertical desses valores somada à orientação horizontal dos valores de 1 a 7 produz os mesmos valores mostrados. A expansão disso é idêntica à que você tinha antes. Suponha que agora você adicione HOJE a esses números.
Observação: editar uma fórmula de matriz existente é muito complicado. Com cuidado, siga estas etapas: Selecione B5: H10. Clique na barra de fórmulas para editar a fórmula existente. Digite + J1, mas não pressione Enter. Para aceitar a fórmula editada, pressione Ctrl + Shift + Enter.
O resultado de 8 de maio de 2012 é:
Esses números são números de série (o número de dias desde 01/01/1900). Se você formatar como datas curtas:
Claramente não está certo, mas você vai chegar lá. E se você os formatar simplesmente como "d" para o dia do mês:
Quase parece um mês, mas nenhum mês começa no nono dia do mês. Ah, aqui está um problema. Você usou o J1 que contém 08/05/2012 e realmente precisa usar a data do primeiro dia do mês. Então, suponha que você coloque =DATE(YEAR(J1),MONTH(J1),1)em J2:
A célula J1 contém 08/05/2012 e a célula J2 muda isso para o primeiro dia do mês de tudo o que é inserido em J1. Portanto, se você alterar J1 na fórmula do calendário para J2:
Mais perto, mas ainda não está certo. É necessário mais um ajuste, ou seja, você precisa subtrair o dia da semana do primeiro dia. Ou seja, a célula J3 contém =WEEKDAY(J2). 3 representa terça-feira. Portanto, agora, se você subtrair J3 desta fórmula, obterá:
E isso é certo para maio de 2012!
Ok, você está bem perto. O que ainda está errado é que os dias 29 e 30 de abril estão aparecendo no calendário de maio e de 1 ° a 9 de junho também. Você precisa limpar isso.
Você pode dar um nome à fórmula para facilitar a referência. Chame de "Cal" (não é "legal" ainda). Veja esta figura:
Em seguida, você pode alterar a fórmula para simplesmente ser =Cal(ainda Ctrl + Shift + Enter):
Agora você pode alterar a fórmula para ler que, se o resultado estiver na linha 5 e o resultado for superior a 20, digamos, esse resultado deve estar em branco. A linha 5 conterá a primeira semana de qualquer mês, portanto, você nunca deve ver nenhum valor acima de 20 (ou qualquer número acima de sete estaria errado - um número como 29 que você vê na célula B5 da figura acima é do mês anterior). Então você pode usar =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Primeiro, observe que as células B5: D5 estão em branco. A fórmula agora lê "se esta for a linha 5, então se o DIA do resultado for superior a 20, mostre em branco".
Você pode continuar a remover os números baixos no final - os valores do próximo mês. Veja como fazer isso facilmente.
Edite a fórmula e selecione a referência final para "Cal"
Comece digitando IF (ROW ()> 8, SE (DIA (Cal) <15, "", Cal), Cal) para substituir o Cal final.
A fórmula final deve ser
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Pressione Ctrl + Shift + Enter. O resultado deve ser:
Duas coisas a fazer. Você pode pegar essa fórmula e dar a ela um nome, "Legal":
Em seguida, use isso na fórmula mostrada aqui:
A propósito, nomes definidos são tratados como se fossem inseridos em matriz.
O que falta fazer é formatar as células e inserir os dias da semana e o nome do mês. Assim, você amplia as colunas, aumenta a altura da linha, aumenta o tamanho da fonte e alinha o texto:
Em seguida, coloque bordas ao redor das células:
Una e centralize o mês e o ano e formate-os:
Em seguida, desative as linhas de grade e voila:
Este artigo convidado é do Excel MVP Bob Umlas. É do livro Excel Outside the Box. Para ver os outros tópicos do livro, clique aqui.
