Come scrivere formule in Excel. Informazioni complete sulle formule in Excel
Programma Microsoft Excel Office ha molte funzioni utili che ti aiutano a non fare lunghi calcoli prima di inserire dati in tabelle o grafici. Per calcolare alcuni valori, ad esempio addizione, sottrazione, rapporto e altri, è possibile utilizzare le formule specificate originariamente. Puoi aggiungerli tu stesso o scegliere dall'elenco di quelli esistenti. Impara a inserire una formula passo dopo passo in questo articolo.
Apri Excel. Avrai uno spazio di lavoro di fronte a te, assicurati che la scheda "Home" sia aperta. In una riga vuota situata tra l'intestazione del programma e la sua area di lavoro, vedrai un campo per inserire una formula, ti servirà dopo aver inserito gli argomenti, cioè i numeri che devono essere calcolati.
Ora sai esattamente come creare una formula in Excel e vedi la loro spiegazione nella procedura guidata della funzione.
Se non hai mai lavorato con Excel, scoprirai presto che questo non è solo un foglio di calcolo per l'immissione di numeri. Naturalmente, in Excel, puoi semplicemente contare gli importi in righe e colonne, ma puoi anche calcolare le rate del mutuo, risolvere problemi matematici e ingegneristici e trovare le opzioni più favorevoli a seconda delle variabili date.
In Excel, tutto ciò viene fatto utilizzando le formule nelle celle. Queste formule vengono utilizzate per eseguire calcoli e altre azioni con i dati nel foglio di lavoro. Una formula inizia sempre con un segno di uguale (\u003d), dopodiché è possibile immettere numeri, operatori matematici (come i segni + e - per addizione e sottrazione) e funzioni di Excel integrate che espandono notevolmente le capacità delle formule.
Di seguito è riportato un esempio di una formula che moltiplica 2 per 3 e aggiunge 5 al risultato per ottenere 11.
Di seguito sono riportati esempi di formule che possono essere utilizzate nei fogli di lavoro.
Parti di una formula Excel
Una formula può anche contenere uno o più elementi come funzioni, collegamenti, operatori e costanti.
L'ordine delle azioni nelle formule
In alcuni casi, l'ordine di valutazione può influenzare il valore restituito da una formula, quindi è importante comprendere l'ordine standard di valutazione e sapere come modificarlo per ottenere i risultati desiderati.
Utilizzo di funzioni e funzioni annidate nelle formule di Excel
Le funzioni sono formule predefinite che eseguono calcoli su valori dati - argomenti - in un ordine o modello particolare. Le funzioni possono essere utilizzate per eseguire calcoli sia semplici che complessi. Tutti funzioni di Excel può essere visualizzato nella scheda Formule.
Utilizzo di collegamenti nelle formule di Excel
Un collegamento punta a una cella o a un intervallo di celle in un foglio di lavoro e indica Microsoft Excel, dove si trovano i valori o i dati richiesti dalla formula. Utilizzando i collegamenti, è possibile utilizzare i dati di diverse parti del foglio di lavoro in una formula, nonché utilizzare il valore di una cella in più formule. Inoltre, puoi impostare collegamenti a celle in diversi fogli di una cartella di lavoro o a celle di altri libri. I riferimenti a celle in altri libri sono chiamati collegamenti o xrif.
Utilizzo di nomi nelle formule di Excel
Per denotare celle, intervalli di celle, formule, costanti e fogli di calcolo Excel puoi creare nomi specifici. Un nome è una breve designazione significativa che spiega lo scopo di un riferimento di cella, costante, formula o tabella, poiché può essere difficile da capire a colpo d'occhio. Di seguito sono riportati esempi di nomi e come possono essere utilizzati per rendere le formule più facili da capire.
Esempio 1
Esempio 2
Copia i dati di esempio dalla tabella seguente e incollali nella cella A1 del nuovo foglio di lavoro Excel... Per visualizzare i risultati delle formule, selezionali e premi F2, quindi premi Invio. Inoltre, puoi regolare la larghezza delle colonne in base ai dati che contengono.
Nota: Nelle formule nelle colonne C e D, il nome specifico Vendite viene sostituito con un riferimento all'intervallo A9: A13 e il nome Informazioni sulle vendite viene sostituito con l'intervallo A9: B13. Se il libro non ha questi nomi, le formule in D2: D3 restituiranno l'errore #NOME?.
|
Tipo di esempio |
Un esempio che non usa i nomi |
Un esempio che usa i nomi |
Formula e risultato utilizzando nomi |
|
"\u003d SUM (A9: A13) |
"\u003d SUM (vendite) |
SOMMA (vendite) |
|
|
"\u003d TESTO (CERCA.VERT (MAX (A9: 13), A9: B13,2, FALSO)," gg / mm / aaaa ") |
"\u003d TESTO (CERCA.VERT (MAX (Vendite), Informazioni sulle vendite, 2, FALSO)," dd.mm.yyyy ") |
TESTO (CERCA.VERT (MAX (Vendite), Informazioni sulle vendite, 2, FALSO), "gg.mm.aaaa") |
|
|
Data di vendita |
|||
Per ulteriori informazioni, vedere l'articolo Definire e utilizzare i nomi nelle formule.
Utilizzo di formule di matrice e costanti di matrice in Excel
Una formula di matrice può eseguire più calcoli e quindi restituire un singolo valore o gruppo di valori. Una formula di matrice elabora più set di valori chiamati argomenti di matrice. Ogni argomento della matrice deve contenere lo stesso numero di righe e colonne. Una formula di matrice viene creata allo stesso modo delle altre formule, con la differenza che si utilizza CTRL + MAIUSC + INVIO per immettere la formula. Alcune funzioni incorporate sono formule di matrice e devono essere immesse come matrici per ottenere risultati corretti.
Le costanti di matrice possono essere utilizzate al posto dei riferimenti se non è necessario immettere ciascuna costante in una cella separata sul foglio.
Utilizzo di una formula di matrice per calcolare uno o più valori
Nota: Quando si immette una formula di matrice, Excel la racchiude automaticamente tra parentesi graffe (e). Se provi a inserire manualmente le parentesi graffe, Excel visualizzerà la formula come testo.
Utilizzo di costanti di matrice
In una formula normale, puoi inserire un riferimento a una cella con un valore o al valore stesso, chiamato anche costante. Allo stesso modo, puoi inserire un riferimento a una matrice o una matrice di valori contenuti nelle celle (a volte chiamata costante di matrice) in una formula di matrice. Le formule di matrice accettano le costanti allo stesso modo delle altre formule, tuttavia, le costanti di matrice devono essere immesse in un formato specifico.
Le costanti di matrice possono contenere numeri, testo, valori booleani come VERO o FALSO o valori di errore come # N / A. Una singola costante di matrice può contenere valori di vari tipi, ad esempio (1,3,4; TRUE, FALSE, TRUE). I numeri nelle costanti di matrice possono essere interi, decimali o esponenziali. Il testo deve essere racchiuso tra virgolette doppie, ad esempio "Martedì".
Assicurarsi che i seguenti requisiti siano soddisfatti durante la formattazione delle costanti di matrice.
Le costanti sono racchiuse tra parentesi graffe ( { } ).
Le colonne sono separate da virgole ( , ). Ad esempio, per rappresentare i valori 10, 20, 30 e 40, immettere (10,20,30,40). Questa costante di matrice è una matrice 1 per 4 e corrisponde a un riferimento di una riga e quattro colonne.
I valori delle celle di righe diverse sono separati da punto e virgola ( ; ). Ad esempio, per rappresentare i valori 10, 20, 30, 40 e 50, 60, 70, 80 in celle una sotto l'altra, è possibile creare una costante di matrice 2 per 4: (10,20,30, 40; 50, 60,70,80).
Eliminazione di una formula
Insieme alla formula, vengono eliminati anche i risultati del suo calcolo. Tuttavia, puoi eliminare la formula stessa e lasciare il risultato del suo calcolo come valore nella cella.
Utilizzando i due punti ( : ), i riferimenti alla prima e all'ultima cella dell'intervallo vengono separati. Per esempio: A1: A5.
Argomenti obbligatori specificati
Una funzione può avere argomenti obbligatori e facoltativi (questi ultimi sono indicati nella sintassi tra parentesi quadre). È necessario immettere tutti gli argomenti richiesti. Inoltre, cerca di non inserire troppi argomenti.
La formula non contiene più di 64 livelli di annidamento delle funzioni
I livelli di annidamento non possono superare 64.
I nomi dei libri e dei fogli sono racchiusi tra virgolette singole
Se i nomi dei fogli o delle cartelle di lavoro a cui fai riferimento a celle o valori contengono caratteri non alfabetici, devi racchiuderli tra virgolette singole ( " ).
Viene specificato il percorso per i libri esterni
Numeri inseriti senza formattazione
Non utilizzare i segni del dollaro quando si immettono numeri in una formula, poiché vengono utilizzati per indicare riferimenti assoluti. Ad esempio, invece del valore $1000 bisogno di entrare 1000 .
Importante: I risultati calcolati delle formule e di alcune funzioni del foglio di lavoro di Excel possono differire leggermente su computer Windows x86 o x86-64 e computer Windows RT basati su ARM.
Per molto tempo, tutto il flusso di documenti è stato eseguito utilizzando computer e programmi applicativi speciali. Di norma, quasi tutte le organizzazioni, studenti, ecc., Per digitare o eseguire calcoli, utilizzano una suite software per ufficio sviluppata da Microsoft. Per creare tabelle di alta qualità in cui i valori verranno ricalcolati automaticamente in base a determinate regole, è necessario studiare a fondo formuleExcel con esempiper semplificare i calcoli.
Alcune celle nella tabella vengono calcolate in base alle statistiche immesse in altre celle. Ad esempio, se è necessario calcolare il costo di più prodotti venduti in una fattura, la quantità viene moltiplicata per il prezzo e il risultato è il valore desiderato. A PARTIRE DAL fogli di calcolo è molto più semplice farlo, perché creando un modello e quindi modificando alcuni valori statistici, viene eseguito il ricalcolo. Affinché il risultato sia corretto, è necessario sapere a fondo come inserire correttamente le formule, perché altrimenti il \u200b\u200bcalcolo non sarà corretto. Consideriamo diversi esempi di semplici formule che vengono utilizzate più spesso, mentre dimostreranno chiaramente la correttezza della loro compilazione.
Per l'esempio più semplice, calcoliamo la somma di due valori. Per fare ciò, nella cella A1, scrivi il numero 5 e in B1 il numero 3. Affinché la somma venga calcolata automaticamente, scrivi la seguente combinazione di caratteri nella cella C1, che sarà la formula: \u003d SUM (A1; B1).
Di conseguenza, il valore calcolato verrà visualizzato sullo schermo. Certo, non è difficile calcolare tali valori, ma se il numero di termini è, ad esempio, circa 10, è abbastanza difficile farlo. Quando si modifica uno dei valori inseriti, il risultato verrà ricalcolato automaticamente.
Per non confondere dove e quale formula è stata enunciata, viene visualizzata una riga appena sopra la tabella, in cui è scritto il valore della cella (statistica o formula di calcolo).
Per complicare il compito, introduciamo numeri frazionari nelle celle A1 e B1, che sono molto più difficili da calcolare. Di conseguenza, C1 verrà ricalcolato e verrà visualizzato il valore corretto.
In Excel, puoi fare non solo addizioni, ma anche altre operazioni aritmetiche: sottrazione, moltiplicazione, divisione. La formula dovrebbe sempre iniziare con il simbolo "\u003d" e indicare le cosiddette coordinate delle celle (in questo esempio, queste sono A1 e B1).
Crea tu stesso le formule
Nell'esempio sopra, la formula \u003d SUM (A1; B1) consente di aggiungere due valori. Il simbolo "\u003d" è inteso come l'inizio di una formula, SUM è una parola di servizio che denota il calcolo della somma, A1 e B1 sono le coordinate dei valori statici, che agiscono come termini, e devono essere separati da un punto e virgola ";".
Considera un esempio di aggiunta di tre celle A1, B1, C1. Per questo, viene scritta una formula quasi identica, ma la terza cella è inoltre indicata tra parentesi. Di conseguenza, è necessario scrivere la seguente combinazione \u003d SOMMA (A1; B1; C1) nella cella D1 per eseguire il calcolo.
Se ci sono molti termini che vanno in una riga (colonna), ad esempio 10, la formula può essere semplificata specificando un intervallo di coordinate. Di conseguenza, la formula sarà simile a questa: \u003d SUM (A1: A10). Come cella per visualizzare il risultato desiderato, selezionare la cella B6. Di conseguenza, otterremo il risultato mostrato nello screenshot.
Per moltiplicare tutti i valori indicati nella colonna è necessario scrivere nella formula PRODOTTO invece di SOMMA.
Nota: se l'utente deve eseguire addizioni o altre operazioni aritmetiche con più colonne e righe, è possibile specificare le coordinate diagonalmente opposte e verrà calcolato un array di valori (per calcolare il prodotto dei valori di 10 righe in colonne A, B, C, la formula sarà simile a questa: \u003d PRODUCT (A1: C10)).
Formule combinate
I fogli di calcolo sono in grado di eseguire non solo semplici calcoli aritmetici, ma anche complessi calcoli matematici.
Ad esempio, calcoliamo la somma dei valori e la moltiplichiamo per un fattore di 1,4, se durante l'addizione il valore era inferiore a 90. Se il risultato è maggiore di 90, allora dovrebbe essere moltiplicato per 1,5. In questo caso, la formula contiene già diversi operatori e sembra molto più complicata: \u003d IF (SUM (A1: C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).
Qui sono già state utilizzate due funzioni invece di una: IF e SUM. Il primo comando è un'istruzione condizionale che funziona in questo modo:
- se la somma di tre valori (A1: C1) è maggiore di 90, la moltiplicazione viene eseguita per 1,4, cioè per il primo coefficiente specificato dopo la condizione;
- se (A1: C1) è minore di 90 (come nel nostro esempio), cioè la condizione non è soddisfatta, questo valore viene moltiplicato per il secondo coefficiente.
Nel nostro esempio, ci sono solo due funzioni, ma ce ne possono essere molte di più, il programma è praticamente illimitato in questo.
Funzioni integrate nei fogli di calcolo
Per non prescrivere parole ufficiali, è possibile utilizzare la speciale capacità di installare la funzione senza comporla. Cioè, per calcolare la somma dei valori, è possibile utilizzare questa funzione e selezionare un'azione specifica per l'intervallo di valori specificato. C'è un pulsante speciale per questo, mostrato nello screenshot.
Vale la pena notare che ci sono alcune funzioni lì e molte di esse potrebbero non essere necessarie, ma alcune di esse verranno utilizzate regolarmente.
Tutte le funzioni sono classificate per facilitare la ricerca di quella desiderata.
Funzione CERCA.VERT
Se la tabella è composta da diverse centinaia di righe e non è sempre possibile trovare la posizione desiderata, è possibile utilizzare la funzione CERCA.VERT, che cercherà e restituirà il risultato nella cella in cui è scritta la formula.
Ad esempio, se dobbiamo trovare il cognome, a cui è assegnato il numero di serie 007, dobbiamo seguire i passaggi che sono chiaramente mostrati nello screenshot.
Il valore da cercare viene selezionato come primo argomento. L'intervallo di valori da cercare viene selezionato come secondo argomento. Il numero 2 indica che il valore della seconda colonna dovrebbe essere visualizzato in questa cella.
Il risultato è il seguente risultato.
Valori di arrotondamento
Excel può arrotondare con precisione per semplificare i calcoli. C'è una funzione speciale "ROUNDUP" per questo. Ad esempio, se è necessario arrotondare il numero specificato nella cella A1, immettere nella formula adiacente \u003d ROUNDUP (A1; 0), dove il secondo argomento mostra a quale ordine di cifre decimali è noioso arrotondare (se si specifica zero, il valore totale sarà un numero intero). Il risultato è il seguente risultato.
Come puoi vedere dallo screenshot, si è verificato un arrotondamento. Per eseguire in un più piccolo, c'è una funzione "ROUNDDOWN".
E sebbene a prima vista sembri che i fogli di calcolo Excel siano piuttosto difficili da capire, devi solo lavorarci per un breve periodo e sentire che questa affermazione non è del tutto corretta. Inoltre, i loro benefici sono difficili da sopravvalutare.
Come sapete, l'editor di fogli di calcolo Excel per ufficio è stato originariamente progettato per eseguire calcoli matematici, algebrici e di altro tipo, per i quali viene utilizzato l'inserimento di formule speciali. Tuttavia, alla luce di come scrivere una formula in Excel, è necessario tenere presente che la maggior parte delle operazioni di input sono radicalmente diverse da tutto ciò che è consuetudine utilizzare nella vita di tutti i giorni. Cioè, le formule stesse hanno una forma leggermente diversa e utilizzano operatori completamente diversi che vengono utilizzati durante la scrittura di calcoli standard.
Consideriamo la questione di come scrivere una formula in Excel, utilizzando pochi semplici esempi, senza toccare operazioni complesse, per le quali è necessario studiare il programma abbastanza a fondo da poter essere compreso. Ma anche la conoscenza precedente consentirà a qualsiasi utente di comprendere i principi di base dell'utilizzo di formule per casi diversi.
Come scrivere una formula in Excel: concetti di base
Quindi, l'inserimento dei valori delle formule nel programma è leggermente diverso dalle operazioni standard, dai simboli utilizzati e dagli operatori utilizzati. Quando si risolve il problema di come scrivere una formula in Excel, è necessario basarsi sui concetti di base utilizzati in quasi tutti i sistemi informatici.
Il fatto è che la macchina non capisce l'input di una combinazione come "2 x 2" o mettendo un componente comune fuori dalle parentesi ("2 + 2) 5"). Per questo, vengono forniti diversi tipi di simboli, che sono presentati nella tabella seguente, senza contare gli operatori logici.
In questo caso, la priorità di eseguire le operazioni inizia dal grado e termina con l'addizione e la sottrazione. Inoltre, sebbene Excel possa essere utilizzato come una normale calcolatrice, di norma è necessario specificare i numeri di celle o gli intervalli di celle per i calcoli. Inutile dire che il formato dei dati in qualsiasi cella di questo tipo deve essere impostato su quello appropriato (almeno numerico).
Somma e differenza
Come scrivere una formula di somma o differenza in Excel? Quindi, iniziamo con il più semplice, quando devi calcolare l'importo. Nella riga delle formule (inoltre, per tutte le operazioni), viene prima inserito un segno di uguale, dopodiché viene inserita la formula desiderata. Nel caso di una calcolatrice convenzionale, è possibile specificare per la cella installata "\u003d 2 + 2".
Se la somma viene eseguita per i valori immessi direttamente in altre celle (ad esempio, A1 e A2), la formula diventa "\u003d A1 + A2". Non è raro utilizzare le parentesi per utilizzare operatori aggiuntivi. Per la differenza - la stessa cosa, solo con un meno invece di un più.
Quando è necessario specificare il numero di celle o il loro intervallo, è possibile utilizzare un comando di somma speciale (nella versione russa "SUM", in inglese - SUM). Quando si specificano più celle, appare così: "\u003d SUM (A1; A2)", per un intervallo - "SUM (A1: A10)", a condizione che sia necessario calcolare la somma di tutti i numeri nelle celle dalla prima alla decimo. In linea di principio, se imposti la cella attiva, che si trova immediatamente dopo l'ultima nella colonna con i valori iniziali, non è necessario inserire la formula, ma è sufficiente fare clic sul pulsante della somma automatica.
Moltiplicazione, divisione ed esponenziazione
Ora vediamo come scrivere una formula di moltiplicazione o divisione in Excel. L'ordine è lo stesso di quando si immette una somma o una differenza, solo gli operatori differiscono.
Per un'opera, viene utilizzata la forma "\u003d A1 * A2", per un quoziente - "A1 / A2". A proposito, questi sono esattamente gli stessi comandi che possono essere trovati quando si utilizza una calcolatrice Windows standard.
Il simbolo "^" viene utilizzato per l'elevazione a potenza. Per il valore nella cella A1, che, ad esempio, deve essere quadrato, viene applicata la formula "\u003d A1 ^ 2".
Calcoli percentuali
Con interesse, se non tocchi calcoli complessi, anche tutto è semplice. Come scrivere una formula con percentuali in Excel?
È sufficiente inserire una formula nella forma "\u003d A1 * 5%", dopodiché otterrai proprio il cinque percento del valore nella cella corrispondente.
Utilizzo dell'input di formule basato sulla selezione delle celle
Ma tutto questo riguardava l'assegnazione manuale o il cosiddetto input diretto di formule (input diretto o diretto). In effetti, a volte è utile utilizzare il mouse e il tasto Ctrl.
Tenendo premuto il pulsante del mouse, puoi semplicemente selezionare le celle desiderate inserendo prima il calcolo richiesto nella barra della formula. Le celle verranno aggiunte direttamente alla barra della formula. Tuttavia, a seconda del tipo di formula, a volte le parentesi dovranno essere inserite manualmente.
Tipi di cellule assolute, relative e miste
Va notato separatamente che il programma può utilizzare diversi tipi di celle, per non parlare dei dati che contengono.
Una cella assoluta rimane invariata ed è indicata come $ A $ 1, relativa è un riferimento alla posizione abituale (A1), mista: esiste una combinazione di riferimenti sia alle celle assolute che relative ($ A1 o A $ 1). In genere, tali formati vengono utilizzati durante la creazione di formule quando i dati sono coinvolti in diversi fogli del libro o anche in file diversi. 
VLOOKUP formule
Infine, vediamo come scrivere una formula CERCA.VERT in Excel. Questa tecnica consente di inserire dati da un intervallo a un altro. In questo caso, il metodo è in qualche modo simile a quello utilizzato per risolvere il problema di come scrivere la formula "Condizione" in Excel, che utilizza i simboli mostrati nella tabella sopra.
In generale, tali calcoli sono come un semplice filtro applicato alle colonne, in cui si desidera filtrare solo valori esatti, non valori approssimativi.
In questa opzione, prima tramite il "Wizard Funzione", viene utilizzato il range di valori della tabella originale (prima), nel campo "Tabella" viene indicato il secondo range con fissazione del contenuto (F4), poi la colonna viene indicato un numero e il valore "FALSE" viene impostato nel campo di visualizzazione dell'intervallo se effettivamente, quando si filtra, è necessario ottenere solo valori precisi, non approssimativi. Di norma, tali formule vengono utilizzate maggiormente in magazzino o contabilità, quando non è possibile installare prodotti software specializzati.
Conclusione
Resta da dire che non tutte le formule che possono essere utilizzate nell'editor di fogli di calcolo di Excel sono state descritte qui. Questo, per così dire, è solo le basi. In effetti, se approfondisci la trigonometria o calcoli logaritmi, matrici o anche equazioni tensoriali, tutto sembra molto più complicato. Ma per studiare tutto questo, è necessario studiare a fondo il manuale dell'editor stesso. E non si tratta del fatto che in Excel, in base alla modifica dei dati, è possibile creare anche i giochi logici più semplici. A titolo di esempio, possiamo citare lo stesso "serpente", che inizialmente non aveva nulla a che fare con l'editor del foglio di calcolo, ma è stato riprodotto da appassionati nel loro campo in Excel.
Per il resto, dovrebbe essere chiaro che, dopo aver studiato formule o azioni primitive con i dati, sarà possibile padroneggiare facilmente calcoli più complessi, ad esempio con la creazione di riferimenti incrociati, l'uso di vari tipi di script o Script VB, ecc. Tutto questo richiede tempo, quindi se vuoi studiare il programma e tutte le sue possibilità al massimo, dovrai sudare per la parte teorica.