Excel2010高級(jí)應(yīng)用(教材相關(guān))

第二單元Excel2010高級(jí)應(yīng)用學(xué)習(xí)任務(wù)任務(wù)1某公司職工工資管理任務(wù)2e家人超市休閑食品銷售情況統(tǒng)計(jì)任務(wù)3蔡先生個(gè)人理財(cái)方案學(xué)習(xí)目標(biāo)熟練操作工作簿、工作表；熟練使用Excel函數(shù)和公式；了解和掌握外部數(shù)據(jù)的導(dǎo)入和導(dǎo)出；掌握數(shù)據(jù)的篩選和高級(jí)篩選；了解數(shù)據(jù)透視表和透視圖的概念，并掌握數(shù)據(jù)透視表和數(shù)據(jù)透視圖的創(chuàng)建、修改和刪除；掌握透視表中切片器的使用；掌握迷你圖的使用任務(wù)1某公司職工工資管理

【任務(wù)提出】某公司共有3個(gè)部門，分別是管理部，銷售部和生產(chǎn)部，每個(gè)月月底要及時(shí)計(jì)算所有職工的工資，以便在下個(gè)月月初發(fā)放本月工資。該公司職工工資包括基本工資、崗位工資和獎(jiǎng)金，每個(gè)月還要繳納養(yǎng)老金、醫(yī)療金和公積金，最后根據(jù)職工的事病假情況進(jìn)行扣除后才得出該月職工的實(shí)發(fā)工資?，F(xiàn)有該公司2012年3月部分職工的相關(guān)信息，要求建立相應(yīng)工資情況表，并對數(shù)據(jù)進(jìn)行分析統(tǒng)計(jì)?！救蝿?wù)分析】本項(xiàng)目要求建立職工工資表，計(jì)算其中相關(guān)數(shù)據(jù)，并對數(shù)據(jù)進(jìn)行分析。在建立工資管理表時(shí)，要先計(jì)算職工崗位工資和獎(jiǎng)金，計(jì)算事病假扣款，計(jì)算個(gè)人所得稅最后，利用Excel的篩選功能對該表中的相關(guān)數(shù)據(jù)進(jìn)行有效統(tǒng)計(jì)和分析。任務(wù)1——【相關(guān)知識(shí)與技能】1．?dāng)?shù)據(jù)有效性及其設(shè)置“數(shù)據(jù)有效性”是一種Excel功能，用于定義可以在單元格中輸入或應(yīng)該在單元格中輸入哪些數(shù)據(jù)。還可以圈釋無效數(shù)據(jù)2.數(shù)組公式及其應(yīng)用可以同時(shí)進(jìn)行多重計(jì)算并返回一種或多種結(jié)果的公式。3.函數(shù)：查找與引用函數(shù)HLOOKUP、VLOOKUP時(shí)間和日期函數(shù)文本函數(shù)邏輯函數(shù)4．?dāng)?shù)據(jù)的篩選任務(wù)1——【相關(guān)知識(shí)與技能】1．?dāng)?shù)據(jù)有效性及其設(shè)置“數(shù)據(jù)有效性”是一種Excel功能，用于定義可以在單元格中輸入或應(yīng)該在單元格中輸入哪些數(shù)據(jù)。還可以圈釋無效數(shù)據(jù)2.數(shù)組公式及其應(yīng)用可以同時(shí)進(jìn)行多重計(jì)算并返回一種或多種結(jié)果的公式。3.函數(shù)：查找與引用函數(shù)HLOOKUP、VLOOKUP時(shí)間和日期函數(shù)文本函數(shù)邏輯函數(shù)4．?dāng)?shù)據(jù)的篩選【相關(guān)知識(shí)與技能】——數(shù)組公式數(shù)組公式的創(chuàng)建步驟如下：1）選定單元格或單元格區(qū)域。如果數(shù)組公式將返回一個(gè)結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個(gè)結(jié)果，則要選定需要輸入數(shù)組公式的單元格區(qū)域。2）輸入數(shù)組公式。3）同時(shí)按“Crtl+Shift+Enter”組合鍵，則Excel自動(dòng)在公式的兩邊加上大括號(hào){}【相關(guān)知識(shí)與技能】——數(shù)組公式數(shù)組公式的編輯1）在數(shù)組區(qū)域中單擊任一單元格；2）單擊公式編輯欄，當(dāng)編輯欄被激活時(shí)，大括號(hào)“{}”在數(shù)組公式中消失；3）編輯數(shù)組公式內(nèi)容；4）修改完畢后，按“Crtl+Shift+Enter”組合鍵。數(shù)組公式刪除首先選定數(shù)組公式的所有單元格，然后按Delete鍵【相關(guān)知識(shí)與技能】——查找和引用函數(shù)查找和引用函數(shù)HLOOKUP：在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并在表格或數(shù)組中指定行的同一列中返回一個(gè)數(shù)值。VLOOKUP：函數(shù)功能：搜索某個(gè)單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值【相關(guān)知識(shí)與技能】——時(shí)間和日期函數(shù)NOW、TODAYNOW()：返回當(dāng)前日期和時(shí)間TODAY()：返回當(dāng)前日期上述兩個(gè)函數(shù)沒有參數(shù)，但是函數(shù)后面的“()”不能省略。YEAR、MONTH、DAYYEAR(Now())HOUR、MINUTE、SECOND【相關(guān)知識(shí)與技能】——文本函數(shù)REPLACE實(shí)現(xiàn)部分字符串的替換MID返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。CONCATENATE將最多255個(gè)文本字符串聯(lián)接成一個(gè)文本字符串TEXT可將數(shù)值轉(zhuǎn)換為文本，并可使用戶通過使用特殊格式字符串來指定顯示格式。PROPER、LOWER大小寫轉(zhuǎn)換函數(shù)SEARCH、FIND字符串查找函數(shù)EXCAT字符串比較函數(shù)【相關(guān)知識(shí)與技能】——文本函數(shù)應(yīng)用1.用“2004”替換“Students”第2-5個(gè)字符2.把“abcdefghij”字符串中的“bcde”取出3.將“abc”和“123”連接起來，組成“abc123”4.若要將數(shù)字“23.5”轉(zhuǎn)換為文本，并設(shè)置為人民幣金額5.將字符串“thisisaTITLE”轉(zhuǎn)換為詞首字母大寫，其余小寫6.

