學不完．教不停．用不盡

讀者問到：在 Excel 中已有一個資料檔(如下圖例子，具有多個欄位的資料表)，如果要由 Word 文件中合併列印取用這個資料檔，並且篩選符合條件的項目來合併，該如何處理？

以下例子要在 Word 文件中篩選學校名稱中有『桃園』二字者來合併列印。

先以合併列印的「信件」模式，假設你在 Word 的文件中已插入要合併的欄位：

先預覽結果：

大家有遇到這種現象嗎？例如：在 Excel 中的 A 檔工作表1的儲存格A1，關聯到另一個 B 檔工作表1的儲存格A1，當你儲存檔案後，如果再複製到USB或是郵寄給別人時，他人打開檔案，會發現其中的公式變的不一樣，可能很複雜且很亂，但是顯示的結果卻是正確的。該如何不讓公式隨之改變呢？

以下是讀者的原始問題：

假設有A和B兩個檔案，B內有儲存格是設公式連結A中的數值，但是一但我搬動或複製或把B檔案傳給別人的時候，雖然數字不會動，但是裡面的公式都會變成很長
例如: 'C:\Users\222\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\NHVAA9JM\[test1.xlsx]Sheet1'!$B$1+'C:\Users\1030502\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.IE5\NHVAA9JM\[test1.xlsx]Sheet1'!$B$2之類的，我試過單一檔案移動 和A檔案一起移動都沒有用， 我現在被要求要想辦法可以移動檔案公式不會跑掉，但是又不是把A併入B檔案當作B檔案的一個Sheet。因為工作多有移動和寄送(MAIL)需求， 請問該怎麼做呢?

在下圖中有 A、B 兩個 Excel 檔案，當這個兩個檔案都開啟時，設定 B 檔案中工作表1的儲存格A1=[A.xlsx]工作表1!$A$1。(這是一個相對位址的表示方法)

本範例是延續前三篇文章的研習講義：

1. Excel-SUMPRODUCT函數範例與說明(研習範例)

2. Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

3. Excel-資料重組(多列轉多欄)與統計(SUMPRODUCT,ROW,INT,MOD)

這次來看看 SUMPRODUCT 在多人多項如何統計個別、全體的次數和總計。

參考下圖，每人在三個項目中勾選，要來計算每人金額的小計，各項目的數量，和全體總金額。

在 Excel 中有個日期和時間所構成的資料清單，如何找出在某個時間區間中的筆數？

【公式設計與解析】

選取儲存格A1:A29，按 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：資料。

因為 Excel 將 1 天(24小時)以 1 表示，也就是說 1 小時為 1/24。

(1) 時段：08:00~12:00

最近又要為校內同仁上 Excel 的研習課程，本篇為課程範例。參考先前範例：

Excel-SUMPRODUCT函數範例與說明(研習範例)

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

本篇要介紹由一個學生升學資料(模擬)，每一個學生的資料分成四列呈現，有數百名學生待處理。而要將姓名中間的一個字遮蔽，並且將多列資料轉換為多欄，再統計國立和非國立的人數。

(1) 遮蔽姓名中間的一個字

(先前文章內容有錯誤，目前內容為更正版)

網友根據這篇文章：Excel-計算10位元數以上的乘法，想要了解如果要模擬10位數字的『加法』運算，該如何處理？

如下圖，有二組10個數字組成的數，要執行相加的動作，如何將每個位數的結果模擬出來呢？

【公式設計與解析】

最近又要為校內同仁上 Excel 的研習課程，做一些講義方便同仁課後參閱。本篇是利用INDEX、MATCH、OFFSET、VLOOKUP、HLOOKUP、ADDRESS、INDIRECT等函數來查詢欄列交集的資料。

參考下圖，在本例中的資料表是由『天干的名稱所組成的欄名』和由『地支的名稱所組成的列名』集合而成的資料。

【準備工作】

(1)選取儲存格B1:K1，在[公式／定義名稱]功能表中，定義名稱：天干。

(2)選取儲存格A2:A13，在[公式／定義名稱]功能表中，定義名稱：地支。

在 Excel 做好一個圖表，也顯示了標籤，如果想要修改其中部分資料的標籤，讓它和其它不一樣，參考下圖，其中有二個資料項目的標籤和其他項目不同。該如何處理？

參考以下的操作步驟：

1. 選取一個已顯示的資料標籤，整個資料數列的標籤每一個都被選取。

2. 點選第 4 個資料標籤，目前只留下這個資料標籤被選取。

3. 在文字方塊中輸入你想要的文字。

在 Excel 中樞紐分析表是一個常用的工具，當我們利用一個資料表的內容產生了一個樞紐分析表後，可以看到一些摘要的結果(已運算後的結果)，如何能看到這些摘要結果的細部運算狀況呢？

以下圖為例，當產生了一個樞紐分析表，如果想要看看公假這一欄的細項，你可以在總計欄位中的『11.0』上「按二下」，即會產生一個新的工作表。在這個工作表中會顯示這個欄位的細部內容。而這個新的工作表會進入篩選模式，你可以直接使用篩選的工具。

