17 formule avanzate di Excel: un must per tutti i professionisti

Se ti ritrovi spesso a flettere le mani e a perdere ore per fare le cose manualmente in Excel, ti stai perdendo quanto può essere potente Excel se sai come usarlo nel modo giusto. Microsoft Excel è noto per le sue formule che possono funzionare in modo efficiente su un gran numero di dati nella cella, senza che sia necessario molto coinvolgimento manuale nel processo.

Abbiamo compilato 17 formule avanzate di Excel che hanno una straordinaria capacità di trasformare lunghe attività banali e manuali in pochi secondi di lavoro. Sono formule che ogni professionista dovrebbe avere a portata di mano per risparmiare tempo prezioso o per impressionare il proprio capo in un momento critico.

Scorri la lista e dicci il tuo preferito!

Gli studenti ricevono un aumento medio dello stipendio del 58% con il massimo fino al 400%.

1. CORRISPONDENZA DELL'INDICE

Formula = INDICE(C3:E9,MATCH(B13, C3:C9,0),MATCH(B14,C3:E3,0))

Un'ottima alternativa alla formula CERCA.VERT o CERCA.VERT su Excel che presenta alcuni inconvenienti per l'esecuzione di attività di ricerca. L'INDEX MATCH è una formula combinata di 2 funzioni separate:

INDICE: questa formula restituisce il valore di una cella in una tabella in base alla colonna e al numero di riga.

CORRISPONDENZA: questa formula restituisce la posizione di una cella in una riga o in una colonna.

Un esempio della combinazione delle formule INDICE e CORRISPONDENZA è: Quando si cerca in alto e si restituisce l'altezza di una persona in base al nome, è possibile utilizzare questa formula per modificare entrambe le variabili (in questo caso, nome e altezza) utilizzando la formula CORRISPONDENZA INDICE .

Fonte

Analizziamolo passo dopo passo:

Come combinare INDEX e MATCH

• Digitare INDICE
• Seleziona l'area che vuoi indicizzare
• Bloccare l'area con F4
• Trova la riga da cui vuoi cercare i dati
• Digitare MATCH con la serie di informazioni e bloccare l'area con F4
• Digitare 0 per la corrispondenza esatta, chiudere le parentesi
• Premi Invio

Idee interessanti per progetti di scienza dei dati

Ora hai un insieme completamente dinamico e funzionale di colonne e righe nel foglio in cui tutta la pettinatura delle celle e delle righe per i dati corretti viene eseguita automaticamente.

2. MEDIA

Per eseguire la formula media per trovare la media di qualsiasi intervallo di numeri, ecco i passaggi che devi seguire:

Inserisci i valori, le celle o la scala delle celle che stai calcolando nel formato.

La formula inizia con =MEDIA(numero1, numero 2, ecc.)

Se vuoi eseguire la media di un intervallo di celle nel foglio, ecco i passaggi che devi seguire:

• Inserisci i valori, le celle o la scala delle celle che stai calcolando nel formato, proprio come hai fatto sopra
• La formula deve essere come =MEDIA (Valore iniziale: Valore finale).

Se ti stai chiedendo come inserire i valori per la regione delle celle, puoi selezionare l'area del tuo foglio con il mouse e bloccarla con F4. Questo farà in modo che Excel faccia il resto del lavoro per te senza fare alcuna somma e divisione della figura con il numero di elementi nel gruppo.

3. SOMMA

Questa formula in Excel è indicata come SUMIF(intervallo, criteri, [intervallo di somma]). Ciò comporterebbe la somma dei valori all'interno dell'intervallo di celle desiderato che soddisferebbe i requisiti impostati dall'utente. Ad esempio, =SOMMA.SE(C3: C12, “>70.000) restituirebbe la somma dei valori tra le celle di C3 e C12 solo dalle celle che hanno un valore superiore a 70.000.

Nel caso di calcoli finanziari, salariali o anche dei costi, è necessario il valore dei lead associati a dipendenti specifici determinato dalla condizione da te impostata. Farlo manualmente richiede molto tempo e rallenta le cose.

La formula per la condizione precedente può essere =SOMMA.SE(intervallo, criteri, [intervallo_somma]), dove Intervallo può essere definito come l'intervallo del foglio da cui è necessario scegliere i valori.

[sum_range] è definito come l'intervallo aggiuntivo o facoltativo che si intende sommare in aggiunta al primo intervallo inserito.

