2014年1月26日 星期日

利用Office 2010製作PDF檔時不要產生圖片路徑

大家已經很熟悉如何使用 Office 2010 來製作 PDF 檔,有同事遇到一個現象(參考下圖)!

當你的 Word 檔中的放了一張圖片,再另存新檔製成 PDF 檔後,將滑鼠移至 PDF 上的圖片時,會顯示圖片原始的檔案路徑,這個結果帶來了一些困擾,不知道可不可以不要顯示檔案路徑呢?

跟著以下這樣做:

在另存為 PDF 檔時,選取「選項」:

取消勾選「協助工具的文件結構標籤」:(預設是勾選的)

簡單的操作,即可解決這個小麻煩!

2014年1月23日 星期四

Excel-表格摘要統計(SUMPRODUCT)

有網友問到一個將表格摘要統計的問題。參考下圖左的資料清單,其中有非常多筆記錄,而 ID 資料共有三類,現在,要摘要出下圖右的統計表。通常這類題目都是交給 SUMPRODUCT 函數來處理!

【準備工作】

選取A欄至E欄中含有資料的儲存格,按一下 Ctrl+Shift+F3 鍵,定義名稱:ITEM、ID、SOH、REC、SOLD。

【輸入公式】

儲存格H3:=SUMPRODUCT((ITEM=$G3)*(ID=$H$1)*INDIRECT(H$2))

ITEM=$G3:從 ITEM 資料中找出符合儲存格G3內容者。

ID=$H$1:從 ID 資料中找出符合儲存格H1內容者。

INDIRECT(H$2):將儲存格H2的內容,藉由 INDIRECID=$H$1T 函數轉成位址。例如將 SOH 字串轉成已定義的「名稱」(先前已定義儲存格範圍)。

複製儲存格H3,貼至儲存格H3:J3。

複製儲存格H3:J3,往下各列貼上。

依上述原理處理其他儲存格:

儲存格K3:=SUMPRODUCT((ITEM=$G3)*(ID=$K$1)*INDIRECT(K$2))

儲存格N3:=SUMPRODUCT((ITEM=$G3)*(ID=$N$1)*INDIRECT(N$2))

 

【補充資料】

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

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

2014年1月22日 星期三

Excel-產生間隔重覆數列(MOD,INT)

有網友問到:如果在儲存格A1~A3中填入數字 1,如何使儲存格A4~A6自動填入數字2及儲存格A7~A9自動填入數字3,以此類推至10。

假設在儲存格A1中填入一個起始值,在儲存格A2中輸入公式,再將儲存格A1複製後,貼至下方各列儲存格。

儲存格A2:=A$1+INT(ROW(1:1)/3)

INT(ROW(1:1)/3):ROW(1:1)=1,往下複製後會產生 ROW(2:2)=2、ROW(3:3)=3、…。所以INT(ROW(1:1)/3) 可以產生 0,0,1,1,1,2,2,2,…。

 

【同場加映】

如果想要做到如上圖每三列的儲存格底色有所間隔,要先選取所有含有資料儲存格,設定格式化條件的規則,

1. 選取「使用公式來決定要格式化哪些儲存格」。

2. 然後輸入規則:=MOD(INT((ROW(1:1)-1)/3),2)

上式由第1列開始,會產生 0,0,0,1,1,1,0,0,0,1,1,1, …。0 會被視為 False(不成立),1 會被視為 True(成立)。

2014年1月21日 星期二

宜蘭一日遊:羅東林業文化園區+頭城伯朗咖啡城堡

學校允許同仁可以組隊參加自強活動,趁著本學期最後一天上課,召集了 8 位同仁,共乘二部車,目標是羅東林業文化園區+頭城伯朗咖啡城堡。

一大早由桃園出發,第一站是羅東林業文化園區。星期一上班時間難免遇到上班車流,稍有睹車,不過五號高速公路可是一路順暢。每每由雪山隧道出頭城隧道口的那一瞬間,看到蔚藍的天空和廣濶的平原,都會讓人心中有微微顫抖的感動。老天爺很幫忙,今天是個郊遊的好天氣!

羅東林業文化園區的路線如下:從國道5號的羅東交流道(五結羅東)號出口下交流道,前行繼續走191甲縣道。右轉走196縣道前行,再右轉中正北路前行,抵達羅東文化林業園區(中正北路118號)

