Caso pratico: consolidare dati delle retribuzioni con Excel

Il problema

La crescita ha comportato una maggior necessità di forza lavoro. Oltre al personale dipendente, l’azienda fa ricorso ai servizi di alcune agenzie interinali.

Fino ad ora, le varie agenzie comunicavano i dati riepilogativi periodici del personale con vari format, tra loro diversi. L’azienda aveva necessità di consolidare i dati, ma l’attività era svolta manualmente, almeno nella prima fase di “riduzione” dei dati, ovvero dell’omogeneizzazione dei formati stessi.

Questo metodo richiede tempo, e si sa che:

• tempo = costo
• tempo = inefficienza

La nuova via…

L’idea è semplice quanto efficace: standardizzare il format di trasmissione del dato, mantenendolo semplice, ed utilizzarlo per alimentare in modo semi-automatico una struttura organizzata come base dati.

La scelta, per comodità e diffusione, è quella di utilizzare il foglio di calcolo excel (ma i concetti potrebbero ben essere applicabili anche a Calc della suite OpenOffice/LibreOffice, o ad altri spreadsheet) strutturando i dati in modo tabellare, prendendo a prestito dalla teoria della gestione dei DBMS le strutture di base, mantenute il più semplici possibile, ed utilizzando fogli di visualizzazione per il reporting e l’analisi dei dati.

In sostanza, si può riepilogare il lavoro di progettazione nella separazione in questi 3 elementi:

• un modello excel che rappresenta il format standard per la raccolta dati, da compilare mensilmente a cura dell’agenzia interinale. Di tale modello esisteranno più fogli, ciascuno dei quali raccoglierà i dati di una certa agenzia di un determinato periodo;
• un foglio excel, che rappresenta lo strumento di lavoro, composto da:

• delle tabelle che fungono da base dati, nelle quali vengono raccolti i dati ed aggregati in forma omogenea. Queste tabelle sono le uniche che possono contenere dati soggetti a inserimento e/o manutenzione. Il caricamento e la manutenzione dei dati è stata automatizzata tramite delle macro (eventualmente oggetto di altri articoli, se d’interesse)
• delle tabelle che fungono da strumento di reporting ed analisi, ovvero il reale strumento di consultazione.

Per rendere maggiormente efficace la struttura, si è proceduto a costruire le tabelle dati in modo normalizzato (anche se non del tutto), ovvero non ridondando i dati e tenendoli separati nelle varie tabelle, collegati tramite relazioni chiave primaria/esterna. Vista la semplicità della struttura, e l’esistenza di una chiave univoca “naturale”, ovvero il codice fiscale del soggetto, si è scelto di usare come chiave primaria delle tabelle proprio questo dato. Univoca la compilazione, univoca la relazione.

Per le nostre tabelle quindi la chiave primaria è il codice fiscale del prestatore di lavoro.

Il format per la raccolta dati è pertanto molto semplice, e segue la seguente struttura (gli esempi sono ridotti e semplificati):

In realtà in questa tabella la normalizzazione non è del tutto corretta, il campo nome/cognome è ridondante, ma è stato lasciato per semplificare la compilazione esterna. Non verrà tuttavia utilizzato se non per dei controlli.

Sono state inizialmente create due semplici tabelle dati:

Tabella dati anagrafici

Decodifica i dati anagrafici sulla base del codice fiscale. Segue la seguente logica:

Codice Fiscale      Nome/Cognome        Qualifica   Centro di analisi
CF1             Primo soggetto          …           …
CF2             Secondo soggetto    …               …

La tabella (lo “sheet” in excel) viene chiamata “anagrafica”

Tabella dati periodici

Raccoglie i dati veri e propri, suddivisi per soggetto, mese, anno. Segue la seguente logica:

Lotto       Codice Fiscale  Anno    Mese    Costo   Ore
…       …                   …       …       …       …
…       …                   …       …       …       …

La tabella (lo “sheet” in excel) viene chiamata “dati”

Si noti che le tabelle qui indicate sono “esemplificative del concetto”, nel caso reale la quantità di informazioni è maggiore, tuttavia la logica qui descritta è quella effettivamente funzionante.

Il campo “Lotto” è stato inserito in quanto ad ogni importazione dati viene attribuito un codice progressivo numerico sequenziale, che ha lo scopo di identificare le righe della tabella che sono state importate congiuntamente. Questo allo scopo di individuarle facilmente in caso di necessità (ad esempio, cancellazione dati errati o altro).

