第1章Excel基礎(chǔ),Excel與數(shù)據(jù)處理第3版,本章學習目標,認識EXCEL的工作界面，窗口組成元素；了解工作薄、工作表、單元格等基本概念；掌握不同類型的數(shù)據(jù)輸入方法；理解相對引用和絕對引用；掌握公式輸入方法；掌握工作表的刪除、插入、移動、改名等操作掌握單元格、行、列常用操作；掌握sumcountif等簡單函數(shù)的應(yīng)用方法,1.1Excel操作基礎(chǔ),1啟動Excel的方法通過Windows任務(wù)欄選擇“開始”按鈕通過Excel快捷方式雙擊xcel文件的名字或圖標、退出xcel的方法單擊Excel窗口右上角的關(guān)閉按鈕選擇“文件”菜單中的“退出”菜單項。按快捷鍵Alt+F4。,1.1Excel操作基礎(chǔ),3、Excel界面,功能區(qū),工作表區(qū),1.1Excel操作基礎(chǔ),功能區(qū)的主要控件Excel2007用功能區(qū)取代了早期版本中的菜單、工具欄和大部分任務(wù)窗格，包括按鈕、庫和對話框內(nèi)容。,功能區(qū)的主要控件,Office按鈕取代了早期版本中的“文件”菜單，單擊時，將彈出與Excel早期版本“文件”菜單相似的菜單項，它是Excel2007中唯一的菜單，提供了常用的Excel文件操作功能，包括的菜單命令有“新建、打開、保存、另存為、打印、準備、發(fā)送、發(fā)布、關(guān)閉”。選項卡功能區(qū)由“開始、插入、頁面布局、公式、數(shù)據(jù)”等選項卡組成。各選項卡是面向任務(wù)的，每個選項卡以特定任務(wù)或方案為主題組織其中的功能控件。能區(qū)中的選項卡是動態(tài)的，為了減少屏幕混亂，某些選項卡平時是隱藏的，在執(zhí)行相應(yīng)的操作時，它們會自動顯示出來。組Excel2007將以前版本中那些隱藏在菜單和工具欄中的命令和功能，以面向任務(wù)的方式放置在不同的邏輯組中，每個邏輯組能夠完成某種類型的子任務(wù)?？焖僭L問工具欄其中包含一組獨立于當前所顯示的選項卡的命令，無論用戶選擇哪個選項卡，它將一直顯示，為用戶提供操作的便利。用戶可以根據(jù)需要設(shè)置其中的工具按鈕.,1.1Excel操作基礎(chǔ),工作表區(qū)的主要對象,1.1Excel操作基礎(chǔ),工作表就是人們常說的電子表格，是Excel中用于存儲和處理數(shù)據(jù)的主要文檔。由一些橫向和縱向的網(wǎng)格組成，橫向的稱為行，縱向的稱為列。Excel2007的一個工作表最多可有1,048,576行、16,384列數(shù)據(jù)（而Excel2003的工作表最多有65536行、256列）。當前正在使用的工作表稱為活動工作表。工作表標簽每個工作表有一個名稱，體現(xiàn)在工作表標簽上。單擊工作表標簽按鈕可使對應(yīng)的工作表成為活動工作表，雙擊工作表標簽按鈕可改變它們的名稱。插入工作表標簽按鈕在默認情況下，Excel只打開3個工作表：Sheet1，Sheet2，Sheet.單擊插入新工作表按鈕就會后面插入一個新工作表sheet4，再單擊一次就會插入新工作表sheet5,1.1Excel操作基礎(chǔ),行號、列標題工作表由1048576行組成，每行的編號稱為行號工作表的每列用英文字母標志，稱為列標題單元格工作表中的網(wǎng)格，它用列標題和行號定位。如A1就表示列第行對應(yīng)的單元格單元格區(qū)域是指多個連續(xù)單元格的組合。表示形式如下：左上角單元格:右下角單元格例如A2:B3代表一個單元格區(qū)域，包括A2、B2、A3、B3只包括行號或列號的單元格區(qū)域代表整行或整列。如，1:1表示第一行的全部單元格組成的區(qū)域，1:5則表示由第1到5行全部單元格組成的區(qū)域；A:A表示第一列全部單元格組成的區(qū)域，A:D則表由A、B、C、D四列的全部單元格組成的區(qū)域。,1.1Excel操作基礎(chǔ),編輯欄用于顯示、修改活動單元格的內(nèi)容全選按鈕用于選定整個工作表所有的單元格。工作表查看方式工作表下邊框的右側(cè)提供的三個按鈕用于切換工作表的查看方式工作表縮放,1.2構(gòu)造第一個工作表,【例1.1】某單位的工資數(shù)據(jù)如下表所示，計算每個職工的總工資。假設(shè)個人所得稅的稅率為：收入1600以下，所得稅率為0%；收入16002500，所得稅率為5%；收入25003500，所得稅率為8%；收入35004500，所得稅率為12%；收入4500以上，所得稅率為15%。計算各位職工應(yīng)繳納的所得稅，以及繳稅后的實際收入。,1.2構(gòu)造第一個工作表,在EXCEL中可以輕松制作出例1-1的報表，如圖所示,輸入A1：H9中的原始檔案數(shù)據(jù),1、在I2中輸入工資計算公式：=D2-E2-F2-G2+H22、向下復制此公式到I9,1、在H2中輸入計算公式：=I2-K2、向下復制此公式到H9,1、在K2中輸入工資計算公式：=I2*J22、向下復制此公式到K9,輸入J列的稅率,1.3工作薄和Excel文件管理,1.3.1工作薄與工作表的關(guān)系工作簿在Excel中創(chuàng)建的文件叫做工作簿，擴展名是.xlsx工作薄由工作表組成，新建工作薄只有默認的個工作表。但可以根據(jù)需要增加，個數(shù)不限。新建第一個工作薄的默認名：book1.xlsx。工作表由一些橫向和縱向的網(wǎng)格組成的表格。一個工作表最多有1,048,576行、16,384列。工作表由工作表標簽區(qū)別，如sheet1、sheet2,1.3.1工作薄與工作表的關(guān)系,工作薄與工作表的關(guān)系,1.3.2創(chuàng)建新工作薄,1、工作薄與模板模板模板即模型、樣板，是一種已經(jīng)建立好的特殊工作薄，已在其中的工作表中設(shè)計好了許多功能，如單元格中的字體、字型，工作表的樣式和功能（如財務(wù)計算，學生成績統(tǒng)計，會議安排等。工作薄創(chuàng)建Excel總是根據(jù)模板創(chuàng)建工作薄，創(chuàng)建的工作薄具有與模板結(jié)構(gòu)、內(nèi)容和功能。在沒有指定建立工作薄模板的情況下，Excel會據(jù)默認模板建立工作薄，這種工作薄在默認情況下只有Sheet1、Sheet2和Sheet3三個工作表。,1.3.2創(chuàng)建新工作薄,2、新建默認的空工作薄，三種方法啟動Excel，Excel就會自動建立一個名為Boolk1.xlsx;在已啟動Excel的情況下，按Ctrl+N；單擊功能區(qū)左上角的Office按鈕，然后從彈出的文件菜單中選擇“新建”；,1.3.2創(chuàng)建新工作薄,3、根據(jù)舊工作薄建立工作?。?）單擊Office按鈕，從彈出的文件菜單中選擇“新建”。Excel將彈出“新建工作薄”對話框。（2）單擊“根據(jù)已有內(nèi)容新建”，然后在彈出的“根據(jù)現(xiàn)有工作薄新建”對話框找到并選擇已有工作薄的名稱，單擊該對話框中的“新建”按鈕。,1.3.2創(chuàng)建新工作薄,4、根據(jù)模板建立工作薄Excel2007模板類型已安裝的模板：安裝Excel時安裝在本地計算機中的模板；我的模板：用戶自已創(chuàng)建的模板；在線模板：微軟公司網(wǎng)站上的在線工作薄模板，在用這類模板創(chuàng)建新工作薄時，Excel會自動從微軟的網(wǎng)站中下載相關(guān)模板到用戶的本地計算機中，并用它創(chuàng)建新工作薄。當然，前提是用戶計算機與Internet網(wǎng)絡(luò)相連接（即Online，在線）。,1.3.2創(chuàng)建新工作薄,5、在線模板示例下面的圖表是用MicrosoftOfficeOnline”中的“預算”模板類中的“商務(wù)旅行預算”模板建立的工作薄。在該工作薄中，只需要輸入D列的費用和F列的數(shù)字，Excel就會自動計算出H列的費用，這一功能是由“商務(wù)旅行模板”提供的。,1.3.2創(chuàng)建新工作薄,5、在線模板示例下圖是用“MicrosoftOfficeOnline”中的“庫存控制”模板類中的“設(shè)備資產(chǎn)清單”模板建立的工作薄。在該工作薄中預設(shè)了許多功能，在L、N、P、Q、R列中都預設(shè)了計算公式，只要填定了其余列的數(shù)據(jù)，Excel就會自動計算出這些列的數(shù)據(jù)。,1.3.2創(chuàng)建新工作薄,5、在線模板的建立方法(1)確定計算機已連接上了Internet網(wǎng)絡(luò)(2)單擊“Office按鈕”新建MicrosoftOfficeOnline.將連接到微軟的在線模板網(wǎng)站。(3)從微軟網(wǎng)站的在線模板網(wǎng)站中選擇需要的模板，就會從網(wǎng)站中將選擇的模板下載到本機中，同時將應(yīng)用。,1.3.3保存和打開工作薄文件,1、保存文件方法一單擊“快速訪問工具欄”中的按鈕保存文件。方法二單擊Office按鈕，從彈出的文件菜單，選擇“另存為”菜單項，然后在“另存為”對話框中輸入文件名。2、打開文件單擊Office按鈕，從彈出的文件菜單，選擇“打開”菜單項，然后在“打開”對話框中選擇要打開文件的名稱。,1.3.4文件格式與兼容性,1、Excel2007的文件格式MicrosoftOffice2007引入了一種基于XML的新文件格式：MicrosoftOfficeOpenXMLFormats，適用于2007版的Word、Excel和PowerPoint。OfficeXMLFormats有許多優(yōu)點：其一，文件自動壓縮。某些情況下文檔最多可縮小75%其二，OfficeXMLFormats格式以模塊形式組織文件結(jié)構(gòu)，是如果文件中的某個組件（如圖表）受到損壞，文件仍然能夠打開其三，更強的安全性。OfficeXMLFormats格式支持以保密方式共享文檔，可避免用文檔檢查器工具獲取信息。其四，更好的業(yè)務(wù)數(shù)據(jù)集成性和互操作性。在Office中創(chuàng)建的信息容易被其他業(yè)務(wù)應(yīng)用程序所采用，只需一個ZIP工具和XML編輯器就可打開和編輯Office文件。,1.3.4文件格式與兼容性,2、Excel2007的文件擴展名和文件兼容性,1.4公式和單元格引用,、公式的概念Excel的公式由運算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號“=”開頭，即在Excel的單元格中，凡是以等號開頭的輸入數(shù)據(jù)都被認為是公式。在等號的后面可以跟數(shù)值、運算符、變量或函數(shù)，在公式中還可以使用括號。例如：=10+4*6/2+(2+1)*50就是公式，可以在任何單元格中輸入此公式Excel會把公式的計算結(jié)果顯示在相應(yīng)的單元格中,1.4公式,2、運算符在xcel公式中可以使用運算符。,1.4公式,3、說明括號的運算級別最高，在Excel的公式中只能使用小括號，無中括號和大括號。小括號可以嵌套使用，當有多重小括號時，最內(nèi)層的表達式優(yōu)先運算。同等級別的運算符從左到右依次進行。“;“零”;則當在此單元格中輸入負數(shù)時，輸入的負數(shù)就不會被顯示出來（被隱藏了）。,4.4格式化數(shù)據(jù),在格式碼中指定的字符被用來作為占位符或格式指示符。0作為一個占位符，它在沒有任何數(shù)字被顯示的位置上顯示一個0。符號“_)”跟在一個正數(shù)的格式后面，以保證這個正數(shù)右邊會留下一個空格，空格的寬度與圓括號“）”的寬度一樣寬。正數(shù)、負數(shù)和0靠單元格的右邊線對齊。,4.4格式化數(shù)據(jù),自定義格式碼的例子,4.4格式化數(shù)據(jù),6、Excel常用格式碼的含義,4.4格式化數(shù)據(jù),7、自定義格式案例,4.4格式化數(shù)據(jù),8自定義格式的方法（1）選中要格式化的單元格。（2）單擊“開始”|“數(shù)字”組右邊的下三角形（3）選擇彈出對話框的“數(shù)字”標簽，然后選擇“分類”列表框中的“自定義”選項。（4）在“類型”文本框中，輸入自定義格式。（5）在單元格中輸入數(shù)據(jù)，數(shù)據(jù)將以自定義格式顯示。,4.5格式化日期,1、概述在Excel中，日期和時間實際上是以序列數(shù)字的形式存儲，以日期形式顯示的。所以當一個單元格的格式被設(shè)置為日期格式后，即使在其中輸入純粹的數(shù)字，該數(shù)字也會被顯示為一個日期。,4.5格式化日期,2、日期的格式碼,4.5格式化日期,3、日期格式化案例,4.6條件格式,關(guān)于條件格式對于不同的數(shù)據(jù)，可按不同的條件設(shè)置其顯示格式。例如，把學生成績表中所有不及格成績顯示為紅色，可以非常輕松地看出其中不及格的成績情況；對于企業(yè)的銷售表，把其中利潤較小的或無利潤的數(shù)據(jù)設(shè)置為黃色，而將利潤最大的數(shù)據(jù)設(shè)置為綠色，可以使人在查看這些數(shù)據(jù)時一目了然。,4.6條件格式,1、條件格式的意義對于不同的數(shù)據(jù)，可按不同的條件設(shè)置其顯示格式。比如在一個學生成績表中，把所有不及格的成績顯示為紅色，就能夠很容易找到有不及格科目的學生。下面是條件格式運用的一個例子：當單元格的數(shù)值大于200時，用黑色顯示；當小于100時用紅色顯示；100200之間的數(shù)據(jù)用藍色顯示。此自定義條件的格式碼如下：黑色>=200#,#;紅色<=100#,#;藍色#,#,4.6條件格式,2、條件設(shè)置案例在下圖所示的成績表中，060之間，6080之間，80100之間的分數(shù)分別以不同的顏色表示和背景表示，便于查詢不同分數(shù)段的信息。,4.6.1Excel2007條件格式概述,條件格式指基于某種條件更改單元格區(qū)域中數(shù)據(jù)的表現(xiàn)形式，如果條件成立就基于該條件設(shè)置單元格區(qū)域的格式（如設(shè)置單元格的背景、用圖形符號表示數(shù)據(jù)），如果條件不成立就不設(shè)置單元格區(qū)域的格式。Excel2007的條件格式Excel2007對條件格式進行了較大的改進，對條件格式的任務(wù)分得更細，涉及的內(nèi)容更多，也更具實用性。Excel2007的條件格式更容易達到以下效果：突出顯示所關(guān)注的單元格或單元格區(qū)域；強調(diào)異常值；使用數(shù)據(jù)條、顏色刻度和圖標集來直觀地顯示數(shù)據(jù)。,4.6.1Excel2007條件格式概述,【例4.8】某中藥材公司2009年的藥材銷售利潤表如圖4.26所示。Page96在圖4.26所示工作表中突出顯示以下信息：利潤小于20的藥材銷售情況，銷售利潤最高或最低的10種藥材是哪些，用條型圖直觀顯示出各藥材在各省的銷售情況，用不同的圖形顯示出各藥材在各省的銷售情況，標識出利潤占前30%的藥材銷售情況。在Excel2007中，通過條件格式可以輕松地實現(xiàn)上述要求。條件格式位于“開始”選項卡的“樣式”組中，單擊該組中的“條件格式”按鈕，將顯示條件格式的命令列表，如圖4.27所示。,4.6.1Excel2007條件格式概述,Excel2007條件格式的內(nèi)容,Excel2007條件格式案例,突出顯示利潤小于20的藥材,用數(shù)據(jù)條顯示單元格數(shù)據(jù),突出顯示利潤10%的藥材,用圖標集和色階顯示不同比例部分的數(shù)據(jù),4.6.1Excel2007條件格式概述,Excel2007條件格式中的幾個概念（1）突出顯示單元格規(guī)則對單元格區(qū)域設(shè)置一定的條件，將按指定的現(xiàn)則突出顯示該區(qū)域中滿足條件的單元格內(nèi)容，默認的規(guī)則用某種色彩填充單元格背景。例如，如果單元格的值大于、小于或等于某個指定值，就將單元格的背景填充為綠色，突出顯示該單元格中的值。（2）項目選取規(guī)則對選中單元格區(qū)域中小于或大于某個給定閾值的單元格實施條件格式。單擊此規(guī)則中的“值最大的10項”、“值最大的10%項”、“高于平均值”等條件，Excel都會顯示出一個條件設(shè)置對話框，可通過它設(shè)置單元格中的條件格式。,4.6.1Excel2007條件格式概述,Excel2007條件格式中的幾個概念（3）數(shù)據(jù)條數(shù)據(jù)條以彩色條型圖直觀地顯示單元格數(shù)據(jù)。數(shù)據(jù)條的長度代表單元格中的數(shù)值。數(shù)據(jù)條越長，表示值越高，數(shù)據(jù)條越短，表示值越低。（4）色階色階用顏色的深淺表示數(shù)據(jù)的分布和變化，包括雙色階和三色階。雙色刻度使用兩種顏色的深淺程度比較某個區(qū)域的單元格。顏色的深淺表示值的高低。（5）圖標集使用圖標集可以對數(shù)據(jù)進行注釋，并可以按閾值將數(shù)據(jù)分為三到五個類別。每個圖標代表一個值的范圍，其形狀或顏色表示的是當前單元格中的值相對于使用了條件格式的單元格區(qū)域中的值的比例。,4.6.2條件格式規(guī)則,條件格式規(guī)則Excel2007中的條件格式是根據(jù)一組規(guī)則來實施完成的，這組規(guī)則稱為條件格式規(guī)則。條件格式規(guī)則類型基于各自值設(shè)置所有單元格的格式只為包含以下內(nèi)容的單元格設(shè)置格式”僅對排名靠前或靠后的數(shù)值設(shè)置格式僅對高于或低于平均值的數(shù)值設(shè)置格式僅對唯一值或重復值設(shè)置格式使用公式確定要設(shè)置格式的單元格,單擊“開始”選項卡中的“條件格式”按鈕，從彈出的菜單中選擇“新建規(guī)則”,4.6.3自定義條件格式,自定義條件格式默認的條件格式能夠解決大多數(shù)問題，但有些時候并不如愿，例如默認的圖標集用三種圖標表示不同比例的數(shù)據(jù)：前33%、中33%和后33%各用一種圖表表示，如果希望的比例為前15%、中45%后40%各用一種圖標表示。就需要更改條件格式的規(guī)則，這可以通過自定義條件格式完成。,4.6.3自定義條件格式,【例4.9】某校采用學評教制度，學生對其任課教師進行評分。某次學評教成績?nèi)鐖D4.33左圖所示。對該工作表進行格式化，標識出學評教分數(shù)最差的20%，最好的15%，以及中間的學評教成績，以便于實施獎勵與懲罰。,最好的15%最后的20%！中間的65%,4.6.3自定義條件格式,自定義條件格式的建立過程,（1）選擇要建立條件格式的區(qū)域，單擊“開始”“樣式”組“條件格式”“新建規(guī)則”命令。彈出圖示“新建格式規(guī)則”對話框。,（2）在“格式樣式”列表中選擇條件格式類型,（3）在“類型（T）”的列表中選擇“百分點值”，然后在“值”下面的文框中輸入值的范圍，如圖所示。,4.6.3自定義條件格式,重復應(yīng)用條件格式在Excel2007中，對同一單元格區(qū)域，可以多次應(yīng)用不同的條件格式。例如，對一個已經(jīng)應(yīng)用了色階的單元格區(qū)域，可以再次應(yīng)用圖標集、數(shù)據(jù)條等條件格式。自定義條件格式也可以疊加，請看下面的例子?！纠?.10】某班期末考試如圖4.35左圖所示。對它進行格式化，用紅色粗體顯示060之間的成績，對90100之間的單元格設(shè)置背景填充圖案。,多次應(yīng)用了條件格式的工作表,條件格式規(guī)則1：060分為紅色，加粗，傾斜,條件格式規(guī)則2：90分以下填充圖案背景,（1）選中C3:E12區(qū)域，單擊“開始”“條件格式”|“突出顯示單元格規(guī)則”|“小于”命令，,在“小于”中輸入60，然后在“設(shè)置為”的下拉列表中選擇“自定義格式”，Excel將彈出“設(shè)置單元格格式”對話框，,（2）在“小于”中輸入60，然后在“設(shè)置為”的下拉列表中選擇“自定義格式”，Excel將彈出“設(shè)置單元格格式”對話框，,（3）單擊“字體”選項，選中“字形”中的“加粗傾斜”，并從“顏色”列表中選擇經(jīng)色。,（4）再次選中C3:E12區(qū)域。設(shè)置大于90的條件條件格式。通過“設(shè)置單元格格式”|“填充”對話框中的“圖案樣式”列表中，為成績大于90的單元格選擇一背景填充圖案。,4.6.4條件格式規(guī)則的管理,1、條件格式的限制在Excel2007中，條件格式最多可以包含六十四個條件。2、條件格式的優(yōu)先級當對相同的單元格或區(qū)域設(shè)置了多重條件后，有可能引起條件規(guī)則的沖突。例如，一個規(guī)則將單元格字體顏色設(shè)置為紅色，而另一個規(guī)則將單元格字體顏色設(shè)置為綠色。因為這兩個規(guī)則沖突，所以只應(yīng)用一個規(guī)則。但到底應(yīng)用哪個規(guī)則呢，要根據(jù)各條件規(guī)則的優(yōu)先級來確定3、條件格式規(guī)則管理條件規(guī)則的優(yōu)先級可通過“條件格式規(guī)則管理器”對話框進行設(shè)置和管理，通過此對話框，還可以在工作簿中創(chuàng)建、編輯、刪除和查看所有條件格式規(guī)則。,4.6.4條件格式規(guī)則的管理,1、本題應(yīng)用了圖標集、色階、數(shù)據(jù)條等多重條件,2、單擊“開始”“樣式”組“條件格式”旁邊的箭頭“管理規(guī)則”。Excel將彈出“條件格式規(guī)則管理器”對話框,3、選擇“新建規(guī)則”或以建立新的規(guī)則；選擇“編輯規(guī)則”可對條件格式進行修改,5、單擊上、下箭頭可以提高或降低當前穩(wěn)定中條件格式規(guī)則的優(yōu)先級,4、條件格式規(guī)則：這些規(guī)則都是應(yīng)用于同一單元格或區(qū)域的，排在前面的優(yōu)先級高；排在后面的優(yōu)級低，可以通過“刪除規(guī)則”右邊的上下箭頭移動規(guī)則，更改優(yōu)先級,4.7自定義格式的應(yīng)用,【例4.11】在數(shù)字單元格中增加文本。如果有較多的單元格或單元格區(qū)域需要輸入相同的文本，如學生編號、公司的職工編號等，可以通過設(shè)置單元格的自定義格式，把編號中相同的部分或文本中相同的部分固定為格式，這樣可以減少輸入，而且可使數(shù)據(jù)更為準確。,定義A列學號的自定義格式為："329901"00,4.7自定義格式的應(yīng)用,【例4.12】設(shè)置自定義格式，控制用戶輸入（使用戶輸入的數(shù)據(jù)在有效的范圍內(nèi)）。,設(shè)置姓名列的自定義格式：紅色"姓名不能輸入數(shù)字"紅色"人名不能是數(shù)字"紅色"姓名不能是0",設(shè)置工作年齡列的自定義格式：G/通用格式;紅色"工作年齡不能是負數(shù)"紅色0;紅色"工作年齡不能是文本",4.7自定義格式的應(yīng)用,【例4.13】隱藏不需要的0值。在輸入學生成績之前，預先在該列中輸入了一個成績計算公式“=SUM(C5:E5*0.7)+F5*0.3”。所以G列出現(xiàn)了許多0,設(shè)置G列總分區(qū)域的自定義格式為：G/通用格式;"綜合成績不能為負",2.6自定義格式的應(yīng)用,【例】定義電話、保險號碼的簡化輸入,TheEnd,謝謝大家！,第5章公式、名稱與函數(shù),Excel與數(shù)據(jù)處理第3版,Excel與數(shù)據(jù)處理第3版,第6章圖表處理,