項(xiàng)目三 Office高級應(yīng)用-Excel軟件

信息技術(shù)與人工智能基礎(chǔ)項(xiàng)目三Office高級應(yīng)用—Excel軟件01項(xiàng)目背景02思維導(dǎo)圖03思政聚焦04項(xiàng)目相關(guān)知識05項(xiàng)目任務(wù)目錄CONTENTS06項(xiàng)目小結(jié)與展望01項(xiàng)目背景在日常的辦公中，在管理各種信息和處理數(shù)據(jù)時經(jīng)常需要處理各種數(shù)據(jù)報(bào)表和圖表，例如在管理員工的基本信息時通過員工信息表來管理、在管理員工的工資信息時通過工資表來管理、在統(tǒng)計(jì)銷售數(shù)據(jù)時通過銷售表來管理等；在做工作總結(jié)匯報(bào)時經(jīng)常需要對各種數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析和可視化展示等。作為微軟Office家族的重要組件之一，Excel軟件以表格形式組織、分析、處理數(shù)據(jù)，憑借其直觀的界面、出色的計(jì)算功能和圖表工具，Excel軟件成為了當(dāng)今最流行的個人計(jì)算機(jī)數(shù)據(jù)處理軟件，它可以幫助人們進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和決策制定，廣泛地應(yīng)用于管理、統(tǒng)計(jì)財(cái)經(jīng)、金融等眾多領(lǐng)域。Excel面世30多年來，經(jīng)過不斷發(fā)展提升，已經(jīng)成為了辦公人員必不可少的數(shù)據(jù)處理軟件，在各個部門的核心工作中發(fā)揮著重要的作用。02思維導(dǎo)圖本項(xiàng)目思維導(dǎo)圖03思政聚焦無論是學(xué)習(xí)還是工作，處理大量數(shù)據(jù)的是一項(xiàng)普遍的事情，熟練地應(yīng)用Excel軟件可以極大地提高學(xué)習(xí)和工作效率，達(dá)到事半功倍的效果，高超的Excel水平能夠極大地助力職場的成功。然而，盡管Excel具有強(qiáng)大的數(shù)據(jù)處理功能，但其數(shù)據(jù)安全保護(hù)功能不夠健全，只能通過設(shè)置密碼控制，無法對用戶進(jìn)行角色的管理，也不能對數(shù)據(jù)進(jìn)行層級的訪問限制。作為一名數(shù)據(jù)分析人員，應(yīng)該客觀評價單位在發(fā)展過程中存在的問題，為決策層提供有效的參考依據(jù)，不應(yīng)受其他因素影響而更改數(shù)據(jù)、隱瞞存在的問題，否則將會對單位的發(fā)展造成嚴(yán)重的后果，因此必須具備嚴(yán)謹(jǐn)負(fù)責(zé)的職業(yè)道德。04項(xiàng)目相關(guān)知識3.1工作表創(chuàng)建及格式化1.工作簿工作簿是多個工作表的集合，一個工作簿最多可包含255個工作表，工作簿文件的擴(kuò)展名為.xlsx。2.工作表工作表是一個二維表格，它由行和列組成，行和列交叉的地方稱為單元格，單元格是存放數(shù)據(jù)的地方，默認(rèn)的工作表名稱為Sheet1。一個工作表最多可以有1048576行和16384列，行號用數(shù)字1～1048576表示，列號用英文字母A～Z，AA～AZ，BA～BZ，…，XFA～XFD表示。3.數(shù)據(jù)類型Excel軟件中的數(shù)據(jù)類型包含數(shù)值型、日期型、時間型、貨幣型、文本型、百分比型等。如果想將數(shù)值當(dāng)成普通的文本，則在輸入數(shù)值前先輸入半角狀態(tài)的上撇符號，之后再輸入數(shù)字。例如將數(shù)值編號“00001”當(dāng)成文本時直接輸入“'00001”，然后按下回車鍵，需要注意的是，輸入的是雙引號內(nèi)的字符。通常將編號、郵政編碼、身份證號碼、電話號碼等無須計(jì)算大小的數(shù)值當(dāng)成文本處理。4.數(shù)據(jù)有效性為了避免在錄入過程中出現(xiàn)不規(guī)范的數(shù)據(jù)，可以在某些列設(shè)置數(shù)據(jù)有效性，采用下拉列表形式選擇數(shù)據(jù)進(jìn)行數(shù)據(jù)錄入，避免用戶錄入下拉列表以外的數(shù)據(jù)。使用下拉列表錄入數(shù)據(jù)的方式通常適用于選擇個數(shù)少且規(guī)范的數(shù)據(jù)，例如性別、職稱、工種、學(xué)歷、學(xué)位、產(chǎn)品類型等。3.2數(shù)據(jù)計(jì)算1.公式公式是指在單元格中執(zhí)行計(jì)算功能的等式，所有公式都必須以等號開頭，公式形式：=表達(dá)式。2.運(yùn)算符運(yùn)算符是用于對公式中的元素進(jìn)行運(yùn)算而規(guī)定的特殊符號。常用的運(yùn)算符有算術(shù)運(yùn)算符、字符串連接運(yùn)算符和關(guān)系運(yùn)算符三類，按優(yōu)先級別由高到低列出各種運(yùn)算符（圓括號優(yōu)先）及其含義，如表3-1所示。3.單元格地址引用單元格地址引用分為相對引用、絕對引用、混合引用三類。相對引用是指引用的單元格與復(fù)制位置的單元格保持一致，即公式隨著引用單元格位置變化而變化，通常直接使用由列號和行號表示的單元格地址，例如對A7的相對引用表示為A7。絕對引用是指固定地址的引用，公式不會隨著引用單元格位置變化而變化，通常在單元格名稱的列號和行號左側(cè)加上“$”符號，例如對A7的絕對引用表示為$A$7?；旌弦檬侵钢唤^對引用固定的單列或單行，而別的行或列使用相對引用，例如對A7的混合引用表示為$A7或A$7。4.函數(shù)函數(shù)即事先創(chuàng)建好的公式，函數(shù)同樣以等號開頭，函數(shù)的形式：=函數(shù)名([參數(shù)1],[參數(shù)2],…)。Excel軟件提供了大量、實(shí)用的函數(shù)，這些函數(shù)為分析計(jì)算提供了極大便利。例如SUM函數(shù)、AVERAGE函數(shù)等。Excel軟件中的常用函數(shù)具體如下。（1）SUM函數(shù)SUM函數(shù)為求和函數(shù)，其功能是對選擇的單元格或單元格區(qū)域進(jìn)行求和計(jì)算，其語法結(jié)構(gòu)為SUM（number1,number2,…），其中number1,number2,…表示若干個需要求和的參數(shù)。填寫參數(shù)時，可以使用單元格地址（如B4,C4,D4），也可以使用單元格區(qū)域（如B4:D4），甚至可混合輸入（如B4,C4:D4）。（2）AVERAGE函數(shù)AVERAGE函數(shù)為平均值函數(shù)，其功能是求平均值，計(jì)算方法是，將選擇的單元格或單元格區(qū)域中的數(shù)據(jù)先相加，再除以單元格個數(shù)，其語法結(jié)構(gòu)為AVERAGE（number1,number2,…），其中number1,number2,…表示若干個需要求平均值的參數(shù)。填寫參數(shù)時，如果對多個連續(xù)的單元格求平均值則直接使用單元格區(qū)域（如B4:G4）即可。（3）RANK函數(shù)RANK函數(shù)是排名函數(shù)，其功能是求某一個數(shù)值在某一區(qū)域內(nèi)的排位，其語法結(jié)構(gòu)為RANK(number,ref,[order])，其中number為需要排位的數(shù)字；ref為一組數(shù)字或一個單元格區(qū)域，而ref中的非數(shù)值型參數(shù)將被忽略；order為一個數(shù)字，用于確定排位的方式。如果order為0或省略，將按降序排位；如果order不為零，將按升序排位。（4）IF函數(shù)IF函數(shù)是條件函數(shù)，它能判斷真假值，并根據(jù)邏輯計(jì)算的真假值返回不同的結(jié)果，其語法結(jié)構(gòu)為IF（logical_test,value_if_true,value_if_false），其中，logical_test表示計(jì)算結(jié)果為true或false的任意值或表達(dá)式；value_if_true表示logical_test為true時要返回的值，可以是任意數(shù)據(jù)；value_if_false表示logical_test為false時要返回的值，也可以是任意數(shù)據(jù)。（5）MAX/MIN函數(shù)MAX函數(shù)為最大值函數(shù)，其功能是返回所選單元格區(qū)域中所有數(shù)值的最大值；MIN函數(shù)為最小值函數(shù)，其功能是返回所選單元格區(qū)域中所有數(shù)值的最小值。其語法結(jié)構(gòu)為MAX/MIN（number1,number2,…），其中number1,number2,…表示要篩選的若干個數(shù)值或一個單元格區(qū)域。（6）COUNTIF函數(shù)COUNTIF函數(shù)用于計(jì)算某個單元格區(qū)域中給定條件的單元格數(shù)目，其語法結(jié)構(gòu)為COUNTIF（range,criteria），其中range為需要計(jì)算的單元格區(qū)域；criteria為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。（7）INDEX函數(shù)INDEX函數(shù)用于返回工作表單元格區(qū)域的值或?qū)χ档囊?。INDEX函數(shù)有兩種形式：數(shù)組形式和引用形式。數(shù)組形式通常返回?cái)?shù)值或數(shù)值數(shù)組；引用形式通常返回對值的引用。其語法結(jié)構(gòu)有兩種形式：INDEX(array,row_num,column_num)用于返回?cái)?shù)組中指定的單元格或單元格數(shù)組的數(shù)值；INDEX(reference,row_num,column_num,area_num)用于返回引用中指定單元格或單元格區(qū)域的引用。3.3數(shù)據(jù)可視化1.圖表圖表是進(jìn)行數(shù)據(jù)分析的強(qiáng)有力工具，圖表以工作表的數(shù)據(jù)為依據(jù)，以多種類型的圖形反映工作表數(shù)據(jù)的相互關(guān)系和變化趨勢。Excel軟件提供了多種類型的圖表，包括柱形圖、條形圖、折線圖、餅圖、面積圖、散點(diǎn)圖、雷達(dá)圖等，用戶可根據(jù)不同的要求選用不同類型的圖表。2.圖表格式化圖表格式化即對圖表進(jìn)行格式設(shè)置，包括設(shè)置圖表樣式、添加圖表元素、設(shè)置圖表區(qū)尺寸和背景、設(shè)置圖表區(qū)文字格式等。3.4數(shù)據(jù)統(tǒng)計(jì)分析1.圖表圖表是進(jìn)行數(shù)據(jù)分析的強(qiáng)有力工具，圖表以工作表的數(shù)據(jù)為依據(jù)，以多種類型的圖形反映工作表數(shù)據(jù)的相互關(guān)系和變化趨勢。Excel軟件提供了多種類型的圖表，包括柱形圖、條形圖、折線圖、餅圖、面積圖、散點(diǎn)圖、雷達(dá)圖等，用戶可根據(jù)不同的要求選用不同類型的圖表。2.圖表格式化圖表格式化即對圖表進(jìn)行格式設(shè)置，包括設(shè)置圖表樣式、添加圖表元素、設(shè)置圖表區(qū)尺寸和背景、設(shè)置圖表區(qū)文字格式等。1.數(shù)據(jù)排序數(shù)據(jù)排序是按照一定的規(guī)則對數(shù)據(jù)列表中的數(shù)據(jù)進(jìn)行整理排列的過程。數(shù)據(jù)排序的方式通常有“升序排序”和“降序排序”兩種。2.數(shù)據(jù)篩選數(shù)據(jù)篩選是指將工作表中符合查詢條件的數(shù)據(jù)顯示出來，不滿足指定條件的數(shù)據(jù)暫時隱藏。Excel軟件的數(shù)據(jù)篩選通常有兩種方式：自動篩選、高級篩選。自動篩選就是按照設(shè)定的條件對工作表中的數(shù)據(jù)進(jìn)行篩選，每次只能按照一個條件篩選，用于篩選簡單的數(shù)據(jù)，能夠幫助用戶從包含大量數(shù)據(jù)記錄的數(shù)據(jù)列表中快速查找出滿足條件的記錄。高級篩選功能較強(qiáng)，可進(jìn)行多條件的復(fù)雜查詢。3.數(shù)據(jù)分類匯總數(shù)據(jù)分類匯總是一個功能強(qiáng)大、使用方便的數(shù)據(jù)處理工具，它能按照用戶指定的分類規(guī)則對指定的字段進(jìn)行匯總計(jì)算并將結(jié)果在工作表中顯示出來。4.數(shù)據(jù)透視表數(shù)據(jù)透視表是一種交互式的數(shù)據(jù)報(bào)表，可以快速地按照多個條件匯總大量的數(shù)據(jù)并對匯總結(jié)果進(jìn)行各種篩選，以查看源數(shù)據(jù)的不同統(tǒng)計(jì)結(jié)果。Excel軟件中的數(shù)據(jù)透視表集成了篩選、排序和分類匯總等功能，最終可生成包含多種匯總統(tǒng)計(jì)信息的匯總表格，是用戶進(jìn)行數(shù)據(jù)分析的有力工具。在數(shù)據(jù)透視表中可以從不同角度查看數(shù)據(jù)，以便做出更好的業(yè)務(wù)決策。5.數(shù)據(jù)透視圖數(shù)據(jù)透視圖為關(guān)聯(lián)數(shù)據(jù)透視表中的數(shù)據(jù)提供圖形表示形式，它是一種交互式的圖表。為了直觀地查看數(shù)據(jù)情況，可以根據(jù)數(shù)據(jù)透視表制作相應(yīng)的數(shù)據(jù)透視圖。對于可視演示文稿，可以使用交互式篩選控件創(chuàng)建數(shù)據(jù)透視圖，以便分析數(shù)據(jù)子集。06項(xiàng)目任務(wù)任務(wù)1工作表創(chuàng)建及格式化——制作員工信息表1、任務(wù)描述作為公司的行政管理人員，常常需要通過制作員工信息表來管理員工信息，行政秘書劉霞接到公司任務(wù)需要創(chuàng)建公司內(nèi)部的員工信息表，員工信息表如圖3-1-1所示。制作員工信息表涉及以下相關(guān)基礎(chǔ)知識：創(chuàng)建及保存Excel表格，錄入表格數(shù)據(jù)，修改表格結(jié)構(gòu)、美化表格，打印表格等。2.制作員工信息表過程創(chuàng)建與管理Excel工作簿單元格數(shù)據(jù)錄入數(shù)據(jù)有效性設(shè)置數(shù)據(jù)表格式設(shè)置打印區(qū)域設(shè)置任務(wù)2數(shù)據(jù)計(jì)算——汽車銷售統(tǒng)計(jì)1、任務(wù)描述小明作為某汽車銷售公司的銷售經(jīng)理，在做半年總結(jié)匯報(bào)的時候需要統(tǒng)計(jì)1~6月的銷售情況，包括每位銷售員的總銷售量、月平均銷售額、銷售額排名、銷售等級、月最高營業(yè)額、月最低營業(yè)額、月營業(yè)額大于100的人數(shù)、查詢周婷六月營業(yè)額等，最終統(tǒng)計(jì)結(jié)果如圖3-2-1所示。2.汽車銷售統(tǒng)計(jì)過程計(jì)算營業(yè)總額：SUM函數(shù)計(jì)算月平均營業(yè)額：AVERAGE函數(shù)計(jì)算總額排名：RANK函數(shù)計(jì)算銷售等級：IF函數(shù)計(jì)算月最高/最低營業(yè)額：MAX/MIN函數(shù)統(tǒng)計(jì)月營業(yè)額大于100萬的人數(shù)：COUNTIF函數(shù)查詢某個單元格的內(nèi)容：INDEX函數(shù)

