Stock de pharmacie dans Excel : lot, péremption, réassort
Le stock d'officine dans Excel, c'est une ligne par produit et par lot. Les colonnes qui comptent au comptoir sont le produit, le lot, la quantité, la date de péremption et le minimum sous lequel vous ne voulez pas descendre. SI signale la ligne dans deux cas que vous définissez. La quantité passe sous ce minimum, ou la péremption entre dans le nombre de jours que vous avez saisi. SOMME.SI additionne ce qui reste d'un même produit sur tous ses lots, pour qu'un total ne cache pas un lot ancien derrière un lot récent. C'est la liste de l'officine, pas un plan d'allées d'entrepôt et pas une prévision de ventes. Vous saisissez la quantité et le prix d'achat unitaire. La valeur de la ligne, c'est la quantité multipliée par ce prix. La quantité à recommander, c'est le minimum moins ce qui reste, quand la différence est positive. Les étapes plus bas ajoutent le statut, la valeur, la quantité à recommander et la liste des péremptions. Elyx peut poser ces colonnes sur l'export que vous avez déjà. Vous comptez toujours le rayon. L'essai dure 7 jours, sans carte. Quand un signal semble faux, regardez la cellule avant la formule. Triez la colonne que la formule lit et cherchez les vides et les mots qui ne sont pas dans la liste déroulante. Un vide est invisible pour NB.SI et NB.SI.ENS. Une date stockée en texte ne se compare pas à une vraie date, donc un retard ou un nombre de jours reste vide. Corrigez la cellule, puis relisez le signal. Laissez les lignes terminées sur la feuille. Ce sont elles qui servent de base quand vous comptez une période. Les effacer pour « faire propre » retire cette base. Filtrez-les hors de la vue. Si vous ouvrez un nouveau mois, copiez la feuille et nommez l'ancienne avec le mois. Les formules ne changent pas. Avant d'envoyer le total, lisez trois lignes face à la source que vous avez saisie. Si ces trois lignes tombent juste, le total est celui de cette feuille. Choisissez les lignes vous-même. Une formule juste sur la ligne que vous avez déjà calculée à la main est celle qu'on recopie. Écrivez l'unité dans l'en-tête : jours, heures ou pièces. Un nombre sans unité sera divisé plus tard par la mauvaise base. Si deux personnes tiennent la feuille, la liste déroulante est l'accord sur les mots. Changez la liste à un seul endroit, pas les cellules une par une. Gardez la colonne source à côté de la colonne formule, pour que le contrôle ne demande pas d'ouvrir un autre fichier.
Le probleme
Un export de pharmacie a souvent une ligne par produit, sans lot. Deux réceptions du même produit partagent alors une quantité, et la péremption la plus proche disparaît. La décision de réassort devient un coup d'œil : on commande parce que le rayon semble léger, ou on découvre le manque au comptoir. Séparez la ligne par lot. Gardez la péremption sur cette ligne, et le minimum à côté du produit. Le signal lit ces cellules. Il ne commande pas chez le grossiste, et il ne dépose pas un dossier réglementaire.
Les benefices
Chaque lot a sa ligne, donc une péremption plus proche n'est pas cachée dans le total du produit.
SI signale la ligne quand la quantité passe sous le minimum saisi, ou quand la péremption entre dans le délai saisi.
SOMME.SI additionne la quantité d'un produit sur ses lots. Le total et le lot restent visibles ensemble.
La valeur du stock, c'est la quantité multipliée par le prix d'achat que vous avez saisi. La feuille ne cherche pas un prix de remboursement.
La quantité à recommander, c'est votre minimum moins la quantité restante, quand cette différence est positive.
Tutoriel pas a pas
Créer la structure du tableau principal
Créez un nouveau classeur Excel et définissez les colonnes essentielles pour le suivi des stocks pharmaceutiques. Ces colonnes doivent inclure les informations critiques pour la gestion quotidienne et la conformité réglementaire.
Utilisez Ctrl+T pour convertir votre plage en tableau structuré, ce qui facilitera l'ajout de formules automatiques
Définir les en-têtes de colonnes
Insérez les colonnes suivantes : Code DCI, Dénomination, Forme pharmaceutique, Dosage, Numéro de lot, Date d'expiration, Stock initial, Entrées, Sorties, Stock actuel, Seuil d'alerte, Fournisseur. Formatez l'en-tête en gras avec une couleur de fond pour une meilleure lisibilité.
Gellez la première ligne (Affichage > Figer les volets) pour la garder visible lors du défilement
Ajouter des données d'exemple réalistes
Remplissez le tableau avec des données pharmaceutiques concrètes (Amoxicilline 500mg, Paracétamol 1000mg, etc.). Incluez au minimum 10-15 médicaments pour tester efficacement vos formules. Utilisez des dates d'expiration réalistes et des quantités variées.
Utilisez la validation de données pour les colonnes Forme pharmaceutique et Fournisseur afin de garantir la cohérence
Créer la formule de calcul du stock actuel
Dans la colonne 'Stock actuel', créez une formule qui additionne le stock initial et les entrées, puis soustrait les sorties. Cette formule doit se mettre à jour automatiquement à chaque modification des entrées ou sorties.
=E2+H2-I2Exemple : Paracétamol avec Stock initial (100) + Entrées (50) - Sorties (30) = Stock actuel (120)
Implémenter l'alerte de rupture de stock
Créez une formule conditionnelle qui compare le stock actuel au seuil d'alerte. Cette alerte est cruciale pour éviter les ruptures de stock et assurer la continuité du service pharmaceutique. Utilisez la mise en forme conditionnelle pour visualiser rapidement les médicaments critiques.
=SI(J2<K2;"ALERTE";"OK")Ajoutez une mise en forme conditionnelle : si Stock actuel < Seuil d'alerte, appliquez un fond rouge pour une visibilité immédiate
Ajouter une colonne de vérification d'expiration
Insérez une formule qui vérifie si la date d'expiration est dépassée ou approche (moins de 30 jours). Cela aide à respecter les normes de pharmacovigilance et à éviter la dispensation de médicaments périmés.
=SI(F2<AUJOURD'HUI();"EXPIRÉ";SI(F2<AUJOURD'HUI()+30;"À EXPIRER";"OK"))Exemple : Un lot expirant dans 15 jours affichera 'À EXPIRER' en jaune pour une action préventive
Créer un tableau de synthèse avec SOMME.SI
Créez un tableau de bord récapitulatif qui utilise SOMME.SI pour calculer automatiquement le stock total par fournisseur ou par forme pharmaceutique. Cela permet une analyse rapide et une optimisation des commandes.
=SOMME.SI($L$2:$L$50;"Fournisseur A";$J$2:$J$50)Cette formule totalise tous les stocks actuels des médicaments fournis par 'Fournisseur A', utile pour la gestion des approvisionnements
Intégrer RECHERCHEV pour les informations fournisseur
Créez une table de référence des fournisseurs avec leurs coordonnées. Utilisez RECHERCHEV pour récupérer automatiquement le numéro de téléphone ou l'email du fournisseur en fonction du nom saisi dans la colonne Fournisseur.
=RECHERCHEV(L2;Fournisseurs!$A$2:$D$50;3;FAUX)Exemple : Entrez 'Fournisseur A' et récupérez automatiquement son email pour les commandes urgentes
Ajouter des statistiques clés avec SUMIF et conditions
Créez des cellules de synthèse qui affichent le nombre de médicaments en alerte, le stock total global, la valeur estimée du stock, et le nombre de lots expirés. Ces indicateurs permettent une gestion stratégique rapide.
=SOMME.SI(M2:M50;"ALERTE";1) pour compter les alertes ou =SOMME(J2:J50) pour le stock totalPlacez ces statistiques en haut de feuille dans une zone 'Tableau de bord' avec un formatage distinctif
Finaliser avec mise en forme et protections
Appliquez une mise en forme professionnelle : alternez les couleurs de lignes, utilisez des polices lisibles, et protégez les cellules de formules pour éviter les modifications accidentelles. Créez également un filtre automatique pour rechercher rapidement par DCI ou fournisseur.
Utilisez Données > Filtre automatique pour activer les filtres, puis Outils > Protéger la feuille pour sécuriser vos formules
Fonctionnalites du template
Alerte automatique de stock minimum
Identifie les médicaments dont le stock est inférieur au seuil critique pour déclencher les commandes à temps et éviter les ruptures
=SI(B2<C2,"COMMANDER","OK")Calcul automatique de la valeur totale du stock
Évalue en temps réel la valeur financière globale du stock pour la gestion budgétaire et les déclarations d'inventaire
=SOMME(B2:B100*D2:D100)Suivi de la date de péremption avec alertes
Signale les médicaments proches de l'expiration pour éviter les pertes et respecter les obligations réglementaires
=SI(JOURSECART(E2,AUJOURD'HUI())<30,"À ÉLIMINER","VALIDE")Historique des mouvements de stock
Enregistre chaque entrée/sortie avec date et quantité pour tracer les flux et justifier les écarts lors des contrôles
=CONCATENER(F2," - ",G2," unités le ",H2)Tableau de bord de rotation des stocks (FIFO)
Priorise la vente des médicaments les plus anciens pour optimiser la gestion et réduire les péremptions
=CLASSEMENT(E2,E$2:E$100,1)Génération automatique de rapports de conformité
Compile les données de stock pour faciliter les audits et les déclarations obligatoires auprès des autorités sanitaires
=FILTRE(A2:D100,C2:C100<B2:B100)Exemples concrets
Gestion des ruptures de stock de médicaments essentiels
Jean, pharmacien titulaire d'une officine urbaine, doit gérer les ruptures fréquentes de médicaments chroniques (antihypertenseurs, anticoagulants) et anticiper les commandes pour éviter les ruptures.
Amiodarone 200mg: stock actuel 45 boîtes, consommation moyenne 12 boîtes/mois, délai de livraison 5 jours. Metformine 850mg: stock 120 boîtes, consommation 35 boîtes/mois, délai 3 jours. Doliprane 1000mg: stock 80 boîtes, consommation 25 boîtes/mois, délai 2 jours.
Resultat : Un tableau avec alertes automatiques (seuil minimum atteint), dates de réapprovisionnement recommandées, et historique de consommation pour identifier les pics saisonniers (grippe, allergies). Identification immédiate que l'Amiodarone doit être commandée en urgence.
Suivi des dates de péremption et conformité réglementaire
Sophie, pharmacienne responsable de la gestion, doit respecter les obligations légales (ANSM) concernant le contrôle des DLUO/DLC et minimiser les pertes liées aux médicaments périmés.
Aspirine 500mg: 30 boîtes expiration 15/03/2024, Ibuprofène 200mg: 45 boîtes expiration 22/06/2024, Paracétamol sirop: 12 flacons expiration 08/02/2024, Vitamine C: 60 boîtes expiration 30/12/2025.
Resultat : Un tableau trié par date d'expiration avec code couleur (rouge <30 jours, orange <60 jours, vert >60 jours). Rapport automatique des médicaments à détruire ou à retourner fournisseur. Historique des pertes par fournisseur pour négocier les conditions.
Analyse de rentabilité par catégorie de produits et marges
Marc, titulaire d'une petite pharmacie de village, doit optimiser son mix produit en identifiant quels médicaments/produits génèrent le meilleur chiffre d'affaires et marges pour justifier l'espace de stockage.
Médicaments génériques: CA 18000EUR/mois, marge 22%, rotation 45 jours. Médicaments princeps: CA 12000EUR/mois, marge 18%, rotation 60 jours. Produits de parapharmacie: CA 8500EUR/mois, marge 38%, rotation 25 jours. Matériel médical: CA 4200EUR/mois, marge 35%, rotation 90 jours.
Resultat : Un tableau de bord avec CA, marge brute, rotation de stock et rentabilité par m² de rayon. Identification que la parapharmacie génère 38% de marge avec une excellente rotation (25 jours), justifiant une augmentation de l'assortiment. Alertes sur le matériel médical à faible rotation (90 jours) nécessitant une restructuration.
Astuces de pro
Alertes automatiques de rupture de stock avec mise en forme conditionnelle
Créez des alertes visuelles instantanées pour les médicaments proches du seuil minimum. Sélectionnez la colonne de stock, allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle, et appliquez une formule pour colorer en rouge les valeurs inférieures au seuil. Cela vous permet de repérer immédiatement les ruptures imminentes sans passer en revue manuellement chaque ligne. Gain de temps : 15-20 min/jour.
=E2<=F2Suivi FIFO automatisé avec formules imbriquées
Utilisez une formule combinant INDEX et MATCH pour identifier automatiquement les lots les plus anciens à consommer en priorité. Cela garantit le respect du FIFO (First In, First Out) obligatoire en pharmacie et réduit les risques de péremption. Créez une colonne 'Lot à consommer' avec la formule ci-contre pour extraire automatiquement le lot le plus ancien.
=INDEX(Lots;MATCH(MIN(SI(Stock>0;DateLot));DateLot;0))Tableau de bord de rotation avec graphiques dynamiques
Créez un tableau croisé dynamique (TCD) pour analyser la rotation des stocks par catégorie thérapeutique. Insérez un TCD depuis l'onglet Insertion, puis ajoutez des graphiques en cascade pour visualiser les produits lents à rotation. Cela vous aide à optimiser vos commandes et à identifier les surstock coûteux. Mettez à jour le TCD chaque semaine avec Actualiser (Ctrl+Alt+F5).
Calcul automatique du coût moyen pondéré avec SUMPRODUCT
Maîtrisez votre valorisation de stock en calculant le coût moyen pondéré de chaque référence. Cette formule évalue précisément votre patrimoine pharmaceutique et facilite les audits. Utilisez SUMPRODUCT pour diviser le coût total par la quantité totale, indispensable pour les déclarations de TVA et de résultats.
=SUMPRODUCT(Quantité;PrixUnitaire)/SUMPRODUCT(Quantité)