2014年7月30日 星期三

Excel-取一段範圍內的數值予以加總(SUMPRODUCT)

網友想要在一個數值清單中,給予限定的範圍內,將符合條件的數值予以加總,該如何處理?

以下圖為例,如果要取 20 ~ 80 的數值來加總,可以使用 SUMPRODUCT 函數來執行乘積和,這樣的做法最簡單。

儲存格D2:=SUMPRODUCT((A2:A25>=20)*(A2:A25<=80)*A2:A25)

(A2:A25>=20):條件 1,數值小於或等於 20,傳回 TRUE/FALSE 陣列。

(A2:A25<=80):條件 1,數值大於或等於 80,傳回 TRUE/FALSE 陣列。

(A2:A25>=20)*(A2:A25<=80)*A2:A25:其中的運算子「*」,可以將上式傳回的 TRUE/FALSE 陣列,在運算過程中轉換為 1/0 陣列。

透過 SUMPRODUCT 執行三個項目的乘積和,即為所求!

Excel-取出同類之間隔欄/列的最小值(ROW,COLUMN,INT,陣列公式)

網友根據另一篇文章:Excel-取出間隔欄/列的最小值(ROW,COLUMN,INT,陣列公式),想要擴大處理的動作,參考下圖,要求不同類別的最大值/最小值,該如何處理?

【準備工作】

選取儲存格A1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目、數值。

 

【輸入公式】

儲存格F2:{=MAX(IF((類別=$E2)*(ROW(項目)/2)=INT(ROW(項目)/2),數值,FALSE))}

儲存格G2:{=MIN(IF((類別=$E2)*(ROW(項目)/2)=INT(ROW(項目)/2),數值,FALSE))}

這些陣列公式,輸入完成,要按 Ctrl+Shift+F3 鍵。會自動產生「{  }」。

(請先參考:Excel-取出間隔欄/列的最小值(ROW,COLUMN,INT,陣列公式)的說明)

(類別=$E2)*(ROW(項目)/2)=INT(ROW(項目)/2):其中的「*」運算,相當於執行邏輯運算 AND 指令。

如此用法,可以在公式中使用「多條件」的運算,例如:條件1*條件2*條件3*…*條件n。本例條件1:(類別=$E2),為判斷是否符合某類別,條件2:*(ROW(項目)/2)=INT(ROW(項目)/2),為判斷是否為間隔列(偶數列,第2列、第4列、…)。

在公式中使用:ROW(項目),其結果和 ROW(類別) 及 ROW(數值) 相同,因為項目、類別、數值的儲存格範圍相同。

複製儲存格F2:G2,貼至儲存格F2:G4。

另外,間隔欄的部分,請自行練習,做法雷同!

2014年7月29日 星期二

Excel-以固定人數分組給予流水號編號(INT,ROW)

有網友問到:在 Excel 中有一個如下圖的資料清單,每 8 個人為一組,並給予一個流水號作為裝箱代號,該如何處理?

每 8 人分一組並給予編號,可以利用 INT 函數和 ROW 函數來處理:

儲存格C2:=INT((ROW(1:1)-1)/8)+1

ROW(1:1):ROW(1:1)=1,往下複製/貼上時,會自動產生 ROW(2:2)=2、ROW(3:3)=3、ROW(4:4)=4、…。

再使用 INT 函數,將上式除以 8 的商取不大於的最大整數,再加 1 即為所求。公式中的「+1」是因為代號由 1 開始編號。公式中的「-1」,是因為第 1 筆資料位於第 2 列。

複製儲存格C2,往下各列有資料的位置貼上。

2014年7月27日 星期日

Excel-根據時間區間傳回對應編號(LEFT,INT)

在網友想要知道在 Excel 中如果將時間每二個小時為一個單位並給予一個編號(如下圖左),如何在輸入一個時間字串後,能自動傳回對應的編號(如下圖右)?

根據上述的規則,其輸入的時間為 4 碼,由時和分組成,設計以下的公式:

儲存格B2:=INT(VALUE(LEFT(D2,2))/2)+1

LEFT(D2,2):取出時間字串的左邊 2 碼,代表「時」的部分。

VALUE(LEFT(D2,2)):將取出時間字串代表時的 2 碼,轉換為數值。

INT(VALUE(LEFT(D2,2))/2)+1:將上述的數值除以 2,再經由 INT 函數的結果加 1,即為所求。

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

2014年7月24日 星期四

Excel-取出間隔欄/列的最小值(ROW,COLUMN,INT,陣列公式)

