Explication Formule Excel : Facturation automatique

  

cliquez sur le lien pour  allez à : Factures Fichier Excel personnalisé & Méthode de préparation & Problème les plus courant 

Structure utilisée pour la Création d'une liste déroulante semi-automatique pour rechercher un client dans Excel

(aussi appelée liste déroulante avec saisie prédictive) est une excellente manière de gagner du temps et éviter les erreurs de saisie.

✅ Objectif :

Permettre à l'utilisateur de taper une partie du nom d’un client dans une cellule (ex: Facture!A12) et d’obtenir automatiquement une liste filtrée de clients correspondants.

Définition des plages nommées exemple liste école

Ajouter une liste de ce que vous voulez exemple liste école. 

Dans Feuille Recherche :

• Colonne A : Liste complète des clients (Client_facture) 

• je peux les apporter d'une autre feuille où il y a tout les client dans A2 j'écris  =Nom_Client_source
• 🎯 Objectif :
• Afficher automatiquement la liste complète des clients (par exemple dans la colonne A d'une feuille "Recherche"), à partir d’une plage de données située dans une autre feuille (par exemple "BaseClients" ou bie "Param CL & PRIX").
• ✅ Étapes à suivre :
• 1. Créer une plage nommée
• Va sur la feuille où se trouvent tous les clients, par exemple "BaseClients".
• Sélectionne la colonne (ou plage) contenant les noms des clients, par exemple B2:B100. le meilleur c'est qu'il soit sous forme d'un tableau 

                              

• Va dans Formules > Gestionnaire de noms (ou Ctrl + F3) et crée un nom de plage :
• Nom : Nom_Client_source
• Fait référence à : =BaseClients!$B$2:$B$100 si c'est un tableau =Clients[Nom Facture]
• ✅ Ce nom permet d'accéder à cette plage depuis n'importe quelle feuille.

• Colonne B : Numéros de ligne : =SI(A2<>"";LIGNES($A$2:A2);"") → Sert de compteur

• Colonne C (Facture_recherche) : Recherche par mot-clé =

  =SI(ESTNUM(CHERCHE(Facture!$A$12;A2));B2;"")

  Cela vérifie si le texte tapé dans Facture!A12 est contenu dans un nom client de la colonne A.

• Colonne D : Liste triée des numéros trouvés =

  =SIERREUR(GRANDE.VALEUR(Facture_recherche;B2);"")

  Cela extrait les lignes valides (triées du plus grand au plus petit).

• Colonne H : Liste finale des clients correspondants =

  =SIERREUR(SI(D2<>"";INDEX(Client_facture;EQUIV(D2;Facture_recherche;0));"");"")

C’est avec cette dernière que nous souhaitons créer notre liste déroulante. Notez que votre liste doit être triée par ordre alphabétique sans quoi votre liste ne fonctionnera pas correctement et que les noms peuvent être modifiés, mais n’oubliez pas de mettre vos propres noms dans les formules.

Premier élément de liste

                                                   figure 1                                                figure 2

                                                          

                                                               figure 3

Placez-vous à la première cellule de la colonne de votre liste, et dans la Zone Nom, remplacez exemple : H2 (voir figure 1) par D_ecole (voir figure 2) (comme première école) et appuyez sur Entrée. Cette action peut aussi être exécutée avec le Gestionnaire de noms (voir figure 3) de l’onglet Formules, mais c’est plus rapide de cette manière.

Toutes les éléments

Sélectionnez ensuite la colonne de votre liste entièrement, ce qui correspond à la colonne de la liste, en cliquant sur l’en-tête de colonne exemple H et nommez cette colonne c_ecole (comme liste de écoles).

 

🏷️ Plages nommées utilisées :

• Client_facture → 'Recherche!$A$2:$A$1000

• Facture_recherche → 'Recherche!$C$2:$C$1000

• D_ecole → 'Recherche'!$H$2

• c_ecole → 'Recherche!$H$2:$H$100

Formule nommée

figure 5

               figure 4

Nous avons maintenant besoin d’une formule nommée. Allez dans Définir un nom (voir figure 4) de l’onglet Formules. Choisissez comme nom rechercherche_ecole (comme formule école) et, dans Fait référence à, saisissez :  =DECALER(D_ecole;0;0;NBVAL(c_ecole);1)

La formule DECALER se décompose de cette manière :

DECALER(réf;lignes;colonnes;hauteur;largeur).

• Réf correspond à la référence par rapport à laquelle le décalage doit être effectué ;
• Lignes correspond au nombre de lignes vers le haut ou le bas dont la cellule supérieure gauche de la référence renvoyée doit être décalée ;
• Colonnes correspond au nombre de colonnes vers la droite ou la gauche dont la cellule supérieure gauche de la référence renvoyée doit être décalée ;
• Hauteur est la hauteur, en nombre de lignes, attendue pour le résultat. Cette hauteur est calculée automatiquement grâce à la formule NBVAL qui compte le nombre de cellules non vides ;
• Largeur est la largeur, en nombre de colonnes, attendue pour le résultat.

🧩 Décomposition de la formule

Cette formule utilise la fonction DECALER pour définir une plage dynamique à partir d’un point de départ (D_ecole), avec une hauteur variable selon le nombre de valeurs présentes dans c_ecole.

1. D_ecole

C’est la cellule de départ (ou plage d’une seule colonne) à partir de laquelle le décalage commence. Par exemple, cela pourrait être une cellule comme A2.

2. 0;0

Cela signifie pas de décalage :

• 0 ligne en dessous

• 0 colonne à droite
  Donc on reste sur D_ecole.

3. NBVAL(c_ecole)

C’est le nombre de cellules non vides dans la plage nommée c_ecole.
→ Cela détermine la hauteur de la nouvelle plage générée.
Par exemple, si c_ecole contient 5 lignes remplies, la nouvelle plage fera 5 lignes de hauteur.

4. 1

C’est la largeur de la plage retournée (1 colonne).

---

🎯 Ce que fait concrètement cette formule

Elle renvoie une plage verticale commençant à D_ecole, s'étendant sur autant de lignes non vides qu’il y en a dans c_ecole, et sur 1 colonne.

---

✅ À quoi ça sert ?

Cette formule est souvent utilisée pour créer une plage dynamique qu'on peut ensuite utiliser :

• dans une liste déroulante (validation de données),

• dans un tableau croisé dynamique,

• ou pour alimenter des graphiques dynamiques.

 Création de la liste déroulante semi-automatique

Sélectionnez ensuite les cellules ou vous voulez créez une liste automatique et allez dans l’onglet Données > Validation des données 

✅ EXSEMPLE Liste déroulante dans Facture!A12 :

• Tu vas dans Données > Validation des données

• Autoriser : Liste dans l’onglet Options de la boîte de dialogue, et gardez cochées les cases Ignorer si vide et Liste déroulante dans la cellule.

• dans Source : =DECALER(D_ecole;0;0;NBVAL(c_ecole);1)

  Cela permet à la liste de ne contenir que les noms correspondant à la recherche.

Pour terminer, dans l’onglet Alerte d’erreur, décochez la case Quand des données non valides sont tapées et cliquez sur Ok.

Désormais, vous pourrez vous placer sur votre liste déroulante, saisir une ou plusieurs lettres du début du nom de ce que vous recherchée et, en cliquant sur la flèche du menu déroulant, seules les éléments commençant par cette ou ces lettres apparaîtront ce qui restreindra votre choix

---

✨ Résultat :

Quand l'utilisateur commence à taper un nom dans Facture!A12, une liste dynamique s'affiche avec uniquement les noms contenant ce texte. Facile, propre et très utile pour les longues listes !

---

Nom client Facture

Cette formule permet d’afficher automatiquement le nom du client dans la cellule A18, en fonction du type de client sélectionné dans la cellule A12.

• Si le client est un "Autre client", alors le nom est directement repris tel qu’il est écrit dans A12.

• Si le client est de type "Facture SMS", le système cherche automatiquement le nom correspondant à l’ID saisi en B13 :

  • D’abord dans le tableau Clients (colonne [ID SMS]).

  • Si aucun résultat, alors dans le tableau clientsms (colonne [ID]).

Cette recherche supprime les éventuels espaces dans les ID pour éviter les erreurs de correspondance.

Nom client Facture E19
=SI(Q5<>"Facture SMS"; SIERREUR(INDEX(Clients[Nom Facture]; EQUIV(MAJUSCULE(SUBSTITUE($A$12;" ";"")); MAJUSCULE(SUBSTITUE(Clients[Nom Facture];" ";""));0)); ""); SIERREUR(INDEX(Clients[Nom Facture]; EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Clients[ID SMS];" ";"");0)); INDEX(clientsms[Nom ecole Facture2]; EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(clientsms[ID];" ";"");0))))

