excel中英文版笔记.docx

excel中英文版笔记.docx

《excel中英文版笔记.docx》由会员分享，可在线阅读，更多相关《excel中英文版笔记.docx（15页珍藏版）》请在冰豆网上搜索。

excel中英文版笔记

EXCEL2003中英文笔记

一.Excel基础

●界面介绍

v各工具依次排放：

标题栏，菜单栏，常用工具栏，格式栏，公式栏，表格区域，表标签，绘图栏，状态栏。

v复位工具按钮：

工具栏最右边，工具栏选项下拉列表，添加或删除按钮，格式，重新设置工具栏。

（AddorremovebuttonsFormattingResetToolbar

v工具栏显示：

视图工具栏相应的打勾。

View-->Toolbar

v菜单栏复位：

开始搜索文件和文件夹搜索*.xlb文件，找到后删除，重新打开excel将重建菜单文件,（方法二：

菜单空白处右击自定义重排命令工具栏工作表菜单栏重新设置确定。

）

菜单空白处右击CustomizeCommandsRearrangecommandsToolbarWorksheetmenubar

v始终显示整个菜单：

工具自定义选项始终显示整个菜单。

ToolsCustomizeOptionsAlwaysshowfullmenus

v为文件夹建立快捷方式：

文件打开选择文件夹并不打开工具中下拉列表添加到我的位置左边列下拉列表右击图标上移到合适位置。

（适用于winxp）

FileOpenToolsAddto“myplaces”右击左边图标Moveup

●移动和选择

v移动:

上下左右方向箭,回车键.TAB键,Shift+Tab键,Ctrl+→,Ctrl+↓,跳到有数据的最右边或最下边。

v选择:

单击某单元格选择某个单元格,在名称框输入A1:

D3,选择区域,单击行号将选择整行,单击列标将选择整列,按Shift选择相邻,按Ctrl键选择不相邻。

v特殊选择：

全选Ctrl+A,当前到最右边Ctrl+Shift+→，当前到最下面Ctrl+Shift+↓

v特殊选择：

F5定位条件选择空值或其他。

●创建和编辑工作表

v编辑工作表：

右击表标签，插入，删除，移动复制，重命名，更改标签颜色。

v表标签切换：

CTRL+PAGEUP切换上一张，CTRL+PAGEDOWN切换下一张

v定位：

从标题栏可查看当前所在工作簿，从表标签可查看当前所在工作表，从名称框可查看当前所在单元格。

●使用模板

v创建模板：

设置标题，边框填充字体颜色等，另存为xlb模板文件

v使用模板：

文件新建本机上的模板

●输入数据：

v输入文本：

默认左对齐

v输入数字：

默认右对齐

v输入分数：

01/3

v输入身份证：

’310104198711233422先输入’，或格式单元格文本

v输入日期:

2008-8-8或2008/8/8如日期为2008.8.8格式可将“.”替换成“-”

如日期格式为20080808，需综合应用函数进行更改。

假设日期在A1中，可以用以下公式：

Date（left（a1,4）,mid（a1,5,2）,right（a1,2））

v批量输入相同内容：

单元格输入内容后，右下角光标变为黑色十字向下拖，也可以选择区域，然后在公式栏输入数据，输完后CTRL+ENTER

v批量输入不同内容：

输入数字，右下角光标变为黑色十字按Ctrl向下拖；输入日期，右下角光标变为黑色十字向下拖；设置自定义序列，工具，选项，自定义序列，新序列，新序列中的元素之间用英文逗号隔开。

v限定输入:

有效性，限定用户的输入，减少错误的发生。

二.格式与公式

●格式

vCtrl+1设置格式，百分比，小数点保留，千分隔，货币符号

v边框与底纹：

选择区域格式单元格边框或图案进行设置

v文本对齐方式：

选择单元格或区域格式对齐方向文本控制可让超出的文本自动换行，或是缩小来显示。

v合并与拆分：

选择多个区域格式工具栏合并及居中

v强制换行：

在需要强制换行处，单击光标。

ALT+ENTER

●样式

样式的目的是预先设置样式方便以后套用。

v新建样式：

