贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201105 (45)

瀏覽方式: 標題列表 簡短摘要

在 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。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

在 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:要無條件捨去成整數的實數。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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,往下儲存格貼上。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 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鍵,即可得到一組新的姓名清單。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:「計算年齡時,如果是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,否則保持原來的月數。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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 星期四

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

如果知道某人生日,想要知道其已出生了幾年幾月幾日,該如何處計算?如果想要計算距下次生日還有幾天,該如何計算?試試DATADIF函數!

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

儲存格C3:=DATEDIF(A3,TODAY(),"YM")

儲存格D3:=DATEDIF(A3,TODAY(),"MD")

儲存格E3:=IF(DATE(YEAR(TODAY()),MONTH(A3),DAY(A3))>=TODAY(),DATE(YEAR(TODAY()),MONTH(A3),DAY(A3))-TODAY(),DATE(YEAR(TODAY())+1,MONTH(A3),DAY(A3))-TODAY())

其意義是判斷今年的生日是否已超過,如果未超過,則以今年生日減今天日期,如果已經超過,則以明年生日減今天日期。

DATEDIF

語法:DATEDIF(start_date,end_date,unit)

Unit說明

"Y":週期中的整年數

"M":週期中的整月數

"D":週期中的天數

"MD"start_dateend_date間的天數差。(忽略日期中的月和年)

"YM"start_dateend_date間的月數差。(忽略日期中的日和年)

"YD"start_dateend_date間的天數差。(忽略日期中的年)

 

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

出生日期
1968/06/19
2011/04/15
1995/11/12
1975/10/27
1988/09/28
1981/05/25
2001/05/04
2010/01/15
2010/10/23
1975/07/30
1997/11/15
2003/08/22
1988/07/25
1979/11/30
1992/11/05
1989/09/07
1989/06/21
1979/11/25
1992/07/09
1979/08/04
2002/05/12
1971/02/01
1978/10/01

vincent 發表在 痞客邦 PIXNET 留言(10) 人氣()

你今天Google了嗎?大家常用搜尋引擎找尋資料,你的方法是正確的嗎?可以快速找到資料嗎?現在有個「一天一Google」活動,可以透過遊戲方式,來檢視自己的搜尋技巧,還可進行抽獎活動。

活動網址:http://agoogleaday.com/tw/

輸入答案:https://www.google.com/appserve/fb/forms/agoogleadaytw/

根據網站的說明,其採用 Deja Google 搜尋 - 一個像是時光機的功能,可讓你只搜尋到當日題目出現之前的網路搜尋結果,這樣才不會輕易地在別人的部落格 po文找到題目的答案,而減少了搜尋過程中的樂趣。

列出這幾天的問題:

5/18, 2011:哪一個瀏覽器可免費下載熱門遊戲「憤怒鳥」(Angry Birds) 網路版?

5/19, 2011:電影「神鬼奇航」出自於迪士尼樂園的哪一項遊樂設施?

5/20, 2011:中華民國總統就職日是從哪一年開始訂在5月20日?

5/21, 2011:英國威廉王子與王妃凱特蜜月所在地的塞席爾群島 (Seychelles),其主島叫什麼名字?

5/22, 2011:金庸名著射鵰英雄傳中,黃蓉的「二十四橋明月夜」,主要的食材是甚麼?

5/23, 2011:以本名田馥甄發片的歌手 Hebe,其個人專輯中官方版 MV在 YouTube上點播次數最高的歌曲為?

5/24, 2011:西元1626年的今天,荷蘭人從印第安人手中買下紐約曼哈頓,花了多少錢?

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中有一個依月份列出的收支金額表,根據這個資料表,要來計算收、支及收支相抵的金額各為多少,該如何處理?

以下均為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

儲存格F2:{=SUM(IF(MOD(ROW(金額),2)=0,金額,FALSE))}

利用MOD函數取得列數除以2的餘數,如果為0,則為偶數列,即為「收」的部分。

儲存格F3:{=SUM(IF(MOD(ROW(金額),2)=1,金額,FALSE))}

利用MOD函數取得列數除以2的餘數,如果為1,則為奇數列,即為「支」的部分。