🧠 Explication : 

• Si on a sélectionné "Autre client" dans Q5, on affiche simplement le texte de A12 (le nom est saisi manuellement). 
• Sinon, on cherche automatiquement le nom correspondant à l'ID en B13 : 
• Dans Clients[ID SMS] en priorité. 
• Si non trouvé, alors dans clientsms[ID]. 

🧩 Explication détaillée :

✅ Partie 1 : SI(Q5<>"Facture SMS";$A$12;...)

Si la cellule Q5 est différente de "Facture SMS" → on renvoie le contenu de $A$12.
Sinon → on passe à la suite.

✅ Partie 2 : SIERREUR(... ; ...)

Cette fonction permet d’éviter une erreur si la première recherche ne donne rien.
Elle suit cette logique :
Si la première formule donne un résultat → l'utiliser.
Sinon → utiliser la deuxième formule.

✅ Partie 3 : Première formule INDEX(Clients[Nom Facture];EQUIV(...))

But : Chercher le Nom Facture dans la table Clients, en fonction de l’ID trouvé dans Facture!B13.
On nettoie les espaces avec SUBSTITUE(...;" ";"") pour comparer des ID sans espace.
EQUIV(...) cherche la position de l’ID (nettoyé) dans la colonne Clients[ID SMS].
INDEX(...) utilise cette position pour renvoyer le Nom Facture correspondant.

 ✅ Conseils :

