2011年5月31日 星期二

Excel-自動在列和欄產生序號

在 Excel 中如果要在多列中產生序號(1,2,3,…),可以透過ROW函數。

(1) =ROW(A1)

(2) =ROW(1:1)

(3) =ROW()

不管那一列中輸入(1)和(2)這二個公式,當往下複製時,都會自動產生1,2,3, … 的數列。如果改變參數A1為B1,C1,…,或是改變參數1:1為2:2,3:3,…,都可以改變數列的起始數值。而ROW()則以儲存格的位址當為參數,若位於第6列,則會傳回6。

參考下圖,如果你將某一列(例如:第10列)刪除,其數列結果不會受到影響。

但是要注意,如果是用在公式中的運算時,例如:儲存格D5:=A13+2

當第13列被刪除時,該公式會產生#REF!錯誤訊息。

如果你要在各欄中產生數列,則可以使用以下公式:

(1) =COLUMN(A1)

(2) =COLUMN(A:A)

(3) =COLUMN()

不管那一欄中輸入(1)和(2)這二個公式,當往右複製時,都會自動產生1,2,3, … 的數列。如果改變參數A1為B1,C1,…,或是改變參數A:A為B:B,C:C,…,都可以改變數列的起始數值。而ROW()則以儲存格的位址當為參數,若位於F欄,則會傳回6。

2011年5月29日 星期日

Excel-計算工讀費所需鈔票及硬幣(INT+MOD)

在 Excel 中有一個工讀費的資料表,如何計算工讀費所需鈔票及硬幣?這次要運用INT和MOD函數來運算。

儲存格C3:=INT(B3/C$2)

INT函數可以求取指定數字的不大於之最大整數,所以INT(B3/1000),相當於求取被1000除盡之整數。

儲存格D3:=INT(MOD($B3,C$2)/D$2)

在求100元鈔時,MOD($B3,1000)會計算薪資除以1000所得的餘數,即求得千元鈔以外所需的錢數。再透過INT函數求得所需的100元鈔數量。

複製儲存格D3至儲存格D3:G3,再複製儲存格C3:G3,貼至儲存格C3:G28。

詳細函數說明,請參考微軟網站:

MODhttp://office.microsoft.com/zh-tw/excel-help/HP010342698.aspx

MOD:傳回兩數相除後之餘數。餘數和除數具有相同的正負號。

語法:MOD(number, divisor)

Number:要計算餘數的數字。

Divisor:指Number 的除數。

 

INThttp://office.microsoft.com/zh-tw/excel-help/HP010342625.aspx

INT:傳回指定小數位數無條件捨去之整數值。

語法:INT(number)

Number:要無條件捨去成整數的實數。

2011年5月28日 星期六

Excel-相對位址和絶對位址

在 Excel 中,如果在一個儲存格中填入一個公式,在複製公式時,Excel 會自動依欄或列的變化,來變換公式中的位址。以實例來練習,將下圖中的左邊的表格轉換成右邊的表格:

撰寫公式時,要先考量好複製公式時所產生的位址變化,例如:

儲存格G3:=IF($B2=G$1,$C2,"")

因為要複製儲存格,所以第一列和B欄和C欄的位址不能改變,因此加上「$」。(使用F4鍵,可以切換四種位址變化,例如:B2、$B$2、$B2、B$2)

複製儲存格G3至儲存格G3:I3,再複製儲存格G3:I3至儲存格G3:I28,便可完成表格的轉換。

善用相對位址和絶對位址的變化,可以一個公式,即可完成一個表格。

2011年5月27日 星期五

Excel-各種排序

在 Excel 提供了排序的函數,例如:RANK,這次來練習不一樣的排序方式。

先定義各種資料名稱:姓名、分數、輔助分別為A欄、B欄、G欄有資料的部分。

(1) RANK排序

儲存格C3:=RANK(B3,分數)

(2) COUNTIF排序

儲存格D3:=COUNTIF(分數,">"&B3)+1

其原理是計算在所有分數中,大於自己的分數個數,再加1,即是自己的排名。

(3) 不產生相同名次的排名

由於以上兩種方式若遇同分的狀況,將會出現相同排名,並且下個分數會跳過同分的名次。如果想要產生不重覆的名次,該如何設計呢?首先要產生一組輔助資料

儲存格G3:=B3+(1000-ROW())/10000

將每個分數加上(1000-列號)/10000,即將分數加上一個不重覆的數字,而列號愈小,加上的數字愈小,反之亦反。

儲存格E3:=RANK(G3,輔助)

由於輔助欄位的資料不會重覆,所以該名次也不會有相同的狀況,只是同分者,列號較小,名次較少。

(4) 依名次列出姓名

儲存格F3:=INDIRECT(ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1))

