Ricerche, grafici, statistiche e tabelle pivot
Dopo aver esaminato le funzioni di base, i riferimenti di cella e le funzioni di data e ora, ci tuffiamo ora in alcune delle funzionalità più avanzate di Microsoft Excel. Presentiamo metodi per risolvere problemi classici in finanza, rapporti di vendita, costi di spedizione e statistiche.
NAVIGAZIONE SCOLASTICA- Perché hai bisogno di formule e funzioni?
- Definizione e creazione di una formula
- Riferimento relativo e assoluto della cella e formattazione
- Funzioni utili da conoscere
- Ricerche, grafici, statistiche e tabelle pivot
Queste funzioni sono importanti per le imprese, gli studenti e coloro che vogliono solo saperne di più.
CERCA.VERT e CERCA.ORIZZ
Ecco un esempio per illustrare le funzioni di ricerca verticale (VLOOKUP) e di ricerca orizzontale (HLOOKUP). Queste funzioni sono utilizzate per tradurre un numero o un altro valore in qualcosa che è comprensibile. Ad esempio, è possibile utilizzare VLOOKUP per prendere un numero parte e restituire la descrizione dell'oggetto.
Per indagare su questo, torniamo al nostro foglio elettronico "Decision Maker" nella Parte 4, in cui Jane sta cercando di decidere cosa indossare a scuola. Non è più interessata a ciò che indossa, dal momento che è approdata ad un nuovo fidanzato, quindi ora indosserà abiti e scarpe casuali.
Nel foglio di calcolo di Jane, elenca abiti in colonne verticali e scarpe, colonne orizzontali.
Apre il foglio di calcolo e la funzione RANDBETWEEN (1,3) genera un numero compreso tra uno e tre corrispondenti ai tre tipi di abiti che può indossare.
Usa la funzione RANDBETWEEN (1,5) per scegliere tra cinque tipi di scarpe.
Poiché Jane non può indossare un numero, è necessario convertirlo in un nome, quindi utilizziamo le funzioni di ricerca.
Usiamo la funzione CERCA.VUOTO per tradurre il numero dell'attrezzatura nel nome del vestito. HLOOKUP si traduce dal numero di scarpe per i vari tipi di scarpe nella riga.
Il foglio di calcolo funziona come questo per gli outfit:
Excel sceglie un numero casuale da uno a tre, poiché ha tre opzioni di outfit.
Successivamente la formula traduce il numero in testo usando = VLOOKUP (B11, A2: B4,2) che usa il numero casuale il valore di B11 per cercare nell'intervallo A2: B4. Quindi fornisce il risultato (C11) dai dati elencati nella seconda colonna.
Usiamo la stessa tecnica per scegliere le scarpe, ma questa volta utilizziamo VOOKUP anziché HLOOKUP.
Esempio: statistiche di base
Quasi tutti conoscono una formula dalle statistiche - media - ma c'è un'altra statistica che è importante per il business: deviazione standard.
Per esempio, molte persone che sono andate al college si sono tormentate per il loro punteggio SAT. Potrebbero voler sapere come si posizionano rispetto agli altri studenti. Le università vogliono saperlo anche perché molte università, specialmente prestigiose, rifiutano gli studenti con bassi punteggi SAT.
Quindi, come faremmo noi, o un'università, a misurare e interpretare i punteggi SAT? Di seguito sono riportati i punteggi SAT per cinque studenti che vanno da 1.870 a 2.230.
I numeri importanti da capire sono:
Media - La media è anche definita "media".
Deviazione standard (STD o σ) - Questo numero mostra quanto sia disperso un insieme di numeri. Se la deviazione standard è grande, i numeri sono molto distanti e se è zero, tutti i numeri sono uguali. Si potrebbe dire che la deviazione standard è la differenza media tra il valore medio e il valore osservato, vale a dire 1,998 e ogni punteggio SAT. Si noti che è comune abbreviare la deviazione standard utilizzando il simbolo greco sigma "σ."
Rank percentile - Quando uno studente riceve un punteggio elevato, può vantarsi di essere nei primi 99 percentili o qualcosa del genere. "Grado percentile" significa che la percentuale dei punteggi è inferiore a un particolare punteggio.
Deviazione standard e probabilità sono strettamente collegate. Puoi dire che per ogni deviazione standard, la probabilità o la probabilità che quel numero sia all'interno di quel numero di deviazioni standard è:
| STD | Percentuale di punteggi | Gamma di punteggi SAT |
| 1 | 68% | 1,854-2,142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99.73% | 1,567-2,429 |
| 4 | 99,994% | 1,424-2,572 |
Come puoi vedere, la probabilità che qualsiasi punteggio SAT sia al di fuori di 3 STD è praticamente pari a zero, perché il 99,73 percento dei punteggi si trova entro 3 STD.
Ora esaminiamo nuovamente il foglio di calcolo e spieghiamo come funziona.
Ora spieghiamo le formule:
= MEDIA (B2: B6)
La media di tutti i punteggi nell'intervallo B2: B6. In particolare, la somma di tutti i punteggi divisi per il numero di persone che hanno preso il test.
= STDEV.P (B2: B6)
La deviazione standard nell'intervallo B2: B6. ".P" significa che STDEV.P è utilizzato su tutti i punteggi, cioè l'intera popolazione e non solo un sottoinsieme.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Questo calcola la percentuale cumulativa nell'intervallo B2: B6 in base al punteggio SAT, in questo caso B2. Ad esempio, l'83% dei punteggi è inferiore al punteggio di Walker.
Rappresentazione grafica dei risultati
Mettere i risultati in un grafico facilita la comprensione dei risultati, inoltre puoi mostrarlo in una presentazione per rendere più chiaro il tuo punto di vista.
Gli studenti sono sull'asse orizzontale e i loro punteggi SAT sono mostrati come un grafico a barre blu su una scala (asse verticale) da 1.600 a 2.300.
La classifica dei percentili è l'asse verticale di destra da 0 a 90 percento, ed è rappresentato dalla linea grigia.
Come creare un grafico
Creare un grafico è un argomento a sé stante, tuttavia spiegheremo brevemente come è stato creato il grafico sopra.
Innanzitutto, seleziona l'intervallo di celle da inserire nel grafico. In questo caso da A2 a C6 perché vogliamo i numeri così come i nomi degli studenti.
Dal menu "Inserisci" seleziona "Grafici" -> "Grafici consigliati":
Il computer consiglia un grafico "Clustered Column, Secondary Axis". La parte "Asse secondario" significa che disegna due assi verticali. In questo caso, questo grafico è quello che vogliamo. Non dobbiamo fare nient'altro.
Puoi usare spostare il grafico e ridimensionarlo fino a quando non lo hai come dimensione e nella posizione che desideri. Una volta che sei soddisfatto puoi salvare il grafico nel foglio di calcolo.
Se fai clic con il pulsante destro del mouse sul grafico, quindi su "Seleziona dati", mostra quali dati sono stati selezionati per l'intervallo.
La funzione "Grafici consigliati" di solito ti impedisce di dover gestire dettagli così complessi come determinare quali dati includere, come assegnare etichette e come assegnare gli assi verticali sinistro e destro.
Nella finestra di dialogo "Seleziona origine dati", fai clic su "punteggio" in "Legenda voci (serie)" e premi "Modifica" e modificalo per dire "Punteggio".
Quindi modifica la serie 2 ("percentile") in "Percentile".
Ritorna al grafico e clicca sul "Titolo del grafico" e cambialo in "Tabelloni SAT". Ora abbiamo un grafico completo. Ha due assi orizzontali: uno per il punteggio SAT (blu) e uno per la percentuale cumulativa (arancione).
Esempio: il problema di trasporto
Il problema dei trasporti è un classico esempio di un tipo di matematica chiamata "programmazione lineare". Ciò consente di massimizzare o minimizzare un valore soggetto a determinati vincoli. Ha molte applicazioni per una vasta gamma di problemi di business, quindi è utile imparare come funziona.
Prima di iniziare con questo esempio, dobbiamo abilitare "Excel Risolutore".
Abilita il componente aggiuntivo Risolutore
Seleziona "File" -> "Opzioni" -> "Componenti aggiuntivi". Nella parte inferiore delle opzioni dei componenti aggiuntivi, fai clic sul pulsante "Vai" accanto a "Gestisci: componenti aggiuntivi di Excel".
Nel menu risultante, fare clic sulla casella di controllo per abilitare "Componente aggiuntivo Risolutore" e fare clic su "OK".
Esempio: calcola i costi di spedizione dell'iPad più bassi
Supponiamo che stiamo spedendo iPad e stiamo cercando di riempire i nostri centri di distribuzione utilizzando i costi di trasporto più bassi possibili. Abbiamo un accordo con una società di autotrasporti e compagnie aeree per spedire iPad da Shanghai, Pechino e Hong Kong ai centri di distribuzione mostrati di seguito.
Il prezzo per spedire ogni iPad è la distanza dalla fabbrica al centro di distribuzione all'impianto diviso per 20.000 chilometri. Ad esempio, è di 8.024 km da Shanghai a Melbourne che è 8.024 / 20.000 o $ 40 per iPad.
La domanda è come spedire tutti questi iPad da questi tre impianti a queste quattro destinazioni al minor costo possibile?
Come puoi immaginare, capire questo potrebbe essere molto difficile senza qualche formula e strumento. In questo caso dobbiamo spedire 462.000 (F12) iPad totali. Le piante hanno una capacità limitata di 500.250 unità (G12).
Nel foglio di calcolo, in modo che tu possa vedere come funziona, abbiamo inserito 1 nella cella B10, ovvero vogliamo spedire 1 iPad da Shanghai a Melbourne. Poiché i costi di trasporto lungo tale percorso sono di $ 0,40 per iPad, il costo totale (B17) è di $ 0,40.
Il numero è stato calcolato utilizzando la funzione = SUMPRODUCT (costi, spedizione) "costi" sono gli intervalli B3: E5.
E "spediti" sono la gamma B9: E11:
SUMPRODUCT moltiplica "costi" per il range "spedito" (B14). Questo è chiamato "moltiplicazione della matrice".
Affinché SUMPRODUCT funzioni correttamente, le due matrici - costi e spedizione - devono essere della stessa dimensione. È possibile aggirare questa limitazione facendo costi aggiuntivi e spedendo colonne e righe con valore zero in modo che gli array abbiano le stesse dimensioni e non incidano sui costi totali.
Utilizzo del Risolutore
Se tutto quello che dovevamo fare era moltiplicare le matrici "costi" tempi "spediti" che non sarebbe troppo complicato, ma anche noi dobbiamo fare i conti con i vincoli.
Dobbiamo spedire ciò che ogni centro di distribuzione richiede. Abbiamo inserito questa costante nel risolutore in questo modo: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Ciò significa che la somma di ciò che viene spedito, cioè il totale nelle celle $ B $ 12: $ E $ 12, deve essere maggiore o uguale a quello richiesto da ciascun centro di distribuzione ($ B $ 13: $ E $ 13).
Non possiamo spedire più di quanto produciamo. Scriviamo questo tipo di vincoli: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Ora vai al menu "Dati" e premi il pulsante "Risolutore". Se il pulsante "Risolutore" non è presente, è necessario abilitare il componente aggiuntivo Risolutore.
Digitare i due vincoli dettagliati in precedenza e selezionare l'intervallo "Spedizioni", che è l'intervallo di numeri che vogliamo calcolare da Excel. Scegli anche l'algoritmo predefinito "Simplex LP" e indica che vogliamo "minimizzare" la cella B15 ("costi di spedizione totali"), dove si dice "Imposta obiettivo".
Premi "Risolvi" ed Excel salva i risultati nel foglio di calcolo, che è ciò che vogliamo. Puoi anche salvare questo in modo da poter giocare con altri scenari.
Se il computer dice che non riesce a trovare una soluzione, allora hai fatto qualcosa che non è logico, ad esempio potresti aver richiesto più iPad di quanto le piante possano produrre.
Qui Excel sta dicendo che ha trovato una soluzione. Premere "OK" per mantenere la soluzione e tornare al foglio di calcolo.
Esempio: valore attuale netto
In che modo un'azienda decide se investire in un nuovo progetto? Se il "valore attuale netto" (NPV) è positivo, investiranno in esso. Questo è un approccio standard adottato dalla maggior parte degli analisti finanziari.
Ad esempio, supponiamo che la compagnia mineraria di Codelco voglia espandere la miniera di rame di Andinas. L'approccio standard per determinare se andare avanti con un progetto è calcolare il valore attuale netto. Se il NPV è maggiore di zero, allora il progetto sarà redditizio dato due input (1) tempo e (2) costo del capitale.
In parole povere, il costo del capitale significa quanto guadagnerebbe quel denaro se lo lasciasse in banca. Utilizzi il costo del capitale per scontare i valori in contanti al valore attuale, in altre parole $ 100 in cinque anni potrebbero essere $ 80 oggi.
Nel primo anno, $ 45 milioni sono stanziati come capitale per finanziare il progetto. I ragionieri hanno stabilito che il loro costo del capitale è del sei per cento.
All'inizio dell'attività mineraria, il denaro inizia a venire quando l'azienda trova e vende il rame che produce. Ovviamente, più loro estraggono, più denaro guadagnano e le loro previsioni mostrano che il loro flusso di cassa aumenta fino a raggiungere $ 9 milioni all'anno.
Dopo 13 anni, il NPV è $ 3.945.074 USD, quindi il progetto sarà redditizio. Secondo gli analisti finanziari il "periodo di ammortamento" è di 13 anni.
Creazione di una tabella pivot
Una "tabella pivot" è fondamentalmente un rapporto. Li chiamiamo tabelle pivot perché puoi facilmente passare da un tipo di rapporto a un altro senza dover creare un nuovo report completo. Così perno a posto. Mostriamo un esempio di base che insegna i concetti di base.
Esempio: rapporti di vendita
Il personale di vendita è molto competitivo (fa parte dell'essere un venditore), quindi naturalmente vogliono sapere come si comportano l'uno contro l'altro alla fine del trimestre e alla fine dell'anno, più quanto le loro commissioni saranno.
Supponiamo di avere tre venditori - Carlos, Fred e Julie - tutti che vendono petrolio. Le loro vendite in dollari per trimestre fiscale per l'anno 2014 sono riportate nel foglio di calcolo qui sotto.
Per generare questi report, creiamo una tabella pivot:
Seleziona "Inserisci -> Tabella pivot, si trova sul lato sinistro della barra degli strumenti:
Seleziona tutte le righe e le colonne (incluso il nome del venditore) come mostrato di seguito:
La finestra di dialogo della tabella pivot viene visualizzata sul lato destro del foglio di calcolo.
Se facciamo clic su tutti e quattro i campi nella finestra di dialogo della tabella pivot (Trimestre, Anno, Vendite e Commerciale), Excel aggiunge un rapporto al foglio di calcolo che non ha senso, ma perché?
Come puoi vedere, abbiamo selezionato tutti e quattro i campi da aggiungere al rapporto. Il comportamento predefinito di Excel consiste nel raggruppare le righe in base ai campi di testo e quindi sommare tutto il resto delle righe.
Qui ci fornisce la somma dell'anno 2014 + 2014 + 2014 + 2014 = 24.168, che è una sciocchezza. Inoltre ha dato la somma dei trimestri 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Non abbiamo bisogno di queste informazioni, quindi deselezioniamo questi campi per rimuoverli dalla nostra tabella pivot.
"Somma delle vendite" (vendite totali) è pertinente, tuttavia, quindi lo aggiusteremo.
Esempio: vendite per venditore
Puoi modificare "Somma delle vendite" per indicare "Vendite totali", che è più chiaro. Inoltre, puoi formattare le celle come valuta proprio come formeresti qualsiasi altra cella. Innanzitutto fai clic su "Sum of Sales" e seleziona "Value Field Settings".
Nella finestra di dialogo risultante, cambiamo il nome in "Vendite totali", quindi fai clic su "Formato numero" e cambialo in "Valuta".
Puoi quindi vedere il tuo lavoro nella tabella pivot:
Esempio: vendite per venditore e trimestre
Ora aggiungiamo i subtotali per ogni trimestre. Per aggiungere i subtotali basta fare clic con il pulsante sinistro del mouse sul campo "Trimestre" e tenerlo premuto e trascinarlo nella sezione "Righe". Puoi vedere il risultato sullo screenshot qui sotto:
Mentre ci siamo, rimuoviamo i valori "Sum of Quarter". Basta fare clic sulla freccia e fare clic su "Rimuovi campo". Nello screenshot, ora è possibile vedere che abbiamo aggiunto le righe "Trimestre", che suddivide le vendite di ogni venditore per trimestre.
Con queste nuove competenze in mente, ora puoi creare tabelle pivot dai tuoi dati!
Conclusione
In conclusione, ti abbiamo mostrato alcune delle funzionalità delle formule e delle funzioni di Microsoft Excel che puoi applicare a Microsoft Excel per la tua azienda, per il mondo accademico o per altre esigenze.
Come hai visto, Microsoft Excel è un prodotto enorme con così tante funzioni che la maggior parte delle persone, anche gli utenti avanzati, non le conoscono tutte. Qualcuno potrebbe dire che lo rende complicato; riteniamo che sia più completo.
Speriamo che, presentandoti molti esempi di vita reale, abbiamo dimostrato non solo le funzioni disponibili in Microsoft Excel, ma ti abbiamo insegnato qualcosa sulle statistiche, la programmazione lineare, la creazione di grafici, l'uso di numeri casuali e altre idee che ora puoi adottare e usa nella tua scuola o dove lavori.
Ricorda, se vuoi tornare indietro e riprendere la lezione, puoi iniziare a utilizzare la lezione 1!