Il codice fiscale è la chiave di ricerca per i quadri riassuntivi per soggetto, le ulteriori chiavi di ricerca saranno l’anno e il mese. I dati riportati nei prospetti di reporting saranno invece “costo” ed “ore”, eventualmente aggregate nel modo desiderato.

Recuperare i dati anagrafici

Recuperare i dati anagrafici è banale, con una formula di LOOKUP, che in excel in versione italiana ha questa forma:

=CERCA.VERT(B909;anagrafica!$A:$E;3;FALSO)

Si ipotizza di essere in un foglio alla riga 909, e che nella colonna B del foglio sia presente il codice fiscale del soggetto da decodificare. La cella contenente il codice fiscale (nell’esempio la B909) può essere inserita a mano o collegata direttamente alla corrispondente cella dello sheet “anagrafica”.

Il primo parametro è quindi la chiave di ricerca: il contenuto della cella B909.

Il secondo parametro (i parametri sono separati tra loro da punto e virgola “;”) anagrafica!$A:$E indica che la corrispondenza verrà cercata nel foglio anagrafica (una delle tabelle base dati), nelle colonne da A ad E. Si noti che in questo caso si è scelto di non inserire i numeri di riga nella definizione del range di ricerca, in questo modo non si è vincolati alla quantità di dati presenti, ed i dati potranno essere aggiunti a discrezione senza modificare la formula.

Il terzo parametro, il valore “3”, significa che stiamo decodificando il valore trovato, nella riga con il codice fiscale della cella B909, che si trova nella terza colonna. Se si va a guardare la tabella anagrafica, si vedrà che si sta decodificando il valore della “qualifica”.

Il quarto ed ultimo parametro (FALSO), pur se opzionale, indica che la ricerca sia puntuale, ovvero che il valore della chiave deve corrispondere esattamente a quanto scritto. Questo offre la garanzia di avere trovato il soggetto specifico.

Recuperare i dati quantitativi

I dati più interessanti per le analisi sono quelli quantitativi, che possono essere decodificati con la formula sotto riportata. Si fa notare che tale formula aggrega eventuali dati “spalmati” su più righe, nel caso esistano, aggregando il dato mensile (per semplicità omettiamo nell’esempio la chiave “anno”).

Ad esempio, in una tabella che riporta i totali mensili, si può scrivere per ciascuna cella del mese la formula:

=SOMMA.PIÙ.SE(dati!$F:$F;dati!$B:$B;B909;dati!$E:$E;E910)

Questa funzione consente di sommare i dati che si trovano in più righe, per essere selezionate le righe devono corrispondere a tutti i criteri indicati (regola booleana AND).

Il primo parametro indica la zona in cui sono presenti i dati numerici da sommare, SOLO SE corrispondono ai criteri di ricerca indicati nei successivi parametri.

I successivi parametri vanno letti 2 a 2, il primo della coppia indica l’area dati contenente le chiavi di ricerca da confrontare, il secondo della coppia contiene il valore della chiave da verificare.

In questo modo, i due parametri successivi vanno così letti:

• dati!$B:$B cerchiamo corrispondenza del valore indicato nel prossimo parametro nell’area dati del foglio chiamato “dati” (una delle “tabelle” del nostro database) che si trovano in colonna B. Notare l’uso del range assoluto senza righe ($B:$B) che consente di non limitare le righe cercate, e quindi essere indipendenti dalla quantità di dati caricati;
• B909 è la cella che contiene il valore corrispondente (in questo caso al Codice Fiscale) delle righe da selezionare nella somma.

Allo stesso modo vanno letti i due successivi parametri, in pratica dopo aver selezionato le sole righe del Codice Fiscale di interesse, la formula opererà una successiva, ulteriore selezione (clausola AND) di quelle righe che avranno anche la seconda corrispondenza (che in questo caso è il mese di interesse, ma potrebbe essere un ulteriore qualsiasi criterio).

Gran finale...

La magia finale viene invero effettuata da una macro apposita, che grazie a codice VBA specifico si occupa di raccogliere i dati dai fogli compilati dalle varie agenzie e li consolida, unificandoli, nel foglio “dati” che consente alle formule sopra descritte di funzionare.

Va detto che da un punto di vista concettuale comunque la macro non sarebbe indispensabile, basterebbe fare un “copia & incolla” dei dati recuperati man mano nel foglio dati, e il sistema continuerebbe a funzionare. Certo, la macro quasi azzera i tempi e le possibilità di errori, qusti ultimi confinati alla qualità dei dati inseriti.

Ad ogni modo, lo scopo di questo articolo era quello di dare le indicazioni di massima della metodologia e della logica seguite per raggiungere il risultato desiderato.