網頁

2016年11月27日 星期日

Excel 實作 (2)- 工作表複製、篩選、排序、小計、頁首頁尾、分頁預覽

請先完成 Excel 實作 (1)的作業
從雲端硬碟 下載自己上次 Excel 實作 (1)的作業檔。

如何複製工作表?
(1)選「成績總表」此工作表
(2)按右鍵,選 「移動或複製」
(3)選「個人成績查詢」,把複製的工作表放在 「個人成績查詢」此工作表的前面
(4) 建立複本 請打勾。如果沒有打勾,變成移動。因為你可以把此工作表移到另一個活頁簿中。

另一種方式: 選 「成績總表」此工作表,同時按住 Ctrl鍵不放,拖曳到「個人成績查詢」工作表的前面,拖曳時會出現 + 符號,表示複製。沒按住 Ctrl鍵,表示移動,拖曳時不會出現 + 符號。

選 複製出來的工作表如  成績總表(1) 或者成績總表(2),按右鍵,選「重新命名」,改為「成績總表 -資料篩選」。
選 列18到列24,按右鍵,選「刪除」,把這些列刪除掉。

如何把國文、英文、數學 都大於80分的學生篩選出來?
使用進階篩選
(1)先在 O4、P4、Q4三個儲存格皆輸入  > 80
三個寫成同一列表示而且的意思。即篩選準則 為國文 > 80 而且 英文 > 80 而且 數學 > 80。
(2)選「資料」此功能索引標籤
(3)選 「進階」 鈕

(1)將篩選結果複製到其他地方,前面打勾
(2) 資料範圍 ,請 按 BackSpace 刪除原來的範圍,再按右邊 的折疊鈕, 讓進階篩選此視窗變小,以便選取正確的資料來源 : $A$3:$M$16
(3)準則範圍,選 $O$3:$Q$4
(4)複製到 , 選 儲存格 $A$20。
按確定後,在 A19 此儲存格輸入 : 結果1

按右邊 折疊鈕的結果:


進階篩選視窗的設定:


結果1:

如何篩選出符合任一列條件的資料?
(1)選取儲存格 O3:Q3,按 Ctrl + C,複製.
游標移到 O6, 按 Ctrl + V,貼上。把篩選準則的欄標題複製過來。
(2)O7 儲存格輸入 >80。  P8、Q9 儲存格也分別輸入 > 80。三個篩選準則寫在不同列,表示 "或者" 的意思,也就是篩選的準則是:
國文 > 80 或者 英文 > 80 或者 數學 > 80,其中有一個條件成立就算找到。
(3)資料來源,仍然維持 $A$3:$M$16
(3)準則範圍,改成  $O$6:$Q$9
(4)複製到 , 選 儲存格 $A$24。
按確定後,在 A23 此儲存格輸入 : 結果2


 結果2如下:

如何使用自動篩選?
(1)選取 儲存格  A3:M16此範圍
(2)資料/ 篩選 鈕
(3)按 「國文」右邊的倒三角形鈕
(1)按國文右邊的倒三角形鈕
(2)數字篩選
(3)大於
輸入 80,也就是篩選條件為  大於 80。 最後按 「確定」鈕。


進一步再篩選英文 大於 80分 的做法:
(1)按「英文」右邊的倒三角形鈕
(2)數字篩選
(3)大於
輸入 80。也就是篩選條件為  英文大於 80
你會發現,結果比前面更少,依我的例子來說祇剩 2筆資料符合。自動篩選設定二個欄位的條件,是而且的意思。也就是國文 大於 80 而且 英文 大於 80。
依此類推,設定數學大於80分。

最後結果,符合 :國文 > 80 而且 英文 > 80 而且 數學 >80 ,祇有二筆(其它不符合條件的列都隱藏起來)。前面的列號變成藍色,如下圖的 4和8 。有設定自動篩選的欄位其右邊的圖形由 倒 三角形,變成一個小倒三角形加上漏斗的圖示。
自動篩選設定三個欄位條件,是而且的意思。結果和 進階篩選 寫成同一行 的結果1 一樣。


參考:
如何把自動篩選 取消?
只要在已設定自動篩選的儲存格,按 資料/ 篩選 鈕 即可。就可把所有的篩選條件清除。

