和學校同事討論一個觀念:
如下圖,在 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) 人氣()
網友想要在 Excel 中顯示數值時,想要區隔正、負數,該如何處理?
預設值負數會顯示「-」,如何讓正數顯示「+」?
如何讓負數會顯示「紅色」,讓正數顯示「藍色」?
【設計與解析】
1. 正數:+,負數:-,數值格式:+0;-0;0
vincent 發表在 痞客邦 留言(0) 人氣()
在 Word 文件中如果想要將相同圖片一次置換為另一個不一樣的圖片,該如何處理?
在以下的文件中,想要快速以大的圖全部取代小的圖。參考以下的步驟:
1. 選取大的圖(含Line的QR Coce),按 Ctrl+X,將其剪下至剪貼簿。
2. 開啟「取代、對話框,點選:更多。
3. 取消勾選所有的搜尋選項。
vincent 發表在 痞客邦 留言(1) 人氣()
在 Excel 的工作表中有時會希望比對多個儲存格是否全數相同,方式有很多種。以下來介紹公式長度不會太長的設計方式。
【設計與解析】
儲存格E3:{=AND(EXACT(A3,A3:D3))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E3,貼至儲存格E3:E15。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡要計算符合條件的個數是常見的計算,但是一旦以多條件運算,會使用到AND或OR運算,該如何處理?
本例中,可以學習 COUNTIF、COUNTIFS、SUMPRODUCT 函數的應用。
1. 計算「國文及格」且「英文及格」的個數(AND運算)
選取儲存格A3:B17,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:國文、英文。
(1) COUNTIFS(國文,">=60",英文,">=60")
vincent 發表在 痞客邦 留言(3) 人氣()
當你編輯好一份文件時,如果傳送給別人時,想確保檔案內容未被編輯過,可以插入自己的簽名。以 Excel 試算表為例來操作:
1. 選取「插入/文字」功能表裡的「簽名欄」。
2. 選取「Microsoft Office 簽名欄」。
3. 填寫簽章設定內容。
vincent 發表在 痞客邦 留言(1) 人氣()
在 Excel 中常會需要用到亂數,而 RAND 和 RANDBETWEEN 兩個函數在巧妙的運用之下,可以產生想要的各種亂數。
例如,根據指定的位數要產生整數或小數的亂數,該如何處理?
1. 產生指定整數位數的亂數
儲存格B3:=RANDBETWEEN(10^(A3-1),10^A3-1)
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡,一天被視為數值「1」,所以1小時:1/24、1分鐘:1/24/60、1秒鐘:1/24/60/60。
本篇來看看如果分別以 TIME 函數和運算式來執行累加的運算,結果是否會正確?
1. 以累加20分鐘為例
(1) 以函數累加20分鐘,儲存格A4:=A3+TIME(0,20,0)
(2) 以運算式累加20分鐘,儲存格C4:=C3+1/24/3
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 裡,依日期判斷是第幾季是常見的事,要如何處理?
【設計與解析】
提供三種方式供參考練習:
(1) 儲存格B3:=ROUNDUP(MONTH(A3)/3,0)
利用 MONTH 函數取儲存格A3裡日期的月份,月份「除以3」之後以 ROUNDUP 函數將商無條件進位至個位數。
vincent 發表在 痞客邦 留言(0) 人氣()
有網友問到:在一個 Excel 的活頁簿檔案中含有多個工作表,如何將全部工作表輸出成一個PDF檔?
例如,下圖中的活頁簿裡有四個工作表,如何將這四個工作表的內容集合在一個PDF檔中。
在另存新檔時,先選取存檔類型為「PDF」。再點選:選項。
選取:整本活頁簿。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 中如果已經有一個資料清單,現在想要將每個儲存格都新增相同的字串,有沒有較快速的方式。以下介紹的是不要使用公式的做法。
參考以下的步驟:
1. 在第一個儲存格輸入新增文字。
2. 選取儲存格B3:B17。
vincent 發表在 痞客邦 留言(0) 人氣()