學不完．教不停．用不盡

有時你要將複雜的運算步驟簡化，你可能要藉用「陣列公式」。常有人無法理解陣列公式的概念，但是如果你想要成為 Excel 進階使用者，就必須學會使用陣列公式。特別提供微軟網站之說明供大家參考(適用 Excel 2010)：

陣列公式的規則和範例(http://office.microsoft.com/zh-tw/excel-help/HA010342564.aspx)

有網友問到，如果要將儲存格中的所有數字加總，應如何處理？(參考下圖)

儲存格C2：=SUMPRODUCT(--MID(B2,ROW($1:$8),1))

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

ROW($1:$8)：在 SUMPRODUCT 函數中，會產生 1 ~ 8 的陣列(1,2,3,4,5,6,7,8)。

--MID(B2,ROW($1:$8),1)：藉著上述1 ~ 8 的陣列，可以取出儲存格B2中每一位數的數字陣列(以儲存格B2為例：1,9,6,8,0,6,1,9)，其中「--」，目的是要將 MID 函數產生的文字數字，變為真正的數字。

透過 SUMPRODUCT 函數，可以將上述中每一位數的數字陣列予以加總。

今天教同事 Excel 中的 OFFSET 函數的觀念時，使用了累加數值(如下圖)的範例。

儲存格B2：=SUM($A$2:A2)

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

我們要利用 OFFSET 函數練習達到這個效果，該如何處理？

儲存格B2：=SUM(OFFSET($A$2,0,0,ROW(2:2),1))

同事問到：如果在儲存格中要引用不同工作表的固定儲存格內容，該如何處理？(參考下圖)

例如：在sheet工作表中的儲存格A1，以資料驗證方式製作清單：工作表A、工作表B、工作表C。

假設：

工作表A的儲存格內容為：AAAA

工作表B的儲存格內容為：BBBB

工作表C的儲存格內容為：CCCC

在 Excel 中常見的資料表可能長長一串(如下圖左)，如果能將資料呈現在一個框框中，並且用捲軸來調整檢視內容，應該會方便許多。這次來練習自製一個查表介面。

首先，在[開發人員/插入/表單控制項]的選項中，選取「捲軸」，並在工作表中拖曳出如下圖之大小。

在[控制項格式]對話框中，輸入以下的數值。其中數值介於1~70，儲存格連結為儲存格F3。也就是說捲軸的輸出結果顯示在儲存格F3中。

在 Excel 如果製作了一個統計圖表，若想要讓圖表的標題能隨意更改，該如何處理？

1. 首先根據一個資料表，建立一個統計圖表。

2. 選取統計圖的「圖表區」。

3. 插入一個文字方塊。

4. 在資料編輯列中輸入「=」，然後選取標題所在的儲存格，本題為：儲存格A1。然後按下 Enter 鍵。

有同事問到，如果在資料表中陸續輸入資料，在眾多資料中，想要標示資料輸入不完全的記錄，該如何處理？以下圖的例子，如果生日或是組別其中一項未輸入，要以不同色彩的底色標示。

這種問題可以使用「設定格式化條件」來處理：

1. 選取資料範圍(例如：儲存格A1:D42)。

2. 選取常用［常用/設定格式化的條件］中的「管理規則」選項。

在 Excel 的工作表中，每種色彩對應一個數(參考下圖)，如果想要以色彩查表取得數值再計算總和，該如何處理？

(1) 轉換數字後計算總和

儲存格B2：=VLOOKUP(A2,$F$1:$G$10,2,FALSE)

利用 VLOOKUP 函數，取得每個色彩所代表的數值，再加總即為答案。

(2) 直接計算總和

如果不想先將色彩轉換為數字，而要直接求得總和。

在 Excel 的運算中，有時你會發現類似以下的問題：

B1：0.000123456789012345

B2：1

B3：1.00012345678901

若 B3=B1+B2，觀察儲存格B3，結果好像不正確(應為0.000123456789012345)。那是因為在 Excel 中最多只能儲存 15 個有效位數的精確度所致。

在 Excel 中，如果要計算兩個日期之間的週數，該如何處理？這個題目的意思是要求二個日期之間橫跨了幾週？只要善用 WEEKDAY 函數，即可完成。

儲存格C2：=INT((B2-A2)/7)+MAX(1,(WEEKDAY(B2,1)<WEEKDAY(A2,1))+1)

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

【補充資料】

在 Excel 中，如果想要將身份證字號中的字母轉換為數字，該何如處理？(參考下圖)

身份證字號中的英文字母A, B ~ Z，分別對應01, 02 ~ 26。

儲存格B2：=RIGHT("0"&CODE(LEFT(A2,1))-64&RIGHT(A2,9),11)

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

LEFT(A2,1)：取出身份證字號中的英文字母。

同事問到，在 Excel 中的一長串日期中，如何能自動標示週末假日(星期六、日)？(參考下圖)

建議使用「設定格式化的條件」來處理較為簡單，參考下的步驟：

1. 選取儲存格A2:B20。

2. 新增規則，選取「使用公式來決定要格式化哪些儲存格」。

3. 輸入公式：=WEEKDAY($A2,2)>5，即取出星期六和星期日者。

在 Excel 中，如果要根據開始點和長度，在儲存格中自動繪出對應的儲存格底色(參考下圖)，該如何處理？只要使用「設定格式化的條件」即可解決！

參考以下步驟：

1. 選取儲存格D1:R11。

2. 在「設定格式化的條件」中新增規則，選取「使用公式來沫定要格式化哪些儲存格」。

3. 輸入公式：=AND(MOD(ROW(1:1),2)=0,D$1>=$B2,D$1<$B2+$C2)，設定儲存格底色為橙色。

有人問到：在 Excel 中如何計算兩組日期區間的重疊天數(參考下圖)？

日期區間 A 和日期區間 B 的相對關係(重疊狀況)可能有以下五種情形：

根據以上觀念，發展出以下的公式：

儲存格C2：=MAX(0,MIN(A2,$F$1)-MAX(B2,$F$2)+(B2-A2)+($F$2-$F$1)+1)

有網友問到：在 Excel 中，如何根據一個數值，將同列的儲存格標示某種底色(參考下圖)？這個問題只要交給「設定格式化的條件」來做即可。

本例根據A欄中的數值，將同列的儲存格設定為和該數值相同數量的儲存格底色。

1. 選取儲存格B2:K16。

2. 選取[常用/樣式]中的「設定格式化的條件」選項。

3. 新增一個規則：使用公式來決定要格式化哪些儲存格。輸入公式：=COLUMN(A:A)<=$A2。

這次來練習，在 Excel 中輸入一個數(例如45789)，如何取出其中的每一位數(4,5,7,8,9)？為了說明方便，假設輸入的數字最多有5位數。

儲存格B2：=MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1)

