陳穎是某環(huán)境科學院的研究人員，現(xiàn)在需要使用Excel來分析我國主要城市的降水量。根據(jù)下列要求，幫助她完成這項工作。1. 在考生文件夾下，將“Excel_素材.xlsx”文件另存為“Excel.xlsx”（“.xlsx”為擴展名），后續(xù)操作均基于此文件，否則不得分。2. 在“主要城市降水量”工作表中，將A列數(shù)據(jù)中城市名稱的漢語拼音刪除，并在城市名后面添加文本“市”，如“北京市”。3. 將單元格區(qū)域A1:P32轉換為表，為其套用一種恰當?shù)谋砀窀袷?，取消篩選和鑲邊行，將表的名稱修改為“降水量統(tǒng)計”。4. 將單元格區(qū)域B2:M32中所有的空單元格都填入數(shù)值0；然后修改該區(qū)域的單元格數(shù)字格式，使得值小于15的單元格僅顯示文本“干旱”；再為這一區(qū)域應用條件格式，將值小于15的單元格設置為“黃色填充深黃色文本”。（注意：不要修改單元格中的數(shù)值本身）5. 在單元格區(qū)域N2:N32中計算各城市全年的合計降水量，對其應用實心填充的數(shù)據(jù)條條件格式，并且不顯示數(shù)值本身。6. 在單元格區(qū)域O2:O32中，根據(jù)“合計降水量”列中的數(shù)值進行降序排名。7. 在單元格區(qū)域P2:P32中，插入迷你柱形圖，數(shù)據(jù)范圍為B2:M32中的數(shù)值，并將高點設置為標準紅色。8. 在R3單元格中建立數(shù)據(jù)有效性，僅允許在該單元格中填入單元格區(qū)域A2:A32中的城市名稱；在S2單元格中建立數(shù)據(jù)有效性，僅允許在該單元格中填入單元格區(qū)域B1:M1中的月份名稱；在S3單元格中建立公式，使用Index函數(shù)和Match函數(shù)，根據(jù)R3單元格中的城市名稱和S2單元格中的月份名稱，查詢對應的降水量；以上三個單元格最終顯示的結果為廣州市7月份的降水量。
9. 按照如下要求統(tǒng)計每個城市各月降水量以及在全年中的比重，并為其創(chuàng)建單獨報告，報告的標題和結構等完成效果可參考考生文件夾下的圖片“城市報告.png”。① 每個城市的數(shù)據(jù)位于一張獨立的工作表中，工作表標簽名為城市名稱，如“北京市”。② 如參考圖片“城市報告.png”所示，各月份降水量數(shù)據(jù)位于單元格區(qū)域A3:C16中，A列中的月份按照1-12月順序排列，B列中為對應城市和月份的降水量，C列為該月降水量占該城市全年降水量的比重。③ 不限制計算方法，可使用中間表格輔助計算，中間表格可保留在最終完成的文檔中。10. 在“主要城市降水量”工作表中，將紙張方向設置為橫向，并適當調整其中數(shù)據(jù)的列寬，以便可以將所有數(shù)據(jù)都打印在一頁A4紙內。11. 為文檔添加名稱為“類別”，類型為文本，值為“水資源”的自定義屬性。
1、選擇Excel素材文件復制，新文件命名為“Excel”2、選擇A列數(shù)據(jù)復制，然后打開word → 在word里粘貼 → 只保留文本光標定位查找內容→點擊更多→特殊格式→選擇任意字母
輸入后如圖所示，點擊全部替換→此時所有字母都被刪除了再次單擊替換→在查找內容輸入^p 替換為那里輸入 市^p → 全部替換，此時后面全部都有了市3、選擇A1:P32，開始→隨便套用一種表格格式→表包含標題→確定點擊表格工具→設計→取消勾選鑲邊行，表名稱改為“降水量統(tǒng)計”4、選擇B2:M32區(qū)域→開始→查找和選擇→定位條件單擊確定之后，鼠標不要點任何地方，直接按鍵盤上的0，然后按ctrl+enter →此時所有空單元格都輸入了0繼續(xù)選擇這部分區(qū)域→條件格式→突出顯示單元格規(guī)則→小于再次選擇這部分區(qū)域→條件格式→突出顯示單元格規(guī)則→小于→同樣輸入15→格式選擇黃色填充，深黃色文本選擇N2:N32區(qū)域→條件格式→數(shù)據(jù)條→其他規(guī)則
勾選“僅顯示數(shù)據(jù)條”，選擇實心填充，確定6、在O2單元格輸入排名函數(shù)RANK.EQ （函數(shù)的參數(shù)）RANK.EQ參數(shù)（排名的數(shù)據(jù)，排名的區(qū)域，0表示降序/1表示升序）排名的數(shù)據(jù)即N2單元格，排名的區(qū)域為N2:N32，0表示降序選中P2單元格→迷你圖工具選項卡→標記顏色→高點→紅色，剩下的自動填充允許條件選擇序列，來源選擇城市的區(qū)域→確定
同理選擇S2單元格，數(shù)據(jù)驗證→允許選擇序列，來源選擇B1:M1區(qū)域
題中要求在S3引用index函數(shù)和match函數(shù)。先介紹一下這兩個函數(shù)。index函數(shù)：在給定的單元格區(qū)域中，返回特定行列交叉處單元格的值或引用。index參數(shù)（引用的區(qū)域，引用的行數(shù)，引用的列數(shù)）最終的結果就是這個區(qū)域中第幾行和第幾列交叉處的值。題中要求返回廣州市和7月的降水量，所以我們的區(qū)域就是B2:M32
而廣州市和7月的所在行數(shù)和列數(shù)要用match函數(shù)返回。MATCH函數(shù)：返回指定數(shù)值在指數(shù)據(jù)區(qū)域中的位置MATCH參數(shù)（數(shù)值，區(qū)域，-1/1/0）所以我們只需要查找廣州市和7月再對應區(qū)域的行數(shù) 和列數(shù)。廣州市的在A2:A32的第幾行，即=MATCH(R3,A2:A32,0)7月在B1:M1的第幾列，即=MATCH(S2,B1:M1,0)9、①選擇A1:M32區(qū)域→插入→數(shù)據(jù)透視表 分析選項卡→數(shù)據(jù)透視表的選項→顯示報表篩選頁
光標定位B3單元格→利用vlookup函數(shù)查找重慶市的降水量，并返回1月的值vlookup函數(shù)（查找的數(shù)據(jù)，查找的范圍，返回的值在范圍的第幾列，0）即=vlookup（重慶市，主要城市降水量的范圍，1月在范圍的第2列，0）此時1月的數(shù)據(jù)就求出來了，那么2月返回的列數(shù)是在范圍的第3列，我們把2改成3即可
所以剩下的我們自動填充，然后把第3個參數(shù)改成對應的月份在范圍的第幾列即可。最后結果如下圖所示全年占比=每月降水量/總計單元格。 總計單元格需要鎖定。剩下的自動填充到C16單元格。選擇C4:C16單元格→右擊設置單元格格式→2位小數(shù)→確定
（圖中的底紋顏色可以不設置，考試不扣分）
選擇我們做好的數(shù)據(jù)區(qū)域，復制然后按住shift鍵選擇底部各城市的工作表，在A3單元格中粘貼，此時每個表就都有了數(shù)據(jù)。10、在頁面布局選項卡→頁面對話框啟動器→選擇紙張方向為橫向→選擇調整為1頁高1頁寬→紙張大小選擇A4→確定，最后適當縮小列寬。
點擊自定→名稱輸入“類別”，類型為“文本”，取值輸入“水資源”→確定。
|