如果要求收支相抵,則將收的金額減掉支的金額即可。

若是想要練習不透過儲存格F2和儲存格F3,該如何處理?

儲存格F4:{=SUM(金額*(MOD(ROW(金額)+1,2)*2-1))}

MOD(ROW(金額)+1,2):判斷為偶數列或是奇數列。

MOD(ROW(金額)+1,2)*2-1):偶數列轉換為1,奇數列轉換為-1。

上式和金額相乘後,即可得「收 – 支 + 收 – 支  + …」,透過SUM函數可得總和。

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

月份 收支 金額
一月 10,179
9,325
二月 5,341
4,968
三月 6,935
4,066
四月 12,064
9,234
五月 7,304
5,842
六月 14,294
11,958
七月 7,564
6,984
八月 7,721
7,472
九月 5,544
4,274
十月 14,655
12,568
十一月 12,002
9,195
十二月 9,945
7,918

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中取得一個日期和數量的資料表,如何根據某個日期求取最近幾天(往前推算)和最近幾筆(往前推算)的數量總和呢(參考下圖)?並且要在儲存格範圍中標示這些被選出來運算的日期。

在計算前,先將B欄的日期資料部分定義名稱為:日期,將C欄中數量資料部分定義為數量。

(1) 最近天數

儲存格H2:{=SUM(IF(日期<=F2,IF(日期>F2-G2,數量,FALSE),FALSE))}

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

IF(日期<=F2,IF(日期>F2-G2,數量,FALSE),FALSE):求取小於指定日期且大於指定日期+數目的日期之間,所對照的數量陣列。

再透過SUM函數加總這些數量陣列值。

接著要將儲存格依選取的日期標示出不同格式的範圍。選取儲存格A2:A25,輸入以下設定公式:

=AND($B2<=$F$2,$B2>=($F$2-$G$2))

將格式設定為較深的紅色。

(2) 最近筆數

儲存格H3:=SUM(OFFSET(C2,MATCH(F3,日期)-1,,-G3,))

MATCH(F3,日期)-1:找出指定日期位於B欄的那個位置。

在OFFSET函數中將指定的數目X(-1),即往前推算儲存格範圍。

利用SUM函數將OFFSET所得的數量之儲存格範圍加總。

接著要將儲存格依選取的日期標示出不同格式的範圍。選取儲存格A2:A25,輸入以下設定公式:

=AND(ROW(A2)<=MATCH($F$3,日期)+1,ROW(A2)>MATCH($F$3,日期)+1-$G$3)

將格式設定為較深的綠色。

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

項次 日期 數量
1 2011/01/01 6
2 2011/01/02 19
3 2011/01/05 11
4 2011/01/07 3
5 2011/01/11 1
6 2011/01/15 11
7 2011/01/19 3
8 2011/01/20 13
9 2011/01/21 5
10 2011/01/25 17
11 2011/01/27 17
12 2011/01/29 2
13 2011/02/01 4
14 2011/02/04 3
15 2011/02/07 15
16 2011/02/11 9
17 2011/02/14 2
18 2011/02/17 19
19 2011/02/18 15
20 2011/02/19 19
21 2011/02/22 15
22 2011/02/24 20
23 2011/02/27 11
24 2011/03/01 20

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中根據一個編號和金額構成的資料表(參考下圖),試著來練習使用陣列公式。為了方便說明:將A欄中的資料命名為「編號」,將B欄中的資料命名為「金額」。

image

(1) 計算金額於2001~3000的個數

以下三種運算方式的結果一樣:

[例] 儲存格D3:=COUNTIF(金額,">2000")-COUNTIF(金額,">3000")

這是沒有使用陣列的公式。

[例] 儲存格D2:=SUM(COUNTIF(金額,">" & {2000,3000})*{1,-1})

公式意義相當於:

=SUM({COUNTIF(金額,">2000”),COUNTIF(金額,">3000”)}*{1,-1})

=SUM(COUNTIF(金額,">2000”),-COUNTIF(金額,">3000”))

將大於2000的個數減掉大於3000的個數,即為2001~3000的個數。

[例] 儲存格D4:=SUMPRODUCT((金額>2000)*(金額<=3000))

