Definizione e creazione di una formula

In questa lezione, ti presentiamo le regole di base per creare formule e utilizzare le funzioni. Sentiamo che uno dei modi migliori per imparare è attraverso la pratica, quindi forniamo diversi esempi e li spieghiamo in dettaglio. Gli argomenti trattati includeranno:

NAVIGAZIONE SCOLASTICA

1. Perché hai bisogno di formule e funzioni?
2. Definizione e creazione di una formula
3. Riferimento relativo e assoluto della cella e formattazione
4. Funzioni utili da conoscere
5. Ricerche, grafici, statistiche e tabelle pivot

• righe e colonne
• esempio di funzione matematica: SUM ()
• operatori
• la precedenza degli operatori
• esempio funzione finanziaria: PMT (), pagamento del prestito
• utilizzando una funzione "stringa" ("stringa" è una scorciatoia per "stringa di testo") all'interno di una formula e funzioni di nidificazione

Le formule sono una combinazione di "funzioni", "operatori" e "operandi". Prima di scrivere alcune formule, dobbiamo creare una funzione ma prima di poter creare una funzione, dobbiamo prima capire la notazione di righe e colonne.

Righe e colonne

Per capire come scrivere formule e funzioni, è necessario conoscere le righe e le colonne.

Le righe vengono eseguite orizzontalmente e le colonne vengono eseguite verticalmente. Per ricordare quale è, pensate a una colonna che sorregge un tetto: le colonne vanno su e giù e quindi le file vanno da sinistra a destra.

Le colonne sono contrassegnate da lettere; righe per numero. La prima cella del foglio di calcolo è A1, ovvero la colonna A, riga 1. Le colonne sono etichettate come A-Z. Quando l'alfabeto si esaurisce, Excel mette un'altra lettera in primo piano: AA, AB, AC ... AZ, BA, BC, BC, ecc.

Esempio: Funzione Sum ()

Ora dimostriamo come usare una funzione.

Le funzioni vengono utilizzate digitandole direttamente o utilizzando la procedura guidata della funzione. La procedura guidata si apre quando scegli una funzione dal menu "Formule" dalla "Libreria funzioni". In caso contrario, puoi digitare = in una cella e un pratico menu a discesa ti permetterà di scegliere una funzione.

La procedura guidata ti dice quali argomenti devi fornire per ciascuna funzione. Fornisce inoltre un collegamento alle istruzioni online se hai bisogno di aiuto per capire cosa fa la funzione e come usarla. Ad esempio, se si digita = sum in una cella, la procedura guidata in linea mostra quali argomenti sono necessari per la funzione SUM.

Quando si digita una funzione, la procedura guidata è in linea o direttamente alle dita. Quando selezioni una funzione dal menu "Formule", la procedura guidata è una finestra pop-up. Ecco la procedura guidata di pop-up per la funzione SUM ().

Per la nostra prima funzione, usiamo SUM (), che aggiunge una lista di numeri.

Supponiamo di avere questo foglio di lavoro per contenere i piani per il budget della vacanza della tua famiglia:

Per calcolare i costi totali potresti scrivere = b2 + b3 + b4 + b5 ma è più facile usare la funzione SUM ().

In Excel, cerca il simbolo Σ nell'angolo in alto a sinistra della schermata di Excel per trovare il pulsante Somma automatica (i matematici usano la lettera greca Σ per aggiungere una serie di numeri).

Se il cursore si trova sotto i numeri del budget familiare, Excel è abbastanza intelligente da sapere che vuoi sommare l'elenco di numeri sopra il punto in cui hai posizionato il cursore, in modo che evidenzi i numeri.

Premere "invio" per accettare l'intervallo selezionato da Excel o utilizzare il cursore per modificare le celle selezionate.

Se si guarda a ciò che Excel ha inserito nel foglio di calcolo, si può vedere che ha scritto questa funzione:

In questa formula, Excel somma i numeri da B2 a B9. Nota, abbiamo lasciato un po 'di spazio sotto la riga 5 in modo da poter aggiungere al budget per le vacanze di famiglia - il costo salirà sicuramente come l'elenco dei bambini di ciò che vogliono fare e dove vogliono andare cresce più a lungo!

