萬盛學電腦網

 萬盛學電腦網 >> Wps Office >> wps綜合教程 >> WPS表格教程 打造簡單清晰成績分析表

WPS表格教程 打造簡單清晰成績分析表

  在每次成績公布時都需要面對一個難題:為了方便匯總《學生成績表》,每人成績占據一行;而每個學生之《成績通知單》為了美觀卻設計為多行多列。如何方便而快速地進行格式轉換?WPS表格的函數可以輕松地為您解決疑難。

  首先,讓我們看看《成績表》和《成績通知單》的格式。見圖1和圖2。

用WPS表格設計成績通知單   三聯

  圖1成績表

用WPS表格設計成績通知單

  圖2成績通知單

  解決思路:

  1.《成績表》和《通知單》分建立在兩個工作表中。

  2.《通知單》可以通過函數調用《成績表》中每個學生之資料。

  3.建立一個單元格下拉菜單供選擇所有學生姓名,當選項改變時,通知單跟隨改變。而且在現有資料上增加學生數量後不受影響,從而使通知單設計達到通用之功能。

  明確了思路,現在以實例具體講解實現方法。

  步驟1.定義名稱

  選擇《成績表》A1,點擊【插入】【名稱】【定義】,然後按圖3方式輸入信息:當前工作簿的名稱處輸入“姓名”(不含引號),在引用位置處輸入“=OFFSET($A$1,1,0,COUNTA(A:A)-1,)”。公式含義:通過COUNTA(A:A)-1返回A列學生數目,減1是因A列中非空單元格數量包括首行“姓名”二字;然後利用OFFSET函數提取A列所有學生姓名。

用WPS表格設計成績通知單

  圖3定義名稱

  步驟2.設置數據有效性

  進入《通知單》單元格A4,點擊【數據】【有效性】【設置】,在有效性條件之允許處選擇條件為序列,來源處輸入“=姓名”,同時確保右邊“提供下拉箭頭”前已打勾(見圖4),確定後點擊單元格I4試試效果(見圖5)

用WPS表格設計成績通知單

  圖4設置數據有效性

用WPS表格設計成績通知單

  圖5效據有效性建立之單元格下拉菜單

  步驟3.建立通知單基本格式

  按以下圖六建立一個空白格式,以待調用成績表之數據。

用WPS表格設計成績通知單

  圖6通知單格式

  步驟4.設計公式調用數據

  A.選中單元格C2,輸入以下公式:

  =VLOOKUP(I4,成績表!A2:D1000,4,0)&" "&I4&" 同學成績通知單"

  B.選中單元格D4,輸入公式:=I4

  C.選中單元格F4,輸入以下公式:

  =VLOOKUP(I4,成績表!A2:C1000,3,0)

  D.選中單元格H4,輸入以下公式:

  =VLOOKUP(I4,成績表!A2:B1000,2,0)

  E.選中單元格F5,輸入以下公式:

  =VLOOKUP(I$4,成績表!A$2:K$1000,ROW(A5),0)

  F.選中單元格F5,將公式向下拉至F11

  此時公式設計完畢,可以選擇單元格I4之下拉箭頭選擇不同學生姓名,可以看到《通知單》之數據有跟隨變化。後續打印時則通過下拉框打印隨時打印任何學生之成績,而不破壞原成績表之格式。

  公式解析:VLOOKUP是查找公式,有四個參數,分別為[查找值]、[數據表]、[序列數]、[匹配條件]。

  查找值即需要到目標區域查找之目標數據,可以輸入目標值,也可以是單元格引用;

  數據表是被查找之區域,本例中數據只有10個,而引用區域輸入A2:D1000是為了讓成績表中增加數據時通知單公式可以通用,不用再修改公式。若您的學生數目超過1000時可以將本引用域再相應增大,使公式能適應所有變化。

  序列數是指目標值在被查找區域所在的列數,輸入2,則返回目標區域第二列的值。

  匹配條件一般有兩個選項,0和1。0表示精確匹配,1表示近似匹配。可以根據不同狀況下選擇相應的匹配條件。

  結語:查找函數除VLOOKUP外還有HLOOKUP、LOOKUP、MATCH、INDIRECT、INDEX、OFFSET等等,只有靈活掌握這些函數才能讓WPS表格真正發揮潛能,讓很多人工查詢方式轉化為函數自動搜索,從而提升效率。

copyright © 萬盛學電腦網 all rights reserved