Uno dei metodi chiave della gestione e della logistica è l'analisi ABC. Con esso, puoi classificare le risorse dell'impresa, delle merci, dei clienti, ecc. In base al grado di importanza. Allo stesso tempo, dal livello di importanza, ciascuna delle suddette unità è assegnata una delle tre categorie: A, B o C. Programma Excel ha nei suoi strumenti del bagaglio che rendono più facile svolgere questo tipo di analisi. Scopriamo come usarli e qual è l'analisi ABC.
Usando l'analisi ABC.
L'analisi ABC è una sorta di miglioramento e adattata alle condizioni moderne per il principio di Pareto. Secondo il metodo della sua condotta, tutti gli elementi dell'analisi sono suddivisi in tre categorie in base al grado di importanza:- Categoria A - elementi aventi una combinazione di oltre l'80% della gravità specifica;
- Categoria B - Elementi la cui combinazione varia dal 5% al 15% della gravità specifica;
- Categoria C - Gli elementi rimanenti, il cui totale aggregato è il 5% e una gravità meno specifica.
Le aziende separate utilizzano tecniche più avanzate e dividere elementi non a 3, ma da 4 o 5 gruppi, ma faremo affidamento sul classico schema di analisi ABC.
Metodo 1: analisi con ordinamento
L'analisi Excel ABC viene eseguita utilizzando l'ordinamento. Tutti gli articoli sono ordinati da grassi a meno. Quindi la quota accumulativa di ciascun elemento è calcolata, sulla base della quale viene assegnata una determinata categoria. Scopriamo come viene utilizzata la metodologia specificata nella pratica.
Abbiamo un tavolo con un elenco di prodotti che vende la società e il corrispondente numero di ricavi della loro vendita per un certo periodo di tempo. In fondo alla tabella, il risultato delle entrate in generale su tutti i nomi dei beni. C'è un compito che utilizza l'analisi ABC, rompi questi beni in gruppi con la loro importanza per l'impresa.
- Evidenzia un tavolo con il cursore chiudendo il tasto sinistro del mouse, escludendo il tappo e la stringa finale. Vai alla scheda "Dati". Facciamo clic sul pulsante "Ordina", situato nella barra degli strumenti "Ordina e filtro" sul nastro.
Puoi anche fare diversamente. Assegniamo la catena di tabella sopra, quindi passiamo alla scheda "Home" e fare clic sul pulsante "Ordina e filtro" Situato nella casella degli strumenti di modifica sul nastro. L'elenco è attivato in cui è attivata la posizione "ordinamento personalizzabile".
- Quando si applica una delle azioni di cui sopra, avvia la finestra delle impostazioni di ordinamento. Guardiamo in modo che il parametro "I miei dati contiene il parametro" sia stato impostato su un segno di spunta. Nel caso della sua assenza, installare.
Nel campo "Column", specificare il nome della colonna in cui i dati delle entrate sono contenuti.
Nel campo "Ordina", è necessario specificare, su cui verranno ordinati criteri specifici. Lasciare le impostazioni preimpostate - "Valori".
Nel campo "Ordina", esibisci la posizione "discendente".
Dopo il prodotto delle impostazioni specificate, fare clic sul pulsante "OK" nella parte inferiore della finestra.
- Dopo aver eseguito l'azione specificata, tutti gli elementi sono stati ordinati per entrate da più a un minimo.
- Ora dovremmo calcolare la proporzione di ciascuno degli elementi per un risultato generale. Creare una colonna aggiuntiva per questi scopi, che chiamiamo "peso azionario". Nella prima cella di questa colonna, mettiamo il segno "=", dopo il quale si specifica un collegamento alla cella in cui è la quantità di entrate derivanti dall'attuazione del prodotto pertinente. Quindi, imposta il segno della divisione ("/"). Successivamente, indichiamo le coordinate della cella, che contiene l'importo totale della vendita di merci in tutta l'azienda.
Dato il fatto che la formula specificata che coperemo ad altre celle della colonna "Condividi" dal marker di riempimento, l'indirizzo del collegamento all'elemento contenente la quantità finale di entrate per l'impresa, dobbiamo risolvere. Per fare questo, fai un link assoluto. Selezionare le coordinate della cella specificata nella formula e premere il tasto F4. Prima delle coordinate, come vediamo, è apparso un segno del dollaro, il che indica che il collegamento è diventato assoluto. Va notato che il riferimento alla quantità di entrate del primo nell'elenco delle merci (Prodotto 3) dovrebbe rimanere parente.
Quindi, per creare calcoli, premere il pulsante ENTER.
- Come vediamo, la percentuale di entrate derivanti dal primo prodotto specificato nell'elenco è apparso nella cella di destinazione. Per copiare la formula nella gamma di seguito, mettiamo il cursore nell'angolo in basso a destra della cella. La sua trasformazione nel marker di riempimento, con un'espressione di una piccola croce. Facciamo clic sul pulsante sinistro del mouse e trasciniamo il marker di riempimento fino alla fine della colonna.
- Come puoi vedere, l'intera colonna è piena di dati che caratterizzano la percentuale di entrate dall'attuazione di ciascun prodotto. Ma il valore della gravità specifica viene visualizzato nel formato numerico e abbiamo bisogno di trasformarlo in percentuale. Per fare ciò, evidenziare il contenuto della colonna "Peso specifico". Quindi passiamo alla scheda "Home". Sul nastro nel gruppo Impostazioni Group è presente un campo di visualizzazione del formato dei dati. Per impostazione predefinita, se non hai prodotto manipolazioni aggiuntive, il formato "Generale" deve essere installato lì. Clicca sull'icona sotto forma di un triangolo situato a destra di questo campo. Nell'elenco dei formati, selezionare la posizione "percentuale".
- Come vediamo, tutti i valori della colonna sono stati trasformati in valori percentuali. Come dovrebbe essere, il 100% indicato nella stringa di "totale". La quota di merci è prevista nella colonna da un più grande a un piccolo.
- Ora dovremmo creare una colonna in cui verrà visualizzata la quota accumulata con un risultato crescente. Cioè, in ogni riga al singolo peso specifico di un prodotto specifico verrà aggiunta la proporzione di tutti quei prodotti che si trovano nell'elenco sopra. Per i primi beni nell'elenco (Prodotto 3), la proporzione individuale e la quota accumulata saranno uguali, ma tutto ciò che è successivo al singolo indicatore dovrà aggiungere la quota accumulata dell'elemento precedente dell'elenco.
Quindi, in prima linea siamo trasferiti alla colonna "Accumulato condividi" L'indicatore della colonna "specifica".
- Quindi, impostare il cursore sulla seconda cella della colonna "Accumulate Share". Qui dobbiamo applicare la formula. Mettiamo il segno "uguale" e piegiamo il contenuto della cella "la proporzione" della stessa linea e il contenuto della cella "accumulato" dalla stringa sopra. Tutti i riferimenti sono riservati relativi, cioè, non produciamo alcuna manipolazione con loro. Dopodiché, eseguire un clic sul pulsante ENTER per visualizzare il risultato finale.
- Ora è necessario copiare questa formula nelle celle di questa colonna, che sono posizionate di seguito. Per fare ciò, usiamo il marker di riempimento a cui abbiamo già fatto ricorso quando si copia la formula nella colonna AZIENDA. Allo stesso tempo, la stringa "Totale" non è necessaria, poiché il risultato accumulato è il 100% verrà visualizzato sull'ultimo prodotto dall'elenco. Come puoi vedere, tutti gli elementi della nostra colonna dopo essere stati riempiti.
- Dopodiché, crea una colonna "Gruppo". Avremo bisogno di raggruppare le merci per categoria A, B e C secondo la quota accumulata specificata. Come ricordiamo, tutti gli elementi sono distribuiti da gruppi secondo il seguente schema:
- A - fino all'80%;
- B - il 15% successivo;
- C - il restante 5%.
Pertanto, tutti i beni che accumulano la quota del peso specifico del quale è al confine fino all'80%, assegniamo la categoria A. Le merci con il peso specifico accumulato dall'80% al 95% sono assegnate alla categoria B. il restante Gruppo di merci con il valore di oltre il 95% della gravità specifica accumulata che assegniamo una categoria C.
- Per chiarezza, è possibile riempire i gruppi specificati con colori diversi. Ma è a volontà.
Pertanto, abbiamo rotto gli elementi sul livello di importanza, utilizzando l'analisi ABC. Quando si utilizzano alcune altre tecniche, come menzionato sopra, è usato per rompere un numero maggiore di gruppi, ma il principio di rottura in questo caso rimane quasi invariato.
Lezione: Ordinamento e filtraggio in Excel
Metodo 2: usando una formula complessa
Naturalmente, l'uso dell'ordinamento è il modo più comune per condurre l'analisi ABC in Excele. Ma in alcuni casi è necessario eseguire questa analisi senza riorganizzare linee in luoghi nel tavolo di origine. In questo caso, una formula complessa arriverà in soccorso. Ad esempio, useremo lo stesso tavolo di origine come nel primo caso.
- Aggiungiamo alla tabella di origine contenente il nome della merce e le entrate dalla vendita di ciascuna di esse, il "gruppo" della colonna. Come puoi vedere, in questo caso, potremmo non aggiungere colonne con il calcolo delle frazioni individuali e accumulative.
- Produciamo la prima assegnazione della cella nella colonna Gruppo, dopo di che si esegue un clic sul pulsante "Funzione inserisci" situato vicino alla riga di formula.
- Attivazione dei master delle funzioni. Spostiamo nella categoria "Link e array". Seleziona la funzione "Scelta". Facciamo un clic sul pulsante "OK".
- La finestra di argomento del gioco è attivata. La sintassi è presentata come segue:
= Selezione (numero_intex; valore1; valore2; ...)
Il compito di questa funzione è il ritiro di uno dei valori specificati, a seconda del numero di indice. Il numero di valori può raggiungere 254, ma avremo bisogno solo di tre nomi che corrispondono alle categorie di analisi ABC: A, B, C. Possiamo immediatamente entrare nel simbolo "A" nel campo "B" nella "B" "Campo, campo" valore3 "-" c ".
- Ma con l'argomento "Numero indice" dovrà essere accuratamente tonata costruendo diversi operatori aggiuntivi. Installare il cursore nel campo "Numero indice". Quindi, fare clic sull'icona con una vista del triangolo a sinistra del pulsante "Inserisci funzione". Si apre un elenco di operatori appena usati. Abbiamo bisogno di una funzione della ricerca. Dal momento che non è nella lista, quindi clicziamo sulla scritta "altre funzioni ...".
- La finestra della finestra delle funzioni guidata viene nuovamente avviata. Ancora una volta, vai alla categoria "Link e array". Troviamo la posizione della "scheda di ricerca", allocatela e fai un clic sul pulsante "OK".
- Si apre gli argomenti degli argomenti dell'operatore di ricerca. La sintassi di esso ha il seguente modulo:
= Scheda di ricerca (search_name; visualizzazione__massive; type_station)
Lo scopo di questa funzione è definire il numero di posizione dell'articolo specificato. Cioè, proprio ciò di cui abbiamo bisogno per il campo "Numero indice" presenta una scelta.
Nel campo "Array" di quotazione ", puoi immediatamente chiedere la seguente espressione:
{0: 0.8: 0,95}
Dovrebbe essere preciso nelle parentesi ricci, come formula di array. Non è difficile indovinare che questi numeri (0; 0.8; 0.95) deniano i confini della quota accumulata tra i gruppi.
Il campo "Tipo di confronto" non è obbligatorio e in questo caso non lo riemperemo.
Nel campo "Secondo valore", impostare il cursore. Successivamente di nuovo attraverso l'icona sopra descritta sotto forma di un triangolo, passiamo alla procedura guidata delle funzioni.
- Questa volta nella procedura guidata funzioni, facciamo trasferirti nella categoria "Matematica". Scegli il nome "silenzioso" e premi il pulsante "OK".
- Verrà lanciata la finestra Argomenti della funzione. L'operatore specificato riassume le celle che soddisfano la condizione definita. La sua sintassi è:
= Silenzioso (intervallo; criterio; range_suming)
Nel campo "Range", inserire l'indirizzo della colonna "Entrate". Per questi scopi, abbiamo impostato il cursore nel campo, quindi rendendo la clip del pulsante sinistro del mouse, selezionare tutte le celle della colonna corrispondente, escludendo il valore "totale". Come puoi vedere, l'indirizzo è apparso immediatamente sul campo. Inoltre, dobbiamo rendere questo link assoluto. Per fare ciò, produrre la sua allocazione e fare clic sul tasto F4. L'indirizzo è stato rilasciato dai cartelli del dollaro.
Nel campo "Criterio", dobbiamo impostare una condizione. Inserisci la seguente espressione:
">"&
Quindi subito dopo, inseriamo l'indirizzo della prima cella della colonna "Entrate". Facciamo coordinate orizzontalmente in questo indirizzo assoluto, aggiungendo il cartello del dollaro dalla tastiera davanti alla lettera. Le coordinate verticali lasciano relativo, cioè, non dovrebbe esserci alcun segno davanti alla cifra.
Dopodiché, non premiamo il pulsante "OK" e fare clic sul nome della funzione di ricerca nella stringa di formula.
- Quindi torneremo alla finestra Argomenti della scheda di ricerca. Come vediamo, nel campo "Significato fogulare", i dati impostati dall'operatore erano silenziosi. Ma non è tutto. Vai a questo campo e già aggiunge ai dati disponibili per aggiungere un segno "+" senza virgolette. Quindi introduciamo l'indirizzo della prima cella della colonna "Entrate". E di nuovo facciamo coordinate orizzontalmente con assoluta e lasciano verticalmente relativo.
Successivamente, prendiamo tutti i contenuti del campo "il valore desiderato" tra le parentesi, dopo di che mettiamo il segno della divisione ("/"). Dopo di ciò, di nuovo attraverso l'icona del triangolo, vai alla funzione di selezionare le funzioni.
- Come l'ultima volta nella procedura guidata della funzione di corsa, stiamo cercando l'operatore necessario nella categoria "matematica". Questa volta la funzione desiderata è chiamata "somme". Li evidenziamo e premi il pulsante "OK".
- Si apre la finestra degli argomenti dell'operatore. Il suo scopo principale è la sommazione dei dati nelle cellule. La sintassi di questo operatore è abbastanza semplice:
= Somme (numero1; numero2; ...)
Per i nostri scopi, sarà necessario solo il campo "Number1". Introduciamo in esso le coordinate della colonna "Entrate", eliminando la cella che contiene i risultati. Abbiamo già eseguito tale operazione nel campo "Range" della funzione. Come in quel momento, le coordinate della gamma fanno assoluta, evidenziandole e premendo il tasto F4.
Dopodiché, fai clic sul tasto "OK" nella parte inferiore della finestra.
- Come puoi vedere, il complesso delle funzioni inserite ha effettuato un calcolo e ha emesso il risultato nella prima cella del "Gruppo" della colonna. Il primo prodotto è stato assegnato un gruppo "A". La formula completa applicata da noi per questo calcolo è la seguente:
= Scelta (scheda di ricerca ((silenzioso ($ B $ 2: $ B $ 27; ">" & $ B2) + $ B2) / importi ($ B $ 2: $ B $ 27); {0: 0.8: 0 , 95}); "A"; "B"; "c")
Ma, naturalmente, in ogni caso, le coordinate in questa formula saranno diverse. Pertanto, non può essere considerato universale. Ma, utilizzando il manuale che è stato dato sopra, è possibile inserire le coordinate di qualsiasi tabella e applicare con successo questo metodo in qualsiasi situazione.
- Tuttavia, questo non è tutto. Abbiamo calcolato solo per la prima riga del tavolo. Per inserire completamente la colonna "Gruppo" del gruppo, è necessario copiare questa formula nel range di seguito (eliminando la cella della stringa "totale") utilizzando il marcatore di riempimento, come abbiamo già fatto più di una volta. Dopo che i dati vengono effettuati, l'analisi ABC può essere considerata eseguita.
Come vediamo, i risultati ottenuti utilizzando l'opzione utilizzando una formula complessa non sono diversi da tali risultati che siamo stati eseguiti dall'ordinamento. Tutte le merci sono assegnate le stesse categorie, solo allo stesso tempo le righe non hanno cambiato la loro posizione iniziale.
Lezione: Maestro delle funzioni in Excele
Il programma Excel è in grado di alleviare in modo significativo l'analisi ABC per l'utente. Questo è ottenuto utilizzando un tale strumento come ordinamento. Successivamente, viene calcolata la quota di gravità specifica individuale, accumulata e, in realtà, viene calcolato il partizionamento in gruppi. Nei casi in cui non è consentita la modifica nella posizione iniziale delle righe nella tabella, è possibile applicare il metodo utilizzando una formula complessa.