2017年10月23日 星期一

Excel-調整時間無條件進位至最近的15分鐘(MROUND)

網友提問:在 Excel 的工作表中有一個打卡時間的清單,如何調整其時間為大於或等於原時間並且最靠近15分鐘的時間。例如:
8:00~8:16→8:15;8:16~8:30→8:30;8:30~8:45→8:45;8:46~9:00→9:00
Excel-調整時間無條件進位至最近的15分鐘(MROUND)

【公式設計與解析】
1. 調整上班打卡
儲存格E2:
=MROUND(B2,15/(24*60))+(MROUND(B2,15/(24*60))
(1) MROUND(B2,15/(24*60))
利用 MROUND 函數調整儲存格B2內容四捨五入至最近的 15 分鐘。
(2) (MROUND(B2,15/(24*60))
判斷第(1)式的結果是否小於儲存格B2的內容,傳回 TRUE/FALSE。若傳回 TRUE,則表示第(1)式的運算執行四了四捨五入的「捨」,所以手動將其加上「15/(24*60)」。若為否,則不會加上「15/(24*60)」。(其中 15/(24*60) 表示 15 分鐘)

2. 調整下班打卡
儲存格F2:
=MROUND(C2,15/(24*60))+(MROUND(C2,15/(24*60))
公式和以上說明完全相同。

Word-快速方便的調整清單的順序

有同事問到:在 Word 的文件中,如果想要改變清單內容的順序,有沒有較方便的方法?
如下圖,如果為了調整清單內容的順序,除了不斷的執行剪下和貼上之外,還可以使用「大綱模式」來操作。
image
在整頁模式下,的確是不易執行此類的操作,既耗時又耗工。但是如果切換至大綱模式下,就方便又輕鬆多了。只要使用上移和下移按鈕,或是Alt+Shift+向上鍵或是Alt+Shift+向下鍵,即可以「段落」為單位調整順序。
操作完成,再切回整頁檢視模式即可。
image

2017年10月19日 星期四

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

當你要列印一個長文件時,如果要節省紙張,你可能選擇「雙面列印」。在雙面列印一個A4版面的文件後,直接由側邊裝訂即可使用。但是,如果你是使用A5版面,一頁A4要印兩頁A5,而且一張紙要印 4 頁(雙面列印),印後再切割裝訂,該如何處理?
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
假設在 Word 文件中,你有 8 頁A5版面的內容:
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
其設定為紙張:A4,方向:橫向,頁數:單面雙頁。
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
印表機設定:雙面列印、短邊翻頁。
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
雙面列印 4 頁的順序如下:
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
你只要在列印時自訂列印範圍:1,3,4,2,5,7,8,6:
(第 1 面:1/3頁、第 2 面:4/2頁、第 3 面:5/7頁、第 4 面:8/6頁)
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
列印後每張從中間切割,就可以1/2、3/4、5/6、7/8各為一頁了。
如果你的文件有很多頁,該如何輸入列印順序?可以借助 Excel 來產生列印順序:
Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?
複製儲存格A12,再貼至 Word 列印對話框中,即可列印。

2017年10月17日 星期二

Excel-新增一列資料時自動調整公式

網友提問:在 Excel 的工作表中,如何在不斷新增的儲存格範圍中找出最大值,並且當最後一個數就是最大值時,於最大值上標示紅色前景色彩,而且當不斷插入新的資料時有效。
如下圖,儲存格B2:B15裡的最大值是50(儲存格B15),所以儲存格B16標示為紅色。如何在新增資料時仍然適用原來公式?
Excel-新增一列資料時自動調整公式
由於 Excel 在你新增列或欄時,有自動調整公式的特性,如何克服這個問題?
在本例中,如果你使用:儲存格A16:=MAX(A2:A15),當你在15列和16列之間新增一列時,公式:=MAX(A2:A15)仍會維持不變。但這不是你要的。
改用以下公式:
儲存格A16=MAX(OFFSET(A2,0,0,ROW(A16)-2,1))
複製儲存格A16,貼至儲存格A16:G16。
OFFSET(A2,0,0,ROW(A16)-2,1)用以找出目前16列以上的儲存格範圍,再以 MAX 函數取儲存格範圍內的最大值。
公式自動變更如下:
Excel-新增一列資料時自動調整公式
儲存格A17:=MAX(OFFSET(A2,0,0,ROW(A17)-2,1))
使用此公式,便形成了一個動態的儲存格範圍。
接著,來設定當最後一個數就是最大值時,於最大值上標示紅色前景色彩。
選取儲存格A17:G17。在[編輯格式化的規則]對話框中設定:
類型:使用公式來決定要格式化哪些儲存格
規則:=A17=OFFSET(A17,-ROW(1:1),0)
格式:紅色前景色彩
在新增一列時,OFFSET(A17,-ROW(1:1),0)永遠對應到儲存格的上一個儲存格。
Excel-新增一列資料時自動調整公式

2017年10月16日 星期一

Excel-計算一維和二維的數列中不重覆的個數(SUMPRODUCT,COUNTIF)

網友問到:如何在 Excel 中,對於一維和二維的數列,計算不重覆的個數。(如下圖)
Excel-計算一維和二維的數列中不重覆的個數(SUMPRODUCT,COUNTIF)
【公式設計與解析】
1. 一維儲存格
儲存格A2:1/COUNTIF(C1:C24,C1:C24)
複製儲存格A2,貼至儲存格A1:A24。
儲存格B2:=SUMPRODUCT(1/COUNTIF(C1:C24,C1:C24))
透過 SUMPRODUCT 函數讓公式執行陣列的運算,本公式的結果相當於:
SUM(A1:A24)
即三個相同時可得三個『1/3』,其和的結果為『1』。

2. 二維儲存格
儲存格J2:=SUMPRODUCT(1/COUNTIF(F1:J6,F1:J6))
二維儲存格的運算和一維儲存格相同。

2017年10月15日 星期日

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

本篇文章是前二篇文章的延伸:
如下圖,如何在 Excel 的工作表中,找出分區各自的最大值所在的儲存格位址?
在下圖中,可以指定每一個分區有幾列,並且於I欄中標示每列的最大值(每列有7個儲存格數值)。當找出每一區的最大值(儲存格L3:L15)後,再找出其儲存格位址(儲存格M4:O15)。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)
【公式設計與解析】
1. 產生序號
儲存格K4:=((ROW(1:1)-1)*$L$1+1)&"-"&ROW(1:1)*$L$1
複製儲存格K4,貼至儲存格K4:K15。
ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
本例中,當儲存格L6為 6 時,會依序產生1-6、7-12、13-18、…。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

2. 找出每區最大值
儲存格L4:=MAX(OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7))
(1) (ROW(1:1)-1)*$L$1
當儲存格L3為3時,公式向下複製時會依序產生 0, 3, 6, 9, …。(作為 OFFSET 函數參數中的相對起始列)
(2) OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7)
根據儲存格L1的數值,透過 OFFSET 函數產生每一區的儲存格範圍。
(3) 再由 MAX 函數取得該範圍中的最大值。(題目有預設每一區中的數值不重覆)

