Database Struttura Contabile n. 5 (Cerca.vert con riferimento di cella come indice)

In questo post vedremo come aggiornare il prospetto che fa da tramite con la struttura del nostro programma gestionale. Rivediamo il prospetto:

Fig. 1

Possiamo notare che le varie voci che compongono questo conto economico sono esattamente le stesse che abbiamo attribuito alle varie coppie conto/cdc del nostro database. Ovviamente non lo abbiamo fatto per caso, ma abbiamo creato una struttura contabile che stabilisce che ad ogni incrocio conto/cdc debba corrispondere una determinata voce di mastro.

A questo punto sarà semplice attribuire ad ogni voce di mastro del prospetto il corrispondente valore all'interno della tabella pivot.

Come? Con il caro vecchio Cerca.vert naturalmente!

Prima di farlo, però, ritorniamo alla tabella pivot

Fig. 2

Prima di tutto rinominiamo la zona che costituirà la matrice a cui farà riferimento la funzione Cerca.vert (in Fig. 2 si può notare che abbiamo attribuito al range di celle B6:F24 il nome di Pivot_CE che viene evidenziato in alto a sinistra)

Poi, visto che tra gli argomenti della funzione Cerca.vert c'è anche l'indice di colonna, vediamo ora come stabilire qual è l'indice corretto che andrà a popolare il prospetto in maniera corretta, senza generare errori.

Fig. 3

Ma andiamo per gradi.

Cominciamo a inserire la formula Cerca.vert nel modo classico (che abbiamo visto in dettaglio qui)

=CERCA.VERT($B3;Pivot_CE;2;0)

Questa formula legge il contenuto della cella B3, lo cerca all'interno della matrice Pivot_CE e restituisce come risultato il valore che appare nella seconda colonna (cioè quella con intestazione Produzione)

Il dollaro, prima della lettera della cella, blocca il riferimento di colonna, mentre il riferimento di riga è libero. Così sarà possibile copiare la formula nelle righe sottostanti, con conseguente aggiornamento della riga nella formula, mentre il riferimento alla colonna B sarà fisso, anche se copiato nelle celle adiacenti.

Se copiamo infatti la formula nelle due colonne a destra, avremo 

=CERCA.VERT($B3;Pivot_CE;3;0) e  =CERCA.VERT($B3;Pivot_CE;4;0)

Queste due formule restituiscono il valore corrispondente alla cella B3 che appare rispettivamente nella terza (Commerciale) e quarta (Struttura) colonna.

Ma c'è un problema: nel nostro esempio siamo in presenza di una struttura piuttosto scarna, ma supponiamo di avere una struttura molto più dettagliata, con un maggiore numero di CDC RICL. Ad un certo punto dell'anno, soprattutto nei primi mesi dell'anno, potrebbero esserci alcuni cdc non ancora movimentati.

Cosa succederebbe se la Pivot non restituisse tutti tre i CDC RICL e gli indici fossero impostati manualmente? Vediamo un esempio.

Fig. 4

In Fig. 4 possiamo notare che non appare la colonna Commerciale. Cosa succede allora alle formule che abbiamo appena visto?

Nella colonna Produzione del prospetto:

=CERCA.VERT($B3;Pivot_CE;2;0)   restituisce il valore prendendolo dalla colonna Produzione

Nella colonna Commerciale del prospetto:

=CERCA.VERT($B3;Pivot_CE;3;0)   restituisce il valore prendendolo dalla colonna Struttura

Nella colonna Struttura del prospetto:

=CERCA.VERT($B3;Pivot_CE;4;0)   restituisce il valore prendendolo dalla colonna Totale

Appare evidente che i dati non vengono riportati in maniera corretta, perché, non essendo presente nella Pivot la colonna Commerciale, nel prospetto la formula riporta la colonna errata (Struttura)

Come fare allora?

Dobbiamo stabilire l'ordine esatto in cui vogliamo che la formula trovi le colonne e lo facciamo inserendo una formula che stabilisca l'ordine in maniera univoca:

Fig. 5

=SE(C5="Produzione";2;SE(C5="Commerciale";3;SE(C5="Struttura";4;"")))

e cioè quando l'intestazione di colonna si chiama Produzione l'indice deve sempre essere il numero 2, se si chiama Commerciale l'indice è 3, se si chiama Struttura l'indice è 4, e se l'intestazione della colonna non corrisponde a nessuna di queste tre descrizioni, l'indice non ha nessun valore (per vedere in dettaglio la Funzione SE vedere qui)

Perché abbiamo dato questo ordine? Perché è l'ordine previsto nel prospetto (Fig. 1) e lo determiniamo con una formula per essere sicuri che al variare della struttura della tabella pivot, non avrò sorprese con le formule.

Di conseguenza, nel prospetto le formule saranno le seguenti:

Fig. 6

Nella colonna Produzione del prospetto: 

=CERCA.VERT($B3;Pivot_CE;CE!C$1;0)   dove CE!C$1 corrisponde a 2

Nella colonna Commerciale del prospetto:

=CERCA.VERT($B3;Pivot_CE;CE!D$1;0)   dove CE!D$1 corrisponde a 3

Nella colonna Struttura del prospetto:

=CERCA.VERT($B3;Pivot_CE;CE!E$1;0)   dove CE!E$1 corrisponde a 4

Nel prossimo post vedremo come copiare velocemente le formule nel prospetto e come evitare che la cella restituisca #N/D

Post precedenti

Database Struttura Contabile n. 4 (Ordinamento manuale)

Database Struttura Contabile n. 3 (Incolla con Formato Speciale)

Database Struttura Contabile n. 2 (Funzione CERCA.VERT, Unione testo celle)

Database Struttura Contabile n.1 (utilizzare un file .txt)