Excel基础到公式运用个人整理.docx

Excel基础到公式运用个人整理.docx

《Excel基础到公式运用个人整理.docx》由会员分享，可在线阅读，更多相关《Excel基础到公式运用个人整理.docx（18页珍藏版）》请在冰豆网上搜索。

Excel

--基本--

加快启动速度：

在桌面Excel图标上点右键属性，在目标路径后加一个空格再加上/e

加载项：

文件——选项——加载项——管理——转到

切换工作表：

Crtl+PageUP/PageDown

选中连续的工作表：

单击第一个工作表，按住Shift键再按最后一张工作表

界面中列标显示为数字而不是英文则是使用了R1C1引用样式，要恢复为英文状态可点击：

文件——选项——公式，取消勾选R1C1引用样式

修改网格线颜色：

文件——选项——高级——网格线颜色

最大行1048576，最大列16384，最大单元格数17179869184个。

Ctrl+右方向键可定位到单元格所在行向右连续非空值的最后一行Ctrl+下方向键可定位到单元格所在列向下连续非空值的最后一行

Ctrl+Home键可到达表格定义的左上角的单元格，Ctrl+End键可以到达表格定义的右下角的单元格。

在名称框中输入字母+数字，可快速定位到此单元格

Shift+空格键：

快速选定单元格所在的行

Ctrl+空格键：

快速选定单元格所在的列（避免与切换中文输入法快捷键冲突）

行高单位是磅，1磅约为0.35278mm，最大限制为409磅，即144.286mm。

列宽的单位是字符，列宽设置数字范围为0~255，最小列宽为0（等于隐藏）。

列宽的宽度约等于该列单元格中可以显示容纳数字字符的个数，标准列宽为8.38。

Ctrl+Backspace：

快速定位活动单元格所在的位置并显示在当前窗口中。

（可用Ctrl+G/F5定位）

Excel中可以表示和存储的数字最大精确到15位有效数字，对于超出的会自动将15位以后的数字变为0，需输入超过15位数字或需显示以0开头的值可在前面加单引号“’”，或者将单元格设成文本格式。

（Ctrl+1键盘左侧数字1）

对于一些很大或很小的数值，Excel会自动以科学记数法来表示，例1.2345E+14表示1.2345×1014。

Excel中日期系统默认为1900年日期系统，即以1900年1月1日作为序列值的基准日，运算时按360天一年计算。

日期系统的序列值是一个整数数值，一天的数值单位就是1，1小时表示为1/24天，1分钟表示为1/（24×60）天。

逻辑值是比较特殊的一类参数，它只有TRUE（真）和FALSE（假）两种类型，Excel中规定：

数字<字母<逻辑值（比较大小）

错误值：

1、#####出现原因：

单元格所含的数字、日期或时间比单元格宽，或日期时间公式产生了负值。

2、#VALUE！

出现原因：

1）在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。

2）将单元格引用、公式或函数作为数组常量输入。

3）赋予需要单一数值的运算符或函数一个数值区域。

3、#DIV/0！

出现原因：

公式被零除时或公式中包含的除数零。

4、#NAME？

出现原因：

1）删除了公式中使用的名称，或者使用了不存在的名称2）名称拼写错误

3）在公式中输入文本时没有使用双引号4）在区域的引用中缺少冒号

5、#N/A出现原因：

函数或公式中没有可用数值

6、#REF！

出现原因：

单元格引用无效

7、#NUM！

出现原因：

公式或函数中某个数字有问题（由公式产生的数字太大或太小，Excel不能表示）

8、#NULL！

出现原因：

为两个并不相交的区域指定交叉点

公式：

通常以“=”开头“+”、“−”号开关也可使用Excel识别其内容为公式Ctrl+~可以在公式与值的显示方式间切换

输入数据：

当用户输入数据时在工作窗口底部状态栏左侧会显示“输入”字样，原有编辑栏左边会出现“×””√”按钮，单击“×”表示

取消输入，单击“√”表示确定输入，单击“√”时和按Enter有相同的效果，但按Enter键后会自动跳到下一单元格，按√

则不会。

Excel中默认按Enter键是向下激活单元格，也可设置成其它，方法：

文件——选项——高级——编辑选项——方向

编辑单元格内容：

双击或者按F2，也可激活目标单元格后在工作窗口编辑栏编辑可以在键盘上按Insert键切换“插入”或“改写”模式

日期输入：

系统默认将0~29之间的数字识别为2000年~2029年，将30~99之间的数字识别为1930年~1999年。

用分隔符“.”来输入日期不能进行运算。

数据输入技巧：

强制换行Alt+Enter

在多个单元格输入相同的数据：

同时选中要输入的单元格，输入数据，在输入结束时按Ctrl+Enter

分数输入：

整数+空格+分数可在编辑栏查看分数值

上下标：

选中要设置的上下标值，Ctrl+1设置单元格格式/字体

在列表中选择：

当要在目标单元格中输入此单元格上方的某一单元格内的内容时可按Alt+下方向键或点鼠标右键选从下拉列

表中选择

自动填充：

输入数据用填充柄下拉（顺序填充，可在填充完后点右下角的填充菜单进行类型选择）双击填充柄

按住Ctrl键下拉为复制填充Ctrl+D向下填充Ctrl+R向右填充（需框选出填充区域后使用）

使用填充菜单：

开始——编辑——填充——序列

粘贴：

粘贴选项，复制、粘贴后在右下角出现的粘贴选项选择性粘贴，复制后在粘贴前点击鼠标右键选择的选择性粘贴选项

查找/替换：

Ctrl+F/Ctrl+H（可设置格式）

查找通配符应用：

