2013年3月31日 星期日

Excel-使用各種小計(SUBTOTAL,VLOOKUP)

在 Excel 中如果要在一個表格中顯示各種小計,可以透過啟用篩選,再勾選顯示小計,即可達成。這次我們來練習用公式來取化手動操作,要使用的是 SUBSTOTAL 函數。

下圖左是一個基本資料表,下圖右列出 SUBSTOTAL 函數的 function_num 參數的意義。

我們要使用微調按鈕來控制小計的函數,並且顯示函數的名稱。

【準備工作】

首先,選取儲存格A1:E11,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:甲、乙、丙、丁、戊。

接著,插入一個微調按鈕:

設定控制項格式,最小值:1,最大值:11,儲存格連結:$A$13。

儲存格A12:=SUBTOTAL($A$13,INDIRECT(A1))

INDIRECT(A1):將儲存格A1的文字內容以 INDIRECT 函數轉換為已定義的名稱。

複製儲存格A12,貼至儲存格A12:E12。

儲存格C13:=VLOOKUP(A13,G1:I12,3,FALSE)

使用查詢方式顯示所使用的 function_num 的小計函數。

Google雲端硬碟-轉換文件為PDF

最近很多學生為了推薦甄選而要將文件轉換為 PDF 格式,卻在某些電腦上找不到可以轉換 PDF 的程式。其實在 Word 2010 等程式中可以直接將文件儲存格為 PDF 檔案,不過在 Word 2003 版本似乎就有些困難。

不過如果你有 Google 帳號,可以試試利用 Google 雲端硬碟的功能。

1. 先將文件上傳至雲端硬碟上。

2. 點選你要轉換的檔案,進入預覽頁面。

3. 按一下「印表機」圖示。

4. 按一下[儲存]按鈕。

6. 為這個 PDF 檔命名。

image

開啟這個 PDF 檔檢視一下,發現有些地方並沒有轉換好(如下圖)。不過相信不久的將來,Google 應該能將這個部分處理好的。初期你可以先將這個功能用在內容較不複雜的文件上。

2013年3月30日 星期六

Windows 7-快速格式化與一般格式化

有一天,同仁突然問了一個大家可能都踫過的問題:在格式化磁碟時,Windows 7 在格式選項中預設在「快速格式化」,除了格式化速度比較快之外,是否也代表一般格式化可以將資料清的比較乾淨?

根據我的理解,答案令他失望了,因為根據微軟網站上的說明:

http://support.microsoft.com/kb/302686/zh-tw

當選擇在磁碟區上執行一般格式化時,檔案會從正在格式化的磁碟區上移除,然後系統會掃描硬碟以尋找損毀的磁區。掃描損毀磁區的動作佔了磁碟區格式化的大部分時間。

如果選擇「快速」格式化選項,格式化的動作會將檔案從磁碟分割移除,但是不會掃描磁碟尋找損毀的磁區。

所以建議你,只有在之前已經格式化硬碟並確認硬碟沒有損壞,才使用快速格式化選項。

不過,很奇怪的是在 Windows 7 中的預設值卻是:快速格式化。

特別提醒:無論是快速格式化或是一般格式化,其實原來磁碟中的資料都沒有真正的被移除,所以都可以使用檔案還原軟體加以還原。

如果你有一些隱私資料或是有資安問題的檔案在刪除後,不想要被救回來,也可以使用像 CClener 這類的清除軟體,它在設定中提供了刪除資料時的覆寫動作,可以將刪除的資料經由覆寫資料來讓原來資料還原不回來。

Windows 7-如何防止誤觸鍵盤滑鼠而喚醒睡眠中的電腦

有人問了這麼個問題,有時在電腦進入睡眠狀態時,因為不小心踫觸了滑鼠或鍵盤,而喚醒睡眠中的電腦,有什麼方法可以防止誤觸滑鼠或鍵盤而喚醒睡眠中的電腦呢?

大家都知道,你可以設定電源按鈕或睡眠按鈕的關機/睡眠/休眠等行為,如果你的電腦在睡眠狀態,你只要再按一下電源按鈕,即可喚醒電腦。不過,你也可以透過觸動滑鼠或鍵盤來喚醒電腦。

