Nel processo di risoluzione di vari tipi di compiti, sia educativi che pratici, gli utenti si rivolgono spesso a Excel.

I fogli di calcolo consentono di analizzare dati, creare diagrammi e grafici ed eseguire una varietà di calcoli. Un'operazione comune è il calcolo delle percentuali. La capacità di eseguire con competenza i calcoli necessari è un'abilità utile che trova applicazione con successo in quasi tutti gli ambiti della vita. Quali tecniche ti aiuteranno a calcolare le percentuali utilizzando i fogli di calcolo Excel?

Come calcolare le percentuali in Excel: la formula di calcolo di base

Prima di procedere al calcolo delle percentuali, è necessario definire la terminologia. Il termine "percentuale" indica il numero di azioni su tutte le 100 azioni del totale. La definizione matematica di percentuale è una frazione, il cui numeratore determina il numero desiderato di parti e il denominatore è il totale. Il risultato viene moltiplicato per 100 (perché il numero intero è 100%). Lavorando con un foglio di calcolo, la formula per determinare la percentuale è la seguente:

Parte/Intero = Percentuale

Si differenzia dalla solita interpretazione in matematica solo per l'assenza di ulteriori moltiplicazioni per 100. Le proprietà dei campi della tabella ti aiuteranno a ottenere il formato del valore necessario: basta attivare il formato della cella Percentuale.

Esempio 1

Ecco una serie di dati inseriti, ad esempio, nella colonna D (D2, D3, D4, D5, ...). È necessario calcolare il 5% di ciascun valore.

• Attiva la cella accanto al primo valore (o qualsiasi altro): conterrà il risultato dei calcoli.
• Nella cella E2, scrivi l'espressione "=D2/100*5" o "=D2*5%".
• Premere Invio.
• "Trascina" la cella E2 sul numero di righe richiesto. Grazie al token di completamento automatico, la formula sopra calcolerà anche il resto dei valori.

Esempio 2

Hai 2 colonne di valori davanti a te, ad esempio torte vendute (D2, D3, D4, D5, ...) e il numero totale di prodotti da forno (E2, E3, E4, E5, ... ) di ogni tipo. È necessario determinare quale parte del prodotto viene venduta.

• Nella cella in cui verrà calcolato il risultato (ad esempio F), scrivere l'espressione "=D2/E2".
• Premi Invio e "allunga" la cella per il numero di righe richiesto. L'utilizzo dell'indicatore di riempimento automatico ti consentirà di applicare questa formula a tutte le celle successive e di eseguire i calcoli corretti.
• Per convertire il risultato in formato percentuale, selezionare le celle richieste e utilizzare il comando Stile percentuale. Per attivare quest'ultimo, puoi fare clic con il tasto destro e selezionare la voce "Formato celle" - "Percentuale" nell'elenco che appare. In tal modo, si specifica il numero desiderato di cifre decimali. Oppure vai alla sezione "Home" - "Numero" e seleziona la visualizzazione "Percentuale".

Come calcolare le percentuali in Excel - percentuale dell'importo

Per calcolare la quota di ciascuna parte rispetto all'importo totale, utilizzare l'espressione "=A2/$A$10", dove A2 è il valore dell'interesse, l'importo totale è indicato nella cella A10. Cosa succede se la posizione che ti interessa appare nella tabella più volte? In questo caso, utilizzare la funzione SOMMA.SE (SOMMA.SE) con i seguenti parametri:

SOMMA.SE(intervallo;criteri;intervallo_somma)/totale

SOMMA.SE(intervallo, criterio, intervallo_somma)/somma totale

• Passare alla cella in cui verrà ottenuto il risultato.
• Scrivi l'espressione "=SOMMA.SE(DO2:DO10;FA1;RE2:RE10)/$D$14" (o =SOMMA.SE (DO2:DO10;FA1;RE2:RE10)/$RE$14), dove

C2:C10, D2:D10 - intervalli di valori all'interno dei quali si verificano i calcoli,

F1 - una cella in cui è indicata la caratteristica studiata,

D14 è la cella in cui viene calcolato l'importo.

Come calcolare le percentuali in Excel - Variazione percentuale

La necessità di tali calcoli sorge spesso nel corso della valutazione dell'aumento o della diminuzione delle prestazioni. Quindi, volumi di vendita per categorie merceologiche per il 2015. inseriti nella colonna D, analoghi dati per il 2016. - nella colonna E. È necessario determinare in quale percentuale il volume delle vendite è aumentato o diminuito.

• Nella cella F2, inserisci la formula "=(E2-D2)/D2".
• Converti i dati della cella in formato percentuale.
• Per calcolare l'aumento o la diminuzione per le restanti categorie (celle), allungare F2 per il numero richiesto di righe.
• Valuta il risultato. Se il valore è positivo si ha un incremento, se negativo si ha un decremento.

Excel contiene potenti strumenti di calcolo delle formule. Una formula è una raccolta di operatori matematici, numeri, riferimenti e funzioni disposti in un certo ordine. Il risultato del calcolo viene inserito nella cella contenente la formula. Quando crei una formula, ricorda che deve iniziare con il segno di uguale "=", quindi si trovano gli elementi calcolati (operandi) tra i quali sono presenti i segni delle operazioni eseguite (operatori).

Gli operandi possono essere valori costanti (costanti numeriche o di testo), riferimenti di cella o intervalli di celle, nomi, valori booleani (come VERO o FALSO) e matrici. Una costante è un numero o un testo che viene inserito direttamente in una cella, ad esempio il testo "Moskovskiye Izvestiya".

La formula può anche includere funzioni integrate che eseguono calcoli standard. Excel ha oltre 200 funzioni integrate.

Per impostazione predefinita, il foglio di calcolo calcola le formule durante la digitazione e le ricalcola ogni volta che i dati di origine in esse contenuti cambiano.

Operatori utilizzati in Microsoft Excel

Excel utilizza i seguenti operatori:

· Operatori aritmetici - utilizzati quando si lavora con i numeri. Il risultato di un'operazione aritmetica è sempre un numero.

· Gli operatori di confronto vengono utilizzati per confrontare due numeri. Come risultato dell'operazione di confronto, si ottiene un valore logico: vero o falso.

· Operatore di testo - utilizzato per indicare l'operazione di combinazione di diverse sequenze di caratteri in un'unica sequenza di caratteri.

Lo scopo degli operatori è riportato in Tabella. 2.2.

Tabella 2.2

Scopo degli operatori utilizzati in Excel

Operatore aritmetico	Assegnazione operatore
+ (segno più)	Aggiunta
- (segno meno)	Sottrazione (o meno unario, ad esempio -1)
/ (barra)
* (asterisco)	Moltiplicazione
% (segno di percentuale)
^ (berretto)	Esponenziamento
Operatori di confronto
= (segno di uguale)
> (segno maggiore)
< (знак меньше)
>= (segno maggiore di e segno uguale)	Più o uguale
<= (знак меньше и знак равенства)	Minore o uguale
<>(minore di segno e maggiore di segno)
Operatore di testo
& (e commerciale)	Unione di due stringhe di testo in una
Operatori di indirizzo
: (due punti)
; (punto e virgola)

Operatori aritmetici

Gli operatori aritmetici sono i più utilizzati. Forniscono addizioni, sottrazioni, moltiplicazioni, divisioni, elevazione a potenza, trovando una percentuale dei dati riportati nelle celle del foglio di calcolo. Esempi di utilizzo di operatori aritmetici sono riportati in Tabella. 2.3. Si presume che le celle A1, A2 e A3 contengano rispettivamente i numeri 4, 5 e 10.

Tabella 2.3

Esempi di utilizzo di operatori aritmetici

Excel esegue i calcoli nelle formule da sinistra a destra e rispetta la precedenza delle operazioni aritmetiche accettate in matematica. Si eseguono prima le operazioni di elevazione a potenza, poi la moltiplicazione e la divisione, e infine l'addizione e la sottrazione. Le parentesi vengono utilizzate per modificare l'ordine in cui vengono eseguite le operazioni.

Le parentesi devono essere accoppiate, gli spazi prima o dopo le parentesi non sono consentiti, ad esempio:

15 è una costante numerica,

"+" e "/" sono operatori di addizione e divisione.

Le operazioni tra parentesi vengono eseguite per prime. Ad esempio, nella formula = (A1 + A2 + A3) / 3, viene prima calcolata la somma dei numeri contenuti nelle celle A1, A2 e A3, poi la somma trovata viene divisa per 3, mentre nella formula = A1 + A2 + A3 / 3 per 3 divide solo l'ultimo termine, non l'intero importo. È possibile inserire altre parentesi all'interno di parentesi, operazione denominata nidificazione delle parentesi.

Immissione di una formula in una cella

Per inserire una formula in una cella, selezionala e inserisci il segno "=" nella barra della formula o nella cella. La barra della formula viene utilizzata come strumento che consente di creare e modificare le formule.

Quando si passa alla modalità di immissione della formula, il campo Nome, situato sul lato sinistro della barra della formula, è sostituito da un pulsante che visualizza l'ultima funzione utilizzata. Il pulsante freccia alla sua destra apre un elenco contenente le 10 funzioni utilizzate più di recente e l'elemento Altre caratteristiche(figura 2.13).

Riso. 2.13. Finestra di Excel in modalità formula

Ad esempio, inseriamo una formula nella cella D4. Quando hai finito di inserire la formula, premi il tasto Invio o fai clic sul pulsante nella barra della formula Ingresso. La cella contenente la formula visualizzerà il risultato del calcolo, la formula stessa sarà visibile nella barra della formula (Fig. 2.14).

