學不完．教不停．用不盡

網友問到一個應該很多人會遇到的問題，不難但實用。問題：如何在一個 Excel 文件上顯示目前的時間？

如下圖，現在時間要顯示在文件的右上角。

首先，要獲取現在的日期、時間預設的快速鍵：

(1) 插入目前日期，按 Ctrl+; (分號)。

(2) 插入目前時間，按 Ctrl+Shift+; (分號)。

同事問到：最近看到網路上的 Excel 討論文章，是關於運算上的Bug。

例如，下圖中，公式 SUM(A6:E6)，原本應為 2，卻顯示為 5。

儲存格F2：=SUM(A2:E2)=5

如果，設定合併四個儲存格，並且只留下第1個儲存格內容

儲存格F4：=SUM(A4:E4)=2

如下圖，要根據早上及下午的上班卡、下班卡時間報表來計算工作時間長度。

【範例一】

因為資料來源分為早上及下午的上班卡、下班卡共四個儲存格，所以分別取出運算即可。

儲存格F4：=C4-B4+E4-D4

複製儲存格F4，貼至儲存格F4:F13。

老師問到：一個學期教了很多班，平時把不同班級的學生成績放在不同檔案，如果想要把所有班級做一些統計分析，必須先把不同班級的成績集合在一起，如何能快速完成？

當然你可以透過複製／貼上來完成，不過，還有其他方式可以減少一些步驟，讓你不管幾個班都能快速完成。

參考以下的步驟：

1. 選取「資料／選取及取得資料」功能表裡的「檔案／從資料夾」。

2. 選取檔案所在的「資料夾」

前一篇文章：Excel-內容重覆留空白不編號(COUNTIF)，介紹了關於編號的問題。

如果你在編號後，遇到了「篩選」動作，如何不須重新再編號？

如下圖，如果你的序號欄位是手動產生的數值：

則在篩選後，將會保留原編號數值結果，不符合所需。

被問到：在 Excel 裡如何將日期時間字串依時區轉換？

如下圖，其中有一個日期和時間的清單，要依其內容轉換為「澳洲」的時區所對照的日期和時間。注意：原始日期和時間之間有被串接文字「T」。

【公式設計與解析】

假設時區轉換規則：

(1) 時間：如果時間加11小時已是隔天，則時間不變，如果不是，則時間加11小時。

網友問到：在 Excel 裡如何執行多件件的格式化設定？

觀察下圖，E欄的要把E欄和D欄完全相同者標示出來，而I欄是要把F欄、G欄、H欄和I欄完全相同者標示出來。

【公式設計與解析】

為了讓讀者方便理解公式，先對儲存格範圍命名。

選取儲存格D1:H15，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：前三碼、末四碼、分類號、案號、卷號、件號。

網友問到：在 Excel 裡有一個在文字和數字組合清單，如何按數字由小至大排序？

如下圖左，如果文數字的清單裡，數字是三碼，則利用排序工具由小至大排序，則結果是正確的。但是，如果文數字的清單裡，數字並非是三碼，則利用排序工具由小至大排序，則結果是錯誤的，因為其會將數字視為文字來排序。

【公式設計與解析】

儲存格F3：{="A"&SMALL(MID($D$3:$D$18,2,99)*1,ROW(1:1))}

有老師想要在成績表上列出指定科目數不及格者的清單，該如何處理？

例如，假設想要在以下的成績清單中列出3科以上不及格者。

【公式設計與解析】

顧慮有些教師不是很熟悉 Excel 的公式設計，為了該其方便套用公式，所以先為儲存格範圍定義名稱。

選取儲存格A2:H32，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：座號、姓名、國文、英文、數學、自然、社會、輔助。

在 Excel 裡，如何根據「XX天XX時XX分」格式來計算加總的天、時、分？

如下圖，鐵定不能直接加總！所以必須經過一些轉換的程序，所以需要一些輔助欄位。

