贊助廠商

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

搜尋本部落格文章資料

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

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

網友問到:在 Excel 中有一個資料表,如何計算出每個日期的配編數有幾類?

(請參考下圖)

image


【公式設計與解析】

為了簡化公式,特別利用二個輔助欄位。

1. 搜尋指定日期的起始列號

儲存格F2:{=MIN(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),999))}

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

2. 搜尋指定日期的結束列號

儲存格E2:{=MAX(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),0))}

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

3. 完整公式

儲存格G2:=SUMPRODUCT((1/COUNTIF(OFFSET($B$1,F2-1,0,G2-F2+1,1),
OFFSET($B$1,F2-1,0,G2-F2+1,1))))

請參考其他說明:

http://isvincent.pixnet.net/blog/post/46855459

http://isvincent.pixnet.net/blog/post/47236374

, ,

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

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?

在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?

Excel-資料交錯排列組合(OFFSET,INT,MOD)


【公式設計與解析】

(1) 處理數字部分

儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)

利用 INT 函數將除法結果再取不大於的最大整數。

公式:INT((ROW(1:1)-1)/4)

當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。

儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。

(2)處理文字部分

儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)

當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。

儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。

(3) 整合文字和數字

儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))

MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。

公式判斷,每隔四列時,會顯示數字,其餘顯示文字。

當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

, , ,

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

網友問到:在 Excel 的活頁簿中有 6 個工作表(A單位、B單位、C單位、D單位、E單位、F單位),如何計算這個 6 個工作表中相同位置儲存格的和?

這 6 個工作表的格式一致,其中「數值」欄位的內容不各有不同。

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

在下圖中,分別計算A單位~F單位的甲乙丙、丁戊己庚、辛壬癸之小計,即根據工作表名稱,來摘要各工作表的小計,該如何處理?

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)


【公式設計與解析】

儲存格B2:=SUM(INDIRECT(A2&"!B2:B4"))

先將工作表名稱(儲存格A2)和字串『!B2:B4』加以串接,再利用 INDIRECT 函數將其轉換為儲存格位址。

儲存格C2:=SUM(INDIRECT(A2&"!B5:B8"))

儲存格D2:=SUM(INDIRECT(A2&"!B9:B11"))

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

,

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

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?

如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

【公式設計與解析】

選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))

在 SUMPRODUCT 函數中利用二個陣列來運算:

LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。

MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)

公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

, , ,

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

網友根據這篇文章:Excel-計算含有部分字串的小計(SUMPRODUCT)

問到如果資料調整為下圖的代碼格式,如何計算各個代碼的加總小計?

Excel-計算含有部分字串的小計(SUMPRODUCT,LEFT,SUBSTITUTE)

【公式設計與解析】

方法一:

儲存格F2:=SUMPRODUCT((1*LEFT(代碼,3)=E2)*金額)

1*LEFT(代碼,3):利用 LEFT 函數將代碼欄位中的代碼取出左邊 3 碼(文字),利用『*1』將其轉換為數字。

1*LEFT(代碼,3)=E2:判斷取出的三碼數字是否和儲存格E2中的內容相同,傳回 TRUE/FALSE 陣列。

(1*LEFT(代碼,3)=E2)*金額:在 SUMPRODUCT 函數中,第 2 個『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。


方法二:

儲存格F2:=SUMPRODUCT((SUBSTITUTE(代碼,E2,"")<>代碼)*金額)

SUBSTITUTE(代碼,E2,"")<>代碼:利用 SUBSTITUTE 函數將代碼中的字串以空字串取代儲存格E2的內容,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(若是不相同,則傳回 TRUE,表示代碼中含有儲存格E2的內容;若是相同,則傳回 FALSE,表示代碼中沒有儲存格E2的內容。)

