2011年1月28日 星期五

Excel-將排版好的資料表貼到部落格文章中

在 Excel 中製作了一個漂亮的表格(如下圖),想要貼到部落格的文章中使用,但是以複製表格的方式,再貼到 Windows Live Write 中,整個格式都會跑掉,只能顯單調的文字,該如何成功的移轉呢?

參考作法:

(1) 選取表格範圍(例如:儲存格A1:D31,可以超過視窗顯示範圍)。

(2) 按一下右鍵,選取[複製]選項。

(3) 按一下右鍵,選取[選擇性貼上]的「複製圖片」按鈕。(該選取範圍內的儲存格已轉成一張圖片)

(4) 貼至 Windows Live Write 中。

(5) 同理,利用文字藝術師設計一個標題字,然後再複製成圖片,並貼到 Windows Live Write 中編輯。

(6) 上傳至部落格,結果如下:

利用此方法,可以讓部落格的內容更為美觀,但是缺點是以圖顯示,別人就無法取得其中的文字,而且會增加儲存格空間。

2011年1月27日 星期四

Excel-利用陣列求最小值

在 Excel 中的一個工作表(如下圖左),如果想要求得業績欄位超過1000的最小值,該如何做?只要利用陣列加上 MIN 函數即可完成。觀察以下的三個公式那一個才是正確的呢?

(1) {=MIN(IF(C2:C25>1000,C2:C25,))}

(2) {=MIN(IF(C2:C25>1000,C2:C25,0))}

(3) {=MIN(IF(C2:C25>1000,C2:C25,""))}

因為(1)和(2)在 C2:C25>1000 條件不滿足時都會給予0,所以最小值都是0。在(3)中,因為 C2:C25>1000 條件不滿足時會給予「""(空字串)」,因其不是數值不會被列入最小值的計算。

此練習可以用在許多陣列的計算上,要小心才不會得到錯誤的結果。

2011年1月26日 星期三

Excel-在SUMPRODUCT函數中執行OR運算

在 Excel 中取得如下圖的資料表:

如果你要計算各領域的篇數,最簡單的做法是使用SUMPRODUCT函數:

儲存格G3:=SUMPRODUCT(--($C$2:$C$137=F3),$D$2:$D$137)

將儲存格G3複製到儲存格G3:G6。其中的「--」是為了將 True/False 陣列轉換為 1/0 陣列。

如果你要在一個儲存格中計算二個領域的篇數,則可以使用兩個 SUMPRODUCT 函數:

SUMPRODUCT(合於條件一的篇數)+SUMPRODUCT(合於條件二的篇數)

此處的「+」為「加」的概念。

你也可以運算 OR 的觀念,在 SUMPRODUCT 函數中使用「+」運算子:

儲存格J3:=SUMPRODUCT(--($A$2:$A$137=I3),--(($C$2:$C$137="文法")+($C$2:$C$137="商管")),$D$2:$D$137)

儲存格K3:=SUMPRODUCT(--($A$2:$A$137=I3),--(($C$2:$C$137="數理")+($C$2:$C$137="工程")),$D$2:$D$137)

千萬不可使用OR(($C$2:$C$137="文法")+($C$2:$C$137="商管")),這是錯誤的方式,會得到 #VALUE! 錯誤訊息。

2011年1月25日 星期二

Outlook-壓縮資料夾

最近看 Outlook 2010 中的郵件 PST 檔,愈來愈大,想要將它壓縮以減小檔案,一時間還找不到呢!

例如,我把郵件分在三個 PST 檔案中以方便管理,如果要壓縮資料,就得做三次。

(1) 在郵件資料檔名稱上按一右鍵,選取[資料檔案屬性]選項。

(2) 在[內容]對話框的[一般]標籤中,按一下[進階]按鈕。

(3) 按一下[開始壓縮]按鈕,就會開始進行壓縮。

壓縮郵件資料檔和封存是不一樣的,封存會將某些郵件(例如:半年以前的郵件等),移至另一個資料檔(封存檔)中,而壓縮郵件則可以使郵件檔的大小變小。比較前後的檔案大小,足足小了4,5百MB。

