贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2207)

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

在網友想要知道在 Excel 中如果將時間每二個小時為一個單位並給予一個編號(如下圖左),如何在輸入一個時間字串後,能自動傳回對應的編號(如下圖右)?

根據上述的規則,其輸入的時間為 4 碼,由時和分組成,設計以下的公式:

儲存格B2:=INT(VALUE(LEFT(D2,2))/2)+1

LEFT(D2,2):取出時間字串的左邊 2 碼,代表「時」的部分。

VALUE(LEFT(D2,2)):將取出時間字串代表時的 2 碼,轉換為數值。

INT(VALUE(LEFT(D2,2))/2)+1:將上述的數值除以 2,再經由 INT 函數的結果加 1,即為所求。

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

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

如果你在 Excel 的資料表中,想要在一欄或一列中取出間隔欄/列來計算其和、平均、最大值、最小值等,通常需要用到「陣列公式」。

在下圖中分別來找出間隔欄的最小值和間隔列的最小值。

(1) 間隔欄的最小值,計算儲存格A2:J2中的價格最小值

儲存格G6:{=MIN(IF(COLUMN(A2:J2)/2<>INT(COLUMN(A2:J2)/2),A2:J2,FALSE))}

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

COLUMN(A2:J2)/2<>INT(COLUMN(A2:J2)/2):找出奇數欄,傳回 TRUE/FALSE 陣列。

再對應到儲存格A2:J2,取出奇數欄(第A,C,E,…欄)的數值,再由 MIN 函數取出最小值。

 

(2) 間隔列的最小值,計算儲存格A4:B21中的價格最小值

儲存格G10:{=MIN(IF(ROW(B4:B21)/2=INT(ROW(B4:B21)/2),B4:B21,FALSE))}

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

ROW(B4:B21)/2=INT(ROW(B4:B21)/2):找出奇數列,傳回 TRUE/FALSE 陣列。

再對應到儲存格B4:B21,取出奇數列(第4,6,8,…列)的數值,再由 MIN 函數取出最小值。

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

有老師在一個學生號碼的 Excel 資料清單中,想要自動列出 1 至 35 個號碼中,有那些號碼缺少了而未出現。例如:學生繳交作業的號碼,逐筆記錄後,想要知道有那些學生尚未繳交,該如何處理這個問題呢?(參考下圖)

【輸入公式】

儲存格B2:{=SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

COUNTIF($A$2:$A$26,ROW($1:$35):找出儲存格A2:A26中,含有 1 至 35 的個數,其中不是 1 就是 0。

IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)):在 IF 函數中,依上式結果,1 代表 TRUE,0 代表 FALSE。意思是如果未出現的數字,即傳回 ROW(1:35) 的結果,該結果就是未出現的數字。而參數 40 要比最大值 35 大。

如果不想讓儲存格中超出 35 的數字(40)顯示出來,則可以修改公式如下:(參考上圖D欄)

{=IF(SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))>35,"",SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

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

有網友問到:在 Excel 中的一個資料表(參考下圖右),要從其中摘要出對應的內容(參考下圖左),該如何處理?

本例題的意思是,例如:在項目「甲」中,在儲存格C3輸入「價格」後(本例為229),自動會在同一列的儲存格D3中顯示對應的數量(本例為38),然後在儲存格B3中顯示價格所對應的類別(本例為CC)。

 

【輸入公式】

(1)儲存格D3:=OFFSET(F3,0,MATCH(C3,F3:M3,0))

MATCH(C3,F3:M3,0):透過 MATCH 函數來找出儲存格C3的內容,在儲存格範圍F3:M3中,位於第幾個(傳回一個數字),其中的參數「0」,表示要找完全相符者。

接著藉由 OFFSET 函數,將上式傳回結果,根據儲存格F3,找到相對位置的儲存格。

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

(2) 儲存格B3:=OFFSET($F$1,0,MATCH(C3,F3:M3,0)-1)

原理同(1)的說明,公中的「-1」運算,乃因為第 1 列中的儲存格是合併儲存格,每二格合併為一格,所做的調整。

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

 

因應如果所輸入的價格,在同一列中如果找不到,避免顯示錯誤訊息,修改公式:

儲存格D3:=IFERROR(OFFSET(F3,0,MATCH(C3,F3:M3,0)),"")

儲存格B3:=IFERROR(OFFSET($F$1,0,MATCH(C3,F3:M3,0)-1),"")

當價格沒有比對成功時,會以空白顯示。(參考第 10 列)

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

