Link rapidi
- Passaggio 1: crea un elenco a discesa
- Passaggio 2: crea una tabella di recupero dati
- Passaggio 3: estrai i dati dalla tabella 1 alla tabella 2
- Passaggio 4: inserisci e formatta il grafico
- Passaggio 5: aggiungi una linea media al grafico
Se vuoi stupire i tuoi amici e colleghi, puoi apportare una modifica al grafico Excel in base a una casella a discesa che hai aggiunto al tuo foglio. Esistono diversi modi per raggiungere questo obiettivo, ma preferiamo il metodo seguente perché è più semplice individuare eventuali problemi se qualcosa va storto.
Utilizzeremo una tabella di dati contenente cinque giocatori di calcio' nomi e la loro valutazione per cinque giochi mentre ti spieghiamo il processo.
Passaggio 1: crea un elenco a discesa
Il primo passo è creare l'elenco a discesa che renderà dinamico il tuo grafico. Inizia digitando una parola d'azione nella cella accanto a dove andrà il tuo menu a discesa. Nel nostro esempio, inseriremo il menu a discesa nella cella B9, quindi digiteremo la nostra parola d'azione nella cella A9. Quindi, fai clic sulla cella in cui verrà inserito il menu a discesa, vai alla scheda Dati sulla barra multifunzione e fai clic su “Convalida dati” nel gruppo Strumenti dati.
Nella finestra Convalida dati, seleziona "Elenco" in Consenti. Quindi, fai clic sulla scheda "Sorgente" casella di campo ed evidenzia le intestazioni delle colonne nella tabella (i dati che verranno visualizzati nell'elenco a discesa che stai creando). Nel nostro esempio, vogliamo scegliere i numeri della partita, poiché nella tabella che creeremo in seguito, vogliamo vedere i numeri dei giocatori. valutazioni per ogni gioco. Quindi, fai clic su “OK”.
Ora lo farai viene visualizzato un elenco a discesa che mostra i dati selezionati quando si fa clic sulla freccia. Abbiamo anche formattato la cella Scegli per farla risaltare.
Passaggio 2: Crea una tabella di recupero dati
Ora dobbiamo creare una seconda tabella che sia separata dai nostri dati grezzi ma che estragga informazioni da essa quando regoliamo il menu a discesa che abbiamo creato. Il grafico che creeremo in seguito verrà creato utilizzando le informazioni contenute in questa tabella di recupero dati.
Da questo punto chiameremo la tabella dei dati principale Tabella 1e la tabella di recupero dei dati Tabella 2 .
Evidenzia i titoli delle righe nella Tabella 1, premi Ctrl+C e usa Ctrl+V per copiare l'elenco dove vuoi che sia la Tabella 2.
Seleziona e fai clic con il pulsante destro del mouse sui dati incollati, passa il mouse su "Ordina" e fai clic su “Ordina dalla A alla Z”.
Per continuare a creare la tabella 2, è necessario dare un'intestazione alla colonna successiva. I dati visualizzati nella Tabella 2 saranno dettati da ciò che selezioni nella casella a discesa, quindi nella cella in cui vuoi inserire l'intestazione, digita =e fare clic sulla cella a discesa. Nel nostro caso, vogliamo la nostra intestazione nella cella I2 e in quella cella digiteremo = e fare clic sulla cella B9.
Premere Invio per vedere l'intestazione della colonna nella Tabella 2 adottare la selezione a discesa. Prova a modificare la selezione a discesa per vedere la modifica dell'intestazione della colonna nella Tabella 2.
Passaggio 3: estrazione dei dati dalla Tabella 1 alla Tabella 2
Ora dobbiamo estrarre i dati rilevanti dalla Tabella 1 alla Tabella 2 utilizzando INDEX e MATCH. Questa è la parte più complessa del processo, ma analizziamola in modo che tu possa completarla più facilmente. Ecco la sinassi:
=INDEX(a,MATCH(b,c,0),MATCH(d,e,0))
dove
- a sono tutti i dati nella Tabella 1 (escluse le intestazioni di riga e colonna),
- b è il primo valore che cerchiamo nella Tabella 2,
- c è il intervallo di valori nella prima colonna della Tabella 1,
- d è l'intestazione della colonna nella Tabella 2 e
- e rappresenta le intestazioni delle colonne di dati nella Tabella 1.
< /ul>
Assicurati che i riferimenti di cella a , c , d ed e siano riferimenti assoluti premendo F4 sulla tastiera dopo aver selezionato ciascun riferimento. Altrimenti, la tua formula non funzionerà quando completerai il resto dei dati nella Tabella 2.
Vediamolo in azione.
Nella cella I3, vogliamo dire a Excel di cercare nella Tabella 1 e inserire un valore nella Tabella 2 tramite la funzione INDICE.
=INDICE
Ora dobbiamo aprire la parentesi e indicare a Excel dove si trovano i dati nella Tabella 1. Per fare ciò, evidenziare tutti i dati nella Tabella 1, ma non le intestazioni di colonna e riga. Premi F4 per renderlo un riferimento assoluto e aggiungi una virgola.
=INDEX($B$3:$F$7,
Il passo successivo è dire a Excel di abbinare i dati nella Tabella 2 con ciò che abbiamo nella Tabella 1. Digita CONFRONTA, apri una nuova parentesi e fai clic sulla prima voce nella prima colonna della Tabella 2. Nel nostro caso, è Davies nella cella H3. Aggiungi una virgola.
=INDICE($B$3:$F$7,MATCH(H3,
Successivamente, Excel deve sapere cosa utilizzerà come riferimento per cercare nella Tabella 1. Nel nostro caso, sono i dati dalla cella A3 alla cella A7 (i nomi dei giocatori), quindi evidenzia questo intervallo, premere F4, quindi aggiungere una virgola. Quindi, digita 0 (zero) per indicare a Excel che stiamo cercando una corrispondenza esatta e chiudi le parentesi. Aggiungi un'altra virgola.
=INDICE($B$3:$F$7,CONFRONTA(H3,$A$3:$A$7,0),
Ora dobbiamo ripetere il processo MATCH per i dati che cambieranno quando selezioniamo un'opzione diversa nel menu a discesa. Nel nostro caso, questo è il numero del gioco, quindi dopo aver digitato CONFRONTA nella nostra formula per la seconda volta, faremo clic su I2, premeremo F4 e aggiungeremo una virgola.
=INDICE($B$3:$F$7,CONFRONTA(H3,$A$3:$A$7,0),CONFRONTA($I$2,
E ancora, dobbiamo dire a Excel dove trovare i dati nella Tabella 1. Nel nostro esempio, si tratta delle celle da B2 a F4. Non dimenticare di premere F4 dopo aver fatto riferimento a queste celle. Quindi aggiungi una virgola, uno 0, e chiudi insieme le due parentesi. Infine, premi Invio.
=INDICE($B$3:$F$7,CONFRONTA(H3,$A$3:$A$7,0),CONFRONTA($I$2,$B$2:$F$2, 0))
Ora, fai clic e trascina la maniglia nell'angolo in basso a destra della cella in cui hai appena digitato la formula per compilare automaticamente il resto dei dati in Tabella 2.
Prova a modificare l'opzione del menu a discesa su un valore diverso e osserva la modifica dei dati nella Tabella 2 e controlla che sia allineata con le informazioni nella Tabella 1. Ricorda che abbiamo ordinato la prima colonna nella Tabella 2 in ordine alfabetico, quindi controlla attentamente, poiché la prima colonna nella Tabella 1 sarà in un ordine diverso!
< p> Ora che la Tabella 2 cambia correttamente in base alla nostra selezione a discesa, siamo pronti per creare il grafico.
Passaggio 4: inserisci e formatta il tuo grafico
Evidenzia tutti i dati nella tabella 2 (inclusa la colonna e intestazioni di riga), aprire la finestra di dialogo "Inserisci" scheda e scegli un grafico adatto a te.
Abbiamo scelto un semplice grafico a colonne 2D. Formatta il grafico in modo che appaia come desideri e, ancora una volta, gioca con il menu a discesa per visualizzare la Tabella 2 e, di conseguenza, il grafico si adatta alla tua selezione. Vedrai anche cambiare il titolo del grafico. Per aggiungere una linea media al grafico, devi aggiungere più dati alla Tabella 2. Aggiungi un'altra intestazione di colonna alla Tabella 2 e chiamala Media. Ora utilizza la funzione MEDIA per acquisire la media dei dati selezionati. Nel nostro caso, abbiamo selezionato il gioco 4, quindi vogliamo trovare la valutazione media tra tutti e cinque i giocatori per questo gioco. Per fare ciò, digita =AVERAGE nella prima cella di questa nuova colonna, quindi seleziona i dati in la colonna precedente. Premi F4 per renderlo un riferimento assoluto, quindi chiudi le parentesi e premi Invio. Quindi, utilizza la maniglia di Compilazione automatica per fare clic e trascinare la formula fino alla fine della colonna. Ora vogliamo aggiungere i dati medi al nostro grafico. Fai clic in un punto qualsiasi verso il bordo del grafico e fai clic su "Design grafico" sul nastro. Quindi, vai al gruppo Dati e fai clic su “Seleziona dati”. Nella finestra Seleziona origine dati, fai clic su “Aggiungi”. Quindi, nella finestra Modifica serie, deseleziona il campo Valori serie, quindi seleziona i dati nella colonna Media della tabella 2. Fai clic su “OK”. e vedrai la media apparire come una barra aggiuntiva nel grafico. Per modificare questi dati medi in una linea sopra le barre esistenti, fai clic con il pulsante destro del mouse su una delle nuove barre e fai clic su “Cambia tipo di grafico serie”. Apri la " Tutte le classifiche” scheda, fai clic su "Combo" nel menu a sinistra e modifica la serie di dati media in “Linea”. Fai clic su OK per vedere il risultato e modifica la scelta del menu a discesa per vedere il grafico cambiare dinamicamente! Puoi quindi formattare la linea in modo che risalti nel modo desiderato. Ora che hai ottenuto questo grafico impressionante e dinamico, scopri alcuni modi per farlo risaltare ancora di più.
Passaggio 5: aggiungi una linea media al grafico
