Di Ernesto Sagramoso Excel - utesandonatosangiuliano.org · I trucchi e i consigli d’uso che vi...

110PC Professionale - Novembre 2008

ExcelI segreti di Office

Di Ernesto Sagramoso

EXCELCOME FARE

111

computer e comprende Word, Excel,PowerPoint e OneNote, si trova ameno di 100 euro, Iva inclusa. E glistudenti universitari possono appro-fittare della recente iniziativa “Offi-ce 30 e lode” per acquistare OfficeUltimate 2007, che raccoglie tutti isoftware della famiglia Office, con lasola eccezione di Communicator2007, per 52 euro.Negli anni Office ha acquistato sem-pre più funzioni, ma gli utilizzatorinormali non ne conoscono che unapiccola parte. La colpa è attribuibilealla scomparsa dei manuali su carta,ma anche all’oggettiva difficoltà diesporre centinaia e centinaia di co-mandi in un’interfaccia nata fin troppianni fa. Palliativi come l’autoconfigu-razione dei menu e delle toolbar pernascondere i comandi meno sfruttatie rendere immediatamente accessibi-li quelli di uso più frequente non han-no raggiunto l’obiettivo sperato. Nel progettare la versione 2007 di Of-fice, Microsoft ha affrontato alla baseil problema riprogettando radical-mente l’interfaccia. Sono spariti menua discesa e toolbar per lasciare il po-sto a una “barra multifunzione” (ilribbon), organizzata in schede a lin-guetta, che mette a disposizione inmaniera logica le funzioni specifichedell’attività di redazione o revisionecui è dedicata la scheda selezionatain quel momento. La barra multifun-zione ha suscitato reazioni contra-stanti: molti utenti l’hanno apprezza-ta ma molti altri l’hanno criticata sen-

L a suite di produttività targataMicrosoft è la regina indiscus-sa del mercato in quanto a

numero di utenti. Si tratta di un pro-dotto sofisticato e costoso, anche se– grazie alla concorrenza di softwa-re gratuiti come OpenOffice.org –gli utenti consumer oggi possono ot-tenerla a un prezzo decisamentecontenuto. La versione Home & Stu-dent, che può essere installata su tre

za mezze misure, ritenendola un saltotroppo brusco col passato. A nostroavviso il ribbon riesce a rendere effet-tivamente più accessibile la potenzadi Office, ma è facile capire il punto divista di chi è arrivato con fatica a pa-droneggiare le idiosincrasie dellavecchia interfaccia e ora si trova a do-ver ricominciare da capo. Anche ilribbon non può fare miracoli, e per di-ventare un vero power user di Officeservono tempo e pazienza.

IInn qquueessttoo aarrttiiccoolloo e nei due che tro-verete nei mesi successivi cerchere-mo di offrirvi una scorciatoia, pre-sentandovi alcuni dei nostri trucchipreferiti per i programmi fondamen-tali della suite Microsoft: Excel,Word e PowerPoint, partendo pro-prio con Excel. Non ci siamo dimen-ticati naturalmente della release2003, ancora molto diffusa: quasitutte le procedure che troverete nel-le prossime pagine sono infatti indoppia versione (quelle applicabili auna sola release sono esplicitamentesegnalate). Buona lettura, e appun-tamento al mese prossimo con i se-greti di Word.

Da 2003 a 2007senza problemi

Quando si passa da Excel 2003 a2007 si rimane spaesati dalla nuo-va interfaccia a schede, denomina-ta Barra multifunzione, che con-

1

Quasi tutti gli utentidi Excel non ne sfrut-tano che in minimaparte l’incredibile po-tenza. I trucchi e iconsigli d’uso che vipresentiamo in que-sto articolo vi aiute-ranno a trasformarvirapidamente in veripower user del foglioelettronico fornitocon Microsoft Office.

PC Professionale - Novembre 2008

1

2007

Quando si lavora con Excel o qualsiasi altroprogramma che gira sotto Windows, solita-

mente si utilizza la tastiera solo per inserire infor-mazioni mentre per i comandi si predilige il mou-se. Il ricorso ai comandi da tastiera permette peròdi velocizzare molte operazioni d’uso comune. Ec-co alcune delle scorciatoie da tastiera più utili.

F4 Ripete l’ultima operazione effettuata

Ctrl + ‘ (apostrofo) Copia nella cellaselezionata la formula e il formato numericopresenti in quella soprastante, senza modifi-care i riferimenti.

Ctrl + 1 Attiva il box Formato Celle. Questasoluzione è più comoda rispetto all’utilizzo siadel classico comando Formato/Celle sia delmenu contestuale attivabile col tasto destrodel mouse.

Ctrl + ; (punto e virgola) e Ctrl + :(due punti) Inserisce la data e l’ora corren-te. A differenza della funzione ADESSO, il valorenon cambia mai.

Alt + Invio Serve per andare a capo quandosi digita del testo in una singola cella. Premen-do invece solo Invio il cursore si porta nella cel-la sottostante.

F11 Consente di creare istantaneamente ungrafico con i dati evidenziati. Di base Excel com-pone istogramma a barre bidimensionali in unnuovo foglio.

Utili scorciatoie da tastiera

EXCELCOME FARE


