2018年3月25日 星期日

Excel-根據時間中的分決定是否進位(CHOOSE,INT,HOUR)

在 Excel 中有一個關於時間的清單(參考下圖左),如何根據以下規則調整(參考下圖右)?
當「分」為:
若為00~14者要捨去
若為15~29要變為30
若為30~44者要變為30
若為45~59者要進位至小時
Excel-根據時間中的分決定是否進位(CHOOSE,INT,HOUR)

【公式設計與解析】
儲存格C2:=CHOOSE(INT(MINUTE(A2)/15)+1,TIME(HOUR(A2),0,0),
TIME(HOUR(A2),30,0),TIME(HOUR(A2),30,0),TIME(HOUR(A2),60,0))
複製儲存格C2,貼至儲存格C2:C17。
(1) INT(MINUTE(A2)/15)+1
利用 MINUTE 函數取出儲存格A2中的「分」數,再利用 INT 函數求取儲存格除以 15 的商數,其結果可能為 0, 1, 2, 3。因為要使用在 CHOOSE 函數中,所以將公式再加 1,使結果可能為 1, 2, 3, 4。
(2) 根據第(1)式的傳回值對應執行以下的公式:
傳回1→TIME(HOUR(A2),0,0)
傳回2→TIME(HOUR(A2),30,0)
傳回3→TIME(HOUR(A2),30,0)
傳回4→TIME(HOUR(A2),60,0)

2018年3月22日 星期四

Excel-在表格中查詢符合者的平均(SUMPRODUCT)

參考下圖在 Excel 中的資料表,如何計算每個人員的平均效能?
下圖中,上圖部分僅依效能欄位來平均,下圖部分另加入參考時數部分。
Excel-在表格中查詢符合者的平均(SUMPRODUCT)

【公式設計與解析】
1. 依效能欄位計算平均效能
儲存格J2:=SUMPRODUCT(($B$2:$E$8=I2)*$F$2:$F$8)/
SUMPRODUCT(($B$2:$E$8=I2)*1)
(1) SUMPRODUCT(($B$2:$E$8=I2)*$F$2:$F$8)
利用 SUMPRODUCT 函數計算全部項目中有包含儲存格I2之人員的效能總和。
(2) SUMPRODUCT(($B$2:$E$8=I2)*1)
利用 SUMPRODUCT 函數計算全部項目中有包含儲存格I2之人員的效能個數。
計算第(1)式/第(2)式,即為所求。

2. 依效能和時數計算平均效能
假設要加入時數的因素。
儲存格J14:=SUMPRODUCT(($B$2:$E$8=I14)*$F$2:$F$8*$G$14:$G$20)/
SUMPRODUCT(($B$2:$E$8=I14)*$G$14:$G$20)
(1) RODUCT(($B$2:$E$8=I14)*$F$2:$F$8*$G$14:$G$20)
利用 SUMPRODUCT 函數計算全部項目中有包含儲存格I2之人員的效能和時數的總和。
(2) RODUCT(($B$2:$E$8=I14)*$G$14:$G$20)
利用 SUMPRODUCT 函數計算全部項目中有包含儲存格I2之人員的效能個數。

2018年3月20日 星期二

Excel-模擬程式語言多選一的Select...Case邏輯(CHOOSE)

如果你在 Excel 的工作表中要實現 Select … Case 多選一的程式邏輯,該如何處理?(參考下圖)
Excel-模擬程式語言多選一的Select…Case邏輯(CHOOSE)

【程式】
Dim k, sum As Integer
sum = 100
k = InputBox("請輸入一個數字")
Select Case k
  Case 1 : sum = sum + k
  Case 2 : sum = sum - k
  Case 3 : sum = sum * k
  Case 4 : sum = sum / k
End Select
Debug.Print(sum)
在 Excel 中可以透過 CHOOSE 函數來執行多選一的邏輯設計:
儲存格E2:=CHOOSE(B3,D3+B3,D3-B3,D3*B3,D3/B3)
儲存格B3(=k)是輸入的數字;儲存格D3(=sum)是預設值。
儲存格F3為 sum 的運算結果。

