15 formule di Excel per problemi della vita reale

Abbiamo selezionato 15 formule semplici, potenti e che ti aiutano a risolvere problemi complessi.

Formule finanziarie

Shopping per una nuova casa e confuso da tutto il gergo dei mutui? Alla ricerca di una nuova auto e confuso dai termini del prestito auto il venditore continua a lanciarti?

Non avere paura. Prima di prendere un prestito, fai le tue ricerche con Excel al tuo fianco!

1. PMT: pagamento

Ogni volta che si confrontano i termini di un prestito e si desidera capire rapidamente il proprio pagamento mensile dato le diverse variazioni in termini, sfruttare la potente (e semplice) formula PMT .

Ecco cosa ti serve per usare questa formula:

• Il tasso di interesse del prestito

• La durata del prestito (quanti pagamenti?)

• Il principio di partenza del prestito

• Valore futuro, se per qualche motivo il prestito sarà considerato pagato prima che raggiunga lo zero (facoltativo)

• Tipo di prestito: 0 se i pagamenti sono dovuti alla fine di ogni mese o 1 se sono dovuti all’inizio (facoltativo)

Ecco un modo interessante per confrontare rapidamente una varietà di prestiti per vedere come saranno i tuoi pagamenti. Crea un foglio Excel che elenca tutti i potenziali prestiti e tutte le informazioni disponibili su di essi. Quindi, crea una colonna “Pagamenti” e utilizza la formula PMT .

Basta afferrare l’angolo in basso a destra della cella PMT appena creata e trascinarla verso il basso in modo da calcolare il totale del pagamento per tutti i termini di prestito elencati nel foglio. La funzione di compilazione automatica di Excel è una funzionalità che utilizzerai molto con questi trucchi.

Ora puoi confrontare i pagamenti mensili per diversi tipi di prestiti.

(Un grande ringraziamento a Mark Jones (@redtexture su Twitter) che ha sottolineato che per le formule PMT e FV, devi stare molto attento all’uso dello stesso periodo, in questo caso l’utilizzo dei pagamenti mensili richiede di dividere il periodo di interesse entro 12 mesi)

Ecco perché i nostri lettori sono così fantastici. Grazie per l’aiuto con questa correzione Mark!

2. FV: valore futuro

La formula successiva è utile quando stai cercando di investire un po ‘di denaro in qualcosa come un certificato di deposito (CD) e vuoi sapere quanto varrà alla fine del termine.

Ecco cosa devi sapere per utilizzare la formula FV :

• Il tasso di interesse del prestito

• Numero di pagamenti (o termine di investimento in mesi)

• Il pagamento per ogni periodo (di solito mensile)

• Saldo iniziale corrente (facoltativo)

• Tipo di prestito: 0 se i pagamenti sono dovuti alla fine di ogni mese o 1 se sono dovuti all’inizio (facoltativo)

Quindi confrontiamo diversi CD usando i termini che conosci dalle informazioni che le banche ti hanno dato. Nell’esempio seguente, supponiamo che tu abbia un patrimonio di $ 20.000 da investire in un CD.

I tassi di interesse sono nuovamente rappresentati in formato decimale (prendi il tasso di interesse che la banca ti ha dato e dividi per 100). I pagamenti sono pari a zero perché i CD si basano generalmente su un valore iniziale e su un valore futuro erogati. Ecco come appare il confronto quando usi la formula FV per ogni CD che stai considerando.

Senza dubbio, il CD di maggiore interesse per un periodo di tempo più lungo paga molto di più. L’unico inconveniente è che non puoi toccare i tuoi soldi per tre anni interi, ma questa è la natura dell’investimento!

3-4. Formule logiche — IF e AND

La maggior parte delle banche in questi giorni ti dà la possibilità di scaricare transazioni bancarie del valore di quasi un anno in un formato come CSV. Questo è un formato perfetto per analizzare le tue spese usando Excel, ma a volte i dati che ricevi dalle banche sono molto disorganizzati.

L’uso di formule logiche è un ottimo modo per individuare le spese eccessive.

