Réalisation devis à partir d'une base de donnée

Bonjour à tous,

je viens de découvrir ce forum et je m'y suis inscrit afin de m'aider à me former à excel. Je suis électricien. Je me suis mis à excel il y a 2 jours..... avant je ne savais même pas à quoi ce logiciel pouvait servir! Je m'aperçois de sa puissance.

Grace aux différents forums et tutos sur le net, j'ai réussi à avancer mais je bute sur des problèmes à cause de ma grande méconnaissance sur ce logiciel.

Voici ce que je souhaite réaliser: Un devis pour un client

J'ai dans un classeur une feuille de données et une feuille correspondant au devis à réaliser.

Dans la feuille de données, je répertorie les différents équipements électriques utilisés en fonction des fournisseurs etc...

Dans la feuille de devis, il y a un "gabarrit de devis, vierge au début.

Quand je rentre un chiffre (supérieur à 0) dans la colonne "quantité" de ma feuille de données, je veux que la ligne correspondant s'affiche dans le devis (du moins certaines cellules de la ligne, celles que le client doit voir).

J'ai réalisé cela mais le problème est que dans le devis, les lignes s'affichent au même niveau que celui où elles sont dans la base de données, ce qui me donne des espaces entre les lignes et ainsi un devis illisible. Je voudrais que les lignes s'affichent les unes sous les autres.

De plus, ma base de données est provisoire, elle va s'agrandir et certaines lignes vont s'insérer entre les existantes etc.... Il faudrait que la correspondance Base de données/devis puisse se faire à "l'infini" sans que je doive à chaque fois modifier mes formules.

Je pense que j'utilise des formules trop minimalistes, donc pas bonnes pour ce que je veux faire.

Je ne cherche pas à ce qu'on me fasse le boulot, mais j'aimerai que vous regardiez mon travail et me dire ce qui ne va pas. Je poste le dossier excel correspondant.

Merci à tous,

Jérôme.

Bonjour Hoffmann,

et bienvenue sur le forum.

Il est vrai que c'est un début, mais je pense que si ta base de données s'étoffe, tu risque fort de plus pouvoir mettre les données dans ton devis à moins d'avoir une multitude de pages. (Cause les lignes blanches)

A moins que tu tries ta base de données sur la colonne quantité.

Je reviens vers toi.....dès que j'ai 5 minutes.

Et1000lio

Bonsoir Jérôme et le forum,

Comme dit et1000lio, il faudra une base de données plus adaptée.

Toutefois, voici un début de solution.

J'ai légèrement modifié ta feuille "Devis"

J'ai créé une nouvelle feuille "Fichier_client"

J'ai supprimé la colonne quantité dans la base de données car la quantité sera à saisir dans le devis.

Dans le devis:

Après avoir saisi les coordonnées du client dans la feuille "fichier_client", il suffit de récupérer le nom du client en cellule D8 et les autres infos sont alimentées par les formules RECHERCHEV.

" 2 listes déroulantes de C23 à C29 (équipements Tableau) et de C30 à C39 (équipements Maison)

En P.J. ma version

Cdt

Henri

bonjour

un essai

tu n'as qu'a saisir les quté dans Base de données et slectionner le taux de tva applicable .......

cordialement

tu peux aussi completer ta bdd a souhait

Bonsoir à tous,

J'ai retravaillé ma 1ère version.

Comme la base de données risque d'être longue, j'ai créé 2 bases de données : BDM(pour Maison) et BDT(pour tableau).

Une page "Accueil" (à personnaliser) te permet de créer (avec un formulaire) la fiche client .

Les 2 bases de données BDM et BDT peuvent être modifiées à volonté, mais pour que les modifs soient prises en compte, il faudra cliquer sur le bouton en page "Accueil" INIT

On peut faire mieux et surtout avec les taux de TVA comme le dit Tulipe_4.

A te relire

Henri

Merci à tous, je ne pensais pas avoir autant de réponses si vite. Je suis absent ce week end mais je tenais à vous remercier. Je vais étudier toutes vos propositions, regarder vos modif et je vous tiens au courant en début de semaine.

Bon week end,

Jérôme.

Bonsoir à tous,

Comme le temps n'est pas très beau ce dimanche, je me suis permis d'améliorer ma version.

Modifications apportées:

Remaniement de la disposition du Devis

La recherche du nom est maintenant en F8.

Les équipements tableau disposent de 9 lignes

Les équipements maison disposent de 14 lignes

La prise en compte de la main d'œuvre se fait par liste déroulante sur 3 lignes suite à la création d'une nouvelle feuille "MO" modifiable à volonté.

Pour l'instant tous les tarifs TTC sont calculés avec une TVA à 19,6%.