((SUBSTITUTE(代碼,E2,"")<>代碼)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

, , ,

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

網友提問:在 Excel 中(如下圖),如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?

在下圖中,原始資料提供年月日的欄位,而統計表中是要以統計各年月的配編數,例如年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何處理?

Excel-計算符合條件者的不重覆數量(SUMPRODUCT,COUNTIF)2


【公式設計與解析】

儲存格G2:=SUMPRODUCT((1*LEFT($A$2:$A$26,5)=F2)*(1/COUNTIF
($C$2:$C$26,$C$2:$C$26)))

(1) 1*LEFT($A$2:$A$26,5)

取出儲存格A2:A26陣列儲格中,年月日的前 5 碼文字(年月)。其中『*1』,乃將前述的 5 碼文字轉換為 5 碼數字。

(2) 1*LEFT($A$2:$A$26,5)=F2

SUMPRODUCT 函數中設定條件,用以判斷儲存格A2:A26中前 5 碼和儲存格F2是否相同。

(3) COUNTIF($C$2:$C$26,$C$2:$C$26)

SUMPRODUCT 函數中,計算儲存格C2:C26中每個數的重覆個數。例如:『1, 1, 1, 2, 2, 3, 3, 4, 4』傳回『3, 3, 3, 2, 2, 2, 2, 2, 2』

(4) 1/COUNTIF($C$2:$C$26,$C$2:$C$26)

將第(2)的傳回值取倒數,例如『3, 3, 3, 2, 2, 2, 2, 2, 2』傳回『1/3, 1/3, 1/3, 1/2, 1/2, 1/2, 1/2, 1/2, 1/2』,當在 SUMPRODUCT 函數加總時會得到結果『4』。

, ,

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

在 Excel 的公式中,SUMPRODUCT 函數被使用在需要『乘積和』的時機。在 SUMPRODUCT 函數中使用兩個陣列來執行乘積和,而兩個陣列必須要欄和欄對稱或列和列對稱。

(1) 1欄多列×1欄多列

Excel-認識SUMPRODUCT函數

儲存格B11:=SUMPRODUCT(B1:B9*D1:D9)

公式:=B1*D1+B2*D2+B3*D3+B4*D4+B5*D5+B6*D6+B7*D7+B8*D8+B9*D9

其中 SUMPRODUCT(B1:B9*D1:D9) 和 SUMPRODUCT(B1:B9,D1:D9) 的結果相同。


(2) 1欄多列×多欄多列

Excel-認識SUMPRODUCT函數

儲存格B11:=SUMPRODUCT(B1:B9*C1:F9)


(3) 1列多欄×1列多欄

Excel-認識SUMPRODUCT函數

儲存格B9:=SUMPRODUCT(B1:J1*B3:J3)


(4) 1列多欄×多列多欄

Excel-認識SUMPRODUCT函數

儲存格B13:=SUMPRODUCT(B1:J1*B2:J5)


(5) 多欄多列×多欄多列

Excel-認識SUMPRODUCT函數

儲存格B16:=SUMPRODUCT(B1:D3*E7:G9)

公式:=B1*E7+C1*F7+D1*G7+B2*E8+C2*F8+C3*F9+B3*E9+D2*G8+D3*G9

,

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

網友問到:在 Excel 的公式常會出現 ROW 函數和 COLUMN 函數,其主要的用途為何?

ROW 函數會傳回儲存格的列號,COLUMN 函數會儲存格的欄號。所以:

ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、...。

COLUMN(A:A)=1、COLUMN(B:B)=2、COLUMN(C:C)=3、...。

使用 ROW 和 COLUMN 來產生定數,尤其在複製後可以產生有規則的定數,這是非常好用的簡單方法。

觀察下圖:

Excel-認識ROW,COLUMN函數

以左圖為例:

儲存格B2:=B$1*$A2

複製儲存格B2,貼至儲存格B2:J10。

以右圖為例:

儲存格L2:=ROW(A1)*COLUMN(A1)

複製儲存格L2,貼至儲存格L2:T10。

這兩個式子的差別在於,右圖不需仰賴第1列和第A欄的數字。

其中:ROW(A1)*COLUMN(A1)=ROW(1:1)*COLUMN(A:A)

, ,

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

網友問到:在 Excel 的工作表中一個數值清單,其中每個儲存格內有3個以『,』隔開的數字,如何計算每個儲存格內數字的總和?

參考下圖左,A欄中每個儲存格有 3 個數字,並且以 2 個『,』隔開。如果以「資料剖析」工具,手動來將一個儲存格內容,調整為 3 個儲存格,再予以加總。這也是一個不錯的做法。但是,如果想要以公式來進行運算,該如何處理?如果是一個儲存格中有 4 個數字,又該如何處理?

Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)

