Excel表格自動計算技巧

Excel表格自動計算技巧

一、顯示單元格例有計算式的結果的設置方法

首先：插入-名稱-定義在彈出的對話框「當前工作薄的名稱」中輸入: X或「結果」的自定義名稱，再在「引用位置」處粘貼＝EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(計算!$C$1,"[","("),"]",")"),"×","*"),"÷","/"))　公式(注意要有「＝」號。再對公式中「計算!$C$1」選擇上，然後再到需要做公式的單元格中點擊即可。如要相對引用，則要刪除$字元。

已經OK，你在 C1輸入表達式 比如 15+5×3 ，在D1中輸入 =x 看看(應該是30)。

二、如何在Excel中輸入計算式後另一單元顯示計算結果

菜單--插入--名稱--定義：輸入AA(任意取名)，在下面輸入公式:

=EVALUATE($A$1)

然後在B1單元格輸入公式：=AA

但本式不能識別如：[　、×、÷　等符號進行計算，使用第一種較好。

三、如何在Excel中編寫自定義函數，象在表格中調用SUM()一樣？

第一種情況：

單元格 A1 = 2；單元格 B1 = 2；單元格 C1 = 2

單元格 D1=(A1+B1)×C1 顯示結果為 8 ；

那麼如何才能在單元格 E1中顯示(2+2)×2的計算表達式，並且建立關聯，當單元格D1變成 =(A1+BA)^C1 計算式後，顯示結果為16；那麼E1也就自動顯示為 (2+2)^2的計算表達式，也就是說隨著單元格D1的計算公式變化，單元格E1顯示的計算表達式也隨之變化。

第二種情況：

是當計算的單元格任意變化時，怎麼辦？比如說計算式E1=A1+B1+C1+D1，也有D2=A2+B2+C2，還有F3=A1+B1+C1+D1+E1時。如何將計算式變為計算表達式。即計算式可以在任意一個單元格，計算公式所引用數據的單元格可以任意變化，需要顯示計算表達式的單元格也是任意的。

針對第一種問題，分兩步做：

1，自定義一個函數

Public Function K(x As Range)

K = Right(x.Formula, Len(x.Formula) - 1)

End Function

自定義函數操作過程如下：

工具－宏－Visual Basic編輯器－右擊－插入模塊－再將上面的函數粘貼進去即可。

2，在E1中寫入公式

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(k(D1),"A"&ROW(A1),A1),"B"&ROW(B1),B1),"C"&ROW(C1),C1)

其它往下拖拉即可。I當需要增加幾個參數應用的單元格時，相應要增加幾個：SUBSTITUTE(　，再相應增加幾個如：,"F"&ROW(F1),F1)　。

工具--》宏--》安全性 裡面選擇 無（不建議使用），這樣載入宏的時候系統不會每次都提示。

**自定義函數也可以換成使用宏表函數 GET.CELL()

第二個問題，請同理照推

四、 編輯技巧

1 　分數的輸入

如果直接輸入「1/5」，系統會將其變為「1月5日」，解決辦法是：先輸入「0」，然後輸入空格，再輸入分數「1/5」。

2 　序列「001」的輸入

如果直接輸入「001」，系統會自動判斷001為數據1，解決辦法是：首先輸入「"」（西文單引號），然後輸入「001」。

3 　日期的輸入

如果要輸入「4月5日」，直接輸入「4/5」，再敲回車就行了。如果要輸入當前日期，按一下「Ctrl+；」鍵。

在Excel中填寫表格時,怎樣在一格中換行填字?

輸入一行後按Alt+Enter就可以換行

5　自動切換輸入法

在一張工作表中，往往是既有數據，又有文字，這樣在輸入時就需要來回在中英文之間反覆切換輸入法，非常麻煩。 如果你要輸入的東西很有規律性，比如這一列全是單詞，下一列全是漢語解釋，你可以用以下方法實現自動切換。方法是：

