当前位置:主页 > 平面设计 > excel公式

excel公式

3个Excel公式都看不懂!”
3个Excel公式都看不懂!”

最近收到某位学员的问题咨询,问题是如何根据单据编号和物料长代码返回对应的含税数额。如下表:其实这位学员的问题就是如何实现多条件查询。下面通过一个实例跟大家分享一下常用的几种多条件查询方法。下表是某电商公司的客户投诉表,现在需要通过A表中的客户姓名与地区两个条件来查询B表中的产品型号,返回到A表的E列中。1、LOOKUP函数函数公式:=LOOKUP(1,0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19),$K$3:$K$19)公式解析:首先通过A3单元格与B表I列数据做对比,同时用B3单元格与B表J列信息做对比。在excel中如果两个单元格对比,相等则返回TRUE,在四则运算中用1表示。如果不相等则返回FALSE,使用0表示。那么(A3=$I$3:$I$19)*(B3=$J$3:$J$19)这部分运算的结果就只有0或者1两种情况,因为只有0*1、1*1、1*0这三种情况。用0来除以0和1,由于分母不能为0,所以0/0返回的是错误,0/1返回的结果为0。Lookup函数在查找的时候是忽略错误的,所以只有数据运算结果为1的公式满足条件。那么我们就很好理解0/(A3=$I$3:$I$19)*(B3=$J$3:$J$19)的目的就是将正确结果用0表示,其他的变成错误值,利用函数查找忽略错误这个特点完成查找。总结:本函数由于使用了二分法原理查找,所以如果数据量较大时运算会很慢。

223 次浏览
excel怎么利用OFFSET函数定义名称
excel怎么利用OFFSET函数定义名称

用普通的传统【插入】——【数据透视表】方法制作的数据透视表,虽然有“刷新”功能,但如果在数据源添加了数据行或者列,也不能实现数据透视表的动态更新,如下动:我们可以利用OFFSET函数定义数据源区域名称,实现数据透视表动态更新。关键操作第一步:定义名称【公式】——【定义名称】:引用位置内输入:=OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))。(本示例数据源位于表Sheet3,如果表名不同,改为相应的表名)该公式的含义是:利用OFFSET函数形成一个新的动态区域:这个区域,以A1为基准单元格,向下偏移0行,向右偏移0列,包含的行数是A列所有非空单元格个数,包含的列数是第一行所有非空单元格个数。如果行和列变化,区域也相应的变化。

VLOOKUP查找出现错误值,IFERROR函数来帮忙
VLOOKUP查找出现错误值,IFERROR函数来帮忙

朋友传来如下数据:其中四列“地区”中,名称与排序都不尽相同,需要保留一列地区名称,把A、B、C、D四种数据并列写到地区列后面,即做成如下结果:关键操作VLOOKUP函数出现错误值如果仅仅用VLOOKUP函数,会出现错误值:在C2单元格内输入公式=VLOOKUP(A2,$E$2:$F$35,2),将公式向下填充,C35单元格的公式是=VLOOKUP(A35,$E$2:$F$35,2),结果是错误值“#N/A”,之所以出现错误是因为在查找区域$E$2:$F$35的首列E2:E35内找不到A35单元格的值“*”。用IFERROR函数修正

excel怎么计算平均值?
excel怎么计算平均值?

某公司进行员工考核,数据录入不规范,部分分数带有数量单位“分”。现需要计算员工平均考核分数。解决过程第一步:统一去单位:数量单位“分”,是文本,不能参与计算。所以,在写公式时,首先要把单位去除。去除单位文本用SUBSTITUTE函数:{=SUBSTITUTE(B2:B10,”分”,)},因为是数组计算,所以CTRL+SHIFT+ENTER结束,如下:第二步:计算平均值在B11单元格输入公式:

excel怎样统计业绩最大值
excel怎样统计业绩最大值