Riso. 2.14. Visualizzazione di una formula di calcolo nella barra della formula

ESPOSITORE FORMULA IN UNA CELLA. Nell'impostazione predefinita, una cella formula visualizza il risultato di un calcolo, non la formula stessa. In alcuni casi, ad esempio, durante la compilazione e il controllo di calcoli complessi in una cella, è più conveniente visualizzare non un valore numerico, ma la formula stessa. Per visualizzare le formule su un foglio di lavoro, selezionare il comando Opzioni sul menu Servizio, apri la scheda Visualizzazione e incorniciato Opzioni finestra selezionare la casella Formule.

Per tornare alla modalità di visualizzazione della formula predefinita, deselezionare la casella di controllo. Per passare alla modalità di visualizzazione delle formule o tornare alla modalità normale, premere i tasti Ctrl+` (il tasto di apice inverso è sullo stesso tasto della tilde "~" - sotto il tasto Esc).

Ad esempio, dobbiamo calcolare la somma dei numeri memorizzati nelle celle A1, A2, A3. Fai doppio clic sulla cella A5 e inserisci la formula =A1+A2+A3. Per calcolare la somma dei numeri memorizzati nelle celle C1, C2, C3, non è possibile riscrivere la formula, ma copiarla dalla cella A5 alla C5. Excel cambierà automaticamente i riferimenti relativi e la formula sarà simile a = C1 + C2 + C3. La formula modificata può essere visualizzata facendo clic sulla cella C5. (Per visualizzare le formule nella finestra dell'applicazione, selezionare la casella di controllo Formule scheda Visualizzazione la finestra di dialogo Opzioni).

Un riferimento assoluto memorizza l'indirizzo di una cella specifica, indipendentemente dalla posizione della cella che contiene la formula. Ad esempio, se copi una formula dalla cella A7 con riferimenti assoluti alla cella C7, la formula non cambierà. Il simbolo del dollaro $ viene utilizzato per indicare un riferimento assoluto. $A$7, $C$7 (figura 2.15).

Riso. 2.15. Visualizza formule con riferimenti assoluti e relativi nella finestra di Excel

Nei casi in cui, durante la copia o lo spostamento di una formula, è necessario mantenere invariato solo il numero di riga o solo il nome di colonna, viene utilizzato un riferimento misto, ad esempio $D7 o F$5. Per ulteriori informazioni sull'utilizzo di un riferimento assoluto in una formula, vedere la sezione "Copia di una formula" di seguito.

È possibile fare riferimento a celle che si trovano in altri fogli di lavoro o in un'altra cartella di lavoro oppure a dati in un'altra applicazione. I collegamenti alle celle in altre cartelle di lavoro sono chiamati collegamenti esterni. I collegamenti ai dati di altre applicazioni sono chiamati collegamenti remoti.

Quando si immette un riferimento di cella in un altro foglio di lavoro, il nome del foglio viene separato dal nome della cella da un punto esclamativo. Se viene immesso un collegamento a un'altra cartella di lavoro, viene specificato il nome file della cartella di lavoro, racchiuso tra parentesi quadre, ad esempio [Libro2]Foglio1!$C$3.

Copia formule

La copia del contenuto di una cella (blocco di celle) in un'altra (blocco di celle) viene eseguita per semplificare l'inserimento dello stesso tipo di dati e formule nella tabella. Quando copi una formula, i relativi riferimenti di cella inclusi nella formula vengono automaticamente modificati in modo che corrispondano alla nuova posizione nel foglio di lavoro. I riferimenti di cella assoluti vengono utilizzati per disabilitare l'impostazione automatica dell'indirizzo. La formula originale da copiare o spostare viene percepita come una sorta di modello, che indica la posizione dell'input rispetto alla posizione della cella con la formula.

COPIA DI UNA FORMULA MEDIANTE TRASCINAMENTO. Ecco un esempio di copia di una formula mediante trascinamento. Supponiamo di dover elevare alla terza potenza i numeri 5, 7 e 10, che si trovano nelle celle A3, A4 e A5. Scriviamo i risultati dei calcoli rispettivamente nelle celle B3, B4 e B5. Seleziona la cella B3 e inserisci la formula =A3^3 (il simbolo "^" viene utilizzato come operatore di esponenziale). Premiamo il tasto Invio. La cella visualizzerà il risultato 125. Selezionare nuovamente la cella B3. Posiziona il puntatore del mouse su un quadratino nero: l'indicatore di riempimento. Premi il pulsante del mouse e trascina la cornice verso il basso di altre due celle. Le celle selezionate visualizzeranno i risultati del calcolo: 343 e 1000. Fare clic sulla cella B4 - nella barra della formula vedremo = A4 ^ 3, ovvero l'indirizzo relativo della cella è cambiato.

Se, durante la copia di una formula, è necessario lasciare invariato il suo indirizzo, viene utilizzato un riferimento assoluto. Scriviamo la formula come =$A$3^3. Quando si copia questa formula in qualsiasi punto della tabella, i dati nella cella A3 verranno sempre elevati alla terza potenza. Quando si copia una formula, i riferimenti relativi vengono modificati automaticamente, i riferimenti assoluti non cambiano.

COPIA FORMULA UTILIZZANDO IL COMANDO COPIA NEL MENU MODIFICA. Per copiare una formula, seleziona la cella che contiene la formula e seleziona dal menu Modificare comando copia. Quindi selezionare la cella o l'intervallo di celle in cui verrà inserita la formula e selezionare il comando Inserire sul menu Modificare. Le celle in cui viene copiata la formula possono trovarsi in un foglio di lavoro diverso o in una cartella di lavoro diversa. Quando si copiano e si spostano le formule, i collegamenti vengono modificati automaticamente.

Funzioni standard di Excel

Excel contiene un ampio elenco di funzioni standard progettate per semplificare i calcoli semplici e complessi. Le funzioni sono formule definite che eseguono calcoli su valori specificati dall'utente nell'ordine specificato. Ad esempio, la funzione YIELD viene utilizzata per calcolare il rendimento di un'obbligazione, ovvero il pagamento periodico degli interessi. Tutte le funzioni hanno la stessa notazione, che include il nome della funzione e un elenco di argomenti. Gli argomenti sono nella sequenza specificata dalla sintassi della funzione e sono separati da una virgola. La funzione consente di eseguire calcoli su fogli di cartelle di lavoro e fogli macro.

Le funzioni vengono inserite nella tabella come parte delle formule o separatamente. Ecco alcune funzioni che possono essere utilizzate nei fogli di calcolo:

· matematico;

· statistico;

· testo;

· rompicapo;

· finanziario;

funzioni di data e ora, ecc.

Funzioni matematiche utilizzato nei calcoli scientifici e ingegneristici per eseguire varie operazioni matematiche: calcolo di logaritmi, funzioni trigonometriche, conversione di radianti in gradi, ecc.

Funzioni statistiche sono utilizzati per analizzare intervalli di dati, calcolare parametri che caratterizzano variabili casuali rappresentate da un insieme di numeri, o loro distribuzioni, come deviazione standard, media, ecc. In particolare, possiamo trovare l'equazione di una retta o di una curva esponenziale che si adatta meglio ai dati sperimentali.

Testo Le funzioni convertono il valore numerico in testo formattato e il risultato non viene più conteggiato come numero. Consentono di calcolare la lunghezza di una stringa, convertire le lettere maiuscole in minuscole e così via. Una e commerciale (&) viene utilizzata per combinare più righe di testo in un'unica riga.

Ad esempio, nella cella B3 puoi scrivere il nome del venditore (Petrov), nella cella C3 - il volume delle sue vendite (5000). Dopo aver scritto in alcune celle B3 & "venduto" & TESTO (C3; "0,00 rubli") e " unità di merce "durante l'esecuzione dei calcoli, il contenuto delle celle verrà combinato in una frase: Petrov ha venduto unità di merce da 5000,00 rubli.

Funzioni logiche vengono utilizzati per costruire espressioni logiche, il cui risultato dipende dalla verità della condizione verificata.

funzioni finanziarie vengono utilizzati in complessi calcoli finanziari, ad esempio, determinando il tasso di sconto, l'importo dei pagamenti mensili per rimborsare un prestito, determinando l'ammortamento, ecc.

Funzioni di data e ora. Funzione data calcola una data in formato numerico come il numero di giorni dal 1 gennaio 1900. Funzione Tempoè una frazione di un intervallo di 24 ore, scritta come frazione decimale, che può assumere valori da 0 a 0,99999999. Zero corrisponde alle 12:00:00 di notte. Le ore vanno da 0 a 23, minuti e secondi da 0 a 59.

Diamo esempi delle funzioni più comuni.

SUM(List) è una funzione statistica per determinare la somma di tutti i valori numerici nella List. L'elenco può essere costituito da indirizzi di cella e di blocco, nonché da valori numerici.

SOMMA(A2:A5;C1:C8)

AvgValue(List) – una funzione statistica per determinare la media aritmetica di tutti i valori elencati nell'elenco.

Media(B1:B10)

MAX(List) è una funzione statistica che restituisce il valore massimo nell'elenco specificato.

MASSIMO(LA3:LA8;253)

IF (Condition, True, False) è una funzione logica che controlla la verità di una data condizione logica. Se la condizione è vera, il risultato della funzione è il valore dell'argomento "Vero". Se la condizione non è soddisfatta, il risultato della funzione è il valore dell'argomento "False".

SE (B4<100, 100,200)

- se la cella B4 contiene un numero inferiore a 100, allora alla funzione viene assegnato il valore 100, se questa condizione non è soddisfatta (ovvero il contenuto della cella B4 è maggiore o uguale a 100), alla funzione viene assegnato il valore 200.

VARIE OPZIONI PER L'ESECUZIONE DEI CALCOLI. Nella maggior parte dei casi, Excel offre diverse opzioni per l'esecuzione dei calcoli. Mostriamolo sull'esempio della somma dei dati. Per aggiungere numeri in un intervallo di celle, utilizzare la funzione SOMMA, che è la funzione più comunemente utilizzata.

Introduciamo i termini nelle celle D3, D4, D5. La sommatoria può essere eseguita nei seguenti modi:

io modo.

Seleziona la cella D6 e inserisci la formula di calcolo, facendo riferimento alle celle contenenti i dati originali:

Premi il tasto Invio e il risultato apparirà nella cella D6.

II metodo

Immettere il segno "=" nella cella D6, fare clic sulla cella D3 - dopo il segno di uguale nella cella D6, verrà visualizzato l'indirizzo della cella D3.

Metti un segno "+" e fai clic sulla cella D4. La voce "=D3+D4" apparirà nella cella D6. Immettere il segno "+", fare clic sulla cella D5 e premere il tasto Invio. Il risultato verrà visualizzato nella cella D6

IIImodo.

Fare clic sulla cella D6 e premere il pulsante Somma automatica sulla barra degli strumenti Standard. La voce apparirà nella cella =SUM(D3:D5), cioè si propone di riassumere i dati registrati nelle celle da D3 a D5, situate nella stessa colonna con D6.

Premi il tasto Invio e vedremo il risultato della sommatoria. Quando viene selezionato un intervallo di celle e si fa clic sul pulsante Somma automatica la formula per calcolare la somma di queste celle verrà inserita nella cella vuota successiva all'intervallo.

Metodo IV.

Seleziona la cella D6 e inserisci da tastiera una formula contenente la funzione = SOMMA (D3: D5).

modo V.

Facciamo clic sulla cella D6 e utilizziamo la funzione guidata per inserire la formula.

Uso dei riferimenti nelle funzioni

Per fare riferimento a un intervallo di celle, digitare il riferimento alla cella in alto a sinistra dell'intervallo, utilizzare i due punti (:), quindi fare riferimento all'angolo in basso a destra dell'intervallo. Ad esempio, A2:C5. Il collegamento a tutte le celle tra la sesta e la quindicesima riga inclusa ha il formato 6:15, a tutte le celle nella colonna C - C:C.

Esempi di voci di intervalli di celle in una funzione:

· =SOMMA(E:E) - si determina la somma dei valori numerici contenuti nella colonna E;

· =SUM(E2:G5) - viene calcolata la somma dei valori numerici situati nell'intervallo da E2 a G5;

· =SOMMA(5:5) – vengono sommati i dati di tutte le celle della quinta riga;

· =SOMMA(2;4) – viene calcolata la somma 2+4.

È consentita la registrazione mista di indirizzi di celle e blocchi di celle. In questo caso, la formula potrebbe essere simile a questa:

SOMMA(С8;D4;Е2:E5;F5)

Esecuzione di un calcolo utilizzando le funzioni standard

Una voce di funzione inizia con il suo nome, seguito da una parentesi di apertura, argomenti e una parentesi di chiusura. La funzione può non avere argomenti. Può essere inserito in una cella del foglio di lavoro come parte di una formula. La funzione consente di eseguire calcoli su fogli di cartelle di lavoro e fogli macro.

Per inserire una funzione, fare clic sul pulsante Inserimento di una funzione, Sullo schermo viene visualizzata la barra della formula. La barra della formula viene visualizzata anche quando si fa clic sul pulsante Inserimento di una funzione sulla barra degli strumenti Standard. Se la formula è scritta in modo errato, sullo schermo potrebbe apparire un messaggio relativo a un riferimento circolare. I nomi delle funzioni possono essere digitati in lettere minuscole. Verranno convertiti in maiuscolo dopo aver premuto il tasto Invio. Le funzioni che sono argomento di un'altra funzione sono chiamate funzioni annidate. Le formule di Excel possono nidificare fino a sette livelli di nidificazione delle funzioni.

Per eseguire un calcolo utilizzando una funzione standard, attenersi alla seguente procedura:

1. Evidenziare la cella in cui si desidera inserire la funzione, immettere "=", quindi nell'elenco a discesa Funzioni nella barra della formula, seleziona quello che ti serve dall'elenco. Sullo schermo apparirà una finestra di dialogo. Argomenti della funzione. Se si seleziona dall'elenco a discesa altre caratteristiche, quindi si aprirà una finestra di dialogo Funzione guidata - passaggio 1 di 2(figura 2.16).

Altri modi per visualizzare una finestra di dialogo Funzione guidata - passaggio 1 di 2:

premi il bottone Inserimento di una funzione nella barra della formula;

Seleziona una squadra Funzione sul menu Inserire.

Riso. 2.16. Finestra di dialogo Creazione guidata funzione - Passaggio 1 di 2

2. Nella finestra di dialogo Funzione guidata - passaggio 1 di 2 in campo Ricerca di funzioni inserire una descrizione dell'azione che si desidera eseguire. Ad esempio, per trovare funzioni relative ai logaritmi di numeri, digitare tronco d'albero. Se non sai a quale categoria appartiene la tua funzione, allora nell'elenco a discesa o categoria selezionare la riga Elenco alfabetico completo e visualizzare l'elenco di tutte le funzioni in ordine alfabetico. La barra di scorrimento consente di visualizzare le voci dell'elenco attualmente invisibili. La parte inferiore della finestra definisce la funzione selezionata ei suoi argomenti. Per una descrizione di una funzione, selezionare la funzione dall'elenco e fare clic sul collegamento Guida per questa funzione. Evidenziare la riga desiderata nell'elenco Seleziona funzione e premere il pulsante OK o il tasto Invio.

3. Sullo schermo apparirà una finestra di dialogo. Argomenti di funzione(figura 2.17). Nella parte superiore della finestra sono presenti i campi per l'inserimento degli argomenti, nella parte inferiore - informazioni di riferimento: il nome della funzione selezionata, tutti i suoi argomenti, lo scopo della funzione e ciascun argomento, il risultato corrente della funzione e il intera formula. Nei casi in cui l'argomento è in grassetto è obbligatorio, se in normale può essere omesso.

Per evitare che la barra della formula copra l'intervallo di celle con i dati, puoi spostarla tenendo premuto il pulsante del mouse. Per inserire un riferimento di cella come argomento, fare clic sull'icona situata sul bordo destro del campo.

L'altezza della finestra di dialogo diminuirà. Con le celle selezionate, fare clic sull'icona per riportare la finestra di dialogo alle sue dimensioni originali. Se la finestra di dialogo Argomenti di funzione consente di inserire più argomenti, è possibile spostarsi da un campo argomento all'altro con il tasto Tab. Dopo aver inserito gli argomenti, verrà visualizzato il risultato corrente.

Riso. 2.17. Utilizzo della barra della formula per stimare la varianza del campione

ESEMPIO 1. Excel aiuta a calcolare vari dati di riferimento senza utilizzare libri di riferimento. Trova il logaritmo in base 10 del numero 250. Svolgi l'esercizio come segue:

Inserimento di una funzione Funzione guidata - passaggio 1 di 2 Categoria evidenziare la linea matematico, e nell'elenco in basso Seleziona funzione – REGISTRO10 e premere il pulsante OK. Sullo schermo apparirà una finestra di dialogo. Argomenti di funzione(figura 2.18). Immettere il numero 250 nella riga Numero finestra di dialogo e ottenere un risultato di 2.398.

Riso. 2.18. Calcolo del logaritmo decimale

ESEMPIO 2. Trova i valori delle funzioni trigonometriche: seno, coseno, tangente, cotangente per un angolo di 10 gradi.

Selezionare la cella in cui si desidera inserire la funzione e fare clic sul pulsante Inserimento di una funzione nella barra della formula. Nella finestra di dialogo Funzione guidata - passaggio 1 di 2(vedi Fig. 2.16) nell'elenco a discesa Categoria evidenziare la linea matematico, e nell'elenco in basso Seleziona funzione selezionare la funzione trigonometrica appropriata e premere il pulsante OK. Inserisci il numero 10 nella riga Numero la finestra di dialogo Argomenti di funzione e vedere il risultato. Per riferimento, presentiamo i valori delle funzioni trigonometriche: sin 100=0.17, cos100=0.98, tg 100=0.18, ctg 100=5.67.

ESEMPIO 3. Determina la media aritmetica dei seguenti valori: 5, 20, 10, 8. Posiziona i valori indicati in celle diverse, ad esempio B6, B10, B13 e B17. Posizionare il cursore nella cella in cui verrà posizionato il risultato del calcolo e premere il pulsante Inserimento di una funzione nella barra della formula. Nella finestra di dialogo Funzione guidata - passaggio 1 di 2(vedi Fig. 2.16) nell'elenco a discesa Categoria evidenziare la linea statistico, e nell'elenco in basso Seleziona funzione installare MEDIA Nella finestra Argomenti di funzione fare clic sul pulsante della finestra di riduzione a icona situato a destra del campo Numero 1 e selezionare l'intervallo di celle contenente i numeri. Premere il tasto Ingresso. Nella parte inferiore della finestra Argomenti di funzione leggerai il valore medio, che dopo aver premuto il pulsante OK verrà inserito nella cella specificata.

Nota

È possibile inserire una nota contenente informazioni aggiuntive in qualsiasi cella della cartella di lavoro. Selezionare una cella e selezionare un comando Nota sul menu Inserire. Una cornice con il nome dell'autore della nota appare sullo schermo accanto alla cella. Il testo della nota relativo a questa cella viene inserito nel riquadro. L'autore della nota è indicato nella scheda Sono comuni nella finestra di dialogo Opzioni, visualizzato sullo schermo dopo aver selezionato l'omonimo comando nel menu Servizio. La dimensione della cornice non determina la quantità di informazioni presentate nella nota, ma solo la quantità di nota che verrà visualizzata sullo schermo. Se passi con il mouse sopra una cella contenente un'icona di nota (il triangolo rosso nell'angolo in alto a destra della cella), verrà visualizzata automaticamente una finestra pop-up con il testo della nota.

Se nella scheda Visualizzazione la finestra di dialogo Opzioni in gruppo Appunti interruttore in posizione Solo un indicatore quindi le celle contenenti commenti sono contrassegnate nell'angolo in alto a destra con un indicatore che assomiglia a un triangolo rosso. Se l'interruttore è in posizione Nota e indicatore, allora sono visibili sia l'indicatore che la parte della banconota delimitata dalla cornice (Fig. 2.19).

Riso. 2.19. Mostra l'indicatore e il testo del commento della cella

Le note fatte da utenti diversi per la stessa cella verranno visualizzate nella stessa finestra delle note e contrassegnate con i nomi dei rispettivi utenti. Questa funzionalità può essere utilizzata insieme a Windows Portfolio. L'utente può lavorare su una copia di un libro mastro condiviso mentre è disconnesso dalla rete locale, quindi inserirlo in una valigetta e unirlo ad altre copie quando la connessione di rete viene ripristinata.

Messaggio di errore

Il messaggio di errore inizia con un segno #, ad esempio, se in una cella appare #VALORE! durante l'esecuzione di calcoli, significa che il programma non riesce a trovare i dati originali. Il tipo di messaggio cambia a seconda della causa dell'errore. Quindi, il messaggio #DIV/0! appare quando la formula propone di dividere per zero (Fig. 2.20). Quando si sposta il puntatore del mouse sull'icona di uno smart tag, accanto a essa viene visualizzato un pulsante. Fare clic sull'icona per aprire un menu da cui è possibile vedere quale tipo di errore è stato rilevato, visualizzare i passaggi di calcolo, apportare modifiche alla barra della formula e altro ancora.

Riso. 2.20. Messaggio di errore

Se Excel ritiene che l'errore nell'espressione immessa possa essere corretto, verrà visualizzata una finestra con un suggerimento su come modificare la formula.

L'utilizzo del programma EXCEL non è così difficile come potrebbe sembrare a prima vista. Questo programma è stato sviluppato da una grande società Microsoft e, quando l'hanno creato, sapevano che con questo programma sarebbe stato possibile semplificare la vita dei lavoratori in vari campi con l'aiuto della funzione di aggiunta di formule. Questo programma ha una funzionalità incredibilmente enorme che può essere utilizzata per una varietà di scopi, sia personali, sia per lavoro o studio.

Le formule stesse non sono difficili e sono molto facili da capire. Ma anche se sono facili da imparare, sono utili, perché grazie a queste formule puoi ridurre il tempo per i calcoli. Grazie alle formule, è possibile ottenere determinati risultati di espressioni in una cella, le variabili in cui agiranno le altre celle. Grazie a questo programma, puoi eseguire i calcoli matematici e finanziari più complessi quasi senza preoccuparti di pensare.

Inoltre, le celle che fungono da variabili per le espressioni possono accettare non solo l'immissione di numeri dalla tastiera, ma anche i risultati di altre formule. Oltre ai soliti calcoli matematici, le formule possono aiutarti a fare calcoli logici.
Attraverso questo programma, puoi apprendere indicatori come:

Massimo, minimo e media.
- Percentuale di numeri
- Criteri vari incluso il test t di Student
- Oltre a molti altri indicatori utili

Il programma ha molti vantaggi, ma il principale, ovviamente, è che può convertire numeri e creare un'alternativa, qualsiasi scenario, e tutti i calcoli sono quasi istantanei.

Applicazione di formule semplici in Excel.

Considera le formule usando un semplice esempio della somma di due numeri per capire come funzionano. Un esempio potrebbe essere la somma di due numeri. Le variabili saranno le celle A1 e B1, in cui l'utente inserirà i numeri. Nella cella C3, la somma di questi due numeri verrà visualizzata se su di essa è specificata la seguente formula:
=SOMMA(A1;B1).

Puoi calcolare tu stesso l'importo, ma in esempi più complessi, in cui devi aggiungere decine e centinaia di migliaia, sarà più difficile farlo mentalmente e, utilizzando la formula della somma, il valore verrà calcolato automaticamente, quasi come in una calcolatrice.

È possibile modificare i dati nelle celle variabili, ma non è necessario modificare una cella formula a meno che non si desideri sostituirla con una formula diversa. Oltre alla somma, puoi eseguire altre operazioni matematiche, come differenza, divisione e moltiplicazione. Una formula inizia sempre con un segno "=". Se non è presente, il programma non conterà la tua formula.

Come creare una formula nel programma?

Nell'esempio precedente è stato considerato un esempio della somma di due numeri, che tutti possono gestire senza l'ausilio di Excel, ma quando è necessario calcolare la somma di più di 2 valori, ci vorrà più tempo, quindi puoi sommare tre celle in una volta, per questo devi solo scrivere la seguente formula nella cella D1:
=SOMMA(A1:B1;C1).

Ma ci sono momenti in cui devi aggiungere, ad esempio, 10 valori, per questo puoi usare la seguente versione della formula:
=SOMMA(A1:A10), che avrà questo aspetto.

E allo stesso modo con il prodotto, usa solo PRODOTTO invece di SUM.

Puoi anche utilizzare formule per più intervalli, per questo è necessario scrivere la seguente versione della formula, nel nostro caso, il prodotto:
=PRODOTTO(A1-A10, B1-B10, C1-C10)

Combinazioni di formule.

Oltre al fatto che puoi impostare una vasta gamma di numeri, puoi anche combinare diverse formule. Ad esempio, dobbiamo sommare i numeri di un certo intervallo e dobbiamo calcolare il loro prodotto con la moltiplicazione per coefficienti diversi per opzioni diverse. Diciamo che dobbiamo trovare il coefficiente 1.4 dalla somma dell'intervallo (A1:C1) se la loro somma è minore di 90, ma se la loro somma è maggiore o uguale a 90, allora dobbiamo trovare il coefficiente 1.5 dalla stessa quantità. Per questo compito apparentemente complesso, viene fornita solo una formula semplice, che combina due formule di base, e assomiglia a questa:

SE(SOMMA(A1:C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Come puoi vedere, in questo esempio sono state utilizzate due formule, una delle quali è IF, che confronta i valori specificati, e la seconda è SUM, con la quale abbiamo già familiarità. La formula IF ha tre argomenti: condizione, vero, falso.

Considera la formula in modo più dettagliato sulla base del nostro esempio. La formula IF accetta tre argomenti. La prima è una condizione che verifica se la somma dell'intervallo è inferiore a 90 o meno. Se la condizione è vera, viene eseguito il secondo argomento e se è falsa, viene eseguito il terzo argomento. Cioè, se inseriamo un valore nelle celle la cui somma è inferiore a 90, questa somma verrà moltiplicata per un fattore di 1,4 e se la loro somma è maggiore o uguale a 90, verrà eseguita una moltiplicazione per un fattore di 1,5 verificarsi.

Tali strutture vengono utilizzate per calcolare valori complessi e possono essere utilizzate più di una volta in una cartella di lavoro di Excel.

Funzioni di Excel di base

La funzionalità di Excel colpisce per la sua diversità e tutti possono utilizzare questa funzionalità per una varietà di scopi (carriera o istruzione). Non tutte le funzioni vengono utilizzate regolarmente, ma ce ne sono alcune che verranno utilizzate quasi ogni volta.

Per visualizzare l'insieme di formule di cui dispone il programma, è necessario fare clic sul pulsante "Inserisci funzione", che si trova nella scheda "Formule".

Oppure puoi premere la combinazione di tasti Maiusc+F3. Questo pulsante (o una combinazione di tasti sulla tastiera) consente di velocizzare il processo di scrittura delle formule. Non è necessario inserire tutto manualmente, perché quando si fa clic su questo pulsante nella cella attualmente selezionata, verrà aggiunta la formula con gli argomenti selezionati nell'elenco. Puoi cercare in questo elenco utilizzando l'inizio della formula o selezionare la categoria in cui si troverà la formula di cui hai bisogno.

Ad esempio, la funzione SOMMA.PIÙ.SE è nella categoria delle funzioni matematiche.

Dopo aver selezionato la funzione di cui hai bisogno, devi compilare i campi a tua discrezione.

funzione CERCA.VERT

Una delle funzioni molto utili in Excel è una funzione chiamata CERCA.VERT. Grazie a questa funzione, puoi ottenere le informazioni necessarie dalla tabella. La funzione ha tre argomenti per aiutarti a farlo.

Il primo argomento è la cella contenente la variabile inserita da tastiera dall'utente. Da questa cella verranno prese le informazioni sulla riga da cui si desidera estrarre le informazioni. Il secondo argomento è l'intervallo stesso, in cui verrà cercato il valore richiesto. E il terzo argomento è un numero che mostrerà il numero della colonna in cui si troveranno le informazioni da restituire.

Vale la pena notare che la ricerca avverrà anche quando alcuni numeri non esistono e se chiedi alla tua formula di trovare informazioni da un numero che non esiste, non incapperai in un errore, ma otterrai il risultato dal precedente cellula.

Ciò è dovuto al fatto che questa funzione ha anche un quarto argomento, che ha solo due valori, VERO o FALSO, e poiché non è impostato per noi, è diventato VERO per impostazione predefinita.

Arrotondamento dei numeri utilizzando le funzioni standard

Le funzioni di Excel consentono non solo di eseguire semplici operazioni matematiche, come addizione, sottrazione, moltiplicazione e così via, ma consentono anche di eseguire arrotondamenti, che possono essere molto utili se si ha bisogno di ottenere un numero definito con precisione, che verrà poi essere utilizzato per qualsiasi scopo specifico. , ad esempio, per utilizzarlo come valore di argomento in una funzione in cui le cifre decimali non sono consentite. Puoi ottenere un numero arrotondato sia per eccesso che per difetto.

Per arrotondare il valore di una cella per eccesso, è necessaria la formula ROUNDUP. Vale la pena prestare attenzione al fatto che la formula non accetta un argomento, che sarebbe logico, ma due, e il secondo argomento deve essere uguale a zero.

La figura mostra chiaramente come è avvenuto l'arrotondamento. Pertanto, le informazioni nella cella A1 possono essere modificate come in tutti gli altri casi, perché è una variabile e viene utilizzata come valore dell'argomento. Ma se devi arrotondare il numero non per eccesso, ma per difetto, questa formula non ti aiuterà. In questo caso, è necessaria la formula ROUNDDOWN. L'arrotondamento avverrà al numero intero più vicino che è minore del valore frazionario corrente. Cioè, se nell'esempio in figura, invece della formula ROUND UP, è impostata la formula ROUND DOWN, allora il risultato non sarà più 77, ma 76.

Grazie alla creazione di Microsoft Corporation, abbiamo un programma molto conveniente che può essere un ottimo assistente sia a scuola che al lavoro, oltre che per alcuni scopi personali. Funzioni e formule rendono questo programma ancora più comodo e funzionale. Grazie alle formule, puoi ottenere la somma ed eseguire altre operazioni matematiche su determinati intervalli di numeri, puoi trovare informazioni specifiche in una colonna o arrotondare i numeri frazionari a un numero intero. Questo programma è particolarmente utile per gli studenti che studiano matematica superiore, perché qui puoi creare un calcolatore completo che calcolerà i valori delle matrici.

Nella seconda parte della serie per principianti di Excel 2010 imparerai come collegare le celle di una tabella con formule matematiche, aggiungere righe e colonne a una tabella esistente, conoscere la funzionalità di completamento automatico e altro ancora.

introduzione

Nella prima parte della serie "Excel 2010 per principianti", abbiamo familiarizzato con le basi di Excel, avendo imparato a creare tabelle ordinarie al suo interno. A rigor di termini, questa è una questione semplice e ovviamente le possibilità di questo programma sono molto più ampie.

Il vantaggio principale dei fogli di calcolo è che le singole celle di dati possono essere collegate tra loro mediante formule matematiche. Cioè, quando il valore di una delle celle interconnesse cambia, i dati delle altre verranno ricalcolati automaticamente.

In questa parte vedremo quanto possono essere utili tali opportunità utilizzando l'esempio della tabella delle spese di bilancio che abbiamo già creato, per la quale dovremo imparare a scrivere semplici formule. Conosceremo anche la funzione di completamento automatico delle celle e scopriremo come inserire righe e colonne aggiuntive nella tabella, nonché unire le celle al suo interno.

Esecuzione di operazioni aritmetiche di base

Oltre a creare tabelle regolari, Excel può essere utilizzato per eseguire operazioni aritmetiche al loro interno, come addizione, sottrazione, moltiplicazione e divisione.

Per eseguire calcoli in qualsiasi cella della tabella, devi creare al suo interno il più semplice formula, che deve sempre iniziare con un segno di uguale (=). Per specificare operazioni matematiche all'interno di una formula, vengono utilizzati operatori aritmetici ordinari:

Ad esempio, immaginiamo di dover aggiungere due numeri: "12" e "7". Posizionare il cursore del mouse in una cella qualsiasi e digitare la seguente espressione: "=12+7". Al termine dell'inserimento, premere il tasto "Invio" e nella cella verrà visualizzato il risultato del calcolo - "19".

Per scoprire cosa contiene effettivamente una cella - una formula o un numero - devi selezionarla e guardare la barra della formula - l'area immediatamente sopra i nomi delle colonne. Nel nostro caso, mostra solo la formula che abbiamo appena inserito.

Dopo aver eseguito tutte le operazioni, prestare attenzione al risultato della divisione dei numeri 12 per 7, che si è rivelato non intero (1.714286) e contiene parecchie cifre dopo la virgola. Nella maggior parte dei casi, tale precisione non è richiesta e numeri così lunghi ingombrano solo il tavolo.

Per risolvere questo problema, seleziona la cella con il numero per il quale desideri modificare il numero di cifre decimali dopo la virgola e nella scheda casa in gruppo Numero seleziona una squadra Diminuire la profondità di bit. Ogni pressione di questo pulsante rimuove un carattere.

A sinistra della squadra Diminuire la profondità di bit c'è un pulsante che esegue l'operazione inversa - aumenta il numero di cifre decimali per visualizzare valori più accurati.

Elaborazione di formule

Ora torniamo alla tabella di spesa del budget che abbiamo creato nella prima parte di questa serie.

Al momento, registra le spese personali mensili per articoli specifici. Ad esempio, puoi scoprire quanto è stato speso a febbraio per il cibo oa marzo per la manutenzione dell'auto. Ma le spese mensili totali non sono indicate qui, sebbene questi indicatori siano i più importanti per molti. Correggiamo questa situazione aggiungendo la riga "Spese mensili" in fondo alla tabella e calcoliamo i suoi valori.

Per calcolare la spesa totale di gennaio nella cella B7, puoi scrivere la seguente espressione: “=18250+5100+6250+2500+3300” e premere Invio, dopodiché vedrai il risultato del calcolo. Questo è un esempio dell'applicazione della formula più semplice, la cui compilazione non è diversa dai calcoli su una calcolatrice. A meno che il segno di uguale non sia posto all'inizio dell'espressione e non alla fine.

Ora immagina di aver commesso un errore nello specificare i valori di una o più voci di spesa. In questo caso, dovrai adeguare non solo i dati nelle celle che indicano le spese, ma anche la formula per il calcolo delle spese totali. Certo, questo è molto scomodo, e quindi in Excel, quando si formulano formule, spesso non vengono usati valori numerici specifici, ma indirizzi e intervalli di celle.

Con questo in mente, cambiamo la nostra formula per il calcolo delle spese mensili totali.

Nella cella B7, inserisci un segno di uguale (=) e ... invece di inserire manualmente il valore della cella B2, fai clic su di essa con il tasto sinistro del mouse. Successivamente, attorno alla cella apparirà una cornice di selezione tratteggiata, che mostra che il suo valore è incluso nella formula. Ora digita un segno "+" e fai clic sulla cella B3. Successivamente, fai lo stesso con le celle B4, B5 e B6, quindi premi il tasto Invio, dopodiché apparirà lo stesso valore dell'importo come nel primo caso.

Seleziona di nuovo la cella B7 e osserva la barra della formula. Si può vedere che al posto dei numeri - valori di cella, la formula contiene i loro indirizzi. Questo è un punto molto importante, poiché abbiamo appena costruito la formula non da numeri specifici, ma dai valori delle celle, che possono cambiare nel tempo. Ad esempio, se ora modifichi l'importo delle spese per l'acquisto di cose a gennaio, l'intera spesa totale mensile verrà ricalcolata automaticamente. Provalo.

Supponiamo ora di dover sommare non cinque valori, come nel nostro esempio, ma cento o duecento. Come capisci, in questo caso è molto scomodo utilizzare il metodo sopra descritto per costruire formule. In questo caso è preferibile utilizzare l'apposito pulsante "Somma automatica", che consente di calcolare la somma di più celle all'interno della stessa colonna o riga. In Excel puoi contare non solo le somme delle colonne, ma anche le righe, quindi lo usiamo per calcolare, ad esempio, le spese alimentari totali per sei mesi.

Posiziona il cursore su una cella vuota a lato della riga richiesta (nel nostro caso è H2). Quindi premere il pulsante Somma sul segnalibro casa in gruppo La modifica. Ora, torniamo al tavolo e vediamo cosa è successo.

Nella cella che abbiamo selezionato è apparsa una formula con un intervallo di celle i cui valori devono essere sommati. Allo stesso tempo, è ricomparsa una cornice di selezione tratteggiata. Solo che questa volta non inquadra una cella, ma l'intero intervallo di celle, la cui somma deve essere calcolata.

Ora diamo un'occhiata alla formula stessa. Come prima, il segno uguale viene prima, ma questa volta è seguito da funzione"SOMMA" è una formula predefinita che aggiungerà i valori delle celle specificate. Subito dopo la funzione ci sono parentesi poste attorno agli indirizzi delle celle i cui valori devono essere sommati, chiamati argomento della formula. Si noti che la formula non contiene tutti gli indirizzi delle celle sommate, ma solo il primo e l'ultimo. I due punti tra di loro lo indicano allineare cellule da B2 a G2.

Dopo aver premuto Invio, il risultato apparirà nella cella selezionata, ma le capacità di questo pulsante Somma non finire. Cliccando sulla freccia accanto si aprirà un elenco contenente le funzioni per il calcolo dei valori medi (Media), del numero di dati inseriti (Number), massimi (Maximum) e minimi (Minimum).

Quindi, nella nostra tabella, abbiamo calcolato le spese totali per gennaio e le spese alimentari totali per sei mesi. Allo stesso tempo, lo hanno fatto in due modi diversi: prima utilizzando gli indirizzi di cella nella formula, quindi utilizzando funzioni e intervalli. Ora è il momento di terminare i calcoli per le restanti celle, dopo aver calcolato i costi totali per i restanti mesi e le voci di spesa.

Completamento automatico

Per calcolare gli importi rimanenti, utilizzeremo una caratteristica notevole del programma Excel, ovvero la capacità di automatizzare il processo di riempimento delle celle con dati sistematizzati.

A volte in Excel devi inserire dati simili dello stesso tipo in una determinata sequenza, come giorni della settimana, date o numeri di riga. Ricordi, nella prima parte di questo ciclo, nell'intestazione della tabella, abbiamo inserito separatamente il nome del mese in ogni colonna? In effetti, non era assolutamente necessario inserire manualmente l'intero elenco, poiché in molti casi l'applicazione può farlo per te.

Cancelliamo tutti i nomi dei mesi nell'intestazione della nostra tabella, tranne il primo. Ora seleziona la cella con l'etichetta "Gennaio" e sposta il puntatore del mouse nell'angolo in basso a destra in modo che assuma la forma di una croce chiamata indicatore di riempimento. Tieni premuto il pulsante sinistro del mouse e trascinalo verso destra.

Sullo schermo apparirà un tooltip, che ti dirà il valore che il programma inserirà nella cella successiva. Nel nostro caso, questo è "febbraio". Man mano che sposti l'indicatore verso il basso, cambierà nei nomi degli altri mesi, il che ti aiuterà a capire dove fermarti. Dopo aver rilasciato il pulsante, l'elenco verrà compilato automaticamente.

Naturalmente, Excel non sempre "capisce" correttamente come riempire le celle successive, poiché le sequenze possono essere molto diverse. Immaginiamo di dover riempire una stringa con valori numerici pari: 2, 4, 6, 8 e così via. Se inseriamo il numero "2" e proviamo a spostare a destra l'indicatore di riempimento automatico, si scopre che il programma propone, sia nella cella successiva che in altre, di inserire nuovamente il valore "2".

In questo caso, l'applicazione deve fornire un po' più di dati. Per fare ciò, nella cella successiva a destra, inserisci il numero "4". Ora seleziona entrambe le celle piene e sposta nuovamente il cursore nell'angolo in basso a destra dell'area di selezione in modo che assuma la forma di un indicatore di selezione. Spostando il marcatore verso il basso, vediamo che ora il programma ha compreso la nostra sequenza e mostra i valori richiesti nei prompt.

Pertanto, per sequenze complesse, prima di utilizzare il completamento automatico, è necessario compilare più celle contemporaneamente, in modo che Excel possa determinare correttamente l'algoritmo generale per il calcolo dei loro valori.

Ora applichiamo questa utile funzionalità del programma alla nostra tabella, indipendentemente da ciò che inseriamo manualmente nelle formule per le celle rimanenti. Innanzitutto, seleziona la cella con l'importo già calcolato (B7).

Ora "aggancia" il cursore nell'angolo in basso a destra del quadrato e trascina il marcatore a destra sulla cella G7. Dopo aver rilasciato il tasto, l'applicazione stessa copierà la formula nelle celle contrassegnate, cambiando automaticamente gli indirizzi delle celle contenute nell'espressione, sostituendo i valori corretti.

Inoltre, se il marcatore viene spostato a destra, come nel nostro caso, o in basso, le celle verranno riempite in ordine crescente ea sinistra o in alto in ordine decrescente.

C'è anche un modo per riempire una riga con un nastro. Usiamolo per calcolare le somme di spesa per tutte le voci di spesa (colonna H).

Selezioniamo l'intervallo da riempire partendo dalla cella con i dati già inseriti. Quindi sulla scheda casa in gruppo La modifica premi il bottone Riempire e scegli il verso di riempimento.

Aggiunta di righe, colonne e unione di celle

Per fare più pratica nella formulazione delle formule, espandiamo la nostra tabella e allo stesso tempo apprendiamo alcune operazioni di formattazione di base. Ad esempio, aggiungiamo le voci di reddito al lato delle spese, quindi calcoleremo i possibili risparmi di budget.

Supponiamo che la parte delle entrate della tabella si trovi sopra la parte delle spese. Per fare ciò, dovremo inserire alcune righe aggiuntive. Come sempre, ci sono due modi per farlo: utilizzando i comandi sulla barra multifunzione o utilizzando il menu contestuale, che è più rapido e semplice.

Fare clic in una qualsiasi cella della seconda riga con il tasto destro del mouse e nel menu che si apre selezionare il comando Inserire… e poi nella finestra - Aggiungi linea.

Dopo aver inserito una riga, notare che per impostazione predefinita viene inserita sopra la riga selezionata e ha il formato (colore di sfondo della cella, impostazioni delle dimensioni, colore del testo, ecc.) della riga sopra di essa.

Se desideri modificare la formattazione predefinita, subito dopo aver incollato, fai clic sul pulsante Aggiungi opzioni, che verrà visualizzato automaticamente accanto all'angolo in basso a destra della cella selezionata e selezionare l'opzione desiderata.

Con un metodo simile, le colonne possono essere inserite nella tabella, che verrà posizionata a sinistra di quella selezionata e delle singole celle.

A proposito, se alla fine una riga o colonna finisce in un posto non necessario dopo l'inserimento, possono essere facilmente cancellate. Fare clic con il tasto destro su una qualsiasi cella appartenente all'oggetto da eliminare e nel menu che si apre selezionare il comando Eliminare. Infine, specifica cosa deve essere eliminato esattamente: una riga, una colonna o una singola cella.

È possibile utilizzare il pulsante sulla barra multifunzione per aggiungere operazioni. Inserire situato nel gruppo cellule sul segnalibro casa e, per la rimozione, il comando con lo stesso nome nello stesso gruppo.

Nel nostro caso, dobbiamo inserire cinque nuove righe in cima alla tabella subito dopo l'intestazione. Per fare ciò, puoi ripetere l'operazione di addizione più volte, oppure puoi utilizzare il tasto "F4" dopo averlo eseguito una volta, che ripete l'operazione più recente.

Di conseguenza, dopo aver inserito cinque righe orizzontali nella parte superiore della tabella, la portiamo nella seguente forma:

Nella tabella abbiamo volutamente lasciato delle righe bianche non formattate per separare le entrate, le spese e le parti totali tra loro scrivendovi le apposite intestazioni. Ma prima di farlo, impareremo un'altra operazione in Excel: fusione cellulare.

Quando si uniscono più celle adiacenti, ne viene formata una, che può occupare più colonne o righe contemporaneamente. In questo caso, il nome della cella unita diventa l'indirizzo della prima cella superiore dell'intervallo unito. In qualsiasi momento, puoi dividere nuovamente una cella unita, ma una cella che non è mai stata unita non può essere divisa.

Quando si uniscono le celle, vengono salvati solo i dati in alto a sinistra, mentre i dati di tutte le altre celle unite verranno eliminati. Tienilo a mente ed è meglio unire prima e solo dopo inserire le informazioni.

Torniamo al nostro tavolo. Per scrivere le intestazioni su righe bianche, abbiamo bisogno di una sola cella, mentre ora sono composte da otto. Risolviamo questo problema. Seleziona tutte le otto celle della seconda riga della tabella e nella scheda casa in gruppo allineamento fare clic sul pulsante Unisci e centra.

Dopo aver eseguito il comando, tutte le celle selezionate nella riga verranno unite in un'unica cella grande.

C'è una freccia accanto al pulsante di unione, facendo clic su di essa si aprirà un menu con comandi aggiuntivi che ti consentono di: unire celle senza allineamento centrale, unire interi gruppi di celle orizzontalmente e verticalmente e anche separare.

Dopo aver aggiunto le intestazioni, oltre a aver compilato le righe: stipendio, bonus e reddito mensile, la nostra tabella ha iniziato ad assomigliare a questa:

Conclusione

In conclusione, calcoliamo l'ultima riga della nostra tabella, utilizzando le conoscenze acquisite in questo articolo, il cui calcolo dei valori delle celle avverrà secondo la seguente formula. Nel primo mese, il saldo sarà costituito dalla consueta differenza tra le entrate percepite per il mese e le spese totali in esso contenute. Ma nel secondo mese aggiungeremo a questa differenza il saldo del primo mese, poiché stiamo calcolando il risparmio. I calcoli per i mesi successivi verranno eseguiti secondo lo stesso schema: gli accumuli per il periodo precedente verranno aggiunti al saldo mensile corrente.

Ora traduciamo questi calcoli in formule comprensibili da Excel. Per gennaio (cella B14), la formula è molto semplice e sarà simile a questa: "=B5-B12". Ma per la cella C14 (febbraio), l'espressione può essere scritta in due modi diversi: “=(B5-B12)+(C5-C12)” o “=B14+C5-C12”. Nel primo caso, calcoliamo nuovamente il saldo del mese precedente e quindi vi aggiungiamo il saldo del mese corrente, e nel secondo, nella formula viene incluso il risultato già calcolato per il mese precedente. Ovviamente, usare la seconda opzione per costruire una formula nel nostro caso è molto più preferibile. Dopotutto, se segui la logica della prima opzione, nell'espressione per il calcolo di marzo ci saranno già 6 indirizzi di cella, ad aprile - 8, a maggio - 10 e così via, e quando usi la seconda opzione, ce ne saranno sempre tre.

Per compilare le restanti celle da D14 a G14 utilizziamo la possibilità di compilarle automaticamente, proprio come abbiamo fatto nel caso degli importi.

A proposito, per verificare il valore del risparmio finale per giugno, situato nella cella G14, nella cella H14 è possibile visualizzare la differenza tra l'importo totale del reddito mensile (H5) e le spese mensili (H12). Come puoi vedere, devono essere uguali.

Come si può vedere dagli ultimi calcoli, le formule possono utilizzare non solo gli indirizzi delle celle adiacenti, ma anche qualsiasi altro, indipendentemente dalla loro posizione nel documento o dall'appartenenza a una particolare tabella. Inoltre, hai il diritto di collegare celle situate su fogli diversi del documento e persino in libri diversi, ma ne parleremo nella prossima pubblicazione.

Ed ecco la nostra tabella finale con i calcoli eseguiti:

Ora, se lo desideri, potrai continuare a compilarlo da solo, inserendo sia ulteriori voci di spesa o entrata (righe) sia aggiungendo nuovi mesi (colonne).

Nel prossimo articolo parleremo più dettagliatamente delle funzioni, affronteremo il concetto di collegamenti relativi e assoluti, ci assicureremo di padroneggiare alcuni elementi di modifica della tabella più utili e molto altro.

Una formula è un'espressione matematica creata per calcolare un risultato e che può dipendere dal contenuto di altre celle. Una formula in una cella può contenere dati, riferimenti ad altre celle e un'indicazione dell'azione da intraprendere.

L'utilizzo dei riferimenti di cella consente di ricalcolare i risultati delle formule quando il contenuto delle celle incluse nelle formule cambia.

In Excel, le formule iniziano con un segno =. Le parentesi () possono essere utilizzate per specificare l'ordine delle operazioni matematiche.

Excel supporta i seguenti operatori:

• Operazioni aritmetiche:
  • addizione (+);
  • moltiplicazione (*);
  • trovare la percentuale (%);
  • sottrazione (-);
  • divisione (/);
  • esponente (^).
• Operatori di confronto:
  • = uguale;
  • < меньше;
  • > più;
  • <= меньше или равно;
  • >= maggiore o uguale;
  • <>non uguale.
• Operatori di telecomunicazioni:
  • : allineare;
  • ; Unione;
  • & operatore di join testo.

Tabella 22. Esempi di formule

Esercizio

Inserisci la formula -25-A1+AZ

Preinserisci qualsiasi numero nelle celle A1 e A3.

1. Selezionare la cella desiderata, ad esempio B1.
2. Inizia a inserire una formula con il segno =.
3. Immettere il numero 25 seguito dall'operatore (segno -).
4. Immettere un riferimento al primo operando, ad esempio facendo clic sulla cella desiderata A1.
5. Immettere il seguente operatore (+ segno).
6. Fare clic nella cella che è il secondo operando nella formula.
7. Completare l'inserimento della formula premendo il tasto accedere. Nella cella B1, ottieni il risultato.

Somma automatica

Pulsante Somma automatica (Somma automatica)- ∑ può essere utilizzato per creare automaticamente una formula che somma l'area delle celle adiacenti che sono direttamente Sinistra in questa linea e direttamente più alto in questa colonna.

1. Seleziona la cella in cui desideri inserire il risultato della sommatoria.
2. Fare clic sul pulsante Somma automatica - ∑ o premere la scorciatoia da tastiera Alt+=. Excel deciderà quale area includere nell'intervallo di sommatoria e la evidenzierà con una casella mobile punteggiata chiamata bordo.
3. Clic accedere per accettare l'area selezionata da Excel oppure utilizzare il mouse per selezionare una nuova area e quindi premere Invio.

La funzione Somma automatica si trasforma automaticamente quando le celle vengono aggiunte e rimosse all'interno dell'area.

Esercizio

Creazione di una tabella e calcolo tramite formule

1. Immettere i dati numerici nelle celle, come mostrato nella tabella. 23.

	UN	IN	CON	D	B	F
1
2		Magnolia	Giglio	Viola	Totale
3	Più alto	25	20	9
4	Specifiche medie.	28	23	21
5	Istituto professionale	27	58	20
v	Altro	8	10	9
7	Totale
8	Senza superiore

Tabella 23. Tabella dei dati iniziali

1. Selezionare la cella B7, in cui verrà calcolata la somma verticale.
2. Fare clic sul pulsante Somma automatica - ∑ oppure fare clic su Alt+=.
3. Ripetere i passaggi 2 e 3 per le celle C7 e D7.

Calcolare il numero di dipendenti senza istruzione superiore (utilizzando la formula B7-B3).

1. Selezionare la cella B8 e digitare il segno (=).
2. Fare clic nella cella B7, che è il primo operando nella formula.
3. Immettere il segno (-) sulla tastiera e fare clic nella cella OT, che è il secondo operando nella formula (la formula verrà inserita).
4. Clic accedere(nella cella B8 verrà calcolato il risultato).
5. Ripeti i passaggi 5-8 per calcolare le formule corrispondenti nelle celle C8 e 08.
6. Salva il file con il nome Education_Employees.x1s.

Tabella 24Risultato del calcolo

	UN	B	CON	D	E	F
1	Distribuzione dei dipendenti per titolo di studio
2		Magnolia	Giglio	Viola	Totale
3	Più alto	25	20	9
4	Specifiche medie.	28	23	21
5	Istituto professionale	27	58	20
6	Altro	8	10	9
7	Totale	88	111	59
8	Senza superiore	63	91	50

Formule duplicate utilizzando la maniglia di riempimento

Un'area di cella (cella) può essere replicata utilizzando indicatore di riempimento. Come mostrato nella sezione precedente, la maniglia di riempimento è il punto di interruzione nell'angolo in basso a destra della cella selezionata.

Spesso è necessario moltiplicare non solo i dati, ma anche le formule contenenti collegamenti di indirizzi. Il processo di replica delle formule utilizzando un quadratino di riempimento consente di colorare una formula mentre si modificano i riferimenti dell'indirizzo nella formula.

1. Seleziona la cella contenente la formula da replicare.
2. Lagna indicatore di riempimento nella giusta direzione. La formula verrà duplicata in tutte le celle.

In genere, questo processo viene utilizzato quando si copiano formule all'interno di righe o colonne contenenti dati dello stesso tipo. Quando si replicano le formule utilizzando un indicatore di riempimento, i cosiddetti indirizzi di cella relativi nella formula cambiano (i riferimenti relativi e assoluti verranno descritti in dettaglio in seguito).

Esercizio

Replica di formule

1.Aprire il file Education_Employees.x1s.

1. Immettere nella cella E3 la formula per la somma automatica delle celle = SOMMA (OT: 03).
2. Trascina e rilascia il quadratino di riempimento per copiare la formula nelle celle E4:E8.
3. Guarda come cambiano gli indirizzi di cella relativi nelle formule risultanti (Tabella 25) e salva il file.

	UN	IN	CON	D	E	F
1	Distribuzione dei dipendenti per titolo di studio
2		Magnolia	Giglio	Viola	Totale
3	Più alto	25	20	9	=SOMMA(VZ:03)
4	Specifiche medie.	28	23	21	=SOMMA(B4:04)
5	Istituto professionale	27	58	20	=SOMMA(B5:05)
6	Altro	8	10	9	=SOMMA(B6:06)
7	Totale	88	111	58	=SOMMA(B7:07)
8	Senza superiore	63	91	49	=SOMMA(B8:08)

Tabella 25. Modifica degli indirizzi di cella durante la replica delle formule

Collegamenti relativi e assoluti

Le formule che implementano i calcoli nelle tabelle utilizzano i cosiddetti collegamenti per indirizzare le celle. Il riferimento di cella può essere parente o assoluto.

L'uso di riferimenti relativi è simile all'indicazione della direzione di marcia lungo la strada: "vai tre isolati a nord, poi due isolati a ovest". Seguire queste istruzioni da luoghi di partenza diversi porterà a destinazioni diverse.

Ad esempio, una formula che somma i numeri in una colonna o riga viene spesso copiata in altri numeri di riga o colonna. Queste formule utilizzano riferimenti relativi (vedere l'esempio precedente nella Tabella 25).

Un riferimento di cella assoluto. L'area della cella Go farà sempre riferimento allo stesso indirizzo di riga e colonna. Se confrontato con le direzioni delle strade, sarà qualcosa del genere: "Vai all'incrocio tra Arbat e Boulevard Ring". Indipendentemente dal punto di partenza, questo porterà allo stesso posto. Se la formula richiede che l'indirizzo della cella rimanga invariato quando viene copiato, è necessario utilizzare un riferimento assoluto (formato record $A$1). Ad esempio, quando una formula calcola le frazioni di un totale, il riferimento alla cella contenente il totale non deve cambiare quando viene copiato.

Un segno di dollaro ($) apparirà prima sia di un riferimento di colonna che di un riferimento di riga (es. $C$2), Premendo successivamente F4 si aggiungerà o rimuoverà un segno prima del numero di colonna o di riga nel riferimento (C$2 o $C2 - il cosiddetti collegamenti misti).

1. Crea una tabella simile a quella qui sotto.

Tabella 26. Buste paga

1. Nella cella SZ, inserisci la formula per calcolare lo stipendio di Ivanov \u003d V1 * VZ.

Quando si replica la formula di questo esempio con riferimenti relativi nella cella C4, viene visualizzato un messaggio di errore (#VALORE!), poiché l'indirizzo relativo della cella B1 cambierà e la formula =B2*B4 verrà copiata nella cella C4;

1. Imposta il riferimento assoluto alla cella B1 posizionando il cursore nella barra della formula su B1 e premendo il tasto F4.La formula nella cella C3 sarà simile a =$B$1*B3.
2. Copia la formula nelle celle C4 e C5.
3. Salvare il file (Tabella 27) sotto il nome Stipendio.xls.

Tabella 27. Risultati del libro paga

Nomi nelle formule

I nomi nelle formule sono più facili da ricordare rispetto agli indirizzi di cella, quindi puoi utilizzare ambiti denominati (celle singole o multiple) invece di riferimenti assoluti. Le seguenti regole devono essere osservate durante la creazione di nomi:

• i nomi possono avere una lunghezza massima di 255 caratteri;
• i nomi devono iniziare con una lettera e possono contenere qualsiasi carattere tranne uno spazio;
• i nomi non dovrebbero assomigliare a collegamenti, come OT, C4;
• i nomi non dovrebbero usare funzioni di Excel come SOMMA SE e così via.

Sul menu Inserisci, Nome Esistono due diversi comandi per la creazione di aree denominate: Crea e Assegna.

Squadra Crea consente di impostare (inserire) il nome richiesto ( solo uno), Assegna comando utilizza le etichette poste sul foglio di lavoro come nomi di area (è consentito creare più nomi contemporaneamente).

Fare un nome

1. Selezionare la cella B1 (Tabella 26).
2. Seleziona dal menu Comando Inserisci, Nome (Inserisci, Nome) Assegna (Definisci).
3. Inserisci il tuo nome Tariffa oraria e fare clic su OK.
4. Seleziona la cella B1 e assicurati che il campo del nome indichi tariffa oraria.

Creazione di più nomi

1. Selezionare le celle ВЗ:С5 (Tabella 27).
2. Seleziona dal menu Inserisci, Nome (Inserisci, Nome) comando Crea (Crea), Apparirà una finestra di dialogo. Crea nomi(figura 88).
3. Assicurati che il pulsante di opzione nella colonna a sinistra sia selezionato e fai clic OK.
4. Evidenzia le celle OT:NW e assicurati che il campo del nome indichi Ivanov.

Riso. Figura 88. Finestra di dialogo Crea nomi

È possibile inserire un nome in una formula anziché un riferimento assoluto.

1. Nella barra della formula, posiziona il cursore nel punto in cui verrà aggiunto il nome.
2. Seleziona dal menu Inserisci, Nome (Inserisci, Nome) comando Incolla (Incolla), Viene visualizzata la finestra di dialogo Inserisci nomi.

1. Selezionare il nome desiderato dall'elenco e fare clic su OK.

Errori nelle formule

Se viene commesso un errore durante l'inserimento di formule o dati, nella cella risultante viene visualizzato un messaggio di errore. Il primo carattere di tutti i valori di errore è #. I valori di errore dipendono dal tipo di errore commesso.

Excel può riconoscere lontano da tutti gli errori, ma quelli che vengono trovati devono essere in grado di correggere.

Errore # # # # appare quando il numero inserito non entra nella cella. In questo caso, aumentare la larghezza della colonna.

Errore #DIV/0! appare quando si tenta di dividere per zero in una formula. Ciò accade più spesso quando il divisore è un riferimento di cella che contiene un valore zero o vuoto.

Errore #N/D!è l'abbreviazione di "dati non definiti". Questo errore indica l'utilizzo di un riferimento di cella vuoto nella formula.

Errore #NOME? appare quando un nome utilizzato in una formula è stato rimosso o non era stato precedentemente definito. Per correggere, definire o correggere il nome dell'area dati, il nome della funzione, ecc.

Errore #BLANK! appare quando viene specificata l'intersezione di due regioni, che in realtà non hanno celle comuni. Molto spesso, l'errore indica che è stato commesso un errore durante l'inserimento dei riferimenti all'intervallo di celle.

Errore #NUMERO! appare quando un formato o un valore di argomento non valido viene utilizzato in una funzione con un argomento numerico.

Errore #VALORE! appare quando in una formula viene utilizzato un argomento o un tipo di operando non valido. Ad esempio, viene immesso testo invece di un valore numerico o booleano per un operatore o una funzione.

Oltre agli errori sopra elencati, durante l'inserimento delle formule, potrebbe comparire un riferimento circolare.

Un riferimento circolare si verifica quando una formula include direttamente o indirettamente riferimenti alla propria cella. Un riferimento circolare può causare distorsioni nei calcoli del foglio di lavoro ed è quindi considerato un bug nella maggior parte delle applicazioni. Inserendo un riferimento circolare compare un messaggio di avviso (Fig. 89).

Per correggere l'errore, eliminare la cella che ha causato il riferimento circolare, modificare o reinserire la formula.

Funzioni in Excel

Calcoli più complessi nelle tabelle Excel vengono eseguiti utilizzando funzioni speciali (Fig. 90). L'elenco delle categorie di funzioni è disponibile quando si seleziona il comando Funzione nel menu Inserisci (Inserisci, Funzione).

Le funzioni finanziarie eseguono calcoli come il calcolo dell'importo del pagamento su un prestito, l'importo del pagamento del profitto sugli investimenti, ecc.

Le funzioni Data e ora consentono di lavorare con i valori di data e ora nelle formule. Ad esempio, puoi utilizzare la data corrente in una formula utilizzando la funzione OGGI.

Riso. 90. Funzione guidata

Le funzioni matematiche sono semplici e complessi calcoli matematici, come il calcolo della somma di un intervallo di celle, il valore assoluto di un numero, l'arrotondamento dei numeri, ecc.

Funzioni statistiche consentono di effettuare analisi statistiche dei dati. Ad esempio, puoi determinare la media e la varianza di un campione e molto altro.

Funzioni di banca dati può essere utilizzato per eseguire calcoli e selezionare i record per condizione.

Funzioni di testo fornire all'utente la possibilità di elaborare il testo. Ad esempio, puoi concatenare più stringhe utilizzando la funzione COLLEGARE.

Le funzioni booleane sono progettate per testare una o più condizioni. Ad esempio, la funzione SE consente di determinare se la condizione specificata è vera e restituisce un valore se la condizione è vera e un altro valore se è falsa.

Funzioni Controllo di proprietà e valori hanno lo scopo di definire i dati memorizzati nella cella. Queste funzioni controllano i valori nella cella in base alla condizione e restituiscono i valori in base al risultato. Vero o falso.

Per i calcoli delle tabelle che utilizzano funzioni integrate, si consiglia di utilizzare la Creazione guidata funzione. La finestra di dialogo Funzione guidata è disponibile quando si seleziona il comando Funzione nel menu Inserisci o premendo un pulsante , sulla barra degli strumenti standard. Durante il dialogo con il wizard è necessario impostare gli argomenti della funzione selezionata, per questo è necessario compilare i campi della finestra di dialogo con i corrispondenti valori o indirizzi delle celle della tabella.

Esercizio

Calcolo del valore medio per ogni riga del file Education.xls.

1. Evidenziare la cella F3 e fare clic sul pulsante Funzione guidata.
2. Nella prima finestra della finestra di dialogo Funzione guidata, dalla categoria Statistica, selezionare una funzione MEDIA, fare clic sul pulsante Ulteriore.
3. Gli argomenti devono essere forniti nella seconda finestra di dialogo della procedura guidata della funzione. Il cursore di input si trova nel campo di input del primo argomento. In questo campo come numero di argomento! inserire l'indirizzo dell'intervallo B3:D3 (Fig. 91).
4. Clic OK.
5. Copia la formula risultante nelle celle F4:F6 e salva il file (Tabella 28).

Riso. 91 Immissione di un argomento nella Creazione guidata funzione

Tabella 28. Tabella dei risultati del calcolo utilizzando la procedura guidata della funzione

	UN	IN	CON	D	E	F
1	Distribuzione dei dipendenti per titolo di studio
2		Magnolia	Giglio	Viola	Totale	Media
3	Più alto	25	20	9	54	18
4	Specifiche medie.	28	23	21	72	24
8	Istituto professionale	27	58	20	105	35
v	Altro	8	10	9	27	9
7	Totale	88	111	59	258	129

Per inserire un intervallo di celle nella finestra della creazione guidata funzione, è possibile cerchiare questo intervallo sul foglio di lavoro della tabella (nell'esempio B3:D3). Se la finestra Creazione guidata funzione copre le celle desiderate, è possibile spostare la finestra di dialogo. Dopo aver selezionato un intervallo di celle (B3:D3), attorno ad esso apparirà una cornice tratteggiata continua e l'indirizzo dell'intervallo di celle selezionato apparirà automaticamente nel campo dell'argomento.