Validare un database in Excel

ORGANIZZARE IL PROPRIO LAVORO

ANALIZZARE

CONTROLLARE E VALIDARE UN DATABASE IN EXCEL

TAGSAnalizzare dati di speso o fatturato

Formattare una tabella

Controllare e validare un database

Confrontare 2 tabelle

Lavorare con i codici, incrociare informazioni provenienti da tabelle differenti

Filtri, formattazione, trova-sostituisci funzione cerca vert, funzione if, funzione val.errore, blocchi, tabelle pivot, uso della tastiera

In questo tutorial viene affrontato il tema del controllo e validazione di un database in Excel

I dati utilizzati, di pura invenzione, sono quelli relativi agli acquisti mensili effettuati da un’azienda manifatturiera X in un determinato anno. Supponiamo che l’azienda abbia 5 siti produttivi ed ipotizziamo che gli acquisti siano suddivisi in:

diretti - materie prime e confezionamento, confluiscono nel prodotto finitoindiretti - tutto ciò che invece non vi confluisce, ma che è necessario per la sua produzione

CapEx - investimenti, spesa per acquistare beni durevoliOpEx - spese operative di gestione

e categorizzati rispetto alla tipologia di merce acquistata (macrocategoria e sottocategoria). I dati di speso sono indicati nella valuta in cui è effettuato l’acquisto.

Supponiamo inoltre che il database sia stato scaricato da un gestionale e che presenti delle imprecisioni da correggere.

L’obiettivo è ottenere un database pulito, il più possibile aderente alla situazione reale, e prepararlo per la successiva presentazione dei dati.

Nel corso del tutorial si affronteranno i problemi che si possono incontrare nello svolgimento di questa tipologia di analisi e si mostrerà come è possibile superarli.

La preparazione del database ai fini dell’analisi dati e la creazione di report e grafici di sintesi dei risultati verranno affrontate nei tutorial successivi.

ORGANIZZARE IL PROPRIO LAVORO ANALIZZARE

CONTROLLARE E VALIDARE UN DATABASE IN EXCEL

CAMPI DATABASE ORIGINALE: DESCRIZIONE E VALORI

Mese

gen-12

feb-12

mar-12

apr-12

mag-12

giu-12

lug-12

ago-12

set-12

ott-12

nov-12

dic-12

Sito produttivo

Sito produttivo_d

escr

IT1 Roma

IT2 Milano

FR1 Parigi

FR2 Lione

UK1 Londra

Numero ordine

Codice a 4 cifre identificativo dell'ordine

Fornitore Fornitore_desc

codice a 3 cifre identificativo del fornitore

Nome del fornitore

Capex

Flag che vale 1 nel caso in cui l'acquisto sia contabilizzato come un Capex, 0 se Opex

1

0

Ordine/NO

Flag che vale 1 per acquisti effettuati con ordine d'acquisto, 0 se effettuati fuori ordine

1

0

Valuta

E' la valuta utilizzata per l'acquisto

Spend in valuta

E' il valore monetario dell‘uscita espresso nella valuta della transazione

Nome del campo

Valori ammessi e/o descrizione

Sulla destra sono riportate le intestazioni delle colonne del database, come sono in partenza, e i valori consentiti

I valori assunti da C12 sono riportati nella slide n.8

C12

Codice a 3 cifre associato alla categoria di merce acquistata

Le prime 2 cifre corrispondono alla macrocategoria, l'ultima alla sottocategoria

CONTROLLARE E VALIDARE UN DATABASE IN EXCELORGANIZZARE IL PROPRIO LAVORO ANALIZZARE

IL DATABASE ORIGINALE: CARATTERISTICHE E STRUTTURA

Mese Sito produttivo

Sito produttivo_des

crNumero ordine

Fornitore

Fornitore_desc C12 Capex Ordine/

NO Valuta Spend in valuta

nov-12 UK1 Londra 7083 1869 fornitore545 103 01 CHF 1141791,99apr-12 UK1 Londra 5995 690 fornitore152 021 0 1 EUR 891.104,46feb-12 FR2 Lione 2709 1950 fornitore572 # 00 USD 806884

mar-12 FR1 Parigi 2852 1728 fornitore498 102 00 EUR 546327,83giu-12 FR1 Parigi 2903 1654 fornitore472 102 00 EUR 536998,11

set-12 IT2 Milano 5350 399 fornitore55 011 0 1 EUR 496.000,00ott-12 UK1 Londra 6719 1143 fornitore303 # 11 EUR 427316apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20

mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 380.198,21mag-12 IT1 Roma 4829 261 fornitore9 010 0 1 EUR 370.400,00

gen-12 FR2 Lione 2787 1827 fornitore531 101 00 EUR 355145,53ott-12 UK1 Londra 6509 999 fornitore255 120 01 EUR 335237,76

mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 305.707,61feb-12 FR2 Lione 2685 1986 fornitore584 # 00 EUR 295182,48feb-12 FR2 Lione 2685 1986 fornitore584 # 00 EUR 269690,44

nov-12 IT2 Milano 5695 579 fornitore115 063 1 1 EUR 249.875,06gen-12 IT1 Roma 4547 237 fornitore1 010 0 1 USD 236.744,15mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 230.590,56mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 230.078,01apr-12 UK1 Londra 5993 690 fornitore152 021 0 1 EUR 215.510,40nov-12 UK1 Londra 7084 1869 fornitore545 011 01 CHF 208128,33

apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 198.390,60set-12 UK1 Londra 6338 837 fornitore201 063 0 1 EUR 187.124,00nov-12 UK1 Londra 7087 1911 fornitore559 121 01 USD 185176,95

apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 183.143,92apr-12 UK1 Londra 6028 696 fornitore154 010 0 1 EUR 177.972,00apr-12 UK1 Londra 6028 696 fornitore154 010 0 1 EUR 177.972,00nov-12 UK1 Londra 6994 1512 fornitore426 063 01 EUR 171862,74

apr-12 UK1 Londra 5993 690 fornitore152 021 0 1 EUR 169.329,60

Il database originale è costituito da 5.108 righe ed ogni riga corrisponde ad una riga di ordine o voce di uscita, nel caso di acquisti fuori ordine. Le colonne sono quelle già descritte.

Come procediamo per la validazione?


A. LA PRIMA OSSERVAZIONE DEI DATI

Mese Sito produttivo

Sito produttivo_

descrNumero ordine Fornitore Fornitore_desc C12 Capex Ordine/

NO Valuta Spend in valuta

nov-12 UK1 Londra 7083 1869 fornitore545 103 01 CHF 1141791,99apr-12 UK1 Londra 5995 690 fornitore152 021 0 1 EUR 891.104,46feb-12 FR2 Lione 2709 1950 fornitore572 # 00 USD 806884

mar-12 FR1 Parigi 2852 1728 fornitore498 102 00 EUR 546327,83giu-12 FR1 Parigi 2903 1654 fornitore472 102 00 EUR 536998,11

set-12 IT2 Milano 5350 399 fornitore55 011 0 1 EUR 496.000,00ott-12 UK1 Londra 6719 1143 fornitore303 # 11 EUR 427316apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 396.781,20

mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 380.198,21mag-12 IT1 Roma 4829 261 fornitore9 010 0 1 EUR 370.400,00

gen-12 FR2 Lione 2787 1827 fornitore531 101 00 EUR 355145,53ott-12 UK1 Londra 6509 999 fornitore255 120 01 EUR 335237,76

mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 305.707,61feb-12 FR2 Lione 2685 1986 fornitore584 # 00 EUR 295182,48feb-12 FR2 Lione 2685 1986 fornitore584 # 00 EUR 269690,44

nov-12 IT2 Milano 5695 579 fornitore115 063 1 1 EUR 249.875,06gen-12 IT1 Roma 4547 237 fornitore1 010 0 1 USD 236.744,15mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 230.590,56mar-12 UK1 Londra 5990 690 fornitore152 021 0 1 EUR 230.078,01apr-12 UK1 Londra 5993 690 fornitore152 021 0 1 EUR 215.510,40nov-12 UK1 Londra 7084 1869 fornitore545 011 01 CHF 208128,33

apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 198.390,60set-12 UK1 Londra 6338 837 fornitore201 063 0 1 EUR 187.124,00nov-12 UK1 Londra 7087 1911 fornitore559 121 01 USD 185176,95

apr-12 UK1 Londra 5994 690 fornitore152 021 0 1 EUR 183.143,92apr-12 UK1 Londra 6028 696 fornitore154 010 0 1 EUR 177.972,00apr-12 UK1 Londra 6028 696 fornitore154 010 0 1 EUR 177.972,00nov-12 UK1 Londra 6994 1512 fornitore426 063 01 EUR 171862,74

apr-12 UK1 Londra 5993 690 fornitore152 021 0 1 EUR 169.329,60