而你如果不想要因不小心踫觸了滑鼠或鍵盤,而喚醒睡眠中的電腦,則可以在「裝置管理員」中,選取一個滑鼠或鍵盤按二下,開啟這個裝置的[內容]對話框,在其[電源管理]標籤下,取消勾選「允許這個裝置喚醒電腦」即可。

Windows 7-設定檔案和資料夾的屬性

在 Windows 7 的檔案總管中,你可以設定檔案和資料夾的屬性,並且可以在檔案總管新增「屬性」欄位來篩選檔案。

你只要在欄位名稱上按一下右鍵,選取「屬性」,即可新增[屬性]欄位。

而檔案和資料夾是如何而來的呢?

你可以在檔案或資料夾上按一下右鍵,選取「內容」來設定:

按一下「進階」按鈕,可以進一步設定:

其設定後產生的屬性代碼如下:

D:資料夾
A:保存檔案
H:隱藏檔案
R:唯讀檔案
S:系統檔案
E:加密內容
C:壓縮內容
I:不建立索引
N:不進行封存

使用「屬性」這個欄位,你可以篩選同樣屬性的檔案,例如:篩選所有的唯讀檔、壓縮檔等。

Windows 7-解除受保護的檢視

當你拿到一個檔案,而這個檔案可能是來自另一部電腦,例如由 Email 的附件儲存而來,或是由網頁下載而來等。在你開啟這個檔案時,通常會看到一個「受保護的檢視」的訊息,並且提醒你要注意安全性的問題,而你要按一下「啟用編輯」,你才能進行編輯的動作。

如果你不想要在開啟檔案時出現這個訊息,你也可以在檔案總管裡選取這個檔案,按一下右鍵,再選取「內容」,在[一般]標籤下,你會發現安全性的訊息。

按一下[解除封鎖]按鈕,再開啟這個檔案時,就不會顯示上圖中「受保護的檢視」的訊息了。

2013年3月29日 星期五

Excel-文字時間轉換為數字時間

有網友問到一個在 Excel 中,關於文字時間轉換為數字時間的問題。在下圖左的資料表中,開始和結束的時間並非以標準的時間表示法來輸入,導至無法用在正確的顯示和計算結束和開始的時間差距。要如何轉換才能在後續使用呢?(參考下圖右)

(一) 開始日期

儲存格F2:=B2+TIME(LEFT(C2,2),RIGHT(C2,2),)

因為原始的開始和結束時間為「文字」型態,所以使用 LEFT 函數取出開始時間的左側 2 個數字做為「時」,再以 RIGHT 函數取出開始時間的右側 2 個數字做為「分」,最後再帶入 TIME 函數轉換為時間的型態。

(二) 結束日期

儲存格G2:=IF(LEFT(D2,2)*1<LEFT(C2,2)*1,B2+1+TIME(LEFT(D2,2),RIGHT(D2,2),),B2+TIME(LEFT(D2,2),RIGHT(D2,2),))

原理同開始日期之做法,但因為結束時間有跨日的問題,所以判斷如果取得結束的時數小於開始的時數,則表示有跨日,則要將日期加上 1 天。

(三) 計算間距

儲存格H2:=G2-F2

因為開始時間和結束時間已經轉換為正確的時間型態,所以計算時間間距時,只要將兩數相減即為間距。

複製儲存格F2:H2,往下各列貼上。

其中的開始時間和結束時間的數值格式,自訂為「[$-409]yyyy/mm/dd hh:mm AM/PM;@」,以求格式的美觀及一致性。

image

2013年3月28日 星期四

Excel-調整列印頁面

同仁有時候會有這樣的困擾:在編輯完 Excel 資料之後,在列印時才發現列印結果會超出一頁(如下圖)。你可能會去調整欄寬、列高或是物件的位置來讓一張紙能容得下,但有時候又無法或不想調整這些內容而感到困擾。

在 Excel 有一個很不錯的設計,如果你以縮放頁面的方式,可以免掉調整的麻煩。所以在 Excel 的版面設定中的「頁面」標籤下,就可以調整縮放比例。

你可以自由縮放比例,可以調整1頁高、1頁寬或是全部放在1頁中:

image

更方便的是:如果你想在列印時來調整縮放比例,可以在[設定]區中的最後一項,選取想要的縮放方式。

提醒一下:在此的設定會同步到版面設定中的設定結果。如果按一下「自訂縮放比例選項」時,就可以切換至版面設定的對話框。

