Excel的公式技巧

2024-08-23 19:48:14

字體：大中小

來源：轉載

供稿：網友

1.公式技巧

1.1 在單元格中顯示工作表和工作簿的名稱

在單元格中顯示工作表的名稱，有兩種方法：

（1）建立如下自定義函數：

Function bookname()

bookname = ActiveSheet.Name

End Function

使用時在單元格中輸入公式：=bookname()，即可返回當前工作簿的標簽名字。

（2）自定義名稱的方法。定義如下名稱：

點擊[插入]à[名稱]à[定義]，名稱的定義為“T_B”，引用位置輸入：“=replace(get.document(1),1, find("]",get.document(1)),)&t(now())”，在單元格輸入“=T_B”就可以顯示當前表名。值得注意的是，返回的工作表名稱隨著工作表名稱的變化而變化。在此引用中，GET.DOCUMENT()是宏表函數，當數據變動時無法自動計算，now()是易失性函數，任何變動都會強制計算，宏表函數所以加上now()就可以自動重算了，T()用來將now()產生的數值轉化為空文本。

在單元格中顯示工作簿的名稱，使用系統函數Cell()：

在單元格中輸入公式：=Cell("filename") ，就會返回該工作簿和工作表的名字（包括絕對路徑名），然后根據自己的需要運用一些文本處理函數進行處理即可。

注意：該函數必須在工作簿已經保存的情況下才生效。

1.2 簡單判斷單元格最后一位是數字還是字母

在有些情況下，需要判斷單元格的最后一位是數字還是字母，可以用下面三個公式之一：

（2）=IF(ISNUMBER(--RIGHT(A1,1)),"數字","字母")，直接返回數字或字母。其中“--”的含義是將文本型數字轉化為數值以便參與運算。

（3）=IF(ISERR(RIGHT(A1)*1),"字母","數字")，直接返回數字或字母。

1.3 如何求出一個人到某指定日期的周歲？

=DATEDIF(起始日期,結束日期,"Y")

1.4 判斷單元格中存在特定字符

假如判斷A欄里是否存在"$"字符，有則等于1，沒有則等于0，公式為：

=IF(COUNTIF(A:A,"*$*")>0,1,0)。

1.5 計算某單元格所在的列數

通常情況下，A列為第1列，AA列為27列。可以在A1單元格中輸入列標，通過下列公式計算出任何列標的列數：

=COLUMN(INDIRECT(A1&"1"))。例如：“FG”列為第163列。

1.6 DATEDIF函數的作用

DATEDIF函數計算兩個日期之間的天數、月數或年數。提供此函數是為了與 Lotus 1-2-3 兼容。

語法：DATEDIF(start_date,end_date,unit)

Start_date 為一個日期，它代表時間段內的第一個日期或起始日期。日期有多種輸入方法：帶引號的文本串（例如 "2001/1/30"）、系列數（例如，如果使用 1900 日期系統則 36921 代表 2001 年 1 月 30 日）或其他公式或函數的結果（例如，DATEVALUE("2001/1/30")）。

End_date 為一個日期，它代表時間段內的最后一個日期或結束日期。

Unit 為所需信息的返回類型。

Unit返回"Y"時間段中的整年數。"M"時間段中的整月數。"D"時間段中的天數。"MD"start_date 與 end_date 日期中天數的差。忽略日期中的月和年。"YM"start_date 與 end_date 日期中月數的差。忽略日期中的日和年。"YD"start_date 與 end_date 日期中天數的差。忽略日期中的年。

說明：Microsoft Excel 按順序的系列數保存日期，這樣就可以對其進行計算。如果工作簿使用 1900 日期系統，則 Excel 會將 1900 年 1 月 1 日保存為系列數 1。而如果工作簿使用 1904 日期系統，則 Excel 會將 1904 年 1 月 1 日保存為系列數 0，（而將 1904 年 1 月 2 日保存為系列數 1）。例如，在 1900 日期系統中 Excel 將 1998 年 1 月 1 日保存為系列數 35796，因為該日期距離 1900 年 1 月 1 日為 35795 天。請查閱 Microsoft Excel 如何存儲日期和時間。

