电子表格培训文稿课件

EXCEL电子表格基本应用技巧简介序

Excel翻译成英语就是优秀、卓越的意思，现在成了电子表格软件的代名词。

简单的说，Excel是一个电子表格软件，它具有强大的数据处理与分析功能，我们甚至可以以它为平台，利用其内置的VBA编程语言，设计功能齐备的专业管理系统。

Excel作为微软Office组件之一，起源于1982年，从最初的Excel2.0，经过Excel5.0、Excel7.0、Excel97、Excel2000、Excel2003、Excel2007，到目前为止升级为Excel2010，其功能不断提升。在当今的信息时代，Excel被各行业广泛使用。

目前，我们接触得最多的是Excel2003。本课程的主要内容及示例文件都是Excel2003的版本。Excel是什么？它又能做什么？第一篇认识Excel1.Excel的文件

使用Excel程序创建的文件类型主要有以下几种：

1).工作簿文件（.xls）

2).模板文件（.xlt）

3).加载宏文件（.xla）

4).工作区文件（.xlw）

5).备份文件（.xlk）

6).网页文件（.mht、htm）理解Excel相关概念

默认情况下，打开Excel，窗口中有纵横交错的灰色细实线，这是网格线，打印时是不会打印的。我们可以通过依次点击“工具”-“选项”-“视图”里的“窗口设置”，设置是否显示网格线。

表格边框用来划分表格区域，增强其视觉效果，打印时随工作表内容一起打印。依次点击“格式”-“单元格”，打开“单元格格格式”对话框，选择“边框”选项卡，设置单元格和区域的边框线条类型、颜色及位置。二、网格线与表格边框在标题单元格中添加对角线边框线条，通常是左上至右下的斜线。输入标题时，先输入下方行标题，再输入右方列标题。将行标题设置为“下标”、列标题设置为“上标”，调整行高列宽以及标题之间添加空格改善显示效果。除此之外，在单元格内强制换行也可以实现这种显示效果。而两个以上的标题单元格只有通过绘制图形和添加“文本框”实现。*

斜线表头的制作在工作表中，尽量避免在内容以外的区域填充颜色（包括白色）、设置边框线、调整行高列宽及合并单元格等设置，因为程序会将这些设置识别为工作表信息，使用时降低运算速度，保存时增加内存，最令人困惑的时打印时无端地多出几张甚至更多的空白页面。

提示在Excel中输入公式和函数时，输入参与运算的符号必需是半角符号，输入全角符号Excel会识别为文本，导致计算错误或失败。按<Shift>+<Ctrl>键打开输入法，鼠标左键单击第三个方框区域，符号输入方式切换为半角符号。*

半角符号3.输入数据技巧

1).利用填充柄填充数据

如下图，选中A2单元格，将光标移至单元格右下角，光标变成加号，按鼠标左键向下拖拽至A7单元格，A3:A7单元格都自动填充A2单元格的数据“1”，按鼠标左键向下拖拽时同时按<Ctrl>键，光标变成大小两个加号，A3:A7单元格以A2单元格数值“1”自动填充序列。选中B2:B3单元格，将光标移至B3单元格右下角，光标变成加号，按鼠标左键向下拖拽至B7单元格，B3:B7单元格以B2:B3单元格的日期序列填充。选中C2单元格，将光标移至C2单元格右下角，光标变成加号，双击鼠标左键，C3:C7单元格都自动填充C2单元格的数据“变速鼓”。

2).在多个单元格同时输入数据

按<Ctrl>键选中需要输入同一内容的单元格区域，输入需要的内容，按<Enter>键结束。

3).强制换行

单元格内输入大量的文字信息时，由于文本过长，无法在一行内完全显示。虽然通过设置单元格格式“自动换行”可以将文本内容分行显示，但换行的位置完全由单元格的列宽决定，影响显示效果。我们需要整个文本内容按自己的意愿显示段落格式，可以使用“强制换行”功能。当单元格处于编辑状态时，在需要换行的地方同时按<Alt>+<Enter>键完成。

5).设置数据有效性

在编辑Excel表格时，我们经常在某些单元格内输入一些固定的内容，为了更快捷的输入，我们可以通过设置“数据有效性”中的“序列”来达到这个目的。

A.选取需要设置“数据有效性”的单元格或区域，依次点击工具栏菜单“数据”-“有效性”，弹出“数据有效性”菜单。

