Question to say "I can!"

excel的修炼术-神奇小技巧(2)

2014-10-22

1.多条件求和
一个条件使用函数sumif
两个以上条件就要用sumif的复数形式 sumifs
公式是长了点,其实就是多一个条件而已
sumif和sumifs最大不同在于,sumif的求和对象在后面,sumifs的在前面

2.跨工作表工作簿使用vlookup有什么意义呢?
首先,我们可以在不同的工作表里引用源数据,并避免对源数据工作表里面数据的误删误改;再次,只要我们更新源数据这一张工作表的数据,其他使用vlookup函数的工作表数据都会自动更新。其实好处还有很多,一用就根本停不下来

3.隔行填充
使用格式刷
步骤一:先选定其中的一行数据,填充一个颜色
步骤二:再选定这一行和下一行,也就是选中2行。再选中格式刷
步骤三:从接下来的数据行开始拖拉格式刷到数据表尾,即可
其实原理就是将选定的两行格式,应用到其余的所有行

4.如何分离混排的文字和不规则日期?
如下:
A列
2012.1.1元旦佳节
2012.1.1-3元旦放假
用数组公式:
=LEFT(A1,MATCH(,0/(MID(SUBSTITUTE(A1," ","我"),ROW($1:$99),1)>="吖"),)-1)
分离日期部分,用普通公式
=TRIM(SUBSTITUTE(A1,B1,))
得到文字部分

5.比如A列下面有这些数值
3
9
12
求和的结果是
3
12
24
也就是向上累加

可以用公式
=SUM($A$1:A2)
这就是思路的转变

4.数组公式
=INDEX(取值区域,SMALL(IF((第一个判断条件)*(第二个判断条件),ROW($A$3:$A$1000),1000),ROW(A1)))&""
可以用来作多条件取值。

5.如何判断单元格里是否包含指定文本?
假定对A1单元格进行判断有无“指定文本”,以下任一公式均可:
=if(countif(a1,"*"&"指定文本"&"*")=1,"有","无")
=if(iserror(find("指定文本",a1,1)),"无","有")

6.求某一区域内不重复的数据个数,也就是重复多次出现只算一个。
数组公式:=sum(1/countif(a1:a100,a1:a100))
乘积求和:=sumpoduct(1/countif(a1:a100,a1:a100))

7.巧用函数组合进行多条件的计数统计
用数组公式:=sum(if((b2:b999="条件")*(c2:c999="条件"),1,0)
也可以用乘积求和:=sumpoduct((b2:b999="条件")*(c2:c999="条件"))
注意:在数组和乘积公式中,指定的单元范围必须一致。

8.查找重复内容公式:=if(countif(a:a,a2)>1,"重复","")
用出生年月来计算年龄公式:=trunc((Days360(a2,now()))/360,0)
从输入的18位身份证号提取出生年月的计算公式:=IF(LEN(C2)=15,MID(C2,7,2)&"-"&MID(C2,9,2)&"-"&MID(C2,11,2),MID(C2,7,4)&"-"&MID(C2,11,2)&"-"&MID(C2,13,2))
如果想从身份证号码中提取性别信息,只要输入:=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")

9.个税计算有速算扣除数,它是怎么计算出来的呢?
本级速算扣除额=本级最低应纳税所得额*(本级税率-上一级税率)+上一级速算扣除数

10.excel中怎样批量地处理按行排序
因为要每一行,那么就无法用主关键字和次关键字排序了,假定要排列的数据在a到e列,请在f1单元格中输入公式:
=large($a1:$e1,column(a1))
用填充柄将公式向右向下复制到相应范围。
从小到大排序:=small($a1:$e1,column(a1))

11.excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色?
可以利用条件格式,在条件1,条件2,条件3中各自输入判断的公式,然后各自选择不同的颜色格式。

12.用vlookup函数近似查找模式去查找
对多数值阶段的条件取值,如果用if函数来求,实在是太苦逼了,vlookup函数就太无敌了。
也就是VLOOKUP(查找值,区域,列序号,逻辑值)中的逻辑值为1



作者:admin | Categories:办公能手 | Tags:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

无觅相关文章插件,快速提升流量