Excel常用功能大全（详解版）

1同一excel工作簿查看不同sheet工作表中的相关数据

视图-新建窗口-全部重排（选择重排的方式：垂直并排、水平并排）-不同窗口显示需要对比的不同sheet工作表。

注意：两个窗口实际互为镜像关系，修改一个窗口的数据，另一个窗口也会跟着变。

e.g垂直并排

2保存工作区（.xlw）

即保存表格的布局样式，再次打开仍是保存时的样式，保存为.xlw格式。

3一次插入多个工作表

点击sheet1-长按shift-点击sheetn-右键插入工作表，即实现了一次插入多个工作表。

4移动某列到同一工作表的不同位置

选中整列-长按shift-鼠标呈现十字箭头-移动到需要的地方

5快速到达工作表的边界（前提：单元格是连续的）

选中某一单元格-鼠标呈现十字箭头-四个方向都可以双击

6快速选中有效单元格（前提：单元格是连续的）

选中某一单元格-长按ctrl+shift+不同的方向键

7填充

Ctrl+；是当日日期，左键拖拽填充，右键拖拽可以选择填充的规则

8编辑自定义

选项-高级-编辑自定义-左边新序列-输入新序列规则

e.g张三-enter-李四- enter -王五- enter车-赵六- enter……

注意：每个数值后一定要enter

格式设置

1单元格内画斜线

①单元格内画一条斜线

单元格内填写好内容-设置单元格格式-边框斜线-alt+enter对不同内容进行分行-空格移动内容位置

②单元格内画多条斜线

直接插入形状

注意：设置单元格里的边框斜线可以随单元格变化，而插入的形状斜线是不会随单元格变化的

2设置单元格格式（数值）

设置单元格格式-数值-千位分隔符（1,000,000）

货币-货币符号（￥）

会计专用（会计专用与货币相似，只是会计专用的货币符号在单元格的最左侧）

日期（microsoft采用的是1900-1-1的日期，所有日期变换成的数字都是距离1900-1-1的天数）

特殊（直接转换中文大小写）

自定义 ;;; 隐藏

aaaa星期“几”

aaa“几”

yyyy-mm-ddxx（年）-xx（月）-xx（日）

yyyy"年"m"月"d”日” xx年xx月xx日

@”市” 在单元格内容后面加“市”

单元格里面的数字可以按照数值的正负标注成不同的颜色

3设置单元格格式（文本）

注意：单元格格式里面的数值与文本不可以来回切换

文本数值转换为数值，单元格左上角的警惕号-转换成数字

有时txt会转换到xlsx中，此刻一般会用到分列

e.g在excel中，文本格式的2019-3-28日期转换成2019年3月28日

选中某列分列-分列结束（此处可以不进行实际分列，只是转换下格式）-设置单元格格式（转换成日期格式）

查找、替换和定位

1替换

① 颜色字体替换

替换-高级-填充-全部替换

② 精确替换

替换-高级-单元格匹配（即精确匹配，查找内容为单元格里的全部内容）-替换

③ 模糊替换

替换-高级-格式（模糊替换的格式，e.g张*、李？等）-替换为

注意：？表示一个字符，*表示多个字符，？？可以表示两个字符

在代码里，~后面的通配符（*/?）不生效，e.g某人叫张*替换为张经理

2添加批注

插入的是一般形状的批注：

右键-插入批注-编辑批注内容（右键可编辑、删除、显示/隐藏批注）

审阅里可显示/隐藏所有批注

插入的是特殊形状的批注：

插入-形状（随便添加一个形状）-绘图工具（格式）-编辑形状（右键）-添加到快速访问工具栏

编辑批注-更改形状

注意：批注也可以设置格式

3定位（ctrl+g）

① 批注

选中所有带批注的单元格

② 公式

选中所有带公式的单元格

③ 对象

同时选中表中所有的图片

④ 空值

一般用到单元格的合并与拆分

选中合并的单元格-合并后居中（即拆分为最小单元格）-定位空值（即选中了所有空的单元格）-=↑（即等于各个最小单元格相邻的上面的值）-ctrl+enter

e.g

排序、筛选

1自定义排序

主要key-依据-次序

次要key-依据-次序

注意：自定义排序里面也可以按照颜色排序

e.g在成绩等排序时经常有字段的重要性及数据的重复性，因此经常用到依次从后向前排序的方法，即依次向前直接点击排序。

（第一列数据并排，第二列大小；第二列数据并排，第三列大小……）

e.g部门自定义排序

自定义排序-依据数值-次序（新序列，自己编写）

2把第一行表头插入到每一行的数据中

e.g工资条