有一位網友問到:在 Excel 中,如果要產生一些亂數值,其整數部分為三個位數,小數部分為四個位數,該如何處理?(參考下圖)

可用的方法很多,以下使用 INT 函數和 RAND 函數來完成。

儲存格A2:=(INT(RAND()*9000000)+1000000)/10000

RAND():產生小於 1 且大於等於 0 的亂數。

RAND()*9000000:產生小於 9000000 且大於等於 0 的亂數。

INT(RAND()*9000000):產生小於 9000000 且大於等於 0 的整數。其中 INT 函數的作用為將一個數值取其不大於(小於或等於)的最大整數。

INT(RAND()*9000000)+1000000:產生小於 10000000 且大於等於 1000000 的整數。

將上一式除以 10000,即可產生整數三位數、小數四位數的亂數了!

另外,如果你使用的 Excel 版本可以使用 RANDBETWEEN 函數,則可以修改公式:

儲存格A2:=RANDBETWEEN(1000000,9999999)/10000

還有各式各樣的寫法可達到這個亂數效果,試試自行練習看看吧!

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

網友問到:在下列的 Excel 資料清單中,如果要根據項目的內容,標示出每個項目的最大值/最小值,該如何處理?

【準備工作】

選取儲存格A1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、內容。

 

 

【輸入公式】

1. 標示數值

(1) 最大值/儲存格C2:{=MAX(IF(項目=A2,內容,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+F3 鍵,公式會自動加入「{ }」。

其中參數「FALSE」,不要以空白或是 0 取代,將會得到錯誤結果。

(2) 最小值/儲存格D2:{=MIN(IF(項目=A2,內容,FALSE))}

 

2. 標示「V」

(1) 最大值/儲存格C2:{=IF(MAX(IF(項目=A2,內容,FALSE))=B2,"V","")}

(2) 最小值/儲存格D2:{=IF(MIN(IF(項目=A2,內容,FALSE))=B2,"V","")}

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

接續上一篇:計算有標示日期者的平均金額(陣列公式,SUMPRODUCT),網友想要在一個日期清單中,給予一個區間,篩選某個項目的平均值,該如何理?

【準備工作】

選取儲存格A1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目、數量。

【輸入公式】

這是個符合多條件計算小計的做法,本例為三個條件:(1)大於或等於「2014/6/5,(2)小於或等於「2014/6/21」,(3)符合項目「A」。

(1) 使用 SUMPRODUCT 函數

儲存格G2:=SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)*數量)/SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2))

SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)):計算符合三個條件的個數。

SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)*數量):計算符合三個條件的個數的總合。

將以上二式相除,即可求得其平均值。

 

(2) 使用陣列公式

儲存格G2:{=SUM((日期>=E2)*(日期<=E3)*(項目=F2)*數量)/SUM((日期>=E2)*(日期<=E3)*(項目=F2))}

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

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

有網友問到:參考下圖,如果只想要將有標示日期者所對應的金額予以平均,該如何處理?

(1) 使用陣列公式

儲存格D2:{=AVERAGE(IF(A2:A20<>"",B2:B20,FALSE))}

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

判斷在儲存格A2:A20陣列中,不是空白儲存格者所對應的B2:B20陣列,再透過 AVERAGE 函數,計算這些陣列值的平均。其中的參數「FALSE」,不可以用空白或是 0 取代。

 

(2) 使用 SUMPRODUCT 函數

儲存格D2:=SUMPRODUCT((A2:A20<>"")*B2:B20)/SUMPRODUCT(--(A2:A20<>""))

SUMPRODUCT((A2:A20<>"")*B2:B20):計算儲存格A2:A20陣列中,不是空白儲存格者所對應的B2:B20陣列內容之總和。

SUMPRODUCT(--(A2:A20<>""))計算儲存格A2:A20陣列中,不是空白儲存格者的個數。其中的「--」運算,可以將以上二式中的 TRUE/FALSE 轉換為 1/0,再代入 SUMPRODUCT 運算。

將以上二式相除,即可得平均值。

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

網友根據另一篇:Excel-條件式加總練習(SUMIF+COUNTIF),想要詢問不同人員之進料/出料次數。以下補充該部分的公式。

請先閱讀原來文章:http://isvincent.pixnet.net/blog/post/35181133

儲存格H11:=SUMPRODUCT((進出=$G11)*(經手人=H$10))

複製儲存格H11,貼至儲存格H11:J12。

