贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201704 (27)

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

很多網友對於在 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 留言(2) 人氣()

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

網友問到:根據在 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。

文章標籤

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

本篇來實作一個有趣的應用!如何在 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的數字時,即可顯示對應的數位形式的數字。

文章標籤

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

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

文章標籤

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

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

文章標籤

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

網友問到:在 Excel 中有一個時間清單資料表(如下圖),其中有一欄位是到期日,如何能讓到期日剩2天時顯示黃色提醒,剩3天時顯示綠色提醒?

假設今天的日期放在儲存格E1(=TODAY())

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

參考以下的設定:

1. 選取儲存格A2:B17。

2. 為這儲存格設定格式化的條件,如下:

規則一:

規則類型:使用公式來決定要格式化哪些儲存格。

規則:=$B2-$E$1=3(或是:=$B2-TODAY()=3)

格式:儲存格底色設為淺綠色

增規則二:

規則類型:使用公式來決定要格式化哪些儲存格。

規則:=$B2-$E$1=2(或是:=$B2-TODAY()=3)

格式:儲存格底色設為淺黃色

文章標籤

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

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

文章標籤

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

網友問到:如何根據 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))*(打卡<TIME(9,30,0))

條件:判斷打卡時間是否在上班時間的區間中。

(4) ((日期=E2)*(人員=F2)*(打卡>=TIME(7,30,0))*(打卡<TIME(9,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 找出下時間區間中的最大值。

文章標籤

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

網友問到:在 Excel 中有一個資料表(如下圖),若在第 5 列和第 6 列之間插入一列時,如何能自動產生小計公式,而不需再手動複製公式?

何如在下圖中,欄A~欄D是非公式的儲存格,欄E中有公式(=欄C×欄D)。

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

如下圖,我使用的 Excel 2013 版,當插入新的一列後,在儲存格A6~D6中輸入資料後,在儲存格D6中按下 Enter 鍵,則儲存格E6會自動產生公式,不需要再輸入公式。

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

參考以下動畫:

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

如果你的 Excel 無法完成以上的動作,也可以試著將儲存格範圍轉換為「表格」。

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

當你新增一列時,小計欄位會自動產生公式:

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

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

文章標籤

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

網友問到:在 Excel 的工作表中,如何依以下條件來處理進位?

(1) 小數介於 .01~.49:進位0.5

(2) 小數介於 .50~.99:進位1.0

Excel-自訂進位條件(INT)


【公式設計與解析】

儲存格D1:=INT(A2)+((A2-INT(A2))>=0.5)*0.5+0.5

(1) A2-INT(A2)

利用 INT 函數將儲存格A2內容取出其整數部分,A2-INT(A2)可以得到儲存格A2中的小數部分。

(2) (A2-INT(A2))>=0.5

判斷儲存格A2的小數部是否大於或等於0.5,傳回 TRUE/FALSE

(3) ((A2-INT(A2))>=0.5)*0.5

將第(2)傳回的 TRUE/FALSE 值乘以 0.5 後,得到結果 0.5/0

(即大於或等 0.5 者得到 0.5,小於 0.5 者得到 0。)

(4) INT(A2)+((A2-INT(A2))>=0.5)*0.5+0.5

將儲存格A2的整數部分+第(2)式傳回的 0.5/0+0.5,即為所求。

文章標籤

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

最近因為 Google 開放使用者可以以一般帳號來使用 Google Classroom(https://classroom.google.com)(原先只能在 G Suite 上的 Eduction 帳號使用),所以在教老師使用時,老師們要用兩個帳號(模擬老師和學生)來練習,一個帳號使用在正常的 Google Chrome,一個使用在無痕模式的 Google Chrome。

(你也可以一個帳號在 Google Chrome 使用,一個帳號在 Internet Explorer 使用)

老師無意中問到:如何能在桌面上建立無痕模式的 Google Chrome 捷徑?

先來看看,如何進入 Google Chrome 的無痕模式?

你可以在啟動 Google Chrome 後,按 Ctrl+Shift+N 鍵,即會在一個新視窗中進入無痕模式。或是使用 Google Chrome 選單中的「新增無痕視窗」指令。

如何在桌面上建立無痕模式的 Google Chrome 捷徑?

如何在桌面上建立無痕模式的 Google Chrome 捷徑?

如果你要建立無痕模式的 Google Chrome 捷徑,則可以:

1. 複製桌面上的 Google Chrome 捷徑。(本例捷徑已更名為:無痕模式)

如何在桌面上建立無痕模式的 Google Chrome 捷徑?

2. 在[無痕模式]捷徑上按右鍵,選取「內容」。

3. 在目標文字方塊中,將內容:(注意:你的檔案位置和本例不一定相同)

"C:\Program Files\Google\Chrome\Application\chrome.exe"

修改成:

"C:\Program Files\Google\Chrome\Application\chrome.exe" --incognito

(注意:新增的參數『--incognito』之前有一個空格)

如何在桌面上建立無痕模式的 Google Chrome 捷徑?

點選該捷徑,即會自動進入無痕模式的 Google Chrome。

我個人是建議要背下快速鍵,因為若在桌面上建立無痕模式捷徑,則離開了你的桌面,你就無法使用了。

然而,從選單中進入無痕模式也不會花太多時間。但是,人常常會為了幾秒鐘而不奈煩,而要求快還要更快!所以才會說科技始終來自人性(人的墮性)!

文章標籤

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

網友問到:在 Excel 中有一個資料表(如下圖),如何篩選出各個欄位指定的項目?

在下圖左中,資料有四個欄位(零件編號、外形、尺寸、售價),在儲存格G1中輸入篩選條件,要在下圖右中自動列出合於條件的資料清單(欄位:零件編號、售價)。

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)


【公式設計與解析】

1. 篩選售價高於700者

如果你使用自動篩選的功能,則可以自訂篩選條件:

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

得到篩選結果:

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)


如果你想要以公式來設計,參考以下的做法:

選取儲存格A1:D19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:零件編號、外形、尺寸、售價。

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

列出合於條件的零件編號:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(售價>=$G$1,ROW(售價),""),
ROW(1:1))-1,0),"")}

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

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

(1) IF(售價>=$G$1,ROW(售價),"")

在售價陣列中列出合於條件的列號(不合條件者傳回空字串)。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數由小至至依序找出最小值。ROW(1:1)向下複製公式:ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。

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

利用 IFERROR 函數將錯誤訊息轉換為顯示空字串(空白)。

同理:

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(售價>=$G$1,ROW(售價),""),
ROW(1:1))-1,0),"")}

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

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


2. 篩選外形為「圓」

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

自行練習:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(外形=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(外形=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}


3. 篩選尺寸為「中」

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

自行練習:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(尺寸=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(尺寸=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

文章標籤

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

網友問到:在 Excel 中有一個數列清單(如下圖),其中每個儲存格中有幾個數字,並且以「,」隔開,該如何計算每個儲存格中含有多少個數字?

Excel-計算儲存格中的數字個數(LEN,SUBSTITUTE)


【公式設計與解析】

要處理這個問題,雖然沒有可以直接使用的函數,但是利用以下技巧,也是可以快速達到效果。

儲存格B2:=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

(1) 先計算儲存格A2的字元數:LEN(A2)

(2) 將儲存格A2中的『,』消除:SUBSTITUTE(A2,",","")

(3) 計算消除『,』後的字元數:LEN(SUBSTITUTE(A2,",",""))

(4) 第(1)式-第(3)式=『,』數

(5) 數字個數=『,』數+1

文章標籤

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

1 2
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