Un dels mètodes clau de gestió i logística és l'anàlisi ABC. Amb ell, podeu classificar els recursos de l'empresa, béns, clients, etc. Segons el grau d'importància. Al mateix temps, pel nivell d'importància, cadascuna de les unitats anteriors se li assigna una de les tres categories: A, B o C. Programa Excel té en les seves eines d'equipatge que faciliten aquest tipus d'anàlisi. Anem a esbrinar com utilitzar-los, i quina és l'anàlisi ABC.
Utilitzant anàlisi ABC
L'anàlisi ABC és una mena de millora i adaptada a les condicions modernes del principi de Pareto. Segons el mètode de la seva conducta, tots els elements de l'anàlisi es divideixen en tres categories segons el grau d'importància:- Categoria A - Elements que tenen una combinació de més del 80% de la gravetat específica;
- Categoria B - Elements la combinació que oscil·la entre el 5% i el 15% de la gravetat específica;
- Categoria C - Els elements restants, l'agregat total del qual és un 5% i una gravetat menys específica.
Les empreses separades utilitzen tècniques més avançades i divideixen articles per no 3, sinó per 4 o 5 grups, però es basarà en el clàssic pla d'anàlisi ABC.
Mètode 1: Anàlisi amb la classificació
L'anàlisi d'Excel ABC es realitza mitjançant la classificació. Tots els articles es classifiquen de greix a menys. A continuació, es calcula la part acumulativa de cada element, sobre la base de la qual se li assigna una determinada categoria. Anem a esbrinar com s'utilitza la metodologia especificada a la pràctica.
Tenim una taula amb una llista de productes que la companyia ven, i el nombre corresponent d'ingressos de la seva venda durant un període de temps determinat. A la part inferior de la taula, el resultat dels ingressos en general en tots els noms de la mercaderia. Hi ha una tasca que utilitza l'anàlisi ABC, trenca aquests productes en grups per la seva importància per a l'empresa.
- Destaquem una taula amb el cursor tancant el botó esquerre del ratolí, excloent la tapa i la cadena final. Aneu a la pestanya "Dades". Fem clic al botó "Ordenar", situat a la barra d'eines "Ordenar i filtrar" a la cinta.
També podeu fer-ne de manera diferent. Assignem l'interval de taula anterior i, a continuació, aneu a la pestanya "Inici" i feu clic al botó "Ordenar i filtrar" situat a la caixa d'eines d'edició a la cinta. La llista està activada en la qual s'activa la posició "Ordenació personalitzable".
- Quan apliqueu qualsevol de les accions anteriors, es llança la finestra Configuració de classificació. Semblem de manera que les "les meves dades contenen el capçalera" es fixin en una marca de verificació. En el cas de la seva absència, instal·lar.
Al camp "columna", especifiqueu el nom de la columna en què es troben les dades dels ingressos.
Al camp "Ordenar", cal especificar, sobre quins criteris específics s'ordenaran. Deixeu la configuració predefinida: "Valors".
En el camp "Ordre", mostra la posició "descendent".
Després del producte de la configuració especificada, feu clic al botó "D'acord" a la part inferior de la finestra.
- Després de realitzar l'acció especificada, tots els articles van ser ordenats per ingressos de més a un més petit.
- Ara hauríem de calcular la proporció de cadascun dels elements per a un resultat general. Creeu una columna addicional per a aquests propòsits, que anomenem "compartir pes". A la primera cel·la d'aquesta columna, posem el signe "=", després d'especificar un enllaç a la cel·la en què es troba la quantitat d'ingressos de la implementació del producte pertinent. A continuació, fixeu el signe de la divisió ("/"). Després d'això, indiquem les coordenades de la cel·la, que conté l'import total de la venda de mercaderies a tota l'empresa.
Tenint en compte el fet que la fórmula especificada que copiarem a altres cel·les de la columna "Compartir" mitjançant el marcador de farciment, l'adreça de l'enllaç a l'element que conté l'import final dels ingressos a l'empresa, hem de solucionar-ho. Per fer-ho, fes un enllaç absolut. Seleccioneu les coordenades de la cel·la especificada a la fórmula i premeu la tecla F4. Abans de les coordenades, com veiem, va aparèixer un signe de dòlar, que indica que l'enllaç s'ha convertit en absolut. Cal assenyalar que la referència a la quantitat d'ingressos de la primera a la llista de béns (producte 3) ha de seguir sent relativa.
A continuació, per fer càlculs, premeu el botó Enter.
- Com veiem, la proporció d'ingressos del primer producte especificada a la llista va aparèixer a la cel·la de destinació. Per copiar la fórmula al rang següent, posem el cursor a la cantonada inferior dreta de la cel·la. La seva transformació en el marcador de farciment, amb una mirada d'una petita creu. Fem clic al botó esquerre del ratolí i arrossegueu el marcador de farciment fins al final de la columna.
- Com podeu veure, tota la columna s'omple de dades que caracteritzen la proporció d'ingressos de la implementació de cada producte. Però el valor de la gravetat específica es mostra en el format numèric, i hem de transformar-lo en percentatge. Per fer-ho, ressalteu els continguts de la columna "Pes específic". A continuació, anem a la pestanya "Inici". A la cinta del grup Configuració del grup hi ha un camp que mostra el format de dades. Per defecte, si no heu produït manipulacions addicionals, cal instal·lar el format "general". Feu clic a la icona en forma de triangle situat a la dreta d'aquest camp. A la llista de formats, seleccioneu la posició "percentatge".
- Com veiem, tots els valors de la columna es van transformar en valors percentuals. Com hauria de ser, 100% indicat a la cadena de "total". La proporció de mercaderies s'espera a la columna d'una major a una més petita.
- Ara hauríem de crear una columna en la qual es mostraria la quota acumulada amb un resultat creixent. És a dir, en cada línia del pes específic individual d'un producte específic s'afegirà la proporció de tots aquells productes que es troben a la llista anterior. Per als primers productes de la llista (producte 3), la proporció individual i la quota acumulada serà igual, però tots els indicadors individuals hauran d'afegir la quota acumulada de l'element anterior de la llista.
Així, a la primera línia que es transferim a la columna "Compartir acumulat" l'indicador de la columna "específic".
- A continuació, establiu el cursor a la segona cel·la de la columna "Compartir acumulada". Aquí hem d'aplicar la fórmula. Posem el signe "igual" i plegem els continguts de la cel·la "La proporció" de la mateixa línia i el contingut de la cèl·lula "Compartir acumulat" de la cadena anterior. Totes les referències es reserven en relació, és a dir, que no produïm cap manipulació amb ells. Després d'això, realitzeu un clic al botó Intro per mostrar el resultat final.
- Ara heu de copiar aquesta fórmula a les cel·les d'aquesta columna, que es col·loquen a continuació. Per fer-ho, utilitzem el marcador de farciment al qual ja hem recorregut en copiar la fórmula a la columna d'accions. Al mateix temps, la cadena "total" no és necessària, ja que el resultat acumulat és 100% es mostrarà a l'últim producte de la llista. Com es pot veure, tots els elements de la nostra columna després van ser omplerts.
- Després d'això, creeu una columna "grup". Necessitarem grups de productes per categoria A, B i C segons la quota acumulada especificada. Com recordem, tots els elements es distribueixen per grups segons el següent esquema:
- A - fins a un 80%;
- B - El següent 15%;
- C - El 5% restant.
Per tant, tots els béns que acumulen la quota de pes específica de la qual es troba a la vora de fins al 80%, assignem categoria A. Els productes amb el pes específic acumulat del 80% al 95% estan assignats a la categoria B. La resta Grup de béns amb valor superior al 95% de la gravetat específica acumulada Assignem una categoria C.
- Per claredat, podeu omplir els grups especificats amb diferents colors. Però està a voluntat.
Així, vam trencar els elements sobre el nivell d'importància, mitjançant l'anàlisi ABC. Quan utilitzeu altres tècniques, com es va esmentar anteriorment, s'utilitza per entrar en un nombre més gran de grups, però el principi de trencar-se en aquest cas es manté gairebé sense canvis.
Lliçó: Ordenació i filtratge a Excel
Mètode 2: utilitzant una fórmula complexa
Per descomptat, l'ús de la classificació és la forma més habitual de realitzar anàlisis ABC en Excel. Però en alguns casos és necessari dur a terme aquesta anàlisi sense reordenar les línies en llocs de la taula d'origen. En aquest cas, una fórmula complexa arribarà al rescat. Per exemple, utilitzarem la mateixa taula d'origen que en el primer cas.
- Afegim a la taula d'origen que conté el nom de la mercaderia i els ingressos de la venda de cadascun d'ells, la columna "Grup". Com podeu veure, en aquest cas, és possible que no afegim columnes amb el càlcul de fraccions individuals i acumulatives.
- Produïm la primera assignació de cel·les a la columna del grup, després de la qual cosa realitzeu un botó "Insereix la funció" situat a prop de la fila de fórmula.
- Activació de màsters de funcions. Anem a la categoria "Enllaços i matrius". Seleccioneu la funció "Elecció". Fem un clic al botó "D'acord".
- La finestra de l'argument del joc està activat. La sintaxi es presenta de la manera següent:
= Selecció (nombre_intex; valor1; valor2; ...)
La tasca d'aquesta funció és la retirada d'un dels valors especificats, segons el número d'índex. El nombre de valors pot arribar als 254, però només necessitarem tres noms que corresponguin a categories ABC-anàlisi: A, B, C. Podem introduir immediatament el símbol "A" al camp "B" del camp "B" "Camp, camp" Value3 "-" C ".
- Però amb l'argument "Número d'índex" haurà de quedar-se a fons mitjançant la construcció de diversos operadors addicionals. Instal·leu el cursor al camp "Número d'índex". A continuació, feu clic a la icona amb vista sobre el triangle, a l'esquerra del botó "Insereix la funció". S'obrirà una llista d'operadors recentment utilitzats. Necessitem una funció de la cerca. Com que no es troba a la llista, feu clic a la inscripció "Altres funcions ...".
- La finestra de la finestra de l'assistent de funcions es torna a començar. De nou, aneu a la categoria "Enllaços i matrius". Trobem la posició del "Consell de cerca", assignar-lo i fer un clic al botó "D'acord".
- S'obrirà els arguments dels arguments de l'operador de cerca. La sintaxi d'això té el següent formulari:
= Consell de cerca (Search_name; Viewing__Massive; Type_station)
El propòsit d'aquesta funció és definir el número de posició de l'element especificat. És a dir, només el que necessitem per al camp "Número d'índex" té una opció.
Al camp "Array de la llista", podeu demanar immediatament la següent expressió:
{0: 0,8: 0,95}
Hauria de ser precisament en claudàtors, com a fórmula de matriu. No és difícil endevinar que aquests números (0; 0,8; 0,95) denoten els límits de la quota acumulada entre grups.
El camp "Tipus de comparació" no és obligatori i en aquest cas no l'omplirem.
Al camp "Segon valor", establiu el cursor. A continuació, de nou a través de la icona es descriu anteriorment en forma de triangle, ens movem a l'assistent de les funcions.
- Aquesta vegada a l'Assistent de les funcions, fem traslladar-nos a la categoria "Mathematical". Trieu el nom "silenciós" i premeu el botó "D'acord".
- Es llançarà la finestra dels arguments de la funció. L'operador especificat resumeix les cèl·lules que compleixen les condicions definides. La seva sintaxi és:
= Silenci (rang; criteri; range_suming)
Al camp "rang", introduïu l'adreça de la columna "Revenue". A aquests efectes, establim el cursor al camp, i després fent el clip del botó esquerre del ratolí, seleccioneu totes les cel·les de la columna corresponent, excloent el valor "total". Com podeu veure, l'adreça va aparèixer immediatament al camp. A més, hem de fer que aquest enllaç sigui absolut. Per fer-ho, produïu la seva assignació i feu clic a la tecla F4. L'adreça va ser alliberada per signes de dòlar.
Al camp "criteri", hem de fixar una condició. Introduïu la següent expressió:
">"&
A continuació, immediatament després d'introduir l'adreça de la primera cel·la de la columna "Revenue". Fem coordenades horitzontalment en aquesta adreça absoluta, afegint el signe del dòlar des del teclat davant de la lletra. Les coordenades verticals deixen relatives, és a dir, no hauria d'haver cap senyal davant del dígit.
Després d'això, no premem el botó "D'acord" i feu clic al nom de la funció de cerca a la cadena de fórmula.
- A continuació, tornem a la finestra dels arguments de la pissarra de cerca. Com veiem, en el camp "significat Fogular", les dades establertes per l'operador estaven en silenci. Però això no és tot. Aneu a aquest camp i ja s'afegeix a les dades disponibles per afegir un signe "+" sense cometes. A continuació, introduïm l'adreça de la primera cel·la de la columna "ingressos". I, de nou, fem coordenades horitzontalment amb absolut, i deixem verticalment en relació.
A continuació, prenem tots els continguts del camp "El valor desitjat" als claudàtors, després de la qual cosa posem el signe de la divisió ("/"). Després d'això, de nou a través de la icona del triangle, aneu a la funció de seleccionar funcions.
- Igual que l'última vegada en l'assistent de la funció en execució, busquem l'operador necessari en la categoria "Matemàtica". Aquesta vegada la funció desitjada es diu "sumes". Ho destaquem i premeu el botó "D'acord".
- S'obrirà la finestra dels arguments de l'operador. El seu propòsit principal és la sumació de les dades a les cèl·lules. La sintaxi d'aquest operador és bastant senzilla:
= Sums (número1; nombre2; ...)
Per als nostres propòsits, només es necessitarà el camp "Nombre1". Introduïm en ella les coordenades de la columna "ingressos", eliminant la cel·la que conté els resultats. Ja hem realitzat aquesta operació en el camp "rang" de la funció. Com en aquest moment, les coordenades de la gamma fan absoluta, destacant-les i prement la tecla F4.
Després d'això, feu clic a la tecla "OK" a la part inferior de la finestra.
- Com es pot veure, el complex de les funcions introduïdes va fer un càlcul i va emetre el resultat a la primera cel·la de la columna "Group". El primer producte se li va assignar un grup "A". La fórmula completa aplicada per nosaltres per a aquest càlcul és la següent:
= Elecció (Consell de cerca ((Silenci ($ B $ 2: $ B $ 27; "> & $ B2) + $ B2) / Quants ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")
Però, per descomptat, en cada cas, les coordenades d'aquesta fórmula seran diferents. Per tant, no es pot considerar universal. Però, utilitzant el manual que es va donat anteriorment, podeu inserir les coordenades de qualsevol taula i aplicar amb èxit aquest mètode en qualsevol situació.
- No obstant això, això no és tot. Hem calculat només per a la primera fila de la taula. Per omplir completament la columna del grup "Grup", cal copiar aquesta fórmula al rang següent (eliminant la cel·la de la cadena "total") utilitzant el marcador de farciment, ja que ja hem fet més d'una vegada. Després de fer les dades, es pot considerar l'anàlisi ABC.
Com veiem, els resultats obtinguts utilitzant l'opció utilitzant una fórmula complexa no són diferents dels resultats que vam realitzar mitjançant la classificació. Totes les mercaderies se'ls assigna les mateixes categories, només al mateix temps que les files no van canviar la seva posició inicial.
Lliçó: Màster en Funcions Excel
El programa d'Excel és capaç de pal·liar significativament l'anàlisi ABC per a l'usuari. Això s'aconsegueix utilitzant aquesta eina com a classificació. Després d'això, es calcula la gravetat específica individual, la quota acumulada i, en realitat, es calculen les particions en grups. En els casos en què es permet el canvi en la posició inicial de les files de la taula, es pot aplicar el mètode mitjançant una fórmula complexa.