學不完．教不停．用不盡

網友問到：如何在 Excel 中將一個矩陣資料內容轉換為一欄？參考下圖，儲存格A1:D5中的資料要轉換至儲存格A7:A26。

【公式設計與解析】

儲存格A7：=OFFSET($A$1,MOD(ROW(1:1)-1,5),INT((ROW(1:1)-1)/5),1,1)

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

參考下圖，在 Excel 中，如果要將儲存格內容「877/13/3214/6481/643/4486」的文字，依其分隔符號『/』取出『877、13、3214、6481、643、4486』，該如何處理？注意其分隔符號之間的內容的文字長度並不一致。

通常，我們會使用 Excel 中的「資料部析」工具來處理，手動操作也很方便，但是如果想要以公式來處理，或像下圖中要把取出的資料放在同一欄中。(資料剖析工具只能將資料部析結果放在同一列中)

【公式設計與解析】

參考上圖，假設：本例的資料中有五個分隔符號『/』，將資料分成六組。

網友想要在 Excel 的工作表中將大量資料的前幾碼刪除，該如何處理？

在下圖中的『資料』欄位中，欄位中的每筆資料都是一樣長度的，但是也可能會遇到資料的長度不一樣的狀況，所以使用的公式要有一點點彈性。

【公式設計與解析】

(1) 省略第1個0

儲存格B2：=MID(A2,2,99)

網友問到：如下圖的 Excel 資料清單中，如何能找出不同料號的最低價廠商？

下圖中的基本資料有：廠商名稱、料號和單價，而單價有可能不同廠商相同單價。在本例中如有相同單價的廠商，則僅列出第一個廠商。

【公式設計與解析】

(1) 為了便於說明，先設定儲存格範圍名稱：

網友問到一個關於用 Excel 來根據打卡清單計算工時的問題，該如何處理？

參考下圖，其條件如下：

8:00~8:14→8:00；8:15~8:30→8:30；8:30~8:44→8:30；8:45~9:00→9:00

【公式設計與解析】

​  ​​   ​​ ​

網友很常問的問題：在 Excel 中如何轉換日期格式，通常會有國曆和西曆的轉換，或是和格式相關的轉換等。

例如：下圖中要將一個國曆的年月日格式轉換為西曆的年/月/日格式。其中國曆年為三碼，西月年為四碼，月和日都是二碼。

【公式設計與解析】

在 Excel 中的儲存格內容常會是數字和非數字混合，如何能找出某個儲存格中第一個非數字的位置呢？(參考下圖)

【公式設計與解析】

先假設儲存格內容不會超過 20 個字元組成。

儲存格B2：{=MATCH(FALSE,ISNUMBER(VALUE(MID(A2,ROW($1:$20),1))),0)}

有老師問到：如果成績中有些數字欄位呈現了文字或是錯誤訊息時，可否不要列入排序，公式可如何處理？

參考下圖，一個成績表中含有『轉學、休學、缺考』等文字，還有錯誤訊息，當在排序時要排除這三個，不要顯示排序結果。如果你使用 RANK 函數，會得到G欄的結果。

【公式設計與解析】

儲存格I2：=IFERROR(RANK(F2,IF(ISNUMBER($F$2:$F$26),F$2:F$26,FALSE)),"")

在 Excel 的工作表中，有時會用到要在資料清單中反推數值所在的儲存格。如下圖，要如何找出儲存格L1的內容對應儲存格A1:J10中相符者的儲存格位址？

【公式設計與解析】

假設儲存格A1:J10的內容具唯一性，不會重覆。