Le funzioni matematiche non funzionano con le lettere, quindi se inserisci delle lettere nella colonna il risultato viene visualizzato come "#NOME?" Come mostrato di seguito.

#NOME? indica che c'è un qualche tipo di errore. Potrebbe essere un numero qualsiasi di cose, tra cui:

• riferimento alle cellule cattive
• usando le lettere nelle funzioni matematiche
• omettendo gli argomenti richiesti
• nome della funzione di ortografia errata
• operazioni matematiche illegali come la divisione per 0

Il modo più semplice per selezionare gli argomenti in un calcolo è utilizzare il mouse. È possibile aggiungere o rimuovere dall'elenco di argomenti alla funzione ingrandendo o riducendo la casella che Excel disegna quando si sposta il mouse o si fa clic in un'altra cella.

Abbiamo cliccato in cima al quadrato disegnato da Excel per prendere "biglietti aerei" fuori dal budget. Puoi vedere il simbolo del mirino che puoi disegnare per ingrandire o ridurre l'intervallo selezionato.

Premere "invio" per confermare i risultati.

Operatori di calcolo

Esistono due tipi di operatori: matematica e confronto.

Operatore di matematica	Definizione
+	aggiunta
-	sottrazione o negazione, ad es. 6 * -1 = -6
*	moltiplicazione
/	divisione
%	per cento
^	esponente, ad es. 24 = 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Ci sono altri operatori non correlati alla matematica come "&" che significa concatenare (unire end-to-end) due stringhe. Ad esempio, = "Excel" e "è divertente" è uguale a "Excel è divertente".

Ora guardiamo gli operatori di confronto.

Operatore di confronto	Definizione
=	è uguale, ad es. 2 = 4 o "b" = "b"
>	maggiore di, ad es., 4> 2 o "b"> "a"
<	inferiore a, ad es. 2 < 4 or “a” < “b”
> =	maggiore o uguale a - un altro modo di pensare a questo è> = significa o > o =.
<=	minore o uguale a.
	non uguale a, ad es., 46

Come puoi vedere sopra, gli operatori di confronto lavorano con numeri e testo.

Nota, se inserisci = "a"> "b" in una cella, dirà "FALSE" poiché "a" non è maggiore di "b". "B" viene dopo "a" nell'alfabeto, quindi "a" > "B" o "B"> "a".

Precedenza dell'ordine dell'operatore

La precedenza dell'ordine è un'idea dalla matematica. Excel deve seguire le stesse regole della matematica. Questo argomento è più complicato, quindi fai un respiro e tuffiamoci dentro.

Ordine precedente indica l'ordine in cui il computer calcola la risposta. Come abbiamo spiegato nella Lezione 1, l'area di un cerchio è πr², che è lo stesso di π * r * r. È non (Πr)².

Quindi devi capire la precedenza dell'ordine quando scrivi una formula.

In generale, puoi dire questo:

1. Excel valuta prima gli elementi tra parentesi lavorando al rovescio.
2. Utilizza quindi le regole di precedenza sull'ordine della matematica.
3. Quando due elementi hanno la stessa precedenza, Excel funziona da sinistra a destra.

La precedenza degli operatori matematici è mostrata sotto, in ordine decrescente.

( e )	Quando vengono utilizzate le parentesi, sovrascrivono le normali regole di precedenza. Ciò significa che Excel eseguirà questo calcolo per primo. Lo spieghiamo più avanti.
-	Negazione, ad es. -1. Questo è lo stesso di moltiplicare un numero per -1. -4 = 4 * (-1)
%	Percentuale, significa moltiplicare per 100. Es., 0,003 = 0,3%.
^	Esponente, ad es. 10 ^ 2 = 100
* e /	Moltiplicare e dividere. Come possono due operatori avere la stessa precedenza? Significa semplicemente che se una formula ha altri due operatori con la stessa precedenza, il calcolo viene eseguito da sinistra a destra.
+ e -	Addizione e sottrazione.

Esistono altre regole di precedenza relative alle stringhe e agli operatori di riferimento. Per il momento, ci limiteremo a ciò che abbiamo appena trattato. Ora, diamo un'occhiata ad alcuni esempi.

Esempio: calcolo dell'area di un cerchio

