贊助廠商

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

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

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

網友問到:在下列的 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) 人氣()

有同仁不知道自己在 Word 文件中執行了什麼動作,導致開啟這個文件時,出現了如下圖的提示訊息,主要是說明此文件可能參照到其他檔案的連結,詢問是否要使用連結檔案的資料來更新此文件。你可以選取「是」或「否」來決定要不要連結更新。

通常你可能不知道這個訊息的形成原因,或是這個文件不是你製作的,所以也無從得知連結至那些檔案。

依據 Word 的說明文件,其描述可能發生的原因:

●使用 [選擇性貼上] 命令 (位於 [常用] 索引標籤的 [剪貼簿] 群組,按一下 [貼上] 下方的箭頭,再按一下 [選擇性貼上]),然後按一下 [貼上連結] 選項。

●使用 [物件] 命令 (在 [插入] 索引標籤的 [文字] 群組,按一下 [物件],再按一下 [物件]),然後核取 [檔案來源] 索引標籤中的 [連結至檔案] 選項。

●使用 [插入圖片] 對話方塊中的 [連結至檔案] 或 [插入與連結] 命令 (位於 [插入] 索引標籤),在 [圖例群組],按一下 圖片,然後按一下 [插入)] 旁邊的箭號。

如果想要控制 Word 在檔案開啟時自動更新連結,可以選取[檔案/選項]功能中的「進階」標籤。在 [一般] 區域下,取消選取「開啟舊檔時自動更新連結」核取方塊,如此再次開啟這個文件時,就不會再出現提示訊息。(原來的檔案的連結功能依然存在)

如果你真的想要找出檔案連結的位置,則可以試試顯示「功能變數」:

1. 若要顯示或隱藏特定功能變數的功能變數,則在功能變數結果上按 Shfit+F9 鍵。

2. 若要顯示或隱藏文件中所有欄位的功能變數,則按 Alt+F9 鍵。

如此,便可以試著找到檔案的連結位置,再進行進一步的處理。

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

有網友問到:參考下圖的金額清單中,因為前三碼是幣別,如果要取出金額的數字部分,並且給予小計加總,該如何處理?

【輸入公式】

儲存格D2:=SUMPRODUCT((LEFT($A$2:$A$25,3)=C2)*(VALUE(RIGHT($A$2:$A$25,LEN($A$2:$A$25)-3))))

複製儲存格D2,貼至儲存格D2:D7。

LEFT($A$2:$A$25,3)=C2:使用 LEFT 函數取得在金額清單中儲存格前三碼,並且判斷是否和儲存格C2中的幣別相同,得到一個 TRUE/FALSE 的陣列。

RIGHT($A$2:$A$25,LEN($A$2:$A$25)-3):使用 RIGHT 函數取得金額清單中儲存格前三碼之後的數字部分。

再使用 VALUE 函數將上式的數字部分(字串),轉換型態為數字。

最後透過 SUMPRODUCT 函數將以上二式相乘加總,即為所求。其中 TRUE/FALSE 陣列在運算時會轉換為 1/0 陣列。

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

網友提問了一個實用的問題:參考下圖,想要在一個下拉式清單中選取某一月份時,在第二個下拉式清單中只出現該月份清單中的日期;並且在輸入時間後,比對日期對應的起姳/終止時間,如果不在範圍內則以不同色彩標示出來。

這個問題看起來有點複雜,可能對某些讀者而言會有些難度,以下就盡量詳細說明解釋:

【準備工作】

選取儲存格C1:C17,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:一月。

選取儲存格D1:D15,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:二月。

選取儲存格E1:E16,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:三月。

選取儲存格F1:F17,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:四月。

【操作步驟】

1. 選取儲存格A2。

2. 選取功能表[資料/資料工具/資料驗證]選項。

3. 在[資料驗證]對話框中的[設定]標籤中設定:

儲存格內允許:清單/來源:=$C$1:$F$1。

當選取儲存格A2時,即可使用下拉式清單,其清單項目即為:一月、二月、三月、四月。

4. 選取儲存格A5。

5. 選取功能表[資料/資料工具/資料驗證]選項。

6. 在[資料驗證]對話框中的[設定]標籤中設定:

儲存格內允許:清單/來源:=INDIRECT(A2)。

使用 INDIRCET 函數將代入的參數:一月、二月、三月、四月(先前已經定義為名稱),轉換為儲存格範圍,例如:二月為儲存格D2:D15。

