EXCEL常用函數公式及技巧搜集之五

EXCEL常用函數公式及技巧搜集之五

將單元格設置為有“凸出”的效果或“凹進去”的效果

用條件格式=mod(row(),2)=mod(column(),2)

方法是設定單元格的邊框

3樓的辦法不錯，但是要一個格一個格地設定，數據多了很麻煩

2樓的格式里設公式能不能搞成隔一行ao隔一行tu的形式呢？

格式—自動套用格式里就有。

湊個熱鬧。邊框用黑白的就可以了

看來還是用條件格式更方便些！

用黑白雙線邊框是最簡單的辦法

在Excel中設計彩色數字

　　用戶在使用Excel處理數據時，經常需要將某些數據以特殊的形式顯示出來，這樣可以起到醒目的作用，使瀏覽者一目了然。如在某用戶的Excel單元格中有“月工資”一欄，需要小于500的顯示為綠色，大于500的顯示為紅色，則可以采用以下的方法來操作：選中需要進行彩色設置的單元格區域，選擇“格式”→“單元格”，在彈出的對話框中單擊“數字”選項卡。然后選擇“分類”列表中的“自定義”選項，在“類型”框中輸入“［綠色］［＜500；［紅色］［＞＝500］”，最后單擊“確定”按鈕即可。

　　小提示

　　除了紅色和綠色外，用戶還可以使用六種顏色，它們分別是黑色、青色、藍色、洋紅、白色和黃色。另外，“［＞＝120］”是條件設置，用戶可用的條件運算符有：“＞”、“＜”、“＞＝”、“＜＝”、“＝”、“＜＞”。當有多個條件設置時，各條件設置以分號“；”作為間隔。

定義名稱的妙處

名稱的定義是EXCEL的一基礎的技能，可是，如果你掌握了，它將給你帶來非常實惠的妙處！

1. 如何定義名稱

插入-名稱-定義

2. 定義名稱

建議使用簡單易記的名稱，不可使用類似A1…的名稱，因為它會和單元格的引用混淆。還有很多無效的名稱，系統會自動提示你。

引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。

在引用工作表單元格或者公式的時候，絕對引用和相對引用是有很大區別的，注意體會他們的區別 – 和在工作表中直接使用公式時的引用道理是一樣的。

3. 定義名稱的妙處1 – 減少輸入的工作量

如果你在一個文檔中要輸入很多相同的文本，建議使用名稱。例如：定義DATA = “I LOVE YOU， EXCEL！”，你在任何單元格中輸入“=DATA”，都會顯示“I LOVE YOU， EXCEL！”

4. 定義名稱的妙處2 – 在一個公式中出現多次相同的字段

例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),””, IF(A1>B1,A1/B1,A1))，這里你就可以將IF(A1>B1,A1/B1,A1)定義成名稱“A_B”，你的公式便簡化為=IF(ISERROR(A_B),””,A_B)

5. 定義名稱的妙處3 – 超出某些公式的嵌套

例如IF函數的嵌套最多為七重，這時定義為多個名稱就可以解決問題了。也許有人要說，使用輔助單元格也可以。當然可以，不過輔助單元格要防止被無意間被刪除。

6. 定義名稱的妙處4 – 字符數超過一個單元格允許的最大量

名稱的引用位置中的字符最大允許量也是有限制的，你可以分割為兩個或多個名稱。同上所述，輔助單元格也可以解決此問題，不過不如名稱方便。

7. 定義名稱的妙處5 – 某些EXCEL函數只能在名稱中使用

例如由公式計算結果的函數，在A1中輸入’=1+2+3，然后定義名稱 RESULT = EVALUATE(Sheet1!$A1)，最后你在B1中寫入=RESULT，B1就會顯示6了。

還有GET.CELL函數也只能在名稱中使用，請參考相關資料。

8. 定義名稱的妙處6 – 圖片的自動更新連接

例如你想要在一周內每天有不同的圖片出現在你的文檔中，具體做法是：

8.1 找7張圖片分別放在SHEET1 A1至A7單元格中，調整單元格和圖片大小，使之恰好合適

8.2 定義名稱MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)

8.3 控件工具箱 – 文字框，在編輯欄中將EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。

這里如果不使用名稱，應該是不行的。

此外，名稱和其他，例如數據有效性的聯合使用，會有更多意想不到的結果。

零值問題

在工作表中隱藏所有零值

　　在Excel默認情況下，零值將顯示為0，這個值是一個比較特殊的數值。如果工作表中包含了大量的零值，會使整個工作表顯得十分凌亂。如果要隱藏工作表中所有的零值，可以這樣操作：選擇“工具”→“選項”，打開“選項”對話框，單擊“視圖”標簽，在“窗口選項”里把“零值”復選框前面的對號去掉，單擊“確定”按鈕。此時，可以看到原來顯示有0的單元格全部變成了空白單元格。

　　小提示

　　若要在單元格里重新顯示0，用上述方法把“零值”復選框前面的打上對號即可。

　　隱藏部分零值