（1）選中要輸入英文的列，單擊「數據」菜單，選擇「有效性...」命令，在彈出的「數據有效性」對話框中，選中「輸入法模式」選項卡，在「模式」框中選擇「關閉（英文模式）」命令，單擊「確定」按鈕（如圖12）。

（2）選中要輸入漢字的列，在「有效數據」對話框中，單擊「IME模式」選項卡，在「模式」框中選擇「打開」命令，單擊「確定」按鈕。這樣，當游標在前一列時，可以輸入英文，在下一列時，直接可以輸入中文，從而實現了中英文輸入方式之間的自動切換。

6、把數據徹底隱藏起來

工作表部分單元格中的內容不想讓瀏覽者查閱，只好將它隱藏起來了。

1.選中需要隱藏內容的單元格(區域)，執行「格式→單元格」命令，打開「單元格格式」對話框，在「數字」標籤的「分類」下面選中「自定義」選項，然後在右邊「類型」下面的方框中輸入「；；；」(三個英文狀態下的分號)。

2.再切換到「保護」標籤下，選中其中的「隱藏」選項，按「確定」按鈕退出。

3.執行「工具→保護→保護工作表」命令，打開「保護工作表」對話框，設置好密碼後，「確定」返回。

經過這樣的設置以後，上述單元格中的內容不再顯示出來，就是使用Excel的透明功能也不能讓其現形。

提示：在「保護」標籤下，請不要清除「鎖定」前面複選框中的「∨」號，這樣可以防止別人刪除你隱藏起來的數據。

7、在Excel中自定義函數　　Excel函數雖然豐富，但並不能滿足我們的所有需要。我們可以自定義一個函數，來完成一些特定的運算。下面，我們就來自定義一個計算梯形面積的函數：

1.執行「工具→宏→Visual Basic編輯器」菜單命令(或按「Alt+F11」快捷鍵)，打開Visual Basic編輯窗口。

2.在窗口中，執行「插入→模塊」菜單命令，插入一個新的模塊——模塊1。

3.在右邊的「代碼窗口」中輸入以下代碼：

Function V(a,b,h)V = h*(a+b)/2End Function

4.關閉窗口，自定義函數完成。

以後可以像使用內置函數一樣使用自定義函數。

提示：用上面方法自定義的函數通常只能在相應的工作簿中使用。

8、單元格內容的合併

解決辦法是：在C行後插入一個空列（如果D列沒有內容，就直接在D列操作），在Ｄ1中輸入「=B1&C1」，D1列的內容就是B、C兩列的和了。選中D1單元格，用滑鼠指向單元格右下角的小方塊「」，當游標變成"＋"後，按住滑鼠拖動游標向下拖到要合併的結尾行處，就完成了B列和C列的合併。這時先不要忙著把B列和C列刪除，先要把D列的結果複製一下，再用「選擇性粘貼」命令，將數據粘貼到一個空列上。這時再刪掉B、C、D列的數據。

下面是一個「&」實際應用的例子。用AutoCAD繪圖時，有人喜歡在EXCEL中存儲坐標點，在繪製曲線時調用這些參數。存放數據格式為「x，y」的形式，首先在Excel中輸入坐標值，將x坐標值放入A列，y坐標值放入到B列，然後利用「&」將A列和B列合併成C列，在C1中輸入：=A1&"，"&B1，此時C1中的數據形式就符合要求了，再用滑鼠向下拖動C1單元格，完成對A列和B列的所有內容的合併（如圖3-4）。

合併不同單元格的內容，還有一種方法是利用CONCATENATE函數，此函數的作用是將若干文字串合併到一個字串中，具體操作為「=CONCATENATE（B1,C1）」。比如，假設在某一河流生態調查工作表中，B2包含「物種」、B3包含「河鱒魚」，B7包含總數45，那麼： 輸入「=CONCATENATE（"本次河流生態調查結果："，B2，""，B3，"為"，B7，"條/公里。"）」 計算結果為：本次河流生態調查結果：河鱒魚物種為45條/公里。