Excel-由頭尾日期計算每日人數(SUMPRODUCT,陣列公式)

有位網友問到:在 Excel 中如果建立一個含有入住日期和退房日期的住房資料表(如下圖左),該如何自動計算每日住房的人數?

【準備工作】

選取儲存格B1:C27(你的資料範圍),按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:入住、退房。

 

【輸入公式】

(1) 使用 SUMPRODUCT 函數

儲存格F2:=SUMPRODUCT((退房>E2)*(入住<=E2))

原理是計算某日日期小於退房日且大於或等於住房日的個數(不含退房當日)。

(2) 使用陣列公式

儲存格F2:{=SUM(IF((退房>E2)*(入住<=E2),1,0))}

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

 

【補充資料】

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

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

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

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

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

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

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

2013年3月27日 星期三

Excel-快速移除隱藏的設定

同仁問到,Excel 的一個檔案用了好長一段時間,想不起來曾經做了那些隱藏動作,所以也不知從何取消隱藏。

在 Excel 中能隱藏的地方有很多,例如:隱藏欄、隱藏列、隱藏工作表、隱藏物件等等(還有其他)。參考下圖有些線索可以看出做了隱藏動作:

如何將這些隱藏找出來,並且恢復未隱藏呢?用一個投機取巧的方式:

1. 選取[檔案]功能表中的「資訊」標籤。

2. 按一下[查看問題]按鈕之下的「檢查文件」選項。

3. 系統會提醒你要先存檔。

4. 在[文件檢查]對話框中選取數個和隱藏有關的選項,

5. 按一下[檢查]按鈕,開始進行檢查。

檢查結果會顯示相關的隱藏項目資訊(參考下圖)。

6. 按一下隱藏項目的[全部移除]按鈕,即可取消該項目的隱藏動作(還原為顯示)。

結果發現:欄和列的隱藏已恢復顯示,工作表的隱藏也恢復顯示,但是有一個隱藏的物件卻被刪除了。(參考下圖)

所以,使用上要很小心,以避免這個動作讓原本只是隱藏的物件,真的消失了。

Excel-複製奇數列資料

在 Excel 中有一個資料表(如下圖左),如果只想複製出奇數序號列的資料(如下圖右),該如何處理較方便?

參考以下的做法:

1. 在儲存格D2輸入公式:=A2;在儲存格E2輸入公式:=B2

2. 選取儲存格D2:E3。

3. 拖曳右下角的填滿控制點至儲存格E26。

複製結果如下:

(目前儲存格D2:E26還在選取狀態)

4. 在選取區上按一下 Ctrl+C 鍵,複製這些資料。