【公式設計與解析】

1. 儲存格內有 3 個數字

儲存格B2:

{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW($1:$3)-1)*20+1,20))}

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

複製儲存格B2,貼至儲存格B2:B18。

(1) SUBSTITUTE(A2,",",REPT(" ",20))

假設每個數字都在數個位數以內,參數 20 是隨意的一個很大的數。

利用 SUBSTITUE 函數,將儲存格A2內容中的『,』全都置換為 20 個空格(REPT(" ",20)),結果如下:

image

(2) MID(第(1)式,(ROW($1:$3)-1)*20+1,20)

在陣列公式中,ROW($1:$3)={1,2,3},透過 MID 函數,藉以取出儲存格中,第 1, 21, 41 個字開始的20個字元。(本例可得:18__________________、17__________________、14__________________,其中『_』表示一個空格)

(3) 1*MID(第(1)式,(ROW($1:$3)-1)*20+1,20)

將第(2)式乘以 1,結果可以將取得的 3 個 20 字元的文字轉換為數字(本例可得:18、17、14)。

(4) SUM(1*第(2)式)

在陣列公式中,透過 SUM 函數將 3 個數字(=18、17、14)予以加總(=49)。


2. 儲存格內有 4 個數字

若儲存格內改為有 4 個數字,儲存格E2:

{=SUM(1*MID(SUBSTITUTE(D2,",",REPT(" ",20)),(ROW($1:$4)-1)*20+1,20))}

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

複製儲存格E2,貼至儲存格E2:E18。

公式原理同『1. 儲存格內有 3 個數字』,只是將公式中 ROW($1:$3) 改成 ROW($1:$4) 即可。


3. 儲存格內有 n 個數字

如果你不想管儲存格內有幾個數字,而修改儲存格公式,該如何處理?

Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)

