110 likes | 239 Views
Workshop 3: Data Import. Importing data. Normally, data to be imported into Excel work sheet are available as: - Excel work sheet file (.XLS) - Public source such as website - Data are saved in other file format i.e., text file (.TXT), and etc.
E N D
Importing data • Normally, data to be imported into Excel work sheet are available as: • - Excel work sheet file (.XLS) • - Public source such as website • - Data are saved in other file format i.e., text file (.TXT), and etc. • If data are saved in .XLS files, we simply open and load them into work sheet. • If data are publish in website, we can save webpage as .HTM files and import • them into work sheet or simply cut and paste data from webpage into work • sheet (if webpage allows us to do so). • If data are available in other formats, we might have to try different techniques.
Importing data from web pages • Use copy & paste technique • Some web pages do not allow us to copy data directly • Example: http://www.pcd.go.th/AirQuality/Regional/defaultThai.cfm
Importing data from file • Data to be input into Excel worksheet might not always available in .XLS • Some raw data produces by data acquisition equipment • Many of them are simply text file containing only numbers and characters • For example, climate data distributed by NOAA (US) http://www.atdd.noaa.gov/?q=node/20
Importing data from file Save this file in your computer.
Importing data from file • This type of ASCII text file is usually found in your work • To import data from text file into Excel, you have to guide Excel how the data • are arrange in the file • Data in text file can be put correctly in cell by specifying proper delimiter
Assigning name to data range • When working with large set of data, normal reference to data range could be • painful since it takes time to type, for example, A12:E87 • It is better to refer to a specific range using name • To assign name to data range, select the data range and then • - go to main menu Insert Name… Define • - Type the name you prefer • The data range can be referred to as its assigned name anywhere in work sheet
Basic statistical functions Exercise • 1) From precipitation data, calculate: • Daily precipitation • Average precipitation of the month • Minimum and maximum precipitation of the month • Number of rainy days in the month • 2) From air quality data, calculate: • Minimum, maximum, average, standard deviation, skewness • of each parameters for all provinces