Excel制表技巧（48）公式及函數 2006年12月14日 星期四 22:18
| 函數與公式的概念 什麼是函數? Excel函數即是預先定義，執行計算、分析等處理資料任務的特殊公式。以常用的求和函數SUM為例，它的文法是“SUM(number1,number2,......)”。其中“SUM”稱為函數名稱，一個函數隻有唯一的一個名稱，它決定了函數的功能和用途。函數名稱後緊跟左括号，接着是用逗号分隔的稱為參數的内容，最後用一個右括号表示函數結束。 參數是函數中最複雜的組成部分，它規定了函數的運算對象、順序或結構等。使得使用者可以對某個單元格或區域進行處理，如分析存款利息、确定成績名次、計算三角函數值等。 按照函數的來源，Excel函數可以分為内置函數和擴充函數兩大類。前者隻要啟動了Excel，使用者就可以使用它們;而後者必須通過單擊“工具→加載宏”菜單指令加載，然後才能像内置函數那樣使用。 如果函數要以公式的形式出現，它必須有兩個組成部分，一個是函數名稱前面的等号，另一個則是函數本身。 一、初步了解Excel公式 1. 什麼是公式 前面已經介紹過，我們把“公式”列為不同于“數值”和“文本”之外的第三種資料類型。公式的共同特點是以“=”号開頭，它可以是簡單的數學式，也可以是包含各種Excel函數的式子。 2. 公式由哪些元素組成 輸入到單元格中的公式均由等号開頭，等号後面由如下五種元素組成： 運算符：例如“+”或者“*”号。 單元格引用：它包括單個的單元格或多個單元格組成的範圍，以及命名的單元格區域。這些單元格或範圍可以是同一工作表中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。 數值或文本：前面介紹過的兩種資料類型。例如，“100”或“新悅國際教育集團”。 工作表函數：可以是Excel内置的函數，如SUM或MAX，也可以是自定義的函數。 括号：即“(”和“)”。它們用來控制公式中各表達式被處理的優先權。 了解公式的組成是建立公式必備的基礎。另外，Excel工作表中的公式最多可以由1024個字元組成，我們日常所建立的公式，超過100個字元的已經非常少，是以這個長度足夠我們使用了。  看看公式長什麼樣子 一、在公式中使用引用 我們在往單元格中輸入公式回車以後，單元格中會顯示公式計算的結果，如果要檢視公式，可以選中該單元格，單元格中的公式則會顯示在編輯欄中。 我們先來看看如果不使用引用将會有什麼弊端。 1. 不使用引用的弊端 在圖1所示的工作表中，A列和B列單元格分别存放着“單價”和“數量”資料，顯然它們都是“數值”類型的，為了求出“FX82MS”的“金額”數值，可以在D2單元格中輸入公式“=250.5*12”，回車後D2單元格顯示該公式的計算結果“3006”。  圖 1 這個公式中并沒使用引用，而是使用的具體數值。下面我們來分析一下不使用引用的兩個缺點： 首先，假設“單價”或“數量”的值改變了，我們必須對公式進行修改，這讓人覺得這個公式相當死闆，毫無靈活性。 其次，假設我們想求出圖1中後面的各種商品的“金額”數值，必須重新逐個輸入公式，這樣不僅費時費力，而且還容易出錯。 如果在公式中使用引用則可以很好地克服上述的兩個缺點，下面我們仍利用圖1的表格進行說明。 Excel 公式位置的引用 一個引用位置代表工作表上的一個或者一組單元格，引用位置告訴Excel在哪些單元格中查找公式中要用的數值。通過使用引用位置，我們可以在一個公式中使用工作表上不同部分的資料，也可以在幾個公式中使用同一個單元格中的數值。 我們也可以引用同一個工作簿上其它工作表中的單元格，或者引用其它工作簿，也可以引用其他應用程式中的資料。引用其他工作簿中的單元格稱為外部引用。引用其他應用程式中的資料稱為遠端引用。單元格引用位置基于工作表中的行号和列标。 7.4.1 單元格位址的輸入 在公式中輸入單元格位址最準确的方法是使用單元格指針。我們雖然可以輸入一個完整的公式，但在輸入過程中很可能有輸入錯誤或者讀錯螢幕單元位址，例如，我們很可能将“B23”輸入為“B22”。是以，在我們将單元格指針指向正确的單元格時，實際上已經把活動的單元格位址移到公式中的相應位置了，進而也就避免了錯誤的發生。在利用單元格指針輸入單元格位址的時候，最得力的助手就是使用滑鼠。 使用滑鼠輸入的過程如下： （1）選擇要輸入公式的單元格，在編輯欄的輸入框中輸入一個等号“=”。 （2）用滑鼠指向單元格位址，然後單擊選中單元格位址。 （3）輸入運算符号，如果輸入完畢，按下“Enter”鍵或者單擊編輯欄上的“确認”按鈕。如果沒有輸入完畢，則繼續輸入公式。 例如，我們要在單元格“B2”中輸入公式“=A1+A2+C6”，則可将滑鼠指向單元格“B2”，然後鍵入一個“=”号，接着将滑鼠指向“A1”單擊，再鍵入“＋”号，重複這一過程直到将全部公式輸入進去。 7.4.2 相對位址引用 在輸入公式的過程中，除非我們特别指明，Excel一般是使用相對位址來引用單元格的位置。所謂相對位址是指：當把一個含有單元格位址的公式拷貝到一個新的位置或者用一個公式填入一個範圍時，公式中的單元格位址會随着改變。例如在上一節中，輸入的公式實際上代表了如下的含義：将單元格“A1”中的内容放置到“B2”單元格中，然後分别和“A2”、“C6”單元格中的數字相加并把結果放回到“B2”單元格中。使用相對引用就好像告訴一個向我們問路的人：從現在的位置，向前再走三個路口就到了。 例如，我們将上例中的公式“=A1+A2+C6”分别拷貝到單元格“C2”、“D2”、“B3”和“B4”中。圖7-5顯示了拷貝後的公式，從中看到相對引用的變化。 7.4.3 絕對位址引用 在一般情況下，拷貝單元格位址時，是使用相對位址方式，但在某些情況下，我們不希望單元格位址變動。在這種情況下，就必須使用絕對位址引用。 所謂絕對位址引用，就是指：要把公式拷貝或者填入到新位置，并且使公式中的固定單元格位址保持不變。在Excel中，是通過對單元格位址的“當機”來達到此目的，也就是在列号和行号前面添加美元符号“＄”。 下面以圖7-6來中的“b2”單元格來說明絕對位址引用。例如，公式“=A1*A3”中的“A1”是不能改變的。我們就必須使其變成絕對位址引用，公式改變為“=$A$1*A3”，當将公式拷貝時就不會被當作相對位址引用了，從圖7-6的“C2”單元格可看到發生的變化。 7.4.4 混合位址引用 在某些情況下，我們需要在拷貝公式時隻有行保持或者隻有列保持不變。在這種情況下，就要使用混合位址引用。所謂混合位址引用是指：在一個單元格位址引用中，既有絕對位址引用，同時也包含有相對單元格位址引用。例如，單元格位址“$A5”就表明保持“列”不發生變化，但“行”會随着新的拷貝位置發生變化；同理，單元格位址“A$5”表明保持“行”不發生變化，但“列”會随着新的拷貝位置發生變化。圖7-7是混合位址引用的範例。 7.4.5 三維位址引用 前面我們學習過，Microsoft Excel 2000中文版的所有工作是以工作簿展開的。比如，要對一年的12個月銷售情況進行彙總，而這些資料是分布在12張工作表中的，要完成這些銷售資料的彙總，就必須要能夠讀取（引用）在每張表格中的資料，這也就引出了“三維位址引用”這一新概念。 所謂三維位址引用是指：在一本工作簿中從不同的工作表引用單元格。三維引用的一般格式為：工作表名！：單元格位址，工作表名後的“！”是系統自動加上的。例如我在第二張工作表的“B2”單元格輸入公式“=Sheet1!:A1+A2”，則表明要引用工作表“Sheet1中的單元格‘B1’和工作表Sheet2中的單元格‘B2’相加，結果放到工作表Sheet2中的‘B2’單元格。 利用三維位址引用，可以一次性将一本工作簿中指定的工作表的特定單元格進行彙總。 函數的參數 函數右邊括号中的部分稱為參數，假如一個函數可以使用多個參數，那麼參數與參數之間使用半形逗號進行分隔。 參數可以是常量(數字和文本)、邏輯值(例如TRUE或FALSE)、數組、錯誤值(例如#N/A)或單元格引用(例如E1:H1)，甚至可以是另一個或幾個函數等。參數的類型和位置必須滿足函數文法的要求，否則将傳回錯誤資訊。 (1)常量 常量是直接輸入到單元格或公式中的數字或文本，或由名稱所代表的數字或文本值，例如數字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式計算出的結果都不是常量，因為隻要公式的參數發生了變化，它自身或計算出來的結果就會發生變化。 (2)邏輯值 邏輯值是比較特殊的一類參數，它隻有TRUE(真)或FALSE(假)兩種類型。例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一個可以傳回TRUE(真)或FALSE(假)兩種結果的參數。當“A3=0”為TRUE(真)時在公式所在單元格中填入“0”，否則在單元格中填入“A2/A3”的計算結果。 (3)數組 數組用于可産生多個結果，或可以對存放在行和列中的一組參數進行計算的公式。Excel中有常量和區域兩類數組。前者放在“{}”(按下Ctrl+Shift+Enter組合鍵自動生成)内部，而且内部各列的數值要用逗号“,”隔開，各行的數值要用分号“;”隔開。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就應該建立一個2行3列的常量數組“{56,78,89;90,76,80}。 區域數組是一個矩形的單元格區域，該區域中的單元格共用一個公式。例如公式“=TREND(B1:B3,A1:A3)”作為數組公式使用時，它所引用的矩形單元格區域“B1:B3,A1:A3”就是一個區域數組。 (4)錯誤值 使用錯誤值作為參數的主要是資訊函數，例如“ERROR.TYPE”函數就是以錯誤值作為參數。它的文法為“ERROR.TYPE(error_val)”，如果其中的參數是#NUM!，則傳回數值“6”。 (5)單元格引用 單元格引用是函數中最常見的參數，引用的目的在于辨別工作表單元格或單元格區域，并指明公式或函數所使用的資料的位置，便于它們使用工作表各處的資料，或者在多個函數中使用同一個單元格的資料。還可以引用同一工作簿不同工作表的單元格，甚至引用其他工作簿中的資料。 根據公式所在單元格的位置發生變化時，單元格引用的變化情況，我們可以引用分為相對引用、絕對引用和混合引用三種類型。以存放在F2單元格中的公式“=SUM(A2:E2)”為例，當公式由F2單元格複制到F3單元格以後，公式中的引用也會變化為“=SUM(A3:E3)”。若公式自F列向下繼續複制，“行标”每增加1行，公式中的行标也自動加1。 如果上述公式改為“=SUM($A $3:$E $3)”，則無論公式複制到何處，其引用的位置始終是“A3:E3”區域。 混合引用有“絕對列和相對行”，或是“絕對行和相對列”兩種形式。前者如“=SUM($A3:$E3)”，後者如“=SUM(A$3:E$3)”。 上面的幾個執行個體引用的都是同一工作表中的資料，如果要分析同一工作簿中多張工作表上的資料，就要使用三維引用。假如公式放在工作表Sheet1的C6單元格，要引用工作表Sheet2的“A1:A6”和Sheet3的“B2:B9”區域進行求和運算，則公式中的引用形式為“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是說三維引用中不僅包含單元格或區域引用，還要在前面加上帶“!”的工作表名稱。 假如你要引用的資料來自另一個工作簿，如工作簿Book1中的SUM函數要絕對引用工作簿Book2中的資料，其公式為“=SUM([Book2]Sheet1! SA S1: SA S8,[Book2]Sheet2! SB S1: SB S9)”，也就是在原來單元格引用的前面加上“[Book2]Sheet1!”。放在中括号裡面的是工作簿名稱，帶“!”的則是其中的工作表名稱。即是跨工作簿引用單元格或區域時，引用對象的前面必須用“!”作為工作表分隔符，再用中括号作為工作簿分隔符。不過三維引用的要受到較多的限制，例如不能使用數組公式等。 提示:上面介紹的是Excel預設的引用方式，稱為“A1引用樣式”。如果你要計算處在“宏”内的行和列，必須使用“R1C1引用樣式”。在這種引用樣式中，Excel使用“R”加“行标”和“C”加“列标”的方法訓示單元格位置。啟用或關閉R1C1引用樣式必須單擊“工具→選項”菜單指令，打開對話框的“正常”頁籤，選中或清除“設定”下的“R1C1引用樣式”選項。由于這種引用樣式很少使用，限于篇幅本文不做進一步介紹。 (6)嵌套函數 除了上面介紹的情況外，函數也可以是嵌套的，即一個函數是另一個函數的參數，例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函數使用了嵌套的RIGHTB函數，并将後者傳回的結果作為IF的邏輯判斷依據。 (7)名稱和标志 為了更加直覺地辨別單元格或單元格區域，我們可以給它們賦予一個名稱，進而在公式或函數中直接引用。例如“B2:B46”區域存放着學生的實體成績，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在給B2:B46區域命名為“實體分數”以後，該公式就可以變為“=AVERAGE(實體分數)”，進而使公式變得更加直覺。 給一個單元格或區域命名的方法是:選中要命名的單元格或單元格區域，滑鼠單擊編輯欄頂端的“名稱框”，在其中輸入名稱後回車。也可以選中要命名的單元格或單元格區域，單擊“插入→名稱→定義”菜單指令，在打開的“定義名稱”對話框中輸入名稱後确定即可。如果你要删除已經命名的區域，可以按相同方法打開“定義名稱”對話框，選中你要删除的名稱删除即可。 由于Excel工作表多數帶有“列标志”。例如一張成績統計表的首行通常帶有“序号”、“姓名”、“數學”、“實體”等“列标志”(也可以稱為字段)，如果單擊“工具→選項”菜單指令，在打開的對話框中單擊“重新計算”頁籤，選中“工作簿選項”選項組中的“接受公式标志”選項，公式就可以直接引用“列标志”了。例如“B2:B46”區域存放着學生的實體成績，而B1單元格已經輸入了“實體”字樣，則求實體平均分的公式可以寫成“=AVERAGE(實體)”。 需要特别說明的是，建立好的名稱可以被所有工作表引用，而且引用時不需要在名稱前面添加工作表名(這就是使用名稱的主要優點)，是以名稱引用實際上是一種絕對引用。但是公式引用“列标志”時的限制較多，它隻能在目前資料列的下方引用，不能跨越工作表引用，但是引用“列标志”的公式在一定條件下可以複制。從本質上講，名稱和标志都是單元格引用的一種方式。因為它們不是文本，使用時名稱和标志都不能添加引号。 EXCEL公式及函數的進階應用(1) 公式和函數是Excel最基本、最重要的應用工具，是Excel的核心，是以，應對公式和函數熟練掌握，才能在實際應用中得心應手。 2.1.1 數組公式及其應用數組公式就是可以同時進行多重計算并傳回一種或多種結果的公式。在數組公式中使用兩組或多組資料稱為數組參數，數組參數可以是一個資料區域，也可以是數組常量。數組公式中的每個數組參數必須有相同數量的行和列。2.1.1.1 數組公式的輸入、編輯及删除1．數組公式的輸入 數組公式的輸入步驟如下： （1）標明單元格或單元格區域。如果數組公式将傳回一個結果，單擊需要輸入數組公式的單元格；如果數組公式将傳回多個結果，則要標明需要輸入數組公式的單元格區域。 （2）輸入數組公式。 （3）同時按“Crtl+Shift+Enter”組合鍵，則Excel 自動在公式的兩邊加上大括号{ } 。 特别要注意的是，第（3）步相當重要，隻有輸入公式後同時按“Crtl+Shift+Enter”組合鍵，系統才會把公式視為一個數組公式。否則，如果隻按Enter鍵，則輸入的隻是一個簡單的公式，也隻在選中的單元格區域的第1個單元格顯示出一個計算結果。 在數組公式中，通常都使用單元格區域引用，但也可以直接鍵入數值數組，這樣鍵入的數值數組被稱為數組常量。當不想在工作表中按單元格逐個輸入數值時，可以使用這種方法。如果要生成數組常量，必須按如下操作： （1）直接在公式中輸入數值，并用大括号“{ }”括起來。 （2）不同列的數值用逗号“,”分開。 （3）不同行的數值用分号“;”分開。 Ø 輸入數組常量的方法： 例如，要在單元格A1:D1中分别輸入10，20，30和40這4個數值，則可采用下述的步驟： （1）選取單元格區域A1:D1，如圖2-1所示。 圖2-1 選取單元格區域A1:D1 （2）在公式編輯欄中輸入數組公式“={10,20,30,40}”，如圖2-2所示。 圖2-2 在編輯欄中輸入數組公式 （3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A1、B1、C1、D1中分别輸入了10、20、30、40，如圖2-3所示。 假若要在單元格A1、B1、C1、D1、A2、B2、C2、D2中分别輸入10、20、30、40、50、60、70、80，則可以采用下述的方法： 圖2-3 同時按Ctrl+Shift+Enter組合鍵，得到數組常量 （1）選取單元格區域A1:D2，如圖2-4所示。 圖2-4 選取單元格區域A1:D2 （2）在編輯欄中輸入公式“={10,20,30,40;50,60,70,80}”，如圖2-5所示。 圖2-5 在編輯欄中輸入數組公式 （3）按Ctrl+Shift+Enter組合鍵，就在單元格A1、B1、C1、D1、A2、B2、C2、D2中分别輸入了10、20、30、40和50、60、70、80，如圖2-6所示。 圖2-6 同時按Ctrl+Shift+Enter組合鍵，得到數組常量 Ø 輸入公式數組的方法 例如，在單元格A3:D3中均有相同的計算公式，它們分别為單元格A1:D1與單元格A2:D2中資料的和，即單元格A3中的公式為“=A1+A2”，單元格B3中的公式為“=B1+B2”，…，則可以采用數組公式的方法輸入公式，方法如下： （1）選取單元格區域A3:D3，如圖2-7所示。 （2）在公式編輯欄中輸入數組公式“=A1:D1+A2:D2”，如圖2-8所示。 圖2-8 在編輯欄中輸入數組公式 （3）同時按Ctrl+Shift+Enter組合鍵，即可在單元格A3:D3中得到數組公式“=A1:D1+A2:D2”，如圖2-9所示。 圖2-9 同時按Ctrl+Shift+Enter組合鍵，得到數組公式 2．編輯數組公式 數組公式的特征之一就是不能單獨編輯、清除或移動數組公式所涉及的單元格區域中的某一個單元格。若在數組公式輸入完畢後發現錯誤需要修改，則需要按以下步驟進行： （1）在數組區域中單擊任一單元格。 （2）單擊公式編輯欄，當編輯欄被激活時，大括号“{ }”在數組公式中消失。 （3）編輯數組公式内容。 （4）修改完畢後，按“Crtl+Shift+Enter”組合鍵。要特别注意不要忘記這一步。 3．删除數組公式 删除數組公式的步驟是：首先標明存放數組公式的所有單元格，然後按Delete鍵。 2.1.1.2 數組公式的應用1．用數組公式計算兩個資料區域的乘積 【例2-1】如圖2-10所示，已經知道12個月的銷售量和産品單價，則可以利用數組公式計算每個月的銷售額，步驟如下： 圖2-10 用數組公式計算銷售額 （1）選取單元格區域B4:M4。 （2）輸入公式“=B2:M2*B3:M3”。 （3）按“Crtl+Shift+Enter”組合鍵。 如果需要計算12個月的月平均銷售額，可在單元格B5中輸入公式“=AVERAGE(B2:M2*B3:M3)”，然後按“Crtl+Shift+Enter”組合鍵即可，如圖2-10所示。 在數組公式中，也可以将某一常量與數組公式進行加、減、乘、除，也可以對數組公式進行乘幂、開方等運算。例如在圖2-10中，每月的單價相同，故我們也可以在單元格B4:M4中輸入公式“=B2:M2*28”，然後按“Crtl+Shift+Enter”組合鍵；在單元格B5中輸入公式“=AVERAGE(B2:M2*28)”，然後按“Crtl+Shift+Enter”組合鍵。 在使用數組公式計算時，最好将不同的單元格區域定義不同的名稱，如在圖2-10中，将單元格區域B2:M2定義名稱為“銷售量”，單元格區域B3:M3定義名稱為“單價”，則各月的銷售額計算公式為“=銷售量*單價”，月平均銷售額計算公式為“=AVERAGE(銷售量*單價)”，這樣不容易出錯。 2．用數組公式計算多個資料區域的和 如果需要把多個對應的行或列資料進行相加或相減的運算，并得出與之對應的一行或一列資料時，也可以使用數組公式來完成。 【例2-2】某企業2002年銷售的3種産品的有關資料如圖2-11所示，則可以利用數組公式計算該企業2002年的總銷售額，方法如下： 圖2-11 某企業的月銷售總額計算 （1）選取單元格區域C8:N8。 （2）輸入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。 （3）按“Crtl+Shift+Enter”組合鍵。 3．用數組公式同時對多個資料區域進行相同的計算 【例2-3】某公司對現有三種商品實施降價銷售，産品原價如圖2-12所示，降價幅度為20%，則可以利用數組公式進行計算，步驟如下： 圖2-12 産品降價計算 （1）選取單元格區域G3:I8。 （2）輸入公式“=B3:D8*(1-20%)”。 （3）按Crtl+Shift+Enter組合鍵。 此外，當對結構相同的不同工作表資料進行合并彙總處理時，利用上述方法也将是非常友善的。有關不同工作表單元格的引用可參閱第1章的有關内容，關于資料的合并計算可參閱本章2.3.5節的内容。 2.1.2 常用函數及其應用在第1章中介紹了一些有關函數的基本知識，本節對在财務管理中常用的一般函數應用進行說明，其他有關的專門财務函數将在以後的有關章節中分别予以介紹。2.1.2.1 SUM函數、SUMIF函數和SUMPRODUCT函數在财務管理中，應用最多的是求和函數。求和函數有三個：無條件求和SUM函數、條件求和SUMIF函數和多組資料相乘求和SUMPRODUCT函數。 1．無條件求和SUM函數 該函數是求30個以内參數的和。公式為 = SUM（參數1，參數2，…，參數N） 當對某一行或某一列的連續資料進行求和時，還可以使用工具欄中的自動求和按鈕 。 例如，在例2-1中，求全年的銷售量，則可以單擊單元格N2，然後再單擊求和按鈕 圖2-13 自動求和 2．條件求和SUMIF函數 SUMIF函數的功能是根據指定條件對若幹單元格求和，公式為 =SUMIF(range,criteria,sum_range) 式中 range—用于條件判斷的單元格區域； criteria—确定哪些單元格将被相加求和的條件，其形式可以為數字、表達式或文本； sum_range—需要求和的實際單元格。 隻有當range中的相應單元格滿足條件時，才對 sum_range 中的單元格求和。如果省略 sum_range,則直接對 range 中的單元格求和。 利用這個函數進行分類彙總是很有用的。 【例2-4】某商場2月份銷售的家電流水記錄如圖2-14所示，則在單元格I3中輸入公式“=SUMIF(C3:C10,211,F3:F10)”，單元格I4中輸入公式“=SUMIF(C3:C10,215,F3:F10)”，在單元格I5中輸入公式“=SUMIF(C3:C10,212,F3:F10)”，單元格I6中輸入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分類銷售額彙總表。 圖2-14 商品銷售額分類彙總 SUMIF函數的對話框如圖2-15所示。 圖2-15 SUMIF函數對話框 當需要分類彙總的資料很大時，利用SUMIF函數是很友善的。 3．SUMPRODUCT函數 SUMPRODUCT函數的功能是在給定的幾組數組中，将數組間對應的元素相乘，并傳回乘積之和。公式為 = SUMPRODUCT(array1,array2,array3,…) 式中，array1,array2,array3,...為1至30個數組。 需注意的是，數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 将傳回錯誤值 #VALUE!。對于非數值型的數組元素将作為0處理。 例如，在例2-2中，要計算2002年産品A的銷售總額，可在任一單元格（比如O2）中輸入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可。 Excel“條件格式”公式執行個體 我們知道，Excel“條件格式”功能可以根據單元格内容有選擇地自動應用格式，它為Excel增色不少的同時，還為我們帶來很多友善。如果讓“條件格式”和公式結合使用，則可以發揮更大的威力，下面提供幾個在“條件格式”中使用公式的應用執行個體，希望能給讀者朋友帶來一些啟發。 一、 判别輸入是否正确 在輸入如身份證等有固定位數的号碼，出現位數不正确的情形時，我們希望Excel能夠給出提示。雖然可以使用“資料有效性”設定實作，但是當輸入出錯時，Excel總會彈出一個提示的對話框，有朋友可能覺得這樣“唐突”的提醒有點影響心情，那就讓“條件格式”來“溫和”的提醒吧。 1、建立“條件格式”的公式 假設我們通過“條件格式”，把符合位數(15位或18位)的号碼所在單元格的填充色設定為綠色，輸入完成後，通過檢視單元格的填充色是否變為綠色，就可以知道輸入的正确性了。 由于身份證号碼資料是屬于“文本”類型的，先選中需要存放身份證号碼的A2:A52單元格區域，将它們的數字格式設定為“文本”。然後在A2:A52單元格區域處于被選中的狀态下，選擇菜單“格式→條件格式”指令，打開“條件格式”對話框，單擊“條件1”下方的下拉箭頭，在彈出的下拉清單中選擇“公式”(圖 1)。 小提示：上面的操作，先選中了一個單元格範圍A2:A52，然後為這個單元格範圍設定條件格式的公式。在這種情況下，公式中應使用選擇範圍中左上單元格的引用，此例中為A2。公式輸入完成後，可以檢視一下這個範圍中的其它單元格的條件格式公式，如A8單元格，為“=OR(LEN(A8)=15,LEN(A8)=18)”，這是由于上面的引用為相對應用，它會根據單元格的實際偏移量自動改變，進而得到适合其它單元格的公式。 2、實作的具體效果 現在來測試一下上面設定可以實作的效果，在A2：A52區域的單元格中輸入一些身份證号碼，當位數是18位或15位時，所在單元格的填充色自動變為“綠色”，而位數不對的身份證号碼，所在單元格的填充色不發生任何改變(圖 4)，從是否變色我們就可以判斷輸入的正确性了。 輸入資料時禁止輸入空格 在Excel中輸入資料時如果不允許輸入空格，可通過設定資料的有效性來實作。單擊“資料”菜單中的“有效性”指令，将有效性條件設為“自定義”，在公式框中輸入“=COUNTIF（F1，"* *")=0”（假設不允許在F列中輸入空格），點“确定”後即可；如果隻是不允許在單元格的開始輸入空格，其他地方可以，隻要将公式改為“=COUNTIF（F1，" *")=0”；如果隻是不允許在單元格的最後輸入空格，隻要将公式改為“=COUNTIF（F1，"* ")=0”即可。 Excel也能統計字數 想在Excel中跟word一樣統計字數嗎？試試數組公式“{=SUM（LEN（範圍）)}”吧，方法是輸入公式“=SUM（LEN（單元格區域範圍）)”後按“Ctrl+Shift+Enter”組合鍵，怎麼樣？看到統計結果了吧。 |
http://hi.baidu.com/hbezniu/blog/item/63cef7111d0107c6a6ef3f66.html