網友發問:在 Excel 的工作表中,有一個日期清單,如何依距今日的日期區間標示不同色彩?
參考下圖,分別在0-20、21-40、41-60的區間裡顯示不同色彩。
【公式設計與解析】
網友發問:在 Excel 的工作表中,有一個日期清單,如何依距今日的日期區間標示不同色彩?
參考下圖,分別在0-20、21-40、41-60的區間裡顯示不同色彩。
【公式設計與解析】
在 Excel 裡製作統計分析圖表非常方便、簡單,但有時資訊的安排會讓結果難以解讀。例如:下圖中的折線圖裡有五個數列全都擠在一起,如果能篩選部分數列,則可以清晰易讀。
當你做好一個五個數列旳圖表:
只要點選圖表,再選取圖表右側的「篩選」圖示:
最近課程中需要用到「文字雲」,使用的是免費且線上即可操作的文字雲產生器 Word Art。使用上十分的方便,產出效果也是十足。
Word Art:https://wordart.com
準備工作:
1.資料來源
在 Excel 裡,如何計算指定日數特定星期幾的日期?
參考下圖,根據起始日期,再加十天後,要計算最接近指定星期幾的日期。
【公式設計與解析】
儲存格C2:=WORKDAY.INTL(A2+10-1,1,D2)
在 Excel 的資料清單中(參考下圖),在欄位中的內容是日期+時間,如果想要比對特定日期來做運算,該如何處理?
【公式設計與解析】
儲存格E3:=SUMPRODUCT((INT(A2:A14)=E2)*B2:B14)
在 Excel 裡,每個日期時間的數值中,日期為整數部分,時間為小數部分。
在 Excel 裡,資料有時是分散在多個儲存格範圍裡的,如何在這些範圍中取得最高分或是最低分?
例如,一個老師有多個班級,當要比較多個班級分數的最高分或最低分,該如何處理?
【公式設計與解析】
在 Excel 裡的日期格式通常為 2020/11/22 或是 2020-11-22 等,但是國人常會有人表達為 20201122。因為 Excel 把 20201122 視為文字,如何轉換讓日期變為數值,以便於運算?
【公式設計與解析】
儲存格B2:=TEXT(A2,"0-00-00")*1
利用 TEXT 函數,配合參數「0-00-00」將日期轉換為「年-月-日」格式。
網友提問:
如果在 Excel 中,要根據多個儲存格範圍的內容來排名,該如何處理?
【公式設計與調整】
儲存格C3:=RANK.EQ(B3,($B$3:$B$10,$F$3:$F$10),0)
網友提問:在 Excel 中,如何根據以下的檢測數值給予對應的指引?
60-80 健康
80-100或20-60 稍加注意生活作習飲食
100-150或0-20 建議健康檢查
>150或<0 立即就醫
網友問到:
在 Excel 的工作表中,每個月都有設定一個目標值,參考下圖,在實際值大於目標值時,希望能自動標示出來,該如何處理?
下圖中的例子,是在當實際值大於目標值時,該月份的文字以紅色顯示。
【公式設計與說明】
在進行大數據分析前,必須要做資料清理。而在 Power BI 或 Excel 的 Power Query 編輯器中,對於資料的格式可以做的處理如下圖。使用工具方便、輕鬆,但是如果想要以 Excel 的公式來處理呢?
這些功能,如何在 Excel 的工作表中以公式呈現來完成?
1. 小寫
所有文字轉換成大寫字,使用UPPER函數,儲存格B2:=UPPER(A2) 。
網友根據這一篇:Excel-移除表格中重覆的資料,詢問如何刪除重覆欄?
這一篇文章主要是以表格功能裡的「移除重覆項」來操作,但是其主要是刪除重覆的「列」,如果是要刪除「欄」,該如何處理?
我好像找不到適用的工具,可以直接刪除重覆欄。但是,可以使用以下的間接方式。
本例假設要刪除「甲」的重覆項目(項四-A)。
網友想要根據一個 Excel 的資料表(如下圖左),在輸入一個代碼後查得資料表中的價格,該如何處理?
主要是要根據代碼中的「a」和「b」來查詢價格。
【公式設計與解析】
首先,要將「b欄位」的資料重建在二欄之中(如下圖)。
在 Excel 裡建立統計圖表是件容易的事,如果能再把圖表以人們熟悉的圖形或符號來加以視覺化,效果想必更佳。
例如:下圖中將長條圖以人形符號搭配色彩來表示占比,看起來視覺化效果更好。
如果要建立這樣的視覺化圖表,參考以下的作法:
1. 首先,利用資料表格內容建立一個長條圖。
學校裡很多老師會讓學生每一段時間調整座位,而期中考後是一個好時機。最近有老師希望能讓學生的座號是由電腦亂數產生,詢問該如何處理?
【參考公式與解析】
1. 在儲存格H1中輸入公式:RAND(),並複製到儲存格H1:H24。
2. 在儲存格I1:I24中,輸入 1~24 的數值。
回答同事問題,用 Excel 來處理。
學生年齡統計以實足年齡計算:
1.出生月份在9月至12月間者,學生年齡=今年 - 出生年 -1。
2.出生月份在1月至8月間者,學生年齡=今年 - 出生年。
其中出生日期並非正規的日期表示法。
在 Excel 中一般會用到「%」來表示百分比,如果要使用「‰」來表示千分比,如何輸入 「‰」?
你可以:
(1) 先按著 Alt 鍵,再依序按數字1→3→7
(2) 在插入符號對話框中,切換至一般標點,再找到「‰」插入。
其實「‰」很少會用到,這裡主要是要表示,該符號無法拿來運算,原因很簡單:Excel不認得它。「‰」是一個Unicode字元,是一個符號,無法在 Excel 裡運算。
在 Excel 中,快速填滿數值和公式,是試算表中超級重要的操作。當你產生了第一個數值或公式之後,除了複製/貼上的做法之外,你還可以這樣快速產生多個儲存格中的數值和公式。
1. 拖曳填滿控制點
(1) 產生第一個輸入好公式的儲存格。
(2) 藉由拖曳第一個儲存格右下角的填滿控制點,自動產生公式。
在 Excel 的工作表中,其實可以藉由物件的連結來切換工作表。利用以下的操作練習,來製作切換工作表的標籤。
參考以下步驟:
1. 建立一個物件,輸入工作表名稱。
2. 設定文字排列方向(本例:文字由下至上排列)