如果你在 Excel 的資料表中,想要在一欄或一列中取出間隔欄/列來計算其和、平均、最大值、最小值等,通常需要用到「陣列公式」。

在下圖中分別來找出間隔欄的最小值和間隔列的最小值。

(1) 間隔欄的最小值,計算儲存格A2:J2中的價格最小值

儲存格G6:{=MIN(IF(COLUMN(A2:J2)/2<>INT(COLUMN(A2:J2)/2),A2:J2,FALSE))}

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

COLUMN(A2:J2)/2<>INT(COLUMN(A2:J2)/2):找出奇數欄,傳回 TRUE/FALSE 陣列。

再對應到儲存格A2:J2,取出奇數欄(第A,C,E,…欄)的數值,再由 MIN 函數取出最小值。

 

(2) 間隔列的最小值,計算儲存格A4:B21中的價格最小值

儲存格G10:{=MIN(IF(ROW(B4:B21)/2=INT(ROW(B4:B21)/2),B4:B21,FALSE))}

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

ROW(B4:B21)/2=INT(ROW(B4:B21)/2):找出奇數列,傳回 TRUE/FALSE 陣列。

再對應到儲存格B4:B21,取出奇數列(第4,6,8,…列)的數值,再由 MIN 函數取出最小值。

2014年7月23日 星期三

Excel-在數值清單中找出缺少的號碼(COUNTIF,SMALL)

有老師在一個學生號碼的 Excel 資料清單中,想要自動列出 1 至 35 個號碼中,有那些號碼缺少了而未出現。例如:學生繳交作業的號碼,逐筆記錄後,想要知道有那些學生尚未繳交,該如何處理這個問題呢?(參考下圖)

【輸入公式】

儲存格B2:{=SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

COUNTIF($A$2:$A$26,ROW($1:$35):找出儲存格A2:A26中,含有 1 至 35 的個數,其中不是 1 就是 0。

IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)):在 IF 函數中,依上式結果,1 代表 TRUE,0 代表 FALSE。意思是如果未出現的數字,即傳回 ROW(1:35) 的結果,該結果就是未出現的數字。而參數 40 要比最大值 35 大。

如果不想讓儲存格中超出 35 的數字(40)顯示出來,則可以修改公式如下:(參考上圖D欄)

