Alcuni trucchi EXCEL!

Titolo: Alcuni trucchi EXCEL! Mar Apr 21, 2009 6:14 pm

Il file personal.xls è, in un certo senso, un file di sistema di Excel. Se presente nella directory corretta, viene caricato da Excel all'apertura. In esso possono essere memorizzate tutte le macro che si desiderano e che si vuole sempre disponibili. Spesso capita infatti di avere sviluppato una macro o una funzione interessante durante lo sviluppo di un progetto e si vorrebbe rendere tale macro o funzione disponibile anche quando si lavora con altri file.
Ovviamente, se si tiene la macro in un modulo appartente a un file specifico, una volta chiuso tale file, la macro non sarà più disponibile. Se invece si copia tale macro in un modulo del file personal.xls, dato che tale file viene sempre caricato all'apertura di Excel, essa sarà sempre disponibile per essere utilizzata con qualisasi altro file.
Il file personal.xls è effettivamente un file xls normale. L'unica differenza, sostanziale, è che si tratta di un foglio di lavoro nascosto. Questo significa che tale file non verrà visualizzato come cartella di lavoro.
La directory corretta, sul mio calcolatore, dove inserire il file si trova per OFFICE 2000 sotto il seguente percorso:

C:\Programmi\Microsoft Office\Office\XLStart

Se non esiste tale directory, cercate una directory di nome XLStart all'interno del vostro disco fisso. Tutti i file presenti all'interno di questa directory verranno infatti eseguiti al momento dell'apertura di Excel.

Ok. Adesso che avete individuato la directory corretta e ci avete copiato dentro il file, come si fa ad utilizzarlo?
Nel file personal.xls presente su questo sito, ho inserito una macro di esempio che può essere tranquillamente eliminata o modificata a vostro piacimento. Tale Macro permette di impostare il layout di stampa del foglio di calcolo secondo un formato più comodo rispetto a quello usuale che si ha quando si crea un file nuovo. La Macro imposta la pagina in orizzontale, imposta la stampa su un solo foglio e aggiunge alcune informazioni, quali il nome del file e la data, ai bordi della stampa stessa. (tale macro è quella descritta nella sezione "Impostare rapidamente il formato di stampa").
Trovandosi ora nel file personal.xls caricato, è possibile utilizzarla sul file nuovo aperto.
Infatti, premete ALT+F8 per visualizzare l'elenco delle macro disponibili:
Impostate la combo box "Macros in:" nella posizione "All Open Workbooks"

Pur avendo il file nuovo, è disoponibile una macro che si trova all'interno del file personal.xls, come indicato nella finestra stessa. Aprendo l'editore di VBA è possibile visualizzare e modificare tale macro: (premere Cancel e poi ALT+F11)
Ora si apre la finestra del VBA editor.
Sulla sinistra appaiono l'elenco dei file aperti. Si tratta di file .xla (si tratta di librerie tipicamente caricate da altri programmi). Appare anche il file Book1.xls che è il file corrente di Excel e il file PERSONAL.XLS che è il nostro file appena caricato. Sotto la cartellina indicante tale file si aprono altre cartelline contenenti i moduli con i codice delle macro.
Sulla sinistra della schermata appare il codice della macro. Potete modificare tale macro come meglio credete.Ora tale macro è stata resa disponibile ogni volta che si apre Excel per ogni file .xls che utilizzate.
NOTA BENE: L'utilizzo all'interno di altri file di funzioni memorizzate in personal.xls, limita la portabilità dei file stessi. Infatti se un file fa uso di una funzione di personal.xls, poichè tale funzione non è "interna" al file stesso, quando viene portato su un altro PC che non possiede il file personal.xls che contiene la stessa funzione, genera un errore. Il mio consiglio è di utilizzare personal.xls per tenere sempre a portata di mano delle funzioni comode e, quando si decide di utilizzarla in un file, di copiarla all'interno di un modolo VBA del file stesso prima dell'utilizzo in modo da garantire la consistenza e la portabilità del file xls prodotto.

