學不完．教不停．用不盡

網友問到 Excel 的問題：在下圖中有二個清單包含了天和時的資料，如何將兩個數據予以加總？其中 1min = 0.1H（1分鐘＝0.1小時）

以下圖中的第一列為例：1天14.41時+2天21.21時＝4天12.02時。

【公式設計與解析】

1. 計算天數

網友問到 Excel 的問題：如何將一個儲存格中裡數值中的每一個數字填入對應的儲存格？

例如：在下圖中有一個最多 9 位數的數字，如何將其每個位元的數字填填入對應的儲存格，其中包含負數的符號。

【公式設計與解析】

儲存格J2：=IFERROR(MID($L2,LEN($L2)-(10-COLUMN(J:J)),1),"")

網友問到 Excel 的問題：在工作表中的資料表有 9 個字元，分別對應數字 1～9（參考下圖右），如何將數個字元的字串轉換為數字？

在原始的欄位中的儲存格裡有多個不同數量的字元要轉換為數字，例如：在下圖左中 FCCG 要轉換為數字 6377，該如何處理？

【公式設計與解析】

儲存格B2：

網友問到：在 Excel 的工作表中（如下圖），如何在D欄、E欄、F欄中，只能輸入同一列的A欄、B欄、Ｃ欄的內容？（共有1000列要套用同一規則）

參考下圖，例如在儲存格D1:F25範圍，利用下拉式清單，讓每儲存格只能輸入A1:C25中對應同一列中內容。

【設計與解析】

基本上這是要利用「資料驗證」的方式來產生下拉式清單。如下操作：

1. 選取儲存格D1。

同事常會問到 Excel 真的那麼有用？很多公式我也不懂，學 Excel 要做什麼？我用以下的例子做了一部分的回應，重點是資料的處理。

例如：在圖書館的流通系統中匯出了一個200頁的文件，其中是學生的圖書借用資料。現在高三要畢業了，必須找出那些學生沒有還清圖書。如果系統有這個功能，那就不用傷腦筋了，問題是沒有。所以打算根據學生的借用資料來得到想要的結果，然而由系統匯出的資料並不是可以直接處理的資料（如下圖）。

其中的文字沒有排的很整齊，更不用說進一步的處理了。

這個時候，Excel 就派上用場了！

在 Excel 中當我們在執行查詢時，有時會有錯誤的訊息產生，該如何避免出現這些訊息，而改以其他適當的方式來呈現？

在下圖中，當你使用 VLOOKUP 函數查詢時，若是查詢到空的儲存格，則會傳回 0，但是應該是顯示空白比較恰當。或是使用錯誤內容來查詢，正常會傳回 #N/A 這類的訊息，該如何以其他文字來回應呢？

【公式設計與解析】

(1) 儲存格E4：=VLOOKUP(D4,A2:B17,2,FALSE)

當使用 VLOOKUP 函數查詢，由於儲存格B7為空白，所以應該傳回空白，而非傳回 0。

網友問到：在 Excel 中有一個資料表，如何計算出每個日期的配編數有幾類？

（請參考下圖）

【公式設計與解析】

為了簡化公式，特別利用二個輔助欄位。

在 Excel 的資料表如下圖左，網友問到如何重組每三個文字插入一個數字，依此規則產生資料清單？

在下圖左之中，A欄由數字組成，B欄由文字組成，如何取出每三個文字插入一個數字？

【公式設計與解析】

(1) 處理數字部分

網友問到：在 Excel 的活頁簿中有 6 個工作表（A單位、B單位、C單位、D單位、E單位、F單位），如何計算這個 6 個工作表中相同位置儲存格的和？

這 6 個工作表的格式一致，其中「數值」欄位的內容不各有不同。

在下圖中，分別計算A單位～F單位的甲乙丙、丁戊己庚、辛壬癸之小計，即根據工作表名稱，來摘要各工作表的小計，該如何處理？

網友問到：在 Excel 的工作表中有一個資料表（如下圖），如何計算資料中的的每個項目之小數的和？

如下圖，資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。

【公式設計與解析】

選取資料欄位中有資料的所有儲存格（本例為儲存格A1:A22），按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：資料。

儲存格D2：=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))

網友根據這篇文章：Excel-計算含有部分字串的小計(SUMPRODUCT)

問到如果資料調整為下圖的代碼格式，如何計算各個代碼的加總小計？

【公式設計與解析】

