excel數(shù)據(jù)分析與處理-第2章工作表數(shù)據(jù)的輸入

第2章工作表數(shù)據(jù)輸入第2章工作表數(shù)據(jù)輸入概述數(shù)據(jù)輸入是很實際的問題，許多工作人員不得不面對它。針對不同規(guī)律的數(shù)據(jù)，采用不同的輸入方法，不僅能減少數(shù)據(jù)輸入的工作量，還能保障輸入數(shù)據(jù)的正確性。如果不了解掌握不同類型數(shù)據(jù)的輸入方法，可能就要成天座在電腦前，一五一十地輸入數(shù)據(jù)，輸完了還要檢查。某些人要幾天才能輸入完成的數(shù)據(jù)，但有的人幾分鐘就完成了，而且數(shù)據(jù)不會出錯！這就是學(xué)習(xí)EXCEL數(shù)據(jù)輸入方法的原因！學(xué)習(xí)目標1、了解Excel的數(shù)據(jù)類型2、掌握不同類型數(shù)據(jù)的輸入方法3、掌握復(fù)制公式輸入數(shù)據(jù)的方法4、掌握大批量相同數(shù)據(jù)的輸入方法5、掌握通過自定義格式簡少數(shù)據(jù)輸入的方法6、掌握用IF、Vlookup等函數(shù)查找輸入數(shù)據(jù)的方法7、掌握用RandBetween、Rand和int函數(shù)產(chǎn)生大量模擬數(shù)據(jù)的方法。

2.1Excel的數(shù)據(jù)類型1、Excel的常見數(shù)據(jù)類型數(shù)字型、日期型、文本型、邏輯型數(shù)據(jù)數(shù)字型表現(xiàn)形式多樣：貨幣、小數(shù)、百分數(shù)、科學(xué)計數(shù)法、各種編號、郵政編碼、電話號碼等多種形式2.1Excel的數(shù)據(jù)類型2、Excel2010數(shù)值型數(shù)據(jù)的取值范圍功能最大限制數(shù)字精度15位單元格中可鍵入的最大數(shù)值9.99999999999999E+307最大正數(shù)1.79769313486231E+308最小負數(shù)-2.2251E-308最小正數(shù)2.229E-308最大負數(shù)-1E-3072.2基本數(shù)據(jù)的輸入數(shù)據(jù)輸入并非易事，請看下面的例子【例2.2】某電信公司有用戶檔案如圖2.2所示。如果不講技巧，一字不誤的輸入，費時費力。本章將介紹一些數(shù)據(jù)輸入技術(shù)，應(yīng)用這些技術(shù)可以減少數(shù)據(jù)輸入過程的不少麻煩如何輸入客戶編號、月租費、保險號、繳費賬號才是最快捷的呢？2.2基本數(shù)據(jù)的輸入1、數(shù)據(jù)輸入的一般過程（1）選定要輸入數(shù)據(jù)的單元格。（2）從鍵盤上輸入數(shù)據(jù)。（3）按Enter鍵后2、輸入數(shù)值（1）正數(shù)的輸入：+234，13,333,333，12E3（2）負數(shù)的輸入：-234，(234)，-12e2（3）分數(shù)的輸入:22/3,03/4（4）貨幣數(shù)據(jù)的輸入:￥123，$212.2基本數(shù)據(jù)的輸入3、輸入文本字符文本應(yīng)逐字輸入數(shù)字文本以‘開頭輸入，或用=“數(shù)字”方式輸入。輸入文本32，可輸入：=“32”

