贊助廠商

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

搜尋本部落格文章資料

在 Excel 中常見到在一堆資料中要找出數個符合的項目,參考下圖。將檢核結果符合者,給予「V」記號,該如何處理?

 

為了讓學生多多練習,提供四種方式來操作,複製儲存格B2,往下各列貼上:

(1) 儲存格B2:=IF(ISNA(VLOOKUP(A2,$D$2:$D$9,1,FALSE)),"","V")

VLOOKUP(A2,$D$2:$D$9,1,FALSE):查詢儲存格A2的內容是否為符合的項目,若不是會得到一個錯誤訊息「#N/A」。

再透過 ISNA 函數,得到一個 True/False 結果。如果為 True,則輸出空白,否則輸出「V」。

 

(2) 儲存格B2:=IF(ISNA(MATCH(A2,$D$2:$D$9,0)),"","V")

MATCH(A2,$D$2:$D$9,0):查詢儲存格A2的內容在項目資料中的第幾列,若不在項目中則得到一個錯誤訊息「#N/A」。

再透過 ISNA 函數,得到一個 True/False 結果。如果為 True,則輸出空白,否則輸出「V」。

 

(3) 儲存格B2:=IF(SUMPRODUCT(--(A2=$D$2:$D$9)),"V","")

(A2=$D$2:$D$9):比對儲存格A2的內容在項目資料中是否有相符的 True/Fasle 陣列。「--」運算可以將 True/Fasle 陣列轉換為 1/0 陣列。如果結果為「1」,表示有相符的項目,結果為「0」,表示沒有相符的項目。

IF 函數中的條件判斷式,1 和 True 同義、0 和 False 同義,如果絛件為 True,則輸出空白,否則輸出「V」。

 

(4) 儲存格B2:{=IF(OR(A2=$D$2:$D$9),"V","")}

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

(A2=$D$2:$D$9):比對儲存格A2的內容在項目資料中是否有相符的 True/Fasle 陣列。OR 函數的參數中,只要有一個為 True,則結果為 True,否則為 False

IF 函數中的條件判斷式,1 和 True 同義、0 和 False 同義,如果絛件為 True,則輸出空白,否則輸出「V」。

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

在 Excel 中輸入資料,預設值為按下 Enter 鍵後移至同欄的下一列儲存格。同事問到,如何能向下輸入、向上輸入、向右輸入、向左輸入呢?

有一些簡單的技巧,可能常被忽略了。你可以選取一段儲存格範圍,參考以下有四種做法:

(1) 輸入資料後按 Enter 鍵,作用儲存格的移動方向為:由上往下,由左至右。

(2) 輸入資料後按 Shift + Enter 鍵,作用儲存格的移動方向為:由下往上,由右至左。

(3) 輸入資料後按 Tab 鍵,作用儲存格的移動方向為:由左往右,由上至下。

(4) 輸入資料後按 Shift + Tab 鍵,作用儲存格的移動方向為:由右往左,由下至上。

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

在 Excel 中常會使用到「日期/時間」的表示,所以日期/時間的格式設定相當重要。在儲存格的格式設定之「數值/自訂」中,可以輸入自訂的格式:

整理了一些常用的格式如下:

【日期格式】

格式 功能
m 將月份顯示為數字,前面不補零。
mm 將月份顯示為數字,適當時前面補零。
mmm 將月份顯示為縮寫 (Jan 至 Dec)。
mmmm 顯示完整月份名稱 (January 至 December)。
mmmmm 將月份顯示為單一字母 (J 至 D)。
d 將日期顯示為數字,前面不補零。
dd 將日期顯示為數字,適當時前面補零。
ddd 將星期顯示為縮寫 (Sun 至 Sat)。
dddd 顯示完整星期名稱 (Sunday 至 Saturday)。
yy 將年份顯示為兩位數字。
yyyy 將年份顯示為四位數字。
gg 顯示民國
ggg 顯示中華民國
aaa 以週日~週六表示
aaaa 以星期日~星期六表示

 

【日期格式範例】

原始 顯示 代碼
1-12 m
01–12 mm
Jan–Dec mmm
January–December mmmm
J–D mmmmm
1-31 d
01–31 dd
星期 Sun–Sat ddd
星期 Sunday–Saturday dddd
00–99 yy
1900-9999 yyyy

 

【時間格式】

