3 formule pazzesche di Excel che fanno cose incredibili

Microsoft Excel è uno dei più potenti strumenti di foglio di calcolo, con un'impressionante raccolta di strumenti e funzionalità integrate. In questo articolo, imparerai come potenti formule di Excel e la formattazione condizionale possono essere, con tre esempi utili.

Scavando in Microsoft Excel

Abbiamo coperto diversi modi per utilizzare meglio Excel, ad esempio utilizzandolo per creare il proprio modello di calendario o utilizzarlo come strumento di gestione dei progetti.

Gran parte del potere si trova dietro le formule e le regole di Excel che è possibile scrivere per manipolare dati e informazioni automaticamente, indipendentemente da quali dati vengono inseriti nel foglio di calcolo.

Scopriamo come utilizzare le formule e altri strumenti per sfruttare al meglio Microsoft Excel.

Formattazione condizionale con formule

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 controllare l'articolo di Sandy sulla formattazione dei dati in Microsoft Excel con la formattazione condizionale.

Con l'utilizzo di formule di Excel, regole o solo poche semplici impostazioni, puoi trasformare un foglio di calcolo in un dashboard automatico.

Per ottenere la formattazione condizionale, basta fare clic sul Casa scheda e fare clic su Formattazione condizionale icona della barra degli strumenti.

In Formattazione condizionale, ci sono molte opzioni. La maggior parte di questi sono oltre lo scopo di questo particolare articolo, ma la maggior parte di essi riguarda l'evidenziazione, la colorazione o le celle di ombreggiatura basate sui 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 quelle. Ulteriori informazioni su come utilizzare le istruzioni IF in Excel.

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

Quando si fa clic su Gestisci le regole, ti porterà al Gestore regole di formattazione condizionale.

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

Quando si fa clic su Modifica regola, vedrai il dialogo in cui avviene la magia.

Qui è dove è possibile creare la formula logica e le equazioni che visualizzeranno l'icona del cruscotto che si desidera.

Questo dashboard di esempio mostrerà il tempo trascorso su diverse attività rispetto al tempo previsto. Se superi la metà del budget, verrà visualizzata una luce gialla. Se sei completamente fuori budget, diventerà rosso.

Come puoi vedere, questa dashboard mostra che il time budgeting non ha successo.

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

È ora di rimettere a fuoco e gestire al meglio il tuo tempo!

1. Utilizzo della funzione VLookup

Se desideri utilizzare più funzioni di Microsoft Excel avanzate, ecco un altro per te.

Probabilmente hai familiarità con la funzione VLookup, che ti consente di cercare un particolare elemento in una colonna in un elenco e 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 i dati che stai cercando siano sulla destra, ma cosa succede se sono passati?

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

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

Se leggi i forum per utenti di Microsoft Excel troverai molte persone che dicono che ciò non è possibile con VLookup e che devi utilizzare una combinazione di funzioni di indice e corrispondenza per farlo. Questo non è completamente vero.

Puoi far funzionare VLookup in questo modo inserendo una funzione di SCEGLIERE in essa. In questo caso, la formula di Excel sarà simile a questa:

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

Che cosa significa questa funzione è che si desidera trovare la data 6/25/2013 nell'elenco di ricerca e quindi restituire il valore corrispondente dall'indice della colonna.

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

È vero, ma quello che stai facendo con “SCEGLIERE” la funzione sta manipolando i due campi.

Stai assegnando un riferimento “indice” numeri a intervalli di dati - assegnazione delle date all'indice numero 1 e attività per l'indice numero 2.

Quindi, quando digiti “2” nella funzione VLookup, in realtà ti stai riferendo al numero indice 2 nella funzione SCEGLI. Cool, giusto?

Quindi, ora il VLookup usa il Data colonna e restituisce i dati dal Compito colonna, 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 dei dati avanzate, assicurati di controllare l'articolo completo di Dann sulla ricerca dei dati in Excel utilizzando le funzioni di ricerca.

2. Formula annidata alle stringhe di analisi

Ecco un'altra formula pazzesca 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 nome, indirizzo e numero di telefono delimitato dal “;” personaggio.

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

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

"= LEFT (A2, TROVA ("; "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 in 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 dal momento che vogliamo provare a creare la formula nidificata di Excel più pazza possibile (che funzioni effettivamente), useremo un approccio unico.

Per estrarre le parti sulla destra, è necessario nidificare più funzioni DESTRA per afferrare la sezione del testo fino a quella prima “;” simbolo, ed eseguire di nuovo la funzione SINISTRA. Ecco come appare per estrarre la parte del numero civico dell'indirizzo.

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

Sembra pazzo, ma non è difficile da mettere insieme. Tutto ciò 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”.

Questo estrae correttamente la seconda sezione della stringa.

Ogni sezione successiva della stringa necessita di un altro nido creato. Quindi ora prendi il pazzo “DESTRA” equazione che avevi creato per l'ultima sezione, e poi passa a una nuova formula RIGHT con la formula RIGHT precedente incollata su se stessa ovunque vedi “A2”. Ecco come si presenta.

(RIGHT ((RIGHT (A2, LEN (A2) -find ( ";", A2))), LEN ((RIGHT (A2, LEN (A2) -find ( ";", A2)))) - TROVA ( ";", (RIGHT (A2, LEN (A2) -find ( ";", A2))))))

Quindi prendi QUELLA formula e inseriscila nella formula LEFT originale ovunque ci sia un “A2”.

La formula finale per piegare la mente assomiglia a questa:

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

Quella formula estrae correttamente “Portland, ME 04076” fuori dalla stringa originale.

Per estrarre la sezione successiva, ripetere il processo sopra tutto da capo.

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

Sì, questo soddisfa i requisiti per “pazzo”. Ma siamo onesti, c'è un modo molto più semplice per realizzare la stessa cosa con una sola funzione.

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

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

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

Impazzire con Microsoft Excel

Così il gioco è fatto. Le formule di cui sopra dimostrano come una persona può ottenere il massimo 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 realizzare le cose. La maggior parte dei programmatori ti dirà di mantenerla semplice, e questo è vero con le formule di Excel come qualsiasi altra cosa.

Se vuoi davvero fare sul serio con l'utilizzo di Excel, ti consigliamo di leggere la nostra guida per principianti sull'uso di Microsoft Excel Guida per principianti a Microsoft Excel Guida per principianti a Microsoft Excel Utilizzare questa guida per principianti per iniziare l'esperienza con Microsoft Excel. I consigli di base del foglio di calcolo qui ti aiuteranno a iniziare a imparare Excel da solo. Leggi di più . Ha tutto ciò che serve per iniziare ad aumentare la tua produttività con Excel.

Scopri di più su: Microsoft Excel, Microsoft Office 2016, Foglio di calcolo.