或輸入：'32文本形式的公式，要通過“插入”選項卡“文本”組中的“對象”命令如2.2基本數(shù)據(jù)的輸入4、輸入日期以yy-mm-dd形式，或mm-dd形式輸入通過格式化得到其它形式的日期，這樣可減少輸入在“開始”選項卡“數(shù)字”組卡，單擊其中“數(shù)字”組的對話框啟動器的箭頭，彈出圖示的“單元格格式”設(shè)置對話框2.2基本數(shù)據(jù)的輸入5、輸入公式公式是對工作表中的數(shù)值進行計算的等式。公式要以等號（＝）開始。例如在A3輸入：=5+2*3，結(jié)果等于2乘3再加5。A3中將顯示112.3相同數(shù)據(jù)的輸入1、復(fù)制相同數(shù)據(jù)建立具有相同數(shù)據(jù)的不同工作表，可用復(fù)制方法。某班主任建立相同的成績表頭，可用此法。復(fù)制一班表頭2.3相同數(shù)據(jù)的輸入2、填充復(fù)制相同數(shù)據(jù)設(shè)要建立下圖所示“學(xué)生檔案”表。其中入學(xué)時間、班級、系都是相同數(shù)據(jù)。（1）在G2中輸入“計算機通信”，然后Enter。（2）單擊G2，G2右下角會出現(xiàn)黑色小方塊，它就是填充柄。（3）向下拖動填充柄，拖過的單元格都被填入了“計算機通信”。雙擊此填充柄可自動復(fù)制生成G列數(shù)據(jù)2.3相同數(shù)據(jù)的輸入3、用Ctrl+Enter輸入相同數(shù)據(jù)設(shè)要建立下圖所示“學(xué)生檔案”表。其中入學(xué)時間、班級、系都是相同數(shù)據(jù)。（1）選中F2：F11（2）輸入：JK001（3）按Ctrl+Enter可用同樣方法輸入C、G列數(shù)據(jù)2.4編號的輸入1、復(fù)制輸入連續(xù)的編號連續(xù)編號（或等差、等比性質(zhì)的數(shù)據(jù)）應(yīng)該采用復(fù)制或序列填充的方式進行輸入。比如公職或單位的職工編號、電話號碼、手機號碼、零件編號等。輸入右圖A列的學(xué)號（1）在A2輸入1003020101

在A3輸入1003020101（2）選中A2：A3（3）向下拖動A3單元格的填充柄。2.4編號的輸入2、填充產(chǎn)生連續(xù)編號填充序列方式適用于輸入較多的連續(xù)編號數(shù)據(jù)。比如要輸入電話號碼：663321240~663328000，如右圖所示，方法如下。在第一個單元格中輸入起始號碼在

“開始”選項的“編輯”組，單擊該組中填充控件右側(cè)的下三角形。彈出“序列”對話框，在Excel彈出的“序列”對話框中指定“列”在步長中輸入1在終止值中輸入最后一個號碼。2.4編號的輸入3、利用自定義格式產(chǎn)生特殊編號人們常會遇到各種具有一定規(guī)則的特殊格式的編號。如右圖的保險號。1、選中要輸入保險號的區(qū)域2、在“開始”選項卡中，單擊數(shù)字組右邊的箭頭，Excel會彈出單元格格式設(shè)置對話框，。3、選中“自定義”4、輸入自定格式：p00-000-00002.4編號的輸入4.用自定義格式輸入大數(shù)字編號當(dāng)在單元格中輸入一個位數(shù)較多的數(shù)值時（比如超過15位的整數(shù)），Excel會自動將輸入的數(shù)據(jù)顯示為科學(xué)記數(shù)法，這可能并不符合我們的需要。產(chǎn)生這種問題的原因是數(shù)值精度問題，Excel的默認精度是15位，如果一個數(shù)值的長度超過15位，則15位之后的數(shù)字都被視為0。這種位數(shù)較多的大數(shù)字編號，可以用自定義格式進行輸入,或者用文本形式輸入2.4編號的輸入4.用自定義格式輸入大數(shù)字編號建立右圖所示的商場用戶檔案，其中客戶編號為21位，且前18位全部相同。若直接輸入，不僅數(shù)據(jù)多，且要出錯，比如在A3輸入：356800993102155129001,Excel會顯示為3.57E20解決方法是定義G列保存身份證號的單元格的自定義格式為："356800993102155129"000設(shè)置自定義格式后，只需要輸入身份證的最后4位編號2.4編號的輸入選中A3單元格，單擊

