第一章认识EXCEL及数据的编辑.docx

1、第一章认识EXCEL及数据的编辑第一章 认识EXCEL2010及数据的编辑1.1 认识界面及操作演示Excel 2010界面如图1.1所示。图1.1 Excel2010界面1. 工作簿 工作簿是计算和存储数据的文件，也就是通常意义上的Excel文件，默认后缀为xlsx。每一个工作簿可由一个或多个工作表组成，在默认的情况下是由3个工作表组成，在早期的 Excel 中，一个工作簿中的工作表数量最多不得超过 255 个，从2007版开始，工作表的数量仅受计算机的可用物理内存限制。2. 工作表它是工作簿的一部分，也称作电子报表，工作的主要对象，由行、列组成的表格，Excel2010工作表的大小为 16

2、,384 列1,048,576 行。每个工作表有一个标签名称，当前活动的白色，其余灰色，默认的三个工作表标签名为Sheet1Sheet3，用户可根据需要增加或删除工作表，并可以更改标签名称。3. 单元格单元格是表格的最小单位，是工作表的行和列的交叉点，每个单元格通过“列标+行号”来表示单元格的位置，如：B2，就表示第B列第2行的单元格。为了便于记忆和理解，也可以为某个单元格另外命一个名称，直接在名称框输入一个名称即可活动单元格命名，此时既可以用该名称也可以使用“列标+行号”引用该单元格。光标所在单元格为活动单元格，如图3.1中边框比较粗的单元格。1.2 数据输入在Excel工作表的单元格中，可

3、以使用两种最基本的数据格式：常数和公式。常数是指文字、数字、日期和时间等数据，还可以包括逻辑值和错误值，每种数据都有它特定的格式和输入方法，为了使用户对输入数据有一个明确的认识，有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。一、输入文本Excel单元格中的文本包括任何中西文文字或字母以及数字、空格和非数字字符的组合，每个单元格中最多可容纳32000个字符数。如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“”（西文单引号），然后输入“001”。虽然在 Excel中输入文本和在其它应用程序中没有什么本质区别，但是还是有一些差异，比如我们在Word、Powe

4、rPoint的表格中，当在单元格中输入文本后，按回车键表示一个段落的结束，光标会自动移到本单元格中下一段落的开头，在Excel的单元格中输入文本时，默认状态是按一下回车键却表示结束当前单元格的输入，光标会自动移到当前单元格的下一个单元格，出现这种情况时，如果你是想在单元格中分行，则必须在单元格中输入硬回车，即按住Alt键的同时按回车键。二、输入分数几乎在所有的文档中，分数格式通常用一道斜杠来分界分子与分母，其格式为“分子/分母”，在Excel中日期的输入方法也是用斜杠来区分年月日的，比如在单元格中输入“1/2”，按回车键则显示“1月2日”，为了避免将输入的分数与日期混淆，我们在单元格中输入分数

5、时，要在分数前输入“0” (零)以示区别，并且在“0”和分子之间要有一个空格隔开，比如我们在输入1/2时，则应该输入“0 1/2”。如果在单元格中输入“8 1/2”，则在单元格中显示“8 1/2”，而在编辑栏中显示“8.5”。三、输入负数在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在()括号内来标识，比如在单元格中输入“(88)”，按一下回车键，则会自动显示为“-88”。四、输入小数在输入小数时，用户可以向平常一样使用小数点，还可以利用逗号分隔千位、百万位等，当输入带有逗号的数字时，在编辑栏并不显示出来，而只在单元格中显示。当你需要输入大量带有固定小数位的数字或带有固定位数的

6、以“0”字符串结尾的数字时，可以采用下面的方法：选择“工具”、“选项”命令，打开 “选项”对话框，单击“编辑”标签，选中“自动设置小数点”复选框，并在“位数”微调框中输入或选择要显示在小数点右面的位数，如果要在输入比较大的数字后自动添零，可指定一个负数值作为要添加的零的个数，比如要在单元格中输入“88”后自动添加3个零，变成“88 000”，就在“位数”微调框中输入“-3”，相反，如果要在输入“88”后自动添加3位小数，变成“0.088”，则要在“位数”微调框中输入“3”。另外，在完成输入带有小数位或结尾零字符串的数字后，应清除对“自动设置小数点”符选框的选定，以免影响后边的输入;如果只是要暂

