Excel表格自動計(jì)算技巧

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

2、何在 Excel 中輸入計(jì)算式后另一單元顯示計(jì)算結(jié)果菜單 -插入 -名稱 -定義：輸入 AA( 任意取名 )，在下面輸入公式 :=EVALUATE($A$1)然后在 B1 單元格輸入公式： =AA但本式不能識別如： 、等符號進(jìn)行計(jì)算，使用第一種較好。三、如何在 Excel 中編寫自定義函數(shù)，象在表格中調(diào)用SUM() 一樣？第一種情況：單元格A1 = 2；單元格B1 = 2；單元格C1 = 2單元格D1=(A1+B1)C1 顯示結(jié)果為8 ；那么如何才能在單元格 E1 中顯示 (2+2) 2 的計(jì)算表達(dá)式，并且建立關(guān)聯(lián)，當(dāng)單元格 D1 變成 =(A1+BA)C1 計(jì)算式后，顯示結(jié)果為 16；那么

3、E1 也就自動顯示為 (2+2)2 的計(jì)算表達(dá)式， 也就是說隨著單元格 D1 的計(jì)算公式變化， 單元格 E1 顯示的計(jì)算表達(dá)式也隨之變化。第二種情況：是當(dāng)計(jì)算的單元格任意變化時(shí)，怎么辦？比如說計(jì)算式 E1=A1+B1+C1+D 1，也有 D2=A2+B2+C2 ，還有 F3=A1+B1+C1+D1+E1 時(shí)。如何將計(jì)算式變?yōu)橛?jì)算表達(dá)式。即計(jì)算式可以在任意一個(gè)單元格， 計(jì)算公式所引用數(shù)據(jù)的單元格可以任意變化，需要顯示計(jì)算表達(dá)式的單元格也是任意的。針對第一種問題，分兩步做：1 / 6.1，自定義一個(gè)函數(shù)Public FunctionK(x As Range)K = Right(x.Formula,

4、Len(x.Formula)- 1)End Function自定義函數(shù)操作過程如下：工具宏 Visual Basic 編輯器右擊插入模塊再將上面的函數(shù)粘貼進(jìn)去即可。2，在 E1 中寫入公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(k(D1),A&ROW(A1),A1),B &ROW(B1),B1),C&ROW(C1),C1)其它往下拖拉即可。 I 當(dāng)需要增加幾個(gè)參數(shù)應(yīng)用的單元格時(shí)，相應(yīng)要增加幾個(gè)： SUBSTITUTE( ，再相應(yīng)增加幾個(gè)如： ,F&ROW(F1),F1) 。工具 -宏 -安全性 里面選擇 無（不建議使用），這樣加載宏的時(shí)候系統(tǒng)不會每次都提示。*自定義

5、函數(shù)也可以換成使用宏表函數(shù)GET.CELL()第二個(gè)問題，請同理照推四、 編輯技巧1 分?jǐn)?shù)的輸入如果直接輸入 “1/5，”系統(tǒng)會將其變?yōu)?“1月 5 日”，解決辦法是：先輸入 “0，”然后輸入空格，再輸入分?jǐn)?shù) “1/5?！? 序列 “ 001的”輸入如果直接輸入 “001，”系統(tǒng)會自動判斷 001 為數(shù)據(jù) 1，解決辦法是：首先輸入 “（”西文單引號），然后輸入 “001?！? 日期的輸入如果要輸入 “4月 5 日”，直接輸入 “4/5，”再敲回車就行了。如果要輸入當(dāng)前日期，按一下 “Ctrl+； ”鍵。在 Excel 中填寫表格時(shí) ,怎樣在一格中換行填字 ?2 / 6.輸入一行后按 Alt+E