B.点击“设置”，在允许条件框中选择“序列”，在来源条件框中输入需要的文字，中间用半角逗号隔开，也可以引用表格中的已有的数据内容，点击“确定”退出。

C.点击已经设置数据有效性的单元格，你会发现多了一个下拉箭头，点击箭头，选择需要输入的内容。1.单元格格式

通过菜单工具栏中的格式菜单，我们可以根据自己的需要调整单元格内容的字体、字号、字体颜色等，以及单元格的边框、填充颜色等。四、格式化工作表2.数字格式

在菜单栏上依次点击“格式”-“单元格”打开“单元格格式”对话框，点击“数字”选项，我们可以对单元格内容进行进一步格式设置。3.自定义格式

在菜单栏上依次点击“格式”-“单元格”打开“单元格格式”对话框，点击“数字”选项，选择“自定义”选项，在“类型”框中，编辑数字格式代码以创建所需的格式。3.打印标题

在多页打印时，有时需要将标题行或标题列重复打印在各页面上。在菜单栏上依次点击“文件”-“页面设置”，弹出“页面设置”对话框，选择“工作表”选项，鼠标定位在“顶端标题行”或“左侧标题行”的编辑栏中，然后在工作表上选择需要的标题行或标题列，按“确定”完成。4.非连续区域的打印

如果选取的打印区域的是非连续的单元格区域，Excel会将各个区域分别打印在单独的页面上。有时需要将非连续的单元格区域连续打印，事实上是可以办到的。下面介绍两种变通的方法。

1).图片转移

选取需要打印的区域，按<Shift>键，依次点击“编辑”-“复制图片”，将选取的区域粘贴到其他空白的地方，各区域图片首尾相接，然后打印图片区域。（该方法操作简单，但原始内容编辑修改后图片区域不会改变。）

2).利用“照相机”功能

A.依次点击“工具”-“自定义”，打开“自定义”对话框，点击“命令”，选择“工具”，在右边选项中选择“照相机”，将其拖拽至“工具栏”中停靠，然后关闭对话框。

B.选取需要打印的区域，单击“照相机”，鼠标变成加号（+），在工作表空白区域鼠标左键单击完成粘贴。将用“照相机”粘贴得到的各区域首尾相接。

C.设置图片区域边框线条颜色为“无线条颜色”，然后打印图片区域。（该方法原始内容包括数据及格式编辑修改后图片区域都会随时保持一致。）6.设置页眉页脚

在菜单栏上依次点击“文件”-“页面设置”，弹出“页面设置”对话框，选择“页眉/页脚”选项，在选项中，我们可以插入Excel内置固定代码的页眉页脚，也可以设置自定义的页眉页脚。第三篇使用公式和函数1.公式的概念和结构

公式是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式，其目的就是为了计算结果。

构成公式的元素通常包括等号、常量、引用和运算符等，其中等号是不可缺的。在实际应用中，公式还可以使用数组、Excel函数或名称来进行运算。2.公式中的运算符

运算符是构成公式的基本元素之一，每个运算符分别代表一种运算。Excel包含四种类型的运算符：

1).算术运算符，主要包含加减乘除及乘幂等常规的算术运算。

2).比较运算符，用于比较大小的运算。

3).文本运算符，用于将文本字符或字符串进行连接、合并。

4).引用运算符，是Excel特有的运算符，主要用于单元格引用。一、公式和函数基础公式中的运算符：5.理解Excel函数

1).函数的概念和结构

Excel的工作表函数简称Excel函数，它是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块，其最终返回结果为值。各Excel函数名称唯一，输入不区分大小写。

Excel函数通常由函数名称、左括号、参数、半角逗号和右括号构成，部分函数比较特殊，仅有函数名称和一对括号组成，不需要任何参数，如NOW函数。与公式相比，Excel函数除了能够简化公式书写外，还可以实现许多自编公式无法实现的功能。

2).常用函数的分类

根据来源不同，Excel函数通常分为内置函数、扩展函数和自定义函数三种。

根据应用领域不同，Excel函数一般分为以下十类：◆财务函数◆日期与时间函数◆数学与三角函数◆统计函数◆查找与引用函数◆数据库函数◆文本函数

◆逻辑函数

◆信息函数◆工程函数6.函数的输入和编辑

1).利用自动求和按钮输入函数鼠标点击菜单栏上的自动求和按钮，Excel会自动添加求和函数。单击按钮右侧的倒三角时，还会展开下拉菜单，其中包括“求和、平均值、计数、最大值、最小值和其他函数”6个备选项。

