學不完．教不停．用不盡

在 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。