Ecco un esempio:

Fonte

4. OFFSET COMBINATO CON SOMMA

Da sola, la funzione OFFSET potrebbe non essere utile, ma se combinata con altri servizi, puoi ottenere una formula complessa con risultati più rapidi se vuoi creare un ruolo dinamico che può sommare qualsiasi numero variabile di celle, la normale formula SOMMA, che è static deve essere combinato con la funzione OFFSET.

Quindi, per scoprire la somma dei valori nelle celle variabili, la formula dovrebbe essere:

=SOMMA(B4:OFFSET(B4,0,E2-1))

Fonte

Qui il riferimento finale della funzione SUM viene sostituito con la funzione OFFSET. La formula SOMMA che inizia in B4 termina con una variabile ed è una formula OFFSET che inizia da B4, continuando per il valore in E2 ("3") meno uno. Questo aiuta il metodo a spostare due celle e sommare tre anni di dati. Nel riferimento sopra, puoi vedere che la cella F7 contiene la somma delle celle B4: D4 = 15.

5. SE E

Questa formula è utile in situazioni in cui è necessario creare determinate condizioni per esaminare un mucchio di dati. L'istruzione IF consente di utilizzare la funzione IF avanzata di Excel per creare un nuovo campo basato su queste condizioni su una traccia già esistente.

Ad esempio, se vuoi contrassegnare i dipendenti con stipendi superiori a 50.000 e ID dipendente maggiore di 3, la formula sarà:

SE(E(E4>3,F4>50000)1,0)

Fonte

Poiché non ci sono casi reali nei dati sopra, la formula restituirebbe il valore 0.

6. CONCATENA

Se hai molte stringhe di testo nel tuo foglio dati e desideri far apparire i dati in una riga, questa formula è la tua scelta. Ad esempio, se si desidera rappresentare l'ID dipendente e il nome del dipendente in un'unica colonna, il metodo dovrebbe essere:

=CONCATENA(B3, C3)

Fonte

7. PMT

Questa funzione ti aiuterà a capire i pagamenti futuri dovuti per un prestito, dati un tasso di interesse, un importo capitale e una durata specifici del prestito. Ecco cosa ti serve per iniziare:

• La durata del prestito
• Il capitale iniziale (denaro) del prestito
• Il valore futuro
• Il tipo di prestito

Ora, se vuoi trovare la rata mensile per un importo principale, la formula per lo stesso sarà:

= PMT (tasso, per, PV, [fv], [tipo])

Capiamolo con un esempio:

Fonte

=PMT(C2/12.B2.A2)

E la parte migliore è che puoi trascinare l'angolo in basso a destra delle celle PMT attraverso i campi per calcolare automaticamente l'importo del pagamento mensile per tutti i campi!

Tipi popolari di lavori di scienza dei dati

8. RIFILA

Questa è una delle formule più utilizzate in Excel che pulisce qualsiasi spazio indesiderato nei campi. Ad esempio: se devi rimuovere gli spazi all'inizio di un nome, puoi farlo usando la funzione TRIM:

=TRIM(C6)

Fonte

Questo ti restituirebbe il valore di Chandan Kale senza spazi aggiuntivi attaccati alla parte anteriore o alla fine.

9. LENTE

Questa è una funzione che ti dice il numero di caratteri in una stringa di testo. Uno dei modi più interessanti per usarlo è, ad esempio, se vuoi evidenziare i donatori che hanno donato oltre $ 1.000 contando il numero di cifre nella colonna della donazione, la formula sarà:

=LUNGHEZZA(B2)

Fonte

E se vuoi evidenziare tutte le celle nella colonna Donazione, devi:

• Seleziona la scheda Home nel menu
• Fare clic su Formattazione condizionale (nella barra degli strumenti)
• Selezionare Usa una formula per determinare quali celle formattare

Fonte

Qui, se si imposta la condizione "> 3", qualsiasi cosa superiore a $ 1.000 riceverà la formattazione univoca, che puoi scegliere facendo clic sull'opzione Formato.

10. SCEGLI

Questa è un'ottima funzione per l'analisi degli scenari nella modellazione finanziaria. Ti consente di scegliere tra un numero specifico di opzioni e restituire la "scelta" che hai selezionato. Ad esempio, se disponi di tre valori diversi per la crescita dei ricavi l'anno prossimo in base a ipotesi, utilizzando la funzione SCEGLI, puoi restituire l'importo in base alle tue esigenze.