　　有些時候可能需要有選擇地隱藏部分零值，使隱藏的零值只會出現在編輯欄或正在編輯的單元格中，而不會被打印，這時候就要通過設置自定義數字格式來實現：先按住Ctrl鍵用鼠標左鍵一一選定需要隱藏零值的單元格，然后選擇“格式”→“單元格”，在“單元格格式”對話框選擇“數字”選項卡，在“分類”列表框中選擇“自定義”選項，然后在右邊的“類型”文本框中輸入“0；_0；；@”，單擊“確定”按鈕。

要將隱藏的零值重新顯示出來，可選定單元格，然后在“單元格格式”對話框的“數字”選項卡中，單擊“分類”列表中的“常規”選項，這樣就可以應用默認的格式，隱藏的零值就會顯示出來。

　　條件隱藏零值

　　利用條件格式也可以實現有選擇地隱藏部分零值：首先選中包含零值的單元格，選擇“格式”→“條件格式”，在“條件1”的第一個框中選擇“單元格數值”，第二個框中選擇“等于”，在第三個框中輸入0，然后單擊“格式”按鈕，設置“字體”的顏色為“白色”即可。

　　如果要顯示出隱藏的零值，請先選中隱藏零值的單元格，然后選擇“格式”菜單中“條件格式”，單擊“刪除”按鈕，在彈出的“選定要刪除的條件”對話框中選擇“條件1”即可。

　　使用公式將零值顯示為空白

　　還可以使用IF函數來判斷單元格是否為零值，如果是的話就返回空白單元格，例如公式“=IF(A2-A3=0，""，A2-A3)”，如果A2等于A3，那么它們相減的值為零，則返回一個空白單元格；如果A2不等于A3，則返回它們相減的差值。

㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜

【匯總計算與統計】

個調稅公式

=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}

-{0,0,25,125,375,1375,3375,6375,10375,15375})

{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 為稅率

{0,0,25,125,375,1375,3375,6375,10375,15375}  為稅收扣除數

上列公式的簡化式 :

=MAX(應納稅所得額*0.05*{1,2,3,4,5,6,7,8,9}

-25*{0,1,5,15,55,135,255,415,615},0)

算物價的函數

物價的那個三七作五，二舍八入的尾數處理，做一個函數。就是小數點后面第二位如果是1，2的就舍掉，如果是3，4，5，6，7的都變為5，如果是8，9的小數點第一位加1，第二位就變為0。比如價格是3.32、3.31，作尾數處理就是3.3；價格是3.33、3.34、3.36、3.37，做尾數處理就是3.35；價格是3.38、3.39，做尾數處理就是3.4。

=CEILING(A1-0.02,0.05)

都是二位小數　B2=ROUND(2*A2,1)/2

超過二位小數　B2=ROUND(2*ROUNDDOWN(A2,2),1)/2

自動計算應收款滯納金

要求在給定的應收日期、滯納金率、當前日期（自動取）的基礎上自動計算出應收滯納金。

解答：=(DATEDIF(應收日期,NOW(),"d"))*滯納金率（每天）*應收金額

淘汰率

題目如下：這個工廠有1000人，今天抽出十人來做調查，這十人一天的產量分別為101 102 105 106 98 95 96 104 110 103 （A3-A12）。

1000人當中淘汰率為5%，以這十人為標準那么這1000人他們的生產應該為多少才不會被淘汰，看看函數的幫助就知道了呀，返回數組K百分比值點，你要1000人淘汰5人就是5/1000=0.5%=0.005，就是你以這10個抽樣調查的數據為基準，只要產量達到這個數就不會被淘汰了。（95.45）

公式=PERCENTILE(A3:A12,G1)

應用公積金的一個函數

我公司職工公積金比例為26％ 也就是個人和單位各13％，給公積金投繳人員制作了一個函數。直接用基數乘以比例 基數*比例＝投繳額， 對于投繳額的要求是：取最接近“投繳額”的偶數。

我制作的函數是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))”

注：A1=基數  B1＝投繳比例

    也可以改成這樣

=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1)+1)

或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))

如何利用公式將數值轉為百分比格式

如用公式將1.289675顯示為128.97%，不是用格式來達到的。

公式＝ROUND(B1*100,1)&"%"

比高得分公式

=RANK(B4,$B$4:$B$26,1)

自動評定獎級

=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)

=LOOKUP(L179,{0,4,7,12,24},{"一等獎","二等獎","三等獎","紀念獎","紀念獎"})

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

對帶有單位的數據如何進行求和

在數據后必須加入單位，到最后還要統計總和，請問該如何自動求和？（例如：A1：2KG，A2：6KG.....，在最后一行自動計算出總KG數）。

=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”

對a列動態求和

可以隨著a列數據的增加，在“b1”單元格=sum(x)對a列動態求和。 

=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))

動態求和公式

自A列A1單元格到當前行前面一行的單元格求和。

=SUM(INDIRECT("A1:A"&ROW()-1))

列的跳躍求和