不過你要按 復原, 因為老師評分是要看篩選 後的結果。

選 「成績總表」此工作表,同時按住 Ctrl鍵不放,拖曳到「個人成績查詢」工作表的前面,拖曳時會出現 + 符號,表示複製。
選 複製出來的工作表如  成績總表(2),按右鍵,選「重新命名」,改為「成績總表 -排序」
選 列18到列24,按右鍵,選「刪除」。把這些列刪除掉。

如何排序資料?
以班級欄位作遞增排序(由小排到大),若班級相同,再依姓名遞增排序。

(1) 選 儲存格 A3 :M16。

註: 因列17 平均和儲存格 A3 :M16 本身連在一起,所以課本寫選取含資料的任一儲存格,在資料的排序與篩選區,按排序。此做法會連平均也一起排序。除非在列17 按右鍵,插入,在前面插入一列隔開。

(2)按「資料」
(3)按「排序」
(4)排序方式,選 「班級」 。因為班級是文字資料,所以 順序 出現 A到Z , 表示遞增 。
「我的資料有標題」,打勾。表示只會針對資料內容排序(不含標題列)
(5)按「新增層級」
註1:若是以加權總分來排序,因加權總分是數值性資料,所以 順序會出現 最小到最大、最大到最小。
註2:若按「選項」鈕,預設是以 循欄排序,依筆劃排序



次要排序方式,選 「姓名」,也是遞增排序(從姓名第一個字依筆劃開始判斷,由小排到大) ,按「確定」。
結果如下,資二1 排在前面,且依姓名由小排到大。再來資二2,最後是資二3。

















註:Excel 2010 排序層級最多64層,而Word表格內的排序最多3層,畢竟Excel試算表的功能強過 Word。

如何新增工作表?
選「成績總表_排序」 此工作表,按右鍵  ,選 「插入」。
選「工作表」,按「確定」。這樣可以新增一個工作表。
選新的工作表,按右鍵,重新命名,改為 「自訂清單排序」。

如何作自訂清單排序?
在A1 輸入 星期。
A2 輸入 星期日,接著按A2 儲存格右下方的填滿控點,往下拉,直到出現 星期六。

下面資料 以星期 此欄位作排序,順序 「A 到 Z」,預設是依筆劃由小排到大。

結果為:

 以星期 此欄位作排序,順序改用「自訂清單」。

結果為:

如何作小計?
資料要先經過排序,才能小計
上述資料已經依班級排序,所以才能依班級作小計。
(1)選 儲存格 A3 :M16
(2)按「資料」
(3)按「小計」

小計選項的設定:
(1)分組小計欄位:選班級
(2)使用函數 :平均值
(3)新增小計位置 :總分和平均。
說明 :
(2)和(3)的意思 :我們要 求出 各班每位學生總分的平均,和各班每位學生平均的平均
(4)取代目前小計:預設為打勾。
說明:取代目前小計預設勾選,僅會保留最後一次小計的結果;若要將每次小計的結果保留下來,必須取消勾選。
(5)每組資料分頁:預設為不打勾,請改為 打勾。讓每班印一頁。若不打勾,則三班全部印在一起。
說明 :勾選後,Excel會自動在每組資料的下方插入分頁線,可讓工作表在列印時,各組資料分開列印。

(6)摘要置於小計資料下方:預設為打勾。產生出來的小計和總計放在下方。
說明 :設定小計列及總計列置於分組資料的上方或下方。預設為勾選,表示置於下方;取消勾選表示置於上方。
按「確定」鈕

小計結果:

 選 J13、J15 ,按 Del鍵,把 「 #DIV/0!」   錯誤訊息刪除。
選 I10,按 Ctrl鍵不放,再選 I19,、I20, 三個儲存格。
選「常用」,重複按「減少小數位數」 鈕,變成沒有小數點的整數。

說明:
小計左邊有 1 2 3 ,表示層級的編號,總計屬於第1層、小計屬於第2層、明細資料屬於第3層。單按層級的編號,會顯示第1層到該層級的資料。
如按2,會顯示總計列和小計列。


