贊助廠商

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

搜尋本部落格文章資料

網友又問到最多人提問的問題,我想也是使用需求最高的問題。在 Excel 中如何根據某一欄的內容來查詢對應的另一欄中的內容?

如下圖,希望根據A欄的日期來查詢B欄的數值,該如何處理?

Excel-多種查表方式(VLOOKUP,MATCH,INDEX,OFFSET,ADDRESS,INDIRECT)

假設日期已排序,日期範圍由儲存格A2(2015/1/1)至儲存格A366(2015/12/31)。

查表的概念是在一個表格(A2:B366)中,由第一欄(A欄)來查詢某個日期的所在位置,再找出第二欄(B欄)中的『對應』數值,通常在第一欄(A欄)來查詢某個日期的所在位置時會傳回一數值(位於第幾個;在本例中這個數值會和列號相差 1,因為第一個日期由第 2 列開始)。

Excel-多種查表方式(VLOOKUP,MATCH,INDEX,OFFSET,ADDRESS,INDIRECT)

以下提供 5 種公式運用,都是基本公式的應用,您可以依樣畫葫蘆的套用。

儲存格F2:=OFFSET(B1,MATCH(E2,A2:A366,0),0)

儲存格F2:=INDEX(A2:B366,MATCH(E2,A2:A366,0),2)

儲存格F2:=VLOOKUP(E2,A2:B366,2,FALSE)

儲存格F2:=LOOKUP(E2,A2:A366,B2:B366)

儲存格F2:=INDIRECT(ADDRESS(MATCH(E2,A2:A366,0)+1,2))

若想要進一步的說明,可以參考:

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

Excel-查表的應用(INDEX,OFFSET,MATCH,VLOOKUP)

或是使用以下超連結:

http://isvincent.pixnet.net/blog/search/查表

會有超過 100 篇的相關文章供查詢。

Excel-多種查表方式(VLOOKUP,MATCH,INDEX,OFFSET,ADDRESS,INDIRECT)

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

有同仁問到:在 Excel 的工作表中,常會由其他來源帶入資料,但是內容是文字或數字,常會一時間看不出來,卻又造成運算結果的錯誤,該如何處理?

討論之餘,或許可以試試以下方式:利用設定格式化的條件來輔助檢查。

參考下圖:分別檢查儲存格為『數字、邏輯值、文字、錯誤訊息』。

Excel-檢視儲存格內容的屬性(數字、文字、邏輯值等)

在本例中,選取儲存格A1:D17。利用以下四個條件新增四個規則:

條件1格式:淺黃色;公式:=ISNUMBER(A1)

條件2 格式:粉紅色;公式:=ISLOGICAL(A1)

條件3 格式:淺藍色;公式:=ISTEXT(A1)

條件4 格式:淺綠色;公式:=ISERROR(A1)

Excel-檢視儲存格內容的屬性(數字、文字、邏輯值等)

以本例來看,儲存格A1,A2,A3顯然是錯誤的內容(顏色不對):

儲存格A1看以數字123,其實內容是文字的『123』,相當於『'123』;

儲存格A2看似空白儲存格,其實內容是空字串,相當於『=""』;

儲存格A3看似邏輯值TRUE,真實內容是文字的TRUE,相當於『="TRUE"』。

如此便可稍加檢查儲存格內容是否和你想要的一致。

Excel-檢視儲存格內容的屬性(數字、文字、邏輯值等)

而 Excel 本身也提供一些檢查機制,輔助你、提醒你注意到可能的錯誤。(下圖的56被標示為粉紅色文字)

Excel-檢視儲存格內容的屬性(數字、文字、邏輯值等)

而在 Excel 的選項中,你可以勾選想要 Excel 執行的錯誤檢查規則:

例如『格式化為文字或以單引號開頭的數字』這一項,將有助於辨識真的數字,還是文字格式的數字。

Excel-檢視儲存格內容的屬性(數字、文字、邏輯值等)

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