格式样式样式命名如Title修改设置样式如蓝底白字加粗14号

v套用样式：

选择单元格或区域格式样式选择样式名如上述的Title

●自动套用格式

选择区域格式自动套用格式可自套用EXCEL现存的预置格式

●条件格式：

根据不同的条件设置不同的格式,EXCEL03同时只支持三个不同的条件格式

v单元格数值：

格式条件格式单元格数值小于60格式，设置红底白字

v公式：

格式条件格式公式=A2<=60格式，设置红底白字

v复杂的条件格式:

需在公式中写入的公式函数=mod（row（）,2）=1奇数行设颜色

●公式

v公式写法:

等号开始,值或单元格,运算符,值或单元格,回车结束如=1+1=A2+B2=”张”&”三”

v运算符+-*/^&>=<=<>

●函数:

v公式函数规律

=函数名称（参数1,参数2……）

参数由用户提供,最少为0,最大30

[]号中的参数有时可省略参数中……代表若干参数

所有符号用英文半角

字符用“”参数用,括号成对出现

不区分大小写

任何函数均有返回值

函数具有自动更新功能

v常用统计函数

=sum（0,1,2,3,4）求和返回值10

=average（0,1,2,3,4）求平均值返回值2

=count（0,1,2,3,4）计数返回值5

=max（0,1,2,3,4）最大值返回值4

=min（0,1,2,3,4）最小值返回值0

v相对引用与绝对引用

相对引用：

当把公式复制到其它单元格中时，行或列的引用会改变。

所谓行或列的引用会改变，即指代表行的数字和代表列的字母会根据实际的偏移量相应改变。

A1

绝对引用：

当把公式复制到其它单元格中时，行和列的引用不会改变。

$A$1

混合引用：

行或列中有一个是相对引用，另一个是绝对引用。

$A1或A$1

名称引用：

将区域保存为一个名字，方便反复引用，或是动态范围，以后统计分析的区域有可能变化。

选中区域，在名称框中输入名字，回车，或是选中区域，插入，名称，定义。

也可以以这里进行修改删除。

v常用文本函数

Left（“ABCDE”,2）左边取2字符返回AB

Right（“ABCDE”,2）右边取2字符返回DE

Mid（“ABCDE”,2,3）第2字符后3返回BCD

Len（“ABCDE”）长度返回5

REPT（0,10）用10个0填充0000000000

TRIM（howareyou）删除多余的空格返回howareyou

v常用日期函数

Today（）今天

Now（）现在

YearMonthDay提取年月日

Date（年,月,日）返回日期型

v逻辑判断函数If

=IF（A1>=60,”及格”,”补考”）

=IF（A1>=90,"优",IF（A1>=80,"良",IF（A1>=60,"中","差"）））

=And（条件1,条件2,条件3……条件n）

=Or（express1,express2,……,expressn）

=not（express1,express2,……,expressn）

三.图表

●绘制图表

v选中数据源（要包含表头），插入/图表，按向导完成

v格式化图表：

选中要格式化的元素，右键，设置***格式

v更改图表类型：

图表菜单/图表类型

v修改网格线/数据标志/标题/图例等:

图表菜单/图表选项

v三维图表：

图表/设置三维视图格式

v修改数据源：

图表/源数据

v显示图表工具栏：

视图/工具栏/图表

v

●双轴图表只能针对平面图表类型

v步骤：

选系列（seriel）

打开数据系列格式对话框

坐标轴，次坐标轴

换图表类型

●保存自定义图表类型

v保存：

选中设计好的图表à图表/图表类型à自定义类型选项卡à自定义添加按钮

v使用：

选图表类型时，到这里选即可

●动态图表

具备以下基础：

1.简单图表的制作2.相对引用绝对引用3.控件的使用4.index步骤１：

准备数据源：

表格数据第一列复制，这样做的目的是为后面图表准备数据来源，将标题行列转置，这样做的目的是为控件的组合框提供数据来源。

步骤2：

制作组合框

视图à工具栏à窗体　　　窗体工具栏中拖放组合框在表格中

　　ViewàToolbaràFormscombobox

