1-10 個最常用的函數
IF 函數
使用此函數以在條件符合時傳回一個值，並在條件不符合時傳回另一個值。以下是關於使用 IF 函數的影片。
LOOKUP 函數
如果您需要查詢單列或單欄，並在第二列或第二欄的同一個位置尋找某值時，請使用此函數。
VLOOKUP 函數
如果您需要在表格或範圍中按列搜尋資料，可以使用此函數。例如，用員工編號查詢員工的姓氏，或按姓氏查詢電話號碼 (就像電話簿一樣)。請看這段使用 VLOOKUP 的影片。
MATCH 函數
使用此函數在儲存格範圍中搜尋某個項目，並傳回此項目於該範圍中的相對位置。例如，若範圍 A1:A3 中含有值 5、7 及 38，則公式 =MATCH(7,A1:A3,0) 會傳回數字 2，因為 7 是範圍中的第二個項目。
CHOOSE 函數
使用此函數可以根據索引值，選取最多 254 個數值中的一個。例如，如果 value1 到 value7 分別代表星期一到星期日，則 CHOOSE 便會根據範圍在 1 到 7 之間的 index_num，傳回其中的一天。
DATE 函數
使用此函數傳回代表特定日期的連續序號。在年、月、日由公式或儲存格參照提供的情況中，此函數最有用。例如，您的工作表中可能有 Excel 無法辨識的日期格式，例如 YYYYMMDD。
使用 DATEDIF 函數來計算兩個日期之間的日數、月數或年數。
DAYS 函數
使用此函數傳回兩個日期之間的天數。
FIND 與 FINDB 函數
FIND 及 FINDB 會在某個文字字串內找到另一個文字字串，並傳回該文字字串在第一個文字字串中的起始位置。
INDEX 函數
使用此函數會傳回表格或範圍內的某個值或值的參照。
SUM 函數
使用此函數來加總儲存格中的值。
使用此函數來加總儲存格中的值。
IF 函數
使用此函數以在條件符合時傳回一個值，並在條件不符合時傳回另一個值。以下是關於使用 IF 函數的影片。
LOOKUP 函數
如果您需要查詢單列或單欄，並在第二列或第二欄的同一個位置尋找某值時，請使用此函數。
VLOOKUP 函數
如果您需要在表格或範圍中按列搜尋資料，可以使用此函數。例如，用員工編號查詢員工的姓氏，或按姓氏查詢電話號碼 (就像電話簿一樣)。請看這段使用 VLOOKUP 的影片。
MATCH 函數
使用此函數在儲存格範圍中搜尋某個項目，並傳回此項目於該範圍中的相對位置。例如，若範圍 A1:A3 中含有值 5、7 及 38，則公式 =MATCH(7,A1:A3,0) 會傳回數字 2，因為 7 是範圍中的第二個項目。
CHOOSE 函數
使用此函數可以根據索引值，選取最多 254 個數值中的一個。例如，如果 value1 到 value7 分別代表星期一到星期日，則 CHOOSE 便會根據範圍在 1 到 7 之間的 index_num，傳回其中的一天。
DATE 函數
使用此函數傳回代表特定日期的連續序號。在年、月、日由公式或儲存格參照提供的情況中，此函數最有用。例如，您的工作表中可能有 Excel 無法辨識的日期格式，例如 YYYYMMDD。
使用 DATEDIF 函數來計算兩個日期之間的日數、月數或年數。
DAYS 函數
使用此函數傳回兩個日期之間的天數。
FIND 與 FINDB 函數
FIND 及 FINDB 會在某個文字字串內找到另一個文字字串，並傳回該文字字串在第一個文字字串中的起始位置。
INDEX 函數
使用此函數會傳回表格或範圍內的某個值或值的參照。
2-3個常用的excel資料處理方式，學會這三招，就可以去闖蕩半個江湖
2.1第一招：伊符（IF）
「伊符」這招是一種簡單的邏輯轉換，可以用的很複雜很高深，但是最簡單的先熟練再去變化就可以了。
假設有一天你與哥哥和弟弟一起去「搶錢、搶糧、搶娘們」，搶回了10個娘們，你哥哥喜歡豐腴型的，你喜歡濃纖合度的，你弟弟喜歡輕飄飄的女人，你們把搶來女人的體重記錄在excel上，這時如何快速的用「伊符」將女人分給你們三兄弟呢？
【Step1】先將女人的體重依序輸入
【Step2】在B欄位相對應的位置打上「伊符」語法，內容如：「=IF(A2<50,"弟弟的",IF(A2<60,"我的","哥哥的"))」，這段語法其實是兩段式的，因為分類了兩次。先看前半段「=IF(A2<50,"弟弟的",xxxxxx)」這一段的意思就是：如果體重小於50公斤，則分給弟弟，如果不是小於50公斤，則分給xxxxxxxx。再看後半段的語法「IF(A2<60,"我的","哥哥的")」這一段的意思就是：如果體重小於60公斤，則分給我，如果不是小於60公斤，則分給哥哥。那由於體重小於50公斤的女人已經分給了弟弟，所以分給我的一定是在50-60公斤之間的女人。兩段語法加起來就把這個女人分配好囉！！
【Step3】第一格（B2）的語法寫好以後再往下拉，這樣每一格都會有結果產生，所有的女人都有分配給三兄弟，檢查一下，有沒有漏掉的ㄋㄟ？？
2.1##Excel-IF函數中多條件的使用
在 Excel 中， IF 函數是很常被使用的，在 IF 函數中可以使用多個條件以達到想要的判別結果。例如在下圖中的例子，要判別數值是否為介於40~80中的奇數，其中要用到三個條件：
(1) 是否大於或等於 40
(2) 是否小於或等於 80
(3) 是否為奇數
以儲存格B2為例，觀察以下三種寫法，都可以達到相同結果：
(1) 儲存格B2：=IF(A2>=40,IF(A2<=80,IF(MOD(A2,2)=1,"V",""),""),"")
利用 IF 函數的巢狀結果，因為有三個判別條件，所以要使用三個 IF 函數。
(2) 儲存格B2：=IF(AND(A2>=40,A2<=80,MOD(A2,2)=1),"V","")
使用一個 IF 函數，藉助 AND 函數，其中以三個條件來判別條件是否成立。
(3) 儲存格B2：=IF((A2>=40)*(A2<=80)*(MOD(A2,2)=1),"V","")
使用一個 IF 函數，使用「*」運算子及三個條件來判別條件是否成立。
因此，思考以下的公式，應該也可以產生相同結果：
儲存格B2：=IF(SUMPRODUCT((A2>=40)*(A2<=80)*(MOD(A2,2)=1)),"V","")
其中 SUMPRODUCT((A2>=40)*(A2<=80)*(MOD(A2,2)=1))，在三個條件都成立時，其結果為「1」，而在 IF 函數中，條件結果為 1，表示條件為「成立」。
2.2第二招：數伊符（COUNTIF）
「數伊符」這招是很簡單的一招，看起來沒啥卻非常實用，並且可附加的變化性很高，這裡先替大家簡單示範一次。
延續上面例子，當女人都分配完以後由於你的女人有點太多了，你來不及點，這時你想要快速瞭解一下，到底你分配到多少個女人呢？？
【Step1】在C1那一格（隨便一格就可以）上打上語法「=COUNTIF(B2:B11,"我的"」，這一段的意思就是：幫我算一下B2到B11這個範圍裡面，標記是「我的」有多少個？
【Step2】語法打完很簡單按enter，立刻幫你數好有多少個女人屬於你的，報告：有三個。（「『很好很好，哈哈哈哈…..』你邪惡的笑了」）
2.3第三招：速速配（VLOOKUP）
「速速配」這一招比較抽像，初學者要多練習幾次比較容易上手，但是他好用的程度簡直比3M還高，學會這一招，許多武林中人都會對你投以崇敬的眼神。
延續上面例子，有一天你和兄弟返回山寨，發現「娘們」都散亂的混在一起，這時要如何快速知道他們各自是屬於誰的呢？
【Step1】女人都散亂了，沒按照原本的順序排列
【Step2】在E2打上語法「=VLOOKUP(D2,$A$2:$B$11,2,FALSE)」意思是說：幫我比對A2到B11個區塊，看A排有沒有和D2一樣的數字，如果有的話請找出那一格（在這個例子上是A3），並且傳回B排和那一格相對應位置(就是B3)的數字或文字。特別注意括號中的意義（A,B,C,D），A代表你要尋找比對的主要目標。B代表你要參照的區塊，參照的區塊的第一排必定是與你要比對的目標做比對的，他不會比對其它排只會比對第一排。C代表你要他回傳的數值的欄位，像例子中你寫2就是回傳第二排相對應的格子，如果參照區塊很大，你可以回傳第三欄第四欄都可以，那數字就改成3或4。D你永遠打false就ok，百分之98的情況都用false，如果有發生例外情況，你再打電話給我。
【Step3】語法輸入完按enter，然後把整排拉到底，所有女人的配屬又都出現了，絕對不會亂唷～
教完這三招請多多練習，闖蕩江湖就靠他們囉!!
最後備註兩點，
1."哥哥的"這個括號的意思表示裡面要打的是文字，如果數字就不必這樣。
2. $A$2:$B$11，$的意思是固定區塊，由於我們要固定比對這個區塊，所以加上這個符號來固定，實際操作可以按F4。
3-EXCEL 交互參照必學神器， 用 LOOKUP 系列函數一鍵搞定資料比對 :
3.1-VLOOKUP篇
十一月 30, 2014WENDELL.HUANG 2 COMMENTS
Excel 當中主要的函數列表，可以參考 Microsoft 提供的清單(按此)。在官方的分類底下，有一類就叫做「查閱與參照函數」，相同的分類也可以在各版的 Excel 函數說明中找到，可惜對於 Excel 使用不深的人，比起「統計」、「財務」等類別，這是一個經常會被遺忘的函數集，不過若以泛用性而言，參照函數或許才是各 Excel 課程首先必須著墨的部分。
不得不說，它真的太重要啦，在企業中 Excel 的使用量很大，就算自己不用，你的客戶或供應商八成也都用得很兇，而且 Excel 的應用範圍也很廣，幾乎所有部門都能用得到，各種專案合作中只要看到別人該用卻不用對應的函數，只靠手動製作資料摘要，我就會忍不住內心嘆氣+翻白眼，因為這實在是太浪費人力了，畢竟好人才的時間成本可是很貴的啊!!
各位看官，介紹參照函數必學第一名，就是大名鼎鼎的「vlookup」函數。
假如你有以下的狀況…:
必須同時管理許多個部份資料有重疊的工作表
沒有任何單一工作表包含所有要用到的資料欄位
工作表的資料筆數很多，不太可能花時間人工一一對照
那麼再不學 vlookup ，就真的太遜啦!
本文用氣象局的觀測資料為例，範例包含兩個工作表各自包含「酸雨PH值」及「雨量」的資料，其中「地點(鄉鎮名)」是兩個資料集的共同點，如下圖所示:
這裡另外加入一個獨立的工作表，包含了鄉鎮名稱資料方便檢視結果。
假如我們想要建立一個包含鄉鎮、雨量以及酸雨PH值的資料集(如 test 工作表的範例)，那麼該怎麼做?
如果不利用 Excel 函數，那麼恐怕我們就得一筆一筆資料尋找，然後複製貼上了，假若只有幾個地點那倒無所謂，但是面對數百個鄉鎮地點，顯然最好的辦法不是手動。藉由 vlookup 函數，我們可以設定一組參照值，然後讓公式根據參照值，在給定的工作表每一列當中尋找符合參照值的資料，回傳設定好的欄位所對應的數值。
然後呢? 點兩下複製公式到所有格子就完成了! 就這麼簡單。
vlookup 共有幾個參數要設定，一一說明如下:
lookup_value
想要指定的參照值在此設定，以我們的例子來說，就是「鄉鎮地名」，藉由這個參照值，我們可以分別在「雨量」和「PH值」的資料當中，尋找具有對應地名的資料。這裡稍微偷懶，直接選擇整個 A 欄，不過不影響結果就是了。
Table_array
被比對的資料範圍在此設定，至少需要有兩欄(Column)。例如稍後展示的例子中，在「PH值」資料表中就選定了「I欄、J欄、K欄」三個欄位的資料。
Col_index_num
針對被比對的資料範圍(在前面的 Table_array 當中設定)，指定要回傳的值。例如稍後展示的例子中，根據上述「Table_array」參數在「PH值」資料表中已經選定的「I欄、J欄、K欄」，選擇要回傳的欄位資料，本例選擇「K欄」，由於公式是左邊起算，因此填上「3」。
Range_lookup
這是一個邏輯判斷式，填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE ，則參照值和被比對的值必須完全一樣，若為 TRUE，則會採用大略比對(但是這是站在程式的角度來看，跟你以為的大略符合常常有很大差異，經驗上非常不建議採用 TRUE )。
把公式往下複製到每一列，結果就會如下圖所示。PH 值只要比照辦理，調整參數值，馬上就可以得到結果。
範例中出現許多「N/A」值，表示在原始參照資料中並沒有這筆資料，或者在被比對的資料中沒有對應的項目。
事實上， test 工作表中的鄉鎮清單是來自另外的資料來源。之所以要刻意讓 N/A 值出現，正是要補充 vlookup 函數除了自動填入資料之外，同時也是用來查勘錯誤的一大利器。
例如，配合「 iferror 」函數以及「條件格式化」技巧，我們還可以得到額外視覺化標記的效果:
附帶一提的是，其實範例的資料並不適合用 vlookup…，假如你有稍微練習就會發現，由於參照資料和被比對的資料的項目都不是唯一(例如，同時有多個" 新竹 “的存在)，這時候 vlookup 只能取到其中一個值而已，當然不合用，但是在多個資料集共用一組各自唯一的 key 值時，這個函數就能做報表時幫上大忙。
這個情況，也是許多人會粗心忽略的地方，在檢查別人的公式時可得特別小心。再補充一點，這裡的「 v 」指的是 vertical (垂直) 的意思，你可以從實作中品味出命名的邏輯。
你可能好奇，既然有 vlookup ，有沒有 hlookup (horizontal，水平)呢?
答案是有的， lookup 參照函數系列共有三個函數: 「lookup」、「vlookup」、「hlookup」。 由於常見資料集格式的關係，導致大多數人最常用到的是「vlookup」。
3.1##Excel-多條件判斷結果(IF,VLOOKUP)
網友在下圖的 Excel 工作表中，想要根據圖上的A欄條件，要在B欄顯示一個結果，該如何處理？
如下圖，共有四個條件，四個結果，假設A欄小於或等於 500 時，結果預設為 0。
【公式設計與解析】
(1) 使用 IF 函數
儲存格B1：=IF(A1>2000,98,IF(A1>1600,75,IF(A1>1100,60,IF(A1>500,30,0))))
這個例子只有四個條件，很容易即可獲取答案。但是如果條件擴充為 7 個以上，則在 Excel 中將會受限 IF 函數的巢狀結果只能有 7 層，如此公式寫法將會傳回錯誤訊息。
(2) 使用 VLOOKUP 函數
儲存格B1：=VLOOKUP(A1,{0,0;500,30;1100,60;1600,75;2000,98},2,TRUE)
{0,0;500,30;1100,60;1600,75;2000,98} 是一個二維陣列的表示法，如果條件超過 7 個時，這個公式可以擴充，不受影響。
注意：公式中的『;』和『,』，{0,0;500,30;1100,60;1600,75;2000,98}可以看成：
(1) 儲存格C2：=IF(OR(A2=1,A2>=66),0,IF(B2="男",1,2))
OR(A2=1,A2>=66) 兩個條件其一成立，傳回 0，若不成立再判斷儲存格B2是否為『男』，成立傳回 1，不成立傳回 2。
(2) 儲存格C2：=IF(AND(A2>1,A2<66),IF(B2="男",1,2),0)
AND(A2>1,A2<66) 兩個條件都成立時，再判斷儲存格B2是否為『男』，成立傳回 1，不成立傳回 2。若兩個條件其一不成立，傳回 0。
(3) 儲存格C2：=IF((A2>1)*(A2<66),(B2="女")+1,0)
(A2>1)*(A2<66)：使用『*』運算子相當於執行邏輯 AND 運算。
(B2="女")+1：(B2="女")成立時傳回 TRUE，不成立時傳回 FALSE，經由『+1』運算時，TRUE／FALSE 會轉換為 1／0，結果成立時傳回 2，不成立時傳回 1。
5.Excel-分段取出儲存格中的字串
有人想要將 Excel 試算表中的資料分段取出，以下圖為例，內容共有三段，其中第二段以「< >」含括，如何將此三段分別取出呢？
你如果使用「資料剖析」工具，也是很快就可以完成這個工作。這次是要練習以公式來取出這三段文字。
運用多個文字相關函數，可以巧妙取出文字：
1. 第一段
儲存格B2：=LEFT(A2,SEARCH("<",A2,1)-1)
SEARCH("<",A2,1)：找到「<」的位置，再透過 LEFT 函數取出字串最左一個字元至「<」前一個字元。
2.第三段
儲存格D2：=RIGHT(A2,LEN(A2)-SEARCH(">",A2,1))
SEARCH(">",A2,1)：找到「>」的位置，再透過 RIGHT 函數取出字串最右一個字元至「>」前一個字元(LEN(A2)-SEARCH(">",A2,1))。
3.第二段
儲存格C2：=MID(A2,SEARCH("<",A2)+1,SEARCH(">",A2)-SEARCH("<",A2)-1)
使用 MID 函數取出「<」至「>」之間的文字。
您或許對這些文章有興趣:
3-EXCEL 交互參照必學神器， 用 LOOKUP 系列函數一鍵搞定資料比對 :
3.1-VLOOKUP篇
十一月 30, 2014WENDELL.HUANG 2 COMMENTS
Excel 當中主要的函數列表，可以參考 Microsoft 提供的清單(按此)。在官方的分類底下，有一類就叫做「查閱與參照函數」，相同的分類也可以在各版的 Excel 函數說明中找到，可惜對於 Excel 使用不深的人，比起「統計」、「財務」等類別，這是一個經常會被遺忘的函數集，不過若以泛用性而言，參照函數或許才是各 Excel 課程首先必須著墨的部分。
不得不說，它真的太重要啦，在企業中 Excel 的使用量很大，就算自己不用，你的客戶或供應商八成也都用得很兇，而且 Excel 的應用範圍也很廣，幾乎所有部門都能用得到，各種專案合作中只要看到別人該用卻不用對應的函數，只靠手動製作資料摘要，我就會忍不住內心嘆氣+翻白眼，因為這實在是太浪費人力了，畢竟好人才的時間成本可是很貴的啊!!
各位看官，介紹參照函數必學第一名，就是大名鼎鼎的「vlookup」函數。
假如你有以下的狀況…:
必須同時管理許多個部份資料有重疊的工作表
沒有任何單一工作表包含所有要用到的資料欄位
工作表的資料筆數很多，不太可能花時間人工一一對照
那麼再不學 vlookup ，就真的太遜啦!
本文用氣象局的觀測資料為例，範例包含兩個工作表各自包含「酸雨PH值」及「雨量」的資料，其中「地點(鄉鎮名)」是兩個資料集的共同點，如下圖所示:
這裡另外加入一個獨立的工作表，包含了鄉鎮名稱資料方便檢視結果。
假如我們想要建立一個包含鄉鎮、雨量以及酸雨PH值的資料集(如 test 工作表的範例)，那麼該怎麼做?
如果不利用 Excel 函數，那麼恐怕我們就得一筆一筆資料尋找，然後複製貼上了，假若只有幾個地點那倒無所謂，但是面對數百個鄉鎮地點，顯然最好的辦法不是手動。藉由 vlookup 函數，我們可以設定一組參照值，然後讓公式根據參照值，在給定的工作表每一列當中尋找符合參照值的資料，回傳設定好的欄位所對應的數值。
然後呢? 點兩下複製公式到所有格子就完成了! 就這麼簡單。
vlookup 共有幾個參數要設定，一一說明如下:
lookup_value
想要指定的參照值在此設定，以我們的例子來說，就是「鄉鎮地名」，藉由這個參照值，我們可以分別在「雨量」和「PH值」的資料當中，尋找具有對應地名的資料。這裡稍微偷懶，直接選擇整個 A 欄，不過不影響結果就是了。
Table_array
被比對的資料範圍在此設定，至少需要有兩欄(Column)。例如稍後展示的例子中，在「PH值」資料表中就選定了「I欄、J欄、K欄」三個欄位的資料。
Col_index_num
針對被比對的資料範圍(在前面的 Table_array 當中設定)，指定要回傳的值。例如稍後展示的例子中，根據上述「Table_array」參數在「PH值」資料表中已經選定的「I欄、J欄、K欄」，選擇要回傳的欄位資料，本例選擇「K欄」，由於公式是左邊起算，因此填上「3」。
Range_lookup
這是一個邏輯判斷式，填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE ，則參照值和被比對的值必須完全一樣，若為 TRUE，則會採用大略比對(但是這是站在程式的角度來看，跟你以為的大略符合常常有很大差異，經驗上非常不建議採用 TRUE )。
把公式往下複製到每一列，結果就會如下圖所示。PH 值只要比照辦理，調整參數值，馬上就可以得到結果。
範例中出現許多「N/A」值，表示在原始參照資料中並沒有這筆資料，或者在被比對的資料中沒有對應的項目。
事實上， test 工作表中的鄉鎮清單是來自另外的資料來源。之所以要刻意讓 N/A 值出現，正是要補充 vlookup 函數除了自動填入資料之外，同時也是用來查勘錯誤的一大利器。
例如，配合「 iferror 」函數以及「條件格式化」技巧，我們還可以得到額外視覺化標記的效果:
附帶一提的是，其實範例的資料並不適合用 vlookup…，假如你有稍微練習就會發現，由於參照資料和被比對的資料的項目都不是唯一(例如，同時有多個" 新竹 “的存在)，這時候 vlookup 只能取到其中一個值而已，當然不合用，但是在多個資料集共用一組各自唯一的 key 值時，這個函數就能做報表時幫上大忙。
這個情況，也是許多人會粗心忽略的地方，在檢查別人的公式時可得特別小心。再補充一點，這裡的「 v 」指的是 vertical (垂直) 的意思，你可以從實作中品味出命名的邏輯。
你可能好奇，既然有 vlookup ，有沒有 hlookup (horizontal，水平)呢?
答案是有的， lookup 參照函數系列共有三個函數: 「lookup」、「vlookup」、「hlookup」。 由於常見資料集格式的關係，導致大多數人最常用到的是「vlookup」。
3.1##Excel-多條件判斷結果(IF,VLOOKUP)
網友在下圖的 Excel 工作表中，想要根據圖上的A欄條件，要在B欄顯示一個結果，該如何處理？
如下圖，共有四個條件，四個結果，假設A欄小於或等於 500 時，結果預設為 0。
【公式設計與解析】
(1) 使用 IF 函數
儲存格B1：=IF(A1>2000,98,IF(A1>1600,75,IF(A1>1100,60,IF(A1>500,30,0))))
這個例子只有四個條件，很容易即可獲取答案。但是如果條件擴充為 7 個以上，則在 Excel 中將會受限 IF 函數的巢狀結果只能有 7 層，如此公式寫法將會傳回錯誤訊息。
(2) 使用 VLOOKUP 函數
儲存格B1：=VLOOKUP(A1,{0,0;500,30;1100,60;1600,75;2000,98},2,TRUE)
{0,0;500,30;1100,60;1600,75;2000,98} 是一個二維陣列的表示法，如果條件超過 7 個時，這個公式可以擴充，不受影響。
注意：公式中的『;』和『,』，{0,0;500,30;1100,60;1600,75;2000,98}可以看成：
3.2- HLOOKUP篇
十二月 2, 2014WENDELL.HUANG 1 COMMENT
延續上一篇的 vlookup 教學文章，藉由先給定參照值，再於指定範圍內尋找符合條件的資料並傳回指定「欄位」的對應數值，藉著介紹的 hlookup 也大同小異，但找到符合條件的資料後卻是傳回指定「列」的對應數值。
由於大部分資料庫匯出的格式，欄位代表的是變數，列資料代表的是樣本(或筆數)，以至於絕大部份時候會用到的是 vlookup ，因為我們希望回傳的通常是同一筆樣本在不同變數的數值，這樣就能夠在不同的資料庫(例如客服中心電話紀錄 vs 銷售紀錄)根據相同的 key 值(例如客戶編號)，建立一個新的資料集或摘要表來使用，但有些時候 hlookup 也是可以派得上用場，像是典型的實驗設計表格。
範例表格如下，這是一個飲料好感度盲測研究的實驗資料。
假如我們想把指定的資料填入一組如下方的特定清單，該怎麼做呢?
當然就是要透過 hlookup 囉!
如同 vlookup ， hlookup 也擁有 4 個參數，唯一的差別是傳回值的參數，在 vlookup 中是「Col_index_num」，可指定要傳回數值的垂直範圍，在 hlookup 中則是「Row_index_num」，用來指定要傳回數值的水平範圍。
Lookup_value
想要指定的參照值在此設定，以本例而言，是各廠牌的飲料名稱，先選擇摘要工作表中的 A1:A10 範圍作為參照值。
Table_array
接著指定要被比對的資料範圍在此設定，至少需要包含兩列(Row)，一列用來和參照值比對，另一列則是提供要回傳的資料。以本例而言，選擇 A1:K5 的範圍。
Row_index_num
針對被比對的資料範圍(根據 Table_array 設定的範圍)，指定要回傳的值，指定的方式是選擇所要回傳的「第幾列」的資料，順序由上至下。以本例而言，希望回傳盲測組 C 的評分，也就是第 4 列，填入數字 4。
Range_lookup
這是一個邏輯判斷式，填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE ，則參照值和被比對的值必須完全一樣，若為 TRUE，則會採用大略比對(但是這是站在程式的角度來看，跟你以為的大略符合常常有很大差異，經驗上非常不建議採用 TRUE )。
假如真的想要採用「大略比對」，這裡的 excel 討論串中有一些建議可以幫助你傳回正確的值。
結果如下，接著把公式往下拓展到每一格就能得到對應的值。
往下複製公式的時候有一個小技巧，在撰寫 VBA 時經常會用到，就是當所要指定的範圍或儲存格是固定時，為了不讓 Excel 自動 +1 (例如第一格 A4 ，往下複製時 Excel 公式自動變為 A5 )，你可以在行/列或格子編號前加上符號「$」來固定選擇範圍。
例如 $ A1 表示固定在 A 欄當中，但是往下複製時還是會出現 A2 、 A3 ….，另一方面 $ A $ 1 則表示固定選擇為「 A1 」儲存格，往下複製時該位置永遠只有 A1 。
範例如下:
基本上， vlookup 和 hlookup 只需要學一個就可以了，最常用到的當然還是 vlookup ，但假如偶爾有使用到「列參照」的需求，那其實還有一個小撇步，那就是運用 transpose 函數，將行列顛倒之後再用 vlookup 來執行 hlookup 的工作。
聽起來很複雜? 不，一點也不。
因為你甚至不需要會 transpose 函數，記得複製貼上時會出現的小選單嗎? 其中就包含了自動執行 transpose 再貼上的選項。
範例如下:
4.Excel-IF使用三個條件判斷式(AND,OR,＊運算子)
網友問到：在下圖中的 Excel 工作表，其中要根據年齡和性別來判定傳回值，條件：
1. 年齡<1或年齡>=66，則不分男女傳回 0。
2. 若不在此年齡範圍，則男生傳回 1，女生傳回 2。
該如何設計公式。
【公式設計與解析】
觀察這兩個條件，其實需要三個判斷式，以下試著在 IF 函數中使用這三個判斷式，來練習 AND 和 OR 邏輯運算。
AND 邏輯運算：兩者皆成立，結果才成立，反之不成立。
OR 邏輯運算：兩者皆不成立，結果才不成立，反之成立。
十二月 2, 2014WENDELL.HUANG 1 COMMENT
延續上一篇的 vlookup 教學文章，藉由先給定參照值，再於指定範圍內尋找符合條件的資料並傳回指定「欄位」的對應數值，藉著介紹的 hlookup 也大同小異，但找到符合條件的資料後卻是傳回指定「列」的對應數值。
由於大部分資料庫匯出的格式，欄位代表的是變數，列資料代表的是樣本(或筆數)，以至於絕大部份時候會用到的是 vlookup ，因為我們希望回傳的通常是同一筆樣本在不同變數的數值，這樣就能夠在不同的資料庫(例如客服中心電話紀錄 vs 銷售紀錄)根據相同的 key 值(例如客戶編號)，建立一個新的資料集或摘要表來使用，但有些時候 hlookup 也是可以派得上用場，像是典型的實驗設計表格。
範例表格如下，這是一個飲料好感度盲測研究的實驗資料。
假如我們想把指定的資料填入一組如下方的特定清單，該怎麼做呢?
當然就是要透過 hlookup 囉!
如同 vlookup ， hlookup 也擁有 4 個參數，唯一的差別是傳回值的參數，在 vlookup 中是「Col_index_num」，可指定要傳回數值的垂直範圍，在 hlookup 中則是「Row_index_num」，用來指定要傳回數值的水平範圍。
Lookup_value
想要指定的參照值在此設定，以本例而言，是各廠牌的飲料名稱，先選擇摘要工作表中的 A1:A10 範圍作為參照值。
Table_array接著指定要被比對的資料範圍在此設定，至少需要包含兩列(Row)，一列用來和參照值比對，另一列則是提供要回傳的資料。以本例而言，選擇 A1:K5 的範圍。
Row_index_num
針對被比對的資料範圍(根據 Table_array 設定的範圍)，指定要回傳的值，指定的方式是選擇所要回傳的「第幾列」的資料，順序由上至下。以本例而言，希望回傳盲測組 C 的評分，也就是第 4 列，填入數字 4。
Range_lookup
這是一個邏輯判斷式，填入「TRUE」or「FALSE」以指定選擇的標準。若填上 FALSE ，則參照值和被比對的值必須完全一樣，若為 TRUE，則會採用大略比對(但是這是站在程式的角度來看，跟你以為的大略符合常常有很大差異，經驗上非常不建議採用 TRUE )。
假如真的想要採用「大略比對」，這裡的 excel 討論串中有一些建議可以幫助你傳回正確的值。
結果如下，接著把公式往下拓展到每一格就能得到對應的值。
往下複製公式的時候有一個小技巧，在撰寫 VBA 時經常會用到，就是當所要指定的範圍或儲存格是固定時，為了不讓 Excel 自動 +1 (例如第一格 A4 ，往下複製時 Excel 公式自動變為 A5 )，你可以在行/列或格子編號前加上符號「$」來固定選擇範圍。
例如 $ A1 表示固定在 A 欄當中，但是往下複製時還是會出現 A2 、 A3 ….，另一方面 $ A $ 1 則表示固定選擇為「 A1 」儲存格，往下複製時該位置永遠只有 A1 。
範例如下:
基本上， vlookup 和 hlookup 只需要學一個就可以了，最常用到的當然還是 vlookup ，但假如偶爾有使用到「列參照」的需求，那其實還有一個小撇步，那就是運用 transpose 函數，將行列顛倒之後再用 vlookup 來執行 hlookup 的工作。
聽起來很複雜? 不，一點也不。
因為你甚至不需要會 transpose 函數，記得複製貼上時會出現的小選單嗎? 其中就包含了自動執行 transpose 再貼上的選項。
範例如下:
4.Excel-IF使用三個條件判斷式(AND,OR,＊運算子)
網友問到：在下圖中的 Excel 工作表，其中要根據年齡和性別來判定傳回值，條件：
1. 年齡<1或年齡>=66，則不分男女傳回 0。
2. 若不在此年齡範圍，則男生傳回 1，女生傳回 2。
該如何設計公式。
【公式設計與解析】
觀察這兩個條件，其實需要三個判斷式，以下試著在 IF 函數中使用這三個判斷式，來練習 AND 和 OR 邏輯運算。
AND 邏輯運算：兩者皆成立，結果才成立，反之不成立。
OR 邏輯運算：兩者皆不成立，結果才不成立，反之成立。
(1) 儲存格C2：=IF(OR(A2=1,A2>=66),0,IF(B2="男",1,2))
OR(A2=1,A2>=66) 兩個條件其一成立，傳回 0，若不成立再判斷儲存格B2是否為『男』，成立傳回 1，不成立傳回 2。
(2) 儲存格C2：=IF(AND(A2>1,A2<66),IF(B2="男",1,2),0)
AND(A2>1,A2<66) 兩個條件都成立時，再判斷儲存格B2是否為『男』，成立傳回 1，不成立傳回 2。若兩個條件其一不成立，傳回 0。
(3) 儲存格C2：=IF((A2>1)*(A2<66),(B2="女")+1,0)
(A2>1)*(A2<66)：使用『*』運算子相當於執行邏輯 AND 運算。
(B2="女")+1：(B2="女")成立時傳回 TRUE，不成立時傳回 FALSE，經由『+1』運算時，TRUE／FALSE 會轉換為 1／0，結果成立時傳回 2，不成立時傳回 1。
5.Excel-分段取出儲存格中的字串
有人想要將 Excel 試算表中的資料分段取出，以下圖為例，內容共有三段，其中第二段以「< >」含括，如何將此三段分別取出呢？
你如果使用「資料剖析」工具，也是很快就可以完成這個工作。這次是要練習以公式來取出這三段文字。
運用多個文字相關函數，可以巧妙取出文字：
1. 第一段
儲存格B2：=LEFT(A2,SEARCH("<",A2,1)-1)
SEARCH("<",A2,1)：找到「<」的位置，再透過 LEFT 函數取出字串最左一個字元至「<」前一個字元。
2.第三段
儲存格D2：=RIGHT(A2,LEN(A2)-SEARCH(">",A2,1))
SEARCH(">",A2,1)：找到「>」的位置，再透過 RIGHT 函數取出字串最右一個字元至「>」前一個字元(LEN(A2)-SEARCH(">",A2,1))。
3.第二段
儲存格C2：=MID(A2,SEARCH("<",A2)+1,SEARCH(">",A2)-SEARCH("<",A2)-1)
使用 MID 函數取出「<」至「>」之間的文字。
您或許對這些文章有興趣:
【延伸學習】
參考其他關於資料驗證使用的學習文章…
Excel-在公式中善用名稱和資料驗證清單(VLOOKUP,INDIRECT)
Excel-使用表單下拉式方塊篩選資料
Excel-固定資料驗證提示訊息的位置
Excel-保護下拉式清單中的內容不變動
Excel-選取儲存格顯示公式說明
Excel-不允許輸入某些字串(資料驗證)
Excel-限定儲存格中只能輸入大寫字母(資料驗證)
Excel-限定儲存格輸入內容(設定格式化的條件)
Excel-讓圖案中的文字可變
Excel-分組報告成績輸入統計表
Excel-設計二層的下拉式選單
Excel-資料驗證使用多個條件
Excel-加總下拉式清單的內容
Excel-使用高速公路里程表來計費
Excel-四則運算練習
Excel-各種資料驗證的應用
Excel-在一個儲存格中參照不同工作表的內容(INDIRECT)
Excel-以資料驗證來輸入資料
Excel-限制儲存格中輸入的字數和首字為英文
Excel-輸入資料時提示輸入重覆
Excel-使用下拉式清單設定格式
Excel-為不同組別計算平均分數(OFFSET)
Excel-使用多層下拉式清單結構輸入資料(基礎)
Excel-限制同一欄中輸入唯一值
Excel-設定格式化條件區隔男女生資料
Excel-限制不可輸入未來日期
Excel-限制輸入的小數點位數
Excel-查表練習(INDEX,MATCH,OFFSET)
Excel-在下拉式選單中選取日期
Excel-顯示四則運算的計算過程
Excel-依選單內容顯示圖表
Excel-用公式篩選資料(陣列公式)
Excel-查表練習(INDEX,陣列)
Excel-製作單位轉換工具(多層選單)
Excel-Index+Match練習
Excel-研習人員統計報表(清單、INDIRECT)
Excel-避免同一欄中輸入重覆值
Excel-使用多層下拉式清單結構輸入資料(進階)
Excel-多條件的查詢(INDEX+MATCH+陣列)
Excel-用公式取代樞紐分析(二)
Excel-計算每月各日及星期各天的平均
Excel-顯示動態圖片(OFFSET+資料驗證+定義名稱)
Excel-COUNTIF+SUMIF練習
Excel-研習人員統計報表(SUMPRODUCT,INDEX,MATCH,VLOOKUP)
Excel-列出週六日的日期
參考其他關於資料驗證使用的學習文章…
Excel-在公式中善用名稱和資料驗證清單(VLOOKUP,INDIRECT)
Excel-使用表單下拉式方塊篩選資料
Excel-固定資料驗證提示訊息的位置
Excel-保護下拉式清單中的內容不變動
Excel-選取儲存格顯示公式說明
Excel-不允許輸入某些字串(資料驗證)
Excel-限定儲存格中只能輸入大寫字母(資料驗證)
Excel-限定儲存格輸入內容(設定格式化的條件)
Excel-讓圖案中的文字可變
Excel-分組報告成績輸入統計表
Excel-設計二層的下拉式選單
Excel-資料驗證使用多個條件
Excel-加總下拉式清單的內容
Excel-使用高速公路里程表來計費
Excel-四則運算練習
Excel-各種資料驗證的應用
Excel-在一個儲存格中參照不同工作表的內容(INDIRECT)
Excel-以資料驗證來輸入資料
Excel-限制儲存格中輸入的字數和首字為英文
Excel-輸入資料時提示輸入重覆
Excel-使用下拉式清單設定格式
Excel-為不同組別計算平均分數(OFFSET)
Excel-使用多層下拉式清單結構輸入資料(基礎)
Excel-限制同一欄中輸入唯一值
Excel-設定格式化條件區隔男女生資料
Excel-限制不可輸入未來日期
Excel-限制輸入的小數點位數
Excel-查表練習(INDEX,MATCH,OFFSET)
Excel-在下拉式選單中選取日期
Excel-顯示四則運算的計算過程
Excel-依選單內容顯示圖表
Excel-用公式篩選資料(陣列公式)
Excel-查表練習(INDEX,陣列)
Excel-製作單位轉換工具(多層選單)
Excel-Index+Match練習
Excel-研習人員統計報表(清單、INDIRECT)
Excel-避免同一欄中輸入重覆值
Excel-使用多層下拉式清單結構輸入資料(進階)
Excel-多條件的查詢(INDEX+MATCH+陣列)
Excel-用公式取代樞紐分析(二)
Excel-計算每月各日及星期各天的平均
Excel-顯示動態圖片(OFFSET+資料驗證+定義名稱)
Excel-COUNTIF+SUMIF練習
Excel-研習人員統計報表(SUMPRODUCT,INDEX,MATCH,VLOOKUP)
Excel-列出週六日的日期
沒有留言:
張貼留言