• Assure-toi que les valeurs dans B13 et les colonnes ID ne contiennent pas d'espaces parasites (le SUBSTITUE(...;" ";"") règle en partie ce souci).
• Si tu veux aussi gérer la casse (majuscules/minuscules), on peut aussi ajouter MINUSCULE() pour standardiser.

---

Prix unitaire SMS forfait (entre un palier min et max)

Formule
=SI(F25="Pack SMS Forfaitaire";     INDEX(Tab_SMS_Forfait_Prix_unite;         EQUIV(1;             (H25>=Tab_SMS_Forfait_Palier_Min)*(H25<Tab_SMS_Forfait_Palier_Max);             0         )     ); "")

📘 Étape par étape :

1. SI(F25="Pack SMS Forfaitaire"; ... ; "")

Cette partie vérifie si la cellule F25 contient "Pack SMS Forfaitaire".

Si oui, la formule continue pour chercher le prix.

Sinon, elle renvoie "" (vide).

2. INDEX(Tab_SMS_Forfait_Prix_unite; ...)

INDEX sert à récupérer une valeur dans la plage Tab_SMS_Forfait_Prix_unite.

Mais pour savoir quelle ligne prendre, il faut un numéro de ligne. C’est là que EQUIV intervient.

3. EQUIV(1; (H25>=Min)*(H25<Max); 0)

Tu cherches à savoir dans quel palier se trouve la valeur H25.

Par exemple, si H25 = 120, tu veux trouver dans quelle ligne des paliers ça tombe :

Palier Min = 101, Palier Max = 200 → c'est ici.

La condition :

(H25>=Tab_SMS_Forfait_Palier_Min)*(H25<Tab_SMS_Forfait_Palier_Max)

crée une plage de 0 et 1 :

1 si la valeur de H25 est dans le palier (entre min et max)

0 sinon