3. 找出最大值的列號
儲存格M4:=MATCH(L4,OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1),0)+$L$1*
(ROW(1:1)-1)+1
(1) OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1)
利用 OFFSET 函數找到每一區的儲存格範圍。(例如:序號1-3區為儲存格B2:H4、序號4-6區為儲存格B5:H7)
(2) MATCH(L4,第(1)式,0)
利用 MATCH 函數找到最大值位於第(1)式傳回的儲存格範圍中的第幾個。
(3) 第(2)式+$L$1*(ROW(1:1)-1)+1
找出各區最大值在I欄中的列號。(本例在第 1 區中,最大值是在第 2 列。)
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

4. 找出最大值的欄號
儲存格N4:=MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1
(1) OFFSET($B$1,M4-1,0,1,7)
根據儲存格M4所提供的最大值在I欄中的列號,透過 OFFSET 函數取得該列的儲存格範圍。(在下圖的範例中第 1 區傳回儲存格B4:H4)
(2) MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1
利用 MATCH 函數取得最大值的位置是 4,所以欄號是4+1=5。(因為資料由B欄開始)
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

5. 產生各區最大值儲存格位址
儲存格O4:=ADDRESS(M4,N4,4)
使用 ADDRESS 函數將第(3)式和第(4)式取得的列和欄參數代入,即可得各區最大值的位址。

只要改變佔儲存格L1的數值,即可改變分區的列數。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

