In questo tutorial, ti spiegheremo come creare una formula complessa in Excel e daremo anche un'occhiata a errori tipici derivanti da utenti inesperti a causa di disattenzione. Se non conosci Excel, ti consigliamo di fare riferimento prima alla lezione in cui abbiamo discusso della creazione di formule semplici.

Come creare una formula complessa in Excel

Nell'esempio seguente, dimostreremo come Excel calcola formule complesse in base all'ordine in cui vengono eseguite le operazioni. In questo esempio, vogliamo calcolare l'importo dell'imposta sulle vendite per i servizi di ristorazione. Per fare ciò, scrivi la seguente espressione nella cella D4: = (D2 + D3) * 0,075... Questa formula aggiungerà il valore di tutti gli articoli nella fattura, quindi moltiplicherà per l'importo dell'imposta sulle vendite 7,5% (svalutato come 0,075).

Excel segue l'ordine e aggiunge prima i valori tra parentesi: (44.85+39.90)=$84.75 ... Quindi moltiplica quel numero per l'aliquota fiscale: $84.75*0.075 ... Il risultato del calcolo mostra che l'imposta sulle vendite sarà $6.36 .

È estremamente importante inserire formule complesse nell'ordine corretto. In caso contrario, i calcoli di Excel potrebbero essere imprecisi. Nel nostro caso, in assenza di parentesi, la moltiplicazione viene eseguita per prima e il risultato non sarà corretto. Le parentesi sono il modo migliore determinare l'ordine dei calcoli in Excel.

Crea formule complesse usando l'ordine delle azioni

Nell'esempio seguente, utilizzeremo i collegamenti insieme ai dati quantitativi per creare una formula complessa che calcoli il costo totale di una fattura alimentare. La formula calcolerà il costo di ogni voce di menu e poi aggiungerà tutti i valori insieme.

Puoi aggiungere parentesi a qualsiasi formula per facilitarne la lettura. Sebbene in questo esempio ciò non cambierà il risultato dei calcoli, possiamo comunque racchiudere la moltiplicazione tra parentesi. Ciò renderà chiaro che viene eseguito prima dell'aggiunta.

Excel non sempre avvisa degli errori delle formule, quindi è necessario controllare tutte le formule da soli. Per sapere come eseguire questa operazione, vedere le Formule di verifica delle lezioni.

Se non hai mai lavorato con Excel prima, scoprirai presto che questo non è solo un foglio di calcolo per l'immissione di numeri. Naturalmente, in Excel puoi solo 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 in base ai valori delle variabili dati.

In Excel, tutto questo viene fatto usando 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 (=), dopo di che è possibile inserire numeri, operatori matematici (come i segni + e - per addizione e sottrazione) e funzioni 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 è possibile utilizzare nei fogli di lavoro.

Parti di una formula Excel

Una formula può contenere anche uno o più elementi come funzione, link, 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 nidificate nelle formule di Excel

Le funzioni sono formule predefinite che eseguono calcoli su determinati valori - argomenti - in un ordine o modello specifico. Le funzioni possono essere utilizzate per eseguire calcoli sia semplici che complessi. Qualunque cosa Funzioni di Excel può essere visto nella scheda Formule.

Utilizzo dei collegamenti nelle formule di Excel

Un collegamento punta a una cella o a un intervallo di celle in un foglio di lavoro e informa Microsoft Excel, dove si trovano i valori o i dati richiesti dalla formula. Utilizzando i collegamenti, è possibile utilizzare i dati in diverse parti del foglio di lavoro in una formula, nonché utilizzare il valore di una cella in più formule. Inoltre, puoi specificare riferimenti a celle in fogli diversi di una cartella di lavoro o a celle di altri libri. I riferimenti alle celle in altri libri sono chiamati collegamenti o xrif.

Utilizzo dei nomi nelle formule di Excel