{=IF(SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1))>35,"",SMALL(IF(COUNTIF($A$2:$A$26,ROW($1:$35)),40,ROW($1:$35)),ROW(1:1)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格B2,往下各列貼上。

Google地圖-量測多點間的距離和面積

Google 地圖最近推出了在地圖上多點間量測距離的功能,這個實用的功能可以讓你量測走路/跑步的距離,可以讓你量量校園圍牆跑一圈有多長等,可以讓你量測校園的佔地總面積等。

以衛星地圖模式來看看一些有趣的麥田園圖案到底有多大?下圖的路徑:

https://www.google.com/maps/@51.5625579,-1.6050689,227m/data=!3m1!1e3

在一點上按右鍵,選取「測量距離」:

至第二個想要的位置上按一下,產生第二個點,此時已可以看到總距離的數值了:

你可以多在幾個地方按左鍵,即可得到總路徑長度,可以使用拖曳方式來改變位置,也可以在圓點上按一下,即可刪除這條線。

如果你設定的多點可以圍成一個封閉空間,則 Google 會幫你算出該空間的總面積。你也試著想想看還有那些可行的妙用!

image

Windows 7/8-找回不小心拖曳不見的檔案

同事問過一個問題,也是使用者常會發生的現象:在檔案總管裡,不小心將某一個檔案拖曳(移動)至某一個資料夾中,由於是「不小心」的隨意動作,所以也就可能不知道檔案被搬到那裡去了!如何在當下找回來啊?

印象中,已有幾個人問過同類的問題,除了檔案總管之外,也有人在 Microsof Outlook 中不小心把一封郵件或是一個資料夾搬移到其他地方了。

要救回來,其實很簡單,即是使用「復原」按鍵:Ctrl+Z,或是[復原]按鈕。

在發生上述狀況時,當下立即使用「復原」,則消失的檔案(資料夾)會立刻復原回原來的資料夾。這個動作當然也包括刪除檔案的複原,因為檔案被移至垃圾筒資料夾了。在檔案總中能復原的不只一個檔案而已,它可以連續復原多個動作,所以也可以復原多個檔案。

Excel-由資料清單中摘要符合的項目(MATCH,OFFSET)

有網友問到:在 Excel 中的一個資料表(參考下圖右),要從其中摘要出對應的內容(參考下圖左),該如何處理?

本例題的意思是,例如:在項目「甲」中,在儲存格C3輸入「價格」後(本例為229),自動會在同一列的儲存格D3中顯示對應的數量(本例為38),然後在儲存格B3中顯示價格所對應的類別(本例為CC)。

 

【輸入公式】

(1)儲存格D3:=OFFSET(F3,0,MATCH(C3,F3:M3,0))

MATCH(C3,F3:M3,0):透過 MATCH 函數來找出儲存格C3的內容,在儲存格範圍F3:M3中,位於第幾個(傳回一個數字),其中的參數「0」,表示要找完全相符者。

接著藉由 OFFSET 函數,將上式傳回結果,根據儲存格F3,找到相對位置的儲存格。

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

(2) 儲存格B3:=OFFSET($F$1,0,MATCH(C3,F3:M3,0)-1)

原理同(1)的說明,公中的「-1」運算,乃因為第 1 列中的儲存格是合併儲存格,每二格合併為一格,所做的調整。

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

 

因應如果所輸入的價格,在同一列中如果找不到,避免顯示錯誤訊息,修改公式:

儲存格D3:=IFERROR(OFFSET(F3,0,MATCH(C3,F3:M3,0)),"")

儲存格B3:=IFERROR(OFFSET($F$1,0,MATCH(C3,F3:M3,0)-1),"")

當價格沒有比對成功時,會以空白顯示。(參考第 10 列)

2014年7月22日 星期二

Excel-產生固定位數的亂數值(INT,RANDBETWEEN)

有一位網友問到:在 Excel 中,如果要產生一些亂數值,其整數部分為三個位數,小數部分為四個位數,該如何處理?(參考下圖)

可用的方法很多,以下使用 INT 函數和 RAND 函數來完成。

儲存格A2:=(INT(RAND()*9000000)+1000000)/10000

RAND():產生小於 1 且大於等於 0 的亂數。

RAND()*9000000:產生小於 9000000 且大於等於 0 的亂數。

INT(RAND()*9000000):產生小於 9000000 且大於等於 0 的整數。其中 INT 函數的作用為將一個數值取其不大於(小於或等於)的最大整數。

INT(RAND()*9000000)+1000000:產生小於 10000000 且大於等於 1000000 的整數。

將上一式除以 10000,即可產生整數三位數、小數四位數的亂數了!

另外,如果你使用的 Excel 版本可以使用 RANDBETWEEN 函數,則可以修改公式:

儲存格A2:=RANDBETWEEN(1000000,9999999)/10000

還有各式各樣的寫法可達到這個亂數效果,試試自行練習看看吧!

2014年7月16日 星期三

Excel-標示資料清單中的最大值/最小值(陣列公式)

網友問到:在下列的 Excel 資料清單中,如果要根據項目的內容,標示出每個項目的最大值/最小值,該如何處理?

【準備工作】

選取儲存格A1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、內容。

 

 

【輸入公式】

1. 標示數值

(1) 最大值/儲存格C2:{=MAX(IF(項目=A2,內容,FALSE))}

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

其中參數「FALSE」,不要以空白或是 0 取代,將會得到錯誤結果。

(2) 最小值/儲存格D2:{=MIN(IF(項目=A2,內容,FALSE))}

 

2. 標示「V」

(1) 最大值/儲存格C2:{=IF(MAX(IF(項目=A2,內容,FALSE))=B2,"V","")}

(2) 最小值/儲存格D2:{=IF(MIN(IF(項目=A2,內容,FALSE))=B2,"V","")}

2014年7月14日 星期一

Excel-符合多條件計算小計(SUMPRODUCT)

接續上一篇:計算有標示日期者的平均金額(陣列公式,SUMPRODUCT),網友想要在一個日期清單中,給予一個區間,篩選某個項目的平均值,該如何理?

【準備工作】

選取儲存格A1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、項目、數量。

【輸入公式】

這是個符合多條件計算小計的做法,本例為三個條件:(1)大於或等於「2014/6/5,(2)小於或等於「2014/6/21」,(3)符合項目「A」。

(1) 使用 SUMPRODUCT 函數

儲存格G2:=SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)*數量)/SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2))

SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)):計算符合三個條件的個數。

SUMPRODUCT((日期>=E2)*(日期<=E3)*(項目=F2)*數量):計算符合三個條件的個數的總合。