【延伸學習】
如果你想要像本例中可以自動標示每一區中的最大值,可以在設定格化的條件中做以下的設定。
1. 選取儲存格B2:H298,設定格式化的條件。
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)
2. 設定格式化的條件
規則類型 :使用公式來決定要格式哪些儲存格
條件公式:=B2=MAX(OFFSET($B$2,(INT((ROW(1:1)-1)/$L$1))*$L$1,0,$L$1,7))
格式:紅色前景色彩
Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

2017年10月14日 星期六

Excel-設定格式化的條件使用横條圖

當在 Excel 中使用設定格式化的條件時,有許多的預設樣式可供選取使用。本例要使用資料横條來練習。
如下圖,列有六種不同的呈現方式,對於正負數和圖形起始位置略有不同之處。
Excel-設定格式化的條件使用横條圖
(本例中,儲存格B2=儲存格A2、儲存格B3=儲存格A3、儲存格B4=儲存格A4、...)
(A)這是 Excel 的預設值,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
(本例全部都勾選:僅顯示資料横條,所以不會顯示數值。)
Excel-設定格式化的條件使用横條圖
(B)以百分比概念,最小值為百分比=0,所以最小值沒有顯示圖形。設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(百分比)/值(0)
最大值:類型(百分比)/值(100)
Excel-設定格式化的條件使用横條圖
(C) 最小值設為50,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(數值)/值(=MIN($G$2:$G$7)-50)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
(D) 此例數列有工數和負數,設定:
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
負值和座標軸的設定:(預設值)
Excel-設定格式化的條件使用横條圖
(E) 設定負值在中央(0)的左邊(紅色),設定正值在中央(0)的右邊。
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(自動)
最大值:類型(自動)/值(自動)
負值和座標軸的設定:(預設值)
Excel-設定格式化的條件使用横條圖
(F) 正負值不分左右邊,負值以紅色顯示。
Excel-設定格式化的條件使用横條圖
最小值:類型(自動)/值(=MIN($G$11:$G$16)-50)
最大值:類型(自動)/值(自動)
Excel-設定格式化的條件使用横條圖
負值(預設值)和座標軸的設定(無:將負值橫條顯示為正值横條同方向)
Excel-設定格式化的條件使用横條圖

2017年10月13日 星期五

Excel-依起始日期和循環週數標示日期(MOD,INT)

網友問到:如何在 Excel 中能由指定的日期開始,依指定週數循環不同格式,該如何處理?
如下圖,本例在儲存格A2中,指定一個日期為起始日期,第一列會自動標示星期幾,再利用儲儲存格I2的循環週數,自動產生間隔週數的儲存格背景和前景色彩。
Excel-依起始日期和循環週數標示日期(MOD,INT)

【公式設計與解析】
1. 產生日期
儲存格B2:=A2+1
複製儲存格B2,貼至儲存格B2:G2。
儲存格B3:=A2+7
複製儲存格B3,貼至儲存格B3:G17。

2. 產生星期幾
儲存格A1:=A2
複製儲存格A1,貼至儲存格A1:G1。
自定儲存格數值格式:星期三格式

3. 設定日期儲存格的格式化的條件
複製儲存格B2,貼至儲存格B2:G2。
選取儲存格A2:G17,設定格式化的條件。
規則類型:使用公式來決定要格式化哪些儲存格
規則:=MOD(INT((ROW(A2)-2)/$I$2),2)=0
格式:粉紅色背景和紅色前景
Excel-依起始日期和循環週數標示日期(MOD,INT)
公式:=MOD(INT((ROW(A2)-2)/$I$2),2)=0
INT((ROW(A2)-2)/$I$2):將列號除以循環週數取其商,得到其為第幾個循環週期。
MOD(INT((ROW(A2)-2)/$I$2),2)=0:判斷前式的商是否為 2 的倍數(偶數週期數),若是,則顯示不同格式(粉紅色背景和紅色前景)。
Excel-依起始日期和循環週數標示日期(MOD,INT)
另外,
(參考下圖)如果你想要指定一個日期(儲存格I2)為起始,再依循環週數(儲存格I5)顯示不同的格式,該如何處理?
Excel-依起始日期和循環週數標示日期(MOD,INT)
1. 產生日期
儲存格A2:=TODAY()-WEEKDAY(TODAY(),1)+COLUMN(A:A)
關於公式說明,請參考 :Excel-產生最近四週的日期並標示顏色
2. 設定格式
步驟如下:
複製儲存格B2,貼至儲存格B2:G2。
選取儲存格A2:G17,設定格式化的條件。
規則類型:使用公式來決定要格式化哪些儲存格
規則:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)
格式:紅色前景
Excel-依起始日期和循環週數標示日期(MOD,INT)
公式:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)
利用二個條件來判斷是否標示為紅色前景:
(A2>=$I$2):儲存格日期要大於或等於儲存格I2。
MOD(INT((A2-$I$2)/($I$5*7)),2)=0:利用INT((A2-$I$2)/($I$5*7))計算每個儲存格日期是第幾個日期,並且位於第幾個循環週期。再利用 MOD 函數判斷是否是偶數週期,若是給予紅色前景。
Excel-依起始日期和循環週數標示日期(MOD,INT)

