Table des matières
Lorsque l'on parle de bases de données (DB), la première chose qui vient à l'esprit, bien sûr, est toutes sortes de mots à la mode comme SQL, Oracle, 1C ou au moins Access. Bien sûr, ce sont des programmes très puissants (et coûteux pour la plupart) qui peuvent automatiser le travail d'une grande entreprise complexe avec beaucoup de données. Le problème est que parfois une telle puissance n'est tout simplement pas nécessaire. Votre entreprise peut être petite et avoir des processus commerciaux relativement simples, mais vous souhaitez également l'automatiser. Et c'est pour les petites entreprises que c'est souvent une question de survie.
Pour commencer, formulons le TOR. Dans la plupart des cas, une base de données pour la comptabilité, par exemple, les ventes classiques devrait pouvoir :
- garder dans les tableaux des informations sur les biens (prix), les transactions réalisées et les clients et relier ces tableaux les uns aux autres
- avoir à l'aise formulaires de saisie données (avec listes déroulantes, etc.)
- remplir automatiquement certaines données formulaires imprimés (paiements, factures, etc.)
- émettre le nécessaire rapports contrôler l'ensemble du processus métier du point de vue du manager
Microsoft Excel peut gérer tout cela avec un petit effort. Essayons de mettre cela en œuvre.
Étape 1. Données initiales sous forme de tableaux
Nous stockerons les informations sur les produits, les ventes et les clients dans trois tableaux (sur la même feuille ou sur des feuilles différentes – peu importe). Il est fondamental de les transformer en "tables intelligentes" avec auto-taille, pour ne plus y penser à l'avenir. Cela se fait avec la commande Formater sous forme de tableau languette Accueil (Accueil — Format sous forme de tableau). Sur l'onglet qui apparaît alors Constructeur (Conception) donner aux tableaux des noms descriptifs dans le champ Nom de la table pour une utilisation ultérieure :
Au total, nous devrions obtenir trois "tables intelligentes":
Veuillez noter que les tableaux peuvent contenir des données de clarification supplémentaires. Ainsi, par exemple, notre Prixcontient des informations supplémentaires sur la catégorie (groupe de produits, emballage, poids, etc.) de chaque produit, et le tableau Client — ville et région (adresse, NIF, coordonnées bancaires, etc.) de chacun d'eux.
lampe de table Ventes sera utilisé par nous plus tard pour y saisir des transactions terminées.
Étape 2. Créer un formulaire de saisie de données
Bien sûr, vous pouvez saisir les données de vente directement dans le tableau vert Ventes, mais cela n'est pas toujours pratique et entraîne l'apparition d'erreurs et de fautes de frappe dues au «facteur humain». Par conséquent, il serait préférable de créer un formulaire spécial pour la saisie des données sur une feuille séparée de quelque chose comme ceci :
Dans la cellule B3, pour obtenir la date-heure actuelle mise à jour, utilisez la fonction Les TDATA (MAINTENANT). Si le temps n'est pas nécessaire, alors à la place Les TDATA la fonction peut être appliquée AUJOURD'HUI (AUJOURD'HUI).
Dans la cellule B11, trouvez le prix du produit sélectionné dans la troisième colonne du tableau intelligent Prix en utilisant la fonction VPR (VLOOKUP). Si vous ne l'avez jamais rencontré auparavant, lisez et regardez d'abord la vidéo ici.
Dans la cellule B7, nous avons besoin d'une liste déroulante avec les produits de la liste de prix. Pour cela, vous pouvez utiliser la commande Données – Validation des données (La validation des données), spécifiez comme une contrainte Liste (Liste) puis entrez dans le champ Identifier (La source) lien vers la colonne Nom de notre table intelligente Prix:
De même, une liste déroulante avec des clients est créée, mais la source sera plus restreinte :
=INDIRECT("Clients[Client]")
Fonction INDIRECT (INDIRECT) est nécessaire, dans ce cas, car Excel, malheureusement, ne comprend pas les liens directs vers les tables intelligentes dans le champ Source. Mais le même lien "enveloppé" dans une fonction INDIRECT en même temps, cela fonctionne avec un bang (plus à ce sujet était dans l'article sur la création de listes déroulantes avec du contenu).
Étape 3. Ajout d'une macro de saisie des ventes
Après avoir rempli le formulaire, vous devez ajouter les données saisies à la fin du tableau Ventes. A l'aide de liens simples, nous allons former une ligne à ajouter juste en dessous du formulaire :
Ceux. la cellule A20 aura un lien vers =B3, la cellule B20 aura un lien vers =B7, et ainsi de suite.
Ajoutons maintenant une macro élémentaire de 2 lignes qui copie la chaîne générée et l'ajoute à la table Sales. Pour cela, appuyez sur la combinaison Alt + F11 ou bouton Visual Basic languette promoteur (Développeur). Si cet onglet n'est pas visible, activez-le d'abord dans les paramètres Fichier – Options – Configuration du ruban (Fichier — Options — Personnaliser le ruban). Dans la fenêtre de l'éditeur Visual Basic qui s'ouvre, insérez un nouveau module vide via le menu Insertion – Module et entrez notre code macro ici :
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copier la ligne de données du formulaire n = Worksheets("Sales").Range("A100000").End(xlUp) . Row 'détermine le numéro de la dernière ligne du tableau. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'coller dans la prochaine ligne vide Worksheets("Input Form").Range("B5,B7,B9"). Effacer le sous-formulaire de fin de ClearContents Nous pouvons maintenant ajouter un bouton à notre formulaire pour exécuter la macro créée à l'aide de la liste déroulante insérer languette promoteur (Développeur — Insérer — Bouton):
Après l'avoir dessiné, en maintenant le bouton gauche de la souris enfoncé, Excel vous demandera quelle macro vous devez lui attribuer - sélectionnez notre macro Ajouter_Vendre. Vous pouvez modifier le texte d'un bouton en cliquant dessus avec le bouton droit de la souris et en sélectionnant la commande Modifier le texte.
Maintenant, après avoir rempli le formulaire, vous pouvez simplement cliquer sur notre bouton, et les données saisies seront automatiquement ajoutées au tableau Ventes, puis le formulaire est effacé pour saisir une nouvelle transaction.
Étape 4 Relier les tables
Avant de créer le rapport, relions nos tableaux afin de pouvoir calculer rapidement les ventes par région, client ou catégorie. Dans les anciennes versions d'Excel, cela nécessiterait l'utilisation de plusieurs fonctions. VPR (VLOOKUP) pour remplacer les prix, les catégories, les clients, les villes, etc. dans le tableau Ventes. Cela nous demande du temps et des efforts, et « consomme » également beaucoup de ressources Excel. A partir d'Excel 2013, tout peut être implémenté beaucoup plus simplement en mettant en place des relations entre les tables.
Pour cela, dans l'onglet Données (Date) cliquez Rapports (Rapports). Dans la fenêtre qui apparaît, cliquez sur le bouton Création (Nouveau) et sélectionnez dans les listes déroulantes les tables et les noms de colonne par lesquels elles doivent être liées :
Un point important : les tables doivent être spécifiées dans cet ordre, c'est-à-dire table liée (Prix) ne doit pas contenir dans la colonne clé (Nom) produits en double, comme cela se produit dans le tableau Ventes. En d'autres termes, la table associée doit être une table dans laquelle vous rechercheriez des données à l'aide de VPRs'il était utilisé.
Bien sûr, la table est connectée de la même manière Ventes avec table Client par colonne commune Témoignages:
Après la mise en place des liens, la fenêtre de gestion des liens peut être fermée ; vous n'avez pas à répéter cette procédure.
Étape 5. Nous créons des rapports à l'aide du résumé
Maintenant, pour analyser les ventes et suivre la dynamique du processus, créons, par exemple, une sorte de rapport à l'aide d'un tableau croisé dynamique. Définir la cellule active sur le tableau Ventes et sélectionnez l'onglet sur le ruban Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique). Dans la fenêtre qui s'ouvre, Excel nous interroge sur la source de données (c'est-à-dire le tableau Ventes) et un emplacement pour télécharger le rapport (de préférence sur une nouvelle feuille) :
Le point essentiel est qu'il faut activer la case à cocher Ajouter ces données au modèle de données (Ajouter des données au modèle de données) en bas de la fenêtre pour qu'Excel comprenne que nous voulons construire un rapport non seulement sur la table actuelle, mais aussi utiliser toutes les relations.
Après avoir cliqué sur OK un panneau apparaîtra dans la moitié droite de la fenêtre Champs de tableau croisé dynamiqueoù cliquer sur le lien Touspour voir non seulement l'actuel, mais toutes les "tables intelligentes" qui sont dans le livre à la fois. Et puis, comme dans le tableau croisé dynamique classique, vous pouvez simplement faire glisser les champs dont nous avons besoin de toutes les tables associées dans la zone Filtre, rangées, Stolbtsov or Valeurs – et Excel créera instantanément tout rapport dont nous avons besoin sur la feuille :
N'oubliez pas que le tableau croisé dynamique doit être mis à jour périodiquement (lorsque les données source changent) en faisant un clic droit dessus et en sélectionnant la commande Mettre à jour et enregistrer (Rafraîchir), car il ne peut pas le faire automatiquement.
Aussi, en sélectionnant n'importe quelle cellule dans le résumé et en appuyant sur le bouton Graphique croisé dynamique (Graphique croisé dynamique) languette Analyse (Analyse) or Paramètres (Options) vous pouvez rapidement visualiser les résultats qui y sont calculés.
Étape 6. Remplissez les imprimables
Une autre tâche typique de toute base de données est le remplissage automatique de divers imprimés et formulaires (factures, factures, actes, etc.). J'ai déjà écrit sur l'une des façons de le faire. Ici, nous mettons en œuvre, par exemple, le remplissage du formulaire par numéro de compte :
On suppose que dans la cellule C2, l'utilisateur saisira un nombre (numéro de ligne dans le tableau Ventes, en fait), puis les données dont nous avons besoin sont extraites à l'aide de la fonction déjà familière VPR (VLOOKUP) et caractéristiques INDEX (INDICE).
- Comment utiliser la fonction VLOOKUP pour rechercher et rechercher des valeurs
- Comment remplacer VLOOKUP par les fonctions INDEX et MATCH
- Remplissage automatique des formulaires et formulaires avec les données du tableau
- Création de rapports avec des tableaux croisés dynamiques