將以上二式相除,即可求得其平均值。

 

(2) 使用陣列公式

儲存格G2:{=SUM((日期>=E2)*(日期<=E3)*(項目=F2)*數量)/SUM((日期>=E2)*(日期<=E3)*(項目=F2))}

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

Excel-計算有標示日期者的平均金額(陣列公式,SUMPRODUCT)

有網友問到:參考下圖,如果只想要將有標示日期者所對應的金額予以平均,該如何處理?

(1) 使用陣列公式

儲存格D2:{=AVERAGE(IF(A2:A20<>"",B2:B20,FALSE))}

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

判斷在儲存格A2:A20陣列中,不是空白儲存格者所對應的B2:B20陣列,再透過 AVERAGE 函數,計算這些陣列值的平均。其中的參數「FALSE」,不可以用空白或是 0 取代。

 

(2) 使用 SUMPRODUCT 函數

儲存格D2:=SUMPRODUCT((A2:A20<>"")*B2:B20)/SUMPRODUCT(--(A2:A20<>""))

SUMPRODUCT((A2:A20<>"")*B2:B20):計算儲存格A2:A20陣列中,不是空白儲存格者所對應的B2:B20陣列內容之總和。

SUMPRODUCT(--(A2:A20<>""))計算儲存格A2:A20陣列中,不是空白儲存格者的個數。其中的「--」運算,可以將以上二式中的 TRUE/FALSE 轉換為 1/0,再代入 SUMPRODUCT 運算。

將以上二式相除,即可得平均值。

Excel-條件式加總練習(SUMIF+COUNTIF)

網友根據另一篇:Excel-條件式加總練習(SUMIF+COUNTIF),想要詢問不同人員之進料/出料次數。以下補充該部分的公式。

請先閱讀原來文章:http://isvincent.pixnet.net/blog/post/35181133

儲存格H11:=SUMPRODUCT((進出=$G11)*(經手人=H$10))

複製儲存格H11,貼至儲存格H11:J12。

(進出=$G11):判斷「進出」的陣列中是否符合儲存格G11的內容,傳回 TRUE/FALSE 陣列。

(經手人=H$10):判斷「經手人」的陣列中是否符合儲存格H10的內容,傳回 TRUE/FALSE 陣列。

(進出=$G11)*(經手人=H$10):其中的「*」運算,可以將以上二式中的 TRUE/FALSE 轉換為 1/0,再代入 SUMPRODUCT 運算。

2014年7月13日 星期日

Excel-出現某數字時進行加總(COUNTIF)

參考下圖,有網友問到:如果在一個儲存格範圍中出現某一數字時,即將不同列中的儲存格範圍予以加總。

本例以在儲存格A2:E2中出現「2」為例,分別計算不同色彩的儲存格範圍中的數字總和。

(1) 儲存格B7:=(COUNTIF(A2:E2,2)>0)*SUM(A2:E2)

COUNTIF(A2:E2,2):判斷是否在儲存格A2:E2中出現「2」。

COUNTIF(A2:E2,2)>0):只要有一個「2」,則傳回 TRUE,否則傳回 FALSE。

(COUNTIF(A2:E2,2)>0)*SUM(A2:E2):在運算過程中,「*」運算會將上式中的 TRUE/FALSE 轉換為 1/0 再運算。

(2) 儲存格B8:=(COUNTIF(A2:E2,2)>0)*SUM(C3:G3)

(3) 儲存格B9:=(COUNTIF(A2:E2,2)>0)*SUM(E4:I4)

(4) 儲存格B10:=(COUNTIF(A2:E2,2)>0)*SUM(G5:K5)

2014年7月7日 星期一

Excel-資料表查詢(OFFSET,MATCH)

有網友問到:在下圖的資料表中,如何根據「級距」和「天數」,查出對應的「勞工」和「單位」?

本例要使用的查詢函數有二個:OFFSETMATCH

要注意這個表格是每二欄為一種級距,這也是一個水平/垂直方向交叉位置的查詢,參考以下的公式:

儲存格B16:=OFFSET(B2,B15,MATCH(B14,B1:I1,0)-1,,)

MATCH(B14,B1:I1,0):使用 MATCH 函數,將儲存格B14的內容和儲存格B1:I1中的內容比對,傳回位於第幾欄的數值。

