第四章Excel 2010

总字符数: 8.06K

代码: 无, 文本: 4.76K

预计阅读时间: 21 分钟

认识Excel

Excel 2010的窗口组成

名称框
编辑栏
- 单元格名称框
- 按钮组
- 编辑框
列标、行号
当前单元格
工作表编辑区
工作表控制按钮
工作表标签
插入工作表按钮

工作簿与工作表

可以以文件的形式存盘

工作簿:是指在Excel中用来存储并处理数据的文件,其扩展名是.xlsx
工作表(Sheet):是由一个行和列交叉排列的二维表格

单元格

任何的单元格地址都是其列标和行号的组合,且必须是列标在前,行号在后每个单元格都有一个唯一的地址

删除工作表

永久删除无法撤销

数据类型及编辑

数值型数据

数值型数据默认右对齐,数字与非数字的组合均作为文本型数据处理

输入分数时:在分数前输入0(零)及一个空格如:0 5/6
输入负数时应在负数前输入负号或将其置于括号中如:-8或(8)
在单元格中输入超过11位的数字时,Excel会自动使用科学计数法来显示该数字
Excel只保留15位的数字精度

日期和时间型数据

默认状态下:日期和时间型数据在单元格中右对齐

日期分隔符:”/“,”-“
时间分隔符:”:”

如果要基于12小时制输入时间,则在时间(不包括只有小时数和冒号的时间数据)后输入一个空格,然后输入AM或PM来表示上午或下午
Ctrl+;—>当天的日期
Ctrl+Shift+;—>当前时间
AM/PM—>上午/下午

文本型数据

默认左对齐要输入纯数字的文本如身份证号等,应在第一个数字前加英文符号单引号’

数据的清除和删除

清除(delete等同于Backspace):清除单元格格式、单元格中的内容及格式、批注、超链接等单元格本身并不受影响
数据删除的对象是单元格、行或列,即单元格、行或列的删除.删除后,选取的单元格、行或列连同里面的数据都从工作表中消失

数据有效性

用于定义可以在单元格中输入或应该在单元格中输入那些数据

自动填充数据

初值为纯数字型数据
- 直接拖动填充柄—>复制填充
- Ctrl+拖动—>正数部分自动增1
初值为日期时间型数据
- 直接拖动填充柄—>自动增1以日、时为单位
- Ctrl+拖动—>复制填充
初值为纯文字型数据
- 始终为复制填充
初值为文字型数据和数字型数据混合体,填充时文字不变,数字递增减
- 直接拖动填充柄—>最右的数字自动增1
- Ctrl+拖动—>复制填充
初值为Excel预设序列中的数据
- 直接拖动填充柄—>按按预设序列填充
- Ctrl+拖动—>复制填充

公式与函数

公式

公式以”=”开始,一个公式一般包含单元格有引用、运算符、值、常量、函数等几种元素

四类运算符
- 算术运算符
- 比较运算符True and False
  - excel字母比较不区分大小写(字符串比较从第一个开始顺序比较)
- 文本运算符
- 引用运算符
  - “:”称为单元格引用运算符:用于定义一个连续的数据区域
  - “,”称为联合运算符:用于将多个引用合并成一个引用
  - “ “(空格)称为交叉运算符,表示只处理区域中相互重叠的部分
:(冒号)、 (空格)、,(逗号)—>%(百分比)—>^(乘幂)—>*(乘)、/(除)—>+(加)、-(减)—>&(连接符)—> =、<、>、<=、>=、<>(比较运算符)

引用方式

相对引用

单元格地址会随着公式所在位置而异如:=F2+G2
绝对引用

单元格地址不会随着公式所在位置而异形式是在每一个列标及行号前加一个"$"符号.如:$A$2
混合引用

单元格或单元格区域的地址部分是相对引用,部分是绝对引用如:$A2、A$2
F4切换引用方式
三维地址引用

在Excel中,不但可以引用同一工作表中的单元格, 还能引用不同工作表中的单元格.引用格式:[工作簿]工作表名!单元格引用如:[工作簿2]Sheet1!E3

函数出错信息表

错误值

可能的原因

#####

列宽不足 or 日期时间公式产生负值

#>VALUE!

参数或运算对象类型错误 or 公式自动更正功能失效

#DIV/0!

公式被0除

#NAME?

使用了无法识别的文本

