歐飛斯 Office 教學: 2008/8/10

VLOOKUP,HLOOKUP,LOOKUP 資料查詢

VLOOKUP 在表格陣列的最左邊欄中搜尋某個數值，並傳回該表格陣列中同一列之其他欄中的數值。 VLOOKUP 中的「V」代表「垂直」。

語法

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)
VLOOKUP(查詢值,查詢表,傳回相對應第n欄的資料,查詢方式)

Lookup_value 在Table_array的最左邊欄中搜尋Lookup_value ，可以是值也可以是參照位址。

Table_array 被搜尋資料且有兩欄以上的資料表格。通常table_array是個儲存格範圍的參照位址或範圍名稱，最左邊欄中的值可以是文字、數字或邏輯值 (不分大小寫)。

table_array 第一欄中的值必須以遞增順序排序；否則，VLOOKUP 可能無法提供正確的值。

Col_index_num 是個數字，代表要傳回的值位於 table_array 列中的第幾欄。如果 col_index_num 引數值為 1，則傳回 table_array 第一欄中的值；如果 col_index_num 引數值為 2，則傳回 table_array 第二欄中的值，依此類推。

Range_lookup 一個邏輯值，用來指定 VLOOKUP 應該要尋找完全符合(1)還是部分符合(0)的值：

如果此引數值為 TRUE 或被省略了，則傳回完全符合或部分符合的值。如果找不到完全符合的值，將會傳回僅次於 lookup_value 的值。
如果此引數值為 FALSE ，則 VLOOKUP 函數只會尋找完全符合的值。在此情況下，table_array 第一欄中的值便不需要排序。如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符，將會使用第一個找到的值。如果找不到完全符合的值，則傳回錯誤值 #N/A。

按我觀看範例

HLOOKUP 在表格的最上方列中尋找含有某特定值的欄位，再傳回該表格陣列中同一欄之其他列中的數值。H 在 HLOOKUP 中表示 "水平"。

語法

HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)
HLOOKUP(查詢值,查詢表,傳回相對應第n列的資料,查詢方式)

Lookup_value 在Table_array的最左邊欄中搜尋Lookup_value。lookup_value 可以是數值、參照位址或文字串。

Table_array 被搜尋資料且有兩列以上的資料表格。通常table_array是個儲存格範圍的參照位址或範圍名稱，最上方列中的值可以是文字、數字或邏輯值 (不分大小寫)。

Table_array 第一列中的值可以是文字、數字或邏輯值。
如果 range_lookup 為 TRUE，則 table_array 第一列中的數值必須按照遞增次序排列：...-2，-1，0，1，2，...，A-Z，FALSE，TRUE；否則 HLOOKUP 函數找出的值不一定正確。如果 range_lookup 為 FALSE，table_array 就不須排序。
字母的大小寫被視為是相同的。

Row_index_num 是個數字，代表要傳回的值位於 table_array 列中的第幾列。如果 row_index_num 引數值為 1，傳回 table_array 裡第一列的值，如果 row_index_num 引數值為 2，傳回 table_array 裡第二列的值，依此類推。

Range_lookup 一個邏輯值，用來指定 VLOOKUP 應該要尋找完全符合(1)還是部分符合(0)的值。

如果此引數值為 TRUE 或被省略了，則傳回完全符合或部分符合的值。如果找不到完全符合的值，將會傳回僅次於 lookup_value 的值。
如果此引數值為 FALSE ，則 HLOOKUP 函數只會尋找完全符合的值。在此情況下，table_array 第一欄中的值便不需要排序。如果 table_array 第一欄中有兩個以上的值與 lookup_value 相符，將會使用第一個找到的值。如果找不到完全符合的值，則傳回錯誤值 #N/A。

按我觀看範例

LOOKUP 函數有兩種語法形式：

向量向量形式的 LOOKUP 函數會在一列或一欄的範圍中 (亦稱為向量) 尋找值並且從第二個一列或一欄範圍的相同位置中傳回值。

向量形式的 LOOKUP 函數會在一列或一欄的範圍中 (亦稱為向量) 尋找值並且從第二個一列或一欄範圍的相同位置中傳回值。當您想要指定包含您要比對的值的範圍，請使用這個形式的 LOOKUP 函數。其他形式的 LOOKUP 函數會自動尋找第一欄或列。