Excel中支持两个通配符*和？

例：

要查找以E开头以L结束可用E*L以EX开头L结束的五个字母单词可用EX?

?

L

隐藏单元格数据：

选中要隐藏的单元格——点击右键——设置单元格格式——数字——自定义——在类型处打上三个半角分号（到这一步虽隐藏了单元格表面显示的内容但在编辑栏还是可以看到它本身的内容）——再点右键——设置单元格格式——保护——隐藏——审阅——保护工作表——加密（即可完全隐藏单元格的内容）

禁止编辑表格中部分区域：

单击行号和列标交叉处的按钮（全选整张工作表Ctrl+A）——Ctrl+1——保护——取消勾选锁定OK——选中要禁止编辑的区域——Ctrl+1——保护——先勾选隐藏再勾选锁定——审阅——保护工作表——加密OK

批注中插入图片：

审阅——新建批注——选中批注外边框——点击右键设置批注格式——颜色与线条——填充——填充效果——图片

打印批注：

页面布局——页面设置（点右下角小箭头）——工作表——打印——批注

--公式、函数--

公式以”=“号以引号，必须且只能返回值。

函数按特定算法计算的产生一个或一个组结果的预定义的特殊公式。

公式组成

序号

公式

说明

1

=15*3+20*2

包含常量运算的公式

2

=A1*3+A2*2

包含单元格引用的公式

3

=单价*数量

包含名称的公式

4

=SUM（A1*3,A2*2）

包含函数的公式

在输入公式的状态下，鼠标选中其他单元格区域时，被选区域将作为引用自动输入到公式里。

运算符

符号

说明

例

-

算术运算符：

负号

=8*-5=-40

%

算术运算符：

百分号

=60*5%=3

^

算术运算符：

乘幂

=3^2=9=16^（1/2）=4

*和/

算术运算符：

乘和除

=3*2/4=1.5

+和-

算术运算符：

加和减

=3+2+5=0

=、<>、>、<、>=、<=

比较运算符（比较大小）：

等于、不等于、大于、小于、大于等于、

小于等于

=（A1=A2）判断A1与A2相等

=（B1<>”B2”）判断B1不等于B2

=（C1>=5）判断C1大于等于5

&

文本运算符（将文本字符或字符串进行连接合并）：

连接文本

=”爱”&”点点”返回结果”爱点点“

:

区域运算符：

冒号

=SUM（A1：

C10）引用冒号两边所引用的单元格左上角和右下角之间的所有单元格组成的矩形区域。

空格

交叉运算符：

单个空格

=SUM（A1:

B5A4:

D9）引用A1:

B5与A4:

D9的交叉区

域，公式相当于=SUM（A4:

B5）

，

联合运算符：

逗号

=SUM（A1,（A1:

A10,C1:

C10））第2参数引用A1:

A10

和C1:

C10两个不连续的单元格区域。

运算符优先顺序

序号

公式

说明

0

（）

小括号（优先级高于其他运算符，如果公式内有多组括号进行嵌套，计算顺序则由最内层括号逐

级向外进行运算）

1

：

空格，

冒号、单个空格、逗号

2

-

负号

3

%

百分比

4

^

乘幂

5

*和/

乘和除（注意区别数学中的×和÷）

6

+和-

加和减

7

&

连接文本

8

=、<、>、<=、>=、<>

比较两个值（注意区别数学中的≤、≥、≠）

相对引用：

当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变。

例=A1向下复制为=A2、=A3、=A4-----

绝对引用：

当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变。

例=$A$1向下复制始终=$A$1，不随公式复制改变

混合引用：

当复制公式到其他单元格时，Excel仅保持所引用单元格的行或行方向之一的绝对位置不变，而另一方向位置发生变化。

例：

=$A5向下复制为=$A6、=$A7、=$A8……向右复制始终=$A5

=A$5向右复制为=B$5、=C$5、=D$5……向下复制始终=A$5

混合引用实例：

跨表引用：

在公式中引用其他工作表的单元格区域。

表示方式：

工作表名+半角感叹号+引用区域

方法：

在公式编辑状态下，通过鼠标单击相应的工作表标签，然后选取相应的单元格区域。

例：

要在工作表Sheet1中的A1单元格中引用工作表Sheet2中的A1:

B10区域，则在Sheet1表中A1单元格输入=SUM（后单击

Sheet2工作表标签，然后选取A1:

B10区域，按回车结束。

则公式将显示为=SUM（Sheet2！

A1:

B10）

引用连续多工作表相同区域：

当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时，可以使用三维引用进行计算而无须逐个工作表

对单元格区域进行引用。

它的表示方式为：

按工作表排列顺序，使用冒号将起始工作表和终止工作表名进行连

接，作为跨表引用的工作表表名。

例：

工作薄内有连续工作表1、2、3、4，要在另一汇总表内计算前4张表里的同一区域（假设为B5:

E10）数值，在需执行公式的单元格内

输入=SUM（然后单击1工作表标签，按住Shift键后再单击4工作表标签，然后选取引用区域，按Enter结束输入。

显示公式为=SUM（'1:

4'!

B5:

E10）如果汇总表在表2与表3之间，则公式为=SUM（'1:

2'!

B5:

E10,'3:

4'!

B5:

E10）

套用表格格式：

开始——套用表格格式方法：

选择某一区域——开始——套用表格格式（勾选表包含标题），可在设计选项下进行其

他操作，例如勾选汇总行，会在表下方自动出现汇总，在下拉列表中有各项汇总项。

--函数--

函数表示：

=号开头、函数名称、左括号、以半角逗号相间隔的参数、右括号。

公式中允许多个函数或计算式，通过运算符连接

函数的参数可