Excel基础到公式运用(个人整理)

Excel

--基本--

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

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

切换工作表：Crtl+PageUP/PageDown

选中连续的工作表：单击第一个工作表，按住Shift键再按最后一张工作表

界面中列标显示为数字而不是英文则是使用了R1C1引用样式，要恢复为英文状态可点击：文件——选项——公式，取消勾选R1C1引用样式修改网格线颜色：文件——选项——高级——网格线颜色

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

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

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

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

Shift+空格键：快速选定单元格所在的行

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

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

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

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

Ctrl+Backspace：快速定位活动单元格所在的位置并显示在当前窗口中。（可用Ctrl+G/F5定位）

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

对于一些很大或很小的数值，Excel会自动以科学记数法来表示，例+14表示×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+F/Ctrl+H（可设置格式）

查找通配符应用：Excel中支持两个通配符* 和例：要查找以E开头以L结束可用E*L 以EX开头L结束的五个字母单词可用EXL

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

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

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

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

--公式、函数--

公式以”=“号以引号，必须且只能返回值。函数按特定算法计算的产生一个或一个组结果的预定义的特殊公式。

公式组成

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

运算符

运算符优先顺序

混合引用：当复制公式到其他单元格时，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)

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

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

--函数--

函数表示：=号开头、函数名称、左括号、以半角逗号相间隔的参数、右括号。公式中允许多个函数或计算式，通过运算符连接函数的参数可以由数值、日期、文本等元素组成，可以使用常量、数组、单元格引用或其他函数。当使用函数作为另一个函数的参数时，称为嵌套函数。

Alt + = ：插入求和函数Shift+F3：打开插入函数对话框

生成26个大写字母序列：=CHAR(64+COLUMN()) 小写字母序列：=CHAR(96+COLUMN())

合并并换行：先点自动换行按钮，再输入公式=G7&CHAR(10)&H7 10是自动换行符的ANSI编码

分离姓名和数字

LEFT：从一个文本字符串的第一个字符开始返回指定个数的字符

LENB：返回文本中包含的字符数

LEN：返回文本字符串中的字符个数

RIGHT：从一个文本字符串的最后一个字符开始返回指定个数

的字符。

LENB函数按照每个双字节字符（汉字姓名）为2个长度计算，单字节字符按1个长度计算，因此LENB(B4)-LEN(B4)可以求得单元格中双字节字符的个数。2*LEN(B4)-LENB(B4)则可以求得单元格中单字节字符的个数。LEFT=左RIGHT=右

计算职工人数

G列数据中带有“人”字，要在黄色填充部分求人数的和可以用SUBSTITUTE函数将“人”字删除后再求和。

SUBSTITUTE函数获得的结果为文本型数字，所以要使用减负（- -）运算将基转换为数值，以便SUMPRODUCT函数求和。

=IF(MOD(RIGHT(LEFT(B3,17)),2),"男","女")

MOD：取余函数（返回两数相除的余数）

=MOD（34,9）结果为7 余数结果符号与除数一致被除数是除数的整数倍时结果为零

判断奇偶性：=IF（MOD（15，2）>0，”奇数”,”偶数”）解释：如果15除以2的余数大于0，则返回奇数，否则就返回偶数

IF（条件函数）：判断是否满足某个条件，如果满足就返回这个值，如果不满足则返回另一个值。

INT：将数值向下取最接近的整数例：=INT（）结果为15

TRUNC：将数字截为整数或保留指定位数的小数例：=TRUNC（）结果为-8 =TRUNC（）结果为9

ROUNDUP：无条件向上舍入数字例：=ROUNDUP（,2）结果为（和四舍五入类似）

ROUNDOWN：无条件向下舍入数字例：=ROUNDUP（,2）结果为（直接舍去）可用TRUNC函数代替使用。CEILING：向上舍入例：=CEILING, 结果为

FLOOR：向下舍去例：=FLOOR, 结果为

利用取舍函数控制单元格录入数值：数据有效性—在公式处输入=C3=FLOOR(C3, C3为可变更单元格。

ROUND：按指定的位数对数值进行四舍五入例：=ROUND,2) 结果为：

日期和时间函数：允许输入日期区间1900-1-1至9999-12-31