La formula è:

=SCEGLI(C7,D3,D4,D5)

Fonte

11. CELLA, SINISTRA, MEDIA e DESTRA

Tutte le funzioni di cui sopra possono essere combinate in molti modi per ottenere alcune formule avanzate.

• La funzione CELLA viene utilizzata per restituire diversi tipi di informazioni sul contenuto della cella
• Il servizio LEFT viene utilizzato per sostituire il testo dall'inizio della cella.
• La funzione MID può restituire testo da qualsiasi punto iniziale della cella.
• La funzione DESTRA restituisce solo il testo dalla fine della cella.

Fonte

12. XNPV e XIRR

Per tutti gli analisti che si imbattono in investment banking, ricerca azionaria o qualsiasi altra area della finanza aziendale che richieda lo sconto dei flussi di cassa, queste formule ti faranno sicuramente risparmiare un sacco di tempo e brontolare!

Domande e risposte sull'intervista sulla scienza dei dati

Ad esempio, se si desidera calcolare il valore attuale netto (NPV) per qualsiasi investimento utilizzando un determinato tasso di attualizzazione e flussi di cassa che si verificano a intervalli irregolari, utilizzare la seguente funzione.

=XNPV(tasso di sconto, flussi di cassa, date)

Fonte

D'altra parte, la funzione XIRR ti dice il tasso di rendimento interno (dove deflusso = afflusso) per una serie di flussi di cassa che si verificano a intervalli irregolari, come ad esempio:

13. CONTA ( )

Gli analisti si trovano spesso in punta di piedi che lottano per trovare il numero di celle con valori (numeri, errori, testo, valori logici) e quelle vuote. La funzione COUNTA( ) può aiutarti a trovare il nome delle celle non vuote in un intervallo selezionato. Ad esempio, la formula per contare i valori per un foglio dati con colonne A1-A10 è:

=CONTA.A(A1: A10)

Fonte

14. FV

Questa formula è utile se stai cercando di investire denaro in qualcosa e di conoscerne il valore. I requisiti della formula FV sono:

• Il tasso di interesse del prestito
• Numero di pagamenti
• Il pagamento per ogni periodo
• Saldo iniziale attuale
• Tipo di prestito

Ad esempio, se vuoi confrontare diversi CD vergini e hai un'eredità di $ 20.000 da investire in un CD. I tassi di interesse sono rappresentati in formato decimale; i pagamenti sono zero. La formula per lo scenario sarà:

=VV(LA2/12,LA2,C2,RE2)

Fonte

I risultati saranno:

Fonte

15. TRA

Questa funzione aiuta a selezionare un numero all'interno di un intervallo predefinito di numeri in modo casuale. Dopo aver inserito i numeri più bassi e più alti nella formula, Excel può scegliere i dati corretti dai campi a cui sono allegati i nomi nell'intervallo e sceglierli casualmente. Il metodo per lo scenario è:

=RANDBETWEEN(punto di partenza, punto di arrivo)

Fonte

16. PICCOLO

La funzione PICCOLO in Excel restituisce valori numerici in base alla posizione del valore in un elenco classificato per importanza. Questa funzione aiuta a recuperare "l'ennesimo valore più piccolo" da una matrice o da un intervallo di celle come il valore più piccolo, il 2° valore più basso, il 3° valore più basso, ecc.

La sintassi della formula è

=PICCOLO (tempi, intervallo)

Per esempio,

Fonte

Poiché la funzione SMALL è automatica, è necessario fornire un intervallo e un numero intero per 'nesimo' per specificare il valore classificato. I nomi ufficiali di questi argomenti sono "array" e "k".

17. QUARTILE

Questa funzione restituisce il quartile (ciascuno di quattro gruppi uguali) in un determinato insieme di dati e può restituire il valore minimo, il primo quartile, il valore massimo del secondo quartile. Questa funzione porta la quantità quartile dei campi in una matrice. La funzione restituisce un valore numerico in base al percentile richiesto.

Sintassi: =QUARTILE (array, quart)

Fonte

CONCLUSIONE:

Microsoft Excel è abbastanza inevitabile quando si tratta del posto di lavoro del 21° secolo, ma il trucco è che non deve essere così scoraggiante. Rendilo tuo amico e guarda la tua produttività aumentare!