7. 選取儲存格A8。

8. 選取功能表[常用/樣式/設定格式化的條件]選項。

9. 選取「新增規則」選項。

10. 新增一條規則:便用公式來決定要格式化哪些儲存格

(1) 輸入公式:=NOT((A8>=OFFSET(I$1,A5,0))*(A8<=OFFSET(J$1,A5,0)))

OFFSET(I$1,A5,0):根據儲存格A5的內容,對照由儲存格I1為時間起始值。

OFFSET(J$1,A5,0):根據儲存格A5的內容,對照由儲存格J1為時間終止值。

(A8>=OFFSET(I$1,A5,0))*(A8<=OFFSET(J$1,A5,0)):其中的「*」相當於執行 AND 邏輯運算,表示判斷儲存格A8是否介於起始值和終止值之間。

再以 NOT 運算子表示判斷儲存格A8是否「」介於起始值和終止值之間。

(2) 設定格式:紅色字。

本例的日期為二月4日時間為「17」,不是在 8 至 16 之間,所以顯示紅色字。

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

有網友問到在以下的日期清單中(參考下圖),如果工時超過 8 小時的部分算為加班,如何分開計算平時和假日的加班?又如何在一個儲存格中即算出加班的總和?

【計算方式一】

(1) 計算平時加班時數

儲存格D2:=IF(WEEKDAY(A2,2)<6,C2-8,0)

WEEKDAY(A2,2)<6:判斷日期是否為「平時」,其中 WEEKDAY 函數的參數 2,代表星期一傳回 1,星期二傳回 2,…,星期六傳回 6,星期日傳回 7。

image

(2) 計算假日加班時數

儲存格E2:=IF(WEEKDAY(A2,2)>5,C2-8,0)

(3) 計算平時加班時數總和

儲存格G2:=SUMPRODUCT((WEEKDAY(A2:A32,2)<6)*(C2:C32-8))

(4) 計算假日加班時數總和

儲存格H2:=SUMPRODUCT((WEEKDAY(A2:A32,2)>5)*(C2:C32-8))

 

【計算方式二】

(1) 計算平時加班時數

儲存格D2:=SUMPRODUCT((WEEKDAY(A2,2)<6)*(C2-8))

WEEKDAY(A2,2)<6:判斷日期是否為「平時」,其中 WEEKDAY 函數的參數 2,代表星期一傳回 1,星期二傳回 2,…,星期六傳回 6,星期日傳回 7。

(2) 計算假日加班時數

儲存格E2:=SUMPRODUCT((WEEKDAY(A2,2)>5)*(C2-8))

(3) 計算平時加班時數總和

儲存格G2:{=SUM(IF(WEEKDAY(A2:A32,2)<6,C2:C32-8,FALSE))}

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

(4) 計算假日加班時數總和

儲存格H2:{=SUM(IF(WEEKDAY(A2:A32,2)>5,C2:C32-8,FALSE))}

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

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

最近同仁從網路書店搜集到一些購書的資料放在 Excel 的工作表中(參考下圖左),資料是直式的清單,不知如何轉換為表格形式來呈現(參考下圖右)?

這個例子,要使用 OFFSET 函數試試看!

【輸入公式】

(1) 儲存格C2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(A:A)-1,0),"","")

(ROW(1:1)-1)*5:在第 1 列時為 0,第 2 列時為 5,第 3 列時為 10,…。

(ROW(1:1)-1)*5+COLUMN(A:A)-1:在 C 欄的第 1 列為 0,往右複製到 D 欄時為 1,往右複製到 E 欄時為 2,往右複製到 F 欄時為 3。

使用 SUBSTITUTE 函數的用意是要將傳回結果為 0 者(在清單中沒有的資料的儲存格)改以空白顯示。

(2) 儲存格D2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(B:B)-1,0),"作者:","")

使用 SUBSTITUTE 函數將「作者:」置換為空字串。

(3) 儲存格E2:

=SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(C:C)-1,0),"出版社:","")

使用 SUBSTITUTE 函數將「出版社:」置換為空字串。

(4) 儲存格F2:

=SUBSTITUTE(SUBSTITUTE(OFFSET($A$1,(ROW(1:1)-1)*5+COLUMN(D:D)-1,0),"定價:",""),"元","")

使用 SUBSTITUTE 函數將「定價:」和「元」置換為空字串。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