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 aziende, 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 a 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.
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 del vestito 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:
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.
Ad 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.
Media - La media viene 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 è compreso tra 3 STD.
Ora vediamo di nuovo il foglio di calcolo e spieghiamo come funziona.
= 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)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, B2)
Rappresentazione grafica dei risultati
Mettere i risultati in un grafico facilita la comprensione dei risultati, inoltre è possibile visualizzarli in una presentazione per rendere più chiaro il punto.
La classifica del percentile è l'asse verticale della mano 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.
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".
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".
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 indicati 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.
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).
Utilizzo del Risolutore
Se tutto ciò 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 delle 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. In altre parole, ciò che spediamo da ogni impianto $ F $ 9: $ F $ 11 non può superare (deve essere inferiore o uguale a) la capacità di ogni impianto: $ G $ 9: $ G $ 11.
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".
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 VAN è più grande di zero, allora il progetto sarà redditizio dato due input (1) di tempo e (2) costo del capitale.
In parole povere, il costo del capitale significa quanto guadagnerebbe quel denaro se lo lasciasse in banca. Usi 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 contabili 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
Gli addetti alle vendite sono molto competitivi (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, oltre a quanto saranno le loro commissioni.
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.
Seleziona "Inserisci -> Tabella pivot, si trova sul lato sinistro della barra degli strumenti:
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é?
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.
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".
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:
Conclusione
In conclusione, ti abbiamo mostrato alcune 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!
