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:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 广东26选5 青铜步行者什么意思 手机免费麻将 酷犬酒店 国王vs湖人推荐 北京pk10 预测过海3d今天预测直 Av作品番号网 新疆11选5开奖直 亚洲人成在线播放网站 163足球即时比分网 福彩大乐透开奖结果查询玩法 西安小姐妹 德科钻石 银川站街女爽记 上海时时乐走势图