贊助廠商

學不完.教不停.用不盡文章列表

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

Excel-利用資料驗證來除錯

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

Excel-利用資料驗證來除錯

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

Excel-利用資料驗證來除錯

Excel 會幫你圈選出來發生錯誤的儲存格。

如果要清掉圈選,可以再選取「資料/資料工具」功能表中的「清除錯誤圈選」。

Excel-利用資料驗證來除錯

利用這個方法,可以來找「資料」欄位中不是整數者。例如,在「資料」欄位中設定儲存格為 1~100 的整數。

Excel-利用資料驗證來除錯

結果圈選了二個看似整數的儲存格,這是怎麼一回事?

原來是儲存格輸入非整數,但卻以設定了小數點位數為 0 的格式。所以,看似整數,其實不然。還好,使用資料驗證工具,一下子就找出問題點。

Excel-利用資料驗證來除錯

你也來想想,還可以用在那裡?

利用Google搜尋本部落格全部「資料驗證」文章

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(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。

image

 

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

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

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

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

image

 

【自行練習】產生固定間隔1小時2分3秒

儲存格A3:=A2+1/24+2/(24*60)+3/(24*60*60)

Excel-產生固定間隔的時、分、秒

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

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

認識SUMPRODUCT函數

 

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

每一欄垂直陣列中的個數必須相同。

認識SUMPRODUCT函數

 

3. 1列水平陣列 X 1列水平陣列

每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

 

4. 1列水平陣列 X 多列水平陣列

每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

 

5. 矩形陣列 X 矩形陣列

每一欄垂直陣列中的個數必須相同;每一列水平陣列中的個數必須相同。

認識SUMPRODUCT函數

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

在 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

Excel-認識SUMPRODUCT函數

 

甲: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

Excel-認識SUMPRODUCT函數

 

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

Excel-認識SUMPRODUCT函數

 

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 陣列。

Excel-認識SUMPRODUCT函數

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

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

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

當在第3列新增一列時,公式會自動被修正為:=(B4-B7)/(A4-A7)

這是錯誤的結果。因為第 3 列裡預備要輸入資料,公式應為:=(B3-B7)/(A3-A7)

該如何處理?

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

公式修正:=(INDIRECT("B3")-B7)/(INDIRECT("A3")-A7)

INDIRECT:文字串所指定的參照位址。

INDIRECT("B3")將文字「B3」轉換為儲存格B3位址。

轉成字串就不會因插入動作而自動改變參照位址。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

輸入資料後,結果是正確的。

不論插入幾列,都是會保持儲存格B3不變。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

 

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)式傳回值所對應的儲存格位址。

Excel-不因在第一列插入資料或是在最後一列新增資料而改變公式(INDIRECT,OFFSET,COUNT)

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()