资源描述
1,Chapter 2,資料的取得由鍵盤輸入與由外界匯入,在這一章您會學習到 資料型態與資料驗證 輸入注意事項與資料編輯 將其他軟體的資料轉換成Excel活頁簿 連結其他軟體以取得即時資訊 工作表和工作表之間的連結 儲存工作環境 工作表與工作群組的運用,2,資料型態的分類 Classify the Data type,通常所謂的資料型態是指數值和文字，在資料庫系統的軟體中還加上日期、邏輯和備註，這些型態 Excel 活頁簿都已具備了；不過這裡要討論的是以資料的固定性和變動性來作區分的型態，可分為： 1.常數 Constant：指直接由鍵盤輸入或由其他類型檔案轉換而來的數值、文字、日期、等固定性資料，這些資料不會因為其他儲存格內容的變動而受到影響。 2.公式 Formular：指在鍵盤上輸入的一些運算式，這些運算式由其他儲存格位址和一些加減乘除等四則運算(如 =A1+B1 )組合而成，呈現出來的是計算之後得到的結果值；當參考的儲存格內容變動時，公式會重新計算而顯示出最新結果。在輸入公式之前要先輸入 =，以表示要設定公式。 3.函數 Function：在 Excel 有很多內建的函數供使用者作各式各樣的計算，例如數學及三角函數(加總、平均、最大值、最小值、SIN、COS)，財務函數(年金終值、投資淨現值、每期付款)等共九大類足以應付使用者大部份需求的函數；和公式相同的是，計算結果也會隨參考的儲存格內容變動而更新。輸入函數前，同樣要先輸入 =，如 =SUM(A1:A5)。,3,輸入注意事項,輸入技巧須知,4,輸入數值 Entry numerical data,數字輸入後會自動靠右。Number will put on right hand side automatically. 逗號可以含在數字之間，如 100,000。 Comma can put within number 數字中的點號將視為小數點。 Dot within number will look like decimal point. 數字前的正號會被忽略。 Positive sign in front of number will be ignore. 輸入公式時要先輸入 。 Equal sign should be put before Formula. 數字前的減號，或數字被括弧起來會被視為負數。Dash or parenthesis mean negative 當計算結果數字太大時，欄寬會自動調整。 Column width will adjust automatically. 將滑鼠游標指在 E 欄名右方界線上，向左右拖曳可手動調整欄寬(欄寬不足時數字會先以科學記號顯示，若連顯示科學記號 Scientific Notation 的寬度都不足則顯示 #)；若欄寬曾被更改過，再次欄寬不足時只會顯示出 # Pound Sign；在欄名右方界線上按兩下，可將欄寬調至適寬。 若是直接輸入除式(如4/5或4-5)而未在前面加上，則會被視為是日期4月5日；輸入常數除式時，請在前面加上或是0(0後面有一空格)以設定時儲存格內顯示運算結果，資料編輯列顯示公式；以0設定時則相反。 Put Equal sign before formula.,輸入文字 entry Letter or Character,文字輸入時自動靠左。 Letter, Character will put on left hand side automatically 字串長度超過儲存格寬度時，若旁邊的儲存格內沒有資料，則字串會完整出現；否則字串超出儲存格的部份會被截掉。若要字串不受影響，只要將欄寬調至適寬即可。 文字和數字可混合輸入。 Number and Character can mix entry. 若要將數字當文字處理，在輸入數字前先按單引號 。 Single quote,輸入日期和時間 Enter Date and Time,以 yyyy/mm/dd 格式輸入日期。 以 hh:mm pm(am) 或 hh:mm 格式輸入時間 ( hh:mm 和 pm 或 am 之間要空格) 。 以函數 today() 擷取系統日期。 Put = Equals in front of the function 以函數 now() 擷取系統日期及時間。 以 Ctrl +；輸入電腦上的今天日期。 Ctrl+ Semicolon 以 Ctrl + Shift +；輸入電腦上的現在時間。 Ctrl+ Shift + Semicolon 夜4技3甲 week 1,儲存格的數字格式會保留在格內，刪除數字並不會將格式也刪除，再輸入其他數 字，仍會套用舊格式；要擺脫舊格式，只需在同一儲存格再套用其他數字格式就可以了。Cell Formatting will still keep in the Cell Even you erase the Data.,6,自動填滿功能及自訂清單 Auto Fill & Customize,以下填滿功能均可用編輯 Edit填滿 Fill數列 Series來完成 ： 單純數字或文字填滿 1 or aaa 快速填滿相同的值 學生姓名 輸入公差 arithmetic ratio 為 1 的數字等差數列 1, 2 輸入公差為 1 的日期或編號等差數列 940901 輸入公差不為 1 (遞增或遞減) 的等差數列 2, 4 輸入等比 geometric ratio 數列 2 編輯 Edit填滿 Fill數列 Series等比 Growth 間距 step value為 5 輸入文字清單：當文字清單內容已全部顯示，而拖曳的範圍尚未結束時，清單內容會循環顯示。 自訂清單：製訂一個合自己用的清單如右。 Excise : 工具 Tool 選項 Options Custom List,7,資料驗證功能 Data Validation Function, 何謂資料驗證功能 在資料的輸入過程中，“忙中有錯”是很難避免的現象，所以在一般較專業的軟體中，通常都會有防誤措施，以期待在輸入時減少錯誤發生。什麼是資料驗證呢？在下面的例子中或許可得到一些了解：,這張單據應該是 2004 年 3 月份的資料，但輸入的人不小心打成 2204 年，接受時系統就會發出警告，並要求重新輸入或取消。,8, 如何設定資料驗證功能 How to set up the Validation Function 將作用儲存格移到等候輸入的儲存格上(每一格可能有不同的需求，所以要個別設定)，再選功能表資料 Data驗證 Validation，資料驗證對話方塊會出現在螢幕上。在這個對話方塊內有四張標籤，每張標籤的功能說明如下：,9,將其他類型的檔案轉換成 Excel 活頁簿檔,由文字檔轉入 Transfer text file to Excel file,文字檔的來源，可能有下列數種： 由 PE2PE3 產生的檔案 (延伸檔名最好用 .txt )。 由 Microsoft 記事本 NotePad 產生的 .txt 檔。 其他軟體產生的純文字 (ASC II) 檔。 Excel在開啟文字檔時，必須透過匯入字串精靈 Import wizard，來進行資料欄位的分割；所以要轉入Excel的文字檔，內容最好是用 Tab 鍵將資料欄與資料欄隔開(這樣雖然排列不像用 空白 鍵隔開那麼整齊，但比較不會產生困擾)，否則分割結果可能會不盡理想。,通常會將不同類型的檔案轉入 Excel 活頁簿，大部份情形是因為資料原來是用別的軟體來運作，而現在想要換成由 Excel 來處理。轉換的方法是在開啟舊檔時，在檔案類型欄選取欲開啟檔案的類型；資料轉入 Excel 活頁簿後，只有一張工作表，以檔名為工作表標籤名稱。請特別注意：資料轉入後必須經過另存新檔、將檔案類型轉換成Microsoft Excel 活頁簿存檔才算轉換完成。,10,(1)選檔案 File開啟舊檔，或按開啟舊檔鈕。 (2)在檔案類型 File of Type名單中選文字檔案 text file。 (3)在文字檔的檔案名稱中選 Sales.Txt，按開啟鈕。您會看到匯入字串精靈步驟3之1對話方塊： 在這個畫面中，輸入資料類型欄已被自動選取為分隔符號Delimited，是因為 sales.txt這個檔案在輸入時是以 Tab 鍵將資料隔開的。若輸入時以 空白 隔開資料，則匯入字串精靈會將檔案以固定寬度來分割，也就是說檔案內容必須排列得很整齊才會得到正確的切割結果。,11,(4)按下一步。分割結果item separate出現後，您可上下捲動看看是否正確。,(5)按下一步。右上角欄位的資料格式可決定每一欄位的資料屬性，如數值或文字。,12,(6)按完成 Finished鈕。這時看到的資料仍然是文字檔類型。 (7)選檔案 File另存新檔 Save as。 (8)在另存新檔對話方塊中，將檔案類型 File Type 改為Microsoft Excel 活頁簿，磁碟機改為您預備存檔的路徑上(比方說我的文件)，再按儲存 Save。這個動作才真正將檔案轉成活頁簿類型了。,(9)儲存完請保留檔案 Sales.xls，但勿關閉。,13,連結 Connection,使用連結的時機,在有下列需求時可以考慮使用連結： 活頁簿內容與別處資訊有連帶關係，如銷貨記錄和銷貨月報。 連結的來源資料經常更改，而活頁簿內容又必須保持最新版本。 可透過網路取得來源資料來更新活頁簿內容。 有一份以上的活頁簿文件共享原始資料。,連結是指在活頁簿內插入的資訊，仍與來源保持連接。當來源資料更動時，活頁簿內的資訊立即反應，隨之更新。在開啟設有連結的活頁簿檔時，最好連同來源檔一起開啟，以保持資訊的流暢及隨時更新。連結的對象可以是同一活頁簿內的工作表，也可以是不同的活頁簿，甚至是不同的軟體。,與活頁簿內其他工作表連結,儲存格與儲存格之間連結的格式是儲存格位址,14,與其他活頁簿檔連結 Connect with the other worksheet,活頁簿與活頁簿之間連結的格式是 put file name in the brackets =活頁簿檔名工作表標籤！儲存格位址 File name Worksheet name ! Cell point (1)開啟 Chap02Month.xls and Sales.xls (2)選視窗 Window重排視窗 Arrange垂直並排 Vertical。 (3)在 Month.xls 的工作表一月份的 C3 輸入 Sales.xlsSales!D4 (也可以輸入 後，切換到檔案 Sales.xls、在工作表Sales的 D4 上按一下，再按 Enter 鍵接受) 。您會看到 Sales.xls 上Sales的 D4 的內容出現在 Month.xls 上的 C3。 (4)將 Sales.xls 內 D4 的日期更改為 2004/1/10。檔案 Month.xls 上 C3 的內容跟著改過來了。 (5)在 Sales.xls 按復原。,15,嵌入 embedding,嵌入是指在活頁簿內透過插入Insert圖片或插入物件，使物件成為活頁簿的一部份。和連結不同的是：嵌入的物件不會隨來源資料更改而變更，它可調整大小，也可以刪除或複製；在嵌入的物件上按兩下，可進入物件編輯狀態。,Web 匯入或 XML 匯入,透過網路匯入資料，已是跨組織或是跨國合作必要的工作方式了：有關 Web 匯入與XML 匯入，將留待全球化商務篇再來詳細討論。,資料的編輯,更改資料內容,將作用儲存格移到要更改的儲存格，然後反白資料編輯列上要更改的地方，直接輸入。 在要更改的儲存格上按兩下，使該格進入編輯狀態，再反白要更正的部份，輸入正確資料。,16,復原與取消復原 Recovery or Undo,在鍵盤輸入的過程中，難免會有錯誤發生；有時是打錯字，有時是刪錯儲存格的資料。遇到這種情形，請千萬不要急著重新輸入資料！您只需要按一下復原鈕，就可輕易取消剛才錯誤動作所帶來的結局。多重復原可以記錄到您前面 16 步的動作，所以即使您發現在上一次的錯誤之後已做了很多個動作，也不必擔心；只要在 16 步的範圍內，都是可以復原的。,複製儲存格與移動儲存格 Copy or Move Cell,當要輸入的資料，在別的儲存格內已存在時，可將該儲存格內容複製到現在要使用的儲存格內。開始複製之前，要先選好儲存格範圍。複製工作可一步完成，也可以分兩階段完成。 一步完成複製或移動 One Step for Copy or Move Cell 滑鼠游標指在選好範圍四周的邊線上(滑鼠呈形狀)，左手按住鍵盤的 Ctrl 鍵，右手將選取範圍拖曳到目的地放開。在拖曳的過程中，白色箭頭形狀的游標旁會多一個小的黑十字，而且會有一灰色矩形框跟隨著游標移向目的地，游標旁會出現一個小框標示著目前最新位置。,17, 兩階段完成複製或移動 Two Step for Move or Copy Cell 一步式的複製法雖然方便，但只適用於同一畫面上(近距離)的複製；若複製的目的地離原來的範圍太遠，需要轉動畫面時，兩階段的方式會比較適用。 所謂兩階段，是指複製的工作，分成複製和貼上兩個動作來進行：複製將選取的資料存放一份在 Microsoft 的剪貼簿上，貼上則從剪貼簿上將資料擷取下來，放在使用者指定的位置。複製只需做一次，貼上則可以執行無限次數，直到按 Esc 鍵取消複製狀態為止。 複製和貼上都有很多種途徑可以執行。以下將複製和貼上分別介紹，兩者之間並無限定要如何搭配，只要有複製的動作，也有貼上的動作就可以了。 移動儲存格和複製儲存格非常類似，只是在第一階段複製儲存格選複製功能，移動儲存格選剪下功能；第二階段貼上的動作相同。以下為複製儲存格與移動儲存格的比較：,18,貼上 Paste,移到目的格後： 選功能表編輯貼上。 按滑鼠右鍵，在快顯功能表中選貼上。 按工具列的 貼上鈕 (旁邊箭頭按下去，若是 複製 可選擇只貼上值或公式等屬性；若是 剪下 則無法選擇。直接按圖示則是全部貼上。 按 Ctrl + V。,選擇性貼上及轉置,Paste Special & Transpose (1)選取 G4:G7，執行複製；在 G9 執行貼上。G9:G12 中出現的是 - (這是會計格式的 0 )，而不是原來 G4:G7 的數字。請注意看資料編輯列的內容，Sum函數參照的儲存格已經變動了。,19,(2)在 G12 右下角出現一個貼上選項 paste optional方塊，在方塊上按一下，會看到很多選項：,(3)在 B9 再貼上一次，您會看到和剛才完全不同的結果。 (4)在 C9 再貼上一次，您會發現連原來好好的 G9:G12 都受到牽連，因為 C9:C12 屬於 G9:G12 參照的範圍。按一次復原鈕恢復原狀 (5)在 F9 輸入任意輸入一數字，G9 內容會自動隨著更改。,20,傳統的選擇性貼上操作方法如下： (1)選取 G4:G7，執行複製。 (2)在 H9 按一下，游標指在 H9 中央，按滑鼠右鍵。 (3)在快顯功能表中選選擇性貼上，在對話方塊中選值，按確定。您可比較 G9:G12 及 H9:H12 的內容。 按出右下角貼上選項的選項，選值與原始格式設定，原來的格式會被貼上，公式則被貼成數值。若看到 # Pound sign代表欄寬不足， 請在 H 欄名右邊的邊線按兩下調到適寬。,21,若您想將原本水平方向排列的資料改為垂直排列，可以用選擇性貼上中的轉置 Transpose來做到： (1)同時選取 C3:F3 及 C7:F7，執行複製 Copy。 (2)將作用儲存格移到 C9，在 C9 按出快顯功能表，選選擇性貼上Paste Special，在貼上欄選值與數字格式、勾選下方轉置 Transpose項目，按確定。,(3)您會發現文字和數字被貼上了，數字的格式和原來一樣是千分位，但填滿色彩沒有貼過來。若您希望儲存格的填滿色彩也複製過來，就原地再執行一次選擇性貼上，在貼上欄選格式、勾選轉置，按確定即可。,22,若您想在選擇性貼上的同時，進行加減乘除arithmetic operation的運算，可以如此進行： (1)在 E9 輸入 0.01，在填滿空點按兩下向下填滿。若您在資料編輯列看到的數字是0.01，而 E9:E12 內卻是 0，那是因為 E9:E12 格式被設定為整數 (資料編輯列可以看到實際的數值)。您只要先選取 E9:E12，在工具列的 Increase Decimal 鈕上按兩次，就可以顯示到小數兩位了。 (2)選取 G4:G7，執行複製。 將作用儲存格移到 E9，在 E9 按出快顯功能表，選選擇性貼上中的 值及乘Multiply， 按確定。必要時這兩項 可和轉置同時設定。,23,清除儲存格 Clear Cell,清除儲存格的動作，其實在前面已做過了：將要清除的範圍選起來，然後按 Del 鍵。這是最簡捷的方法，但是只能清除儲存格內容，不能清除格式。以下列出所有的清除功能及效力範圍，請以剛才選擇性貼上產生的資料來練習清除儲存格。,24,插入儲存格 Insert Cell,輸入大量資料時，難免會犯錯；有時不小心看錯欄、而導致整欄資料和標題不符的情形，可用插入儲存格來補救。插入儲存格時要先選取要插入的範圍，然後： 選功能表插入 Insert儲存格 Cell現有儲存格右移 Shift Cell Right(或下移) 按出快顯功能表，選插入現有儲存格右移(或下移),刪除儲存 Delete Cell,刪除儲存格和清除儲存格的差別在於：清除儲存格之後，儲存格的空間仍然存在，但刪除儲存格之後，連儲存格也消失不見了。刪除儲存格時，先選取要刪除的儲存格範圍，然後： 在快顯功能表中選刪除Delete右側儲存格左移Shift Cell Left(或下側儲存格上移Shift Cell Up) 選功能表編輯Edit刪除Delete右側儲存格左移(或下側儲存格上移),25,工作表的運用,除了資料編輯須注意的事項之外，工作表運用技巧的熟練與否，也直接影響到工作效率。以下所討論的是有關工作表本身的一些運用技巧，如凍結視窗Freeze Panes、分割視窗Split 等。,改變視窗的顯示比例 Zoom the Window,當資料太多，希望螢幕上能顯示更多資料時，可以考慮將視窗的顯示比例縮小。改變螢幕顯示比例的途徑的如下： 選檢視View顯示比例Zoom，在顯示比例對話方塊中選擇顯示百分比。 按工具列顯示比例方塊。在這個方塊中最下面的選項選定範圍代表：只要您事先選定要顯示的範圍，再選顯示比例中的選定範圍，Excel 就會將範圍內的資料全部顯示在同一畫面上，不必考慮比例的問題。,視窗的隱藏與顯示,開啟之後的活頁簿視窗，是可以隱藏的。只要選視窗Window隱藏視窗Hide，目前正在使用的活頁簿視窗就會被隱藏了。,26,分割視窗 Split the Window,如果工作表資料有很多列，縮小顯示比例未必理想，因為字太小時對使用者的眼睛是項負擔。何況很多時候使用者想看的不是全部的資料，而是無法在同一畫面同時顯示的兩處位置不同的資料；這時使用視窗分割是最理想的方法。 視窗分割途徑有二： 將作用儲存格移到要分割的地方，選視窗分割Split。分割線會以作用儲存格 cell point 位置為準，沿著作用儲存格左方及上方邊界，將視窗分割為四個區域。 拖曳分割方塊(請參考第一章視窗介紹)。分別使用時，可橫向或縱向將視窗分為兩個區域。兩者皆用時，可以將視窗分割為四個區域。,解除分割狀態,選視窗Window移除分割Remove Split。 在分割線上按兩下。 按住分割線，拖曳到視窗外去。,27,改變水平捲軸的長度 Change the Horizontal Scroll,工作表下方的水平捲軸右端是分割方塊，左端是調整水平捲軸長度的方塊。只要按住這個方塊向左右拖曳，就可任意改變水平捲軸的長度。,凍結窗格 Freeze Panels,當您在捲動工作表時，可能已注意到：當捲動到工作表下方時，上方的資料會消失不見，捲動到工作表右方時，左方的資料會消失不見。如果標題不能保留在螢幕上，會造成使用者極大的不便；這時用凍結窗格，可以使您想保留的部份固定在螢幕上，不受捲動的影響。視窗-凍結窗格 (Will freeze the cell left and above),工作表標籤的命名 Rename Label,在工作表標籤上按兩下。 滑鼠游標指在工作表標籤上，按滑鼠右鍵，在快顯功能表中選重新命名Rename。 輸入完標籤名稱後，按 Enter 鍵或在任一儲存格按一下，就完成命名了。工作表標籤重新命名是不能復原的動作，若想取消新名，只能再重新命名一次。,28,工作表的插入、複製、移動、刪除,插入 Insert for WorkSheet 在練習連結 Word 檔時已用插入工作表增加了一張工作表。 複製或移動 Move or Copy 指在工作表標籤上，按滑鼠右鍵，在快顯功能表中選移動或複製Move or Copy若要複製工作表時勾選建立複本Create a copy，若要移動工作表則不要勾選此項點選複製目的位置。 選編輯Edit移動或複製工作表Move or Copy若要複製工作表時勾選建立複本Create a Copy，若要移動工作表則不要勾選此項點選複製目的位置。 指在工作表標籤上，複製時按住 Ctrl 及滑鼠左鍵，拖曳到目的地後放開；移動時不要按 Ctrl，直接將工作表拖曳到目的地放開。 刪除 Delete worksheet 指在工作表標籤上，按滑鼠右鍵，在快顯功能表中選刪除，在出現的對話方塊中回答刪除。 選編輯刪除工作表，在出現的對話方塊中回答刪除。,29,設定工作群組,至目前為止，所討論的編輯都是單一工作表的運用；但往往不同工作表之間的資料會互相關連，要考慮的問題不是只有單一工作表，而是整個脈絡，這時多張工作表的運用技巧就很重要了。本節要討論的是，在輸入資料牽涉到多張工作表時，會用到哪些功能或技巧。設定工作群組是指將許多張工作表同時選取，被選取的這些工作表組成工作群組之後，所執行的動作會同時在群組內的所有工作表上進行。設定工作群組的方法如下： 1.選取連續工作表：按住 Shift 鍵，在預定組成群組的第一張工作表按一下，最後一張工作表按一下，這兩張以及在這兩張之間的工作表會全部被選取。 2.選取間隔工作表：按住 Ctrl 鍵，在預定組成群組的每一張工作表(不論工作表位置在哪裡)按一下，只有被點到的工作表會被選取。 取消工作群組：只要指在群組中任何一張工作表上，按滑鼠右鍵，選取消工作群組，或按任一張不在工作群組內的工作表標籤即可。,30,習題實作 : (Open Ch02/sales.xls),31,32,Exercise 1,請至 ftp:/140.127.138.46 帳號 為 mis 無 密碼 林輝鐸老師 檔案夾 中的 Excel 中的 Exercise 中的 BankStaff.xls 下載,33,此練習為一銀行招考新進人員之考試成績資料,本題之要求為 1. 計算 筆試總分 及 合格與否 的欄位. 2. 計算 各科總平均. 3. 計算 筆試及格人數. 4. 及 筆試不及格人數.,34,35,第二章 結束,
展开阅读全文