2013年10月31日 星期四

Excel-設定儲存格底色呈現統計圖表(SUMPRODUCT)

在 Excel 中取得一個含有日期時間的資料表(參考下圖左),例如:網路連線的 log 資料等,如何將這些資料依星期和時間形成矩陣統計結果,根據這個統計表設定儲存格底色呈現統計圖表(參考下圖右)?這個圖表可以用來呈現每個時段的網路流量大小。

例如在下圖中是一個月中搜集到的網路流量資訊,其中記錄了日期、時間和資料,所以要根據日期資料區隔出週日、週一、…、週六,根據時間資料區隔出 0 時至 23 時,並分別統計在這些時段中的數量,再依據這些數量,讓數據大者呈現較深的儲存格底色,數據小者呈現較淺的儲存格底色。

【準備工作】

選取A欄和B欄中含有資料的儲存格,按一下 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:日期、時間。

【輸入公式】

儲存格F2:=SUMPRODUCT((WEEKDAY(日期,1)=COLUMN(A:A))*(HOUR(時間)=ROW(1:1)-1))

WEEKDAY(日期,1)=COLUMN(A:A):在日期陣列中透過 WEEKDAY 函數找出星期日的陣列,其中參數 1,表示傳回數字 1 為星期日、…、數字 7 為星期六。

COLUMN(A:A)=1,當往右複製時會產生 COLUMN(B:B)=2、COLUMN(C:C)=3、…。

HOUR(時間)=ROW(1:1)-1:透過 HOUR 函數找出時間陣列中時數形成的陣列。

ROW(1:1)=1,當往下複製時會產生 ROW(2:2)=2、ROW(3:3)=3、…。

將以上二式,透過 SUMPRODUCT 函數即可取得該時段中的個數。

複製儲存格F2,貼至儲存格F2:L25。

 

接著,要將顯示的數字隱藏起來!

選取儲存格F2:L25,設定儲存格格式,自訂數值格式為「;;;」(三個分號),儲存格內容即會不顯示出來,也就是被隱藏了。

接著,要來設定儲存格的底色。

選取儲存格F2:L25,選取[常用/樣式]功能表中的「設定格式化的條件」項下的「色階/其他規則」。接著設定規則:

在[格式樣式]中選取「三色色階」,在[中間點]項下的值設定為「50」(或自訂其他數值),最後修改最小值、中間值、最大值的色彩,最好是同一色系由淺至深,如下圖。

如此,便可大功告成,數值較大的儲存格顯示較深的色彩,數值較小的儲存格顯示較淺的色彩。這樣的圖表遠勝過原來數字呈現的圖表,馬上可以反應那些時段是網路流量較大/較小的時候。

真是一圖(表)勝萬字!

2013年10月30日 星期三

Excel-讓使用者輸入密碼才能編輯某些儲存格

Excel 的工作表可能會遇到某些儲存格只想讓特定的使用者編輯,大家想到的都是以「保護工作表」的方式來進行。Excel 提供了設定「允許使用者編輯範圍」的功能,讓你可以設定不同使用者可以使用的儲存格。

例如,在下圖中,如果要讓儲存格B2:C3在保護工作表的狀態下能讓輸入正確密碼的使用者可以輸入資料。做法如下:

選取[校閱/變更]功能表中的「允許使用者編輯範圍」:

按一下[新範圍]按鈕:

輸入標題名稱,並在參照儲存格中輸入或是選取儲存格範圍,例如:=B2:C3。輸入範圍密碼後,按一下[確定]按鈕。

再次輸入密碼以確認:

這個儲存格範圍在使用時必須輸入密碼才能編輯:

按一下[保護工作表]按鈕,選取使用者可以執行的工作,讓工作表進入保護的狀態。

在保護工作表的狀態下,使用者要輸入密碼才能編輯指定的儲存格範圍。

如果你要設定二個不連續的儲存格範圍使用同一密碼,則可以在設定儲存格參照時,使用「,」或是按著 Ctrl 鍵,再選取儲存格範圍。例如:=$B$2:$C$3,$B$6:$C$7。

你也可以設定兩個範圍,並且使用不同的密碼,這樣可以讓不同使用者只能使用有密碼的那個範圍。

如果,在下圖中:你要讓使用者 A 可以編輯 A 區,而使用者 B 可以編輯 B 區,但其中有重疊區域(儲存格F5:F6)是 A、B 兩者都可以輸入的,該怎麼處理呢?

目前個人還未想到如何處理?歡迎有解決方案的網友能夠不吝指導囉!

2013年10月28日 星期一

Excel-動態計算移動平均(OFFSET)

在 Excel 中計算平均值時,有時會用到移動平均,以下圖為例計算:1~5、2~6、3~7、…,每5個的平均。如果配合微調按鈕,如何像下圖一樣,產生一個動態的移動平均結果呢?