“開始”選項卡“數(shù)字”組右下功的下箭頭，彈出設(shè)置單元格格式對話框“類型”文本框中輸入："356800993102155129"0002.5用&組合多個單元格數(shù)據(jù)1、用&運算符組合數(shù)據(jù)“&”為字符連接運算，其作用是把前后的兩個字符串（也可以是數(shù)值）連接為一個字符串。如“ADKDKD”&“DKA”的結(jié)果為ADKDKDDKA。123&45&678的結(jié)果為“'12345678”

設(shè)下圖E列數(shù)據(jù)由C、D列數(shù)據(jù)組合而成。在E3中輸入公式=C3&D3，然后向下復(fù)制！2.5用&組合多個單元格數(shù)據(jù)2、用&和文本函數(shù)的結(jié)合&常和文本函數(shù)left、right、mid等合用，這幾個函數(shù)都很簡單，其調(diào)用語法如下：left(text,n)right(text,n)mid(textn1,n2)其中：left截取text文本左邊的n個字符；比如left("1234",2)="12"right截取text文本右邊的n個字符；比如right("1234",2)="34"mid從text的第n1字符開始，取n2個字符；比如MID("1234",2,2)="23"2.5用&組合多個單元格數(shù)據(jù)3、用&和文本函數(shù)的結(jié)合案例某電話公司的繳費帳號由身份證號的后15位（全長18位），后接電話號碼構(gòu)成。如下圖所示。用&和文本函數(shù)可以很快建立繳費帳號。在I3輸入公式“=Right(G3,17)&H3”，然后向下填充復(fù)制該公式到I列的其余單元格2.6采用下拉列表進行數(shù)據(jù)選擇

1、下拉列表的適用范圍項目個數(shù)少而規(guī)范的數(shù)據(jù)，比如職稱、工種、單位及產(chǎn)品類型等，這類數(shù)據(jù)適宜采用Excel的“數(shù)據(jù)有效性”檢驗方式，以下拉列表的方式輸入。例如：某校教師檔案如右圖所示，其中的職稱列數(shù)據(jù)只能從“講師、教授、副教授、助教…”中選擇。這類數(shù)據(jù)用下拉列表輸入，方便而準確。2.6采用下拉列表進行數(shù)據(jù)選擇

2、下拉列表建立方法（1）選中要建立下拉列表的單元格區(qū)域。（2）然后選擇功能區(qū)中的“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”控件（3）選擇“設(shè)置”標簽，然后從“允許”下拉列表中選擇“序列”選項。（4）在“來源”文本框中輸入職稱名字“助教,講師,副教授,教授……”