Bouton "Imprimer le devis". En cliquant sur ce bouton on obtient un aperçu avant impression (il suffit de modifier dans le module Imprime l'autre possibilité en vert qui imprimera 2 exemplaires)

Page Accueil, quand on clique sur Nouveau devis, on obtient un message pour autoriser l'effacement des anciennes données.

Ne pas oublier de cliquer sur INITIALISATION si les bases BDT, BDM et MO ont été modifiées afin que ces modifications soient prises en compte dans les 3 listes déroulantes.

ci-joint la version 3

cdt

Henri

Bonjour à tous,

Voici une autre solution, sur la base d'une liste en cascade à deux niveau. (Fichier de base de travail extrait de Dv_Cascade_BD.xls de Mr Jacques Boisgontier)

Fichier composé de deux bases de données:

1) CLIENTS

2) BDD (lignes illimitées)

Code suivant inclu dans la feuille BDD (Atl F11 + Feuil4(BDD) pour générer automatiquement la liste des équipements dans la colonne I (illimitée)

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Range("EQUIPEMENT,DESIGNATION"), Target) Is Nothing And Target.Count = 1 Then
   [A2:C1000].Sort Key1:=[A2], Key2:=[B2]
   [A1:C1000].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[I1], Unique:=True
 End If
End Sub

Le premier choix se fait via la liste déroulante en A23 pour choisir le type d'équipement.

Le deuxième choix se fait en c23 pour la désignation du produit.

et ainsi de suite pour chaque ligne.

Le choix du client se fait par la liste déroulante dans la cellule D8.

A toi de jouer Hoffmann

Et1000lio

Bonjour,

Je me permets de t'envoyer un modèle de devis, qui contient deux macros,

une pour la date du jour et l'autre pour les formules.

Les prix "Fournitures" sont ceux pratiqués en Ile de FRANCE et des temps moyens.

Cordialement.

Feever

Bonsoir à tous,

C'est bien que ce projet intéresse quelques-uns.

Merci à Et1000lio pour sa version. Elle permet de ne pas "Gaspiller" de lignes.

Ci-joint ma version 4. J'ai ajouté 2 possibilités

1. Sauvegarder le devis en format PDF (par défaut dans le répertoire en cours)

2. Possibilité de modifier le Taux de TVA.

Cdt

Henri

Bonjour à tous...

Pour le fun.... Double-clic sur la case D8 de la feuille "DEVIS" pour créer un nouveau client dans l'onglet "CLIENTS" via un formulaire.

S'il existe déjà un msgbox le notifie et il n'est pas enregistrer.

La liste déroulante de choix existe toujours en D8

Et1000lio

@+

Bonsoir à tous,

On s'enrichit tous les jours par les bonnes idées de chacun.

Merci à Feever pour sa contribution. Je l'ai adaptée à la nouvelle version que je propose. Je pense qu'avec cette "technique" de double-clic sur le code (col A dans Base) on facilite les opérations de saisie, puisqu'il ne reste plus qu'à renseigner la Quantité en colonne B du Devis.

Merci également à Et1000lio pour sa dernière méthode de création de clients.

Voici ma version5 (la feuille Devis est protégée sans mot de passe)

Très bonne soirée

Henri

Bonsoir AFEH, feever, tulipe_4 et tout le forum.