儲存格L4：{=ADDRESS(SUM((A1:J10=L1)*ROW(1:10)),SUM((A1:J10=L1)*

如果在 Excel 的工作表中有一個資料清單，其中每一個儲存格是由數值和單位所組成，如何能將數值和單位分離出來？

參考下圖，資料清單中的數值和單位的字元數都不相同，如何分離其中的數值和單位？

【公試設計與解析】

(1) 取出數值

根據前一篇文章：Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

如果要進一步找出每年第一個13號星期出現的月份，該如何處理？

【公式設計與解析】

儲存格C2：

在 Excel 的工作表中，如果你想要找出一年中有幾個13號星期五，該如何處理？以本例來練習『陣列公式』。

【公式設計與解析】

儲存格B2：{=SUM(1*(WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5))}

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

網友問到：在 Excel 的工作表中如何限制儲存格只能輸入某一時間區間的數值？

參考下圖，想要限制儲存格只能輸入 10:00 至 14:00 之間的數值，該如何處理？

【公式設計與解析】

1. 選取儲存格A2:A21。

有網友問到：在 Excel 中有一個項目清單(參考下圖左)，若要依照項目的數量列出(如下圖右)，該如何處理？

【公式設計與解析】

我參考了網路其他網友提供的公式，覺得是解題的好方式。

同仁問到：一般在 Word 文件中都是由上而下／由左而右的輸入資料，如何才能讓所有資料靠下對齊，由下而上／由左而右的輸入資料？

有別於一般的文件，翻轉參考下圖，資料(包含圖文)輸入是先出現於版面的最下方，輸入的文字會往上推高，該如何處理？

很簡單的步驟即可設定完成：

選取[版面配置／版面設定]功能表中右下角的完整設定按鈕，在開啟的[版面設定]對話框中，進入[版面配置]標籤，在[頁面]區中的[垂直對齊方式]下拉式清單中選取「靠下」選項。

印象中網友問過：在 Excel 中如何將時間四捨五入至指定的位數，該如何處理？

參考下圖，如何將一個時間四捨五入至最近的15分鐘、30分鐘、60分鐘？

【公式設計與解析】

儲存格C1：=MROUND(A2,15/(24*60))

網友延續前一篇文章：Excel-指定數值清單出現的位置(OFFSET)，延伸問到：參考下圖，如果指定一個位置(儲存格B1)要列出幾個清單(儲存格A1:A15)中的內容(儲存格C1:C3)，該如何處理？

這個問題完全符合 OFFSET 函數的概念，你只要給予一個起始位置，再給予相對的欄列位置，即可對應出符合的儲存格。

儲存格C1：=OFFSET($A$1,$B$1-ROW(1:1),0,1,1)

$B$1-ROW(1:1)：如果將公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。本例B1-ROW(1:1)，會傳回 6 → 7 → 8 → …。

複製儲存格C1，貼至儲存格C1:C3。

參考下圖，網友想要 Excel 的工作表中指定數值清單出現的位置，如何處理？例如：下圖中的C欄有 3 個數值，在儲存格B1指定一個位置，在A欄中的相對位置顯示C欄的 3 個數值。

【公式設計與解析】

儲存格A1：

=IF((ROW(A1)>=$B$1)*(ROW(A1)<$B$1+3),OFFSET($C$1,ROW(A1)-$B$1,0),0)

在使用 Word 時你會重覆用到一些圖文的組合嗎？如何才能快速而有效的整組插入文件中？而 Word 中其實已經內建立許多的圖文組合可以選用，你知道嗎？

先來看看 Word 內建提供的建置組塊。在[插入／文字]功能表中有一個[快速組件]功能，選取其中的「建置組塊組合管理」：

在[建置組塊組合管理中]對話框中，已在清單中列有多個建置好的組塊，你可以在對話框右側先行檢視預覽的結果，也可以按一下[編輯內容]按鈕，進一步修改設定值。

當你插入所選取的組塊後：

在 Word 中的長文件編輯時，如果你建立了文件目錄(如下圖)，一旦你選擇更新功能變數後，再選取更新整個目錄時。

有可能會遇到原先設定好的目錄格式也隨之被更新了，如下圖，在小節部分的格式已跑掉了(和上圖比較)。每更新一次，就得重新設定目錄的格式一次，該如何避免這種狀況呢？

參考以下的步驟：

1. 點選[常用／樣式]功能表的右下角圖示，開啟[樣式]功能窗格。