6、nter 就可以換行5 自動切換輸入法在一張工作表中， 往往是既有數(shù)據(jù)， 又有文字，這樣在輸入時(shí)就需要來回在中英文之間反復(fù)切換輸入法，非常麻煩。 如果你要輸入的東西很有規(guī)律性，比如這一列全是單詞， 下一列全是漢語解釋， 你可以用以下方法實(shí)現(xiàn)自動切換。 方法是：（1）選中要輸入英文的列，單擊 “數(shù)據(jù) ”菜單，選擇 “有效性 . 命”令，在彈出的 “數(shù)據(jù)有效性 ”對話框中， 選中 “輸入法模式 ”選項(xiàng)卡，在 “模式 ”框中選擇 “關(guān)閉（英文模式） ”命令，單擊 “確定 ”按鈕（如圖 12）。（2）選中要輸入漢字的列， 在“有效數(shù)據(jù) ”對話框中，單擊 “IME模式 ”選項(xiàng)卡，在 “模式 ”框中選擇

7、“打開 ”命令，單擊 “確定 ”按鈕。這樣，當(dāng)光標(biāo)在前一列時(shí)，可以輸入英文，在下一列時(shí)， 直接可以輸入中文， 從而實(shí)現(xiàn)了中英文輸入方式之間的自動切換。6、把數(shù)據(jù)徹底隱藏起來工作表部分單元格中的內(nèi)容不想讓瀏覽者查閱，只好將它隱藏起來了。1.選中需要隱藏內(nèi)容的單元格 (區(qū)域 )，執(zhí)行 “格式 單元格 ”命令，打開 “單元格格式 ”對話框， 在“數(shù)字 ”標(biāo)簽的 “分類 ”下面選中 “自定義 ”選項(xiàng)，然后在右邊 “類型 ” 下面的方框中輸入 “； ”(三個(gè)英文狀態(tài)下的分號 )。2.再切換到 “保護(hù) ”標(biāo)簽下，選中其中的 “隱藏 ”選項(xiàng)，按 “確定 ”按鈕退出。3.執(zhí)行 “工具 保護(hù) 保護(hù)工作表 ”命令

8、，打開 “保護(hù)工作表 ”對話框，設(shè)置好密碼后， “確定 ”返回。經(jīng)過這樣的設(shè)置以后，上述單元格中的內(nèi)容不再顯示出來，就是使用 Excel 的透明功能也不能讓其現(xiàn)形。提示：在“保護(hù) ”標(biāo)簽下， 請不要清除 “鎖定 ”前面復(fù)選框中的 “”號，這樣可以防止別人刪除你隱藏起來的數(shù)據(jù)。7、在 Excel 中自定義函數(shù)Excel 函數(shù)雖然豐富，但并不能滿足我們的所有需要。我們可以自定義一個(gè)函數(shù)，來完成一些特定的運(yùn)算。 下面，我們就來自定義一個(gè)計(jì)算梯形面積的函數(shù)：1.執(zhí)行 “工具 宏 Visual Basic 編輯器 ”菜單命令 (或按 “ Alt+F11快”捷鍵 )，打開 Visual Basic 編輯窗

9、口。2.在窗口中，執(zhí)行 “插入 模塊 ”菜單命令，插入一個(gè)新的模塊 模塊 1。3 / 6.3.在右邊的 “代碼窗口 ”中輸入以下代碼：FunctionV(a,b,h)V= h*(a+b)/2EndFunction4.關(guān)閉窗口，自定義函數(shù)完成。以后可以像使用內(nèi)置函數(shù)一樣使用自定義函數(shù)。提示：用上面方法自定義的函數(shù)通常只能在相應(yīng)的工作簿中使用。8、單元格內(nèi)容的合并解決辦法是：在 C 行后插入一個(gè)空列（如果 D 列沒有內(nèi)容，就直接在 D 列操作），在 1 中輸入 “=B1&C1”，D1 列的內(nèi)容就是 B、 C 兩列的和了。選中 D 1 單元格，用鼠標(biāo)指向單元格右下角的小方塊 “”，當(dāng)光標(biāo)變成 后，按