在 Excel 中如果要將數字轉換成國字來表示，例如：『123456』轉換成『壹拾貳萬參仟肆佰伍拾陸』，是相當容易的事，只要在儲存格中設定數值格式即可。但是，如果你要在 Word 中操作呢？

參考下圖，如果要轉換數字為國字，則可以先選取數字，再選取[插入／符號]功能表中的「數字」。

在開啟的[數字]對話框中選取『壹, 貳, 參, …』項，即可以執行這個轉換工作。

要特別注意：

最近又要為校內同仁上 Excel 的研習課程，做一些講義方便同仁課後參閱。本篇是關於 SUMPRODUCT 函數的說明。

下圖是微軟提供的 SUMPRODUCT 函數說明，主要是執行陣列元素的『乘積和』。

下圖是一個實例，要由『姓名、性別、成績』三個欄位中，求取不同性別的人數，和不同性別的及格和不及格人數。(參考下圖)

為了解說方便，選取儲存格B1:C16，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、成績。

有同仁問到：

在 Wrod 中處理文件時，如下圖的內容中，想要在已設定項目符號的段落中，為了要使某些文字對齊，所以先設定了定位點符號(參考下圖)，當插入點移至下圖的第三列的第１個字，然後按一下 Tab 鍵，卻沒有產生內縮的結果，反而是執行預設之「增加縮排」的動作。

該如何解決呢？(要把第三列的起始位置移至冒號(：)後)

答案很簡單：

改按 Ctrl+Tab 鍵即可，參考下圖(不解釋)。

在 Excel 中使用 VLOOKUP 函數來檢索資料是常手的工具，在下圖中可以發現，要使用「學號」來查詢各欄的資料，公式如下：

儲存格I2：=VLOOKUP($I$1,$A$2:$F$24,ROW(2:2),FALSE)

而 VLOOKUP 函數必須要將查詢的值在資料第1欄中查詢。

複製儲存格I2，貼至儲存格I2:I6。

但是，如果你的資料表如果是像下圖這樣，用以檢索的欄位(學號)並非是資料的第1欄，所以無法使用 VLOOKUP 函數。該如何來正確查詢？

在 Excel 中如果要將一個資料數列四捨五入至千位數或萬位數，該如何處理？

(1) 四捨五入

如下圖，要根據原始資料，轉換為四捨五入至百位、千位、萬位和10萬。通常要四捨五入，你會使用 ROUND 函數，例如：ROUND(A1,3)，表示要將儲存格A1的內容，取四捨五入至小數第3位。

一樣也是使用 ROUND 函數，如果要轉換為四捨五入至百位、千位、萬位和10萬等，只要將參數改為『負數』即可。

四捨五入至百位數，儲存格C3：=ROUND(A3,-2)

網友問到：Excel 的資料表有個數列資料，網友想要求其中的項目有多少種，把相同數字算一種，要求有多少不同的數字？(參考下圖)

【公式設計與解析】

(上圖)儲存格C2：=SUMPRODUCT(1/COUNTIF(A2:A24,A2:A24))

其公式原理是先求出每個數字在數列中出現的次數，然後將每個次數求倒數，再將倒數加總，該數即為項目的個數。(每種項目加總後為1)

在 Excel 的工作表中常會用到要重組資料，例如下圖中，每天會輸入幾筆資料，隨著日期不斷的增加，如何能將每天不同欄位的資料重組在一欄中？

下圖中，每天都要輸入甲、乙、丙、丁、戊、己、庚等項目的資料，要將這些資料重組在一個欄位中。

【公式設計與解析】

儲存格J2：=OFFSET($B$2,MOD(ROW(1:1)-1,COUNT(A:A)),COLUMN(A:A)-1+

在 Excel 中繪製圖表時，有時會遇到某些儲存格沒有輸入任何的資料，也就是空白儲存格，但不包含是空字串的儲存格(例如：="")，在圖表中會產生數列資料的折線圖有中斷的現象，該如何來改善？

如下圖，因為幾個空白的儲存格，而導至圖表中的線條產生不連續的狀況。

你可以試著這樣做：

選取折線圖，再按右鍵，選取「選取資料」：

在 Excel 中儲存格可能會放有各種，例如：數字、文字、邏輯值、錯誤訊息等，如何能統計在儲存格中這些資料類型的數量？(參考下圖)

【公式設計與解析】

(1) 計算空白儲存格數量

儲存格F2：=SUMPRODUCT(ISBLANK(A2:C24)*1)

有老師在製作 Excel 的講義時，會需要用到顯示某些儲存格中的公式內容，以下使用兩種方式來呈現，希望對老師們的備課有幫助。(參考下圖)

(1) 使用 FORMULATEXT 函數

在儲存格D2中輸入公式：=FORMULATEXT(C2)

在儲存格D2中會顯示：=SUMPRODUCT(1*(A2:A14>=500))

網友對前一篇文章產生了興趣：Excel-計算儲存格內左、右方連續0的個數(SUMPRODUCT,LEFT,RIGHT,ROW)，想要了解如何計算儲存格內左方、右方連續n個相同數字個數的做法。

以下圖為例，來計算左方連續個1的個數，和右方連續個1的個數。

【公式設計與解析】

1. 計算儲存格左側連續的 1 個數