Exercise 6 - Spreadsheet tools and database functions

In next tasks you learn to use different kinds of spreadsheets tools.

1. Fetch weather information of month from following address: <URL: http://appro.mit.jyu.fi/ohjelmistot/demot/saatila6.xls>. Save the file into U:-disk and open it in Excel.
2. In following tasks you can try functionality of some database functions. Next we prepare a little the condition range and weather table:
   • At first make a condition range as in picture on where you can copy headings of weather information. Name the range (2 rows, 4 columns) as condition.

     

   • Name the weather information range and it´s headings asweather.

   Count results conformable of following conditions by using database functions:

   • Count mean temperature (DAVERAGE)from days witch temperature is bigger than -20 degrees. (The result is -5 degrees.)
   • Count mean temperature from days witch temperature is bigger than -20 degrees and the cloudiness is S. (The result is -8,5 degrees.)
   • Count the number of days (DCOUNT) when cloudiness is K (The result is 5 pieces.)
   • Count the number of days when cloudiness is K or S. Notice that you have to expand condition range with one row so it would be better to name a new condition range in use. (The result is 21 pieces.)
   • Count the number of days when temperature is bigger than -2 degrees and smaller than 10 degrees. Notice that you have to expand original condition range with one column (1 row, 5 columns) so it would be better to name a new condition range. (The result is 2 pieces.)
   • Count the number of days when temperature is bigger than -2 degrees or smaller than -15 degrees. What kind of should the condition range now be? (The result is 21 pieces.)
   • Count the max temperature (DMAX) of days when cloudiness is K or S and weather is V. (The result is -23 degrees.)
3. Tool for organizing weather condition table is used in the following tasks. Start organizing the weather condition table by selecting as active the whole table and headings. After that by selecting Data | Sort (finn. Tiedot | Järjestä) you can edit order conditions. Make organizing by observing following criterions:
   • Organize the weather condition table in rising order according to temperature.
   • Primarily organize the weather condition table in rising order according to temperature and in secondary in setting order according to cloudiness.
   • Primarily organize the weather condition table in rising order according to weather and in secondary in setting order according to temperature.
   • How you can organize the weather information into original order with organize tool? Organize the weather condition table into original order.
4. There is a completed blank for handling data of blank form in spreadsheet. Select one single cell as active from weather information. You can get the blank for use by selecting Data | Form (finn. Tiedot | Lomake). Try the functionality of blank by making following tasks.
   • Change weather information of the tenth day with using blank. Check that information were changed into weather condition table.
   • Insert a New (finn. Uusi) day (29.) with using blank and insert also weather information for the new day. Check that the inserted day emerged in the end of weather information.
   • You can end using the blank by selecting Close (finn. Sulje).
5. In following tasks you filter weather information with auto filter. You can open the filter by selecting filtered range and it´s headings at first. After that open the auto filter by selecting Data | Filter | Auto Filter (finn. Tiedot | Suodata | Pikasuodatus). You can change filter conditions by using menus which open into heading row. Make filters for the weather with observing following conditions:
   1. Filter all days in sight when freeze is -30 degrees.
   2. Filter all days in sight when freeze is -30 degrees and cloudiness is clear (S).
   3. Filter all days in sight when freeze is -30 degrees and cloudiness is clear (S) and weather is snowing (L).
   4. Delete all filter conditions from all points. So pick up in sight All (finn. Kaikki). Deleting filter conditions have to be done always when whole new filter conditions are inserted.
   5. Filter 4 coldest days in sight. Tip: (Top 10...)
   6. Filter 5 warmest days in sight.
   7. Filter all days in sight when temperature has been bigger than -23 degrees and when temperature has been smaller than 5 degrees. (Tip: Custom...)
   8. Filter all days in sight when temperature has been bigger than -23 degrees and when temperature has been smaller than 5 degrees and when cloudiness have been partly clouded (P).
   9. Filter all days in sight when temperature has been smaller than -23 degrees and when temperature has been bigger than 5 degrees.

   End Auto filter by selecting Data | Filter | Auto Filter (finn. Tiedot | Suodata | Pikasuodatus).