語法

LOOKUP」(Lookup_value,Lookup_vector,Result_vector)

Lookup_value LOOKUP 函數在第一個向量中搜尋的值。Lookup_value 可為數字、文字、邏輯值，或是指向某值的名稱或參照位址。

Lookup_vector 僅包含一列或一欄的範圍。lookup_vector 中的值可為文字、數字，或邏輯值。

Result_vector 僅包含一列或一欄的範圍，而且大小一定與 lookup_vector 相同。

註解

如果 LOOKUP 函數找不到 lookup_value，就會比對 lookup_vector 中，小於或等於 lookup_value 的最大值。

如果 lookup_value 小於 lookup_vector 中的最小值，LOOKUP 函數會提供 #N/A 錯誤值。

陣列陣列形式的 LOOKUP 函數則會在陣列的第一列或第一欄尋找指定值，然後從陣列中最後一列或最後一欄的相同位置中傳回值。

陣列形式的 LOOKUP 函數會在的第一個欄或列中尋找指定值，然後從陣列中最後一列或最後一欄的相同位置中傳回值。當您想要比對的值位在陣列第一列或第一欄時，請使用這個形式的 LOOKUP 函數。當您想要指定欄或列的位置時，請使用其他形式的 LOOKUP 函數。

語法

LOOKUP(Lookup_value,Array)

Lookup_value LOOKUP 函數在陣列中搜尋的值。Lookup_value 可為數字、文字、邏輯值，或是指向某值的名稱或參照位址。

如果 LOOKUP 函數找不到 lookup_value，就會使用陣列中小於或等於 lookup_value 的最大值。

如果 lookup_value 小於第一列或第一欄 (視陣列的尺寸而定) 中的最小值，則 LOOKUP 函數會傳回 #N/A 錯誤值。

Array 包含文字、數字，或您要與 lookup_value 比較的邏輯值之儲存格範圍。陣列中的值必須以遞增順序排列。

陣列形式的 LOOKUP 函數與 HLOOKUP 和 VLOOKUP 函數非常類似；其差異是

HLOOKUP 會在第一列搜尋 lookup_value
VLOOKUP 會在第一欄搜尋 lookup_value
LOOKUP 會根據陣列的尺寸選擇第一列或第一欄的搜尋

陣列區域欄比列多，則 LOOKUP 函數會在第一列中搜尋 lookup_value。
陣列是正方形或是列比欄多，則 LOOKUP 函數會在第一欄中搜尋。
HLOOKUP 和 VLOOKUP，您可以向下或橫向索引，但是 LOOKUP 總是選取欄或列的最後一個值。

COUNTIF 計算符合條件的儲存格

常常我們在分析大量資料時，需要知道在某條件下有多少筆資料是符合的，例如，問卷這一類的統計，這時我們就可以用 COUNTIF 函數來找出符合條件的儲存格筆數有多少？

COUNTIF 計算某範圍內符合準則的儲存格數量。

語法

COUNTIF(Range,Criteria)

Range 是您想計算符合準則之儲存格個數的儲存格範圍。

Criteria 是用以決定要將哪些儲存格列入計算的準則，可以是數字、表示式、儲存格參照或文字。例如，Criteria 可以是 32、"32"、">32"、"apples" 或 B4。

範例：計算「表決人數」與「投票比率」

對於10人的表決中，共有4位是贊成的（YES），所以要利 COUNTIF 函數在 B2:B11 這個儲存格範圍中找出 YES 的記錄並統計其筆數共幾筆。

第一個引數當然就是範圍，有了範圍，函數才能從範圍中依條件來計算，因此接著需要第二個引數，也就是條件，而條件就是記錄著 YES 的儲存格，所以，

D4 = COUNTIF(B2:B11,"YES")

條件的部分，因 YES 是文字，所以記得一定要加雙引號，另外，如果您的條件是一個比較式（如 ">60"、"<100"...），也一定要記得加雙引號。但在函數視窗中，此類的比較式會自動加上雙引號。反對人數與未投票人數的公式也相同（見上圖）。