sente di selezionare velocemente lamaggior parte delle opzioni delprogramma. Inoltre la disposizionedei comandi che non rispecchiaquella dei menu a tendina dellaversione 2003, può rendere difficol-tosa la ricerca delle funzioni ancheai più esperti. Per questa ragioneMicrosoft ha creato una serie di uti-lity per Word, Excel, Powerpoint,Outlook e Access che consentonodi scoprire, in modo interattivo, lanuova posizione dei comandi.

LLaa vveerrssiioonnee ppeerr EExxcceell ppuuòò eesssseerreessccaarriiccaattaa all’indirizzo www.micro-soft.com/downloads/details.aspx?familyid=89718abd-2758-47b3-9f90-93788112b985&displaylang=it e in-stallata direttamente sul propriocomputer (la soluzione che vi con-sigliamo). In alternativa è possibileconsultarla direttamente sul sito diMicrosoft Office (http://office.mi-crosoft.com/it-it/excel/HA101491511040.aspx?pid=CH100648241040). Il modus operandi è molto sempli-ce, basta lanciare la guida (compa-rirà la classica interfaccia di Excel2003) e scegliere la funzione desi-derata. A questo punto verrà visua-lizzato un box che spiega comecomportarsi con la versione 2007del software. Per esempio, per proteggere un fo-glio, si scopre che – invece di opta-re per Strumenti/Protezione/Pro-teggi foglio… – è necessario andarenel gruppo Celle della scheda Ho-me, scegliere Formato e quindi Pro-teggi foglio, oppure dalla schedaRevisione attivare Proteggi fogliodal gruppo Revisioni.

Nascondere la Barra multifunzione

La nuova Barra multifunzione puòrisultare d’intralcio quando si devo-no gestire fogli molto estesi. Fortu-natamente è possibile nasconderla efarla comparire solo all’occorrenza(proprio come la Barra delle applica-zioni di Windows). Basta fare clic coltasto destro del mouse sulla Barramultifunzione e scegliere Riduci aicona Barra multifunzione. A questopunto, Excel mostrerà solo i nomidelle varie schede.

Formattazionepersonalizzataper i nuovi fogli

Quando si crea un nuovo foglio,Excel lo formatta in base ai para-metri impostati tramite Strumen-ti/Opzioni… in 2003 e Pulsante Of-fice/Opzioni di Excel.. in 2007. Poi-ché questa funzione consente solodi scegliere tipo e dimensione deicaratteri oltre al numero di fogli dilavoro, è preferibile sfruttare la ge-stione dei modelli. Con 2003 si de-ve formattare un documento con lecaratteristiche desiderate e poi sal-varlo come modello con il nome diCartel.xlt (il modello standard usa-to da Excel) nella cartella Program-mi\Microsoft Office\Office\XL-START presente sul disco C. Poi-ché esiste già un file con lo stessonome, è necessario sovrascriverlo.Con Excel 2007 bisogna invece sal-vare come Modello (con un nome apiacere) il documento formattato in

3

22007

2 3

EXCELCOME FARE


modo opportuno e, quando si creaun nuovo file, sceglierlo dal riqua-dro Ultimi modelli usati.

Righe con colorialternati

Per lavorare più agevolmente con ifogli che contengono un numeropiuttosto elevato di colonne, è mol-to comodo colorare le righe a tintealternate – per esempio bianco perquelle dispari e azzurro per le pari– sullo stile dei vecchi tabulati perle stampanti a impatto. Per farlo basta controllare il risultatodella divisione per due del numerodi riga (RIF.RIGA()). Se non c’è unresto decimale (è vera la condizioneRESTO(RIF.RIGA();2)=0) significache la riga è pari e si applica quindila colorazione desiderata.Con Excel 2003 bisogna richiamareFormato/Formattazione condizio-nale, scegliere La formula è comeCondizione 1 e inserire =RESTO(RIF.RIGA();2)=0 nell’apposita ca-sella. Ora non rimane che scegliereil formato prescelto (il fondo azzur-ro) e copiare la formattazione sututte le altre celle con il pennelloCopia formato. Con 2007 si deveinvece scegliere Nuova regola dalmenu Formattazione condizionale,presente nel gruppo Stil i dellascheda Home e fare un clic su Uti-lizza una formula per determinarele celle da formattare. Nel box For-matta i valori per cui questa formu-la restituisce Vero digitare =RE-STO(RIF.RIGA();2)=0 e scegliere ilformato preferito.

4

Estrapolare un numero da unacella alfanumerica

Molte volte è necessario estrapolarela parte numerica dalle celle conte-nenti una sigla alfanumerica seguitada un numero. Nel caso in cui il testoabbia sempre la stessa lunghezza ba-sta usare la funzione =STRINGA.ESTRAI(A1;8;2). In questo esempio siestrapolano 2 caratteri dalla cella A1partendo dall’ottava posizione. I duecaratteri vengono inseriti nella cellaricevente come testo e non sarà quin-di possibile effettuare delle operazio-ni matematiche. Per sopperire a que-sto inconveniente basta modificare laformula in =VALORE(STRINGA.ESTRAI(A1;8;2)) oppure aggiungereun’operazione aritmetica che non in-

5 fluenzi il risultato, per esempio=(STRINGA.ESTRAI(A1;8;2)) + 0.Se la stringa da saltare ha invece unalunghezza variabile ma termina sem-pre con un determinato carattere (peresempio “/“) e la dimensione del nu-mero cambia, bisogna agire in modoleggermente diverso, sfruttando lefunzioni TROVA e LUNGHEZZA.Per prima cosa si sostituisce TRO-VA(“/”;A2;1))+1 al numero 8, in modoche l’estrapolazione parta automati-camente dal carattero che segue “/”.Al posto del numero 2 bisogna poi in-serire LUNGHEZZA(A2)-TRO-VA(“/”;A2;1), formula che calcola ilnumero di caratteri dopo il segno “/”.La formula finale sarà quindi =VALO-RE(STRINGA.ESTRAI(A2;(TRO-VA(“/”;A2;1))+1;LUNGHEZZA(A2)-TROVA(“/”;A2;1))).

