學不完．教不停．用不盡

在 Excel 中取得一個資料表(參考下圖左)，如果建立了樞紐分析表，想要以公式建立相同結果，該如何處理？參考以下作法：

選取資料範圍(儲存格A1:D28)，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義四個名稱：類別、狀態、請購數、實支數。

儲存格H13：=SUMPRODUCT((類別=F13)*(狀態=G13)*請購數)

複製儲存格H13，貼至儲存格H13:H17。

儲存格H21：=SUMPRODUCT((類別=F21)*(狀態=G21)*實支數)

在 Word 中如果調整了同一段落中的部分文字大小，其預設的垂直對齊是「靠下對齊」，如果想要調整為「置中對齊」(參考下圖)，該如何操作？

1. 選取[常用]功能表中的[段落]選項。

2. 在[段落]對話框中選取[中文印刷樣式]標籤。

3. 在[文字對齊方式]的下拉式清單中選取[置中]選項。

當你在 Word 文件中，使用取代工具將某些文字取代為不同的文字，如果想要知道那些字被取代了，該如何處理呢？試試以下的做法：

1. 選取[常用/取代]選項，開啟[尋找及取代]對話框。

2. 在[尋找目標]文字方塊中輸入要被取代的文字。

3. 在[取代為]文字方塊中輸入用來取代的文字。

4. 按一下[取多]按鈕。

條列一些關於陣列公式應用的文章，當作練習的參考：

• 數列中重覆者加總時只計算一次(COUNTIF+陣列公式)
• Excel-統計借用狀況(SUMPRODUCT+陣列公式)
• 陣列公式練習
• 計算奇數列和偶數列的和
• 利用答案卡刷卡資料計算各班平均成績
• 計算符合條件的數列總和(SUMIF、SUMPRODUCT和陣列)
• 找出資料所在的欄和列(陣列應用)
• 查表練習(INDEX,陣列)
• LOOKUP函數(使用陣列)
• 陣列中的邏輯運算
• 成績評定(使用VLOOKUP+陣列)
• 成績等第評定(CHOOSE+MATCH)
• 物品領用統計與分析(陣列應用)
• 利用陣列求平方和
• 利用陣列求最小值
• 陣列及FREQUENCY應用
• 間隔列的運算(運用陣列)
• 陣列應用(計算請假時數)
• 將一維數值轉成二維陣列
• 陣列應用(AVERAGE函數)
• 利用陣列製作摘要表
• 找出一欄中最後一個數值
• 計算學生的分組成績
• 數列的運算
• 陣列公式的AND和OR運算
• 用公式取代樞紐分析(一)
• 利用陣列求分組平均
• 在陣列中使用OR運算
• 二進制的應用
• 陣列的應用
• 計算儲存格範圍中的不重覆個數
• 產生連續的數列來運算
• 自動重新排序+只顯示第一個
• 陣列應用
• 統計各種入學管道的人數及平均成績
• SUM+IF+陣列的練習
• 分類排序
• 在COUNTIF中計算*個數
• 樞紐分析時對於空格的處理
• 以SUMPRODUCT製作摘要表
• 產生多個範圍的亂數值
• 比較各組數字的個數(SUMPRODUCT)
• 列出排名前10名
• 計算各種平均
• 交叉比對資料的最大值
• 查表練習(INDEX,陣列)
• 計算每月各日及星期各天的平均
• 計算時忽略有錯誤的儲存格
• 分類排序
• 查表練習(班級幹部名單摘要)
• 統計前幾個高分的平均成績
• 關於數列的排名
• 計算每週各天的收益
• 摘要運動會報名表中各項運動的參賽者
• 標記三科以上不及格者
• 修正設定數值格式的總和
• 統計各班某個成績範圍的人數
• 統計運動會報名參賽人數
• 計算分月分星期的小計摘要
• 日、時、分之轉換
• 採計部分成績的平均
• 一段文字自動置入稿紙格子中

   