Già dalla prima osservazione può emergere la presenza di imprecisioni.

In questo caso:1) disallineamento dei mesi

2) in C12 sono presenti dei codici non assegnati (#)

3) nella colonna Capex ci sono dei numeri memorizzati come valori (in Excel sono allineati automaticamente a destra) ed altri come testo (allineati a sinistra)

4) nel campo Spend in valuta in alcune righe c’è il separatore, in altre no

Correggiamo le imprecisioni.

(clic per video)

1)

3) Testo

3) Valore

2)

4) Con separatore

4) Senza separatore


A questo punto possiamo procedere con la validazione dei dati. Il primo passo consiste nell’ individuare delle REGOLE DI VALIDAZIONE.

B. LA VALIDAZIONE - INDIVIDUAZIONE DEI VALORI NON VALIDI O MANCANTI

Un metodo che funziona è quello di costruire uno specchietto per i valori ammissibili e le corrispondenze incrociate, a cui far riferimento nella fase di identificazione e correzione degli errori. Spesso si ha già a disposizione queste informazioni. In questo caso utilizzeremo la codifica delle variabili e la tabella delle categorie merceologiche.

Per identificare le regole:

verifichiamo quali sono i valori validi per ciascun campo del database

analizziamo le corrispondenze incrociate, cioè i valori che possono (e che non possono) essere assunti contemporaneamente da 2 o più campi sulla stessa riga

si può fare riferimento alle informazioni che derivano dalla conoscenza della realtà analizzata


Questi sono i valori validi per ciascun campo. Se troviamo dei valori diversi da questi, dobbiamo correggerli

Per il controllo, se i valori validi sono pochi (meno di 20/25), si possono utilizzare agevolmente i filtri. In questo caso sfrutteremo questa tecnica per la verifica di mese e sito produttivo. Se troviamo dei valori non validi o mancanti li evidenziamo.

(clic per video)

I filtri servono ad ordinare e filtrare i dati di una colonna. Si attivano selezionando [ordina e filtra -> filtro] nella barra multifunzione home.Sono dinamici, nel senso che se filtriamo una colonna Excel automaticamente filtrerà tutte le colonne adiacenti (riconosciute dal programma come appartenenti alla stessa tabella) e se clicchiamo sulla freccina mostrerà nella tendina tutti i valori presenti nella colonna in ordine crescente (sia per numeri che per stringhe di testo). Se c’è un filtro attivo, il numero di riga sulla destra è blu. Per visualizzare tutte le righe della tabella quando c’è un filtro attivo ma senza rimuovere il filtro selezionare [cancella] nel menù Ordina e filtra


A destra la tabella riportante le categorie merceologiche d’acquisto.

MACROCATEGORIA

E’ la categoria di livello superiore

SOTTOCATEGORIA

E’ sempre associata alla macrocategoria a cui appartiene

C12

E’ il codice completo, costituito dall’unione di macrocategoria e sottocategoria

Es. per “Materie prime” è

MACROCATEGORIA SOTTOCATEGORIAC12 D/I Ordine/NO

