2017年4月30日 星期日

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

很多網友對於在 Excel 中,想要根據儲存格內容來顯示對應的圖片很有興趣!本篇再次介紹運用名稱定義,配合 OFFSET 和 MATCH 等函數來顯示結果。
例如:在下圖中有七個國家的國名稱和國旗,看看如何來運用。
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)

2017年4月28日 星期五

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

有網友問到:在 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)

Excel-兒童與青少年體位判讀(SUMPRODUCT,OFFSET,MATCH,CHOOSE)

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

2017年4月27日 星期四

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

在 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 函數取出起迄的年月,即可代入公式運算。完全用不到查表清單!
你要做的是妥善建立項目的內容,透過巧妙安排資料的呈現,也能簡化公式!

2017年4月25日 星期二

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

網友問到一個加班時數計算的問題:
依據(下圖中)所給的加班原則,再根據下班打卡時間來換算加班時間,該如何處理?
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,往下各列貼上。

Excel-多人多項的金額統計(SUMPRODUCT練習)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)

2017年4月24日 星期一

Excel-分時小計(SUMPRODUCT,TIME)

網友問到:在 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 函數予以加總。
 

2017年4月22日 星期六

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

網友詢問:如下圖中有一個 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) 字元左邊的文字即可。

2017年4月21日 星期五

Excel-資料重覆取最後一個(OFFSET,ROW)

網友問到:根據在 Excel 中的資料表(如下圖左),如何查詢各個款號的最後一筆資料(如下圖右)?
Excel-資料重覆取最後一個(OFFSET,ROW)