再來計算投票率。投票率是將『投票人數 / 總人數』所得到的百分比。我們可以利用 COUNTA 來計算範圍中非空白的儲存格數目，得到的值就是投票的人數。另外總人數的部分可以用 COUNT 來計算 A 欄中有數字的儲存格就可以知道總人數是多少人。圖中也另外提供了另一個做法，讓您可以想一想。

COUNT 計算含有數字的儲存格數量，以及引數清單中的數字。使用 COUNT 可以在一個數字陣列或範圍中取得一個數字欄位內的項目數量。

語法 COUNT(value1,value2,...)

Value1, value2, ... 是 1 到 30 個引數，裡面可能含有或參照到不同類型的資料，但只計算數字部份。可以列入計算的引數有數字、日期或代表數字的文字。不列入計算的引數有錯誤值，以及不能轉換成數字的文字。

COUNTA 計算不是空白的儲存格數量，以及引數清單中的數值。使用 COUNTA 可以在一個範圍或陣列中計算出裡面含有資料的儲存格的數量。

語法 COUNTA(value1,value2,...)

Value1, value2, ... 係指 1 到 30 個引數，代表您要計算的值。在這種情況下，此值可能是任何資訊，包含空文字 ("")，但是不包含空白的儲存格。

COUNTBLANK 計算指定範圍內空白儲存格的個數。

語法 COUNTBLANK(range)

Range 要計算空白儲存格的範圍。如果儲存格裏的公式結果為 "" (或空字串)，此儲存格仍會被計算在內。但是如果是零則不予計算。

NOW,TIME(HOUR,MINUTE,SECOND) 時間函數

日期函數在 EXCEL 計算中也是常用的，尤其是在計算人事出勤表、生產製程效率...等。

日期是一個序列值，預設序列值是將 1900 年 1 月 1 日當成 1 ，2008 年 1 月 1 日的序列值為 39448。因此 2008 年 1 月 1 日的日期是在 1900 年 1 月 1 日的 39448 天之後。

時間值是日期值的一部份，而且它是由小數點數字來表示，序列值小數點右邊的數字代表時間；左邊的數字代表日期。例如，如果為半天，則 12:00 PM 是以 0.5 來表示。

NOW 傳回現在的日期與時間。此值是 Microsoft Excel 從電腦系統中抓取出的現在的日期與時間值。

語法 NOW( )

HOUR 傳回時間值的小時。小時必須用整數指定，有效範圍是 0 (12:00 A.M.) 到 23 (11:00 P.M.)。

語法

HOUR(serial_number)

Serial_number 您要尋找的時間（包含小時）。

例如，HOUR("4:48:18 PM") 會得到小時 16 （因為是下午時間）。

MINUTE 傳回分鐘時間值。minute 為介於 0 到 59 之間的整數。

語法

MINUTE(serial_number)

Serial_number 是您要尋找的時間（包含分鐘）。

例如，MINUTE("4:48:18 PM") 會得到小時 48 。

SECOND 傳回時間序列的秒數。秒數是個從 0 (零) 到 59 的整數。

語法

SECOND(serial_number)

Serial_number 為您要尋找的時間 (包含秒數)。

例如，SECOND("4:48:18 PM") 會得到小時 18 。

TIME 傳回引數中代表特定時間的小數。

由 TIME 函數所傳回的時間小數位數值是個介於 0 (零) 到 0.99999999 之間的值，對應著由 0:00:00 (12:00:00 A.M.) 到 23:59:59 (11:59:59 P.M.) 的時間。

語法

TIME(Hour,Minute,Second)

Hour 代表小時的數字，範圍從 0 (零) 到 23。

Minute 代表分鐘的數字，範圍從 0 到 59。

Second 代表秒鐘的數字，範圍從 0 到 59。

例如，TIME(15,4,32) 會得到日期為 03:04:32 PM（此值需要自訂數值格式；預設值03:04 PM）。

TODAY,DATE(YEAR,MONTH,DAY) 日期函數

日期函數在 EXCEL 計算中也是常用的，尤其是在計算人事資料中的年資、勞健保的投保天數...等。