公式意義相當於:

=SUMPRODUCT((金額>2000的True/Fasle陣列)*(金額<=3000的True/False陣列))

公中的「*」,可以藉由運算,將True/Fasle轉換為1/0,其結果相當於執行邏輯AND的運算。

(2) 編號開頭各個字母的個數

[例] 儲存格D7:{=SUM(IF(LEFT(編號,LEN(D7))=D7,1,0))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格D7至儲存格D7:D10。

LEN(D7):求出儲存格D7中字串的長度。

LEFT(編號,LEN(D7)):取出編號最左端和儲存格D7一樣的字串。

IF(LEFT(編號,LEN(D7))=D7,1,0):如果編號最左端和儲存格D7一樣的字串一樣時就會回1,反之傳回0。

透過陣列公式可得一個1/0的陣列,再藉由於SUM函數將1/0加總。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中的儲存格加上註解,有助於資料的記載。如果想要顯示一個有背景圖片的註解以美化版面,該如何處理呢?(參考下圖)

1. 在要插入註解的儲存格上按一下右鍵,選取[插入註解]指令。

2. 輸入註解文字。

3. 在註解的邊框上按一下右鍵,選取[註解格式]指令。

4. 在[註解格式]對話框中的[色彩和線條]標籤下,在[色彩]的下拉式清單中,選取[填滿效果]指令。

5. 在[圖片]標籤下,按一下[選取圖片]按鈕。挑選一張圖片。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 中,陣列是很好的工具,可以簡化運算的公式。陣列又分一維陣列和二維(多維)陣列。陣列中的元素若為固定內容則為常數陣列。常數陣列以「{ }」含括,其中的元素可以是相同資料型態,也可以是不同資料型態。

(1) 一維陣列

常數水平陣列:相當於同一列之不同欄的儲存格構成,用「,」分隔。

例:{2,4,6,8},相當於下圖中的儲存格A2:D2,而儲存格內容可以改變。

例:{"A","a","中","3"} (文字組成元素)

例:{TRUE,FALSE,FALSE,TRUE} (邏輯值組成元素)

例:{5/22,5/28,6/3,6/7} (日期組成元素)

例:{2,"A",TRUE,5/22} (多種資料型態組成元素)

例:COLUMN(A:D)

常數垂直陣列:相當於同一欄之不同列的儲存格構成,用「;」分隔。

例:{1;3;5;7},相當於下圖中的儲存格A13:A16,而儲存格內容可以改變。

例:{2;"A";TRUE;5/22}

例:ROW(1:4)

(2) 二維陣列

常數二維陣列

例:{1,2;3,4;5,6}

例:{1,"A";3,"B";5,"C"}

例:{Ture,False,1;False,True,3;True,Ture,5}

(3) 運算

使用常數陣列時,要自行輸入「{ }」,如果使用儲存格陣列時,在輸入完成時,要按 Ctrl+Shift+Enter 鍵,由系統自行加入「{ }」。

例:

=SUM(4*{2,4,6,8}) =8+16+24+32 =80 (此為水平陣列)

相當於陣列公式:{=SUM(4*A2:D2)},在輸入完成時,要按 Ctrl+Shift+Enter 鍵。

例:

=SUM(4*{1;3;5;7}) =4+12+20+28 =64 (此為垂直陣列)

相當於陣列公式:{=SUM(4*A13:A16)},在輸入完成時,要按 Ctrl+Shift+Enter 鍵。

例:

=SUM({2,4,6,8}*{1;3;5;7})

=(2*1+4*1+6*1+8*1)+(2*3+4*3+6*3+8*3)+(2*5+4*5+6*5+8*5)+(2*7+4*7+6*7+8*7)

=20+60+100+140

=320

相當於陣列公式:{=SUM(A2:D2*A13:A16)},在輸入完成時,要按 Ctrl+Shift+Enter 鍵。

例:

=SUM(({2,4,6,8}>5)*{2,4,6,8})

=SUM({2>5,4>5,6>5,8>5}*{2,4,6,8})

=SUM({FALSE,FASLE,TRUE,TRUE}*{2,4,6,8})

= FALSE*2+FALSE*4+TRUE*6+TRUE*8)

