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 è alla base delle formule e delle regole di Excel che è possibile scrivere per manipolare dati e informazioni automaticamente, indipendentemente dai dati che si inseriscono 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'uso di formule, regole o solo alcune semplici impostazioni di Excel, puoi trasformare un foglio di calcolo in un dashboard automatico.
Per ottenere la formattazione condizionale, basta fare clic sulla scheda Home e fare clic sull'icona della barra degli strumenti Formattazione condizionale .
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 è l'opzione Set di icone , che offre un grande set di icone che è possibile utilizzare per trasformare una cella di dati Excel in un'icona di visualizzazione dashboard.
Quando fai clic su Gestisci 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 fai clic su Modifica regola , vedrai la finestra di 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 rifocalizzare e gestire al meglio il tuo tempo !
1. Utilizzo della funzione VLookup
Se desideri utilizzare più funzioni avanzate di Microsoft Excel, 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 cambiati?
Nell'esempio seguente, che 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 a destra e vuoi restituire il valore corrispondente a 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 SCEGLI dentro. In questo caso, la formula di Excel sarà simile a questa:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({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 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à per indicizzare il numero 2.
Quindi, quando si digita "2" nella funzione VLookup, ci si riferisce in realtà al numero indice 2 nella funzione SCEGLI. Fantastico, giusto?
Quindi, ora VLookup utilizza la colonna Date e restituisce i dati dalla colonna Task , 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 carattere ";".
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,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 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?
Ci possono essere 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 al primo 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))),FIND(";",(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 l'equazione pazzesca "DESTRA" che hai creato per l'ultima sezione, e poi la passa in 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))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))) Quindi prendi QUELLA formula e inseriscila nella formula LEFT originale ovunque ci sia una "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)))))),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 il processo sopra tutto da capo.
Le tue formule di Excel possono diventare davvero loopy, ma tutto quello che stai facendo è tagliare e incollare lunghe formule in se stesso, fare nidi lunghi che funzionano davvero.
Sì, questo soddisfa il requisito di "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 la voce di menu Dati , selezionare Testo su 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 in questo?
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 veramente fare sul serio con l'utilizzo di Excel, ti consigliamo di leggere la nostra guida per principianti sull'utilizzo di Microsoft Excel . Ha tutto ciò che serve per iniziare ad aumentare la tua produttività con Excel.
Leggi l'articolo completo: 3 formule Crazy Excel che fanno cose incredibili
Fonte: Utilizzare
