MS EXCEL 2010 for the Applications of Computers in Agriculture course

1. MS EXCEL 2010for the Applications of Computers in Agriculture course PHẠM QUANG DŨNG, PhD. Dept. of Computer Science Faculty of Information Technology Hanoi University of Agriculture Website: www.hua.edu.vn/khoa/fita/en/pqdung

2. Outline • The Ribbon • Getting started • Formulas and Functions • Managing lists • Charts

3. 1. The Ribbon • Home tab • Insert tab • Page layout tab • Formulas tab • Data tab • Review tab • View tab  Self-study

4. 2. Getting started • Entering data • Selecting ranges • Adjusting Column width / Row height • Wrapping texts • Merging/Splitting cells • Formatting cell borders • Formatting numbers

5. Entering data • Click the cell where you want to enter data • Type the data in the cell • Texts  align left automatically • Numbers  align right, use “.” before decimal places • Dates  in m/d/yy format, align right • Hours  in h:m:s format, align right Note: use ’ before a number to fix it as a text Eg: ’04.38262625 • Press enter or tab to move to the next cell

6. Automatically fill data … to quickly fill in several types of data series (numbers, dates, months) • Enter the first 2 values, then select the two cells • Move the cursor to the small black square in the lower-right corner • Click and hold the mouse then drag the fill handle across the cells, horizontally or vertically.

7. Selecting ranges … to format/copy/move. • Click the first cell in the range, and then drag to the last cell • Hold the Ctrl key to select discrete ranges • Click the column heading to select the whole column • Click All button to select all cells.

8. Adjusting Column width / Row height Column width: • Place the cursor on the line between two columns. • Drag the boundary on the right side of the column heading until the column is the width that you want. • or double click to fit column contents automatically

9. Wrapping texts • Click the cell in which you want to wrap the text. • On the Home tab, in the Alignment group, click Wrap Text.

10. Merging/Splitting cells • Select the range. • On the Home tab, in the Alignment group, click Merge and Center.

11. Formatting cell borders • Select the range • Go to the Home tab, in the Font group • Click the arrow next to Borders • Click on the border style you would like • Task:

12. Formatting numbers • Click the cell(s) containing the numbers, • On the Home tab, in the Number group, click the arrow next to the Number Format box, • Click the format that you want. • For more detail, choose More Number Formats… • Task: Try to format DOB column to be displayed in dd/mm/yy style.

13. Print Preview and Print • From Quick Access Toolbar

14. 3.Formulas and Functions • Introduction to Formulas • Frequently used Functions: • AND, OR • LEFT, RIGHT • SUM, AVERAGE, MAX, MIN, COUNT • DSUM, DAVERAGE,… • IF, SUMIF, COUNTIF • VLOOKUP, HLOOKUP

15. Introduction to Formulas • Always starts with an equal sign (=) • The rest can contain: • Functions, e.g. PI(), TODAY() • Cell references, e.g. A1, A1:B5 • Constants, e.g. 10, 3.14 • Operators: +, -, *, /, ^ • Parentheses ( ) • E.g. =(A1+A2)*2 =PI()*A1^2 =SUM(A1:B5)/100

17. Copy a formula • HOW? • Using the Fill Handle: the same as filling data automatically • Using traditional copy & paste

18. Referencing cells in Formulas • Relative references • E.g. A1 A1:B10 • Used by default • Changed appropriately when you copy the formula • Absolute references • E.g. $A$1 $A$1:$B$10 • Retainedwhen you copy the formula • Press F4 to change a Relative ref. to Absolute ref. • Mixed references • E.g. $A1 A$1

19. Create a formula without function • WHERE? At a cell OR at the Formula bar • HOW? • Select the cell in which you want to create the formula • Type the formula in from your keyboard, (you should) insert a cell reference by clicking your mouse on that cell. • Hit the ENTER key to finish

20. Functions • Structure: Function_name(arg1, arg2,…,arg_n) • Some functions have no args. • E.g. TODAY(), PI() • A string inside must be enclosed by a “ ” couple • A function can be nested in another one • E.g. IF(AND(A1>=8,A2<>“Kinh”),1000,0)

21. Insert a function • WHERE? Formulas tab OR Insert Function button • HOW? • Select the function • Enter the arguments • enter a range by clicking then dragging the cursor over the range. • Press ENTER • You can, of course, type the whole function in

22. Frequently used Functions • AND(arg1, arg2,…, arg_n):a logic function, returns True only if all of args are True. Each arg. can be a logic constant/expression. E.g. =AND(B3>=23,B3<25) • OR(arg1, arg2,…, arg_n): returns False only if all of args are False. E.g. =OR(D3>=25,D3<23)

23. Frequently used Functions (2) • LEFT(“a string”,[n]): • returns ncharacters from the left. n = 1 by defaul. • E.g.1: =LEFT(“GiaLâm – Hà Nội”,7) returns “GiaLâm” • E.g.2: =LEFT(A1) returns the leftmost character of the string in A1 cell • RIGHT(“a string”,[n]): • returns n characters from the right. • E.g. =RIGHT(“GiaLâm – Hà Nội”,6) returns “HàNội” • MID(“a string”,m,n):returns n characters from the mth position.