一般人對「林場肉羹」的印象遠超過羅東林業文化園區,林場肉羹就在園區的對面。而園區內適合慢遊,尤其是一群人七嘴八舌,以亂七八糟隊型忽左忽右的前進,順便曬曬冬天的太陽,有一點涼、有一點暖,那種感覺就是舒服!(圖中那二位姊妹上演了常在廣告中看到的情景)

二個小時不知不覺的過去了,肚子開始有餓的感覺了!中午用餐地點是蘭城晶英酒店的蘭城百匯自助餐廳。因為是自強活動,所以挑選比較寬敞、安靜的用餐環境。飽餐一頓,又是開心的二小時。

路線如下:走台9線中山北路段,再直行至民權路二段左轉,抵達蘭城晶英酒店(民權路二段36號)

接下來的行程是:頭城伯朗咖啡城堡。在這裡可以遠眺龜山島、外澳沙灘、烏石港,風景極佳,又有咖啡相伴,邊聊是非,不知不覺忘了要回家。

路線:前往頭城伯朗咖啡城堡,走台9線往礁溪至頭城。續走台9線往頭城,過烏石港外澳遊客中心,左轉濱海路二段,轉往石空路至伯朗咖啡城堡

從同仁的參與程度看來,應該是對行程很滿意吧!早上和下午各一個行程,有走動、有休閒,中午也能盡興的用餐。

選擇一個對的地點,和一群對的人,一起做一件對的事,那就不會出錯啦!已經開始規劃了,期待下一攤吧!

2014年1月19日 星期日

Excel-查詢後建立含有特定字串的清單(使用公式)

有網友問到:如果想要在一個資料表中,查詢含有某些特定字串的資料搜集在一個清單中,該如何處理?(原始資料參考下圖左,篩選後清單參考下圖右。)

如果你使用「篩選」工具,在文字篩選中使用「包含」條件:

在條件中選擇「包含」+「電子」:

即可篩選出含有特定字串的資料。

如果你想使用公式來篩選,參考以下的作法:

【準備工作】

選取儲存格A1:B26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、內容。

【輸入公式】

(1) 先求內容(含有:電子)

儲存格E2:{=OFFSET($B$1,SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1))-1,)}

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

FIND("電子",內容)>=1:判斷內容各個儲存格中是否含有「電子」,如果有,會傳回一個數字(位置),如果沒有,則會傳為 #VALUE!(錯誤訊息)。

IF(FIND("電子",內容)>=1,ROW(內容),):如果 FIND 有傳回值(含有「電子」),則顯示該儲存格所有列號,否則傳回空的內容。

IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999):使用 IFERROR 函數處理如果傳回值是 #VALUE! 錯誤訊息時,顯示 9999。這只是一個較大的數即可,必須要大於所有資料的列數。

SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1)):當向下複製時會傳回第 1 小值、第 2 小值、第 3 小值、…。該值為一個列號。

最後透過 OFFSET 函數,代入上式所傳回的列號,即可產生一個動態的儲存格位址,顯示的內容即為所求。

(2) 再求日期(含有:電子)

儲存格D2:{=OFFSET($A$1,SMALL(IFERROR(IF(FIND("電子",內容)>=1,ROW(內容),),9999),ROW(1:1))-1,)}

公式之原理與上式完全相同,只須改變 OFFSET 的起始參考值。

圖中有些顯示 1900/01/00 和 0 的資料,是因為公式已查不到其他對應的資料了。

你也可以試試練習含有「電腦」的篩選清單。

2014年1月15日 星期三

Word-將一段文字放在多個物件中

你是否想過要將一段文字放在數個圖案中,而這些文字是連續的被顯示,參考下圖:

在 Word 中可以輕易的做到!方法如下:

1. 先準備好幾個圖案(設定好可以新增文字)。

2. 選取第一個圖案物件,再選取[格式]功能表中的「文字」區中的「建立連結」。

3. 接著在要接連顯示文字的物件上按一下。

如此,便完成了這兩個物件之間的連結。當第一個物件的文字超過物件所能容納,即會將文字顯示在第二個物件中。而第二個物件,也可以設定下一個物件建立連結,如此,這三個物件即可連結在一起了!

三個文件將可以接連顯示同一段文字內容。如果想要中斷連結關係,則只要選取物件後,點選[格式/文字/中斷連結]即可。

Google圖片搜尋-可過濾有版權的圖片

在製作文件和網頁時,常會用到圖片,而當你在搜尋圖片來用時,很有可能會下載到具有版權的圖片而誤用。Google 圖片搜尋現在提供了「使用權」工具,讓你可以過濾有版權的圖片,顯示授權範圍內可用的圖片。