Puoi creare nomi specifici per fare riferimento a celle, intervalli di celle, formule, costanti e tabelle di Excel. 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 sottostante e incollali nella cella A1 del nuovo Foglio di lavoro Excel... Per visualizzare i risultati delle formule, selezionale 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 Sales viene sostituito con un riferimento all'intervallo A9: A13 e il nome Sales Information viene sostituito dall'intervallo A9: B13. Se il libro non ha questi nomi, le formule in D2: D3 restituiranno l'errore #NAME?.

Tipo di esempio	Un esempio che non usa nomi	Un esempio usando i nomi	Formula e risultato usando i nomi
	"= SOMMA (LA9: LA13)	"= SOMMA (Vendite)	SOMMA (Vendite)
	"= TESTO (CERCA.VERT (MAX (A9: 13), A9: B13,2, FALSE)," gg / mm / aaaa ")	"= TESTO (CERCA.VERT (MAX (vendite), informazioni sulle vendite, 2, FALSE)," gg.mm.aaaa ")	TESTO (CERCA.VERT (MAX (vendite), informazioni sulle vendite, 2, FALSO), "gg.mm.aaaa")
	Data di vendita

Per altre informazioni, vedere l'articolo Definire e usare 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 un gruppo di valori. Una formula di matrice elabora più set di valori chiamati argomenti di matrice. Ogni argomento dell'array 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 utilizzi CTRL + MAIUSC + INVIO per inserire la formula. Alcune funzioni integrate 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 cella separata sul foglio.

Utilizzo di una formula di matrice per calcolare uno o più valori