2018年3月19日 星期一

Excel-模擬程式語言的廻圈運算(SUMPRODUCT,MOD,ROW)

近來,大家都在討論運算思維!今天上課的學生約略有程式設計的概念,所以用以下的例子來和 Excel 建立關聯。

【範例一】
參考下圖,若要計算1~100的總和,可以善用 Excel 試算的特性,於儲存格B3建立公式:=B2+A3,再複製儲存格B3,貼至儲存格B3:B101。(共100個)
其最後結果,儲存格B101的內容為5050,即為1+2+3+ … + 100的總和。
Excel-模擬程式語言的廻圈運算(SUMPRODUCT,MOD,ROW)
如果,你是使用程式來運算,程式範例如下:
Dim sum As Integer
For i = 1 To 100
 Sum = Sum + i
Next
Debug.Print(Sum)
也可以使用陣列公式:
公式:{=SUM(ROW(1:100))}
輸入完成要Ctrl+Shift+Enter鍵,Excel 會自動加「{}」。
其中ROW(1:100)在陣列公式中代表ROW(1:1)=1、ROW(2:2)=2、...、ROW(100:100)=100。
或是使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT(ROW(1:100))

【範例二】
如果改成要計算1+3+…+99(奇數和),該如何處理?
Excel-模擬程式語言的廻圈運算(SUMPRODUCT,MOD,ROW)
程式範例如下:
Dim sum As Integer
For i = 1 To 99 Step 2
 Sum = Sum + i
Next
Debug.Print(Sum)
也可以使用陣列公式:
公式:{=SUM(ROW(1:99)*(MOD(ROW(1:99),2)))}
輸入完成要Ctrl+Shift+Enter鍵,Excel 會自動加「{}」。
MOD(ROW(1:99),2):如果除以 2 的餘數為 0,表示為偶數;如果除以 2 的餘數為 1,表示為奇數。在此 0/1 分別代表 FALSE/TRUE
ROW(1:99)*(MOD(ROW(1:99),2))=ROW(1:99)*(FALSE/TRUE陣列),其結果只會留下奇數部分,再透過 SUM 函數予以加總。
或是使用 SUMPRODUCT 函數:
公式:=SUMPRODUCT(ROW(1:99)*(MOD(ROW(1:99),2)))

【範例三】
如果改成要計算1+4+…+100(間隔3的數之和),該如何處理?
(請自行練習)
Excel-模擬程式語言的廻圈運算(SUMPRODUCT,MOD,ROW)
公式:{=SUM(ROW(1:100)*(MOD(ROW(1:100),3)=1))}
公式:=SUMPRODUCT(ROW(1:100)*(MOD(ROW(1:100),3)=1))

2018年3月18日 星期日

Excel-根據時間清單計算各個時段有多少個(SUMPRODUCT)

在 Excel 裡,如何在一個時間清單中,計算各個時段包含多少個?(參考下圖)
Excel-根據時間清單計算各個時段有多少個(SUMPRODUCT)

【公試設計與解析】
儲存格D2:=SUMPRODUCT((HOUR(時間)=C2)*1)
複製儲存格D2,貼至儲存格D2:D25。
HOUR(時間)=C2:在 SUMPRODUCT 函數中,利用 HOUR 函數取得時間陣列中的每一個時數。再判斷時數陣列是否和儲存格C2內容相同,若是,傳回 TRUE,若否,則傳回 FALSE。其中「*1」運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。SUMRPODUT 函數會將所有的「1」加總,結果即為出現的次數。

2018年3月16日 星期五

收錄部落格文章內容並且產生PDF檔製作為講義(Evernote)

