Il y a quelques temps, Katy, une lectrice de Mon Cher Watson, 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’insrivent 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 en renfort
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
Le format conditionnel
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-bas.
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 de billet de blogue.
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou [email protected]
Jeu de briques
Snake
Pacman
Bubble