在搜尋圖片時,使用「搜尋工具/使用權」,再挑選想要的內容:(過濾某些內容)

例如選取「標示為允許以商業用途重複使用」,再和上圖比較一下,有些圖片被過濾掉了:

其使用權的類別,共有五種(如下圖):

要注意:其篩選結果是否準確?有否有無誤用的風險?還是要自行承擔的。

2014年1月14日 星期二

Google-限制Google+上的網友不能寄Email給你

如果你是 Gmail 的用戶,最近應該都有收到這樣的一封信(如下圖),Google 美其名是要拓展你的通信人脈,讓那些沒有你的電子郵件地址的網友,也能寄信給你。這對某些人而言,真的是可以好好拓展人脈了,但對某些人而言或是災難的開始。

而 Google 已經「預設」了你的網友可以透過 Google + 寄信給你了。不過,不管你用的到還是用不到,等你弄清楚你的需求再開放。

如果你想修改這個預設功能,只要到 Gmail 的設定的一般設定中,將哪些人可以透過您的 Google+ 個人資料傳送電子郵件給您,修改設定為「無」即可。

2014年1月13日 星期一

Excel-搜尋多個字串(FIND、SEARCH)

有網友問到:如果要在一個儲存格中搜尋多個字串,該如何判斷是否存在?(參考下圖)

在下圖中分別使用了 FIND 函數和 SEARCH 函數來找尋一個字串和二個字串,來練習如何判斷是否存在要找的關鍵字。

1. 使用 FIND 函數

(1) 儲存格C2:=IFERROR(IF(FIND("感測器",B2),"V",),"X")

FIND("感測器",B2):如果儲存格中存在「感測器」字串,則會傳回該字串所在的位置,否則會傳回「#VALUE!」。如果 FIND 的傳回傳大於 0,表示找到關鍵字,則顯示「V」。

再利用 IFERROR 函數將傳回值「#VALUE!」,顯示為「X」

(2) 儲存格D2:=IFERROR(IF(FIND("網路",B2),"V",),"X")

(3) 儲存格F2:=IFERROR(IF(FIND("Wi-Fi",B2)*FIND("追蹤",B2),"V",),"X")

如果要搜尋二個以上的關鍵字,則使用二個 FIND 函數,再加上運算子「*」,執行 AND 運算,即可判斷是否二個關鍵字字串均存在。

 

2. 使用 SEARCH 函數

SEARCH 函數和 FIND 函數一樣,如果儲存格中存在要搜尋的,則會傳回該字串所在的位置,否則會傳回「#VALUE!」。

(1) 儲存格C3:=IFERROR(IF(SEARCH("感測器",B3),"V",),"X")

(2) 儲存格F3:=IFERROR(IF(SEARCH("Wi-Fi",B3)*SEARCH("追蹤",B3),"V",),"X")

2014年1月11日 星期六

Excel-儲存格位置重組(OFFSET)

有網友問了這樣的一個問題:在 Excel 中有一個如下圖左的資料表,想要將儲存格位置重組成下圖右的資料表,該如何處理?

仔細觀察發現儲存格位置重新排列有其規則性,每一個屬性相同的儲存格都是間隔 3 列。

公式如下:

儲存格G2:=OFFSET($A$2,(ROW(1:1)-1)*3,,,)

ROW(1:1)=1,若往下複製可以產生 ROW(2:2)=2、ROW(3:3)=3、ROW(4:4)=4、…。

(ROW(1:1)-1)*3,若往下複製可以產生 0、3、6、…。

透過 OFFSET 函數即可取得和儲存格A2間隔 0、3、6、… 列的儲存格位置。

儲存格H2:=OFFSET($E$2,(ROW(1:1)-1)*3,,,)

儲存格I2:=OFFSET($E$3,(ROW(1:1)-1)*3,,,)

複製儲存格G2:I2,往下各列貼上。

依此規則,即可將各種表格的內容重組了!

2014年1月10日 星期五

Excel-將較大的數字以科學記號表示

在網路上看到有人在討論一個較大的數字通常在 Excel 中會以指數型式表示,例如:6.37104E+13(參考下圖)。如果想要以科學記號來表示(6.37104 x 10^13)或是完整顯示(63710401047552),該如何處理?

其中輸入的數字以指數型示顯示會預設 7 個有效數字,若將所有數字完整顯示也只能顯示 15 個有效數字。

在A欄的顯示結果,是一般輸入較大的數字預設的顯示方式,如果要改成B欄的型式,而且取 6 個有效數字。

