Un dos principais métodos de xestión e loxística é a análise de ABC. Con ela, pode clasificar os recursos da empresa, produtos, clientes, etc. en orde de importancia. Ao mesmo tempo, de acordo co nivel de importancia, cada unha das unidades mencionadas está asignada a unha das tres categorías: A, B ou C. Excel ten ferramentas na súa equipaxe que facilitan a realización deste tipo de análises. Vexamos como usalos e cal é a análise ABC.
Usando análises ABC
A análise de ABC é unha especie de variante mellorada e adaptada ás condicións modernas do principio de Pareto. Segundo a metodoloxía da súa conduta, todos os elementos da análise divídense en tres categorías por orde de importancia:
- Categoría A - elementos que teñen máis en común 80% peso específico;
- Categoría B - elementos do que é total 5% ata 15% peso específico;
- Categoría C - Os elementos restantes cuxo total é agregado 5% e menos peso específico.
Algunhas empresas usan técnicas máis avanzadas e dividen os elementos non en 3, senón en 4 ou 5 grupos, pero contaremos co esquema clásico da análise de ABC.
Método 1: análise por ordenación
En Excel, a análise de ABC realízase mediante a ordenación. Todos os elementos ordénanse de maior a menor. Entón calcúlase o peso específico acumulado de cada elemento, baseándose nel asignándolle unha determinada categoría. Usamos un exemplo específico para descubrir como se aplica esta técnica na práctica.
Temos unha táboa cunha lista de produtos que a empresa vende e a cantidade correspondente de ingresos procedentes da súa venda durante un determinado período de tempo. Na parte inferior da táboa, resúmense os ingresos totais para todos os bens. A tarefa consiste en utilizar a análise ABC para dividir estes produtos en grupos segundo a súa importancia para a empresa.
- Seleccione a táboa co cursor de datos, mantendo o botón esquerdo do rato, excluíndo a cabeceira ea última fila. Vaia á pestana "Datos". Fai clic no botón. "Ordenar"situado nun bloque de ferramentas "Ordenar e filtrar" na cinta.
Tamén pode facelo de xeito diferente. Seleccione o rango da táboa anterior e, a continuación, desprácese á pestana "Fogar" e prema no botón "Ordenar e filtrar"situado nun bloque de ferramentas Edición na cinta. Activa unha lista na que seleccionamos unha posición nela. "Ordenación personalizada".
- Ao aplicar calquera das accións anteriores, ábrese a ventá de configuración de clasificación. Vemos o parámetro "Os meus datos conteñen cabeceiras" Se marcou. En caso de ausencia, instale.
No campo "Columna" especifique o nome da columna na que se atopan os datos dos ingresos.
No campo "Ordenar" cómpre especificar con qué criterios específicos se ordenarán. Deixamos a configuración predefinida - "Valores".
No campo "Orde" definir a posición "Descendente".
Despois de facer esta configuración, fai clic no botón "OK" na parte inferior da xanela.
- Despois de realizar esta acción, todos os elementos ordenáronse por ingresos do máis alto ao máis baixo.
- Agora necesitamos calcular a proporción de cada un dos elementos para o total. Creamos para estes fins unha columna adicional que chamaremos "Compartir". Na primeira cela desta columna pon un sinal "="despois diso indicamos a referencia á cela na que se atopa a suma dos ingresos procedentes da venda do produto correspondente A continuación, configure o signo de división ("/"). Despois indicamos as coordenadas da cela, que contén a cantidade total de vendas de bens en toda a empresa.
Tendo en conta que copiaremos a fórmula indicada a outras celas da columna "Compartir" Por medio dun marcador de recheo, o enderezo da ligazón ao elemento que contén a cantidade total de ingresos para a empresa, necesitamos arranxar. Para facelo, fai a ligazón absoluta. Seleccione as coordenadas da cela especificada na fórmula e prema a tecla F4. Como podemos ver, apareceu un sinal de dólar diante das coordenadas, o que indica que a ligazón volveuse absoluta. Nótese que a referencia á cantidade de ingresos do primeiro elemento da lista (Tema 3) debe permanecer relativo.
Entón, para facer cálculos, fai clic no botón. Intro.
- Como podes ver, a proporción de ingresos do primeiro produto listado na lista móstrase na cela de destino. Para facer unha copia da fórmula no rango inferior, coloque o cursor no canto inferior dereito da cela. É transformado nun marcador de enchido que parece unha pequena cruz. Fai clic co botón esquerdo do rato e arrastra o mango de recheo ata o final da columna.
- Como podes ver, toda a columna está chea de datos que describen a cota de ingresos procedentes da venda de cada produto. Pero o valor do peso específico móstrase en formato numérico e necesitamos transformalo en porcentaxe. Para facelo, selecciona o contido da columna "Compartir". Despois desprácese á pestana "Fogar". Na cinta do grupo de opcións "Número" Hai un campo que amosa o formato de datos. De xeito predeterminado, se non realizou ningunha manipulación adicional, debería configurarse o formato alí. "Xeral". Facemos clic na icona en forma de triángulo situado á dereita deste campo. Na lista de formatos que se abre, seleccione a posición "Interese".
- Como podes ver, todos os valores da columna convertéronse en porcentaxes. Como debería ser, na liña "Total" indicado 100%. A proporción de bens que se espera estar na columna de maior a menor.
- Agora debemos crear unha columna na que se mostraría a cota acumulada cun total acumulado. É dicir, en cada fila, engadirase ao peso específico individual dun determinado produto o peso específico de todos aqueles bens situados na lista anterior. Para o primeiro elemento da lista (Tema 3) o peso específico individual e a cota acumulada serán iguais, pero para todas as seguintes, a cota acumulada do elemento anterior da lista deberá engadirse ao indicador individual.
Así, na primeira fila transferimos á columna "Compartir acumulada" taxa de columna "Compartir".
- A continuación, axuste o cursor na segunda cela. "Compartir acumulada". Aquí temos que aplicar a fórmula. Poñemos un sinal igual e dobra o contido da cela "Compartir" contido da mesma fila e cela "Compartir acumulada" desde a liña anterior. Todos os enlaces son relativos, é dicir, non realizamos ningunha manipulación con eles. Despois, faga clic no botón. Intro para mostrar o resultado final.
- Agora ten que copiar esta fórmula ás celas desta columna, que se atopan a continuación. Para iso, usa o marcador de recheo, ao que xa recorremos a copiar a fórmula na columna "Compartir". Ao mesmo tempo, a cadea "Total" a captura non é necesaria porque o resultado acumulado 100% aparecerá no último elemento da lista. Como podes ver, enchéronse todos os elementos da nosa columna.
- Despois creamos unha columna "Grupo". Necesitamos agrupar produtos en categorías A, B e C segundo a cota acumulada indicada. Como recordamos, todos os elementos divídense en grupos segundo o seguinte esquema:
- A - ata 80%;
- B - o seguinte 15%;
- Con - resto 5%.
Así, todos os bens, a cota acumulada do peso específico do que entra na fronteira con 80%asignar unha categoría A. Bens cun peso específico acumulado de 80% ata 95% asignar unha categoría B. O grupo de produtos restante cun valor máis 95% asignar un peso específico asignar unha categoría C.
- Por máis claridade, pode encher estes grupos en diferentes cores. Pero isto é opcional.
Deste xeito, desglosamos os elementos en grupos segundo o nivel de importancia, utilizando a análise ABC. Cando se usan outros métodos, como se mencionou anteriormente, aplique unha partición en máis grupos, pero o principio do particionamento permanece case sen modificacións.
Lección: Ordenación e filtrado en Excel
Método 2: usando unha fórmula complexa
Por suposto, o uso da clasificación é o xeito máis común de realizar análises ABC en Excel. Pero nalgúns casos é necesario realizar esta análise sen reorganizar as filas na táboa de orixe. Neste caso, rescatarase unha fórmula complexa. Por exemplo, usaremos a mesma táboa de orixe como no primeiro caso.
- Engade á táboa orixinal o nome dos bens e os ingresos procedentes da venda de cada un deles, a columna "Grupo". Como podes ver, neste caso non podemos engadir columnas co cálculo de accións individuais e acumulativas.
- Seleccione a primeira cela da columna. "Grupo"faga clic no botón. "Inserir función"situado preto da barra de fórmulas.
- Realízase a activación Mestría de funcións. Mover á categoría "Ligazóns e matrices". Escolla unha función "SELECCIONAR". Fai clic no botón. "OK".
- Activa a xanela de argumento de función. SELECCIÓN. A súa sintaxe é a seguinte:
= SELECT (número_Indice; Valor1; Valor2; ...)O obxectivo desta función é sacar un dos valores especificados, dependendo do número de índice. O número de valores pode chegar a 254, pero só necesitamos tres nomes que corresponden ás categorías do análise ABC: A, B, Con. Podemos entrar inmediatamente no campo "Valor1" o símbolo "A"no campo "Valor2" - "B"no campo "Valor3" - "C".
- Pero cun argumento "Número de índice" Será necesario xogar completamente, incorporando algúns operadores adicionais. Sitúe o cursor no campo "Número de índice". A continuación, faga clic na icona que ten a forma dun triángulo á esquerda do botón "Inserir función". Ábrese unha lista de operadores usados recentemente. Necesitamos unha función PARTIDO. Como non está na lista, fai clic na lenda "Outras funcións ...".
- Executa a xanela de novo. Mestría de funcións. De novo vaia á categoría "Ligazóns e matrices". Alí atopamos unha posición "MATCH"selecciona e fai clic no botón "OK".
- Ábrese a xanela de argumento do operador PARTIDO. A súa sintaxe é a seguinte:
= MATCH (valor buscado; matriz buscada; tipo_combinación)O propósito desta función é determinar o número de posición do elemento especificado. É dicir, exactamente o que necesitamos para o campo "Número de índice" funcións SELECCIÓN.
No campo "Matriz visto" Pode axustar inmediatamente a seguinte expresión:
{0:0,8:0,95}Debe estar exactamente en chaves, como unha fórmula matricial. Non é difícil adiviñar que estes números (0; 0,8; 0,95) denotan os límites da cota acumulada entre grupos.
Campo "Tipo de mapeamento" non obrigatorio e neste caso non o encheremos.
No campo "Valor buscado" definir o cursor. Unha vez máis, a través da icona descrita anteriormente en forma de triángulo, pasamos a Asistente de funcións.
- Esta vez Asistente de funcións mover á categoría "Matemática". Escolla un nome "SUMMESLI" e prema no botón "OK".
- Iníciase a fiestra do argumento da función. Sumas. O operador especificado suma as celas que cumpren a condición especificada. A súa sintaxe é:
= SUMMES (rango; criterio; range_summing)No campo "Intervalo" introduza o enderezo da columna "Ingresos". Para estes efectos, definimos o cursor no campo e, logo de fixar o botón esquerdo do rato, selecciona todas as celas da columna correspondente, excluíndo o valor "Total". Como podes ver, o enderezo móstrase inmediatamente no campo. Ademais, necesitamos facer esta ligazón absoluta. Para facelo, fai a súa selección e preme a tecla F4. O enderezo resáltase con signos de dólar.
No campo "Criterio" necesitamos establecer unha condición. Introduza a seguinte expresión:
">"&Entón inmediatamente despois introduciremos o enderezo da primeira cela da columna. "Ingresos". Facemos as coordenadas horizontais neste enderezo absolutas, engadindo un sinal de dólar desde o teclado diante da letra. As coordenadas verticais son relativas, é dicir, non debería haber signo diante do número.
Despois diso, non preme o botón "OK"e prema no nome da función PARTIDO na barra de fórmulas.
- Entón volvemos á fiestra de argumento de función. PARTIDO. Como podes ver, no campo "Valor buscado" Os datos apareceron dados polo operador Sumas. Pero iso non é todo. Vaia a este campo e engade o sinal aos datos existentes. "+" sen comiñas. Entón introducimos o enderezo da primeira cela da columna. "Ingresos". E de novo facemos que as coordenadas horizontais desta ligazón sexan absolutas, e deixamos en vertical.
A continuación, toma todo o contido do campo "Valor buscado" entre parénteses, a continuación, coloque o signo de división ("/"). Despois diso, de novo a través da icona do triángulo, vai á ventá de selección de funcións.
- Como última vez en correr Asistente de funcións buscando o operador desexado na categoría "Matemática". Esta vez chámase a función desexada "SUMM". Selecciona e fai clic no botón. "OK".
- Ábrese a xanela de argumento do operador SUM. O seu obxectivo principal é a suma de datos nas células. A sintaxe desta afirmación é bastante sinxela:
= SUMA (Número1; Número2; ...)Para os nosos propósitos só necesitamos un campo. "Número1". Introduza as coordenadas do rango da columna "Ingresos", excluíndo a cela que contén os totais. Xa realizamos unha operación similar no campo. "Intervalo" funcións Sumas. Como nese momento, realizamos coordenadas absolutas do rango seleccionándoas e premendo a tecla F4.
Despois faga clic na tecla "OK" na parte inferior da xanela.
- Como podes ver, o complexo das funcións introducidas produciu un cálculo e deu o resultado na primeira cela da columna "Grupo". O primeiro elemento foi asignado a un grupo. "A". A fórmula completa que usamos para este cálculo é a seguinte:
= SELECT (MATCH ((SUMMES ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / SUM ($ B $ 2: $ B $ 27); {0: 0.8: 0.95} ); "A"; "B"; "C")Pero, por suposto, en cada caso as coordenadas desta fórmula serán diferentes. Polo tanto, non pode considerarse universal. Pero, usando o manual que se indicou anteriormente, pode inserir as coordenadas de calquera táboa e aplicar con éxito este método en calquera situación.
- Non obstante, isto non é todo. Calculamos só para a primeira fila da táboa. Para encher completamente a columna de datos "Grupo", cómpre copiar esta fórmula ao rango inferior (excluíndo a cela de fila) "Total") empregando o marcador de recheo, como fixemos máis dunha vez. Despois de introducir os datos, a análise de ABC pode considerarse completo.
Como podes ver, os resultados obtidos usando a variante co uso dunha fórmula complexa non difieren dos resultados que realizamos mediante a clasificación. Todos os produtos están asignados ás mesmas categorías, pero as liñas non cambiaron a súa posición inicial.
Lección: Asistente de funcións de Excel
Excel pode facilitar moito a análise de ABC para un usuario. Isto lógrase usando unha ferramenta como a ordenación. Despois calcúlase o peso específico individual, a cota acumulada e, de feito, a división en grupos. Nos casos en que o cambio na posición inicial das filas na táboa non está permitido, pode aplicar o método usando unha fórmula complexa.