SUM函數(shù)格式：

SUM（number1,number2,……）或

SUM（單元格地址1:單元格地址2）功能：SUM函數(shù)用于計(jì)算單個或多個參數(shù)之和。33AVERAGE函數(shù)格式：

AVERAGE（number1,number2,……）或

AVERAGE（單元格地址1:單元格地址2）功能：AVERAGE函數(shù)對所有參數(shù)計(jì)算算術(shù)平均值。34

RANK函數(shù)格式：

RANK（number，ref，order）

功能：RANK函數(shù)返回某數(shù)字在一列數(shù)字中相對于其他數(shù)字按大小進(jìn)行的排位。其中number為需要找到排位的數(shù)字，ref為一組數(shù)字或引用，而ref中的非數(shù)值型參數(shù)將被忽略，order為一數(shù)字，確定排位的方式。如果order為0或省略，將按降序排位；如果order不為零，將按升序排位。35

if函數(shù)

格式：

IF(logical_test,value_if_true,value_if_false)其中l(wèi)ogical_test是計(jì)算結(jié)果為TRUE或FALSE的任何數(shù)值或表達(dá)式.value_if_true是當(dāng)logical_test為TRUE時函數(shù)的返回值.value_if_false是當(dāng)logical_test為FALSE時函數(shù)的返回值.