右击组合框控件à设置控件格式à控制à输入区域选择转置后的欧洲日本美国亚太数据，链接单元格选择一个空白单元格，如A9目的是可以记下选择的数据，如果选择欧洲，则为１，选择了日本，则为2

右击ComboboxàFormatControlàControlàInputrangeàCelllink

步骤3：

使用Index函数

使用Index函数返回B1:

E1区域中的第几个，由Ａ9决定，如Ａ9为１，则返回第一个，即欧洲,如Ａ9为2，则返回日本,其中B1:

E9范围相对引用，因为后面1995年对应的数据为B2:

E2,1996年对应的数据为B3:

E3,但Ａ9必须是绝对引用，后面的数据区域的第几个也由Ａ9决定

步骤４：

制作图表

右击组合框控件，拖放在图表中，选中图表，右击，置于底层（Sendtoback）

右击组合框控件，再单击图表，右击，组合可结合成一组（groupingàgroup）

四.数据分析

●数据导入

1.导入TXT文本文件：

数据à导入外部数据à导入数据à选择文件à选择分隔符号（如中间没有符号隔开，选择固定宽度然后手动切割）à如果有身份证号，需选中该列，文本处理à选择保存文本的位置新建或是当前表中

2.DataàImportexternaldataàimportdataàDelimitedàTabàTextàFinish

●排序

3.选择数据：

全选表格à数据à排序（DataàSort）或选择标题字段，直接点工具栏上的图标

4.默认三个级别排序数据à排序à第一关键字à第二关键字à第三关键字DataàSortàSortbyàThenbyàThenby

5.多关键字排序：

如想先按地区再按年份排序，可先点击年份字段，点击图标

，再点击地区，再点击

，倒过来做。

6.升序Ascending降序Descending,数字大小，字母a-z，汉字可选择笔划或是字母，数据à排序à选项à按字母或笔划DataàSortàOptionsàSyllabaryordering或Strokeordering

7.按字符长度排序，辅助列用len（）求长度，再该辅助列排序

8.按行排序：

第一列不选中，数据à排序à选项à按行DataàSortàOptionsàSortlefttoright再进行排序

9.特定排序：

比如想排成总经理,副总经理，经理，组长，员工，先自定义序列，工具à选项à自定义序列à新序列à输入内容，添加。

（ToolsàOptionsàCustomlistsàNewlistàAdd）再进行排序，数据à排序à选择关键字à选项à自定义序列（DataàSortàSortbyàOptionsàCustomsortorder

●筛选数据à筛选à自动筛选DataàFilteràAutofilter

10.同字段并且关系，如工资>800并且工资<1000基本工资下拉列表选择自定义

11.不同字段并且关系，如男并且年龄<40性别字段下拉列表中选择男，年龄字段下拉列表中选择自定义，设为<40

12.前几名如工资最高的三个基础工资字段下拉列表中选择前十，修改

13.复杂条件可用高级筛选

●高级筛选数据à筛选à高级筛选DataàFilteràAdvancedFilter

14.列表区域，条件区域，结果区域

15.条件写在同行为并且关系，条件写在异行为或者关系

16.选择区域时将必须包含标题

如下图，数据区域即列表区域均为A1:

B8,条件区域如果为D1:

E2,即条件写在同行，为并且关系，同时满足条件aaa并且销量>20结果区域选择单元格D5,如果条件区域为G1：

H3，结果区域为G5，或者关系将产生更多的记录。

17.删除重复项数据区域A1:

A8，条件区域，无，结果区域D1，如果这样做相当于直接复制粘贴，勾选相同的取唯一Uniquerecordsonly

●分组数据量大时可展开折叠显示数据

上图左边先进行排序，在浦东区下方插入一行，A7输入浦东区，用来进行汇总D7选中,ALT+=即快速求和,在浦西区后方A11输入浦西区,D11选中,ALT+=在A12中输入总计,D12选中,ALT+=以上是手动求和

最后,将整个表格数据选中,数据à组及明细à自动级别DataàGroupandOutlineàAutoOutline最终效果如下,点击左边1,2,3可进行展开折叠。

如果D7D11D12不存在汇总数据,则需要手动分组,选中,A2:

