贊助廠商

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

搜尋本部落格文章資料

目前日期文章: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)

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

在 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:要計算餘數的數字。

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(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,即將分數加上一個不重覆的數字,而列號愈小,加上的數字愈小,反之亦反。

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

在 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函數取出姓名最右邊的二個字,即取得「名字」的部分。

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(0) 人氣()

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

先定義名稱,資料:儲存格A1:D28,再定義姓名、部門、

使用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),業績)

產品、 業績為各欄的資料。

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

vincent 發表在 痞客邦 留言(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])

vincent 發表在 痞客邦 留言(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":週期中的整月數

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(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函數可得總和。

vincent 發表在 痞客邦 留言(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) 最近筆數

vincent 發表在 痞客邦 留言(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 發表在 痞客邦 留言(0) 人氣()

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

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

2. 輸入註解文字。

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

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

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


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

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

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

在 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函數將文字轉換成數字。

vincent 發表在 痞客邦 留言(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中指定年份。

vincent 發表在 痞客邦 留言(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)

vincent 發表在 痞客邦 留言(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:決定傳回值類型的數字。

vincent 發表在 痞客邦 留言(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])

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

1 23

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