微調按鈕控制項的格式設定如下:

儲存格C2:=IF(COUNT($B$2:B2)<$F$1,"",AVERAGE(OFFSET($B$2,ROW(1:1)-$F$1,,$F$1,)))

IF(COUNT($B$2:B2)<$F$1,"",XXXX):由儲存格B2起算,儲存格個數小於分組數者顯示空白。(不到分組數量時,不計算平均。)

OFFSET($B$2,ROW(1:1)-$F$1,,$F$1,):往回推算分組數儲存格所構成的儲存格範圍。產生例如下圖中的儲存格B2:B6、儲存格B3:B7、儲存格B4:B8、儲存格B5:B9。

複製儲存格C2,往下各列貼上。

 

 

只要使用微調按鈕,即可不斷改變移動平均的分組數和結果。

2013年10月27日 星期日

刪除Windows Update已安裝的更新副本以釋放可用空間

如果你有定期執行 Windows Update 的好習慣,可以讓系統保持在最穩定和安全的基本狀態。但這些更新的動作也會耗掉一些磁碟空間,日子一久也會累積不少的浪費。經過前陣子的 Windows Update 之後,微軟已提供可以清除這些 Windows Update 已安裝的更新副本的工具,可以釋放更多的可用空間。

你可以由控制台中點選「系統及安全性」:

再點選「釋放磁碟空間」:

點選一個想要清理的磁碟機:

經過一段時間的掃描過程:

隨後會列出可以刪除的檔案,其中有一項:Windows Update 清理,通常這些檔案都累積了滿大的空間,當你勾選這個項目來刪除這些檔案,而這些都是不會再用到的檔案,將可以大大釋放出可用空間。

2013年10月26日 星期六

Excel/Word/PowerPoint 2013-關閉啟動時的開始畫面

有人問到:用了 Word 2013、Excel 2013、PowerPoint 2013 也有一陣子了,還是很懷念一啟動程式時能直接進入一個空白文件,因為大多數的使用情況都是要新增一個空白文件,2013 版的開始畫面,讓人又要多一個步驟才能開始編輯。該如何取消這個開始畫面呢?

Word 2013 的啟動畫面:

Excel 2013 的啟動畫面:

這些開始畫面讓你可以 快速取用範本和最近使用的檔案(含釘選的檔案),也可以開啟其他舊檔,還有搜尋線上範本的功能。只是有人要不需要這麼複雜的啟動過程!

其實,你只要在 Word/Excel/PowerPoint 2013 的[檔案/選項]中的[一般]標籤下,取消勾選「這個應用程式啟動時顯示開始畫面」:

爾後,只要啟動 Excel 2013、Word 2013、PowerPoint 2013,都可以直接新增一個未命名的文件。

Excel-製作階梯圖

在 Excel 中有一個資料表(如下圖左),如果想要製作成如下圖右的階梯圖,該如何處理呢?因為 Excel 提供的圖表中沒有這類的圖表類型,所以要稍做一些變化才能產生。

要製作這樣的階梯圖必須先將資料轉換成下圖右的資料型式(注意儲存格色彩):

儲存格D2:=INT((ROW(1:1))/3)

產生 0, 0, 1, 1, 1, 2, 2, 2, … 數列。

儲存格E2:=OFFSET($B$2,INT((ROW(1:1)-1)/3),)

依據B欄的儲存格內容,依序重覆顯示三個。INT((ROW(1:1)-1)/3) 向下複製時,會產生 0,0,0,1,1,1,2, …,在複製公式之後,即可在 OFFSET 函數中即可將一個儲存格複製三次。

複製儲存格D2:E2,貼至儲存格D2:E31。

選取這個新的資料範圍,選取[插入/圖表]中的「帶有直線的XY散佈圖」:

稍加修改座標軸格式,即可產生如下的階梯圖:

2013年10月25日 星期五

Excel-計算分組小計(SUMPRODUCT,VALUE,LEFT,RIGHT,LEN)

在 Excel 中,有人取得下圖中的資料,是由一個分組編碼和一個數字組合而成,如果想要在一個儲存格中計算各個分組的小計,而不需要使用輔助欄位來分離資料中的編碼和數字,再予以加總,該如何處理?

【準備工作】

選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

 

【輸入公式】

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*VALUE(RIGHT(資料,LEN(資料)-1)))

LEFT(資料,1)=C2:取出資料陣列中每個儲存格的第一個字元,並判斷是否與儲存格C2相同,得到一個 True/False 陣列。

RIGHT(資料,LEN(資料)-1)):取出資料陣列中每個儲存格除了第一個字的其他字元。

VALUE(RIGHT(資料,LEN(資料)-1)):利用 VALUE 函數將上述字元(字串)轉換成數字。

