2011年4月28日 星期四

Excel-製作單位轉換工具(多層選單)

在 Excel 中有一個單位轉換的函數:CONVERT,可以將多種類型的度量單位做轉換,例如:

=CONVERT(100,"F","C"),可以將100度的華氏溫度轉換為攝氏溫度。

參考微軟網站:http://office.microsoft.com/zh-tw/excel-help/HP010342306.aspx

CONVERT:將數字換算成不同的度量單位。

語法:CONVERT(number, from_unit, to_unit)

numberfrom_units 中要轉換的值。

from_unitnumber 的單位。

to_unit:換算後的單位。

如果我們想用這個函數來製作一個多種度量的轉換工具,該如何做呢?(參考下圖)

我們將會用到資料驗證和多層選單等觀念。

首先,將要用到的類別資料放在儲存格G2:G11中,接著將儲存格A2設定資料驗證,儲存格允許:清單;來源:=$G$2:$G$11(各種類別) 。

如此,在儲存格A2中即可以選取方式來輸入類別:

因為各種單位類別中的項目是放在儲存格I2:AB10中:

所以要根據儲存格A2中所選取的類別,找到其下所有的單位名稱,因此先定義DATA1:

DATA1:=OFFSET($I$1,1,MATCH($A$2,$I$1:$AB$1,0)-1,9,)

其根據儲存格A2的類別,在儲存格I1:AB1中尋出符合的位置。其中的參數9是因為各種度量類別,最多只有9種單位。

接著,將儲存格B4和儲存格E4設定資料驗證:

儲存格允許:清單;來源:=OFFSET($I$1,1,MATCH($A$2,I1:$AB$1,0)-1,COUNTA(DATA1),)

為何使用COUNTA(DATA1),是為了避免下拉式選單中,若不足9個單位時,出現空白的狀態。

接著,再定義DATA2:

DATA2:=OFFSET($I$1,1,MATCH($A$2,$I$1:$AB$1,0)-1,COUNTA(DATA1),2)

其中的參數2,是因為國字的單位是給人看的,而我們真正要取用的是其左欄英文字的單位,所以必須把國字和英文字兩欄變成一個儲存格範圍。例如「重量」的儲存格範圍是I2:J6。

接著使用VLOOKUP函數,以查表方式將國字的單位求得英文字的單位:

儲存格D4:=CONVERT(A4,VLOOKUP(B4,DATA2,2,FALSE),VLOOKUP(E4,DATA2,2,FALSE))

如此便可完成一個完整的單位換算工具。

如果不想使用定義名稱,則完整的公式內容如下:

儲存格D4:=CONVERT(A4,VLOOKUP(B4,OFFSET(I1,1,MATCH(A2,I1:AB1,0)-1,COUNTA(OFFSET(I1,1,MATCH(A2,I1:AB1,0)-1,20,)),2),2,FALSE),VLOOKUP(E4,OFFSET(I1,1,MATCH(A2,I1:AB1,0)-1,COUNTA(OFFSET(I1,1,MATCH(A2,I1:AB1,0)-1,20,)),2),2,FALSE))

你有觀察到各種單位的第一個字母,其實代表著度量的大小,參考下表:

字首

乘數

縮寫

exa

1E+18

"E"

peta

1E+15

"P"

tera

1E+12

"T"

giga

1E+09

"G"

mega

1E+06

"M"

kilo

1E+03

"k"

hecto

1E+02

"h"

dekao

1E+01

"e"

deci

1E-01

"d"

centi

1E-02

"c"

milli

1E-03

"m"

micro

1E-06

"u"

nano

1E-09

"n"

pico

1E-12

"p"

femto

1E-15

"f"

atto

1E-18

"a"

沒有留言:

張貼留言

好康東東