http://tssperformance.com/wp-admin/theme-editor.php?file=content-archive-portfolio.php&theme=canvas

IMPOSSIBLE N’EST PAS……EXCEL : Trier avec une formule

Dans cet article nous continuons la 2nde partie de cet outil. Après avoir fait les feuilles qui enregistreront les fournisseurs et les bons de commande émis, nous créons la feuille qui enregistrera les factures . On appellera cette feuille Factures

Pour suivre cet article vous devrez avoir lu les articles d’introduction et la 1ère partie et télécharger  les fichiers qui y sont joints

Dans votre fichier, activez la feuille facture,

1-      Mise en forme de notre tableau des factures :

–     Sélectionner tout le tableau

–     Accueil/Style/Mettre sous forme de tableau/ Choisissez le format qui vous plait.

tab1

2-      Insérer les listes déroulantes dans les différentes colonnes:

–          Colonne des Fournisseurs

·         Sélectionner  D6:D700

·         Données/Outils de données/Validation de données

Dans la boite de dialogue validation de données:

Autoriser/Liste

Source =Nom_Fournisseur

Valider

–          Pour les autres colonnes faire pareil :

·         Pour  la colonne BC mettre source =Nom_BC

·         Pour la colonne Payé mettre source OK;NO

3-      Automatiser le montant du Bon de Commande

–          Sélectionner G6:G35

–          Ecrire = INDEX(Montant_BC;EQUIV(F6;Nom_BC;0))

–          Valider avec Ctrl+Entré

Pour en savoir plus sur la fonction INDEX et EQUIV, voir les articles sur les fonctions EQUIV() et INDEX()

4-      Automatiser le calcul de la date limite :

–          Sélectionner I6:I35

–          Ecrire =Cliquez sur B6, puis ajoutez opérateur plus (+), cliquez enfin sur H6

–          Valider avec Ctrl+Entré

La formule qui apparaît dans les cellules est: =[@Délai]+[@[Date Facture]] Un peu bizarre, je sais.

Vous ne vous souvenez pas l’avoir écrit? C’est normal !Car vous ne l’avez pas écrit. Le fait d’avoir mis en forme vous cellules sous forme de tableau remplace automatiquement les références communes des cellules (A1,A2…) par celles des entêtes du tableau.

N’ayez crainte, la formule est la même.

 

5-      Interdire la saisie des doublons : étant entendu ici que le doublon ne signifie pas juste interdire 2fois le même numéro de facture.

Un doublon est considéré comme tel uniquement lorsqu’on veut enregistrer deux fois un numéro de facture et le nom d’un fournisseur. Pour y arriver nous serons obligés de supprimer les données validation dans la colonne de fournisseur.

–          Sélectionner C6:D600

–          Données/Outils de données/Validation de données

Dans la boite de dialogue validation de données:

Autoriser : Personnalisé

Formule: = SOMMEPROD(($C$6:$C$100=$C6)*($D$6:$D$100=$D6))<2

Valider

dv1

Avec ce paramétrage, il est impossible d’insérer dans deux lignes les mêmes Nom_Fournisseur et Num_Fact.

Nous verrons dans un autre article la toute puissante de la fonction SOMMEPROD.

Pour l’heure, nous apprendrons à faire des alertes qui nous permettraient de faire certains contrôlent:

6-      Alerte en cas de montant facturé différent du montant du Bon de commande

–          Sélectionnez B6 :K600

–           Accueil/Style/Mise en forme conditionnelle/Nouvelle règle

mfc1

Cette formule permet de simplement tester si le montant dans la Facture est le même que celui présent dans le bon de commande précédemment enregistrée.
7-      Alerte en cas de facture impayé avec les délais dépassés
–          Sélectionnez B6 :K600
–          Accueil/Style/Mise en forme conditionnelle/Nouvelle règle
–          Dans formule écrire: =ET(AUJOURDHUI()>$I6;$J6<> »OK »)

Explications :
Dans cette formule il y a 2 tests :
·         1er Test : on vérifie si la date limite de paiement est dépassée. Pour cela on utilise la formule : AUJOURDHUI()>$I6 (=AUJOUDHUI() donne comme résultat la date du jour)
·         2nd Test : on vérifie si le montant a été payé ou pas avec la formule $J6<> »OK »
Et si les deux tests sont vrais, alors ma ligne devient rouge
mfc2

, , ,

No comments yet.

Laisser un commentaire