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) number：from_units 中要轉換的值。 from_unit：number 的單位。 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"