在 Excel 中通常會使用 ROUND 函數來調整小數和整數的四捨五入動作,在此,要以 INT 函數來模擬 ROUND 函數的動作,並分小數和整數二個部分來處理。
1. 小數部分
在下圖中,分別呈現取小數 1 位至小數 6 位的結果。
(1) 取小數 3 位四捨五入
儲存格B4:=ROUND(B$1,3)
在 Excel 中通常會使用 ROUND 函數來調整小數和整數的四捨五入動作,在此,要以 INT 函數來模擬 ROUND 函數的動作,並分小數和整數二個部分來處理。
1. 小數部分
在下圖中,分別呈現取小數 1 位至小數 6 位的結果。
(1) 取小數 3 位四捨五入
儲存格B4:=ROUND(B$1,3)
學校同仁問到:在 Word 文件中,如果想要將文件中的某些文字置換成一個圖示,該如何處理較為快速且方便。例如:在下圖中的文件中有許多個 Google Chrome 字樣,想要快速置換成 Chrome 的圖示(在文件第一列),該如何處理?
參考以下步驟:
1. 將複製這個圖示。(在剪貼簿中可以看到這個圖示)
2. 在[常用/編輯]功能表中選取「取代」功能。
同仁問:有時候想要利用手邊的照片,做一個自動播放照片的1分鐘的影片,並且希望加上音樂,很簡單的一個小動作,不知取用何種軟體來製作,才能省時又省事。我的做法是利用手邊辦公室電腦裡就有的軟體『PowerPoint』即可,不需再另外找任何軟體。
本例以 PowerPoint 2013 來介紹。
1. 先將照片和標題放在 12 張投影片中(預定每張播放5秒)。
2. 在第一張投影片中插入一個音訊(例如:MP3檔)。
3. 選取這個音訊圖示,在[播放]功能表中,設定播放的動作:
根據前一篇文章:Excel-14個樞紐分析表應用練習,本篇文章要以公式模擬這些樞紐分析的動作。
資料來源是在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額(如下圖)。現在,取用這個資料清單來練習樞分析表的操作,以下使用 Excel 2013 為例,資料來源有 700 筆以上。
【準備工作】
選取資料表中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、店名、業務員、產品、代碼、機型、單價、數量、銷售額。
在另一個工作表中含有產品及業務員的基本資料。也是先定義名稱:代碼清單、產品清單、機型清單、單價清單、店名清單。
在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額。取用這個資料清單來練習樞分析表的操作。
以下使用 Excel 2013 為例,資料來源有 700 筆以上。
1. 計算各店的銷售總額
在樞紐分析表欄位中,設定:
下圖右是一個 Excel 的資料表,是一個矩陣式的資料清單,要如何篩選出同類型的資料?例如:選取『戊』開頭的資料內容。
【公式設計與解析】
(1)
儲存格A2:{=SMALL(IF((SUBSTITUTE($D$1:$H$10,$B$1,"")<>$D$1:$H$10),
網友問到:在 Excel 的工作表中有一個資料清單(如下圖左),想要依『日期』欄位來統計每個人員在各個月份的時數小計(如下圖右),該如何處理?
【公式設計與解析】
選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、時數。
表<1>
網友問到一個有趣的問題:如何使用 Excel 來計算將一張大紙最多可剪裁成幾張小紙?如下圖,一張大紙在剪裁成小紙時,不論長或寬不見得都能剛好剪成是小紙的倍數,可能會有一些剩餘的部分。如何計算能剪裁的最多數量?
如果要以 Excel 的公式來處理,可以試試:
儲存格D2:=MAX(INT(B2/B7)*INT(B3/B6),INT(B2/B6)*INT(B3/B7))
有二種計算方式:
網友問到:在 Excel 的資料清單中(如下圖),有A、B、C三種狀態,要依下列條件評估狀態:
(1)三個中有一個『高負荷』,結果為『高負荷』;
(2)沒有『高負荷』時,三個中有一個『中負荷』,結果為『中負荷』;
(3)三個都為『低負荷』,結果為『低負荷』
網友問了一個問題:在 Excel 的工作表中想要根據一個矩陣表(雙條件)的資料來查詢對應的結果,該如何處理?
以下圖為例,在表<1>中是一個某疾病風險(三個數字區間)和工作負荷(三個文字項目)對應於發病風險的對照表(其中資料為虛擬),如何以兩個條件查詢對應的發病風險。
【公式設計與解析】
因為有些網友是初學者,所以用比較複雜的公式內容來表示,讓網友能知悉公式和查表內容的對照關係。
有網友問到:在 Excel 中有一個如下圖的清單,其中的代碼欄位含有區域碼,如何根據代碼小計各區域的總和?
以下圖為例,每個人的代碼中含區域碼,如果在不另行取出區域碼的做法下,如何直接計算各區域的小計?
【公式設計與解析】
選取儲存格B1:C30,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:代碼、金額。
最近網友根據這篇文章:將PowerPoint簡報檔轉成影片,在操作上一直不能成功,所以我把操作步驟再重新描述一次。我以 PowerPoint 2013 為例。
1. 你在一個投影片的一張簡報中(以第一張投影片為例),選取[插入/多媒體]功能表中選取「音訊/我個人電腦中的音訊」選項,插入一個音訊(以一個MP3檔為例)。
2. 接著,在[音訊工具/播放]功能表中的「音訊選項」區中,設定:
開始:自動;勾選「跨投影片撥放」;勾選「循循播放,直到停止」。(因為希望整個影片都有背景音樂)
網友問到 Excel 的問題:如下圖,依據訂單編號,想要隨機決定生產順序,所以要決定起始的生產時間,該如何處理?
在下圖中,每按一次 F9 鍵,即可隨機產生一個生產順序。而每一個訂單的生產起始時間,都是由先前訂單的生產時間累加而來。
【公式設計與解析】
儲存格H2:=SUMPRODUCT(($C$2:$C$11<C2)*$D$2:$D$11*$E$2:$E$11)
有網友問到在 Excel 中,跨工作表運算的問題。如果在公式中使用跨工作表位址參照,則須使用格式『工作表!儲存格』,但是要特別注意工作表名稱為純數字或是文字。
在下圖中,工作表名稱分別為:1、2、3、4、一、二、三、四。
參考上圖,特別注意:如果工作表範圍中包含數字,則在公式使必須使用『' '』來含括工作表名稱。
1. 計算工作表1~工作4的儲存格A1:A13的和
儲存格J2:=SUM('1:4'!A2)
如果在 Excel 中要設計二層的下拉式選單,可以配合使用「資料驗證」功能來設計,當原始資料為橫式或是直式時,設計的方式並不相同。
一、原始資料為橫式
參考下圖右側,原始資料是橫式呈現。
參考以下步驟來設計:
1. 將儲存格D1:J1定義名稱:商品類別1。
網友根據前一篇文章(Excel-西元年和民國年互換表示(TEXT,MID,DATE,SUBSTITUTE)中,學到如何執行西元年和民國年的互換,例如:
2015/01/01→1040101→104/01/01 和 104/01/01→1040101→2015/01/01
想要進一步知道,若要執行:1040101→104/01/01→2015/01/01
該如何處理?(參考下圖)
有同仁問到:在 Excel 中,如果有一段連續列被隱藏,但是只想取消隱藏其中的某一列時該如何操作?
在下圖中,將第 4 列至第 13 列予以隱藏。
如果想要取消隱藏第 8 列,而保留其他隱藏列時,該如何處理?如果全部取消隱藏,再分二次將第4至第7列和第9列至13列予以隱藏,這是不明智的做法。
你可以這樣做!在名稱方塊中輸入:A8或是8:8,按下 Enter 鍵,即可選取儲存格A8或是第8列。
網友想要針對 Excel 中的數列清單,以一個千分位、二個千分位、三個千分位為單位來顯示數值,而不改變儲存格中所儲存格的數值內容,該如何處理?
參考下圖的C欄、D欄、E欄,分別以一個千分位、二個千分位、三個千分位為單位來顯示數值,以F欄為例,將E欄內容乘以 2,其結果和將 A欄乘以 2,結果相同,表示E欄內容和A欄內容相同。其中被省略的部分,會以四捨五入來呈現。
如果只是想要改變數值的顯示結果,不想改變儲存格內容,則不需使用函數,要使用數值格式設定,在[儲存格格式]對話框的「自訂」 類別中設定,例如:
C欄儲存格:設定『#.000,』(一個逗號,代表一個千分位)
D欄儲存格:設定『#.000,,』(二個逗號,代表二個千分位)