EXCEL教程分析.docx

EXCEL教程分析.docx

《EXCEL教程分析.docx》由会员分享，可在线阅读，更多相关《EXCEL教程分析.docx（20页珍藏版）》请在冰点文库上搜索。

EXCEL教程分析

EXCEL2007应用

Excel2007中的新特征

（1）功能区（Ribbon）/选项卡用户界面

（2）新的XML文件格式

（3）表

（4）巨大的工作表（1048576行16384列）

（5）使用更多内存的能力

（6）每个单元格没有限制条件格式

（7）可以撤销100次

（8）公式长度增加到了8000个字符

（9）公式嵌套多达64层

（10）公式自动完成

（11）外观精美的图表

（12）工作簿主题

（13）工作簿皮肤

（14）页面布局视图

（15）新的条件格式选项

（16）分类清晰的Excel选项对话框

（17）新的协作功能（需要SharePoint）

（18）SmartArt和改进的艺术字

（19）兼容性检查器

（20）更容易的数据透视表

（21）12个新的工作表函数，整合了分析工具库函数

（22）PDF输出（通过加载项）

（23）可调尺寸的公式栏

（24）一些新的模板

（25）状态栏中更多的控制

0、使用excel做表格的一些建议：

0.1同一对象请使用同一名称，而不要使用简写、别称，以便使用函数查找，引用。

如果你使用过vlookup等函数你就可体会到这个习惯的必要性。

同理，不同部门间对同一对象亦应统一名称或编码，以利数据的传递、引用。

0.2排版时不要在字符串内、前后插入空格，尽量使用对齐功能来排版，以便使用函数查找、引用。

0.3在要使用公式的单元格，尽量不使用合并单元格功能（可使用跨列居中来对齐），以便填充公式、排序、筛选。

跨列居中的办法：

选中要居中的范围→右键→〖设置单元格格式〗→〖对齐〗→〖水平对齐〗→〖跨列居中〗

0.4排版时使用顶端标题行、标题列功能，而不要笨拙、野蛮地强制性插入标题行、标题列。

操作：

〖页面布局〗→“打印标题”选项卡

☆选中“行号列标”复选框，打印行号列标；

☆若要在每一页上打印列标题，请在“打印标题”下的“顶端标题行”框中，选取列标志所在行，然后单击“打印”。

☆若要在每一页上打印行标志，可在“打印标题”下的“左端标题列”框中，输入行标志所在列的列标，然后单击“打印”。

0.6学会使用拆分窗口功能，免除为看到标题行而大量滚屏，以提高工作效率。

0.7工作簿内工作表较多时，使用工作表标签的快捷菜单，快速选取工作表。

0.8经常使用快捷键，提高工作效率。

0.9在同一工作簿的不同工作表的相同单元格输入相同的数据或设置相同的格式，请使用组合工作表功能，以提高效率。

0.10要对工作表进行临时性排序时，养成添加序号列的习惯，以便恢复原来的顺序。

0.11当工作表要提交他人（多人）填写时，养成保护工作表、锁定单元格以及对特定单元格设定数据有效性的习惯，以避免他人的错误操作影响报表的汇总。

0.12养成使用状态栏进行临时性的求和、计数、求平均值的习惯。

1、表与单元格操作

1.1表的新建、选择、表名更改、颜色更改、移动、复制。

☆插入新工作表的快捷键是Alt+Shift+F1，如果你需插入多个工作表，请使用此快捷键。

☆右击某个工作表标签,点"插入",选择"工作表",点"确定",然后按住Alt+Enter键不放,你要多少个你就按住多久不放,你会看到工作表数量在不断增加,几千个都没有问题。

☆点击工作表标签，直接拖动工作表标签可移动工作表的顺序；按住Ctrl拖动鼠标可复制工作表到本工作簿。

☆如果工作簿里工作表较多，可以在工作表标签点鼠标右键，点“移动或复制工作表”，使用此方法在较多工作表时移动很方便直观。

如果点“移动或复制工作表”并将“建立副本”的选项勾上，选择要移动至工作簿，即可将工作表复制到其它工作簿。

（见具体的操作）。

☆同时在多张工作表的同一单元格区域中输入或编辑相同的数据：

按住Ctrl选定需要输入数据的工作表（可多张）,再选定需要输入数据的单元格或单元格区域，键入或编辑相应的数据，回车。

☆在其他工作表中输入相同的已有数据：