Idealmente, la banca classifica automaticamente le tue spese o hai impostato il tuo account in modo che le cose vengano inserite in categorie di spesa. Ad esempio, tutti i ristoranti che visitiamo vengono etichettati con l’etichetta DiningOut .

Ciò semplifica l’uso di una formula logica per identificare ogni volta che siamo usciti a mangiare e abbiamo speso oltre $ 20.

Per fare ciò, basta creare una formula logica in una nuova colonna alla ricerca di qualsiasi valore in cui la colonna della categoria è “DiningOut” e la colonna della transazione è maggiore di – $ 20

Nota: il confronto seguente mostra “<“, minore di, perché i valori nella colonna C sono tutti negativi.

Ecco come appare:

Usare IF e AND insieme in una formula sembra complicato, ma in realtà è abbastanza semplice. L’istruzione IF genererà l’importo in dollari (C2) se l’istruzione AND è vera o FALSE in caso contrario. L’istruzione AND verifica se la categoria è “DiningOut” e la transazione è superiore a $ 20.

Ecco qua! Senza dover esaminare manualmente tutte quelle transazioni, ora sai esattamente quelle volte in cui hai speso in una determinata categoria.

Dare un senso agli elenchi

Le liste sono una parte importante della vita di tutti i giorni. Se gestisci una famiglia, usi costantemente gli elenchi. Excel ha alcuni strumenti piuttosto potenti per essere produttivi con le liste di controllo , così come altri tipi di formati di elenco.

5-6. COUNT e COUNTIF

Excel può aiutarti a organizzare rapidamente e ordinare i valori è un elenco. Facciamo l’esempio PTC. Ecco un elenco di donazioni dai membri della comunità.

Vogliamo vedere quante volte il nome di una persona compare nell’elenco. Per fare ciò, puoi combinare la formula COUNT con una formula IF . Innanzitutto, crea una colonna per verificare se la persona è Michelle o meno. La formula utilizzerà un’istruzione IF per riempire la cella con un “1” se questo è vero.

Quindi, crea un’altra colonna che conta quante volte hai trovato Michelle Johnson nell’elenco.

Questo ti dà il conteggio di ogni posto nella colonna E dove c’è un 1 anziché uno spazio vuoto.

Quindi, questo è il modo più semplice per fare questo tipo di cose, ma richiede due passaggi.

6-8. SUMIF, COUNTIF, MEDIA

Se non ti dispiace usare una formula leggermente più avanzata, potresti prendere in considerazione l’uso di una delle molte formule combinate “IF” come SUMIF , COUNTIF o AVERAGEIF . Questi consentono di eseguire la formula (COUNT, SUM o MEDIA) se la condizione logica è vera. Ecco come funziona usando l’esempio sopra.

Questa formula esamina la colonna A, che contiene tutti i nomi dei donatori e se la cella all’interno dell’intervallo corrisponde ai criteri tra virgolette, conta per uno. Questo ti dà un conteggio di tutte le volte che il nome del donatore equivale a “Michelle Johnson” in un solo passaggio.

È molto più veloce rispetto all’utilizzo di due colonne, ma è un po ‘complesso, quindi utilizza l’approccio che funziona meglio per la tua situazione.

Le formule SUMIF e AVERAGEIF funzionano allo stesso modo, solo con risultati matematici diversi. L’uso di SUMIF in questo esempio ti darebbe il totale delle donazioni per Michelle Johnson se invece lo usi.

9. LEN

Un’altra formula che è possibile utilizzare in modo creativo a volte è la formula LEN. Questa formula è una delle tante formule di testo di Excel che ti dice quanti caratteri ci sono in una stringa di testo.

Un modo interessante di utilizzarlo nell’esempio sopra sarebbe quello di evidenziare i donatori che hanno donato oltre $ 1.000 contando il numero di cifre nella colonna delle donazioni. Se la lunghezza del numero è 4 o superiore, hanno donato almeno $ 1.000.

Ora puoi aggiungere una formattazione aggiuntiva per semplificare la vista.

