Un client m’a demandé un développement pour trouver le dernier prix de vente dans une base de données. Ps besoin de développement, une simple formule excel suffit. En effet, il peut être fréquent d’avoir besoin de retrouver le dernier prix de vente d’un article. Cette information est cruciale pour :
- La mise à jour des tarifs
- La création de nouveaux devis
- L’analyse des évolutions de prix
- La vérification des conditions commerciales
La solution avec Excel 365
J’ai commencé par réaliser la formule pour Excel 365. La fonction FILTRE simplifie la formule.
=SOMMEPROD((Ventes[Ref Article]=C3)*(Ventes[DateFact]=MAX(FILTRE(Ventes[DateFact];Ventes[Ref Article]=C3)))*(Ventes[Prix de vente unitaire (€)]))
Comprendre la formule étape par étape : (Ventes[Ref Article]=C3) : Identifie toutes les lignes correspondant à l’article recherché
FILTRE(Ventes[DateFact];Ventes[Ref Article]=C3) : Extrait toutes les dates pour cet article
MAX(…) : Trouve la date la plus récente
(Ventes[DateFact]=MAX(…)) : Identifie la ligne avec la date la plus récente
(Ventes[Prix de vente unitaire (€)]) : Récupère le prix correspondant
La solution compatible Excel 2016 ou antérieur
Pour les versions antérieures d’Excel, nous devons adapter la formule en remplaçant la fonction FILTRE :
=SOMMEPROD((Ventes[Ref Article]=C3)*(Ventes[DateFact]=MAX(SI(Ventes[Ref Article]=C3;Ventes[DateFact])))*(Ventes[Prix de vente unitaire (€)]))
Comment fonctionne la version 2016 ?
SI(Ventes[Ref Article]=C3;Ventes[DateFact]) remplace FILTRE en créant un tableau conditionnel :
Pour chaque ligne où la référence correspond, on garde la date. Pour les autres lignes, on obtient FAUX.
MAX(…) trouve la date la plus récente parmi les dates conservées.
Le reste de la formule est identique à la version 365. Attention il faut penser à valider la formule matriciellement avec : ctrl + shift : entrée.
Adaptation de la formule
Cette formule peut être facilement adaptée pour d’autres besoins :
- Trouver le premier prix plutôt que le dernier (remplacer MAX par MIN)
- Chercher dans une période spécifique (ajouter une condition sur la date)
- Obtenir la date plutôt que le prix (modifier la dernière partie de la formule)
Le fonctionnement de SOMMEPROD
Pour travailler dans cette formule, nous avons utilisé SOMMEPROD. C’est une formule matricielle qui effectue des calculs sur plusieurs cellules simultanément, plutôt que sur une seule valeur. Dans Excel, ces formules sont particulièrement puissantes car elles permettent de :
- Traiter des tableaux entiers de données
- Effectuer plusieurs comparaisons en même temps
- Combiner plusieurs conditions
- Éviter l’utilisation de formules imbriquées complexes
SOMMEPROD est l’une des fonctions matricielles les plus utilisées dans Excel. Elle fonctionne en :
- Multipliant les éléments correspondants de plusieurs tableaux
- Additionnant tous les produits obtenus
Dans notre cas, elle est utilisée avec des expressions booléennes qui renvoient 1 (VRAI) ou 0 (FAUX), ce qui en fait un puissant outil de filtrage.
Pour en savoir plus sur cette formule, vous avez le site de Microsoft : SOMMEPROD ou sur Excel Pratique.
Bonjour
Merci pour cet article et cette astuce.