【公式設計與解析】
選取A欄~B欄中要放資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:款號、日期。
儲存格F2:{=OFFSET($B$2,MAX(IF(款號=E3,ROW(日期),""))-2,0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格F2,貼至儲存格F2:F6。
(1) IF(款號=E3,ROW(日期),"")
在陣列公式中,判斷『款號』陣列中的儲存格是否和儲存格E3相同,若是,則傳回日期儲存格的列號;若否,則傳回空字串(『""』)。
(2) MAX(IF(款號=E3,ROW(日期),""))
根據第(1)式所傳回的日期儲存格的列號,利用 MAX 函數找出最大值。(多個相同款號中的最後一個)
(3) OFFSET($B$2,MAX(IF(款號=E3,ROW(日期),""))-2,0)
將第(2)式代入 OFFSET 函數,求得對應的日期儲存格。
同理,
儲存格G2:{=OFFSET($C$2,MAX(IF(款號=E3,ROW(日期),""))-2,0)}
公式同儲存格F2,僅將起始儲存格定為儲存格C2。

2017年4月20日 星期四

Excel-製作數位顯示的數字(設定格式化的條件)

本篇來實作一個有趣的應用!如何在 Excel 的工作表中模擬數位時鐘的數字(如下圖)?
Excel-製作數位顯示的數字(設定格式化的條件)
先將數字 1,2,3,4,5,6,7,8,9,0 轉換後如下圖:
Excel-製作數位顯示的數字(設定格式化的條件)
顯示器被分成 13 個顯示區:(在下圖中已加上編號)
Excel-製作數位顯示的數字(設定格式化的條件)
若將要顯示的區域(紅色)標示為『1』,要顯示的區域(白色)標示為『0』。並依 1,2,3,4,5,6,7,8,9,0的順序以陣列表示。
以第1個顯示區為例,若用陣列表示為:{0,1,1,1,1,1,1,1,1,1}
Excel-製作數位顯示的數字(設定格式化的條件)
以第9個顯示區為例,若用陣列表示為:{0,1,0,0,0,1,0,1,0,1}
Excel-製作數位顯示的數字(設定格式化的條件)
完整的陣列:
第1個區域的陣列:{0,1,1,1,1,1,1,1,1,1}
第2個區域的陣列:{0,1,1,0,1,1,1,1,1,1}
第3個區域的陣列:{1,1,1,1,1,1,1,1,1,1}
第4個區域的陣列:{0,0,0,1,1,1,0,1,1,1}
第5個區域的陣列:{1,1,1,1,0,0,1,1,1,1}
第6個區域的陣列:{0,1,1,1,1,1,0,1,1,1}
第7個區域的陣列:{0,1,1,1,1,1,0,1,1,0}
第8個區域的陣列:{1,1,1,1,1,1,1,1,1,1}
第9個區域的陣列:{0,1,0,0,0,1,0,1,0,1}
第10個區域的陣列:{1,0,1,1,1,1,1,1,1,1}
第11個區域的陣列:{0,1,1,0,1,1,0,1,1,1}
第12個區域的陣列:{0,1,1,0,1,1,0,1,1,1}
第13個區域的陣列:{1,1,1,1,1,1,1,1,1,1}
若要將儲存格D2的內容已數位方式顯示:
Excel-製作數位顯示的數字(設定格式化的條件)
將上述陣列套入以下的公式:(D2+(D2=0)*10乃將0轉換為10)
第1個區域/儲存格C6:=CHOOSE(D2+(D2=0)*10,0,1,1,1,1,1,1,1,1,1)
第2個區域/儲存格D6:=CHOOSE(D2+(D2=0)*10,0,1,1,0,1,1,1,1,1,1)
第3個區域/儲存格E6:=CHOOSE(D2+(D2=0)*10,1,1,1,1,1,1,1,1,1,1)
第4個區域/儲存格C7:=CHOOSE(D2+(D2=0)*10,0,0,0,1,1,1,0,1,1,1)
第5個區域/儲存格E7:=CHOOSE(D2+(D2=0)*10,1,1,1,1,0,0,1,1,1,1)
第6個區域/儲存格C8:=CHOOSE(D2+(D2=0)*10,0,1,1,1,1,1,0,1,1,1)
第7個區域/儲存格D8:=CHOOSE(D2+(D2=0)*10,0,1,1,1,1,1,0,1,1,1)
第8個區域/儲存格E8:=CHOOSE(D2+(D2=0)*10,1,1,1,1,1,1,1,1,1,1)
第9個區域/儲存格C9:=CHOOSE(D2+(D2=0)*10,0,1,0,0,0,1,0,1,0,1)
第10個區域/儲存格E9:=CHOOSE(D2+(D2=0)*10,1,0,1,1,1,1,1,1,1,1)
第11個區域/儲存格C10:=CHOOSE(D2+(D2=0)*10,0,1,1,0,1,1,0,1,1,1)
第12個區域/儲存格D10:=CHOOSE(D2+(D2=0)*10,0,1,1,0,1,1,0,1,1,1)
第13個區域/儲存格E10:=CHOOSE(D2+(D2=0)*10,1,1,1,1,1,1,1,1,1,1)
接著,選取儲存格C6:F10,設定格式化的條件:
選取「使用公式來決定要格式化哪些儲存格」,規則:C6=1,儲存格底色:紅色。
Excel-製作數位顯示的數字(設定格式化的條件)
最後,要隱藏 1~13 個區域中的數字。
選取儲存格C6:E10,自訂儲存格格式為:『;;;』(三個分號)。
image
結果如下:
image
當你改變儲存格D6的數字時,即可顯示對應的數位形式的數字。

Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

網友問到:在 Excel 中有一個日期清單(如下圖左),如何依指定的月份計算加總(如下圖右)?
如下圖,由於想要計算的月份沒有規則,並且希望能使用下拉式清單來選取月份,直接得到小計結果,該如何處理?
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

【公式設計與解析】
1. 建立下拉式清單
在儲存格G2:I7中先建立想要小計的月份,欄位有:月份名稱、起始月份、終止月份。
接著,選取儲存格D2,建立「資料驗證」,設定如下:
(1) 儲存格內允許:清單
(2) 來源:=$G$2:$G$7
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

2. 設定公式
首先要設定儲存格名稱。選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。
儲存格E2:=SUMPRODUCT((MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE))*
(MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE))*數值)
(1) VLOOKUP(D2,G2:I7,2,FALSE))
利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「起始月份」。
(2) VLOOKUP(D2,G2:I7,2,FALSE))
利用 VLOOKUP 函數求得儲存格D2(下拉式清單的選項)在儲存格G2:I7範圍中,查詢得到「終止月份」。
(3) MONTH(日期)>=VLOOKUP(D2,G2:I7,2,FALSE)
判斷日期陣列中每個日期的月份是否大於或等於起始月份,傳回 TRUE/FALSE 陣列。MONTH 函數可以傳回一個日期的月份。
(4) MONTH(日期)<=VLOOKUP(D2,G2:I7,3,FALSE)
判斷日期陣列中每個日期的月份是否小於或等於終止月份,傳回 TRUE/FALSE 陣列。
(5) SUMPRODUCT(第(3)式*第(4)式*數值)
在 SUMPRODUCT 函數中計算式的『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。在 SUMPRODUCT 函數中執行二個條件和數值陣列的乘積和,結果即為所求。
如果如下:
Excel-利用下拉式清單計算多個月份的小計(SUMPRODUCT,VLOOKUP)

2017年4月19日 星期三

Excel-接近到期日的儲存格改變底色(設定格式化的條件)

網友問到:在 Excel 中有一個時間清單資料表(如下圖),其中有一欄位是到期日,如何能讓到期日剩2天時顯示黃色提醒,剩3天時顯示綠色提醒?
假設今天的日期放在儲存格E1(=TODAY())
Excel-接近到期日的儲存格改變底色(設定格式化的條件)
參考以下的設定:
1. 選取儲存格A2:B17。
2. 為這儲存格設定格式化的條件,如下:
規則一:
規則類型:使用公式來決定要格式化哪些儲存格。
規則:=$B2-$E$1=3(或是:=$B2-TODAY()=3)
格式:儲存格底色設為淺綠色
增規則二:
規則類型:使用公式來決定要格式化哪些儲存格。
規則:=$B2-$E$1=2(或是:=$B2-TODAY()=3)
格式:儲存格底色設為淺黃色
 

Excel-重覆的單字給予編號(COUNTIF)

