學不完．教不停．用不盡

有網友想要知道一年中每個月份的星期一、星期二、…、星期日的數量，該如何處理？

參考下圖，以2014年為例，找出每個月星期日到星期六的日數：

【輸入公式】

儲存格C2：=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))),1)=COLUMN(A:A)))

DATE($A2,$B2,1)：取得該月的第一天。

有網友問到：

如何將同一列上的資料，由左至右改成由右至左呈現(水平翻轉)？(參考下圖)

如何將同一列上的資料，由左至右改成由上至下呈現(90度轉置)？(參考下下圖)

有位網友問到：在 Excel 中有一列數值資料，如何分別計算奇數欄位(A,C,E,…)和偶數欄位(B,D,F,…)的和？(參考下圖)

假設資料範圍：A1:Z1。

(1) 計算偶數欄位和

儲存格AB2：=SUMPRODUCT((A1:Z1)*(MOD(COLUMN(A:Z),2)=0))

COLUMN(A:Z)：欄位A~Z，將要運算的資料範圍起迄欄位轉換為數字，欄位A=1、欄位B=2、欄位C=3、…。

在網路上看到有人在討論：在一個資料範圍中，要找出指定項目的最大值和最小值，而指定一個項目時，該如何處理？指定二個項目時，該如何處理？(參考下圖)

通常這種題目，都必須透過陣列公式來處理。可以用來練習、認識陣列公式的處理！

【準備工作】

選取儲存格A1:B25，按一下 Ctrl+Shift+F3 鍵，選取「頂端列」，定義名稱：項目、內容。

有網友問到一個問題：在 Excel 中要如何怎麼設定，才能呈現以下的效果：

設定一基數，超過此基數時呈現負差值，字體變紅色；小於此基數時呈現正差值，字體變藍色。例如：(參考下圖)

如基數為10....對應數為15.....則呈現-5(文字轉紅)

如基數為10....對應數為7.......則呈現+3(文字轉藍)

(1) 顯示正負差值

學校老師用 Excel 記錄學生小考成績時，要善用試算表的設定格式化的條件功能。製作動態效果的成績表，達到最大的提醒作用。

例如：將不及格的分數用紅色標示，並且將累計超過三次不及格者，以紅色底色顯示。

(1) 標示不及格分數

1. 選取儲存格C2:H26。

2. 在設定格式化的條件中，新增一個規則：

參考下圖，有網友想要將 VIP 滿一年(到期日)者，在到期前二個月(60天)內給予警示，例如以紅色字顯示，而已經過期者以灰色顯示，該如何處理？一般這種案例都是以「設定格式化條件」來操作即可。

(1) 只設定日期欄位給予不同色彩

1. 選取儲存格E2:E22。

2. 設定格式化的條件為：使用公式來決定要格式化哪些儲存格。

3. 輸入公式：=E2<TODAY()

(2022 年有新版本：Excel-將民國年格式轉換為西洋年格式)

前言：先前有一篇將民國年轉西元年的文章，因為只考慮民國年只有二位數所寫出的公式，在三位數的民國年會產生錯誤，特別重新撰寫了新的公式。

在如下的民國年欄位中(年月日以「.」分隔)，想要產生西元年的表示法(年月日以「/」分隔)，該如何處理？

可行的做法：先把第一個和第二個「.」的位置找出來，再分別取出年月日的值，將其以「/」串接。

(1) 第一個「.」的位置

有網友問到：如果要將某個數值依間隔值分割，依序列出，最後列出不足數，該如何處理？(參考下圖)

【輸入公式】

儲存格B3：=IF(COLUMN(A:A)>INT($A3/$B$1)+1,"",IF(COLUMN(A:A)<=INT($A3/$B$1),$B$1,MOD($A3,$B$1)))

INT($A3/$B$1)：計算儲存格A3內容共含有幾個間隔值。

COLUMN(A:A)<=INT($A3/$B$1)：判斷儲存格所在欄是否小於間隔值數。

有網友問到：若要為原始數值加上000或是去除000來表示，該如何處理？(參考下圖)

(1) 加上 000

若要為數值加上 000，表示該數值必須乘以 1000，或是以 TEXT 函數來處理：

儲存格B2：=TEXT(A2&"000","#,###,000")

(2) 去除 000

有位網友問到，想要將一些地址摘要顯示只到路名或街名，不知該如何處理？(參考下圖)

在台灣的地址，好像有路就不會有街，有街就不會有路，如果真是如此，則利用 FIND 函數即可順利解決該問題。

儲存格B2：=LEFT(A2,IFERROR(FIND("路",A2),1)*IFERROR(FIND("街",A2),1))

IFERROR(FIND("路",A2),1)：運用 FIND 函數找尋「路」在儲存格A2中的字串位於第幾個字，如果找不到，運用 IFERROR 函數將結果取「1」。

