贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201104 (43)

瀏覽方式: 標題列表 簡短摘要

在 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),"")}

vincent 發表在 痞客邦 留言(2) 人氣()

在 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。

vincent 發表在 痞客邦 留言(0) 人氣()

在 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(各種類別) 。

vincent 發表在 痞客邦 留言(4) 人氣()

在 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。

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


vincent 發表在 痞客邦 留言(1) 人氣()

有人問到 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

vincent 發表在 痞客邦 留言(0) 人氣()

在 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,".",""))


vincent 發表在 痞客邦 留言(2) 人氣()

在 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,即可完成。


vincent 發表在 痞客邦 留言(0) 人氣()

在 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之第一列要採用絶對位址表示。


vincent 發表在 痞客邦 留言(0) 人氣()

在 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) 三個科目的加權平均成績

vincent 發表在 痞客邦 留言(0) 人氣()

當你在 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:要重複的文字。

vincent 發表在 痞客邦 留言(0) 人氣()

如果在 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,只會計算至「分」的部分。


vincent 發表在 痞客邦 留言(0) 人氣()

在 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


vincent 發表在 痞客邦 留言(2) 人氣()

在 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


vincent 發表在 痞客邦 留言(2) 人氣()

在 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) 正數的和

vincent 發表在 痞客邦 留言(0) 人氣()

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

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

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

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

貼上後的效果如下所示:

ABC

DEF

OPQ

XYZ

TUV

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 中,如果想要製作一個骰子的六個面(有六種點數),該如何處理呢?下圖紅色部分代表六種點數。

參考以下的範例,點數位於儲存格B1中:

儲存格B1:=RANDBETWEEN(1,6),藉以產生1~6的數字。

儲存格B2:=CHOOSE(B1,"","●","●","●","●","●")

儲存格D2:=CHOOSE(B1,"","","","●","●","●")

儲存格B3:=CHOOSE(B1,"","","","","","●")

儲存格C3:=CHOOSE(B1,"●","","●","","●","")

儲存格D3:=CHOOSE(B1,"","","","","","●")

儲存格B4:=CHOOSE(B1,"","","","●","●","●")

儲存格D4:=CHOOSE(B1,"","●","●","●","●","●")

根據儲存格B1的數字,對照是否要顯示「●」,否則顯示空白。

如何儲存格B2中的數字不會顯示出來呢?其實只要設這個儲存格的數值格式為「;;;」(三個分號),儲存格中的數字即不會顯示出來。只要每按一下 F9 鍵,亂數值即會修改。

vincent 發表在 痞客邦 留言(1) 人氣()

再次來練習在SUM函數中執行AND和OR運算,參考下圖。

設定名稱A:儲存格B2:B11、名稱B:儲存格C2:C11、名稱S:儲存格D2:D11。

以下是陣列公式者,輸入完成應按 Ctrl+Shift+Enter 鍵。

(1) 計算A>5,求B的和

儲存格H2:=SUMIF(A,">5",B)

(2) 計算A>5 and A<10,求A的和

儲存格H3:{=SUM((A>5)*(A<10)*A)}

={0,1,0,0,1,1,0,0,0,0}*{1,1,1,1,0,1,1,1,1,1}*A

={0,1,0,0,0,1,0,0,0,0}*A

=6+9

=15

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 中取得一個圖書館圖書借閱的摘要表,其中登錄號代表一本書,借書證號代表一個人。而這個資料表已經先以「借書證號」遞增排序好了。

現在,想要根據這個資料表,要來求得「借閱人次」。其中相同借閱人的借閱日期如果是同一天,則只能算一次。為了說明方便,則於下圖列出「起始列、終止列」的號碼(列號)。

由於這個資料表已經排序完成,假設資料範圍:A2:C2000,要先找出某個借書證號的資料區間。

(1) 計算起始列

儲存格F2:={MIN(IF($C$2:$C$2000=E2,ROW($C$2:$C$2000),""))}

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

(2) 計算終止列

儲存格G2:={MAX(IF($C$2:$C$2000=E2,ROW($C$2:$C$2000),""))}

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

詳細說明參考:Excel-範圍內的最大值和最小值

http://isvincent.blogspot.com/2011/04/excel_19.html

vincent 發表在 痞客邦 留言(1) 人氣()

在 Excel 中的資料範圍內有最大值和最小值,如果要找出第一個最小值、最後一個最小值、第一個最大值、最後一個最大值,該如何處理呢?(參考下圖)

(1) 第一個最小值

儲存格E2:{=ADDRESS(MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))}

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

公式中「IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")」,

可得陣列:{2,"","","","","",8,9,"","","","","","","","","","","","",}

MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),""))

=MIN({2,"","","","","",8,9,"","","","","","","","","","","","",})

=2

所以,=ADDRESS(MIN(IF(B2:B21=MIN(B2:B21),ROW(B2:B21),"")),COLUMN(B:B))

=ADDRESS(2,2))

vincent 發表在 痞客邦 留言(1) 人氣()

在 Excel 建立的一個成績表中(如下圖),如果要顯示成績評定,該如何處理?成績評定方式如下表:

image

image

你可能會用以下的公式:

儲存格F2:=IF(E2=100,"特優",IF(E2>=90,"優",IF(E2>=80,"良",IF(E2>=70,"佳",IF(E2>=60,"可","差")))))

複製儲存格F2至儲存格F2:F21。

利用IF函數的巢狀結構,經由多次判斷後取得成績評定結果。

你還可以使用定義名稱+VLOOKUP函數來以查詢方式評定成績:

先定義一個名稱grade,將grade設定為一個陣列,其中是由許多「分數+評定」所組成:

grade={0,"差",10,"差",20,"差",30,"差",40,"差",50,"差",60,"可",70,"佳",80,"良",90,"優",100,"特優"}

再利用VLOOKUP函數,以查表方式來取出成績評定。

vincent 發表在 痞客邦 留言(0) 人氣()

1 23

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