2. In this task, you are given the data on athletes from Olympic Games since 2000 (find Olympi- cAthletes.xlsx on Moodle, see Lecture 6), a) Insert a pivot table based on given data in a new worksheet. Find the three top countries with the most medals in total. (Sort the data from Largest to Smallest) b) Insert a pivot table in a new worksheet. Based on this pivot table find: What the average age of athletes participated in the Olympic Games (both Winter and Summer) from 2000-2012? Which countries had the youngest and oldest delegation of sportsmen? (Sort the data from Smallest to Largest) c) Insert a pivot chart in a new worksheet. Show how the Kazakhstan Olympic Medal Counts (total medals) changed since the 2000 Games. Reflect the information only for the Summer Olympic Games (2000, 2004,...). OlympicAthletes_547006655107e25e2461dee7515dc264 [Protected View] - Excel Sign in Shy File Home Insert Page Layout Formulas Data Review View Tell me what you want to do.. N R w O PROTECTED VIEW Be canctul files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected Vicw. Enable Editing A2 f Aleksey Nemov -- 1 Athlete 2 Neksey Nemov 3 Ian Thorpe 4 Dara Torres 5 Inge de Bruijn 6 Gary Hall dr. 7 Michael Klim 8 Susie O'Neill 9 Jenny Thompson 10 Pieter van den Hongenband 11 Dmitry Sautin 12 Leontien Zijlaard van Moorsel 13 Therese Alshammar 14 Yana Klochkova 15 Lenny Krayzelburg 16 Massimiliano Rosolina 17 Petria Thomas 18 Matt Welsh 19 Simona Amanar 20 Svetlana Khorkina 21 Yekaterina Labanyuk 22 Yelena Zarnolodchikova 23 Florian Rousseau 24 Kong Linghui 25 Lilu 26 Liu Guoliang 27 Wang Nan 28 Venus Williams 29 Olga Brusnikina 30 Mariya Kiselyova 31 Miya Tachibana 32 Mihu Takeda Sheet1 C Age Country 24 Russia 17 Australia 33 United States 27 Netherlands 25 United States 23 Australia 27 Australia 27 United States 22 Netherlands 26 Russia 3D Netherlands 23 Sweden 18 Ukraine 24 United States 22 Italy 25 Australia 23 Australia 20 Romania 21 Russia 17 Russia 17 Russia 26 France 24 China 24 China 24 China 21 China 20 United States 21 Russia 25 Russia 25 lapan 24 Japan D Year 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 F F Closing Ceremony Date Sport 10/1/2000 Gymnastics 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Diving 10/1/2000 Cycling 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Cycling 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Tennis 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming H 1 1 K 1 M N Gold Medals Silver Medals Bronze Medals Bronze Medals Total Medals 2 1 3 6 3 2 0 5 2 0 3 5 3 1 0 4 2. 1 1 4 4 2 2 0 1 1 1 3 0 4. 3 0 1 4 2 0 2 4 1 1 2 4 3 1 0 4 1 0 2 1 3 2. 1 0 3 3 0 0 3 1 1 1 3 0 2 1 3 0 2 1 3 3 2. 0 1 3 1 1 2 0 3 0 2 1 3 2 1 1 0 3 z 1 0 3 1 1 0 2 1 1 1 0 2 0 1 1 2 2 0 0 2 2 0 0 2 2 0 0 2 2 0 0 2 0 0 2 0 2 Activate Windows 0 2 0 2 Go to Settings to activate Windows 2000 2000 Ready - 1 + 100% Sign in Share 5 Olympic/thletes_5d7006655107e25e2161dee75150-21 - Excel File Home Insert Page Layout Formulas Data Review View Tell me what you want to do.. Show Queries Connections Clear AL A BE From Table E Properties Reapply Fruni From From From Other New Existing Refresh Filter Text to Flash Remove Dala Access Web Text Sources Connections Ouery. Recent Sources Edit Links Advanced Columns Fill Duplicates Validation Gel External Dale Gel & Transform Connections Sort & Fiter F18 10/1/2000 E PEEE Sort Consolidate Relationship Manage What I Forecast Group Ungroup Sublutal Data Model Analysis Sheet Forecast Oluline E F G G H 1 K L M N 1 0 0 1 U 1 1 1 0 0 1 1 1 0 0 0 0 1 1 0 1 0 1 1 1 1 1 0 0 0 1 0 0 1 0 0 0 1 1 1 1 0 1 1 1 0 1 A 2924 Sarah Webb 2925 Mike Wolfs 2926 Yin Jian 2927 Vendela Zachrisson-Santn 2928 Vasilij bogar 2929 Luca Agamennoni 2930 Chris Ahrens 2931 Wyatt Allen 2932 Catello Amarante 2933 Salvatore Amitrano 29 34 Can Bae 2935 Michiel Bartman 2936 Dan Beery 2937 Lorenzo Bertini 2938 Yuliya Bichik 2939 Serhiy Biloushchenko 2940 Cath Bishop 2941 Claudia Blasbene 2942 Kathrin Boron 2943 Amber Bradley 2944 Simon Burgess 2945 Donnie Cech 2946 Chun Wei Cheung 2947 Aurica Chirita Parascu 2948 Pete Cipollene 2949 Ed Coude 2950 Iztok Cop 2951 Alison Cox 2952 James Cracknell Sheet1 1 C 27 Great Britain 33 Canada 25 China 26 Sweden 28 Slovenia 21 Italy 28 United States 25 United States 25 Italy 28 Italy 31 Canada 37 Netherlands 29 United States 28 Italy 21 Belarus 22 Ukraine 32 Great Britain 29 Germany 34 Germany 24 Australia 36 Australia 30 South Africa 32 Netherlands 29 Romania 33 United States 29 Great Britain 32 Slovenia 25 United States 32 Great Britain D 2001 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 1 1 0 0 0 1 1 1 0 8/29/2001 Sailine 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/79/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 0 0 0 0 0 0 0 1 1 1 1 0 0 1 0 1 1 1 0 0 n 0 1 1 0 1 1 1 1 0 0 0 1 1 1 0 0 1 0 0 1 1 1 0 0 1 0 0 0 1 1 1 0 1 1 Activate Windows 1 Get Setting to this 0 Ready - 1 + 100% 2. In this task, you are given the data on athletes from Olympic Games since 2000 (find Olympi- cAthletes.xlsx on Moodle, see Lecture 6), a) Insert a pivot table based on given data in a new worksheet. Find the three top countries with the most medals in total. (Sort the data from Largest to Smallest) b) Insert a pivot table in a new worksheet. Based on this pivot table find: What the average age of athletes participated in the Olympic Games (both Winter and Summer) from 2000-2012? Which countries had the youngest and oldest delegation of sportsmen? (Sort the data from Smallest to Largest) c) Insert a pivot chart in a new worksheet. Show how the Kazakhstan Olympic Medal Counts (total medals) changed since the 2000 Games. Reflect the information only for the Summer Olympic Games (2000, 2004,...). OlympicAthletes_547006655107e25e2461dee7515dc264 [Protected View] - Excel Sign in Shy File Home Insert Page Layout Formulas Data Review View Tell me what you want to do.. N R w O PROTECTED VIEW Be canctul files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected Vicw. Enable Editing A2 f Aleksey Nemov -- 1 Athlete 2 Neksey Nemov 3 Ian Thorpe 4 Dara Torres 5 Inge de Bruijn 6 Gary Hall dr. 7 Michael Klim 8 Susie O'Neill 9 Jenny Thompson 10 Pieter van den Hongenband 11 Dmitry Sautin 12 Leontien Zijlaard van Moorsel 13 Therese Alshammar 14 Yana Klochkova 15 Lenny Krayzelburg 16 Massimiliano Rosolina 17 Petria Thomas 18 Matt Welsh 19 Simona Amanar 20 Svetlana Khorkina 21 Yekaterina Labanyuk 22 Yelena Zarnolodchikova 23 Florian Rousseau 24 Kong Linghui 25 Lilu 26 Liu Guoliang 27 Wang Nan 28 Venus Williams 29 Olga Brusnikina 30 Mariya Kiselyova 31 Miya Tachibana 32 Mihu Takeda Sheet1 C Age Country 24 Russia 17 Australia 33 United States 27 Netherlands 25 United States 23 Australia 27 Australia 27 United States 22 Netherlands 26 Russia 3D Netherlands 23 Sweden 18 Ukraine 24 United States 22 Italy 25 Australia 23 Australia 20 Romania 21 Russia 17 Russia 17 Russia 26 France 24 China 24 China 24 China 21 China 20 United States 21 Russia 25 Russia 25 lapan 24 Japan D Year 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 2000 F F Closing Ceremony Date Sport 10/1/2000 Gymnastics 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Diving 10/1/2000 Cycling 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Swimming 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Gymnastics 10/1/2000 Cycling 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Table Tennis 10/1/2000 Tennis 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming 10/1/2000 Synchronized Swimming H 1 1 K 1 M N Gold Medals Silver Medals Bronze Medals Bronze Medals Total Medals 2 1 3 6 3 2 0 5 2 0 3 5 3 1 0 4 2. 1 1 4 4 2 2 0 1 1 1 3 0 4. 3 0 1 4 2 0 2 4 1 1 2 4 3 1 0 4 1 0 2 1 3 2. 1 0 3 3 0 0 3 1 1 1 3 0 2 1 3 0 2 1 3 3 2. 0 1 3 1 1 2 0 3 0 2 1 3 2 1 1 0 3 z 1 0 3 1 1 0 2 1 1 1 0 2 0 1 1 2 2 0 0 2 2 0 0 2 2 0 0 2 2 0 0 2 0 0 2 0 2 Activate Windows 0 2 0 2 Go to Settings to activate Windows 2000 2000 Ready - 1 + 100% Sign in Share 5 Olympic/thletes_5d7006655107e25e2161dee75150-21 - Excel File Home Insert Page Layout Formulas Data Review View Tell me what you want to do.. Show Queries Connections Clear AL A BE From Table E Properties Reapply Fruni From From From Other New Existing Refresh Filter Text to Flash Remove Dala Access Web Text Sources Connections Ouery. Recent Sources Edit Links Advanced Columns Fill Duplicates Validation Gel External Dale Gel & Transform Connections Sort & Fiter F18 10/1/2000 E PEEE Sort Consolidate Relationship Manage What I Forecast Group Ungroup Sublutal Data Model Analysis Sheet Forecast Oluline E F G G H 1 K L M N 1 0 0 1 U 1 1 1 0 0 1 1 1 0 0 0 0 1 1 0 1 0 1 1 1 1 1 0 0 0 1 0 0 1 0 0 0 1 1 1 1 0 1 1 1 0 1 A 2924 Sarah Webb 2925 Mike Wolfs 2926 Yin Jian 2927 Vendela Zachrisson-Santn 2928 Vasilij bogar 2929 Luca Agamennoni 2930 Chris Ahrens 2931 Wyatt Allen 2932 Catello Amarante 2933 Salvatore Amitrano 29 34 Can Bae 2935 Michiel Bartman 2936 Dan Beery 2937 Lorenzo Bertini 2938 Yuliya Bichik 2939 Serhiy Biloushchenko 2940 Cath Bishop 2941 Claudia Blasbene 2942 Kathrin Boron 2943 Amber Bradley 2944 Simon Burgess 2945 Donnie Cech 2946 Chun Wei Cheung 2947 Aurica Chirita Parascu 2948 Pete Cipollene 2949 Ed Coude 2950 Iztok Cop 2951 Alison Cox 2952 James Cracknell Sheet1 1 C 27 Great Britain 33 Canada 25 China 26 Sweden 28 Slovenia 21 Italy 28 United States 25 United States 25 Italy 28 Italy 31 Canada 37 Netherlands 29 United States 28 Italy 21 Belarus 22 Ukraine 32 Great Britain 29 Germany 34 Germany 24 Australia 36 Australia 30 South Africa 32 Netherlands 29 Romania 33 United States 29 Great Britain 32 Slovenia 25 United States 32 Great Britain D 2001 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 1 1 0 0 0 1 1 1 0 8/29/2001 Sailine 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2004 Sailing 8/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/79/2004 Rowing a/29/2001 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing a/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 8/29/2004 Rowing 0 0 0 0 0 0 0 1 1 1 1 0 0 1 0 1 1 1 0 0 n 0 1 1 0 1 1 1 1 0 0 0 1 1 1 0 0 1 0 0 1 1 1 0 0 1 0 0 0 1 1 1 0 1 1 Activate Windows 1 Get Setting to this 0 Ready - 1 + 100%
