Tutoriels Excel | Créez votre feuille de calcul de profits et pertes automatisée

1. Introduction: Bonjour, je m'appelle George, et je suis ici pour vous apprendre les compétences pour devenir beaucoup plus compétent dans l'utilisation d'Excel. Dans ce cours, je vais vous montrer comment créer votre propre traqueur d'entreprise, feuille de calcul Profit and Loss pour élever votre entreprise et suivre votre croissance. C ou les revenus, les dépenses et les profits et pertes. Facilement. Suivez votre inventaire de produits et faites-le mettre à jour automatiquement. Enfin, sur mesure pour s'adapter à votre marque et à votre entreprise. Mais ce qui est plus important sont les compétences apprises cheveux peuvent être transférées à tant de tâches différentes dans Excel. Le cours est divisé en cinq sections et vous aurez un cahier de travail fourni pour vous permettre de suivre facilement. Je vais vous montrer comment créer des tableaux dynamiques et modifiés automatiquement en fonction des jours que vous entrez. Vous allez améliorer certaines compétences Excel fondamentales et apprendre des fonctions telles que l'utilisation de plages nommées, mise en forme conditionnelle et des tonnes de formules différentes. Ce qui a voulu mettre en valeur les cheveux était difficile d'utiliser beaucoup des fonctions Excel que vous avez disponibles dans un produit vraiment utile. Après ce cours, vous disposerez non seulement d'un outil simple et facile à utiliser pour suivre votre entreprise ? Mais vous aurez également appris des compétences que beaucoup d'utilisations XR avancées ne connaissent pas. Et maintenant, commençons. 2. Leçon 1 : Catégories: Dans cette première vidéo, nous allons créer nos catégories à utiliser dans le reste du classeur. Nous le construirons afin que vous puissiez ajouter jusqu'à 20 types de revenus, types dépenses, types de transactions et produits différents. La chose vraiment cool que vous apprendrez aujourd'hui est de créer des plages nommées dynamiques pour toutes vos catégories. La raison pour laquelle nous allons utiliser ces catégories comme listes déroulantes dans d'autres onglets. Et personne ne veut voir des tas de blancs dans la liste déroulante. Les compétences acquises diable, être transférables à de nombreux scénarios différents. Alors d'abord, ouvrez votre classeur de suivi d'entreprise et accédez à l'onglet Catégories. Alors commençons. D'abord. Créons nos types de revenus. Pour rendre les choses plus faciles. J' ai mis en évidence les cellules en orange où vous devez entrer des données pour les types de revenus va le démarrer avant. Ajoutera aux types de revenus, et nous les appellerons ventes de produits et marketing d'affiliation. Ensuite, ajoutons quelques types de dépenses. À partir de S4 entrera les frais de transaction, la phase de publicité, les coûts du produit et les frais d'expédition. Le passage à des types de transaction entrera certains types courants, tels que le revenu, les dépenses, le solde et le transfert. Maintenant, en passant à la section produits, ajoutons quelques noms de produits fictifs et catégories. En tant que démarrage entrera, non applicable à la fois dans le nom du produit et la catégorie de produit. N' a pas toutes les transactions seront liées à un produit et nous voulons cette flexibilité. Ensuite, nous allons entrer canapé, table, lit, armoires et bureau comme noms de produits dans la colonne F. Et enfin, nous allons créer les catégories suivantes dans la colonne G. Maintenant, comme mentionné précédemment, à la fin de ce cours, vous serez en mesure d'entrer plus ou je voulais dire les catégories que nous avons entrées pour rendre cela utile pour vous. Cependant, pour le cours vous assurer de garder les mêmes que ceux que j'ai entrés. Maintenant vient le bit intéressant, comment créer des plages nommées dynamiques commencera par créer une plage nommée normale. Soulignons donc avant de B203 et en nommant cette gamme de revenus de cellules en allant dans la zone d'adresse dans le coin supérieur gauche et en entrant le revenu. Maintenant, la raison pour laquelle nous créons ces plages nommées est trop facile. Reportez-vous à eux dans d'autres feuilles de calcul dans les listes déroulantes. Je vais vous montrer un exemple de liste déroulante et les avantages d'une plage nommée dynamique. Pour ceux d'entre vous qui veulent une introduction dans les gammes nommées, veuillez consulter mon cours pour débutants. J' ai une section dessus, la cellule K4, et je vais la mettre en évidence dans une couleur différente rapidement juste pour la rendre plus visible. Allez ensuite dans Validation des données et des données sous Autoriser, sélectionnez la liste. Et puis le type source. Et dix, c'est un revenu. Vous remarquerez que la liste déroulante contient beaucoup de cellules vides, ce qui n'est pas génial et peut causer des problèmes. Donc maintenant, nous allons créer une plage nommée dynamique afin que seules les cellules non vides soient nettes en utilisant la fonction de décalage. Permettez-moi de vous montrer comment cette fonction fonctionne d'abord, la fonction offset renvoie une cellule ou une plage de cellules qui est un nombre spécifié de lignes et de colonnes d'une cellule ou d'une plage de cellules. Commencez à taper un décalage égal. Parenthèse ouverte. Le premier argument est la cellule de départ que nous voulons référencer let Center avant comme exemple. Ensuite, l'argument suivant est le nombre de lignes vers le haut ou vers le bas à partir de la première cellule. Nous allons entrer 0 comme nous voulons commencer à partir de très, pour. L' argument suivant est le nombre de colonnes. Encore une fois, nous allons entrer 0 comme nous voulons référencer la colonne b. Nous allons ensuite entrer la hauteur, que nous allons rendre dynamique en utilisant le nombre une formule dans la colonne B. Count a comptera toutes les cellules qui contiennent des données. Et donc, au fur et à mesure que nous ajoutons de nouvelles catégories, cela changera. Saisissez compter a, parenthèse ouverte, puis mettez en surbrillance Colonne B, puis fermez les parenthèses. Et puisque nous avons un titre qui sera compté, donc nous entrerons minus1 après cela. Et enfin, pour la largeur, vous en entrez une car nous ne voulons qu'une seule colonne soit renvoyée. Puis fermez les parenthèses. Comme vous pouvez le voir, les seules catégories que nous entrons sont affichées. Lorsque nous ajoutons ou supprimons une catégorie, vous verrez ce qui est affiché est automatiquement mis à jour. Maintenant, mettons-nous à jour notre gamme nommée avec cette formule. Tout d'abord, accédez aux formules, puis au gestionnaire de noms. Sélectionnez la fourchette de revenu nommée, puis accédez à la zone de formule en bas. Ici, vous allez utiliser la commande Décalage pour créer une plage nommée dynamique. Donc tapez décalage égal, parenthèse ouverte, catégories, point d'exclamation. Avant, comme nous voulons référencer la feuille de calcul. Et nous allons mettre une référence absolue autour de cela dans 0 pour les arguments de ligne et de colonne. Et pour compter un, parenthèse ouverte, point d'exclamation des catégories, colonne B avec une référence absolue, puis fermer les parenthèses et un moins pour l'argument hauteur. Et enfin, un pour l'argument width. Comme un raccourci, vous auriez pu copier la formule que nous avons créée un m3 et il suffit de le coller aussi. Et enfin, cliquez sur la coche verte. Maintenant, en regardant la liste, nous pouvons voir qu'elle ne contient que les catégories que nous avons définies. Et si nous ajoutons plus, la liste se met à jour automatiquement. Nous allons maintenant créer les plages nommées restantes en commençant par la dépense. Retournez au gestionnaire nommé de la formule. Et copions la formule que nous avons utilisée pour la fourchette de revenus pour accélérer cela. Alors sélectionnez-moi. Tapez la dépense dans la formule de revenu. Modifiez la référence en C4. Et ils comptent une portée pour voir et frapper OK. Nous ferons de même pour les types de transactions. Nommera ce type de T. Et nous allons changer la référence à D4 et le nombre une gamme à D. La même chose pour le nom du produit, nommera ce nom fier. Et nous allons changer la référence à F4. Et ils comptent une gamme à F. Et enfin, la catégorie de produits nommera ce chat fier et changera la référence à G4. Et ils comptent une portée jusqu'à G. et là vous l'avez. Nous avons créé nos catégories pour être utilisées dans tout le classeur aide de plages nommées dynamiques et de la fonction de décalage dans Excel. Je vous ai également présenté d'autres fonctions telles que les listes de validation de données. Ce ne sont que quelques-uns que vous utiliserez tout au long de ce cours. Continuez à avancer. Et à la fin de cela, vous aurez une très grande feuille de calcul de suivi des profits et pertes. 3. Leçon 2 : cibles: Dans cette prochaine vidéo, nous allons créer les cibles qui relieront à nos catégories que nous avons définies dans la vidéo précédente et nous permettre de définir un objectif spécifique de chiffre d'affaires ou de dépenses par catégorie. Nous allons utiliser si les déclarations assez abondamment ici. Donc, il introduit ceux qui ne l'ont pas utilisé à une nouvelle fonction. abord, pouvez-vous ouvrir l'entreprise, suivre un classeur et accéder à l'onglet Cibles ? Avant de commencer, j'ai prérempli certaines formules comme indiqué ici. Il suffit d'additionner toutes les données que nous saisissons pour résumer les objectifs de revenus et de dépenses pour chaque période. Commençons donc par ajouter nos catégories. Nous voulons que ce soit dynamique et mis à jour à partir de l'onglet Catégories. Mais nous avons déjà fait le travail acharné et pouvons référer à l'une de nos gammes nommées que nous avons créées. Alors commençons par aller vendre PAR 13 et taper revenu égal. Vous pouvez voir que les catégories de revenus que nous avons définies sont maintenant affichées. Comme dans les vidéos précédentes. J' ai mis en évidence toutes les cellules en orange comme une cellule où l'utilisateur entre du texte libre ou des chiffres. Disons que pour les ventes de produits, nous avons un objectif de 1000 par mois. Donc nous allons écrire un 1000 en d 13. Et pour le marketing d'affiliation, nous avons un objectif de 100 par mois. Donc, entrez un 100 dans d 14 utilisera ensuite cette valeur pour calculer les cibles hebdomadaires, trimestrielles, annuelles et historiques complets. abord, nous allons utiliser une instruction if pour calculer la cible hebdomadaire, qui est le chiffre mensuel, divisé par 4.3. Comme il y a 4,3 semaines dans un mois. Allez à c 13 et entrez égal, si les parenthèses sont ouvertes. Si d 13 est vide, défini par ces guillemets, puis vide pour trouver à nouveau par les guillemets. Sinon d 13 divisé par 4.3. Et faites glisser ça vers le bas. Ensuite, le trimestriel. Allez à E 13 et entrez égal si parenthèse ouverte, le 13 est égal à vide. Puis vide. Sinon d 13 fois trois. Et l'année aller à F 13 et entrer f égal, parenthèse ouverte, d 13 est vide, puis vide. Sinon d 13 fois 12. Et enfin, toute l'histoire. Maintenant, ce sera une formule plus compliquée. Comme votre histoire complète n'est pas une constante. Ça pourrait prendre trois mois ou trois ans. Donc, nous devons être un peu intelligent cheveux pour calculer la cible pour la même période de temps. Nous devrons trouver le nombre de jours que vous avez négocié. Divisez cela par 365, puis multipliez cela par votre objectif de revenu annuel. Je vais vous aider à traverser ça maintenant. Commencez la même chose qu'avant. Allez à g 13 et entrez f égal, parenthèse ouverte, d 13 est vide, puis vide. Maintenant, afin de trouver le nombre de jours que vous avez négocié, nous pouvons utiliser les fonctions min et max sur votre historique de transactions. Tapez, max, parenthèse ouverte, puis cliquez sur les transactions que nous sommes en fait et la colonne de référence B. Et puis appuyez sur F4 une fois pour mettre une référence absolue autour de cela, sorte que lorsque nous copions cette formule, il sera toujours colonne de référence B. Ensuite, tapez soustraire, puis MIN ouvre parenthèse. Et de nouveau référencer la colonne B et appuyez sur F4 une fois pour mettre une référence absolue. Mettez ces formules entre parenthèses. Ensuite, divisez par 365, et mettez ces formules entre parenthèses à nouveau. Et enfin, multipliez par F 13 sur la feuille de calcul des cibles et fermez les parenthèses. Et juste traîner là-bas vous l'avez. Une fois les données des transactions renseignées, vos cibles d'historique complet seront automatiquement mises à jour. Faisons maintenant la même chose pour les catégories de dépenses. Tapez dépense égale dans IEEE 13 pour faire référence à la plage nommée de dépense que nous avons créée dans la vidéo précédente. Entrez ensuite les cibles de dépenses mensuelles. Nous mettrons des numéros fictifs et nous assurerons qu'ils sont négatifs. Donc mettre moins 50 par mois pour la phase des transactions, moins 200 par mois pour la publicité, phase, moins 500 par mois pour le produit, phase moins 100 par mois pour les frais d'expédition. Maintenant, pour entrer les formules, nous pouvons simplement copier les formules que nous avons faites pour le revenu. Sélectionnez C 13 TC 30 pour copier les cellules et les coller dans J 13. Faites maintenant la même chose pour les formules trimestrielles, annuelles et historiques complets. Vous pouvez voir pour l'historique complet, nous référençons toujours la colonne B. Si nous n'avons pas mis de référence absolue et copié cela sur les colonnes aurait changé. Mon cours de débutant couvre également les références absolues et relatives. Alors s'il vous plaît vérifier cela si vous avez besoin d'une introduction. Et enfin, remplissons l'objectif de revenu annuel et l'objectif de profit annuel. Pour l'objectif de chiffre d'affaires annuel, passez à F4 et tapez est égal à F 12. Pour l'objectif de bénéfice annuel, allez à L4, tapez F2 égal plus m2. Et ce sont les cibles réellement terminées. Dans cette vidéo, vous avez vu à quel point les instructions peuvent être efficaces pour effectuer diverses tâches. Ont également utilisé des formules relativement complexes, en particulier lorsque vous essayez de calculer les cibles de l'historique complet. Au cours de la prochaine session, nous examinerons les transactions dont nous avons parlé brièvement aujourd'hui. 4. Cours 3 : Transactions: Entrez. Si la parenthèse ouverte E5 est vide avec les guillemets, alors la planche. Sinon, quelques parenthèses ouvertes. Sélectionnez K4, puis I5, puis moins J5. Et entre parenthèses. Copiez ceci vers le bas à la ligne 100. Cela résume toutes vos transactions entrantes et sortantes pour fournir un montant total du solde. Maintenant, au lieu de devoir entrer des transactions manuellement, j'ai créé des transactions fictives que vous pouvez copier et coller des valeurs dans cette feuille de calcul. Pour les fichiers de transactions enregistrés dans le projet et copiez et collez les valeurs. Toutes les transactions comme psi. N' oubliez pas de ne pas coller sur la catégorie de produit ou les formules de solde total. Et là, vous l'avez. Dans cette vidéo, nous avons abordé certaines utilisations plus complexes de la validation des données. Si les instructions et les recherches V. Vous avez également maintenant une transaction de travail C, qui sera le principal moteur des prochaines feuilles de calcul. Dans la vidéo suivante, nous allons créer notre tableau de bord de stock de produits. 5. Leçon 4 : Produits: Dans cette vidéo, nous allons créer votre tableau de bord de stock de produits, construire de sorte que la feuille de calcul sera automatiquement remplie lorsque vous ajoutez des transactions. Vous n'avez pas à vous soucier de garder manuellement une trace de votre liste de produits. Il sera directement dérivé de vos transactions. abord ouvert la piste d'entreprise d'un classeur, puis accédez à l'onglet produit. Dans cette feuille de calcul utilisera des plages nommées, SUMIFS, et est des fonctions d'erreur pour s'assurer que les formules répondent à n'importe quel scénario. Et vous avez peut-être remarqué que les cellules sont surlignées en gris. Ce format suit le même que les vidéos précédentes. Les cellules grises indiquent que vous allez entrer une formule ici. Commençons donc par le nom du produit. Comme dans les vidéos précédentes, nous pouvons parcourir tous les noms de produits uniques en référençant la gamme nommée. Allez à B5 et entrez un nom fier égal. Pour les catégories de produits, nous allons entrer si vLookup formule combinaison k2, C5, et entrer f égal, parenthèse ouverte. B5 est vide. Puis vide. Sinon, ouvrez la parenthèse B5 pour la valeur de recherche, qui est le nom du produit, et les catégories, FTG pour le tableau de table. Et tirez trois colonnes deux, et ensuite optez pour une correspondance exacte. Et puis pour fermer les parenthèses. Maintenant, faites glisser cette formule vers le bas. Pour les colonnes suivantes, nous allons initialement créer une version plus simple qui calculera les chiffres en fonction de l'historique complet des transactions. Mais dans la prochaine vidéo, nous veillerons à ce qu'il soit encore plus dynamique et à ces chiffres soient mis à jour en fonction de la période sélectionnée. Pour. Maintenant, nous allons d'abord calculer le total acheté. Allez à D5 et entrez f égal, parenthèse ouverte. B5 est vide. Puis vide. Sinon, nous utilisons une formule qui nous permet de résumer les données en fonction de critères spécifiques. Entrez donc la fourchette de somme en tant que colonne Quantité dans la feuille de calcul des mouvements. Et la première plage de critères est la colonne de nom de produit. Et les critères seront le nom du produit sur le produit. Nous sommes en fait le critère suivant est la colonne de type de transaction sur la feuille de calcul des transactions. Et les critères que nous allons coder en dur dans la formule comme dépense. N' oubliez pas de mettre les guillemets doubles. Maintenant, nous allons mettre des références absolues et mélangées sur les cellules pour assurer qu'elles sont verrouillées au bon endroit lorsque nous suivons ou copions les formules. Veuillez vous référer à ma leçon sur les références de cellules. Si ça va trop vite. Et traînons ça vers le bas. Pour le total des produits vendus copiera la formule travers Comme nous avons seulement besoin de faire un petit changement. Changeons les dépenses en revenu, car toutes les cellules entreront en tant que revenu. Et traînons ça vers le bas. Pour les stocks détenus est simplement la différence entre votre tortue achetée et votre tortue vendue. Donc CO2, F5, et entrez f égal, parenthèse ouverte. B5 est vide. Puis vide. Sinon, D5, achats totaux moins E5, votre tortue a vendu. Et enfin, pour le classement des produits vendus, nous pouvons utiliser la fonction de rang dans Excel ici. Mais un problème avec la fonction de rang est qu'elle ne répond pas aux scénarios avec les nombres sont les mêmes. Il y a donc des doublons. Laissez-moi d'abord vous montrer comment fonctionne la fonction de rang. Nous irons à la cellule G5 et au rang égal, parenthèse ouverte. Le premier argument est le nombre que nous essayons de classer, c' est-à-dire le total vendu pour le premier produit. Donc, sélectionnez E5. Le deuxième argument est arrangé. Donc, sélectionnez E5 à E3, E4, et mettez en référence absolue en appuyant sur F4. Le troisième argument est l'ordre. Donc, nous voulons que le plus grand nombre ait rang un. Alors entrez 0 ici et faites glisser ceci vers le bas jusqu'à G dix. Pouvez-vous voir que le canapé et la garde-robe avaient le même nombre de cellules et le même rang. Et il nous manque aussi un rang 5 de la liste. Cela met en évidence le problème où nous essayons de classer les numéros en double. Nous pouvons contourner cela en faisant un petit ajout à la formule. Entrez plus compter si parenthèse ouverte pour votre plage dans E5 à E5, mais mettez en référence absolue autour du premier E5. Pour les critères, sélectionnez à nouveau E5, fermez les parenthèses. Et puis moins une parenthèse proche. Faites glisser ça vers le bas jusqu'à 10 g. Et comme vous pouvez le voir, le canapé et la garde-robe ont un rang différent même s'ils ont le même nombre de ventes. C' est un moyen très pratique de classer les données avec des doublons. Nous devons modifier cette formule juste un peu plus pour tenir compte des blancs. Donc, allez au début et créez une instruction if qui indique quand le nom du produit est vide, affichez un vide. Sinon, calculez le rang comme disons, et faites-le glisser vers le bas. Et c'est ainsi que vous créez un tableau de bord de stock produit dépend de vos transactions. Vous pouvez aller plus loin et ajouter des calculs pour le coût moyen du produit, ventes, les bénéfices et les valeurs de stock. Pour rendre cela encore plus utile. Dans la vidéo suivante, nous allons créer le tableau de bord principal. Ceci est la dernière vidéo du cours et reliera toutes les feuilles de calcul que vous avez créées jusqu'à présent ensemble. Ce tableau de bord contient des graphiques pour vos revenus, vos dépenses et vos produits. Il sera également entièrement dynamique, vous permettant de choisir vos périodes de temps. Et tous les graphiques et les chiffres seront mis à jour en conséquence. 6. Leçon 5 : tableau de bord principal: C' est la dernière vidéo qui rassemblera tout ce que vous avez fait dans les vidéos précédentes. Tous les graphiques et les données seront dynamiques. Et vous aurez la possibilité de modifier les périodes et de mettre à jour automatiquement tous les chiffres et graphiques. Alors commençons. Tout d'abord, pouvez-vous ouvrir le classeur de suivi d'entreprise et accéder à l'onglet principal du tableau de bord ? Cette feuille est pré-remplie avec des données simples pour vous aider à démarrer. Mais nous ferons la majeure partie du travail ensemble pour que ce tableau de bord soit opérationnel. Tout d'abord, commençons par le haut à gauche. Comme toujours, les cellules orange sont des cellules. Nous entrons du texte ou des chiffres. Les cellules bleues contiennent des zones déroulantes où vous allez sélectionner des données. Et les cellules grises seront l'endroit où vous entrez des formules. Allons à la cellule D7 et entrez la date du premier janvier 2019. Ensuite, nous allons créer la liste déroulante période de temps. Sélectionnez D8, K2, Validation des données, sélectionnez la liste. Et dans la source, nous allons entrer manuellement nos options de liste. Pour la source, nous allons entrer hebdomadaire, virgule, mensuel, virgule, annuellement et historique complet, et sélectionner OK. Comme vous pouvez le voir, ils sont maintenant disponibles dans la liste déroulante. Pour la date de début, nous devrons entrer une déclaration if pour répondre à l'option d'historique complet. Donc, si la période sélectionnée est égale à l'historique complet, nous voulons que la date de début soit le jour de la transaction le plus ancien sur la page des transactions. Sinon, nous voulons que la date de début soit celle entrée dans D7. Cela peut être fait comme suit. Entrez égal à, si les parenthèses sont ouvertes, D est égal à l'historique complet dans les guillemets. Alors entrez les hommes. Parenthèse ouverte. Accédez à la feuille de calcul des transactions et sélectionnez la colonne B. Sinon, sélectionnez D7, qui est le jour de début. Nommons également cette cellule comme le soulignement de date commence, car il est beaucoup plus facile de référencer une plage nommée. Pour la date de fin, nous aurons besoin de plusieurs déclarations if pour répondre à chaque période de temps. Alors commençons. Si la période est égale à une semaine. Et la date de fin est la date de début plus six. période de temps est égale à mensuelle. Et nous allons vouloir utiliser la fonction de date. Et l'année sera la même que la date de début. Le mois serait la date de début plus un. Et le jour sera la date commence moins1. Si la période de temps. Nous pouvons copier la même formule que nous avons utilisée pour chaque mois, mais il suffit de changer le mois à 12 mois. Et enfin, pour l'historique complet, nous prendrons l'état maximum sur la feuille de calcul de transaction. Et là, vous l'avez. C' était une formule assez longue, mais vous permet de répondre à toutes les périodes de temps. Et comme une dernière étape, nommons cette cellule comme soulignement de date. Et j'aime faire une autre petite mise à jour qui vous introduira au formatage conditionnel. Comme la plage de dates de l'historique complet ne vous obligera pas à entrer un jour de début, il serait préférable de vider ce qui est faisable dans la cellule D7 pour éviter toute confusion, cela peut être facilement fait avec la mise en forme conditionnelle. Il suffit de mettre en évidence la cellule D7. Accédez à la mise en forme conditionnelle sous l'onglet Accueil. Sélectionnez un nouveau rôle. Accédez à la formule pour déterminer les cellules à mettre en forme. Où il indique les valeurs de format où cette formule est vraie. Entrez D8 égal à l'historique dans les guillemets. Ensuite, sélectionnez Format et entré le remplissage et la couleur de la police a la même couleur. On y va avec du bleu foncé et on va frapper OK. Vous pouvez maintenant voir que lorsque l'historique complet est sélectionné, la cellule de date de début des rapports change de couleur et qu'elle n'est plus visible. Ensuite, remplissons certaines des données en bas, en commençant par le chiffre d'affaires. Allez à la cellule C42 T1. Et nous allons utiliser notre gamme de revenus nommée pour remplir les flux de revenus. Pour les cibles, nous allons devoir parcourir les cibles pour le type de chiffre d'affaires correct en fonction des périodes sélectionnées. Nous allons donc utiliser une combinaison de la fonction d'index et de la fonction de match pour ce faire. Sélectionnez l'indice égal de type 4.2.1, parenthèse ouverte. Accédez à la feuille de calcul des cibles. Et le tableau sera b 11 à G, 32 sur la feuille cible. Et mettons en référence absolue autour de cela. Pour le numéro de ligne, nous devrons rechercher le type de revenu. Donc, pour ce faire, tapez, correspondre les parenthèses ouvertes pour la valeur de recherche au tableau de bord principal et sélectionnez le type de revenu dans C41. Et nous cherchons cette valeur dans la colonne B sur la cible xi. Donc, sélectionnez b 11 à B32 comme tableau de recherche et placez une référence absolue autour de cela. Pour le type de correspondance, nous voulons une correspondance exacte. Alors mettez 0 et fermez les parenthèses. Et pour le numéro de colonne, nous sommes allés référencer la colonne qui correspond la période sélectionnée dans le tableau de bord principal. Alors entrez match, parenthèse ouverte. En tant que valeur de recherche, sélectionnez, déviez du tableau de bord principal et mettez en référence absolue autour de cela. Et pour le tableau de recherche, sélectionnez B11 à G11 et placez une référence absolue autour de cela. Pour le match, nous en voulons un exact. Alors mettez 0 et fermez deux fois les parenthèses, puis faites glisser cette formule vers le bas. Maintenant, vous remarquerez que nous avons un tas d'ADN dont nous voulons nous débarrasser. Cela peut simplement être fait avec une instruction if. Alors mettons-nous à jour la formule. Sélectionnez la cellule D 41, et au début, droite, si parenthèse ouverte, sélectionnez C 41, le type de revenu est vide, puis vide et met une autre parenthèse proche à la fin. Maintenant, si vous faites glisser cette formule vers le bas, nous nous débarrassons des NA. Pour le chiffre d'affaires réel, nous devrons utiliser la formule de somme est pour rechercher les transactions et résumer toutes les transactions fonction du type de produit pour la période spécifique. C' est un peu plus compliqué, mais je vais vous aider. Sélectionnez y4, z1 et tapez sumifs, ouvrez les parenthèses. Pour la plage estivale, accédez à la feuille de calcul des transactions et sélectionnez la colonne Revenu, colonne i, et placez une référence absolue autour de cela. Pour les critères une fourchette, sélectionnez le sous-type de transactions dans la feuille de calcul des mouvements, colonne D, puis placez une référence absolue. Quatre critères. Premièrement, revenez au tableau de bord principal et sélectionnez le type de produit dans la plage de critères C 414 pour accéder à la feuille de calcul des transactions et sélectionnez la colonne de date, colonne B et F Pour, pour une référence absolue. Pour nos critères de date, nous voulons uniquement additionner les transactions qui se situent entre une date de début et une date de fin. Nous devons donc diviser cela en deux critères distincts. Pour les critères, nous voulons seulement quelques jours qui sont supérieurs ou égaux à une date de début. Nous devrons donc entrer des guillemets. Le signe plus grand que. Égale entre guillemets, le symbole n. Et puis nous voulons référencer R étoile a. Donc, tapez la date soulignement commencer car c'était le nom du jour de début que nous avons défini dans le tableau de bord principal. Pour la plage de critères trois, sélectionnez la colonne de date, colonne b. Encore une fois, nous voulons seulement quelques jours qui sont inférieurs ou égaux à la date. Nous devrons saisir des guillemets. Le symbole inférieur à est égal aux guillemets et au symbole n. Et nous voulons faire référence à notre fin de journée. Donc, tapez date soulignement fin, car c'était le nom de la date de fin que nous avons définie sur le tableau de bord principal. Maintenant je sais que c'était beaucoup, mais on y est presque. Pour répondre aux scénarios où vous pouvez avoir des types de transaction négatifs pour le revenu, IE, les rendements ou les remboursements. Nous voulons nous assurer qu'elles sont également prises en compte. Entrez donc un signe moins, puis copiez la formule entière et collez-la sur le signe moins. Et tout ce que vous devez changer est la plage de somme de la colonne entrante, colonne i à la colonne sortante, de la colonne j. Tout comme nous l'avons fait sur la formule cible, nous ferons une mise à jour rapide afin qu'aucune donnée ne soit calculée lorsque nous n'avons pas de revenu. Donc, sélectionnez la cellule E4, t1, et à l'étoile, à droite, si parenthèse ouverte, sélectionnez le type de revenu dans C41 est vide, vide, et mettez une autre parenthèse proche à la fin. Maintenant, faites glisser cette formule vers le bas. La formule des différences est beaucoup plus simple. C' est juste votre revenu réel moins votre revenu cible. Donc, sélectionnez F4, t1 type égal, si les parenthèses ouvertes. Votre type de revenu est vide. Vide. Sinon, votre revenu réel moins votre cible. Donc E4, t1 moins t0 41. Et faites glisser ça vers le bas. Nous allons faire la même chose pour les dépenses maintenant. Allez donc H 41 et tapez dépenses égales pour remplir tous vos flux de dépenses. Pour les objectifs de dépenses, c'est semblable à la formule que nous avons utilisée pour les cibles de revenu. Allez à la cellule I4, T1, et entrez f égal, parenthèse ouverte, dont 41 est vide, puis vide. Sinon, nous allons vouloir tirer les objectifs de dépenses. Donc, entrez index, ouvrez les parenthèses pour le tableau. Accédez à la feuille de calcul des cibles et sélectionnez IL-1 à N32. Appuyez sur F4 pour une référence absolue. Pour le numéro de ligne dans Match. Parenthèse ouverte. Pour la valeur de recherche, sélectionnez 41 ans dans le tableau de bord principal. Le tableau de recherche est IL-1 à 32 dans la feuille de calcul des cibles. Appuyez à nouveau sur F4 pour une référence absolue et sur 0 pour le type de correspondance. Pour la colonne NUM. Entrez match, parenthèse ouverte. Pour la valeur de recherche, sélectionnez D8 dans le tableau de bord principal. Le tableau de recherche est 1111 dans la feuille de calcul des cibles. Et entrez des zéros pour le type de correspondance. Faites glisser cette formule vers le bas. Pour la dépense réelle. Nous pouvons simplement copier la formule de la table des revenus comme ça. Et faites glisser ça vers le bas. Et la différence peut être copiée et glisser Dan aussi. Passons maintenant aux graphiques. Commencera en haut à droite avec un simple. Cliquez avec le bouton droit sur la zone, cliquez sur Sélectionner les données , puis sélectionnez hey, H6 à j 9, et le graphique sera mis à jour. Et cliquez sur OK. Ensuite, nous allons créer le graphique pour le flux de revenus. Et je vais vous montrer comment rendre ces graphiques dynamiques afin qu'ils ignorent les cellules vides. Donc je vais d'abord montrer le problème. Si nous ne rendons pas le graphique dynamique, cliquez avec le bouton droit sur la case au-dessus de la table des revenus et cliquez sur Sélectionner les données détenait le contrôle. Sélectionnez les cellules C4, T1 à E6, puis sélectionnez C 39, t 39. Et a montré les entrées de légende état Cible et réel. Et l'axe horizontal état produit, ventes, marketing d'affiliation. Si elles sont inversées, appuyez simplement sur le bouton de la ligne et des colonnes de commutation. Alors HIT ok. Vous remarquerez que le graphique n'est pas si utile en raison de tous les blancs. Et vous ne voulez pas avoir à mettre à jour les plages chaque fois que vous ajoutez des catégories. Je vais vous montrer comment le rendre dynamique. Nous devons utiliser la fonction offset pour définir la série à côté des plages nommées. Permettez-moi donc de vous montrer la formule pour les valeurs cibles de revenu. Choisissez n'importe quelle cellule libre. Alors choisissons D6, D2 et tapons une parenthèse ouverte égale décalage. Pour la référence, sélectionnez D 41 et appuyez sur F4 pour une référence absolue. Pour les lignes dans 00 pour les colonnes, pour la hauteur dans le compte a. Et sélectionnez par défaut t1, t2, d 60, et appuyez sur F4 pour une référence absolue. Et des parenthèses proches. Puis comptez moins vide, ouvrez les parenthèses, puis sélectionnez à nouveau le 41 à D6 et appuyez sur F4 et fermez les parenthèses. Nous comptons donc le nombre de cellules qui contiennent des données, y compris les formules moins le nombre de blancs, ce qui entraîne le nombre de cellules non vides. Pour la largeur N21. Puis enfin fermer les parenthèses. Et comme vous pouvez le voir, seules les valeurs non vides des cibles de revenu sont affichées. Et c'est la formule que nous allons utiliser pour la cible d'encre nommée plage. Faisons rapidement la formule pour le revenu réel de la fourchette nommée. Copiez la formule que nous venons de faire pour les objectifs de revenu et changez les références de D à E psi. Maintenant que vous avez les formules, définissons une plage nommée. Copiez la formule cible de revenu, accédez aux formules, puis nommez le gestionnaire. Sélectionnez nouveau pour le type de nom ink. La cible quatre fait référence au collage de la formule. De la chaleur. D'accord. Maintenant, copiez la formule réelle de revenu que nous avons fait et créez la plage de noms réels d'encre, comme par exemple, les plages de noms sont créées, nous pouvons mettre à jour nos graphiques. Cliquez avec le bouton droit de la souris et allez sélectionner les données, sélectionnez la série cible et cliquez sur Modifier. Et nous devons faire est de remplacer les références de cellule à la fin cible d'encre juste après le point d'exclamation. Faites de même pour la série réelle et cliquez sur Modifier, remplacez les références de cellule à la fin par l'encre réelle juste après le point d'exclamation. Une fois que vous avez frappé d'accord, la charge est maintenant mise à jour. Nous devrons faire de même pour l'objectif de dépenses et les dépenses réelles. Copiez la formule réelle à l'encre et changez la référence de E à Je le souhaite pour calculer une fourchette dynamique pour la cible de dépense et répétez pour la dépense réelle et changez la référence de i à j. Ensuite, nous allons créer les fourchettes de noms pour la cible de dépense et la dépense réelle. Copiez la formule pour la cible de dépenses. Ensuite, allez dans Name Manager, sélectionnez un nouveau type exp target et collez la formule. Et faites la même chose pour réel comme psi. Nous allons le nommer. Et enfin, nous ferons les cartes. Tout d'abord, cliquez avec le bouton droit sur la zone et sélectionnez des Sélectionnez quelques données pour obtenir des informations renseignées et la rendre beaucoup plus facile tout en maintenant Contrôle. Sélectionnez les cellules H 41 à j 60, puis sélectionnez H 39 à J 39, assurez-vous que l'état d'entrée de légende Cible et réel et l'axe horizontal indiquent les types de dépenses. Si elles sont inversées. Appuyez simplement sur le bouton de la ligne et des colonnes de commutation. Sélectionnez la série cible et cliquez sur Modifier. Et tout ce que vous devez faire est de remplacer les références de cellule à la fin par une cible XP juste après le point d'exclamation. Faites de même pour la série réelle et cliquez sur Modifier. Remplacez les références de cellule à la fin par x Actual juste après le point d'exclamation. Et maintenant, vos graphiques de revenus et de dépenses sont dynamiques. Donc, j'ai moins la section produits jusqu'au dernier que nous avons besoin de faire une mise à jour nos formules sur le produit C. De sorte que les mises à jour basées sur la période définie. Allez à la feuille de calcul du produit et nous devrons mettre à jour les formules pour l'achat total et le total des ventes. Allons d'abord à la cellule D5 dans la colonne Achat total et ajoutons des conditions pour la période. Pour la troisième fourchette de critères. Allez dans les transactions que nous sommes en fait et sélectionnez la colonne B, puis appuyez sur F4 pour une référence absolue. Le troisième critère, entre guillemets de type supérieur à égal à guillemets. Et puis ajoutez le symbole n. Et puis référencez la date de début, qui est la date de début. Pour la quatrième plage de critères, sélectionnez à nouveau la colonne B, puis appuyez sur F4 pour une référence absolue. Et pour le quatrième critère, tapez entre guillemets, inférieurs à égaux, entre guillemets. Et puis le symbole n. Et puis référencez la date de fin, qui est appelée date. Et la fin. Cela ne sera alors que quelques-unes des transactions dans la période sélectionnée sur la page de tableau de bord principal. Allons faire glisser cette formule vers le bas. Maintenant, pour accélérer, mettre à jour le total vendu, nous pouvons copier cette partie de la formule et coller la formule à la fin, comme ça. Et puis faites glisser vers le bas. Ok, super. Maintenant que la chaleur des produits est mise à jour, nous pouvons maintenant compléter les données du produit sur le tableau de bord principal. Accédez à la feuille de calcul du tableau de bord principal et sélectionnez cellule et 41. Et ici, nous voulons tirer à travers les produits les plus vendus. Nous pouvons utiliser les fonctions d'index et de correspondance pour ce faire. Donc, tapez égal à index, ouvrez les parenthèses pour le tableau, allez à la feuille de produit et dormi colonne B, qui est la colonne de nom de produit, et appuyez sur F4 pour une référence absolue. Par conséquent, le numéro de ligne, nous allons chercher un dans le produit, donc rang colonne en utilisant la fonction de correspondance. Entrez donc correspondance, ouvrez les parenthèses pour la valeur de recherche, sélectionnez m pour T1 dans le tableau de bord principal. également mettre une référence mixte où nous voulons regarder les colonnes afin qu'il soit facile de copier en appuyant sur F4 trois fois. Pour la baie de recherche, accédez à la fiche produit et sélectionnez la colonne G et appuyez sur F4 une fois pour obtenir une référence absolue. Pour la correspondance, tapez 0, puis fermez les parenthèses. Et pour la colonne NUM, vous pouvez entrer 0 et fermer les parenthèses. Et puis vous pouvez faire glisser cette formule vers le bas. Vous remarquerez que nous obtenons des NAs car nous avons moins de dix produits. Donc, nous pouvons corriger cela en utilisant une combinaison de l'instruction if et est la fonction d'erreur. Retournez au n 41 et passez au début de la formule. Et tapez f, parenthèse ouverte est erreur, parenthèse ouverte. Et puis allez à la fin et mettez une parenthèse proche comme argument logique pour la valeur si true, entrez des guillemets comme dire et quatre, la valeur est fausse, copiez la formule d'index et collez-la ici. Cela signifie que si la formule d'index génère une erreur parce que la valeur de recherche n'existe pas, puis affichez un vide, sinon, affichez le résultat de l'index. Moins. Faites glisser ça vers le bas. Vous pouvez voir les NA disparaître. Maintenant, pour la colonne totale achetée, nous pouvons copier la formule pour les noms de produits. Et nous avons juste besoin de changer le tableau de la colonne B et la fiche produit à la colonne D. N'oubliez pas de le changer deux fois. Nous changeons le tableau en colonne D car il s' agissait de la colonne totale achetée sur la fiche produit. Ensuite, faites glisser vers le bas. Et puis juste la ligne centrale rapidement. Pour la colonne de sol total. Nous pouvons copier la formule et changer le tableau en colonne E. Et puis faire glisser vers le bas. Pour l'inventaire détenu. Nous pouvons copier la formule et changer le tableau en colonne F, puis faire glisser vers le bas. Enfin, nous mettrons à jour le tableau pour montrer que les meilleurs produits vendus rendront ces produits dynamiques de la même façon que nous l'avons fait pour le revenu et les dépenses. Pour accélérer cela, nous allons copier les formules de décalage que nous avons faites précédemment. Et nous allons changer les plages comme psi pour référencer les données des produits vendus dans la colonne P. Nous allons ensuite copier la formule, goto formules, puis le gestionnaire de noms, et ajouter une nouvelle plage de noms pour le nom, l' appeler produit vendu, et copiez la formule dans la zone de référence à. Fermez ensuite les livres du Gestionnaire de noms. Nous créerons ensuite les graphiques. Cliquez avec le bouton droit sur la zone, cliquez sur Sélectionner les données. Choisissons d'abord quelques données, la maîtrise de soi. Sélectionnez n 41 à 50, puis P 4150. Assurez-vous que les noms des produits figurent dans la case horizontale à droite. Pour la série, cliquez sur Modifier pour le nom de la série, à droite. Produits vendus. Pour les valeurs de la série. Supprimez les références de cellule et remplacez par le produit vendu, qui est la plage de noms. Juste après le point d'exclamation. Nous allons simplement supprimer toutes les formules de décalage que nous avons créées car elles ne sont plus nécessaires. Et c'est-à-dire que vous avez maintenant construit votre piste d'affaires. Une feuille de calcul, les données sont entièrement dynamiques. Vous pouvez modifier les périodes et modifier la date de début et les graphiques et les données seront mis à jour. Comme disons. J' espère vraiment que vous avez trouvé ce cours utile. Et les compétences acquises ici vous permettront de créer toutes sortes de feuilles de calcul différentes. Et donc divers problèmes dans Excel. Si vous avez des questions que vous voulez que je fasse, n'importe quel autre cours, s'il vous plaît faites-le moi savoir.