Créez un calendrier dans Excel avec une formule à l'aide d'une formule saisie par tableau.
Regardez ce chiffre:
Cette formule =Coolest la même dans chaque cellule de B5: H10! Regardez:
Il a été entré dans la matrice une fois que B5: H10 a été sélectionné pour la première fois. Dans cet article, vous verrez ce qui se cache derrière la formule.
Au fait, il y a une cellule qui n'est pas encore affichée et qui est le mois à afficher. Autrement dit, la cellule J1 contient =TODAY(), (et j'écris ceci en décembre) mais si vous la changez en 5/8/2012, vous verrez:
Nous sommes en mai 2012. OK, vraiment cool! Commencez par le début et progressez jusqu'à cette formule dans le calendrier et voyez comment cela fonctionne.
Supposons également qu'aujourd'hui soit le 8 mai 2012.
Tout d'abord, regardez cette figure:
La formule n'a pas vraiment de sens. Ce serait le cas, s'il était entouré de =SUM, mais que vous voulez voir ce qui se cache derrière la formule, vous allez donc le développer en le sélectionnant et en appuyant sur la touche F9.
La figure ci-dessus devient la figure ci-dessous lorsque la touche F9 est enfoncée.
Notez qu'il y a un point-virgule après le 3 - cela indique une nouvelle ligne. Les nouvelles colonnes sont représentées par une virgule. Vous allez donc en profiter.
Le nombre de semaines dans un mois varie, mais aucun calendrier n'a besoin de plus de six lignes pour représenter un mois, et bien sûr, ils ont tous sept jours. Regardez ce chiffre:
Entrez manuellement les valeurs 1 à 42 dans B5: H10, et si vous entrez =B5:H10dans une cellule puis développez la barre de formule, vous voyez ce qui est montré ici:
Notez le placement des points-virgules - après chaque multiple de 7 - indiquant une nouvelle ligne. C'est le début de la formule, mais au lieu d'une formule aussi longue, vous pouvez utiliser cette formule plus courte. Sélectionnez B5: H10. Type
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
comme formule, mais n'appuyez pas sur Entrée.
Pour dire à Excel qu'il s'agit d'une formule matricielle, vous devez maintenir Ctrl + Maj avec votre main gauche. Tout en maintenant Ctrl + Maj, appuyez sur Entrée avec votre main droite. Ensuite, relâchez Ctrl + Maj. Pour le reste de cet article, cet ensemble de frappes s'appellera Ctrl + Maj + Entrée.
Si vous avez fait Ctrl + Maj + Entrée correctement, des accolades apparaîtront autour de la formule dans la barre de formule et les nombres 1 à 42 apparaîtront dans B5: H10 comme indiqué ici:
Notez que vous prenez les nombres de 0 à 5 séparés par des points-virgules (nouvelle ligne pour chacun) et que vous les multipliez par 7, ce qui donne effectivement ceci:
L'orientation verticale de ces valeurs ajoutée à l'orientation horizontale des valeurs 1 à 7 donne les mêmes valeurs que celles indiquées. L'expansion de ceci est identique à ce que vous aviez auparavant. Supposons maintenant que vous ajoutiez AUJOURD'HUI à ces nombres?
Remarque: la modification d'une formule matricielle existante est très délicate. Suivez attentivement les étapes suivantes: Sélectionnez B5: H10. Cliquez dans la barre de formule pour modifier la formule existante. Tapez + J1 mais n'appuyez pas sur Entrée. Pour accepter la formule modifiée, appuyez sur Ctrl + Maj + Entrée.
Le résultat pour le 8 mai 2012 est:
Ces numéros sont des numéros de série (le nombre de jours depuis le 1/1/1900). Si vous les formatez sous forme de dates courtes:
Clairement pas bien, mais vous y arriverez. Que faire si vous les formatez simplement en "d" pour le jour du mois:
On dirait presque un mois, mais aucun mois ne commence par le 9 du mois. Ah, voici un problème. Vous avez utilisé J1 qui contient 5/8/2012, et vous devez vraiment utiliser la date du premier du mois. Supposons donc que vous mettiez =DATE(YEAR(J1),MONTH(J1),1)dans J2:
La cellule J1 contient 5/8/2012 et la cellule J2 change cela au premier du mois de tout ce qui est entré dans J1. Donc, si vous changez J1 dans la formule du calendrier en J2:
Plus proche, mais toujours pas juste. Un autre ajustement est nécessaire, et c'est que vous devez soustraire le jour de la semaine du premier jour. Autrement dit, la cellule J3 contient =WEEKDAY(J2). 3 représente mardi. Alors maintenant, si vous soustrayez J3 de cette formule, vous obtenez:
Et c'est en fait juste pour mai 2012!
D'accord, vous êtes vraiment proche. Ce qui ne va toujours pas, c'est que les 29 et 30 d'avril apparaissent dans le calendrier de mai, et du 1er au 9 juin apparaît également. Vous devez les effacer.
Vous pouvez donner un nom à la formule pour une référence plus facile. Appelez-le "Cal" (pas encore "cool"). Voir cette figure:
Ensuite, vous pouvez modifier la formule pour qu'elle soit simplement =Cal(toujours Ctrl + Maj + Entrée):
Vous pouvez maintenant modifier la formule pour lire que si le résultat est à la ligne 5 et que le résultat est supérieur à 20, par exemple, ce résultat doit être vide. La ligne 5 contiendra la première semaine de n'importe quel mois, vous ne devriez donc jamais voir de valeurs supérieures à 20 (ou tout nombre supérieur à sept serait faux - un nombre comme 29 que vous voyez dans la cellule B5 de la figure ci-dessus est du mois précédent). Vous pouvez donc utiliser =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Tout d'abord, notez que les cellules B5: D5 sont vides. La formule lit maintenant "s'il s'agit de la ligne 5, alors si le JOUR du résultat est supérieur à 20, affichez le blanc".
Vous pouvez continuer à supprimer les nombres faibles à la fin - les valeurs du mois prochain. Voici comment procéder facilement.
Modifiez la formule et sélectionnez la référence finale à "Cal"
Commencez à taper IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) pour remplacer le Cal. Final.
La formule finale devrait être
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Appuyez sur Ctrl + Maj + Entrée. Le résultat devrait être:
Il reste deux choses à faire. Vous pouvez prendre cette formule et lui donner un nom, "Cool":
Ensuite, utilisez cela dans la formule montrée ici:
En passant, les noms définis sont traités comme s'ils étaient entrés dans un tableau.
Il ne reste plus qu'à formater les cellules et à insérer les jours de la semaine et le nom du mois. Vous élargissez donc les colonnes, augmentez la hauteur de la ligne, augmentez la taille de la police et alignez le texte:
Ensuite, mettez des bordures autour des cellules:
Fusionner et centrer le mois et l'année et le formater:
Puis désactivez le quadrillage, et voilà:
Cet article invité provient d'Excel MVP Bob Umlas. Il est tiré du livre, Excel Outside the Box. Pour voir les autres sujets du livre, cliquez ici.
