學不完．教不停．用不盡

在 Excel 中常會遇到要將多個儲存格內容合併在一個儲存格中(分列顯示)，或是將一個儲存格中多列顯示的內容分別放在不同儲存格中，該如何處理呢？(參考下圖)

(1)合併儲存格內容

如果要在一個儲存格中分多列顯示，則要藉助 Alt+Enter 鍵。如果你要使用公式來將儲存格內容分列顯示，則可以使用 CHAR(10)。

儲存格C2：=A2&CHAR(10)&A3&CHAR(10)&A4

然後設定儲存格C2中對齊方式的文字控制為「自動換列」。

今日在網路上看到一位老師在網路上分享了在 Excel 中複製不連續儲存格範圍時所產生的操作問題：參考下圖，有時在操作時會出現「無法在不相鄰的範圍執行此命令」的錯誤訊息。

以前我和其他同事也有遇過相同的問題。通常，你要選取不連續的儲存格區域，你會藉助 Ctrl 鍵和滑鼠來操作比較方便。

先以下圖來說明，當你選取了儲存格A1:D6後，按著 Ctrl 鍵，再選取儲存格B3:C5。當按下 Ctrl +C 鍵(執行複製動作)，此時就會出現：「無法在不相鄰的範圍執行此命令」的錯誤訊息。因為你選取了二個有部分重疊的區域，所以無法在不相鄰的範圍執行此命令。

那麼，為何會出現錯誤訊息呢？試試以下二種操作方式

最近看到同事要將 Excel 中某個工作表的局部內容印出來，竟然是先複製想要的內容到另一個工作表，再執行列印工作，著實嚇了一跳！

如下圖這樣的文件，可能在主要的資料內容之外，還有一些說明內容、美化的圖案等。在某些時候，只想將主要的資料內容列印出來，該如何處理呢？

你可以這樣來操作：

(1)

選取資料部分(儲存格A1:E17)，在[列印]對話框中的[設定]區中，選取「列印選取範圍」，檢視預覽列印區中的內容，即會只列出被選取的內容。

在 Excel 中，有網友取得了看似日期時間組成的字串，要如何轉換為標準的日期時間格式？

參考下圖，的確有很多人會接收到這類格式的文字字串，但是在 Excel 中無法直接以日期時間來處理。因為在 Excel 中，日期時間必須是數值格式，所以在下圖中的資料要處理前必須要轉換。

【公式設計】

儲存格B2：=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))+

有網友問到：在 Excel 中的一個資料表(如下圖左)，如果在 AAA 欄位中篩選出大於 50 者(如下圖右)，如何將被篩選出來的 BBB 欄位中的「乙」全置換為「乙乙乙」。

如果你是直接用尋找／取代的功能，則會連未被篩選的儲存格內容也會被置換，該如何才能做到只置換已被篩選的資料呢？

【參考作法】

首先，選取這些已被篩選的儲存格，然後按 Ctrl + G 鍵，在顯示的[到]視窗中，按一下[特殊]按鈕。

接著，點選[可見儲存格]選項，按一下[確定]按鈕。

在 Excel 中使用公式時，有些儲存格內容看起來像數字其實是文字的格式，或是在公式處理時取出字串中的數字部分，其實還是文字格式，要如何將這些看起來像數字的文字真正的轉換為數字呢？

例如下圖中，在公式中以 RIGHT 函數來取出儲存格A2中的數字部分，這個結果的數字是文字格式(結果預設靠左對齊)。這樣的問題，困擾了很多的 Excel 初學者！

在 Excel 中如果你將文字格式的數字拿來執行數值運算(使用運算子：+、–、*、/ 等)，或是透過其他數值公式(例如：SUM、AVERGE)或是統計公式(例如：MAX、SMALL)的運算，都能自動轉換。

透過以下的這些運算，都能在運算過程中將文字轉換為數字。(以下僅是列舉，族繁不及備載！)

=--RIGHT(A2,3)

最近有一些同仁開始在 Excel 的工作表中使用「名稱」，所以提出了一些做法上的問題。名稱的使用的確可以讓選取的操作變的簡單，讓公式的使用變的易讀且能縮短公式。

用以下的資料來練習：

【定義名稱】

在功能表中，可以使用的名稱相關工具如下：

有網友問到：在 Excel 中的一個資料表(如下圖左)，如何篩選出人員為甲及非空白日期的資料，並且統計其數值的和？

如果你不想手動使用篩選工具，則可以藉助公式來篩選，但是必須經過「陣列公式」的處理。

【準備工作】

選取儲存格B1:D17，按 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：人員、數值、日期。

在 Excel 中，有網友想要將一個已知日期減 1 ，並且將原型格式更改為 8 碼的格式(參考下圖)，該如何處理？

【輸入公式】

儲存格B2：=TEXT(A2-1,"yyyymmdd")

TEXT 函數的參數 A2-1，表示儲存格中的日期減 1，"yyyymmdd" 表示將格式顯示為西元年 4 碼、月 2 碼、日 2 碼。

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

在之前的文章：Excel-將不同類別資料篩選至另一個工作表(陣列公式)中，如果以抓取「北區」的資料為例，公式：

儲存格A2：{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF(區別="北區",ROW(區別),
FALSE),ROW(1:1))-1,1),"")}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛ ｝」

公式說明請參考：Excel-將不同類別資料篩選至另一個工作表(陣列公式)

有網友想要問：如果相同的公式，要改求「北區＋南區」，要如何修改公式？