10、住鼠標(biāo)拖動光標(biāo)向下拖到要合并的結(jié)尾行處，就完成了 B 列和 C 列的合并。這時(shí)先不要忙著把 B 列和 C 列刪除，先要把 D 列的結(jié)果復(fù)制一下，再用 “選擇性粘貼 ”命令，將數(shù)據(jù)粘貼到一個(gè)空列上。這時(shí)再刪掉 B、 C、D 列的數(shù)據(jù)。下面是一個(gè) “&實(shí)”際應(yīng)用的例子。用 AutoCAD 繪圖時(shí)，有人喜歡在 EXCEL 中存儲坐標(biāo)點(diǎn)，在繪制曲線時(shí)調(diào)用這些參數(shù)。存放數(shù)據(jù)格式為 “x，y”的形式，首先在 Excel 中輸入坐標(biāo)值， 將 x 坐標(biāo)值放入 A 列，y 坐標(biāo)值放入到 B 列，然后利用 “&將” A 列和 B 列合并成 C 列，在 C1 中輸入： =A1& ， &B1 ，此時(shí) C1 中的數(shù)據(jù)形

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

12、們知道，利用 If 函數(shù)，可以實(shí)現(xiàn)按照條件顯示。一個(gè)常用的例子，就是教師在統(tǒng)計(jì)學(xué)生成績時(shí)，希望輸入 60 以下的分?jǐn)?shù)時(shí)，能顯示為 “不及格 ”；輸入 60 以上的分?jǐn)?shù)時(shí)，顯示為 “及格 。這樣的效果，利用 IF 函數(shù)可以很方便地實(shí)現(xiàn)。假設(shè)成績在 A2 單元格中，判斷結(jié)果在 A3 單元格中。那么在 A3 單元格中輸入公式： =if（ A260 ， “不及格 ”，“及格 ”） 同時(shí)，在 IF 函數(shù)中還可以嵌套 IF 函數(shù)或其它函數(shù)。例如，如果輸入： =if（ A260 ，“不及格 ”，if（ A2=90 ，“及格 ”，“優(yōu)秀 ）就把成績分成了三個(gè)等級。4 / 6.如果輸入 =if（A260 ，“差

13、 ，if（A2=70 ，“中”，if（ A20 ，SUM （A1 ：A5 ）， 0） 此式就利用了嵌套函數(shù)，意思是，當(dāng)A1 至 A5 的和大于 0 時(shí)，返回這個(gè)值，如果小于0，那么就返回 0。 還有一點(diǎn)要提醒你注意：以上的符號均為半角，而且IF 與括號之間也不能有空格。創(chuàng)建條件格式可以使用六種邏輯符號來設(shè)計(jì)一個(gè)條件格式： （大于）、 =（大于等于）、（小于）、 =（小于等于）、 =（等于）、 （不等于），如果你覺得這些符號不好記，就干脆使用 “或”“=號”來表示。11、在 xcel 中小數(shù)點(diǎn)無法輸入，按小數(shù)點(diǎn)，顯示的卻是逗號，無論怎樣設(shè)置選項(xiàng)都無濟(jì)于事，該怎么辦？這是一個(gè)比較特殊的問題， 我曾

14、為此花了十幾個(gè)小時(shí)的時(shí)間， 但說白了很簡單。在 indows 的控制面板中，點(diǎn)擊“區(qū)域設(shè)置”圖標(biāo)，在彈出的“區(qū)域設(shè)置屬性”對話面板上在“區(qū)域設(shè)置”里選擇“中文（中國）”，在“區(qū)域設(shè)置屬性”對話面板上在“數(shù)字”屬性里把小數(shù)點(diǎn)改為“ .”（未改前是“，”），按“確定”按鈕結(jié)束。這樣再打開 Excel 就一切都正常了。12、如何快速地將數(shù)字作為文本輸入？在輸入數(shù)字前加一個(gè)單引號“”，可以強(qiáng)制地將數(shù)字作為文本輸入。13、如何對工作簿進(jìn)行安全保護(hù)？如果你不想別人打開或修改你的工作簿， 那么想法加個(gè)密碼吧。打開工作薄，選擇“文件”菜單中的“另存為”命令，選取“選項(xiàng)”，根據(jù)用戶的需要分別輸入“打開文件口令”

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