2011年1月24日 星期一

Excel-基本邏輯運算練習

在 Excel 的工作表中如何製作基本邏輯的真值表和邏輯電路呢?參考下圖:

以實心圓當為True(1)[燈泡會亮],空心圓當為False(0)[燈泡不亮];

利用表單控制項的核取方塊來控制是否通電:

通電時,燈泡會亮代表True(1),沒有通電時,燈泡不亮代表False(0)。

藉由控制核取方塊可以對應真值表的True/False值。

將做法以 AND 邏輯稍作說明:

(1) 選取[開發人員/表單控制項]的「核取方塊」項目,在儲存格中拖曳出一個大小,將其名稱改為「通電」。此為 A 輸入。

(2) 選取這個核取方塊,按一下右鍵,選取[控制項格式]。

(3) 將儲存格連結設定至一個儲存格,例如:$H$2。

即勾選核取方塊時,儲存格H2會顯示「TRUE」,若取消核取方塊時,儲存格H2會顯示「FALSE」。

(4) 將儲存格H2的文字色彩設定成和底色相同,如此相當於將字隱藏不顯示。

(5) 輸入公式,儲存格G3:=IF(H2,"●","○")

即儲存格H2=TRUE(核取方塊被勾選),則顯示實心圓(燈泡亮);反之顯示空心圓(燈泡不亮)。

(6) 利用相同原理製作 B 輸入,將結果放在儲存格H6,將燈泡顯示在儲存格H5。

(7) 輸入公式,儲存格K3:=AND(H2,H6),判斷 Y 的結果。

(8) 將儲存格K3的文字色彩設定成和底色相同,如此相當於將字隱藏不顯示。

(9) 輸入公式,儲存格J4:=IF(K3,"●","○")

即儲存格K3的結果為「TRUE」,則顯示實心圓(燈泡亮);反之顯示空心圓(燈泡不亮)。

(10) 同理,來製作 OR 邏輯:

儲存格K9:=OR(H8,H12)

(11) 同理,來製作 XOR 邏輯:

因為 Excel 並沒提供 XOR 邏輯運算的函數,所以要自行以 NOT、AND、OR 來組成。

儲存格K15:=OR(AND(NOT(H14),H18),AND(H14,NOT(H18)))

其中邏輯符號是以繪圖工具中的圖案組成的,雖畫不出標準的符號,但仍能清楚辨識何種邏輯符號。

這個例子,只是要用來練習實做邏輯運算和使用表單控制項,不一定有實用價值,但拿教初學者,或許還是有用的。

Excel-二列資料轉成一列

有網友參考:Excel-一列資料轉成二列(http://isvincent.blogspot.com/2010/10/excel_22.html),

其問到如果要反過來做,要將二列資料轉成一列時(參考下圖,左資料表轉換成右資料表),該如何處理?

和前一篇文章一樣,只要用到 INDIRECT 和 ADDRESS 函數即可完成:

ADDRESS 函數,可以在已知指定列號和欄號時,取得工作表中儲存格的位址。而 INDIRECT 函數會傳回文字串所指定的參照位址,依該參照位址顯示其內容。

儲存格F2:=INDIRECT(ADDRESS(ROW(1:1)*2,2))

儲存格G2:=INDIRECT(ADDRESS(ROW(1:1)*2,3))

將ROW(1:1)*2,可以取得偶數列。

儲存格H2:=INDIRECT(ADDRESS(ROW(1:1)*2+1,2))

儲存格I2:=INDIRECT(ADDRESS(ROW(1:1)*2+1,3))

將ROW(1:1)*2+1,可以取得奇數列。

儲存格J2:=INDIRECT(ADDRESS(ROW(1:1)*2,1))

將儲存格F2:J2往下複製即可完成。

 

關於 INDIRECT 和 ADDRESS 函數的詳細說明,可參考微軟網:

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

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

2011年1月23日 星期日

Excel-以日期的年和月來統計分析

接續前一篇文章:Excel-將多個工作表資料組合成一個資料表

http://isvincent.blogspot.com/2011/01/excel_22.html

如果要對這個資料表,以發表時的年和月來統計分析發表的篇數,該如何處理?

產生報表:

儲存格G2:=SUMPRODUCT(--(MONTH(INDIRECT("A2:A" & COUNTA($A:$A)))=ROW(1:1)),--(YEAR(INDIRECT("A2:A" & COUNTA($A:$A)))=(COLUMN(A:A)+2007)))

複製儲存格G2到儲存格G2:J13。

因為資料來源會不斷的增加,所以無法以固定的絶對位址(例如:$A2:$A863)表示,因此要藉助 INDIRECT 函數。公式中的 INDIRECT("A2:A" & COUNTA($A:$A)) 表示儲存格A2到A欄最後一個儲存格。

原公式=SUMPRODUCT(--(MONTH(A欄有資料的儲存格範圍))=ROW(1:1)),--(YEAR(A欄有資料的儲存格範圍))=(COLUMN(A:A)+2007)))