4 5

6

EXCELCOME FARE


7 8

“ “

Excel vi permette di calcolare la somma di un gruppo di cellesenza dover digitare una formula.

Nascondere i dati e le formule

Quando si scrivono formule com-plesse che eseguono calcoli critici,potrebbe essere utile nasconderle asguardi indiscreti. Per attivare que-sta protezione è sufficiente eviden-ziare la cella desiderata, selezionareFormato/Formato celle e, nella sche-da Protezione, spuntare Nascosta. Attivando poi la protezione del fo-glio (dalla scheda Revisione in Excel2007 e dal menu Strumenti/Protezio-ne/Proteggi foglio in 2003) si vedrà ilrisultato, ma la formula rimarrà invi-sibile. Per evitare che, per ragioniestetiche, non appaiano i dati di al-cune celle, basta optare per il forma-to personalizzato ;;; (tre volte punto evirgola). Per impostarlo, si deve fareun clic col tasto destro del mouse,scegliere Formato celle/Numero edevidenziare Personalizzato. Nel ri-quadro Tipo digitare ;;; e premereInvio. In questo modo il contenutodelle celle interessate verrà riportatocon lo stesso colore dello sfondo inmodo da risultare invisibile, ma po-trà essere controllato e modificatonella barra di editing.

Pollici, galloni e altre unità di misura

Excel facilita la conversione da un’u-nità di misura a un’altra, per esem-pio dal sistema metrico decimale aquello imperiale (utilizzato nei Paesianglosassoni). Con Excel 2003, an-date in Strumenti/Componenti ag-giuntivi, attivate la voce Strumenti

9

8Conoscere il risultato senzascrivere la formula

Excel vi permette di calcolare lasomma di alcune celle, senza doverscrivere la corrispondente formula.Basta infatti evidenziare le caselledesiderate e controllare la parte de-stra della barra di stato del program-ma (quella in basso), che mostreràautomaticamente la somma dei dati.In Excel 2003, facendo un clic con ilpulsante destro del mouse sulla som-ma apparirà una tendina che con-sente di scegliere tra le funzioni:Media, Conteggio, Conta num., Maxe Min. Excel 2007 permette anche diattivare contemporaneamente piùfunzioni.È interessante notare che, a eccezio-ne di Conteggio, le celle selezionateche includono del testo vengonoignorate.

Controllare la presenza di un elemento in una colonna

In alcune applicazioni è necessarioverificare se un elemento è già pre-sente in un foglio di lavoro, per noninserirlo una seconda volta. Per com-piere automaticamente questo con-trollo basta avvalersi della funzioneCONFRONTA che, se è presente l’e-lemento, ne restituisce la posizionein caso contrario visualizza l’errore#N/D. La sintassi è molto semplice:CONFRONTA (Valore o cella che locontiene; range di celle dove cerca-re; Corrispondenza). Come Corri-

7

6 spondenza si deve mettere 0 se ilrange di celle nel quale si ricercal’elemento non è ordinato e la ricer-ca deve fermarsi quando viene tro-vato; con 1 (valore considerato daExcel se si omette questo parametro)la funzione presume che i dati sianoin ordine crescente e si ferma quan-do incontra un valore uguale o mi-nore di quello cercato; con -1 Excelpensa che le informazioni siano in-serite in ordine decrescente e si fer-ma quando incontra un valore ugua-le o maggiore. In questo caso, volendo cercare nelrange I2:I407 quanto inserito nellacella B1, si deve digitare nella cellaC2 la formula:=CONFRONTA(B1;I2:I407;0)Per rendere i risultati più leggibili, alposto dell’errore #N/D e del numerodi posizione si può visualizzare unmessaggio (Presente/Assente) tra-mite la funzione VAL.ERRORE, chesi accorge del sopraggiungere di unerrore, e quella SE che compie delleazioni condizionate. La formula sitrasforma quindi in =SE(VAL.ERRO-RE(CONFRONTA(B1;I2:I407;0));”Assente”;”Presente”).

Sub MaiuscoleMinuscole()' MaiuscoleMinuscole Macro' Emula in Excel il comportamento della scorciatoriaShift + F3 di Microsoft WordDim lunghezza, t As IntegerDim Caratteri, Carattere1, Carattere2 As StringFor Each cella In Selection

lunghezza = Len(cella.Value)Caratteri = Trim(cella.Value)Select Case lunghezza' controlla la lunghezza del contenuto della cellaCase 1

' se è minuscola trasformala in maiuscolot = Asc(Caratteri)If ((t >= 97 And t <= 122) Or t = 232 Or t = 233 Or

t = 224 Or t = 236 Or t = 242 Or t = 249) Thencella.Value = Format(Caratteri, "!>@")

Elsecella.Value = Format(Caratteri, "!<@")

End IfCase Else

