3 formule di Excel pazze che fanno cose incredibili

• Produttività

Annuncio pubblicitario

Le formule di Excel possono fare quasi tutto. In questo articolo imparerai quanto possono essere potenti le formule di Microsoft Excel e la formattazione condizionale, con tre esempi utili.

Sblocca subito il cheat sheet "Formule Excel essenziali"!

Questo ti iscriverà alla nostra newsletter

Scavando in Microsoft Excel

Abbiamo esaminato una serie di modi diversi per utilizzare meglio Excel, ad esempio utilizzarlo per creare il tuo modello di calendario o utilizzarlo come strumento di gestione del progetto.

Gran parte del potere sta dietro le formule e le regole di Excel che puoi scrivere per manipolare automaticamente dati e informazioni, indipendentemente dai dati che inserisci nel foglio di calcolo.

Analizziamo come è possibile utilizzare formule e altri strumenti per utilizzare meglio Microsoft Excel.

Formattazione condizionale con formule di Excel

Uno degli strumenti che le persone non usano abbastanza spesso è la formattazione condizionale. Se stai cercando informazioni più avanzate sulla formattazione condizionale in Microsoft Excel, assicurati di leggere l'articolo di Sandy sulla formattazione dei dati in Microsoft Excel con formattazione condizionale.

Con l'uso di formule Excel, regole o solo alcune impostazioni davvero semplici, puoi trasformare un foglio di calcolo in una dashboard automatizzata.

Per accedere alla formattazione condizionale, è sufficiente fare clic sulla scheda Home e fare clic sull'icona della barra degli strumenti Formattazione condizionale .

Sotto Formattazione condizionale, ci sono molte opzioni. La maggior parte di questi va oltre lo scopo di questo particolare articolo, ma la maggior parte riguarda l'evidenziazione, la colorazione o l'ombreggiatura delle celle in base ai dati all'interno di quella cella.

Questo è probabilmente l'uso più comune della formattazione condizionale: cose come trasformare una cella in rosso usando formule inferiori o superiori a. Ulteriori informazioni su come utilizzare le istruzioni IF in Excel.

Uno degli strumenti di formattazione condizionale meno utilizzati è l'opzione Set di icone, che offre un set eccezionale di icone che è possibile utilizzare per trasformare una cella di dati Excel in un'icona di visualizzazione del dashboard.

Quando fai clic su Gestisci regole, verrai indirizzato al Gestore regole di formattazione condizionale .

A seconda dei dati selezionati prima di scegliere il set di icone, vedrai la cella indicata nella finestra Manager, con il set di icone che hai appena scelto.

Quando fai clic su Modifica regola, vedrai la finestra di dialogo in cui avviene la magia.

Qui è possibile creare la formula logica e le equazioni che visualizzeranno l'icona del dashboard desiderata.

Questa dashboard di esempio mostrerà il tempo speso per diverse attività rispetto al tempo previsto. Se superi la metà del budget, verrà visualizzata una luce gialla. Se hai superato il budget, diventerà rosso.

Come puoi vedere, questa dashboard mostra che il budget del tempo non ha esito positivo.

Quasi la metà del tempo viene speso molto oltre gli importi previsti.

È ora di rifocalizzare e gestire meglio il tuo tempo!

1. Utilizzo della funzione VLookup

Se desideri utilizzare funzioni di Microsoft Excel più avanzate, eccone un'altra per te.

Probabilmente hai familiarità con la funzione VLookup, che ti consente di cercare in un elenco un elemento particolare in una colonna e di restituire i dati da una colonna diversa nella stessa riga di quell'elemento.

Sfortunatamente, la funzione richiede che l'elemento che stai cercando nell'elenco sia nella colonna di sinistra e che i dati che stai cercando siano sulla destra, ma cosa succede se vengono cambiati?

Nell'esempio seguente, cosa succede se desidero trovare l'attività che ho eseguito il 25/06/2018 dai seguenti dati?

In questo caso, stai cercando tra i valori a destra e vuoi restituire il valore corrispondente a sinistra, di fronte al modo in cui VLookup funziona normalmente.

Se leggi i forum per utenti professionisti di Microsoft Excel troverai molte persone che affermano che ciò non è possibile con VLookup e che per farlo devi utilizzare una combinazione di funzioni Index e Match. Questo non è del tutto vero.

Puoi far funzionare VLookup in questo modo annidando una funzione SCEGLI. In questo caso, la formula di Excel sarebbe simile a questa:

 "=VLOOKUP(DATE(2018, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" 

Il significato di questa funzione è che si desidera trovare la data 25/06/2013 nell'elenco di ricerca e quindi restituire il valore corrispondente dall'indice di colonna.

In questo caso, noterai che l'indice di colonna è "2", ma come puoi vedere la colonna nella tabella sopra è in realtà 1, giusto?

È vero, ma quello che stai facendo con la funzione "SCEGLI" è la manipolazione dei due campi.

Stai assegnando numeri di "indice" di riferimento a intervalli di dati, assegnando le date all'indice numero 1 e le attività all'indice numero 2.

Pertanto, quando si digita "2" nella funzione VLookup, ci si riferisce effettivamente all'indice numero 2 nella funzione SCEGLI. Bene, vero?

Quindi, ora VLookup utilizza la colonna Data e restituisce i dati dalla colonna Attività, anche se Task è sulla sinistra.