先做出对应数量的表头（放在数值下面）-给数值行和表头行添加一列（数值不重复且表头行的数据和数值行的数据交叉）-自定义排序新添加的列

3打印时在第二页自动添加表头

页面设置-工作表-顶端标题行（选择表头）

4筛选

e.g筛选一车间、二车间…五车间、财务部、销售部等数据中的车间数据

右键-文本筛选- 结尾是（车间）

等于（*车间）

5数据高级筛选

数据-高级筛选-方式（将筛选结果复制到其他地方）-列表区域（要筛选的区域）-条件区域（如果是去重复值的，此处不用填写）-复制到（结果存放的地方）

注意：去重要勾选选择不重复的记录

6高级筛选多个条件

先复制粘贴出这些条件到某一区域a（两个或多个条件是and关系写在同一行，两个或多个条件是or关系写在不同行）

数据-）高级筛选-方式-列表区域-条件区域（复制出来的条件区域a）-复制到

注意：此刻不用选择不重复记录

分类汇总、数据有效性

1分类汇总前一定要注意先排序

数据-分类汇总-分类字段-汇总方式-选定汇总项

¨ 替换当前分类汇总

¨ 汇总结果显示数据下方

2对多个字段进行分类汇总时

注意：对多个字段进行自定义排序

不要勾选下面的替换当前分类汇总

分类字段与选定汇总项不同

有时会粘贴汇总的结果，注意定位可见单元格

3使用分类汇总批量合并内容相同的单元格

排序-分类汇总-（除去表头）定位空值-合并后居中-分类汇总全部删除-格式刷刷格式至分类字段

4数据有效性

设置A列仅能输入500~1000之间的整数

设置B列仅能输入字符串长度为8位的产品编码

设置C列付款方式中仅能输入现金、转账、支票

5设置某张表的数据有效性（保护表格数据不被修改）

选中整张表格-数据有效性-允许（自定义）-公式（随便输入）

6数据有效性的出错警告

注意：取消表中的格式（数据有效性-全部清除）

数据透视表

1创建数据透视表

插入-数据透视表-右键数据透视表选项（显示为经典数据透视表布局方便使用）-拖拉字段直接到数据透视表中的相应位置-可更改计数、求和等方式

注意双击数据区域中的某一单元格可显示该单元格的详细信息

e.g 双击数据区域的单元格338，在新的工作表中会显示出单元格338的详细信息

注意：数据透视表右侧的工具栏不小心被关掉后，点击数据表中的任何区域，右键显示字段列表

2数据透视表中创建组

3汇总多列数据

拉不同的字段至不同的列，若是排在了同一列只需拉至后一列即可

注意：数据透视表可嵌套不同的数据模板（美化图表的工具）

4创建计算字段

数据透视表-选项-域、项目和集-计算字段（名称'新列名称’、公式'双击字段写公式’）

注意：删除某一行或列，在透视表右侧工具栏，右键删除

计算结果可以更改格式

对于错误值可以选择不显示，e.g #DIV/0！，右键-数据透视表选项-布局和格式-格式（相对错误值显示'无’）

4批量一次性创建多张工作表并命好名称（前提名称在同一张工作表中的同一列）

插入-数据透视表-字段（拖至数据透视表的最上行）-数据透视表-选项-选项-显示报表筛选页-选中字段-确定

同时删除表格里的内容

Shift键选中所有工作表-复制空白行粘贴覆盖掉表中的数值

创建组

公式、函数

1选中-F4-锁定即实现绝对应用

2基本函数公式

Sum/average/count/max/min/rank

注意：rank使用时一般会用到绝对引用 rank（参数,区域）

跳跃式计算要先定位空值，再ctrl+enter

中的if函数

1if（logical-test,[value-if-true],[value-if-false]）

if中可以嵌套2、3个if

2iserror判断对错经常与if连用

3and函数（and里面可以添加多个条件）

4or函数

5and与or函数

中的countif函数

1countif（range,criteria）

2countifs(range1,criteria1, range2,criteria2……)

2条件格式

条件格式-新建规则-使用公式确定要设置格式的单元格-公式-格式

3设置数据有效性

e.g在A列设置不允许输入重复值

数据-数据有效性-自定义-公式（=countif(A:A,a1)<2）

4countif与countifs的区别

Countif是满足单个条件

Countifs是满足多个条件=COUNTIFS(C2:C22,">=80",D2:D22,">=80")

函数

1sumif(条件区域，条件，求和区域)

注意：sumif与countif都是之统计前15位，注意在条件上添加&’*’

2sumif(A:A,j5&k5,G:G)，针对多个条件

3sumifs(求和区域，条件区域，条件1，条件2…)