LARGE(輔助,ROW(1:1)),輔助,0):找出輔助資料中第1大者。

MATCH(LARGE(輔助,ROW(1:1)),輔助,0):找出輔助資料中最大者在第幾列。

ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1):找出最大值在的位址,加2是因為第一列由列號3開始。

INDIRECT(ADDRESS(MATCH(LARGE(輔助,ROW(1:1)),輔助,0)+2,1)):INDIRECT函數將最大值的位址轉成該位址的內容。

複製儲存格C2:F2,往下儲存格貼上。

Excel-重組姓名清單中的姓和名

在 Excel 中取得一些姓名的資料,如果想重組這些姓名,例如將姓和名任意組合成新名字,該如何處理呢?

首先,要建立二個輔助欄位,產生一些亂數值:

儲存格B2:=RAND()

儲存格C2:=RAND()

複製儲存格B2:C2到儲存格B2:C27,產生的這些亂數值幾乎不會重覆。

透過這些亂數的排序結果,可以將A欄的姓名打散。

儲存格D2:=LEFT(INDIRECT(ADDRESS(RANK(B2,$B$2:$B$27)+1,1)),1)&RIGHT(INDIRECT(ADDRESS(RANK(C2,$C$2:$C$27)+1,1)),2)

RANK(B2,$B$2:$B$27):找出B欄中的亂數值在B欄中的排名。

ADDRESS(RANK(B2,$B$2:$B$27)+1,1):將亂數的排名值做為要取第幾列的姓名,其中「+1」是因為姓名由第二列開始。

INDIRECT(ADDRESS(RANK(B2,$B$2:$B$27)+1,1)):將ADDRESS所指定的儲存格,取出其中的內容。

使用LEFT函數取出姓名最左邊的一個字,即取得「姓」的部分。

使用RIGHT函數取出姓名最右邊的二個字,即取得「名字」的部分。

每按一下F9鍵,即可得到一組新的姓名清單。

Excel-計算年齡時的進位問題

網友問到:「計算年齡時,如果是42Y5M6D則轉換為42Y5M,如果D>=15時,例如42Y5M15D則轉換為42Y6M,不知如何使用excel做這件事情?」

請先參考「Excel-計算實際年齡(年月日)-DATEDIF」的做法。

網址:http://isvincent.blogspot.com/2011/05/excel-datedif.html

儲存格B3:=DATEDIF(A3,TODAY(),"Y")

儲存格C3:=IF(DATEDIF(A3,TODAY(),"MD")>=15,DATEDIF(A3,TODAY(),"YM")+1,DATEDIF(A3,TODAY(),"YM"))

DATEDIF(A3,TODAY(),"MD")>=15:判斷年齡中的「日數」,如果大於15,則月數加1,否則保持原來的月數。

2011年5月26日 星期四

Excel-資料庫函數(DSUM練習)

在 Excel 中取得一個部門、產品、業績的資料表,來試著練習資料庫函數中的DSUM函數。

先定義名稱,資料:儲存格A1:D28,再定義姓名、部門、產品、業績為各欄的資料。

(1) 計算部門:業務二科的業績小計

使用SUMPRODUCT函數,儲存格G2:=SUMPRODUCT(--(部門=F2),業績)

使用陣列公式,儲存格G2:{=SUM(IF(部門=F2,業績,FALSE))}

輸入完成,請按 Ctrl+Shift+Enter 鍵。

如果你使用DSUM函數,則公式會更簡單:

儲存格G2:=DSUM(資料,4,F1:F2)

(2) 計算產品:手機的業績小計

儲存格G5:=DSUM(資料,4,F4:F5)

[例] 儲存格G5:=SUMPRODUCT(--(產品=F5),業績)

[例] 儲存格G5:{=SUM(IF(產品=F5,業績,FALSE))} [陣列公式]

 

(3) 計算姓氏 :黃的業績小計

儲存格G8:=DSUM(資料,4,F7:F8)

[例] 儲存格G8:=SUMPRODUCT(--(LEFT(姓名,1)=F8),業績)

[例] 儲存格G8:{=SUM(IF(LEFT(姓名,1)=F8,業績,FALSE))}  [陣列公式]

 

(4) 計算部門:業務三科且業績>25000的業績小計

儲存格H11:=DSUM(資料,4,F10:G11)

將條件置於相同列,則會以AND函數執行,即條件為「部門=業務三科 AND 業績>25000」。

[例] 儲存格H11:=SUMPRODUCT(--(部門=F11),--(業績>25000),業績)

[例] 儲存格H11:{=SUM(IF(部門=F11,IF(業績>25000,業績,FALSE),FALSE))}  [陣列公式]

 

(5) 計算(部門:業務三科)或(產品:電腦)的業績小計

