|
|
在 Excel 中，相信很多人都使用過「資料驗證」來讓資料輸入時能提高正確性。其實，你還可以利用資料驗證來除錯。
例如，下圖中設定讓「性別」欄位只能輸入「男、女」。但是，問題是如果資料是由外部匯入的，就無法由輸入受到資料驗證的把關，該怎麼辦呢？
你可以選取要檢查的欄位，再選取「資料／資料工具」功能表中的「圈選錯誤資料」。
Excel 會幫你圈選出來發生錯誤的儲存格。
如果要清掉圈選，可以再選取「資料／資料工具」功能表中的「清除錯誤圈選」。
利用這個方法，可以來找「資料」欄位中不是整數者。例如，在「資料」欄位中設定儲存格為 1~100 的整數。
結果圈選了二個看似整數的儲存格，這是怎麼一回事？
原來是儲存格輸入非整數，但卻以設定了小數點位數為 0 的格式。所以，看似整數，其實不然。還好，使用資料驗證工具，一下子就找出問題點。
你也來想想，還可以用在那裡？
在 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。
【間隔2小時】儲存格A3：=A2+2/24
【間隔3分鐘】儲存格B3：=B2+3/(24*60)
【間隔4秒鐘】儲存格C3：=C2+4/(24*60*60)
複製儲存格A3:C3，貼至儲存格A2:C25。
【自行練習】產生固定間隔1小時2分3秒
儲存格A3：=A2+1/24+2/(24*60)+3/(24*60*60)
延續這一篇：Excel-認識SUMPRODUCT函數
在 SUMPRODUCT 函數中，要計算乘積和的儲存格範圍，必須符合相同的陣列長度。
1. 1欄垂直陣列 X 1欄垂直陣列
兩個垂直陣列中的個數必須相同。
2. 1欄垂直陣列 X 多欄垂直陣列
每一欄垂直陣列中的個數必須相同。
3. 1列水平陣列 X 1列水平陣列
每一列水平陣列中的個數必須相同。
4. 1列水平陣列 X 多列水平陣列
每一列水平陣列中的個數必須相同。
5. 矩形陣列 X 矩形陣列
每一欄垂直陣列中的個數必須相同；每一列水平陣列中的個數必須相同。
在 Excel 的公式中，SUMPRODUCT 函數應該可以算得上首屈一指的重要了。其「乘積和」的概念，在日常生活中十分常見。練習以下的例子。
1. 計算甲和乙的乘積和
甲：A2:A5；乙：B2:B5
透過 SUMPRODUCT 函數計算「乘積和」
公式：=SUMPRODUCT(A2:A5,B2:B5)
公式：=SUMPRODUCT({1;3;5;7},{2;4;6;8})
垂直陣列（{1;3;5;7}和{2;4;6;8}）以「;」隔開。
公式：=SUMPRODUCT(A2:A5*B2:B5)
公式：=SUMPRODUCT({2;12;30;56})
公式：=2+12+30+56=100
甲：B1:E1；乙：B2:E2
公式：=SUMPRODUCT(B1:E1,B2:E2)
公式：=SUMPRODUCT({1,3,5,7},{2,4,6,8})
水平陣列（{1,3,5,7}和{2,4,6,8}）以「,」隔開。
公式：=SUMPRODUCT({2,12,30,56})
公式：=2+12+30+56=100
2.計算男生的數量總和
公式=SUMPRODUCT(1*(A2:A5="男"),B2:B5)
條件：A2:A5="男"，會傳回 TRUE／FALSE 陣列。
執行「1*」運算，會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
公式=SUMPRODUCT(1*{TRUE;FALSE;TRUE;FALSE},B2:B5)
公式=SUMPRODUCT({1;0;1;0},{2;4;6;8})
公式=SUMPRODUCT({2;0;6;0})
公式：=2+6=8
3. 計算丙級通過的人數
公式=SUMPRODUCT(1*(A2:A5="丙級"),1*(B2:B5="通過"))
公式=SUMPRODUCT(1*{FALSE;TRUE;TRUE;FALSE},1*(B2:B5="通過"))
公式=SUMPRODUCT({0;1;1;0},1*{FALSE;TRUE;TRUE;FALSE})
公式=SUMPRODUCT({0;1;1;0},{0;1;1;0})
公式=0+1+1+0=2
公式=SUMPRODUCT((A2:A5="丙級")*(B2:B5="通過"))
執行「*」運算，相當於邏輯 AND 運算，會將 TRUE／FALSE 陣列轉換為 1／0 陣列。
1. 不因在第一列插入資料改變公式
在 Excel 的公式中，當你在插入一列（欄）後，Excel 會自動幫你調整相對的公式內容。
雖然方便實用，但有時你就是不想讓它自動調整。
【例】如果儲存格F5公式：=(B3-B6)/(A3-A6)
當在第3列新增一列時，公式會自動被修正為：=(B4-B7)/(A4-A7)
這是錯誤的結果。因為第 3 列裡預備要輸入資料，公式應為：=(B3-B7)/(A3-A7)
該如何處理？
公式修正：=(INDIRECT("B3")-B7)/(INDIRECT("A3")-A7)
INDIRECT：文字串所指定的參照位址。
INDIRECT("B3")將文字「B3」轉換為儲存格B3位址。
轉成字串就不會因插入動作而自動改變參照位址。
輸入資料後，結果是正確的。
不論插入幾列，都是會保持儲存格B3不變。
2. 不因在最後一列新增資料而改變公式
如果是在最後一個儲存格之後再新增資料，如何不修公式？
儲存格F2：=(OFFSET(B3,COUNT(B3:B1000)-1,0)-B3)/(OFFSET(A3,COUNT
(A3:A1000)-1,0)-A3)
(1) COUNT(B3:B1000)
利用 COUNT 函數計算儲存格B3:B1000中已輸入幾筆資料。
(2) OFFSET(B3,COUNT(B3:B1000)-1,0)
透過 OFFSET 函數，以儲存格B3為起點，取出相對第(1)式傳回值所對應的儲存格位址。