MATCH(B14,B1:I1,0)-1:因為 OFFSET 是以儲存格B2為起始儲存格,再以相對位址取得儲存格範圍,其以第 0 欄、第 0 列為第一個儲存格。為了查詢每個級距的「勞工」,所以使用 MATCH 函數再減 1,即是以 0 為第一個儲存格。

儲存格B17:=OFFSET(B2,B15,MATCH(B14,B1:I1,0),,)

MATCH(B14,B1:I1,0):為了查詢每個級距的「單位」,其為 MATCH(B14,B1:I1,0)-1+1 的結果。

2014年7月3日 星期四

Excel-符合多個條件(垂直和水平)者的小計(SUMPRODUCT)

有網友問到:在 Excel 中的一個資料清單,如何計算符合垂直和水平標題者的小計?

參考下圖,月份和人員(A、B、C)沒有固定順序且可能重覆。

為了解說方便,首先要定義名稱,先選取[公式/已定義之名稱/名稱管理員],定義以下名稱:

儲存格B1:G1:月份;儲存格A2:A5:人員;儲存格B2:G5:資料。

接著輸入公式:

儲存格B9:=SUMPRODUCT(資料*(人員=$A9)*(月份=B$8))

其中,月份及人員的各個欄位和標題列的名稱順序和是否重覆都不會影響公式的結果。

 

關於函數的相關說明,請參閱微軟網站:

SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

Excel-資料清單中的小計(SUMPRODUCT)

網友問到:在一個 Excel 的資料清單中,如果要根據某個欄位的資料來計算小計,該如何處理?這類問題很適合使用 SUMPRODUCT 函數來運算!

儲存格I2:=SUMPRODUCT(($C$2:$C$16=H2)*$D$2:$D$16)

複製儲存格I2,貼至儲存格I2:I4。

在 SUMPRODUCT 函數中使用 SUMPRODUCT((=區小姐?)*(小計)),其中的「*」運算,可以將邏輯運算結果的 TRUE/FALSE 陣列,轉換為 1/0 陣列,再和「小計」一起計算其「乘積和」。

關於函數的詳細說明,請參閱微軟網站:

SUMPRODUCT:http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

2014年7月1日 星期二

Excel-通訊錄查詢(INDEX,MATCH,OFFSET,VLOOKUP)

有網友問到:如果想要在一個 Excel 的通訊錄清單中,想要藉由下拉式清單來查詢名單中的資料,該如何處理呢?

大多數網友在使用 Excel 來查詢資料時,都會用到幾個常用的查詢函數,藉由這個例子再來練習相關的函數。

參閱下圖,學號是一個唯一值,就用學號來做為查詢的關鍵字。

參考以下步驟:

1. 選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:學號。

2. 選取儲存格H2,選取[資料/資料工具/資料驗證]指令,設定:

儲存格內允許:清單:來源:「=學號」。

即設定儲存格中的資料清單在「學號」 的儲存格範圍。

當你選取儲存格H2的下拉式清單時,即可選取一個學號。

3. 在儲存格I2中輸入公式:(以下提供三種公式來練習)

(1) 使用 VLOOKUP 函數

儲存格I2:=VLOOKUP($H$2,$A$2:$F$25,COLUMN(B:B))

複製儲存格I2,貼至儲存格I2:M2

COLUMN(B:B)=2,向右複製會產生COLUMN(C:C)=3、COLUMN(D:D)=4、…。

(在此之2、3、4、…,是指第2欄、第3欄、第4欄、…)

相關函數詳細說明,請參考微軟網站:

VLOOKUP:http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。


 

(2) 使用 OFFSETMATCH 函數

儲存格I2:=OFFSET($A$1,MATCH($H$2,學號,0),COLUMN(A:A))

複製儲存格I2,貼至儲存格I2:M2

MATCH($H$2,學號,0):先使用 MATCH 函數,找到儲存格H2位於「學號」儲存格範圍中的第幾列。參數「0」為設定查詢學號必須完全一致者

再將這個結果代入 OFFSET 函數中,找出相對位置。

其中COLUMN(A:A)=1,向右複製會產生COLUMN(B:B)=2、COLUMN(C:C)=3、…。

(在此之2、3、4、…,是指位移2欄、位移3欄、位移4欄、…)

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

 

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

(3) 使用 INDEXMATCH 函數

儲存格I2:=INDEX($A$2:$F$25,MATCH($H$2,學號,0),COLUMN(B:B))

仿 (2) 的使用觀念,藉由 INDEX 函數在儲存格A2:F25範圍中,找出某一列的使用資料。

 

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

 

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

檢視其他文章

好康東東