A6,数据à组及明细à组合à按行

●分类汇总分门别类汇总,分类汇总前一定要先排序,

18.先排序:

如按地区汇总,则先按地区排序,如按先地区再年份汇总,则先地区再年份排序

19.再进行分类汇总:

数据à分类汇总分类字段选择区域,汇总方式,求和sum,汇总字段,招生人数如果勾选在每组分页,则打印时可将每组分别打印,pagebreakbetweengroups再选数据à分类汇总分类字段这次选择年份,汇总方式,求和sum,汇总字段,招生人数注意不要勾选替换当前分类汇总replacecurrentsubtotals

20.

●数据透视表

21.数据透视表:

改变了数据的组合方式,从不同角度对数据进行分析

22.透视表的布局：

行，列，页，数据

23.显示指定项（强调显示字段中某一个值页）,显示多项或所有项行，列

24.如果行中有多项字段，可将一项放到列中形成交叉表

25.页，行，列中可以为空，但不能放重复的内容.数据字段可以重复的，但不能为空.

26.制作步骤:

选择数据表,插入\数据透视表，制作结构,根据目的确定所需的字段,将字段放在相应的区域中，行，列，页,根据需修改统计方式,格式属性的调整

27.百分比:

右击,字段设置à选项à占总和的百分比（Fieldsettingsàoptionsà%oftotal

28.统计项如计算浦东区与浦西区相差多少?

数据透视表工具栏à公式à计算项

29.统计字段计算工资加资金汇总

数据透视表工具栏à公式à计算字段

30.按年月分组

日期放行字段,小计放数值字段

选中某个日期,右击à组及明细à组合à按年和季度

GroupandshowdetailàGroupàYearsQuarters

31.频率分析

年龄放在行字段,工资放在值字段,年龄右击分组,步长值为10,汇总方式改为平均值.

32.制作图表

数据透视表中,点击

中图表的图标.

右击位置（location）可改变图表位置,图表中右击,图表类型（Charttype）可改变图表类型,数据透视表工具栏,下拉列表中可以控制显示不显示图表中按钮.

33.分页

接上操作,将部门拖放在页字段中,数据透视表工具栏à显示页（Showpages）

五.数据管理

●合并计算多表合并计算,结构相同,可以顺序不同,项目不同,位置不同

34.如想按左列分类统计,请勾选最左列

35.按第一行分类统计,请勾选首行

36.自动更新功能创建指向源数据的链接

37.当源和目标区域在同一张工作表时，是不能够建立链接的

●链接表

原数据复制,选择目标单元格,右击,选择性粘贴,粘贴链接（pastespecialàpastelink）

●宏简单的重复的事情录制,方便以后一次性执行

38.正常操作之前按下录制,正常操作之后按下停止,一般不将选择单元格录制到宏里,否则将执行宏永远针对该单元格的操作.

v录制一个清除格式的宏:

选择某个单元格à工具à宏à录制新宏à命名à设快捷键Ctrl+Jà编辑à清除à格式à停止录制

v执行宏,选择某个单元格,Ctrl+J即执行上述操作.

v在工具栏上设置宏按钮工具à自定义à命令à宏à将右边图标拖放在工具栏点击图标可指定宏.

v在表格中设置宏按钮,视图à工具栏à窗体à按钮拖放在表格中,右击按钮,可指定宏.ViewàToolbaràForms

v宏的保存位置:

录制宏时可选择个人宏工作簿Personal（隐藏工作簿,随EXCEL打开自动打开,适用于本地电脑,其他文件也方便使用）,当前工作簿Thisworkbook（适合随文件保存）

v启用宏:

工具à宏à安全性à低ToolsàMacroàSecurityàLow

●保护:

v指定密码（toolsàoptionsàsecurity）

v使区域锁定工具à保护à保护工作表

（formatcellsàprotectionàlocked）

（toolsprotectionàprotectsheet）

v各区域不同密码工具à保护à允许用户编辑区域

（toolsàprotectionàallowuserstoeditranges）

v窗口和结构锁定工具à保护à保护工作簿

（toolsàrotectionàprotectworkbook）