✅ Ensuite EQUIV(1; ...; 0) cherche le premier 1 → donc le premier palier correspondant.

🧠 Résultat final :

Tu récupères le prix unitaire correspondant au palier dans lequel se trouve H25.

REF

Formule REF dans facture E 25
=SIERREUR(SI(RECHERCHEV(F25;Produits;2;FAUX)<>"";RECHERCHEV(F25;Produits;2;FAUX);SI(RECHERCHEV(F25;Produits;2;FAUX)="";""));"")

🧠 Explication étape par étape :

• RECHERCHEV(F25;Produits;2;FAUX) cherche une valeur dans la table Produits.

• Si la valeur existe, elle s’affiche.

• Si elle n’existe pas (et donne une erreur), alors SIERREUR(...;"") renvoie simplement vide.

Quantité

Formule quantité dans facture H 25
=SIERREUR(SI(ET($Q$5="Facture SMS";F25="Abonnement mensuel"); INDEX(Consom_SMS_par_mois!F:F;EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Consom_SMS_par_mois!B:B;" ";"");0)); SI(ET($Q$5="Facture SMS";F25<>"Abonnement mensuel");""; SI(ET($Q$5<>"Facture SMS";B25>0);B25;"")));"")

🧠 Explication étape par étape :

SIERREUR(...;"")

➤ Si une erreur survient (par exemple si un client n’est pas trouvé), la cellule reste vide "" au lieu d’afficher une erreur.

Si c’est une "Facture SMS" ET que la ligne concerne "Abonnement mensuel"

➤ On cherche la quantité d’abonnement mensuel dans la colonne F de la feuille Consom_SMS_par_mois, en fonction du client (B13, espaces supprimés).

Sinon, si c’est une "Facture SMS" mais pas un "Abonnement mensuel"

➤ Alors on retourne vide "", car aucune quantité spécifique ne s’applique ici.

Sinon (donc ce n’est pas une "Facture SMS")

➤ La quantité est simplement celle indiquée dans la colonne B25

Si Q5 n'est pas "Facture SMS" et que B25 est supérieur à zéro, la formule retourne la valeur de B25.

Sinon, elle retourne une cellule vide.

✅ Résumé logique :

Facture SMS / Abonnement → cherche la donnée dans un tableau automatique.

Facture SMS / autre que Abonnement → pas de quantité.

Autres factures → laisse l'utilisateur saisir ou récupérer la quantité dans B25.

Prix unitaire

Formule PRIX unitaire HT dans facture I 25

=SIERREUR(   SI(ET($Q$5="Facture SMS";F25="Abonnement mensuel");     INDEX(Consom_SMS_par_mois!G:G;EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Consom_SMS_par_mois!B:B;" ";"");0));   SI(ET($Q$5="Facture SMS";F25="Frais d'abonnement et service SMS.");     INDEX(Consom_SMS_par_mois!I:I;EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Consom_SMS_par_mois!B:B;" ";"");0));   SI(ET($Q$5="Facture SMS";F25<>"Abonnement mensuel";F25<>"Frais d'abonnement et service SMS.");     "";   SI(ET($Q$5<>"Facture SMS";F25="Pack SMS Forfaitaire");     INDEX(Tab_SMS_Forfait_Prix_unite;EQUIV(1;(H25>=Tab_SMS_Forfait_Palier_Min)*(H25<Tab_SMS_Forfait_Palier_Max);0));   SI(ET($Q$5<>"Facture SMS";F25<>"Pack SMS Forfaitaire");     RECHERCHEV(F25;Produits;3;FAUX)   ))))); "")

📘 Étape par étape :

SIERREUR(...;"")

Si une erreur se produit à cause d’une recherche non trouvée, la cellule reste vide au lieu d’afficher une erreur.

Si c’est une facture de type SMS et le produit est "Abonnement mensuel"

➤ Recherche dans la colonne G de l’onglet Consom_SMS_par_mois, en fonction du client (nom en B13, espaces supprimés).

Sinon, si c’est une facture SMS et le produit est "Frais d'abonnement et service SMS."

➤ Recherche similaire mais dans la colonne I.

