office excel最常用函數公式技巧搜集大全(13.12.09更新)

導讀：使用公式將零值顯示為空白，還可以使用IF函數來判斷單元格是否為零值，例如公式「=IF(A2-A3=0，個調稅公式，上列公式的簡化式:，算物價的函數，做一個函數，看看函數的幫助就知道了呀，公式=PERCENTILE(A3:A12,G1)，應用公積金的一個函數，給公積金投繳人員製作了一個函數，我製作的函數是「=IF(MOD(INT(A1*B1),2)=0,INT(A1*B，如何利用公式將數值轉為百

零值問題

在工作表中隱藏所有零值

在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列）

隔列求和的公式

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