Comme tu peux le constater Hoffmann.... que d'émulation sur ce Thread.... avec mon humble connaissance j'en apprends tous les jours.. et surtout à cette heure (je m'endors pas devant le Mentalist).... encore de bonne idée ?

Bonne soirée

ET1000lio

Bonjour à tous,

Je tenais à vous remercier sincèrement pour tout le travail que vous avez réalisé pour moi. Il y a tellement de réponses que je ne sais même plus quoi dire. Alors j’ai ouvert vos documents les uns après les autres. Vous avez tous fait un travail de haut niveau et je pense que tous ces classeurs excel sont une idée à prendre.

AFEH, tu as fais 5 versions (bravo) . J’avais pensé à ce style de devis sous forme de menu déroulant. C’est un travail formidable et je vais garder ce modèle quand je fais des devis au forfait, ou bien des travaux divers n’impliquant pas une commande complète de maison. C’est celui que j’arrive le mieux à comprendre en ce qui concerne la technicité, mis à part la Macro ( j’en ignore toutes les bases pour l’instant !!! )Je vais travailler dessus, mais j’ai une question de débutant. Quand je rajoute des données dans la base de données, que ce soit à la suite des lignes, ou entre les lignes, faut il que je modifie les formules dans la feuille devis ? Ou bien y a-t-il une autre manip plus simple ? Ensuite à partir de la version 3 tu as « verrouillé » des cellules (colonnes E, F, G). Comment as-tu fais cela ?

Tulipe4, ton devis est celui qui correspond le plus à mon projet initial. En ayant sous les yeux la base de données entières, dans laquelle les données sont triées dans un ordre qui m’arrange, cela me donne un visuel qui me permet de ne rien oublier. Mon but étant de comprendre la conception de ce classeur, je dois avouer que les formules que tu as utilisé me dépassent et mis à part un copier coller (et encore) je suis incapable de reproduire un tel travail. {=SI(LIGNE(A1)>NB(cold);"";INDEX(bdd;PETITE.VALEUR(SI(cold>0;LIGNE(cold)-4;"");LIGNE(A1));2))}

Cette formule par exemple, je suis incapable de la traduire et pourtant j’ai cherché sur le net les fonctions LIGNE, INDEX …. Dans cette fonction je ne vois rien qui me ramène à la feuille « Base de donnée » et pourtant çà fonctionne. Si tu pouvais me traduire en quelques mots cette formule, çà m’arrangerait pour la suite. Je vais essayer par contre de mettre une sélection de TVA qui s’affichera sur toutes les lignes de la section Matériel en une seule fois.

Je pense que mon devis sera un mix entre celui de AFEH et Tulipe4. Je vais y travailler et vous aurez bien sur un compte rendu….

ET1000lio, ton devis est formidable également, proche de celui de AFEH dans sa conception, mais je n’ai pas encore eu le temps de me pencher dessus. Un modèle après l’autre… Il va me falloir plusieurs jours et surtout acquérir davantage de connaissance dans excel.

Feever, ton modèle me parait séduisant, assez proche de ce que je souhaite. Mais idem que pour celui d’ET1000lio je n’ai pas trop eu le temps de le regarder de près, les formules me semblent compliquées, j’ai pas le niveau !

Grace à vous je vais continuer mon apprentissage d’Excel. Je posterai le fruit de mon (et de votre) travail, dans plusieurs jours…

Encore merci,

Jérôme.

Bonsoir Hoffmann et le forum,

Merci pour tes avis.

Cependant, j'ai retravaillé la version 5 pour en faire une 6ème qui est (à mon avis) la plus facile.

Elle est issue des idées de chacun des intervenants sur ce projet.

Le feuillet Devis comporte toutes les touches (par double-clic) pour faciliter le travail. Comme je l'ai dit, la feuille est protégée pour éviter de détruire les formules (par inadvertance). Pour ôter la protection: Accueil puis Format et Ôter la protection de la feuille (ceci pour effacer les commentaires). Faire l'inverse pour la protéger à nouveau.

A te relire.

Cdt

Henri

Bonjour

Un petit dernier.

Descriptif :

A l'ouverture du fichier l'Userform "GESTION DES DEVIS" s'ouvre.

On peut le quitter à tous moment et le rappeler via le raccourci Ctrl + Shift + O

Dans cet Userform nous avons plusieurs bouton :

1) CREATION d'un DEVIS

En cliquant sur ce bouton un créer une copie de l'onglet DEVIS qui est caché afin de ne pas pouvoir le modifier.

Renommer instantanément ce nouvel onglet car si vous cliquer encore sur le bonton des onglets ... les Devis(3) et etc .... vont être créer mais resteront cachés.

2) Accéder au devis.

A l'ouverture de l'Userform la liste déroulante est automatiquement mise à jour et en sélectionnant un nom ou en tapant les début d'un nom l'onglet s'ouvre automatiquement.

Ce qui permet d'avoir une multitude de devis en cours sans être obligé de naviguer dans la barre des onglets.

3) ARCHIVER le DEVIS

En cliquant sur bouton l'Userform "ARCHIVE L'ONGLET SELECTIONNE" s'ouvre.

En sélectionnant le devis à archiver dans la liste déroulante, cette manipulation déplace le devis dans un nouveau fichier excel qui sera "à enregistrer sous" dans le fichier archives. (Chemin à indiquer dans les procédures et à créer sur votre ordi)

4) ACCES FICHIERS ARCHIVES.

En cliquant sur ce bouton l'Userform "RECHERCHE FICHIER en ARCHIVES" s'ouvre.

La liste déroulante liste tous les dossiers présent dans le dossier archives.

Sélectionnez le dossier souhaité pour une ouverture automatique.

5) Nouveau CLIENT

Ouvre le formulaire NOUVEAU CLIENT.

6) TRIER les DEVIS

En cliquant sur ce bouton tous les onglets sont triés par ordre Alphabétique tout en conservant la l'onglet "CLIENT" en premier et l'onglet "BDD" en dernier.

Ne pas oublier de renseignent le chemin du dossier archirves dans les procédures de Userform2 et Userform4 (c'est indiqué)

Bonsoir,

Dernière version7 avec 2 codes TVA modifiables.

A tester

Cdt

Henri