2.7利用公式與函數(shù)進行查找輸入1、查找輸入的意義如果表格中的不同數(shù)據(jù)列之間存在一定的依賴關(guān)系，那么用IF公式進行數(shù)據(jù)的轉(zhuǎn)換輸入是一種效率較高的輸入方法。如果某些數(shù)據(jù)已經(jīng)在其它數(shù)據(jù)表中建立好了，查詢輸入可以提高效率，且能減少輸入錯誤。2.7利用公式與函數(shù)進行查找輸入2、IF函數(shù)的應(yīng)用IF函數(shù)又稱為條件函數(shù)，它能夠?qū)o出的條件進行判斷，并根據(jù)判斷結(jié)果的正誤執(zhí)行不同的運算。IF函數(shù)的調(diào)用形式如下：IF(條件式,條件正確時函數(shù)的取值,條件錯誤時函數(shù)的取值)比如，IF(3>2,“right”,“error”)”的結(jié)果是“right”，而IF(3<2,"right","error")的結(jié)果應(yīng)該是“error”因為3>2是正確的，函數(shù)的結(jié)果就是“條件正確時的取值”。與之相對應(yīng)，而3<2是錯誤的，所以……。2.7利用公式與函數(shù)進行查找輸入IF函數(shù)可以嵌套使用，即在IF函數(shù)中還可以使用IF函數(shù)，最多可以嵌套7層。比如：IF(A2>89,“A”,IF(A2>79,“B”))，如果A2等于93，則該函數(shù)的結(jié)果是“A”；如果A2等于80，則函數(shù)的結(jié)果是內(nèi)層IF(A2>79,“B”)的結(jié)果，顯然80>79，所以結(jié)果為“B”；如果A2等于65呢？函數(shù)的結(jié)果就是IF(A2>79,"B")不成立時的取值，這時該函數(shù)的結(jié)果就是一個空值，因為IF(A2>79,"B")沒有給出A2>79不成立時的取值，在這種情況下，Excel將返回一個空值（即什么結(jié)果都沒有?。┙o該函數(shù)。2.7利用公式與函數(shù)進行查找輸入3、IF函數(shù)查找轉(zhuǎn)換案例【例2.10】某學(xué)校年度獎金如下圖所示，獎金根據(jù)職稱確定。教授：2000元，副教授：1500，講師：1000，助教：是500元。在F3輸入公式：=IF(E3="教授",2000,IF(E3="副教授",1500,IF(E3="講師",1000,IF(E3="助教",500))))2.7利用公式與函數(shù)進行查找輸入公式含義分析2.7利用公式與函數(shù)進行查找輸入4、Vlookup函數(shù)查找Vlookup函數(shù)能夠從其它工作表（或本工作表）將某數(shù)據(jù)列中的數(shù)據(jù)查找出需要的數(shù)據(jù)。VLOOKUP函數(shù)的用法如下：VLOOKUP（x,table,n,r）其中，x是要查找的值，table是一個單元格區(qū)域（即由工作表的1列或多列組成）。Excel將在table的第一列查找有無x這樣的值（只在table的第1列查找有無x）

