excel拼接字符串_Excel 常用操作&函数

前些天整理录制了科室的课程培训，正巧我弄的部分是excel。整理了常用的一些操作以及函数公式，就想着在公众号上也同步一下，顺便当作备忘录，主要是那几个查找引用函数的应用。

ps：封面原图是羊圈大触(羊圈里的大黄鸡)画的叶修，hhh，趁机安利一波我的idol杨洋~

常用操作

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

选定多个不同的单元格，在当前单元格中输入数据→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