計算機應(yīng)用基礎(chǔ)實訓(xùn)項目五--補助金額查詢表課件

1、第三部分 Excel 2010電子表格制作項目五 補助金額查詢表本項目我們主要介紹的是countif和sumif這兩種函數(shù)來計算表格數(shù)據(jù)值，此外再使用數(shù)據(jù)有效性來控制單元格內(nèi)容的輸入。使用查找與引用的vlookup函數(shù)完成補助金各字段的查詢?nèi)蝿?wù)。案例分析Countif和sumif函數(shù)計算數(shù)據(jù)有效性的設(shè)置Vlookup函數(shù)完成數(shù)據(jù)查詢相關(guān)知識點打開素材文件夾中的“補助金查詢.xlsx”工作簿，在補助金額那欄的G2單元格中輸入計算公式“=$J$5*E2-F2”，拖動填充柄，完成補助金額的計算。任務(wù)一 公式計算補助金額1、在“匯總”工作表中，單擊D5單元格，運用函數(shù)計算各街道補助金額發(fā)放人數(shù)的總和。

2、打開【公式】選項卡，選擇“其他函數(shù)”“統(tǒng)計”下的“countif”函數(shù)，計算“街道”所在列中各個街道人員的數(shù)量總和。任務(wù)二 countif統(tǒng)計人數(shù)2、在打開的countif函數(shù)參數(shù)設(shè)置中，第一個空格引用了“數(shù)據(jù)”工作表中的B2：B101單元格區(qū)域，在第二個單元格中，引用條件即C5單元格，計算出鳳凰街道的人數(shù)總和。任務(wù)二 countif統(tǒng)計人數(shù)3、雙擊D5單元格，打開函數(shù)計算表達式，將表達式中的“$B$2:$B$101”改成絕對引用地址，即把函數(shù)改成“=COUNTIF(數(shù)據(jù)!$B$2:$B$101,C5)”。任務(wù)二 countif統(tǒng)計人數(shù)4、拖動填充柄，完成其他數(shù)據(jù)的計算，并用求和函數(shù)計算人數(shù)總

3、和。任務(wù)二 countif統(tǒng)計人數(shù)1、用鼠標選中E5單元格，打開【公式】選項卡下的“數(shù)字和三角函數(shù)”，選擇“SUMIF”函數(shù)，計算各個街道補助金額的總和。任務(wù)三 sumif計算各街道金額2、在彈出的SUMIF函數(shù)的參數(shù)設(shè)置對話框中，依次在三個空格中輸入或者引用單元格區(qū)域“數(shù)據(jù)!B2:B101”、“C5”和“數(shù)據(jù)!G2:G101”，完成第一個鳳凰街道的補助金額的數(shù)值求和。任務(wù)三 sumif計算各街道金額3、雙擊D5單元格，將其中“數(shù)據(jù)”工作表的B列和G列區(qū)域設(shè)置為絕對引用地址，鎖定住這兩塊區(qū)域，函數(shù)表達式修改為“=SUMIF(數(shù)據(jù)!$B$2:$B$101,C5,數(shù)據(jù)!$G$2:$G$101)”。

4、任務(wù)三 sumif計算各街道金額4、再用拖動填充柄復(fù)制單元格函數(shù)的方法求出其他街道的金額總數(shù)，最后使用求和函數(shù)求出E12單元格的總金額。任務(wù)三 sumif計算各街道金額1、打開“查詢”工作表，選中E5單元格，打開【數(shù)據(jù)】選項卡中的“數(shù)據(jù)有效性”。任務(wù)四 數(shù)據(jù)有效性設(shè)置2、在“數(shù)據(jù)有效性”對話框中，單擊“設(shè)置”選項卡，在“允許”下拉列表中選擇“序列”選項。單擊“來源”編輯框右側(cè)的“折疊”按鈕，然后用鼠標選取“數(shù)據(jù)”工作表中的A2：A101單元格區(qū)域，再單擊“展開”按鈕返回“數(shù)據(jù)有效性”對話框，“數(shù)據(jù)”工作表中的A2：A101就成為了“序列”的來源。任務(wù)四 數(shù)據(jù)有效性設(shè)置3、單擊“確定”按鈕后，返