RISORSE!!! RISORSE!!!

Leggete quello che si trova su altri siti iguardo al file personal.xls prima di utilizzare il file messo a disposizione su questo sito:

Ricerca con *Google*!
*Personal Computer Tutor*: un sito che contiene degli spunti interessanti su particolarità legate agli applicativi Microsoft
Experts Exchange: sito dove gli "esperti" si scambiano suggerimenti e considerazioni su come risolvere i problemi con le applicazioni

Qui un file personal.xls da inserire nella directory di Excel. Il file è compresso con WinZip. Decomprimetelo e inseritelo nella directory XLSTART.

DISCLAIMER: il redattore di questo sito non si assume nessuna responsabilità implicita o esplicita per l'uso dei file pubblicati sul presente sito e per i danni che l'utente finale potrebbe arrecare al suo proprio sistema per un errato uso dei file stesso o per modifiche da esso apportato sul sistema stesso.

Titolo: Re: Alcuni trucchi EXCEL! Mar Apr 21, 2009 6:16 pm

Quando si costruiscono tanti fogli di lavoro identici che contengono dei dati che hanno la stessa struttura ma si riferiscono ad ambiti diversi è necessario contemporaneamente cambiare il titolo del foglio e il nome del foglio di lavoro stesso.
Con la formula excel seguente invece il valore della cella nella quale la formula è contenuta restituisce esattamente il nome del foglio di lavoro contenuto nel Tab in basso (in questo caso "Sheet1"):

la formula è la seguente:

=MID(CELL("FILENAME";G76);FIND("]";CELL("FILENAME";G76))+1;255)

=STRINGA.ESTRAI(CELLA("FILENAME";G76);TROVA("]";CELLA("FILENAME";G76))+1;255)

La forumula ha la particolarità di funzionare solo quando il file di lavoro è stato salvato almeno una volta, non sui file nuovi.
La formula usa la funzione CELLA (CELL in inglese) che restituisce il valore completo del file di lavoro con tanto di percorso completo, nome del file e foglio attivo. La funzione STRINGA.ESTRAI permette di identificare, all'interno di quanto restituito da CELLA, la parte di nostro interesse e cioè il nome del file, che si trova subito dopo la parentesi quadra "]" che contiene il nome del file.

Titolo: Re: Alcuni trucchi EXCEL! Mar Apr 21, 2009 6:17 pm

A volte capita di avere un elenco di dati in formato database e di voler selezionare solo le righe che noi stessi o qualcun'altro ha colorato. Questo putroppo non è possibile con Excel. si può però superare il problema con VBA scrivendo una piccolissma funzione da inserire nel foglio di lavoro:

_______________________________________________________________
Function ColoreCella(a As Range, Filtro As Integer) As String

Application.Volatile True

If Filtro=0 Then
ColoreCella = a.Interior.ColorIndex
Else
ColoreCella=a.Font.Color
End If

End Function
_______________________________________________________________

Questa funzione restituisce come valore il codice colore della cella. Nel caso in cui Filtro sia posto uguale a 0 restituisce il codice colore di sfondo, altrimenti, per qualsiasi altro valore, restituisce il codice colore del colore del testo inserito nella cella.
Poco importa in realtà come Excel codifica internamente i colori della parte interna delle celle. Quelle che basta sapere è che per colori diversi avremo numeri diversi.

Ma come si usa questa funzione?
Per prima cosa si inserisce il codice (copiandolo e incollandolo direttamente da questa pagina) su un modulo VBA del file nel quale sui vuole usare la funzione. Poi si fa questo: se in cella H1 si desidera avere il codice colore della cella A1 bisogna scrivere nella formula di H1:

=ColoreCella(A1,0)	per avere il codice colore dello sfondo della cella A1
=ColoreCella (A1,1)	per avere il codice colore del testo della cella A1

