1. 首页
  2. 自学中心
  3. 软件
  4. Excel

Excel 常用操作&函数

常用操作
图片
在多个单元格中输入相同数据:选定多个不同的单元格,在当前单元格中输入数据→Ctrl+Enter

图片
数据有效性设置:“数据”菜单→数据验证→设置→根据提示框进行选择

图片
复制可见单元格:选中数据→按F5/Ctrl+G/“开始”菜单“查找与选择”组→定位条件→可见单元格→确定→复制粘贴

图片
分类汇总:第一步,排序:先以分类字段为主关键字对要汇总的所有数据进行排序

第二步,分类汇总:选定数据范围内的任一单元格→“数据”菜单→分类汇总→在对话框中:选择“分类字段”、“汇总方式”、“选定汇总项”→确定

图片
数据透视表:选定包含数值的单元格→“插入”菜单→数据透视表→根据向导操作

ps:数据透视表中的数据不会随着数据源的改变而同时改变,必须按“刷新”按钮才能实现,快捷键是Alt+F5

图片
只计算选定单元格:开发工具→Visual Basic→Ctrl+G→selection.calculate→回车

函数
图片
日期与时间函数
返回“年”:=year(serial_number)

返回“月”:

=month(serial_number)

返回“日”:

=day(serial_number)

返回当天时间,函数无参数:

=today()

向前/向后推几个月 的最后一天 :

=eomonth(日期,推几个月)

信息函数
图片
文本函数
与文本拼接时,将字符转化为相应的格式:=text(value,格式)

图片
将每个汉字的字符数按2计算:=lenb(text)

对所有字符都按1计算:

=len(text)

两者相减即为汉字个数

=lenb(text)- len(text)

截取字符串:

=left(需要截取的字符串,从第一个字符起截取字符数)

=right(需要截取的字符串,从最后一个字符起截取字符数)

=mid(需要截取的字符串,从第几个开始取,提取长度)

将多个字符串合并:

=concatenate(字符串…)

提取文本,数字、日期、时间、错误值、公式生成的值不可提取:

=phonetic(提取文本区域)

重复显示:

=rept(需重复显示的文本,重复显示次数)

统计函数
平均值:=average(number1,[number2],…)

最大值:

=max(number1,[number2],…)

最小值:

=min(number1,[number2],…)

计数:

=count(value1,[value2],…)

=countif(条件所在区域,条件)

=countifs(条件所在区域1,条件1[,条件所在区域2,条件2,…])

第三个参数省略或为0时降序排位,非0时升序排位:

=rank(需排名的数值,需排名数值所在的数据列表[,order])

不重复排名:

例:=rank.eq(H56,H$55:H$99)+countifs(H$55:H56,H56)

数学和三角函数
求和:=sum(number1,[number2],…)

=sumif(条件所在区域,条件,实际求和区域)

=sumifs(实际求和区域,条件所在区域1,条件1[,条件所在区域2,条件2,…])

将数字四舍五入到指定的位数:

=round(number,小数位数)

求余数,符号与除数相同:

=mod(被除数,除数)

例:=mod(row(),2),余数为0是偶数行,余数为1是奇数行。

符号,正数为1,负数为-1,0为0:

=sign(number)

逻辑函数
if条件函数,第三个参数省略直接返回FALSE :=if(条件,条件为真时返回值[,条件为假时返回值])

数据报错时返回指定值:

=iferror(value,value_if_error)

=and(logical1,[logical2],…)

=or(logical1,[logical2],…)

查找引用函数
 find
返回查找字符串在单元格的位置:=find(查找字符串,要查找字符串所在单元格[,开始查找位置])

 indirect
返回由文本字符串指定的引用:=indirect(单元格引用[,引用方式])

引用方式:A1格式为true或忽略;R1C1格式为false。

 offset
返回对单元格或单元格区域中指定行数和列数的区域的引用:=offset(参照点,移动行数,移动列数[,扩展选取行数,扩展选取列数])

 index
查找某一行和某一列的交叉单元格中的值:=index(连续区域,行数,列数)

=index((区域1, 区域2,…),行数,列数,非连续区域中第几个区域)

隔行提取信息:

=index(A:A,ROW(A1)*2)

 match
=match(查找值,查找范围[,匹配类型])第三个参数是模糊匹配或精确匹配:

0为精确匹配;

1或忽略为搜索小于等于查找值的最大值,第二个参数数据源必须是升序排序;

-1为搜索大于等于查找值的最小值,第二个参数数据源必须是降序排序。

 vlookup
=vlookup(查找值,查找范围,列数[,匹配类型])第四个参数是模糊匹配或精确匹配:

0或false为精确匹配;

1、true或忽略为模糊匹配,搜索小于等于查找值的最大值,第二个参数数据源的第一列必须是升序的。

查找列不在首列时:

=VLOOKUP(查找值,IF({1,0},查找值所在列,需返回结果列),2,0)

多条件查找:

{=VLOOKUP(查找值1&查找值2…,IF({1,0},查找值1所在列&查找值2所在列…,需返回结果列),2,0)}

多条件之间用“&”符号拼接,不同的是多条件时是数组,需要按Ctrl+Shift+Enter

 lookup
=lookup(查找值,查找值所在区域,返回的结果)返回小于等于目标值的最大值,第二个参数为单行或单列,所在区域升序排序。

=lookup(查找值,二位数组)

查找并返回小于等于目标值的最大值,第二个参数为单行或单列,所在区域升序排序。

多条件查找:

=LOOKUP(1,0/(条件1)*(条件2)*(条件3),返回结果列)

第一个参数为1,第二个参数为0/(条件),多个条件之间用*号连接,第三个参数是返回的结果列。

第二个参数,条件满足,结果就是true,也就是1,0/(条件)返回的就是0;条件不满足,结果就是false,也就是0,0/(条件)返回的就是#DIV/0。所以,0/(条件)就是为了构建0,#DIV/0组成的数组。

1作为查找值,在数组中查找小于等于1的最大值,也就是0,可以查找最后一个满足条件的记录。

 一对多查找
下拉公式,选出满足条件的一组数(一对多查找):{=iferror(index(输出结果范围,small(if(条件范围=条件,row($1:$条件范围数数量)),row())),””)}

条件范围 与 $1:$条件范围数数量 数据个数相同,所以公式也可写成:

{=iferror(index(输出结果范围,small(if(条件范围=条件,row(条件范围)),row())),””)}

返回数据组中第k个最小值:

=small(数据范围,返回数据在数据区域里按从小到大排所在的位置k)

生成{1;2;3;…;99}的数组:

=row(1:99)

row(条件范围)函数生成由 条件范围的行号 组成的数组。

if函数 条件满足时返回row函数生成的数组(条件范围的行号)的相应值,条件不满足是省略直接返回false。最终生成 数值(条件范围的行号)和false 组成的数组。

small函数 返回  if函数生成数组(行号和false 的数组)中 第k个最小值;用row函数可以直接下拉公式实现递增。

index函数 第一个参数是输出结果范围,第二个参数 查找的行数是small函数得到的数据(排名k的行号),第三个参数 查找的列数省略。

所以通过index+small+if的组合公式,满足条件的计算得到 需要输出的结果,不满足条件的结果是 报错值。

最后用iferror函数对错误值的返回结果进行修改,为空白。

组合公式是数组,需要按Ctrl+Shift+Enter。下拉就可以得到满足条件的一组数。

补充
区域,一般指表格的行、列范围。它有一个连续区域与多个区域之分。数组,对表格区域进行一次顺序计算后置于内部待于调用的数组,称为内存数组。如 A1:A50

数组,人为的以行、列形式输入的数值,用于对照计算的数组,称为常数数组。如 {1;2;3;4;5;6;7;8;9}

IF({1,0},A:A,B:B)数组

第一个参数:{1,0;1,0;1,0;…}

第二个参数:{A1,A1;A2,A2;A3,A3;…}

第三个参数:{B1,B1;B2,B2;B3,B3;…}

返回数组:{A1,B1;A2,B2;A3,B3;…}

升序排列:

…, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE

来自公众号:祎雅轩

Excel 常用操作&函数

原创文章,作者:xsmile,如若转载,请注明出处:http://www.17bigdata.com/excel-%e5%b8%b8%e7%94%a8%e6%93%8d%e4%bd%9c%e5%87%bd%e6%95%b0/

联系我们

在线咨询:点击这里给我发消息

邮件:23683716@qq.com

跳至工具栏