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

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