选中含有输入数据的源工作表，以及复制数据的目标工作表，选定包含需要复制数据的单元格，在【开始】标签页上，指向〖填充〗，再单击“成组工作表”。

☆隐藏已有的工作表：

操作：

【视图】标签页→〖窗口〗→“隐藏”

隐藏工作表分两个层次：

普通隐藏、深层隐藏。

普通隐藏：

在【视图】标签页→〖窗口〗子菜单将选定的工作表隐藏起来。

此类隐藏可用同样的方法将隐藏的工作表取消。

或者在工作表上右键，选择〖隐藏〗。

深层隐藏：

要用VBA，将工作表的Visible属性设置为xlSheetVeryHidden即可。

用此方法所隐藏的表在【视图】→〖窗口〗→“取消隐藏”是看不见的，自然就无法取消对它的隐藏了。

取消隐藏：

【窗口】→〖取消隐藏〗。

如果〖取消隐藏〗命令无效，则说明工作簿中没有隐藏的工作表。

如果〖重命名〗和〖隐藏〗命令均无效，则说明当前工作簿正处于防止更改结构的保护状态。

需要撤消保护工作簿之后，才能确定是否有工作表被隐藏。

☆设置工作表标签颜色：

在工作表标签上单击右键，再单击“工作表标签颜色”。

选择所需颜色，“确定”退出。

如果工作表标签用颜色做了标记，则当选中该工作表标签时，将按用户指定的颜色为其名称添加下划线。

如果工作表标签显示有背景色，则该工作表处于未选中状态。

☆设置工作表背景：

如果你看腻了白色的工作表背景，不妨添加一张自己喜欢的图片作为背景吧。

在【页面布局】标签页→〖页面设置〗子菜单→〖背景〗

背景图案不能打印，并且不会保留在保存为网页的单个工作表或项目中。

然而，如果将整个工作簿发布为网页，则背景将保留。

友情提醒：

增加背景图片将增大工作簿的大小。

1.2查找替换

操作：

【开始】→〖查找和选择〗

可查找（替换）某特定格式或某文本；

可在整个工作簿内查找（默认为仅在工作表内查找）；可设定是否区分大小分、全角半角等。

查找内容中可使用“？

”、“*”等通配符，用法见下表。

☆通配符类型用法

?

（问号）——任何单个字符，例如，sm?

th查找“smith”和“smyth”

*（星号）——任何字符数，例如，*east查找“Northeast”和“Southeast”

☆在*和?

前加~即可使它们失去通配符的作用，如：

要查找“*”那么在查找内容里输入“~*”即可；要查找“lhm?

88”请输入“lhm~?

88”。

☆将工作表中包含某字符串的多处单元格一次性设置为某格式

方法1:

【开始】→〖查找和选择〗，在替换对话框点击“选项”，查找内容和替换内容均输入“”，在“替换为”那一行的“格式”中设置你需要的格式，再点“全部替换”。

方法2：

【开始】→〖查找和选择〗→在查找栏输入“”→点“查找全部”→按Ctrl+A，再设置需要的格式退出。

方法3：

【开始】→〖查找和选择〗→在查找栏输入“”→点“查找下一个”→设置格式（不要退出查找对话框）→双击格式刷→点“查找全部”→按Ctrl+A→关闭退出。

这里介绍替换公式的内容

1.3筛选（高级筛选）

【开始】→〖排序和筛选〗→〖筛选〗

☆对最小或最大的前N个数进行筛选。

☆对包含或不包含特定文本的行进行筛选，注：

最多可按两个条件进行自动筛选、可使用通配符。

☆对空白或非空白单元格进行筛选。

☆对大于或小于另一个数字的数字进行筛选。

☆对等于或不等于另一个数字的数字进行筛选。

☆对文本字符串的开始或结尾进行筛选，如果你要筛选出以或不以某字符串开始或结尾的行请使用此功能。

☆对顶部或底部数字按百分比进行筛选。

☆按照单元格的颜色进行筛选。

1.4排序（多条件排序、行排序）

【开始】→〖排序和筛选〗→〖升序〗、〖降序〗、〖自定义排序〗

☆可按多个关键字进行排序，还可选择排序的方向（按行或列）、排序的方法（按字母排序按笔划排序）。

排序的功能比较简单，主要了解一下排序的顺序。

☆在按升序排序时，Excel使用如下次序（在按降序排序时，除了空白单元格总是在最后外，其他的排序次序反转）