4设置sumif的数据有效性

出库量不能大于实际库存量

函数

1vlookup中第二区域若不是整列，要绝对引用

2只有关键字的匹配(连接通配符) *代表字符或无字符

Vlookup(A2&”*”,数据源！B:E,4,0)

3vlookup模糊匹配

对于数据来说只匹配小于该数据的最大值，即最接近该数据的小值

注意：模糊匹配时，查找区域的数据要从小到大排列

一般用在计算提成方面

3数值格式转化成文本格式

数值只能计算，文本可以连接，若对数据进行连接，excel会自动把数值当成文本来对待，所以【数值&””】可以转化成文本

4文本转换成数值【文本*1】【--文本】即负负文本得正文本

5对于格式不同的数据进行匹配

公式

=IF(ISNA(VLOOKUP(I2*1,$E$2:$G$6,3,0)),VLOOKUP(I2&"",$E$2:$G$6,3,0),VLOOKUP(I2*1,$E$2:$G$6,3,0))

Isna()函数是判断括号里的结果是否是#N/A

注意：一般还是转换成统一的格式进行匹配

6横向的数据用hlookup函数

7Vlookup计算个税

与Vlookup嵌套使用

1vlookup只能实现左侧是id引用右侧的数据，且只能引用数值

2match与index嵌套可实现左右两侧的引用，且可引用图片

① Match（lookup_value，lookup_array，match_type）即查找位置

lookup_value：需要在数据表（lookup_array）中查找的值，也可以是文本

lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据

match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况（为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列；为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列；为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列）

② Index（array，row_num,[column_num]）即引用

Array为单元格区域，必须为一维数据

Row_num为数组中某行的行序号

Column_num是数组中某列的列序号

③ Index与match嵌套

Match查找，index引用

世界上本无vlookup，用的index与match的人多了，便形成了vlookup

注意：嵌套时经常用到绝对引用

3match与vlookup返回多列结果

注意：嵌套时的混合引用

Match也可查找文本

1excel中的数据批量填充到word文档中

邮件-邮件合并-邮件合分步向导-下一步开启-下一步选取收件人-浏览需要导入的excel表格-双击excel表（注意：如果选择错了表格，点击选择另外的表格）-下一步撰写信函-其他项目选择需要插入的字段-预览结果-再次预览结果可以返回编辑界面

-完成并合并-编辑单个文档-合并到新文档-生成一个新的文档（一页中包含一条数据）

-完成并合并-发送电子邮件-选择收件人，发邮件

-目录-完成并合并-编辑单个文档-合并到新文档-生成一个新文档（一页中包含很多数据）

2邮件合并后的资金日期格式处理

ALT+F9查看邮件中日期或者资金的源代码，再次ALT+F9是返回原界面，返回之后注意单击F9进行刷新

常用日期与时间计算

1计算结束时间

excel中的整数时间是代表“天”,所以【“90天”/24小时/60分钟】

2计算时长

注意：设置单元格格式为常规

3计算结束/开始日期

注意：日期在excel中其实是一个数字，所以可以直接相加减

4计算工龄=datedif（start_serial_number,end_serial_number,return_type【”y”,[“m”],[“d”]】）

=datedif（开始时间，结束时间，”ym”,[“md”],[“yd”]）

“ym”指除去整年剩余的月数

“md”指除去整月剩余的天数

5计算间隔年月日

6计算第几周=weeknum（serial_number, return-type）

7计算周几=weekday(serial_number,return_type)

注意：写好公式后要设置成星期的格式

8第几周周几

9自定义周几=text(serial_number,”aaaa”)

注意：先设置自定义星期aaaa

10自定义日期=text(serial_number,”0000-00-00”)

注意：先设置自定义日期0000-00-00

11根据间隔月份计算结束日期=date（year,month,day）

求年份=year(serial_number)

月份=month(serial_number)

日=day(serial_number)

12计算本月最后一天=date（year,month,day）

注意：本月最后一天即为下月的前一天

day=0即为下月的前一天

day=1即为下月的第一天

13计算本月天数

14计算本月剩余天数

1为数据透视表中的数据制作数据条和切片器

注意：在插入数据透视表时要选中非空值的单元格，否则透视表中会出现空白的行和列

在数据透视表中经常对日期列进行右键-创建组-按月或者季度分组

制作数据条：选中数据-条件格式-数据条

插入切片器：选中数据-插入-切片器（切片器也可看做是筛选器，可用来添加新的维度）

添加的新维度可以切换（筛选）

2条件格式类型

① 突出显示单元格规则

>/</<<（注意：min<介于=<max）/=/⊆/发生日期/重复值

