有網友問到,在一個 Excel 的資料清單中(如下圖左),如果想要取出合於部分名稱的總和(如下圖右)該如何處理?
本例為資料清單中的名稱和要搜尋的名稱略有差異,前後多了一些字元。
【準備工作】
有網友問到,在一個 Excel 的資料清單中(如下圖左),如果想要取出合於部分名稱的總和(如下圖右)該如何處理?
本例為資料清單中的名稱和要搜尋的名稱略有差異,前後多了一些字元。
【準備工作】
有學校老師問到:當在 Office 2010 中(例如 PowerPoint 2010)中開啟一個 Office 2013(例如PowerPoint 2003)的檔案,在繪圖物件中選擇格式的色彩時,沒有列出想要的色彩(如下圖),該如何處理?
你只要在[設計]功能表中選取一種喜歡的佈景主題,或是自訂一個新的佈景主題:
之後,即可選用不同色彩配置的格式了:
在 Excel 中,格式化條件設定是一個重要功能,以下要練習:如果你想要讓指定儲存格的周圍(九宮格範圍),顯示不同的色彩,以凸顯這個區域,該如何處理?(參考下圖)
(在一個較深的綠色構成的儲存格範圍中,某個儲存格週圍的九宮格顯示較淺的綠色
【例子示範】
1. 選取儲存格A1:N14。
2. 選取[樣示/設定格式化的條件]。
有同事問到:在 Excel 中如果想要快速建立一個數字組成的(欄X列)矩陣數列(如下圖),該如何處理?
其實方法很多,以下提供一個方便的方式:
1. 選取儲存格A1:J10。
2. 輸入公式:=row()*column()
3. 按一下 Ctrl+Enter 鍵。
在 Excel 中有一個數值的數列,如果要計算符合某些條件的個數,該如何處理?為了讓學生練習,所以使用三種不同的做法來練習。
【準備工作】
選取儲存格A1:A25,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。
【輸入公式】
在 Excel 的資料表中中有一個組別的名冊(如下圖左),有人想要依組別將項目集合在一起(如下圖右),該如何處理?
【準備工作】
選取儲存格A1:C27,按一下 Ctrl+Shift+F3 鍵,定義名稱:序號、組別、姓名。
【輸入公式】
儲存格E2:{=IFERROR(INDEX(姓名,SMALL(IF(組別=E$1,序號,),ROW(1:1)+COUNTIF(組別,"<>"&E$1)),),"")}
人事室常會用到要將累計請假時數換算成天數,而且以 8 小時為一天,該如何處理?(參考下圖)
在 Excel 2010 中有一個 DEC2OCT 函數(將 10 進制數轉換成 8 進制數),十分方便好用。
如果將請假時數(10進制數)轉換為天數(8 進制數),可以觀察 B 欄的運算結果,發現只要取出最末一碼為時數,扣除未一碼以外的數即為天數。
(1) 計算天數,取出 8 進制數的除最末一碼以外的數
儲存格C2:=INT(DEC2OCT(A2)/10)
有網友問到:在 Excel 的工作表中有一些日期的字串(參考下圖A欄),因為不是 Excel 標準的日期格式,所以無法用來排序,該如何轉換成 Excel 可以使用的日期格式呢?(參考下圖E欄)
(1) 取出月
儲存格B2:=LEFT(A2,3)
(2) 取出日
儲存格C2:=TRIM(MID(A2,5,2))
網友問到:如果有一個員工的名冊,想要用其製作一個亂數排班表,其中同一天的員工姓名不能有重覆的現象。
因為亂數產生,所以會用到「反覆運算」,因此要先選取[檔案/選項],在[公式]標籤下勾選「啟用反覆運算」,並將最高次數設定為「5,000」(數值越大,運算時間越長)。
先選取儲存格A1:A9,按一下 Ctrl+Shift+F3 鍵,定義名稱:員工。
儲存格D2:=IF(COUNTIF($D2:$I2,D2)>1,INDEX(員工,INT(RAND()*8+1),),D2)
在 Excel 中有一個時間的數列,如果想要標示每個時間所在的區間(參考下圖),該如何處理?其中以每 2 個小時為一個區間,例如:08:00-09:59。
儲存格B2:=RIGHT("0" & (INT(INT(A2/(1/24))/2)*2),2)&":00" & "-" & RIGHT("0"&(INT(INT(A2/(1/24))/2)*2+1),2)&":59"
INT(A2/(1/24)):找出儲存格A2中的小時數。(Excel 一天以 1 計,每小計以 1/24 計。)
INT(INT(A2/(1/24))/2)*2:找出每間隔 2 小時的起始小時數。
RIGHT("0" & (INT(INT(A2/(1/24))/2)*2),2):將小時數只有 1 位者前面補 0。
有網友問到:在 Excel 中有一個資料表(如下圖左),如果想要將項目和數量整合在一起,並且忽略 0 的項目,該如何處理?
儲存格F2:=IF(B2,$B$1&B2& " ","" ) & IF(C2,$C$1&C2& " ","" ) & IF(D2,$D$1&D2,"")
IF(B2,$B$1&B2& " ","" ):判斷儲存格B2如果不為 0,則串接儲存格B1和B2,再串接一個空格,否則串接空字串。
IF(C2,$C$1&C2& " ","" ):原理同上,處理 B 的資料。
IF(D2,$D$1&D2,""):原理同上,處理 C 的資料。
網友問到:在 Excel 中有一個如下圖中的資料表,如果想要根據編號來找出組別、位置和結果,該如何處理?
【準備工作】
選取儲存格A1:B18,按一下 Ctrl+Shift+F3 鍵,定義名稱:組別、名稱。
【輸入公式】
(1) 找出結果
有人問到如果要表示一個日期為星期幾,該如何處理?(參考下圖的三種表示方式。)
(1) 表示 A 和 表示 B
在儲存格格式中設定「數值」格式的類別為「日期」,類型為「星期三」或「週三」等格式。
有網友問到如下圖的數值儲存格和文字儲存格,如何分別計算其符合的個數?
【準備工作】
選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。這是由數字組成的儲存格。
選取儲存格F1:F26,按一下 Ctrl+Shift+F3 鍵,定義名稱:文字。這是由文字組成的儲存格。
【輸入公式】
在 Excel 中有一個數列(如下圖),如果想要依據區間列出各自的排名,該如何處理?
【準備工作】
選取儲存格B1:B26,按一下 Ctrl+Shift+F3 鍵,定義名稱:數值。
【輸入公式】
(1) 列出區間
有網友問到:在 Excel 中有一個資料表(如下圖),其中有 Ref 和 Name 兩個欄位,如何將相同 Name 的資料集合在同一欄,並且列出 Ref,該如何處理?(其中的 Ref 為文字模式)
【準備工作】
選取儲存格A1:B6,按一下 Ctrl+Shift+F3 鍵,定義名稱:Ref、Name。
在 Excel 的工作表中,常見到要找出一段日期中不含六日的天數,該如何處理?(參考下圖)
你可以使用 NETWORKDAYS 函數很容易的求得結果,這次要用公式模擬這個函數的功能。
【公式解析】
(1) 儲存格C2:=NETWORKDAYS(A2,B2)
只要知道起始日期和終止日期,即可透過 NETWORKDAYS 函數求得不含六日的天數。
在 Excel 中有可能有些儲存格含有文字數,如何取出數字來執行小計呢?(參考下圖)
【準備工作】
選取儲存格A1:B25,按一下 Ctrl+Shift+F3 鍵,定義名稱:內容、數字。
【輸入公式】
在 Excel 中有一個資料表(如下圖左),根據不同組距值,要自動顯示範圍、最大值、最小值、平均值和總和(如下圖右),該如何處理?
【準備工作】
選取資料範圍(例如:儲存格A1:B52),按一下 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:序號、數值。
選取儲存格E1,定義名稱:組距。
網友問到:在 Excel 的工作表 1 中已經輸入了物品名稱和價格,如果想在工作表 2 中輸入工作表 1 內的物品名稱,即可自動顯示該物品的價格(參考下圖),該如何處理?
就用這例子來做查表練習吧!
【準備工作】
(1) 將儲存格A1:A27,定義名稱:物品。
(2) 將儲存格B1:B27,定義名稱:價格。