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

  導讀:確定後利用填充柄將該公式進行復制即可,設置數據有效性:自定義>公式:「=LEN(A1)=12」,條件格式:公式為=MOD(ROW(),2)=0,本公式核心在於GET.DOCUMENT,這是4.0宏函數,OFFICE97及以前,本公式中取64,用於計算當前行與分頁符之前後關係.GET.DOCUMENT(64,請看公式求值之計算圖示)做比較,則取IF函數第三參數值即,本公式結果根隨變化,起公

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

式:「=IF(LEN(B2)>=3,B2,(MID(B2,1,1)&&” “&&MID(B2,2,1)))」,確定後利用填充柄將該公式進行復制即可。

數字居中而小數點又對齊

可在小數點的任一邊替無效的零加入空間,以便當格式設定為固定寬字型 ,小數點可以對齊。

格式-單元格-數字-自定義-???.???-確定

請問:小數點後的「0」還有辦法顯示嗎?比如:

2.0

12.001

格式-單元格-數字-自定義-???.0?-確定

計算指定單元格編號組中非空單元格的數量

計算B252到B262之間的非空單元格的數量。

=COUNTA(B252:B262)

比較兩個單元格內容是否一致

74P125148 74P125148

比較單元格B53與C53中的內容是否一致。

假如內容一致,那麼返回值為TRUE,不一致的話,返回值為FALSE。

=EXACT(B53,C53)

結果:TRUE

怎麼樣設置才能讓這一列的每個單元格只能輸入12位

怎麼樣設置才能讓某一列或某一行的每個單元格只能輸入12位,(阿拉伯數字和26個英文字母在內,沒有中文。)

選中A列,設置數據有效性:自定義>公式:「=LEN(A1)=12」

如何讓工作表奇數行背景是紅色偶數行背景是藍色

用條件格式

=ROW()/2=INT(ROW()/2) 設定顏色

條件格式: 公式為 =MOD(ROW(),2)=0

計算特定的一組單元格中,滿足條件的單元格的個數

仍以上題為例,計算三個人在B307到B313中各自所占的單元格數。

李六的: =COUNTIF(B307:B313,B323)

王武的: =COUNTIF(B307:B313,C323)

陳豐的: =COUNTIF(B307:B313,D323)

姓名: 李六 王武 陳豐

結果: 3 2 2

把文本格式的數字轉換成真正的數字

=VALUE(B1)

設置頁碼

如何設置「第×頁,共×頁」頁碼。

在頁腳中設置:第&[頁碼]頁,共&[總頁碼]頁 即可

Excel表格裡如何插入頁碼的?

我想把表格中的第1頁的頁碼從第30頁開始編,不知道該如何做到,哪位高手能幫忙? 在頁面設置的頁眉頁腳中設置。

在插入頁腳中輸入&[頁碼]+29即可。

如何設置頁腳首頁為第5頁

Excel頁腳設置頁碼是按順序來的,首頁為第1頁。如何設置首頁為第5頁?

在頁腳輸入「第 &[頁碼]+4 頁」,結果本該顯示「第1頁」的就顯示第5頁了。(用於多個工作表全選)

頁面設置—頁面—起始頁碼輸入5(用於單個工作表)。

表格的頁腳問題

是這樣的,我每個表格有4張,總共一個文件裡面有6個表格,相當於總共24頁,我希望它能夠自動打,而且我想設置頁腳為,共24頁,第?頁,怎麼辦?

試一試選擇所有的工作表(工作組)然後再設置頁腳,列印的時候也是用工作組列印。 把所有工作表選中就可以了然後你再點列印,或者你先瀏覽,再設置也行!

按shift依次點表單的標籤。

其實,就是在選擇瀏覽或者列印前,先選中你想要的工作表,然後再一個個的瀏覽,就相當於你的操作對所有工作表都已經起了作用似的。

請樓主試一試,按以下步驟辦:

1.文件→頁面設置→頁眉/頁腳→頁腳(F),選自己需要的頁腳格式

2.文件→列印→整個工作簿。

無拘無束的頁眉

頁眉和頁腳大家都用過吧?用得最多的莫過於當前第幾頁/總共第幾頁。但你是否想過將「第N頁/總M頁」無拘無束的放置,而不是只能置於頁眉頁腳中?,現教你一法,可以通用。到任何地方均可使用。

首先:點CTRL+F3打開定義名稱,再在上面輸入「縱向當前頁」,在下面引用位置處輸入=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1,MATCH(ROW(),GET.DOCUMENT(

64))+1)。然後再繼續添加第二個名稱:「橫向當前頁」,在下面引用位置處輸入

=IF(ISNA(MATCH(column(),GET.DOCUMENT(65))),1,MATCH(column(),GET.DOCUMENT(65))+1)。再輸入「總頁」;引用位置處輸入:=GET.DOCUMENT(50)+RAND()*0。最後再定義「無拘無束的頁眉」;引用位置:=”第”&IF(橫向當前頁=1,縱向當前頁,橫向當前頁+縱向當前頁)&”頁/共”&總頁&”頁”。

現在你在工作表任何處輸入=無拘無束的頁眉即可。

本公式核心在於GET.DOCUMENT,這是4.0宏函數,OFFICE 97及以前版專用,新版OFFICE中仍兼容,但只限定義名稱中使用。

在幫助中說(64和65為其參數):64 行數的數組,相應於手動或自動生成頁中斷下面的行。65 列數的數組。相應於手動或自動生成的頁中斷右邊的列。”

本公式中取64,用於計算當前行與分頁符之前後關係.GET.DOCUMENT(64)即返回分頁符所在行下一行之行號(亦即第二頁第一行)。