Nota: Quando inserisci 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 array

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 in una formula di matrice (a volte chiamata costante 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 logici come VERO o FALSO o valori di errore come # N/A. Una costante di matrice può contenere valori di vario tipo, ad esempio (1,3,4; TRUE, FALSE, TRUE). I numeri nelle costanti di matrice possono essere in formato intero, decimale o esponenziale. Il testo deve essere racchiuso tra virgolette, ad esempio "Martedì".

Assicurati 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 a una riga ea 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 l'una sotto l'altra, puoi creare una costante matrice 2 per 4: (10,20,30, 40; 50, 60,70,80).

Eliminare 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.

due punti ( : ), i riferimenti alla prima e all'ultima cella dell'intervallo sono separati. Per esempio: LA1: LA5.

Argomenti richiesti specificati

Una funzione può avere argomenti obbligatori e facoltativi (questi ultimi sono indicati nella sintassi tra parentesi quadre). Tutti gli argomenti richiesti devono essere inseriti. Inoltre, cerca di non inserire troppi argomenti.

La formula non contiene più di 64 livelli di nidificazione delle funzioni

Non possono esserci più di 64 livelli di annidamento.

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 celle o valori contengono caratteri non alfabetici, devi racchiuderli tra virgolette singole ( " ).

Il percorso dei libri esterni è specificato

Numeri inseriti senza formattazione

Non utilizzare i simboli del dollaro quando si immettono numeri in una formula perché vengono utilizzati per indicare riferimenti assoluti. Ad esempio, al posto del valore $1000 bisogno di entrare 1000 .

Importante: I risultati calcolati delle formule e di alcune funzioni del foglio di lavoro Excel potrebbero differire leggermente nei computer Windows x86 o x86-64 e nei computer Windows RT basati su ARM.

In questo post, come nella maggior parte dei post del blog, le tabelle di Excel sono ciò che chiamo gli intervalli di celle a cui viene applicato il comando Scatola - Tavoli - Tavolo ... Puoi leggere di più sullo scopo e sui vantaggi di tale procedura in. Se non hai letto questo post, sarà più difficile per te assorbire le informazioni presentate qui.

Ora diamo un'occhiata più da vicino a come funzionano le formule nelle tabelle.

Linea totale

Per calcolare i totali, le tabelle hanno una riga dei totali. Per visualizzarlo, seleziona qualsiasi cella all'interno della tabella e seleziona la casella sulla barra multifunzione Costruttore - Opzioni stile tabella - Riga totali ... La riga "Totale" apparirà sotto la tabella. Seleziona una cella in questa riga per calcolare i totali delle colonne. Nell'elenco a discesa, selezionare la formula che il programma utilizzerà per calcolare i totali. Ad esempio, Somma, Minimo, Media, ecc. Fare clic su "Altre funzioni..." per selezionare una funzione diversa.

Riga totale in Excel

Puoi anche scrivere una formula per calcolare tu stesso i totali, è scritta allo stesso modo delle formule ordinarie, solo che al posto degli indirizzi delle celle vengono visualizzati i loro nomi dalle intestazioni della tabella. Specificare i riferimenti alle celle con il mouse, il programma sostituirà i loro nomi da solo.

Ricorda che la riga dei totali elabora le celle della tabella che sono visibili sullo schermo. Se hai applicato un filtro per alcuni valori della tabella, i totali verranno ricalcolati solo per i dati selezionati.

Scrivere formule su colonne

Se è necessario eseguire calcoli nelle colonne di una tabella, è ancora più semplice rispetto al normale intervallo di celle. Crea una nuova colonna nella tabella. Posiziona il cursore in qualsiasi cella di questa colonna e annota la formula. Usa il metodo di puntare il mouse sui riferimenti di cella. Il programma visualizzerà i nomi delle colonne invece che nelle formule, questo faciliterà la percezione della formula. Premi Invio e lasciati stupire! Excel non solo calcolerà il risultato, ma copierà anche la formula su tutte le celle della colonna. Non devi nemmeno copiare le formule manualmente!

Formule di Excel

Questa funzione non è sempre utile, quindi puoi disabilitarla. Per fare ciò, quando si inserisce una formula, fare clic sullo smart tag e selezionare " Non creare automaticamente colonne calcolate "

Personalizzazione delle formule della tabella di Excel

Fare riferimento a una tabella per nome

Puoi fare riferimento al contenuto della tabella per nome. Per specificare il nome della tabella, inseriscilo nel campo della barra multifunzione: Costruttore - Proprietà - Nome tabella .

Ora, per eseguire alcune operazioni con i dati della tabella, è possibile fare riferimento ad essa per nome. Ad esempio, la nostra tabella si chiama Persone:

• Formula = Somme (Persone) proverà a sommare tutte le celle della tabella
• Formula = somma (Persone [Sales_Sum]) determinerà la somma dei dati nella colonna "Amount_sales" della tabella "People"
• = Persone [@Sales_Sum] / 100- dividerà per 100 vendite dalla stessa linea

Se i numeri di riga di una cella con una formula e una cella in una tabella non corrispondono, Excel visualizzerà il riferimento tradizionale con le coordinate.

L'utilizzo delle formule nelle tabelle è altamente automatizzato e intuitivo. Mentre molti li troveranno difficili da usare all'inizio, dopo pochi giorni ti abituerai e apprezzerai tutti i vantaggi offerti dai fogli di calcolo Excel.

Grazie per aver letto questo post. E nel prossimo articolo te lo dirò. Ci vediamo sulle pagine del blog.

Come sai, l'editor di fogli di calcolo Excel per ufficio era originariamente destinato all'esecuzione di calcoli matematici, algebrici e di altro tipo, per i quali viene utilizzato l'input 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 fondamentalmente 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, usando alcuni semplici esempi, senza toccare operazioni complesse, per le quali è necessario studiare il programma abbastanza a fondo da capire. Ma anche la conoscenza precedente darà a qualsiasi utente una comprensione dei principi di base dell'uso delle formule per casi diversi.

Come scrivere una formula in Excel: concetti di base

Pertanto, l'immissione dei valori delle formule nel programma è leggermente diversa 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 il componente comune fuori dalle parentesi ("2 + 2) 5"). Per questo, sono forniti diversi tipi di simboli, che sono presentati nella tabella seguente, senza contare gli operatori logici.

In questo caso, la priorità di eseguire operazioni parte dal grado e termina con addizioni e sottrazioni. Inoltre, sebbene Excel possa essere utilizzato come una normale calcolatrice, di norma è necessario specificare i numeri di cella o gli intervalli di celle per i calcoli. Va da sé che il formato dei dati in tale cella deve essere impostato sull'appropriato (almeno numerico).

Somma e differenza

Come scrivere una formula di somma o differenza in Excel? Quindi, iniziamo con il più semplice, quando è necessario calcolare l'importo. Nella riga delle formule (inoltre, per tutte le operazioni), viene prima inserito il segno di uguale, dopodiché viene inserita la formula desiderata. Nel caso di una calcolatrice convenzionale, è possibile specificare per la cella installata "= 2 + 2".

Se la sommatoria viene eseguita per i valori immessi direttamente in altre celle (ad esempio A1 e A2), la formula diventa "= 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 i numeri 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ì: "= SUM (A1; A2)", per un intervallo - "SUM (A1: A10)", a condizione che sia necessario calcolare la somma di tutti i numeri nelle celle dal primo al decimo. In linea di principio, se si imposta la cella attiva, che si trova subito dopo l'ultima nella colonna con i valori iniziali, non è necessario inserire la formula, ma è sufficiente fare clic sul pulsante di somma automatica.

Moltiplicazione, divisione ed elevamento a potenza

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, differiscono solo gli operatori.

Per un'opera, viene utilizzata la forma "= A1 * A2", per il quoziente - "A1 / A2". A proposito, questi sono esattamente gli stessi comandi che si possono trovare quando si utilizza una calcolatrice standard di Windows.

Il simbolo "^" viene utilizzato per l'elevamento a potenza. Per il valore nella cella A1, che, ad esempio, deve essere al quadrato, viene applicata la formula "= 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 del modulo "= A1 * 5%", dopo di che otterrai quel cinque percento del valore nella cella corrispondente.

Utilizzo dell'input della formula basato sulla selezione delle celle

Ma tutto ciò riguardava l'assegnazione manuale o la cosiddetta immissione diretta di formule (immissione diretta o diretta). Talvolta, infatti, è utile utilizzare il mouse e il tasto Ctrl.

Tenendo premuto il pulsante del mouse, puoi semplicemente selezionare le celle desiderate, avendo precedentemente inserito il calcolo richiesto nella barra della formula. Le celle verranno aggiunte direttamente alla barra della formula. Ma, a seconda del tipo di formula, a volte devi mettere le parentesi manualmente.

Tipi cellulari assoluti, relativi e misti

Separatamente, va notato che il programma può utilizzare diversi tipi di celle, per non parlare dei dati che contengono.

Una cella assoluta è invariata ed è indicata come $ A $ 1, relativa è un riferimento alla posizione abituale (A1), mista: esiste una combinazione di riferimenti a celle sia 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.

Formule CERCA.VERT

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 termini generali, tali calcoli sono come un semplice filtro applicato alle colonne quando si desidera filtrare solo valori esatti, non valori approssimativi.

In questa opzione, prima tramite la "Funzione guidata", viene utilizzato l'intervallo di valori della (prima) tabella originale, il secondo intervallo con il fissaggio dei contenuti (F4) viene specificato nel campo "Tabella", quindi la colonna viene indicato il numero e il valore "FALSE" viene impostato nel campo della visualizzazione dell'intervallo se in effetti, durante il filtraggio, è necessario solo ottenere valori accurati, 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 Excel sono state descritte qui. Questo, per così dire, è solo l'essenziale. Infatti, se si approfondisce la trigonometria o si calcolano logaritmi, matrici o anche equazioni tensoriali, tutto sembra molto più complicato. Ma per studiare tutto questo, è necessario studiare a fondo il manuale per l'editore stesso. E non si tratta del fatto che in Excel, in base alla modifica dei dati, puoi creare anche i giochi di logica più semplici. A titolo di esempio, possiamo citare lo stesso "serpente", che inizialmente non aveva nulla a che fare con l'editor di fogli di calcolo, ma veniva riprodotto dagli 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 al massimo il programma e tutte le sue possibilità, dovrai sudare sulla parte teorica.