C1 C1_descrizione C2 C2_descrizione01 Materiali 1 Materie prime 011 Diretti 101 Materiali 2 Semilavorati 012 Diretti 102 Confezionamento 1 Imballaggio primario 021 Diretti 102 Confezionamento 2 Imballaggio secondario 022 Diretti 102 Confezionamento 3 Etichette 023 Diretti 103 Acquisti diretti non categorizzati 0 Acquisti diretti non categorizzati 030 Diretti 104 Servizi generali 1 Servizi 041 Indiretti 104 Servizi generali 2 Manutenzione 042 Indiretti 105 Utilities 1 Energia elettrica 051 Indiretti 105 Utilities 2 Gas 052 Indiretti 105 Utilities 3 Acqua 053 Indiretti 105 Utilities 4 Altre tipologie di energia 054 Indiretti 106 Strumenti e parti di ricambio 1 Strumenti 061 Indiretti 106 Strumenti e parti di ricambio 2 Manutenzione 062 Indiretti 106 Strumenti e parti di ricambio 3 Parti di ricambio 063 Indiretti 107 Trasporti 1 Costi consegna prodotti 071 Indiretti 107 Trasporti 2 Trasporti a mezzo corriere 072 Indiretti 007 Trasporti 3 Altri costi di trasporto 073 Indiretti 108 Informatica, telecomunicazioni e cancelleria 1 Hardware IT 081 Indiretti 108 Informatica, telecomunicazioni e cancelleria 2 Software 082 Indiretti 108 Informatica, telecomunicazioni e cancelleria 3 Reti 083 Indiretti 108 Informatica, telecomunicazioni e cancelleria 4 Telecomunicazione 084 Indiretti 108 Informatica, telecomunicazioni e cancelleria 5 Cancelleria 085 Indiretti 109 Marketing 1 Merchandising 091 Indiretti 109 Marketing 2 Pubblicità 092 Indiretti 109 Marketing 3 Sponsorizzazioni 093 Indiretti 110 Consulenze e outsourcing 1 Consulenti 101 Indiretti 110 Consulenze e outsourcing 2 Servizi in outsourcing 102 Indiretti 110 Consulenze e outsourcing 3 Traduzioni 103 Indiretti 111 Viaggi di lavoro 1 Alloggio 111 Indiretti 111 Viaggi di lavoro 2 Trasporto 112 Indiretti 111 Viaggi di lavoro 3 Rimborsi spese 113 Indiretti 112 Acquisti indiretti non categorizzati 0 Acquisti indiretti non categorizzati 120 Indiretti 198 Spese finanziarie 1 Interessi 981 N/A 098 Spese finanziarie 2 Servizi bancari 982 N/A 098 Spese finanziarie 3 Imposte e tasse 983 N/A 099 Dipendenti e spese per il sociale 1 Costo del personale 991 N/A 099 Dipendenti e spese per il sociale 2 Donazioni 992 N/A 0

C101

C21

C12

011

D/I

Classificazione in acquisti diretti, indiretti o non applicabile

Ordine/NO

1 per merci acquistate con ordine d’acquisto, 0 per Spend fuori ordine


Per verificare l’esistenza dei codici riportati in [C12], possiamo inserire una colonna nuova, che chiameremo [C12_check] e ricercare i codici nella tabella delle categorie (foglio “categorie”). La funzione più adatta per effettuare quest’operazione è senza dubbio il cerca verticale combinato con le funzioni se e val.errore

IL CERCA VERTICALEIl cerca verticale serve a ricercare un valore all’interno di un elenco di valori. Per un dato valore X restituisce un valore Y di una colonna della tabella di origine appartenente alla stessa riga di X

La sintassi 1 2 3 4=CERCA.VERT(VALORE;TABELLA;INDICE;INTERVALLO)1) Cella in cui è contenuto il valore da ricercare2) Il posto dove bisogna ricercare il valore specificato in VALORE3) Indica il numero di colonne dalla prima della TABELLA, in cui è contenuto il valore da restituire. II valore da ricercare deve essere collocato per forza nella prima colonna4) E’ 0 se si vuole che cerchi il valore esatto, 1 se si consente di ricercare per approssimazioni

Un esempio praticoSe ricerchiamo il valore 030 nella tabella categorie, e impostiamo il Cerca vert chiedendogli di resituirci la seconda colonna a partire da C12 (vedi freccia nella tabella categorie in alto), il risultato sarà “Diretti”.La formula sarà:

E’ la cella in cui è contenuto il valore da ricercare

Il foglio + l’intervallo in cui c’è la tabella dove cercare il valore in G18

Indice: il valore è nella colonna FIL VALORE RESTITUITO

1 2


LA FUNZIONE SE – sintassi

1 2 3SE(CONDIZIONE; SE_VERO; SE_FALSO)

1) E’ un test (ad esempio A1<1; A2=A3)

2) E’ l’istruzione che viene applicata se il test dà come risultato VERO (ad esempio A1+A2)

3) E’ l’azione da fare se il risultato è FALSO (es. A1-A2)


LA FUNZIONE VAL.ERRORE

