2011年4月30日 星期六

Excel-在LOOKUP中使用兩個條件

在 Excel 中有一個資料(如下圖左),要由這個資料表中查詢對應的數值,並標示其位址,該如何處理?這次要在LOOKUP函數中使用兩個件來進行查詢。

首先定義三個資料範圍:DA:儲存格A3:A26,DB:儲存格B3:B26,DC:儲存格C3:C26。

(1) 查詢資料

儲存格G3:=IFERROR(LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26),"X")

LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26)

=LOOKUP(1,1/{True,False,True,False,False,…}*{Fasle,False,True,True,False,…},{1,2,3,4,5,…})

=LOOKUP(1,1/{0,0,1,0,0,…},{1,2,3,4,5,…})

=LOOKUP(1,{#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!,…},{1,2,3,4,5,…})

=536

使用IFERROR函數,讓查不到結果的儲存格顯示「X」,而不顯示錯誤訊息。

(2) 查詢位址

儲存格H3:{=IFERROR(ADDRESS(SUM(IF((E3=DA)*(F3=DB)*(G3=DC),ROW($3:$26))),3),"")}

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

{SUM(IF((E3=DA)*(F3=DB)*(G3=DC),ROW($3:$26))}

=SUM(IF({True,False,True,False,…}*{Fasle,False,True,True,…}*{False,False,True,False},{3,4,5,6…}))

=SUM(IF({0,0,1,0,…},{3,4,5,6,…}))

=5

使用IFERROR函數,讓查不到結果的儲存格顯示空白,而不顯示錯誤訊息。

複製儲存格G3:H3,往下貼上。

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

LOOKUP函數:http://office.microsoft.com/zh-tw/excel-help/HP010342671.aspx

LOOKUP:從單列、單欄範圍或陣列中傳回一個值。

語法:LOOKUP(lookup_value,  array)

lookup_value:在陣列中搜尋的值。

array:此引數包含文字、數字,或要與lookup_value比較的邏輯值之儲存格範圍。

 

ADDRESS函數:http://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

 

ADDRESS:在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:指定要傳回之參照類型的數值。(1或省略→絕對儲存格參照,2→列:絕對;欄:相對,3→列:相對;欄:絕對,4相對參照

2011年4月29日 星期五

Excel-自動畫出資料的分隔線

在 Excel 中取得一個資料表(如下圖),其中已經按生日欄位排序,如何讓 Excel 依間隔5歲自動畫出分隔線?即以31-35,36-40,41-45,…為間隔。

只要藉助設定格式化的條件規則,即可自動完成。

(1) 選取儲存格A3:E25。注意:不選取儲存格A2:E2。

(2) 設定條件格式化規則為:使用公式來決定要格式化哪些儲存格,並設定公式為「=INT(($D3-1)/5)<>INT(($D2-1)/5)」。

因為要以1-5為一組,6-10為一組,所以INT(($D3-1)/5),可以取得(年齡+1)除以5的商。用來判斷自已和上一個儲存格是否不相同。

(3) 設定條件成立的規則為儲存格的上框線畫上紅色實心線條。

如果資料表以單位欄位先排序,再依證照欄位排序,然後要在不同單位間畫上綠色實線,而在不同證照間畫上紅色虛線(參考下圖),該如何處理?

這個工作必須在設定格式化的條件規則時,以二個條件來處理,且必須注意條件的先後次序。

(1) 選取儲存格A3:E25。注意:不選取儲存格A2:E2。

(2) 設定條件格式化規則為:使用公式來決定要格式化哪些儲存格,並設定公式為「=$C3<>$C2」。

(3) 設定條件成立的規則為儲存格的上框線畫上紅色虛線線條。

(4) 選取儲存格A3:E25。注意:不選取儲存格A2:E2。

(5) 設定條件格式化規則為:使用公式來決定要格式化哪些儲存格,並設定公式為「=$E3<>$E2」。

(6) 設定條件成立的規則為儲存格的上框線畫上綠色實心線條。

你可以使用上移或下移按鈕,來調整規則的順序。

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"

2011年4月26日 星期二

Excel-計算字串中的字元數量

在 Excel 的儲存格中輸入了多個字元的字串,如何來計算這些字元的數量呢?(參考下圖)

儲存格C2:=LEN($A2)-LEN(SUBSTITUTE($A2,C$1,""))

原理是:將儲存格C2的字串總長度(字元數),減掉去除某個字元的字串總長度(字元數)。

複製儲存格C2至儲存格C2:L12。

當你有了以上的每個字元的數量,如果要計算總和(參考13列)是很容的事。如果你不想透過先將每一列中每個字元的數量算出來,直接想得到總和,該如何做呢?

儲存格C13:{=SUM(LEN($A2:$A12)-LEN(SUBSTITUTE($A2:$A12,C$1,"")))}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格C13至儲存格C13:L13。

觀察前後兩個公式,是不是十分相像,只差在是否為陣列運算,將一個儲存格改成儲存格範圍。

Excel-欄名和欄數的轉換

有人問到 Excel 2010 的最大欄數是XFD,這個數字代表第幾欄呢?我們可以把Excel的欄名順序看成是一種26進制的表示法,A, ~ , Z, AA ~ AZ, BA ~ ZZ, AAA ~ XFD。

以3個位元字母的第XFD欄(最後一欄)為例:

XFD1:=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),ROW(),"") 

=SUBSTITUTE(ADDRESS(1,16384,4),ROW(),"") [取得列數和欄數]

=SUBSTITUTE("XFD1",ROW(),"")  [將列數和欄數轉成欄名列號字串]

=SUBSTITUTE("XFD1",1,"")  [將欄名列號字串中的列號去掉]

=XFD

XFD2:=(CODE(MID(XFD1,1,1))-64)*26*26+(CODE(MID(XFD1,2,1))-64)*26+(CODE(MID(XFD1,3,1))-64)

因為 A~Z 有26個,所以看成是26進制的觀念。

MID(XFD1,1,1)="X",MID(XFD1,2,1)="F",MID(XFD1,1,3)="D"。

CODE(MID(XFD1,1,1))-64 = CODE("X")-64 = 88–64 = 24

CODE(MID(XFD1,2,1))-64 = CODE("F")-64 = 70–64 = 6

CODE(MID(XFD1,3,1))-64 = CODE("D")-64 = 68–64 = 4

XFD2:= 24*26*26+6*26+4 = 16384

複製XFD1:XFD2,可以貼在所有3個位元字母的欄名儲存格中。

由此看出Excel 2010的最後一欄是編號16384(=65536/4)。

如果想要不管1,2,3位元的欄位名稱都能適用,則儲存格XFD2中的公式改為:

= CHOOSE(LEN(XFD1),CODE(MID(XFD1,1,1))-64,(CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64)

利用LEN函數判斷儲存格的欄位名稱有幾個位元,再利用CHOOSE函數來選擇:

1:CODE(MID(XFD1,1,1))-64

2:CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64

3:CODE(MID(XFD1,2,1)))-64CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64

2011年4月25日 星期一

Excel-將時間轉成十進制表示

在 Excel 中如果要將時間轉為十進制表示,該如何處理呢?以下先以「分」來列舉,1-60分鐘如果要轉換成小時,而以十進制表示(請參考下表),則每1分鐘代表1/60小時。

如果要將一個時間(時:分)轉成十進制表示,則:

儲存格B2:=TEXT(HOUR(A2),"00") & "." &SUBSTITUTE(MINUTE(A2)/6,".","")

HOUR(A2)為取出儲存格A2中的時間之「時」的部分,TEXT(HOUR(A2),"00") 則將格式設定為以二位數表示,不足二位數者要補0。

MINUTE(A2)/6乃將儲存格A2中的時間之「分」的部分除以6,而SUBSTITUTE(MINUTE(A2)/6,".","")乃將MINUTE(A2)/6所得數值的部分之小數點去除(即左移一位,例:0.16666666變為.01666666),相當於除以10。所以整個公式為將儲存格A2中的時間之「分」的部分除以60。請觀察下圖較容易理解:

如果想要取這個十進制來運算,則可以使用VALUE函數將其轉換為數字。

儲存格B2:=VALUE(TEXT(HOUR(A2),"00") & "." &SUBSTITUTE(MINUTE(A2)/6,".",""))

2011年4月24日 星期日

Excel-改變間隔列的底色

在 Excel 中如果想要有規律的改變間隔列的底色(參考下圖)該如何處理呢?假設第1列的數字做為標記間隔列數,並依先深色間隔白色的規律。

儲存格B2:=LEFT(MOD(ROW()-2,B$1*2)<B$1,1),複製儲存格B2至儲存格B2:G25。

公式中的MOD(ROW()-2,B$1*2)會得到如下圖的數字,MOD(ROW()-2,B$1*2)<B$1會得到TURE/FALSE的數列,透過LEFT函數取出第一個字母。

因此,只要將儲存格B2:G25的格式化條件設定為=MOD(ROW()-2,B$1*2)<B$1,成立時改變其底色即可。

你試著練習,若是變成下圖的底色變化(依先白色間隔深色的規律)該如何做呢?

只要將格式化條件的公式改為:=MOD(ROW()-2,B$1*2)>=B$1,即可完成。

Excel-採計部分成績的平均

在 Excel 中取得一個學生考試成績記錄表(參考下圖),現在有部分成績將不予採計,該如何計算平均呢?

採用的方式是在各個成績欄位上標記「X」記號,表示該成績不予計算,而且該欄位的成績會予以淡化。

(1) 計算平均

儲存格M3:{=AVERAGE(IF(UPPER($C$1:$L$1)<>"X",C3:L3,FALSE))}

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

透過UPPER函數可以接受「X、x」的標示,公式中將不符合條件者,給予FALSE,該數將不會列入平均。

(2) 不採計的分數予以淡化

選取儲存格C3:L42(有40個學生),設定條件公式:UPPER(C$1)="X",文字色彩為淡灰色。

因為採計標示(X)在第一列,所以C$1之第一列要採用絶對位址表示。

2011年4月23日 星期六

Excel-計算學生的分組成績

在 Excel 中取得學生成績表(如下圖),因為學生成績是依座號順序輸入,而學生是以亂數分組而成。共有40位學生,每4位學生一組,共分為10組。現在要根據這個成績表,來計算A成績的最高分、B成績的不及格數和三個科目的加權平均成績。

請注意:以下均為這是陣列公式,輸入完成應按 Ctrl+Shift+Enter 鍵。

(1) A成績的最高分

{=MAX(($C$3:$C$42=$H3)*D$3:D$42)}

=MAX((符合組別1的True/False陣列)*A成績陣列)

=取出陣列中的最大值

(2) B成績的不及格數

{=SUM(($C$3:$C$42=$H3)*(E$3:E$42<60)*1)}

=SUM((符合組別1的True/False陣列)*(B成績小於60的True/False陣列*1)

=計算所有1的和

(3) 三個科目的加權平均成績

加權平均是指:以所有人的(A成績*2+B成績*3+C成績*4)/(2+3+4)之總和,再除以4,當為加權平均成績。

{=SUM(($C$3:$C$42=$H3)*D$3:F$42*$D$1:$F$1)/SUM($D$1:$F$1)/4}

複製儲存格I3:K3至儲存格I3:K10。

你可以試著以第1組的學生成績來驗算。

Excel-用符號字元繪製統計圖表

當你在 Excel 取得一個資料表(如下圖左),想要以符號字元(例如:「■、□」字元)來繪製達成率的統計圖表,該如何做呢?

特別注意圖表是以月份為中心,左邊為未達目標(達成率為負數),右邊為超過目標(達成率為正數)。而且未達目標者是以中心往左填入「■」字元,而超過目標者是以中心往右填入「■」字元。

儲存格E2:=IF(D2<0,REPT("□",10+ROUND(D2*100,0))&REPT("■",-ROUND(D2*100,0)),REPT("□",10))

利用ROUND函數將達成率乘以100後四捨五入,再以REPT函數重覆「■」字元。因為全為要填滿10個字元,所以其餘以「□」字元呈現。而且要先判斷達成率如果是負數才填入,如果不是負數,則填入10個「□」字元。

儲存格G2:=IF(D2>0,REPT("■",ROUND(D2*100,0))&REPT("□",10-ROUND(D2*100,0)),REPT("□",10))

同上原理,先判斷達成率如果是正數才填入,如果不是正數則,填入10個「■」字元。

複製儲存格E2至儲存格E2:E13,複製儲存格G2至儲存格G2:G13。

REPT函數說明:

REPT:依指定的次數重複顯示文字。

語法:REPT(text, number_times)

text:要重複的文字。

number_times:必須是一個正數,用以指定要重複文字的次數。

ROUND函數說明:

ROUND:對數字執行所指定數字位數的四捨五入計算。

語法:ROUND(number, num_digits)

number:要執行四捨五入計算的數字。

num_digits:指定數字引數執行四捨五入計算時的位數。

 

2011年4月22日 星期五

Excel-時間的排名與總和

如果在 Excel 中的取得一個時間的數列(含分:秒),如何計算排名與總和呢?

儲存格C2:=RANK(B2,$B$2:$B$16,1)

複製儲存格C2至儲存格C2:C16。

RANK函數中的參數1,代表要遞增排序。(參數0,代表要遞減排序)

(2) 計算總和

儲存格B17:=SUM(B2:B16)

將儲存格B17的數值格式自訂為[hh]:mm:ss,可以發現在「時」的部分是超過24小時,所以可反應真實的「時數」。同理也可以設定:[mm]:ss,只會計算至「分」的部分。

Excel-分離Email地址的帳號和伺服器名稱

在 Excel 中如果取得一個Email地址的清單,如果想要將Email中的帳號和伺服器名稱分離出來,該如何處理呢?

儲存格B2:=LEFT(A2,FIND("@",A2)-1)

儲存格C2:=RIGHT(A2,LEN(A2)-FIND("@",A2)-1)

複製儲存格B2:C2,往下貼上即可。

FIND:在某個文字字串內找到另一個文字字串,並傳回該文字字串在第一個文字字串中的起始位置。

語法:FIND(find_text, within_text, [start_num])

Find_text:要尋找的文字。

Within_text:包含所要尋找之文字的文字。

Start_num:指定開始搜尋的字元。如果省略 start_num,會假設其值為 1

Excel-標示數列中重覆的數

在 Excel 中取得一個數列(如下圖),想要標示出數列中不重覆的數、重覆的數、重覆的數但不包第1個、排除重覆的數(圖中深色底、紅色字的儲存格為標示)。這次要以設定格式化條件來處理,而且只要使用COUNTIF函數即可。

(1) 重覆的數

選取儲存格B2:B20,設定公式條件:=COUNTIF($B$2:$B$20,B2)=1

image

(2) 重覆的數

選取儲存格C2:C20,設定公式條件:=COUNTIF($C$2:$C$20,C2)>1

(3) 重覆的數但不包第1個

選取儲存格D2:D20,設定公式條件:=COUNTIF($D$2:$D2,D2)>1

(4) 排除重覆的數

選取儲存格E2:E20,設定公式條件:=COUNTIF($E$2:$E2,E2)=1

2011年4月21日 星期四

Excel-數列的運算

在 Excel 中常見各種數列,以下列舉各種數列的運算。假設數列位於儲存格B2:B16,並且命名為NumList。

(1) 將數列反列

儲存格C2:=OFFSET($B$2,COUNT(NumList)-ROW(1:1),,),複製後往下貼上。

COUNT(NumList)用以計算全部的數列有多少格。

(2) 由小到大排列

儲存格D2:=LARGE(NumList,ROW(1:1)),複製後往下貼上。

(3) 由大到小排列

儲存格E2:=SMALL(NumList,ROW(1:1)),複製後往下貼上。

(4) 負數的個數

儲存格H2:=COUNTIF(NumList,"<0")

(5) 正數的和

儲存格H3:=SUMIF(NumList,">0")

(6) 非0的和

儲存格H4:=SUMIF(NumList,"<>0")

(7) 負數的最大值

儲存格H5:{=MAX(IF(NumList<0,NumList,FALSE))}

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

其中FALSE參數非常重要,不可以0或空白來表示。

(8) 正數的最小值

儲存格H6:{=MIN(IF(NumList>0,NumList,FALSE))}

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

(9) -10~+10的和

儲存格H7:{=SUM((NumList>-10)*(NumList<10)*NumList)}

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

運算子「*」可以執行邏輯AND運算。

將Word表格貼在Windows Live Writer

在 Word 2010 中可以製作精美的表格(如下圖),如何將這個表格放在 Windows Live Writer 中使用呢?

(1) 在 Word 中複製這個表格。

(2) 在 Windows Live Write 文件中按一下右鍵,選取[選擇性貼上]選項。

(3) 選取[保持格式設定]選項。

貼上後的效果如下所示:

ABC

DEF

OPQ

XYZ

TUV

MNL

1992

1541

1615

1836

1931

1712

1919

1916

1172

1650

1560

1236

1282

1267

1753

1127

1456

1205

1102

1762

1452

1009

1983

1429

1667

1992

1631

1836

1389

1308

1142

1268

1805

1038

1860

如此便可善用 Word 的強大編輯功能,套用在自己的部落格文章中了。

你也可以利用 Excel 2010 來完成這個功能。

貼上後的效果如下所示:

ABC DEF OPQ XYZ TUV MNL
1992 1541 1615 1836 1931
1712 1919 1916 1172 1650
1560 1236 1282 1267 1753
1127 1456 1205 1102 1762
1452 1009 1983 1429 1667
1992 1631 1836 1389 1308
1142 1268 1805 1038 1860

好康東東