'Inserisce nelle variabili Carattere1 e Carattere2i primi due caratteri del testo.

'Se il secondo carattere è uno spazio, allora pren-de il terzo e il quarto.

'In questo modo si capisce se la parola è maiusco-la, minuscola e cosÏ via

Carattere1 = Left(Caratteri, 1)Carattere2 = Mid(Caratteri, 2, 1)If Carattere2 = " " Then

Carattere1 = Mid(Caratteri, 3, 1)car2 = Mid(Caratteri, 4, 1)

End IfIf (Asc(Carattere1) > 64 And Asc(Carattere1) < 91

And Asc(Carattere2) > 96) Then' la prima è maiuscola e la seconda è minusco-

la, quindi converto in maiuscolocella.Value = StrConv(Caratteri, vbUpperCase)

ElseIf (Asc(Carattere1) > 64 And Asc(Carattere1)< 91) And (Asc(Carattere2) > 64 And Asc(Carattere2) <91) Then

' la prima e la seconda sono maiuscole, quindiconverto in minuscolo

cella.Value = StrConv(Caratteri, vbLowerCase)Else

' altrimenti converto tutto in minuscolocella.Value = StrConv(Caratteri, vbProperCase)

End IfEnd Select

NextEnd Sub

Da maiuscolo a minuscolo (e ritorno)

EXCELCOME FARE


di analisi, premere OK e seguire leistruzioni per l’installazione dell’o-monimo componente. Con 2007 fareclic sul Pulsante Office e quindi suOpzioni di Excel/Componenti ag-giuntivi. Scegliere Componenti ag-giuntivi di Excel nella casella Gesti-sci, premere Vai e verificare cheStrumenti di analisi sia attivo. Aquesto punto è possibile utilizzarela funzione CONVERTI(Numero ocella; Da unità di misura; a unità dimisura). L’elenco completo delleunità utilizzabili con questa funzio-ne (e delle relative sigle) può esse-re ottenuto chiedendo aiuto all’As-sistente di Office.

Nessun duplicato

Per evitare che in una colonna delfoglio di lavoro vengano immessi da-ti duplicati (per esempio quando sigestisce un indirizzario), risulta inte-ressante lo strumento Convalida.Evidenziate una cella della colonna(per esempio la A) poi, dalmenu/scheda Dati, scegliete la voceConvalida dati (in Excel 2007 si tro-va nel gruppo Strumenti dati). Oradal menu a tendina Consenti attiva-te Personalizzato e come Formula di-gitate =CONTA.SE(A:A;A1)=1. Conqueste impostazioni Excel conta

10

10

9

EXCELCOME FARE


biente di sviluppo di Office). Se appa-re il messaggio Impossibile modificareuna macro su una cartella di lavoronascosta… nessun problema, attivareFinestra/Scopri (2003) oppure Visua-lizza/Finestra/Scopri (2007) e scoprirePERSONAL.XLS (la cartella persona-le di Excel che contiene la macro). Aquesto punto non vi rimane che inse-rire il codice della macro.

Barre strumenti e Componentiaggiuntivi

Excel 2003 permette di creare unabarra degli strumenti personalizzatacollegata sia all’applicativo sia a unospecifico documento. Il vantaggio del-la seconda soluzione è la possibilità,per esempio, di inviare a un altroutente una barra studiata apposita-mente per un determinato foglio di la-voro. E con 2007? Poiché questa ver-sione non usa barre degli strumenti enon consente di gestire in modo sem-plice le schede, è necessario ricorrerea uno stratagemma. Per prima cosa sideve creare un documento con Excel2003 (Test.xls) e poi aggiungere allostesso la barra delle applicazioni per-sonalizzata. Per compiere questa ope-razione aprite la finestra di dialogoPersonalizza, facendo un clic con il ta-sto destro del mouse sulla barra deimenu, e premete il pulsante Allega.Evidenziate la barra desiderata, fateclic su Copia in modo che compaianel riquadro Barre degli strumentinella cartella e salvare il file. Ora,quando lancerete Excel 2007 e aprire-te il documento Test.xls apparirà labarra delle applicazioni personalizza-ta come un nuovo gruppo della sche-da Componenti aggiuntivi. Dato chequesto nuovo gruppo non verrà elen-cato tra i componenti aggiuntivi in-stallati (se volete controllarli, selezio-nate Pulsante Office/Opzioni di Ex-cel/Componenti aggiuntivi), non po-trà essere disattivato.

Copiare i subtotali

Excel può calcolare automaticamentei subtotali di un lungo elenco, ma dibase non consente di copiarli. Infatti,quando si visualizzano solo le righecon i conteggi e si utilizzano le funzio-ni Copia e Incolla, vengono elaborate

13

12

13

12

quante volte il valore che si sta digi-tando nella cella corrente apparenella colonna e, se la risposta è di-versa da 1, blocca l’immissione. Peravvisare l’utente della duplicazione,andate nella scheda Messaggio dierrore, lasciate il segno di spunta suMostra messaggio di errore quan-do… e come Messaggio di errore di-gitate, per esempio, Valore già inse-rito!. Attenzione: è necessario attiva-re la convalida prima di inserire i da-ti nelle celle.

Maiuscolee minuscole

Con Word, premendo contempora-neamente i tasti Maiuscolo+F3, sicambia ciclicamente il formato del te-sto secondo questa sequenza:

• iniziali di ciascuna parola in maiuscolo • tutto il testo selezionato in maiuscolo • tutto il testo selezionato in minuscolo