2).使用插入函数向导鼠标点击编辑栏左侧的按钮，可以打开”插入函数“对话框，我们可以在对话框里选择或搜索需要的函数。

3).直接输入函数7.使用公式的常见问题

1).错误值在使用公式计算时，有时计算结果为一个错误值。常见的错误值含义各不相同，下表为常见的错误值及含义：

当公式结果返回错误值时，我们需要及时查找错误原因，并修改公式解决问题。Excel提供的错误检查工具可以进行辅助检查。选定包含错误的单元格，单击左侧的快捷按钮，我们可以根据弹出的选项选择下一步操作。

3).输入公式的注意事项

有时我们已经正确地输入了函数公式，但编辑完成后公式并没有计算出结果，仍然显示公式本身。一般在以下两种情况下会出现这种现象：

A.勾选“选项”对话框中“公式”复选框

B.单元格设置为文本格式8.函数公式的限制

在Excel2003中，单元格里输入公式的字符数限制为1024个字符，函数的参数不能超过30个，函数嵌套层数最多为7层。9.定义名称

1).名称的概念

在Excel中，名称与普通公式类似，是一种由用户自行设计并能进行数据处理的算式它存在于Excel的内存中。

2).使用名称的优点

在编辑公式时，合理地定义名称和使用名称有以下几个好处：

A.增强公式的可读性，便于公式修改。假如将月产量汇总，其公式为=SUM(D4:D120)，现将D4:D120定义为名称“每日产量”，则公式变成=SUM(每日产量)，结果更利于理解。

B.有利于简化公式。假如对员工进行超产补贴，其公式为=IF(SUM(D4:D120)>=12000,SUM(D4:D120)*0.05*1.3,SUM(D4:D120))，将其中嵌套公式SUM(D4:D120)定义为“月产量”，则公式变为=IF(月产量>=12000,月产量*0.05*1.3,月产量*0.05)。

C.代替辅助工作表区域。假如在工作表1中设置数据有效性来输入姓名，序列数据源无法直接引用工作表2的姓名区域，把工作表2的姓名区域复制到工作表1里又影响视觉效果。这时我们可以把工作表2的姓名区域定义为“姓名”，则数据有效性序列的数据源就可以编辑为=姓名。

D.突破函数嵌套的限制。由于函数嵌套层数最多为7层，假如将其中的部分嵌套函数定义名称，这个限制自然就没有了。

3).定义名称的方法

A.使用“定义名称”对话框。点击菜单栏“插入”-“名称”-“定义”，打开“定义名称”对话框进行操作。

B.使用名称框。选择指定的单元格区域，鼠标单击“名称框”，输入名称，按<Enter>键完成。

C.批量创建名称。选取数据区域，点击菜单栏“插入”-“名称”-“指定”，打开“指定名称”对话框进行操作。第四篇常用函数的运用1.SUM函数

SUM函数用于计算所有单元格区域中所有数值的和。其参数最多为30个，超过30个我们可以将其中的参数定义名称或将所有参数（或部分参数）用括号括起来。使用SUM函数还可以进行简单的条件求和及条件统计。2.SUMIF函数

SUMIF函数用于按给定条件将指定单元格求和。其参数有3个：Range为用于条件判断的单元格区域；Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；Sum_range

是需要求和的实际单元格。一、数学计算3.SUMPRODUCT函数

SUMPRODUCT函数用于计算相对应的数组部分的乘积和，其参数最多为30个。数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE。对于非数值型的数组元素，SUMPRODUCT将作为0处理。

SUMPRODUCT函数还广泛运用于条件求和。4.SUBTOTAL函数

SUBTOTAL函数用于计算列表或数据库中的分类汇总

。通常，使用“数据”菜单中的“分类汇总”命令可以容易地创建带有分类汇总的列表。一旦创建了分类汇总，就可以通过编辑SUBTOTAL函数对该列表进行修改。其参数有2个：Function_num

为1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算；Ref为要进行分类汇总计算的区域或引用，其区域或引用最多29个。利用SUBTOTAL函数，可以在筛选模式下进行数据汇总及自动编号。在“筛选”工作表中，在A2单元格输入公式=SUBTOTAL(3,$B$2:B2)，向下填充公式至A101单元格，在F102单元格输入公式=SUBTOTAL(9,F2:F101)，然后对B:F列进行筛选，A列序号自动编号，合计栏只对显示行求和。5.数值取舍相关函数