(進出=$G11):判斷「進出」的陣列中是否符合儲存格G11的內容,傳回 TRUE/FALSE 陣列。

(經手人=H$10):判斷「經手人」的陣列中是否符合儲存格H10的內容,傳回 TRUE/FALSE 陣列。

(進出=$G11)*(經手人=H$10):其中的「*」運算,可以將以上二式中的 TRUE/FALSE 轉換為 1/0,再代入 SUMPRODUCT 運算。

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

參考下圖,有網友問到:如果在一個儲存格範圍中出現某一數字時,即將不同列中的儲存格範圍予以加總。

本例以在儲存格A2:E2中出現「2」為例,分別計算不同色彩的儲存格範圍中的數字總和。

(1) 儲存格B7:=(COUNTIF(A2:E2,2)>0)*SUM(A2:E2)

COUNTIF(A2:E2,2):判斷是否在儲存格A2:E2中出現「2」。

COUNTIF(A2:E2,2)>0):只要有一個「2」,則傳回 TRUE,否則傳回 FALSE。

(COUNTIF(A2:E2,2)>0)*SUM(A2:E2):在運算過程中,「*」運算會將上式中的 TRUE/FALSE 轉換為 1/0 再運算。

(2) 儲存格B8:=(COUNTIF(A2:E2,2)>0)*SUM(C3:G3)

(3) 儲存格B9:=(COUNTIF(A2:E2,2)>0)*SUM(E4:I4)

(4) 儲存格B10:=(COUNTIF(A2:E2,2)>0)*SUM(G5:K5)

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

有網友問到:在下圖的資料表中,如何根據「級距」和「天數」,查出對應的「勞工」和「單位」?

本例要使用的查詢函數有二個:OFFSETMATCH

要注意這個表格是每二欄為一種級距,這也是一個水平/垂直方向交叉位置的查詢,參考以下的公式:

儲存格B16:=OFFSET(B2,B15,MATCH(B14,B1:I1,0)-1,,)

MATCH(B14,B1:I1,0):使用 MATCH 函數,將儲存格B14的內容和儲存格B1:I1中的內容比對,傳回位於第幾欄的數值。

MATCH(B14,B1:I1,0)-1:因為 OFFSET 是以儲存格B2為起始儲存格,再以相對位址取得儲存格範圍,其以第 0 欄、第 0 列為第一個儲存格。為了查詢每個級距的「勞工」,所以使用 MATCH 函數再減 1,即是以 0 為第一個儲存格。

儲存格B17:=OFFSET(B2,B15,MATCH(B14,B1:I1,0),,)

MATCH(B14,B1:I1,0):為了查詢每個級距的「單位」,其為 MATCH(B14,B1:I1,0)-1+1 的結果。

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

有網友問到:在 Excel 中的一個資料清單,如何計算符合垂直和水平標題者的小計?

參考下圖,月份和人員(A、B、C)沒有固定順序且可能重覆。

為了解說方便,首先要定義名稱,先選取[公式/已定義之名稱/名稱管理員],定義以下名稱:

儲存格B1:G1:月份;儲存格A2:A5:人員;儲存格B2:G5:資料。

接著輸入公式:

儲存格B9:=SUMPRODUCT(資料*(人員=$A9)*(月份=B$8))

其中,月份及人員的各個欄位和標題列的名稱順序和是否重覆都不會影響公式的結果。

 

關於函數的相關說明,請參閱微軟網站:

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

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

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

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

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

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

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

網友問到:在一個 Excel 的資料清單中,如果要根據某個欄位的資料來計算小計,該如何處理?這類問題很適合使用 SUMPRODUCT 函數來運算!

儲存格I2:=SUMPRODUCT(($C$2:$C$16=H2)*$D$2:$D$16)

複製儲存格I2,貼至儲存格I2:I4。

在 SUMPRODUCT 函數中使用 SUMPRODUCT((=區小姐?)*(小計)),其中的「*」運算,可以將邏輯運算結果的 TRUE/FALSE 陣列,轉換為 1/0 陣列,再和「小計」一起計算其「乘積和」。

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

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

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

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

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

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

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

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

有網友問到:如果想要在一個 Excel 的通訊錄清單中,想要藉由下拉式清單來查詢名單中的資料,該如何處理呢?

大多數網友在使用 Excel 來查詢資料時,都會用到幾個常用的查詢函數,藉由這個例子再來練習相關的函數。

參閱下圖,學號是一個唯一值,就用學號來做為查詢的關鍵字。