7、时取消在“自动设置小数点”中设置的选项，可以在输入数据时自带小数点。五、输入日期Excel是将日期和时间视为数字处理的，它能够识别出大部分用普通表示方法输入的日期和时间格式。用户可以用多种格式来输入一个日期，可以用斜杠“/”或者“-”来分隔日期中的年、月、日部分。比如要输入“2001年12月1日”，可以在单元各种输入“2001/12/1”或者 “2001-12-1”。如果要在单元格中插入当前日期，可以按键盘上的Ctrl+;组合键。六、输入时间在Excel中输入时间时，用户可以按24小时制输入，也可以按12小时制输入，这两种输入的表示方法是不同的，比如要输入下午2时30分38 秒，用24小时制输

8、入格式为：2:30:38，而用12小时制输入时间格式为：2:30:38 p，注意字母“p”和时间之间有一个空格。如果要在单元格中插入当前时间，则按Ctrl+Shift+;键。七、 多张工作表中输入相同的内容 几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着

9、出现在选中的其它工作表内。 八、不连续单元格填充同一数据 选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。 九、利用Ctrl*选取文本 如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl*键可选定整个表格。Ctrl*选定的区域为：根据选定单元格向四周辐射所涉及到的有连续数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。 1.3 数据的填充一、一般方法填充序列方法有两种

10、。第一种方法是：先输入前2个单元格内容，选定前两个单元格，当鼠标光标移动到选定区域右下角时，会变成“+”字形状，此时可能通过按住鼠标左键拖动实行自动填充操作。如需要输入的数据为自然递增（递减）数值或字符+自然递增（递减）数值，比如输入“1，2，3”或“a1，a2，a3”，可以采用此填充序列的方式。第一种方法根据前两个单元格中所含数值的差值自动计算后续单元格中数值部分，若要填充的是等比数列，比如要填充“1，2，4，8”，不能采用第一种方法。第二种方法：先输入第一个单元格的值，然后选择该单元格及要填充的单元格，进入选项卡“开始”，选择“编辑”“填充”“序列”，在弹出的对话框中选择“等比数列”，输入

11、步长为2，单击“确定”按钮。二、自定义序列经常需要输入一些有规律的序列文本，比如“甲，乙、丙、丁”，文本中没有包含数值，系统无法自动判断后一个单元格需要填充的内容。此时，我们可以自己定义一个序列，定义方法是：选择菜单“文件选项高级”，单击按钮“编辑自定义列表”，在弹出的对话框输入自定义列表。系统已经定义了月份、季度等常用的序列。三、用公式和函数如果想在Excel中输入连续字母，用普通的办法只能一个一个输入了，有些人会想到，第一单元格输入A,下面单元格输入B，然后选中A，B单元格，然后拖动就可得到A、B、C、D了，结果是不对的，得到的是A、B、A、B，难道真要一个一个输入吗，有没有其他更快捷的办

12、法呢，答案是肯定的。我们完全可以通过excel中的char函数结合Excel序号自动填充功能来完成。在电脑字符编码中，特定的编码数字和特定的字符是对应的，而 CHAR函数可以将这些数字编码转换为相应的字符，例如65对应的是大写A ，97对应小写a ，这样在单元格中只需利用char函数进行转换，就实现了Excel中快速输入连续的26个英文字母了。我们在这里用到CHAR函数，在单元格内输入=char(row(1:1)+64),然后鼠标放到单元格右下角，左键点击向下拖拉可以得出A B CD等等一直往下排列。如果要用小写字母连续输入就是把公式中的64修改成97即可。四、填充条纹 如果想在工作簿中加入漂

13、亮的横条纹，可以利用对齐方式中的填充功能。先在单元格内填入“*”或“”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，右键单击“单元格格式”按钮，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮，如下图：1.4 数据的格式一、一般介绍在Excel的单元格中可以输入多种类型的数据，如文本、数值、日期、时间等。在输入时一般需要先设定单元格的格式，然后再输入相应数据，否则系统将根据输入值的特征自动判断何种类型，有些时候这种类型并不是我们需要的，比如要输入分数“2/3”，在未设定单元格格式为分数时，系统认为是日期格式，转换为“2月3日”。设置单元格格式方法：选定需要设

