Formule Excel avanzate

Elenco delle prime 10 formule e funzioni avanzate di Excel

Puoi scaricare questo modello di formule Excel avanzate qui - Modello di formule Excel avanzate

# 1 - Formula CERCA.VERT in Excel

Questa funzione avanzata di Excel è una delle formule più utilizzate in Excel. È principalmente dovuto alla semplicità di questa formula e alla sua applicazione nella ricerca di un determinato valore da altre tabelle che hanno una variabile comune tra queste tabelle. Supponiamo di avere due tabelle che contengono i dettagli sullo stipendio e il nome del dipendente di un'azienda con ID dipendente come colonna principale. Vuoi ottenere lo stipendio dalla tabella B nella tabella A.

Puoi usare CERCA.VERT come di seguito.

Risulterà nella tabella seguente quando applichiamo questa formula Excel avanzata in altre celle della colonna Stipendio dei dipendenti.

Trascina la formula sul resto delle celle.

Esistono tre principali delimitazioni di CERCA.VERT:

  1. Non è possibile avere una colonna principale a destra della colonna per la quale si desidera popolare il valore di un'altra tabella. In questo caso, la colonna Salario dipendente non può essere precedente all'ID dipendente.
  2. Nel caso di valori duplicati nella colonna principale della Tabella B, il primo valore verrà popolato nella cella.
  3. Se inserisci una nuova colonna nel database (Ex Inserisci una nuova colonna prima dello stipendio del dipendente nella Tabella B), l'output della formula potrebbe essere diverso in base a una posizione che hai menzionato nella formula (Nel caso precedente, l'output sarebbe vuoto)

# 2 - Formula INDICE in Excel

Questa formula Excel avanzata viene utilizzata per ottenere il valore di una cella in una determinata tabella specificando il numero di righe, colonne o entrambi. Ex. Per ottenere il nome di un dipendente alla 5a osservazione, di seguito sono riportati i dati.

Possiamo usare la formula avanzata di Excel come di seguito:

La stessa formula INDICE può essere utilizzata per ottenere il valore lungo la riga. Quando si utilizzano sia il numero di riga che quello di colonna, la sintassi sarebbe simile a questa:

La formula di cui sopra restituirebbe "Rajesh Ved".

Nota: se inserisci un'altra riga nei dati in 5a riga, la formula restituirà "Chandan Kale". Pertanto, l'output dipenderà da eventuali modifiche che si verificano nella tabella dati nel tempo.

# 3 - MATCH Formula in Excel

Questa formula avanzata di Excel restituisce il numero di riga o colonna quando esiste una corrispondenza di una determinata stringa o numero nell'intervallo specificato. Nell'esempio seguente, stiamo cercando di trovare la posizione di "Rajesh Ved" nella colonna Nome dipendente.

La formula sarebbe la seguente:

La funzione CONFRONTA restituirà 5 come valore.

Il terzo argomento viene utilizzato per la corrispondenza esatta. Puoi anche utilizzare +1 e -1 in base alle tue esigenze.

Nota: è possibile combinare INDICE e CORRISPONDENZA per superare la limitazione di CERCA.VERT.

# 4 - SE E Formula in Excel

Ci sono molti casi in cui è necessario creare flag in base ad alcuni vincoli. Conosciamo tutti la sintassi di base di IF. Usiamo questa funzione avanzata IF di Excel per creare un nuovo campo basato su alcuni vincoli su un campo già esistente. Ma cosa succede se dobbiamo prendere in considerazione più colonne durante la creazione di una bandiera. Ex. Nel caso seguente vogliamo contrassegnare tutti i dipendenti il ​​cui stipendio è superiore a 50.000 ma l'ID dipendente è maggiore di 3.

Useremo IF AND in questi casi. Si prega di trovare sotto lo screenshot per lo stesso.

Restituirebbe il risultato come 0.

Possiamo avere molte condizioni o vincoli per creare un flag basato su più colonne usando AND.