Benissimo. Adesso in cella H1 comparirà un numero. Tale numero è il codice colore della cella A1. Se avete un database che si estende dalla cella A1 alla cella A200 non dovete fare altro che ripetere fa formula della cella H1 fino alla cella H200. A questo punto, ricalcolato il foglio di lavoro, non dovrete fare altro che eseugire un autofiltro sulla colonna H:H per il codice colore corrispondente al colore che volete selezionare!

Titolo: Re: Alcuni trucchi EXCEL! Mar Apr 21, 2009 6:19 pm

Ad alcune persone capita di dover lavorare in due contesti diversi su due PC differenti che hanno installato due versioni di Excel differenti, una in Inglese, l'altra in Italiano.
La Microsoft ha deciso di tradurre le formule in inglese di Excel nel loro corrispettivo mnemonico italiano. Dunque questi sfortunati devono impararsi le formule excel in due lingue. Di seguito una tabella con alcune formule, le più usate, in inglese e in italiano.

INGLESE	ITALIANO

=SUMPRODUCT( .. )	=MATR.SOMMA.PRODOTTO( .. )
=SUM( .. )	=SOMMA( .. )
=CONCATENATE( .. )	=CONCATENA( .. )
=AVERAGE( .. )	=MEDIA( .. )
=VLOOKUP( .. )	=CERCA.VERT( .. )
=HLOOKUP( .. )	=CERCA.ORIZZ( .. )
=CELL( .. )	=CELLA( .. )
=ADDRESS( .. )	=INDIRIZZO( .. )
=OFFSET( .. )	=SCARTO( .. )
=CHOOSE( .. )	=SCEGLI( .. )
=ROW( .. )	=RIF.RIGA( .. )
=COLUMN( .. )	=RIF.COLONNA( .. )
=COUNT( .. )	=CONTA.NUMERI( .. )
=COUNTA( .. )	=CONTA.VALORI( .. )
=OR( .. )	=O( .. )
=AND( .. )	=E( .. )
=NOT( .. )	=NON( .. )
=ROUND( .. )	=ARROTONDA( .. )
=ROUNDDOWN( .. )	=ARROTONDA.PER.DIF( .. )
=ROUNDUP( .. )	=ARROTONDA.PER.ECC( .. )
=SUMIF( .. )	=SOMMA.SE( .. )
=NPV( .. )	=VAN( .. )
=IRR( .. )	=TIR.COST( .. )
=FIND( .. )	=TROVA( .. )
=LEFT( .. )	=SINISTRA( .. )
=MID( .. )	=STRINGA.ESTRAI( .. )
=RIGHT( .. )	=DESTRA( .. )
=TRIM ( .. )	=ANNULLA.SPAZI (.. )

Titolo: Re: Alcuni trucchi EXCEL! Mar Apr 21, 2009 6:19 pm

Da una richiesta di un gentile lettore nasce questo trucco.Il problema da risolvere è il seguente:

Vogliamo rappresentare i numeri nelle celle in millimetri nel formato "20 mm" ma poter eseguire i calcoli su di essi come se fosse un numero qualsiasi.

Excel permette di formattare la cella in modo da inserire nella stessa qualsiasi carattere oltre al valore numerico stesso:

Alcuni trucchi EXCEL! Formato_millimetri

Il valore nella cella è semplicemente 22 (come si vede in alto di fianco a fx. Nella cella però appare il valore "22 mm" e non semplicemente 22. la formattazione da utilizzare è "Personalizzato" con il formato #0 "mm". Il fatto che è possibile utilizzare il dato 22 come un valore qualsiasi è dimostrato dalla cella B8 che contiene semplicemente la formula =B6*3

Se desiderate scrivere altro, ad esempio rappresentare un moltiplicatore, potete utilizzare il formato Personalizzato #0"x" dove, se inserite nella cella 2, otterrete 2x. Allo stesso modo potete personalizzare i numeri come meglio credete affiancando ad essi i caratteri che desiderate circondati dal doppio apice ".