參考以下步驟:

1. 選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:學號。

2. 選取儲存格H2,選取[資料/資料工具/資料驗證]指令,設定:

儲存格內允許:清單:來源:「=學號」。

即設定儲存格中的資料清單在「學號」 的儲存格範圍。

當你選取儲存格H2的下拉式清單時,即可選取一個學號。

3. 在儲存格I2中輸入公式:(以下提供三種公式來練習)

(1) 使用 VLOOKUP 函數

儲存格I2:=VLOOKUP($H$2,$A$2:$F$25,COLUMN(B:B))

複製儲存格I2,貼至儲存格I2:M2

COLUMN(B:B)=2,向右複製會產生COLUMN(C:C)=3、COLUMN(D:D)=4、…。

(在此之2、3、4、…,是指第2欄、第3欄、第4欄、…)

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

VLOOKUP:http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

 

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

 

 

 

(2) 使用 OFFSETMATCH 函數

儲存格I2:=OFFSET($A$1,MATCH($H$2,學號,0),COLUMN(A:A))

複製儲存格I2,貼至儲存格I2:M2

MATCH($H$2,學號,0):先使用 MATCH 函數,找到儲存格H2位於「學號」儲存格範圍中的第幾列。參數「0」為設定查詢學號必須完全一致者

再將這個結果代入 OFFSET 函數中,找出相對位置。

其中COLUMN(A:A)=1,向右複製會產生COLUMN(B:B)=2、COLUMN(C:C)=3、…。

(在此之2、3、4、…,是指位移2欄、位移3欄、位移4欄、…)


MATCH:http://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx 

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

OFFSET:http://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

(3) 使用 INDEX 和 MATCH 函數

儲存格I2:=INDEX($A$2:$F$25,MATCH($H$2,學號,0),COLUMN(B:B))

仿 (2) 的使用觀念,藉由 INDEX 函數在儲存格A2:F25範圍中,找出某一列的使用資料。

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

 

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

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

有網友想要知道在下圖中的 Excel 資料清單,如何計算前幾天的平均分別為多少?

本例使用 OFFSET 函數來取得相對某一儲存格的儲存格範圍,例如:

儲存格C5:=AVERAGE(OFFSET($B$3,0,0,1,ROW(1:1)))

複製儲存格C5,貼至儲存格C5:C14。

OFFSET($B$3,0,0,1,ROW(1:1)):表示儲存格B3:B3

向下複製/貼上時產生:

OFFSET($B$3,0,0,1,ROW(2:2)):表示儲存格B3:C3

OFFSET($B$3,0,0,1,ROW(3:3)):表示儲存格B3:D3

OFFSET($B$3,0,0,1,ROW(3:3)):表示儲存格B3:K3

再置入 AVERAGE 函數求得平均。

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

 

有網友問到如下圖左的 Excel 資料清單中,如果要將編號為奇數/偶數的項目,分別取出計算其平均,該如何處理?其中有部分儲存格的內容為空白。

計算平均時,如果儲存格的內容為空白,理應不併入計算。而 AVERAGE 函數,也是會將儲存格為空白儲存格者不列入平均。

先選取儲存格A1:B20,按一下 Ctrl+Shfit+F3 鍵,定義名稱:編號、數值。

【錯誤結果】

儲存格E2:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT(--(MOD(編號,2)=1))

MOD(編號,2)=1:判斷編號除以 2 的餘數是否為 1 (該數為奇數),得到一個 TRUE/FALSE 的陣列。

SUMPRODUCT((MOD(編號,2)=1)*數值):計算編號為奇數者的數值總和。

SUMPRODUCT(--(MOD(編號,2)=1)):藉由「--」運算,將計算結果為 TRUE/FALSE 的陣列轉換為 1/0 的陣列。

儲存格E3:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT(--(MOD(編號,2)=0))

MOD(編號,2)=0:判斷編號除以 2 的餘數是否為 0 (該數為偶數),得到一個 TRUE/FALSE 的陣列。

但是以上的公式,會將儲存格B4和儲存格B13這二個空白儲存格的內容視為 0,在計算平均值時會產生錯誤。

 

【正確結果】

為了將空白儲存格不在計算平均時併入計算,將公式稍做修改:

儲存格E4:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=1))

NOT(ISBLANK(數值)):透過 ISBLANK 函數來判斷數值陣列中是否為空白儲存格,得到一個 TRUE/FALSE 陣列,再藉由 NOT 函數將結果轉換為 FALSE/TRUE 陣列。

