Contenuti
Quando si parla di database (DB), la prima cosa che viene in mente, ovviamente, sono tutti i tipi di parole d'ordine come SQL, Oracle, 1C o almeno Access. Naturalmente, si tratta di programmi molto potenti (e per la maggior parte costosi) che possono automatizzare il lavoro di un'azienda grande e complessa con molti dati. Il problema è che a volte tale potere semplicemente non è necessario. La tua azienda può essere piccola e con processi aziendali relativamente semplici, ma desideri anche automatizzarla. Ed è per le piccole imprese che questa è spesso una questione di sopravvivenza.
Per cominciare, formuliamo il TOR. Nella maggior parte dei casi, un database per la contabilità, ad esempio vendite classiche, dovrebbe essere in grado di:
- mantenere nelle tabelle informazioni su merci (prezzo), transazioni completate e clienti e collegare tra loro queste tabelle
- avere comodo moduli di input dati (con elenchi a discesa, ecc.)
- compilare automaticamente alcuni dati moduli stampati (pagamenti, bollette, ecc.)
- emettere il necessario rapporti per controllare l'intero processo aziendale dal punto di vista del manager
Microsoft Excel può gestire tutto questo con un piccolo sforzo. Proviamo a implementarlo.
Passaggio 1. Dati iniziali sotto forma di tabelle
Conserveremo le informazioni su prodotti, vendite e clienti in tre tabelle (sullo stesso foglio o su fogli diversi, non importa). È fondamentale trasformarli in “tavoli intelligenti” con dimensioni automatiche, per non pensarci in futuro. Questo viene fatto con il comando Formatta come una tabella linguetta Casa (Home — Formatta come tabella). Nella scheda che poi appare Costruttore (Design) dare alle tabelle nomi descrittivi nel campo Nome della tabella per un uso successivo:
In totale, dovremmo ottenere tre "tabelle intelligenti":
Si prega di notare che le tabelle possono contenere ulteriori dati chiarificatori. Quindi, ad esempio, il nostro Prezzocontiene informazioni aggiuntive sulla categoria (gruppo di prodotti, imballaggio, peso, ecc.) di ciascun prodotto e la tabella . — città e regione (indirizzo, TIN, coordinate bancarie, ecc.) di ciascuno di essi.
Table Vendite verrà utilizzato da noi in seguito per inserire le transazioni completate in esso.
Passaggio 2. Crea un modulo di immissione dati
Ovviamente puoi inserire i dati di vendita direttamente nella tabella verde Vendite, ma ciò non è sempre conveniente e comporta la comparsa di errori e refusi dovuti al “fattore umano”. Pertanto, sarebbe meglio creare un modulo speciale per inserire i dati su un foglio separato di qualcosa del genere:
Nella cella B3, per ottenere la data-ora corrente aggiornata, utilizzare la funzione Il TDATA (ADESSO). Se il tempo non è necessario, allora invece Il TDATA è possibile applicare la funzione OGGI (OGGI).
Nella cella B11, trova il prezzo del prodotto selezionato nella terza colonna della smart table Prezzo utilizzando la funzione VPR (CERCA.VERT). Se non l'hai mai incontrato prima, prima leggi e guarda il video qui.
Nella cella B7, abbiamo bisogno di un elenco a discesa con i prodotti del listino prezzi. Per questo puoi usare il comando Dati – Convalida dei dati (Convalida dei dati), specificare come vincolo Lista (Elenco) e poi entrare nel campo Fonte (Fonte) collegamento alla colonna Nome dal nostro tavolo intelligente Prezzo:
Allo stesso modo, viene creato un elenco a discesa con i client, ma la fonte sarà più ristretta:
=INDIRETTO("Clienti[Cliente]")
Funzione INDIRETTI (INDIRETTO) è necessario, in questo caso, perché Excel, purtroppo, non comprende i collegamenti diretti alle tabelle intelligenti nel campo Sorgente. Ma lo stesso collegamento "avvolto" in una funzione INDIRETTI allo stesso tempo, funziona con il botto (maggiori informazioni su questo erano nell'articolo sulla creazione di elenchi a discesa con contenuto).
Passaggio 3. Aggiunta di una macro di voci di vendita
Dopo aver compilato il modulo, è necessario aggiungere i dati inseriti in esso alla fine della tabella Vendite. Utilizzando dei semplici link, formeremo una riga da aggiungere proprio sotto il modulo:
Quelli. la cella A20 avrà un collegamento a =B3, la cella B20 avrà un collegamento a =B7 e così via.
Ora aggiungiamo una macro elementare di 2 righe che copia la stringa generata e la aggiunge alla tabella Sales. Per fare ciò, premere la combinazione Alt + F11 o pulsante Visual Basic linguetta sviluppatore (Sviluppatore). Se questa scheda non è visibile, abilitala prima nelle impostazioni File – Opzioni – Configurazione della barra multifunzione (File — Opzioni — Personalizza barra multifunzione). Nella finestra dell'editor di Visual Basic che si apre, inserisci un nuovo modulo vuoto tramite il menu Inserisci – Modulo e inserisci lì il nostro codice macro:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy 'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Riga 'determina il numero dell'ultima riga nella tabella. Fogli di lavoro di vendita ("Vendite").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'incolla nella riga vuota successiva Fogli di lavoro ("Modulo di input"). Intervallo ("B5, B7, B9"). ClearContents 'cancella il modulo secondario finale Ora possiamo aggiungere un pulsante al nostro modulo per eseguire la macro creata utilizzando l'elenco a discesa inserire linguetta sviluppatore (Sviluppatore — Inserisci — Pulsante):
Dopo averlo disegnato, tenendo premuto il tasto sinistro del mouse, Excel ti chiederà quale macro devi assegnargli – seleziona la nostra macro Aggiungi_Vendi. È possibile modificare il testo su un pulsante facendo clic con il pulsante destro del mouse su di esso e selezionando il comando Cambia testo.
Ora, dopo aver compilato il modulo, puoi semplicemente cliccare sul nostro pulsante, e i dati inseriti verranno automaticamente aggiunti alla tabella Vendite, quindi il modulo viene cancellato per inserire una nuova offerta.
Passaggio 4 Collegamento delle tabelle
Prima di creare il report, colleghiamo le nostre tabelle in modo che in seguito possiamo calcolare rapidamente le vendite per regione, cliente o categoria. Nelle versioni precedenti di Excel, ciò richiederebbe l'uso di diverse funzioni. VPR (CERCA.VERT) per sostituire alla tabella prezzi, categorie, clienti, città, ecc Vendite. Ciò richiede tempo e impegno da parte nostra e "mangia" anche molte risorse di Excel. A partire da Excel 2013, tutto può essere implementato in modo molto più semplice impostando relazioni tra tabelle.
Per fare ciò, nella scheda Dati (Data) clicca Relazioni (Relazioni). Nella finestra che appare, fai clic sul pulsante Creare (nuovo) e seleziona dagli elenchi a discesa le tabelle e i nomi delle colonne con cui devono essere correlati:
Un punto importante: le tabelle devono essere specificate in questo ordine, cioè tabella collegata (Prezzo) non deve contenere nella colonna chiave (Nome) prodotti duplicati, come avviene nella tabella Vendite. In altre parole, la tabella associata deve essere quella in cui cercheresti i dati utilizzando VPRse fosse usato.
Naturalmente, il tavolo è collegato in modo simile Vendite con tavolo . per colonna comune :
Dopo aver impostato i collegamenti, è possibile chiudere la finestra per la gestione dei collegamenti; non è necessario ripetere questa procedura.
Passaggio 5. Creiamo report utilizzando il riepilogo
Ora, per analizzare le vendite e tenere traccia delle dinamiche del processo, creiamo, ad esempio, una sorta di report utilizzando una tabella pivot. Imposta la cella attiva sulla tabella Vendite e seleziona la scheda sulla barra multifunzione Inserisci – Tabella pivot (Inserisci — Tabella pivot). Nella finestra che si apre, Excel ci chiederà l'origine dati (es. tabella Vendite) e un luogo per caricare il report (preferibilmente su un nuovo foglio):
Il punto vitale è che è necessario abilitare la casella di controllo Aggiungi questi dati al modello di dati (Aggiungi dati al modello di dati) nella parte inferiore della finestra in modo che Excel capisca che vogliamo creare un report non solo sulla tabella corrente, ma anche utilizzare tutte le relazioni.
Dopo aver fatto clic su OK un pannello apparirà nella metà destra della finestra Campi tabella pivotdove fare clic sul collegamento Tuttiper vedere non solo quello attuale, ma tutti i “tavoli intelligenti” che ci sono nel libro contemporaneamente. E poi, come nella classica tabella pivot, puoi semplicemente trascinare i campi di cui abbiamo bisogno da qualsiasi tabella correlata nell'area Filtro, Righe, Stolbcov or Valori – ed Excel creerà istantaneamente qualsiasi rapporto di cui abbiamo bisogno sul foglio:
Non dimenticare che la tabella pivot deve essere aggiornata periodicamente (quando cambiano i dati di origine) facendo clic con il tasto destro su di essa e selezionando il comando Aggiorna e salva (Ricaricare), perché non può farlo automaticamente.
Inoltre, selezionando una cella qualsiasi nel riepilogo e premendo il pulsante Grafico pivot (Grafico pivot) linguetta Analisi (Analisi) or parametri (Opzioni) puoi visualizzare rapidamente i risultati calcolati in esso.
Passaggio 6. Compila gli stampabili
Un altro compito tipico di qualsiasi database è la compilazione automatica di vari moduli e moduli stampati (fatture, fatture, atti, ecc.). Ho già scritto di uno dei modi per farlo. Qui implementiamo, ad esempio, la compilazione del modulo per numero di conto:
Si presume che nella cella C2 l'utente inserisca un numero (numero di riga nella tabella Vendite, in effetti), e quindi i dati di cui abbiamo bisogno vengono recuperati utilizzando la già familiare funzione VPR (CERCA.VERT) e caratteristiche INDICE (INDICE).
- Come utilizzare la funzione CERCA.VERT per cercare e cercare valori
- Come sostituire VLOOKUP con le funzioni INDEX e MATCH
- Compilazione automatica di moduli e moduli con i dati della tabella
- Creazione di report con tabelle pivot