6. Next make some reports from weather conditions table. There is some differences in points of help if you use different versions about the program.
   • Start making the report by selecting Data | PivotTable Report (finn. Tiedot | Pivot-taulukon raportti)
   • Select the place from where you take the data at first point. In this case select Microsoft Excel List or database (finn. Microsoft Excelin luettelo tai tietokanta) as data. Select what kind of report is created at the same. In this case create a simple Pivot Table (finn. Pivot-taulu).
   • In Next (finn. Seuraava) point define the Range (finn. data-alue) from where you want to make the report. Select weather conditions table and it´s headings as data Range.
   • In Next (finn. Seuraava) point you can choose will you make the report into existing worksheet or into New worksheet (finn. taulukkolaskentalomake).
   • In Next (finn. Seuraava) point you can select the data which come into report. Now it is able to define the headings which come into rows and columns. And also the data which may probably come between intersection.
     • Move PVM-button from PivotTable toolbar as Row field (finn. Rivikenttä) with your mouse.
     • Move Temperatures-button from PivotTable-toolbar as data finn. tieto with your mouse.
     • What you can see from the report?
     • Move PVM -button and Sum of temperature -button away from report with your mouse.

     In next create a new report according to following instructions.

     • Move Cloudiness-button as row field (finn. Rivikenttä) from PivotTable-toolbar with mouse.
     • Move Temperatures-button as data (finn. Tieto) from PivotTable-toolbar with mouse.
     • What you can see from the report?
     • Change the report by double-clicking Sum of Temperatures-button. Change Average (finn. Keskiarvo) as counted result.
     • Change rows of report by using down-menu found from Cloudiness-button. Delete some of the menus choices and approve the changes.

     Delete data of previous report and create a one new report.

     • Move Cloudiness-button as Row field (finn. Rivikenttä) from PivotTable-toolbar with mouse.
     • Move Weather-button as Column field (finn. Sarakekenttä) from PivotTable-toolbar with mouse.
     • Move Temperatures-button as data (finn. Data) from PivotTable-toolbar with mouse.
     • Change the report by double-clicking Sum of Temperatures)- button. Change Count (finn. Lukumäärä) as counted result.
     • What you can see from the report?
     • If there isn´t number 28 in the down corner of the report, edit Cloudiness -fields so that all alternatives of cloudiness will be in sight. It is talk about the number of days of whole month.

     You can edit properties of PivotTable with right mouse button. Try to remove Grand Total -fields of rows and columns.

     If you like you can try to implement also other Pivot-tables from the weather data.

7. In this task you use again the condition range which was created in the first task. On next it meant to filter data of weather table by using conditions of the condition range. On next there are specific instructions for using Advanced filter:
   • Mark a condition into the condition range and use that for filtering days in sight when temperature is bigger than -2 degrees.
   • Select the whole weather conditions table and all it´s headings as active.
   • After that start the Advanced filter by selecting Data | Filter | Advanced Filter (finn. Tiedot | Suodata | Erikoissuodata) .
   • You can choose if you want move filtered data into other place or will you filter data selected directly from the opened window. In this case you can filter the list in place (finn. Suoraan valittuja tietoja).
   • The whole data range is the List Range (lista-alue) which data is filtered. In this case the whole weather conditions table and it´s headings.
   • Criteria range (Ehtoalue) is the range in where the conditions of filtering are. Select the range where you have marked the temperature condition as criteria range.
   • After accepting the information of window filter the data of weather condition table according to conditions of criteria range.
   • You can get all data in sight by selecting Data | Filter | Show All (finn. Tiedot | Suodata | Näytä kaikki) .

   

   Filter the data of weather condition table according to following conditions:

   • Filter all days in sight when cloudiness is K or S. What kind of should the criteria range now be?
   • Filter those days in sight when temperature is bigger than -2 degrees and smaller than 10 degrees. What kind of should the criteria range now be?
   • Filter those days in sight when temperature is bigger than -2 degrees or smaller than -15 degrees. What kind of should the criteria range now be?
   • Try also to copying data to other place when you filter.

Extra exercises

In these extra tasks you exercise bringing the data into Excel from outside.

1. First save following data into U:-disk:
   • Text file: valuutat.txt
   • Database: tietokanta.mdb
2. Bringing the text file organized into columns into Excel can be done by following next help.
   • Select Data | Get External Data | Import Text File and then you can select the text file which you want to bring into Excel from directory structure. Select valuutat.txtas the file.
   • In next point there open a window from where you can select the form of brought file. You can select Delimited (finn. Rajattu tieto) data or Fixed width (finn. Sarakkeissa oleva tieto) data as the form of file. Choose the latter alternative.
   • On next point you select locations of data columns. Select the places of column separators so that all data will come into right column.
   • In next point you can select the type of data which come into columns if you want. You don´t have to do the type selecting because the supposition type is General (finn. Yleinen) -type.
   • At the end choose will you put the data into new one or into existing worksheet. You can define a whole new worksheet as the place.
3. You can also bring tables of Microsoft Access -database to the Excel. Read point "Set up a data source that uses the Microsoft Access driver" from Excel help and according to that bring data from dataa.mdb -database into Excel. You don´t have to do any of the special definitions of help. So the operation is much more easier than the help says.
   • There comes a point on making the inquiry when you can define what data is taken from database. Select all data into the inquiry.
   • If you want you can filter the data when making the inqyiry.
   • If you want you can organize the data when making the inquiry.
   • At the end select point bringing the data into whole new worksheet of Excel.