學不完．教不停．用不盡

在各個工作場合中計算請假時數是不論單位或個人都是常用的到的！在 Excel 中是很容易就根據資料表來計算累計的請假時數並換算成天數。以下有二種請假時數的記錄方式，一起來看看如何換算。

(1)請假時數完全以「時」來記錄

儲存格D2：=SUMIF($B$2:B2,B2,$C$2:C2)

儲存格E2：=INT(D2/8)&"天"&MOD(D2,8)&"時"

複製儲存格D2:E2，往下各列貼上。

在 Excel 中定義「名稱」是十分常用的功能，設定方式也相當便捷。但是，在使用實務上，當資料內容設定為「範圍」或是「表格」時，在操作定義名稱上有些不同之處，所以，要特別注意其差異。

1. 定義儲存格範圍的名稱

一般自行定義名稱時，如下圖，選取儲存格A1:E7，定義名稱：第一年。

使用者在使用 Excel 輸入資料時，都是希望能用最快速的方式來填入想要的資料。

但是，電腦腦怎能猜出你要如何的輸入資料，進而幫你填入呢？所以，你必須要先建構一些規則，讓電腦自動依這些規則來幫你快速輸入資料。

以下介紹三種方式，都可以讓你快速的依某種規則來產生並自動填入內容。

(1) 自動填滿

在 Excel 的[常用]功能表中，可以使用[編輯/填滿]功能來快速填入具有規則的內容。

最近教到 Excel 的自動篩選後，接著當然是要教「進階篩選」囉！有些學生對於多條件篩選時，要執行 AND 和 OR 的邏輯運算不是很清楚，而且對於篩選條件位於同一列是執行 AND 運算，而篩選條件位於不同列是執行 OR 運算，在初學階段更是一頭霧水。

(1) 篩選：甲項>150

這是「單一條件」的篩選動作，正確的選取資料範圍和準則範圍，才能順利篩選出正確的結果。

網友問到：在 Excel 中有多個工作表，例如：31個，其工作表名稱為 1～31，如何快速在這些工作表的相同儲存格中產生某一個月中每一天的日期？

例如：參考下圖，其中有 5 個工作表(名稱：1～5)，每個工作表的儲存格A2要輸入 2015年8月的某一天的日期(日期為工作表名稱之數字)

參考作法：

1. 新增一個活頁簿，並且先命名存檔。

2. 建立 31 個工作表，名稱定為：1～31。

在執行 Excel 的各種運算工作時，常會遇到某些列可能會被隱藏掉或被篩選掉(沒有顯示出來)，但是你只想要計算被顯示出來的資料，該如何獲得想要的結果呢？

例如下圖中，儲存格B17：=SUM(B2:B16)，可以計算AAA的總和。

當有部分資料被隱藏時，如果只想計算被顯示的資料總和，原公式無法獲取正確的結果。例如：下圖中，第 6～9 列被「手動隱藏」了。

建議改用 SUBTOTAL 函數，其中參數 9 和 109 都是用以計算總和(SUM)：

網友問到在 Excel 中如果要給予一個日期後，執行推算若干日數之前的日期，在一個儲存格中該如何設計公式？(參考下圖)

如果根據某個輸入日期的儲存格來推算若干日數之前的日期，可以使用儲存格內容減一個數字的方式來執行。

如果是要在一個儲存格中直接運算出結果，則必須將日期(年,月,日)轉換為一個數字，再來減某個數字，以下圖為例，假設今天日期為2015/7/28。

(1) 儲存格C2：=DATE(2015,7,28)-164

透過 DATE 函數將年,月,日代入，即可轉換為一個數字。

有網友想要利用 Excel 中的微調按鈕來調整儲存格中顯示的時間，該如何處理？參考下圖。

微調按鈕是開發人員功能表的一個物件：

首先，為「時、分、秒」分別新增各一個微調按鈕。其中的設定：

時→最小值：0、最大值：23，遞增值：1，儲存格連結：D2。

有個網友問了一個 Excel 中很普通的問題，但我卻發現了很特殊的結果。參考下圖，想要依照A欄中的數值小數點位數，如果是一位數則顯示B欄的內容，如果是二位數則顯示C欄的內容，該如何處理？

我很直覺的使用了以下的公式：

儲存格D2：=IF(INT(MOD(A2*100,10)),C2,B2)

MOD(A2*100,10)：求出小數點的內容。因為A欄中的數值，不是一位數，就是二位數，所以將數值乘以100，再求除以10的餘數。如果為 0，表示為小數點後為一位數；如果不為 0，表示小數點後為二位數。

複製儲存格D2，往下各列貼上。

在 Excel 中常會使用數值格式設定來改變數值呈現的結果，例如在數值前補上０或是設定幾個小數點等，但是這些數值呈現的是其「格式」，也就是說其儲存的數值內容並未被改變。

例如：把儲存格A2:A5設其自訂格式：000，顯示結果如儲存格C2:C5。

所以 32 → 032；25 → 025，如果把這四個儲存格以「&」串接在一起時，例如公式「=C2&C3&C4&C5」，觀察其結果，格式中有些「0」消失了。

如果要避免這種現象，你並不需要先將儲存格內容先予以改變數值格式，而是直接在公式中改變其格式，例如：