儲存格B2:{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW(INDIRECT
("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*20+1,20))}

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

複製儲存格B2,貼至儲存格B2:B18。

將原公式 ROW($1:$3) 改為:

ROW(INDIRECT("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

如果儲存格有4個『,』,則會產生 ROW($1:$5);如果儲存格有6個『,』,則會產生 ROW($1:$7);...。

, ,

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

網友在 Excel 中想要製作大量的超連結,能夠從一個工作表,經由按一下的動作,就跳到另一個工作表的某個儲存格上,有辦法做到嗎?

參考下圖,在工作表1的儲存格A9上按一下「工作表2的A9」,就會自動跳到工作表2的儲存格A9。

Excel-大量產生連結到另一個工作表的超連結(HYPERLINK)

到達到這個效果,可以使用HYPERLINK函數來完成。參考以下的做法:

若要在工作表1中的儲存格A1(顯示:工作表2的A1)上按一下,要自動跳到工作表2的儲存格A1。輸入以下公式:

儲存格A1:=HYPERLINK("#工作表2!A"&ROW(1:1),"工作表2的A"&ROW(1:1))

複製儲存格A1,貼至儲存格A1:A15。

"#工作表2!A"&ROW(1:1):超連結的位址(置)。

當公式向下複製時,會產生「#工作表2!A1、#工作表2!A2、#工作表2!A3、….」

"工作表2的A"&ROW(1:1):超連結顯示的文字。

當公式向下複製時,會產生「工作表2的A1、工作表2的A2、工作表2的A3、…」

,

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

在使用 PowerPoint 時,如果想要將圖案中的文字分欄顯示,該如何處理?

(本篇以 PowerPoint 2013 為例)

Excel、PowerPoint-將圖案中的文字分欄顯示

如果你在 Word 文件中,將文字置入一個圖案裡,除了改變文字方向之外:

Excel、PowerPoint-將圖案中的文字分欄顯示

你還可以透過文字方塊設定其他選項:(Word)

Excel、PowerPoint-將圖案中的文字分欄顯示

在 PowerPoint 中的文字方塊選項多了一個「欄」的設定:(本例適用於 Excel)

Excel、PowerPoint-將圖案中的文字分欄顯示

選取圖案後,按一「欄」按鈕,設定分欄的數值(本例:2),即可將文字分多欄顯示。

Excel、PowerPoint-將圖案中的文字分欄顯示

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

網友在 Excel 的工作表中,想要產生重覆數字的數列(如下圖),例如:1,1,2,2,3,3,4,5,5,...,該如何處理?

在下圖中,如果選取儲存格B1:B4,再利用自動填滿方式,將會產生B欄的錯誤結果。所以,得利用公式來產生數列。

Excel-產生重覆數字的數列(ROW,INT)

【公式設計與解析】

1. 產生重覆 2 次的數列: 1,1,2,2,3,3,4,4,…

儲存格D1:=INT((ROW(1:1)-1)/2)+1

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

2. 產生重覆 n 次的數列(例如 n=5): 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,…

儲存格F1:=INT((ROW(1:1)-1)/$G$2)+1

n 置於儲存格G2,當 n 值改變時,數列隨之改變。

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

, ,

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

很多網友對於在 Excel 中,想要根據儲存格內容來顯示對應的圖片很有興趣!本篇再次介紹運用名稱定義,配合 OFFSETMATCH 等函數來顯示結果。

例如:在下圖中有七個國家的國名稱和國旗,看看如何來運用。

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

參考以下的操作:

1. 先在儲存格E1:F8中,將國名和國旗的圖片安置妥適。

2. 定義名稱:flag

參照到:=OFFSET(對照!$F$2,MATCH(對照!$B$2,對照!$E$2:$E$8,0)-1,0)

(其中「對照」是該工作表的名稱)

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

(1) MATCH(對照!$B$2,對照!$E$2:$E$8,0)

找出儲存格B2的內容在儲存格E2:E8中的位置,傳回一個數字。(公式中請使用絶對參照的寫法)

(2) OFFSET(對照!$F$2,MATCH(對照!$B$2,對照!$E$2:$E$8,0)-1,0)

利用第(1)式的傳回值,在儲存格F2起始位置查詢對應的儲存格內容。

3. 將任一個國旗圖片,複製到儲存格C2。

4. 點選該圖片,並在公式編輯列中輸入:=flag。

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

如此,只要在儲存格B2中輸入一個國家名稱,該圖片即會顯示對應的國旗照片。

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

可以如何運用呢?例如下圖:

顯示加拿大的統計圖時,同時也顯示加拿大地圖:(儲存格A1)

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

顯示韓國的統計圖時,同時也顯示韓國地圖:

Excel-根據儲存格內容顯示對應圖片(OFFSET,MATCH)

, ,

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

有網友問到:在 Excel 中的資料清單,如果想要將英文字置換成數字,例如:A:01/B:02/C:03,該如何處理?

以下圖為例,透過 SUBSTITUTE 函數將英文字置換成數字。

公式:儲存格C3

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"A","01"),"B","02"),"C","03")

Excel-大量置換英文字為數字及如何產生公式(SUBSTITUE)

但是,如果你要 26 個英文字母都要置換時,你如何輸入公式?

公式如下:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(SUBSTITUTE(A1,"A","01"),"B","02"),"C","03"),"D","04"),"E","05"),
"F","06"),"G","07"),"H","08"),"I","09"),"J","10"),"K","11"),"L","12"),"M","13"),
"N","14"),"O","15"),"P","16"),"Q","17"),"R","18"),"S","19"),"T","20"),"U","21"),
"V","22"),"W","23"),"X","24"),"Y","25"),"Z","26")

你會如何產生這個公式,慢慢輸入嗎?可能錯誤率頗高的。其實,你可以用公式來產生!

參考下圖,先建立B欄至F欄的內容,

儲存格A1:SUBSTITUTE(A1,"A","01")

儲存格A2:="SUBSTITUTE("&A1&B2&C2&D2&E2&")"

複製儲存格A2,貼至儲存格A2:A26。

Excel-大量置換英文字為數字及如何產生公式(SUBSTITUE)

,

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