9、條件顯示

我們知道，利用If函數，可以實現按照條件顯示。一個常用的例子，就是教師在統計學生成績時，希望輸入60以下的分數時，能顯示為「不及格」；輸入60以上的分數時，顯示為「及格"。這樣的效果，利用IF函數可以很方便地實現。 假設成績在A2單元格中，判斷結果在A3單元格中。那麼在A3單元格中輸入公式： =if（A2<60，「不及格」，「及格」） 同時，在IF函數中還可以嵌套IF函數或其它函數。

例如，如果輸入： =if（A2<60，「不及格」，if（A2<=90，「及格」，「優秀"）） 就把成績分成了三個等級。

如果輸入　=if（A2<60，「差"，if（A2<=70，「中」，if（A2<90，「良」，「優」））） 就把成績分為了四個等級。

再比如，公式： =if（SUM（A1：A5>0，SUM（A1：A5），0） 此式就利用了嵌套函數，意思是，當A1至A5的和大於0時，返回這個值，如果小於0，那麼就返回0。 還有一點要提醒你注意：以上的符號均為半形，而且IF與括弧之間也不能有空格。

創建條件格式

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

11、在Ｅxcel中小數點無法輸入，按小數點，顯示的卻是逗號，無論怎樣設置選項都無濟於事，該怎麼辦？

這是一個比較特殊的問題，我曾為此花了十幾個小時的時間，但說白了很簡單。在Ｗindows的控制面板中，點擊「區域設置」圖標，在彈出的「區域設置屬性」對話面板上在「區域設置」里選擇「中文（中國）」，在「區域設置屬性」對話面板上在「數字」屬性里把小數點改為「.」（未改前是「，」），按「確定」按鈕結束。這樣再打開Excel就一切都正常了。

12、如何快速地將數字作為文本輸入？

在輸入數字前加一個單引號「」』，可以強制地將數字作為文本輸入。

13、如何對工作簿進行安全保護？

如果你不想別人打開或修改你的工作簿，那麼想法加個密碼吧。打開工作薄，選擇「文件」菜單中的「另存為」命令，選取「選項」，根據用戶的需要分別輸入「打開文件口令」或「修改文件D令」，按「確定」退出。工作簿（表）被保護之後，還可對工作表中某些單元格區域的重要數據進行保護，起到雙重保護的功能，此時你可以這樣做：首先，選定需保護的單元格區域，選取「格式」菜單中的「單元格」命令，選取「保護」，從對話框中選取「鎖定」，單由「確定」按鈕退出。然後選取「工具」菜單中的「保護」命令，選取「保護工作表」，根據提示兩次輸入口令後退出。

注意：不要忘記你設置有「口令」。

14、工作表保護的口令忘記了怎麼辦？

如果你想使用一個保護了的工作表，但口令又忘記了，有辦法嗎？有。選定工作表，選擇「編輯」＼「複製」、「粘貼」，將其拷貝到一個新的工作薄中（注意：一定要是新工作簿），即可超越工作表保護。當然，提醒你最好不用這種方法盜用他人的工作表。

15、如何命名常數？

有時，為常數指定一個名字可以節省在整個工作簿中修改替換此常數的時間。例如，在某個工作表中經常需用利率4.9％來計算利息，可以選擇「插入」＼「名字」＼「定 義」，在「當前工作薄的名字」框內輸入「利率」，在「引用位置」框中輸入「＝ 0.04.9」，按「確定」按鈕。

16、如何減少重複勞動？

我們在實際應用Excel時，經常遇到有些操作重複應用（如定義上下標等）。為了減少重複勞動，我們可以把一些常用到的操作定義成宏。其方法是：選取「工具」菜單中的「宏」命令，執行「記錄新宏」，記錄好後按「停止」按鈕即可。也可以用VBA編程定義宏。

推薦閱讀：