L'area di un cerchio è= PI () * raggio ^ 2.

Guardando la tabella sopra vediamo che gli esponenti vengono prima della moltiplicazione. Quindi il computer prima calcola il raggio ^ 2 e poi moltiplica quel risultato per Pi.

Esempio: calcolo di un aumento in salario

Diciamo che il tuo capo decide che stai facendo un ottimo lavoro e lui o lei ti darà un aumento del 10%! Come calcoleresti il ​​tuo nuovo stipendio?

Innanzitutto, ricorda che la moltiplicazione viene prima dell'aggiunta.

È = salario + stipendio * 10% o è = salario + (salario * 10%)?

Supponiamo che il tuo stipendio sia $ 100. Con un aumento del 10%, il nuovo stipendio sarà:

= 100 + 100 * 10% = 100 + 10 = 110

Puoi anche scriverlo in questo modo:

= 100 + (100 * 10%) = 100 + 10 = 110

Nel secondo caso, abbiamo reso esplicito l'ordine di precedenza usando le parentesi. Ricorda che le parentesi vengono valutate prima di ogni altra operazione.

A proposito, il modo più semplice per scrivere è = salario * 110%

Le parentesi possono essere annidate l'una dentro l'altra. Quindi, quando scriviamo (3 + (4 * 2)), lavorando dall'interno all'esterno, prima calcoliamo 4 * 2 = 8, quindi aggiungiamo 3 + 8 per ottenere 11.

Alcuni altri esempi

Ecco un altro esempio: = 4 * 3 / 2. Qual è la risposta?

Vediamo dalle regole nella tabella sopra che * e / hanno uguale precedenza. Quindi Excel funziona da sinistra a destra, 4 * 3 = 12 prima, quindi divide quello per 2 per ottenere 6.

Ancora una volta potresti renderlo esplicito scrivendo = (4 * 3) / 2

Che dire = 4 + 3 * 2?

