第4章Excel操作部分

第4章Excel20031、基本概念工作表默认有3张sheet1、sheet2、sheet3工作表最多可有255张一张工作表由65536个行、256个列组成行号1到65536编号（上→下）；列号（左→右）用字母编号，由A到IV。行、列交叉位置称为单元格一个工作簿可有多个工作表，为区分不同工作表的单元格，可在单元格地址前加上工作表名来区别例如：Sheet3!A3表示Sheet3工作表中的A3单元格2、创建、保存、关闭、打开工作簿和工作表3、工作表重命名，新增工作表方法：（1）工作表重命名：右单击工作表标签，重命名，输入工作表新的名称（2）新增工作表：右单击工作表标签，插入，选“工作表”，确定，在当前工作表的前面（左边）插入一张新的空工作表（3）改变工作表的位置：拖曳某工作表的标签到相应位置即可4、在工作表中输入日期和时间的信息方法：（1）单击选中某单元格（2）Ctrl+;输入当前日期（3）Ctrl+Shift+;输入当前时间5、选择工作表中的内容方法：（1）选中行、多行（按住Ctrl+行号）（2）列、多列（按住Ctrl+列号）（3）整表（单击工作表左上角全选按钮）（4）连续区域（拖曳）（5）多个不连续的区域（先按Shift+F8打开增加模式，状态栏上显示ADD（或显示“添加”），再按一次Shift+F8可取消增加模式，然后拖曳选可中多个不连续的区域）6、单元格的设置方法：（1）选择相应的单元格，右单击，设置单元格格式（2）“数字”选项卡，设置数据类型，包括文本、数值、货币等注意：1）数字前面的0被忽略，如果要保留0，可将单元格设置为文本类型2）若要将数值型的数据保留若干位小数位，也可在“数字”选项卡中设置（3）“对齐”选项卡，设置对齐方式，水平对齐，包括左、居中、右、两端对齐、跨列居中；垂直对齐，包括上、中、下、两端对齐、分散对齐注意：1）标题的“跨列居中”和“合并单元格居中”是有区别的2）这是合并单元格并居中的效果，不能选中标题中的某个单元格3）这是跨列居中的效果，可以选中标题中的某个单元格（4）“字体”选项卡，设置字体，包括字体、字型、字号、字体颜色、下划线例如：以下是将“上海第二工业大学”水平方向在A1~F1跨列居中，垂直方向居中，蓝色，幼圆，斜体，字号24在A1单元格内输入：上海第二工业大学，拖曳选中A1~F1，右单击，设置单元格格式，“对齐”选项卡，“水平对齐”选择“跨列居中”，“垂直对齐”选择“居中”，“字体”选项卡，字体中选择“幼圆”，字形中选择“倾斜”，字号中选择“24”，颜色中选择蓝色（2行6列），确定设置后的效果如下：（5）“边框”选项卡，设置单元格边框线（包括外部框线和内部框线），主要用于表格的制作（详见下面的综合举例）（6）“图案”选项卡，设置单元格的填充色和填充图案例如：接上例，将“上海第二工业大学”的底纹设置为“细对角线剖面线”（填充图案），左右两个单元格设置为粉红色（填充色）拖曳选中B1~E1（“上海第二工业大学”所在区域），右单击，设置单元格格式，“图案”选项卡，“图案”下拉列表中选择“细对角线剖面线”（3行6列），确定，单击A1，按住Ctrl，单击F1，同时选中这两个单元格，右单击，设置单元格格式，“图案”选项卡，颜色区选择粉红色（5行1列），确定设置后的效果如下：（7）设置行高、列宽1）行高例如：接上例，将第1行的行高设置为45右单击行号1，行高，输入45，确定设置后的效果如下：2）列宽例如：将H列的列宽设置为10右单击列号H，列宽，输入10，确定设置后的效果如下：（8）文字的旋转例如：在G2单元格内输入“上海第二工业大学”，要求纵向排列文字在G2中输入：上海第二工业大学，右单击G2单元格，设置单元格格式，“对齐”选项卡，单击“方向”区的“文本”按钮（见下图），确定设置后的效果如下：7、复制数据或公式例如：在A1~A20中填写连续的数字30~49方法：（1）单击A1，输入：30（2）单击A2，输入：=A1+1，A2中显示31（即A1+1的结果）（3）拖曳A2单元格的填充柄到A20单元格，可看到从A3到A20单元格中自动填入了32~498、公式计算：在单元格中输入“=公式”例如：计算学生的总评成绩，总评成绩=平时成绩*0.3+考试成绩*0.7，并用此公式计算某一学生的总评成绩（平时：80，考试：75）方法：（1）在C1单元格中输入：=A1*0.3+B1*0.7，按回车键（2）在A1单元格中输入80（平时成绩），在B1单元格中输入75（考试成绩），在C1单元格中可看到76.5（总评成绩）例如：计算5个学生的平均成绩，5个学生的成绩分别为：77，65，83，49，92方法：（1）在A1~A5中分别输入：77，65，83，49，92（横向输入也可以）（2）在A6单元格中输入：=AVERAGE(A1:A5)，按回车（3）在A6单元格中显示73.2（5个学生的平均成绩）注意：使用公式进行计算时，需加上=9、排序（见综合举例）10、分类汇总（见综合举例）11、数据透视表、数据透视图（见综合举例）12、统计图（柱状图、饼图等）（见综合举例）13、冻结窗口作用：当Excel窗口滚动时，始终显示某些单元格（如标题行等）例如：冻结A1~B3区域的单元格，当Excel窗口滚动时，始终显示A1~B3的信息方法：（1）单击C4单元格（第4行第3列）（2）菜单中依次选择：窗口，冻结窗口（3）在C4单元格的上面和左边分别出现一条直线，直线上面部分和左边部分在屏幕滚动时将被冻结（4）向下、向右滚动屏幕，查看冻结的效果设置后的效果如下：14、记录单作用：用于批量输入表格数据例如：（P48）打开Ex7.xls文件，建立记录单方法：（1）打开Ex7.xls文件，光标定位在Sheet1工作表的任意单元格内（2）菜单中依次选择：数据，记录单说明：1）用记录单可编辑表格中的数据2）可以建立一个空表，用记录单的形式填写数据设置后的效果如下：15、函数随机数：rond()产生0~1之间的随机数符号函数：sign(表达式)取表达式的符号1（正数），0（零），-1（负数）余数：mod(数值1，数值2)取两数相除的余数最大值：max()最小值；min()求和：sum()平均值：average()查找：find()日期函数：date()日期year()年month()月day()日字符串处理函数：trim()截去字符串两端的空格left()取字符串左边若干字符mid()取字符串中间若干字符right()取字符串右边若干字符len()取字符串的长度（字符个数或汉字个数）例如：在A1单元格中输入：广东省广州市天河软件公司在其他单元格中分别输入以下函数，可得到相应的结果=left(a1,3)广东省=mid(a1,4,3)广州市=right(a1,6)天河软件公司=len(a1)1216、选择性粘贴如表格是数据行、列置换17、筛选1）自动筛选例如：打开Ex7.xls文件，自动筛选出“教授”和“副教授”的记录（记录指表格中的行）方法：（1）打开Ex7.xls文件，光标定位在Sheet1工作表的任意单元格内（2）菜单中依次选择：数据，筛选，自动筛选，各字段名右边出现一个下拉箭头（3）单击“职称”右边的下拉箭头，选“自定义”，输入以下条件，确定注意：条件之间的关系应该选择“或”筛选后的效果如下：2）显示全部记录菜单中依次选择：数据，筛选，全部显示3）取消自动筛选菜单中依次选择：数据，筛选，自动筛选（即取消“自动筛选”前面的勾），各字段名右边的下拉箭头消失（即不筛选状态）4）高级筛选例如：（P50）打开Ex7.xls文件，筛选出职称是讲师，且基本工资不低于2000元的记录方法：（1）打开Ex7.xls文件，选Sheet1工作表（2）在空白单元格内输入：注意：按以上方式输入两个条件，条件数值在同一行上，表示这两个条件要同时满足，即条件之间的关系是“与”如果要将条件之间的关系设置为“或”，应将条件数值输入在不同的行上，例如，要筛选出职称是讲师，或者工资不低于2000的所有记录，条件输入见下图：（3）将光标放在任意空的单元格内（4）菜单中依次选择：数据，筛选，高级筛选，出现对话框（5）光标放在“列表区域”对应的文本框内，拖曳选中A1~F13区域（表格区域）（6）光标放在“条件区域”对应的文本框内，拖曳选中A15~B16区域（条件区域）（7）确定高级筛选后的效果如下：5）显示全部记录（取消高级筛选）菜单中依次选择：数据，筛选，全部显示6）高级筛选的另一个例子例如：（P51）打开Ex7.xls文件，筛选出“教授”或“女性、副教授”或“30岁以下”的人员记录方法：（1）打开Ex7.xls文件，选Sheet1工作表（2）在空白单元格内输入：（3）将光标放在任意空的单元格内（4）菜单中依次选择：数据，筛选，高级筛选，出现对话框（5）光标放在“列表区域”对应的文本框内，拖曳选中A1~F13区域（表格区域）（6）光标放在“条件区域”对应的文本框内，拖曳选中A15~C18区域（条件区域）（7）确定高级筛选后的效果如下：18、IF函数的使用IF函数的基本格式：IF（条件，表达式1，表达式2）当条件满足时，显示表达式1当条件不满足时，显示表达式2例如：（P56）在Excel中输出九九乘法表（输出样式与书上略有不同）方法一：用行号列号实现（1）在A1单元格中输入：=IF(AND(ROW()10,COLUMN()10,COLUMN()=ROW()),ROW()&×&COLUMN()&=&ROW()*COLUMN(),)说明：1）AND表示后面括号内的多个条件之间的关系是“与”（同时满足）2）&表示字符串的连接3）ROW()表示Excel工作表中的行号，COLUMN()表示Excel工作表中的列号4）×可通过插入特殊符号来实现5）输入公式的时候，可以大写，也可以小写（2）拖曳A1的填充柄到I1（共9列）（3）拖曳I1的填充柄到I9（共9行9列）操作完的效果如下图：方法二：用数字序列实现（1）建立表头（列标题和行标题）：A1中输入1，B1中输入=A1+1，拖曳B1的填充柄到I1，A2中输入=A1+1，拖曳A2的填充柄到A9设置后的效果如下：（2）在B2单元格内输入：=IF(AND($A2,B$1,B$1=$A2),B$1*$A2,)（3）拖曳B2的填充柄到I2（不算表头，共8列）（4）拖曳I2的填充柄到I9（不算表头，共8行）操作完的效果如下图：说明：1）相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1当将公式复制到C2单元格时变为：=A2+B2当将公式复制到D1单元格时变为：=B1+C12）绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1当将公式复制到C2单元格时仍为：=$A$1+$B$1当将公式复制到D1单元格时仍为：=$A$1+$B$1规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。3）观察本例：公式中的A2特指行标题，行单元格变化时，只能变后面的数字，前面的A不能变（始终为第1列），所以在A的前面加上了$符号；公式中的B1特指列标题，列单元格变化时，只能变前面的字母，后面的1不能变（始终为第1行），所以在1的前面加上了$符号综合举例：小区订报管理小区共有3幢楼，分别编号为1~3，每幢楼有3层，每层有3户居民，分别编号01~03（如1层3户居民的编号为101~103）小区住户在2010年共订阅3种报纸，分别为新闻晨报、解放日报、新民晚报，由小区居委会负责每天的投递每户居民可以订阅其中任意1种或多种报纸，但每种报纸最多只能订阅1份，也可以都不订阅根据以上叙述，打开名为“test.xls”的文件，进行以下操作，结果以同名文件保存1、工作表1：根据给定的小区每户居民订阅报纸信息，对每户居民、每种报纸的订阅数量汇总，加标题“绿叶小区2010年度订阅报纸汇总”，单元格内字符居中，表格加内细外粗框线，标题