Dans cette vidéo, nous verrons comment calculer et mettre en évidence les dates d'expiration.
Disons que votre entreprise a lancé un programme d'adhésion quelconque et que votre patron vient de vous envoyer un ensemble de données. Elle vous a donné une liste de 1000 personnes qui ont renouvelé leur adhésion au cours de la dernière année environ, et elle recherche plusieurs choses.
Premièrement, elle souhaite que vous calculiez une date d'expiration un an dans le futur, le dernier jour du même mois où l'adhésion a été renouvelée.
Deuxièmement, elle veut voir combien de jours il reste avant l'expiration.
Troisièmement, elle souhaite voir le statut "Expiré" pour les adhésions déjà expirées et "Expirant bientôt" pour les adhésions expirant dans les 30 prochains jours.
Enfin, elle a dit que ce serait bien de voir les membres expirés surlignés en rose et ceux qui expiraient bientôt surlignés en jaune.
Aussi - peut-elle l'avoir avant son déjeuner-réunion à midi?
Euh, bien sûr.
Tout d'abord, convertissons ces données en un tableau Excel approprié. Cela rendra les formules beaucoup plus faciles à saisir, car Excel les copiera automatiquement au fur et à mesure.
Calculons maintenant les dates d'expiration. Celles-ci sont censées être à la fin du même mois un an plus tard, mais utilisons d'abord un simple hack, pour entrer dans le vif du sujet. Comme vous l'avez vu dans les vidéos précédentes, les dates ne sont que des numéros de série, nous pouvons donc simplement entrer une formule qui ajoute 365 jours à la date de renouvellement.
C'est un bon début. Nous pouvons terminer l'ébauche de la solution et revenir y remédier plus tard.
Lorsque vous résolvez un problème plus complexe dans Excel, c'est une bonne idée de valider l'approche globale, puis de revenir aux détails à la fin. Vous ne voulez pas rester coincé sur une petite chose dès le début, surtout si l'approche peut changer.
Maintenant que nous avons une date d'expiration, nous pouvons maintenant calculer les jours restants. Cela doit être mis à jour automatiquement à l'avenir, nous allons donc utiliser la fonction AUJOURD'HUI, qui renvoie toujours la date d'aujourd'hui.
La formule est simplement E5 moins AUJOURD'HUI (). Quand je frappe retour, il nous reste des jours avant l'expiration. Les nombres négatifs indiquent un abonnement déjà expiré.
Pour le statut, nous utiliserons une simple formule IF imbriquée. Si les jours restants sont inférieurs à zéro, l'adhésion est expirée. Sinon, si le nombre de jours restants est inférieur à 30, le statut doit être "Expirant bientôt". Sinon, le statut n'est rien.
=IF(F5<0,"Expired",IF(F5<30,"Expiring soon",""))
Ensuite, nous devons créer des règles de mise en forme conditionnelle qui mettent en évidence ces valeurs.
Tout d'abord, sélectionnez les données et définissez la cellule active dans le coin supérieur droit. Ensuite, créez une règle de formule qui teste la cellule active pour la valeur «Expiré». La colonne doit être verrouillée.
Répétez maintenant le même processus pour les adhésions qui expirent bientôt.
Cela a l'air bien. Nous avons juste besoin de fixer les dates d'expiration pour atterrir le dernier jour du mois.
Eh bien, il s'avère qu'il existe une fonction intéressante appelée EOMONTH (pour la fin du mois) qui obtient le dernier jour d'un mois dans le passé ou le futur.
La date de début correspond à la date de renouvellement et les mois correspondent au 12.
Et voilà, tout ce que votre patron voulait, et vous avez encore le temps de prendre une tasse de café avant le déjeuner.
Cours
Formule de baseRaccourcis associés
Insérer un tableau Ctrl + T ⌃ + T Développer ou réduire le ruban Ctrl + F1 ⌘ + ⌥ + R Sélectionner un tableau Ctrl + A ⌘ + A Déplacer la cellule active dans le sens des aiguilles d'une montre dans la sélection Ctrl + . ⌃ + .