Excel non offre questa comoda fun-zionalità, ma è possibile simularla conuna macro. Con 2007 bisogna andarenella scheda Sviluppo e scegliere Re-

11

gistra macro dal gruppo Codice, men-tre con 2003 si deve attivare Strumen-ti/Macro/Registra nuova macro. Se lascheda Sviluppo non fosse disponibi-le, per visualizzarla fate clic sul Pul-sante Office, poi su Opzioni Excel enella categoria Impostazioni generali(nella sezione Opzioni principali perl’utilizzo di Excel) spuntate la casellaMostra scheda Sviluppo…) Come no-me inserire, per esempio, Maiuscole-Minuscole e come Tasto di scelta ra-pida, CTRL+m. Attenzione a selezio-nare Cartella macro personale nellacasella Memorizza macro in:, in casocontrario il codice verrà collegato soloal documento aperto. Ora bisognapremere il pulsante OK e subito dopoquello per terminare la registrazione.In questo modo avrete creato una ma-cro vuota a cui dovrete aggiungere ilcodice visibile nel riquadro “Da mi-nuscolo a maiuscolo (e ritorno)”; perrisparmiarvi la fatica ed evitare possi-bili errori di digitazione potete sfrutta-re il file di testo che trovate sul Dvdallegato. Andate in Strumenti/Ma-cro/Macro oppure in Sviluppo/Macro,evidenziate MaiuscoleMinuscole epremete Modifica per aprire l’editordi Visual Basic for Application (l’am-

EXCELCOME FARE


anche quelle con il dettaglio. Per ov-viare a questo inconveniente, bisognaselezionare le celle con i subtotali,premere il tasto F5 (oppure attivare ilcomando Modifica\Vai a) e, dalla fi-nestra di dialogo che appare, fare clicsul pulsante Speciale. Selezionatequindi Solo celle visibili e utilizzateCopia/Incolla: verranno copiate solole celle con i subtotali.

Solo per i vostri occhi

Quando si controlla un foglio di Ex-cel inviato da un amico o un colle-ga, può capitare di constatare chela numerazione delle righe è di-scontinua. Questo non è dovuto aun bug di Excel, ma all’utilizzo del-la funzionalità che consente di ce-lare righe e colonne (di solito perevitare che si modifichino involon-tariamente dati o formule). Per na-scondere, per esempio, la quinta ri-ga, basta evidenziarla facendo unclic sul numero 5 dell’intestazione,premere il tasto destro del mouse escegliere l’opzione Nascondi. Perevitare che chiunque renda nuova-mente visibile la riga, è sufficienteproteggere il foglio di lavoro. Prima di attivare la protezione (tra-mite l’opzione Strumenti/Protezio-ne/Proteggi foglio in 2003, mentrecon 2007 si parte dalla scheda Homee si sceglie Formato/Proteggi fogliodal gruppo Celle), si devono eviden-ziare tutte le celle, premere Ctrl+1,andare in Protezione e togliere il se-gno di spunta da Bloccata. In caso

14contrario sarà impossibile inseriredati in tutte le celle del foglio.

Senza errori

Excel può risultare utile anche perrealizzare modulistica da distribui-re tramite e-mail. Per evitare che imoduli vengano compilati in modoerrato bisogna affidarsi all’opzioneConvalida, che controlla la con-gruenza dei dati e avvisa l’utentedi eventuali errori di battitura. Perattivare il controllo evidenziare lacella o il gruppo di celle da verifi-care e, dal menu/scheda Dati sce-gliere la voce Convalida dati. Ilprogramma consente di gestire inmodo diverso numeri e testo. Ad esempio per consentire l’inseri-mento di un valore compreso tra 0e 100, in Consenti scegliere Deci-male, in Dati lasciare tra e digitarei valori minimo e massimo (0 e100). Si può inoltre definire unmessaggio di avvertimento e deci-dere se bloccare l’utente finchénon inserisce le informazioni cor-rette (nella scheda Messaggio dierrore scegliere Interruzione) op-

15

pure mostrare solo un avviso (opta-re per Avviso o Informazione).Quando si lavora con dati testuali,come un nominativo, è possibilefarli scegliere da un menu a tendi-na. La prima operazione è quella diinserire gli elementi della tendinain una colonna del medesimo fogliodi lavoro che difficilmente verrà vi-sualizzata come la IV (meglio se ditipo Nascosto). Ora bisogna evidenziare le celle in-teressate, scegliere Convalida dati,in Consenti optare per Elenco e co-me Origine scrivere il range checontiene i nominativi (=$IV$2:$IV$200). Quando si evidenzia lacella apparirà il classico pulsanteche attiva la tendina.

Riferimentifissi

Per inserire l’indirizzo di una cellain una formula, sono disponibilidue convenzioni: il riferimento re-lativo e quello assoluto. Nel primocaso Excel memorizza la distanzatra la cella selezionata e quella dicui si sta scrivendo l’indirizzo. Se ilcursore è in C10 e si digita la for-

16

14 15

“

“

Excel può risultare utile anche per realizzaremodulistica da spedire tramite e-mail.

EXCELCOME FARE


mula =C1-C2, il programma capi-sce che si vuole sottrarre il conte-nuto della cella distante 8 righedalla C10 a quello della cella di-stante 9 righe. Quando si copia laformula, per esempio, nella cella afianco (D10), Excel la modifica(=D1-D2) in modo da sottrarre sem-pre il contenuto delle celle distanti8 e 9 righe dalla D10. Questo modus operandi è molto co-modo, tranne quando è necessarioduplicare delle formule senza chevengano modificati gli indirizzi. Inquesto caso risulta utile l’indirizzoassoluto, che consiste nell’inserireil carattere $ prima dei numeri edelle lettere. Utilizzando quindi=$C$1+$C$2, quando si copia o sitrascina la formula, Excel sommeràsempre il contenuto delle celle C1e C2. Per cambiare velocemente iltipo di riferimento, posizionarsi conil cursore nella barra di editing do-ve compare la formula e premereciclicamente F4.

Modificare le costanti

Molto spesso, nei calcoli interven-gono dei valori che variano fre-quentemente, come il prezzo dellabenzina. Quando si lavora con nu-merose formule (magari in fogli dif-ferenti) che utilizzano questi ele-menti, è preferibile poterli modifi-care una sola volta per evitare di-menticanze. Una soluzione consistenell’inserire, al posto del valore co-stante, il riferimento a una cella po-

17

sta, per esempio, in un foglio indi-pendente. Modificando il numerocontenuto in tale cella cambieràautomaticamente il risultato di tut-te le formule. Un’opzione più ele-gante è quella di creare delle co-stanti numeriche a cui assegnareun valore modificabile in ogni mo-mento. Per farlo, con Excel 2003 bi-sogna attivare il menu Inserisci,scegliere Nome e poi Definisci. Nella casella Nomi nella cartella dilavoro digitare Benzina e in quellaRiferito a inserire =1,522. Ora, ri-portando nelle formule il termineBenzina, verrà utilizzato automati-camente il valore 1,522. Con Excel2007 si deve scegliere Gestione

Nomi del gruppo Nomi definiti pre-sente nella scheda Formule, pre-mere il pulsante Nuovo e agire co-me già spiegato.

Formule al microscopio

Quando si scrivono formule com-plesse che utilizzano funzioni nidi-ficate (per esempio una sequenzadi SE..), può accadere che il risulta-to non sia quello sperato. Per sco-prire la causa del malfunzionamen-to è consigliabile valutare l’esito diciascuna funzione sfruttando, peresempio, il pulsante F9. Per esegui-

18

16 17

18

EXCELCOME FARE


re questo controllo bisogna, sullabarra di modifica della formula,evidenziare la funzione preselta epremere F9 in modo che venga so-stituita con il valore calcolato. At-tenzione: una volta trovato l’errore,è essenziale uscire dalla modalitàdi modifica tramite il tasto ESC cheannulla tutte le operazioni effettua-te, in caso contrario la formulaverrà sostituita permanentementecon il risultato.

Non per tutti

A volte è necessario distribuire unfoglio di lavoro a più persone per-mettendo però solo ad alcuni utentidi modificarlo. In teoria si potreb-bero compilare due documentiidentici e proteggerne uno solo, ma

19

si tratta di una soluzione poco pra-tica. Excel offre una comoda opzio-ne che autorizza solo determinatiutenti a cambiare il contenuto diun range di celle. Per prima cosa ènecessario evidenziare tutto il fo-glio e attivare la protezione dellecelle (inserendo un segno di spuntasulla casella Bloccata che comparefacendo un clic destro del mouse escegliendo Formato celle/Protezio-ne). Dopo scegliere Strumenti/Pro-tezione/Consenti agli utenti la mo-difica degli intervalli con Excel2003 e Revisione/Consenti agliutenti la modifica degli intervallicon 2007. A questo punto premereil pulsante Nuovo…, dare un nome

all’intervallo (per esempio quellodella persona che potrà consultar-lo), decidere il range (Riferito a cel-le:) e digitare la password. Excelpermette di impostare range diver-si a seconda dell’utente. L’ultimopasso consiste nell’attivare la pro-tezione del foglio tramite il coman-do Proteggi foglio (nella schedaRevisione di Excel 2007 e nel menuStrumenti/Protezione di 2003). Gliutenti autorizzati a lavorare sul do-cumento protetto dovranno sempli-cemente inserire la password.

Un controllo finale

Una cartella di Excel può contene-re numerosi dati nascosti che pos-sono essere scoperti facilmente daun utente esperto. Per questa ra-gione, prima di inviare un file Ex-cel conviene verificare che noncontenga informazioni riservate.Con 2007 è sufficiente fare un clicsul pulsante Office, scegliere Pre-para e poi Controllo documento. Aquesto punto il programma propo-ne una serie di controlli, tra cui Ri-ghe e Colonne nascoste, Commen-ti e annotazioni. Dopo aver lasciato i l segno dispunta su quelli desiderati, si devepremere Controlla per ottenerel’elenco degli elementi riscontrati,con a fianco il pulsante Rimuovitutto che consente di eliminarliautomaticamente. Attenzione:quando si utilizza questo comando

ed è presenteu n f o g l i o n a -scosto, questoviene elimina-t o d e f i nit iva-mente (non èpossibile recu-perarlo nean-che con Ctrl+Z).Sempre da Pre-para, att ivareVerifica compa-tibilità per con-trollare che i ldocumento noncontenga carat-terist iche nongestite da ver-sioni precedentidi Excel. •

202007

“ “

Excel permette di autorizzare solodeterminati utenti a cambiare il contenutodi un range di celle.

19

20

EXCELCOME FARE


PASSO PER PASSO

Formattazione condizionale con Excel 2007

Q uando si devono gestire fogli di grosse dimensioni è comodo ri-correre alla formattazione condizionale. Solo in questo modo si

vede immediatamente se una cella contiene le informazioni desidera-te oppure se il suo contenuto si scosta sensibilmente dalle previsio-ni. Fino a Excel 2003 si poteva agire solo sugli attributi del testo (ca-rattere, dimensioni e così via) oppure sul colore di fondo, mentre conOffice 2007 si possono gestire anche barre colorate o inserire picco-le icone. Sono state create inoltre sei sotto-opzioni (Regole eviden-ziazione celle, Regole Primi/Ultimi, Barre dei dati, Scale di colori, Setdi icone e Nuova regola) che consentono di formattare le celle conpochi clic del mouse (figura 1).

Per questo esempio abbiamo deciso di personalizzare un’ipoteticatabella di case in vendita presso un’immobiliare, contenente le infor-mazioni Tipologia, Zona, Superficie, Terrazzo, Prezzo e Prezzo/Mq.Osservando il foglio non formattato sarebbe difficile individuare acolpo d’occhio gli appartamenti che rispondono a determinati requi-

siti (ad esempio, un 4 locali con una superficie tra 100 e 150 mq e unprezzo inferiore ai 400.000 euro). Vediamo come bisogna procedere.In primo luogo, selezionata la colonna Superficie, scegliere Formatta-zione condizionale dal gruppo Stili della scheda Home, e attivare Re-gole evidenziazione celle/Tra. Dopo aver inserito 100 e 150 nelle ap-posite caselle, decidere il colore di riempimento, per esempio verdecon testo verde scuro (figura 2).

Per estrapolare solo le abitazioni di tipo 4 locali, evidenziare la co-lonna Tipologia, attivare Testo contenente (sempre da Regole eviden-ziazione celle), digitare 4 locali e poi impostare il colore desiderato(figura 3). Per attivare il vincolo relativo al prezzo della casa, selezio-nare l’omonima colonna, scegliere Regole evidenziazione celle/Mino-re di e scrivere 400 (figura 4). Excel 2007 permette di evidenzare gli appartamenti anche in funzionedel prezzo/mq, scegliendo Formattazione condizionale/Barre dei dati.Nelle celle compariranno delle barre colorate di lunghezza diretta-

1 2

5 6


mente proporzionale al valore contenuto (figura 5). Per personalizza-re tali barre bisogna attivare Formattazione condizionale/Gestisci re-gole. Questa opzione consente di sfruttare tutte le tonalità di coloreofferte da Windows e di decidere se la barra più corta deve corri-spondere al valore massimo/minimo, a un determinato numero, allapercentuale o a una formula (Figura 6).

Per evidenziare gli appartamenti si possono utilizzare anche delleicone colorate come frecce, semafori e bandierine. Ad esempio, peridentificare quelli con il terrazzo scegliete Formattazione condiziona-le/Set di icone/Altre regole e impostate i valori desiderati (Figura 7). Finora sono stati inseriti filtri indipendenti per ogni colonna, deter-minando una colorazione “a macchia di leopardo”. Nel caso si desi-deri evidenziare solo le righe che sottostanno a un insieme di vinco-li (4 locali dotati di una superficie tra 100 e 150 mq, con un prezzosotto i 400.000 euro) bisogna agire come segue. Creare una maschera per l’inserzione dei parametri all’inizio del fo-

glio (come nella riga 2) e aggiungere alla tabella una colonna deno-minata, per esempio, Soddisfa dove inserire la formula che control-la i dat i del l ’appartamento sfruttando dei SE nidif icat i :=SE(A5=$A$2;SE(B5=$B$2;SE(C5>$C$2;SE(C5<$D$2;SE(E5<$E$2;”si”;”no”);”no”);”no”);”no”);”no”). Per velocizzare il lavoro basta in-serire questa formula nella cella G5I e trascinarla su tutte quelle del-la colonna G. Nella nuova colonna apparirà quindi si oppure no. Oraposizionare il cursore sulla prima cella con i dati (A5), attivare For-mattazione condizionale/Nuova regola ed evidenziare Utilizza una for-mula per determinare le celle da formattare. Come formula digitare=$G5=”si” (attenzione a mettere il $ solo prima della lettera G per co-piare la formattazione) e poi riportare il formato (tramite il pulsanteCopia formato, la cui icona è un pennello di colore giallo) su tutto l’e-lenco (Figura 8). Da questo momento in poi basterà inserire i vinco-li per evidenziare solo le righe desiderate. Per concludere, vi segna-liamo che con Excel 2007 è possibile applicare a una cella più rego-le di formattazione.

3 4

7 8

PASSO PER PASSO

Chi cerca trova

E xcel viene utilizzato principalmente per elaborare dati numerici ecreare grafici, anche se offre potenti funzioni per la gestione del testo

come CERCA, che estrapola il contenuto delle celle vicine a quelle conte-nenti determinate stringhe alfanumeriche. Questa funzione, per esempio,può essere utilizzata per la gestione di un listino prezzi o della modulisti-ca legata a un indirizzario. In Excel 2003/2007 sono presenti CERCA, CERCA.VERT e CERCA.ORIZZ.La prima è stata mantenuta per garantire la compatibilità con le preceden-ti versioni del programma, mentre le altre due hanno un modus operandisimile: CERCA.VERT controlla una colonna e CERCA.ORIZZ una riga.

In questo riquadro vi proponiamo un semplice esempio di come si pos-sono sfruttare queste utilissime funzioni. Creeremo un foglio (che salvere-mo nel file Scheda.xls) che ci permetterà di “interrogare” un database didati aziendali (Responsabile, indirizzo, telefono e così via) memorizzatiun’altra cartella di Excel (Indirizzi.xls), nel foglio Nominativi (per evitarvi lafatica di crearlo da zero, abbiamo inserito nel Dvd quello che abbiamo uti-lizzato noi). Potrete poi ispirarvi alle procedure che esamineremo per com-porre moduli o elenchi personalizzati estrapolando dati da altri fogli di Ex-

cel. La prima operazione da compiere consiste nel creare un nuovo foglioelettronico e denominarlo Modulo (figura 1), inserendovi poi le dicitureopportune (nel nostro caso: Ragione Sociale, Nome contatto, Cognomecontatto, Indirizzo, CAP, Città, Provincia e Telefono) nella colonna A. L’o-biettivo è quello di poter digitare un nome nella cella B1 (Ragione Sociale)e ottenere automaticamente le altre informazioni in B2, B3 e così via. Se ilnumero delle aziende non è troppo elevato, si può sfruttare Convalida perfar comparire in B1 un menu a tendina con cui selezionare una ragione so-ciale senza doverla digitare. Dato che Convalida lavora solo con i dati pre-senti sul foglio stesso, per poter implementare questa caratteristica è ne-cessario utilizzare un semplice stratagemma. Aprite poi il file Indirizzi.xls, tornate a Modulo, posizionate il cursore nel-la prima cella di una colonna lontana dalla B1, per esempio IM1, e inseri-tevi la formula =[Indirizzi.xls]Nominativi!A2 (che visualizza in IM1 il con-tenuto della cella A2 del foglio Nominativi presente nel file Indirizzi.xls).Trascinate la formula sia in orizzontale sia verticale fino a visualizzare i da-ti di tutti i nominativi e qualche riga vuota per future aggiunte (figura 2).Per nascondere le colonne con i dati basta evidenziarle, premere il tastodestro del mouse e scegliere Nascondi.

EXCELCOME FARE


1

5 6

2

Prima di continuare, salvate i due documentie richiamate solo Scheda.xls. Se compare ilmessaggio L’aggiornamento automatico deicollegamenti è stato disattivato (figura 3) pre-mete il pulsante Opzioni e inserite il segno dispunta in Attiva il contenuto. In questo modoExcel aggiornerà la colonna degli indirizzi ognivolta che verrà caricato il documento. Con Ex-cel 2003 apparirà invece un box di avverti-mento in cui sarà possibile premere subitoAggiorna.Ora portate il cursore di fianco alla RagioneSociale (B2) e, dal gruppo Strumenti dati del-la scheda Dati, scegliete Convalida dati (con2003 utilizzate l’opzione Dati/Convalida). Dalmenu a tendina Consenti scegliete Elenco ecome origine impostate il range =$IM:$IM (fi-gura 4). Per evitare che l’utente inserisca ma-nualmente un nome errato, andate nella sche-da Messaggio di errore e controllate che nelbox Stile compaia Interruzione (figura 5). Sipuò anche far visualizzare un messaggio chesegnali l’errore d’inserzione. Dopo aver pre-muto OK comparirà il classico tasto con lafreccia rivolta verso il basso che consente diattivare il menu a tendina.A questo punto bisogna chiedere a Excel diestrapolare automaticamente i dati dell’a-zienda selezionata. Dopo aver posizionato ilcursore a fianco di Indirizzo (B3) premete ilpulsante fx presente nella barra di modifica,scegliete come categoria di funzioni Ricercae riferimento e selezionate CERCA.VERT.Dopo questo passaggio comparirà il classi-co box (figura 6) che richiede gli argomentidella funzione, e più precisamente Valore,Matrice_tabella, Indice e Intervallo. ComeValore inserite il testo da cercare, in questo

caso il nome dell’azienda contenuto nellacella B2, e in Matrice_tabella il range checontiene gli indirizzi (IM:IS). Nel riquadro In-dice riportate il numero di colonna da cuiprendere i dati: 2 per il Nome, 3 per il Co-gnome, 4 per l’Indirizzo e così via (in questocaso 4). Nella casella Intervallo è preferibiledigitare FALSO per avere una corrisponden-za esatta (quello che si desidera è il nomecompleto). La formula sarà quindi =CER-CA.VERT($B$1;IM:IS;4;FALSO). Excel cer-cherà il testo nella prima colonna (RagioneSociale) del range IM:IS e, una volta trova-tolo, restituirà il contenuto della cella corri-spondente nella quarta colonna (Indirizzo)della matrice. Dopo aver impostato la funzio-ne CERCA.VERT anche nelle altre celle, ognivolta che sceglierete un nominativo verran-no estrapolate automaticamente tutte le altreinformazioni correlate (figura 7).


7

3 4

Di Ernesto Sagramoso Excel - utesandonatosangiuliano.org · I trucchi e i consigli d’uso che vi...

Documents

Transcript of Di Ernesto Sagramoso Excel - utesandonatosangiuliano.org · I trucchi e i consigli d’uso che vi...