Il computer vede entrambi gli operatori * e +. Quindi, seguendo le regole di precedenza (la moltiplicazione viene prima dell'aggiunta), calcola prima 3 * 2 = 6, quindi aggiunge 4 per ottenere 10.

Se si desidera modificare l'ordine di precedenza, scrivere = (4 + 3) * 2 = 14.

Che dire di questo = -1 ^ 3?

Quindi la risposta è -3 perché il computer ha calcolato = (-1) ^ 3 = -1 * -1 * -1 = -1.

Ricorda che i tempi negativi negativi sono positivi e quelli negativi negativi sono negativi. Puoi vedere questo in questo modo (-1 * -1) * -1 = 1 * -1 = -1.

Quindi ci sono alcuni esempi di ordine matematico e di precedenza, speriamo che aiuti a chiarire alcune cose su come Excel esegue calcoli (e questo è probabilmente abbastanza matematica da durare una vita per alcuni di voi).

Esempio: pagamento del prestito di funzione (PMT)

Diamo un'occhiata a un esempio per calcolare un pagamento di prestito.

Inizia creando un nuovo foglio di lavoro.

Formatta i numeri con i simboli del dollaro e usa zero cifre decimali dato che al momento non siamo interessati ai centesimi perché non contano molto quando parli di dollari (nel prossimo capitolo esploreremo come formattare i numeri in dettaglio). Ad esempio, per formattare il tasso di interesse, fai clic con il tasto destro del mouse sulla cella e fai clic su "Formatta celle". Seleziona la percentuale e utilizza 2 cifre decimali.

Allo stesso modo, formattare le altre celle per "valuta" anziché percentuale e selezionare "numero" per il termine del prestito.

Ora abbiamo:

Aggiungere la funzione SUM () alle spese mensili "totali".

Notare la mutuo la cella non è inclusa nel totale. Excel non sa che vuoi includere quel numero, poiché non c'è alcun valore lì. Quindi fai attenzione ad estendere la funzione SUM () verso l'alto usando il cursore o digitando E2 dove dice E3 per includere l'ipoteca nella somma.

Metti il ​​cursore nella cella di pagamento (B4).

Nel menu Formule selezionare il menu a discesa "Finanziario" e quindi selezionare la funzione PMT. Viene visualizzata la procedura guidata:

Utilizzare il cursore per selezionare "tasso", "nper" (termine del prestito), "Pv" ("valore attuale" o importo del prestito). Si noti che è necessario dividere il tasso di interesse per 12 poiché gli interessi sono calcolati mensilmente. Inoltre è necessario moltiplicare il termine del prestito in anni per 12 per ottenere il termine del prestito in mesi. Premere "OK" per salvare il risultato nel foglio di calcolo.

Si noti che il pagamento viene visualizzato come numero negativo: -1013.37062. Per renderlo positivo e aggiungerlo alle spese mensili, puntare alla cella del mutuo (E2). Digitare "= -" quindi utilizzare il cursore per selezionare il campo di pagamento. La formula risultante è = -B4.

Ora il foglio di calcolo è simile al seguente:

Le tue spese mensili sono $ 1,863 - Ouch!

Esempio: funzione di testo

Qui dimostriamo come utilizzare le funzioni all'interno di una formula e funzioni di testo.

Supponiamo di avere una lista di studenti come mostrato di seguito. Il nome e il cognome si trovano in un campo separato da una virgola. Abbiamo bisogno di mettere i nomi ultimi e firmati in celle separate. Come facciamo questo?

Per affrontare questo problema è necessario utilizzare un algoritmo, ad esempio una procedura dettagliata per farlo.

Ad esempio, guarda "Washington, George". La procedura per dividerlo in due parole sarebbe:

1. Calcola la lunghezza della stringa.
2. Trova la posizione della virgola (questo mostra dove finisce una parola e inizia l'altra).
3. Copia il lato sinistro della stringa fino alla virgola.
4. Copia il lato destro della stringa dalla virgola alla fine.

Discutiamo come fare questo con "George Washington" passo dopo passo in Excel.

1. Calcola la lunghezza della stringa con la funzione = LEN (A3) - il risultato è 18.
2. Ora trova la posizione della virgola inserendo questa funzione = TROVA (",", A3 ") - il risultato è 11.
3. Ora prendi il lato sinistro della stringa fino alla virgola e crea questa formula annidata utilizzando il risultato del passaggio 1: = SINISTRA (A3, TROVA (",", A3) -1). Nota, dobbiamo sottrarre 1 dalla lunghezza perché FIND fornisce la posizione della virgola.

Ecco come appare quando tutte le funzioni sono raggruppate in una formula. Nella cella B3, puoi vedere questa formula prende tutte le informazioni dalla cella A3 e inserisce "Washington" in essa.

Quindi abbiamo "Washington", ora dobbiamo ottenere "George". Come facciamo??

Nota che avremmo potuto salvare il risultato del passaggio 1 in una cella da solo, ad esempio B6, quindi scrivere una formula più semplice = SINISTRA (A3, B6-1). Ma questo utilizza una cella per il passo intermittente.

1. Ricorda la posizione della virgola o calcola di nuovo.
2. Calcola la lunghezza della stringa.
3. Contare i caratteri dalla fine della stringa alla virgola.

Prendi il numero di personaggi dal Passo 3 e sottrai uno per omettere la virgola e lo spazio.

Facciamo questo passo dopo passo.

1. Dall'alto, questo è = TROVA (",", A3 ")
2. La lunghezza della stringa è = LEN (A3)
3. È necessario utilizzare alcuni calcoli per trovare il numero di caratteri da prendere: = LEN (A3) - TROVA (",", A3) - 1
4. Il lato destro della stringa che vogliamo è = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1)

Il tuo foglio di calcolo dovrebbe essere simile allo screenshot qui sotto. Abbiamo copiato le formule come testo nella parte inferiore del foglio di calcolo per semplificare la lettura e la visualizzazione.

Quella era un po 'difficile, ma hai solo bisogno di scrivere queste formule una volta.

In arrivo…

Questo conclude la nostra lezione per oggi. Dovresti avere una comprensione abbastanza solida ora delle formule e delle funzioni, delle righe e delle colonne e del modo in cui tutto questo può essere impiegato attraverso diversi esempi definiti.

Prossimamente nella lezione 3, discuteremo del riferimento e della formattazione delle celle, nonché dello spostamento e della copia delle formule in modo da non dover riscrivere ogni formula più e più volte!