Topic: Analyzing Energy use data based on pivot tables and charts, and database functions 1. Transforming raw data into the database format
Data source: www.wiod.org ---> Release 2013 ---> Environmental Accounts. Download workbook denoted as “Energy Use” for a selected (one) country.
All data from individual worksheets should be included in one new sheet, according to the following structure:
EnergyForm Sector Year Value
… … … …
… … … …
… … … …
In the source database, sectors are either marked with long-term descriptive names or hard to remember codes. In the new data layout, one-word (possibly short), own names of sectors should be used. Note that sectors include both producers (industries) and the household sector (denoted FC_HH).
Hints:
• For downloading data from different sheets to a single sheet, you can use, for example, the combination of INDIRECT and INDEX functions.
• Substitution of old to new sector names can be done with the VLOOKUP function.
2. Summaries to be carried out using pivot tables / pivot charts (for each sub-point – on a separate sheet).
a) Total energy use in the economy in subsequent years.
b) Energy use in the economy in subsequent years (columns), and sectors (rows).
c) Structure (shares in%) of energy sources in the electricity production sector (Electricity, gas and water supply) in the selected year (table and pie chart). The structure should only include the following sources: HCOAL, BCOAL, HYDRO, GEOTHERM, SOLAR, WIND, NUCLEAR, HFO, NATGAS.
d) Ranking of sectors by total fuel consumption (DIESEL + GASOLINE) in the selected year, sorting descending (table and bar/column chart).
e) Changes in hard coal consumption (HCOAL) in individual sectors (in TJ) as compared to 1995.
f) Growth rates (changes in% compared to the previous year) of electricity consumption in particular sectors.
g) Electricity consumption by households in subsequent years, expressed in megawatt hours (MWh). The appropriate conversion factor should be used, without adding a column to the source data, but using a calculated field.
h) The number of sectors using biogas in particular years.
3. Based on database functions (D*) calculate:
a) Total energy consumption in the economy in 2007 b) Total energy consumption in the mining sector in 2004
c) Average annual fuel consumption (DIESEL + GASOLINE) in the economy in the period 2005- 2009.
d) The number of sectors using biogas in 2009.
e) The sum of energy consumption from natural gas - only from even years (apply the ISEVEN function in the criteria area).
f) The maximum annual hard coal consumption by the electricity production sector.
The results should be - where possible - compared with the values obtained in the pivot tables.
4. Advanced filter - on its basis, a new database should be created in a separate sheet, containing data for the transport sectors (land, water, air) only, for the years 2000-2009.