② 项目选取规则

选取数据中的max/min/max%/min%/>average/<average

③ 数据条

即筛选器，添加新的数据维度

④ 色阶

一般用于处理温度，表示随着数据的升降，颜色的深浅跟着改变

⑤ 图标集

⑥ 新建规则对应

Ø 数据条

Ø 突出显示，可以查找错误值#DIV!0

Ø Max.min

Ø 比较选定的值

Ø 重复值

Ø 公式

选中需要设置条件格式的字段（注意：在选中数据时一般不要选中表头，条件格式中的公式经常用到混合引用）

⑦ 管理规则即条件格式中的公式

2利用条件格式将日期为周末的标记为红色

选中日期（除表头）-条件格式-新建规则-公式=WEEKDAY(A2,2)>5

将周末整行标记为红色

选中所有数据（除表头）-条件格式-新建规则-公式= WEEKDAY($A2,2)>5

利用条件格式标记未来15天内将要过生日的员工

=DATEDIF(DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),12,31),"d")-DATEDIF(B2,TODAY()-1,"yd")<=15

=本年的总天数-到今天为止除去到某一日期整年后剩余的天数=到今天为止本年剩余的天数

1文本函数包含

Ø =Left(text,num_chars)，num_chars从左至右截取的字符数，左截取

Ø =Right(text,num_chars)，右截取

Ø =mid(text,start_num,num_chars), start_num查找字符串文本中的起始位置，中间截取

Ø =find(find_text,within_text,[start_num))，find_text要查找的字符，求某一字符的位置

Ø =len(text)求字符个数

Ø =lenb(text)求字节个数

注意：英文字母/数字/符号一个字符都代表一个字节，而汉字是一个字符代表两个字节

2文本函数结合运用

Ø 身份证倒数第二个偶数代表女性，奇数代表男性

15位的是倒数第一位，18位的是倒数第二位

Ø 截取单位len/lenb

Ø 截取特定字符前后

注意：100是取巧，因为后面要截取的字符已知不超过100个

Ø 截取地区码

注意：文本函数*1才能当做数学函数运算

Ø 计算出生年月date(year,month,day)

Ø 求身份证性别mod是求余函数

1数学函数包含

Ø =Round(number,num-digits)，求四舍五入，num-digits四舍五入后的小数点个数

Ø =roundup(number,num-digits)，无条件向上进位

Ø =rounddown (number,num-digits)，无条件向下舍去

Ø =Int(number)，取整，结果都是小于该值的整数

Ø =mod(number,divisor)=mod(被除数，除数)，求余

2row与column函数

=row()求行数

=column()求列数

注意：match()查找与特定字符有关，row()与column()与位置有关

3round、roundup、rounddown函数应用

4int函数应用

5row与column函数应用

注意：row与column后的数字是随单元格位置的变化而变化的

一行的转置也可选中数据-复制-移到需要粘贴的位置-选择性粘贴-转置

注意：row*（相隔的行数+1）

注意：row*（相隔的行数+1），column-与位置有关的规律数

1数组即为矩阵，数组外一定要加大括号ctrl+shift+enter

注意：一个值可以与一组值相比较计算，true=1,false=0,因此计算结果的true/false*1可以与其他值进行计算

注意：sumproduct=大括号+sum

Sumproduct返回相应的数组或区域乘积的和

2lookup(lookup_value,lookup_vector,[result_vector])

lookup_value,要查找的内容

lookup_vector,要查找的区域

result_vector,返回需要的某列

注音：lookup没有第四参数，即没有精确/模糊匹配参数，一般lookup都是模糊

E4=$A$2:$A$92是true/false

(E4=$A$2:$A$92)*1是1/0

0/((E4=$A$2:$A$92)*1)是0/#DIV0!(即错误值)

而lookup只能查找正确的数据

函数

1indirect(ref_text,[a1]),ref_text单元格的引用

Indirect与index的引用比较

2引用多表中的数据问题

注意：&字符的运用，因为A:G是不变的，所以要””

注意：多表引用时的混合引用

3indirect与数据有效性

Ø 选中省份下面的城市-公式-定义名称（注意定义的名称要与indirect引用的内容一致）

Ø 给每列设置数据有效性（每列的标题先不要填写，在最后有限性设置完，再取消第一行的标题栏的有效性）-序列-序列内容的区域

Ø 后面列的有效性-序列-indirect(前列对应单元格)

1图表中的元素

2了解主次坐标轴

注意：主次坐标轴的刻度与刻度的max/min

3折线图与柱形图结合图表

4制作计划于实际对比图

注意：坐标轴的刻度单位