儲存格E5:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=0))

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

有網友根據這篇:Excel-計算實際年齡(年月日)-DATEDIF文章,提出了一個問題:

如果在 DATEDIF 函數中,使用參數:md(忽略年月,計算天數),當起始日為 2014/7/28,結束日分別是 2014/10/9 與 2014/11/9,結果分別是11和12,為何會有這樣的差異呢?

關於 DATEDIF 函數的語法說明如下:

DATEDIF

語法:DATEDIF(start_date,end_date,unit)

Unit說明

"Y":週期中的整年數

"M":週期中的整月數

"D":週期中的天數

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

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

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

 

依照我的推估(參考下圖),其天數差的計算可能是如下的運作:

以 1 月的 28 日和每個月的 9 日來計算天數差為例,則是先找出「迄日前一個月的28日距最後一天的天數,再加上迄日當月的天數(9)。

所以不同月份的迄日,將會在使用「md」參數時,會得到不同的天數差。

如果網友有不同的看法,歡迎給我參考!

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

有網友問到:如下圖的資料表,如何將各欄位中有「V」勾選的項目,將第一欄予以加總小計?例如:現貨的小計為 20000+5000+600+1000 = 26600。

這是一個很典型的 SUMPRODUCT 函數的應用,函數的基本語法:

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

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

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

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

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

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

 

【輸入公式】

儲存格B7:=SUMPRODUCT($A$2:$A$6,(B2:B6="V")*1)

B2:B6="V":判斷儲存格範圍中的內容是否為「V」,得列一個 TRUE/FALSE 的陣列。

image

(B2:B6="V")*1:利用「*1」運算,將 TRUE/FALSE 陣列轉換成 1/0 陣列。

image

再透過 SUMPRODUCT 函數運算:

以「現貨」欄位為例:20000X1 + 5000X1 + 600X1 + 500X0 + 1000X1 = 26600。

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

有網友問到:如何將一個資料來源清單,複製到另一個資料清單中,而且每個資料要間隔二列。(參考下圖)

(1) 假設下圖C欄中的資料,每二列有一個「空白」儲存格。

參考以下的做法:

1. 選取C欄中的資料的儲存格。

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

3. 按一下[特殊]按鈕。

4. 在[特殊目標]對話框中選取「空格」選項,按一下[確定]按鈕。

目前的狀態是每間隔二列的C欄中的儲存格已被選取:

5. 輸入公式:=OFFSET($A$2,INT((ROW(1:1)-1)/3),,,),按一下 Ctrl+Enter 鍵。

INT((ROW(1:1)-1)/3):當向下複製公式時,會產生 0,0,0,1,1,1,2,2, …。

透過 OFFSET 函數,可以取得 甲,甲,甲,乙,乙,乙,丙,丙,丙, …。因為每間隔二列只有一個儲存格被選取,所以結果如下,空格已被填入A欄的內容。

(2) 假設下圖C欄中的資料,每個儲存格已有資料。

如果儲存格中已有資料,則只能使用一個輔助欄位來重組資料了。

儲存格E2:

=IF(INT((ROW(1:1)-1)/3)=(ROW(1:1)-1)/3,OFFSET($A$2,INT((ROW(1:1)-1)/3),,,),C2)

INT((ROW(1:1)-1)/3)=(ROW(1:1)-1)/3:當向下複製時會產生 TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, …。

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

有網友問到:如何在一個 Excel 的日期清單中,只計算本週的金額小計?

【準備工作】

選取A欄至C欄中要輸入資料的儲存格,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、金額。

【輸入公式】

(1) 計算本週的第一天(星期日)

公式:=TODAY()-WEEKDAY(TODAY(),2)

(2) 計算本週的第六天(星期五)

公式=TODAY()+5-WEEKDAY(TODAY(),2)

(3) 計算本週內星期日至星期五的金額小計

儲存格E5:=SUMPRODUCT((日期>=TODAY()-WEEKDAY(TODAY(),2))*(日期<=TODAY()+5-WEEKDAY(TODAY(),2))*金額)

日期>=TODAY()-WEEKDAY(TODAY(),2):判斷日期是否大於本週第一天,傳回 TRUE/FALSE 的陣列。

日期<=TODAY()+5-WEEKDAY(TODAY(),2):判斷日期是否小於本週第六天,傳回 TRUE/FALSE 的陣列。

公式中的「*」運算子,相當於執行邏輯 AND 運算。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