Excel for Windows 和 Excel for Macintosh 使用不同的默認日期系統。有關詳細信息，請參閱 NOW。示例

DATEDIF("2001/1/1","2003/1/1","Y") 等于 2，即時間段中有兩個整年。

DATEDIF("2001/6/1","2002/8/15","D") 等于 440，即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之間有 440 天。

DATEDIF("2001/6/1","2002/8/15","YD") 等于 75，即在 6 月 1 日與 8 月 15 日之間有 75 天，忽略日期中的年。

DATEDIF("2001/6/1","2002/8/15","MD") 等于 14，即開始日期 1 和結束日期 15 之間的差，忽略日期中的年和月。

1.7 在一個單元格中指定字符出現的次數

例如在A1單元格中有“abcabca”字符串，求“a”在單元格A1內出現次數，用下列公式：

=LEN(A1)-LEN(SUBSTITUTE(A1, "a", ""))。

1.8 日期形式的轉換

我們在有些情況下寫日期會用“20060404”表示，如何轉換成“2006-04-04”的標準日期格式，用下面的兩個公式之一（假定在A1單元格中有原始日期）：

=TEXT(A1,"0000-00-00")

=TEXT(A1,"????-??-??")。

也可以使用以下公式，轉換成“2006-4-4”的格式。

=LEFT(A1,4)&SUBSTITUTE(RIGHT(A1,4),0,"-")。

反之，如何把“2006年4月4日”轉換成“20060404”？可以利用下面的公式之一（假定在A1單元格中有原始日期）：

=YEAR(A1)&TEXT(MONTH(A1),"00")&TEXT(DAY(A1),"00" )

=YEAR(A1)&IF(MONTH(A1)<10,"0"&MONTH(A1),MONTH(A1))&IF(DAY(DAY(A1)<10),"0"&DAY(A1),DAY(A1))

=TEXT(A1,"yyyymmdd")。

也可以直接自定義格式：yyyymmdd。

1.9 用“定義名稱”的方法突破IF函數的嵌套限制

Excel中的IF()函數的一個眾所周知的限制是嵌套不能超過7層。例如下面的公式是錯誤的，因為嵌套層數超過了限制。

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))

通常的方法會考慮用VBA代替。但是也可以可以通過對公式的一部分”定義名稱”來解決這種限制定義一個名叫”OneToSix”的名稱, 里面包括公式：

=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

再定義另一個名叫”SevenToThirteen”的名稱，里面包括公式：

=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,IF(Sheet1!$A$4=13,130,"NotFound")))))))

最后單元格中輸入下面的公式：

=IF(OneToSix,OneToSix,SevenToThirteen)

1.10 動態求和

舉一個簡單例子：例如對于A列，求出A1到當前單元格行標前面一行的單元格中的數值之和，更直接地說，如果當前單元格在B17，那么求A1:A16之和。利用下面的公式：

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

1.11 COUNTIF函數的16種公式設置（設DATA為區域名稱）

（1）返加包含值12的單元格數量：=COUNTIF(DATA,12)

（2）返回包含負值的單元格數量：=COUNTIF(DATA,"<0")

（3）返回不等于0的單元格數量：=COUNTIF(DATA,"<>0")

（4）返回大于5的單元格數量：=COUNTIF(DATA,">5")

（5）返回等于單元格A1中內容的單元格數量：=COUNTIF(DATA,A1)

（6）返回大于單元格A1中內容的單元格數量：=COUNTIF(DATA,“>”&A1)

（7）返回包含文本內容的單元格數量：=COUNTIF(DATA,“*”)

（8）返回包含三個字符內容的單元格數量：=COUNITF(DATA,“???”)

（9）返回包含單詞"GOOD"(不分大小寫)內容的單元格數量：=COUNTIF(DATA,“GOOD”)

（10）返回在文本中任何位置包含單詞"GOOD"字符內容的單元格數量：=COUNTIF(DATA,“*GOOD*”)

