2013年8月27日 星期二

Excel-標記公式中參照的儲存格尚未輸入資料

有同仁問到:當一個公式中參照到多個儲存格,如果某些儲存格尚未輸入資料,是否可以自動標記出來當做是一種提醒?

參考下圖:儲存格F2:=B2&C2&D2,即儲存格F2參照到儲存格B2、儲存格C2、儲存格D2。

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

由圖可知有幾個儲存格顯示了標記,指出公式中參照的儲存格尚未輸入資料。

這個效果主要是利用設定 Excel 的錯誤檢查規則而來,你只要選取[檔案]功能表中的「選項」,再勾選[公式]頁面之下的「參照到空白儲存格的公式」即可(預設是沒有勾選)。讓 Excel 幫你檢查是否在公式中是否有空白的儲存格。

Word-使用自動校正功能加速文字輸入

大家可能都用過 Word 中(其他 Office 軟體亦適用)的自動校正功能,例如:輸入(c) 會自動轉換成 ©,輸入 adn 會自動更正為 and。

要設定自動校正功能,可以選取[檔案]功能表中的「選項」,在[校訂]頁面下,按一下「自動校正選項」按鈕。

你可以發現 Word 已設定將 (c) 取代為 ©,如果你不想要這個自動功能,則可以在此頁面下取消勾選「自動取代字串」。

我們就要藉助這個自動取代的功能來加快文字輸入的速度。例如:

在[取代]文字方塊中輸入:學前暑

在[成為]文字方塊中輸入:教育部學前及國民教育暑

再按一下[新增]按鈕,即可建立這個自動校正項目。

或是設定將「職稱」換成「國立火星人高中圖書館主任火星人 mars@mail.earth.edut.tw」

利用這個功能可以輸入較少的字,換得較多的文字。每個人的工作環境中,都會有些每天都要輸入的文字,或是冗長、制式的一些文字,不仿用個代碼(文字)來自動取代,省時而且不易出錯。

如果,你輸入的文字不想執行自動校正的工作,則可以立即按一下 Ctrl + Z 鍵(復原),即會取消自動校正的文字而回復取代前的文字了。

如果設定巧妙的話,說不定在辦公文時,只用 1/4 的文字即輸入完所有的文字呢!你也來試試吧!

2013年8月26日 星期一

Excel-使用以顯示值來計算

在 Excel 中提供了很好的工具可以用來計算,而且對於數值的顯示也提供了多樣的格式可以任君選擇,足以應付日常及各種科學、工程上的應用。但是你看到的數值和電腦記憶的數值是有差異的!

觀察一下下圖:如果要計算甲、乙、丙的平均(三者的和再除以3),很常見的會出現無限循環的小數。而 Excel 的有效位數最多是 15 位,所以在最後一個小數點可能會有進位的狀況。

觀察把平均值 X 2 和把平均值 X 3 的結果,很明顯的將三者的平均再 X 3,結果沒有出現任何的小數。因為電腦並不是以顯示的數值來計算。

下圖將小數點減少,平均值 X 2 和平均值 X 3 的結果並不會改變(和上圖對照):

下圖再將小數點減少,平均值 X 2 和平均值 X 3 的結果還是沒有改變(和上圖對照):

以上的結果會讓報表在印出來的時候產生錯誤的結果,如上圖:80.33 X 2 = 160.666666666667,正確應該是 80.33 X 2 = 160.66,多出來了 0.006666666667。

如果你要避免這種狀況,則可以選取「檔案/選項」功能,在「進階」頁面中,勾選其中的「以顯示值為準」:(此設定會影響整個活頁簿,請小心使用!)

Excel 會警告你這樣做將失去精準度:

再來觀察上述的狀況,平均值 X 2 和平均值 X 3 的結果符合用手計算的結果(和上圖對照):

下圖再將小數點減少,平均值 X 2 和平均值 X 3 的結果符合用手計算的結果(和上圖對照):

善用這個技巧可以避免紙本列印的結果和電腦計算的結果產生差異,避免一些困擾!

Excel-產生最近四週的日期並標示顏色

學校同仁有這樣的需求:希望每日打開 Excel 的檔案時,能自動產生以今日為起始的最近四週日期(可能橫跨五週),前兩週以紅色標示,後兩週以藍色標示,該如何處理?

例如:這個功能可以用在圖書館讀者每日借書,借書期限為兩週,續借可以展延兩週,用以讓讀者有何時還書的時間概念。

本例之今天日期為:08/26。

 

