In questo tutorial impariamo ad usare Excel per generare un’applicazione di gestione del magazzino, ottimizzando le operazioni di carico e scarico in modo da tenere sotto controllo la soglia minima di merce. L’applicazione sarà costituita da tre fogli di lavoro:
- PRODOTTI, con tabella in cui registrare la merce in magazzino, il fornitore e i pezzi in giacenza (calcolati sulla base delle forniture del foglio successivo): ogni volta che la giacenza raggiunge una soglia di guardia verrà segnalata la necessità di fornitura ulteriore.
- CARICO, con un calendario dove inserire data, prodotto e quantità di merce in magazzino, preventivamente registrata nel foglio precedente.
- SCARICO, con le merci in uscita: durante la trascrizione delle informazioni comparirà accanto ai dati la quantità residua del prodotto così da sapere quanti pezzi potranno essere scaricati. Se che ne indicheremo più di quanto presenti in giacenza, un messaggio di errore obbligherà a ridigitare un valore accettabile.
=> La scheda di magazzino con EXCEL
Tabella Prodotti
Posizioniamoci nel foglio “Prodotti” e digitiamo rispettivamente sulle celle A1;B1;C1 “Prodotti”, “Fornitori” e “Pezzi in giacenza”. Formattiamo le celle scegliendo dimensioni, carattere e colore e popoliamo la tabella.
Tabella Carico
Posizioniamoci nel foglio “Carico”, digitiamo rispettivamente nelle celle A1; B1; C1 e D1: “Data”, “Prodotto”, “Fornitore” e “N° Pezzi” e formattiamo a piacimento. La tabella servirà per archiviare le fasi di carico del magazzino (data di fornitura, merce, fornitore e quantità).
Menù di selezione
Generiamo un menù a tendina dal quale selezionare il prodotto da caricare scegliendolo fra quelli presenti nel primo foglio (“Prodotti”).
Scegliamo le celle su cui agire, nel nostro esempio l’intervallo B2:B5000. Selezioniamo la barra multifunzione “Dati” e clicchiamo su “Convalida dati”.
Nella finestra, posizioniamoci nella prima scheda (“Impostazioni”), apriamo il menù “Consenti” e selezioniamo “Elenco”. Nel campo “Origine” digitiamo =PRODOTTI!$A$2:$A$5000
L’intervallo di celle da noi scelto è volutamente ampio per poter archiviare una gran mole di merce. Clicchiamo OK.
Impostiamo ora le celle della colonna “Fornitori” in modo tale che, una volta scelto il prodotto, ci venga restituito automaticamente il fornitore di riferimento. Clicchiamo su C2 e digitiamo la formula: =SE(B2=””;””;CERCA(B2;PRODOTTI!$A:$A;PRODOTTI!$B:$B))
Se B2=”” (cioè è vuota) nella cella C2, dove ci siamo posizionati, non verrà scritto nulla (“”), altrimenti verrà cercato il contenuto della cella B2 nella colonna A del foglio “Prodotti” e verrà trascritto (sempre su C2) il corrispondente valore della colonna B dello stesso foglio (cioè il fornitore).
Al termine trasciniamo il quadratino di riempimento della cella C2 per ricopiare la formula nelle celle sottostanti. Nel nostro esempio fino a C5000.
Proviamo ora a collegare i primi due fogli per sommare quantità di merce inserita nel foglio “Carico” e quantità di merce presente nel foglio “Prodotti”. A tale scopo posizioniamoci nel foglio Prodotti e nella cella C2 digitiamo la formula
=(SOMMA.SE(CARICO!$B$2:$B$5000;PRODOTTI!A2;CARICO!$D$2:$D$5000))-(SOMMA.SE(SCARICO!$B$2:$B$5000;PRODOTTI!A2;SCARICO!$C$2:$C$5000))
Al termine ricopiamo la formula fino a C5000.
Proviamo ora a compilare il foglio “Carico”: la quantità inserita verrà aggiunta a quella dei prodotti corrispondenti, nel primo foglio. Il valore ottenuto costituirà la giacenza del magazzino.
Lasciando così le cose, tuttavia, avremmo per assurdo la possibilità di inserire delle quantità di carico negative. Eseguendo i punti seguenti imporremo una regola che ce lo impedisca: selezioniamo le celle su cui agire, nel nostro esempio l’intervallo D2:D5000, attiviamo la barra multifunzione “Dati” e clicchiamo su “Convalida dati”.
Apriamo il menù “Consenti” e selezioniamo la voce “Personalizzato”. Nel campo “Formula” digitiamo =SE(D2<0;FALSO;VERO)
Facciamo click (in alto) nella scheda “Messaggio di errore” e digitiamo un messaggio di errore che venga visualizzato all’utente tutte le volte che questo scriverà un valore negativo. Confermiamo col tasto OK
=> Gestione ordini con Excel con magazzino limitato
Scarico Merce
Posizioniamoci nel foglio “SCARICO” e scriviamo nelle celle A1, B1 e C1 rispettivamente “Data”, “Prodotto venduto” e “N° Pezzi Venduti”, dopodiché formattiamole a piacimento.
Una volta ricreata la tabella, potete impostare nelle celle della colonna B dei menù a tendina che consentano di scegliere la merce archiviata nel primo foglio (“PRODOTTI”), a tale scopo rieseguiamo le procedure già viste per il foglio “CARICO”.
Facciamo ora in modo che, una volta selezionato un prodotto, ci compaia accanto il numero di pezzi presenti in magazzino. Nella cella D2 trascriviamo la formula:
=SE(B2=””;””;SE(C2=””;CERCA.VERT(B2;PRODOTTI!$A:$C;3;FALSO);””))
Nella cella E2: =SE(D2=””;””;”Pezzi in magazzino”)
Al termine ricopiamo le formule nelle celle sottostanti.
Ora non appena inseriremo una data e selezioneremo un prodotto, ci comparirà a lato la giacenza dello stesso. Potremo così digitare il “N° pezzi venduti”, verificando facilmente la giacenza in magazzino per quel prodotto. Ovviamente il valore trascritto non dovrà superare il deposito. Per dare risalto potete colorare i caratteri delle colonne D ed E di rosso.
Per evitare che venga inserito un valore superiore alla giacenza magazzino, nella cella I2 trascriviamo la formula: =CERCA.VERT(B2;PRODOTTI!$A:$C;3;FALSO) e ricopiamo nelle celle sottostanti. Per evitare confusioni, nascondiamo la colonna I cliccando col tasto destro nell’intestazione della colonna e poi su “NASCONDI”.
Passiamo ora ad impostare il messaggio di errore Selezionando le celle C2:C5000 e richiamando la finestra “Convalida dati” (vista più volte in precedenza).
Nella scheda “Impostazioni” selezioniamo dal menù “Consenti” la voce “Personalizzato” e trascriviamo nel campo “Formula”… =SE(I2<0;FALSO;VERO). Successivamente nella scheda “Messaggio di errore” personalizziamo il testo del messaggio. Terminiamo col tasto OK.
Formattazione Condizionale
Con la procedura seguente faremo in modo di evidenziare lei merci la cui quantità giacente in magazzino ha raggiunto una soglia minima di sicurezza, oltre la quale sarebbe meglio non scendere per evitare di rimanere sprovvisti del prodotto.
Dopo aver assunto a 5 il valore appena citato, posizioniamoci sul foglio PRODOTTI, selezioniamo le celle C2:C5000, posizioniamoci sulla barra HOME e clicchiamo sul pulsante “Formattazione condizionale” che darà accesso ad un menù, all’interno del quale selezioniamo “Nuova regola”.
Nella finestra visualizzata scegliamo “Formatta solo le regole che contengono”. In basso nella stessa finestra lasciamo “Valore” nel primo campo, mentre nel secondo modifichiamo “Tra” con “Minore di”, e nel terzo digitiamo 6.
Apriamo ora la finestra “Formato carattere” cliccando sul pulsante “Formato” posto in basso. Nella scheda “Carattere” impostiamo un colore carattere chiaro e nella scheda “Riempimento” impostiamo un colore scuro. Confermiamo le scelte cliccando su OK nelle due finestre di dialogo aperte.
Applichiamo ora la formattazione condizionale anche alle celle relative al prodotto e ai fornitori: selezioniamo le celle A2:A5000, rieseguiamo da capo i primi tre punti visti in precedenza. nell’elenco della finestra visualizzata scegliamo “Utilizza una formula per…” e nel campo posto in basso digitare la formula =SE(C2=””;FALSO;SE(C2<5;VERO;FALSO))
Rieseguiamo alla lettera i punti 6, 7 e 8 precedenti. Selezioniamo le celle B2:B5000 e rieseguiamo alla lettera quest’ultimo procedimento.
Blocco righe
Per rendere più gestibili le informazioni blocchiamo la prima riga di ciascuno foglio in modo da lasciare sempre visibili le etichette: selezioniamo i tre fogli contemporaneamente cliccando sopra l’etichetta “PRODOTTI” e poi sulle altre due etichette tenendo premuto CTRL, selezioniamo la riga 2 cliccando sull’apposita intestazione di riga, clicchiamo sulla barra “Visualizza” e poi sul pulsante “Blocca riquadri”.