RIGHT("00000"&$A2,5)：將 "00000" 串接原來的數，再透過 RIGHT 函數取出由右算起的5 個字。將不滿 5 位數者，在其左側以 0 填補。

MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1)：透過 COLUMN 函數指定第幾個數，其中 COLUMN(A:A)=1、COLUMN(B:B)=2、…。透過 MID 函數逐一取出各個位數。

在 Excel 的資料表中，可能一個數列中包含了沒有規則排序的一些正數和一些負數(參考下圖)，例如數列中有收入和支出的資料等。如果想要以此計算正數和、負數和該如何輸入公式。

選取儲存格B1:B21，按一下 Ctrl+Shift+Enter 鍵，定義名稱：數值。

儲存格F2：=SUMPRODUCT(--(數值>=0),數值)

儲存格F3：=SUMPRODUCT(--(數值<0),數值)

上式中的「--」，可以將關係運算(<0)所得的 True/Fasle 陣列，轉換為 1/0 陣列。

在 Excel 中如果要執行排多的運算，最常看到使用 RANK 函數來操作，例如：

儲存格C2：=RANK(B2,$B$2:$B$11)

複製儲存格C2，貼至儲存格C2:C11。

還有其他方式可以來操作嗎？例舉以下三種來練習：

(1) 儲存格C2：=COUNTIF($B$2:$B$11,">="&B2)

常會有人問到如何在一個資料表中，以垂直和水平交叉方式來查詢資料，方法有很多種，例舉如下：

首先定義名稱：月分、人員、資料和報表。(參考下圖的名稱範圍)

選取儲存格A1:G11，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列、最左欄」，定義一月～六月、甲～癸等名稱。

以下介紹四種函數，分別經由儲存格J1和儲存格J2的內容要交叉查詢資料：

在 Excel 中有一個資料表(如下圖)，根據這個資料表要求得「加權平均」分數該如何處理？

儲存格E2：

(1) 求得加權計分

=SUMPRODUCT(MOD(ROW(C1:C20),2),C1:C20,C2:C21)

MOD(ROW(C1:C20),2)：{1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0}

C2:C21：{65,4,81,4,74,4,84,2,79,2,80,2,60,2,73,2,69,2,78,2}