14、定单元格区域，单击鼠标右键，在弹出的菜单中选择“设置单元格格式”，选择“数字”选项卡，选择相应的格式。图1.2 设置单元格格式如图3.5所示，“对齐”选项卡设置单元格文本对齐方式、显示方式，“字体”选项卡设置单元格的字体，“边框”选项卡设置选定区域单元格的边框线和颜色，“填充”选项卡设置选定区域背景色。二、案例1. 正数前显示正号“+”默认情况下，Excel工作表中负数前都显示负号“-”，而正数前不显示正号“+”。如果我们不仅需要在Excel表中的负数前显示负号，还需要在正数前也显示正号，该怎么办呢?方法如下：选择需要显示正号的单元格区域。单击“开始”选项卡，然后在“数字”组中，单击对话框启动

15、器。单击“数字”选项卡，然后在“分类”框中，单击“自定义”;在“类型”下的方框中，键入“+0;-0;0;”。注意键入的是半角字符，最好是在英文状态下输入。最后单击“确定”按钮。现在，在上述区域中已有的正数前将自动加上正号“+”，而且再在其中输入正数时，正数前也将自动加上“+”。Excel允许用户同时对正数、负数、零值和文本的显示格式进行定义，它们之间以半角分号“;”间隔。例如，在“+0;-0;0;”这个自定义格式中，第1个分号前的“+0”是定义正数的显示格式，第2个分号前的“-0”是定义负数的显示格式;第3个分号前的“0”是定义零值的显示格式;第3个分号后的“”是定义文本的显示格式，即正常显示

16、文本。如果三个分号间的某个位置不键入任何字符，则将该类型数据隐藏起来，比如格式“0;-0;”则隐藏单元格中的零值。知道了这些，你就可以利用这个“;”完成特殊的任务，比如你要隐藏整个工作表中的所有数据(包括文本)，则将整个工作表所有单元格定义为“;”格式即可。但编辑栏能看见。2把数据彻底隐藏起来工作表部分单元格中的内容不想让浏览者查阅，只好将它隐藏起来了。（1）选中需要隐藏内容的单元格(区域)，执行“格式单元格”命令，打开“单元格格式”对话框，在“数字”标签的“分类”下面选中“自定义”选项，然后在右边“类型”下面的方框中输入“；”(三个英文状态下的分号)。（2）再切换到“保护”标签下，选中其中的

17、“隐藏”选项，按“确定”按钮退出。（3）执行“工具保护保护工作表”命令，打开“保护工作表”对话框，设置好密码后，“确定”返回。经过这样的设置以后，上述单元格中的内容不再显示出来。提示：在“保护”标签下，请不要清除“锁定”前面复选框中的“”号，这样可以防止别人删除你隐藏起来的数据。3在录入数据时自动添加计量单位我们在单元格中进行数据录入时，经常需要添加一些单位。例如金额单位元、分数单位元。如果频繁的重复录入这些单位显的比较麻烦，其实我们可以使用自定义格式来实现自动添加计量单位。步骤如下：将需要自动添加计量单位的单元格选中，然后打开“格式”菜单下的“单元格”命令。将打开的单元格格式窗口切换到“数字

18、”标签，在“分类”列表中选择“自定义”，在“类型”中选择对应的数据格式，并在选中的数据类型的格式文本后添加上相应的计量单位，例如元、分等。设置好之后单击“确定” 按钮，这样在经过格式设置的单元格里录入数据时，将会自动添加预设的单位。提示：将数字格式应用于单元格之后，如果Excel在单元格中显示 #，则可能是单元格不够宽，无法显示该数据。若要扩展列宽，请双击包含出现#错误的单元格的列的右边界。这样可以自动调整列的大小，使其适应数字。您也可以拖动右边界，直至列达到所需的大小。在大多数情况下，无论您在工作表中输入数字，还是从数据库或其他外部源导入数字，数字都会按照您期望的方式显示。但是，Excel偶

19、尔也会应用错误的数字格式，因此您可能需要进行一些调整。例如，如果您键入一个包含斜杠标记 (/) 或连字符 (-) 的数字，Excel可能将其解释为日期，并将其转换为日期格式。如果您希望键入非计算值(例如 10e5、1 p 或 1-2)，但不希望 Excel 将该值转换为内置的数字格式，则可在单元格中应用“文本”格式，然后再输入数字。如果内置的数字格式不能满足您的需要，您也可以创建自己的自定义数字格式。由于用于创建数字格式的代码难以快速理解，因此最好先使用某一种内置数字格式，然后再更改该格式的任意一个代码节，以创建自己的自定义数字格式。若要查看内置数字格式的数字格式代码，请单击“自定义”类别，然