網友問到:在 Excel 中有一個英文字詞的清單,如何對重覆出現的字詞給予編號?
例如,在下圖的 about 共有二個,所以應該標示:about 1 和 about 2。
Excel-重覆的單字給予編號(COUNTIF)

【公式設計與解析】
(1) 儲存格C2:=A2& " " & COUNTIF($A$2:A2,A2)
利用 COUNTIF 函數計算儲存格A2在資料範圍內從第一個儲存格開始出現的次數。
(2) 儲存格E2:
=IF(COUNTIF($A$2:$A$2000,A2)>1,A2&" " & COUNTIF($A$2:A2,A2),A2)
假設資料範圍是儲存格A2:A2000,若字詞僅出現一次者不予編號。
參考下圖,若是字詞出現超過一次以上者,給予的編號是二位數,該如何處理?
Excel-重覆的單字給予編號(COUNTIF)
儲存格E2:=IF(COUNTIF($A$2:$A$2000,A2)>1,A2&" "& TEXT(
COUNTIF($A$2:A2,A2),"00"),A2)
利用 TEXT(COUNTIF($A$2:A2,A2),"00") 函數,將數字加以格式為 2 碼,1~9者會變成 01~09。

2017年4月16日 星期日

Excel-如何建立含有『,』的自訂清單項目?

網友問到:在 Excel 中建立自訂清單時,清單內容如何操作,才能含有『,』?
Excel-如何建立含有『,』的自訂清單項目?
這是一個很特殊的問題,因為 Excel 在建立自訂清單的操作中,是以『,』作為清單項目間的區隔,例如要建立「101、102、103、104、105」清單,你可以在清單項目中手動輸入以五列呈現。
Excel-如何建立含有『,』的自訂清單項目?
或是輸入「101,102,103,104,105」清單項目(以『,』隔開不同項目),在按下「新增」按鈕時,即可新增這個自訂清單:
image
如今,你要產生「101,甲、102,乙、103,丙、104,丁、105,戊」清單,其中每個清單項目由兩個元素組成,並且以『,』隔開。如果你依上述操作方式來執行新增清單項目。
image
則新增後的結果是錯誤的。(Excel把『,』認定為項目的分隔符號)
image
如何解決這個問題?
參考下圖,你只要把預定的項目清單先置於 Excel 的工作表中,再於自訂清單的設定中以選取儲存格的方式來匯入項目清單。
image
結果如下:
002

Excel-依打卡時間清單找出上班和下班時間(TIME,陣列公式)

網友問到:如何根據 Excel 中的打卡資料清單(如下圖左),列出每日每個人的上班時間和下班時間(如下圖右)?
上班時間和下班時間的規範:
上班卡:介於07:00~09:30最先一個時間
下班卡:介於17:30~24:00最後一個時間
Excel-依打卡時間清單找出上班和下班時間(TIME,陣列公式)

【公式設計與解析】
選取儲存格A1:C13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、打卡。
1. 找出上班時間
儲存格G1:{=MIN(IF((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡<
TIME(9,30,0)),打卡,""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格G1,往下各列貼上。
(1) TIME(7,30,0):用以產生7:30。
(2) TIME(9,30,0)):用以產生9:30
(3) (打卡>=TIME(7,30,0))*(打卡
條件:判斷打卡時間是否在上班時間的區間中。
(4) ((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡
判斷『日期、人員和上班時間區間』三個條件是否都符合,傳回 TRUE/FALSE 陣列。其中『*』運算,相當於執行邏輯 AND 運算。
(5) IF(第(4)式,打卡,"")
在陣列公式中判斷符合第(4)式者,傳回打卡陣列,否則傳回空字串『""』。
(6) MIN(IF(第(4)式,打卡,""))
在傳回的打卡陣列中,利用 MIN 函數取出其中的最小值。

2. 找出下班時間
儲存格H1:{=MAX(IF((日期=E2)*(人員=F2)*(打卡>=TIME(17,30,0))*(打卡<=
TIME(23,59,59)),打卡,""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格H1,往下各列貼上。
原理同 1. 找出上班時間,時間區間設為 TIME(17,30,0) 和 TIME(23,59,59)。
利用 MAX 找出下時間區間中的最大值。

2017年4月15日 星期六

Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式

網友問到:在 Excel 中有一個資料表(如下圖),若在第 5 列和第 6 列之間插入一列時,如何能自動產生小計公式,而不需再手動複製公式?
何如在下圖中,欄A~欄D是非公式的儲存格,欄E中有公式(=欄C×欄D)。
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式
如下圖,我使用的 Excel 2013 版,當插入新的一列後,在儲存格A6~D6中輸入資料後,在儲存格D6中按下 Enter 鍵,則儲存格E6會自動產生公式,不需要再輸入公式。
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式
參考以下動畫:
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式
如果你的 Excel 無法完成以上的動作,也可以試著將儲存格範圍轉換為「表格」。
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式
當你新增一列時,小計欄位會自動產生公式:
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式
Excel-在含有小計欄位的資料表中新增一列時自動產生小計公式

好康東東