在 Excel 的資料表中，如果要符合特定年月日期的總和，通常你會使用 SUMPRODUCT 函數。因為在符合雙條件之下要計算總和，使用 SUMPRODUCT 函數以乘積和的概念來計算，讓公式顯的簡單易懂。

網友問到：日期由於是輸入或由其他來源導入，所以有可能以數字型態呈現，也可能以文字型態呈現。公式應該如何來設計呢？

以下圖為例，日期是數字型態。先選取儲存格A1:B27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數字日期、數值。

儲存格E3：=SUMPRODUCT((YEAR(數字日期)=2014)*(MONTH(數字日期)=4)*數值)

以下圖為例，日期是文字型態。先選取儲存格A1:B27，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：文字日期、數值。

同事問到的問題：同事已將手機的連絡人已和 Google 通訊錄整合，想要在電腦中備份這些連絡人資料，於是在 Google 通訊錄中匯出了連絡人資料。但是由於 Google 匯出的連絡人資料是一個CSV格式，當由 Excel 開啟時並由明確的將各欄位資料置於各個欄位中：

於是試著使用「資料剖析」工具(使用[資料／資料工具]選單的「資料部析」)，參考下圖，卻發現電話欄位中不管是市話或是手機號碼，只要是 0 開頭者，這個 0 都不會顯示，實在很困擾。

該如何避免這種問題發生呢？

這個問題其實不難，只要在執行資料剖析時，在第三個步驟中，挑選電話號碼欄位，再將欄位的資料格式設定為「文字」(預設為「一般」)，如果有多個電話號碼欄位，則要一個一個欄位設定：

學校同仁問到在 Word 文件中要插入頁碼很容易，但要插入文件的頁數卻找到不相關功能位置。如何才能插入文件的頁數呢？

的確，在 Word 2010/2013 都是找不到「插入頁數」的功能，無法直接使用按鈕來插入頁數。

你可以這樣操作：

切換至頁首或頁尾中，顯示[頁首及頁尾工具]功能表中，在[插入／快速組件]選單中，選取「功能變數」。

有網友問到一個在 Excel 中，大家常會遇到的問題：當在一個運算公式中如何排除含有錯誤訊息的儲存格？

例如在下圖中要計算儲存格A1:D8中的所有數值總和，但是很明顯的看到如果使用公式：=SUM(A1:D8)，會傳回錯誤訊息，因為運算公式中包含儲存格B4和儲存格C6，這兩個儲存格是運算產生錯誤的儲存格。

但是常常不想要重新設定公式，只想要排除含有錯誤訊息的儲存格，該如何處理呢？

儲存格F6：｛=SUM(IF(NOT(ISERR(A1:D8)),A1:D8,))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛、｝」。

在 Excel 中可以使用自動填滿或公式方式來產生各種數列和清單，

參考：http://isvincent.pixnet.net/blog/post/32233649

如果像下圖中根據第一個數來產生頭尾都要以二位數流水號來變化，該如何處理呢？

儲存格C1：=TEXT(ROW(1:1),"00")&"XXXX"&TEXT(ROW(1:1),"00")

其中TEXT(ROW(1:1),"00")的用意是：在第一列中ROW(1:1)=1，當往下複製公式時會產生 ROW(2:2)=2 → ROW(3:3)=3 → ROW(4:4)=4 → … 。

在 TEXT 函數中使用「"00"」格式，可以讓 1 ~ 9 的數字顯示為 00 ~ 09。

網友想要驗證身分證字號是否正確，要如何使用 Excel 來幫這個忙呢？

首先要來理解中華民國國民身份證字號的編碼規則，你可以參考維基百科：

http://zh.wikipedia.org/wiki/中華民國國民身分證

其中第一碼的對應轉換字元：

有些目前已不使用：

最近在輔導學生和教職員工參加 GLAD 的 BAP 認證時，同仁問到為何要使用「名稱」這個概念。因為大多數人在操作 Excel 時都沒有用過「名稱」，所以無法體會其用意。

以下圖中的統計表為例，要查詢季別和人員的交叉對應的內容：

儲存格D8：=INDEX(B2:E6,MATCH(C8,A2:A6,0),MATCH(B8,B1:E1,0))

公式正確的求得結果。接著來設定名稱：

選取儲存格A1:E6，按 Ctrl+Shift+F3 鍵，勾選「頂端列、最左欄」(預設)，定義名稱：第一季、第二季、第三季、第四季和甲、乙、丙、丁、戊。

有網友想要在 Excel 的工作表中複製不連續的儲存格，而且在不同的位置依原來的排列來貼上，但是似乎有些困難存在。

參考下圖，已經選取了儲存格A2, A4, A6, A8, A10，想要複製到 I 欄的平行位置。

當按下 Ctrl+C 鍵進行複製，再於儲存格I2中按一下 Ctrl+V 鍵，執行貼上動作，卻發現結果並沒有依照複製儲存格的相對位置來貼上，而是分散的儲存格變成連續了。

所以，要改變做法。

網友想要在 Excel 的工作表中計算多個欄位的乘積，如下圖中每一個商品有Ａ組和Ｂ組的數量，如何計算所有Ａ組數量乘以單價加上Ｂ組數量乘以單價所得的總金額？

【準備工作】

選取儲存格A1:D11，按 Ctrl+Shift+F3 鍵，定義名稱：商品、單價、A組、B組。

網友想要在 Excel 的一個含有日期和金額的清單中，分別計算例假日和平日之金額的平均，該如何處理呢？(參考下圖)

這個問題藉助 SUMPRODUCT 函數可以輕易達成。

【準備工作】

選取儲存格A1:C26，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、星期、金額。