Um dos principais métodos de gerenciamento e logística é a análise ABC. Com ele, você pode classificar os recursos da empresa, bens, clientes, etc. De acordo com o grau de importância. Ao mesmo tempo, pelo nível de importância, cada uma das unidades acima é atribuída uma de três categorias: A, B ou C. Programa Excel tem em suas ferramentas de bagagem que facilitam a realização desse tipo de análise. Vamos descobrir como usá-los e qual é a análise ABC.
Usando análise ABC.
A análise da ABC é uma espécie de reforçada e adaptada às condições modernas para o princípio do Pareto. De acordo com o método de sua conduta, todos os elementos da análise são divididos em três categorias de acordo com o grau de importância:- Categoria A - elementos com uma combinação de mais de 80% da gravidade específica;
- Categoria B - elementos cuja combinação varia de 5% a 15% da gravidade específica;
- Categoria C - Os remanescentes elementos, o total agregado é de 5% e menos gravidade específica.
Empresas separadas usam técnicas mais avançadas e dividir itens para não 3, mas por 4 ou 5 grupos, mas confiaremos no clássico esquema de análise ABC.
Método 1: Análise com classificação
A análise do Excel ABC é realizada usando a classificação. Todos os itens são classificados de gordurosos para menos. Em seguida, a participação acumulativa de cada elemento é calculada, com base na qual é atribuído uma determinada categoria. Vamos descobrir como a metodologia especificada é usada na prática.
Temos uma tabela com uma lista de produtos que a empresa vende e o número correspondente de receitas de sua venda por um determinado período de tempo. Na parte inferior da tabela, o resultado da receita em geral sobre todos os nomes das mercadorias. Há uma tarefa usando análise ABC, quebra esses bens em grupos por sua importância para a empresa.
- Destacamos uma tabela com o cursor fechando o botão esquerdo do mouse, excluindo a tampa e a string final. Vá para a guia "Data". Clicamos no botão "Classificar", localizado na barra de ferramentas "Classificar e Filtro" na fita.
Você também pode fazer de maneira diferente. Alocamos o intervalo de tabela acima e, em seguida, movemos para a guia "Home" e clique no botão "Classificar e filtro" localizado na caixa de ferramentas de edição na fita. A lista é ativada na qual a posição "Classificação personalizável" é ativada.
- Ao aplicar qualquer uma das ações acima, a janela Configurações de classificação é lançada. Nós olhamos para que os "Meus dados contenham os cabeçalhos" paramétrico foi definido para uma marca de seleção. No caso de sua ausência, instale.
No campo "Coluna", especifique o nome da coluna na qual os dados de receita estão contidos.
No campo "Classificar", você precisa especificar, no qual os critérios específicos serão classificados. Deixe as configurações predefinidas - "Valores".
No campo "Ordem", exibir a posição "Descendente".
Após o produto das configurações especificadas, clique no botão "OK" na parte inferior da janela.
- Depois de executar a ação especificada, todos os itens foram classificados por receita de mais para um menor.
- Agora devemos calcular a proporção de cada um dos elementos para um resultado geral. Crie uma coluna adicional para esses fins, que chamamos de "compartilhamento de peso". Na primeira célula desta coluna, colocamos o sinal "=", após o qual você especifica um link para a célula na qual a quantidade de receita da implementação do produto relevante é. Em seguida, defina o sinal da divisão ("/"). Depois disso, indicamos as coordenadas da célula, que contêm o montante total da venda de mercadorias em toda a empresa.
Dado o fato de que a fórmula especificada, copiaremos para outras células da coluna "Compartilhar" pelo marcador de enchimento, o endereço do link para o elemento que contém a quantidade final de receita para a empresa, precisamos consertar. Para fazer isso, faça um link absoluto. Selecione as coordenadas da célula especificada na fórmula e pressione a tecla F4. Antes das coordenadas, como vemos, apareceu um sinal de dólar, o que indica que o link se tornou absoluto. Deve-se notar que a referência ao montante da receita do primeiro na lista de mercadorias (produto 3) deve permanecer relativa.
Em seguida, para fazer cálculos, pressione o botão ENTER.
- Como vemos, a proporção de receita do primeiro produto especificado na lista apareceu na célula-alvo. Para copiar a fórmula no intervalo abaixo, colocamos o cursor para o canto inferior direito da célula. Sua transformação no marcador de enchimento, tendo uma aparência de uma pequena cruz. Clicamos no botão esquerdo do mouse e arraste o marcador de preenchimento até o final da coluna.
- Como você pode ver, toda a coluna é preenchida com dados que caracteriza a proporção de receita da implementação de cada produto. Mas o valor da gravidade específica é exibido no formato numérico, e precisamos transformá-lo em porcentagem. Para fazer isso, destaque o conteúdo da coluna "Peso específico". Em seguida, passamos para a guia "Home". Na fita no grupo de configurações do grupo, há um formato de dados de exibição de campo. Por padrão, se você não produziu manipulações adicionais, o formato "geral" deve ser instalado lá. Clique no ícone na forma de um triângulo localizado à direita deste campo. Na lista de formatos, selecione a posição "porcentagem".
- À medida que vemos, todos os valores da coluna foram transformados em valores percentuais. Como deveria ser, 100% indicado na sequência de "total". A parte das mercadorias é esperada na coluna de um maior para um menor.
- Agora devemos criar uma coluna na qual a participação acumulada com um resultado crescente seria exibida. Isto é, em cada linha para o peso específico individual de um produto específico, será adicionado a proporção de todos os produtos que estão localizados na lista acima. Para os primeiros bens da lista (produto 3), a proporção individual e a ação acumulada serão iguais, mas todas subsequentes ao indicador individual precisarão adicionar a participação acumulada do elemento anterior da lista.
Assim, na primeira linha, somos transferidos para a coluna "Compartilhar acumulado", o indicador da coluna "específico".
- Em seguida, defina o cursor para a segunda célula da coluna "Compartilhar Acumulado". Aqui temos que aplicar a fórmula. Colocamos o sinal "igual" e dobramos o conteúdo da célula "a proporção" da mesma linha e o conteúdo da célula "Compartilho acumulado" da string acima. Todas as referências são reservadas relativas, isto é, não produzimos manipulações com elas. Depois disso, execute um clique no botão ENTER para exibir o resultado final.
- Agora você precisa copiar esta fórmula nas células desta coluna, que são colocadas abaixo. Para fazer isso, usamos o marcador de enchimento para o qual já recorremos ao copiar a fórmula na coluna Share. Ao mesmo tempo, a string "Total" não é necessária, uma vez que o resultado acumulado é 100% será exibido no último produto da lista. Como você pode ver, todos os elementos da nossa coluna depois estavam cheios.
- Depois disso, crie uma coluna "grupo". Precisamos agrupar mercadorias por categoria A, B e C de acordo com a participação acumulada especificada. Como nos lembramos, todos os elementos são distribuídos por grupos de acordo com o seguinte esquema:
- A - até 80%;
- B - os seguintes 15%;
- C - os restantes 5%.
Assim, todos os bens que acumulam a participação do peso específico é dentro da fronteira de até 80%, atribuímos a categoria A. Os produtos com o peso específico acumulado de 80% a 95% são atribuídos à categoria B. Grupo de bens com o valor de mais de 95% da gravidade específica acumulada, atribuímos uma categoria C.
- Para clareza, você pode preencher os grupos especificados com cores diferentes. Mas é à vontade.
Assim, quebramos os elementos no nível de importância, usando a análise ABC. Ao usar algumas outras técnicas, como mencionado acima, ele é usado para quebrar em um número maior de grupos, mas o princípio de quebrar neste caso permanece quase inalterado.
Lição: Classificando e filtragem no Excel
Método 2: Usando uma fórmula complexa
Claro, o uso de classificação é a maneira mais comum de realizar análise ABC em Excele. Mas, em alguns casos, é necessário realizar esta análise sem reorganizar linhas em lugares na tabela de origem. Neste caso, uma fórmula complexa virá ao resgate. Por exemplo, usaremos a mesma tabela de origem no primeiro caso.
- Adicionamos à tabela de origem contendo o nome das mercadorias e a receita da venda de cada uma delas, a coluna "Grupo". Como você pode ver, neste caso, não podemos adicionar colunas com o cálculo de frações individuais e acumulativas.
- Produzimos a primeira alocação de células na coluna do grupo, após o qual você executa um clique no botão "Inserir função" localizado perto da linha de fórmula.
- Mestrado ativação de funções. Nós nos movemos para a categoria "links e matrizes". Selecione a função "escolha". Nós formamos um clique no botão "OK".
- A janela do argumento do jogo é ativada. A sintaxe é apresentada da seguinte forma:
= Seleção (number_intex; valor1; valor2; ...)
A tarefa desse recurso é a retirada de um dos valores especificados, dependendo do número do índice. O número de valores pode chegar a 254, mas precisaremos de apenas três nomes que correspondem às categorias de análise ABC: A, B, C. Podemos inserir imediatamente o símbolo "A" no campo "B" B "Campo, campo" Value3 "-" C ".
- Mas com o argumento "Número do índice" terá que ser completamente intermitente construindo vários operadores adicionais. Instale o cursor no campo "Index Number". Em seguida, clique no ícone com uma visão do triângulo, à esquerda do botão "Inserir Função". Uma lista de operadores recém-usados é aberta. Precisamos de uma função da pesquisa. Como não está na lista, clicamos na inscrição "Outras funções ...".
- A janela da janela Wizard de funções é iniciada novamente. Mais uma vez, vá para a categoria "links e matrizes". Encontramos a posição da "placa de pesquisa", aloque-a e clique no botão "OK".
- Os argumentos dos argumentos do operador de pesquisa são abertas. A sintaxe tem a seguinte forma:
= Pesquisa (pesquisa_name; visualização__massive; type_station)
O objetivo desta função é definir o número de posição do item especificado. Ou seja, o que precisamos para o campo "Número do índice" uma escolha.
No campo "Matriz de listagem", você pode perguntar imediatamente à seguinte expressão:
{0: 0,8: 0,95}
Deve ser precisamente em colchetes encaracolados, como uma fórmula de matriz. Não é difícil adivinhar que esses números (0; 0,8; 0,95) denotam os limites da participação acumulada entre os grupos.
O campo "Tipo de comparação" não é obrigatório e, neste caso, não o preencheremos.
No campo "Segundo valor", defina o cursor. Em seguida, novamente através do ícone descrito acima na forma de um triângulo, passamos para o assistente das funções.
- Desta vez no Assistente de funções, fazemos mudar para a categoria "matemática". Escolha o nome "silencioso" e pressione o botão "OK".
- A janela Argumentos de Função será lançada. O operador especificado resume as células que atendem à condição definida. Sua sintaxe é:
= Silencioso (intervalo; critério; rango_suming)
No campo "Range", insira o endereço da coluna "Receita". Para esses propósitos, definimos o cursor no campo e, em seguida, tornando o clipe do botão esquerdo do mouse, selecione todas as células da coluna correspondente, excluindo o valor "total". Como você pode ver, o endereço imediatamente apareceu no campo. Além disso, precisamos tornar este link absoluto. Para fazer isso, produza sua alocação e clique na tecla F4. O endereço foi liberado por sinais de dólar.
No campo "critério", precisamos definir uma condição. Digite a seguinte expressão:
">"&
Em seguida, imediatamente depois de entrarmos no endereço da primeira célula da coluna "Receita". Fazemos coordenadas horizontalmente neste endereço absoluto, adicionando o cifrão do teclado na frente da carta. Coordenadas verticais deixam parentes, isto é, não deve haver sinal na frente do dígito.
Depois disso, não pressione o botão "OK", e clique no nome da função de pesquisa na cadeia de fórmula.
- Em seguida, retornar à janela argumentos do conselho de pesquisa. Como podemos ver, no campo "Significado Fogular", o conjunto de dados pelo operador ficou em silêncio. Mas isso não é tudo. Vá a este campo e já contribui para os dados disponíveis para adicionar um sinal de "+" sem aspas. Então nós introduzimos o endereço da primeira célula da coluna "Receita". E mais uma vez fazemos coordenadas horizontalmente com absoluta, e verticalmente deixar relativa.
Em seguida, tomamos todos os conteúdos do campo "o valor desejado" entre parênteses, após o qual vamos colocar o sinal de divisão ( "/"). Depois disso, mais uma vez através do ícone de triângulo, vá para a função de seleção de funções.
- Como a última vez no assistente função funcionando, nós estamos olhando para o operador necessária na categoria "matemática". Desta vez, a função desejada é chamado de "somas". Nós destacamos e pressionamos o botão "OK".
- janela argumentos operador abre. Seu principal objetivo é o somatório de dados nas células. A sintaxe deste operador é bastante simples:
= Somas (número1; número2; ...)
Para nossos propósitos, será necessário apenas o campo "number1". Nós introduzimos nele as coordenadas da coluna "Receita", eliminando a célula que contém os resultados. Nós já executou uma operação como no campo "Intervalo" da função. Como naquele tempo, as coordenadas alcance fazer absoluta, destacando-os e pressionando a tecla F4.
Depois disso, clique no botão "OK" na parte inferior da janela.
- Como você pode ver, o complexo das funções entrado feito um cálculo e emitido o resultado na primeira célula da coluna "Grupo". O primeiro produto foi atribuído um grupo "A". A fórmula completa aplicado por nós para este cálculo é a seguinte:
= Choice (placa de pesquisa ((silent ($ b $ 2: $ b $ 27; ">" & $ b2) + $ B2) / quantidades ($ b $ 2: $ b $ 27); {0: 0,8: 0 , 95}); "Um", "B", "C")
Mas, é claro, em cada caso, as coordenadas nesta fórmula será diferente. Portanto, não pode ser considerado universal. Mas, usando o manual que foi dado acima, você pode inserir as coordenadas de qualquer tabela e com êxito aplicar este método em qualquer situação.
- Entretanto, isso não é tudo. Foram calculados apenas para a primeira linha da tabela. Para preencher plenamente no grupo coluna "Grupo", você precisa copiar esta fórmula na faixa abaixo (eliminando a célula do string "Total"), utilizando o marcador de enchimento, como já fizemos mais de uma vez. Depois que os dados é feita, a análise ABC pode ser considerado realizado.
À medida que vemos, os resultados obtidos usando a opção usando uma fórmula complexa não são diferentes desses resultados que foram realizados pela classificação. Todas as mercadorias são atribuídas as mesmas categorias, apenas ao mesmo tempo as linhas não alteraram sua posição inicial.
Lição: Mestrado em Funções em Excele
O programa do Excel é capaz de aliviar significativamente a análise do ABC para o usuário. Isso é conseguido usando uma ferramenta como classificação. Depois disso, a gravidade específica individual, ação acumulada e, na verdade, particionar em grupos é calculada. Nos casos em que a mudança na posição inicial das linhas na tabela não é permitida, você pode aplicar o método usando uma fórmula complexa.