在 SUMPRODUCT 函數中使用「*」運算,將上述的 True/False 陣列轉換為 1/0 陣列。

複製儲存格D2,貼至儲存格D2:D5。

Excel-計算多個數字的小數總和(SUMPRODUCT,陣列)

有人問到:在 Excel 工作表中有一組含有小數的數值清單,如何在一個儲存格中計算這些數值清單中所有小數部分的總和呢?

你可能會利用一個輔助欄位將每個數值的小數取出,再予以加總得到結果。今天要練習,只要在一個儲存格中利用一個公式來獲得結果,當然,要藉助「陣列」的概念來運算。參考下圖。

 

【準備工作】

選取儲存格A1:A24,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。

 

【輸入公式】

(1) 整數的總和

儲存格C2:=SUMPRODUCT(INT(數值))

以儲存格A2為例:其中 INT 函數可以取不大於儲存格數值的最大整數。

透過 SUMPRODUCT 函數將所有整數加總即為總和。

 

(2) 小數的總和

儲存格C5:=SUMPRODUCT(數值-INT(數值))

以儲存格A2為例:要抓出儲存格A2中的小數部分可執行公式:=A2-INT(A2)。

數值-INT(數值):可以將數值陣列中的所有小數取出,成為小數形成的陣列。

透過 SUMPRODUCT 函數將所有小數加總即為總和。

 

(3) 四捨五入取整數的總和

儲存格C8:=SUMPRODUCT(ROUND(數值,0))

透過 ROUND 函數,其中的參數 0,可以取得 0 個小數位數,即取得四捨五入至整數。

 

【延伸練習】

如果改以陣列公式來輸入:

儲存格C2:{=SUM(INT(數值))}

儲存格C5:{=SUM(數值-INT(數值))}

儲存格C8:{=SUM(ROUND(數值,0))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

2013年10月23日 星期三

Google-在Gmail和Docs中新增手寫輸入功能

先前已介紹過在 Google 的 Gmail 和 Docs 中可以使用線上輸入工具,不過你得先啟動這個小工具。要在 Gmail 中啟動輸入工具,請參考文章:Gmail-使用雲端輸入工具

而 Google 最近在線上輸入工具中新增了多個國家的手寫工具,你只要啟用並選取手寫的輸入工具(筆型符號),即可使用它。不管會不會唸,不論會不會 key 字,只要會寫就能輸入。(須在 Gmail 的設定中的語言部分,先啟用輸入工具。)

如果你無法用鍵盤來輸入日文,但是你可以使用滑鼠以寫的方式來描繪日文字:

以中文字而言,踫到不會唸的字,例如:囹(音同玲)、圄(音同雨),也是用寫的就可以輕鬆來輸入文字。

2013年10月22日 星期二

Excel-計算儲存格裡最後一個數字之個數(RIGHT,VALUE,SUMPRODUCT)

有人問到如下圖中的代碼欄位,其中的每個代碼都有一個檢查碼(最後一個數字),想要來找出最後一碼是 0 ~ 1 的個數和分別為多少?

【準備工作】

選取A欄中有代碼的儲存格,按一下 Ctrl+Shift+F3 ,勾選「頂端列」,定義名稱:代碼。

【輸入公式】

儲存格D2:=SUMPRODUCT(--(VALUE(RIGHT(代碼,1))=ROW(1:1)-1))

RIGHT(代碼,1):透過 RIGHT 函數取出所有代碼中最右(後)一碼成為一個陣列,目前這個陣列都是數字字串("0”,"1”,…)。

VALUE(RIGHT(代碼,1)):透過 VALUE 函數將上述的數字字串陣列("0”,"1”,…)轉換為數字的陣列(1,2,…)。

ROW(1:1)-1:ROW(1:1)=1,當往下複製時可以產生 ROW(2:2)=2、ROW(3:3)=3、…,再減一可得 0 ~ 9。

最後透過 SUMPRODUCT 函數計算總和,其中「--」運算,乃將公式 VALUE(RIGHT(代碼,1))=ROW(1:1)-1) 所得的 True/False 陣列轉換為 1/0 陣列。

複製儲存格D2,貼至儲存格D2:D11。

2013年10月20日 星期日

Windows 8-企業版更新為Windows 8.1

最近 Windows 8.1 已開放免費更新,一般使用者由 Windows 市集中即可更新,但是企業版的用戶必須得使用安裝光碟才能更新。

執行安裝光碟中的 Setup 程式,安裝程式首先要你取得重要更新,請務必下載並安裝更新:

安裝程式請你選擇要保留的項目,選錯的話可能個人資料會不見了,請三思而選:

確認你要安裝的項目,按一下[安裝]按鈕:

安裝過程需要一段不算短的時間等待,過程中會重新啟動你的電腦:

咖啡差不多喝完了,系統也更新完畢了!

你需要做一些簡易的設定即會進入 Windows 8.1 的主畫面了。過程和之前新安裝 Windows 8 時第一次使用的安裝過程相同。

看到一個沒有什麼作用的 Windows 開始鈕,就知道已級升級至 Windows 8.1 了。

不過你要再次輸入產品金鑰,而且 Windows 8.1 的產品金鑰和 Windows 8 不同,請注意!

關於 Windows 8 輸入金鑰的方式,請參考另一篇文章:

http://isvincent.pixnet.net/blog/post/38306493

整合雲端應用:IFTTT(if this then that)

現在的電腦使用者,或多或少都會使用到一些雲端應用程式,但是這些應用程式之間大多沒有關聯性。當我們把資料放在雲端時,如果能透過整合程式,將雲端上的資料互相交流與應用,則更可以擴大雲端的效果,真正往人的第二顆大腦邁進。

有一個雲端應用 IFTTT 能達到整合應用的效果!所謂的 IFTTT 指的是「if this then that」,意指你可以定義一個 Recipe(食譜),其中包含了 Trigger(觸發條件)和 Action(執行動作),當 Trigger 的條件成立時會執行 Action 的動作。

例如下圖中的這個 Recipe,其中用到 Dropbox 和 Evernote 應用程式,這兩個被稱為:Channel,Channel 必須先經過授權才能使用。

image

而系統會每隔 15 分鐘檢驗你建立的 Recipe,你也可以隨時進行檢驗動作(下圖第 2 個按鈕)。可以隨時停止這個 Recipe(第 1 個按鈕),或是編輯這個 Recipe(第 4 個按鈕)。如果按一下第 3 個按鈕,則可以分享這個 Recipe,別人就可以套用你的做法了(你也可以選用別人的 Recipe):

參考以下的做法:

首先連線網頁:https://ifttt.com/,按下「Join IFTTT」進行註冊:

你必須提供一個 Email 地址,並設定密碼:

接著你要進入上圖所輸入的 Email 帳號中收信,並且按一下 Confirm your account,進行帳號的認證。

進入 IFTTT,我們要來建立一個 Recipe 做為例子:

當(if)在 Dropbox 中(this)指定的一個資料夾中新增一個檔案時,就(then)在 Evernote 中(that)建立一個含有這個檔案的記事本。

先按一下 Create a Recipe,要來建立一個新的 Recipe:

按一下「this」:

點選 Dropbox:

你必須按一下 Activate,進行 Dropbox Channel 的授權:(之後再使用 Dropbox 時不需再次授權)

按下「允許」,以確認授權:

授權完後回到設定的步驟中,這裡列出了 2 個動作,我們選取右邊這個「New photo in your Public folder」:

在此你輸入一個資料夾名稱,例如:evernote。

按下 Create Trigger,即完成「this」的設定。(注意到這個資料夾是設定在 Public 資料夾之下)

接著,按一下 that:

選取 Evernote 這個 Channel,並且仿上述步驟獲得 Evernote 的授權:

這裡列出了 5 種動作,本例中選取:Create Image note from URL

在這個接下來的步驟中,你可以為自動建立的記事設定一些自動化的標題和內文等。

目前已有內建了一些參數,按一下「+」,可以自訂/新增參數:

最後,按一下網頁最下方的 Create Action:

接著在最後一個步驟中按一下 Create Recipe,完成新增一個 Recipe 的設定。

當在 Dropbox 中的 Public/evernote 資料夾中新增一個檔案時(本例為 001.png):

一段時間後檢視 Evernote 中的記事,會發現已自動新增了一個記事。

IFTTT 提供的應用程式很多,如果你在第一頁找不到你要的 Channel,可以先按一下 View all Channels,會顯示更多 Channel:

尚未授權的 Channel 以灰色呈現,例如 Gmail:

你必須按一下 Activate,啟用 Gmail Channel,輸入Gmail 的帳號和密碼:

image

按一下「允許存取」,以取得進行第三方認證的授權:

現在會列出有許多人使用的 Recipe 供選取,在網頁的下半部按一下 Browse more Recipes:

你可以看到一些這個 Channel 的 Recipe 清單,或是在 Search 對話框中搜尋你要的 Recipe:

各種 Channel 的動作加以排列組合,透過 IFTTT 來將各種雲端資料加以結合,創造 1+1 > 2 的效果。自動化的效果,也可以讓你更有效率的來操作雲端應用程式。

網路上已有很多關於 IFTTT 的應用,不妨自行 Google 一下,而 IFTTT 中也提供了很多人分享出來的 Recipe,你也可以自己創造並且分享給別人使用喔!

好康東東