Formule avanzate di Excel (sommario)
- Introduzione alle formule avanzate in Excel
- Esempi di Formula avanzata in Excel
Introduzione alle formule avanzate in Excel
Una volta raggiunto il livello intermedio in Excel devi lavorare sodo per passare al livello avanzato. Per passare al livello avanzato, è necessario essere consapevoli di alcune delle formule avanzate utilizzate di frequente. In questo articolo tratterò le 10 migliori formule avanzate che tutti gli studenti di Excel devono conoscere. Segui questo articolo per imparare ed esplorare.
Esempi di Formula avanzata in Excel
Comprendiamo come utilizzare le formule avanzate in Excel con alcuni esempi.
Puoi scaricare questo Modello Excel formule avanzate qui - Modello Excel formule avanzateEsempio n. 1 - Funzione VLOOKUP parziale
È necessario che si sia verificata una situazione in cui VLOOKUP genera un errore anche se si è verificata una leggera mancata corrispondenza nel valore di ricerca. Ad esempio, se stai cercando lo stipendio del nome Abhishek Sinha e se hai solo Abhishek, VLOOKUP non può recuperarti i dati.
Tuttavia, utilizzando il carattere asterisco alle due estremità del valore di ricerca, è possibile recuperare i dati per un valore di ricerca parziale.
Nella tabella 1 ho il nome completo e lo stipendio, ma nella tabella 2 ho solo nomi parziali e ho bisogno di trovare lo stipendio di ciascun dipendente.
Passaggio 1: aprire la formula VLOOKUP nella cella E3. Prima di selezionare il valore di ricerca, inserire un asterisco (*) su entrambi i lati del valore di ricerca.
Step 2: Come al solito puoi completare la formula di VLOOKUP ora e avremo i risultati.
Dopo aver utilizzato la formula sopra, l'output viene mostrato di seguito.
La stessa formula viene utilizzata in altre celle.
Esempio n. 2 - COUNTIFS con simboli operatore
Devi aver usato la funzione COUNTIF e IFS per contare le cose nell'elenco. Possiamo anche contare sulla base di simboli dell'operatore come maggiore di (>) minore di (<) e segno uguale (=).
Ora dai un'occhiata ai dati qui sotto per esempio. Se si desidera contare il numero totale di fatture per la regione SUD dopo la data del 10 gennaio 2018, come si conta?
Esempio n. 3 - Condizione IF con condizioni AND & OR
Le funzioni logiche fanno parte delle nostre attività quotidiane. Devi padroneggiarli per passare al livello successivo. Se si sta calcolando il bonus in base a più condizioni, è necessario nidificare la condizione AND o OR con la condizione IF per eseguire il lavoro.
Si supponga di dover calcolare l'importo del bonus per ciascun reparto in base al dipartimento e agli anni di servizio necessari per queste funzioni. Sulla base dei criteri seguenti è necessario calcolare il bonus.
Se il servizio ha una durata superiore a 4 anni e il reparto ha un bonus vendite o supporto è 50000, oppure il bonus è 25000.
Applica sotto la formula per ottenere l'importo del bonus.
Dopo aver utilizzato la formula sopra, l'output mostrato di seguito.
Stessa formula che si applica nella cella E3 a E9.
Esempio n. 4 - Funzione TESTO per renderti dinamico
Supponiamo che tu stia mantenendo una tabella di vendita giornaliera e che devi aggiornare la tabella ogni giorno. All'inizio della tabella, hai un'intestazione che dice "Dati di vendita consolidati da GG-MM-AAAA a GG-MM-AAAA". Come e quando la tabella si aggiorna è necessario modificare la data dell'intestazione. Non è frustrante fare la stessa cosa ancora e ancora? Possiamo rendere dinamica questa direzione usando la funzione TETX, MIN e Max insieme alla e commerciale di simbolo dell'operatore concatenata (&).
Esempio n. 5 - INDICE + PARTITA + MAX per trovare il venditore più alto
Si dispone di un elenco di addetti alle vendite e delle vendite effettuate in base al loro nome. Come si dice chi è il venditore migliore o più alto nell'elenco? Naturalmente, abbiamo diverse altre tecniche per dire il risultato, ma questa funzione può restituire il più alto venditore dal lotto.
Applica la seguente funzione per ottenere il nome di venditore più alto.
Dopo aver utilizzato la formula sopra, l'output mostrato di seguito.
Esempio n. 6: ottenere un conteggio di valori univoci dall'elenco
Se hai molti valori duplicati e ti viene richiesto di dire quanti valori univoci ci sono in che modo? Puoi dirlo rimuovendo il valore duplicato, ma questo non è il modo dinamico di dire il conteggio del valore univoco.
Usando questa funzione array possiamo distinguere i valori univoci dal lotto.
Applica sotto la formula per ottenere un elenco di valori univoci.
Esempio n. 7: utilizzare l'intervallo denominato per rendere dinamico il menu a discesa
Se si lavora spesso con un elenco a discesa e si aggiorna l'elenco a discesa, è necessario tornare all'intervallo dell'elenco di origine per eliminare o aggiungere valori. Successivamente, è necessario tornare alle celle a discesa e aggiornare nuovamente l'intervallo dell'elenco a discesa.
Tuttavia, se è possibile creare un intervallo denominato per l'elenco a discesa, è possibile rendere l'elenco a discesa dinamico e aggiornato.
Crea un intervallo di nomi come mostrato nell'immagine seguente.
Ora vai alla cella a discesa e apri la finestra di dialogo a discesa.
Nella fonte premere il tasto F3 mostrerà tutti i nomi definiti, selezionare il nome dell'elenco a discesa.
Ok, un elenco a discesa è pronto e aggiornerà automaticamente i valori in caso di modifica dell'intervallo dell'elenco a discesa.
Esempio n. 8: eliminare i valori di errore utilizzando la funzione IFERROR
Sono sicuro che hai riscontrato valori di errore mentre lavori con VLOOKUP, calcoli di divisione. La gestione di questi valori di errore è un'attività noiosa. Ma possiamo eliminare questi valori di errore usando la funzione IFERROR nella formula.
Dopo aver utilizzato la formula sopra, l'output mostrato di seguito.
Stessa formula usata in altre cellule.
Esempio n. 9: utilizzare la funzione PMT per creare il proprio grafico EMI
Oggi l'opzione EMI non è una cosa strana per tutti noi. In Excel possiamo stimare il nostro grafico EMI utilizzando la funzione PMT. Segui i passaggi seguenti per creare il tuo grafico.
Applicare la formula seguente nella cella B4 per ottenere l'importo EMI.
Esempio n. 10 - INDICE + PARTITA come alternativa alla funzione VLOOKUP
Spero che tu sia a conoscenza della limitazione della funzione VLOOKUP. Una delle principali limitazioni è che VLOOKUP può recuperare i dati da sinistra a destra, non da destra a sinistra. Non sempre i dati sono ben organizzati e pronti per l'uso. Spesso la colonna del valore che stiamo cercando si trova sul lato sinistro del valore di ricerca, quindi VLOOKUP non aiuta in questi casi.
Una combinazione della funzione INDICE + MATCH serve come alternativa alla funzione VLOOKUP.
Dopo aver utilizzato la formula sopra, l'output viene mostrato di seguito.
La stessa formula viene utilizzata in altre celle.
In base all'ID prodotto è necessario estrarre i valori delle vendite. Nella tabella principale, l'ID prodotto si trova sul lato destro della colonna delle vendite. Quindi VLOOKUP non può recuperare i dati. Utilizzare la formula seguente per recuperare i dati.
Articoli consigliati
Questa è una guida alle formule avanzate in Excel. Qui discutiamo come utilizzare le formule avanzate in Excel insieme ad esempi pratici e modelli Excel scaricabili. Puoi anche consultare i nostri altri articoli suggeriti:
- Come utilizzare la funzione INDICE di Excel?
- Excel avanzato | Funzione database
- Esempi sulla formula TRIM in Excel
- Guida alla formula OFFSET di Excel