20、后查看“类型”框。例如，使用代码 =9999999#-#;(#) #-# 可以显示电话号码 (555) 555-1234。1.5 数据的有效性一、案例1：设置输入数据的范围可以设定某些单元格区域输入的内容限制，以避免一些输入错误，比如在输入年龄列，可以规定输入值的有效范围为18-65的整数。操作步骤：（1）选定单元格区域。（2）选择“数据”选项卡，单击“数据有效性”，如下图所示对话框，在“设置”选项卡的有效性条件输入允许输入的数据类型及其范围，另外还可以设置当输入的值不满足条件时的错误提示信息等选项。（3）当用户在该单元格区域内输入的值不是整数或范围不在18,65之间时，系统提示错误信息，不接

21、收用户输入。图 数据有效性设置二、案例2：让中、英文输入法智能化地出现在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，反复切换输入法实在不方便，怎样设置一下让输入法智能化地调整呢？选中需要输入中文的单元格区域，执行“数据有效性”命令，打开“数据有效性”对话框，切换到“输入法模式”标签下，按“模式”右侧的下拉按钮，选中“打开”选项后，“确定”退出。以后当选中需要输入中文的单元格区域中任意一个单元格时，中文输入法(输入法列表中的第1个中文输入法)自动打开，当选中其它单元格时，中文输入法自动关闭。三、案例3：建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性

22、，利用“数据有效性”功能建了一个分类下拉列表填充项。1在工作表中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。2 选中需要输入“企业类别”的列(如F列)，执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”序列(各元素之间用英文逗号隔开，不要引号)，确定退出。这样，单击F列任意单元格，用右侧下拉按钮，选择相应的“企业类别”填入单元格。3选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字

23、符后，按“回车”键进行确认。仿照上面的操作，将B、C列分别命名为“商业企业”、“个体企业”,(定义名称：也可以到“公式-名称管理器”查看或定义)。定义的名称一定要同F列单元格里的值一致。4选中需要输入企业名称的单元格(如G2)，再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT(F2)，确定退出。F2里的值应该是定义了的名称。5选中F列任意单元格(如F2)，单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的G列单元格(如G2)，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。提示：在以后打印报表时

24、，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。 1.6 条件格式在工资表中，如果想让大于等于2000元的工资总额的字体以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置：选中“工资总额”所在列数据，执行“格式条件格式新建规则”命令打开“新建格式规则”对话框选择规则类型“只为包含以下内容的单元格设置格式”，在“编辑规则说明：”选中“大于或等于”选项，在后面的方框中输入数值“2000”。 如下图： 单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为

25、“红色”。重复上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。 设置完成后看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。注意，颜色有“字体”“边框”“填充”不同选择。条件格式的选项很多，请大家仔细看，多练习。1.7 练习1.“编辑选择定位条件”应用案例：轻松插入工资细目数据行其实用Excel打印工资条，归根结底就是让Excel制作的工资表格中，一行工资细目数据，一行员工的记录。如果在每一个员工数据的上面插入一行工资细目数据显然我们的要求也就完成了，当然纯手工的一行一行插入显然是个“不可能完成的任务”，这里需要有点小技巧。第

26、一步：在工资细目的右侧两列中，交叉输入任意数字(主要是为了后面的“空位”空值，所以数字可任意输)，然后选中交叉的四个单元格，双击右下角的“填充柄”，使这种格式一直填充至工资表的结束行。第二步：执行“编辑”“定位”命令，在打开的“定位”对话框中单击“定位条件”按钮，在打开的“定位条件”对话框中，选择“空值”，然后单击“确定”按钮。第三步：执行“插入”“行”命令，这时便会从第三行开始，每一行的前面插入了一个空行。第四步：复制表头的工资细目数据，然后选中工资表的数据区域，执行“编辑”“定位”命令，在打开的“定位”对话框中单击“定位条件”按钮，在打开的“定位条件”对话框中，选择“空值”，然后单击“确定”按钮，这时会选中刚插入的空行，执行“编辑”“粘贴”命令，这时工资条就算制作完成了。2.借款单-（格式、单位的练习）3.成本明细表（条件格式的练习）