Excel常用12个公式

兰色今天精选出12个excel函数公式,虽然它们不并常用,但很实用。需要用时你还真不好搜到它们,所以赶紧收藏起来吧。

  1、动态获取A列最后一个数字

  =LOOKUP(1,0/(A2:A100>0),A2:A100)

  2、不重复个数公式

  =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

  3、提取唯一值公式

  数组公式(按ctrl+shift+enter三键输入,以下带{}输入方法相同)

  =IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$2:A$7,A$2:A$7,0)=ROW(A$1:A$6),ROW(A$2:A$7)),ROW(A1))),"")

  4、中国式排名公式

  =SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

  5、提取字符串任一位中的数字

  A1的值为 ABC123.45FE ,截取结果:123.45

  数组公式:

  {=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1)}

  6、金额大写转换公式

  =TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")

  7、个人所得税计算公式

  假如A2中是应税工资,则计算个税公式为:

  =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

  8、一对多查找包含公式

  =COUNT(FIND({"AAA花苑","CCC龙廷"},A2))

  9、Vlookup多表查找公式

  工资表模板中,每个部门一个表。

  在查询表中,要求根据提供的姓名,从销售~综合5个工作表中查询该员工的基本工资。

  方法1:

  =IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))

  方法2

  =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

  10、Sumif多表求和公式

  工作表名称常常是不规则的,如下图所示

  分析:

  首先我们需要用宏表函数取得表名

  公式 - 名称管理器 - 新建名称 - 在新建名称中输入名称“sh”,然后“引用位置”框中输入公式:

  =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(now())

  最终公式为:

  =SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh&"!c:c"))))

  11、筛选后连续序号公式

  插入辅助列,输入公式 =1。加入这列是方便在序号列使用公式统计行数。

  在序号列输入公式

  =SUBTOTAL(2,B$1:B2)

  12、用QQ聊天的excel公式

  =HYPERLINK("tencent://message/?uin="&C3,"点击联系他")

时间: 05-11

Excel常用12个公式的相关文章

Excel常用函数总结

Excel常用函数总结 2016-10-28 Kevin 叼着奶瓶撩妹 1. VLOOKUP函数 常见形式 问题描述:将下图中G列的数据根据学生的姓名填充到D列. 公式解析: =VLOOKUP(A2,$F$2:$G$16,2,FALSE) 参数1:需要查询的数据 参数2:指定查询的区域,必须包含查找值和返回值,且第1列必须是查找值 参数3:$F$2:$G$16区域的第2例 参数4:指定的查找方式,TRUE表示模糊 查找,FALSE表示精确查找 解法之二 上图中的问题也可使用INDEX+MATCH

[转]EXCEL如何使用动态公式

本文转自:http://tech.cncms.com/ruanjian/office/excel/95440.html 也许大家可能还不知道Excel中的动态公式是什么,所谓的动态公式,不是普通的公式,而是更好灵活的公式的使用方法.下面就给你举例来说明. 一.构造动态公式 在EXCEL某个工作表中,如果随时查看某个动态区域的统计结果,可以设计动态公式: 在某个单元格中输入函数式:=AVERAGE(INDIRECT(数据统计区域的开始位置标记):INDIRECT(数据统计区域的结束位置标记)) 比

EXCEL常用函数公式

1    EXCEL基本操作 1      基本概念:工作簿,工作表,行,列,单元格 2      数据录入 批量录入相同数据(ctrl+enter) ***号码输入(设置单元格属性为"文本"或者前面加 ' ) 分数输入(直接输1/3EXCEL默认为1月3日,应该写成0空格 1/3) 单元格里换行(alt+enter) 3      查找 :模糊查找.值查找 4      冻结窗口 5      筛选:一般筛选,空行筛选 6      行列转置 7      插入对象:对象格式的设置

Excel常用函数公式TOP6(下)

正如我们所知,Excel在数据分析中是一个必不可少的工具,不管是分析师.业务还是运营人员,最常用到的工具软件非Excel莫属,接下来介绍一下在Excel中较为常用的一些函数公式. 1.vlookup---条件查找函数用法:=vlookup(要查找的目标,查找范围,返回第几列的内容,精确或者近似结果)PS:查找值必须在返回值的前面. 2.lookup---多条件查找函数用法:=lookup(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查找目标的区域) 3.mid---提取字符串函数

excel常用公式

trim:去除单元格两端的空格. concat/&:连接单元格内的内容. mid:  提取字符串中间的字符串. left:  提取字符串左边的字符串. right: 提取字符串右边的字符串. replace/substitute: 替换字符串中的连续几个字符或者某个字符. len/lenb: 计算字符串长度.注:对于len来说,一个中文字符的长度计算为1,对于lenb来说,一个中文字符的长度计算为2,对于英文字符或者数字则没有差异. 原文地址:https://www.cnblogs.com/im

Excel常用函数大全

1.ABS函数  函数名称:ABS  主要功能:求出相应数字的绝对值.  使用格式:ABS(number)  参数说明:number代表需要求绝对值的数值或引用的单元格.  应用举例:如果在B2单元格中输入公式:=ABS(A2),则在A2单元格中无论输入正数(如100)还是负数(如-100),B2中均显示出正数(如100).  特别提醒:如果number参数不是数值,而是一些字符(如A等),则B2中返回错误值“#VALUE!”. 2.AND函数  函数名称:AND  主要功能:返回逻辑值:如果所

Excel教程(12) - 数学和三角函数

ABS     用途:返回某一参数的绝对值.   语法:ABS(number) 参数:number 是需要计算其绝对值的一个实数. 实例:如果 A1=-16,则公式"=ABS(A1)"返回 16. ACOS 用途:返回以弧度表示的参数的反余弦值,范围是 0~π. 语法:ACOS(number) 参数:number 是某一角度的余弦值,大小在-1-1 之间. 实例:如果 A1=0.5,则公式"=ACOS(A1)"返回 1.047197551(即 π/3 弧度,也就是

EPPLUS 实现excel报表数据及公式填充

年后工作第一天,根据客户要求修善EPPLUS报表. Epplus: Epplus是一个使用Open Office XML(Xlsx)文件格式,能读写Excel 2007/2010文件的开源组件 好处很多,比office COM组件好用,不用考虑运营机的office版本问题.需要引用epplus.及 using OfficeOpenXml; 规范: 所有操作语句都放在以下using中: string file = outputDir.FullName; if (File.Exists(file))

POI/Excel/HTML单元格公式问题

一.问题描述 使用MyBatis从数据库中获取数据,然后用POI把数据填充到Excel模板中,生成最终的xls文件.把最终的xls文件转换为html文件,并返回给前台显示在Panel中. Excel模板中,除了数据点位符外,还有一些计算公式.由于这些计算公式引用的数据在模板中是点位符,所以计算单元显示为"#VALUE!".见下图: 生成Excel文件,在添加了重算的相关代码(见下文)后,计算单元格的值能够正常显示.转换为html后,这些计算单元格不会重新计算,仍然显示为"#V