(1) 儲存格B2輸入公式
=LEFT(TEXT(A2,"0.00000E+0"),7) & " x 10^" & RIGHT(TEXT(A2,"0.00000E+0"),2)

TEXT(A2,"0.00000E+0"):將儲存格A2內容自訂格式為 6 個有效數字。

利用 LEFT 函數取出左邊 7 個數字(有效數字),利用 RIGHT 函數取出右邊 2 個數字(指數)。

 

(2) 儲存格C2設定數值格式

在自訂格式中填入「?」或「#」或「0」,都可以完整的顯示所有的數字。

2014年1月9日 星期四

Excel-修正讀卡結果錯誤的成績

有老師遇到這樣的問題:考試後,讀完答案卡後才發現有題目的答案弄錯了,但是讀卡系統中的資料已被刪除,又不想要重新再讀卡一次,該如何修改學生分數呢?還好,讀卡系統產生的成績檔還在,那就可以將分數還原為正確了!

參考下圖:例如第 16,18,20 題的答案為 B,D,C,要更正為 D,B,D。

如果學生原來的答案和原來的標準答案相同,則答案區中會以「=」標記,如果和原來的標準答案不相同,則會顯示學生做答的答案。

(1) 將第 16 題答案由 B 更正為 D

儲存格E3:=IF(MID(C3,16,1)="D",1,IF(MID(C3,16,1)="=",-1,0))

MID(C3,16,1)="D":取出儲存格C3的第16個字,判斷是否為「D」,如果為真,則必須給分1。

IF(MID(C3,16,1)="=",-1,0):如果上述結果為否,則再判斷原答案是否為「=」,代表原答案為正確且被給 1 分,因此要扣回 1 分(-1)。如果不是「=」,則代表學生作答的答案和原來的答案是及更新的正確答案都不相同,表示要給 0 分(不加分也否扣分)。

透過二層 IF 函數得到 1, 0,  -1 其中之一的結果。

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

(2) 將第 18 題答案由 D 更正為 B

儲存格F3:=IF(MID(C3,18,1)="B",1,IF(MID(C3,18,1)="=",-1,0))

(3) 將第 20 題答案由 C 更正為 D

儲存格G3:=IF(MID(C3,20,1)="D",1,IF(MID(C3,20,1)="=",-1,0))

(4) 修正後總成績

儲存格H3:=D3+SUM(E3:G3)

2014年1月8日 星期三

Word/Excel-讓物件不隨文字或儲存格移動

有同仁在利用 Word 或是 Excel 時,常遇到一個困擾:當在 Word 中製作要套印的文件時,會因為輸入不同字數的文字,而導致某些需要固定位置的文字移位,該如何解決這樣的問題呢?

以 Word 2013 為例,參考下圖,假設要讓圖文框(下圖的校長字樣)位置固定:

你只要選取這個圖文框,設定版面配置選項:固定於頁面上的位置,這樣就搞定了。

如果你是其他版本的 Word,則可以在圖文框的版面配置中的[位置]標籤下,取消勾選:物件隨文字一起移動。

如果你在 Excel 中的物件,也不想要讓物件的大小因為調整儲存格大小而跟著改變大小,則可以在物件的格式設定中選取:大小位置不隨儲存格改變。

2014年1月7日 星期二

Excel-計算文字型式的數字(SUMPRODUCT,VALUE,陣列公式)

有朋友問到:如果在一個資料表中的數字是以文字型式或是全型文字來顯示,能夠執行加總的動作嗎?因為有些儲存格是這類的數字,在運算時帶來一些困擾。

例如:參考下圖,A欄:正常的數字,B欄:半型的文字,C欄為全型的文字,D欄和E欄為國字。

(1) 參考第12列,其公式為:=SUM(範圍),例如:=SUM(B2:B11)

如果使用 SUM 函數,則只有數字的欄位能正常計算,其餘文字皆會被視為「0」來運算。

(2) 參考第13列,其公式為:=SUMPRODUCT(範圍*1)

建議改用 SUMPRODUCT 函數來運算,例如:=SUMPRODUCT(B2:B11*1)

公式中的「*1」,乃用於將文字自動轉換為數字,如此便可以運算了。

(3) 參考第14列,其公式為:{=SUM(VALUE(範圍))},例如:{=SUM(VALUE(B2:B11))}

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

透過 VALUE 函數將文字轉換為數字,使用陣列公式可以將一個儲存格範圍內的文字全部轉換為數字。