La funzione Val.errore serve ad identificare gli errori (#N/D, #RIF, etc.) in un database.

SintassiVal.errore(cella)Da come risultato VERO se il valore contenuto nella cella è un errore, FALSO altrimenti.

E’ molto utlizzata in abbinamento a funzioni di ricerca

USO DEI BLOCCHI

I blocchi $ servono a bloccare i riferimenti di una formula per far sì che questi non cambino se trasciniamo la formula in altre celle del foglio di lavoro. Per il cerca.vert di solito viene bloccata la tabella dove viene ricercato il valore (come si vedrà nel video). Esempi di blocchi:

Per verificare l’esistenza dei codici riportati in [C12], possiamo inserire una colonna nuova, che chiameremo [C12_check] e ricercare i codici nella tabella delle categorie (foglio “categorie”). La funzione più adatta per effettuare quest’operazione è senza dubbio il cerca verticale combinato con le funzioni se e val.errore

(clic per video)Intervallo

Oggetto del blocco Come attivarlo

$A$1:$B$5 Righe e colonne

Selezioniamo nella formula A1:B5 e premiamo sulla tastiera F4

A$1:B$5 Solo righe Selezioniamo A1:B5 e premiamo 2 volte F4

$A1:$B5 Solo colonne Selezioniamo A1:B5 e premiamo 3 volte F4

Per l’analisi delle corrispondenze incrociate, lo strumento migliore è senza dubbio la tabella pivot. Prima di effettuare l’analisi occorre fare un ragionamento su quali sono nel caso specifico le corrispondenze da verificare, considerando anche le informazioni che provengono dalla conoscenza del fenomeno studiato.

1) Ci sono delle categorie per cui gli acquisti vengono effettuati fuori ordine (Ordine/NO=0). Controlliamo se nel database viene rispettata questa regola

MACROCATEGORIA SOTTOCATEGORIAC12 D/I Ordine/NO

C1 C1_descrizione C2 C2_descrizione06 Strumenti e parti di ricambio 3 Parti di ricambio 063 Indiretti 107 Trasporti 1 Costi consegna prodotti 071 Indiretti 107 Trasporti 2 Trasporti a mezzo corriere 072 Indiretti 007 Trasporti 3 Altri costi di trasporto 073 Indiretti 108 Informatica, telecomunicazioni e cancelleria 1 Hardware IT 081 Indiretti 108 Informatica, telecomunicazioni e cancelleria 2 Software 082 Indiretti 111 Viaggi di lavoro 3 Rimborsi spese 113 Indiretti 112 Acquisti indiretti non categorizzati 0 Acquisti indiretti non categorizzati 120 Indiretti 198 Spese finanziarie 1 Interessi 981 N/A 098 Spese finanziarie 2 Servizi bancari 982 N/A 098 Spese finanziarie 3 Imposte e tasse 983 N/A 099 Dipendenti e spese per il sociale 1 Costo del personale 991 N/A 099 Dipendenti e spese per il sociale 2 Donazioni 992 N/A 0

La tabella pivot è una tabella dinamica in cui possiamo inserire i campi appartenenti al database di origine e creare tabelle di frequenza o semplicemente rappresentare un fenomeno a 2 dimensioni. E’ simile al filtro, con la differenza che qui possiamo lavorare incrociando due o più dimensioni contemporaneamente ed ottenere degli indici di sintesi - conteggio, somma, minimo, massimo, media. Si compila trascinando i campi (in elenco campi tabella pivot) che ci interessano nello spazio dedicato. Gli spazi sono: • filtro rapporto – per filtrare le informazioni da visualizzare in base al valore/valori di uno o più campi (come i filtri normali)• etichette di riga e colonna• Somma valori – per decidere il campo dei valori da sommare/contare/etc. (ad esempio possiamo sommare i valori dello Spend o contare il numero di fornitori)

(clic per video)


Per effettuare il controllo utilizziamo la stessa procedura mostrata per il punto 1), selezionando questa volta i codici C12 classificati come “acquisti diretti” nella tabella categorie. Verifichiamo nella pivot che non ci siano codici classificati come Capex (Capex=1), se li troviamo li evidenziamo di rosso

C12 Acquisti Diretti - Indiretti

011 Diretti012 Diretti021 Diretti022 Diretti023 Diretti030 Diretti

Ci sono 12 righe con codice 030 classificate come Capex


2) Un’informazione derivata dalla conoscenza della realtà analizzata è che gli acquisti diretti non possono essere investimenti (Capex), per cui controlliamo che ai codici C12 diretti sia sempre associato Capex=0

C. LA VALIDAZIONE – CORREZIONE DEI VALORI NON VALIDI E IMPUTAZIONE DI

QUELLI MANCANTIPer correggere i valori non validi e imputare i valori mancanti si possono utilizzare diverse tecniche:

Se riconosciamo errori di digitazione troviamo il valore valido più vicino e li correggiamo di conseguenza

I valori che non rispettano le regole di corrispondenza incrociata (vedi la tabella categoria) possono essere corretti sulla base di queste

Se esistono dei valori generici, (es. 030 - acquisti diretti non categorizzati, 120 - acquisti indiretti non categorizzati) possono essere assegnati ai valori mancanti