日期是一個序列值，預設序列值是將 1900 年 1 月 1 日當成 1 ，2008 年 1 月 1 日的序列值為 39448，因此 2008 年 1 月 1 日的日期是在 1900 年 1 月 1 日的 39448 天之後。

TODAY 傳回今天的日期。此值是 Microsoft Excel 從電腦系統中抓取出的日期值。

語法 TODAY( )

YEAR 傳回 serial_number 日期所對應的年份。年份傳回介於 1900 到 9999 之間的整數。

語法 YEAR(serial_number)

Serial_number（序列值） 您要尋找的年份的日期。如YEAR(2008/6/8) 會得到年份 2008 。

MONTH 傳回 serial_number 所對應的月份。月份數為介於 1 （1月）到 12（12月）之間的整數。

語法 MONTH(serial_number)

Serial_number（序列值） 您要尋找的月份的日期。如MONTH(2008/6/8) 會得到月份 6 。

DAY 傳回 serial_number 所對應的日期天數。日數的有效範圍是 1 到 31 之間的整數。

語法 DAY(serial_number)

Serial_number（序列值） 您要尋找的天數的日期。如DAY(2008/6/8) 會得到天數 8 。

YEAR、MONTH 和 DAY 函數傳回的值是「西曆」值，不論來源日期格式顯示為什麼格式。

DATE 傳回引數中代表特定日期的序列值。

語法

DATE(Year,Month,Day)

Year 四位數的年份數值。年份數值從 1900 ～ 9999 之間。

Month 係指 1 ～ 12 的數字，代表一年中的月份。

Day 係指 1 ～ 31 的數字，代表這個月份中的日數。

例如，DATE(2008,7,6) 會得到日期為 2008/7/6 。

IF,AND,OR 真與假的判斷

邏輯函數在所有函數中算是很重要的函數，其使用率是很高的，且利用 IF 函數可以幫忙得到不少分析的結果。如儲存格內是否有字元、或存在什麼字元、負值判斷、或數值超過/不足某一個標準...之類的判斷，都要有 IF 的幫助才能讓公式更加的完整，而不會有一大堆的錯誤問題。本教學最下方有範例使您可以更了解實際運用的情況。

IF
如果您指定的情況結果為 TRUE，則傳回一個值，若結果為 FALSE，則傳回另一個值。

語法

IF(Logical_test,Value_if_true,Value_if_false)

Logical_test 是用來計算 TRUE 或 FALSE 的任何值或運算式或公式。

Value_if_true 係指 logical_test 為 TRUE 時所傳回的值。

Value_if_false 如果 logical_test 為 FALSE，則會傳回該值。

註解

為配合 value_if_true 與 value_if_false 引數，以處理更為複雜的測試，最多可插入七層的 IF 函數。
當 value_if_true 引數或 value_if_false 引數被執行時，則 IF 函數傳回這些引數的運算結果（TRUE 或 FALSE），而非引數本身。

AND
如果它的所有引數都是 TRUE，則傳回 TRUE。如果有一個或多個引數是 FALSE，則傳回 FALSE。

語法

AND(logical1,logical2, ...)

Logical1, logical2, ... ，係指您要測試的 1 到 30 個條件，可能是 TRUE 或 FALSE。

註解

要測試的條件可以是值或運算式或是一個完整的公式。
大部分的情況中， AND 函數會和 IF 一起使用，且通常來說 AND 函數會放在巢狀公式中的第二層。

OR
如果有任何一個引數的邏輯值為 TRUE，即傳回 TRUE；唯有所有引數的邏輯值均為 FALSE 時，才會傳回 FALSE。

語法

OR(logical1,logical2, ...)

Logical1, logical2, ... ，係指您要測試的 1 到 30 個條件，可能是 TRUE 或 FALSE。

註解

要測試的條件可以是值或運算式或是一個完整的公式。
大部分的情況中， OR 函數會和 IF 一起使用，且通常來說 OR 函數會放在巢狀公式中的第二層。

範例：學科與術科都要及格才算及格；有任何一科不及格就算不及格，利用邏輯函數判斷結果是否「及格」。

說明