在对数值的处理中，经常需要进位或舍去处理，即改变数据精度。在“单元格格式”对话框中“小数位”的设置，只是在显示“视觉”上改变，其单元格的值并未发生变化，除非在“选项”对话框中设置“以显示精度为准”，否则回造成计算误差。6.RAND和RANDBETWEEN函数

RAND函数用于产生0~1之间的随机数，其结果大于0小于1，产生的随机小数几乎不会重复。该函数不需要参数。

RANDBETWEEN函数也是一个随机函数，其参数有两个：Bottom为函数将返回的最小整数，Top为函数将返回的最大整数。RANDBETWEEN函数返回的结果为大于等于Bottom小于等于Top的随机整数。如果该函数不可用，并返回错误值#NAME？则需要先加载“分析工具库”才能正常使用。加载“分析工具库”的方法：依次点击菜单栏“工具”-“加载宏”，弹出“加载宏”对话框，勾选“分析工具库”，点击“确定”后按提示结束。

RAND和RANDBETWEEN函数都是“易失性函数”，当编辑任意单元格或重新打开工作簿时，它们都将重新计算更新。1.NOW和TODAY函数

顾名思义，NOW就是现在、当前的意思，TODAY表示今天的意思。NOW函数返回系统当前日期和时间所对应的序列号；TODAY函数返回系统当前日期所对应的序列号。这两个函数都不需要参数，都是“易失性函数”，当编辑任意单元格或重新打开工作簿时，它们都将重新计算随系统日期和时间变化而实时更新。2.WEEKDAY函数

WEEKDAY函数返回日期所对应的星期几。该函数有两个参数：Serial_number表示一个顺序的序列号，代表要查找的那一天的日期；Return_type为确定返回值类型的数字（1或省略：数字1(星期日)到数字7(星期六)；2：数字1(星期一)到数字7(星期日)；3：数字0(星期一)到数字6(星期日)。）3.YEAR、MONTH和DAY函数

YEAR、MONTH和DAY函数用于提取日期数据中的年份、月份和日期数。YEAR、MONTH和DAY函数的参数都只有1个，即需要提取的日期数据。二、日期与时间4.DATE函数

DATE函数返回代表特定日期的序列号，该函数有三个参数：Year、Month和Day。参数year可以为一到四位数字，如果year位于0（零）到1899（包含）之间，则Excel会将该值加上1900，再计算年份。如果year位于1900到9999（包含）之间，则Excel将使用该数值作为年份。如果year小于0或大于等于10000，则Excel将返回错误值#NUM!。

Month代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份开始往上加算。Day代表在该月份中第几天的数字。如果day大于该月份的最大天数，则将从指定月份的第一天开始往上累加。5.DATEDIF函数

DATEDIF函数是Excel中的一个隐藏函数，虽然其他日期函数也能实现相关计算处理，但在某些方面，DATEDIF相对简单灵活，更加方便。

DATEDIF函数用于计算两个日期之间相差的年数、月数或天数。其参数有3个：参数start_date为启始日期；参数end_date为结束日期，参数unit为所需信息的单位代码，”y”为时间段的整年数，”m”为时间段的整月数，”d”为时间段的天数，”my”为开始日期与结束日期相差的年数，忽略其中的月和日，”ym”为开始日期与结束日期相差的月数，忽略其中的年和日，”md”为开始日期与结束日期相差的天数，忽略其中的年和月，”yd”为开始日期与结束日期相差的天数，忽略其中的年。1.AVEDEV、AVERAGE和AVERAGEA函数

AVEDEV函数返回一组数据与其均值的绝对偏差的平均值，ADEDEV用于评测这组数据的离散度。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

AVERAGE函数返回参数的平均值（算术平均值）。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。

AVERAGEA函数计算参数列表中数值的平均值（算数平均值）。不仅数字，而且文本和逻辑值（如TRUE和FALSE）也将计算在内。包含文本的数组或引用参数将作为0计算。空文本(“”)也作为0计算，包含TRUE的参数作为1计算；包含FALSE的参数作为0计算。

当对单元格中的数值求平均值时，应牢记空白单元格与含零值单元格的区别，尤其在“选项”对话框中的“视图”选项卡上已经清除了“零值”复选框的条件下，空白单元格不计算在内，但计算零值。三、统计2.COUNT和COUNTA函数

COUNT函数返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数COUNT可以计算单元格区域或数字数组中数字字段的输入项个数。COUNT函数在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。

COUNTA函数返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。参数值可以是任何类型，它们可以包