有網友想要根據衛生福利部國民健康署提供的兒童與青少年生長身體質量指數(BMI)建議值來判讀其體位。本篇即利用衛生福利部國民健康署所提供的資料來練習。

資料來源:http://obesity.hpa.gov.tw/PDA/BMIproposal.aspx

image

先將資料稍微轉換如下圖:(其中的三個值分別是正常、過重和肥胖的臨界值)

image


【公式設計與解析】

儲存格J5:=CHOOSE(IFERROR(SUMPRODUCT((J4>=OFFSET(M1,
MATCH(J3,A4:A39,0)+2,(J2="女")*3,1,3))*1),0)+1,"過輕","正常","過重","肥胖")

(1) MATCH(J3,A4:A39,0)

利用 MATCH 函數查詢儲存格J3(年齡)在儲存格範圍A4:A39中的位置,傳回一個數字。

(2) OFFSET(M1,第(1)式+2,(J2="女")*3,1,3)

(J2="女")*3:若是因,傳回0;若是女,傳回3。(因為男和女的資料儲存格差3欄)

利用第(1)式傳回的位置代入 OFFSET 函數,取得符合年齡、性別的儲存格範圍(例如本例為:儲存格P13:R13)

(3) SUMPRODUCT((J4>=第(2)式)*1)

SUMPRODUCT 函數利用條件:J4>=第(2)式,來判斷儲存格J4是否大於第 1,2,3 個儲存格。傳回 TRUE/FALSE 陣列,公式中的『*1』,用以將 TRUE/FALSE 陣列轉換為 1/0 陣列。最後 SUMPRODUCT 函數予以加總,傳回一個數字(可能為 1,2,3)。

(4) IFERROR(第(3)式,0)

利用 IFERROR 函數將第(3)式可能傳回的錯誤訊息(因為體重過輕者是小於正常者)轉換為 0。(目前可能傳回的數字:1、2、3、4)

(5) CHOOSE(IFERROR(第(4)式+1,"過輕","正常","過重","肥胖")

利用第(4)式可能傳回的數字:1、2、3、4,在 CHOOSE 函數對應傳回:過輕、正常、過重、肥胖。


如果你要使用的表格式的記錄呈現,也可以改為以下的樣式:

image

, , , ,

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

在 Excel 中有一個日期和數值的資料清單,如果想要建立動態的小計查詢作業,該如何處理?

以下圖為例,希望使用下拉式清單根據起迄年月來查詢某個日期區間的小計。

Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)

註:儲存格F2公式=G2&"/"&H2&"-"&I2&"/"&J2


【公式設計與解析】

先來建立下拉式選單:

選取儲存格D2,開啟資料驗證對話框,在[設定]標籤中設定資料驗證準則:

儲存格內允許:清單;來源:=$F$2:$F$11

Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)

接著要定義名稱,選取儲存格A1:B200,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。

儲存格D10:=SUMPRODUCT((日期>=DATE(VLOOKUP(D2,F2:J11,2,FALSE),
VLOOKUP(D2,F2:J11,3,FALSE),1))*(日期<=DATE(VLOOKUP(D2,F2:J11,4,FALSE),
VLOOKUP(D2,F2:J11,5,FALSE)+1,1)-1)*數值)

公式很長挺嚇人的!看看以下的解析:

(1) 查詢起年:VLOOKUP(D2,F2:J11,2,FALSE)

透過 VLOOKUP 函數在起迄年月的清單查詢。

(2) 查詢起月:VLOOKUP(D2,F2:J11,3,FALSE)

(3) 建立起年月的第一日:DATE(第(1)式,第(2)式,1)

(4) 查詢迄年:VLOOKUP(D2,F2:J11,4,FALSE)

(5) 查詢迄月:VLOOKUP(D2,F2:J11,5,FALSE)

(6) 建立迄年月的最後一日:DATE(第(3)式,第(4)式+1,1)-1

(7) SUMPRODUCT((日期>=第(3)式)*(日期<=第(6)式)*數值)

條件一:日期>=第(3)式;條件二:日期<=第(6)式

SUMPRODUCT 函數將雙條件再乘以數值,即可利用乘積和來建立小計。