= 0+0+6+8 (執行運算時會將TRUE/FALSE轉換為1/0)

= 14

相當於陣列公式:{=SUM((A2:D2>5)*A2:D2)},在輸入完成時,要按 Ctrl+Shift+Enter 鍵。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 中如果取得一個電話號碼數列(格式如下圖左),如何轉成不同格式呢?

(1) 轉換一

儲存格B2:="("&LEFT(A2,2)&") "&MID(A2,4,4)&"-"&RIGHT(A2,4)

LEFT(A2,2):取得「-」左邊2個字。

MID(A2,4,4):取得「-」右邊4個字。

RIGHT(A2,4):取得「-」字串最右邊4個字。

(2) 轉換二

儲存格C2:=VALUE(RIGHT(SUBSTITUTE(A2,"-",""),LEN(SUBSTITUTE(A2,"-",""))-1))

SUBSTITUTE(A2,"-",""):將儲存格A2中的「-」去除。

再利用RIGHT函數將第一個字「0」之後的數字取出。

最後透過VLAUE函數將文字轉換成數字。

你也可以使用以下的公式,結果一樣。

儲存格C2:=VALUE(REPLACE(RIGHT(A2,LEN(A2)-1),FIND("-",RIGHT(A2,LEN(A2)-1)),1,""))

(3) 轉換三

進一步將轉換二的結果,利用數值格式設定為:

特殊:一般電話號碼(8位數),也可以達到(0X) XXXX-XXXX的效果。

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

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

SUBSTITUTE:將文字字串中的 old_text 部分以新字串 new_text 取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

text:要取代字元的文字,或含有該文字之儲存格的參照。

old_text:這是要取代的文字。

new_text:是要用來取代 old_text 的文字。

instance_num:指定要將第幾個 old_text 取代為 new_text

 

 

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

REPLACE:根據指定的字元數,以不同的文字字串來取代文字字串的某一部分。

語法:REPLACE(old_text, start_num, num_chars, new_text)

old_text:想要取代其中某些字元的文字。

start_num:在 old_text 中,要以 new_text 取代的字元位置。

num_chars:要用 REPLACE old_text 取代成 new_text 的字元數。

new_text:要取代 old_text 之字元的文字。

 

vincent 發表在 痞客邦 PIXNET 留言(4) 人氣()

在 Excel 中利用Weekday函數可以製作萬年曆。

(1) 製作最近四週的萬年曆

若在儲存格B1中輸入一個日期,則要顯示當週以後的四週日期。

儲存格A3:=DAY($B$1-(WEEKDAY($B$1,1)-1)+COLUMN(A:A)-1+(ROW(1:1)-1)*7)

WEEKDAY($B$1,1):求取今天日期為星期幾,因為星期日為會傳回1、星期一傳回2、…、星期六傳回7。

WEEKDAY($B$1,1)-1:星期日得0、星期一得1、…、星期六得6。

DAY($B$1-(WEEKDAY($B$1,1)-1)):可得當週的第一天。

透過加上COLUMN(A:A)-1,在向右複製時增加1日;ROW(1:1)-1)*7,則在向下複製時增加7日。

複製儲存格A3至儲存格A3:G7。

(2) 製作萬年曆

在儲存格A1中指定月份,在儲存格G1中指定年份。

儲存格A3:=DAY(DATE($G$1,$A$1,1)-(WEEKDAY(DATE($G$1,$A$1,1),1)-1)+COLUMN(A:A)-1+(ROW(1:1)-1)*7)

同(1)之原理,DATE($G$1,$A$1,1)可以指定判定的日期。

複製儲存格A3至儲存格A3:G7。

(3) 利用微調按鈕使用萬年曆

在[開發人員]功能表中新增二個「微調按鈕」,設定控制項格式:

月份→最小值:1,最大值:2,儲存格連結:$A$1。

年份→最小值:1904,最大值:2999,儲存格連結:$G$1。

如此,便可以使用微調按鈕取得各年各月的萬年曆了。

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

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(星期六)

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

