Un dos métodos clave de xestión e loxística é a análise ABC. Con el, pode clasificar os recursos da empresa, bens, clientes, etc. Segundo o grao de importancia. Ao mesmo tempo, polo nivel de importancia, cada unha das unidades anteriores é asignada unha das tres categorías: A, B ou C. Excel Program ten nas súas ferramentas de equipaxe que facilitan a realización deste tipo de análise. Imos descubrir como usalos, e cal é a análise ABC.
Usando ABC Análise
A análise ABC é unha especie de mellorada e adaptada ás condicións modernas para o principio de Pareto. Segundo o método da súa conduta, todos os elementos da análise divídense en tres categorías segundo o grao de importancia:- Categoría A - Elementos que teñen unha combinación de máis do 80% da gravidade específica;
- Categoría B - Elementos cuxa combinación varía do 5% ao 15% da gravidade específica;
- Categoría C - Os elementos restantes, o total do agregado é un 5% e menos de gravidade específica.
As empresas separadas utilizan técnicas máis avanzadas e divide elementos de non a 3, senón por 4 ou 5 grupos, pero contaremos co clásico esquema de análise ABC.
Método 1: análise con clasificación
A análise de Excel ABC realízase mediante a clasificación. Todos os elementos están ordenados de graxa a menos. A continuación, a cota acumulada de cada elemento calcúlase, en base a que se lle atribúe unha determinada categoría. Descubriremos como se usa a metodoloxía especificada na práctica.
Temos unha mesa cunha lista de produtos que vende a empresa e o número correspondente de ingresos da súa venda durante un determinado período de tempo. No fondo da mesa, o resultado dos ingresos en xeral sobre todos os nomes dos bens. Existe unha tarefa usando ABC Analysis, rompe estes produtos en grupos pola súa importancia para a empresa.
- Destacamos unha mesa co cursor pechando o botón esquerdo do rato, excluíndo a tapa ea cadea final. Vaia á pestana "Data". Facemos clic no botón "Ordenar", situado na barra de ferramentas "Ordenar e Filtrar" na cinta.
Tamén podes facer de forma diferente. Restauramos o rango de táboa anterior e, a continuación, trasládase á pestana "Home" e faga clic no botón "Ordenar e filtrar" situado na caixa de ferramentas de edición na cinta. A lista está activada na que se activa a posición "Clasificación personalizable".
- Ao aplicar calquera das accións anteriores, inicia a xanela de configuración de clasificación. Nós miramos para que os "Os meus datos contén o parámetro dos cabeceiras" foi definido como unha marca de verificación. No caso da súa ausencia, instala.
No campo "Columna", especifique o nome da columna na que se contén os datos de ingresos.
No campo "Ordenar", cómpre especificar, sobre o cal será ordenado criterio específico. Deixar a configuración predefinida - "Valores".
No campo "orde", exhibe a posición "descendente".
Despois do produto da configuración especificada, faga clic no botón "Aceptar" na parte inferior da xanela.
- Despois de realizar a acción especificada, todos os elementos foron ordenados por ingresos de máis a un menor.
- Agora debemos calcular a proporción de cada un dos elementos dun resultado xeral. Crea unha columna adicional para estes efectos, que chamamos "compartir peso". Na primeira célula desta columna, poñemos o sinal "=", despois de que especifique unha ligazón á cela na que a cantidade de ingresos da implementación do produto relevante é. A continuación, configure o sinal da división ("/"). Despois diso, indicamos as coordenadas da célula, que contén o importe total da venda de mercadorías en toda a empresa.
Dado o feito de que a fórmula especificada copiaremos a outras celas da columna "Compartir" polo marcador de recheo, a dirección da ligazón ao elemento que contén a cantidade final de ingresos á empresa, necesitamos corrixir. Para iso, faga unha ligazón absoluta. Seleccione as coordenadas da cela especificada na fórmula e prema a tecla F4. Antes das coordenadas, como vemos, apareceu un sinal de dólar, o que indica que a ligazón converteuse en absoluta. Nótese que a referencia á cantidade de ingresos do primeiro na lista de produtos (produto 3) debe permanecer relativa.
Entón, para facer cálculos, prema o botón Intro.
- Como vemos, a proporción de ingresos do primeiro produto especificada na lista apareceu na cela de destino. Para copiar a fórmula no intervalo de abaixo, poñemos o cursor á esquina inferior dereita da cela. A súa transformación no recheo, tendo unha mirada dunha pequena cruz. Facemos clic no botón esquerdo do rato e arrastre o marcador de recheo ata o final da columna.
- Como podes ver, toda a columna está chea de datos que caracterizan a proporción de ingresos desde a implementación de cada produto. Pero o valor da gravidade específica móstrase no formato numérico e necesitamos transformalo en porcentaxe. Para iso, resalte os contidos da columna "peso específico". Entón pasamos á pestana "Home". Na cinta do grupo Configuración do grupo hai un campo que mostra un formato de datos. Por defecto, se non produciu manipulacións adicionais, o formato "xeral" debe instalarse alí. Fai clic na icona en forma de triángulo situado á dereita deste campo. Na lista de formatos, seleccione a posición "Porcentaxe".
- Como vemos, todos os valores das columnas transformáronse en valores porcentuais. Como debería ser, o 100% indicado na cadea de "total". A porcentaxe de bens espérase na columna dun maior a un menor.
- Agora debemos crear unha columna na que se mostraría a participación acumulada cun resultado crecente. É dicir, en cada liña ao peso específico individual dun produto específico engadirase a proporción de todos aqueles produtos que están situados na lista de arriba. Para os primeiros produtos da lista (produto 3), a proporción individual e a acción acumulada serán iguais, pero todos os posteriores ao indicador individual deberán engadir a parte acumulada do elemento anterior da lista.
Así, na primeira liña transferimos á columna "Acumulado Compartir" o indicador da columna "específica".
- A continuación, configure o cursor á segunda cela da columna "Acumulado Share". Aquí temos que aplicar a fórmula. Poñemos o sinal "igual" e dobra os contidos da célula "a proporción" da mesma liña e os contidos da célula "acción acumulada" da cadea anterior. Todas as referencias son relativas reservadas, é dicir, non producimos ningunha manipulación con eles. Despois diso, realice un clic no botón Intro para mostrar o resultado final.
- Agora tes que copiar esta fórmula nas celas desta columna, que se colocan a continuación. Para iso, usamos o marcador de recheo ao que xa recorremos ao copiar a fórmula na columna de accións. Ao mesmo tempo, a cadea "total" non é necesaria, xa que o resultado acumulado é que o 100% mostrarase no último produto da lista. Como podes ver, todos os elementos da nosa columna despois foron cubertos.
- Despois diso, cree unha columna "grupo". Necesitaremos agrupar mercadorías por categoría A, B e C de acordo coa participación acumulada especificada. Como recordamos, todos os elementos distribúense por grupos segundo o seguinte réxime:
- A - ata o 80%;
- B - o 15% seguinte;
- C - O 5% restante.
Así, todos os produtos que acumulan a parte do peso específico está dentro do límite de ata o 80%, asignamos a categoría A. Os produtos co peso específico acumulado do 80% ao 95% están asignados á categoría B. O resto Grupo de mercadorías co valor de máis do 95% da gravidade específica acumulada que asignamos unha categoría C.
- Para a claridade, pode encher os grupos especificados con diferentes cores. Pero está a gusto.
Así, rompemos os elementos sobre o nivel de importancia, usando a análise ABC. Ao usar algunhas outras técnicas, como se mencionou anteriormente, úsase para entrar nun maior número de grupos, pero o principio de romper neste caso permanece case inalterado.
Lección: Clasificación e filtrado en Excel
Método 2: usando unha fórmula complexa
Por suposto, o uso da clasificación é a forma máis común de realizar unha análise ABC en Excele. Pero nalgúns casos é necesario levar a cabo esta análise sen reorganizar as liñas en lugares da táboa de orixe. Neste caso, unha fórmula complexa virá ao rescate. Por exemplo, utilizaremos a mesma táboa de orixe como no primeiro caso.
- Engadimos á táboa de orixe que contén o nome dos bens e os ingresos da venda de cada un deles, a columna "Grupo". Como podes ver, neste caso, podemos non engadir columnas co cálculo de fraccións individuais e acumulativas.
- Producimos a primeira asignación de celas na columna do grupo, despois de que realice un clic no botón "Inserir función" situada preto da Fórmula Fila.
- Masters activación de funcións. Pasamos á categoría "Ligazóns e arrays". Seleccione a función "Choice". Facemos un clic no botón "Aceptar".
- A xanela do argumento do xogo está activada. A sintaxe preséntase do seguinte xeito:
= Selección (número_intex; valor1; valor2; ...)
A tarefa desta característica é a retirada dun dos valores especificados, dependendo do número de índice. O número de valores pode chegar a 254, pero só necesitaremos tres nomes que correspondan a Categorías ABC-Analysis: A, B, C. Podemos entrar inmediatamente o símbolo "A" no campo "B" no "B" "Campo, campo" Value3 "-" C ".
- Pero co argumento "Número de índice" terá que ser completamente enganado construíndo varios operadores adicionais. Instala o cursor no campo "Número de índice". A continuación, faga clic no ícono cunha vista do triángulo, á esquerda do botón "Inserir función". Abre unha lista de operadores recentemente utilizados. Necesitamos unha función da busca. Xa que non está na lista, faga clic na inscrición "Outras funcións ...".
- A xanela da xanela de asistente de funcións é iniciada de novo. De novo, vai á categoría "Ligazóns e arrays". Atopamos a posición do "consello de busca", asignalo e facer un clic no botón "Aceptar".
- Abre os argumentos dos argumentos do operador de busca. A sintaxe diso ten o seguinte formulario:
= Buscar tarxeta (Search_name; visualización__massive; type_station)
O obxectivo desta función é definir o número de posición do elemento especificado. É dicir, só o que necesitamos para o campo de "número de índice" que ten unha selección.
No campo "Array Listing", pode pedir inmediatamente a seguinte expresión:
{0: 0.8: 0,95}
Debe ser precisamente en corchetes, como unha fórmula de matriz. Non é difícil adiviñar que estes números (0; 0,8; 0,95) denotan os límites da participación acumulada entre grupos.
O campo "Tipo de comparación" non é obrigatorio e, neste caso, non o encheremos.
No campo "Segundo valor", axusta o cursor. A continuación, a continuación, a través da icona descrita anteriormente en forma de triángulo, pasamos ao mago das funcións.
- Esta vez no asistente de funcións, facemos trasladarse á categoría "matemática". Escolla o nome "silencioso" e prema o botón "Aceptar".
- Lanzarase a xanela de argumentos de función. O operador especificado resume as células que cumpren a condición definitiva. A súa sintaxe é:
= Silencioso (gama; criterio; range_suming)
No campo "Range", introduza a dirección da columna "Receita". Para estes efectos, fixamos o cursor no campo e, a continuación, facendo o clip do botón esquerdo do rato, seleccione todas as celas da columna correspondente, excluíndo o valor "Total". Como podes ver, a dirección apareceu inmediatamente no campo. Ademais, necesitamos facer esta ligazón absoluta. Para iso, produce a súa asignación e faga clic na tecla F4. O enderezo foi lanzado por signos de dólar.
No campo "Criterion", necesitamos establecer unha condición. Introduza a seguinte expresión:
">"&
A continuación, inmediatamente despois introducimos a dirección da primeira cela da columna "Receita". Facemos coordenadas horizontalmente neste enderezo absoluto, engadindo o sinal de dólar do teclado diante da letra. As coordenadas verticais deixan relativas, é dicir, non debe haber ningún sinal diante do díxito.
Despois diso, non presionamos o botón "OK" e faga clic no nome da función de busca na cadea de fórmulas.
- Entón volvemos á xanela de argumentos da tarxeta de busca. Como vemos, no campo "Significado NOGULAR", os datos fixados polo operador quedou en silencio. Pero iso non é todo. Vaia a este campo e xa engade os datos dispoñibles para engadir un signo de "+" sen comiñas. Entón presentamos a dirección da primeira célula da columna "Receita". E de novo facemos coordenadas horizontalmente con absoluto e deixa verticalmente parentes.
A continuación, tomamos todos os contidos do campo "O valor desexado" nos parénteses, despois de que poñamos o sinal da división ("/"). Despois diso, de novo a través do ícono do triángulo, vai á función de seleccionar funcións.
- Do mesmo xeito que a última vez no asistente de funcións de execución, estamos a buscar o operador necesario na categoría "Matemática". Esta vez a función desexada chámase "sumas". Resaltámolo e presionamos o botón "Aceptar".
- Abre a xanela Argumentos do operador. O seu principal obxectivo é a suma de datos nas células. A sintaxe deste operador é bastante sinxela:
= Sums (número1; número2; ...)
Para os nosos propósitos, só será necesario o campo "número1". Presentamos nel as coordenadas da columna "Receita", eliminando a célula que contén os resultados. Xa realizamos unha operación como o campo "intervalo" da función. Como nese momento, as coordenadas de gama fan absoluta, destaca e presionando a tecla F4.
Despois diso, faga clic na tecla "Aceptar" na parte inferior da xanela.
- Como podes ver, o complexo das funcións introducidas fixo un cálculo e emitiu o resultado na primeira célula da columna "Grupo". O primeiro produto foi asignado a un grupo "A". A fórmula completa aplicada por nós para este cálculo é a seguinte:
= Elección (bordo de busca ((silencio ($ b $ 2: $ b $ 27; ">" & $ b2) + $ b2) / cantidades ($ b $ 2: $ b $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "C")
Pero, por suposto, en cada caso, as coordenadas nesta fórmula serán diferentes. Polo tanto, non se pode considerar universal. Pero, usando o manual que foi dado 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. Co fin de encher completamente a columna do grupo "Group", ten que copiar esta fórmula no intervalo de abaixo (eliminando a cela da cadea "Total") usando o marcador de recheo, como xa fixemos máis dunha vez. Despois de que se realicen os datos, pódese considerar a análise ABC realizada.
Como vemos, os resultados obtidos usando a opción usando unha fórmula complexa non son diferentes dos resultados que fomos realizados por clasificar. Todos os produtos son asignados as mesmas categorías, só ao mesmo tempo que as filas non cambiaron a súa posición inicial.
Lección: Máster de Funcións en Excele
O programa Excel é capaz de aliviar significativamente a análise ABC para o usuario. Isto conséguese usando tal ferramenta como a clasificación. Despois diso, a gravidade específica individual, a participación acumulada e, en realidade, a particionamento en grupos calcúlase. Nos casos en que non se permite o cambio na posición inicial das filas da táboa, pode aplicar o método utilizando unha fórmula complexa.