Preparare il database per la creazione dei report

ORGANIZZARE

IL PROPRIO

LAVORO

ANALIZZARE

PREPARARE IL DATABASE PER LA CREAZIONE DI

REPORT

TAGS

Analizzare dati di speso o fatturato

Valuta locale e valuta della transazione, tassi di cambio

Lavorare con i codici, incrociare informazioni provenienti

da tabelle differenti

Stringhe, funzioni annidate

Funzioni destra, sinistra, stringa.estrai, cerca.vert

=SE(K2="EUR";L2;CERCA.VERT(K2;tassi

_cambio!$A$1:$B$8;2;0)*dati!L2)

C1

01

C2

1

C12

011

In questo tutorial viene affrontato il tema della preparazione del database per la fase successiva di

creazione di report.

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 finito indiretti - tutto ciò che invece non vi confluisce, ma che è necessario per la sua produzione

CapEx - investimenti, spesa per acquistare beni durevoli OpEx - 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.

Il database è già stato validato e depurato di tutte le imprecisioni nel tutorial precedente. In questo

tutorial verranno aggiunti dei campi al database per convertire il valore degli acquisti in valuta locale e

per associare al codice C12 la descrizione della macrocategoria e sottocategoria

La creazione di report e grafici di sintesi dei risultati sarà oggetto del prossimo e ultimo tutorial.

ORGANIZZARE IL

PROPRIO LAVORO

ANALIZZAREPREPARARE IL DATABASE PER LA CREAZIONE DI REPORT

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_

descr

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

ORGANIZZARE IL

PROPRIO LAVORO


ID

1

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_descrizione

01 Materiali 1 Materie prime 011 Diretti 1

01 Materiali 2 Semilavorati 012 Diretti 1

02 Confezionamento 1 Imballaggio primario 021 Diretti 102 Confezionamento 2 Imballaggio secondario 022 Diretti 1

02 Confezionamento 3 Etichette 023 Diretti 103 Acquisti diretti non categorizzati 0 Acquisti diretti non categorizzati 030 Diretti 1

04 Servizi generali 1 Servizi 041 Indiretti 104 Servizi generali 2 Manutenzione 042 Indiretti 1

05 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 1

06 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 0

07 Trasporti 3 Altri costi di trasporto 073 Indiretti 1

08 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 1

98 Spese finanziarie 1 Interessi 981 N/A 098 Spese finanziarie 2 Servizi bancari 982 N/A 0

98 Spese finanziarie 3 Imposte e tasse 983 N/A 0

99 Dipendenti e spese per il sociale 1 Costo del personale 991 N/A 0

99 Dipendenti e spese per il sociale 2 Donazioni 992 N/A 0

C1

01

C2

1

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

ORGANIZZARE IL

PROPRIO LAVORO


IL DATABASE ORIGINALE: CARATTERISTICHE E STRUTTURA

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.

ORGANIZZARE IL

PROPRIO LAVORO


A) CONVERSIONE DELLO SPEND IN VALUTA LOCALE

Per la conversione bisogna mappare le valute presenti nel database, ricercare i

tassi di cambio e costruire una tabellina di conversione da cui pescare per

applicare il cambio riga per riga.

Per prima cosa cerchiamo nel campo valuta tutte le valute presenti e le riportiamo

su un foglio nuovo.

ORGANIZZARE IL

PROPRIO LAVORO


Spesso le aziende strutturate fissano

dei tassi di cambio standard per il

periodo (che può essere annuale,

semestrale, mensile, etc.). In questo

caso basta recuperare la tabella di

conversione nella intranet aziendale.

Poi scarichiamo da internet i tassi di cambio.

Le valute sono 8, per cui è più veloce

ricercare una tabella di conversione rispetto al

singolo valore (rates table). In questo caso

abbiamo preso i tassi giornalieri del 2/1/2014.

Incolliamo i tassi e completiamo la tabellina di

conversione in Excel.

A questo punto possiamo uniformare la valuta

delle righe. Creiamo una colonna nel

database (Spend in valuta locale) e, per le

valute diverse da Euro, moltiplichiamo per il

valore del tasso.

Clic per video

VALUTATASSO

CAMBIO

CAD 0,685907

CHF 0,813122

DKK 0,134065

GBP 1,208215

HUF 0,003352

NOK 0,119053

USD 0,730424

tasso di

cambio valuta-

euro del 2-

gen-2014

http://www.x-rates.com/table/?from=EUR&amount=1.00

http://www.x-rates.com/table/?from=EUR&amount=1.00

Ecco il risultato. I valori che erano già in EURO sono rimasti invariati, mentre quelli

in altra valuta sono stati convertiti in EURO applicando i tassi di cambio della

tabella di conversione.

ORGANIZZARE IL

PROPRIO LAVORO