n是table中待返回的匹配值的列序號。。r可以取0或1，當(dāng)r為0時，該函數(shù)將進行精確匹配查找；當(dāng)r=1時，table的第一列應(yīng)按升序排列，否則找到的結(jié)果可能不正確，r=1表示Excel實行的是近似值查找2.7利用公式與函數(shù)進行查找輸入5、Vlookup函數(shù)查找案例（1）問題描述：某移動公司更換了新系統(tǒng)，使原有的部分電話號碼不能使用，同時又新增加了許多電話號碼。系統(tǒng)變化后，大部分用戶仍然使用原來的用戶檔案（電話號碼和繳費合同號都沒有改變），而那些不能使用的電話號碼和新增加的電話號碼則需要重新建立用戶檔案2.7利用公式與函數(shù)進行查找輸入（2）帳號查找過程在需要提取帳號的新號碼工作表的B3輸入查找公式，并向下復(fù)制此公式。=VLOOKUP(D2,$A$2:$B$18,2,0)E列是從舊帳號的B列找到的數(shù)據(jù)，#N/A表示D列的帳號在A列不存在2.7利用公式與函數(shù)進行查找輸入在實際工作中，不同類型的數(shù)據(jù)往往存儲在不同的工作表中，這就需要在不同工作表中進行跨表數(shù)據(jù)查找?！纠?.12】某超市將其產(chǎn)品單價和產(chǎn)品銷售匯總表分別保存在兩個工作表中，如圖2.21所示。在產(chǎn)品的單價表中包括有該商場大約幾百種產(chǎn)品的單價，銷售匯總表每月統(tǒng)計一次，現(xiàn)在要計算每種產(chǎn)品的銷售總額。2.7利用公式與函數(shù)進行查找輸入在C3單元格中輸入公式：=VLOOKUP(A3,產(chǎn)品!$B$3:$D$16,3,0)然后向下復(fù)制此公式到C列其他單元格2.8利用填充序列輸入數(shù)據(jù)1、數(shù)據(jù)序列的含義序列就是一組數(shù)據(jù)，這組數(shù)據(jù)有先后順序，內(nèi)容固定，在應(yīng)用中常常以一組數(shù)據(jù)的整體形式出現(xiàn)。2、Excel內(nèi)置序列對于經(jīng)常使用的一些數(shù)據(jù)序列，比如月份、星期、季度等，Excel已經(jīng)將它們內(nèi)置在系統(tǒng)中，在輸入這些數(shù)據(jù)時，只需要輸入第一個數(shù)據(jù)，其余的可以采用填充復(fù)制的方法由Excel自動產(chǎn)生。2.8利用填充序列輸入數(shù)據(jù)3、自定義序列用戶可以將經(jīng)常使用的數(shù)據(jù)序列添加到Excel系統(tǒng)中，該序列就會擁有與Excel內(nèi)置序列相同功能。比如，輸入該序列的第一項內(nèi)容后，其后各項都可以通過填充復(fù)制產(chǎn)生。案例【例2.14】某大學(xué)有“計算機學(xué)院，通信學(xué)院，經(jīng)濟管理學(xué)院，法律學(xué)院，中藥學(xué)院，電子技術(shù)學(xué)完”等多所學(xué)院，學(xué)校辦公室經(jīng)常要用到這些學(xué)院名稱?，F(xiàn)以此為例說明Excel自定義序列的建立方法。2.8利用填充序列輸入數(shù)據(jù)建立方法（1）單擊Office按鈕→“Excel選項”→“常用”→“編輯自定義列表”。彈出圖示的“自定義序列”對話框。（2）在“輸入序列”中依次輸入序列內(nèi)容。（3）建立輸入序列后，輸入第一項，即“計算機學(xué)院”后，通過填充復(fù)制就能產(chǎn)生其它各項內(nèi)容。2.9行列轉(zhuǎn)置輸入某些時候需要將工作表的行數(shù)據(jù)作為列數(shù)據(jù)輸入，或?qū)⒘袛?shù)據(jù)作為新表的行數(shù)據(jù)。這樣的工作表數(shù)據(jù)可通過行列轉(zhuǎn)置快捷生成?！纠?.15】某品牌電視2008年的銷售統(tǒng)計數(shù)據(jù)如圖2.25所示。其中的A、B兩列數(shù)據(jù)是以前輸入在工作表中的各省銷售排名，在A列中包括有中國全部省份的名稱?，F(xiàn)在要構(gòu)造D1：AA16的統(tǒng)計表2.9行列轉(zhuǎn)置輸入1、選中A列的省份名稱所在的單元格區(qū)域A4:A34。2、在“開始”選項卡上的“剪貼板”組中，單擊“復(fù)制”按鈕。3、單擊E4單元格4、在“開始”選項卡上的“剪貼板”組中，單擊下面的三角形，然后從彈出的快捷菜單中選擇“轉(zhuǎn)置”，這樣就完成了E4:AA4中的省份名稱的輸入。

2.9行列轉(zhuǎn)置輸入【例2.16】矩陣涉及到較多的數(shù)據(jù)，它由緊密相連的一組單元格區(qū)域構(gòu)成，矩陣的運算比較復(fù)雜，Excel提供了一組矩陣運算的函數(shù)。建立圖2.26中A2:D8區(qū)域的行列轉(zhuǎn)換矩陣。1、選中A2:D8。2、在“開始”選項卡上的“剪貼板”組中，單擊“復(fù)制”按鈕。3、單擊G3單元格4、在“開始”選項卡上的“剪貼板”組中，單擊下面的三角形，然后從彈出的快捷菜單中選擇“轉(zhuǎn)置”，這樣就完成了G3:M6中的矩陣輸入。

2.9限定輸入數(shù)據(jù)的長度，顯示提示和出錯信息

對輸入到單元格中的數(shù)據(jù)進行一定程度的限制，比如限制小數(shù)位數(shù)、日期和時間的范圍、字符的個數(shù)等。當(dāng)輸入的數(shù)據(jù)不符合限定規(guī)則時，Excel就顯示一些警告信息，并且不接受輸入的數(shù)據(jù)。如果工