Excel表格自動計算技巧6頁

1、Excel表格自動計算技巧 一、顯示單元格例有計算式的結果的設置方法首先：插入-名稱-定義在彈出的對話框“當前工作薄的名稱”中輸入: X或“結果”的自定義名稱，再在“引用位置”處粘貼EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(計算!$C$1,(),),*),/)公式(注意要有“”號。再對公式中“計算!$C$1”選擇上，然后再到需要做公式的單元格中點擊即可。如要相對引用，則要刪除$字符。已經OK，你在 C1輸入表達式 比如 15+53 ，在D1中輸入 =x 看看(應該是30)。二、如何在Excel中輸入計算式后另一單元顯示計算結果菜單

2、-插入-名稱-定義：輸入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的計算公式變化，單

3、元格E1顯示的計算表達式也隨之變化。第二種情況：是當計算的單元格任意變化時，怎么辦？比如說計算式E1=A1+B1+C1+D1，也有D2=A2+B2+C2，還有F3=A1+B1+C1+D1+E1時。如何將計算式變?yōu)橛嬎惚磉_式。即計算式可以在任意一個單元格，計算公式所引用數據的單元格可以任意變化，需要顯示計算表達式的單元格也是任意的。針對第一種問題，分兩步做：1，自定義一個函數Public Function K(x As Range)K = Right(x.Formula, Len(x.Formula) - 1)End Function自定義函數操作過程如下：工具宏Visual Basic編輯器右

4、擊插入模塊再將上面的函數粘貼進去即可。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)。工具-宏-安全性 里面選擇 無（不建議使用），這樣加載宏的時候系統(tǒng)不會每次都提示。*自定義函數也可以換成使用宏表函數 GET.CELL()第二個問題，請同理照推四、 編輯技巧1 分數的輸入如果直接輸入“1/5”，系統(tǒng)會將其變?yōu)椤?月5日”

5、，解決辦法是：先輸入“0”，然后輸入空格，再輸入分數“1/5”。2 序列“001”的輸入如果直接輸入“001”，系統(tǒng)會自動判斷001為數據1，解決辦法是：首先輸入“”（西文單引號），然后輸入“001”。3 日期的輸入如果要輸入“4月5日”，直接輸入“4/5”，再敲回車就行了。如果要輸入當前日期，按一下“Ctrl+；”鍵。在Excel中填寫表格時,怎樣在一格中換行填字?輸入一行后按Alt+Enter就可以換行5自動切換輸入法在一張工作表中，往往是既有數據，又有文字，這樣在輸入時就需要來回在中英文之間反復切換輸入法，非常麻煩。 如果你要輸入的東西很有規(guī)律性，比如這一列全是單詞，下一列全是漢語解釋，

6、你可以用以下方法實現自動切換。方法是：（1）選中要輸入英文的列，單擊“數據”菜單，選擇“有效性.”命令，在彈出的“數據有效性”對話框中，選中“輸入法模式”選項卡，在“模式”框中選擇“關閉（英文模式）”命令，單擊“確定”按鈕（如圖12）。（2）選中要輸入漢字的列，在“有效數據”對話框中，單擊“IME模式”選項卡，在“模式”框中選擇“打開”命令，單擊“確定”按鈕。這樣，當光標在前一列時，可以輸入英文，在下一列時，直接可以輸入中文，從而實現了中英文輸入方式之間的自動切換。6、把數據徹底隱藏起來工作表部分單元格中的內容不想讓瀏覽者查閱，只好將它隱藏起來了。1.選中需要隱藏內容的單元格(區(qū)域)，執(zhí)行“格

7、式單元格”命令，打開“單元格格式”對話框，在“數字”標簽的“分類”下面選中“自定義”選項，然后在右邊“類型”下面的方框中輸入“；”(三個英文狀態(tài)下的分號)。2.再切換到“保護”標簽下，選中其中的“隱藏”選項，按“確定”按鈕退出。3.執(zhí)行“工具保護保護工作表”命令，打開“保護工作表”對話框，設置好密碼后，“確定”返回。經過這樣的設置以后，上述單元格中的內容不再顯示出來，就是使用Excel的透明功能也不能讓其現形。提示：在“保護”標簽下，請不要清除“鎖定”前面復選框中的“”號，這樣可以防止別人刪除你隱藏起來的數據。7、在Excel中自定義函數Excel函數雖然豐富，但并不能滿足我們的所有需要。我們