教師常會搜集資料來備課,如果要將網頁上的內容製作成講義,通常會希望內容聚焦在文章主體,而其他元素(例如:網站名稱、廣告和其他外掛等)最好能被過濾掉。(參考下圖)
收錄部落格文章內容並且產生PDF檔製作為講義(Evernote)
你可以利用 Chrome 上的擴充程式來處理成為PDF檔。我的方式是利用 Chrome 結合Evernote 的擴充程式,選擇「簡化文章」,再儲存至自己的記事本。
收錄部落格文章內容並且產生PDF檔製作為講義(Evernote)
回到電腦中的 Evernote 裡同步相關記事,選取這篇記事,再選取「檔案/列印預覽」選項。(或是選取直接另存為 PDF)
收錄部落格文章內容並且產生PDF檔製作為講義(Evernote)
Evernote 會自動產生該文章的 PDF 檔。(參考下圖)
收錄部落格文章內容並且產生PDF檔製作為講義(Evernote)

Excel-根據成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)

(練習題)最近又有一項大考放榜了!學校取得考試成績後,如何利用 Excel 來稍微分析(例如:計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標等),以利後續應用?
本篇簡單的做了一個範例來練習。(本例數據內容為模擬數值)
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)

【公式設計與解析】
1. 定義名稱
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
假設原始資料置於儲存格A1:H420。
選取儲存格A1:H420,按 Ctrl+Shift+F3 鍵,勾選「頂端列」。定義名稱:班級、座號、國文、英文、數學、社會、自然、總級分。

2. 計算各班最高分和最低分
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
最高→儲存格K3:{=MAX(IF(班級=$J3,INDIRECT(K$1),""))}
最低→儲存格L3:{=MIN(IF(班級=$J3,INDIRECT(K$1),""))}
以上式是陣列公式,輸入完成要按Ctrl+Shift+Enter鍵,Excel 自動加上「{}」。
複製儲存格K3:L3,貼至儲存格K3:V14。
(1) INDIRECT(K$1)
公式中利用 INDIRECT 將儲存格K1內文字轉換為儲存格範圍(已定義名稱:國文)。
(2) IF(班級=$J3,INDIRECT(K$1),"")
在陣列公式中找出班級陣列和儲存格J3相同者,傳回對應的國文成績。
平均→儲存格K16:=AVERAGE(INDIRECT(K1))
(3) MAX(IF(班級=$J3,INDIRECT(K$1),""))
將第(2)式傳回的國文成績陣列,利用 MAX 函數取其最大值。(可利用 MIN 函數取其最小值)

3. 計算各科之頂標、前標、均標、後標、底標
Excel-根據考試原始成績計算各班最高分和最低分和各科之頂標、前標、均標、後標、底標(INDIRECT,MAX,MIN,SMALL,陣列公式)
頂標→儲存格K20:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*88%)
前標→儲存格K21:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*75%)
均標→儲存格K22:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*50%)
後標→儲存格K23:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*25%)
底標→儲存格K24:=SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*12%)
複製儲存格K20:K24,貼至儲存格M20:M24、O20:O24、Q20:Q24、S20:S24。
(1) COUNT(INDIRECT(K$18))*88%
利用 COUNT 函數計算國文成績的數量,「*88%」乃取總數的第88個。
(2) SMALL(INDIRECT(K$18),COUNT(INDIRECT(K$18))*88%)
利用 SMALL 函數取該科成績最小的第 88 個即為頂標成績。其餘各標做法依此類推。
(如果把 SMALL 函數換為 LARGE 函數,該如何處理?)

2018年3月15日 星期四

Excel-利用BMI建立成年人健康體重對照表(TEXT)

(範例練習)本例要根據國健署提供的成年人健康體重對照表,利用 Excel 建構一個相同的報表。(參考下圖)
Excel-利用BMI建立成年人健康體重對照表(TEXT)
下圖是國健署提供的成年人健康體重對照表:
Excel-利用BMI建立成年人健康體重對照表(TEXT)
下圖是利用 Excel 建構的資料表:
Excel-利用BMI建立成年人健康體重對照表(TEXT)