(4) 因為D欄和E欄的文字無法轉換為數字,所以運算結果都為 #VALUE! 錯誤。

使用Google+ Auto Backup自動備份電腦的相片至Google+

最近更新了 Google 的 Picasa 軟體,發現增加了一個附屬程式:Google+自動備份(Google+ Auto Backup)。如果你已在使用 Google Drive 的同步軟體,則 Google+ Auto Backup 的使用觀念是相同的,只是它是用來備份電腦中的照片/影片,而且是備份至你的 Google+中。

這個應用程式除了備份的功能之外,還可以藉助 Google+ 的相片自動修圖功能讓相片能更好看,而這個程式和 Picasa 軟體是分開處理的。

執行 Google+ Auto Backup,你必須先登入才能使用:

第一次使用時,按一下[設定自動備份]按鈕,來設定那些資料夾的照片/影片要備份至 Google+:

你可以選取要備份的媒體裝置或是指定的資料夾,按一下[新增]按鈕,可以調整想要備份的資料夾。而你也可以選取備份至 Google+ 的相片要使用原來尺寸或是標準尺寸(縮小)。

按一下[進階設定]按鈕,可以選取是否在備份媒體的相片/影片時,也一併複製到電腦中(例如:「我的圖片\Google+ Auto Backup\」資料夾,這個資料夾可自行設定。)

按一下[開始備份]按鈕,隨即會將指定的資料夾中的相片/影片,備份至 Google+ 的相片中。

在工作列的右下角,你可以暫停備份或是修改設定(備份的資料夾等):

除了備份電腦的相片/影片,你的手機是否也設定了將相片自動備份至 Google+,可以參考以下文章:

2014年1月4日 星期六

Excel-更改多個儲存格公式而不變動格式

看到同事的操作:在 Excel 中有一個資料表,如果已經設定好格式,又要修改全部的公式,例如下圖。如果是將儲存格B2的公式修改後再複製到儲存格B2:B20(大家習慣用填滿控制點來執行複製的動作),則每個儲存格的格式都會和儲存格B2相同。也就是說,原來設定好的格式會跑掉了,有那些可行的方式呢?

方法一:

選取儲存格B2:B20,輸入公式,按一下 Ctrl+Enter 鍵。

方法二:

在儲存格B2中修改公式後,複製儲存格B2,選取儲存格B3:B20,按一下右鍵,選取貼上「公式」。

Excel-儲存格複製多份(公式)

有人在 Excel 中想要將某一欄中的儲存格內容重覆複製多個(參考下圖),該如何處理?

儲存格B2:=OFFSET($A$2,INT((ROW(1:1)-1)/COLUMN(B:B)),,)

INT((ROW(1:1)-1):向下複製公式時會產生 INT((ROW(1:1)-1)=0、INT((ROW(2:2)-1)=1、INT((ROW(3:3)-1)=3、…。

INT((ROW(1:1)-1)/COLUMN(B:B):向右複製公式時會產生 INT((ROW(1:1)-1)/2、INT((ROW(1:1)-1)/3、INT((ROW(1:1)-1)/4、…。

參考下圖的結果:

透過 OFFSET 函數,以類似查表方式取得各個儲存格中對照的內容。

複製儲存格B2,貼至想要重覆的儲存格上。

Excel-檢查兩個項目是否相符(排除未輸入項目)

有網友想要:在一個 Excel 資料表中,檢查兩個項目欄位中的內容是否相符,但又要排除尚未輸入項目,不予以評定,該如何處理?(參考下圖)

如果你輸入以下的公式:

儲存格C2:=IF(A2=B2,"V","X")

則會在某一個項目尚未輸入時,顯示「X」,而二個項目都未輸入時,顯示「V」,很明顯的這樣的判定有問題。

儲存格D2:=IF(COUNTIF(A2:B2,"")>0,"",IF(A2=B2,"V","X"))

如果加上 COUNTIF(A2:B2,"")>0 來判別,即兩個項目只要是空白的個數大於 1,即顯示空白(不判定),就可以解決了。

這樣的思維,如果將要判定的項目變為三個時,公式該如何輸入呢?(參考下圖)

儲存格D2:=IF(COUNTIF(A2:C2,A2)=3,"V","X")

儲存格E2:=IF(COUNTIF(A2:C2,"")>0,"",IF(COUNTIF(A2:C2,A2)=3,"V","X"))

上述的公式,即可輕易修改成判定四個項目了,請自行修改囉!

好康東東