ValutaSpend in

valutaSpend in

EUR

EUR 1.012 1.012

EUR 856 856 EUR 196 196

EUR 13.218 13.218 EUR 12.491 12.491 EUR 2.808 2.808 EUR 2.632 2.632 EUR 2.293 2.293

EUR 1.870 1.870

VALORI IN EURO

ValutaSpend in

valutaSpend in

EUR

CHF - 12.606 - 10.250 USD - 7.984 - 5.831

USD - 3.823 - 2.792 USD - 3.823 - 2.792 USD - 1.372 - 1.002

GBP - 1.343 - 1.622 USD - 380 - 278

GBP - 328 - 396 GBP - 261 - 315

ALTRE VALUTE

Le descrizioni di macro e sottocategoria sono associate al codice L2 nella tabella delle

categorie. Per riportarle nel database occorre scomporre il codice L2 nelle 2 parti

componenti con le funzioni per l’estrazione delle stringhe e ricercare con il

cercaverticale la parte del codice associata prima alla macrocategoria (C1) e poi alla

sottocategoria (C2), facendoci restituire le descrizioni rispettivamente associate ai codici.

ORGANIZZARE IL

PROPRIO LAVORO


B) AGGIUNTA DESCRIZIONE MACROCATEGORIA E SOTTOCATEGORIA

C1

01

C2

1

C12

011LE FUNZIONI DESTRA(), SINISTRA() E STRINGA.ESTRAI()

Servono a prelevare un numero di caratteri specificato da una stringa di testo

contenuta nella cella di riferimento.

La funzione DESTRA(riferimento_cella;num_caratteri) restituisce un numero

di caratteri specificato della stringa di riferimento a partire da destra;

la funzione SINISTRA(riferimento_cella;num_caratteri) a partire da sinistra;

STRINGA.ESTRAI(riferimento_cella; inizio; num_caratteri) restituisce il

numero di caratteri specificato in num_caratteri a partire dalla posizione

definita in inizioClic per

video

Il risultato è quello riportato nella tabella in basso. Il database ora è pronto per la

creazione di report e grafici di sintesi dei risultati.

ORGANIZZARE IL

PROPRIO LAVORO


B) AGGIUNTA DESCRIZIONE MACROCATEGORIA E SOTTOCATEGORIA

Numero ordine

Fornitore Fornitore_desc C12 C1 C1_descr C2 C2_descr

6774 1200 fornitore322 063 06 Strumenti e parti di ricambio 3 Parti di ricambio

7004 1527 fornitore431 103 10 Consulenze e outsourcing 3 Traduzioni

4547 237 fornitore1 011 01 Materiali 1 Materie prime


6850 1350 fornitore372 084 08 Informatica, telecomunicazioni e cancelleria 4 Telecomunicazione

6239 780 fornitore182 120 12 Acquisti indiretti non categorizzati 0 Acquisti indiretti non categorizzati

5473 450 fornitore72 022 02 Confezionamento 2 Imballaggio secondario



5474 450 fornitore72 022 02 Confezionamento 2 Imballaggio secondario


6704 1119 fornitore295 023 02 Confezionamento 3 Etichette

6704 1119 fornitore295 023 02 Confezionamento 3 Etichette


6973 1473 fornitore413 102 10 Consulenze e outsourcing 2 Servizi in outsourcing

2713 1944 fornitore570 981 98 Spese finanziarie 1 Interessi

3037 1257 fornitore341 073 07 Trasporti 3 Altri costi di trasporto

3037 1257 fornitore341 073 07 Trasporti 3 Altri costi di trasporto

6794 1245 fornitore337 102 10 Consulenze e outsourcing 2 Servizi in outsourcing


ORGANIZZARE

IL PROPRIO

LAVORO

ANALIZZARE

PREPARARE IL DATABASE PER LA CREAZIONE DI

REPORT

BACKUP


ORGANIZZARE IL

PROPRIO LAVORO


Ora inseriamo la funzione che ci consentirà di convertire i valori dello

Spend dalla valuta della transazione alla valuta locale (EUR).

=SE(K2="EUR";L2;CERCA.VERT(K2;tassi_cambio!$A$1:$B$8;2;0)*dati!L2)

Se la valuta è già euro

(condizione>

K2=EUR) non c’è

bisogno di nessuna

conversione, per cui

viene riportato il valore

in Spend in valuta

(SE_VERO>L2)

Altrimenti, cerca la valuta

CERCA.VERT(K2 nella tabella di

conversione (foglio tassi_cambio!,

riferimenti di cella $A$1:$B$8) e

restituisce il valore del cambio

(indice 2)

Moltiplica il

tasso per il

valore dello

Spend da

convertire

*dati!L2

Preparare il database per la creazione dei report

Education

Transcript of Preparare il database per la creazione dei report