EXCEL在工程中的應(yīng)用.doc

EXCEL在工程中的應(yīng)用開講主導(dǎo)思想：大家好！很高興與大家分享我實(shí)踐應(yīng)用EXCEL的一些心得和成果。在開講之前，我想問大家兩個(gè)問題：1、龜兔賽跑，兔子為什么跑不過烏龜？很多人想到了我們小學(xué)的那篇課文龜兔賽跑，兔子打瞌睡了。然而，我的解釋是：兩個(gè)出發(fā)點(diǎn)不一樣，烏龜在跑的同時(shí)，兔子也在跑，它們的目的地是一樣的，兔子雖然跑得快，然而每次跑得只是烏龜跑過的路程，所以最后烏龜勝利。2、兩條平行直線相交于一點(diǎn)？我們都知道，在初中的幾何里，兩條平行直線是不可能相交的。這是歐幾里德站在二維平面的角度說出的，大家不必詫異，這不是個(gè)弱智的問題，大家只是沒有思考過而已。愛因斯坦認(rèn)為光線在送到質(zhì)量大的物體時(shí)，由于引力作用，無法直線行進(jìn)會(huì)相交于一點(diǎn)。這兩個(gè)問題是我講解本課的主導(dǎo)思想：股票市場(chǎng)流傳著這樣一句話,思維有多遠(yuǎn)，財(cái)富就有多近。因循守舊，固步自封是制約我們前進(jìn)的羈絆，思維的革新，創(chuàng)造性的工作帶來卻是我們的自信人生。下面我?guī)ьI(lǐng)大家一起步入EXCEL殿堂，“聞道有先后”，在這里，我只能說有些東西我比大家學(xué)的早，但是不一定比大家認(rèn)識(shí)的深刻，在講得過程中，若有錯(cuò)誤希望大家不吝賜教。前言：EXCEL發(fā)展歷程EXCEL是1985年JENSEN HARRIS帶領(lǐng)MS OFFICE UI開發(fā)團(tuán)隊(duì)開發(fā)的，在數(shù)年的應(yīng)用中，與時(shí)俱進(jìn)，不斷跟進(jìn)社會(huì)的工作效率要求，持續(xù)的改進(jìn)。在EXCEL 5.0版本里出現(xiàn)了VISUAL BASIC FOR APPLICATION，也就是VBA，VBA程序的綁定是EXCEL發(fā)展歷程中的里程碑。它的出現(xiàn)把EXCEL帶入了一個(gè)自由的國度。EXCEL是WINDOWS XP九種軟件中，第一個(gè)綁定VBA的軟件，后來，又在WORD、POWERPOINT、ACCESS、PUBLISHER等八種軟件中綁定了VBA。CAD的開發(fā)者，后來吸取了這些經(jīng)驗(yàn)，也綁定VBA、VLISP、VSCRIPT三種編程語言。EXCEL的發(fā)展歷程我就做這些簡(jiǎn)要的介紹，下面我將分基礎(chǔ)部分、中級(jí)部分、高級(jí)部分三個(gè)部分予以介紹：一、基礎(chǔ)部分：1.1、單元格文本間距調(diào)整單元格的長(zhǎng)度（文本） 32767字符，只有1024個(gè)字符會(huì)顯示在單元格內(nèi)，所有的字符會(huì)顯示在編輯欄，這是因?yàn)樾懈?409，列高0255之間限制。實(shí)例：略1、單擊右鍵-設(shè)置單元格格式-對(duì)齊-水平對(duì)齊、垂直對(duì)齊在下拉菜單中均選擇兩端對(duì)齊-確定。2、強(qiáng)制換行ALT+ENTER。在VBA和VB中，換行是CHR(13)+CHR(10),其中CHR(13)回車，CHR(10)為換行。1.2、符號(hào)的快速錄入實(shí)例：我們經(jīng)常會(huì)錄入平方、立方、人民幣符號(hào)等，最快捷的方法就是： ： ALT+0178 ： ALT+0179 ： ALT+0165： ALT+0215： ALT+0247% ： ALT+0037： ALT+0137 等等，后面的數(shù)字是怎么來的，打開插入-符號(hào)-來自-ASCII(十進(jìn)制) -字符代碼。1.3、照相機(jī)功能實(shí)例：略工具-自定義-工具-照相機(jī)。點(diǎn)擊要照相的區(qū)域，按住CTRL鍵，鼠標(biāo)變?yōu)槭贮c(diǎn)擊任意區(qū)域，可以看到這個(gè)功能相當(dāng)于我們粘貼圖片給表格。1.4、單元格輸入中語言切換我們?cè)贓XCEL單元格輸入中經(jīng)常會(huì)在英文模式和其他文字錄入模式切換，我首先帶領(lǐng)大家把自己經(jīng)常使用的文字錄入模式至首位：方法一、開始-運(yùn)行-REGEDIT-HKEY_CURRENT_USERKEYBOARD LAYOUTPRELOAD在這個(gè)里面更改次序，這個(gè)順序?qū)?yīng)的是你的電腦目前輸入法的順序，更改后不要忘了重新啟動(dòng)電腦。方法二、用超級(jí)兔子-魔法設(shè)置-個(gè)性化設(shè)置-上移、下移或刪除輸入法順序。實(shí)例：略數(shù)據(jù)-數(shù)據(jù)有效性-輸入法-模式-打開。1.5、記憶鍵入法的妙用文本錄入中，經(jīng)常會(huì)出現(xiàn)單元格文本內(nèi)容，很多文字性的東西輸入會(huì)影響工作的效率，可以運(yùn)用記憶鍵入法（ALT+）。實(shí)例：略1.6、常量定義這個(gè)財(cái)務(wù)上經(jīng)常使用，不同稅率，如何在快速的在表格中使用。實(shí)例：略插入-名稱-在當(dāng)前工作表中的名稱-引用位置輸入數(shù)字。1.7、保護(hù)工作表工作表的保護(hù)大家都會(huì)操作：工具-保護(hù)-保護(hù)工作表-兩次錄入密碼。我這里要說的是如果密碼忘記了怎么辦，數(shù)據(jù)又無法編輯。有兩種方法：方法一、重新打開一張新的工作表，把保護(hù)的進(jìn)行復(fù)制，粘貼到新的工作表中；方法二、可以下載PASSWORD RECOVERY進(jìn)行密碼破解，這個(gè)軟件可以破解EXCEL的VBA密碼、表格的保護(hù)密碼。在我寫程序的過程中，我經(jīng)常會(huì)及時(shí)的錄入一段反破解的密碼，這樣用這個(gè)軟件就不會(huì)破解掉。無論怎么說“道高一尺魔高一丈”，需要的還是不斷的學(xué)習(xí)。1.8、去除0值在我們工具-選項(xiàng)-視圖-零值去勾,這個(gè)在計(jì)價(jià)中經(jīng)常使用，比如有些單價(jià)里設(shè)備單價(jià)是等于0，我們可以清除該部分。1.9、條件格式的應(yīng)用格式-條件格式-設(shè)置，可以凸顯數(shù)據(jù)的重點(diǎn)。1.10、數(shù)據(jù)自定義功能我們都知道，文本格式是不能進(jìn)行數(shù)學(xué)運(yùn)算的。我們?cè)趯?shí)際的操作中，這個(gè)功能是個(gè)偉大的功能。實(shí)例：平均斷面法計(jì)算方量對(duì)該單元格應(yīng)用自定義功能，設(shè)置單元格格式-自定義-改為：“30+”0。1.11、超級(jí)鏈接表格之間切換，便于操作。實(shí)例：物資管理程序.xls1.12、輸入相同的數(shù)據(jù)這里有兩個(gè)快捷鍵一個(gè)是應(yīng)用區(qū)域的，一個(gè)是應(yīng)用單元格的。應(yīng)用區(qū)域的是CTRL+SHIFT+ENTER;應(yīng)用單元格的是CTRL+D。實(shí)例：略1.13、分列功能這個(gè)功能用的最多的是在測(cè)量中，我們知道測(cè)量的數(shù)據(jù)一般是SCS數(shù)據(jù)：點(diǎn)號(hào)，Y坐標(biāo)，X坐標(biāo),Z坐標(biāo)。我們一般需要對(duì)數(shù)據(jù)進(jìn)行一定的處理，便于實(shí)際報(bào)驗(yàn)資料，這個(gè)時(shí)候我們用的最多的就是分列功能，下面，我通過實(shí)例講解這個(gè)功能。實(shí)例：略這個(gè)功能，我曾經(jīng)實(shí)現(xiàn)了SET210 TOTAL STATION數(shù)據(jù)的轉(zhuǎn)換。又用C+處理過全站儀數(shù)據(jù)寫成了論文淺談全站儀數(shù)據(jù)轉(zhuǎn)換論文。1.14、快速刪除表格中空行刪除Excel XP工作表中的空行，一般的方法是需要將空行都找出來，然后逐行刪除，但這樣做操作量非常大，很不方便。實(shí)例：略插入-列-插入一新的列X-順序填入整數(shù)-行排序-刪去所有空行中X列的數(shù)據(jù)-以X列重新排序-然后刪去X列。1.15、快速進(jìn)行頁面設(shè)置一個(gè)工作簿中有多個(gè)工作表應(yīng)該是常有的事兒。各個(gè)工作表需要打印時(shí)，怎樣進(jìn)行頁面設(shè)置最快？當(dāng)然是將這些工作表同時(shí)選中同時(shí)設(shè)置最快了。實(shí)例：略方法是在工作表標(biāo)簽處右擊鼠標(biāo)，在彈出的快捷菜單中選擇“選定全部工作表”。然后再點(diǎn)擊菜單命令“文件頁面設(shè)置”就可以對(duì)它們同時(shí)進(jìn)行頁面設(shè)置了.如果要選中的只是部分工作表，那么可以按住“Ctrl”鍵依次單擊各工作表標(biāo)簽。1.16、自動(dòng)更正功能我們經(jīng)常為輸入某些固定的文本而煩惱，到底有沒有一種簡(jiǎn)便的方法呢？回答是有的。實(shí)例：蘭州商貿(mào)有限公司(LZSM)執(zhí)行“工具自動(dòng)更正”命令，打開“自動(dòng)更正”對(duì)話框。在“替換”下面的方框中輸入“LZSM”（也可以是其他字符，“l(fā)zsm”用小寫），在“替換為”下面的方框中輸入“蘭州商貿(mào)有限公司，再單擊“添加”和“確定”按鈕。以后如果需要輸入上述文本時(shí)，只要輸入“LZSM”字符此時(shí)可以不考慮“l(fā)zsm”的大小寫，然后回車就可以了。1.17、繪制雙斜線表頭實(shí)例：略首先，我們?cè)赪ord 2003中“插入表格”工具插入一個(gè)二行二列的表格。選中表格中左上角的單元格，單擊“表格”菜單，在彈出的下拉菜單中單擊“繪制斜線表頭”命令，彈出“繪制斜線表頭”對(duì)話框，該對(duì)話框共提供了五種斜線表頭的樣式，根據(jù)需要選擇一種樣式，并設(shè)置適當(dāng)?shù)淖痔?hào)，然后在行標(biāo)題欄中分別填入各欄的標(biāo)題，完成后單擊“確定”按鈕，則“插入斜線表頭”對(duì)話被關(guān)閉，在被選中的單元格中插入了一個(gè)斜線表頭。 其實(shí)在Word 2003中制作的斜線表頭是預(yù)先制作好的一個(gè)斜線表頭框架，用鼠標(biāo)點(diǎn)擊后會(huì)在四周出現(xiàn)控點(diǎn)。用鼠標(biāo)選中這個(gè)斜線表頭框架，將它復(fù)制到“剪貼板”上。 關(guān)閉Word 2003回到Excel 2003中，將斜線表頭框架粘貼到工作表中并移動(dòng)到要放置斜線表頭的位置。調(diào)整斜線表頭框架及它要覆蓋的單元格的大小，選擇所有被斜線表頭覆蓋的單元格，單擊工具欄上的“合并單元格”按鈕，將它們合并起來，則一個(gè)帶有斜線表頭的Excel工作表制作完畢。1.18、快速拆分窗口實(shí)例：略為方便查看表格的不同部分，我們經(jīng)常要對(duì)工作表進(jìn)行垂直或水平拆分。快速拆分的方法是：先在需要拆分的位置單擊鼠標(biāo)，激活活動(dòng)單元格，然后將鼠標(biāo)指向垂直滾動(dòng)條頂端的拆分框，待鼠標(biāo)指針變成拆分指針時(shí)，雙擊鼠標(biāo)，那么窗口就會(huì)在活動(dòng)單元格上方將窗口水平拆分，再次雙擊拆分框，則拆分取消。1.19、隔行粘貼實(shí)例：略按Ctrl鍵后同時(shí)選中A1、E1、A5、E5四個(gè)單元格，復(fù)制后粘貼會(huì)發(fā)現(xiàn)復(fù)制的內(nèi)容連接到了一起。這樣可以達(dá)到快速復(fù)制不連接單元格的作用。當(dāng)然如果中間有空白單元格則是無法選中的。1.20、查找替換實(shí)例：略1、如果A1單元格中的公式是：=152,值為30。要直接查找30，你肯定查不到！你肯定很奇怪，明明有30，卻查不到！ 原來，excel默認(rèn)按公式進(jìn)行查詢，點(diǎn)擊“選項(xiàng)（T)”按鈕你就明白了，選擇按值查找，就可以找到。2、在我們替換的過程中，比如：我有1000個(gè)數(shù)據(jù)要更改，用系統(tǒng)的查找替換命令一個(gè)個(gè)的改太要時(shí)間，當(dāng)然懂VBA的人肯定不在話下，通過程序很快也就改掉。其實(shí)，只需將范圍切換到“工作簿”，就一切OK了，根本不需要額外的工具！公式運(yùn)算的結(jié)果則必須按“值”進(jìn)行查詢。3、如果想查詢: Abbbc abc abbbbbc,那么在查找內(nèi)容中使用*通配符，即: a*c。 *代表一串字符串?代表一個(gè)字符初級(jí)部分就講到這里，我總共講了20個(gè)被忽略的功能。大家經(jīng)常用的那些功能，我在此不在贅述！下面我們進(jìn)入中級(jí)階段！二、中級(jí)部分 函數(shù)的熟練操作，是你步入中級(jí)殿堂的標(biāo)志。學(xué)過C、C+、VB、VLISP等編程軟件的都知道，函數(shù)是執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。而中級(jí)部分，我們主要講函數(shù)。EXCEL的函數(shù)分為內(nèi)置函數(shù)和擴(kuò)展函數(shù)。內(nèi)置函數(shù)是預(yù)定義好的，擴(kuò)展函數(shù)是運(yùn)用程序二次開發(fā)的。EXCEL的內(nèi)置函數(shù)共分十一類：1、數(shù)據(jù)庫函數(shù)：如DAVERAGE、DMAX等，共13個(gè)；2、日期與時(shí)間函數(shù)：如DATE、NOW等，共20個(gè)；3、外部函數(shù)：如EUROCONVERT等，共2個(gè)；4、工程函數(shù)：BESSELI等，共39個(gè)；5、財(cái)務(wù)函數(shù)： FV等，共52個(gè)；6、信息函數(shù):CELL等，共9個(gè)；7、邏輯運(yùn)算符：AND、NOT等，共6個(gè)；8、查找和引用函數(shù)：ROW、TRANSPOSE等，共17個(gè)；9、數(shù)學(xué)和三角函數(shù)：ABS、ATAN等，共28個(gè)；10、統(tǒng)計(jì)函數(shù)：COUNT、COUNTA等，共80個(gè)；11、文本和數(shù)據(jù)函數(shù)：TEXT、TRIM等，共28個(gè)。以上11類函數(shù)，共294個(gè)。內(nèi)容：1、數(shù)據(jù)庫函數(shù)：（）這類函數(shù)有3個(gè)共同點(diǎn)：1、均有三個(gè)參數(shù)：DATABASE（構(gòu)成列或數(shù)據(jù)庫的單元格區(qū)域）,FIELD（指定函數(shù)所使用的數(shù)據(jù)列）和CRITERIA（包含給定條件的單元格區(qū)域）；2、都以字母D開頭，因此又稱DFUNCTIONS函數(shù)；3、去掉D后我們發(fā)現(xiàn)很多數(shù)據(jù)庫函數(shù)在其他類型函數(shù)中出現(xiàn)過了。下面通過一個(gè)實(shí)例進(jìn)行說明：樹 高度 使用年限 產(chǎn)量 利潤(rùn)蘋果樹 18 20 14 105梨樹櫻桃樹樹蘋果樹=DAVERAGE($A$1:$E$7,B1,C9:C10)2、日期與時(shí)間函數(shù)：（）通過日期和時(shí)間函數(shù)，可以在公式中分析處理日期值和時(shí)間值。1、用于取出當(dāng)前系統(tǒng)時(shí)間/日期信息的函數(shù)主要有NOW、TODAY；2、如果需要單獨(dú)的取年份、月份、日期或小時(shí)的數(shù)據(jù)，可以使用HOUR,DAY,MONTH,YEAR。實(shí)例：1、如果“=NOW()”=2009-12-2 19:30:00 則：“=year()”=2009“=month()”=12“=day()”=2“=hour()”=19“=minute”=30“=second()”=02、再學(xué)一個(gè)日期與時(shí)間函數(shù)DATEDIF(START_TIME,END_TIME,UNNIT)如果張三進(jìn)入公司的時(shí)間為2006-7-21，那么他的工齡可以這樣輸入：=DATEDIF(“2006-7-21”,now(),“y”)=3年3、財(cái)務(wù)函數(shù)：由于外部函數(shù)和工程函數(shù)不經(jīng)常使用，這里不在多說。財(cái)務(wù)函數(shù)大體包括四類：投資計(jì)算函數(shù)、折舊計(jì)算函數(shù)、償還計(jì)算函數(shù)、債券。投資計(jì)算函數(shù)PV（計(jì)算投資現(xiàn)值）、FV（計(jì)算投資未來值）、NPV（計(jì)算凈現(xiàn)值）、PMT（計(jì)算貸款的每期付款額）經(jīng)常使用，掌握一個(gè)其他觸類旁通，這里主要介紹PV函數(shù)。實(shí)例：假設(shè)要購買一項(xiàng)保險(xiǎn)年金，該保險(xiǎn)可以在今后20年內(nèi)于每月末回報(bào)500元。此項(xiàng)年金的購買成本為60000，假定投資回報(bào)率為8%。那么該項(xiàng)年金的現(xiàn)值多少？=PV(RATE,NPER,PMT,FV,TYPE)RATE:各期利率。如果年利率為12%，則月利率為1%；NPER：總投資期。如果4年期按月償還，共有48個(gè)償還期數(shù)；PMT：各期應(yīng)支付的金額。FV:未來值，如果18年后支付5萬元，那么5萬元就是未來值；TYPE:數(shù)字0或者1，用以指定各期的付款時(shí)間是在期初還是期末。=PV(0.08/12,12*20,500,0)=-59777.15備注：500指月底年金的支出。負(fù)值指的是支出的現(xiàn)金流，年金59777.15的現(xiàn)值小于實(shí)際支付60000。因此，不是可以看出這不是一項(xiàng)合適的投資。4、信息函數(shù)：專門返回某些指定單元格或區(qū)域等的信息。1、CELL函數(shù)用于返回單元格的格式、位置或內(nèi)容的信息函數(shù)。語法形式：CELL(INFO_TYPE,REFERENCE)INFO_TYPE:一個(gè)文本值，所