$B,SMALL(IF($A$3:
$A$9158=$E3,ROW($A$3:
$A$9158)),COLUMN()-COLUMN($E3))))
1、总体意思:
如果在A:
A中出现的重名字次数小于取名字排名的次数时则显示空白,否则取得与名字相同的第1、2、3......N个工号
2、COUNTIF($A:
$A,$E3):
计算与E3相同的名字在A栏的次数
3、COLUMN():
取当前栏位的栏号,COLUMN($E3):
取E3栏位的栏号即“5”
4、INDEX()取B栏中对应的序列号的内容
5、MALL()计算第几个最小值
6、Row()取行号
数组公式要三键结束:
Ctrl+Shift+Enter(回车键)
附与其他年度表比较公式:
=VLOOKUP(A3,'2011'!
$A$3:
$E$9433,2,FALSE)
人员变动:
=IF(ISERROR(VLOOKUP(A3,'2011'!
$A$3:
$E$9433,1,FALSE))=TRUE,"人员调出",IF(AND(ISERROR(VLOOKUP(A3,'2011'!
$A$3:
$E$9433,1,FALSE))=FALSE,B3=VLOOKUP(A3,'2011'!
$A$3:
$E$9433,2,FALSE)),"人员、部门都不变","人员不变但部门变"))
单位(部门)合并:
=F3&"/"&G3&"/"&H3&"/"&I3&"/"&J3&"/"&K3&"/"&L3
单元格含有类似下列字符a/b/c/////、aa/s/d/r//、e/rrr/tt/、kk等(中间只有单),如何把后面所有的“/”替换掉但中间的“/”不替换,变成a/b/c、aa/s/d/r、e/rrr/tt、kk等
分两步进行:
首先含两个//以上可以直接替换,即把//*替换成空白。
其次把所有单元格中最后一个字符为/的替换掉而最后字符不是/则保留原来,用公式1:
=IF(RIGHT(A1)="/",LEFT(A1,LEN(A1)-1),A1&"")这有个缺点,如果A列为数字时,公式返回的数字会变为文本格式
或公式2:
=IF(A1="","",IF(RIGHT(A1)="/",LEFT(A1,LEN(A1)-1),A1))第二个公式不会产生数值格式变化的问题
特别注意:
有时两个电子表中的单元格字符间(如以两个字命名的姓名间)常常会有空格,引起不匹配,这时要用查找替换把两个电子表中的空格去掉,查找内容处输入一个空格,替换为那个地方什么都不填,然后选择全部替换即可。
有时可能还有空格,那个空格可能是全角空格,再替换一次全角空格就可以。
EXCEL中如何设置列中有重名的自动标注为红色
EXCEL2003:
选定需操作区域-------格式-----条件格式-----公式-----=COUNTIF(A:
A,A1)>1 -----单元格格式为图案,最主要的是公式中的A:
A表示A列(需操作的区域,如A1:
A9),必须是你有数值的区域。
EXCEL2007:
选定需操作区域-------开始-----条件格式-----新建规则----使用公式确定要设置格式的单元格-----=COUNTIF(A:
A,A1)>1 -----单元格格式为填充,最主要的是公式中的A:
A表示A列(需操作的区域,如A1:
A9),必须是你有数值的区域。
用红圈圈圈画Excel表格中的数据格
公司统计时也许想把特别的数据突出显示,考试后老师也可能会想把Excel表格中高分或者容易错误的地方用红圈突出显示出来。
这样在Excel中如何实现呢?
下面我以成绩表为例讲解一下如何实现这一要求,本例以语文成绩在120分以上的用红圈圈画出来突出显示。
屏显操作步骤:
第一步:
打开成绩表→选择语文成绩→单击菜单栏上的“数据”按钮→选择“有效性”。
第二步:
在弹出的“数据有效性”对话框中,在“允许”一栏中选择“整数”→在“数据”下面框中选择“介于”→最小值输入0→最大值输入120→确定。
第三步:
单击菜单栏上的“工具”按钮→选择“公式审核”→选择显示“公式审核”工具栏。
第四步:
单击“圈释无效数据”按钮。
这样语文分数在120分以上的就被圈上了一个红圈,起到了突出显示的作用。
但这些圈只能在电脑中显示出来并不能打印出来。
有的老师想把它打印出来,笔者经过探索,找到一种可行的办法,下面把这种方法介绍一下。
打印操作步骤:
第一步:
选择表格→按住Shift键不放单击菜单栏上的“编辑”按钮→选择“复制图片”→确定。
第二步:
单击菜单栏上的“编辑”按钮→选择粘贴图片→把原数据表删除→调整一下复制的数据表的位置就行了。
注:
在进行第一步和第二步的操作过程中要始终按住Shift键不放,否则操作不成功。
当单元格不等于0,或不等于1,或空白时就显示红色底纹
在第一个图中:
选定需操作区域---格式--条件格式--单元格数值--单元格格式为图案--红色--然后按“添加”设置条件2、3……,所得的结果却全部显示红色,这是因为三个条件之间没有明确并集或者交集的关系,所以要用第二个图的方法
选定需操作区域---格式----条件格式---公式---=AND(A2<>1,A2<>0,A2<>"") ---单元格格式为图案--红色
IF函数中参数省略或为空的执行结果
IF(logical_test,value_if_true,value_if_false)
其中有三个参数:
第一个固定为逻辑参数,第二个固定为TRUE参数,第三个固定为FALSE参数。
TRUE参数任何情况下都不能省略,只能为空,而FALSE参数可以出现省略或为空的情况。
两个(TRUE参数和FALSE参数)都省略的话就没什么意义了(此时函数会出现错误无法运行),而两个都为空时,不管逻辑为TRUE或FALSE,执行结果都为0。
当逻辑为TRUE时,TRUE参数为空,执行结果为0。
TRUE参数为非空值,执行结果为非空值。
当逻辑为FALSE时,FALSE参数被省略,执行结果为FALSE。
FALSE参数为空,执行结果为0。
FALSE参数为非空值,执行结果为非空值。
特别说明:
TRUE参数为空即logical_test后有逗号但没有值,如“=IF(A2<0,)”、“=IF(A2<0,,)”、“=IF(A2<0,,"正数")”
FALSE参数为空即value_if_true后有逗号但没有值并紧跟着右括号,如“=IF(A1<0,"负数",)”、“=IF(A1<0,,)”
FALSE参数被省略即value_if_true后没有逗号,如“=IF(A3<0,"负数")”、“=IF(A3<0,)”
[已解决]公式理解请教:
=INDEX($A$2:
$A$7,MATCH(,COUNTIF($B$1:
B2,$A$2:
$A$7),))
山菊花:
这个公式是gvntw版主写的,应该有过解释。
公式COUNTIF($B$1:
B2,$A$2:
$A$7)计算区域$B$1:
B2中包含$A$2:
$A$7每一个元素的个数。
把公式展开,相当于:
COUNTIF($B$1:
B2,$A$2)=1
COUNTIF($B$1:
B2,$A$3)=1
…………
COUNTIF($B$1:
B2,$A$7)=0
因为是数组公式,该公式返回的结果是一个与$A$2:
$A$7大小相同的数组,最后结果是{1;1;0;1;1;0}
取得的结果主要是判断$A$2:
$A$7中的元素是否在结果区域$B$1:
B2中出现过,从中获得第一个没出现的元素的位置,即0的位置。
该例$B$1:
B2中第一个没出现的元素是“1”,有:
COUNTIF($B$1:
B2,A4)=0
即:
COUNTIF($B$1:
B2,1)=0
0在结果数组中的位置是3。
MATCH(,COUNTIF($B$1:
B2,$A$2:
$A$7),)相当于MATCH(,{1;1;0;1;1;0},)其中省略参数默认值为0,即公式相当于:
MATCH(0,{1;1;0;1;1;0},0)它的结果是3。
这个3的意义是,$A$2:
$A$7中,第一个没有在区域$B$1:
B2中出现的元素是第3个,即A4单元格的值,即“1”。
=INDEX($A$2:
$A$7,MATCH(,COUNTIF($B$1:
B2,$A$2:
$A$7),))相当于:
=INDEX($A$2:
$A$7,3)结果是1。
终于理解MATCH(,COUNTIF($B$1:
B2,$A$2:
$A$7),)的意思了,原来是省略了0。
谢谢山菊花老师!
问1:
MATCH函数的第一个参数是0,省略了
问3:
这个公式应该是用MATCH函数查找这些数字在B列没出现的,然后用INDEX函数定位,如果A列有空格,空格默认为0值
问4:
如果第一行不为空,我想那就不能用这个公式了,因为就靠第一行空格来定位第1个数字呢。
不知我的理解对不?
问题2的公式我也看不懂,从这个例子中又学会了一个提取不重复值的方法,谢谢啦
这个公式看过好几遍了,就是提取不重复值的公式,以前一直不明白其中的道理,现在总算是清楚了,谢谢这么详细的解释
这里的$号是不是有误?
是不是应该($B$1:
B$2,$A2),如果b2的“2”前没有$号,下拖就要变成($B$1:
b3,$a$2),$a$2下拖不会改变。
我的理解对吗?
和你有同样的疑问?
B2是不是要加$
@adidas315073682,个人认为你的理解是对的,不知什么原因,我发觉大家都没有提到这一块,另外,关于第一行是否空行,好像不是很重要,同时提供了提取重复值的一个思路。
楼上adidas315073682小雨点沙沙响两位朋友对$的理解肯定是正确的。
这里用不用$、怎么用都没关系,因为这段话是纯解说词,你就想像在教室里,老师一行一行把它写在黑板上,而不是录入在单元格里,中间所有$符号只是保持原公式的风格。
这里主要是在说“展开”,一个数组公式得到的结果,相当于6个普通公式得到的结果。
如果你把学习的重点放在这里,你就会理解前面许多朋友为什么没有提$这个问题。
excel从有分隔符的字符串中提取关键词及
用指定字符替换有多个相同分隔符字符串中的第几个的方法
需要针对部门进行相关数据分析,但是现有的数据没有规范的可直接使用部门字段,字段“管理渠道”中含有部门信息。
“管理渠道”这个字段中字符串用“.”分隔,需要提取的部门名称信息在字符串中的位置存在两种情况。
一是单独一个部门名称,二是处在逗号分隔的第二段。
分析时增加了一个辅助列“部门”。
通过一个公式将上述两种情况中部门信息提取到辅助字段“部门”中。
1
管理渠道
部门
2
88部门
88部门
3
AA市.88部门
88部门
4
AA市.88部门.IX中心
88部门
5
AA市.88部门.IX中心.00班组
88部门
方法/步骤
如果A2中的逗号达到2个,则取分隔符所分割后的第二个关键词;如果A2中的逗号是1个,则取分隔符所分割后的第二个关键词;其他情况(没有分隔符)则取A2中的字符串。
公式解读
=IF((LEN(A2)-LEN(SUBSTITUTE(A2,".",)))>=2,(MID(A2,SEARCH(".",A2,1)+1,SEARCH(".",A2,SEARCH(".",A2,1)+1)-SEARCH(".",A2,1)-1)),(IF((LEN(A2)-LEN(SUBSTITUTE(A2,".",)))=1,(MID(A2,SEARCH(".",A2,1)+1,LEN(A2)-SEARCH(".",A2,1))),A2)))
分隔符的个数达到2个:
(LEN(A4)-LEN(SUBSTITUTE(A4,".",)))>=2
取第二个关键词的内容:
(MID(A2,SEARCH(".",A2,1)+1,SEARCH(".",A2,SEARCH(".",A2,1)+1)-SEARCH(".",A2,1)-1))
其中SEARCH(".",A2,1)+1是所取关键词的开始位置。
其中SEARCH(".",A2,SEARCH(".",A2,1)+1)-SEARCH(".",A2,1)-1)是第二个关键词的长度。
分隔符的个数是1个:
(LEN(A2)-LEN(SUBSTITUTE(A2,".",)))=1
取第二个字符串的内容:
(MID(A2,SEARCH(".",A2,1)+1,LEN(A2)-SEARCH(".",A2,1)))
其中SEARCH(".",A2,1)+1是所取关键词的开始位置。
其中LEN(A2)-SEARCH(".",A2,1)是第二个关键词的长度。
其他情况(分隔符的个数是0个)时直接用单元格的字符串:
A2
提取特定字符*与#之间字符串的方法:
B1=MID(A1,FIND("8",A1)+1,FIND("#",A1)-FIND("*",A1)-2)
SUBSTITUTE函数编辑
在文本字符串中用new_text替代old_text。
如果需要在某一文本字符串中替换指定的文本,请使用函数SUBSTITUTE;如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数REPLACE。
语法编辑
SUBSTITUTE(text,old_text,new_text,[instance_num])
Text为需要替换其中字符的文本,或对含有文本的单元格的引用。
Old_text为需要替换的旧文本。
New_text用于替换old_text的文本。
Instance_num为一数值,用来指定以new_text替换第几次出现的old_text。
如果指定了instance_num,则只有满足要求的old_text被替换;如果缺省则将用new_text替换TEXT中出现的所有old_text。
示例编辑
如果您将示例复制到空白工作表中,可能会更易于理解该示例。
A
1
数据
2
销售数据
3
2008年第一季度
4
2011年第一季度
公式
说明(结果)
=SUBSTITUTE(A2,"销售","成本")
“成本”替代“销售”(成本数据)
=SUBSTITUTE(A3,"一","二",1)
用“二”代替示例中第一次出现的“一”(2008年第二季度)
=SUBSTITUTE(A4,"1","2",2)
用“2”代替示例中第二次出现的“1”(2012年第一季度)
提取特定字符*与#之间字符串的方法:
B1=MID(A1,FIND("8",A1)+1,FIND("#",A1)-FIND("*",A1)-2)
excel如何提取特定字符前后的字符串
本例介绍如何提取文本字符串中特定字符前或后的内容。
操作步骤:
如下图,要提取#后面的字符,也即红色的“SDK”到B列。
首先,在B2中输入公式:
=FIND("#",A2)
返回#在字符串中的位置,#在A2单元格文本中是第6个字符。
知识点说明:
FIND()函数查找第一参数在第二参数中的位置。
如下图,查找“B”在“ABCD”中是第几个字符。
第一参数是要查找的字符“B”,第二参数是被查找的字符串。
最终返回“B”在“ABCD”中是第2个字符。
1.
109°7'43.3524"
然后,在B2中输入公式:
=MID(A2,FIND("#",A2)+1,99)
这样,就提取出了#后的字符。
知识点说明:
MID()函数返回从字符串中制定字符开始若干个字符的字符串。
如下图,MID()函数返回“ABCDE”字符串中从第2个字符开始的连续3个字符,也就是返回“BCD”。
2.
综上,=MID(A2,FIND("#",A2)+1,99)的意思就是从A2单元格#字符后面的一个字符起,取长度为99的字符串。
其中的99是一个较大的数字,能涵盖#后字符的最大长度即可。
而要提取#前面的字符,可以用公式:
=MID(A2,1,FIND("#",A2)-1)
考勤表中每天下乡人员统计:
=IF(OR(c7="▲",c8="▲"),"梁","")&IF(OR(c9="▲",c10="▲"),"黄","")&IF(OR(c11="▲",c12="▲"),"覃","")&IF(OR(c13="▲",c14="▲"),"刘","")&IF(OR(c15="▲",c16="▲"),"韦","")&IF(OR(c17="▲",c18="▲"),"阮","")&IF(OR(c19="▲",c20="▲"),"陈","")
按顺序排列:
=IF(ISNUMBER(SEARCH("梁",A1)),"梁","")&IF(ISNUMBER(SEARCH("黄",A1)),"黄","")&IF(ISNUMBER(SEARCH("覃",A1)),"覃","")&IF(ISNUMBER(SEARCH("刘",A1)),"刘","")&IF(ISNUMBER(SEARCH("韦",A1)),"韦","")&IF(ISNUMBER(SEARCH("阮",A1)),"阮","")&IF(ISNUMBER(SEARCH("陈",A1)),"陈","")
excel如何判断(统计)某一个单元格内字符串中包含特定字符(串)的个数
假设需要统计A1单元格TJfkFfkFG中字符串“fk”的个数
公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"fk",)))/2
公式中函数LEN是计算字符串中有几个字符;
SUBSTITITE函数是将字符串中的指定字符替换成新的字符,语法结构如下:
SUBSTITUTE(text,old_text,new_text,[instance_num])
SUBSTITUTE函数语法具有下列参数(参数:
为操作、事件、方法、属性、函数或过程提供信息的值。
):
Text必需。
需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
Old_text必需。
需要替换的旧文本。
New_text必需。
用于替换old_text的文本。
Instance_num可选。
用来指定要以new_text替换第几次出现的
old_text。
如果指定了instance_num,则只有满足要求的old_text被替换;否则会将Text中出现的每一处old_text
都更改为new_text
如果new_text为空(或省略)的话,就是将原指定字符用空符代替,也就是将指定字符删除。
整个公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"fk",)))的意思就是原字符串长度减去删除指定字符(串)长度,结果就是特定字符(串)的长度,再除以2(/2)就得字符串fk个数(若要统计包含特定单个字符的个数则不用除以2)。
尊享无限量精美信纸!
立即LOOKUP函数的几种经典用法
篇外话:
对于lookup的计算原理,可以自行XX搜索Excel二分法原理了解,这方面的内容要细说比较复杂,不过要想用好lookup也不是非得掌握这个原理不可,好比很多数学公式,我们都是直接拿来用,但是要去证明或者推导公式,就需要更多的专业理论才行。
当然,如果以后有机会的话,会用比较通俗好理解的言辞来专题讨论二分法了。
本帖目的在于将lookup函数的常用写法进行整理后方便使用者随时查找套用,用的多了自然会达到熟能生巧的效果,更重要的是,lookup的计算效率和功能之强大的确是令人称赞的。
一、逆向查找、多条件查找:
先看例题:
一般这样的查找可以使用vlookup或者index+match这些函数和数组配合使用来实现需要,但是使用lookup的话只要记得查找公式的标准格式,然后不管多少个条件,都可以自己套写公式了。
例一公式为:
=LOOKUP(1,0/(B2:
B9=G2),A2:
A9)
例二公式为:
=LOOKUP(1,0/((A2:
A9=G6)*(C2:
C9=H6)),B2:
B9)
这两个公式就是LOOKUP函数最典型用法。
可以归纳:
=LOOKUP(1,0/(条件),目标区域或数组)
其中,条件可以是多个逻辑判断相乘组成的多条件数组,=LOOKUP(1,0/((条件1)*(条件2)*(条件N)),目标区域或数组)
使用要求:
能够理解条件的写法以及目标区域的写法,记准公式格式,注意条件之间的括号,切不可配错哦。
二、要查询A列中的最后一个文本,也非LOOKUP函数莫属,用到的公式是:
=LOOKUP("々",A:
A)
"々"通常被看做是一个编码较大的字符,它的输入方法为组合键。
如果感觉每次写这个符号有点费事儿,也可以写成:
=LOOKUP("座",A:
A)
一般情况下,第一参数写成“座”也可以返回一列或一行中的最后一个文本。
三、要查询A列中的最后一个数值,用到的公式是:
=LOOKUP(9E307,A:
A)
9E307被认为是接近Excel规范与限制允许键入最大数值的数,用它做查询值,可以返回一列或一行中的最后一个数值。
很多时候9E307也可以9^9取代,也即9个9相乘的结果387420489,不过,如果你的数据中有超过这个数字的,还是用9E307稳妥一些。
四、根据简称查询全称的问题相信大家都会经常遇到吧?
如下面这个图中所示,A列是客户的简称,要求根据E列的客户全称对照表,在C列写出客户的全称。
C2单元格输入以下公式,可得到“上海沛发”的客户全称“上海沛发包装材料有限公司”。
=IFERROR(LOOKUP(1,0/FIND(A2,E$2:
E$13),E$2:
E$13),"")
公式中“0/FIND(A2,E$2:
E$13)”部分,首先用FIND函数查询A2单元格“上海沛发”在E$2:
E$13的起始位置,得到一个由错误值和数值组成的数组。
余下部分的计算过程就和咱们前面说过的一样了,使用IFERROR函数来屏蔽公式查询不到对应结果时返回的错误值。
五、多个区间的条件判断。
例题如图,50分以下的为“很差”,50-59分的为“差”,60-74分的为“一般”,75-85分的为“较好”,86-95分的为“优秀”,96分及以上的为“能手”。
这种多个区间的判断,如果需要判断的条件和区间都很多,如果使用IF函数来计算,估计会把自己都转晕了。
而使用LOOKUP函数来解决,不过是