Excel-將分數轉換為文字等第(CHAR,MID,INT)

有老師想要在 Excel 的工作表中將總分轉換為等第,該如何處理?
參考下圖,分別以『A...J』 和『甲…癸』來當為等第。
Excel-將分數轉換為文字等第(CHAR,MID,INT)

【公式設計與解析】
本例隨意列舉二種不同的函數來完成。
1. 使用 CHAR 函數
儲存格F2:=CHAR(64+INT((E2-180)/10))
INT((E2-180)/10):可以產生以 180 為準,每 10 分為一級距。INT((E2-180)/10)會產生 1, 2, 3, …。
因為『A...J』是由英文字母構成,所以可以使用 CHAR 函數加入參數(ASCII碼)來產生對應的英文字,例如:『A是65』、『B是66』、『C是67』、…。

2. 使用 MID 函數
儲存格G2:=MID("甲乙丙丁戊己庚辛壬癸",INT((E2-180)/10),1)
第二種等第是以中文字來表示,無法使用 CHAR 函數,所以改用 MID 函數來取出對應位置的等第。
INT((E2-180)/10):可以產生以 180 為準,每 10 分為一級距。INT((E2-180)/10)會產生 1, 2, 3, …。

2017年10月12日 星期四

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

網路看到的教學文值得推一下!如何在統計圖上只顯示最大值和最小值的數值標籤?
如下圖,在一個數列中只有最大值和最小值的數被標示,該如何處理?
Excel-如何在圖表中只顯示數列最大值和最小值標籤?

【設計與解析】
先新增二個輔助欄位:
儲存格C2:=IF(B2=MAX($B$2:$B$17),B2,NA())
儲存格D2:=IF(B2=MIN($B$2:$B$17),B2,NA())
複製儲存格C2:D2,貼至儲存格C2:D17。
接著,選取儲存格B2:D17,新增折線圖並顯示標籤。
Excel-如何在圖表中只顯示數列最大值和最小值標籤?
選取折線圖上的數列標籤:
Excel-如何在圖表中只顯示數列最大值和最小值標籤?
按一下 Delete 鍵,刪除這些標籤。(只會刪除三組數列中的一組)
Excel-如何在圖表中只顯示數列最大值和最小值標籤?
稍加整理圖表,即可得如下樣式。
Excel-如何在圖表中只顯示數列最大值和最小值標籤?
在使用實務上,不要將輔助欄位隱藏,因為最大值和最小值標籤也會被隱藏。若有需要則可以將輔助欄位移出畫面外即可。
Excel-如何在圖表中只顯示數列最大值和最小值標籤?

2017年10月11日 星期三

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

有老師在評量成績時,想要以符號或等第來代替分數,以模糊對分數的斤斤計較。因此,想要以符號來表示學習狀況(如下圖),在 Excel 中可以如何來處理 ?
假設老師要以手動方式(由下拉式清單中選取)來輸入學生的學習狀況(如下圖):
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
可以在儲存格G2:J21中設定格式化的條件:
(1) 規則類型:根據其值格式化所有儲存格
(2) 格式樣式:圖示集
(3) 圖示樣式:如下圖,本例挑選四個圖示
(4) 勾選「只顯示圖示」
(5) 圖示設定分別是:數值>3、數值>2、數值>1、數值<=1
(數值>3對應4、數值>2對應3、數值>1對應2、數值<=1對應1)
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
只要在儲存格中輸入1~4,即可顯示對應的符號。
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
如果你要直接以學生的分數來顯示符號(如下圖):
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
設定如下:
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
如果要以學生的分數位於全部的百分比來顯示符號(如下圖):
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
設定如下:(類型已改為百分比)
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)
在 Excel 中還有其他符號可以選,但其圖示集,同一類型最大只有五種變化。
Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

檢視其他文章

好康東東