# 5 - SE O Formula in Excel

Allo stesso modo, possiamo anche usare la funzione OR in Excel invece di AND se abbiamo solo bisogno di soddisfare una delle condizioni di molte.

Nei casi precedenti, se una qualsiasi di una condizione viene soddisfatta, avremo la cella popolata come 1 altro 0. Possiamo anche sostituire 1 o 0 con alcune sottostringhe con virgolette doppie ("").

# 6 - SUMIF Formula in Excel

In alcune analisi, potrebbe essere necessario filtrare alcune osservazioni quando si applica la funzione somma o conteggio. In questi casi, questa funzione avanzata SUMIF di Excel in Excel è in nostro soccorso. Filtra tutte le osservazioni in base a determinate condizioni fornite in questa formula avanzata di Excel e le riassume. Ex. E se volessimo conoscere la somma degli stipendi dei soli dipendenti che hanno un ID dipendente maggiore di 3.

Applicando la formula SUMIFS:

La formula restituisce i risultati come 322000.

Possiamo anche contare il numero di dipendenti nell'organizzazione con ID dipendente maggiore di 3 quando usiamo COUNTIF invece di SUMIF.

# 7 - CONCATENA Formula in Excel

Questa funzione avanzata di Excel è una delle formule che possono essere utilizzate con più varianti. Questa formula avanzata di Excel ci aiuta a unire più stringhe di testo in una stringa di testo. Ad esempio, se vogliamo mostrare l'ID del dipendente e il nome del dipendente in una singola colonna.

Possiamo usare questa formula CONCATENATE solo per farlo.

La formula sopra risulterà in "1Aman Gupta".

Possiamo avere un'altra variante inserendo un solo trattino tra ID e NAME. Ex. CONCATENATE (B3, "-", C3) risulterà in "1-Aman Gupta". Possiamo anche usarlo in CERCA.VERT quando CERCA nel valore di Excel è una combinazione di più di una variabile.

# 8 - Formula SINISTRA, MEDIA e DESTRA in Excel

Possiamo usare questa formula avanzata di Excel se vogliamo estrarre una certa sottostringa da una data stringa. Le formule menzionate potrebbero essere utilizzate in base alle nostre esigenze. Ex. Se vogliamo estrarre i primi 5 caratteri da Employee Name, possiamo usare la formula LEFT in excel con il nome della colonna e il secondo parametro come 5.

L'output è fornito di seguito:

Anche l'applicazione della formula RIGHT in Excel è la stessa, è solo che guarderemmo il carattere da una parte destra della stringa. Tuttavia, nel caso di una funzione MID in Excel, dobbiamo fornire la posizione iniziale della stringa di testo richiesta e la lunghezza della stringa.

# 9 - Formula OFFSET in Excel

Questa funzione avanzata di Excel con una combinazione di altre funzioni come SOMMA o MEDIA potrebbe essere utile per dare un tocco dinamico ai calcoli. È utilizzato al meglio nei casi in cui stiamo inserendo righe continue in un database esistente. OFFSET Excel ci offre un intervallo in cui dobbiamo menzionare la cella di riferimento, il numero di righe e le colonne. Ex. Se vogliamo calcolare la media dei primi 5 dipendenti dell'azienda in cui abbiamo uno stipendio di dipendenti ordinato per ID dipendente, possiamo fare quanto segue. Il calcolo sotto ci darà sempre stipendiato.

  • Ci darà la somma degli stipendi dei primi 5 dipendenti.

# 10 - TRIM Formula in Excel

Questa formula avanzata di Excel viene utilizzata per ripulire gli spazi non importanti dal testo. Ex. Se vogliamo rimuovere gli spazi all'inizio di un nome, possiamo usarlo utilizzando la funzione TRIM in Excel come di seguito:

L'output risultante sarebbe "Chandan Kale" senza spazio prima di Chandan.