參考下圖，網友想要根據一個 Excel 的日期清單，將「星期」欄位中自動標示六(休)和日(休)，並且將星期六、日的各列儲存格底色標示不同色彩，該如何處理？

一、將「星期」欄位中自動標示六(休)和日(休

儲存格B2：=CHOOSE(WEEKDAY(A2,1),"日(休)","一","二","三","四","五","六(休)")

或

儲存格B2：

有網友想要將 Excel 的工作表中儲存格A1內的多組數字，每三個字一組，拆放在各列或各欄中，該如何處理？(參考下圖)

觀察儲存格A1中的數字組合，可以發現其中每個數字皆以三碼來表示，而且每三碼即以一個「,」隔開。也就是每個儲存格要置入 11 個字元，例如：「002,003,004」，而第 12 個字元的「,」不需取用。即每隔 12 個字元取出前 11 個字元，以下分述分成多列或分成多欄之公式。

(1) 分成多列，每列儲存格中三個數字

儲存格A3：=MID($A$1,(ROW(A1)-1)*12+1,11)

在 Excel 的工作表中如果提供了分數或秒數的清單(參考下圖)，要如何將分數轉換為「時:分」格式，及將秒數轉換為「時:分:秒」格式？

先前的一篇文章所使用的公式：

儲存格B2：=TEXT(INT(A2/60),"00") & ":" & TEXT(MOD(A2,60),"00")

複製儲存格B2，貼至儲存格B2:B20。

乃是主要利用 INT 和 MOD 函數來求一個數除以 60 求得商和餘數的觀念來求解。

在 Excel 中如果某個儲存格內容要關聯至另一個工作表的某個儲存格，其公式為：

儲存格=工作表名稱!儲存格名稱

如果你要使用公式將工作表名稱以變數方式來處理，則必須藉助 INDIRECT 函數來將字串轉換為位址。

參考下圖，其中有三類的工作表名稱：工作表1、AAA、1。

你可以在 INDIRECT 函數中如此設定：

最近有同仁問到：有些文件(以 Word 為例)中置入很好看的字型，卻在電腦重灌時遺失字型或是將文件移到別的電腦時，漂亮的字型全都顯示為預設的字型了，該怎麼辦才好呢？

這是每個人都會碰到的問題，以下圖為例，其中的字型都不是電腦預設的字型。

要解決上述的問題，可以將文件儲存格 PDF／XPS 格式，用來檢視和列印沒有問題，但是文件無法再執行編輯。

另一種方式是將字型嵌入文件中。將文件移至另一部電腦時，下圖左為未嵌入字型時，只能以預設字型顯示，下圖右為有嵌入字型時，電腦中雖沒有相關字型，仍可以順利顯示。

網友問到如下圖左的 Excel 資料表，這是個一維的資料表，如何摘要成如下圖右的二維資料表？

這是非常多人會用到的問題，還是得請出 SUMPRODUCT 函數，一式就搞定。初學者常會問到這樣的類似問題，可見還是得再解說一次。

儲存格F2：=SUMPRODUCT(($A$2:$A$21=$E2)*($C$2:$C$21=F$1))

其中的「*」相當於執行邏輯 AND 運算，運算過程中會將 $A$2:$A$21=$E2 和 $C$2:$C$21=F$1 的關係運算傳回值 TRUE／FALSE 陣列，轉換為 1／0 陣列。

最後再將這些 1／0 陣列加總，即為所求。

有網友問到，在 Excel 中有一個多列的資料表，如何將多列轉換成同一列？參考下圖。

【公式設計】

儲存格A1：
=OFFSET($A$5,INT((COLUMN(A:A)-1)/5),MOD(COLUMN(A:A)-1,5),,)

在 Excel 的資料表中每個儲存格是由英文字、中文字和數字所組成，如何將每個字分離，並且依序置入儲存格中呢？(參考下圖)

【公式設計】

觀察每個儲存格的內容，其中每個字元以「,」分隔，並且包含英文字、中文字和數字。

儲存格C2：=MID(SUBSTITUTE($A2,",",""),COLUMN(A:A),1)

有網友問到在 Excel 的資料表中，如果要分別計算由文字和數字組成的儲存格中，其數字的個數和文字的個數，要如何處理？(參考下圖)

(一) 儲存格中的字數固定

參考上圖，其中儲存格內的字數固定，本例假設每個儲存格中有10個字元。

(1) 儲存格B2：｛=COUNT(MID(A2,ROW($1:$10),1)*1)｝

有些人的確對於 Excel 會有恐懼感！因為要設計公式，並非人人都能勝任愉快！如果不需使用公式就能處理資料該有多好啊！這是你的心聲嗎？

其實 Excel 本來就提供了許多各式各樣的工具，透過操作程序，一步一步的達到想要的結果。所以，不想寫公式，就善用 Excel 的各項工具吧！

以 Microsoft Excel 2013 為例，例如：在下圖中的資料表，如果要根據「性別、年級、認證」等欄位來交叉分析，設計公式對很多人而言是一大負擔。這次要來介紹在樞紐分析表中使用交叉分析篩選器。

檢視：三年級，只要按一下「三年級」，即可只篩選三年級的資料。