儲存格H14:=DSUM(資料,4,F13:G15)

將條件置於不同列,則會以OR函數執行,即條件為「(部門=業務三科) OR (產品=電腦)」。

[例] 儲存格H14:=SUM(IF(((部門=F14)+(產品=G15))>0,1,0)*業績)

 

(6) 計算(部門:業務三科且業績>25000)或(產品:電腦且業績>25000)的業績小計

儲存格I18:=DSUM(資料,4,F17:H19)

條件相當為「(部門=業務三科 AND 業績>25000) OR (產品=電腦 AND 業績>25000)」。

如果使用SUMPRODUCT或是陣列公式,將會太複雜,不易呈現。而使用DSUM函數,則相對簡捷。

 

相關函數說明,請參考微軟網站說明:

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

DSUM:將清單或資料庫的記錄欄位 () 中符合指定條件的數字予以加總。

語法:DSUM(database, field, criteria)

database:組成清單或資料庫的儲存格範圍。

field:指出函數中所使用的資料欄。

criteria:是包含指定條件的儲存格範圍。

 

練習用數據可由下表中取用(複製後,在儲存格貼上):

姓名 部門 產品 業績
郭柏辰 業務二科 手機  $ 28,529
黃韻如 業務四科 手機  $ 25,294
王國榮 業務一科 電腦  $ 23,459
湯德斌 業務三科 手機  $ 23,931
黃文杰 業務一科 螢幕  $ 17,308
簡文鼎 業務四科 電腦  $ 14,311
劉家瑋 業務二科 手機  $ 21,052
連宥媛 業務二科 印表機  $ 14,949
湯絜蘭 業務一科 手機  $ 27,113
蔡珮甄 業務一科 印表機  $ 10,077
林傑文 業務一科 電腦  $ 18,898
葉克芸 業務二科 印表機  $ 26,549
彭士豪 業務二科 印表機  $ 23,093
呂宜蓁 業務四科 手機  $ 24,660
彭筱晴 業務三科 電腦  $ 28,740
劉增偉 業務四科 手機  $ 20,762
郭嘉揚 業務二科 螢幕  $ 14,186
胡宜潔 業務一科 電腦  $ 25,883
柯佳齊 業務三科 電腦  $ 13,152
莊孟儒 業務一科 印表機  $ 10,173
邱創陽 業務一科 印表機  $ 15,229
許景崴 業務三科 螢幕  $ 18,057
林婷暄 業務二科 螢幕  $ 20,769
蔣和諠 業務一科 印表機  $ 20,352
趙昱欣 業務四科 電腦  $ 27,818
吳欣儀 業務三科 螢幕  $ 29,321
周柏任 業務二科 手機  $ 25,006
許庭姍 業務四科 印表機  $ 29,177

2011年5月25日 星期三

Excel-找出最近的一個星期日

在 Excel 中指定一個日期,要來找出最近的一個星期日,該如何處理?試試WEEKDAY函數。

儲存格C2:=A2+7-WEEKDAY(A2,2)

WEEKDAY(A2,2):根據儲存格A2,參數2,表示星期一傳回1、星期二傳回2、…、星期日傳回7。

7-WEEKDAY(A2,2):得到和下個星期日的差距天數。

A2+7-WEEKDAY(A2,2):將指定日期加上差距天數。

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

如果你要找出最近的某個星期幾,則可以試著改變WEEKDAY中的參數,即可達成。

WEEKDAY函數說明請參閱微軟網站:

WEEKDAYhttp://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

serial_number:要找的日期的代表序列值。

return_type:決定傳回值類型的數字。

 

RETURN_TYPE

傳回的數字

1或省略

數字1(星期日)7(星期六)

2

數字1(星期一)7(星期日)

3

數字0(星期一)6(星期六)

11

數字1(星期一)7(星期日)

12

數字1(星期二)7(星期一)

13

數字1(星期三)7(星期二)

14

數字1(星期四)7(星期三)

15

數字1(星期五)7(星期四)

16

數字1(星期六)7(星期五)

17

數字1(星期日)7(星期六)

 

練習用數據可由下表中取用(複製後,在儲存格貼上):

日期
2011/08/21 星期日
2011/08/25 星期四
2011/08/27 星期六
2011/09/05 星期一
2011/09/10 星期六
2011/09/11 星期日
2011/09/14 星期三
2011/09/19 星期一
2011/09/23 星期五
2011/09/30 星期五
2011/10/09 星期日
2011/10/15 星期六
2011/10/25 星期二
2011/11/03 星期四
2011/11/07 星期一
2011/11/08 星期二
2011/11/18 星期五
2011/11/20 星期日
2011/11/24 星期四

檢視其他文章

好康東東