Excel表格自動計算技巧

1、Excel 表格自動計算技巧一、顯示單元格例有計算式的結(jié)果的設(shè)置方法首先：插入 - 名稱 - 定義在彈出的對話框“當前工作薄的名稱”中輸入 : X 或“結(jié)果”的自定義名稱，再在“引用位置”處粘貼 EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(計算 !$C$1,"","("),"",")"),"×","*"),"÷ ","/")公式 ( 注意要有“”號。再對公

2、式中“計算 !$C$1”選擇上，然后再到需要做公式的單元格中點擊即可。如要相對引用，則要刪除$字符。已經(jīng) OK，你在 C1 輸入表達式比如 15+5 × 3 ，在 D1中輸入 =x看看 ( 應(yīng)該是 30)。二、如何在 Excel 中輸入計算式后另一單元顯示計算結(jié)果菜單 - 插入 - 名稱 - 定義：輸入 AA(任意取名 ) ，在下面輸入公式 :=EVALUATE($A$1)然后在 B1 單元格輸入公式： =AA但本式不能識別如： 、×、÷等符號進行計算，使用第一種較好。三、如何在 Excel 中編寫自定義函數(shù)，象在表格中調(diào)用SUM()一樣？第一種情況：單元格 A1

3、=2 ；單元格 B1=2 ；單元格 C1=2 單元格 D1=(A1+B1)×C1 顯示結(jié)果為 8 ；那么如何才能在單元格 E1 中顯示 (2+2) ×2 的計算表達式，并且建立關(guān)聯(lián)，當單元格 D1 變成 =(A1+BA)C1 計算式后，顯示結(jié)果為 16；那么 E1 也就自動顯示為 (2+2)2 的計算表達式，也就是說隨著單元格 D1的計算公式變化，單元格 E1 顯示的計算表達式也隨之變化。第二種情況：是當計算的單元格任意變化時，怎么辦？比如說計算式 E1=A1+B1+C1+D1，也有 D2=A2+B2+C2，還有 F3=A1+B1+C1+D1+E1時。如何將計算式變?yōu)橛嬎惚?/p>

4、達式。即計算式可以在任意一個單元格，計算公式所引用數(shù)據(jù)的單元格可以任意變化，需要顯示計算表達式的單元格也是任意的。針對第一種問題，分兩步做：1，自定義一個函數(shù)Public Function K(x As Range)K = Right(x.Formula, Len(x.Formula) - 1)End Function自定義函數(shù)操作過程如下：工具宏 Visual Basic 編輯器右擊插入模塊再將上面的函數(shù)粘貼進去即可。2，在 E1 中寫入公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(k(D1),"A"&ROW(A1),A1),"

5、B"&ROW(B 1),B1),"C"&ROW(C1),C1)其它往下拖拉即可。 I 當需要增加幾個參數(shù)應(yīng)用的單元格時，相應(yīng)要增加幾個： SUBSTITUTE( ，再相應(yīng)增加幾個如： ,"F"&ROW(F1),F1) 。工具 - 宏- 安全性里面選擇無（不建議使用），這樣加載宏的時候系統(tǒng)不會每次都提示。* 自定義函數(shù)也可以換成使用宏表函數(shù) GET.CELL()第二個問題，請同理照推四、 編輯技巧1 分數(shù)的輸入如果直接輸入“ 1/5 ”，系統(tǒng)會將其變?yōu)椤?1 月 5 日”，解決辦法是：先輸入“ 0”，然后輸入空格，再輸入分

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

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

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

9、數(shù)Excel 函數(shù)雖然豐富，但并不能滿足我們的所有需要。我們可以自定義一個函數(shù)，來完成一些特定的運算。 下面，我們就來自定義一個計算梯形面積的函數(shù)：1. 執(zhí)行“工具宏 Visual Basic 編輯器”菜單命令 ( 或按“ Alt+F11 ”快捷鍵 ) ，打開 Visual Basic編輯窗口。2. 在窗口中，執(zhí)行“插入模塊” 菜單命令，插入一個新的模塊模塊1。3. 在右邊的“代碼窗口”中輸入以下代碼：Function V(a,b,h)V = h*(a+b)/2End Function4. 關(guān)閉窗口，自定義函數(shù)完成。以后可以像使用內(nèi)置函數(shù)一樣使用自定義函數(shù)。提示：用上面方法自定義的函數(shù)通常只能

10、在相應(yīng)的工作簿中使用。8、單元格內(nèi)容的合并解決辦法是： 在 C行后插入一個空列 （如果 D 列沒有內(nèi)容， 就直接在 D 列操作），在 1 中輸入“ =B1&C1”， D1列的內(nèi)容就是 B、C 兩列的和了。選中 D1 單元格，用鼠標指向單元格右下角的小方塊“”，當光標變成 " " 后，按住鼠標拖動光標向下拖到要合并的結(jié)尾行處， 就完成了 B 列和 C列的合并。這時先不要忙著把 B 列和 C 列刪除，先要把 D 列的結(jié)果復制一下，再用“選擇性粘貼”命令，將數(shù)據(jù)粘貼到一個空列上。這時再刪掉 B、C、D 列的數(shù)據(jù)。下面是一個“ &”實際應(yīng)用的例子。用AutoCAD繪