（11）返回包含以單詞"AB"(不分大小寫)開頭內容的單元格數量：=COUNTIF(DATA,“AB*”)

（12）返回包含當前日期的單元格數量：=COUNTIF(DATA,TODAY()）

（13）返回大于平均值的單元格數量：=COUNTIF(DATA,">"&AVERAGE(DATA))

（14）返回平均值上面超過三個標準誤差的值的單元格數量：=COUNTIF(DATA,“>"&AVERAGE(DATA)+STDEV(DATA)*3)

（15）返回包含值為或-3的單元格數量：=COUNTIF(DATA,3）+COUNIF(DATA,-3)

（16）返回包含值邏輯值為TRUE的單元格數量：=COUNTIF(DATA,TRUE)

1.12 計算一個日期是一年中的第幾天

例如2006年7月29日是本年中的第幾天？在一年中，顯示是第幾天用什么函數呢？假定A1中是日期，利用下列公式：

=A1-DATE(YEAR(A1),1,0)，將單元格格式設置為常規，返回210，即2006年7月29日是2006年的第210天。

1.13 如何用公式求出最大值所在的行？

如A1：A10中有10個數，怎么求出最大的數在哪個單元格？

=MATCH(LARGE(A1:A10,1),A1:A10,0)

=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)

=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)

1.14 在Excel中的絕對引用與相對引用之間切換

在Excel中創建公式時，該公式可以使用相對引用，即相對于公式所在的位置引用單元；也可以使用絕對引用，即引用特定位置上的單元。引用由所在單元格的“列的字母”和“行的數字”組成，絕對引用由在“列的字母”和“行的數字”前面加“$”表示，例如，$B$1是對第一行B列的絕對引用。公式中還可以混合使用相對引用和絕對引用。可以利用F4切換相對引用和絕對引用，選中包含公式的單元格，在公式欄中選擇想要改變的引用，按F4鍵可以進行切換。

1.15 在Excel公式和結果之間快速切換

在excel工作表中輸入計算公式時，可以利用“Ctrl+`（中音號）”鍵來決定顯示或隱藏公式，可讓儲存格顯示計算的結果，還是公式本身。

1.16 如果某列中有大于0和小于0的數，將小于0數字所在的行自動刪除

假定在A1-A6中有大于0和小于0的數，可以用下面的VBA程序實現：

for i=6 to 1 step -1

if cells(i,1)<0 then rows(i).Delete

next i

1.17 奇數行和偶數行求和

有時候需要奇數行和偶數行單獨求和，例如要求A列第1行至1000行中奇數行之和，利用公式=SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))，要求這些行中偶數行之和，利用公式=SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))。

1.18 用函數來獲取單元格地址

在復雜的計算中，往往要獲知單元格的地址，可以用函數=ADDRESS(ROW(),COLUMN())獲得當前單元格的地址。

1.19 求一列中某個特定的值對應的另外列的最大或最小值

為了直觀起見，舉一個簡單的例子：例如在A1:A10中有若干臺計算機、打印機、傳真機等物品的名稱，在B1:B10中有上述設備對應的價格，求“計算機”對應的最低價格。可以用公式：

=min(if(a1:a10="計算機",b1:b10))，輸入該公式后按Ctrl+Shift+Enter完成。

1.20 自動記錄數據錄入時間

利用VBA實現，建立一個Time.xls文檔，輸入以下VBA代碼：

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 1 Then

Exit Sub

Else

Target.Offset(0, 1) = Now

End If

End Sub

1.21 如果一個單元格中既有數字又有字母，怎么提取其中的數字呢

Function getnumber(rng As String) As String

Dim mylen As Integer

Dim mystr As String

mylen = Len(rng)

For I = 1 To mylen

mystr = Mid(rng, I, 1)

If Asc(mystr) >= 48 And Asc(mystr) <= 57 Then

getnumber = getnumber & mystr

End If

Next I

End Function

1.22 Excel數組的應用

數組就是單元的集合或是一組處理的值集合。可以寫一個數組公式，即輸入一個單個的公式，它執行多個輸入的操作并產生多個結果——每個結果顯示在一個單元中。數組公式可以看成是有多重數值的公式。與單值公式的不同之處在于它可以產生一個以上的結果。一個數組公式可以占用一個或多個單元。數組的元素可多達6500個。

（1）了解數組

首先我們通過幾個例子來說明數組是如何工作的。我們可以從圖中看到，在“B”列中的數據為銷售量，在“C”列中的數據是銷售單價，要求計算出每種產品的銷售額和總的銷售金額，一般的做法是計算出每種產品的銷售額，然后再計算出總的銷售額。但是如果我們改用數組，就可以只鍵入一個公式來完成這些運算。

輸入數組公式的步驟為：

選定要存入公式的單元格，在本例中我們選擇“D4”單元格。輸入公式=SUM(B2:B4*C2:C4)，但不要按下[Enter]鍵（輸入公式的方法和輸入普通的公式一樣），按下[Shift]+[Ctrl]+[Enter]鍵。我們就會看到在公式外面加上了一對大括號“{}”，如圖 7-36所示。

在單元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范圍內的每一個單元格和“C2：C4”內相對應的單元格相乘，也就是把每個地區的銷售量和銷售單價相乘，相乘的結果共有3個數字，每個數字代表一個地區的銷售額，而“SUM”函數將這些銷售額相加，就得到了總的銷售額。

下面我們再以使用數組計算3種產品的銷售額為例，來說明如何產生多個計算結果。其操作過程如下：

(1) 選擇“D2：D4”單元格區域，該區域中的每個單元格保存的銷售金額。如圖7-37所示。

(2) 在“D2”單元格中輸入公式“=B2:B4*C2:C4”（不按[Enter]鍵）按下[Shift]+[Ctrl]+[Enter]”鍵，我們就可以從圖7-38中看到執行后的結果。同時我們可以看到“D2”到“D4”的格中都會出現用大括弧“{ }”框住的函數式，這表示“D2” 到“D4”被當作一個單元格來處理，所以不能對“D2”到“D4”中的任一格作任何單獨處理，必須針對整個數組來處理。

（2）使用數組常數

我們也可以在數組中使用常數值。這些值可以放在數組公式中使用區域引用的地方。要在數據公式中使用數組常數，直接將該值輸入到公式中并將它們放在括號里。例如，在圖7-39中，就使用了數組常數進行計算。

常數數組可以是一維的也可以是二維的。一維數組可以是垂直的也可以是水平的。在一維水平數組中的元素用逗號分開。下面是一個一維數組的例子。例如數組：{10,20,30,40,50}。在一維垂直數組中的元素用分號分開。在下面的例子是一個6×1的數組，{100;200;300;400;500;600}。

對于二維數組，用逗號將一行內的元素分開，用分號將各行分開。下一個例子是“4 ×4”的數組（由4行4列組成）：{100,200,300,400;110, … … ;130,230,330,440}。

注意：不可以在數組公式中使用列出常數的方法列出單元引用、名稱或公式。例如：{2*3,3*3,4*3}因為列出了多個公式，是不可用的。{A1,B1,C1}因為列出多個引用，也是不可用的。不過可以使用一個區域，例如{A1:C1}。

對于數組常量的內容，可由下列規則構成：

數組常量可以是數字、文字、邏輯值或錯誤值。

數組常量中的數字，也可以使用整數、小數或科學記數格式。

文字必須以雙引號括住。

同一個數組常量中可以含有不同類型的值。

數組常量中的值必須是常量，不可以是公式。

數組常量不能含有貨幣符號、括號或百分比符號。

所輸入的數組常量不得含有不同長度的行或列。

（3）數組的編輯

數組包含數個單元格，這些單元格形成一個整體，所以，數組里的某一單元格不能單獨編輯。在編輯數組前，必須先選取整個數組。

選取數組的步驟為：

(1) 選取數組中的任一單元格。

(2) 在“編輯”菜單中選擇“定位”命令或者按下[F5]鍵，出現一個“定位”對話框。按下“定位條件”按鈕，出現一個定位條件對話框，如圖7-40所示。

選擇“當前數組”選項，最后按下“確定”按鈕，就可以看到數組被選定了。

編輯數組的步驟為:

選定要編輯的數組，移到數據編輯欄上按[F2]鍵或單擊左鍵，使代表數組的括號消失，之后就可以編輯公式了。編輯完成后，按下[Shift] +[Ctrl] + [Enter]鍵。

若要刪除數組，其步驟為：選定要刪除的數組，按[Ctrl]+[Delete]或選擇編輯菜單中的“清除”。

（4）數組的擴充

在公式或函數中使用數組常量時，其它運算對象或參數應該和第一個數組具有相同的維數。必要時，Microsoft Excel 會將運算對象擴展，以符合操作需要的維數。每一個運算對象的行數必須和含有最多行的運算對象的行數一樣，而列數也必須和含有最多列數對象的列數一樣。

例如: = SUM({1,2,3}+{4,5,6}）內的第一個數組為1×3,得到的結果為1+4、2+5和3+6的和,也就是21。如果將公式寫成 = SUM({1,2,3}+4}）,則第二個數據并不是數組，而是一個數值，為了要和第一個數組相加，Excel 會自動將數值擴充成1 ×3 的數組。使用=SUM({1,2,3}+{4,4,4})做計算，得到的結果為1+4、2+4和3+4的和, 即18。

將數組公式輸入單元格區域中時，所使用的維數應和這個公式計算所得數組維數相同。這樣，Microsoft Excel 才能把計算所得的數組中的每一個數值放入數組區域的一個單元格內。

如果數組公式計算所得的數組比選定的數組區域還小，則 Microsoft Excel會將這個數組擴展，以便將它填入整個數組區域內。例如：={1,2;3,4}*2擴充后的公式就會變為={1,2;3,4}*{2,2;2,2}，則相應的計算結果為“2,4,6,8”。再如：輸入公式={1,2;3,4}*{2,3}擴充后的公式就會變為={1,2;3,4}*{2,3;2,3} ，則相應的計算結果為“2,6,6,12”。

如果 Microsoft Excel 將一個數組擴展到可以填入比該數組公式大的區域內，而沒有擴大值可用的單元格內，這樣就會出現#N/A錯誤值。例如：={1,2;3,4}={1,2,3} 擴充后的公式就會變為={1,2,#N/A;3,4,#N/A}*{1,2,#/A;1.2.#N/A} ，而相應的計算結果為“2,4,#N/A,4,6,#N/A”。

如果數組公式計算所得的數組比選定的數組區域還要大，則超過的值不會出現在工作表上。

1.23 數組的應用

（1）數組公式的實現方法：

其實這些都是數組公式，數組公式的輸入方法是將公式輸入后，不要直接按回車鍵（Enter），而是要同時按Ctrl+Shift+Enter，這時電腦自動會為你添加“{ }”的。

在論壇上，為了告訴大家這是數組公式，故在公式的頭尾都加上了“{ }”。如果不小心按回車了，可以用鼠標點一下編輯欄中的公式，再按Ctrl+Shift+Enter。

編輯或刪除數組公式編輯數組公式時，須選取數組區域并且激活編輯欄，公式兩邊的花括號將消失，然后編輯公式，最后按Ctrl＋Shift＋Enter鍵。選取數組公式所占有的全部區域后，按Delete鍵即可刪除數組公式。

數組常量的使用數組公式中還可使用數組常量，但必須自己鍵入花括號“{ }”將數組常量括起來，并且用“，”和“；”分離元素。其中“，”分離不同列的值，“；”分離不同行的值。

2、數組公式的原理：

數組公式，說白了就是同時對一組或幾組數同時處理，然后得到需要的答案。運用數組公式的最重要的原理是數于數之間一一對應。

1、假設要將A1：A50區域中的所有數值舍入到2位小數位，然后對舍入的數值求和。很自然地就會想到使用公式：=ROUND（A1，2）＋ROUND（A2，2）＋…＋ROUND（A50，2）。或者添加ROUND輔助列（A1=ROUND（A1，2）），然后對輔助用SUM函數合計（=SUM（A1：A50））。

如果用數組公式就不要這么麻煩，公式為：

{=SUM（ROUND（A1：A50，2））}，它的意思即為在數組A1：A50用ROUND函數進行二位小數的四舍五入，然后進行合計。

2、假設一題為A1：A10區域中為商品單價，B1：B10為對應的銷售數量，需要統計總銷售額，常規做法需要添加輔助列C列，在C列中計算出C1：C10的每個單價的銷售額（C1=A1*B1），然后進行SUM合計（C11=SUM（C1：C10））。

而數組公式為：

{=SUM（A1：A10*B1：B10）}

3、注意：關于常數項的數組可以直接手工添加{ }，如此公式 = SUM({1,2,3}+{4,5,6}），這也是數組公式的一種形式。

需要統計如下圖所示銷量的頻率分布，即分別統計銷量在5000以下、5000到10000、10000到50000以及大于50000的銷售點數量

a2b2C2

銷售點銷售額分段點

城北001 4100 5000

城北002 15890 10000

城南001 8700 50000

城南002 25900

城南003 5800

城東001 15300

城東002 38000

城東003 9800

城西001 56000

城西002 72050

城中001 130000

城中002 60400

城中003 48700

步驟：

"1、制作如上圖所示的表格

2、選中單元格G7：G10，直接輸入公式：=FREQUENCY（B4：B14，c4：c6）

3、輸入公式后，按CTRL+SHIFT+ENTER鍵結束

類型日期單價銷售數量

A 2005-6-15 1000 10

B 2005-6-20 1000 15

B 2005-7-1 4000 10

C 2005-7-10 4000 11

B 2005-8-15 9000 13

C 2005-8-20 9000 15

A 2005-9-30 1000 14

A 2005-10-10 1000 20

B 2005-10-15 4000 25

類型從B1格開始

計算B產品8月份銷量

13{=SUM(IF(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8),($E$2:$E$10),0))}

13 {=SUM(($B$2:$B$10="B")*(MONTH($C$2:$C$10)=8)*($E$2:$E$10))}

計算A產品和B產品的銷量

107 {=SUM(IF(($B$2:$B$10="A")+($B$2:$B$10="B"),($E$2:$E$10),0))}

107 {=SUM((($B$2:$B$10="A")+($B$2:$B$10="B"))*($E$2:$E$10))}

計算8月份前不包括B產品銷量和8月后不包括C產品銷量

49 {=SUM(IF(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C")),$E$2:$E$10))}

49 {=SUM(IF(((MONTH($C$2:$C$10)<8)-($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)-($B$2:$B$10="C")),$E$2:$E$10))}

49 {=SUM(((MONTH($C$2:$C$10)<8)<>($B$2:$B$10="B"))*((MONTH($C$2:$C$10)>=8)<>($B$2:$B$10="C"))*$E$2:$E$10)}

以上公式中*的意思為AND,+的意思為OR,-的意思為<> "不等于"

1.24 求一個單元格數值中的最大數字和個數字之和

我們平時都是對不同單元格之間的數字進行計算，但是在一個單元格內部，各數字之間有什么關系？這是一個很有創新意識的命題。例如A1中的數字為389732，求其中最大的數字9，求這和6個數字之和為32。

（1）求其中最大的數字，利用數組公式：

{=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)}

先輸入=MAX(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)，再按Ctrl+Shift+Enter。

（2）求其中數字之和，利用下面的公式：

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)

1.25 邏輯函數的非邏輯表現

例如，求取范圍Data中小于0或大于5的數值之和:

正確用法：

{=SUM(IF((Data<0)+(Data>5),Data))}

錯誤用法：

{=SUM(IF(OR(Data<0,Data>5),Data))}

1.26 在EXCEL的數組公式中ROW函數的用法

在EXCEL的數組公式中，ROW()是一個非常有用的函數，現在舉個例子來說明。

（1）返回一列中最后一個數值

{=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100<>"")))}

在這個公式中用ROW函數返回A1:A100<>""即A1格到A100中不為空的單元格，它是一組數據，然后用MAX確定最大的一個行號，即最后一格不為空的單元格，然后用INDEX，來返回A1到A100中A列最大行號的那個數據。

（2）同理如果要返回一行中最后一個數值則為

{=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))}

（3）下面出一個小題目，如果有興趣想學數組的可以試一下，返回A列100行中最后一個有數值的行號的公式是什么？

{=MAX(IF(A1:A100<>"",ROW(A1:A100),""))}

1.27 返回最大值的行號和地址

返回最大值的行號：

{=min(if(A1:A100=max(A1:A100),row(A1:A100),"")}

返回最大值的地址：

{=ADDRESS(MIN(IF(A1:A100=max(A1:A100),ROW(A1:A100),"")),COLUMN(A1:A100))}

{=MAX(IF((A1:A100<>"")*ISNUMBER(A1:A100),ROW(A1:A100),""))}

1.28 Excel常見錯誤及解決辦法

經常用Excel可能都會遇到一些錯誤值信息，如：# N/A！、#VALUE！、#DIV/O！等等，出現這些錯誤的原因有很多種，如果公式不能計算正確結果，Excel將顯示一個錯誤值，例如，在需要數字的公式中使用文本、刪除了被公式引用的單元格，或者使用了寬度不足以顯示結果的單元格。以下是幾種常見的錯誤及其解決方法。

（1）#####！

原因：如果單元格所含的數字、日期或時間比單元格寬，或者單元格的日期時間公式產生了一個負值，就會產生#####！錯誤。

解決方法：如果單元格所含的數字、日期或時間比單元格寬，可以通過拖動列表之間的寬度來修改列寬。如果使用的是1900年的日期系統，那么Excel中的日期和時間必須為正值，用較早的日期或者時間值減去較晚的日期或者時間值就會導致#####！錯誤。如果公式正確，也可以將單元格的格式改為非日期和時間型來顯示該值。

（2）#VALUE!

當使用錯誤的參數或運算對象類型時，或者當公式自動更正功能不能更正公式時，將產生錯誤值#VALUE!。

原因一：在需要數字或邏輯值時輸入了文本，Excel不能將文本轉換為正確的數據類型。

解決方法：確認公式或函數所需的運算符或參數正確，并且公式引用的單元格中包含有效的數值。例如：如果單元格A1包含一個數字，單元格A2包含文本"學籍"，則公式"=A1+A2"將返回錯誤值#VALUE!。可以用SUM工作表函數將這兩個值相加（SUM函數忽略文本）：=SUM（A1:A2）。

原因二：將單元格引用、公式或函數作為數組常量輸入。

解決方法：確認數組常量不是單元格引用、公式或函數。

原因三：賦予需要單一數值的運算符或函數一個數值區域。

解決方法：將數值區域改為單一數值。修改數值區域，使其包含公式所在的數據行或列。

（3）#DIV/O!

當公式被零除時，將會產生錯誤值#DIV/O!。

原因一：在公式中，除數使用了指向空單元格或包含零值單元格的單元格引用（在Excel中如果運算對象是空白單元格，Excel將此空值當作零值）。

解決方法：修改單元格引用，或者在用作除數的單元格中輸入不為零的值。

原因二：輸入的公式中包含明顯的除數零，例如：=5/0。

解決方法：將零改為非零值。

（4）#NAME?

在公式中使用了Excel不能識別的文本時將產生錯誤值#NAME?。

原因一：刪除了公式中使用的名稱，或者使用了不存在的名稱。

解決方法：確認使用的名稱確實存在。選擇菜單"插入"|"名稱"|"定義"命令，如果所需名稱沒有被列出，請使用"定義"命令添加相應的名稱。

原因二：名稱的拼寫錯誤。

解決方法：修改拼寫錯誤的名稱。

原因三：在公式中使用標志。

解決方法：選擇菜單中"工具"|"選項"命令，打開"選項"對話框，然后單擊"重新計算"標簽，在"工作薄選項"下，選中"接受公式標志"復選框。

原因四：在公式中輸入文本時沒有使用雙引號。

解決方法：Excel將其解釋為名稱，而不理會用戶準備將其用作文本的想法，將公式中的文本括在雙引號中。例如：下面的公式將一段文本"總計："和單元格B50中的數值合并在一起：="總計："&B50

原因五：在區域的引用中缺少冒號。

解決方法：確認公式中，使用的所有區域引用都使用冒號。例如：SUM（A2:B34）。

（5）#N/A

原因：當在函數或公式中沒有可用數值時，將產生錯誤值#N/A。

解決方法：如果工作表中某些單元格暫時沒有數值，請在這些單元格中輸入"#N/A"，公式在引用這些單元格時，將不進行數值計算，而是返回#N/A。

（6）#REF!

當單元格引用無效時將產生錯誤值#REF！。

原因：刪除了由其他公式引用的單元格，或將移動單元格粘貼到由其他公式引用的單元格中。

解決方法：更改公式或者在刪除或粘貼單元格之后，立即單擊"撤消"按鈕，以恢復工作表中的單元格。

（7）#NUM！

當公式或函數中某個數字有問題時將產生錯誤值#NUM！。

原因一：在需要數字參數的函數中使用了不能接受的參數。

解決方法：確認函數中使用的參數類型正確無誤。

原因二：使用了迭代計算的工作表函數，例如：IRR或RATE，并且函數不能產生有效的結果。

解決方法：為工作表函數使用不同的初始值。

原因三：由公式產生的數字太大或太小，Excel不能表示。

解決方法：修改公式，使其結果在有效數字范圍之間。

（8）#NULL！

當試圖為兩個并不相交的區域指定交叉點時將產生錯誤值#NULL！。

原因：使用了不正確的區域運算符或不正確的單元格引用。

解決方法：如果要引用兩個不相交的區域，請使用聯合運算符逗號（，）。公式要對兩個區域求和，請確認在引用這兩個區域時，使用逗號。如：SUM（A1:A13，D12:D23）。如果沒有使用逗號，Excel將試圖對同時屬于兩個區域的單元格求和，但是由于A1:A13和D12:D23并不相交，所以他們沒有共同的單元格。

1.29 金額大寫的轉換

假設A1單元格為原始數據，即小寫數字。

公式法一：

=IF(A1=0,"零元整",IF(A1<0,"負",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[dbnum2]")&"角",IF(INT(ABS(A1))=ABS(A1),,"零"))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)* 10,) , TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"[dbnum2]")&"分","整"))

公式法二：

=IF(A1<0,"負",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元"&IF(ROUND(A1,3) =ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF (ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))

公式法三：

=CONCATENATE(TEXT(INT(A1),"[DBNum2][$-804]G/通用格式")&"元"&IF((INT(A1*10) -INT(A1)*10)=0,"",TEXT(IF(AND(CEILING(A1*100,1)-INT(A1*10)*10=10,INT(A1*1000)-INT(A1*100)*10>=5),INT(A1*10)-INT(A1)*10+1,INT(A1*10)-INT(A1)*10),"[DBNum2][$-804]G/通用格式")&"角")&IF(OR(INT(A1*100)-INT(A1*10)*10=0,(IF(INT(A1*1000)-INT(A1*100)*10>=5,CEILING(A1* 100,1)-INT(A1*10)*10=10,FALSE))),"整",(IF(INT(A1*1000)-INT(A1*100)*10>=5,(IF(CEILING(A1 *100,1)-INT(A1*10)*10=10,"",(TEXT(CEILING(A1*100,1)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分"))),(TEXT(INT(A1*100)-INT(A1*10)*10,"[DBNum2][$-804]G/通用格式")&"分")))),"(￥",FIXED(A1,2,TRUE),"元)")

四、VBA代碼法（自定義函數）

Public Function BigNum(xiaoxie As Currency)

application.Volatile

Dim fuhao As String

fuhao = ""

If xiaoxie < 0 Then

xiaoxie = -xiaoxie

fuhao = "負"

End If

If xiaoxie = 0 Then

BigNum = "零元整"

Else