I valori mancanti possono essere imputati guardando le righe con valori degli altri campi simili a quelli della riga in cui è contenuto il mancante

Anche in questo caso si può fare affidamento alla conoscenza diretta del fenomeno

Si può recuperare il documento originario (es. ordine d’acquisto, bolla di consegna, fattura, etc.), compatibilmente con la sua disponibilità, e verificare il valore direttamente alla fonte

La scelta delle tecniche da utilizzare dipende dal tempo a disposizione, dalla presenza di regole di validazione e dall’importanza del dato da correggere. Si può anche utilizzare più tecniche insieme


Analizziamo con le tecniche descritte, uno alla volta, i campi in cui abbiamo riscontrato valori non validi.

SITO PRODUTTIVOSito produttivo

Sito produttivo_descr

IT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoIT2f MilanoFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 LioneFR21 Lione

Filtriamo per colore rosso

E’ evidente che i due valori non validi sono errori di digitazione. Possiamo correggerli con il TROVA SOSTITUISCI IT2f si sostituisce con IT2 FR21 con FR2


C12Bisogna correggere i valori per cui il check aveva dato come risultato “no”. Nella tabella seguente vengono elencati i valori da correggere, la procedura scelta per la correzione e il valore valido di sostituzione

Se mentre procediamo con la correzione sostituiamo ai valori errati i valori consentiti e lasciamo la formula in check attiva, questa si aggiornerà contemporaneamente alla correzione. A correzione ultimata i “no” dovrebbero essere sostituiti completamente dagli “ok”

Valore

Metodo di correzione Valore valido

98 Errore di digitazione: cerchiamo il valore valido più vicino (nella stessa macrocategoria)

981

120 E’ un problema di formati in quanto il valore esiste. Possiamo ignorarlo.

# Scegliamo una regola per i valori mancanti: li assegnamo alle categorie generiche di acquisti diretti ed indiretti (assegniamo i Capex agli indiretti e gli Opex ai diretti)

030 per gli Opex, 120 per i Capex


011


023

Nella ricerca del valore sostitutivo possiamo anche considerare la tipologia di merce venduta dal fornitore associato al valore errato, utilizzando la nostra conoscenza della realtà analizzata


VALIDAZIONI INCROCIATE

Sfruttando la tabella pivot e l’identificatore andiamo a correggere tutte le incompatibilità riscontrate nella verifica incrociata, con la quale avevamo evidenziato:

i conteggi delle righe che avevano ORDINE/NO=1 con un codice C12 per cui era consentito solo il valore ORDINE/NO=0

i conteggi delle posizioni di ordine classificate negli acquisti diretti e con flag Capex=1 (un acquisto diretto non può essere un investimento!!)

Per la sostituzione si possono utilizzare 2 metodi differenti:

a) Se le righe conteggiate sono poche, le correggiamo utilizzando il comando “mostra dettagli” (doppio clic sul conteggio dei valori non validi ed modificando il valore direttamente nel database

b) Se le righe sono più di 5 utilizziamo il comando “mostra dettagli” per la tabella pivot e facciamo la correzione con il cerca.vert

L’inserimento di un campo chiave primaria (ID) è molto utile quando si lavora con i database. In particolare lo è per le azioni di ricerca e selezione, in quanto è identificatore univoco di ciascuna riga. Nel nostro esempio avere l’ID significa poter automatizzare con una formula (metodo b) la ricerca dei valori non validi nel database invece che farlo riga per riga (metodo a)

Nel prossimo video verranno applicate entrambe le metodologie (metodo a per Capex, metodo b per ORDINE/NO)

(clic per video)


In conclusione, ripercorriamo gli step che abbiamo seguito in questa prima analisi e validazione dei dati:

Osservazione del database e delle codifiche, se presenti Formattazione del database per uniformare gli allineamenti e formati Individuazione delle regole di validazione Ricerca dei valori non validi Correzione dei valori non validi e imputazione di quelli mancanti

Le funzionalità di Excel che abbiamo utilizzato sono:

Filtri Trova-sostituisci Tabelle pivot (formattazione, mostra dettagli) Funzioni se(), val.errore() e cerca,vert()

Questo tutorial ha per oggetto dati relativi agli acquisti, ma le metodologie descritte hanno un ambito di applicazione più generale (vendite, fatturato, spedizioni, etc.) che prescinde dalla natura del dato.

Nel prossimo tutorial il database verrà preparato per la creazione dei report di sintesi


Validare un database in Excel

Self Improvement

Transcript of Validare un database in Excel