判斷當前行是否大於分頁符所在行

「=IF(ISNA(MATCH(ROW(),GET.DOCUMENT(64))),1」此句利用MATCH之模糊查找功能將當前行號與分頁符下行(分頁符下一行是一個單元N行的一維數組,文檔有幾頁則有幾行,本實例文檔有三頁,請看公式求值之計算圖示)做比較,此處省略MATCH第三參數,即查找小於等於目標值,如果目標值大於當前行號,則MATCH返回錯誤值。那麼此處再用IF(ISNA(),1)加以判斷,即若找不到小於等於當前行號的值則顯示1,表示當前行處於第一頁。

取得當前行所在頁

=MATCH(ROW(),GET.DOCUMENT(64))+1

如果前一個MATCH返回FALSE,則取IF函數第三參數值即

MATCH(ROW(),GET.DOCUMENT(64))+1

此參數再用MATCH在GET.DOCUMENT(64)產生的數組中查找當小於等於前行號的數值,若數組中第N個值小於等於當前行號,則當前行在N+1頁。

取得總頁

=GET.DOCUMENT(50)+RAND()*0

GET.DOCUMENT(50)即求當前設置下欲列印的總頁數,其中包括註釋,如果文件為圖表,值為1

RAND()*0作用是當文件分頁數改變時,本公式結果根隨變化,起公式結果刷新作用。 獲取「橫向當前頁」

橫向當前頁與縱向當前頁原理相同,改ROW()為COLUMN(),並將GET.DOCUMENT參數改為65即可

若你的工作表只有縱向分頁或者橫向分頁,那麼現在就可以使用前面的公式定義的名稱獲取當前頁及總頁了;但如果分頁方式為橫向多頁縱向也多頁呢?則在將以上「橫向當前頁」與「縱向當前頁」無縫接合方可使用,否則將返回錯誤結果。

最後生成「無拘無束的頁眉」(或者改稱文件分頁)

=”第”&IF(橫向當前頁=1,縱向當前頁,橫向當前頁+縱向當前頁)&”頁/共”&總頁&”頁”

公式解說完畢!各位可以用不同的文字定義名稱在各自的工作表中試用了。

列印表頭

在Excel中如何做到一個表頭列印在多頁上?

請選擇文件-頁面設置-工作表-列印標題-頂端標題行,然後選擇你要列印的行。

列印表尾,通過Excel直接提供的功能應該是無法做到的,需要用vba編制才行。

Excel列印中如何不顯示錯誤值符號

在「頁面設置」-「工作表」-「錯誤單元格列印為」中,

將「顯示值」改為「空白」即可。

對於一些不可列印的字符的處理

對於一些不可列印的字符(在Excel顯示中類似空格),直接用替換方法不容易去掉。 可以這麼做:

=SUBSTITUTE(CLEAN(A1),” “,””)

用那個函數可將個位數前面的零值顯示出來?

如果單元格A1的內容是5,在A2用那個函數可將A1的內容變為05?

(Text或value也可,總之個位數的零也顯示,例:5變05,15則15)

可以用=TEXT(A2,”00″)

可能將單元格格式自定義為00

如果你要在A3的前面插入100行

可以這樣:在名稱框輸入 3:103-回車-ctrl+shift+”+”(大鍵盤)

請問如何每隔30行黏貼一新行

偶在班上負責統計企業進出口業務量,主管要求每30家做一合計數,偶只有每隔30行插入復制單元格的方法來添加的,很是麻煩,請教各位大蝦有什麼快捷的方法呀

在最後加一輔助列,輸入=INT((ROW()-1)/31)+1 (假設一個標題行)

然後以該行分類字段匯總.

在工作表裡有連續10行數據, 現在要每行間格2行

解答:1:如sheet1!$A$1:$D$10中有連續10行資料,在sheet2中把sheet1中的數據每行間隔2行 ,sheet2!A1中公式可用:

=IF(ROW()=1,Sheet1!A1,IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$D$10,INT((ROW()-1)/

2)+1,COLUMN()),””))

然後填充公式(注意公式在SHEET2中的填充範圍,超過範圍會出錯!)

2:小修改

=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN()),””) 一個大表每一行下面需要加一行空行,怎麼加最方便

方法一:增加輔助列,填充數據排序完成

方法二:增加輔助列,函數完成

=IF(MOD(ROW(),2),INDIRECT(“a”&ROUNDUP(ROW()/2,0)),””)

Excel中插入空白行

如果想在某一行上面插入幾行空白行,可以用滑鼠拖動自此行開始選擇相應的行數,然後單擊右鍵,選擇插入。如果在每一行上面均插入一空白行,按住Ctrl鍵,依次單擊要插入新行的行標按鈕,單擊右鍵,選擇插入即可。

快速刪除工作表中的空行

如果用戶想刪除Excel工作表中的空行,一般的方法是需要將空行都找出來,然後逐行刪除,但這樣做操作量非常大,很不方便。下面提供二種快速刪除工作表中的空行的方法:

1、首先打開要刪除空行的工作表,在打開的工作表中單擊「插入→列」命令,從而插入一新的列X,在X列中順序填入整數,然後根據其他任何一列將表中的行排序,使所有空行都集中到表的底部。刪去所有空行中X列的數據,以X列重新排序,然後刪去X列。

2、如批量刪除空行,我們可以利用「自動篩選」功能,把空行全部找到,然後一次性刪除。 做法:先在表中插入新的一個空行,然後按下Ctrl+A鍵,選擇整個工作表,用滑鼠單擊「數據」菜單,選擇「篩選」項中的「自動篩選」命令。這時在每一列的頂部,都出現一