IFERROR(FIND("街",A2),1)：運用 FIND 函數找尋「街」在儲存格A2中的字串位於第幾個字，如果找不到，運用 IFERROR 函數將結果取「1」。

有網友問到，如果要將一欄的資料轉換成多行，該如何處理？一般這種問題，可能有二種轉換方式，分述如下。

若要將一欄資轉換為多欄資料，只要藉助 OFFSET 函數，配合 ROW 和 COLUMN 函數即可完成。

(1) 取出連續固定數值(例如10)儲存格為 1 欄

例如：取儲存格A1:A10為 1 欄、取儲存格A11:A20為 1 欄，依此類推。。

儲存格C1：=OFFSET($A$1,ROW(1:1)+(COLUMN(A:A)-1)*10-1,,)

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

大家已經很熟悉如何使用 Office 2010 來製作 PDF 檔，有同事遇到一個現象(參考下圖)！

當你的 Word 檔中的放了一張圖片，再另存新檔製成 PDF 檔後，將滑鼠移至 PDF 上的圖片時，會顯示圖片原始的檔案路徑，這個結果帶來了一些困擾，不知道可不可以不要顯示檔案路徑呢？

跟著以下這樣做：

在另存為 PDF 檔時，選取「選項」按鈕：

有網友問到一個將表格摘要統計的問題。參考下圖左的資料清單，其中有非常多筆記錄，而 ID 資料共有三類，現在，要摘要出下圖右的統計表。通常這類題目都是交給 SUMPRODUCT 函數來處理！

【準備工作】

選取A欄至E欄中含有資料的儲存格，按一下 Ctrl+Shift+F3 鍵，定義名稱：ITEM、ID、SOH、REC、SOLD。

【輸入公式】

儲存格H3：=SUMPRODUCT((ITEM=$G3)*(ID=$H$1)*INDIRECT(H$2))

有網友問到：如果在儲存格A1~A3中填入數字 1，如何使儲存格A4~A6自動填入數字2及儲存格A7~A9自動填入數字3，以此類推至10。

假設在儲存格A1中填入一個起始值，在儲存格A2中輸入公式，再將儲存格A1複製後，貼至下方各列儲存格。

儲存格A2：=A$1+INT(ROW(1:1)/3)

INT(ROW(1:1)/3)：ROW(1:1)=1，往下複製後會產生 ROW(2:2)=2、ROW(3:3)=3、…。所以INT(ROW(1:1)/3) 可以產生 0,0,1,1,1,2,2,2,…。

有網友問到：如果想要在一個資料表中，查詢含有某些特定字串的資料搜集在一個清單中，該如何處理？(原始資料參考下圖左，篩選後清單參考下圖右。)

如果你使用「篩選」工具，在文字篩選中使用「包含」條件：

在條件中選擇「包含」+「電子」：

你是否想過要將一段文字放在數個圖案中，而這些文字是連續的被顯示，參考下圖：

在 Word 中可以輕易的做到！方法如下：

1. 先準備好幾個圖案(設定好可以新增文字)。

2. 選取第一個圖案物件，再選取［格式］功能表中的「文字」區中的「建立連結」。

3. 接著在要接連顯示文字的物件上按一下。

有網友問到：如果要在一個儲存格中搜尋多個字串，該如何判斷是否存在？(參考下圖)

在下圖中分別使用了 FIND 函數和 SEARCH 函數來找尋一個字串和二個字串，來練習如何判斷是否存在要找的關鍵字。

1. 使用 FIND 函數

(1) 儲存格C2：=IFERROR(IF(FIND("感測器",B2),"V",),"X")

有網友問了這樣的一個問題：在 Excel 中有一個如下圖左的資料表，想要將儲存格位置重組成下圖右的資料表，該如何處理？

仔細觀察發現儲存格位置重新排列有其規則性，每一個屬性相同的儲存格都是間隔 3 列。

公式如下：

儲存格G2：=OFFSET($A$2,(ROW(1:1)-1)*3,,,)

ROW(1:1)=1，若往下複製可以產生 ROW(2:2)=2、ROW(3:3)=3、ROW(4:4)=4、…。

在網路上看到有人在討論一個較大的數字通常在 Excel 中會以指數型式表示，例如：6.37104E+13(參考下圖)。如果想要以科學記號來表示(6.37104 x 10^13)或是完整顯示(63710401047552)，該如何處理？

其中輸入的數字以指數型示顯示會預設 7 個有效數字，若將所有數字完整顯示也只能顯示 15 個有效數字。

在A欄的顯示結果，是一般輸入較大的數字預設的顯示方式，如果要改成B欄的型式，而且取 6 個有效數字。

(1) 儲存格B2輸入公式
=LEFT(TEXT(A2,"0.00000E+0"),7) & " x 10^" & RIGHT(TEXT(A2,"0.00000E+0"),2)