Aggiornato da Ryan Dube nell'ottobre 2017.
Ho sempre creduto che Microsoft Excel sia uno degli strumenti software più potenti in circolazione. Non è solo il fatto che si tratta di un potente foglio di calcolo. Lo stesso Microsoft Excel ha una collezione così impressionante di strumenti e funzionalità integrati. In questo articolo, ho intenzione di mostrarvi come potenti formule e formattazioni condizionali possono essere, con tre esempi utili.
Abbiamo coperto diversi modi per utilizzare meglio Excel, ad esempio utilizzarlo per creare il proprio modello di calendario Come creare un modello di calendario in Excel Come creare un modello di calendario in Excel Avrai sempre bisogno di un calendario. Ti mostriamo come creare il tuo modello personalizzato gratuitamente con Microsoft Excel. Per saperne di più, utilizzalo come strumento per la gestione degli obiettivi 10+ Modelli Excel utili per la gestione e il monitoraggio dei progetti 10+ Modelli Excel utili per la gestione dei progetti e il monitoraggio I modelli sono per i project manager quali sono i coltelli per i cuochi professionisti: indispensabili. Ti mostriamo come è possibile replicare progetti di successo utilizzando modelli già pronti in Microsoft Excel e oltre. Leggi altro e altri modi esclusivi che puoi utilizzare per gestire la tua vita. Basta leggere quegli articoli e vedrai quanto potente possa essere Microsoft Excel.
Gran parte del potere risiede in realtà dietro le formule e le regole che è possibile scrivere per manipolare dati e informazioni automaticamente, indipendentemente da quali dati vengono inseriti nel foglio di calcolo.
Scavando in Microsoft Excel
Con le giuste informazioni, è possibile creare un sistema che calcoli e ricalcoli automaticamente i risultati e i report da tali dati grezzi. Oggi vorrei scavare un po 'più in profondità e mostrarvi come utilizzare alcune delle formule e altri strumenti sottostanti per sfruttare al meglio Microsoft Excel.
Formattazione condizionale fredda con formule
Uno degli strumenti che penso che le persone non utilizzino 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 formattazione condizionale Formatta automaticamente i dati in fogli di calcolo Excel con formattazione condizionale Formatta automaticamente i dati in fogli di calcolo Excel con formattazione condizionale La funzione di formattazione condizionale di Excel consente di formattare singole celle in un foglio di calcolo di Excel in base al loro valore. Ti mostriamo come usarlo per varie attività quotidiane. Leggi di più .
Con l'uso di formule, regole o solo poche semplici impostazioni, puoi trasformare un foglio di calcolo in un cruscotto automatico che ti mostra molte informazioni sul foglio di calcolo con un semplice sguardo.
Per ottenere la formattazione condizionale, basta fare clic sulla scheda Home e fare clic sull'icona della barra degli strumenti "Formattazione condizionale".
Sotto la formattazione condizionale, ci sono un sacco di 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, ad esempio trasformare una cella in rosso utilizzando formule logiche inferiori o superiori a. Brad Jones descrive come utilizzare molti di questi nel suo articolo sulla creazione di una dashboard di Excel Visualizza i tuoi dati e crea i tuoi fogli di calcolo User friendly con una dashboard di Excel Visualizza i tuoi dati e crea i tuoi fogli di calcolo User friendly con un dashboard di Excel A volte, un semplice foglio di calcolo non è abbastanza coinvolgente per rendere i tuoi dati accessibili. Una dashboard ti consente di presentare i tuoi dati più importanti in un formato facile da digerire. Per saperne di più - vale la pena controllare se questo è il tuo obiettivo.
Uno degli strumenti di formattazione condizionale meno utilizzati è l'opzione set di icone, che offre una serie di icone che è possibile utilizzare per trasformare una cella di dati di Excel in un'icona di visualizzazione dashboard.
L'ho scoperto dopo aver aggiornato i motivi di Microsoft Office 13+ che dovresti aggiornare a Microsoft Office 2016 13+ motivi per cui dovresti eseguire l'upgrade a Microsoft Office 2016 Microsoft Office 2016 è qui ed è ora che tu prenda una decisione. La domanda sulla produttività è: dovresti aggiornare? Vi forniamo le nuove funzionalità e i motivi più freschi per aiutarvi ... Leggi di più e utilizzate la formattazione condizionale in Microsoft Excel per la prima volta.
Ho controllato i set di icone e ho visto queste fantastiche luci a LED che avevo visto solo in alcuni dei display di automazione industriale che avevo programmato in passato. 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 ...", viene visualizzata 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. Nel mio esempio, controllo il tempo trascorso su diversi compiti rispetto al mio tempo previsto. Se ho superato la metà del mio budget, voglio che venga visualizzata una luce gialla, e se ho un budget eccessivo, voglio che diventi rosso.
Come puoi vedere, in realtà non sto pagando troppo bene il mio tempo. Quasi la metà del mio tempo è speso molto oltre quello che ho preventivato.
È ora di rimettere a fuoco e gestire meglio il mio tempo Utilizzare la regola di gestione del tempo 80/20 per assegnare priorità alle attività Utilizzare la regola di gestione del tempo 80/20 per assegnare priorità alle attività Come ottimizzare il tempo? Se hai già la priorità, delegare e ancora fatica a fare tutto, dovresti provare la regola 80/20, nota anche come principio di Pareto. Leggi di più !
Cerca elementi con la funzione VLookup
Ok, forse non è abbastanza pazzo per te. Forse non sei così entusiasta delle semplici formule logiche che accendono e spengono le luci. Se ti piace usare le funzioni avanzate di Microsoft Excel, allora ne ho fatto 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/2013 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 Cerca fogli di calcolo Excel più veloci: Sostituisci CERCA.VERT con INDICE e MATCH Cerca fogli di calcolo Excel più veloci: Sostituisci CERCA.VERT Con INDICE e MATCH stai ancora utilizzando VLOOKUP per cercare informazioni nel tuo foglio di calcolo? Ecco come INDICE e MATCH possono fornire una soluzione migliore. Leggi di più . 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 sarà simile a questa:
"=VLOOKUP(DATE(2013, 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 due. Quindi, quando si digita "2" nella funzione VLookup, ci si riferisce in realtà al numero indice 2 nella funzione SCEGLI. Pazzesco, eh?
Così ora VLookup usa la colonna Data e restituisce i dati dalla colonna Task, anche se Task è sulla sinistra. Ora che conosci questo piccolo bocconcino, immagina cosa 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 Trova qualsiasi cosa nel foglio di calcolo Excel con le funzioni di ricerca Trova qualsiasi cosa nel foglio di calcolo Excel con funzioni di ricerca In un gigante Foglio di calcolo Excel, CTRL + F ti porterà solo lontano. Sii intelligente e lascia che le formule facciano il duro lavoro. Le formule di ricerca consentono di risparmiare tempo e sono facili da applicare. Leggi di più .
Insane formule nidificate di stringhe
Come puoi vedere, sto cercando di diventare un po 'più pazzo mentre andiamo, perché so che ci sono alcuni di voi là fuori che pensano: "... beh, non è da pazzi TUTTI !!" Lo so, i tuoi standard sono alti. Sto cercando di essere all'altezza di loro. Ecco un'altra formula pazza per te.
In alcuni casi è possibile importare dati in Microsoft Excel da una fonte 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)" Questo cerca la stringa di testo da A2, trova il simbolo delimitatore ";", sottrae uno per la posizione corretta della fine di quella sezione di stringa, quindi afferra il testo più a sinistra in quel punto. In questo caso, è "Ryan". Missione compiuta.
Nesting Excel Formule
Ma per quanto riguarda le altre sezioni? Bene, 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 possono diventare davvero annebbiate, ma tutto quello che stai facendo è tagliare e incollare lunghe formule in se stesso, fare nidi lunghi che funzionano davvero bene!
Sì, questo soddisfa il requisito di "pazzi", 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 sopra ... ma qual è 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 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. 16 Formule di Excel che ti aiuteranno a risolvere problemi di vita reale 16 Formule di Excel che ti aiuteranno a risolvere problemi di vita reale Lo strumento giusto è metà del lavoro. Excel può risolvere calcoli ed elaborare i dati più velocemente di quanto tu possa trovare la tua calcolatrice. Vi mostriamo le formule chiave di Excel e dimostriamo come usarle. Leggi di più come qualsiasi altra cosa.
Queste formule e queste tecniche erano abbastanza selvagge? Hai dei fantastici trucchi per le formule che hai creato con la tua cassetta degli attrezzi per superutente di Microsoft Excel? Condividi i tuoi input e feedback nella sezione commenti qui sotto!
Immagine di credito: kues / Depositphotos