學不完．教不停．用不盡

最近剛好有人問到要對名單重排的問題，在 Excel 中如何對同一欄的資料以亂數重新排序？（參考下圖）

【公式設計與解析】

Excel 中有很好的排序工具，但是沒有提供亂數排序的功能，偏偏在實務上常會需要用到亂數重排的應用，所以必須以手動方式建立公式來處理。每按一次F9鍵，資料即會重新排序，然後再依需要取用。

1. 產生亂數

在 Excel 的工作表中常會看到有人輸入了 YYYY.MM.DD 格式的日期，由於在 Excel 中可接受的日期格式中，年月日必須使用「-」或「/」分隔。該如何轉換為正確的日期格式？

【公式設計與解析】

在 Excel 中必須使用「-」或「/」做為年、月、日的分隔。

1. 將「.」置換為「/」

在 Excel 中每個儲存格內容由多列的文字組成，如何能分別取出每一列的文字？

（參考下圖）

【公式設計與解析】

儲存格C2：=SUBSTITUTE(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",50)),

手機上有攝影機，所以掃描 QR Code 是一件很容易的事，用來掃描「超連結網址、文字、郵件地址、電話號碼、地理座標、WIFI存取」等，實用且方便。很多的 App 也陸續結合掃描 QR Code 功能變成內建功能，讓使用者能更方便來使用。以下例舉數個實例。

1. 在Edge 瀏覽器中使用掃描 QR Code 來連結網址或是搜尋文字

當開啟 Edeg 瀏覽器時，在新增內容時，可以利用「搜尋或輸入網址」中的 QR Code 掃器。對準條碼或 QR Code 加以掃描，掃描得到的網址或文字都會被複製到剪貼簿中。

一個班級的導師都有掌握學生的基本資料，以便了解學生和快速和家長取得連繫。通常這個資料表是以 Excel 的檔案來儲存，老師們可以拿它來做什麼？

基本資料不能只拿檢視，改變其顯示的方式，可以擴大資料表格的效用。

（以下的學生資料是虛構的）

1. 資料表格

網友陸續問到一些關於 Excel 中資料驗證的做法，以下提供三個例子參考：

1.限定輸入特定數字限定

2.只能輸入大寫字

3.限定大寫英文和全部8碼

在 Excel 的工作表中，取得一個以小時為單位的網頁瀏覽量統計表，如何將其轉換為天報表、週報表？（參考下圖）

【公式設計與解析】

1. 將時報表轉為天報表

儲存格E2：=SUM(OFFSET($B$2,(ROW(1:1)-1)*24,0,24,1))

有同仁問到：在收 Gmail 郵件時，有些郵件來源總是會被 Google 判定為垃圾郵件，不勝其擾，該如何處理呢？

如果把某些郵件位址設定為白名單，Google 就不會將其放入垃圾郵件處理了。

作法如下：（建立該郵件的篩選器）

1. 開啟會被放入垃圾郵件的郵件。

2. 在選單中選取「篩選這類的郵件」。

在 Excel 中，相信很多人都使用過「資料驗證」來讓資料輸入時能提高正確性。其實，你還可以利用資料驗證來除錯。

例如，下圖中設定讓「性別」欄位只能輸入「男、女」。但是，問題是如果資料是由外部匯入的，就無法由輸入受到資料驗證的把關，該怎麼辦呢？

你可以選取要檢查的欄位，再選取「資料／資料工具」功能表中的「圈選錯誤資料」。

在 Excel 中的一天以「1」表示，所以：

1小時＝1/24；1分鐘=1/24/60；1秒鐘=1/24/60/60

【間隔1小時】儲存格A3=A2+1/24

【間隔1分鐘】儲存格B3=B2+1/(24*60)

【間隔1秒鐘】儲存格C3=C2+1/(24*60*60)

複製儲存格A3:C3，貼至儲存格A3:C25。

延續這一篇：Excel-認識SUMPRODUCT函數

在 SUMPRODUCT 函數中，要計算乘積和的儲存格範圍，必須符合相同的陣列長度。

1. 1欄垂直陣列 X 1欄垂直陣列

兩個垂直陣列中的個數必須相同。

在 Excel 的公式中，SUMPRODUCT 函數應該可以算得上首屈一指的重要了。其「乘積和」的概念，在日常生活中十分常見。練習以下的例子。

1. 計算甲和乙的乘積和

甲：A2:A5；乙：B2:B5

透過 SUMPRODUCT 函數計算「乘積和」

公式：=SUMPRODUCT(A2:A5,B2:B5)

公式：=SUMPRODUCT({1;3;5;7},{2;4;6;8})

1. 不因在第一列插入資料改變公式

在 Excel 的公式中，當你在插入一列（欄）後，Excel 會自動幫你調整相對的公式內容。

雖然方便實用，但有時你就是不想讓它自動調整。

【例】如果儲存格F5公式：=(B3-B6)/(A3-A6)

在 Excel 中要輸入各種符號，有時不是很直覺和方便，但是總要完成輸入工作啊！

通常我會使用以下的方式來輸入。（這只是個人習慣，你會如何輸入？）

(1) 利用 Excel 的「符號」功能表，在「符號」對話框中選取想要的符號。

快速鍵：按著Alt鍵+N→U，即可開啟「符號」對話框。

有人問到：身份證的第一碼為英文字母，常會用到要將其轉換為數字，例如：A→1、B→2、C→3、….、Z→26。如何能利用 Excel 來建立這個轉換的對照表？

【公式設計與解析】

儲存格A1=CHAR(64+ROW(A1))

複製儲存格A1，貼至儲存格A1:A26。

歡度春節之後，利用寒假開學前，來讓學校同仁，再次有機會親近 Excel，看看能否利用試算表工具來讓工作效能提升，所以製作了研習的範例。本篇是關於「陣列+SUM+IF、SUMIF、SUMIFS、SUMPRODUCT」的綜合應用。

延續上一篇文章：Excel-陣列公式初探

在使用陣列在執行加總計算時，利用 SUM+IF、SUMIF、SUMIFS、SUMPRODUCT等公式可以得到相同結果，可謂異曲同工，以下希望能融會貫通，使用時如魚得水。

下載練習檔案：點我下載

1. 計算「A*B」的總和

網友問到：在 Excel 的工作表中若要將西元年以民國年顯示，並標示星期N，以「(N)」顯示，該如何處理？（參考下圖）

【公式設計與解析】

(1) 2020/02/22→109/02/22

儲存格D2：=TEXT(A2,"[$-zh-TW]e/mm/dd;@")

在如下圖的 Excel 工作表裡，每個學生的各科成績可能是：A++、A+、A、B++、B+、B、C。如何計算每個學生 A、B、C 分別的個數？（內容亂數產生）

【公式設計與解析】

儲存格H2：=SUMPRODUCT(1*(SUBSTITUTE($C2:$G2,H$1,"")<>$C2:$G2))

複製儲存格H2，貼至儲存格H2:J26。

學過程式設計的使用者，對於「陣列」一定不陌生！

陣列是一群相同資料類型的集合，在運算時透過相同規則可以處理大量的資料，並執行相同的運算。在 Excel 中陣列的觀念和程式設計中的觀念類似，為了讓 Excel 知道你是要使用陣列公式來運算，所以在輸入公式結束後，要按 Ctrl+Shift+Enter 鍵，而 Excel 會將公式以「{  }」來含括。

1. 水平一維常數陣列

(1) 選取儲存格A1:F1（共 6 個儲存格）。

(2) 輸入公式：={1,2,3,4,5,6}。（注意：每個數字以「,」分隔）

Google 試算表雖然不像 Excel 的功能如此其全，但仍有其優點。例如：在 Google 試算表中就可以直接對文字或句子做翻譯的功能。

在此利用 GOOGLETRANSLATE 函數：

語法：GOOGLETRANSLATE(文字, 原文語言, 譯文語言)

(1) 文字