【公式設計與解析】
BMI的公式為:
image
1. 正常體重範圍 18.5≦BMI<24,儲存格B3:
=TEXT((A3/100)^2*18.5,"??.0")&"-"&TEXT((A3/100)^2*24-0.1,"??.0")
利用 (A3/100)^2*18.5 和 ((A3/100)^2*24-0.1 求得數值範圍。
再利用 TEXT 函數將數值格式設定為:??.0

2. 體重過重範圍 24≦BMI<27,儲存格C3:
=TEXT((A3/100)^2*24,"??.0")&"-"&TEXT((A3/100)^2*27-0.1,"??.0")
計算方式原理和 1. 相同。
3. 肥胖 BMI≧27,儲存格D3:
=TEXT((A3/100)^2*27,"??.0")
計算方式原理和 1. 相同。

如何設定間隔列的儲存格底色?可以藉助設定格式的條件。
Excel-利用BMI建立成年人健康體重對照表(TEXT)
選取儲存格A3:D49,新增格式化的條件:
選取規則類型:使用公式來決定要格式化哪些儲存格。
編輯規則:=MOD(ROW(A3),2)=0
格式:背景色彩設定為淺灰色
Excel-利用BMI建立成年人健康體重對照表(TEXT)

2018年3月14日 星期三

Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)

(教學範例)在文書處理過程中,有很多的動作常會多次重覆,如何能以快速的方式產生想要的結果?除了可以加速作業流程之外,也可以避免因錯誤產生的不一致。
參考以下的做法,要以 F4 鍵和 Ctrl+D 鍵來加快速度。
例如在 Word 文件中,若要在幾個分開的文字上設定為紅色,可以如何處理才會快速?
1. 將「頁首」的字型色彩設定為紅色。
2. 接著選取「頁尾」,按 F4 鍵;再選取「封面」;按 F4 鍵。再選取「文字方塊設計」,按 F4 鍵。
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)
如果要產生上圖中三個箭頭圖案,則可以:
1. 建立第一個箭頭圖案,並選取該圖案。
2. 按 Ctrl +D 鍵,自動複製一個箭頭圖案;再按 Ctrl +D 鍵,再複製另一個箭頭圖案。

例如在 Excel 的工作表中,如何使用 Ctrl+D 鍵?(參考下圖)
1. 在儲存格B2中輸入公式(本例:=A2^2)。
2. 選取儲存格B3:B14,然後按 Ctrl+D 鍵。(結果相當於複製儲存格B2的公式)
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵) Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)
如果想要標示部分儲存格為紅色,可以:(參考下圖)
1. 選取儲存格A4:B4,設定字型色彩為紅色。
2. 選取儲存格A10:B10,按 F4 鍵;選取儲存格A12:B12,按 F4 鍵。
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)
或是建立好一個圖表之後,選取該圖表,再按 Ctrl+D 鍵,複製該圖表,再進行變更圖表類型。如此,也可以加快圖表的產生。
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)

例如在 PowerPoint 中,可以善用 Ctrl+D 鍵快速產生多個相同物件,再利用對齊和上/下層的設定來完成下圖。
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)
如果在此狀態下,要調整部分方塊的色彩,則可以設定好第一個方塊的色彩後,逐一點選想要修改的方塊,再按 F4 鍵即可。
Word,Excel,PowerPoint的重覆操作(F4鍵,Ctrl+D鍵)

2018年3月13日 星期二

Excel-持續計算項目和同日的累計(SUMIF,SUMPRODUCT)

練習:比較 SUMIF 函數和 SUMPRODUCT 函數。
根據下圖,每日會持續輸入多筆資料,如何計算項目的累計和同日的累計?
Excel-持續計算項目和同日的累計(SUMIF,SUMPRODUCT)

