和學校同事討論一個觀念:
如下圖,在 Excel 中對一個清單中的數值計算時取百分比,加總時合計100%,看似沒問題。但是如果取四捨五入至小數4位再加總,很容易產生合計並非100%的問題。
也就是當數值取了百分比,再以「數值格式」取指定的小數點位數,顯示出來的數值,和以「公式計數」取指定的小數點位數,兩者有可能產生落差。
例如:有時 Excel 用來計算1項成績,是由多人成績的平均而來,而該項成績又是只占某個百分比時,而多次的運算過程中,小數點是否由公式指定計算而得,其結果會有些微的差別,要小心。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡,如果要根據起始時間和結束時間要計算起迄時間,是一件簡單的事。因為時間是數值,所以只要把兩個時間相減即可。
觀察下圖,因為沒有日期,所以當結束時間小於起始時間時,會出現公式錯誤。
該如何處理?
例如,儲存格C12:=B12-A12+(B12<A12)
以 B12<A12 來修正,其會傳回 TRUE/FALSE,因為在運算過程中會轉換為 1/0。
所以可以用來在結束時間小於起始時間時能夠加 1。
vincent 發表在 痞客邦 留言(0) 人氣()
本篇的例子要以「計算絶對值的平均」來練習陣列公式。
【設計與解析】
儲存格C3:{=AVERAGE(ABS(A3:A13))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動會加上「{}」。
ABS 函數:取儲存格內容的絶對值
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡,如下圖,如果想要根據一個清單利用設定格式化的條件加以標示其中有的項目,該如何處理?
參考作法:
1. 選取儲存格A3:B24
2. 設定格式化的條件:=COUNTIF($D$3:$D$10,$A3)
3. 設定淺綠色儲存格色彩。
vincent 發表在 痞客邦 留言(0) 人氣()
如下圖,在 Excel 裡如果要串接含有日期的多個儲存格,該如何處理?
【作法一】
儲存格E3:=PHONETIC(A3:D3)
利用 PHONETIC 函數串接儲存格,但是數值不會被串接,所以日期不會出現。
vincent 發表在 痞客邦 留言(0) 人氣()
在文件中常會用到項目符號或編號,而在 Excel 裡可以在公式中利用數字或字母自動產生編號,該如何處理?
1. 用數字當編號
儲存格B3:=ROW(1:1)&"."&A3
複製儲存格B3,貼至儲存格B3:B11。
ROW 函數會傳回儲存格的列號。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 的工作表中有一個項目清單,每個儲存格裡都有多個項目,每個項目以「,」隔開。如何計算每個儲存格裡的項目個數?
【設計與解析】
儲存格B3:=LEN(A3)-LEN(SUBSTITUTE(A3,",",""))
(1) SUBSTITUTE(A3,",","")
利用 SUBSTITUTE 函數將儲存格A3裡「,」,全置換成空字串「""」
vincent 發表在 痞客邦 留言(1) 人氣()
在 Excel 的工作表裡,如果要找出內容重覆的儲存格,有時是一件煩人的事,該如何快速標示出重覆的儲存格?
【設計與解析】
1. 利用公式
儲存格B3:=IF(COUNTIF($A$3:A4,A4)<>1,"V","")
複製儲存格B4,貼至儲存格B4:B14。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡,如何判斷數值是否為整數?
【設計與解析】
提供以下三種方式:
(1) 儲存格B3:=INT(A3)=A3
利用 INT 函數,取不大於儲存格A3的最大整數,如果和儲存格A3相等,則為整數(傳回TRUE)。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 中,如果想要計算指定日期區間的平均值,該如何處理?
在下圖中,有一個日期和數值的清單,如何根據起始日期結束日期計算數值的平均。
【設計與解析】
1. 使用 AVERAGEIS 函數
儲存格D7:=AVERAGEIFS(數值,日期,">="&E3,日期,"<="&E4)
vincent 發表在 痞客邦 留言(0) 人氣()