方法一：

儲存格F2：=SUMPRODUCT((1*LEFT(代碼,3)=E2)*金額)

網友提問：在 Excel 中（如下圖），如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)？

在下圖中，原始資料提供年月日的欄位，而統計表中是要以統計各年月的配編數，例如年月是10501者，有 1, 2, 3, 4 共四種配編類別，該如何處理？

【公式設計與解析】

儲存格G2：=SUMPRODUCT((1*LEFT($A$2:$A$26,5)=F2)*(1/COUNTIF

在 Excel 的公式中，SUMPRODUCT 函數被使用在需要『乘積和』的時機。在 SUMPRODUCT 函數中使用兩個陣列來執行乘積和，而兩個陣列必須要欄和欄對稱或列和列對稱。

(1) 1欄多列×1欄多列

儲存格B11：=SUMPRODUCT(B1:B9*D1:D9)

公式：=B1*D1+B2*D2+B3*D3+B4*D4+B5*D5+B6*D6+B7*D7+B8*D8+B9*D9

其中 SUMPRODUCT(B1:B9*D1:D9) 和 SUMPRODUCT(B1:B9,D1:D9) 的結果相同。

網友問到：在 Excel 的公式常會出現 ROW 函數和 COLUMN 函數，其主要的用途為何？

ROW 函數會傳回儲存格的列號，COLUMN 函數會儲存格的欄號。

當在儲存格A1，則 ROW() 會傳回 1，而在儲存格A20，則 ROW() 會傳回 20。而，

ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、...。

網友問到：在 Excel 的工作表中一個數值清單，其中每個儲存格內有3個以『,』隔開的數字，如何計算每個儲存格內數字的總和？

參考下圖左，A欄中每個儲存格有 3 個數字，並且以 2 個『,』隔開。如果以「資料剖析」工具，手動來將一個儲存格內容，調整為 3 個儲存格，再予以加總。這也是一個不錯的做法。但是，如果想要以公式來進行運算，該如何處理？如果是一個儲存格中有 4 個數字，又該如何處理？

【公式設計與解析】

1. 儲存格內有 3 個數字

儲存格B2：

網友在 Excel 中想要製作大量的超連結，能夠從一個工作表，經由按一下的動作，就跳到另一個工作表的某個儲存格上，有辦法做到嗎？

參考下圖，在工作表1的儲存格A9上按一下「工作表2的A9」，就會自動跳到工作表2的儲存格A9。

到達到這個效果，可以使用HYPERLINK函數來完成。參考以下的做法：

若要在工作表1中的儲存格A1（顯示：工作表2的A1）上按一下，要自動跳到工作表2的儲存格A1。輸入以下公式：

儲存格A1：=HYPERLINK("#工作表2!A"&ROW(1:1),"工作表2的A"&ROW(1:1))

在使用 PowerPoint 時，如果想要將圖案中的文字分欄顯示，該如何處理？

（本篇以 PowerPoint 2013 為例）

如果你在 Word 文件中，將文字置入一個圖案裡，除了改變文字方向之外：

你還可以透過文字方塊設定其他選項：（Word）

網友在 Excel 的工作表中，想要產生重覆數字的數列（如下圖），例如：1,1,2,2,3,3,4,5,5,...，該如何處理？

在下圖中，如果選取儲存格B1:B4，再利用自動填滿方式，將會產生B欄的錯誤結果。所以，得利用公式來產生數列。

【公式設計與解析】

1. 產生重覆 2 次的數列： 1,1,2,2,3,3,4,4,…

儲存格D1：=INT((ROW(1:1)-1)/2)+1

很多網友對於在 Excel 中，想要根據儲存格內容來顯示對應的圖片很有興趣！本篇再次介紹運用名稱定義，配合 OFFSET 和 MATCH 等函數來顯示結果。

例如：在下圖中有七個國家的國名稱和國旗，看看如何來運用。

參考以下的操作：

1. 先在儲存格E1:F8中，將國名和國旗的圖片安置妥適。

2. 定義名稱：flag

有網友問到：在 Excel 中的資料清單，如果想要將英文字置換成數字，例如：A：01／B：02／C：03，該如何處理？

以下圖為例，透過 SUBSTITUTE 函數將英文字置換成數字。

公式：儲存格C3

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"A","01"),"B","02"),"C","03")

但是，如果你要 26 個英文字母都要置換時，你如何輸入公式？