☆在按字母先后顺序对文本项进行排序时，Excel从左到右一个字符一个字符地进行排序。

☆文本以及包含数字的文本，按下列次序排序:

0123456789（空格）!

"#$%&（）*,./:

;?

@[\]^_`{|}~+<=>ABCDEFGHIJKLMNOPQRSTUVWXYZ

☆在逻辑值中，FALSE排在TRUE之前；所有错误值的优先级相同；空格始终排在最后。

1.5选择性粘贴（转置、粘贴运算）

〖复制〗→点右键→〖选择性粘贴〗

复制就不多说了，快捷键为Ctrl+C。

注意：

不能对多重数据区域使用复制剪切命令。

选择性粘贴是一个非常实用的功能，下面介绍常用的几个选项：

☆粘贴公式——当复制公式时，单元格引用将根据所用引用类型而变化。

如要使单元格引用保证不变，请使用绝对引用。

☆粘贴数值——将单元格中的公式转换成计算后的结果，并不覆盖原有的格式；仅粘贴来源数据的数值，不粘贴来源数据的格式。

☆边框除外——有时，在粘贴数据时不需要原来的边框，则可以在粘贴时调出“选择性粘贴”对话框，选中“边框除外”前的单选按钮。

☆粘贴格式——复制格式到目标单元格。

但不能粘贴单元格的有效性。

此外，选择含有要复制格式的单元格或单元格区域，单击“常用”工具栏上的“格式刷”，也可复制单元格或区域的格式。

☆若要将选定单元格或区域中的格式复制到多个位置，请双击“格式刷”按钮。

当完成复制格式时，请再次单击该按钮或按Esc以关闭格式刷。

☆若要复制列宽，请选定要复制其列宽的列标，再单击“格式刷”按钮，然后单击要将列宽复制到的目标列标。

☆粘贴时运算

可对粘贴对象进行加减乘除运算，如：

A1单元格数值为2，复制A1单元格，选定B5：

E7区域，进行下列操作：

选择性粘贴--运算--乘，则B5：

E7单元格的数据变成原来的2倍。

其余运算类推。

☆如果某单元格区域的数字已设成文本格式，无法对其进行加减，可在某空白单元格输入数值1，复制数值为1的单元格，再选定目标区域，选择性粘贴→乘，即可将文本格式的数字转换为数字格式。

另：

用除1或加减零都可转换。

同理，使用“选择性粘贴→乘”的功能将单元格区域的数字转换正负号、将以元为单位的报表转换为以千元或万元为单位是非常方便的！

☆跳过空单元

当复制的源数据区域中有空单元格时，粘贴时空单元格不会替换粘贴区域对应单元格中的值。

防止用复制的空白单元格替换数据单元格。

☆转置

复制区域的顶行数据将显示于粘贴区域的最左列，而复制区域的最左列将显示于粘贴区域的顶行，很方便哦。

☆有效性

粘贴单元格的有效性条件。

技巧：

“全部”和“边框除外”选项也可复制数据有效性设置。

1.6定位

操作：

【开始】标签页→〖查找与选择〗→〖定位条件〗；快捷捷为Ctrl+G

什么是定位？

定位就是Excel替你选定符合你所设定位条件的单元格。

打开定位对话框后单击“定位条件”按钮可选择包含特殊字符或条目的对象、批注或单元格。

这是一个很实用的功能。

下面介绍一下它的一些常用选项：

☆若要选择包含批注的单元格，请单击“批注”。

☆若要选择包含常量的单元格，请单击“常量”。

☆若要选择包含公式的单元格，请单击“公式”

注：

常量和公式选项又有数字、文本、逻辑值、错误等子选项可选。

☆若要选择空白单元格，请单击“空值”。

☆若要仅选择区域中可见的单元格，虽然该区域也跨越隐藏的行和列，请单击“可见单元格”。

☆若要选择当前区域，如整个列表，请单击“当前区域”。

注释：

当前区域指填写了数据的区域，该区域包括当前选定的单元格或单元格区域。

该区域向四周扩展到第一个空行或空列。

技巧：

如果要选定一个很大的单元格区域，建议使用定位功能。

如要定选A1：

D20000区域，为避免大量滚屏，可以直接在定位对话框的“引用位置”直接输入A1：

D20000即可。

当然也可先选定A1单元格，然后拖动滚动条到20000行，然后按住Shift选定D20000单元格即可。

☆若要选择两行内容中有差异的部分，请单击“行内容差异单元格”。

1.7保护工作表

操作：

【审阅】标签页→〖保护工作表〗

保护工作表及锁定单元格中的内容：

可防止未解除锁定的单元格、查看隐藏的行或列、查看隐藏的单元格中的公式。

☆选择锁定单元格：

将此选项去掉可阻止用户将鼠标指向您在“单元格格式”对话框中“保护”选项卡上的“锁定”复选框中已选中的单元格。

☆选择解除锁定的单元格：

同上，不赘述。

☆单元格格式：

清除此项时，可以防止用户更改“单元格格式”或“条件格式”对话框中的任何选项。

其他可禁止用户使用插入/删除行/列、排序、使用自动筛选、使用数据透视表等功能。

☆允许用户编辑区域

☆保护工作簿

结构：

防止用户进行如下操作：

查看隐藏的工作表、移动删除隐藏或更改工作表名称、插入新工作表或图表工作表、将工作表移动或复制到其他工作簿中；

窗口：

防止用户进行如下操作：

在工作簿打开时，更改工作簿窗口的大小和位置、移动窗口、调整窗口大小或关闭窗口。

但是，用户可以隐藏或取消隐藏窗口；

☆保护并共享工作簿

如要设定工作簿的打开或修改密码,点击【文件】菜单→〖另存为〗子菜单，打开“另存为”对话框，在此对话框的“工具”菜单下的“常规选项”里设置。

1.8在EXCEL中如何把B列与A列不同之处标识出来

☆如果是要求A、B两列的同一行数据相比较：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为:

“单元格数值”“不等于”=B2

点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。

用格式刷将A2单元格的条件格式向下复制。

B列可参照此方法设置。

☆如果是A列与B列整体比较（即相同数据不在同一行）：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为:

“公式”=COUNTIF（$B:

$B,$A2）=0

点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。

用格式刷将A2单元格的条件格式向下复制。

B列可参照此方法设置。

按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。

2、函数

2.1什么是函数

可以直接用来对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。

函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。

如果函数以公式的形式出现，请在函数名称前面键入等号（=）。

在创建包含函数的公式时，公式选项板将提供相关的帮助。

2.2与求和有关的函数应用

SUM函数

☆是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。

这里具体展开讲。

☆SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。

换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。

SUMIF函数

☆可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。

☆要计算广州嘉忠营业部11月份的社保费用，则可以输入公式为：

=SUMIF（$I:

$I,"广州嘉忠营业部",$P:

$P）

其中"$I:

$I"为提供逻辑判断依据的单元格区域，"广州嘉忠营业部"为判断条件,因为是文本性质，所以需要添加双引号，即只统计$I:

$I区域中部门为"广州嘉忠营业部"的单元格，$P:

$P为实际求和的单元格区域。

COUNTIF函数

可以用来计算给定区域内满足特定条件的单元格的数目。

比如在社保报表中计算买在清远的人数有多少个。

语法形式为COUNTIF（范围,计数的条件）。

其中范围为需要计算其中满足条件的单元格数目的单元格区域。

计数的条件确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"、"apples"。

Count函数

用于求单元格个数的统计函数COUNT

语法形式为COUNT（value1,value2,...）

其中Value1,value2,...为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。

函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。

如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。

如果要统计逻辑值、文字或错误值，应当使用函数COUNTA。

举例说明COUNT函数的用途，示例中也列举了带A的函数COUNTA的用途。

仍以上例为例，要计算一共有多少评委参与评分（用函数COUNTA），以及有几个评委给出了有效分数（用函数COUNT）。

Counta函数

返回参数组中非空值的数目。

利用函数COUNTA可以计算数组或单元格区域中数据项的个数。

Countblank函数

COUNTBLANK用于计算指定单元格区域中空白单元格的个数。

其语法形式为COUNTBLANK（范围）其中范围为需要计算其中空白单元格个数的区域。

需要注意的是，即使单元格中含有返回值为空文本（""）的公式，该单元格也会计算在内，但包含零值的单元格不计算在内。

Sumproduct函数

SUMPRODUCT函数可以方便地计算工作表内多列中对应值相乘后的和，其语法为：

SUMPRODUCT（array1,array2,array3,…）

SUM是求和，PRODUCT是乘积,array是条件区域。

其中，Array1,array2,array3,…为2到30个数组，其相应元素需要进行相乘并求和。

数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!

。

该函数将非数值型的数组元素作为0处理。

看一个例子就容易明白SUMPRODUCT的用法：

公式：

=SUMPRODUCT（A1:

B3,C1:

D3）

说明：

两个数组的所有元素对应相乘，然后把乘积相加，即1*10+2*20+3*30+4*40+5*50+6*60（结果为910）

另外的用法：

多条件计数（用*号分隔组合条件）

==SUMPRODUCT（（$E$2:

$E$10="初中"）*（$F$2:

$F$10="男"））

这个公式的意思是统计，$E$2:

$E$10是初中，同时$F$2:

$F$10是男的人数。

来看它的计算过程：

如图E7、E8、E9＝初中，其他都不是，然后除了F2、F3之外，其余都为男。

这时候公式变为

=SUMPRODUCT（（FALSE，FALSE，FALSE，FALSE，FALSE，TRUE，TRUE，TRUE，FALSE）*（FALSE，FALSE，TRUE，TRUE，TRUE，TRUE，TRUE，TRUE，TRUE））

这不知道能理解不，因为E2=高中，所以第一个值为FALSE。

E7是初中，符合条件，所以值就为TRUE。

接下来，TRUE和FALSE分别代表1和0。

所以公式变为：

=SUMPRODUCT（（0，0，0，0，0，1，1，1，0）*（0，0，1，1，1，1，1，1，1））

然后接下来就是SUMPRODUCT的计算过程了

=0*0+0*0+0*1+0*1+0*1+1*1+1*1+1*1+0*1=3

所以最后的结果等于3。

2.4统计函数：

平均数（AVERAGE）、最大数（MAX）、最小数（MIN）、排序（rank）

数值的排位是与数据清单中其他数值的相对大小，当然如果数据清单已经排过序了，则数值的排位就是它当前的位置。

数据清单的排序可以使用Excel提供的排序功能完成。

语法形式为RANK（number,ref,order）其中Number为需要找到排位的数字；Ref为包含一组数字的数组或引用。

Order为一数字用来指明排位的方式。

如果order为0或省略，则Excel将ref当作按降序排列的数据清单进行排位，即最高为第一位。

如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位，即最低为第一位。

需要说明的是，函数RANK对重复数的排位相同。

但重复数的存在将影响后续数值的排位。

嗯，这就好像并列第几的概念啊。

例如，在一列整数里，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。

2.5vlookup函数

VLOOKUP函数可以根据搜索区域内最左列的值，去查找区域内其它列的数据，并返回该列的数据，对于字母来说，搜索时不分大小写。

语法：

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

参数：

Lookup_value为要在数据表第一列中查找的数值，它可以是数值、引用或文字串。

Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。

Col_index_num为table_array中待返回的匹配值的列序号。

Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，如果为FALSE，函数VLOOKUP将返回精确匹配值。

如果找不到，则返回错误值#N/A。

2.6文本函数。

（UPPER、LOWER、PROPER）

（一）大小写转换

LOWER--将一个文字串中的所有大写字母转换为小写字母。

UPPER--将文本转换成大写形式。

PROPER--将文字串的首字母及任何非字母字符之后的首字母转换成大写。

将其余的字母转换成小写。

这三种函数的基本语法形式均为函数名（text）。

2.6.0字符串的连接（&、LEN）

CONCATENATE将若干个文字项合并至一个文字项中。

CONCATENATE（文本1,文本2……）

比如，从字符串“Thisisanapple.”分别取出字符“This”、“apple”、“is”，再把提取出的三段字符重新连接在一起。

具体函数写法为：

LEFT（"Thisisanapple",4）=This

RIGHT（"Thisisanapple",5）=apple

MID（"Thisisanapple",6,2）=is

CONCATENATE（"This","apple","is"）=Thisappleis

或者是="This"&"apple"&"is"

LEN返回文本串中的字符数。

2.6.1取出字符串中的部分字符（LEFT、RIGHT、MID）

可以使用Mid、Left、Right等函数从长字符串内获取一部分字符。

具体语法格式为

LEFT函数：

LEFT（text,num_chars）其中Text是包含要提取字符的文本串。

Num_chars指定要由LEFT所提取的字符数。

MID函数：

MID（text,start_num,num_chars）其中Text是包含要提取字符的文本串。

Start_num是文本中要提取的第一个字符的位置。

RIGHT函数：

RIGHT（text,num_chars）