【產生日期】

儲存格A2:=TODAY()-WEEKDAY(TODAY(),1)+COLUMN(A:A)

WEEKDAY 函數可以傳回一個星期幾的數值,本例使用參數 1,表示 1 (星期日)到 7 (星期六)。其他參數參考下圖:

image

複製儲存格A2,貼至儲存格A2:G2。

儲存格A3:=A2+7

複製儲存格A3,貼至儲存格A3:G6。

此工作表可因每日不同日期,而自動產生最近四週的日期。

 

【標示顏色】

選取儲存格A2:G6,設定文字前景色彩:灰色。

接著設定格式化條件:

新增一個規則,選取「使用公式來決定要格式化哪些儲存格」:

設定公式:=(A2>=TODAY())*(A2<TODAY()+14)  [找出距今天最近的1~14天]

設定格式:紅色粗體。

新增一個規則,選取「使用公式來決定要格式化哪些儲存格」:

設定公式:=(A2>=TODAY()+14)*(A2<TODAY()+28)  [找出距今天最近的15~28天]

設定格式:藍色粗體。

2013年8月25日 星期日

Excel-執行多個條件的查表(SUMPRODUCT)

有網友留言問到:在 Excel 中,如果要在一個含有「寬、高、深、單價」的資料表中,想要以輸入「寬、高、深」來求得單價(參考下圖),該如何處理?

【準備工作】

選取儲存格A1:D14,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:寬、高、深、單價。

 

【輸入公式】

因為「寬、高、深」為三個條件,所以使用 SUMPRODUCT 函數,來執行「查表」的動作。

儲存格G5:=SUMPRODUCT((寬=G1)*(高=G2)*(深=G3),單價)

如果查表的結果沒有任何符合的單價,則會顯示「0」。

 

【補充資料】

相關函數的詳細說明,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

2013年8月24日 星期六

Google街景服務新增多個動物園上線

Google 的街景服務不僅上山下海,拍街景、拍建築,現在也可以在家逛動物園了!

連線到街景服務的首頁:https://www.google.com/maps/views/streetview?gl=us

即可進入動物園首頁:

包含了多個國家的動物園和野生動物公園,連中國四川的熊貓基地也可以瀏覽了(剛好最近台灣也在吹熊貓熱)。

世界各國多多少少都有一些動物園或是野生動物公園,每次出國也不見得會去動物園,像上次造訪新加坡,就只有去鳥園,沒有去動物園,所以就線上來瞧瞧囉!

使用右下角的圖示,可以快速切換動物園,並且進入自動展示:

以下這個動物園在那裡呢?是否有些眼熟?這是台灣高雄的「壽山動物園」!也被搜羅在 Google 街景服務的動物園中了。

而且有二個場景可以進入喔!

2013年8月23日 星期五

Google Keep新增時間和地點的提醒

