Il y a quelques temps, Katy, une lectrice, m’a fait parvenir un fichier Excel en me demandant de lui venir en aide. Elle cherchait à écrire une formule, dans un fichier contenant les ventes mensuelles d’une petite entreprise, qui lui permettrait de mettre en évidence automatiquement le mois où les revenus cumulés des 12 derniers mois devenaient supérieurs à 30,000$. Pourquoi? Parce qu’une petite entreprise qui ne fait pas 30,000$ en 12 mois n’est pas obligée de s’inscrire aux taxes. Comme comptable, Katy souhaitait donc que son fichier lui indique quand il serait le temps que ses clients, des petites entreprises, s’inscrivent aux taxes.
Tableau croisé dynamique ou non
Le premier réflexe de Katy a été de tenter une approche avec un tableau croisé dynamique et c’est là qu’elle s’est résignée à me parler. Je lui ai plutôt proposé une approche très simple avec la fonction Offset (ou Decaler en français). Pour relire un article qui explique comment fonctionne Offset/Decaler, je vous invite à consulter celui-ci: Excel: Fonction Decaler (Offset).
La fonction DECALER pour calculer le seuil minimum de revenus pour s’inscrire aux taxes
Katy m’avait donc fourni un fichier avec des ventes mensuelles placées en colonnes. J’ai simplement ajouté une colonne supplémentaire, qui calcule le cumulatif des 12 derniers mois. Dans l’exemple en question, la petite entreprise a ouvert ses portes en janvier 2014. Voici comment on doit interpréter la formule que j’ai utilisée, soit SUM(OFFSET(,C6,0,0,-MIN((ROW()-5),12),1)) ou en français SOMME(DECALER(,C6;0;0;-MIN((LIGNE()-5);12);1)):
- row()-5 ou ligne()-5 ramène le numéro de la ligne où on se situe moins 5, ainsi sur la ligne 6, qui est le mois de janvier, on obtient 1, sur la ligne 7, qui est le mois de février, on obtient 2 et ainsi de suite
- min((row()-5),12 ou min((ligne()-5),12 ramène le minimum entre le montant trouver en 1 et 12, donc en janvier on obtient 1, en février on obtient 2 et ainsi de suite jusqu’à janvier 2015, ou on obtient 12 ainsi que pour tous les mois suivants
- offset(,C6,0,0,-min((row()-5),12),1) ou Decaler(,C6;0;0;-min((ligne()-5);12);1) fonctionne comme suit… on part de la cellule C6, on descend vers le bas de 0 et vers la gauche de 0 (donc, on reste sur la cellule C6) et on sélectionne une hauteur de cellules correspondant au résultat trouvé en 2 et une largeur de 1 cellule, donc en janvier, on obtient la valeur de la cellule C6, en février, on obtient la somme de la valeur de la cellule C6 et D6 et ainsi de suite
- on fait finalement la somme de la plage déterminée en 3
Vous devez consolider des données, les analyser, les interpréter et présenter des conclusions utiles pour des fins de prise de décision ? Apprenez à développer des solutions robustes et automatisées avec nos formations en Tableaux de bord avec Excel. |
|---|
La mise en forme conditionnelle pour souligner quand il faut s’inscrire aux taxes
Pour mettre en évidence les mois où le revenus cumulatifs des 12 derniers mois sont supérieurs à 30,000$, j’ai simplement utilisé le format conditionnel, en demandant à Excel de mettre en rouge ces mois en question, tel qu’illustré ci-dessous.
Et vous?
Vous êtes comptable et vous devez automatiser ce genre de calculs ou d’autres calculs de même nature? Utilisez la zone de commentaires ci-bas pour nous posez vos questions. Nous saurons sans doute y répondre par le biais d’un billet de blogue.
Fichier d’accompagnement
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Êtes-vous aussi bon que vous le pensez avec Excel ? Faites le test ! Vous pourrez ensuite suivre la formation Excel – Mise à niveau qui peut servir de mise à niveau pour les professionnels qui utilisent déjà Excel dans le cadre de leurs fonctions et qui désirent aller plus loin et elle est également un prérequis recommandé pour les formations Excel – Tableaux de bord (niveau 1), Excel O365 – Bienvenue dans l’ère moderne! et surtout, Excel – Modélisation financière (niveau 1).
Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :
La mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.