從原始時間列表看來，可能的格式包含：「XX天XX時XX分」、「XX時XX分」、「XX分」，如何來設計公式？

【公式設計與解析】

1. 調整時間表示

因為一直在學校圖書館兼任行政工作，所以常會鼓勵老師推廣閱讀活動。而填寫相關的心得是教師、學生常見的工作，所以也建議老師：

1. 以Google表單搜集學生的心得資料。

2. 以 Word 的合併列印製成閱讀單。

3. 產生全部學生合輯或是學生個別檔案。

例如：

將在Google表單所搜集的資料下載成Excel檔，要當為Word合併列印的資料來源檔。

有網友問到：在一個工作表中，如何對一個資料清單的內容給予編號，其中內容重覆留空白不編號。

如下圖，編號由上而下編，當內容在前面已經現過時，留下空白不編號。

【公式設計與解析】

儲存格B2：內容填入1

延續這篇：Excel-依據民國年日期計算時間差

若加上一些限制：

若處理時間為8:30以前，則系統是以8:30計算；

若處理時間為17:30以後，則系統是以17:30計算；

若處理時間為12:30至13:30，則系統是以12:30計算。

若時間不滿一分鐘，則以一分鐘計算。

本篇要依據不日期日期計算時間差，該如何處理？

如下圖，在一個時間清單中，使用了明國年來表示日期，利用這些日期和時間的格式，如何來計算二個時間之間的差距？

【公式設計與解析】

因為時間清單中使用了民國年的日期格式，所以必須先轉換為西元年日期格式。

延續這篇：Excel-指定小數位數或整數位數轉換

如何靈活運用各種函數來調整小數和整數的進位問題？在 Excel 處理數值進位問題可用的函數例如：ROUND、ROUNDUP、ROUNDDOWN、MROUND等。看看以下的分析。

(1) ROUND函數為四捨五入至指定小數位數(參數為正數)

在日常生活中，有時候並不那麼在意小數點，所以常會有取整數的時候(量其約的概念)。

在 Excel 裡要取一個數值的整數，有多種方式，看看以下的分析。

利用 INT 函數，對於正數而言，就是把小數無條件捨去。

利用 INT 函數，對於負數而言，就是把小數無條件進位。

如果使用 TRUNC、ROUNDDOWN 函數都可以指定小數位數並捨去其後數值。

如果想要以運算方式：INT(A3*10)/10，則只適用於正數，而不適用負數。

學校同事好不容易，已經學會使用 Excel 的 VLOOKUP 函數來查詢。突然發現，如果要查詢的值不是在資料的第一欄，VLOOKUP 函數無法派上用場時，該如何來進行查詢？

如下圖所示，想要以姓名來查詢年級和班級，而姓名欄位並不在資料的第一欄，讓如何設計公式？

【公式設計與解析】

1. 定義儲存格範圍

先選取儲存格A3:C19(全部資料)，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：年級、班級、姓名。

最近有老師遇到了這樣的問題：在 Google Classroom 上下載了成績檔，是一個 CSV 檔案格式：

在記事本或是 Excel 中開啟以會出現以下的亂碼狀況。

這都是因為中文字的編碼問題所造成的，雖然先前有提供解決方式：

Excel-解決網路下載CSV檔而產生的亂碼問題

前一篇文章有提供：Google Classroom-使用評分量表對學生作業評分

參考下圖，假如你要依據一個評分量表在 Excel 中計算分數，試著練習看看。

【公式設計與解析】

根據成績代碼和分數的對照表：

在 Google Classroom 上學生繳交作業後，如果老師要線上評分，可以直接給分，也可以採「評分準則」來評分。以下來看看如果要使用評分量表來評分，要如何處理。

在新增作業時，可以點選：評分量表。你有三種方式來建立：

(1) 建立評分量表：手動新增評分準則

(2) 重複使用評分量表：使用先前已建立的評分量表