L'analyse ABC est l'une des principales méthodes de gestion et de logistique. Avec cela, vous pouvez classer les ressources de l'entreprise, des biens, des clients, etc. Selon le degré d'importance. Dans le même temps, au niveau de l'importance, chacune de l'unité ci-dessus est attribuée à l'une des trois catégories: A, B ou C. Excel Program contient dans ses outils de bagages qui facilitent la réalisation de ce type d'analyse. Trouvons comment les utiliser et quelle est l'analyse ABC.
Utilisation d'une analyse ABC
L'analyse ABC est une sorte d'amélioration et adaptée aux conditions modernes du principe Pareto. Selon la méthode de sa conduite, tous les éléments de l'analyse sont divisés en trois catégories en fonction du degré d'importance:- Catégorie A - éléments ayant une combinaison de plus de 80% de la gravité spécifique;
- Catégorie B - éléments dont la combinaison varie de 5% à 15% de la gravité spécifique;
- Catégorie C - Les éléments restants, dont l'agrégat total est de 5% et une gravité moins spécifique.
Les entreprises distinctes utilisent des techniques plus avancées et des articles de division non à 3, mais de 4 ou 5 groupes, mais nous nous fierrons sur le programme classique d'analyse ABC.
Méthode 1: Analyse avec le tri
Excel ABC L'analyse est effectuée à l'aide de tri. Tous les articles sont triés de gras à moins. Ensuite, la part cumulative de chaque élément est calculée sur la base desquelles il est attribué une certaine catégorie. Découvrons comment la méthodologie spécifiée est utilisée dans la pratique.
Nous avons une table avec une liste de produits que la société vend et le nombre de revenus correspondant de leur vente pendant une certaine période. Au bas de la table, le résultat des revenus en général sur tous les noms des marchandises. Il existe une tâche utilisant l'analyse ABC, cassons ces marchandises en groupes par leur importance pour l'entreprise.
- Nous mettons en évidence une table avec le curseur en fermant le bouton gauche de la souris, à l'exclusion du capuchon et de la chaîne finale. Aller à l'onglet "Données". Nous cliquons sur le bouton "Trier", situé dans la barre d'outils "Trier et filtrer" sur la bande.
Vous pouvez aussi faire différemment. Nous allouons la plage de table ci-dessus, puis passons à l'onglet "Accueil" et cliquez sur le bouton "Trier et filtrer" dans la boîte à outils de montage sur la bande. La liste est activée dans laquelle la position "Tri personnalisable" est activée.
- Lors de l'application de l'une des actions ci-dessus, la fenêtre Paramètres de tri se lance. Nous avons l'air de sorte que les paramètres "Mes données contiennent le paramètre Headers" ont été définis sur une coche. Dans le cas de son absence, installez.
Dans le champ "colonne", spécifiez le nom de la colonne dans laquelle les données de revenus sont contenues.
Dans le champ «Trier», vous devez spécifier, sur lequel des critères spécifiques seront triés. Laissez les paramètres préréglés - «Valeurs».
Dans le champ "Commander", expose la position "décroissante".
Après le produit des paramètres spécifiés, cliquez sur le bouton "OK" en bas de la fenêtre.
- Après avoir effectué l'action spécifiée, tous les éléments ont été triés par des revenus de plus à un plus petit.
- Nous devrions maintenant calculer la proportion de chacun des éléments pour un résultat général. Créez une colonne supplémentaire à ces fins, que nous appelons «Poids partage». Dans la première cellule de cette colonne, nous mettons le signe "=", après quoi vous spécifiez un lien vers la cellule dans laquelle se trouve la quantité de revenus de la mise en œuvre du produit concerné. Ensuite, définissez le signe de la division ("/"). Après cela, nous indiquons les coordonnées de la cellule, qui contient le montant total de la vente de marchandises dans toute l'entreprise.
Compte tenu du fait que la formule spécifiée nous copierons sur d'autres cellules de la colonne "Partager" par le marqueur de remplissage, l'adresse du lien vers l'élément contenant le montant final des revenus à l'entreprise, nous devons corriger. Pour ce faire, faites un lien absolu. Sélectionnez les coordonnées de la cellule spécifiée dans la formule et appuyez sur la touche F4. Avant les coordonnées, comme on le voit, un signe dollar est apparu, ce qui indique que le lien est devenu absolu. Il convient de noter que la référence au montant des revenus du premier dans la liste des marchandises (produit 3) devrait rester relatif.
Ensuite, pour effectuer des calculs, appuyez sur la touche ENTER.
- Comme nous le voyons, la proportion de revenus du premier produit spécifié dans la liste est apparue dans la cellule cible. Pour copier la formule dans la plage ci-dessous, nous mettons le curseur dans le coin inférieur droit de la cellule. Sa transformation dans le marqueur de remplissage, ayant un look d'une petite croix. Nous cliquons sur le bouton gauche de la souris et faites glisser le marqueur de remplissage vers la fin de la colonne.
- Comme vous pouvez le constater, la colonne entière est remplie de données caractérisant la proportion de revenus de la mise en œuvre de chaque produit. Mais la valeur de la gravité spécifique est affichée au format numérique et nous devons le transformer en pourcentage. Pour ce faire, mettez en surbrillance le contenu de la colonne "Poids spécifique". Ensuite, nous passons à l'onglet "Accueil". Sur la bande dans le groupe Paramètres du groupe, un champ affichant le format de données. Par défaut, si vous n'avez pas produit de manipulations supplémentaires, le format "général" doit être installé là-bas. Cliquez sur l'icône sous la forme d'un triangle situé à droite de ce champ. Dans la liste des formats, sélectionnez la position "Pourcentage".
- Comme nous le voyons, toutes les valeurs de colonne ont été transformées en valeurs de pourcentage. Comme il se doit, 100% indiqués dans la chaîne de "Total". La part des marchandises est attendue dans la colonne d'une plus grande à une plus petite.
- Nous devrions maintenant créer une colonne dans laquelle la part accumulée avec un résultat croissant serait affichée. C'est-à-dire que dans chaque ligne du poids spécifique individuel d'un produit spécifique sera ajouté à la proportion de tous ces produits situés dans la liste ci-dessus. Pour les premiers produits de la liste (produit 3), la proportion individuelle et la part accumulée seront égales, mais toutes les personnes suivantes à l'indicateur individuel devront ajouter la part accumulée de l'élément précédent de la liste.
Donc, dans la première ligne, nous sommes transférés à la colonne "Share accumulé" l'indicateur de la colonne "spécifique".
- Ensuite, réglez le curseur sur la deuxième cellule de la colonne "Share accumulé". Nous devons ici appliquer la formule. Nous mettons le signe "égal" et plier le contenu de la cellule "la proportion" de la même ligne et le contenu de la cellule "Share accumulé" de la chaîne ci-dessus. Toutes les références sont réservées sont réservées, c'est-à-dire que nous ne produisons aucune manipulation avec eux. Après cela, effectuez un clic sur le bouton ENTER pour afficher le résultat final.
- Maintenant, vous devez copier cette formule dans les cellules de cette colonne, qui sont placées ci-dessous. Pour ce faire, nous utilisons le marqueur de remplissage sur lequel nous avons déjà recours lors de la copie de la formule de la colonne Partager. Dans le même temps, la chaîne "Total" n'est pas nécessaire, car le résultat accumulé est de 100% sera affiché sur le dernier produit de la liste. Comme vous pouvez le constater, tous les éléments de notre colonne après celles-ci étaient remplis.
- Après cela, créez une colonne "groupe". Nous devrons regrouper des marchandises par catégorie A, B et C en fonction de la part accumulée spécifiée. Comme nous nous souvenons, tous les éléments sont distribués par des groupes selon le schéma suivant:
- A - jusqu'à 80%;
- B - les 15% suivants;
- C - les 5% restants.
Ainsi, tous les biens qui accumulèrent la part du poids spécifique se situent à la frontière allant jusqu'à 80%, nous attribuons la catégorie A. Les marchandises avec le poids spécifique accumulé de 80% à 95% sont attribuées à la catégorie B. Les restants Groupe de marchandises ayant la valeur de plus de 95% de la gravité spécifique accumulée que nous assignons une catégorie C.
- Pour plus de clarté, vous pouvez remplir les groupes spécifiés avec différentes couleurs. Mais c'est à volonté.
Ainsi, nous avons enfreint les éléments sur le niveau d'importance, en utilisant une analyse ABC. Lorsque vous utilisez d'autres techniques, comme mentionné ci-dessus, il est utilisé pour briser un plus grand nombre de groupes, mais le principe de briser dans ce cas reste presque inchangé.
Leçon: Tri et filtrage dans Excel
Méthode 2: Utilisation d'une formule complexe
Bien entendu, l'utilisation du tri est le moyen le plus courant de mener une analyse ABC dans Excele. Mais dans certains cas, il est nécessaire de mener à bien cette analyse sans réorganiser des lignes dans des endroits dans la table source. Dans ce cas, une formule complexe viendra à la rescousse. Par exemple, nous utiliserons la même table source que dans le premier cas.
- Nous ajoutons à la table source contenant le nom des marchandises et des revenus de la vente de chacun d'eux, la colonne "Groupe". Comme vous pouvez le constater, dans ce cas, nous ne pouvons pas ajouter de colonnes avec le calcul des fractions individuelles et accumulatives.
- Nous produisons la première allocation de cellules dans la colonne Groupe, après quoi vous effectuez un clic sur la touche "Insérer la fonction" situé près de la ligne de formule.
- Master Activation des fonctions. Nous passons à la catégorie "Liens et tableaux". Sélectionnez la fonction "choix". Nous faisons un clic sur le bouton "OK".
- La fenêtre de l'argument de jeu est activée. La syntaxe est présentée comme suit:
= Sélection (Number_intex; valeur1; valeur2; ...)
La tâche de cette fonctionnalité est le retrait de l'une des valeurs spécifiées, en fonction du numéro d'index. Le nombre de valeurs peut atteindre 254, mais nous n'aurons besoin que de trois noms correspondant aux catégories d'analyse ABC: A, B, C. Nous pouvons entrer immédiatement dans le symbole "A" dans le champ "B" dans "B "Champ, champ" valeur3 "-" c ".
- Mais avec l'argument "Numéro d'index" devra être soigné à fond en construisant plusieurs opérateurs supplémentaires. Installez le curseur dans le champ "Numéro d'index". Ensuite, cliquez sur l'icône avec une vue du triangle, à gauche du bouton "Insérer une fonction". Une liste des opérateurs nouvellement utilisés s'ouvre. Nous avons besoin d'une fonction de la recherche. Comme il n'est pas dans la liste, nous cliquons sur l'inscription "Autres fonctions ...".
- La fenêtre de la fenêtre de l'assistant de fonctions est à nouveau démarré. Encore une fois, allez à la catégorie "Liens et tableaux". Nous trouvons la position de la «carte de recherche», allouez-la et faites un clic sur le bouton «OK».
- Les arguments des arguments de l'opérateur de recherche s'ouvrent. La syntaxe de celle-ci a le formulaire suivant:
= Board de recherche (Nom de recherche; Affichage__massif; type_station)
Le but de cette fonction est de définir le numéro de position de l'élément spécifié. C'est-à-dire que ce dont nous avons besoin pour le champ "numéro d'index" est un choix.
Dans le champ "Array de liste", vous pouvez immédiatement demander l'expression suivante:
{0: 0,8: 0,95}
Il devrait être précisément entre crochets, en tant que formule de tableau. Il n'est pas difficile de deviner que ces chiffres (0; 0,8; 0,95) désignent les limites de la part accumulée entre les groupes.
Le champ "type de comparaison" n'est pas obligatoire et dans ce cas, nous ne le remplirons pas.
Dans le champ "Deuxième valeur", définissez le curseur. Ensuite, à travers l'icône décrite ci-dessus sous la forme d'un triangle, nous passons à l'assistant des fonctions.
- Cette fois dans l'assistant de fonctions, nous faisons passer à la catégorie "Mathématique". Choisissez le nom "Silent" et appuyez sur le bouton "OK".
- La fenêtre Arguments de fonction sera lancée. L'opérateur spécifié résume les cellules qui répondent à la condition définie. Sa syntaxe est:
= Silencieux (plage; critère; plage_suming)
Dans le champ "Range", entrez l'adresse de la colonne "Revenue". À ces fins, nous définissons le curseur dans le champ, puis en faisant le clip du bouton gauche de la souris, sélectionnez toutes les cellules de la colonne correspondante, à l'exclusion de la valeur "Total". Comme vous pouvez le constater, l'adresse est immédiatement apparue sur le terrain. De plus, nous devons rendre ce lien absolu. Pour ce faire, produire son allocation et cliquez sur la touche F4. L'adresse a été publiée par des signes dollar.
Dans le champ "critère", nous devons définir une condition. Entrez l'expression suivante:
">"&
Ensuite, immédiatement après, nous entrons dans l'adresse de la première cellule de la colonne "Revenue". Nous faisons des coordonnées horizontalement dans cette adresse absolue, ajoutant le signe dollar du clavier devant la lettre. Les coordonnées verticales laissent un parent, c'est-à-dire qu'il ne devrait y avoir aucun signe devant le chiffre.
Après cela, nous n'appuyez pas sur le bouton "OK" et cliquez sur le nom de la fonction de recherche dans la chaîne de formule.
- Ensuite, nous retournons dans la fenêtre Arguments de la planche de recherche. Comme nous le voyons, dans le champ "Signification Fogulaire", les données définies par l'opérateur étaient silencieuses. Mais ce n'est pas tout. Allez dans ce champ et ajoute déjà aux données disponibles pour ajouter un signe "+" sans guillemets. Ensuite, nous introduisons l'adresse de la première cellule de la colonne "revenus". Et encore une fois, nous fabriquons des coordonnées horizontalement avec absolue et verticalement laissez-les relatifs.
Ensuite, nous prenons tout le contenu du champ "la valeur souhaitée" entre crochets, après quoi nous avons mis le signe de la division ("/"). Après cela, à nouveau à travers l'icône Triangle, allez à la fonction de sélection de fonctions.
- Comme la dernière fois dans l'assistant de fonctionnement en cours d'exécution, nous recherchons l'opérateur nécessaire dans la catégorie "mathématique". Cette fois, la fonction souhaitée s'appelle "SUMS". Nous en soulignons et appuyez sur le bouton "OK".
- Les arguments de l'opérateur Fenêtre ouvre. Son objectif principal est la somme de données dans les cellules. La syntaxe de cet opérateur est assez simple:
= Somme (numéro1; numéro2; ...)
Pour nos besoins, seul le champ "numéro1" sera nécessaire. Nous en introduisons les coordonnées de la colonne "revenus", éliminant la cellule contenant les résultats. Nous avons déjà effectué une telle opération dans le champ "gamme" de la fonction. Comme à ce moment-là, les coordonnées de la plage sont absolues, en les mettant en surbrillance et appuyant sur la touche F4.
Après cela, cliquez sur la touche "OK" en bas de la fenêtre.
- Comme vous pouvez le constater, le complexe des fonctions entrées a effectué un calcul et a émis le résultat dans la première cellule de la colonne "Groupe". Le premier produit a été attribué à un groupe "A". La formule complète appliquée par nous pour ce calcul est la suivante:
Choix (planche de recherche ((silencieux ($ B $ B $ B $ B $ 27; ">" & $ B2) + $ B2) / montants ($ B $ B $ 27); {0: 0,80 $ B $ B $ , 95}); "A"; "B"; "C")
Mais, bien sûr, dans chaque cas, les coordonnées de cette formule seront différentes. Par conséquent, cela ne peut pas être considéré comme universel. Mais, en utilisant le manuel qui a été indiqué ci-dessus, vous pouvez insérer les coordonnées de n'importe quelle table et appliquer avec succès cette méthode dans n'importe quelle situation.
- Cependant, ce n'est pas tout. Nous avons calculé uniquement pour la première rangée de la table. Afin de remplir pleinement la colonne du groupe "Groupe", vous devez copier cette formule dans la plage ci-dessous (éliminant la cellule de la chaîne "Total") à l'aide du marqueur de remplissage, comme nous l'avons déjà fait plus d'une fois. Une fois les données effectuées, l'analyse ABC peut être considérée comme effectuée.
Comme nous le voyons, les résultats obtenus à l'aide de l'option à l'aide d'une formule complexe ne diffèrent pas de ces résultats que nous avons effectués par tri. Toutes les marchandises sont attribuées aux mêmes catégories, mais seulement en même temps que les lignes n'ont pas changé leur position initiale.
Leçon: Maître des fonctions dans Excele
Le programme Excel est capable de réduire considérablement l'analyse ABC pour l'utilisateur. Ceci est réalisé en utilisant un tel outil tel que le tri. Après cela, la gravité spécifique individuelle, la part accumulée et, en fait, la partition en groupes est calculée. Dans les cas où la variation de la position initiale des lignes de la table n'est pas autorisée, vous pouvez appliquer la méthode à l'aide d'une formule complexe.