8、可以自定義一個函數，來完成一些特定的運算。下面，我們就來自定義一個計算梯形面積的函數：1.執(zhí)行“工具宏Visual Basic編輯器”菜單命令(或按“Alt+F11”快捷鍵)，打開Visual Basic編輯窗口。2.在窗口中，執(zhí)行“插入模塊”菜單命令，插入一個新的模塊模塊1。3.在右邊的“代碼窗口”中輸入以下代碼：Function V(a,b,h)V = h*(a+b)/2End Function4.關閉窗口，自定義函數完成。以后可以像使用內置函數一樣使用自定義函數。提示：用上面方法自定義的函數通常只能在相應的工作簿中使用。8、單元格內容的合并解決辦法是：在C行后插入一個空列（如果D列沒有內

9、容，就直接在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列

10、合并成C列，在C1中輸入：=A1&，&B1，此時C1中的數據形式就符合要求了，再用鼠標向下拖動C1單元格，完成對A列和B列的所有內容的合并（如圖3-4）。合并不同單元格的內容，還有一種方法是利用CONCATENATE函數，此函數的作用是將若干文字串合并到一個字串中，具體操作為“=CONCATENATE（B1,C1）”。比如，假設在某一河流生態(tài)調查工作表中，B2包含“物種”、B3包含“河鱒魚”，B7包含總數45，那么： 輸入“=CONCATENATE（本次河流生態(tài)調查結果：，B2，B3，為，B7，條/公里。）” 計算結果為：本次河流生態(tài)調查結果：河鱒魚物種為45條/公里。9、條件顯示我們知道，利

11、用If函數，可以實現按照條件顯示。一個常用的例子，就是教師在統(tǒng)計學生成績時，希望輸入60以下的分數時，能顯示為“不及格”；輸入60以上的分數時，顯示為“及格。這樣的效果，利用IF函數可以很方便地實現。 假設成績在A2單元格中，判斷結果在A3單元格中。那么在A3單元格中輸入公式： =if（A260，“不及格”，“及格”） 同時，在IF函數中還可以嵌套IF函數或其它函數。例如，如果輸入： =if（A260，“不及格”，if（A2=90，“及格”，“優(yōu)秀） 就把成績分成了三個等級。如果輸入=if（A260，“差，if（A2=70，“中”，if（A20，SUM（A1：A5），0） 此式就利用了嵌套函數

12、，意思是，當A1至A5的和大于0時，返回這個值，如果小于0，那么就返回0。 還有一點要提醒你注意：以上的符號均為半角，而且IF與括號之間也不能有空格。創(chuàng)建條件格式可以使用六種邏輯符號來設計一個條件格式：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、（不等于），如果你覺得這些符號不好記，就干脆使用“”或“=”號來表示。11、在xcel中小數點無法輸入，按小數點，顯示的卻是逗號，無論怎樣設置選項都無濟于事，該怎么辦？這是一個比較特殊的問題，我曾為此花了十幾個小時的時間，但說白了很簡單。在indows的控制面板中，點擊“區(qū)域設置”圖標，在彈出的“區(qū)域設置屬性”對話面板上在“區(qū)域設置

13、”里選擇“中文（中國）”，在“區(qū)域設置屬性”對話面板上在“數字”屬性里把小數點改為“.”（未改前是“，”），按“確定”按鈕結束。這樣再打開Excel就一切都正常了。12、如何快速地將數字作為文本輸入？在輸入數字前加一個單引號“”，可以強制地將數字作為文本輸入。13、如何對工作簿進行安全保護？如果你不想別人打開或修改你的工作簿，那么想法加個密碼吧。打開工作薄，選擇“文件”菜單中的“另存為”命令，選取“選項”，根據用戶的需要分別輸入“打開文件口令”或“修改文件D令”，按“確定”退出。工作簿（表）被保護之后，還可對工作表中某些單元格區(qū)域的重要數據進行保護，起到雙重保護的功能，此時你可以這樣做：首先，選定需保護的單元格區(qū)域，選取“格式”菜單中的“單元格”命令，選取“保護”，從對話框中選取“鎖定”，單由“確定”按鈕退出。然后選取“工具”菜單中的“保護”命令，選取“保護工作表”，根據提示兩次輸入口令后退出。注意：不要忘記你設置有“口令”。14、工作表保護的口令忘記了怎么辦？如果你想使用一個保護了的工作表，但口令又忘記了，有辦法嗎？有。選定工作表，選擇“編輯”“復制”、“粘貼”，將其拷貝到一個新的工作薄中（注意：一定要是新工作簿），即可超越工作表保護。當然，提醒你最好不用這種方法盜用他人的工作表。15、如何命名常數？有時，為常數指定一個名字可以節(jié)省在整個工作簿中修改替換此常數的時間。例如，在