Ora che conosci questo piccolo bocconcino, immagina cos'altro puoi fare!

Se stai cercando di eseguire altre attività di ricerca avanzata dei dati, assicurati di leggere l'intero articolo di Dann sulla ricerca di dati in Excel utilizzando le funzioni di ricerca.

2. Formula nidificata per stringhe di analisi

Ecco un'altra formula pazza di Excel per te.

In alcuni casi è possibile importare dati in Microsoft Excel da un'origine esterna costituita da una stringa di dati delimitati.

Una volta inseriti i dati, si desidera analizzare tali dati nei singoli componenti. Ecco un esempio di informazioni su nome, indirizzo e numero di telefono delimitate dal carattere “;”.

Ecco come puoi analizzare queste informazioni usando una formula di Excel (vedi se riesci a seguire mentalmente insieme a questa follia):

Per il primo campo, per estrarre l'elemento più a sinistra (il nome della persona), dovrai semplicemente utilizzare una funzione SINISTRA nella formula.

 "=LEFT(A2, FIND(";", A2, 1)-1)" 

Ecco come funziona questa logica:

• Cerca la stringa di testo da A2
• Trova il simbolo delimitatore “;”
• Sottrae uno per la posizione corretta della fine di quella sezione di stringa
• Afferra il testo più a sinistra fino a quel punto

In questo caso, il testo più a sinistra è "Ryan". Missione compiuta.

3. Formula nidificata in Excel

Ma per quanto riguarda le altre sezioni?

Potrebbero esserci modi più semplici per farlo, ma poiché vogliamo provare a creare la formula di Nested Excel più folle possibile (che funziona davvero), useremo un approccio unico.

Per estrarre le parti sulla destra, è necessario annidare più funzioni DESTRA per afferrare la sezione di testo fino al primo simbolo “;” ed eseguire nuovamente la funzione SINISTRA. Ecco come appare per estrarre il numero civico dell'indirizzo.

 "=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" 

Sembra folle, ma non è difficile mettere insieme. Tutto quello che ho fatto è stato prendere questa funzione:

 RIGHT(A2, LEN(A2)-FIND(";", A2)) 

E inserito in ogni posto nella funzione SINISTRA sopra dove c'è un "A2".

Ciò estrae correttamente la seconda sezione della stringa.

Ogni sezione successiva della stringa necessita di un altro nido creato. Quindi ora prendi semplicemente la folle equazione "DESTRA" che hai creato per l'ultima sezione, e poi la passi in una nuova formula DESTRA con la precedente formula DESTRA incollata su se stessa ovunque vedi "A2". Ecco come appare.

 (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))) 

Quindi prendi quella formula e la inserisci nella formula LEFT originale ovunque ci sia una "A2".

La formula finale di strabiliante assomiglia a questo:

 "=LEFT((RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), FIND(";", (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), 1)-1)" 

Quella formula estrae correttamente "Portland, ME 04076" dalla stringa originale.

Per estrarre la sezione successiva, ripetere nuovamente la procedura sopra descritta.

Le tue formule di Excel possono diventare davvero complicate, ma tutto ciò che stai facendo è tagliare e incollare formule lunghe in se stesso, creare lunghi nidi che funzionano davvero.

Sì, questo soddisfa il requisito di "pazzo". Ma siamo onesti, c'è un modo molto più semplice per ottenere lo stesso risultato con una sola funzione.

Basta selezionare la colonna con i dati delimitati, quindi nella voce di menu Dati, selezionare Testo in colonne .

Verrà visualizzata una finestra in cui è possibile dividere la stringa per qualsiasi delimitatore desiderato.

In un paio di clic puoi fare la stessa cosa di quella pazza formula sopra ... ma dov'è il divertimento?

Impazzire con le formule di Microsoft Excel

Così il gioco è fatto. Le formule di cui sopra dimostrano quanto una persona può ottenere esageratamente quando crea formule di Microsoft Excel per eseguire determinate attività.

A volte quelle formule di Excel non sono in realtà il modo più semplice (o migliore) per ottenere risultati. La maggior parte dei programmatori ti dirà di mantenerlo semplice, e questo è vero con le formule di Excel come qualsiasi altra cosa. Puoi persino utilizzare funzionalità integrate come Power Query.

Se vuoi davvero seriamente usare Excel, ti consigliamo di leggere la nostra guida per principianti all'utilizzo di Microsoft Excel Guida per principianti a Microsoft Excel Guida per principianti a Microsoft Excel Utilizza questa guida per principianti per iniziare la tua esperienza con Microsoft Excel. I suggerimenti di base sul foglio di calcolo qui ti aiuteranno a iniziare ad imparare Excel da solo. Leggi di più . Ha tutto il necessario per iniziare a migliorare la tua produttività con Excel. Dopodiché, assicurati di consultare il nostro cheat sheet sulle funzioni essenziali di Excel Il cheat sheet sulle funzioni e le formule di Microsoft Excel essenziali Il cheat sheet sulle formule e le funzioni di Microsoft Excel essenziale Scarica questo cheat sheet sulle formule di Excel per prendere una scorciatoia attraverso il programma di fogli di calcolo preferito al mondo. Leggi di più .

Ulteriori informazioni su: Microsoft Excel, Microsoft Office 2016, foglio di calcolo.