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

導讀：方法1、用一列公式填充，什麼函數可以插入字元，答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,&q，C列C1輸入公式C1=A1&B1,，用以下公式：，不用公式的方法最簡單了，2、用公式：，1、你好啊→你好啊要用什麼函數啊?，要用什麼函數或怎樣在自定義裡面設置？，1.在E1單元格中輸入公式：=B1&C1&D1下拉，E列的內容（不是公式）即被複制

方法1、用一列公式填充

=IF(LEFT(RIGHT($A$1,ROW()),1)=CHAR(32),RIGHT($A$1,ROW()-1),「」)

方法2、=MID(A1,FIND(" *",SUBSTITUTE(A1," ","

*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)-FIND(" ",A1))

方法3、=IF(ISERROR(SEARCH("",TRIM(LEFT(B1)))),RIGHT($A$1,ROW()),"")拖出來的第一個字元就行。

方法4、

{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="

")*ROW(INDIRECT("1:"&LEN(A1)))))}

嫌長就（假定最長100字元）

{=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(1:100),1)=" ")*ROW(1:100)))}

如何去掉單元格最後一位數字

說明:單元格前面的數據不能改變，去掉最後一位數字。

=LEFT(A2,LEN(A2)-IF(ISNUMBER(--RIGHT(A2)),LEN(LOOKUP(9E+307,--RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))

如果後面代的數值在兩位以內,也可以用以下方式實現,當然如果超過兩位的話,可以加語句 IF(ISNUMBER(RIGHT(TRIM(A2), X )+0)=TRUE,1,0) X 代表後面數值的個數。 =LEFT(TRIM(A2),LEN(TRIM(A2))-IF(ISNUMBER(RIGHT(TRIM(A2),1)+0)=TRUE,1,0)+IF(ISNUMBER(RIGHT(TRIM(A2),2)+0)=TRUE,1,0))

如何在一列已經輸入的數據前添加「p」

比如一列數據 添加後變

112234 p112234

123435 p123435

124355 p124355

123545 p123545

選中所有單元格，設置格式，自定義-〉在預設的「G/通用格式」前面加上「"p"」（半形的雙引號中間是p）即可

加一列全是「p」，使用&=a1&b1 Shift+7

="P"&A1

在自定義中輸入"Q"#即可,很簡單的。

什麼函數可以插入字元

怎樣用第一列的數據形成第二列的數據，即在特定位置加上幾個相同字元串？

解答： b1="04"& a1

問：哪如果倒過來呢？

答：用公式A1=MID(B1,3,13)或A1=SUBSTITUTE(B1,"04","",1)

如何在數據前添加「*」號

數據如在B列,在A列整列加「*」，C列C1輸入公式C1=A1&B1,

下拉。用「選擇性粘貼」選「值」複製到D列，刪去A、B、C列。

數字前面加上數字

123

0123

75223

比如說上述的數字，我想在它們前面加上38910104，而且位置短的數字，會自動補0 變成如下：

3891010400123

3891010400123

3891010475223）

=TEXT(A1,"3891010400000")

3、查找替換，查找欄輸入：第*班，替換欄空置，全部替換

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

【數據拆分與合併】

數字如何拆分

我有一組數據，如123，59等，假如這些數據均在A列，我現在需要將123或者59這樣的數據拆成到B,C,D列。

B1=MID(TEXT($A1,"000"),COLUMN(A1),1) 往右拖

=MID(REPT(0,3-LEN($A1))&$A1,COLUMN(A1),1)

單元格中的數據拆分

如何將一個單元格中的11位數據拆分11各單元格（每個單元格一個數字）。

如：01234567890 變為：0，1，2，3，4，5，6，7，8，9，0（一個單元格一個數字），文字也同樣。用以下公式：

=MID($A1,COLUMN(A1),1) 向右拖

=MID($A$2,COLUMN()-1,1) 向右拖

=MID($A$2,COLUMN(),1) 向右拖

也可以，選中區域後―數據‖-----分列，―固定列‖-----看到尺寸的時候分別在01234567890兩數之間點擊一下-----完成就行了！

單元格的拆分

一個單元格數據即包含了物品名又包含其規格,兩者之間用"/"來隔開,現想把兩者單獨分開。

如：軸承/SKF 62122R，外六角螺絲/M10*30....."/"前後都無標準長度。

1、用「數據-分列」，不用公式的方法最簡單了 。

2、用公式：

=LEFT(A1,FIND("/",A1)-1)

=RIGHT(A1,FIND("/",A1)-1)

如何拆分字組

如何將一個單元格里的字組拆開來啊，

1、你好啊→你 好 啊 要用什麼函數啊?

=SUBSTITUTE(A1,"好"," 好 ")

2、‖你— 好 — 啊―改成―你好啊‖ 把中間的橫線去掉。要用什麼函數或怎樣在自定義裡面設置？

①用查找替換功能，查找―—‖，替換為空值

②=SUBSTITUTE(A1,"— 好 — ","好")

用連字元「&」來合併文本

將B、C、D列合併。

1.在E1單元格中輸入公式：=B1&C1&D1 下拉

2.選中E列，執行「複製」操作，然後選中F列，

執行「編輯→選擇性粘貼」命令，打開「選擇性粘貼」對話框，選中其中的「數值」選項，按下「確定」按鈕，E列的內容（不是公式）即被複制到F列中。

3.將B、C、D、E列刪除，完成合併工作。

怎樣把不同格式不同位置的內容合併到一個單元格內

如：一個是文本格式一個是日期格式，怎麼合併呢？

今天是： 2007-06-25

＝A1&TEXT(B1,"yyyy-m-d h:mm;@")

把不同單元格的內容合到一個單元格里

比如：A1=好，A2=好，A3=學，A4=習

在A5輸入公式，使A5單元格內容為：好好學習

=TEXT(A1&A2&A3&A4,"")

=CONCATENATE(A1,B1,C1,D1)

合併與拆分

兩列合併成一列如何做

=OFFSET($A$2,TRUNC(ROW(A2)/2,0)-1,MOD(ROW(A2),2))向下拖

如果是一列拆開分成兩列又如何做

=OFFSET($C$2,ROW()*2-4+COLUMN(A:A)-1,)

=INDEX($C:$C,(ROW(1:1))*2+COLUMN(A:A)-1)

=INDEX($C:$C,(ROW(2:2)-1)*2+COLUMN(A:A)-1)向右拖一個再向下拖。

=IF(ROW()>COUNT(A:A),INDEX(B:B,ROW()-COUNT(A:A)),A1)公式下拖。

合併不同單元格的內容

合併不同單元格的內容，可以利用CONCATENATE函數，此函數的作用是將若干文字串合併到一個字串中，具體操作為

=CONCATENATE（B1,C1）

比如，假設在某一河流生態調查工作表中，B2包含「物種」、B3包含「河鱒魚」，B7包含總數45，那麼： 輸入「=CONCATENATE（"本次河流生態調查結果："，B2，""，B3，"為"，B7，"條/公里"）」 計算結果為：本次河流生態調查結果：河鱒魚物種為45條/公里。 關於文字在表格中的組合

C1=A1&B1

求拆解合併公式

若拆解：B1=left(A1,find("/",A1))

C1=right(A1,find("/",A1))

如何把字母和數字分開?

想把A列中如A8中"n.m.1."分解成兩列"n.m."和"1.",有什麼好辦法嗎?可以用什麼公式把字母與數字和符號分開嗎?

=IF(ISERR(FIND(1,A8)),"",RIGHT(A8,LEN(A8)-FIND(1,A8)+1))

把分散在各單元格的內容合在一個單元格中

75 P 128 66 75P12866

=CONCATENATE(A1,B1,C1,D1)

多個工作表的單元格合併計算

=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)

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

【條件自定義格式】

通過條件格式將小計和總計的行設為不同的顏色

答：輸入=RIGHT(RC,1)="計"；設定字體、邊框、圖案；確定。

如何實現這樣的條件格式

有一個excel表單，若當其中一欄數值超過某一值，使整個一行底色為某一顏色（比如紅色），用條件格式不能實現

注意公式為=$A1>100,而不是=A1>100

先選定整行再設置條件格式...

列標"A"用絕對引用, 行標"1"用相對引用, 用"格式刷"刷下去...

為方便和不易出錯起見, 先設置一行的條件格式, 再用格式刷將格式複製到需要的行。