網頁

2016年11月27日 星期日

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

(2022/11/25 修改)
請先完成 Excel 實作 (1)的作業
從雲端硬碟 下載自己上次 Excel 實作 (1)的作業檔。
(沒有的話,改用 (老師的檔案),但檔案/資訊/摘要資訊/進階摘要資訊, 摘要資訊中的作者,改成自己的座號姓名)
檔名改為 : 01王小明_段考成績_實作2.xlsx
請好好利用  Alt + Tab ,可在二個工作視窗快速切換 (如 老師的部落格 和Excel )。
如何複製工作表?
(1)選「成績總表」此工作表
(2)按右鍵,選 「移動或複製」
(3)選「個人成績查詢」,複製出來的工作表會放在 「個人成績查詢」此工作表的前面。
(4) 建立複本 請打勾。如果沒有打勾,變成移動。因為你可以把此工作表移到另一個活頁簿中。





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

選 複製出來的工作表如 「成績總表(2)」,按右鍵,選「重新命名」,改為「成績總表 -資料篩選」。

選 列18到列24,按右鍵,選「刪除」,把這些列刪除掉。

如何把國文、英文、數學 都大於80分的學生篩選出來?
使用進階篩選
先在 O4、P4、Q4三個儲存格皆輸入  >80 ,  大於和80中間不要有空白。
三個寫成同一列表示 而且 的意思。即篩選準則 為國文 >80 而且 英文 >80 而且 數學 >80。

移到某個欄位的名稱,如 「地理」,選「資料」此功能索引標籤,選 「進階」 鈕。

進階篩選視窗的設定:
勾選「篩選結果複製到其他地方」。
資料範圍 ,請 按  BackSpace 鍵刪除原來的範圍,再按右邊 的折疊鈕, 讓進階篩選此視窗變小。
以便選取正確的資料來源 : $A$3:$M$16

準則範圍,選 $O$3:$Q$4,結果變成 Criteria。
複製到 , 選 儲存格 $A$20。

按「確定」鈕後,在 A19 此儲存格輸入 : 結果1。


如何篩選出符合任一列條件的資料?
選取儲存格 O3:Q3,按 Ctrl + C,複製.
游標移到 O6, 按 Ctrl + V,貼上。把篩選準則的欄標題複製過來。

O7 儲存格輸入 >80。  P8、Q9 儲存格也分別輸入 >80,請用手動輸入,不要用複製部落格的 >80,以免錯誤

三個篩選準則寫在不同列,表示 "或者" 的意思,也就是篩選的準則是:
國文 > 80 或者 英文 > 80 或者 數學 > 80,祇要其中一個條件成立就算找到。

選「資料」此功能索引標籤,選 「進階」 鈕。

  (1)資料來源,仍然維持 $A$3:$M$16
  (2)準則範圍,改成  $O$6:$Q$9
  (3)複製到 , 選 儲存格 $A$24。

按確定後,在 A23 此儲存格輸入 : 結果2

結果1的前面要空一列,不然做完下面的自動篩選,進階篩選的結果會不見。
請 按結果1此列,再按右鍵,選「插入」。



結果:

如何使用自動篩選?
(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,按右鍵,選「刪除」。把這些列刪除掉。
列17保留,不刪除。

如何排序資料?
以班級欄位作遞增排序(由小排到大),若班級相同,再依姓名遞增排序。
(1) 選 儲存格 A3 :M16。
註: 因列17 「平均」和儲存格 A3 :M16 本身連在一起,所以課本寫選取含資料的任一儲存格,在資料的排序與篩選區,按排序。此做法會連平均也一起排序。除非在列17 按右鍵,插入,在前面插入一列隔開。
(2)按「資料」
(3)按「排序」
(4)排序方式,選 「班級」 。因為班級是文字資料,所以 順序 出現 A到Z , 表示遞增 。
「我的資料有標題」,打勾。表示只會針對資料內容排序(不含標題列)
(5)按「新增層級」
以下祇是說明,不用做。
--------------------------------------------------------------------------------
註1:若是以加權總分來排序,因加權總分是數值性資料,所以 順序會出現 最小到最大、最大到最小。
註2:若按「選項」鈕,「班級」預設是以 「循欄排序」,「依筆劃排序」。
次要排序方式,選 「姓名」,也是遞增排序(從姓名第一個字依筆劃開始判斷,由小排到大) ,按「確定」。
--------------------------------------------------------------------------------
結果如下,「資二1」 排在前面,且依姓名由小排到大。再來「資二2」,最後是「資二3」。
















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

以下不用做 。
------------------------------------------
如何新增工作表?
選「成績總表_排序」 此工作表,按右鍵  ,選 「插入」。
選「工作表」,按「確定」。這樣可以新增一個工作表。
選新的工作表,按右鍵,重新命名,改為 「自訂清單排序」。
如何作自訂清單排序?
在A1 輸入 星期。
A2 輸入 星期日,接著按A2 儲存格右下方的填滿控點,往下拉,直到出現 星期六。
下面資料 以星期 此欄位作排序,順序 「A 到 Z」,預設是依筆劃由小排到大。
結果為:

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

結果為:
------------------------------------------
小計要做。
如何作小計?
資料要先經過排序,才能小計
選「成績總表 -排序」此工作表,資料已經依班級排序,所以才能依班級作小計。
(1)選 儲存格 A3 :M16
(2)按「資料」
(3)按「小計」
小計選項的設定:

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


 將 J 欄 (平均) 中有 「 #DIV/0!」刪除,如選 先J13( 同學的有錯誤儲存格可能跟我不一樣),按 Del鍵,把「 #DIV/0!」錯誤訊息刪除。後面的 J15 、J20 就沒有錯誤訊息。如果還有錯誤訊息,再把這些儲存格的內容刪除。
將 I 欄(總分) 中 各班的小計平均和總計平均數,取整數。
如 選 I10,按 Ctrl 鍵不放,再選 I15、 I19,、I20、, 四個儲存格。(這四個儲存格,同學可能跟我不一樣)
選「常用」,重複按「減少小數位數」 鈕,直到變成沒有小數點的整數。



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

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

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

插入/頁首及頁尾
工作表選「成績總表 -排序」。
插入的文字區,按頁首及頁尾。
若出現以下訊息,按「確定」鈕。

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

在頁尾: (在 Excel 2019 , 看不到頁尾,請改用下面註2的方式,進入頁尾模式 )
(1)在頁尾中間欄位,插入「頁碼」,其功能變數為 &[頁碼]。再輸入 「/ 」
(2)插入「頁數」,其功能變數為 &[總頁數]。

註1:按頁首及頁尾鈕後,Excel會自動將檢視模式切換為整頁模式,若要回到標準模式,可在檢視的活頁簿檢視區,按標準模式鈕。
註2:進入頁首頁尾另一種方式:
在版面配置的版面設定區,按右邊的 細部設定 鈕。
切換至「頁首/頁尾」標籤,也可設定頁首/頁尾資料。
按「自訂頁首」鈕,先插入「日期」,按空白,再插入「時間」。
按「自訂頁尾」鈕,先插入「頁碼」,輸入  / ,再插入「總頁數」。
檔案/列印,檢查頁首/頁尾 設定是否正確。

如何作頁面設定?
版面配置,按右邊的 細部設定 鈕
按「頁面」,縮放比例設為 100%。
切換到「邊界」標籤,置中方式 /水平置中,前面打勾
按「預覽列印」鈕(或者按 「檔案/列印」。
原來工作表的右邊有些資料不見了(其實是跑到另外一頁),總共12頁。
檢視/分頁預覽
會看到有12頁,藍色線不管是虛線或實線都是分頁線,而灰底的儲存格,表示不在列印範圍內。


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

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

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

會改以藍色實線來顯示,目前變成4頁。
(1)將列20下面的藍色實線移到列21下面,變成3頁
(2) Q欄右邊分頁線移到M欄

(如果 評等有做 小計,才做下面第3項)
(3)按住 Ctrl鍵不放(不連續選取),選 M10、M15、M19、M20, 4個儲存格,接著 按 Del鍵,將 #DIV/0! 錯誤訊息刪除。因為 評等無法求平均。

最後變成3頁。
檔案/列印,檢查結果是否跟下面一樣?
當你按下一頁時,會發現沒有標題列,怎麼辦呢?

如何跨頁印標題?
先離開預覽列印模式。
有二種方式,可以設定跨頁印標題 :
1. 版面配置/版面設定,按「列印標題」。
2.或者 按 右邊 細部設定鈕。

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

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

結果第2頁有三列標題。

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