將字符串“thisisaTITLE”轉(zhuǎn)換為小寫【相關(guān)知識(shí)與技能】——邏輯函數(shù)IF例如：給學(xué)生成績進(jìn)行等級(jí)評定，當(dāng)成績大于85分時(shí)，評為優(yōu)秀；60~85分，為良好；小于60分，為不及格。AND例如：可以評中級(jí)職稱的條件是“工齡”大于5年，“職稱”為“初級(jí)”。OR=OR(TRUE,FALSE,TRUE)NOT=NOT(1+1=2)【相關(guān)知識(shí)與技能】——數(shù)據(jù)的篩選篩選按條件篩選（有“數(shù)字”和“文本”兩類按顏色篩選（對設(shè)置了字體顏色的單元格）按值列表高級(jí)篩選：適合設(shè)置比較復(fù)制的篩選條件設(shè)置篩選條件設(shè)置“高級(jí)篩選”對話框按條件篩選按值列表篩選任務(wù)1——【任務(wù)實(shí)施】

“職工工資統(tǒng)計(jì)表.xlsx”任務(wù)11.數(shù)據(jù)有效性應(yīng)用要求：為使數(shù)據(jù)輸入更加規(guī)范化，在“性別”列單元格中進(jìn)行數(shù)據(jù)有效性控制，使數(shù)據(jù)以“男，女”的序列方式來填充在Sheet1的“職工工資表”的“性別”列中，填入職工性別列數(shù)據(jù)，如下：“女，男，男，男，女，女，男，男，女，男，男，女，女，男”。2.利用日期和時(shí)間函數(shù)來計(jì)算職工年齡根據(jù)“出生年月”列數(shù)據(jù)，得出職工的“年齡”，并填入E列相應(yīng)位置。3.“查找和引用”函數(shù)VLOOKUP的應(yīng)用根據(jù)“崗位和獎(jiǎng)金分配表”，填充“崗位工資”并計(jì)算“獎(jiǎng)金”。任務(wù)14.利用“篩選”對數(shù)據(jù)進(jìn)行分類計(jì)算對所有職工進(jìn)行獎(jiǎng)金的輸入，由于“銷售員”的獎(jiǎng)金是根據(jù)其銷售額度來計(jì)算，不同銷售額所折算的獎(jiǎng)金數(shù)不同，而其他人員的獎(jiǎng)金是定值。5.利用數(shù)組公式來進(jìn)行數(shù)據(jù)計(jì)算以數(shù)組公式方式計(jì)算“應(yīng)發(fā)合計(jì)”和應(yīng)繳納的“三金”?！叭稹睘椤梆B(yǎng)老金”、“醫(yī)療金”、“公積金”，分別為“應(yīng)發(fā)合計(jì)”的8％、2％、10%。任務(wù)16．事病假扣款額、所得稅和實(shí)發(fā)工資的計(jì)算根據(jù)“事病假扣款表”計(jì)算“事假扣款”和“病假扣款”，再計(jì)算“應(yīng)發(fā)工資”。應(yīng)發(fā)工資=應(yīng)發(fā)合計(jì)-三金-事假扣款-病假扣款計(jì)算所得稅和實(shí)發(fā)工資，其中個(gè)人所得稅起征額為3500，所得稅=（應(yīng)發(fā)工資-3500）*稅率-速算扣除數(shù)任務(wù)17．利用文本函數(shù)REPLACE對職工號(hào)進(jìn)行升級(jí)在“職工號(hào)”和“姓名”之間增加一列“新職工號(hào)”。對職工號(hào)進(jìn)行升位處理，在原“職工號(hào)”前增加“F1”8．高級(jí)篩選在數(shù)據(jù)分析中的應(yīng)用對工資表進(jìn)行分析，使其只顯示“銷售部”的“全勤”記錄。任務(wù)2e家人超市休閑食品銷售情況統(tǒng)計(jì)【任務(wù)提出】超市的營運(yùn)要涉及到很多貨物，每種貨物又可以有很多供貨商，還擁有不同的品牌和不同的價(jià)格，情況比較復(fù)雜，因此在超市管理上，貨物的庫存和銷售情況必須經(jīng)常統(tǒng)計(jì)，且統(tǒng)計(jì)和分析都比較繁瑣，因此可以借助Excel的數(shù)據(jù)統(tǒng)計(jì)和分析功能，來實(shí)現(xiàn)對超市中貨物及其銷售的管理?！救蝿?wù)分析】超市貨物銷售情況管理，首先應(yīng)具有一個(gè)所有商品的相關(guān)數(shù)據(jù)庫，該庫中的數(shù)據(jù)應(yīng)該包含商品名、規(guī)格、商品條碼、價(jià)格、庫存量等；要建立一個(gè)與銷售相關(guān)的表，當(dāng)商品售出后，庫存量信息要更新。借助數(shù)據(jù)庫函數(shù)、數(shù)據(jù)透視表和透視圖等對數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析。任務(wù)2——【相關(guān)知識(shí)與技能】1．函數(shù)數(shù)據(jù)庫函數(shù)數(shù)據(jù)庫信息函數(shù)DCOUNT、DCOUNTA、DGET數(shù)據(jù)庫分析函數(shù)DAVERAGE、DSUM、DMAX、DMIN信息函數(shù)用以確定存儲(chǔ)在單元格中數(shù)據(jù)的類型，返回值是邏輯型ISTEXT、ISNONTEXT、ISBLANK、ISNUMBER、ISLOGICAL任務(wù)2——【相關(guān)知識(shí)與技能】1．函數(shù)統(tǒng)計(jì)函數(shù)COUNT（COUNTA）、COUNTBLANK、COUNTIF；用來統(tǒng)計(jì)某些特定內(nèi)容的數(shù)據(jù)個(gè)數(shù)COUNT函數(shù)用來統(tǒng)計(jì)數(shù)值型數(shù)據(jù)個(gè)數(shù)；COUNTA函數(shù)用以統(tǒng)計(jì)非空單元格個(gè)數(shù)；COUNTBLANK函數(shù)用來統(tǒng)計(jì)空單元格個(gè)數(shù)；COUNTIF則是根據(jù)條件統(tǒng)計(jì)，統(tǒng)計(jì)符合條件的數(shù)據(jù)個(gè)數(shù)。AVERAGE、AVERAGEA、MAX、MAXA、MIN、MINARANK、RANK.AVG、RANK.EQ；返回一個(gè)數(shù)字在數(shù)字列表中的排位，數(shù)字的排位是其大小與列表中其他值的比值任務(wù)2——【相關(guān)知識(shí)與技能】2．?dāng)?shù)據(jù)透視表數(shù)據(jù)透視表是一種交互的、交叉制表的Excel報(bào)表，用于對多種來源（包括Excel的外部數(shù)據(jù)）的數(shù)據(jù)進(jìn)行匯總和分析1）創(chuàng)建數(shù)據(jù)透視表2）數(shù)據(jù)透視內(nèi)容清除3）數(shù)據(jù)透視表的刪除3．?dāng)?shù)據(jù)透視圖1.基于工作表數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視圖插入——表格——數(shù)據(jù)透視表”的下拉菜單中選擇“數(shù)據(jù)透視圖”2.基于已經(jīng)存在的數(shù)據(jù)透視表創(chuàng)建數(shù)據(jù)透視圖任務(wù)2——【相關(guān)知識(shí)與技能】4．切片器1 .創(chuàng)建切片器A，“插入——篩選器——切片器”B，“數(shù)據(jù)透視表工具”的“選項(xiàng)”選項(xiàng)卡上的“排序和篩選”組中，單擊“插入切片器”2.設(shè)置切片器格式3.多個(gè)數(shù)據(jù)透視表之間共享切片器4.切片器的刪除任務(wù)2——【任務(wù)實(shí)施】某超市休閑食品銷售情況表.xlsx任務(wù)2——【任務(wù)實(shí)施】1．用文本函數(shù)實(shí)現(xiàn)文本的連接運(yùn)算在Sheet1中，通過將“商品原名”和“規(guī)格”兩個(gè)字段值合并，生成“商品全名”，并填入C列的相應(yīng)位置。2．用信息函數(shù)檢測單元格中值的類型在Sheet2中，判斷“商品條碼”列的數(shù)據(jù)是否都是文本，并將結(jié)果填入“是否文本”列，是則為“Ture”，否為“False”。3．?dāng)?shù)值轉(zhuǎn)換為文本在Sheet2中，將“商品條碼”的內(nèi)容轉(zhuǎn)換為文本，并填入“商品條碼（文本）”字段。4．用查找和引用函數(shù)實(shí)現(xiàn)不同工作表間數(shù)據(jù)的查找根據(jù)Sheet2，對Sheet1中的“商品條碼”和“價(jià)格（元）”進(jìn)行填充，并計(jì)算“會(huì)員價(jià)（元）”、“周總銷售額（元）”，其中，會(huì)員價(jià)比超市原價(jià)優(yōu)惠8%，“周總銷售額（元）”為兩種銷售的銷售額之和。任務(wù)2——【任務(wù)實(shí)施】5．利用統(tǒng)計(jì)函數(shù)進(jìn)行數(shù)據(jù)統(tǒng)計(jì)首先用數(shù)組公式計(jì)算“周銷售總量（包）”和“售后庫存量（包）”。然后利用統(tǒng)計(jì)函數(shù)，統(tǒng)計(jì)周銷售量為0產(chǎn)品種數(shù)，填入B28。在表格最右邊添加一列“銷售額排名”，并對“周總銷售額（元）”進(jìn)行排名統(tǒng)計(jì)。6．用數(shù)據(jù)庫函數(shù)對銷售情況進(jìn)行統(tǒng)計(jì)在Sheet1中進(jìn)行如下統(tǒng)計(jì)：若庫存小于200就要進(jìn)貨，則需要進(jìn)貨的“洽洽香瓜子”種類數(shù)；求售后粉絲類的總庫存；求售后所有粉絲中，庫存量最大的商品庫存；求售后所有粉絲中，庫存量最小的商品庫存；獲取本周零銷售的商品全名。任務(wù)2——【任務(wù)實(shí)施】7．?dāng)?shù)據(jù)透視表的新建和切片器的應(yīng)用根據(jù)Sheet1中的“部分休閑食品銷售情況匯總表”，在Sheet4中新建2個(gè)數(shù)據(jù)透視表。數(shù)據(jù)透視表1：用來統(tǒng)計(jì)每種不同規(guī)格的商品的銷售情況，包括原價(jià)銷售和會(huì)員價(jià)銷售的數(shù)量。要求行標(biāo)簽為“商品全名”，數(shù)值區(qū)為“周原價(jià)銷售量（包）”和“周會(huì)員價(jià)銷售量（包）”，透視表從A1單元格開始數(shù)據(jù)透視表2：統(tǒng)計(jì)每種不同規(guī)格的商品在本周銷售前后的庫存數(shù)量情況。要求行標(biāo)簽為“商品條碼（文本）”，數(shù)值區(qū)為“庫存（包）”和“售后庫存（包）”，數(shù)據(jù)透視表從E1單元格開始要求插入一個(gè)以字段“商品原名”作為篩選條件的切片器，鏈接上述兩個(gè)透視表，同時(shí)在切片器中同時(shí)選中“龍口灣牌龍口粉絲”、“洽洽香瓜子”進(jìn)行多條件篩選任務(wù)3

