Mekki MOURADI
Data Analyst
Rabat, Maroc
À Propos
Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.
Introduction to Data Analysis using Microsoft Excel
Liens
Compétences & Outils
Description du projet
- Utiliser les fonctions de recherche et de récupération pour effectuer une analyse de données
- Utiliser la fonction “IF” pour l’extraction de données
- Établir des références entre tableaux et utiliser VLOOKUP pour rechercher des informations
- Exploiter les tableaux croisés dynamiques (PivotTables) pour une analyse plus approfondie des données
Objectifs de l'analyse
- Utiliser des fonctions de recherche et de récupération des données pour les besoins de l’analyse
- Perform Data Analysis using Sorting and Filtering tools
- Afficher uniquement les ventes réalisées par l'agent commercial "David Garcia", et déterminer la Somme des Ventes ainsi que la Somme des Unités Vendues
- Transformer les données en tableau
- Filtrer les données via la colonne “Sales Rep” pour garder uniquement “David Garcia” dans la liste de filtres
| Num | Date | Month | Sales Rep | Region | Customer ID | Model | Color | Item Code | Number | Price / Unit | Total |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 7 | 18/1/2020 | January | David Garcia | South | 152 | Cosmo | white | C2699wh | 8 | $375 | $3,000 |
| 11 | 28/1/2020 | January | David Garcia | South | 157 | Energy | black | E2376bl | 33 | $350 | $11,550 |
| 19 | 19/2/2020 | February | David Garcia | South | 132 | Flash | brown | F2248br | 35 | $235 | $8,225 |
| 50 | 30/4/2020 | April | David Garcia | South | 162 | Flash | gray | F2248gr | 38 | $235 | $8,930 |
| 56 | 13/5/2020 | May | David Garcia | South | 180 | Urban | black | U2683bl | 30 | $260 | $7,800 |
| 64 | 27/5/2020 | May | David Garcia | South | 157 | Aero | white | A2258wh | 15 | $220 | $3,300 |
| 66 | 2/6/2020 | June | David Garcia | South | 178 | Cosmo | gray | C2699gr | 33 | $375 | $12,375 |
| 68 | 5/6/2020 | June | David Garcia | South | 136 | Urban | gray | U2683gr | 26 | $260 | $6,760 |
| 75 | 18/6/2020 | June | David Garcia | South | 166 | Urban | red | U2683rd | 35 | $260 | $9,100 |
- Performing Data Mining using the IF Function
- Utiliser la fonction "IF" pour déterminer les ventes dont les Unités sont supérieures ou égales à 20 afin de leur accorder une réduction
- Insertion d’une nouvelle colonne “Discount”
- Insertion de la fonction suivante :
| Num | Date | Month | Sales Rep | Region | Customer ID | Model | Color | Item Code | Number | Price / Unit | Total | Discount |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2/1/2020 | January | Eric Jones | North | 132 | Flash | black | F2248bl | 15 | $235 | $3,525 | N |
| 2 | 6/1/2020 | January | Amy Brown | West | 144 | Urban | red | U2683rd | 22 | $260 | $5,720 | Y |
| 3 | 9/1/2020 | January | Sara Davis | West | 136 | Energy | black | E2376bl | 16 | $350 | $5,600 | N |
| 4 | 12/1/2020 | January | Marc Williams | South | 144 | Flash | brown | F2248br | 30 | $235 | $7,050 | Y |
| 5 | 12/1/2020 | January | Eric Jones | North | 166 | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y |
| 6 | 15/1/2020 | January | Stacy Peters | North | 136 | Energy | brown | E2376br | 14 | $350 | $4,900 | N |
| 7 | 18/1/2020 | January | David Garcia | South | 152 | Cosmo | white | C2699wh | 8 | $375 | $3,000 | N |
| 8 | 22/1/2020 | January | Amy Brown | West | 132 | Flash | brown | F2248br | 22 | $235 | $5,170 | Y |
| 9 | 22/1/2020 | January | Sara Davis | West | 136 | Urban | brown | U2683br | 40 | $260 | $10,400 | Y |
| 10 | 26/1/2020 | January | Eric Jones | North | 166 | Energy | black | E2376bl | 25 | $350 | $8,750 | Y |
| 11 | 28/1/2020 | January | David Garcia | South | 157 | Energy | black | E2376bl | 33 | $350 | $11,550 | Y |
| 12 | 4/2/2020 | February | Marc Williams | South | 178 | Volt | white | V2944wh | 15 | $295 | $4,425 | N |
| 13 | 7/2/2020 | February | Eric Jones | North | 180 | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N |
| 14 | 8/2/2020 | February | Emily Moore | West | 132 | Urban | brown | U2683br | 45 | $260 | $11,700 | Y |
| 15 | 10/2/2020 | February | Amy Brown | West | 180 | Energy | white | E2376wh | 32 | $350 | $11,200 | Y |
| 16 | 12/2/2020 | February | Marc Williams | South | 166 | Energy | black | E2376bl | 28 | $350 | $9,800 | Y |
| 17 | 14/2/2020 | February | Sara Davis | West | 162 | Aero | red | A2258rd | 10 | $220 | $2,200 | N |
| 18 | 15/2/2020 | February | Eric Jones | North | 136 | Urban | brown | U2683br | 16 | $260 | $4,160 | N |
| 19 | 19/2/2020 | February | David Garcia | South | 132 | Flash | brown | F2248br | 35 | $235 | $8,225 | Y |
| 20 | 21/2/2020 | February | Amy Brown | West | 132 | Volt | black | V2944bl | 12 | $295 | $3,540 | N |
| 21 | 26/2/2020 | February | Marc Williams | South | 136 | Cosmo | gray | C2699gr | 40 | $375 | $15,000 | Y |
| 22 | 28/2/2020 | February | Stacy Peters | North | 144 | Energy | brown | E2376br | 10 | $350 | $3,500 | N |
| 23 | 1/3/2020 | March | Sara Davis | West | 132 | Cosmo | black | C2699bl | 25 | $375 | $9,375 | Y |
| 24 | 4/3/2020 | March | Emily Moore | West | 162 | Urban | black | U2683bl | 50 | $260 | $13,000 | Y |
| 25 | 7/3/2020 | March | Amy Brown | West | 180 | Flash | white | F2248wh | 22 | $235 | $5,170 | Y |
| 26 | 9/3/2020 | March | Eric Jones | North | 144 | Volt | brown | V2944br | 15 | $295 | $4,425 | N |
| 27 | 11/3/2020 | March | Stacy Peters | North | 166 | Aero | white | A2258wh | 10 | $220 | $2,200 | N |
| 28 | 12/3/2020 | March | Marc Williams | South | 178 | Energy | black | E2376bl | 20 | $350 | $7,000 | Y |
| 29 | 14/3/2020 | March | Emily Moore | West | 157 | Flash | gray | F2248gr | 14 | $235 | $3,290 | N |
| 30 | 18/3/2020 | March | Amy Brown | West | 152 | Aero | gray | A2258gr | 28 | $220 | $6,160 | Y |
| 31 | 23/3/2020 | March | Emily Moore | West | 162 | Flash | black | F2248bl | 12 | $235 | $2,820 | N |
| 32 | 24/3/2020 | March | Eric Jones | North | 180 | Volt | white | V2944wh | 35 | $295 | $10,325 | Y |
| 33 | 26/3/2020 | March | Marc Williams | South | 178 | Cosmo | white | C2699wh | 20 | $375 | $7,500 | Y |
| 34 | 28/3/2020 | March | Stacy Peters | North | 152 | Aero | gray | A2258gr | 45 | $220 | $9,900 | Y |
| 35 | 2/4/2020 | April | Amy Brown | West | 136 | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N |
| 36 | 6/4/2020 | April | Emily Moore | West | 132 | Energy | black | E2376bl | 14 | $350 | $4,900 | N |
| 37 | 7/4/2020 | April | Marc Williams | South | 157 | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y |
| 38 | 11/4/2020 | April | Sara Davis | West | 132 | Urban | black | U2683bl | 40 | $260 | $10,400 | Y |
| 39 | 12/4/2020 | April | Stacy Peters | North | 166 | Flash | black | F2248bl | 45 | $235 | $10,575 | Y |
| 40 | 12/4/2020 | April | Amy Brown | West | 180 | Aero | white | A2258wh | 24 | $220 | $5,280 | Y |
| 41 | 14/4/2020 | April | Emily Moore | West | 132 | Cosmo | black | C2699bl | 30 | $375 | $11,250 | Y |
| 42 | 15/4/2020 | April | Emily Moore | West | 144 | Urban | red | U2683rd | 15 | $260 | $3,900 | N |
| 43 | 16/4/2020 | April | Stacy Peters | North | 157 | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N |
| 44 | 19/4/2020 | April | Eric Jones | North | 180 | Volt | brown | V2944br | 42 | $295 | $12,390 | Y |
| 45 | 20/4/2020 | April | Eric Jones | North | 132 | Energy | black | E2376bl | 26 | $350 | $9,100 | Y |
| 46 | 22/4/2020 | April | Marc Williams | South | 162 | Urban | gray | U2683gr | 35 | $260 | $9,100 | Y |
| 47 | 23/4/2020 | April | Stacy Peters | North | 144 | Aero | white | A2258wh | 32 | $220 | $7,040 | Y |
| 48 | 27/4/2020 | April | Emily Moore | West | 132 | Volt | brown | V2944br | 18 | $295 | $5,310 | N |
| 49 | 27/4/2020 | April | Marc Williams | South | 180 | Energy | black | E2376bl | 22 | $350 | $7,700 | Y |
| 50 | 30/4/2020 | April | David Garcia | South | 162 | Flash | gray | F2248gr | 38 | $235 | $8,930 | Y |
| 51 | 1/5/2020 | May | Eric Jones | North | 180 | Aero | black | A2258bl | 42 | $220 | $9,240 | Y |
| 52 | 3/5/2020 | May | Emily Moore | West | 162 | Volt | red | V2944rd | 15 | $295 | $4,425 | N |
| 53 | 7/5/2020 | May | Marc Williams | South | 136 | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N |
| 54 | 8/5/2020 | May | Sara Davis | West | 136 | Flash | black | F2248bl | 26 | $235 | $6,110 | Y |
| 55 | 12/5/2020 | May | Stacy Peters | North | 152 | Flash | red | F2248rd | 40 | $235 | $9,400 | Y |
| 56 | 13/5/2020 | May | David Garcia | South | 180 | Urban | black | U2683bl | 30 | $260 | $7,800 | Y |
| 57 | 15/5/2020 | May | Marc Williams | South | 152 | Energy | gray | E2376gr | 26 | $350 | $9,100 | Y |
| 58 | 17/5/2020 | May | Stacy Peters | North | 132 | Volt | black | V2944bl | 18 | $295 | $5,310 | N |
| 59 | 19/5/2020 | May | Sara Davis | West | 180 | Flash | gray | F2248gr | 22 | $235 | $5,170 | Y |
| 60 | 21/5/2020 | May | Marc Williams | South | 144 | Energy | black | E2376bl | 42 | $350 | $14,700 | Y |
| 61 | 21/5/2020 | May | Emily Moore | West | 162 | Energy | white | E2376wh | 45 | $350 | $15,750 | Y |
| 62 | 24/5/2020 | May | Marc Williams | South | 132 | Volt | red | V2944rd | 20 | $295 | $5,900 | Y |
| 63 | 26/5/2020 | May | Eric Jones | North | 136 | Volt | black | V2944bl | 22 | $295 | $6,490 | Y |
| 64 | 27/5/2020 | May | David Garcia | South | 157 | Aero | white | A2258wh | 15 | $220 | $3,300 | N |
| 65 | 28/5/2020 | May | Stacy Peters | North | 132 | Flash | brown | F2248br | 35 | $235 | $8,225 | Y |
| 66 | 2/6/2020 | June | David Garcia | South | 178 | Cosmo | gray | C2699gr | 33 | $375 | $12,375 | Y |
| 67 | 5/6/2020 | June | Marc Williams | South | 144 | Urban | black | U2683bl | 22 | $260 | $5,720 | Y |
| 68 | 5/6/2020 | June | David Garcia | South | 136 | Urban | gray | U2683gr | 26 | $260 | $6,760 | Y |
| 69 | 8/6/2020 | June | Eric Jones | North | 132 | Aero | red | A2258rd | 16 | $220 | $3,520 | N |
| 70 | 9/6/2020 | June | Emily Moore | West | 178 | Volt | black | V2944bl | 10 | $295 | $2,950 | N |
| 71 | 9/6/2020 | June | Sara Davis | West | 162 | Urban | black | U2683bl | 40 | $260 | $10,400 | Y |
| 72 | 12/6/2020 | June | Amy Brown | West | 157 | Flash | brown | F2248br | 15 | $235 | $3,525 | N |
| 73 | 14/6/2020 | June | Stacy Peters | North | 132 | Cosmo | gray | C2699gr | 25 | $375 | $9,375 | Y |
| 74 | 15/6/1900 | June | Eric Jones | North | 144 | Volt | gray | V2944gr | 20 | $295 | $5,900 | Y |
| 75 | 18/6/2020 | June | David Garcia | South | 166 | Urban | red | U2683rd | 35 | $260 | $9,100 | Y |
| 76 | 23/6/2020 | June | Marc Williams | South | 178 | Energy | black | E2376bl | 22 | $350 | $7,700 | Y |
| 77 | 24/6/2020 | June | Amy Brown | West | 166 | Aero | white | A2258wh | 16 | $220 | $3,520 | N |
| 78 | 27/6/2020 | June | Sara Davis | West | 162 | Volt | black | V2944bl | 50 | $295 | $14,750 | Y |
| 79 | 29/6/2020 | June | Stacy Peters | North | 178 | Cosmo | gray | C2699gr | 32 | $375 | $12,000 | Y |
| 80 | 29/6/2020 | June | Amy Brown | West | 136 | Flash | white | F2248wh | 14 | $235 | $3,290 | N |
- Utiliser la fonction "IF" pour directement calculer le prix final pour les ventes où la réduction sera appliquée
- Insertion d’une nouvelle colonne “Final Price”
- Insertion de la fonction suivante :
| Num | Date | Month | Sales Rep | Region | Customer ID | Model | Color | Item Code | Number | Price / Unit | Total | Discount | Final Price |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2/1/2020 | January | Eric Jones | North | 132 | Flash | black | F2248bl | 15 | $235 | $3,525 | N | $3,525 |
| 2 | 6/1/2020 | January | Amy Brown | West | 144 | Urban | red | U2683rd | 22 | $260 | $5,720 | Y | $5,434 |
| 3 | 9/1/2020 | January | Sara Davis | West | 136 | Energy | black | E2376bl | 16 | $350 | $5,600 | N | $5,600 |
| 4 | 12/1/2020 | January | Marc Williams | South | 144 | Flash | brown | F2248br | 30 | $235 | $7,050 | Y | $6,698 |
| 5 | 12/1/2020 | January | Eric Jones | North | 166 | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 6 | 15/1/2020 | January | Stacy Peters | North | 136 | Energy | brown | E2376br | 14 | $350 | $4,900 | N | $4,900 |
| 7 | 18/1/2020 | January | David Garcia | South | 152 | Cosmo | white | C2699wh | 8 | $375 | $3,000 | N | $3,000 |
| 8 | 22/1/2020 | January | Amy Brown | West | 132 | Flash | brown | F2248br | 22 | $235 | $5,170 | Y | $4,912 |
| 9 | 22/1/2020 | January | Sara Davis | West | 136 | Urban | brown | U2683br | 40 | $260 | $10,400 | Y | $9,880 |
| 10 | 26/1/2020 | January | Eric Jones | North | 166 | Energy | black | E2376bl | 25 | $350 | $8,750 | Y | $8,313 |
| 11 | 28/1/2020 | January | David Garcia | South | 157 | Energy | black | E2376bl | 33 | $350 | $11,550 | Y | $10,973 |
| 12 | 4/2/2020 | February | Marc Williams | South | 178 | Volt | white | V2944wh | 15 | $295 | $4,425 | N | $4,425 |
| 13 | 7/2/2020 | February | Eric Jones | North | 180 | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 14 | 8/2/2020 | February | Emily Moore | West | 132 | Urban | brown | U2683br | 45 | $260 | $11,700 | Y | $11,115 |
| 15 | 10/2/2020 | February | Amy Brown | West | 180 | Energy | white | E2376wh | 32 | $350 | $11,200 | Y | $10,640 |
| 16 | 12/2/2020 | February | Marc Williams | South | 166 | Energy | black | E2376bl | 28 | $350 | $9,800 | Y | $9,310 |
| 17 | 14/2/2020 | February | Sara Davis | West | 162 | Aero | red | A2258rd | 10 | $220 | $2,200 | N | $2,200 |
| 18 | 15/2/2020 | February | Eric Jones | North | 136 | Urban | brown | U2683br | 16 | $260 | $4,160 | N | $4,160 |
| 19 | 19/2/2020 | February | David Garcia | South | 132 | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 20 | 21/2/2020 | February | Amy Brown | West | 132 | Volt | black | V2944bl | 12 | $295 | $3,540 | N | $3,540 |
| 21 | 26/2/2020 | February | Marc Williams | South | 136 | Cosmo | gray | C2699gr | 40 | $375 | $15,000 | Y | $14,250 |
| 22 | 28/2/2020 | February | Stacy Peters | North | 144 | Energy | brown | E2376br | 10 | $350 | $3,500 | N | $3,500 |
| 23 | 1/3/2020 | March | Sara Davis | West | 132 | Cosmo | black | C2699bl | 25 | $375 | $9,375 | Y | $8,906 |
| 24 | 4/3/2020 | March | Emily Moore | West | 162 | Urban | black | U2683bl | 50 | $260 | $13,000 | Y | $12,350 |
| 25 | 7/3/2020 | March | Amy Brown | West | 180 | Flash | white | F2248wh | 22 | $235 | $5,170 | Y | $4,912 |
| 26 | 9/3/2020 | March | Eric Jones | North | 144 | Volt | brown | V2944br | 15 | $295 | $4,425 | N | $4,425 |
| 27 | 11/3/2020 | March | Stacy Peters | North | 166 | Aero | white | A2258wh | 10 | $220 | $2,200 | N | $2,200 |
| 28 | 12/3/2020 | March | Marc Williams | South | 178 | Energy | black | E2376bl | 20 | $350 | $7,000 | Y | $6,650 |
| 29 | 14/3/2020 | March | Emily Moore | West | 157 | Flash | gray | F2248gr | 14 | $235 | $3,290 | N | $3,290 |
| 30 | 18/3/2020 | March | Amy Brown | West | 152 | Aero | gray | A2258gr | 28 | $220 | $6,160 | Y | $5,852 |
| 31 | 23/3/2020 | March | Emily Moore | West | 162 | Flash | black | F2248bl | 12 | $235 | $2,820 | N | $2,820 |
| 32 | 24/3/2020 | March | Eric Jones | North | 180 | Volt | white | V2944wh | 35 | $295 | $10,325 | Y | $9,809 |
| 33 | 26/3/2020 | March | Marc Williams | South | 178 | Cosmo | white | C2699wh | 20 | $375 | $7,500 | Y | $7,125 |
| 34 | 28/3/2020 | March | Stacy Peters | North | 152 | Aero | gray | A2258gr | 45 | $220 | $9,900 | Y | $9,405 |
| 35 | 2/4/2020 | April | Amy Brown | West | 136 | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 36 | 6/4/2020 | April | Emily Moore | West | 132 | Energy | black | E2376bl | 14 | $350 | $4,900 | N | $4,900 |
| 37 | 7/4/2020 | April | Marc Williams | South | 157 | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 38 | 11/4/2020 | April | Sara Davis | West | 132 | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 39 | 12/4/2020 | April | Stacy Peters | North | 166 | Flash | black | F2248bl | 45 | $235 | $10,575 | Y | $10,046 |
| 40 | 12/4/2020 | April | Amy Brown | West | 180 | Aero | white | A2258wh | 24 | $220 | $5,280 | Y | $5,016 |
| 41 | 14/4/2020 | April | Emily Moore | West | 132 | Cosmo | black | C2699bl | 30 | $375 | $11,250 | Y | $10,688 |
| 42 | 15/4/2020 | April | Emily Moore | West | 144 | Urban | red | U2683rd | 15 | $260 | $3,900 | N | $3,900 |
| 43 | 16/4/2020 | April | Stacy Peters | North | 157 | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 44 | 19/4/2020 | April | Eric Jones | North | 180 | Volt | brown | V2944br | 42 | $295 | $12,390 | Y | $11,771 |
| 45 | 20/4/2020 | April | Eric Jones | North | 132 | Energy | black | E2376bl | 26 | $350 | $9,100 | Y | $8,645 |
| 46 | 22/4/2020 | April | Marc Williams | South | 162 | Urban | gray | U2683gr | 35 | $260 | $9,100 | Y | $8,645 |
| 47 | 23/4/2020 | April | Stacy Peters | North | 144 | Aero | white | A2258wh | 32 | $220 | $7,040 | Y | $6,688 |
| 48 | 27/4/2020 | April | Emily Moore | West | 132 | Volt | brown | V2944br | 18 | $295 | $5,310 | N | $5,310 |
| 49 | 27/4/2020 | April | Marc Williams | South | 180 | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 50 | 30/4/2020 | April | David Garcia | South | 162 | Flash | gray | F2248gr | 38 | $235 | $8,930 | Y | $8,484 |
| 51 | 1/5/2020 | May | Eric Jones | North | 180 | Aero | black | A2258bl | 42 | $220 | $9,240 | Y | $8,778 |
| 52 | 3/5/2020 | May | Emily Moore | West | 162 | Volt | red | V2944rd | 15 | $295 | $4,425 | N | $4,425 |
| 53 | 7/5/2020 | May | Marc Williams | South | 136 | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 54 | 8/5/2020 | May | Sara Davis | West | 136 | Flash | black | F2248bl | 26 | $235 | $6,110 | Y | $5,805 |
| 55 | 12/5/2020 | May | Stacy Peters | North | 152 | Flash | red | F2248rd | 40 | $235 | $9,400 | Y | $8,930 |
| 56 | 13/5/2020 | May | David Garcia | South | 180 | Urban | black | U2683bl | 30 | $260 | $7,800 | Y | $7,410 |
| 57 | 15/5/2020 | May | Marc Williams | South | 152 | Energy | gray | E2376gr | 26 | $350 | $9,100 | Y | $8,645 |
| 58 | 17/5/2020 | May | Stacy Peters | North | 132 | Volt | black | V2944bl | 18 | $295 | $5,310 | N | $5,310 |
| 59 | 19/5/2020 | May | Sara Davis | West | 180 | Flash | gray | F2248gr | 22 | $235 | $5,170 | Y | $4,912 |
| 60 | 21/5/2020 | May | Marc Williams | South | 144 | Energy | black | E2376bl | 42 | $350 | $14,700 | Y | $13,965 |
| 61 | 21/5/2020 | May | Emily Moore | West | 162 | Energy | white | E2376wh | 45 | $350 | $15,750 | Y | $14,963 |
| 62 | 24/5/2020 | May | Marc Williams | South | 132 | Volt | red | V2944rd | 20 | $295 | $5,900 | Y | $5,605 |
| 63 | 26/5/2020 | May | Eric Jones | North | 136 | Volt | black | V2944bl | 22 | $295 | $6,490 | Y | $6,166 |
| 64 | 27/5/2020 | May | David Garcia | South | 157 | Aero | white | A2258wh | 15 | $220 | $3,300 | N | $3,300 |
| 65 | 28/5/2020 | May | Stacy Peters | North | 132 | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 66 | 2/6/2020 | June | David Garcia | South | 178 | Cosmo | gray | C2699gr | 33 | $375 | $12,375 | Y | $11,756 |
| 67 | 5/6/2020 | June | Marc Williams | South | 144 | Urban | black | U2683bl | 22 | $260 | $5,720 | Y | $5,434 |
| 68 | 5/6/2020 | June | David Garcia | South | 136 | Urban | gray | U2683gr | 26 | $260 | $6,760 | Y | $6,422 |
| 69 | 8/6/2020 | June | Eric Jones | North | 132 | Aero | red | A2258rd | 16 | $220 | $3,520 | N | $3,520 |
| 70 | 9/6/2020 | June | Emily Moore | West | 178 | Volt | black | V2944bl | 10 | $295 | $2,950 | N | $2,950 |
| 71 | 9/6/2020 | June | Sara Davis | West | 162 | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 72 | 12/6/2020 | June | Amy Brown | West | 157 | Flash | brown | F2248br | 15 | $235 | $3,525 | N | $3,525 |
| 73 | 14/6/2020 | June | Stacy Peters | North | 132 | Cosmo | gray | C2699gr | 25 | $375 | $9,375 | Y | $8,906 |
| 74 | 15/6/1900 | June | Eric Jones | North | 144 | Volt | gray | V2944gr | 20 | $295 | $5,900 | Y | $5,605 |
| 75 | 18/6/2020 | June | David Garcia | South | 166 | Urban | red | U2683rd | 35 | $260 | $9,100 | Y | $8,645 |
| 76 | 23/6/2020 | June | Marc Williams | South | 178 | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 77 | 24/6/2020 | June | Amy Brown | West | 166 | Aero | white | A2258wh | 16 | $220 | $3,520 | N | $3,520 |
| 78 | 27/6/2020 | June | Sara Davis | West | 162 | Volt | black | V2944bl | 50 | $295 | $14,750 | Y | $14,013 |
| 79 | 29/6/2020 | June | Stacy Peters | North | 178 | Cosmo | gray | C2699gr | 32 | $375 | $12,000 | Y | $11,400 |
| 80 | 29/6/2020 | June | Amy Brown | West | 136 | Flash | white | F2248wh | 14 | $235 | $3,290 | N | $3,290 |
- Create References between Tables and search for information with VLOOKUP
- Utiliser la fonction "VLOOKUP" pour identifier le nom de l'entreprise (présent dans la feuille "Customer Info") relative au Customer ID
Le tableau “Customer Info” se présente comme suit :
| Customer ID | Company Name | Representative |
|---|---|---|
| 132 | Bankia | Lucas Adams |
| 136 | Telmark | Emily Flores |
| 144 | Affinity | Christina Bell |
| 152 | Secspace | Rob Nelson |
| 157 | MarkPlus | Matt Reed |
| 162 | Cruise | Denise Harris |
| 166 | Port Royale | Dan Hill |
| 178 | Vento | Amanda Wood |
| 180 | Milago | Sam Cooper |
- Insertion d’une nouvelle colonne “Company Name”
- Insertion de la fonction suivante :
| Num | Date | Month | Sales Rep | Region | Customer ID | Company Name | Model | Color | Item Code | Number | Price / Unit | Total | Discount | Final Price |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2/1/2020 | January | Eric Jones | North | 132 | Bankia | Flash | black | F2248bl | 15 | $235 | $3,525 | N | $3,525 |
| 2 | 6/1/2020 | January | Amy Brown | West | 144 | Affinity | Urban | red | U2683rd | 22 | $260 | $5,720 | Y | $5,434 |
| 3 | 9/1/2020 | January | Sara Davis | West | 136 | Telmark | Energy | black | E2376bl | 16 | $350 | $5,600 | N | $5,600 |
| 4 | 12/1/2020 | January | Marc Williams | South | 144 | Affinity | Flash | brown | F2248br | 30 | $235 | $7,050 | Y | $6,698 |
| 5 | 12/1/2020 | January | Eric Jones | North | 166 | Port Royale | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 6 | 15/1/2020 | January | Stacy Peters | North | 136 | Telmark | Energy | brown | E2376br | 14 | $350 | $4,900 | N | $4,900 |
| 7 | 18/1/2020 | January | David Garcia | South | 152 | Secspace | Cosmo | white | C2699wh | 8 | $375 | $3,000 | N | $3,000 |
| 8 | 22/1/2020 | January | Amy Brown | West | 132 | Bankia | Flash | brown | F2248br | 22 | $235 | $5,170 | Y | $4,912 |
| 9 | 22/1/2020 | January | Sara Davis | West | 136 | Telmark | Urban | brown | U2683br | 40 | $260 | $10,400 | Y | $9,880 |
| 10 | 26/1/2020 | January | Eric Jones | North | 166 | Port Royale | Energy | black | E2376bl | 25 | $350 | $8,750 | Y | $8,313 |
| 11 | 28/1/2020 | January | David Garcia | South | 157 | MarkPlus | Energy | black | E2376bl | 33 | $350 | $11,550 | Y | $10,973 |
| 12 | 4/2/2020 | February | Marc Williams | South | 178 | Vento | Volt | white | V2944wh | 15 | $295 | $4,425 | N | $4,425 |
| 13 | 7/2/2020 | February | Eric Jones | North | 180 | Milago | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 14 | 8/2/2020 | February | Emily Moore | West | 132 | Bankia | Urban | brown | U2683br | 45 | $260 | $11,700 | Y | $11,115 |
| 15 | 10/2/2020 | February | Amy Brown | West | 180 | Milago | Energy | white | E2376wh | 32 | $350 | $11,200 | Y | $10,640 |
| 16 | 12/2/2020 | February | Marc Williams | South | 166 | Port Royale | Energy | black | E2376bl | 28 | $350 | $9,800 | Y | $9,310 |
| 17 | 14/2/2020 | February | Sara Davis | West | 162 | Cruise | Aero | red | A2258rd | 10 | $220 | $2,200 | N | $2,200 |
| 18 | 15/2/2020 | February | Eric Jones | North | 136 | Telmark | Urban | brown | U2683br | 16 | $260 | $4,160 | N | $4,160 |
| 19 | 19/2/2020 | February | David Garcia | South | 132 | Bankia | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 20 | 21/2/2020 | February | Amy Brown | West | 132 | Bankia | Volt | black | V2944bl | 12 | $295 | $3,540 | N | $3,540 |
| 21 | 26/2/2020 | February | Marc Williams | South | 136 | Telmark | Cosmo | gray | C2699gr | 40 | $375 | $15,000 | Y | $14,250 |
| 22 | 28/2/2020 | February | Stacy Peters | North | 144 | Affinity | Energy | brown | E2376br | 10 | $350 | $3,500 | N | $3,500 |
| 23 | 1/3/2020 | March | Sara Davis | West | 132 | Bankia | Cosmo | black | C2699bl | 25 | $375 | $9,375 | Y | $8,906 |
| 24 | 4/3/2020 | March | Emily Moore | West | 162 | Cruise | Urban | black | U2683bl | 50 | $260 | $13,000 | Y | $12,350 |
| 25 | 7/3/2020 | March | Amy Brown | West | 180 | Milago | Flash | white | F2248wh | 22 | $235 | $5,170 | Y | $4,912 |
| 26 | 9/3/2020 | March | Eric Jones | North | 144 | Affinity | Volt | brown | V2944br | 15 | $295 | $4,425 | N | $4,425 |
| 27 | 11/3/2020 | March | Stacy Peters | North | 166 | Port Royale | Aero | white | A2258wh | 10 | $220 | $2,200 | N | $2,200 |
| 28 | 12/3/2020 | March | Marc Williams | South | 178 | Vento | Energy | black | E2376bl | 20 | $350 | $7,000 | Y | $6,650 |
| 29 | 14/3/2020 | March | Emily Moore | West | 157 | MarkPlus | Flash | gray | F2248gr | 14 | $235 | $3,290 | N | $3,290 |
| 30 | 18/3/2020 | March | Amy Brown | West | 152 | Secspace | Aero | gray | A2258gr | 28 | $220 | $6,160 | Y | $5,852 |
| 31 | 23/3/2020 | March | Emily Moore | West | 162 | Cruise | Flash | black | F2248bl | 12 | $235 | $2,820 | N | $2,820 |
| 32 | 24/3/2020 | March | Eric Jones | North | 180 | Milago | Volt | white | V2944wh | 35 | $295 | $10,325 | Y | $9,809 |
| 33 | 26/3/2020 | March | Marc Williams | South | 178 | Vento | Cosmo | white | C2699wh | 20 | $375 | $7,500 | Y | $7,125 |
| 34 | 28/3/2020 | March | Stacy Peters | North | 152 | Secspace | Aero | gray | A2258gr | 45 | $220 | $9,900 | Y | $9,405 |
| 35 | 2/4/2020 | April | Amy Brown | West | 136 | Telmark | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 36 | 6/4/2020 | April | Emily Moore | West | 132 | Bankia | Energy | black | E2376bl | 14 | $350 | $4,900 | N | $4,900 |
| 37 | 7/4/2020 | April | Marc Williams | South | 157 | MarkPlus | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 38 | 11/4/2020 | April | Sara Davis | West | 132 | Bankia | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 39 | 12/4/2020 | April | Stacy Peters | North | 166 | Port Royale | Flash | black | F2248bl | 45 | $235 | $10,575 | Y | $10,046 |
| 40 | 12/4/2020 | April | Amy Brown | West | 180 | Milago | Aero | white | A2258wh | 24 | $220 | $5,280 | Y | $5,016 |
| 41 | 14/4/2020 | April | Emily Moore | West | 132 | Bankia | Cosmo | black | C2699bl | 30 | $375 | $11,250 | Y | $10,688 |
| 42 | 15/4/2020 | April | Emily Moore | West | 144 | Affinity | Urban | red | U2683rd | 15 | $260 | $3,900 | N | $3,900 |
| 43 | 16/4/2020 | April | Stacy Peters | North | 157 | MarkPlus | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 44 | 19/4/2020 | April | Eric Jones | North | 180 | Milago | Volt | brown | V2944br | 42 | $295 | $12,390 | Y | $11,771 |
| 45 | 20/4/2020 | April | Eric Jones | North | 132 | Bankia | Energy | black | E2376bl | 26 | $350 | $9,100 | Y | $8,645 |
| 46 | 22/4/2020 | April | Marc Williams | South | 162 | Cruise | Urban | gray | U2683gr | 35 | $260 | $9,100 | Y | $8,645 |
| 47 | 23/4/2020 | April | Stacy Peters | North | 144 | Affinity | Aero | white | A2258wh | 32 | $220 | $7,040 | Y | $6,688 |
| 48 | 27/4/2020 | April | Emily Moore | West | 132 | Bankia | Volt | brown | V2944br | 18 | $295 | $5,310 | N | $5,310 |
| 49 | 27/4/2020 | April | Marc Williams | South | 180 | Milago | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 50 | 30/4/2020 | April | David Garcia | South | 162 | Cruise | Flash | gray | F2248gr | 38 | $235 | $8,930 | Y | $8,484 |
| 51 | 1/5/2020 | May | Eric Jones | North | 180 | Milago | Aero | black | A2258bl | 42 | $220 | $9,240 | Y | $8,778 |
| 52 | 3/5/2020 | May | Emily Moore | West | 162 | Cruise | Volt | red | V2944rd | 15 | $295 | $4,425 | N | $4,425 |
| 53 | 7/5/2020 | May | Marc Williams | South | 136 | Telmark | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 54 | 8/5/2020 | May | Sara Davis | West | 136 | Telmark | Flash | black | F2248bl | 26 | $235 | $6,110 | Y | $5,805 |
| 55 | 12/5/2020 | May | Stacy Peters | North | 152 | Secspace | Flash | red | F2248rd | 40 | $235 | $9,400 | Y | $8,930 |
| 56 | 13/5/2020 | May | David Garcia | South | 180 | Milago | Urban | black | U2683bl | 30 | $260 | $7,800 | Y | $7,410 |
| 57 | 15/5/2020 | May | Marc Williams | South | 152 | Secspace | Energy | gray | E2376gr | 26 | $350 | $9,100 | Y | $8,645 |
| 58 | 17/5/2020 | May | Stacy Peters | North | 132 | Bankia | Volt | black | V2944bl | 18 | $295 | $5,310 | N | $5,310 |
| 59 | 19/5/2020 | May | Sara Davis | West | 180 | Milago | Flash | gray | F2248gr | 22 | $235 | $5,170 | Y | $4,912 |
| 60 | 21/5/2020 | May | Marc Williams | South | 144 | Affinity | Energy | black | E2376bl | 42 | $350 | $14,700 | Y | $13,965 |
| 61 | 21/5/2020 | May | Emily Moore | West | 162 | Cruise | Energy | white | E2376wh | 45 | $350 | $15,750 | Y | $14,963 |
| 62 | 24/5/2020 | May | Marc Williams | South | 132 | Bankia | Volt | red | V2944rd | 20 | $295 | $5,900 | Y | $5,605 |
| 63 | 26/5/2020 | May | Eric Jones | North | 136 | Telmark | Volt | black | V2944bl | 22 | $295 | $6,490 | Y | $6,166 |
| 64 | 27/5/2020 | May | David Garcia | South | 157 | MarkPlus | Aero | white | A2258wh | 15 | $220 | $3,300 | N | $3,300 |
| 65 | 28/5/2020 | May | Stacy Peters | North | 132 | Bankia | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 66 | 2/6/2020 | June | David Garcia | South | 178 | Vento | Cosmo | gray | C2699gr | 33 | $375 | $12,375 | Y | $11,756 |
| 67 | 5/6/2020 | June | Marc Williams | South | 144 | Affinity | Urban | black | U2683bl | 22 | $260 | $5,720 | Y | $5,434 |
| 68 | 5/6/2020 | June | David Garcia | South | 136 | Telmark | Urban | gray | U2683gr | 26 | $260 | $6,760 | Y | $6,422 |
| 69 | 8/6/2020 | June | Eric Jones | North | 132 | Bankia | Aero | red | A2258rd | 16 | $220 | $3,520 | N | $3,520 |
| 70 | 9/6/2020 | June | Emily Moore | West | 178 | Vento | Volt | black | V2944bl | 10 | $295 | $2,950 | N | $2,950 |
| 71 | 9/6/2020 | June | Sara Davis | West | 162 | Cruise | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 72 | 12/6/2020 | June | Amy Brown | West | 157 | MarkPlus | Flash | brown | F2248br | 15 | $235 | $3,525 | N | $3,525 |
| 73 | 14/6/2020 | June | Stacy Peters | North | 132 | Bankia | Cosmo | gray | C2699gr | 25 | $375 | $9,375 | Y | $8,906 |
| 74 | 15/6/1900 | June | Eric Jones | North | 144 | Affinity | Volt | gray | V2944gr | 20 | $295 | $5,900 | Y | $5,605 |
| 75 | 18/6/2020 | June | David Garcia | South | 166 | Port Royale | Urban | red | U2683rd | 35 | $260 | $9,100 | Y | $8,645 |
| 76 | 23/6/2020 | June | Marc Williams | South | 178 | Vento | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 77 | 24/6/2020 | June | Amy Brown | West | 166 | Port Royale | Aero | white | A2258wh | 16 | $220 | $3,520 | N | $3,520 |
| 78 | 27/6/2020 | June | Sara Davis | West | 162 | Cruise | Volt | black | V2944bl | 50 | $295 | $14,750 | Y | $14,013 |
| 79 | 29/6/2020 | June | Stacy Peters | North | 178 | Vento | Cosmo | gray | C2699gr | 32 | $375 | $12,000 | Y | $11,400 |
| 80 | 29/6/2020 | June | Amy Brown | West | 136 | Telmark | Flash | white | F2248wh | 14 | $235 | $3,290 | N | $3,290 |
- Utiliser la fonction "VLOOKUP" pour identifier le nom de l'agent commercial (présent dans la feuille "Customer Info") relative au Customer ID
- Insertion d’une nouvelle colonne “Representative”
- Insertion de la fonction suivante :
| Num | Date | Month | Sales Rep | Region | Customer ID | Company Name | Representative | Model | Color | Item Code | Number | Price / Unit | Total | Discount | Final Price |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2/1/2020 | January | Eric Jones | North | 132 | Bankia | Lucas Adams | Flash | black | F2248bl | 15 | $235 | $3,525 | N | $3,525 |
| 2 | 6/1/2020 | January | Amy Brown | West | 144 | Affinity | Christina Bell | Urban | red | U2683rd | 22 | $260 | $5,720 | Y | $5,434 |
| 3 | 9/1/2020 | January | Sara Davis | West | 136 | Telmark | Emily Flores | Energy | black | E2376bl | 16 | $350 | $5,600 | N | $5,600 |
| 4 | 12/1/2020 | January | Marc Williams | South | 144 | Affinity | Christina Bell | Flash | brown | F2248br | 30 | $235 | $7,050 | Y | $6,698 |
| 5 | 12/1/2020 | January | Eric Jones | North | 166 | Port Royale | Dan Hill | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 6 | 15/1/2020 | January | Stacy Peters | North | 136 | Telmark | Emily Flores | Energy | brown | E2376br | 14 | $350 | $4,900 | N | $4,900 |
| 7 | 18/1/2020 | January | David Garcia | South | 152 | Secspace | Rob Nelson | Cosmo | white | C2699wh | 8 | $375 | $3,000 | N | $3,000 |
| 8 | 22/1/2020 | January | Amy Brown | West | 132 | Bankia | Lucas Adams | Flash | brown | F2248br | 22 | $235 | $5,170 | Y | $4,912 |
| 9 | 22/1/2020 | January | Sara Davis | West | 136 | Telmark | Emily Flores | Urban | brown | U2683br | 40 | $260 | $10,400 | Y | $9,880 |
| 10 | 26/1/2020 | January | Eric Jones | North | 166 | Port Royale | Dan Hill | Energy | black | E2376bl | 25 | $350 | $8,750 | Y | $8,313 |
| 11 | 28/1/2020 | January | David Garcia | South | 157 | MarkPlus | Matt Reed | Energy | black | E2376bl | 33 | $350 | $11,550 | Y | $10,973 |
| 12 | 4/2/2020 | February | Marc Williams | South | 178 | Vento | Amanda Wood | Volt | white | V2944wh | 15 | $295 | $4,425 | N | $4,425 |
| 13 | 7/2/2020 | February | Eric Jones | North | 180 | Milago | Sam Cooper | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 14 | 8/2/2020 | February | Emily Moore | West | 132 | Bankia | Lucas Adams | Urban | brown | U2683br | 45 | $260 | $11,700 | Y | $11,115 |
| 15 | 10/2/2020 | February | Amy Brown | West | 180 | Milago | Sam Cooper | Energy | white | E2376wh | 32 | $350 | $11,200 | Y | $10,640 |
| 16 | 12/2/2020 | February | Marc Williams | South | 166 | Port Royale | Dan Hill | Energy | black | E2376bl | 28 | $350 | $9,800 | Y | $9,310 |
| 17 | 14/2/2020 | February | Sara Davis | West | 162 | Cruise | Denise Harris | Aero | red | A2258rd | 10 | $220 | $2,200 | N | $2,200 |
| 18 | 15/2/2020 | February | Eric Jones | North | 136 | Telmark | Emily Flores | Urban | brown | U2683br | 16 | $260 | $4,160 | N | $4,160 |
| 19 | 19/2/2020 | February | David Garcia | South | 132 | Bankia | Lucas Adams | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 20 | 21/2/2020 | February | Amy Brown | West | 132 | Bankia | Lucas Adams | Volt | black | V2944bl | 12 | $295 | $3,540 | N | $3,540 |
| 21 | 26/2/2020 | February | Marc Williams | South | 136 | Telmark | Emily Flores | Cosmo | gray | C2699gr | 40 | $375 | $15,000 | Y | $14,250 |
| 22 | 28/2/2020 | February | Stacy Peters | North | 144 | Affinity | Christina Bell | Energy | brown | E2376br | 10 | $350 | $3,500 | N | $3,500 |
| 23 | 1/3/2020 | March | Sara Davis | West | 132 | Bankia | Lucas Adams | Cosmo | black | C2699bl | 25 | $375 | $9,375 | Y | $8,906 |
| 24 | 4/3/2020 | March | Emily Moore | West | 162 | Cruise | Denise Harris | Urban | black | U2683bl | 50 | $260 | $13,000 | Y | $12,350 |
| 25 | 7/3/2020 | March | Amy Brown | West | 180 | Milago | Sam Cooper | Flash | white | F2248wh | 22 | $235 | $5,170 | Y | $4,912 |
| 26 | 9/3/2020 | March | Eric Jones | North | 144 | Affinity | Christina Bell | Volt | brown | V2944br | 15 | $295 | $4,425 | N | $4,425 |
| 27 | 11/3/2020 | March | Stacy Peters | North | 166 | Port Royale | Dan Hill | Aero | white | A2258wh | 10 | $220 | $2,200 | N | $2,200 |
| 28 | 12/3/2020 | March | Marc Williams | South | 178 | Vento | Amanda Wood | Energy | black | E2376bl | 20 | $350 | $7,000 | Y | $6,650 |
| 29 | 14/3/2020 | March | Emily Moore | West | 157 | MarkPlus | Matt Reed | Flash | gray | F2248gr | 14 | $235 | $3,290 | N | $3,290 |
| 30 | 18/3/2020 | March | Amy Brown | West | 152 | Secspace | Rob Nelson | Aero | gray | A2258gr | 28 | $220 | $6,160 | Y | $5,852 |
| 31 | 23/3/2020 | March | Emily Moore | West | 162 | Cruise | Denise Harris | Flash | black | F2248bl | 12 | $235 | $2,820 | N | $2,820 |
| 32 | 24/3/2020 | March | Eric Jones | North | 180 | Milago | Sam Cooper | Volt | white | V2944wh | 35 | $295 | $10,325 | Y | $9,809 |
| 33 | 26/3/2020 | March | Marc Williams | South | 178 | Vento | Amanda Wood | Cosmo | white | C2699wh | 20 | $375 | $7,500 | Y | $7,125 |
| 34 | 28/3/2020 | March | Stacy Peters | North | 152 | Secspace | Rob Nelson | Aero | gray | A2258gr | 45 | $220 | $9,900 | Y | $9,405 |
| 35 | 2/4/2020 | April | Amy Brown | West | 136 | Telmark | Emily Flores | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 36 | 6/4/2020 | April | Emily Moore | West | 132 | Bankia | Lucas Adams | Energy | black | E2376bl | 14 | $350 | $4,900 | N | $4,900 |
| 37 | 7/4/2020 | April | Marc Williams | South | 157 | MarkPlus | Matt Reed | Volt | gray | V2944gr | 32 | $295 | $9,440 | Y | $8,968 |
| 38 | 11/4/2020 | April | Sara Davis | West | 132 | Bankia | Lucas Adams | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 39 | 12/4/2020 | April | Stacy Peters | North | 166 | Port Royale | Dan Hill | Flash | black | F2248bl | 45 | $235 | $10,575 | Y | $10,046 |
| 40 | 12/4/2020 | April | Amy Brown | West | 180 | Milago | Sam Cooper | Aero | white | A2258wh | 24 | $220 | $5,280 | Y | $5,016 |
| 41 | 14/4/2020 | April | Emily Moore | West | 132 | Bankia | Lucas Adams | Cosmo | black | C2699bl | 30 | $375 | $11,250 | Y | $10,688 |
| 42 | 15/4/2020 | April | Emily Moore | West | 144 | Affinity | Christina Bell | Urban | red | U2683rd | 15 | $260 | $3,900 | N | $3,900 |
| 43 | 16/4/2020 | April | Stacy Peters | North | 157 | MarkPlus | Matt Reed | Cosmo | black | C2699bl | 15 | $375 | $5,625 | N | $5,625 |
| 44 | 19/4/2020 | April | Eric Jones | North | 180 | Milago | Sam Cooper | Volt | brown | V2944br | 42 | $295 | $12,390 | Y | $11,771 |
| 45 | 20/4/2020 | April | Eric Jones | North | 132 | Bankia | Lucas Adams | Energy | black | E2376bl | 26 | $350 | $9,100 | Y | $8,645 |
| 46 | 22/4/2020 | April | Marc Williams | South | 162 | Cruise | Denise Harris | Urban | gray | U2683gr | 35 | $260 | $9,100 | Y | $8,645 |
| 47 | 23/4/2020 | April | Stacy Peters | North | 144 | Affinity | Christina Bell | Aero | white | A2258wh | 32 | $220 | $7,040 | Y | $6,688 |
| 48 | 27/4/2020 | April | Emily Moore | West | 132 | Bankia | Lucas Adams | Volt | brown | V2944br | 18 | $295 | $5,310 | N | $5,310 |
| 49 | 27/4/2020 | April | Marc Williams | South | 180 | Milago | Sam Cooper | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 50 | 30/4/2020 | April | David Garcia | South | 162 | Cruise | Denise Harris | Flash | gray | F2248gr | 38 | $235 | $8,930 | Y | $8,484 |
| 51 | 1/5/2020 | May | Eric Jones | North | 180 | Milago | Sam Cooper | Aero | black | A2258bl | 42 | $220 | $9,240 | Y | $8,778 |
| 52 | 3/5/2020 | May | Emily Moore | West | 162 | Cruise | Denise Harris | Volt | red | V2944rd | 15 | $295 | $4,425 | N | $4,425 |
| 53 | 7/5/2020 | May | Marc Williams | South | 136 | Telmark | Emily Flores | Cosmo | gray | C2699gr | 10 | $375 | $3,750 | N | $3,750 |
| 54 | 8/5/2020 | May | Sara Davis | West | 136 | Telmark | Emily Flores | Flash | black | F2248bl | 26 | $235 | $6,110 | Y | $5,805 |
| 55 | 12/5/2020 | May | Stacy Peters | North | 152 | Secspace | Rob Nelson | Flash | red | F2248rd | 40 | $235 | $9,400 | Y | $8,930 |
| 56 | 13/5/2020 | May | David Garcia | South | 180 | Milago | Sam Cooper | Urban | black | U2683bl | 30 | $260 | $7,800 | Y | $7,410 |
| 57 | 15/5/2020 | May | Marc Williams | South | 152 | Secspace | Rob Nelson | Energy | gray | E2376gr | 26 | $350 | $9,100 | Y | $8,645 |
| 58 | 17/5/2020 | May | Stacy Peters | North | 132 | Bankia | Lucas Adams | Volt | black | V2944bl | 18 | $295 | $5,310 | N | $5,310 |
| 59 | 19/5/2020 | May | Sara Davis | West | 180 | Milago | Sam Cooper | Flash | gray | F2248gr | 22 | $235 | $5,170 | Y | $4,912 |
| 60 | 21/5/2020 | May | Marc Williams | South | 144 | Affinity | Christina Bell | Energy | black | E2376bl | 42 | $350 | $14,700 | Y | $13,965 |
| 61 | 21/5/2020 | May | Emily Moore | West | 162 | Cruise | Denise Harris | Energy | white | E2376wh | 45 | $350 | $15,750 | Y | $14,963 |
| 62 | 24/5/2020 | May | Marc Williams | South | 132 | Bankia | Lucas Adams | Volt | red | V2944rd | 20 | $295 | $5,900 | Y | $5,605 |
| 63 | 26/5/2020 | May | Eric Jones | North | 136 | Telmark | Emily Flores | Volt | black | V2944bl | 22 | $295 | $6,490 | Y | $6,166 |
| 64 | 27/5/2020 | May | David Garcia | South | 157 | MarkPlus | Matt Reed | Aero | white | A2258wh | 15 | $220 | $3,300 | N | $3,300 |
| 65 | 28/5/2020 | May | Stacy Peters | North | 132 | Bankia | Lucas Adams | Flash | brown | F2248br | 35 | $235 | $8,225 | Y | $7,814 |
| 66 | 2/6/2020 | June | David Garcia | South | 178 | Vento | Amanda Wood | Cosmo | gray | C2699gr | 33 | $375 | $12,375 | Y | $11,756 |
| 67 | 5/6/2020 | June | Marc Williams | South | 144 | Affinity | Christina Bell | Urban | black | U2683bl | 22 | $260 | $5,720 | Y | $5,434 |
| 68 | 5/6/2020 | June | David Garcia | South | 136 | Telmark | Emily Flores | Urban | gray | U2683gr | 26 | $260 | $6,760 | Y | $6,422 |
| 69 | 8/6/2020 | June | Eric Jones | North | 132 | Bankia | Lucas Adams | Aero | red | A2258rd | 16 | $220 | $3,520 | N | $3,520 |
| 70 | 9/6/2020 | June | Emily Moore | West | 178 | Vento | Amanda Wood | Volt | black | V2944bl | 10 | $295 | $2,950 | N | $2,950 |
| 71 | 9/6/2020 | June | Sara Davis | West | 162 | Cruise | Denise Harris | Urban | black | U2683bl | 40 | $260 | $10,400 | Y | $9,880 |
| 72 | 12/6/2020 | June | Amy Brown | West | 157 | MarkPlus | Matt Reed | Flash | brown | F2248br | 15 | $235 | $3,525 | N | $3,525 |
| 73 | 14/6/2020 | June | Stacy Peters | North | 132 | Bankia | Lucas Adams | Cosmo | gray | C2699gr | 25 | $375 | $9,375 | Y | $8,906 |
| 74 | 15/6/1900 | June | Eric Jones | North | 144 | Affinity | Christina Bell | Volt | gray | V2944gr | 20 | $295 | $5,900 | Y | $5,605 |
| 75 | 18/6/2020 | June | David Garcia | South | 166 | Port Royale | Dan Hill | Urban | red | U2683rd | 35 | $260 | $9,100 | Y | $8,645 |
| 76 | 23/6/2020 | June | Marc Williams | South | 178 | Vento | Amanda Wood | Energy | black | E2376bl | 22 | $350 | $7,700 | Y | $7,315 |
| 77 | 24/6/2020 | June | Amy Brown | West | 166 | Port Royale | Dan Hill | Aero | white | A2258wh | 16 | $220 | $3,520 | N | $3,520 |
| 78 | 27/6/2020 | June | Sara Davis | West | 162 | Cruise | Denise Harris | Volt | black | V2944bl | 50 | $295 | $14,750 | Y | $14,013 |
| 79 | 29/6/2020 | June | Stacy Peters | North | 178 | Vento | Amanda Wood | Cosmo | gray | C2699gr | 32 | $375 | $12,000 | Y | $11,400 |
| 80 | 29/6/2020 | June | Amy Brown | West | 136 | Telmark | Emily Flores | Flash | white | F2248wh | 14 | $235 | $3,290 | N | $3,290 |
- Perform Data Analysis using PivotTables
- Calculer la Somme Totale des Ventes réalisées par chaque agent commercial pendant les 6 premiers mois de l'année
- Valeurs : Sum of Final Price
- Lignes : Sales Representative
| Row Labels | Sum of Final Price |
|---|---|
| Amy Brown | 56265 |
| David Garcia | 67803 |
| Emily Moore | 76710 |
| Eric Jones | 87433.25 |
| Marc Williams | 118099.5 |
| Sara Davis | 71074.75 |
| Stacy Peters | 84724.25 |
| Grand Total | 562109.75 |
- Calculer la Somme Totale des Ventes réalisées par chaque agent commercial pendant les 6 premiers mois de l'année et les segmenter par mois
- Valeurs : Sum of Final Price
- Lignes : Sales Representative
- Colonnes : Month
| Sum of Final Price | Column Labels | ||||||
|---|---|---|---|---|---|---|---|
| Row Labels | January | February | March | April | May | June | Grand Total |
| Amy Brown | 10345.5 | 14180 | 10763.5 | 10641 | 10335 | 56265 | |
| David Garcia | 13972.5 | 7813.75 | 8483.5 | 10710 | 26823.25 | 67803 | |
| Emily Moore | 11115 | 18460 | 24797.5 | 19387.5 | 2950 | 76710 | |
| Eric Jones | 20805.5 | 7910 | 14233.75 | 20415.5 | 14943.5 | 9125 | 87433.25 |
| Marc Williams | 6697.5 | 27985 | 13775 | 24928 | 31965 | 12749 | 118099.5 |
| Sara Davis | 15480 | 2200 | 8906.25 | 9880 | 10716 | 23892.5 | 71074.75 |
| Stacy Peters | 4900 | 3500 | 11605 | 22359.25 | 22053.75 | 20306.25 | 84724.25 |
| Grand Total | 72201 | 74703.75 | 77743.5 | 121504.75 | 109775.75 | 106181 | 562109.75 |
- Calculer le nombre de chaises vendues et les segmenter par modèle et par mois
- Valeurs : Sum of Number
- Lignes : Model
- Colonnes : Month
| Sum of Number | Column Labels | ||||||
|---|---|---|---|---|---|---|---|
| Row Labels | January | February | March | April | May | June | Grand Total |
| Aero | 10 | 83 | 56 | 57 | 32 | 238 | |
| Cosmo | 8 | 50 | 45 | 60 | 10 | 90 | 263 |
| Energy | 88 | 70 | 20 | 62 | 113 | 22 | 375 |
| Flash | 67 | 35 | 48 | 83 | 123 | 29 | 385 |
| Urban | 62 | 61 | 50 | 90 | 30 | 123 | 416 |
| Volt | 32 | 27 | 50 | 92 | 75 | 80 | 356 |
| Grand Total | 257 | 253 | 296 | 443 | 408 | 376 | 2033 |