在 Excel 中有一個各班成績統計表,現在要定出上標值和下標值,求得各班在這個成績範圍的人數。(參考下圖)

(一) 定義各班的名稱:

(1) 選取儲存格B1:F24。

(2) 按一下 Ctrl+Shift+F3,開啟[以選取範圍建立名稱]。

(3) 勾選「頂端列」選項,按一下[確定]按鈕。

如此,便可建立五個名稱:三年1班、三年2班、三年3班、三年4班、三年5班。

(二) 計算超過上標人數:

儲存格I4:=COUNTIF(INDIRECT(H4),">"&$I$1)

其中INDIRECT(H4)乃將儲存格內容(三年1班)轉換為位址(名稱:三年1班)

如果你不使用名稱,也可輸入以下公式:

儲存格I4:=COUNTIF(OFFSET($B$2,0,ROW(1:1)-1,23,1),">"&$I$1)

使用OFFSET($B$2,0,ROW(1:1)-1,23,1),可以由上而下的班級名稱,抓取由左而右的班級資料。

(三) 計算低於下標人數:

儲存格J4:=COUNTIF(INDIRECT(H4),"<"&$K$1)

如果你不使用名稱,也可輸入以下公式:

儲存格J4:=COUNTIF(OFFSET($B$2,0,ROW(1:1)-1,23,1),"<"&$K$1)

(四) 計算一般人數:

儲存格K4:=COUNTIF(INDIRECT(H4),">="&$K$1)-COUNTIF(INDIRECT(H4),">"&$I$1)

或是使用SUMPRODUCT函數:

儲存格K4:=SUMPRODUCT((INDIRECT(H4)>=$K$1)*(INDIRECT(H4)<=$I$1))

其中SUMPRODUCT函數中使用「*」,可以將True/False陣列轉換為1/0陣列。

或許你也可以使用陣列公式:

儲存格K4:{=SUM(IF((INDIRECT(H4)>=$K$1)*(INDIRECT(H4)<=$I$1),1,FALSE))}

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

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

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

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

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

COUNTIF:計算範圍內符合您所指定單一條件準則的儲存格總數。

語法:COUNTIF(range, criteria)

range:要列入計算的一個或多個儲存格,包括數字或包含數字的名稱、陣列或參照;空白或文字值會忽略。

criteria:定義要將哪些儲存格列入計算的準則,可以是數字、表示式、儲存格參照或文字字串。

可以在準則中使用萬用字元:問號 (?) 及星號 (*);問號可以替代任何單一字元;星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號,請在該字元前輸入波狀符號 (~)

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 中,如果想要產生一個固定間隔日期的數列,但不包含星期六和星期日,該如何處理?(參考下圖)

儲存格A3:=IF(WEEKDAY(A3+$B$1,2)>5,A3+$B$1+8-WEEKDAY(A3+$B$1,2),A3+$B$1)

利用WEEKDAY(A3+$B$1,2)判斷其值是否大於5(星期六和星期日),

如果不是:將儲存格A3加上儲存格B1,

如果是:將儲存格A3加上儲存格B1,再加上8-WEEKDAY(A3+$B$1,2)。(星期六加2,星期日加1)

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

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(星期六)

 

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 中有個實施日期和工作項目的資料表(參考下圖),要根據這些資料來統計各個工作項目在假日和非假日的數量,該如何處理?

儲存格E2:=SUMPRODUCT((WEEKDAY($A$2:$A$40,2)<6)*($B$2:$B$40=E1))

WEEKDAY($A$2:$A$40,2)<6,要找出實施日期中星期小於6者(星期一~星期五)的True/False陣列,

$B$2:$B$40=E1,找出工作項目符號各個項目的True/False陣列,

儲存格E2:SUMPRODUCT(實施日期True/False陣列*工作項目True/False陣列),其中的「*」會將True/False陣列轉換為1/0陣列,再執行乘積的和。

儲存格E3:=SUMPRODUCT((WEEKDAY($A$2:$A$40,2)>5)*($B$2:$B$40=E1))

WEEKDAY($A$2:$A$40,2)<6要找出實施日期中星期大於5者(星期六和星期日)的True/False陣列。

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

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(星期六)

 

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

1 23
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