当年份缺省时默认为1900年，当月份缺省为0时表示上一年的12月，当日期缺省为0时表示上一个月的月末天数。

常用日期函数

函数名称功能作用

TODAY和NOW用于生成当前日期和时间

DATE根据指定的年份、月份和日期数返回具体的日期值YEAR、MONTH和DAY用于从日期数据中提取年份、月份和日期值YEARFRAC用于计算两个日期序列相差的年数，结果以实数显示EDATE根据指定月份数返回指定日期之前或之后的日期EOMONTH从任意一个日期返回指定月份数的之前或之后的月末日期

基本日期函数：快速生成当前日期和时间Ctrl+ ；和Ctrl+Shift+ ；这样生成的日期和时间格式是固定不变的

=Today()和=Now() 生成的日期会自动更新

=DATE(A2,B2+1,C2)类似都OK

从特定日期提取年月日：年份=YEAR(单元格名称) 月份=MONTH(单元格名称) 日期=DAY(单元格名称)

如=DATE(YEAR(TODAY())+1,10,1)会返回明年的国庆节日期

日期之间的运算：+或- 1)计算相差天数：日期-日期=数值2）计算之前的日期：日期-数值=日期3）计算之后的日期：日期+数值=日期

TODAY=2014年6月29日

判断一个日期所在的季度：

公式利用了DAY (A2)返回的日期数是A2所在月份从第1日起的第几天的来进行处理，当日期减去天数后得出当前月第0天的日期，即为上个月月末日期，再通过DAY函数得出天数。

可根据此公式算出当前日期的下一

个星期天的日期。

工作日函数：

NETWORKDAYS：返回参数start_date 和end date之间完整

的工作日数值。工作日不包括周末和专门指定的假期。

可以使用函数NETWORKDAYS，根据某一特定时期内雇员的

工作天数，计算其应计的报酬。

提示:若要使用参数来指明周末的日期和天数,从而计算两

个日期间的全部工作日数,请使用函数.

统计与求和

汇总大于1000的点点的合计类似汇总指定商品的销量

计算退休日期：

计算工龄（年龄）：

开始时期结束日期

开始--条件格式

利用VLOOKUP函数查询并汇总指定产品月销量和

1、选中区域创建数据表：表1

2、输入内容：

3、制作查询表：

4、输入数组公式：公式在输入结束后，按Ctrl + Shift + Enter完成数组公式的输入。

按Ctrl + Shift + Enter两边多了个大括号

使用通配符（* ）

二维区域的条件求和（SUMIF/SUMIFS）

高级图表

1、筛选法动态图表设置自动筛选是实现动态图表最简单的方法，只要选择全部数据制作图表，再设置自动筛选就好啦。比如：先建立数据表，再选中全部数据建立柱形图（其它的也可以）

再设置筛选数据----筛选看看效果：

2、公式法动态图表

制表添加辅助行

列行（ROW）

确定后完成有效性设置，效果然后在B11中输入公式并将公式复制到C11和D11单元格中。效果

再选中A11--D11建立图表

选任意一个部门查看图表效果

3、定义名称法动态图表

制表添加组合框窗体控件（开发工具—插入）点击组合框点键，选取设置控件格式

设置好相应数据后按确定效果是这样：然后再定义名称：公式---名称管理器

新建

输入定义名称：

及位置

工作表名同样的方法定义另一个名称：

制作图表：选中A1—D2区域插入图表

然后选中图表点击右键，选“选择数据”

在“选择数据源”下点击“编辑”

在系列名称及系列值内输入刚才定义的名称，

点击确定后完成图表的设置，在组合框下拉列表中选择不同的部门查看

下效果：

4、数据透视图

数据透视图可以通过更改报表布局或选择不同的字段实现动态图表，在创建数据透视图过程，EXCEL同时也创建了数据透视表，以便为创

建的图表提供数据源。

创建数据表（还是用刚才的表）：选中A1—D6区域，插入/表格

点击确定后的效果：

创建数据透视图：框选中整个数据表，单击“插入”下的“数据透视表”，选择“数据透视图”

输入相应信息后点确定：

效果

如右

勾选所有字段效果如下：

区域内字段可相互拖动，

效果不同：

部门处可进行筛选：

到此常用就基本OK啦！加油哦！