Sinon, si c’est une facture SMS mais pas un produit spécifique (ni abonnement ni frais d’abonnement)

➤ Retourne vide "" → rien à facturer pour ce cas.

Sinon, si ce n’est pas une "Facture SMS" mais le produit est "Pack SMS Forfaitaire"

➤ On utilise une logique de palier : on regarde la quantité en H25, et on cherche dans la table des prix le bon palier correspondant, pour en tirer le prix unitaire.

Sinon, si ce n’est pas une "Facture SMS" et ce n’est pas un pack forfaitaire non plus

➤ C’est probablement un autre produit standard, donc on va chercher son prix dans la table Produits, 3e colonne.

📝 Remarque sur ma logique métier :

je traites "Pack SMS Forfaitaire" comme un produit non lié à la Facture SMS (contrairement aux abonnements). Ce choix est bien respecté par la formule grâce à SI(ET($Q$5<>"Facture SMS";F25="Pack SMS Forfaitaire")...).

✨ Résumé

Cas	Résultat
Abonnement mensuel SMS Frais d’abonnement/service SMS Autre cas dans "Facture SMS" Pack SMS Forfaitaire Autres produits Erreur dans un des cas	Cherche dans la colonne G Cherche dans la colonne I Vide Calcule prix selon quantité (paliers) RECHERCHEV dans la table Produits Résultat vide ""

 TOTAL HORS TAXE

Formule dans facture J 25

=SIERREUR( SI(ET($Q$5="Facture SMS";F25="Abonnement mensuel"); INDEX(Consom_SMS_par_mois!H:H;EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Consom_SMS_par_mois!B:B;" ";"");0)); SI(ET($Q$5="Facture SMS";F25="Frais d'abonnement et service SMS."); INDEX(Consom_SMS_par_mois!I:I;EQUIV(SUBSTITUE($B$13;" ";"");SUBSTITUE(Consom_SMS_par_mois!B:B;" ";"");0)); SI(ET($Q$5="Facture SMS";F25<>"Abonnement mensuel";F25<>"Frais d'abonnement et service SMS."); ""; SI(ET($Q$5<>"Facture SMS";I25=""); ""; SI(ET($Q$5<>"Facture SMS";H25<>""); H25*I25; SI(ET($Q$5<>"Facture SMS";H25=""); I25)))))); "")

🔍 Ce que fait cette formule, étape par étape :

✅ 1. Cas : Facture SMS

Si c’est une "Facture SMS" et que le produit est "Abonnement mensuel" : → Cherche la valeur totale hors taxe dans la colonne H de la feuille Consom_SMS_par_mois, à partir du client (B13 sans espaces).

Sinon, si le produit est "Frais d’abonnement et service SMS" : → Cherche la valeur dans la colonne I de la même feuille (toujours pour le même client).

Sinon, si ce n’est ni "Abonnement mensuel" ni "Frais d’abonnement" : → Ne rien afficher (""), car ces produits n’ont pas de total calculé ici.

✅ 2. Cas : Autres factures (≠ Facture SMS)

Si la cellule I25 (prix unitaire) est vide : → Pas de total ("").

Sinon, si la cellule H25 (quantité) est remplie : → Multiplie la quantité × prix unitaire.

Sinon, si la cellule H25 est vide : → On suppose qu’il s’agit d’un prix unique, donc renvoie le prix unitaire seul.

🛑 Et si une erreur survient (client non trouvé, références absentes, etc.) :

→ La cellule reste vide grâce à SIERREUR(...;"").

🧠 Conclusion :

Cette formule est adaptative :

Elle gère deux cas bien distincts (Facture SMS / Autres factures),

Elle évite les erreurs,

Elle est claire pour un usage automatisé.

Publipostage imprimer enveloppe facture

"NB : Le téléchargement comprend deux fichiers : un fichier Excel et un document Word lié nommé 'Enveloppe'. Toutefois, l'utilisation du fichier Word n'est pas obligatoire. La facture est directement générée au format PDF, avec la possibilité d'ajouter un en-tête personnalisé (au format PNG sans arrière-plan) avant son enregistrement. Ainsi, la facture peut être envoyée par e-mail sans passer par le document Word."