很多时候,数据输入并不规范,比如下表中的员工姓名和业绩挤在一个单元格里,要求统计业绩最大值。这种不规范的数据并不是不能统计,只是给统计带来了麻烦。公式实现在C2单元格输入公式:{=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))},(TRL+SHIFT+RNTER结束)。公式分解{=ROW($1:$100)}:返回值是1-100组成的数组{1;2;3;4;5;6;7……98;99;100}{=SUBSTITUTE(B2,ROW($1:$100),)}:

excel一串串长短不一的文本算式,怎么算结果?
excel一串串长短不一的文本算式,怎么算结果?

今天,有一位男士提出这样一个问题:他那可爱的老婆,做微商,学着用EXCEL做买卖记录,今天拿出记录表让他帮着计算现有的存货,这位男士拿到数据,眼睛瞪的老大!这样的交易记录怎么算目前存货?如下:来支招:关键操作第一步:选项设置【文件】——【选项】——【高级】:勾选【转换Lotus 1-2-3 公式】:第一步:数据分列复制B2:B8到C2:C8:

EXACT函数设置条件格式
EXACT函数设置条件格式

以下两列身份证号码,绝大部分是相同的,只有小部分数据不同,且两列的排序不同。现要求标识两列中不相同的数据。关键操作1、选中A2:A21区域,[开始]——[条件格式]——[新建规则]:2、选择规则类型为[使用公式确定要设置格式的单元格],并输入公式:=OR(EXACT(A2,$B$2:$B$21))=FALSE3、选择上右下角[格式],在跳出的设置单元格格式对话框中选择[填充],选择一种背景颜色,确定。选中B2:B21,重复以上三个步骤,只不过输入公式改为:=OR(EXACT(B2,$A$2:$A$21))=FALSE。

COUNT+MATCH,统计两列有多少重复值
COUNT+MATCH,统计两列有多少重复值

有朋友问如何统计两列重复的个数,他想来统计前两个季度销售都进入前一百名的人数。这了好述,把数据改成了统计都进入前十的人数,如下:关键操作公式:在D2单元格输入公式:=COUNT(MATCH(A2:A11,B2:B11,0)),以CTRL+SHIFT+ENTER结果。公式解释:MATCH(A2:A11,B2:B11,0):MATCH函数使用A2:A11为查询值,在区域B2:B11中进行依次查找,查找方式为0,即精确查找,结果返回A2:A11在B2:B11区域首次出现的位置。

INDIRECT函数转换成适合打印的多行多列
INDIRECT函数转换成适合打印的多行多列

尤其打印单位人员名单时,单列数据太长,要做成多行多列适合打印的形式:关键操作公式:在D2单元格输入公式:=INDIRECT(“A”&6*ROW(A1)-5+COLUMN(A1))&””,然后向右向下填充,即可变成适合打印的6列多行数据。公式解析:6*ROW(A1)-5+COLUMN(A1)这部分在D2的运算结果是2,当公式向下填充时,ROW(A1)会自动变成ROW(A2)、ROW(A3)……,计算结果也会变成8、14、20、……,即生成一个步长为6的数值系列;当公式向右填充时,COLUMN(A1)会自动变成COLUMN(B1)、COLUMN(C1)……,这部分结果会变成3、4、5、6、7步长为1的数值系列。

SUMPRODUCT计算指定年份与月份的销售总额
SUMPRODUCT计算指定年份与月份的销售总额

一位朋友说遇到难题:公司要求把近三年的销售额按照年份与月份进行汇总,三年的销售数据有四万多行,他问有没有函数可以实现快速统计。用下所示的简单数据,一下汇总方法:关键操作公式:在F2单元格输入公式:=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15)),向下填充,即得所有指定年份与月份的销售总额。公式分解:(YEAR($A$2:$A$15)=D2):YEAR函数计算$A$2:$A$15单元格的年份,并与D2单元格的年份进行比较,如果等于D2年份,返回TURE,否则返回FALSE。所以此部分返回一组TURE与FALSE的数组(数组1):{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}。

共计279条记录 1 2 3 4 5 6 7 ..28 下一页