Maison >Les sujets >excel >Partage pratique de compétences Excel : utiliser intelligemment les fonctions pour créer un tableau statistique automatique des achats, des ventes et des stocks
Dans l'article précédent « Partage pratique de compétences Excel : plusieurs opérations pratiques de « l'outil de tri de colonnes » », nous avons appris les opérations pratiques de plusieurs outils de tri. Aujourd'hui, nous allons apprendre quatre fonctions. En les utilisant habilement, nous pouvons créer un « Tableau de statistiques d'achat, de vente, d'inventaire et d'inventaire » qui peut automatiquement compter l'inventaire. Il s'agit simplement de 666. Une fois ce fonctionnement maîtrisé, vous pourrez vous libérer du travail fastidieux et profiter de la poésie et du lointain. Venez jeter un œil !
Comment réaliser un tableau d'achats, de ventes et d'inventaire capable de compter automatiquement les stocks en temps réel et les quantités entrantes et sortantes ? En fait, cela ne nécessite aucune technologie difficile. Il suffit de maîtriser quatre fonctions et d'en posséder quelques-unes. capacités de base d’édition et de composition d’Excel. Vous pouvez le créer vous-même.
Ces quatre fonctions sont : vlookup, iferror, sumif et if. Ensuite, laissez le vétéran vous guider étape par étape pour créer ce tableau d'entrée et de sortie.
Description de la fonction « Tableau des achats, des ventes, des stocks et des statistiques entrantes et sortantes » :
Fonction de statistiques en temps réel : il vous suffit d'enregistrer le tableau des flux sortants et entrants selon le format prescrit, et vous pouvez effectuer automatiquement statistiques en temps réel sur le dernier inventaire et les quantités entrantes et sortantes.
Fonction de rappel intelligent : lorsque l'inventaire d'un article est inférieur à la quantité du stock de sécurité, il sera automatiquement marqué pour obtenir un effet d'avertissement.
La composition du « Tableau des achats, des ventes, des stocks et des statistiques d'entrées et de sorties » :
Selon les besoins les plus élémentaires, la réalisation d'un tableau d'achats, de ventes, d'inventaire et d'entrées et de sorties nécessite généralement trois parties : Données de base table (également appelée table d'informations de base), table d'enregistrements entrants et sortants (également appelée table détaillée de l'eau courante), table de statistiques d'inventaire (également appelée table de requête de résultats). Ce qui suit décrit respectivement les méthodes de ces trois parties.
1. Tableau de données de base
Conçu en fonction des besoins réels de l'entreprise, en saisissant un principe de base, le tableau doit être capable de refléter tous les attributs de l'article, et chaque attribut doit être stocké dans un colonne. Le tableau n’a pas besoin d’être beau et les cellules fusionnées ne doivent pas apparaître.
Par exemple, l'image ci-dessous est un tableau de données de base relativement standardisé :
Remarque : Le numéro de série n'est pas nécessaire, juste pour faciliter la recherche, utilisez généralement les codes de produit comme seule base. entreprise Si le produit n'a pas de code, le numéro de série peut être utilisé comme code.
Afin de garantir l'exactitude des données statistiques, lorsqu'il y a de nouveaux produits, des enregistrements doivent être ajoutés au tableau. S'il y a des produits obsolètes, il n'est pas nécessaire de supprimer les enregistrements d'origine.
2. Feuille d'enregistrement entrant et sortant
Habituellement, les documents sortants et entrants sont divisés en deux feuilles pour le stockage, ou ils peuvent être stockés ensemble Pour des raisons de commodité, nous les combinerons ensemble pour un exemple.
Les colonnes de données du tableau doivent contenir des informations de base sur le produit ainsi que la date et la quantité des entrepôts entrants et sortants. Le format est grossièrement :
Dans le tableau de flux ci-dessus, il n'y a que des A, E bleus. , F et G. Les colonnes doivent être enregistrées en temps opportun. Les informations de base telles que les colonnes de classification, de nom et d'unité sont automatiquement générées via des formules. Vous devez l'avoir deviné, il est temps pour vlookup d'entrer en scène !
Oui, c'est à ce moment-là que vlookup entre en jeu. Sur l'image ci-dessous, vous pouvez voir que les trois colonnes après l'encodage sont toutes obtenues à l'aide de la fonction vlookup. La formule dans la cellule B2 est : =VLOOKUP($A2, table de données de base !$B:$E,COLUMN(B1),0)=VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0)
公式解读：vlookup一共需要四个参数，基本格式为
=vlookup(查找值，查找区域，列数，精确查找)
第一个参数$A2表示想要查找的内容，注意因为公式要右拉下拉，因此在A前面加了$对列进行锁定，防止右拉时发生错误；
第二个参数基础数据表!$B:$E表示要查找的区域（文章前面介绍的基础数据表），注意这个区域是以编码为首列的，因为编码在基础数据表的B列，所以区域也是从B列开始而不是从A列开始，这一点一定要记住，因为很多新手使用vlookup都在这个地方犯了错误；
第三个参数表示返回的内容为查找区域的第几列，因为公式要右拉，所以我们使用column(B1)作为返回列数。
column的作用是得到参数的列号。我们要返回基础数据表$B:$E 中的C列即第2列， 在整个参数基础数据表中B1单元格的列号是2，因此这里用column(B1)
=vlookup(valeur de recherche, zone de recherche, nombre de colonnes, recherche exacte)🎜 $A2 représente le contenu que vous souhaitez rechercher. Notez que, comme la formule doit être déroulée vers la droite, ajoutez <code>$ devant A pour verrouiller la colonne afin d'éviter les erreurs lors de l'extraction vers la droite ; 🎜🎜🎜🎜Deuxième paramètre Tableau de données de base !$B:$E représente la zone à rechercher (le tableau de données de base introduit plus tôt dans l'article). première colonne, car le codage est dans la colonne B du tableau de données de base, donc la zone commence également à partir de la colonne B au lieu de la colonne A. Il faut s'en souvenir, car de nombreux novices font des erreurs à cet endroit lorsqu'ils utilisent vlookup 🎜🎜🎜🎜Le ; le troisième paramètre représente le retour. Le contenu est le numéro de colonne de la zone de recherche. Parce que la formule doit être tirée vers la droite, nous utilisons column(B1) comme numéro de colonne renvoyé. La fonction de 🎜🎜column est d'obtenir le numéro de colonne du paramètre. Nous voulons renvoyer la colonne C dans la table de données de base $B:$E, qui est la colonne 2. Le numéro de colonne de la cellule B1 dans l'ensemble de la table de données de base des paramètres est 2, donc colonne est utilisé ici. (B1)Indique le nombre de colonnes à retourner. Lorsque la formule est tirée vers la droite, B1 devient C1 et le numéro de colonne passe de 2 à 3, atteignant ainsi l'objectif de tirer vers la droite d'une formule pour compléter plusieurs références de colonnes. 🎜🎜🎜🎜Le dernier paramètre 0 signifie recherche exacte. 🎜
Les trois dernières colonnes du tableau, la date et la quantité entrante et sortante, peuvent être enregistrées en fonction de la situation réelle. Dans des circonstances normales, cet organigramme est complété, mais afin de l'utiliser plus intelligemment, la partie vlookup peut également être optimisée.
Lorsque nous saisissons le code produit, il peut y avoir une erreur dans la saisie (ou un nouveau code qui n'est pas dans le tableau des données de base), et nous obtiendrons alors des codes tronqués :
L'effet n'a pas très belle, Par conséquent, une autre fonction iferror est nécessaire pour coopérer avec vlookup pour résoudre ce problème. La formule est modifiée comme suit : =IFERROR(VLOOKUP($A2, basic data table!$B:$E,COLUMN(B1). ),0)," Veuillez vérifier si l'encodage est erroné ! ")=IFERROR(VLOOKUP($A2,基础数据表!$B:$E,COLUMN(B1),0),"编码有误请核查！")
可能有些朋友是第一次见到iferror这个函数，简单介绍一下：
=iferror(公式，公式结果错误时显示的内容)，公式只需要两个参数，第一个参数是一个公式，第二个参数是当公式结果错误时需要显示的内容。以本例来说，第一参数就是vlookup，当vlookup的结果正确时，iferror不发生作用，但是当vlookup的结果错误时，就会显示需要的内容，本例是显示了一串文字：编码有误请核查！注意：如果要显示的内容是文本一定要加引号。
三、库存统计表
这个库存统计表的功能是对所有产品的库存情况进行实时显示，大致有以下一些信息：累计出库数量、累计入库数量、当前库存数量；如果需要进行缺货提示的话还需要一个安全库存数量以及是否缺货的内容。
这个统计表并不需要单独再建立一个sheet，只需要在基础数据表的后面添加刚才列出来的这些内容就OK了，格式如下图所示：
可以看到，在基础数据表后面增加了六列内容，其中只有初始库存和安全库存数是需要录入的，累计出库数量、累计入库数量和是否缺货都是通过公式来实现的，以下对这些字段做个简要的说明：
初始库存：也可以叫做库存结转，在启用这个出入库统计表的时候对原有库存进行记录。
累计出库数量（G列）：使用公式=SUMIF(出入库记录表!A:A,B2,出入库记录表!F:F)统计所得：
公式解析：sumif函数需要三个参数，基本结构为=SUMIF(条件区域, 条件, 求和区域)
第一个参数出入库记录表!A:A表示条件列；
第二个参数B2表示前面条件列应该满足的条件（对应该行物品编码）；
第三个参数出入库记录表!F:F表示对满足条件的在此列求和。
同样的方法将第三个参数出入库记录表!$F:$F换成出入库记录表!$G:$G得到累计入库数量（H列）：
当前库存数量：用初始库存-累计出库数量+累计入库数量 即可；
安全库存数量：本例中都设置的是50，可以根据每个产品的情况进行确定。此项需要手工输入。
是否缺货：这里用到了IF函数，公式为：=IF(I2>J2,"","缺货")
If函数的基本格式为if(条件, 成立时需要的结果, 不成立时需要的结果)；
本例中条件为I2>J2
C'est peut-être la première fois que certains amis voient la fonction iferror. Permettez-moi de la présenter brièvement :
=iferror (formule, le contenu affiché lorsque le résultat de la formule est erroné) , la formule ne nécessite que deux paramètres, le premier paramètre est une formule et le deuxième paramètre est le contenu qui doit être affiché lorsque le résultat de la formule est erroné. Dans cet exemple, le premier paramètre est vlookup. Lorsque le résultat de vlookup est correct, iferror n'a aucun effet. Mais lorsque le résultat de vlookup est erroné, le contenu requis sera affiché. L'encodage a Veuillez vérifier par erreur ! Remarque : Si le contenu à afficher est du texte, il doit être placé entre guillemets. 
3. Tableau des statistiques d'inventaire
La fonction de ce tableau de statistiques d'inventaire est d'afficher l'état de l'inventaire de tous les produits dans en temps réel. Il existe à peu près les informations suivantes : la quantité sortante cumulée, la quantité entrante cumulée et la quantité de stock actuelle. Si une invite de pénurie est requise, une quantité de stock de sécurité et s'il est en rupture de stock sont également requises ;
🎜Ce tableau statistique n'a pas besoin de créer une feuille séparée. Il vous suffit d'ajouter le contenu qui vient d'être répertorié à la fin du tableau des données de base : 🎜🎜
🎜🎜Vous pouvez voir que six sont ajoutés après le tableau de données de base Contenu de la colonne, seules la quantité de l'inventaire initial et du stock de sécurité doivent être saisies. La quantité sortante cumulée, la quantité entrante cumulée et si elle est en rupture de stock sont toutes réalisées à l'aide de formules. Voici une brève explication de ces champs : 🎜🎜Initial. Inventaire : il peut également être appelé report d'inventaire. L'inventaire d'origine est enregistré lorsque ce tableau de statistiques d'inventaire entrant et sortant est activé. 🎜🎜Quantité sortante cumulée (colonne G) : Utilisez la formule =SUMIF (table des enregistrements sortants et sortants ! A:A, B2, table des enregistrements sortants et sortants ! F:F) pour obtenir des statistiques : 🎜 🎜
🎜🎜Analyse de formule : fonction sumif Trois paramètres sont requis, et la structure de base est =SUMIF (zone de condition, condition, zone de sommation)🎜Table d'enregistrements entrants et sortants ! A:A représente la colonne de condition 🎜🎜B2 représente la condition que la condition précédente ; la colonne doit être remplie (correspondant à cette ligne Code article); 🎜🎜Table des enregistrements entrants et sortants ! F : F signifie la somme des colonnes qui remplissent les conditions. 🎜🎜🎜🎜De la même manière, remplacez le troisième paramètre Table des enregistrements entrants et sortants !$F:$F par Table des enregistrements entrants et sortants !$G:$G pour obtenir la quantité totale entrante (colonne H) : 🎜🎜🎜🎜Quantité d'inventaire actuelle : Utiliser l'inventaire initial - quantité sortante cumulée + quantité entrante cumulée 🎜🎜
🎜🎜Quantité du stock de sécurité : Dans cet exemple, 50 est fixé, qui peut être déterminé en fonction de la situation de chaque produit. Cet élément doit être saisi manuellement. 🎜🎜Est-ce en rupture de stock : La fonction SI est utilisée ici, la formule est : =IF(I2>J2,"","Rupture de stock")🎜🎜
🎜🎜Le format de base de la fonction If est if(condition, establishment Le résultat requis lorsque , le résultat requis lorsqu'il n'est pas établi) 🎜🎜Dans cet exemple, la condition est I2>J2, c'est-à-dire qu'elle On estime que lorsque la quantité actuelle du stock est supérieure à la quantité du stock de sécurité, un blanc est obtenu. Quoi qu'il en soit, vous obtenez le mot « en rupture de stock ». 🎜🎜En même temps, définissez une mise en forme conditionnelle pour cette colonne. En cas de pénurie, utilisez la couleur pour obtenir un effet accrocheur. 🎜🎜La méthode de paramétrage consiste à sélectionner la colonne k, à cliquer sur [Mise en forme conditionnelle] → [Mettre en surbrillance les règles de cellule] → [Égal à] : 🎜🎜🎜🎜🎜Entrez le mot en rupture de stock dans la case de gauche et sélectionnez ce dont vous avez besoin. à droite Une fois l'effet obtenu, confirmez. 🎜
À ce stade, un tableau statistique automatique d'entrée et de sortie peut être facilement mis en place ! Avec cet outil, vous n'avez plus à vous soucier d'un mauvais calcul de l'inventaire de l'entrepôt de milliers d'articles. Une fois que vous constatez qu'il y a une pénurie, vous pouvez dire au service des achats de l'acheter, et l'efficacité s'est également améliorée !
Enfin, je voudrais souligner que les idées de conception pour des tableaux de statistiques entrants et sortants similaires sont à peu près les mêmes. Certaines optimisations peuvent être effectuées en fonction de l'application réelle. S'il existe un prix unitaire et d'autres informations, elles peuvent être ajoutées. au tableau des données de base, puis utilisé Quantité * prix unitaire pour obtenir le montant.
C'est une bonne idée d'utiliser la validité des données pour standardiser la saisie des données. Par exemple, si l'encodage doit être unique, vous pouvez définir la validité pour éviter une saisie répétée (vous pouvez laisser un message si vous ne savez pas comment procéder). fais-le encore).
Définissez la protection des formules pour éviter que des erreurs d'opération ne détruisent les formules et n'affectent l'exactitude des données, etc...
Recommandations d'apprentissage associées : Tutoriel Excel
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!