若有20列（只有一行），需沒間隔3列求和，該公式如何做？

假設a1至t1為數據（共有20列），在任意單元格中輸入公式：=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

按ctrl＋shift＋enter結束即可求出每隔三行之和。

跳行設置：如有12行，需每隔3行求和

=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))

有規律的隔行求和

要求就是在計劃、實際、差異三項中對后面的12個月求和。

=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3)+0,3)=0),F3:AO3)

=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)

=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)

也可以拖動填充，插入行、列也不影響計算結果。

如何實現奇數行或偶數行求和

假設數據在A1:A100

奇數行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)

偶數行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))

奇數行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))

偶數行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))

單數行求和

隔行求和用什么函數，即：A1+A3+A5+A7+A9…公式如何用。

{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}

{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}

統計偶數單元格合計數值

統計F4到F62的偶數單元格合計數值 。｛=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))｝

隔行求和公式設置

均為數組公式：

=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))

=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))

=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))

=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))

=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)

隔列將相同項目進行求和

隔列將出勤日和工資分別進行求和

數組公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))

或；

=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)

隔行或隔列加總

隔2列加總

=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)

隔2欄加總

=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)

請問如何在一百行內做隔行相加

數組公式

A1+A3+……+A99    單

=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))

A2+A4+……+A100   雙

=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))

如何將間隔一定的列的數據相加呢

碰到100多列的數據將間隔一定的數據用手工相加太煩了，也容易出錯。如果需要相加的數據均有相同的名稱（字段），可以用Sumif()來求解，如果沒有，就需要用數組公式來解決了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)}    1、4、7……行相加。

隔列求和（A、B列）

=SUM(A:A,B:B)

=SUM(A:A,B:B,C:C)   （統計A、B、C列）

隔列求和的公式

=SUMIF($B$2:$K$2,"進",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3)

=SUM(SUMIF(B$2:K$2,{"進","出"},B3:K3)*{1,-1})

隔列求和

庫存合計=SUMIF($D$3:$BS$3,"庫存",$D$4:$BT$4),
銷售合計=SUMIF($D$3:$BS$3,"銷售",$D$4:$BT$4)

=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)

=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)

關于隔行、隔列求和的問題

隔2列加總

=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)   

隔2行加總

=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)

均為數組公式。

EXCEL中求兩列的對應元素乘積之和

如:a1*b1+a2*b2+b3*b3...的和

=SUM(A1:A3*B1:B3) (數組公式)

=SUMPRODUCT(A1:A10,B1:B10)

計算900~1000之間的數值之和

sumif函數的計算格式為: =sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大于1000的數值的和,但如果想計算900~1000之間的數值之和,應該如何編寫。

請參考：{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}或{=SUM((900<1000)*A1:A20)}

2、=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")

雙條件求和

1、 求一班女生的個數 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""女""))

2、求一班成績的和 :

=SUMIF(A2:A9,1,C2:C9) "                             

3、求一班男生成績的和 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""男""),C2:C9) "             

如何實現這樣的條件求和

求型號中含BC但不含ABC的量:

=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$B$2:$B$12)

=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)

A1:A10數字顯為文本格式時，如何求和

=SUMPRODUCT(A1:A10)

求和

所有本范例所使用的數據都為引用以下綠色區域，并定義為對應的標題 。         

A   求所有演員工資總額                          

71700    =SUM($G$7:$G$16)

簡單求和

B   求男演員工資總額  

42200    =SUMIF($D$7:$D$16,"男",$G$7:$G$16)

       單條件求和.1

C   求年齡在20歲以下的演員工資 

22500    =SUMIF($E$7:$E$16,"<20",$G$7:$G$16)

       單條件求和.2

D   求主角和配角的工資(不是普通演員)

67500    =SUMIF($F$7:$F$16,"*角",$G$7:$G$16)

       單條件求和.3

E    求20歲以下女演員工資      

9500      {=SUM(($D$7:$D$16="女")*($E$7:$E$16<20)*$G$7:$G$16)}

       多條件求和-同時滿足條件

F    求男性或主角的工資     

59200    {=SUM(IF(($D$7:$D$16="男")+($F$7:$F$16="主角"),$G$7:$G$16))}

       多條件求和-只須滿足條件之一

G   求男性非主角或主角非男性的工資(即除男主角外的男性和主角) 

g.1  21200    {=SUM(IF(($D$7:$D$16="男")-($F$7:$F$16="主角"),$G$7:$G$16))}

g.2  21200    {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16="男"),$G$7:$G$16))}

       多條件求和-只滿足條件之一而不能同時滿足

H    啊~~~你不知道什么是數組函數啊，可是你有時候也要用多條件求和？  

       不要緊，教你用另外的方法：SUBTOTAL   

       求20歲以下女演員工資      

71700    =SUBTOTAL(9,$G$7:$G$16)

       現在你看到的還不是最后結果，請按如下操作   

       1、把數據區域設置成可篩選     

       2、把SEX篩選成"=女", 把年齡篩選成<20  

       3、你再看上面的公式結果…