首先，您需要先使用 AND 或 OR 函數來建立一個運算子，而這個運算子就是為了要判斷是否符合及格或不及格的條件。

當您要判斷的事情只有一件時，您不需要 AND 或 OR 函數，像本例中需要分別判斷「學科」與「術科」這二件是否及格，因此就需要 AND 或 OR 函數來幫助二件事以上的判斷。

所以：

如果您想建立一個判斷學術科皆及格的式子，您應該這樣做：
AND(A2="及格",B2="及格")
如果您想建立一個判斷學術任一科不及格的式子，您應該這樣做：
OR(A8="不及格",B8="不及格")

IF函數是用來判斷當條件成立與不成立時傳回不同的值，而本例中，我們利用 AND 或 OR 函數來建立判斷的條件，有了判斷式之後，在 IF 函數中第一個引數 Logical_test 就使用上述 1. 或 2. 的式子來放入 IF 公式中

了解並完成條件判斷式之後，您就可以將兩個結果「及格」、「不及格」分別放入「條件成立的值」、「條件不成立的值」這兩個引數內。不過這裡要注意的事是，因為本例可以有兩種判斷的方式，所以您在將結果放入時，應該要注意其兩種結果的位置。以免最後判斷出來的結果剛好相反了。（參考範例題目下方的附圖）

您還可以到此頁參考一下 IF , AND , OR 這幾個函數的使用，這網站是多年前做一半的，因年久密碼忘了，所以荒廢了。

LEFT,MID,RIGHT 取得字串中的某些字元

LEFT 傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準。
LEFTB 傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準。這個函數是使用在二位元的字元組。

語法

LEFT(Text,Num_chars)
LEFTB(Text,Num_chars)

Text 為含有所要選錄文字之字串。

Num_chars 指定您要 LEFT 選錄的字元數。

num_chars 必須大於或等於零。
如果 num_chars 大於文字的長度，LEFT 將傳回所有的文字。
如果 num_chars 被省略了，則假定其值為 1。

Num_bytes 指定您要 LEFTB 選錄的字元數 (依據位元組)。

兩個函數之間的差別（黃色填滿的部分）

RIGHT 傳回自一文字串的最後字元或字元組 (依據您所指定的字元組數)。
RIGHTB 傳回自一文字串的最後字元或字元組 (依據您所指定的字元組數)。

語法

RIGHT(Text,Num_chars)
RIGHTB(Text,Num_chars)

Text 是含有您想選錄的部分字元之文字字串。

Num_chars 指定您要以 RIGHT 所選錄的字元數目。
Num_chars 指定您要以 RIGHTB 所選錄的字元數目 (依據位元組)。

註解

num_chars 必須大於或等於零。
如果 num_chars 大於字串的長度，則 RIGHT 函數會傳回所有的文字字串。
如果省略了 num_chars，則假定其值為 1。

兩個函數之間的差別（黃色填滿的部分）

MID 傳回自一字串您所指定的某個起始位置傳回特定數目的字元組（依據您指定的字元組數）。
MIDB 傳回自一字串您所指定的某個起始位置傳回特定數目的字元組（依據您指定的字元組數）。這個函數是使用在二位元的字元組。

語法

MID(Text,Start_num,Num_chars)
MIDB(Text,Start_num,Num_chars)

Text 是含有您想選錄的部分字串之文字字串。

Start_num 為用以指定您要由 text 的第若干個位元組開始抽選。text 中的第一個字元為 start_num 1，依此類推。

Num_chars 定您要 MID 從字串傳回的字元組。

Num_chars 指定您要 MIDB 從字串傳回的字元組 (以位元組)。

註解

如果 start_num 大於文字的長度，MID 將傳回 " " (空字串)。
如果 start_num 小於 text，但 start_num 加上 num_chars 的長度超過 text 的長度，MID 將傳回由 start_num 到 text 結尾的所有字元。
如果 start_num 小於 1，MID 將傳回 #VALUE! 的錯誤值。
如果 num_chars 為負值，MID 將傳回 #VALUE! 的錯誤值。
如果 num_bytes 為負值，MIDB 將傳回 #VALUE! 的錯誤值。

兩個函數之間的差別（黃色填滿的部分）

訂閱：文章 (Atom)