#N/A

无可用数值

#REF!

单元格引用无效

#NUM!

数字有问题

#NULL!

试图为不相交的区域指定交叉点

常用函数

求和函数Sum(number1,number2,….)

计算一组数值number1,number2,….的总和(如果错误或无法转换的值将会自动忽略)

单条件求和函数SumIF(range,criteria,[sum_range])

用于对区域中符合指定条件的值求和

需求:统计系别为城市规划的同学的哲学总分
=SUMIF(C3:C11,"城市规划",F3:F11)
range参数(必选):用于条件计算的单元格区域.每个区域中的单元格都必须是数字或名称、数组或包含数字的引用,但空值和文本值将被忽略
criteria参数(必选):用于确定对哪些单元格求和的条件,其形式可以为数字、表达式、单元格引用、文本或函数例如,条件可以表示为32、”>32”、B5、”TRUE”或TODAY()等

任何文本条件或任何含有逻辑或数字符号的条件都必须使用双引号括起来.如果条件为数字,则无需使用双引号
sum_range参数(可选):指要求和的实际单元格.如果sum_range参数被省略,Excel会对range参数中指定的单元格(即应用条件的单元格)求和

多条件求和函数sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, …)

需求:统计系别为”城市规划”性别为”女”的哲学分
=SUMIFS(F2:F11,C2:C11,"城市规划",E2:E11,"女")
sum_range:实际求和区域
criteria_range1:第一个条件区域
criteria1:第一个对应的求和条件
criteria_range2:第二个条件区域
criteria2:第二个对应的求和条件

求平均值函数Average(number1,number2,…..)

计算number1,number2,…..的平均值

number1,number2,…..为要计算平均值的参数,参数可以是数字或涉及数字的名称、数组或引用.
如果数组或单元格引用参数中有文字、逻辑值或空单元格则忽略其值;如果单元格包含零值则计算在内

计数函数Count(value1,value2,….)

Count函数计算单元格以及参数列表中数值数据的个数

如果参数为数字、日期或者代表数字的文本(例如:用引号引起的数字,如”1”),则将被计算在内
逻辑值和直接键入到参数列表中代表数字的文本则被计算在内
如果参数为错误值或不能转换为数字的文本,则不会计算在内
如果参数为数组或引用,则只计算数组或引用中数字的个数,不会计算数组或引用中的空单元格、逻辑值、文本或错误值

筛选计数CountIF(Range,criteria)

统计区域中满足给定条件的单元格个数

需求:统计系别为”城市规划”的人数
=COUNTIF(C2:C11,"城市规划")
range:要统计的单元格区域
criteria:指定的条件表达式

其形式可以为数字、表达式、单元格引用或文本
允许引用的单元格区域中有空白单元格出现

COUNTIFS(条件匹配查询区域1,条件1,条件匹配查询区域2,条件2,以此类推……)

需求:统计系别为”城市规划”哲学分为80分以上的人数
=COUNTIFS(C2:C11,"城市规划",F2:F11,">80")

排名函数Rank(number,ref,order)

返回单元格number在一个垂直区域ref中的排名

需求:排名
=RANK(J3,$J$3:$J$11,0)
number:为需要找到排位的数字
ref:为包含一组数字的数组或引用绝对引用
order:为一数字用来指明排位的方式
- 降序:如果order为0或省略,则Excel将ref当作按降序排列的数据清单进行排位
- 升序:如果order不为零,Excel将ref当作按升序排列的数据清单进行排位

Max/Min(number1,number2,….)

Max、Min分别用来求解数据集的极值(即最大值、最小值)

其中:number1,number2,….为需要找出最大数值的参数区域.参数中的空白单元格、逻辑值或文本将被忽略

IF(logical_test,[value_if_true],[value_if_false])

如果指定条件的计算结果为True,则IF函数将返回[value_if_true];如果该条件的计算结果为False,则返回[value_if_false]

logical_test参数(必选),是计算结果可能为True或False的任意值或表达式
value_if_true参数是计算结果为True时所要返回的值
value_if_false参数是计算结果为False时所要返回的值
需求如果总分大于300为优,大于250为二级优等于200为三级优
=IF(J3>300,"优",IF(J3>250,"二级优",IF(J3=200,"三级优")))

取字符串子串函数Left、Right、Mid

都是字符串提取函数