Per fare ciò, è necessario evidenziare tutte le celle nella colonna Donazione, selezionare la scheda Home nel menu e fare clic su Formattazione condizionale nella barra degli strumenti. Quindi seleziona Usa una formula per determinare quali celle formattare .

Impostare l’intervallo in Valori formato in cui questa formula è vera: sulla colonna / intervallo in cui vengono visualizzati tutti gli output della formula LEN.

In questo esempio, se si imposta la condizione “> 3”, qualsiasi cosa superiore a $ 1.000 riceverà la formattazione speciale. Non dimenticare di fare clic sul pulsante Formato … e scegliere il tipo di formattazione speciale che desideri per questi.

Inoltre, una breve nota. Noterai che il mio intervallo è definito come “$ E2: $ E11”, non “$ E $ 2: $ E $ 11”. Quando si seleziona l’intervallo, il valore predefinito è il primo, che non funzionerà. È necessario utilizzare l’indirizzamento relativo come mostrato nella figura sopra. Quindi, la formattazione condizionale funzionerà in base alle condizioni del secondo intervallo.

Organizzazione di download bancari e finanziari

A volte, quando scarichi informazioni dalle aziende, che si tratti della tua banca o della tua compagnia di assicurazione sanitaria, il formato dei dati in arrivo non corrisponde sempre a quello che ti serve.

Ad esempio, supponiamo che nei dati esportati dalla tua banca e ti venga data la data nel formato standard.

Se vuoi aggiungere una tua nuova colonna con la tua che è preceduta dall’anno e include le informazioni del Beneficiario (per i tuoi scopi di ordinamento), estrarre informazioni da una colonna è davvero facile.

10-14. DESTRA, SINISTRA, TESTO e CONCATENATO

Puoi estrarre l’anno dal testo in quella colonna usando la formula DESTRLa formula sopra sta dicendo a Excel di prendere il testo nella colonna D ed estrarre i quattro caratteri dal lato destro. La formula CONCATENA unisce le quattro cifre, con il testo del beneficiario dalla colonna E.

Tieni presente che se desideri estrarre il testo da una data, dovrai convertirlo in formato testo (anziché data) utilizzando la formula “= TEXT (D2,” mm / gg / aaaa “)”. Quindi puoi usare la formula DESTRA per tirare fuori l’anno.

Cosa succede se le tue informazioni sono a sinistra? Bene, invece usa la formula SINISTRA e puoi estrarre il testo da sinistra a destra.

CONCATENATE è davvero utile quando hai del testo da un gruppo di colonne diverse che vuoi mettere insieme in una lunga stringa. Esistono anche alcuni modi per separare il testo in Excel se si desidera imparare a manipolare completamente le stringhe.

Scegliere nomi casuali da un cappello

15. ESTRATTO

Un’ultima formula divertente è quella che puoi usare se devi fare qualcosa come scegliere alcuni nomi da un cappello per una festa di Natale. Metti via quel cappello e quei frammenti di carta e tira fuori il tuo laptop e avvia Excel!

Utilizzando la formula RANDBETWEEN, puoi fare in modo che Excel selezioni casualmente un numero tra un intervallo di numeri specificato.

I due valori che devi utilizzare sono i numeri più bassi e più alti, che dovrebbero trovarsi alla fine dell’intervallo di numeri che hai applicato al nome di ogni persona.

Dopo aver premuto il tasto Invio, la formula selezionerà casualmente uno dei numeri all’interno dell’intervallo.

È quasi casuale e a prova di manomissione che puoi ottenere. Quindi, invece di scegliere un numero da un cappello, scegli un numero da Excel!

Utilizzo di Excel per problemi quotidiani

Come puoi vedere, Excel non è solo per guru dell’analisi dei dati e professionisti. Chiunque può beneficiare delle numerose formule che troverai nascoste in Excel . Scopri queste formule e puoi iniziare a risolvere i problemi della vita reale in Excel.

Non smettere di imparare Excel. C’è un lungo elenco di formule e funzioni di Excel che puoi imparare a usare, potresti trovare alcuni piccoli trucchi che non avresti mai pensato che Excel potesse fare.