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

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

導讀:不用增加公式的數目就可以改進工作表的可讀性和效率,如何輸入自定義的數字格式:需要先知道自定義格式中那些常用符號的含意,1、=indirect(“sheet”&row()+,例如在a1輸入該公式,公式裡的值變為indirect(“sheet2!a1”),跟,在a2輸入該公式,公式裡的值變為indirect(“sheet3!a1”

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

13. [紅色][<0];[綠色][>0] (小於0時顯示紅色,大於0時綠色,都以絕對值顯示) 14 [>0]#,##0.00;[<0]#,##0.00;0.00 (會計格式,以絕對值形式顯示)

自定義格式

Excel中預設了很多有用的數據格式,基本能夠滿足使用的要求,但對一些特殊的要求,如強調顯示某些重要數據或信息、設置顯示條件等,就要使用自定義格式功能來完成。 Excel的自定義格式使用下面的通用模型:正數格式,負數格式,零格式,文本格式,在這個通用模型中,包含三個數字段和一個文本段:大於零的數據使用正數格式;小於零的數據使用負數格式;等於零的數據使用零格式;輸入單元格的正文使用文本格式。 我們還可以通過使用條件測試,添加描述文本和使用顏色來擴展自定義格式通用模型的應用。

(1)使用顏色 要在自定義格式的某個段中設置顏色,只需在該段中增加用方括號括住的顏色名或顏色編號。Excel識別的顏色名為:[黑色]、[紅色]、[白色]、[藍色]、[綠色]、[青色]和[洋紅]。Excel也識別按[顏色X]指定的顏色,其中X是1至56之間的數字,代表56種顏色(如圖5)。

(2)添加描述文本 要在輸入數字數據之後自動添加文本,使用自定義格式為:"文本內容"@;要在輸入數字數據之前自動添加文本,使用自定義格式為:@"文本內容"。@符號的位置決定了Excel輸入的數字數據相對於添加文本的位置。

(3)創建條件格式 可以使用六種邏輯符號來設計一個條件格式:>(大於)、>=(大於等於)、<(小於)、<=(小於等於)、=(等於)、<>(不等於),如果你覺得這些符號不好記,就乾脆使用「>」或「>=」號來表示。

由於自定義格式中最多只有3個數字段,Excel規定最多只能在前兩個數字段中包括2個條件測試,滿足某個測試條件的數字使用相應段中指定的格式,其餘數字使用第3段格式。如果僅包含一個條件測試,則要根據不同的情況來具體分析。

自定義格式的通用模型相當於下式:[>;0]正數格式;[<;0]負數格式;零格式;文本格式。

下面給出一個例子:選中一列,然後單擊「格式」菜單中的「單元格」命令,在彈出的對話框中選擇「數字」選項卡,在「分類」列表中選擇「自定義」,然後在「類型」文本框中輸入「”正數:”($#,##0.00);”負數:”($ #,##0.00);”零”;”文本:”@」,單擊「確定」按鈕,完成格式設置。這時如果我們輸入「12」,就會在單元格中顯示「正數:($12.00)」,如果輸入「-0.3」,就會在單元格中顯示「負數:($0.30)」,如果輸入「0」,就會在單元格中顯示「零」,如果輸入文本「this is a book」,就會在單元格中顯示「文本:this is a book」。 如果改變自定義格式的內容,「[紅色]”正數:”($#,##0.00);[藍色]”負數:”($ #,##0.00);[黃色]”零”;”文本:”@」,那麼正數、負數、零將顯示為不同的顏色。如果輸入「[Blue];[Red];[Yellow];[Green]」,那麼正數、負數、零和文本將分別顯示上面的顏色。

再舉一個例子,假設正在進行帳目的結算,想要用藍色顯示結餘超過$50,000的帳目,負數值用紅色顯示在括號中,其餘的值用缺省顏色顯示,可以創建如下的格式: 「[藍色][>50000] $#,##0.00_);[紅色][<0]( $#,##0.00); $#,##0.00_)」 使用條件運算符也可以作為縮放數值的強有力的輔助方式,例如,如果所在單位生產幾種產品,每個產品中只要幾克某化合物,而一天生產幾千個此產品,那麼在編制使用預算時,需要從克轉為千克、噸,這時可以定義下面的格式: 「[>999999]#,##0,,_m”噸””;[>999]##,_k_m”千克”;#_k”克”」 可以看到,使用條件格式,千分符和均勻間隔指示符的組合,不用增加公式的數目就可以改進工作表的可讀性和效率。

另外,我們還可以運用自定義格式來達到隱藏輸入數據的目的,比如格式";##;0"只顯示負數和零,輸入的正數則不顯示;格式「;;;」則隱藏所有的輸入值。 自定義格式只

改變數據的顯示外觀,並不改變數據的值,也就是說不影響數據的計算。靈活運用好自定義格式功能,將會給實際工作帶來很大的方便。

怎樣定義格式

怎樣定義格式表示如00062920020001、00062920020002只輸入001、002

答:格式-單元格-自定義-“00062920020″@-確定

在工具按鈕之間設置分隔線

工具欄中只有不同組的工具按鈕才用分隔線來隔開,如果要在每一個工具按鈕之間設置分隔線該怎麼操作?

答:先按住「Alt」鍵,然後單擊並稍稍往右拖動該工具按鈕,鬆開後在兩個工具按鈕之間就多了一根分隔線了。如果要取消分隔線,只要向左方向稍稍拖動工具按鈕即可。 自定義區域為每一頁的標題

自定義區域為每一頁的標題。

方法:文件-頁面設置-工作表-列印標題-頂端標題行與左頂標題列

這樣就可以每一頁都加上自己想要的標題。

一個單元格內格式問題

如果我做了一個表某一列是表示重量的,數值很多在1————–1524745444444之間的數不等。這些表示重量的數。如果我想次給他們加上單位,但要求是單位是>999999噸,之下>999是千克,其餘的是克。如何辦

答:[>9999]###.00,”噸”;*,*.00″千克”

定制單元格數字顯示格式

定制單元格數字顯示格式,先選擇要定制的單元格或區域,》單擊滑鼠右鍵》單元格格式》選擇?數字?選項》選擇?自定義?》在「類型」中輸入自定義的數字格式。

如何輸入自定義的數字格式:需要先知道自定義格式中那些常用符號的含意,具體可以先不選擇?自定義?,而選擇其它已有分類觀看?示例?,以便得知符號的意義。

比如:先選擇?百分比?然後馬上選擇?自定義?,會發現?類型?中出現?0.00%?,這就是百分比的定義法,把它改成小數位3位的百分比顯示法只要把?0.00%?改成?0.000%?就好了,把它改成紅色的百分比顯示法只要把?0.00%?改成?[紅色]0.00%?就好了。

巧用定位選條件單元格

Excel表格中經常會有一些字段被賦予條件格式。如果要對它們進行修改,那麼首先得選中它們。可是,在工作中,它們經常還是處在連續位置。按」Ctrl」健逐列選取恐怕有點太麻煩。其實,我們可以使用定位功能來迅速查找它們。方法是點擊「編輯—定位」單命令,在彈出的「定位」對話框中,點擊「定位條件」按鈕,在彈出的「定位條件」對話框中,選中「條件格式」單選項成為可選。選擇「相同」則所有被賦予相同條件格式的單元格會被選中。

工作表的標籤的字體和大小可以更改嗎

答:在桌面上點右鍵─內容─外觀,相關的設定都在此更改。

sheet1工作表的A1、A2、A3單元格分別鏈接到sheet2、sheet3、sheet4

解答:

1、=indirect(“sheet”&row()+1&”!a1″)《程香宙的解釋:indirect是把文本變為單元格引用的函數row()是取當前行號。例如在a1輸入該公式,則row()=1,公式裡的值變為indirect(“sheet2!a1”),跟=sheet2!a1同效,在a2輸入該公式,則row()=2,公式裡的值變為indirect(“sheet3!a1”)》

2、使用插入-超級鏈接-書簽-(選擇)-確定

經驗技巧

按「Ctrl+~」可以一次顯示所有公式(而不是計算結果)。再按一次回到計算結果。

隔行用不同顏色顯示,請問如何做

我想將隔行用不同顏色顯示,請問如何做?

條件格式,自定義,公式, … 格式 –> 自動套用格式,選擇你想要的格式,確定。 我現找到了一種方法,即在上下兩單元格格中設計不同顏色,再選中兩單元格,用格式刷刷即可。

條件格式中用公式,

=mod(row()/2,color)

依次類推即可,一次設置兩種、三種、四種等顏色。

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

用條件格式=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就大功告成了。

這裡如果不使用名稱,應該是不行的。

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