旗立課本寫 單按層級的編號,會顯示該層級的資料,如按2,僅會顯示小計列,這是錯的。
按「 -」  鈕可隱藏明細,按 「+ 」鈕可顯示明細。

(以下祇是 參考 ,若 移除小計,因 無法復原,請重新作小計!!)
 如何移除小計?
(1)先選 儲存格 A3:M20
(2)按 資料/小計
(3) 按「全部移除」鈕 


插入/頁首及頁尾

工作表選「成績總表 -排序」。
插入的文字區,按頁首及頁尾。

進入頁首頁尾
(1)在頁首的左邊欄位,輸入自己的座號和姓名。
(2)右邊欄位,插入「目前日期」,其功能變數為 &[日期]。
(3) 記得按空白鍵,讓日期後面有空格。  
(3)插入「目前時間」,其功能變數為   &[時間]。
(4)按「移至頁尾」

在頁尾:
(1)在頁尾中間欄位,插入「頁碼」,其功能變數為 &[頁碼]。再輸入 「/ 」
(2)插入「頁數」,其功能變數為 &[總頁數]。

註1:按頁首及頁尾鈕後,Excel會自動將檢視模式切換為整頁模式,若要回到標準模式,可在檢視的活頁簿檢視區,按標準模式鈕。
註2:另外,在版面配置的版面設定區,按右下方的 細部設定 鈕,切換至頁首/頁尾標籤,也可設定頁首/頁尾資料。

如何作頁面設定?
版面配置,按右下方的 細部設定 鈕

按「頁面」,縮放比例預設是 100%,再按「預覽列印」
原來工作表的右邊有些資料不見了(其實是跑到另外一頁),怎麼辦?

在 預覽列印 狀態下,直接按左下方的 「版面設定」

在版面設定的頁面,縮放比例改成 「調整成1頁寬1頁高」,可讓整個工作表自動縮成1頁紙的大小。
這對於 分成二頁印,結果第2頁祇有少許資料 或者 工作表右邊的資料印在下一頁,很有用。

切換到「邊界」標籤,置中方式 /水平置中,前面打勾。按「確定」。
再按右下角的「縮放至頁面」鈕(左邊的按鈕是 「顯示邊界」,可調整上下左右邊界),可看到整頁紙印出的效果。工作表的內容全部印成1頁。


在版面設定 /頁面 ,縮放比例 請再改成 100% 一般大小,否則目前祇有一頁,不是12頁。

檢視/分頁預覽
會看到有12頁,藍色線不管是虛線或實線都是分頁線,而灰底的儲存格,表示不在列印範圍內。


上面是「循欄列印」的結果,由上往下編頁,第1頁、第2頁、第3頁 、第4頁,再來是第5頁、第6頁、第7頁 、第8頁,依此類推。

如果改成「循列列印」,結果為 由左往右編頁,第1頁、第2頁、第3頁,再來是第4頁、第5頁、第6頁 等,依此類推。


將藍色虛線的分頁線移到最右邊,如下圖所示:

會改以藍色實線來顯示,目前變成4頁。
(1)將列20下面的藍色實線移到列21下面
(2) Q欄右邊分頁線移到M欄
(3)按住 Ctrl鍵不放(不連續選取),選 M10、M15、M19、M20, 4個儲存格,接著 按 Del鍵,將 #DIV/0! 錯誤訊息刪除。因為 評等無法求平均。

最後變成3頁。

檔案/列印,檢查結果是否跟下面一樣?

當你按下一頁時,會發現沒有標題列,怎麼辦呢?

如何跨頁印標題?
離開預覽列印模式, 版面配置/版面設定,按 右下方細部設定鈕


在版面設定交談窗:
1.切換到「工作表」標籤
2.按「標題列」右邊的 摺疊鈕,將視窗縮小,以方便選要重複列印的標題列 $1:$3

按住不放選列1到列3,這三列要出現在每一頁。一般是出現 $1:$3,如果最後 出現 Print_Titles 也可以。

第2頁有三列標題

存檔,檔名為: 01王小明_段考成績.xlsx,請務必改成為自己的座號和姓名。上傳到老師的classroom。

沒有留言 :

張貼留言