11、圖時，有人喜歡在EXCEL中存儲坐標點，在繪制曲線時調(diào)用這些參數(shù)。存放數(shù)據(jù)格式為“ x，y”的形式，首先在 Excel 中輸入坐標值，將 x 坐標值放入 A 列，y 坐標值放入到 B 列，然后利用“ &”將 A 列和 B 列合并成 C列，在 C1 中輸入： =A1&"，"&B1，此時 C1 中的數(shù)據(jù)形式就符合要求了，再用鼠標向下拖動 C1單元格，完成對 A 列和 B 列的所有內(nèi)容的合并（如圖 3-4 ）。合并不同單元格的內(nèi)容， 還有一種方法是利用 CONCATENATE函數(shù)，此函數(shù)的作用是將若干文字串合并到一個字串中， 具體操作為 “=CONCATE

12、NATE（B1,C1）”。比如，假設(shè)在某一河流生態(tài)調(diào)查工作表中， B2 包含“物種” 、B3 包含“河鱒魚” ， B7 包含總數(shù) 45，那么： 輸入“ =CONCATENATE（"本次河流生態(tài)調(diào)查結(jié)果： " ，B2，"" ，B3，" 為" ，B7，" 條 / 公里。 " ）” 計算結(jié)果為：本次河流生態(tài)調(diào)查結(jié)果：河鱒魚物種為 45 條/ 公里。9、條件顯示我們知道，利用 If 函數(shù)，可以實現(xiàn)按照條件顯示。一個常用的例子，就是教師在統(tǒng)計學生成績時，希望輸入 60 以下的分數(shù)時，能顯示為“不及格”；輸入 60 以上的分數(shù)

13、時，顯示為“及格 " 。這樣的效果，利用 IF 函數(shù)可以很方便地實現(xiàn)。 假設(shè)成績在 A2單元格中， 判斷結(jié)果在 A3 單元格中。 那么在 A3 單元格中輸入公式： =if （A2<60，“不及格”，“及格”） 同時，在 IF 函數(shù)中還可以嵌套 IF 函數(shù)或其它函數(shù)。例如，如果輸入： =if （A2<60，“不及格”， if （ A2<=90，“及格”， “優(yōu)秀 " ） 就把成績分成了三個等級。如果輸入 =if （ A2<60，“差 " ，if （A2<=70，“中”， if （ A2<90，“良”，“優(yōu)”） 就把成績分為了四個等

14、級。再比如，公式： =if （ SUM（A1：A5>0，SUM（ A1：A5），0） 此式就利用了嵌套函數(shù)，意思是，當 A1至 A5 的和大于 0 時，返回這個值，如果小于 0，那么就返回 0。 還有一點要提醒你注意：以上的符號均為半角，而且 IF 與括號之間也不能有空格。創(chuàng)建條件格式可以使用六種邏輯符號來設(shè)計一個條件格式： >（大于）、 >=（大于等于）、<（小于）、 <=（小于等于）、 =（等于）、 <>（不等于），如果你覺得這些符號不好記，就干脆使用“ >”或“ >=”號來表示。11、在 xcel 中小數(shù)點無法輸入，按小數(shù)點，顯示的卻

15、是逗號，無論怎樣設(shè)置選項都無濟于事，該怎么辦？這是一個比較特殊的問題， 我曾為此花了十幾個小時的時間， 但說白了很簡單。在 indows 的控制面板中，點擊“區(qū)域設(shè)置”圖標，在彈出的“區(qū)域設(shè)置屬性”對話面板上在“區(qū)域設(shè)置”里選擇“中文（中國）”，在“區(qū)域設(shè)置屬性”對話面板上在“數(shù)字”屬性里把小數(shù)點改為“ . ”（未改前是“，”），按“確定”按鈕結(jié)束。這樣再打開 Excel 就一切都正常了。12、如何快速地將數(shù)字作為文本輸入？在輸入數(shù)字前加一個單引號“”，可以強制地將數(shù)字作為文本輸入。13、如何對工作簿進行安全保護？如果你不想別人打開或修改你的工作簿， 那么想法加個密碼吧。打開工作薄，選擇“文件

16、”菜單中的“另存為”命令，選取“選項”，根據(jù)用戶的需要分別輸入“打開文件口令”或“修改文件 D令”，按“確定”退出。工作簿（表）被保護之后，還可對工作表中某些單元格區(qū)域的重要數(shù)據(jù)進行保護， 起到雙重保護的功能，此時你可以這樣做：首先，選定需保護的單元格區(qū)域，選取“格式”菜單中的“單元格”命令，選取“保護”，從對話框中選取“鎖定”，單由“確定”按鈕退出。然后選取“工具”菜單中的“保護”命令，選取“保護工作表”，根據(jù)提示兩次輸入口令后退出。注意：不要忘記你設(shè)置有“口令”。14、工作表保護的口令忘記了怎么辦？如果你想使用一個保護了的工作表， 但口令又忘記了， 有辦法嗎？有。 選定工作表，選擇“編輯” “復制” 、“粘貼” ，將其拷貝到一個新的工作薄中 （注意：一定要是新工作簿），即可超越工作表保護。當然，提醒你最好不用這種方法盜用他人的工作表。15、如何命名常數(shù)？有時，