贊助廠商

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

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

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

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

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

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

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

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

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

如果知道某人生日,想要知道其已出生了幾年幾月幾日,該如何處計算?如果想要計算距下次生日還有幾天,該如何計算?試試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())

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

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

在 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,則為奇數列,即為「支」的部分。

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):求取小於指定日期且大於指定日期+數目的日期之間,所對照的數量陣列。

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. 在[註解格式]對話框中的[色彩和線條]標籤下,在[色彩]的下拉式清單中,選取[填滿效果]指令。

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

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

(1) 一維陣列

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

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

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

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

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個字。

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。

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

Close

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

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

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

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

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