可能簡化公式?如果將項目改成如下圖的格式,其內容中每個項目的格式是固定的:

Excel-利用下拉式清單動態計算合於起迄年月的總和(SUMPRODUCT,VLOOKUP,DATE,MID)

儲存格D10:=SUMPRODUCT((日期>=DATE(MID(D2,1,4),MID(D2,6,2),1))*
(日期<=DATE(MID(D2,9,4),MID(D2,14,2)+1,1)-1)*數值)

利用 MID 函數取出起迄的年月,即可代入公式運算。完全用不到查表清單!

你要做的是妥善建立項目的內容,透過巧妙安排資料的呈現,也能簡化公式!

, , , ,

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

網友問到一個加班時數計算的問題:

依據(下圖中)所給的加班原則,再根據下班打卡時間來換算加班時間,該如何處理?

Excel-計算加班時數(TIME,VLOOKUP)


【公式設計與解析】

1. 計算加班時間

儲存格C2:=B2-TIME(18,30,0)

TIME(18,30,0):取得時間為18:30的數值。

2. 換算成分鐘數

儲存格D2:=(B2-TIME(18,30,0))*24*60

3. 換算成加班時數

儲存格E2:=VLOOKUP(D2,{1,1;51,1.5;81,2;101,2.5;131,3},2,TRUE)

利用陣列:{1,1;51,1.5;81,2;101,2.5;131,3}在 VLOOKUP 函數中查表。

複製儲存格C2:E2,往下各列貼上。

, ,

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

網友根據這篇文章:Excel-多人多項的金額統計(SUMPRODUCT練習)(參考下圖)

Excel-多人多項的金額統計(SUMPRODUCT練習)2

問到:若每個人購買的份數不一定是一份時,該如何處理?

可以換成以下的資料呈現方式:

Excel-多人多項的金額統計(SUMPRODUCT練習)2

儲存格F3:=SUM(C3:E3)

儲存格I2:=SUM(C3:C27)&"本"

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

儲存格I5:=SUMPRODUCT(C3:E27*C1:E1)

,

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

網友問到:在 Excel 中有一個時間的清單,如何分時小計?

在下圖中,每個時間有一個對應的數值,如果要以每一個小時為單位,來計算總和,該如何處理?

分時小計(SUMPRODUCT,TIME)


【公式設計與解析】

假設本題中的數值欄位的儲存格內容遠大於 1。

儲存格I2:=SUMPRODUCT((A2:E21>=TIME(7+ROW(1:1),0,0))*(A2:E21<=
TIME(8+ROW(1:1),0,0))*B2:F21)

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

(1) 條件一:A2:E21>=TIME(7+ROW(1:1),0,0)

SUMPRODUCT 函數中判斷儲存格內容是否大於或等於某一時數(本例為8:00),傳回 TRUE/FALSE 陣列。

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ …。

(2) 條件二:A2:E21<=TIME(8+ROW(1:1),0,0)

SUMPRODUCT 函數中判斷儲存格內容是否小於或等於某一時數(本例為9:00),傳回 TRUE/FALSE 陣列。

(3) SUMPRODUCT((條件一)*(條件二)*B2:F21)

公式中的『*』運算,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再和儲存格B2:F21相乘,相當於取出合於條件的「數值」。最後,透過 SUMPRODUCT 函數予以加總。

, ,

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

網友詢問:如下圖中有一個 Excel 的資料清單,每一個儲存格中有多列文字,如何能取出每個儲存格中的第一列呢?

儲存格中有多列文字,乃是因為輸入時使用 Alt+Enter 鍵加換列。

Excel-擷取儲存格中多列文字的第一列(FIND,CHAR(10)


【公式設計與解析】

儲存格C2:=LEFT(A2,FIND(CHAR(10),A2)-1)

複製儲存格C2,貼至儲存格C2:C6。

(1) FIND(CHAR(10),A2)

在 Excel 中分行的字元是 CHAR(10),所以要用 FIND 函數先尋找第一個 CHAR(10) 的位置。

(2) LEFT(A2,FIND(CHAR(10),A2)-1)

再利用 LEFT 函數取出第一個 CHAR(10) 字元左邊的文字即可。

, ,

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