NB : document déjà fourni mais je vous montre la façon de le faire

Donc :

• Pas créer de fichier Word final.

• Générer l’impression directement depuis Word (via publipostage lié à Excel).

• Et l'Excel doit être fermé avant l'impression (⚡ car Word ne peut pas lire un fichier Excel ouvert en modification).

📜 Comment faire un publipostage Word avec ta feuille spécifique :

publipostage directement depuis ton fichier Excel "Facturation", en utilisant une feuille appelée "Adresse enveloppe" avec ces colonnes :

1- Vérifie ton fichier Excel "Facturation"

Dans ta feuille Adresse enveloppe, vérifie bien que :

A (Nom client)	B (Ville)	C (Adresse)
Client 1	Casablanca	123 rue X
Client 2	Rabat	456 avenue Y

        

La première ligne contient les titres des colonnes.

2- Lance Word

• Ouvre Word.

• Va dans Publipostage > Démarrer la fusion et le publipostage > Lettres.

3- Sélectionne ton fichier Excel "Facturation"

• Sélectionner les destinataires > Utiliser une liste existante.

 

• Choisis ton fichier Excel Facturation.

• Dans la fenêtre qui s'ouvre, sélectionne la feuille "Adresse enveloppe".

4. Insère les champs

 

Dans Word, écris ton texte et insère les champs :

Par exemple : 

Nom : «Nom client»

Adresse : «Adresse»

Ville : «Ville»

 

Utilise "Insérer un champ de fusion" pour mettre :

• «Nom client»

• «Adresse»

• «Ville»

5. Aperçu et impression

• Clique sur Aperçu des résultats pour vérifier.

• Puis Terminer et fusionner pour imprimer toutes les lettres ou enveloppes.

                                          

👉 Quand tu fais un publipostage depuis Excel vers Word, par défaut, Word ne saute pas automatiquement les lignes vides :
il essaie de traiter toutes les lignes, même celles qui n'ont pas de données (ça peut donner des enveloppes ou lettres vides).

Pour ignorer les lignes vides, tu as deux solutions simples :

🛠 Solution 1 : Filtrer les données dans Excel

Avant de faire ton publipostage :

Sélectionne tout ton tableau (Nom client, Ville, Adresse).

Mets un filtre automatique : Menu Données > Filtrer.

Dans la colonne "Nom client", filtre pour n'afficher que les cellules non vides.

Ainsi Word ne lira que les lignes visibles !

(Puisque Word lit ce que tu vois).

🛠 Solution 2 : Ajouter une règle dans Word

Dans Word, tu peux utiliser une règle de publipostage pour sauter une ligne si un champ est vide.

Procédure :

Dans Word, place toi où tu veux insérer par exemple «Nom client».

Clique sur Règles > Sauter l’enregistrement si.

Condition :

Nom client est vide.

➔ Résultat : Word ne fusionnera pas les lignes vides

Noms des feuilles dans VBA 

s'il y a un problème et la feuille est supprimer par mégarde il faut après la création d'une autre renommer son nom

Parameters

Param_CL_et_PRIX

Param_Liste_Des_Information

Param_Recherche_divers

Facturation

Factures_Liste

Factures_Suivie_Règlement

Factures_Fichier_Banc

Commande_gestion

Livraison_gestion

Devis_gestion

Envlope_Adresse

Calculette_monai

SMS_Client

SMS_Tarif

SMS_Consom_par_mois

Tableau_de_bord

Tableau_Traitement

Macro utiliser le nom de feuille visible	Macro utiliser son code feuille VBA
Sheets("Réglement") Sheets("Réglement").Select ActiveWorkbook.Worksheets("Réglement") If ActiveSheet.Name = "Facture" Then Set feuille = wb.Sheets("Facture")	Factures_Suivie_Règlement Factures_Suivie_Règlement.Select Factures_Suivie_Règlement If ActiveSheet Is Facturation Then Set feuille = Facturation sans wb.

rtytry