5制作双向柱形图（旋风图）

插入-条形图-设置次坐标轴-次坐标轴的刻度固定大小-删掉上面的次坐标轴-下面的主坐标轴设置数值格式为0%;0%-分类轴（y轴）标签设置为高或者低-逆刻度值是指分类轴的上下换位置-复制背景图片前先设置背景图片的艺术效果

5利用复制粘贴更改数据系列显示样式

制作好条形图后直接插入心形-复制-粘贴到原条形区域-右键-设置数据系列格式-填充-层叠

注意：若想拉开心形间的距离可以在心形形状上在插入一个无填充的矩形

若插入的无填充矩形不好选中-开始-查找与选择-选择对象-点击矩形大概区域

6甘特图（一般用在项目进度上）

插入堆积条形图-把日期条设置成完全隐藏的格式-设置坐标轴的刻度-设置分类轴的逆刻度-日期刻度的固定值大小是按照日期的数字格式确定的

6.1动态甘特图

动态甘特图里条形分为3段（隐藏的日期段、已完成、未完成）-所以选取的是计划开始时间、已完成、未完成数据（注意if函数的应用）-c18一般是指当日的日期-插入的滚动条刻度设置为日期的刻度范围（注意是实际范围，不是数值大小）-滚动条链接一个空单元格-c18与空单元格之间建立一定的联系（c18=b2+空单元格）

7巧用图表模板

注意：上面的这些图表都可以粘贴到excel中作为模板使用

粘贴到excel中-图表工具-另存为模板

附加：图片-右键-大小和属性-属性-对象位置-大小和位置随单元格而变

坐标轴-右键-设置坐标轴格式-显示单位

图表链接与动画

1双坐标柱形图

插入簇状柱形图-设置主次坐标轴-选择次坐标轴-选择数据-添加两个系列值为0的新系列-图表工具布局-选中一个系列值为0的系列-设置所选内容格式为主/次坐标轴-选中一个系列-选择数据-把一个空的新序列向上移

注意：把系列值为0的图例删掉

2饼图美化

插入三维饼图-右键三维旋转-取消自动缩放-高度调小-右键-三维格式-棱台-右键-数据标签居中

3双层饼图

双层饼图哪一个在上面先做哪一个

插入二维饼图-选择数据-添加（注意：系列值选择添加的是后饼图的数据）-新系列的水平轴标签选择的是新分类-右键设置前饼图为次坐标轴-向外同时拖动次坐标轴可以看到后面的主坐标轴-单个向内拖动次坐标轴-添加数据标签

注意：要设置边框

4图表插入到PPT中

Ø 复制图表-直接粘贴到PPT中（此时的格式是随PPT的主体系列更改的）

Ø 复制图表-PPT中保留原格式（格式不随PPT的主题格式变化）

Ø 复制图表-PPT中保留链接数据（若更改excel中的原数据，PPT中的表格工具设计里刷新数据可以直接在PPT中更改图表样式）

Ø 复制图表-保留原格式和链接数据（若更新excel中的数据，在新打开PPT时会提示是否更新数据，此时的更新是整个PPT中的数据都进行更新，不用再单个更新）

5PPT中图表的动画设置

选中图表-动画-动画进入方式-动画-动画窗格-右键-效果选项-图表动画-组合图表中选择按分类中的元素（或其他）-在动画窗格里可以看到多个动画对象-选中第二个对象shift至图表中的最后一个对象-右键-从上一项开始之后开始-播放

1 OFFSET(reference,rows,cols,height,width)

e.g以A1单元格为例，OFFSET（A1，1,1,2,2）即以A1单元格为参照物，下移1行，右移1列，选取两行两列，所以offset得到的是数据区域

把整个表格用offset定义为一个动态数据区域后，可以插入一个数据透视表-表/区域填写为定义的动态数据区域

选取动态数据区域，用offset（$A$1，0,0，counta($A:$A),b）

Counta($A:$A)代表A列非空单元格数，b代表选取的列数

定义名称动态数据区域，编辑公式为offset

然后在数据透视表中选中某行数据刷新即可随着表格及时更新

2offset函数经常与数据透视表、各种条形折线图连用

注意：有时一个表格会用到两个offset函数来定义名称

e.g取后10天的成交量（后10天的数据随着数据的增删而变，所以是动态图）

定义名称-日期（=offset($A$1,counta($a:$a)-10,0,10,1)）

定义名称-成交量（=offset($B$1,counta($a:$a)-10,0,10,1)）

插入空白柱形图-选择数据-添加新系列（成交量，=表名称!定义的名称）

水平轴标签编辑（=表名称!定义的名称）

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。