5. 在選取區上按一下右鍵,選取「貼上值」。(去除儲存格中的公式中

6. 按一下[常用/排序與篩選]功能表中的「從最小到最大排序」,即可篩選出奇數列。

如法炮製,你是否也會複製偶數列或是第 1, 4, 7, 10, …  列的資料呢?

Excel-刪除重覆者(只留下第一個)(COUNTIF)

在 Excel 中有一個資料表(如下圖左),有人想要將同一欄中相同的資料只保留第一個,讓資料看起來比較簡單一點,該如何處理?

參考以下的做法:

儲存格F2:=IF(COUNTIF(A$2:A2,A2)>1,"",A2)

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

其原理是利用以第 2 列的絶對參照位址(A$2)為起點,利用 COUNTIF 函數來找出由第一個儲存格開始到某個儲存格之間,該儲存格內容所出現的次數,如果大於 1,表示重覆出現了,就以空白顯示。

Excel-去除日期中的時間

在 Excel 中的一個資料表,其中有一個日期和時間組成的數列,有人想要將時間的部分除去,只留下日期,該如何處理?

以下提供二種方式:

一、使用 INT 函數

儲存格C2:=INT(A2)

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

因為在 Excel 中,用「1」表示一天,所以 :

1 小時為 1/24,1 分鐘為 1/(24*60),1 秒鐘為1/(24*60*60)

INT 函數可以取出不大於某數的最大整數,相當於將小數部分除了。

 

二、使用「取代」功能

1. 選取儲存格A2:A18。

2. 開啟「取代」對話框。

3. 在[尋找目標]方塊中輸入「 *」(空格+星號)

4. 在[取代成]方塊中留下空白。

5. 按一下[全部取代]按鈕。

其中的「 *」(空格+星號),乃是利用「萬用字元:*」來代表所有的時間部分。

2013年3月26日 星期二

Windows 7:在命令提示字元視窗中快速切換路徑

有人問到:不管你是由開始功能表的附屬應用程式叫出「命令提示字元」視窗,或是按一下 WinKey + R 鍵,輸入「cmd」命令,所開啟的「命令提示字元」視窗的預設路徑都是:

C:\Users\Administrator> (如下圖)

每次想要切換到某個路徑底下,尤其是踫到資料夾很多層或是名稱很長等因素,在做切換工作時會產生一些困擾,例如要切換至:F:\行政資料[圖書館]\一般業務\公文影本。

以下提供一種作法:

先在檔案總管中選取想要的目錄:

在網址列輸入:「cmd」,按一下 Enter 鍵。

會立即開啟「命令提示字元」視窗,並且已切換至想要的路徑:

2013年3月25日 星期一

Excel-複製相同公式內容

有人問到:在 Excel 的資料表中,如果在公式中未使用絶對參照,但又想複製相同公式,貼在另一個位置該如何處理?(參考下圖)

大家應該知悉,如果在公式中使用「絶對參照」(例如:$A$1),則複製儲存格公式,貼至另一個儲存格時,可以完整的複製相同公式;如果在公式中使用「相對參照」(例如:A1),則複製儲存格公式,貼至另一個儲存格時,則會依儲存格的相對位址,自行調整公式內容。

然而,如果你已在儲存格公式中使用相對參照,而又想複製原來一樣的公式內容貼在其一個儲存格,該如何操作是好?參考以下的操作:(以上圖為例)

1. 選取儲存格D2:E19。

2. 按一下 Ctrl+C 鍵,複製儲存格D2:E19。

3. 在儲存格C2上按一下右鍵,選取「貼上連結」。

(來源儲存格內的相同公式會被貼至目的儲存格中)

2013年3月24日 星期日

Excel-向下填滿空白儲存格

有人問到:在 Excel 中的一個資料表(如下圖),想要向下填滿一些空白儲存格,不知那種方式比較方便。

常常會拿到一些資料表,並不符合自己要用的格式,所以要自行動些手腳。這次不是要想些複雜的公式,而是以一些操作來完成。

參考以下的操做步驟:

1. 選取資料範圍,例如:儲存格A2:B22。

2. 按一下 Ctrl+G 鍵,開啟[到]對話框。

3. 按一下[特殊]按鈕,開啟[特殊目標]對話框。

4. 選取「空格」,按一下[確定]按鈕。

image

5. 在公式列中輸入「=A2」,按一下 Ctrl+Enter 鍵。

所有空格都會自動填入公式,會顯示上一個儲存格相同內容。

2013年3月22日 星期五

Google Keep(簡單的記事工具)

Google 最近放棄了 Google Reader,搞的沸沸揚揚,但是也推出了 Google Keep 記事工具。相信大家都用過或聽過 Evernote 這類的數位筆記軟體,相對之下,Google Keep 顯得較為陽春。

Google Keep 結合在 Google 雲端硬碟中,網址:https://drive.google.com/keep/

你可以有二種檢視筆記的方式:清單、格狀,除了可以檢視目前的筆記,還可以檢視已封存的筆記。

它的介面很簡單,輸入標題和筆記內容,按一下「完成」,即輸入完一個筆記。

你可以上傳圖片,在筆記中插入一張或多張圖片:

可以標記筆記的色彩,用色彩來歸類筆記,或是作為提醒作用:

可以在筆記中放置項目清單,當你打勾某個項目時,該項目內容會被加上刪除線。

除了在雲端使用筆記本之外,Google 也推出了行動裝置上的 Google Keep App。使用這個 App,除了上述的記事功能之外,可以使用語音輸入功能來記事(以錄音方式輸入,語音會被翻譯成文字,並且儲存錄音檔),也可以使用在記事中使用照相功能來記錄照片。

你可以選取單欄檢視或是多欄檢視。

 2013-03-22_13-20-57_調整大小

可以將記事封存(收納起來),並且檢視已封存的記事,記事也可以設定色彩。

2013-03-22_13-21-09_調整大小 2013-03-22_13-21-50_調整大小

檢視其他文章

好康東東