格式 功能
h 會將時顯示為數字,前面不補零。
[h] 會以時顯示經過的時間。如果使用會傳回時間的公式,其中時超過 24,則使用 [h]:mm:ss 的數字格式。
hh 會將時顯示為數字,適當時前面會補零。如果格式包含 AM 或 PM,則為 12 小時制,否則就是 24 小時制。
m 會將分顯示為數字,前面不補零
  註:m 或 mm 代碼必須緊跟在 h 或 hh 代碼之後,或緊接在 ss 代碼之前;否則,Excel 會顯示月份,而不是顯示分。
[m] 會以分顯示經過的時間。如果使用會傳回時間的公式,其中分超過 60,則使用 [mm]:ss 的數字格式。
mm 會將分顯示為數字,適當時前面會補零
  附註:m 或 mm 代碼必須緊跟在 h 或 hh 代碼之後,或緊接在 ss 代碼之前;否則,Excel 會顯示月份,而不是顯示分。
s 會將秒顯示為數字,前面不補零
[s] 會以秒顯示經過的時間。如果您使用會傳回時間的公式,其中秒數超過 60,請使用像這樣 [ss] 的數字格式。
ss 會將秒顯示為數字,適當時前面會補零。如果要顯示秒的分數,則使用 h:mm:ss.00 的數字格式。
AM/PM, am/pm, A/P, a/p 會使用 12 小時制來顯示小時。如果是午夜到中午的時間,Excel 會顯示 AM、am、A 或 a;如果是中午到午夜的時間,則會顯示 PM、pm、P 或 p。

 

【時間格式範例】

原始 顯示 代碼
0-23 h
00–23 hh
0-59 m
00–59 mm
0-59 s
00–59 ss
時間 4 AM h AM/PM
時間 4:36 PM h:mm AM/PM
時間 4:36:03 P h:mm:ss A/P
時間 4:36:03.75 h:mm:ss.00
經過的時間 (時與分) 1:02 [h]:mm
經過的時間 (分與秒) 14:24 [mm]:ss
經過的時間 (秒與百分之一秒) 216.00 [ss].00

 

【日期時間綜合範例】

顯示結果 自訂格式
週三 [$-404]aaa
星期三 [$-404]aaaa
下午 01:30:55 [$-404]AM/PM hh:mm:ss
90314 [$-404]e"年"m"月"d"日"
90/3/14 [$-404]e/m/d
民國90314 [$-404]gge"年"m"月"d"日"
中華民國90314 [$-404]ggge"年"m"月"d"日"
14-Mar [$-409]d-mmm
14-Mar-01 [$-409]d-mmm-yy
1:30 PM [$-409]h:mm AM/PM
1:30:55 PM [$-409]h:mm:ss AM/PM
M [$-409]mmmmm
M-01 [$-409]mmmmm-yy
2001/3/14 1:30 PM [$-409]yyyy/m/d h:mm AM/PM
下午 01:30:55 [$-F400]h:mm:ss AM/PM
2001314 [$-F800]dddd, mmmm dd, yyyy
九十年三月十四日 [DBNum1][$-404]e"年"m"月"d"日"
民國九十年三月十四日 [DBNum1][$-404]gge"年"m"月"d"日"
中華民國九十年三月十四日 [DBNum1][$-404]ggge"年"m"月"d"日"
三月十四日 [DBNum1][$-404]m"月"d"日"
二○○一年三月十四日 [DBNum1][$-404]yyyy"年"m"月"d"日"
13:30:55 [h]:mm:ss
14-Mar d-mmm
14-Mar-01 d-mmm-yy
Mar-01 mmm-yy
Mar-2001 mmm-yyyy
2001年3月14日 yyyy""m""d""
2001/3/14 yyyy/m/d
2001/3/14 13:30 yyyy/m/d h:mm
2001/3/14 13:30 yyyy/m/d hh:mm
2011/3/14 yyyy/m/d
1330 h"時"mm"分"
133055 h"時"mm"分"ss"秒"
13:30:55 h:mm:ss
13:30 h:mm
13時30分 hh""mm""
13時30分55秒 hh""mm"""
13:30 hh:mm
01:30 PM hh:mm AM/PM
13:30:55 hh:mm:ss
01:30:55 PM hh:mm:ss AM/PM
3月14日 m""d""
3/14/01 m/d/yy
3/14 m/d
03/14/01 mm/dd/yy
30:55 mm:ss
01:30:55 PM mm:ss.0
下午130 上午/下午h"時"mm"分"
下午13055 上午/下午h"時"mm"分"ss"秒"
下午01時30分 上午/下午hh""mm""
下午01時30分55秒 上午/下午hh""mm""ss""

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