記得,不久前 Evernote 才在記事中加入了「提醒」的功能,而 Google Keep (https://drive.google.com/keep/)在最近的更新中,也加入了時間和地點的提醒功能,這是忙碌的現代人很需要的功能。在記事中不只記錄要辦什麼事,而且在事前就能加以提醒。

在 Google Keep 網頁中建立一個記事後,可以加入要提醒的日期和時間,按一下「提醒我」。

也可以設定一個位置,當你進入該區域時,它就會提醒你:

當然大多數狀況是用手機來提醒的,所以進入手機的 Google Keep 中,你所建立的記事已經進來了。按一下「时間提醒」即可選取:時間提醒和地點提醒。

 

按一下「今天、明天、下週五」或是「選擇日期」,即可選取想要的日期:

 

按一下「上午、下午、傍晚、晚上」或是「選擇時間」,即可設定想要的時間:

 

Google Keep 和 Evernote 有很大的差異,雖然本質上二者都可以讓你做為記事工具,Google Keep 提供很簡單的記事操作,不像 Evernote 可以讓你做到複雜的記事結構及記事內容,Google Keep 可以讓你很方便、很實用的使用記事。二者各有優點,端看你的需求。

2013年8月17日 星期六

善用Dropbox同步備份及版本管理功能

學校同仁問到一個常常會發生的狀況:

經驗上在最近的幾天會編輯同一個檔案(文件),內容常常改來改去(有時同一個長官改了幾個版本後,又改回原來的內容!),因為檔案存檔後會蓋掉之前的版本,所以同一個檔案會另存新檔很多次(例如加上以日期或流水號來編號或是某人修改等字眼)。一個檔案會變成十幾個檔案,當要結案時,真不知這些過程中的檔案,到底要不要刪掉呢?會不會那個時候又會用到呢?

這好像是電腦文書作業中每個人都會有的困擾(如果你是老板可能就不會吧!),似乎也沒有特定的好方法或工具可以來解決。不過現在如果你把檔案送到雲端上,倒是可以藉助雲端的檔案版本管理功能,來幫你保存多個版本的檔案。

以 Dropbox 為例(https://www.dropbox.com),先來觀察一下 Dropbox 的檔案版本管理功能。

先在電腦中開啟 Dropbox 的同步軟體,在修改了 Dropbox 資料夾中的一個檔案並且存檔之後,Dropbox 同步軟體會立刻同步到 Dropbxo 雲端中。

開啟瀏覽器進入 Dropbox 雲端空間,在先前修改後的檔案上按一下右鍵,選取「之前的版本」:

你可以清楚的看到不同時間更改的版本,並且可以看到那一部機器和作者所更改,以及檔案的大小:

你可選取一個之前的版本,按一下視窗右下角的「還原」按鈕:

還原後的檔案會變成一個新的版本,也就是說之前的版本仍是全數被留下來,版本管理的畫面中,你可以知道那些作者編輯或是還原了那些檔案(符號不同):

如果你覺得檔案用不到而刪除檔案,或是不小心把檔案刪除了(有人會自己誤刪了檔案而不自知),你可以在網頁上按一下右鍵,選取「顯示刪除的檔案」:

你可以選取「之前的版本」:

目前狀態為刪除,但是你可以選取之前的一個版本來還原:

你也可以在上上圖的狀態下選取還原檔案(之前編輯的最後一個版本,即上圖的版本5)或是永久刪除(再也救不回來了)。

同仁提到並沒有雲端備份的需求,所以沒有申請/使用 Dropbox 雲端硬碟,或是認為 Dropbox 只提供 2GB 的容量或許太小了。

但是,如果是針對要使用上述功能,不妨可以拿來當一個「檔案管理工具」使用。在編輯最近需要的某個檔案時,先把檔案放在 Dropbox 資料夾中,啟用同步功能,待完成最終編輯後再將檔案刪除,編輯過程中和刪除後的檔案都會被保留。你也來試試吧!

2013年8月16日 星期五

擷取PowerPoint簡報中多張投影片中的文字

今天同仁要我協助將一份簡報中的全部投影片裡文字全部擷取出來(參考下圖),因為在這麼多張投影片中以複製(Ctrl+C)和貼上(Ctrl+V)方式,也要花掉不少時間,而且選取文字也要花掉一些時間,有什麼好方法呢?

image

將 PowerPoint 切換至大綱模式,可以發現裡面一個文字也沒有,因為其投影片中的每的文字都放在圖文框中,否則複製大綱中的文字也是方法之一。還有別的方法嗎?

試著將其另存新檔為 PDF 檔,可是每次選取文字時,只能以單張投影片為選取範圍,無法一次選取多張投影中的文字。該作法也是失敗!

其實,你只要將其另存為「XPS文件」格式:

就可以使用滑鼠以拖曳選取文字,或是按一下 Ctrl+A 鍵選取全部文字,再複製到其他文件中使用了!

這類的問題常被問到,要能擅用不同文件格式的特性,即可達自己想要的操作。使用土法煉鋼可能花個20分鐘,用對方法可能只要2分鐘。

2013年8月15日 星期四

Google-在Google+中用關鍵字搜尋照片

如果你的相簿中堆滿了相片,你會如何來找尋某一張照片呢?依時間、依地點、依人物都得依你存放的概念和結構而定。我想你可能最想用「關鍵字」來搜尋!但是照片又是字,如何依關鍵字來搜尋呢?

先前的文章(http://isvincent.pixnet.net/blog/post/39155219)中已提到在 Google Drive 已可以搜尋圖片中的內容,現在 Google+ 也在其相片中,讓你可以透過關鍵字來搜尋相關的概念、物體、主題等。

不過目前仍無法使用中文關鍵字來搜尋,那就用英文來試試吧!我的相簿中其實照片並不多,不過還真能找到一些照片,以下提供一些例子供參考:

搜尋:「flower」

搜尋:「mountain」

搜尋:「sky」

搜尋:「map」

搜尋:「beach」

搜尋:「taipei 101」或是「台北 101」

你也可以來玩玩自己的相片,看能找到多少!

檢視其他文章

好康東東