5、回工作表，在E5單元格右側(cè)會出現(xiàn)下拉列表，可以從中選擇所有的序號，方便快捷且不容易出錯。任務(wù)四 數(shù)據(jù)有效性設(shè)置3、單擊“確定”按鈕后，返回工作表，在E5單元格右側(cè)會出現(xiàn)下拉列表，可以從中選擇所有的序號，方便快捷且不容易出錯。任務(wù)四 數(shù)據(jù)有效性設(shè)置1、回到“數(shù)據(jù)”工作表，在表格中選中A2：G102單元格，在表格的左上方的位置輸入“數(shù)據(jù)”字樣，命名這塊單元格區(qū)域的名字為“數(shù)據(jù)”。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)2、再返回“查詢”工作表，單擊E8單元格，打開【公式】選項卡中的“查找與引用”下的VLOOKUP函數(shù)，通過這個函數(shù)的查找引用的功能，利用序號，找到每一個人員所在的街道、居委會、戶主姓名

6、、人口、救助金額等內(nèi)容。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)3、在VLOOKUP函數(shù)的參數(shù)設(shè)置對話框中，共有4個空格。第一個表示需要查找與引用的關(guān)鍵字，我們用序號來查找，因此，此處填入“E5”單元格。第二個空格表示的是需要查找信息的原始表格區(qū)域名稱，我們在前面已經(jīng)將A2：G101單元格命名為“數(shù)據(jù)”，因此，此處輸入“數(shù)據(jù)”即可。第三個空格指的是我們查找的E8單元格需要顯示“街道”的這個內(nèi)容，在“數(shù)據(jù)”區(qū)域中是第幾列，根據(jù)數(shù)據(jù)所在區(qū)域可以看出“街道”所在的位置是表格的第2列。同樣的“居委會”是第3列，“戶主姓名”是第4列，“人口”是第5列，“補助金額”是第7列。第四個空格如果需要精確查找的話，

7、可以忽略不計。根據(jù)我們的要求，依次在4個空格中填入內(nèi)容。單擊“確定”按鈕返回表格。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)4、當我們在E5單元格中，在下拉列表下選擇任意一個序號，在E8單元格就會出現(xiàn)所對應(yīng)的街道名稱，順利的完成了數(shù)據(jù)的查找與引用。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)5、雙擊E8單元格，將VLOOKUP函數(shù)表達式修改成“=VLOOKUP($E$5,數(shù)據(jù),2)”。根據(jù)查詢要求，我們再依次在E9、E10、E11、E12等單元格中復(fù)制或者輸入“=VLOOKUP($E$5,數(shù)據(jù),3)”、“=VLOOKUP($E$5,數(shù)據(jù),4)”、“=VLOOKUP($E$5,數(shù)據(jù),5)”、“=VLOOKUP($E$5,數(shù)據(jù),7)”等函數(shù)表達式。完成“居委會”、“戶主姓名”、“人口”、“補助金額”等數(shù)據(jù)的查詢與引用。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)6、在E5單元格中任意選擇幾個序號，查看VLOOKUP函數(shù)所制作出來的查詢引用效果。任務(wù)五 使用vlookup函數(shù)查詢數(shù)據(jù)本項目是對補助金數(shù)據(jù)進行查詢。先使用countif函數(shù)和sumif函數(shù)對金額進行計算，然后設(shè)定“序號”字段的數(shù)據(jù)有效性，最后使用vlookup函數(shù)進行各種字段數(shù)據(jù)的查詢。案例總結(jié)打開素材文件夾中的“家電銷售