學不完．教不停．用不盡

有網友問到：在 Excel 中有一個報名資料總表，如何讓報名結果依梯次顯示在不同工作表中？

如下圖的報名總表，其梯次有三種選項：一月、二月、三月。

如何將資料自動顯示在『一月、二月、三月』不同的工作表中？

網友根據先前的這篇文章：Excel-多條件的查詢(INDEX+SMALL+陣列)，發現他使用的 Excel 版本無法使用 IFERROR 函數，該如何修改公式？

在下圖中，其實是要根據三個條件(編號、外形、尺寸)求得對應的售價，而且當查無資料時以空白顯示，該如何處理？

【公式設計與說明】

選取儲存格A1:C19，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號、外形、尺寸。

在 Excel 中如果你有一些統計圖是經常性要產生且格式固定，你大概不會想要每次重覆多個步驟來設定，希望能有快速生成的方式。將圖表儲存成『範本』，是個不錯的選擇！

參考下圖，在本例中有三個表格要產生相同格式的統計圖。

當你取一個表格產生了一個統計圖，也都調整好所有的格式。

在統計圖的圖表區中，按一下右鍵，選取「另存為範本」。

網友問到：在 Excel 中如果要將一列多欄轉為多列多欄，該如何處理？

參考下圖，想要將同一列中的儲存格A1:R1，轉換為儲存格A5:D9(四欄)。

【公式設計與解析】

儲存格A5：=OFFSET($A$1,0,+(ROW(1:1)-1)*4+MOD(COLUMN(A:A)-1,4),1,1)

網友問到：參考下圖，在 Excel 中，如何將表格中的欄列互換？

在下圖中，如果你使用複製功能，再使用『轉置』貼上，即可得到欄列互換的結果。但是，如果要使用公式來設計，該如何處理？

【公式設計與解析】

透過 OFFSET 函數處理欄列互換很簡單！根據微軟的定義：

在 Word 中或許你使用過『樣式』功能，來加速文書排版工作，並且讓文件不同地方的版面效果趨近於一致，更可以加速修改格式時的速度。

而在 Excel 中也可以使用『樣式』功能！例如，在下圖中有三個表格，其中一個已經格式設定完成，如果要讓另外兩個表格，也是套用相同的表格格式。相信你的直覺做法是用『複製格式』，刷二下即可完成，當然，這是沒問題的。但是，若要修改其中的格式，就得重覆操作。

本例特別使用『樣式』功能來練習。

1. 選取本例中的儲存格A1:D1。(這些儲存格已經設定跨欄置中效果)

有老師問到：如下圖，在 Excel 中如何製作這個二週小考平均的比較表？

在下圖中，已有兩週的小考平均，如何在第2週比第1週的平均進步時顯示綠色正三角型圖示，在第2週比第1週的平均退步時顯示紅色倒三角型圖示？

【公式設計與解析】

1. 先在 K 欄中計算第2週和第1週的平均值的差。

網友根據這篇文章：Excel-表格的轉換(OFFSET,SUMPRODUCT,ROW)，問到想要如下圖中由B表格查詢A表格，該如何處理？

【公式設計與解析析】

儲存格E3：{=IFERROR(OFFSET($A$1,SMALL(IF($B$3:$B$18=$D3,ROW
($B$3:$B$18),""),COLUMN(A:A))-1,0),"")}

網友想要在 Excel 中做資料處理，參考下圖中的數值欄位，要依規則取後2碼來調整「進位」，該如何處理？

規則：

(1) 若後2碼<=50，則調整為：50

(2) 若後2碼>50，則調整為：100

網友問到：如何在 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：