Un dels mètodes clau de gestió i logística és l'anàlisi ABC. Amb ell, podeu classificar els recursos de l'empresa, els productes, els clients, etc. per ordre d'importància. Al mateix temps, segons el nivell d'importància, cadascuna de les unitats esmentades arriba a una de les tres categories: A, B o C. Excel té eines a l'equipatge que faciliten la realització d'aquest tipus d'anàlisi. Vegem com utilitzar-los i quina és l’anàlisi ABC.
Ús d’anàlisi ABC
L’anàlisi d’ABC és una variant de condicions modernes millorada i adaptada al principi de Pareto. Segons la metodologia de la seva conducta, tots els elements de l’anàlisi es divideixen en tres categories per ordre d'importància:
- Categoria A - elements que tenen en comú més 80% pes específic;
- Categoria B - elements de la qual es troba la totalitat 5% fins a 15% pes específic;
- Categoria C - els elements restants, el total dels quals és 5% i menys pes específic.
Algunes empreses utilitzen tècniques més avançades i divideixen els elements no en 3, sinó en 4 o 5 grups, però depenem de l’esquema clàssic de l’anàlisi ABC.
Mètode 1: anàlisi per ordre
A Excel, l’anàlisi ABC es realitza mitjançant l’ordenació. Tots els elements s'ordenen de major a menor. Aleshores es calcula el pes específic acumulat de cada element, a partir del qual se li assigna una determinada categoria. Utilitzem un exemple específic per esbrinar com s'aplica aquesta tècnica a la pràctica.
Tenim una taula amb una llista de productes que la companyia ven i la quantitat corresponent d’ingressos de la seva venda durant un període de temps determinat. Al final de la taula, es resumeixen els ingressos totals per a tots els articles. La tasca és utilitzar l'anàlisi ABC per dividir aquests productes en grups segons la seva importància per a l'empresa.
- Seleccioneu la taula amb el cursor de dades, mantenint el botó esquerre del ratolí, sense incloure la capçalera i la fila final. Aneu a la pestanya "Dades". Feu clic al botó. "Ordena"situat en un bloc d’eines "Ordenar i filtrar" a la cinta.
També es pot fer de manera diferent. Seleccioneu l’abast de la taula anterior i, a continuació, aneu a la pestanya "Inici" i feu clic al botó "Ordenar i filtrar"situat en un bloc d’eines Edició a la cinta. S'ha activat una llista en la qual seleccionem una posició. "Ordre personalitzat".
- Quan apliqueu qualsevol de les accions anteriors, es llançarà la finestra de configuració d’ordenació. Veiem el paràmetre "Les meves dades contenen capçaleres" s'ha marcat el tick. En cas d’absència, instal·leu-lo.
Al camp "Columna" especifiqueu el nom de la columna en què es troben les dades sobre els ingressos.
Al camp "Ordena" heu d’especificar amb quins criteris específics s’ordenaran. Deixem la configuració predefinida: "Valors".
Al camp "Ordre" estableix la posició "Descendent".
Després de fer aquesta configuració, feu clic al botó "D'acord" al final de la finestra.
- Després de realitzar aquesta acció, tots els elements van ser ordenats per ingressos des del més alt fins al més baix.
- Ara hem de calcular la proporció de cadascun dels elements del total. Creem per a aquests propòsits una columna addicional que anomenarem "Compartir". A la primera cel·la d’aquesta columna hi ha un signe "="després d'això indiquem la referència a la cel·la en la qual es troba la suma dels ingressos de la venda del producte rellevant. A continuació, establiu el signe de divisió ("/"). Després, indiquem les coordenades de la cel·la, que conté la quantitat total de vendes de béns a tota l'empresa.
Tenint en compte que copiarem la fórmula indicada a altres cel·les de la columna "Compartir" mitjançant un marcador d’emplenament, l’adreça de l’enllaç a l’element que conté l’import total dels ingressos de l’empresa, hem de solucionar-ho. Per fer-ho, feu l’enllaç absolut. Seleccioneu les coordenades de la cel·la especificada a la fórmula i premeu la tecla F4. Com veiem, apareix un signe de dòlar davant de les coordenades, el que indica que l’enllaç s’ha convertit en absolut. Cal assenyalar que la referència a la quantitat d’ingressos del primer element de la llista (Tema 3) ha de romandre relatiu.
Després, per fer càlculs, feu clic al botó. Introduïu.
- Com podeu veure, la proporció d’ingressos del primer producte que apareix a la llista es mostra a la cel·la de destinació. Per fer una còpia de la fórmula en el rang inferior, poseu el cursor a la cantonada inferior dreta de la cel·la. Es transforma en un marcador de farciment que sembla una petita creu. Feu clic al botó esquerre del ratolí i arrossegueu el mànec fins al final de la columna.
- Com podeu veure, tota la columna s’omplix de dades que descriuen la quota d’ingressos de la venda de cada producte. Però el valor del pes específic es mostra en format numèric i hem de transformar-lo en un percentatge. Per fer-ho, seleccioneu el contingut de la columna "Compartir". A continuació, aneu a la pestanya "Inici". A la cinta del grup de configuració "Nombre" Hi ha un camp que mostra el format de dades. Per defecte, si no heu fet cap manipulació addicional, el format s’hauria de configurar allí. "General". Feu clic a la icona en forma de triangle situat a la dreta d’aquest camp. A la llista de formats que s'obrirà, seleccioneu la posició "Interès".
- Com podeu veure, tots els valors de les columnes es van convertir en percentatges. Com hauria de ser, en línia "Total" indicat 100%. La proporció de béns que es preveu ubicar a la columna de més gran a menor.
- Ara hauríem de crear una columna en la qual es mostri el percentatge acumulat amb un total acumulat. És a dir, en cada fila, el pes específic de tots els béns que es troben a la llista anterior s’afegirà al pes específic individual d’un producte en particular. Per al primer element de la llista (Tema 3) el pes específic individual i la quota acumulada seran iguals, però per a totes les subsegüents s'haurà d’afegir la quota acumulada de l’element anterior a la llista.
Així, a la primera fila la transferim a la columna "Accumulation Share" taxa de columna "Compartir".
- A continuació, establiu el cursor a la cel·la de la segona columna. "Accumulation Share". Aquí hem d’aplicar la fórmula. Posem un signe igual i plega el contingut de la cel·la "Compartir" contingut de la mateixa fila i cel·la "Accumulation Share" de la línia anterior. Tots els enllaços són relatius, és a dir, no fem cap manipulació amb ells. Després, feu clic al botó. Introduïu per mostrar el resultat final.
- Ara heu de copiar aquesta fórmula a les cel·les d’aquesta columna, que es troben a continuació. Per fer-ho, utilitzeu el marcador de farciment, al qual ja hem recorregut per copiar la fórmula a la columna "Compartir". Al mateix temps, la cadena "Total" la captura no és necessària perquè el resultat acumulat és 100% es mostrarà a l’últim element de la llista. Com podeu veure, es van omplir tots els elements de la nostra columna.
- Després d'això, crearem una columna "Grup". Haurem d’agrupar productes en categories A, B i C segons la quota acumulada indicada. Com recordem, tots els elements es divideixen en grups segons el següent esquema:
- A - fins a 80%;
- B - el següent 15%;
- Amb - restant 5%.
Així, tots els béns, la part acumulada del pes específic del qual entra a la frontera 80%assignar una categoria A. Mercaderies amb un pes específic acumulat de 80% fins a 95% assignar una categoria B. El grup de productes restant amb més valor 95% assignar una categoria de pes específic acumulat C.
- Per claredat, podeu omplir aquests grups de diferents colors. Però això és opcional.
Per tant, hem trencat els elements en grups segons el nivell d'importància, utilitzant l'anàlisi ABC. Quan utilitzeu altres mètodes, com es va esmentar anteriorment, apliqueu una partició en més grups, però el principi del particionat roman gairebé sense canvis.
Lliçó: Ordenació i filtratge a Excel
Mètode 2: utilitzant una fórmula complexa
Per descomptat, l’ús de l’ordenació és la forma més habitual de realitzar anàlisis ABC a Excel. Però en alguns casos és necessari realitzar aquesta anàlisi sense reordenar les files de la taula font. En aquest cas, es resoldrà una fórmula complexa. Per exemple, utilitzarem la mateixa taula de fonts que en el primer cas.
- Afegiu a la taula original el nom de la mercaderia i el resultat de la venda de cadascun d’ells, la columna "Grup". Com podeu veure, en aquest cas no podem afegir columnes amb el càlcul de les accions individuals i acumulades.
- Seleccioneu la primera cel·la de la columna. "Grup"després feu clic al botó. "Insereix la funció"situat a prop de la barra de fórmules.
- Es realitza l’activació Màsters de funcions. Vés a la categoria "Enllaços i matrius". Trieu una funció "SELECCIONA". Feu clic al botó. "D'acord".
- S'activa la finestra d’argument de funció. SELECCIÓ. La seva sintaxi és la següent:
= SELECCIONA (nombre_index; valor1; valor2; ...)L’objectiu d’aquesta funció és emetre un dels valors especificats, segons el nombre d’índex. El nombre de valors pot arribar a 254, però només necessitem tres noms que corresponen a les categories d’anàlisi ABC: A, B, Amb. Podem entrar immediatament al camp "Valor1" el símbol "A"al camp "Valor2" - "B"al camp "Valor3" - "C".
- Però amb un argument "Número d'índex" Caldrà trencar bé, havent incorporat alguns operadors addicionals. Situeu el cursor al camp "Número d'índex". A continuació, feu clic a la icona que té la forma d’un triangle a l’esquerra del botó "Insereix la funció". S'obrirà una llista d’operadors utilitzats recentment. Necessitem una funció PARTIT. Com que no es troba a la llista, feu clic a la llegenda "Altres funcions ...".
- Voleu tornar a executar la finestra. Màsters de funcions. Un cop més, aneu a la categoria "Enllaços i matrius". Hi trobem una posició "MATCH"seleccioneu-lo i feu clic al botó "D'acord".
- S'obre la finestra d’argument d’operador PARTIT. La seva sintaxi és la següent:
= MATCH (valor buscat; matriu vista; Match_type)El propòsit d’aquesta funció és determinar el nombre de posició de l’element especificat. És a dir, el que necessitem per al camp "Número d'índex" funcions SELECCIÓ.
Al camp "Tauler vist" Podeu definir immediatament la següent expressió:
{0:0,8:0,95}Hauria de ser exactament en claus, com una fórmula matricial. No és difícil endevinar que aquests números (0; 0,8; 0,95) denoten els límits de la participació acumulada entre grups.
Camp "Tipus de mapatge" no obligatòria i, en aquest cas, no l'omplirem.
Al camp "Valor buscat" definiu el cursor. De nou, a través de la icona descrita anteriorment en forma de triangle, ens desplacem a Auxiliar de funcions.
- Aquesta vegada Auxiliar de funcions passar a la categoria "Matemàtica". Trieu un nom "SUMMESLI" i feu clic al botó "D'acord".
- S'inicia la finestra d'argument de la funció. Suma. L'operador especificat suma les cel·les que compleixen la condició especificada. La seva sintaxi és:
= SUMMES (rang; criteri; rang_sum)Al camp "Interval" introduïu l’adreça de la columna "Ingressos". A aquests efectes, establim el cursor en el camp i, després de fixar el botó esquerre del ratolí, seleccioneu totes les cel·les de la columna corresponent, excloent-ne el valor "Total". Com podeu veure, l’adreça es mostra immediatament al camp. A més, necessitem que aquest enllaç sigui absolut. Per fer-ho, feu la selecció i premeu la tecla F4. L’adreça es ressalta amb signes de dòlar.
Al camp "Criteri" necessitem establir una condició. Introduïu l’expressió següent:
">"&Després, immediatament després, introduïm l'adreça de la primera cel·la de la columna. "Ingressos". Fem absolutes les coordenades horitzontals d’aquesta adreça, afegint un signe de dòlar des del teclat davant de la lletra. Les coordenades verticals són relatives, és a dir, no hauria de tenir cap signe al davant del número.
Després d'això, no premeu el botó "D'acord", i feu clic al nom de la funció PARTIT a la barra de fórmules.
- Després tornem a la finestra d’argument de la funció. PARTIT. Com podeu veure, al camp "Valor buscat" les dades van aparèixer donades per l’operador Suma. Però això no és tot. Aneu a aquest camp i afegiu el signe a les dades existents. "+" sense pressupostos. A continuació, introduïm l’adreça de la primera cel·la de la columna. "Ingressos". I de nou, fem absolutes les coordenades horitzontals d’aquest enllaç, i deixem verticalment les relacions.
A continuació, traieu tot el contingut del camp "Valor buscat" entre parèntesis, llavors poseu el signe de divisió ("/"). Després, a través de la icona del triangle, aneu a la finestra de selecció de funcions.
- Com a última vegada en córrer Auxiliar de funcions buscant l’operador desitjat a la categoria "Matemàtica". Aquesta vegada, es crida la funció desitjada "SUMM". Seleccioneu-lo i feu clic al botó. "D'acord".
- S'obrirà la finestra d’argument d’operador SUMA. El seu objectiu principal és la suma de dades a les cèl·lules. La sintaxi d’aquesta afirmació és bastant simple:
= SUMA (Nombre1; Nombre2; ...)Per als nostres propòsits només necessitem un camp. "Nombre1". Introduïu les coordenades de l’abast de la columna "Ingressos", excloent la cel·la que conté els totals. Ja hem realitzat una operació similar al camp. "Interval" funcions Suma. Com en aquest moment, fem coordenades absolutes del rang seleccionant-les i prement la tecla F4.
Després fes clic a la tecla "D'acord" al final de la finestra.
- Com podeu veure, el complex de les funcions introduïdes va produir un càlcul i va donar el resultat a la primera cel·la de la columna "Grup". Al primer tema se li va assignar un grup. "A". La fórmula completa que hem utilitzat per a aquest càlcul és la següent:
= SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ 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 s’ha donat anteriorment, podeu inserir les coordenades de qualsevol taula i aplicar correctament aquest mètode en qualsevol situació.
- Tanmateix, això no és tot. Hem calculat només per a la primera fila de la taula. Per omplir completament la columna de dades "Grup", heu de copiar aquesta fórmula al rang inferior (excloent la cel·la de fila) "Total") utilitzant el marcador d’ompliment, com hem fet més d’una vegada. Després d’introduir les dades, l’anàlisi ABC es pot considerar completa.
Com podeu veure, els resultats obtinguts amb la variant amb l’ús d’una fórmula complexa no difereixen gens dels resultats que hem realitzat mitjançant l’ordenació. A tots els productes se'ls assigna les mateixes categories, però les línies no van canviar la seva posició inicial.
Lliçó: Auxiliar de funcions d'Excel
Excel pot facilitar enormement l'anàlisi ABC per a un usuari. Això s’aconsegueix utilitzant una eina com la classificació. Després d'això, es calcula el pes específic individual, la participació acumulada i, de fet, la divisió en grups. En els casos en què el canvi en la posició inicial de les files de la taula no està permès, podeu aplicar el mètode utilitzant una fórmula complexa.
