Microsoft Excel General introduction Basic operations Data visualization Data management 画
1 General introduction Basic operations Data visualization Data management Microsoft Excel
General introduction >Workbook >Worksheet File extension:*.xls /*.xIsx >Interface 2
2 General introduction ➢Workbook ➢Worksheet ➢ File extension: *.xls / *.xlsx ➢Interface
Basic operations Select Cells Single cell A couple of continuous cells A couple of separated cells Whole column and row All cells Deselect cells Cell name B5
3 Basic operations Select Cells Single cell A couple of continuous cells A couple of separated cells Whole column and row All cells Deselect cells Cell name
Input One-click Double-click data text:05055 Input data using 2 fill handles 6 埴充柄 2 6 1.Arithmetic progression 10 Fill handle 2.Geometric progression (right click fill handle, drag,release,growth trend) System offered data:e.g.week Data validation:e.g.1-20 Format cell:e.g.font,color,category Practice fill handles,format
4 One-click / Double-click text: ’05055 Input data using fill handles 1. Arithmetic progression 2. Geometric progression (right click fill handle, drag, release, growth trend) Fill handle Input data System offered data: e.g. week Data validation: e.g. 1-20 Format cell: e.g. font, color, category Practice fill handles, format
Cell references Relative B6,A4,C5:F8 Absolute $B$6,$A$4, $C$5:$F$8 Hybrid A$4,C$5:F$8 Cells in other sheets e.g.sheet2!B3 Embedded flash animation(plum blossom, Lantern Festival)
5 Relative B6 ，A4 , C5:F8 Absolute $B$6 , $A$4 , $C$5:$F$8 Hybrid A$4 , C$5:F$8 Cells in other sheets e.g. sheet2!B3 Cell references Embedded flash animation (plum blossom, Lantern Festival)
Use of formula operand:celI、constant、character、area name、area、 function operator:十、一、*、/、%、入、() Character operator: Relation operator:=、>、、∈、<、◇ 人 =(B4①25)OSUM(D5:F5) Demo:oan examples D4 is computed as the sum of (al-a5)and b2 divided by Practice the use of formula c3 in sheet2
6 Use of formula operand：cell、constant、character、area name、area、 function operator：＋、－、*、／、%、 、（） Character operator：＆ Relation operator：=、>、>=、 =（B4 + 25）/ SUM（D5:F5） Demo: loan_example.xls Practice the use of formula D4 is computed as the sum of (a1-a5) and b2 divided by c3 in sheet2
Operations for areas >Select >Name(name box)area->a1:b5 >Reference (e.g.sum(area))
7 ➢Select ➢Name(name box) area-> a1:b5 ➢Reference (e.g. sum(area)) Operations for areas
Use of functions Sum,if,average,etc.(Formulas Insert function) if (logical test,value if true,value if false) Demos:student scores.xls 1.Check if the math.score is passed or not 2.Decision made for good or bad based on average score (absolute reference) 3.Conditional formatting Practice:5-level score
Use of functions Sum, if, average, etc. (Formulas → Insert function) if (logical_test, value_if_true, value_if_false) Demos: student_scores.xls 1. Check if the math. score is passed or not 2. Decision made for good or bad based on average score (absolute reference) 3. Conditional formatting Practice: 5-level score
Data visualization Edit 0000 BDCCO 70C00 30000 口老电 口水用 40000 口格安机 J0000 20000 I0000 S 直0量 Tue ■ed 刀量 更xi Sat
9 Data visualization & Edit
Data management >Input data (health.xls) >Sort (weight,whole column;heart beat,some data;multi-level, height+weight+diastolic pressure,some data) Filter (great than,between;multi-column;clear) >Subtotal >Classify data according to a given field and compute the result (e.g.sum,average,count) >Note:sort first >Demo: >1.Average of height,weight,heart beat for each class >2.Count the number of students >3.Cancel the subtotal (remove all) >Practice:Compute the average of left and right eyesight for male and female students;count the number of students
10 Data management ➢ Input data (health.xls) ➢ Sort (weight, whole column; heart beat, some data; multi-level, height+weight+diastolic pressure, some data) ➢ Filter (great than, between; multi-column; clear) ➢Subtotal ➢Classify data according to a given field and compute the result (e.g. sum, average, count) ➢ Note: sort first ➢Demo: ➢1. Average of height, weight, heart beat for each class ➢2. Count the number of students ➢3. Cancel the subtotal (remove all) ➢Practice: Compute the average of left and right eyesight for male and female students; count the number of students