功能:邏輯判斷，對邏輯判斷的結(jié)果返回不同的值。36MAX/MIN函數(shù)格式：

MAX/MIN（number1,number2,……）或

MAX/MIN（單元格地址1:單元格地址2）

功能：MAX/MIN函數(shù)對所有參數(shù)計(jì)算最大/最小值。37COUNTIF函數(shù)格式：

COUNTIF（range,criteria）功能：COUNTIF函數(shù)計(jì)算某個區(qū)域中給定條件的單元格數(shù)目。38INDEX函數(shù)格式：INDEX(array,row_num,[column_num])功能：返回表格或數(shù)組中的元素值，此元素由行號和列號的索引值給定。當(dāng)函數(shù)INDEX的第一個參數(shù)為數(shù)組常量時，使用數(shù)組形式。

Array必需。單元格區(qū)域或數(shù)組常量。如果數(shù)組只包含一行或一列，則相對應(yīng)的參數(shù)row_num或column_num為可選參數(shù)。如果數(shù)組有多行和多列，但只使用row_num或column_num，函數(shù)INDEX返回?cái)?shù)組中的整行或整列，且返回值也為數(shù)組。Row_num必需。選擇數(shù)組中的某行，函數(shù)從該行返回?cái)?shù)值。如果省略row_num，則必須有column_num。Column_num可選。選擇數(shù)組中的某列，函數(shù)從該列返回?cái)?shù)值。如果省