在 Excel 中的一個資料表(如下圖)，如果想要去除括號內容(一個日期)，該如何處理？

儲存格C2：=LEFT(B2,FIND("(",B2)-1)

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

FIND("(",B2)：找出第1個「(」的位置。

LEFT(B2,FIND("(",B2)-1)：利用 LEFT 函數取出「(」左邊的所有文字。

根據前一篇文章：

Excel-一段文字自動置入稿紙格子中(http://isvincent.blogspot.com/2011/10/excel_10.html)

有人問到如果要其填入直式的稿子中該如何修改公式？

儲存格K3：=MID($A$1,ROW(1:1)+(10-COLUMN(J:J))*20,1)

複製儲存格K3，貼至儲存格A3:K22。

公式說明：

當我們在 Word 中貼上一張圖時，Word 會根據圖片大小稍作縮小的動作。當你在圖片上按一下右鍵，選取[大小及位置]選項，可以來調整圖片的比例：

有人問到：如果在高度或寬度上輸入比例為 100% 時(原先縮放比例設為78%)，再按一下[確定]按鈕。

設定後的結果(縮放比例已調整為100%)是不清晰的：

在 Excel 中的一個資料表(參考下圖)，如果想要固定某一間隔列顯深淺不同的底色，如果要以設定格式化條件，該如何處理？

(1) 選取所有資料區(不含標題)。

(2) 設定為淺色底色。

(3) 設定格式化的條件。

選取[使用公式來決定要格式哪些儲存格]選項，輸入公式「=ISODD(INT((ROW()-2)/3))」。

有網友問到：以下表中的範例，每個儲存格的內容是前後二串中文字夾一串英文字，試著要取出前後的中文字，並比較二者是否相同。

這個題目有一點難，如果不使用VBA(大多數人不喜歡寫程式)，則要運用陣列公式才能達成。

首先，參考下表可以得知如果利用 BIG5 函數，不論輸入的是半型字或全型字均會轉成全型字，即半型的英文A會轉成全型的英文Ａ。這個題目要用到這個函數。

為了避免公式冗長，所以分數個儲存格來完成：

假如取得一些文字，想要將字一個一個放入類似稿紙的方格子中，該如何操作比較方便(參考下圖)？

假設文字置於儲存格A1，而稿紙的一列是20個字。參考以下做法：

儲存格A3：=MID($A$1,(ROW(1:1)-1)*20+COLUMN(A:A),1)

複製儲存格A3，貼至儲存格A3:T3。複製儲存格A3:T3，往下各列貼上。

利用COLUMN函數，在往右複製時會自動產生COLUMN(A:A)、COLUMN(B:B)、COLUMN(C:C)、…，相當於產生1、2、3、…。

利用ROW函數，在往下複製時會自動產生ROW(1:1)、ROW(2:2)、ROW(3:3)、…，相當於產生1、2、3、…。

當你在 Word 中建立了一個表格，也設定了表格樣式，如果想要新增一列，有那些方式可以使用？

(1) 將插入點移至某一列，選取[表格工具/版面配置/列與欄]中的「插入上方列、插入下方列」等。

(2) 在某一列上右一下右鍵，選取[插入]選項之下的「插入上方列、插入下方列」等。

在 Excel 中常用來處理成績等第評定的問題，這次要使用 CHOOSE 和 MATCH 函數。

根據儲存格E1:F6的成績和等第對照表，要給予每個成績評定一個等第。

儲存格C2：=CHOOSE(MATCH(B2,{0,40,60,80,100},1),"D","C","B","A","A+")

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

公式原理：

MATCH(B2,{0,40,60,80,100},1)：根據儲存格B2的成績，利用MATCH函數找出符合成績陣列中的第幾個。參數1的用意是找符合小於對應陣列中的數為第幾個。

在 Excel 中選取「版面配置/排列」項下的「選取窗格」按鈕，則可以開啟「選取項目和可見度」窗格。

在這個窗格中，你可以看到每一個物件的名稱、顯示/隱藏及排列順序。

在物件右側的眼睛圖示上按一下，可以切換該物件是否顯示。

根據上一篇文章：Excel-使用樞紐分析表工具建立摘要表
(http://isvincent.blogspot.com/2011/10/excel_3901.html)

如果要在日期欄位中新增其他月份的收入時，如何在樞紐分析表中自動新增新月份的資料？

1. 我們必須先定義一個名稱，在名稱管理員中新增「DATA」名稱：

DATA：=OFFSET(工作表1!$A$1,0,0,COUNT(工作表1!$A:$A)+1,2)

注意：儲存格位址必須使用絶對位址(加上$)。

在 Excel 中取得一個日期和收入的工作表(如下圖)，如果想要建立每個月收入的摘要表，該如何處理？這次不要使用公式，改用樞紐分析表工具來做。

1. 選取資料表中的任一個儲存格。

2. 選取[插入/樞紐分析表]選項。

3. 在[樞紐分析表欄位清單]方塊中，將日期拖曳至「列標籤」中，將收入拖曳至「值」中(預設為：加總)。

在 Excel 的工作表中可能需要用要連續的英文字母(參考下圖)，可以用公式自動產生嗎？

(1) 在同一列中產生重覆的字母

儲存格A1：=LEFT(ADDRESS(1,MOD(COLUMN(D:D),4)+1,4),1)

複製儲存格A1，往右各欄貼上，可以產生A,B,C,D的重覆字母。

利用COLUMN函數，可以在各列產生連續的數字，透過MOD函數來求除以4的餘數(因為每4個字母要重覆)。再由ADDRESS函數產生A1,B1,C1,D1等位址，經由LEFT函數取出左邊第1個字，即為A,B,C,D。

在 Excel 中的一個資料表(參考下圖)，如果計算B欄中的數值總和，但是重覆者只能計算一次，該如何處理？

求得B欄中每個數字重覆的次數，輸入公式：

儲存格C2：=COUNTIF($B$2:$B$16,B2)

(1) 計算未重覆者之和

儲存格F2：=SUMIF(C2:C16,1,B2:B16)

根據C欄的重覆次數，利用SUMIF函數求得總和。

或是以陣列公式來計算：

在 Excel 中如果要產生如下的數列，該如何撰寫公式？在B欄中每1列插入一個間隔列、在C欄中每2列插入一個間隔列、…、在I欄中每8列插入一個間隔列。

儲存格B2：=IF(MOD(ROW(1:1),COLUMN(B:B))=0,"",MOD(ROW(1:1),COLUMN(B:B)))

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

在B欄中：MOD(ROW(1:1),COLUMN(B:B))會產生1,0,1,0,1,0,…

在C欄中：MOD(ROW(1:1),COLUMN(C:C))會產生1,2,0,1,2,0,…

…

在 Excel 的工作表中，若要計算一個數列中符合某一條件者的總和，大概都是要想到 SUMIF 函數。最近讓學生練習以 SUMPRODUCT 函數和陣列公式來完成相同工作：

儲存格D2：=SUMIF(B2:B13,">5")

如果要以 SUMPRODUCT 函數來代替：

(B2:B13>5)：會產生{True,False,False,True,True,False,False,True,False,True,False,Fasle}的陣列。

(B2:B13>5)*B2:B13：可以將 True/False 陣列轉換成 1/0 陣列，再和儲存格B2:B12來做乘積的運算。

如果要以陣列公來完成：

輸入完成，要按 Ctrl+Shift+Enter 鍵。

即取出符合條件的陣列，再以 SUM 運算。

有同事問到要如何避免在檢視Excel 的工作表時，不小心修改了其中的內容？我建議可以使用「唯讀」方式來開啟。

開啟檔案時，當選取了一個檔案，要按一下[開啟]按鈕右側的三角型符號。

選取[開啟為唯讀檔案]選項：

這個檔案開啟後會被標示為「唯讀」。