Excel parte seconda - Bocconi, Appunti di Elementi di Informatica

Scarica Excel parte seconda - Bocconi e più Appunti in PDF di Elementi di Informatica solo su Docsity! Pagina di 1 9 COMPUTER SCIENCE FUNZIONI Funzione SOMMA.PIÙ.SE = SOMMA.PIÙ.SE (int_somma; intervallo_criteri1; criteri1; [intervallo_criteri2]; [criteri2] …) • L’argomento “int_somma” non può mai essere omesso • Le celle degli argomenti “intervallo_criteri”, se vuote, vengono ignorate Funzione CONTA.PIÙ.SE = CONTA.PIÙ.SE (intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2] …) • Le celle degli argomenti “intervallo_criteri”, qualora fossero presenti dei riferimenti a celle vuote, la funzione le considera come celle contenti un valore uguale a 0. Funzione MEDIA.PIÙ.SE = MEDIA.SE (int_media; intervallo_criteri1; criteri1; [intervallo_criteri2]; [criteri2] …) • L’argomento “int_media” non può mai essere omesso: se questo argomento è vuoto o contiene solo stringhe di testo, la funzione restituisce il valore di errore #DIV/0! • Le celle degli argomenti “intervallo_criteri”, qualora fossero vuote, la funzione le considera come celle contenti un valore uguale a 0. Funzione RATA = RATA (tasso_int; periodi; val_attuale; [val_futuro]; [tipo]) • “tasso_int” : tasso d’interesse fisso semplice • “periodi” : numero totale di rate da pagare • “val_attuale” (o “pv”) : capitale finanziato • “val_futuro” : l’eventuale capitale da versare dopo l’ultimo pagamento; se omesso, l’argomento assume il valore 0 • “tipo” : “0” per le rate posticipate, “1” per le rate anticipate; se omesso, l’argomento assume il valore 0 → calcolare l’esborso complessivo comporta la moltiplicazione della rata costante per il numero totale delle rate Funzione VAL.FUT = VAL.FUT (tasso_int; periodi; pagam; [val_attuale]; [tipo]) La funzione permette di calcolare il valore futuro di un investimento che prevede una serie di versamenti periodici e un tasso d’interesse costante. • “tasso_int” : tasso d’interesse semplice • “periodi” : numero totale dei versamenti da effettuare • “pagam” : importo costante dei versamenti • “val_attuale” : eventuale versamento effettuato in aggiunta ai versamenti periodici • “tipo” : “0” se i versamenti avvengono alla fine di ogni periodo, “1” se i versamenti avvengono all’inizio di ogni periodo; se omesso, l’argomento assume il valore 0 → calcolare l’esborso complessivo comporta la moltiplicazione della rata costante per il numero totale delle rate almeno uno dei due argomenti deve essere presente, mentre l’altro può essere omesso: in mancanza di entrambi, la funzione restituisce il valore “0” → stessa unità di misura • non solo i criteri di testo ma anche tutti quelli comprendenti simboli logici o matematici devono essere racchiusi tra virgolette (1) • se il criterio corrisponde ad un numero o si tratta di un riferimento di cella, non comprendendo simboli logici o matematici, le virgolette non devono essere utilizzate (1) → i criteri che prevedono l’utilizzo di un operatore di confronto insieme a un riferimento di cella che contiene una data non funzionano: è necessario insieme direttamente la data in uno dei formati disponibile, ad esempio “>10/06/19” • gli argomenti facoltativi vanno sempre inseriti in coppia: è possibile avere fino a 127 diverse coppie “intervallo_criteri” - “criteri” • ogni argomento “intervallo_criteri” aggiuntivo deve avere le stesse dimensioni dell’argomento “intervallo_criteri1” Pagina di 2 9 Funzione CERCA.VERT = CERCA.VERT (valore; matrice_tabella; indice; [intervallo] ) • “valore” contiene il valore da cercare nella prima colonna della tabella indicata nell’argomento “matrice_tabella” • “indice” indica il numero della colonna dalla quale deve essere restituito il valore corrispondente • “intervallo” contiene un valore logico: FALSO: la funzione cerca il valore specificato e se non trova una corrispondenza esatta restituisce il valore di errore #N/D! (se nella prima colonna della tabella sono presenti due o più valori che corrispondono al valore cercato, la funzione utilizza il primo valore trovato) VERO: la funzione, se non trova una corrispondenza esatta, prende il valore inferiore più vicino tra quelli trovati e restituisce il valore corrispondente a quest’ultimo (in questo caso, è necessario che i valori della prima colonna siano ordinati in maniera crescente) Funzione CONFRONTA = CONFRONTA (valore; matrice; [corrisp] ) La funzione cerca un valore specificato in un intervallo e ne restituisce la posizione. • “valore” contiene il valore da cercare nell’intervallo di celle indicato nell’argomento “matrice” • “corrisp” indica in che modo la funzione deve cercare il valore che ci interessa: - “0” : la funzione cerca una corrispondenza esatta, altrimenti restituisce il valore di errore #N/D (i valori della matrice possono essere disposti in qualsiasi ordine) - “1” : la funzione restituisce la posizione del valore più grande che è minore o uguale al valore specificato nell’argomento “valore” (l’intervallo deve essere ordinato in maniera crescente) - “-1” : la funzione restituisce la posizione del valore più piccolo che è maggiore o uguale al valore specificato nell’argomento “valore” (l’intervallo deve essere ordinato in maniera decrescente) Funzione RANGO.UG = RANGO.UG (num; rif; [ordine]) La funzione restituisce la posizione di un numero in un elenco di numeri. • “num” contiene il numero di cui si vuole trovare la posizione • “rif” contiene il riferimento ad un intervallo di celle che contengono un elenco di numeri • “ordine” è un numero che determina come impostare la classificazione - “0” o omesso : la posizione di “num” viene calcolata in base ad un ordinamento decrescente dei numeri presenti nell’elenco - ≠“0” : la posizione di “num” viene calcolata in base ad un ordinamento crescente dei numeri presenti nell’elenco Quando nell’intervallo di numeri considerati sono presenti due numeri uguali, la funzione attribuisce ad entrambi la stessa posizione, ma salta la posizione successiva. Funzione SE.ERRORE = SE.ERRORE (valore; valore_se_errore) I valori che vengono considerati errori sono #N/D, #VALORE!, #RIF, #DIV/0!, #NUM!, #NOME?, #NULLO! Funzione SINISTRA = SINISTRA (testo; [num_caratt]) Funzione DESTRA = DESTRA (testo; [num_caratt]) Funzione STRINGA.ESTRAI = STRINGA.ESTRAI (testo; inizio; num_caratt) La funzione estrae un determinato numero di caratteri da una stringa di testo a partire da una posizione specificata: l’argomento “inizio” specifica la posizione del primo carattere che deve essere estratto dal testo a partire da sinistra. se l’argomento “num_caratteri” viene omesso, viene considerato automaticamente il valore predefinito 1; qualora fosse inserito un numero negativo, le formule restituiscono l’errore #VALORE! Pagina di 5 9 Funzione DATA.DIFF = DATA.DIFF (data_iniziale; data_finale; [unità] ) La funzione calcola la differenza in giorni, mesi o anni tra due date • l’argomento “unità” contiene un valore che specifica quale unità di misura deve essere usata per descrivere la distanza tra le due date: “d” per giorni, “m” per mesi, “y” per anni • per calcolare la distanza tra due date espressa in giorni è sempre meglio utilizzare la funzione GIORNI o la semplice differenza tra le due date: infatti, la funzione DATA.DIFF non è in grado di restituire un risultato negativo (restituisce un errore), quindi non può essere utilizzata, per esempio, per calcolare quanti giorni mancano ad una certa data. ⚠ DIFFERENZA TRA DATE IN GIORNI la funzione GIORNI calcola la distanza in giorni senza considerare eventuali parti decimali, che vengono prese in considerazione qualora si utilizzasse la sottrazione tra le due date COME UTILIZZARE LE DATE IN UN TEST : • usare i riferimenti di cella • usare i numeri seriali • per impostare una data di controllo fissa, usare la funzione DATA • utilizzare una funzione che restituisca un seriale (la funzione OGGI) ⚠ qualunque testo risulta sempre più grande di un numero CONVALIDA DATI in Dati 1) Impostazioni : scelta del criterio di convalida 2) Messaggio di input 3) Messaggio di errore • “interruzione” consente di inserire il valore errato • “avviso”/“informazione” consente l’inserimento dopo aver avvisato l’utente Nel comando “Convalida dati” si selezionano le celle interessate, ma la regola viene impostata solo sulla cella attiva. Il comando “Convalida dati” non tiene conto dei dati già inseriti nelle celle e nemmeno tiene conto delle variazioni effettuate all’interno dei valori presi come origine dell’elenco. ⚠ non vengono segnalati i valori sbagliati impostati prima della convalida dati o prima dell’aggiornamento dell’elenco : per questo si usa l’opzione “Cerchia dati non validi” Pagina di 6 9 ANALISI DI SIMULAZIONE Sulla base del modello di valutazione e dei valori previsti per le variabili, l’analisi di simulazione consente di valutare direttamente i diversi risultati a cui si può giungere quando cambiano i valori di una o più variabili. RICERCA OBIETTIVO nella scheda Dati, comando “Analisi di simulazione” Ricerca Obiettivo è uno strumento che permette di trovare per quale valore di una determinata variabile il risultato di una formula assume un valore specifico. • nella casella “Imposta la cella” inserire il riferimento alla cella che contiene la formula alla quale si vuole imporre un determinato risultato • nella casella “Al valore” impostare il valore finale che si vuole ottenere • nella casella “Cambiando la cella” impostare inserire la cella che contiene la variabile da cambiare • si visualizza la finestra di dialogo “Stato ricerca obiettivo”, dove viene confermato il fatto che è stata trovata una soluzione ⚠ la cella il cui riferimento viene inserito nella casella “Imposta la cella” deve contenere una formula o una funzione; viceversa, la cella impostata nella casella “Cambiando la cella” deve contenere un valore numerico (non una funzione o una formula) SCENARI nella scheda Dati, comando “Analisi di simulazione” Uno scenario può essere definito come una delle possibili varianti di un modello in cui i risultati parziali e finali sono determinati da una serie di possibili valori assegnati alle variabili. • fare clic su “Aggiungi”; si apre la finestra di dialogo “Aggiungi scenario” : nella casella “Nome” digitare il nome dello scenario e inserire nella casella “Celle variabili” i riferimenti delle celle (massimo 32 celle variabili) ⚠ le celle variabili devono essere delle costanti, non devono cioè contenere formule o funzioni • eliminare qualsiasi commento e cliccare su OK dopo aver lasciato inalterate le due opzioni di protezione • nella finestra “Valori scenario” si inseriscono i valori ipotizzati per le diverse celle Dalla finestra “Gestione scenario” è possibile aggiungere un nuovo scenario, eliminare o modificare uno scenario esistente, unire diversi scenari tra loro. • con il pulsante “Mostra”, si mostrano i dati di uno scenario direttamente nel foglio di lavoro, comportando la sostituzione dei valori correnti con quelli dello scenario selezionato • il pulsante “Riepilogo” visualizza in maniera comparativa gli scenari creati : nella finestra “Riepilogo scenari” è necessario inserire le “Celle risultato”, ossia le celle più adatte a mostrare l’impatto delle ipotesi dei diversi scenari sui risultati del modello adottato Il riepilogo produce un’istantanea dei valori presenti nel foglio di lavoro e nei diversi scenari, ma non è uno strumento dinamico: per aggiornare un riepilogo è necessario eliminarlo e generarne uno nuovo. ASSEGNARE UN NOME ALLE CELLE Per assegnare un nome è sufficiente selezionare la cella o l’intervallo sul foglio di lavoro, quindi fare clic nella “Casella nome” e digitare il nome. • il nome assegnato a una cella non può contenere spazi: per separare le parole si usa il carattere underscore ( _ ) • una volta assegnati i nomi, questi saranno visibili nell’elenco a discesa che si apre facendo clic sulla freccia posta a destra della “Caselle Nome” • il nome assegnato alle celle o a un intervallo corrisponde a un riferimento assoluto, quindi il nome può essere utilizzato al posto dei riferimenti di cella direttamente negli argomenti delle funzioni o nelle formule Per modificare o eliminare un nome assegnato è possibile utilizzare il comando “Gestione nomi” presente nella scheda “Formule”. Pagina di 7 9 TABELLA PIVOT in Inserisci • attraverso l’opzione “Tabella Pivot” si seleziona l’intera tabella, con intestazioni comprese • raggruppare i valori delle etichette di riga e di colonna : → clic destro su una delle etichette, “Raggruppa” → selezionare le celle raggruppare, clic destro, “Raggruppa” → “gruppo da selezione” nella scheda Analizza - per eliminare il raggruppamento : “Separa” • personalizzare manualmente l’intestazione nelle etichette • filtri sulle etichette di riga e di colonna • ordinare : → ordinamento automatico dei valori nelle etichette di riga e di colonna con un clic destro su una delle celle delle etichette → per avere un ordinamento non necessariamente crescente o decrescente: - clic destro sulla cella che interessa, “sposta” (all’inizio, alla fine, su, giù) - selezionare la casella e trascinarla nella posizione desiderata con il cursore • formattazione dei valori contenuti nel campo “Valori” : → “impostazioni campo” - “riepiloga per” : per cambiare il tipo di operazione - cambiare il nome della tabella - “formato numero" → una volta che il campo viene eliminato, viene persa la formattazione • nella scheda “Analizza” : → “Cambia origine dati” → “Aggiorna” : la tabella d’origine non è collegata alla tabella pivot, se non quando viene aggiornata manualmente GRAFICO PIVOT • il grafico è completamente alla tabella : i cambiamenti effettuati sul grafico vengono effettuati anche nella tabella e viceversa ⚠ è importante aggiungere il grafico dalla scheda “Analizza” : infatti, se si utilizzasse la scheda “Inserisci” → “Grafico pivot”, verrebbe creata una seconda tabella pivot alla base del nuovo grafico PROTEZIONE FOGLIO in Revisione Proteggere le celle di un foglio di lavoro Questa opzione consente di definire una protezione a livello di singole celle o intervalli di un foglio di lavoro, o per l’intero foglio di lavoro. L’impostazione “Bloccata”, nella finestra di dialogo “Formato celle”, è predefinita: non sarà possibile modificarne o eliminarne il contenuto. L’opzione “Nascosta” permette di nascondere eventuali formule contenute nella cella. Una volta definite correttamente le proprietà delle celle, attivare il comando “Proteggi foglio”, nella scheda “Revisione”, che contiene una serie di opzioni che permettono di controllare le impostazioni di protezione in maniera molto precisa (variazione del formato delle celle, eliminazione o inserimento di righe e colonne, ordinamento della tabella, uso di filtri). La casella “Password per rimuovere la protezione” consente di impostare una password per la rimozione della protezione. In caso contrario è sufficiente selezionare il comando “Rimuovi Protezione foglio”, che compare sulla barra multifunzione. Il comando “Proteggi foglio” funziona solo per il foglio corrente.