24. Frequently used Functions (3) • SUM(num1,num2,…,num_n): returns the sum of all args. each arg. can be a constant/cell ref./range ref. • AVERAGE(num1,num2,…,num_n):returns the average of the args.

25. Frequently used Functions (4) • MAX(num1,num2,…,num_n): returns the greatest value. • MIN(num1,num2,…,num_n):returns the smallest value.

26. Ví dụ 1 Ví dụ 2 Frequently used Functions (5) • COUNT(arg1, arg2,…, arg_n): • returns the number of non-text args.

27. Frequently used Functions (6) • IF(logical_test,value_if_true,[value_if_false]): • returns value_if_trueiflogical_testis True • otherwise, returns value_if_false E.g. =IF(A3>=5,“Pass”,“Fail”) • IF can be nested in another IF. Result: - ifĐiểm ≥8 then Họcbổng = 240, - Otherwise: ifĐiểm [7,8) then HB = 120, otherwise (when Điểm <7) then HB = 0.

28. Frequently used Functions (7) • SUMIF(range,“criteria”,sum_range): • Returns the sum of the values within the sum_range that have corresponding items in range satisfying the criteria • E.g. Calculate sum of interest (sum_range) of the items whose capital (range) is greater than 160 (criteria) • You needn’t to use “=” sign in case of equal criteria • E.g. =SUMIF(B2:B5,“200”,C2:C5)

29. Frequently used Functions (8) • COUNTIF(range,“criteria”): returns the number of cells within the range that match the criteria. E.g. 1 E.g. 2

30. Task • Fill in the Result column: Pass or Fail based on Mark >= 5 or not. • Scholarship:240000 if Mark >= 8 120000 if 7<=Mark <8 0 if Mark < 7 • Use IF function to classify students to XS, Giỏi, Khá, TB, Yếu, Kém. • Count the number of students who got scholarship. • Calculate the sum of scholarship whose Mark >=8.

31. Frequently used Functions (9) • VLOOKUP(lookup_value,table,col_index_num,[1/0]): • Vertically looks for lookup_value in the leftmost of the table, and then returns a value in the same row from the col_index_num.

32. VLOOKUP – important notice • VLOOKUP(lookup_value,table,col_index_num,[1/0]): • Use absolute reference for table to make it retain when you copy the formula. • Usage of the 4th argument: • If it is set as 1 (or True) or is omitted: • finds an approximate match: If an exact match is not found, the next largest value that is less than lookup_value is returned • the values in the first column of the tableNEED to be sorted in ascending order • If it is set as 0 (or False): • finds an exact match • the values in the first column of the tableDO NOT NEED to be sorted

33. Frequently used Functions (10) • HLOOKUP(lookup_value,table,row_index_num,[1/0]): • Horizontally looks…

34. Database functions • DSUM, DAVERAGE, DMAX, DMIN, DCOUNT • Function_name(database,“field”,criteria_range): handles the numbers in the field (column) of the records in the database that match the criteria. • E.g. =DSUM(A4:E10,“Profit”,A1:F2) • The 2nd arg. can be replaced by its column_index_number in the database. • E.g. =DSUM(A4:E10,5,A1:F2)

35. Examples of Criteria ranges You should COPY column’s heading from original list to Criteria range! In the same row -> AND In the different row -> OR

36. DSUM Headings MUST be the same

37. DMAX

38. DAVERAGE

39. DCOUNT

40. 4. Managing lists • Sorting data • Subtotals • Filtering data

41. Sorting data • Sorts alphabetically or numerically by some fields • HOW? • Choose the database to be sorted • Menu Data – click Sort button • Select the fields, what to sort on, sort order… then press OK The upper has a higher priority: The lower field is sorted locally in the rows whose upper field’s values are equal

42. Example result sorted A to Z locally

43. Subtotals • Can sum numeric data, find the highest/lowest values and averages, count the number of rows in a group. • HOW? • Sort the list by the field you want to have groups • Menu Data – click Subtotal button • Select the grouped field, function to use, fields to add subtotal to. • Press OK

44. Example result

45. Filtering data: auto or advanced • Auto-filter: • Select the list • Menu Data, click Filter button • Select the drop down arrows from the columns you want to filter data • Tick/Untick to make sure the values you wish to be seen are Ticked, click OK; or • Click Number Filters (or Text Filters,…) to choose an submenu item

47. AutoFilter: Custom Filter… • Here you can make AND/OR conditions on 1 column. • E.g. Filter the list where NS99  [1300,1600) • !!! Note that AutoFilter cannot solve OR conditions between 2 or more fields.

48. Advanced Filter • Need to create Criteria range • Select the list • Menu Data, Sort & Filter frame, click Advanced

49. 5. Charts • Creating • Formatting • Changing the chart layout • Changing the chart design

50. Creating a chart • Select data range • Menu Insert, choose an appropriate type from a chart category (column, line, pie, etc.) • An initial chart appears; we will modify it later… • Here we mention 4 most common used types: • 3-D Clustered Column • Line with Markers • Pie in 3-D • XY Scatter with Smooth Lines and Markers