Left(text,num_chars)

从左向右取
- text:文本,是包含要提取字符串的文本字符串,可以是一个字符串,或是一个单元格引用
- chars:是想要提取的个数
Right(text,num_chars)

从右向左取
- text:文本,是包含要提取字符串的文本字符串,可以是一个字符串,或是一个单元格引用
- chars:是想要提取的个数
Mid(text,start_num,num_chars)

从左向右取,但不一定是从第一个起,可以从中间开始
- text:文本,是包含要提取字符串的文本字符串,可以是一个字符串,或是一个单元格引用
- start_num:要提取的开始字符
- num_chars:是想要提取的个数

AND、OR语法格式为:函数(logical1,logical2,….)

参数必须是逻辑值True或False,或者包含逻辑值的数组或引用.参数中的文本或空白单元格将被忽略.如果指定的单元格区域内包括非逻辑值,则AND将返回错误值”#VALUE!”,表示参数错误

AND函数

所有参数的逻辑值为真时,返回True;只要一个参数的逻辑值为假,即返回False
OR函数

所有参数的逻辑值为假时,返回False;只要一个参数值为真,即返回True

VlookUp(Lookup_value,table_array,col_index_num,[range_lookup])

使用VlookUp函数搜索某个单元格区域的第一列,然后返回该区域相同行上任何单元格中的值

查找李东同学的总成绩
=VLOOKUP("李东",$B$3:$J$11,9)李东为你要查找区域的第一列数据,9代表的是从B3开始数总分列为第9列,区域要为绝对引用,相对引用的话会导致填充时区域随着公式位置而变
Lookup_value(必选参数):即要查找的值,可以是值或引用

如果Lookup_value参数提供的值小于table_array参数第一列中的最小值,则VLookUp将返回错误值#N/A
Table_array(必选参数):即查找的范围
Col_index_num(必选参数):是Table_array参数中要返回的匹配值对应的列号

Col_index_num参数为1时,返回Table_array第一列中的值以此类推
Range_lookup(可选参数):是一个逻辑值,指定希望VlookUp查找精确匹配值还是近似匹配值

如果Range_lookup为True或被省略,则返回精确匹配值或近似匹配值;如果找不到精确匹配值,则返回小于Lookup_value的最大值

系统日期和时间函数

now()返回系统当前时间
year(now()):获取系统当前年份
month(now()):获取系统当前月份
=day(now()):获取系统当前第多少天
=year(now())-mid(A1,7,4)获取多少岁A1:'370509200106012000

mod(number,divisor)=mod(被除数,除数)

date(year,month,day)给出指定数值的日期

在单元格中输入:=date(2021,13,35)结果为:2022-2-4
特别提醒:由于上述公式中,月份为 13,多了一个月,顾延至2022年1月;天数为35,比2022年1月的实际天数又多了4天,故又顺延至2022年2月4日

数据处理

数据清单

具有二维表特性的电子表格在Excel中被称为Excel数据清单又称Excel数据库,其中行表示记录,列表示字段.数据清单的第一行必须为文本类型,为相应列的名称(每一列包含相同类型的数据)

排序

Excel 2010中最多按64个关键字排序,Word 2010表格中,最多按3个关键字排序
排序是对数据清单中的一列或多列数据按升序或降序排列的一种组织数据的手段

简单排序:只需选中该列中的任一单元格

单击”数据”选项卡—>”排序和筛选”组—>”升序”或”降序”命令或”自定义序列”进行排序
复杂排序:对多列进行排序

在需排序的数据清单中选择任一单元格—>”数据”选项卡—>”排序和筛选”组—>”排序”命令—>弹出”排序对话框”

筛选

有条件地筛选出满足某种条件的记录行:而另一部分不满足条件的记录行只是暂时隐藏起来

自动筛选

选择数据清单中的任一单元格—>”数据”—>”排序和筛选”—>”筛选”命令,数据清单中的每个字段名旁边会显示一个向下的三角箭头为筛选器箭头多个列之间只能”与运算”不能”或运算”
高级筛选:根据复合条件或计算条件来对数据进行筛选
筛选条件区域由两部分组成

类似于一个只包含条件的数据清单
- 条件的列标题和具体的筛选条件
- 条件区域中,若条件是”与”关系则将条件放在同一行,若是”或”关系则将条件放在不同行