蔡先生個(gè)人理財(cái)管理【任務(wù)提出】蔡先生是某公司高級(jí)白領(lǐng)，2012年時(shí)45歲，家有定期存款28萬元，持有股票型基金10萬元，股票5萬元，住房公積金15萬元，養(yǎng)老金賬戶12萬元。還有一套價(jià)值50萬元的房子，如今家里兒子漸大，房子不夠用，因此打算買一套新房子?，F(xiàn)有一套房子，價(jià)值100萬元，可以用80萬元購入，兩年后交付新房，蔡先生要交首付50萬元，剩余30萬元可以貸款?！救蝿?wù)分析】本任務(wù)主要對蔡先生的個(gè)人財(cái)產(chǎn)進(jìn)行理財(cái)管理，只涉及到“住房投資”和“養(yǎng)老金”投資業(yè)務(wù)。住房投資：蔡先生可以提取公積金15萬，出售股票基金10萬，出售股票5萬，存款20萬，湊成首付。30萬元貸款中，如果用商業(yè)貸款，則貸款利率為5.94%，如果用公積金貸款，貸款利率為5%，為了讓每個(gè)月的月供負(fù)擔(dān)不會(huì)太重（小于每月收入的30%），可以考慮組合貸款方式，貸款年限設(shè)為15年，其中公積金貸款15萬，商業(yè)貸款也是15萬。兩年后新房交付后，可以考慮將舊房賣掉，然后直接將房貸一次還清，從而可以節(jié)省利息。養(yǎng)老金投資蔡先生一直在交養(yǎng)老金，迄今為止已經(jīng)有養(yǎng)老金資產(chǎn)12萬元，今后每年還要繼續(xù)按照工資以一定比例方式繳納養(yǎng)老金保險(xiǎn)，一直到退休為止，該投資的報(bào)酬率為8%任務(wù)3——【相關(guān)知識(shí)與技能】1.函數(shù)財(cái)務(wù)函數(shù)PMT、PPMT、IPMTPV、FV數(shù)學(xué)和三角函數(shù)ABS、MOD、INTROUND，可將某個(gè)數(shù)字四舍五入為指定的位數(shù)使用ROUND函數(shù)來四舍五入到最接近的"x"的時(shí)間語法公式：“=ROUND(<時(shí)間值>*24/x,0)*x/24”2．外部數(shù)據(jù)的導(dǎo)入和導(dǎo)出1、外部數(shù)據(jù)導(dǎo)入：“數(shù)據(jù)——獲取外部數(shù)據(jù)”“自Access”、“自文本”、“自網(wǎng)站”2、數(shù)據(jù)導(dǎo)出“文件——另存為”任務(wù)3——【相關(guān)知識(shí)與技能】3．迷你圖它是工作表單元格中的一個(gè)微型圖表，可提供數(shù)據(jù)的直觀表示，能顯示一列（行）數(shù)值的趨勢，是單元格背景中的一個(gè)微型圖表。1）創(chuàng)建迷你圖2）設(shè)計(jì)迷你圖3）刪除迷你圖4．工作表的保護(hù)1.保護(hù)工作表“審閱——更改——保護(hù)工作表”2.取消工作表保護(hù)工作表保護(hù)后，“保護(hù)工作表”命令改為“取消工作表保護(hù)”單擊并輸入正確密碼可以取消保護(hù)任務(wù)3——【相關(guān)知識(shí)與技能】4．切片器1 .創(chuàng)建切片器A，“插入——篩選器——切片器”B，“數(shù)據(jù)透視表工具”的“選項(xiàng)”選項(xiàng)卡上的“排序和篩選”組中，單擊“插入切片器”2.設(shè)置切片器格式3.多個(gè)數(shù)據(jù)透視表之間共享切片器4.切片器的刪除任務(wù)3——【任務(wù)實(shí)施】“蔡先生的個(gè)人理財(cái)表.xlsx”任務(wù)3——【任務(wù)實(shí)施】1．計(jì)算房貸月供蔡先生的30萬房貸中，15萬商業(yè)貸款，利率為5.94%，1