Menu

Tabelle Excel, queste sconosciute

Tabelle Excel, queste sconosciute

Confesso di aver scoperto tardivamente le tabelle di Excel, anche perché in teoria se ne può fare a meno, usando normali intervalli con intestazioni di campo nella prima riga. Come nell’esempio riportato fra poco. L’articolo rivela particolari poco noti (e macro VBA) e il pratico Modulo dati, obliato ma tutt’altro che obsoleto.

Ed ecco l’esempio di riferimento citato in apertura, un intervallo sormontato da nomi di campo:

COD

Descrizione

Quantita

Prezzo

Importo

AA

Cappelli

33,00

€ 10,00

€ 330,00

CC

Berretti

45,00

€ 8,00

€ 360,00

EE

Coppole

35,00

€ 9,00

€ 315,00

BB

Cilindri

12,00

€ 20,00

€ 240,00

EE

Coppole

45,00

€ 9,00

€ 405,00

 

Per tramutarlo in tabella verace, il da farsi è semplice. Si può partire da una riga di testatine come quelle che vanno da COD a Importo, per poi selezionarla e infine lanciare nella scheda Home il comando Tabella. Si ottiene subito quanto segue, ovvero un record vuoto subito sotto le intestazioni.

 

COD

Descrizione

Quantita

Prezzo

Importo

 

 

 

 

 

Da quel momento si possono aggiungere i dati che si vogliono, ottenendo ogni volta un ulteriore record vuoto. Insomma la tabella cresce automaticamente e, di pari passo, il nome riservato Tabella1 (che poi diventa Tabella2, Tabella3 ecc. con ulteriori tabelle create sulla cartella di lavoro), relativo ai soli record, al netto delle intestazioni. Il bello della storia è che cliccando sui pulsanti tipici delle combo box che corredano sulla destra i nomi dei vari campi si scoprono utili comandi manuali che sulla base di quel campo permettono di ordinare, in senso crescente o decrescente, la tabella oppure filtrarla in base a criteri prestabiliti: Uguale a, Diverso da, ecc. e altri speciali: Tra, Primi 10, Superiore alla media, Inferiore alla media, nonché Filtro personalizzato... (che lascio alla sperimentazione di chi legge).

A beneficio dei piĂą esperti, mostro subito una macro che applica il filtro dei Primi dieci al database fin qui mostrato, che ovviamente dovrĂ  essere arricchito di record portandoli, diciamo, a una ventina.

Sub Macro1()

' Range("Tabella1[[#Headers],[Importo]]").Select

ActiveSheet.ListObjects("Tabella1").Range.AutoFilter Field:=5, _

Criteria1:= "10", Operator:=xlTop10Items

Range("Tabella1[#All]").Select

End Sub

Si noti che ho tramutato in commento la prima riga (selezione dell’Header Importo) rivelatasi superflua in quanto il successivo comando agisce a distanza, su precisa indicazione del Field:=5 (Importo è appunto il quinto campo).

NOTA. In luogo di Ctriteria1:= “10” si può inserire un valore diverso, putacaso Ctriteria1:= “5” o quant’altro si voglia.
La macro che elimina il filtro, depurata dalla riga superflua per lo stesso motivo già visto, è la seguente, ove il metodo AutoFilter è totalmente privo dell’argomento dei criteri:

Sub Macro2()

' Range("Tabella1[[#Headers],[Importo]]").Select

ActiveSheet.ListObjects("Tabella1").Range.AutoFilter Field:=5

End Sub

La macro infine che copia nella cella K1 il risultato della Macro1 (che culmina con la seleziona della tabella filtrata sui primi dieci) è davvero sintetica:

Sub Macro3()

Selection.Copy Range("K1")

End Sub

NOTA. Il Registratore fornisce codice ridondante che riproduce la copia + la selezione di K1 + il paste e, infine, la cancellazione della casella ruotante. Invece basta applicare l’argomento Destination:= che si può omettere, limiandosi  a specificare Range(...).

Il modulo dati

Prima della comparsa della barra multifunzione nella barra dei menu era accessibile il comodo comando Dati > Modulo, che lancia una “maschera” (come si ostinano a chiamarla i vecchi programmatori Cobol/RPG), ovvero una sorta di UserForm che visualizza i contenuti dei record su cui navigare, modificandoli eccetera. Purtroppo tale opzione non è più visibile con il Ribbon standard a causa della sua ridondanza. In verità, esplorando le (noiose) operazioni di personalizzazione del Ribbon i più pazienti e fortunati scoprono il non defunto comando predetto. Confesso che non sono riuscito a portare a termine l’operazione, rigettata con un messaggio di Excel che pretende la creazione di un gruppo misterioso, almeno per me.
Chi ci riuscisse sveli il da farsi. Io comunque mi arrangio con questa sintetica routine:

Sub ModuloDati()

Foglio1.ShowDataForm

End Sub

Importante! Perché il Modulo dati abbia successo è indispensabile che il nostro database inizi dalla cella A1. Inoltre con un numero troppo alto (non ricordo quale) di campi il modulo non funziona.

Il Modulo dati, come è facile sperimentare, contiene gratis tutto o quasi quel che occorre per navigare e gestire un database collocato in cima al foglio di lavoro, come eliminare o aggiungere un record e fissare criteri, sulla base di una seconda maschera con celle vuote. Se, per esempio, digitiamo >200 nella cella dell’Importo, la navigazione da quel momento procede solo sui record di importo superiore a 200.

Le anime belle di solito si premurano di circuire l’utente finale con UserForm di più piacevole aspetto, ma implementare le funzionalità del pur umile Modulo dati e qualcuna in più richiede molto tempo. Buon lavoro a chi ci si cimenta!
Procediamo piuttosto con faccenduole piĂą terra terra.
In un secondo foglio si crei la tabella seguente, denominata automaticamente Tabella2: 

CODICE

Descrizione

Prezzo

AA

Cappelli

€ 10,00

BB

Cilindri

€ 20,00

CC

Berretti

€ 8,00

DD

Baschi

€ 7,00

EE

Coppole

€ 9,00

Soltanto i piĂą distratti non si rendono subito conto che trattasi di una pur ridicola tabella Articoli, che associa a ciascun codice la Descrizione e il prezzo standard. I sani principi dei database relazionali imporrebbero di evitare ripetizioni ed errori, relativi a copricapi inesistenti o prezzi impropri. Noi ci accontenteremo di evitare il secondo rischio.
Prima di proseguire, affibbiamo ai record del campo CODICE al netto della testata omonima il nome Codici, poi riattivando il Foglio1 e relativo database si compiano le mosse qui sotto descritte.
1. Selezionare il campo COD, esclusa l’intestazione.
2. Nella scheda Dati scegliere Convalida dati...
3. Nell’accorrente riquadro scegliere nella casella a discesa in alto la voce Elenco e, nella casella sotto, digitare =Codici.
Si vedranno comparire pulsanti tipici delle caselle a discesa nelle celle dei record del campo COD, che limitano la scelta ai codici della Tabella2 ospitata nel Foglio2.

Attenzione! .Purtroppo tali pulsanti non compaiono subito in un record aggiuntivo. Tuttavia ciò avviene se  si inserisce un valore anche fasullo, dopo di che si cliccherĂ  per operare una scelta corretta, pena l’iroso rifiuto di Excel.

Altre utili operazioni

A questo punto vediamo come limitare la digitazione di dati noti relativi agli articoli, ossia la descrizione e il prezzo. Ci viene incontro la funzione CERCA, con argomenti Valore e Matrice. Nella cella B2 del campo Descrizione si può scoprire, anche operando interattivamente (con le frecce o col mouse) la particolare formula seguente, che attinge alla appena vista Tabella2:
=CERCA([@COD];Tabella2[[CODICE]:[Descrizione]])
Una peculiarità delle tabelle Excel è che le formule di un campo sono copiate automaticamente in tutto il campo stesso, aggiustando i riferimenti, ergo non occorre ricopiare in basso la predetta né consolidarla con la mossa Ctrl+Invio previa selezione del campo.
Eseguendo le analoghe operazioni nel campo Prezzo la modalità interattiva è meno “brillante”, perché dà luogo a un’espressione basata su riferimenti, ossia per la cronaca
=CERCA([@COD];Dati!$A$2:$A$6;Dati!$C$2:$C$6)
Ragionando sul caso precedente si può comunque imporre, digitandola, quest’altra, nella cella D2:
=CERCA([@COD];Tabella2[[CODICE]:[Prezzo]])
NOTA. I piĂą abili possono trovare la precedente formula anche interattivamente, avendo cura, nel finale, di selezionare in modo discontinuo (col tasto Ctrl) i campi CODICE e Prezzo.
Faccio infine notare che il ricorso alla funzione CERCA ha due conseguenze: nel modulo Dati è inibita la digitazione forzata di un valore (viene solo indicato il risultato conseguente al codice scelto); digitando nel campo COD di in un nuovo record si va incontro a un rigetto sdegnoso.

 

Ultima modifica ilMartedì, 18 Marzo 2014 17:57

Aggiungi commento


Codice di sicurezza
Aggiorna

Torna in alto