【公式設計與解析】
1. 計算項目累計
儲存格D2:=SUMPRODUCT((B$2:B2=B2)*(C$2:C2))
儲存格D2:=SUMIF($B$2:B2,B2,$C$2:C2)
複製儲存格D2,往下各列貼上。每日持續增加資料,持續複製公式。
本例可以比對兩個函數,其中的參數幾乎一致,只是寫法不一樣! SUMPRODUCT 函數中的 B$2:B2=B2 是條件判斷,和 SUMIF 裡的參數 $B$2:B2,B2 有異曲同工之妙。

2. 計算同日累計
儲存格E2:=SUMPRODUCT((A$2:A2=A2)*(C$2:C2))
儲存格E2:=SUMIF($A$2:A2,A2,$C$2:C2)

2018年3月5日 星期一

Excel-使用公式執行多欄位排序(SUMPRODUCT)

在 Excel 中如何設計公式來執行多欄位的排序?
以下圖為例,欄位:國文、英文、數學、社會、自然在總分相同時的比序順序為:國文→數學→英文→自然→社會,要設計公式以得到這樣的結果。(本例假設有 5 個欄位的資料要比序,且每一個資料為 0~100 之間的數值。)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
下圖是利用 RANK 函數在H欄中產生的排序結果:
Excel-使用公式執行多欄位排序(SUMPRODUCT)
下圖是以排序欄位由小至大的排序結果。(先觀察其中排序相同者,目前尚未依指定比序來排序。)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
本例需要一個輔助欄位:
儲存格H3:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
複製儲存格H3,貼至儲存格H3:H27。
排序結果如下圖,排序已依指定的比序:國文→數學→英文→自然→社會,產生不同的排序結果。
公式:=G3+SUMPRODUCT(B3:F3*0.01^$B$1:$F$1)
其中 0.01^$B$1:$F$1 在 SUMPRODUCT會產生:
國文18, 英文19, 數學18, 社會19, 自然19
=93+0.01*18+0.000001*19+0.0001*18+0.0000000001*19+0.00000001*19
=93.1818191919(小)
國文19, 英文18, 數學19, 社會18, 自然19
=93+0.01*19+0.000001*18+0.0001*19+0.0000000001*18+0.00000001*19
=93.1919181918(大)
Excel-使用公式執行多欄位排序(SUMPRODUCT)
輔助欄位和原始資料的對應關係如下圖:
image
本例每筆資料均為0~100之間,所以設計方式如此。如果你的資料是更大的數值,或是比較欄位為更多筆時,該如何處理?先自己想一想。

如何關閉Windows 10的Windows Update功能

最近有一部電腦因為更新到較新版本, 會導致某些功能出錯,所以必須關閉 Windows Update 功能,關閉後果然能順利執行。要如何關閉Windows 10的Windows Update功能?
利用快速鍵:WinKey+I,開啟「設定」視窗,再選取「更新與安全性」。
在「設定」視窗中,點選進入Windows Update,再點選「進階選項」。
如何關閉Windows 10的Windows Update功能
在進階選項中的「暫停更新」設定為「開啟」。(最多35天)
如何關閉Windows 10的Windows Update功能
或是修改「本機群組原則」也可以:
按 WinKey+R 鍵,然後輸入:gpedit.msc,再按下「確定」。(開啟「本機群組原則編輯器」視窗)
接著於視窗左側點選:「電腦設定/系統管理範本/Windows 元件/Windows Update」,再於視窗右側的「設定自動更新」點選兩下。
如何關閉Windows 10的Windows Update功能
選取「已停用」:
如何關閉Windows 10的Windows Update功能
或是停止 Windows Update 服務:
按 WinKey+R 鍵,然後輸入:services.msc,再按下「確定」。(開啟「服務」視窗)
於視窗中點選二下「Windows Update」。
如何關閉Windows 10的Windows Update功能
在「啟動類型」中選取「已停用」,再於服務狀態中按「停止」按鈕。
如何關閉Windows 10的Windows Update功能

檢視其他文章

好康東東