因為ROW(1:1)=1向下複製時,會產生ROW(2:2)=2 –> ROW(3:3)=3 –> …,所以可以代表1到12月。

同理COLUMN(A:A)=1向右複製時,會產生COLUMN(B:B)=2 –> COLUMN(C:C)=3 –> …。再加上2007,即可產生2008~2012。

此公式,當資料來源增加或是報表中的年份增加,都只要將公式向下或向左複製即可。

2011年1月22日 星期六

Excel-將多個工作表資料組合成一個資料表

在 PIXNET 部落格的「所有文章列表」功能中,每一頁只顯示了20筆資料(參考下圖)。如果想要將這些資料匯入 Excel 中,並且合併成一個資料表,不用再切換多次才能看到全部內容,該怎麼做呢?

要將網頁內容匯入 Excel 的工作表,相關做法請參考另一篇文章:
從網頁上取得外部資料(http://isvincent.blogspot.com/2011/01/excel_5843.html)

本例先匯入1~12頁的內容來練習,分別放在工作表名稱1~12中。其中「人氣」欄位中的負數是因為匯入資料時將「(1)」轉成「-1」的原因。觀察這些工作表的內容格局都是一致的。

現在要將工作表名稱1~12的資料合併在一個工作表中,並且依序列出,參考下圖。

每個工作表中要取用儲存格A14:C33,而且工作表的名稱為流水號,可以使用INDIRECT函數來簡化公式的撰寫。INDIRECT函數會傳回文字串所指定的參照位址,並顯示其內容。INDIRECT 函數通常是想在公式中改變參照位址卻不想改變公式本身時使用

儲存格A2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!A" & MOD(ROW(2:2)-2,20)+14)

公式中的「INT((ROW(2:2)-2)/20)+1」,在向下複製時,ROW(2:2)=2 –> ROW(3:3)=3 –> …

可以產生 1,1,…,1,2,2…,2,3..的數字,即20個1、20個2、20個3、…。

公式中的「MOD(ROW(2:2)-2,20)」,在向下複製時,ROW(2:2)=2 –> ROW(3:3)=3 –> …

可以產生 1,2,…,20,1,2…,20,.. 的數字

「MOD(ROW(2:2)-2,20)+14」可以產生14,15,…,33,14,15,…,33,.. 的數字。

將儲存格A2往下複製時可以產「1!A14、1!A15、…、1!A33、2!A14、2!A15、…、2!A33、…

同理可以產生:

儲存格B2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!B" & MOD(ROW(2:2)-2,20)+14)

儲存格C2:=INDIRECT("'" & INT((ROW(2:2)-2)/20)+1 & "'!C" & MOD(ROW(2:2)-2,20)+14)*-1

因為來源儲存格為負數,所以C欄的內容要再乘以負1。

儲存格D2:=IFERROR(C2/(TODAY()-A2),0)

因為TODAY()-A2有可能結果為0,所以使用IFERROR函數來避免顯示錯誤訊息。

如果有新增工作表,則只要將儲存格往下複製即可。

如果你將每個工作表中的連線內容設定「檔案開啟時自動更新」,則開啟檔案時可以在一個工作表中看到完整的最新資料。

在A欄中的日期格式,可以設定數字格式為「yyyy/mm/dd」,在視覺上較為整齊。

要如何讓奇、偶數列的底色不同呢?

1. 先將儲存格設定一個底色(例如:較淺的綠色)。

2. 在「設定格式化條件」中設定「使用公式來決定要格式化哪些儲存格」的公式為「=MOD(ROW(2:2),2)<>0」。

如此可以設定奇數列為較深的綠色,偶數列則會維持原來的較淺綠色。

關於INDIRECT函數的詳細說明,請參考微軟網站:

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

2011年1月21日 星期五

照片的EXIF資訊

最近同仁們遇到需要使用數位照片的日期等資訊,所以藉機告訴他們 Windows 7 的新功能。當你在 Windows 7 的檔案總管中選取一張照片,則在視窗最下方的資訊區會顯示一大堆這張照片的 EXIF(Exchangeable image file format, 可交換圖像文件)資訊,它清楚的記錄了拍照當時相機屬性和數據。

參考維基百科中的 EXIF 相關說明:http://zh.wikipedia.org/zh-tw/EXIF

你可以透過各種照片管理軟體來檢視 EXIF 資訊,現在如果使用 Windows 7 ,將可以直接在檔案總管中來檢視及修改這個資訊。

當你在某個資訊上按一下,例如「拍攝日期」,則可以重新調整拍攝日期,按一下[儲存檔案]按鈕,則原來的拍攝日期就會被修改。如果當初拍攝時相機的日期設定錯誤,則可以用此方法來修正,即使拿去相館沖印,也會以修改的日期為準。

但是有些項目不准修改的,例如:尺寸、大小、光圈、焦距等。但是,相機製造商和相機型號等竟然也是可修改,難道相機本身會偽造嗎?不過,即使 Windows 7 不提供修正功能,也會有很多軟體提供修改功能。

如果你選取一張照片檔案按一下右鍵,選取[內容]選項,在[詳細資料]標籤下也有詳盡的屬性值。相同的,你也可以在此修改一些資訊(參考下圖)。

如果基於保護隱私,而想要移除這些資訊(不是要去做壞事),可以按一下「移除檔案屬性和個人資訊」。

如果你想要消除所有的 EXIF 資訊,則選取[建立已移除所有可能檔案屬性的複本]選項。

如果你只想要消除某些資訊,則個別勾選想要移除的屬性。

image

同仁又問到,如果要將照片中加上日期,該如何處理呢?以 PhotoCap 5.0 這個軟體來試試。

1. 按一下[洗照片]按鈕。

2. 先載入一張照片。

3. 勾選[加上日期]選項,相片右下角即會顯示 EXIF 資訊中的日期和時間。

4. 按一下[輸出照片]按鈕。

PowerPoint-將簡報轉成Flash檔

如果你已經有一個製作完成的簡報,想要將簡報轉成 Flash 檔,方便在網頁上呈現或是以 Flash Player 播放,可以試試 iSpring 軟體(http://www.ispringsolutions.com/)。

iSpring 提供了免費版本:

http://www.ispringsolutions.com/free_powerpoint_to_flash_converter.html

下載網頁:http://www.ispringfree.com/download.html

檔案下載:http://www.ispringfree.com/download/ispring_free_5_5_1.msi

當安裝完成後,會在 PowerPoint 中新增一個功能表:

當你開啟一個簡報後,按一下[iSpring Free/Publish]按鈕,開始進行出版動作。

輸入簡報的 Title 及簡報要輸出的位置,按一下[Publish]按鈕,開始進行轉換成 Flash 檔案的工作。

當檔案轉換完成後,會在你指定的資料夾中輸出如下的檔案:

當你開啟 index.html 檔案時,可以在瀏覽器中播放這個 Flash 檔案,利用視窗最下方提供的簡單功能來操作。而輸出的 SWF 格式的檔案也可以使用在其他接受 Flash 格式的地方。

iSpring Free 還供了可以在簡報中插入 Flash 檔和 YouTube 影片的功能。

電子書製作-以Flip PDF為例

最近練習了以Flip PDF(http://www.flipbuilder.com/flip-pdf/)來製作電子書,它可以將一個 PDF 檔轉換成網頁或以 Flash player 播放的 Flash 電子書。

試用版下載網址:http://www.flipbuilder.com/download/a-pdf-pt.exe

操作方式如下:

1. 啟動時,因為是試用版,所以按一下[Try]按鈕。

2. 按一下[Improt PDF],匯入一個 PDF 檔。

你可以調整檔案品質及檔案大小,並且決定要不要一併匯入書籤和超連結等資料,及是否啟動Search功能。

3. 按下[Import Now]按鈕後,它會先匯入前面10頁。

3. 按一下範本(Templates)旁的三角形符號,可以挑選外觀樣式。

有三類範本可以挑選:

4. 按一下[Apply Change],套用各種設定。

5. 按一下[Convert To Flipping Book],準備轉換成電子書。

6. 選取一種輸出格式:*.html 或 *.zip 或 *.exe。其中 HTML 格式是瀏覽器播放,而 EXE 檔是以 Flash Player 播放。

image

觀賞一下完成作品,畫面最下方會因為你使用的是試用版,所以會出現該公司網址訊息。而且網頁 TITLE 也會因為你使用中文而出現亂碼。不過這比較容易解決,只要自行將這個 HTML 的 TITLE 修改成你要的字即可。

2011年1月20日 星期四

Excel-依據加權表計算總額

在 Excel 中的一個資料表(如下圖左),若要根據類別所屬的加權來計算金額,該如何處理呢?

或許你會使用傳統的方法:

儲存格D2:=IF(B2="A",C2*G2,IF(B2="B",C2*G3,IF(B2="C",C2*G4,IF(B2="D",C2*G5,))))

使用這個方法,如果類別增加,則程式將會變長,不好撰寫。

試試 SUMPRODUCT 函數:

儲存格D2:=SUMPRODUCT(--($F$2:$F$5=B2),$G$2:$G$5)*C2

複製儲存格D2到儲存格D2:D16。

其中的「--」,乃將 ($F$2:$F$5=B2) 的 True/False 陣列,轉換成 1/ 0 的陣列。

如果增加類別,則只要改變儲存格範圍即可。

關於SUMPRODUCT函數的詳細說,請參考微軟網站:

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

製作Flash相簿-Wondershare Flash Gallery Factory

最近試用了 Wondershare Flash Gallery Factory 來將相簿轉成 Flash 檔,並且放在 PowerPoint 2010 的簡報中,發現效果還不錯。

Wondershare Flash Gallery Factory

功能說明:http://www.wondershare.com/pro/flash-gallery-factory-deluxe.html

下載網址:Download Wondershare Flash Gallery Factory Standard

安裝後執行 Wondershare Flash Gallery Factory ,其有二種模式可選擇:

(1) Slidershow Mode

(2) Gallery Mode(如果你是試用版,則本模式無法使用)

在[Browse]標籤下選取相片所在的資料夾,並選取想要的照片,將其拖曳至最下方的工作區。

切換至[Template]標籤,選取一種範本。

在[Effect]標籤下設定每張照片的轉場效果,你也可以選取[Randomize to All]選項,由電腦隨機亂數設定。每個照片間的轉場特效標示的非常清楚,你可以隨時重新設定。

在[Decorate]標籤下,為每一張照片設定顯示文字及文字的動畫效果。

在[Publish]標籤下,選取一種輸出方式,其提供了多種的輸出格式,例如:SWF(Flash格式)、XML、HTML、執行檔等,也可以建立螢幕保護程式。按一下[播放]按鈕,可以預覽播放效果。

當要輸出檔案時,你必須設定路徑和檔案名稱,按一下[Publis]按鈕即可。

開啟瀏覽器檢視成果:

好康東東