贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2207)

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

前一篇文章說明查表法的應用:http://isvincent.blogspot.com/2010/05/excel_12.html

如果要將編號輸入改用微調按鈕來取代較為方便,該如何操作呢?

在Excel中如果要使用表單控制項,則必須先顯示這個工具。在Excel選項中的[自訂]項下,選取[開發人員索引標籤],新增[控制項]。

在自訂工具中會新增[控制項],按一下[插入],選取[微調按鈕],然後在工作表上拖曳出適當的大小。

在[控制項格式]對話框中設定[目前值]、[最小值]、[最大值]、[遞增值]及儲存格連結。

例如:要產生101~223範圍的數值,而微調結果要輸出於F1儲存格,則如下的設定:

image09

然後,再設定儲存格G2、G5、G8、G11的內容為:="A"&F1。

可以透過微調按鈕產生A101~A223的編號,進而查到該編號的資料。

關於查表法的應用,請參閱前一篇文章:http://isvincent.blogspot.com/2010/05/excel_12.html

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

在Excel中有一個基本表如下:

如果要藉由查表方求得個別一個人的資料(如下表),有那些方式可行?

 

儲存格G2:輸入一個編號,例如:A103

通常使用查表精靈,再稍微修後,可以得到公式:

儲存格H2:=INDEX($A$1:$E$124, MATCH($G2,$A$1:$A$124,), MATCH(H$1,$A$1:$E$1,))

將儲存格H2,複製到H2:K2。即可以根據儲存格G2,求得該編號的個人資料。

如果想要簡化公式,可以:

整個資料($A$1:$A$124)定義名稱為data

編號欄位($A$1:$A$124)定義名稱為ID

標題欄位($A$1:$E$1)定義為title

則可以簡化公式為:

儲存格H5:=INDEX(data, MATCH($G5,ID,), MATCH(H$1,title,))

其實還有更簡化的方式,利用VLOOKUP來查詢:

儲存格H8:=VLOOKUP($G$8,data,2,)

儲存格I8:=VLOOKUP($G$8,data,3,)

儲存格J8:=VLOOKUP($G$8,data,4,)

儲存格K8:=VLOOKUP($G$8,data,5,)

你也可以將公式調整為

儲存格H11:=VLOOKUP($G$11,data,COLUMN(B1),)

將儲存格H11,複製到H11:K11。

以上這些方式,都可達到查表的效果。

如果你要使用查閱精靈,則必選在Excel選項的[增益集]中選用,按一下[執行]按鈕,開啟[增益集]對話框。

勾選[查閱精靈],按一下[確定]按鈕。

加入的[查閱精靈],位於[公式]功能表之下:

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

如果有一個表格,其欄位中有分男、女要分開計算,而男、女是在間隔列中,該如何計算總和及平均呢?

如果利用SUMPRODUCT函數來計算總和:

儲存格G2:=SUMPRODUCT((MOD(ROW(C2:C25),2)=0)*(C2:C25))

儲存格H2:=SUMPRODUCT((MOD(ROW(C2:C25),2)=1)*(C2:C25))

MOD(ROW(C2:C25),2)=0,找出偶數列;MOD(ROW(C2:C25),2)=1,找出奇數列。

你還可以使用陣列方式來計算總和:

儲存格G3:{=SUM(IF(MOD(ROW(C2:C25),2)=0,C2:C25))}

儲存格H3:{=SUM(IF(MOD(ROW(C2:C25),2)=1,C2:C25))}

稍微改一下公式就可以計算平均:

儲存格G4:{=AVERAGE(IF(MOD(ROW(C2:C25),2)=0,C2:C25))}

儲存格H4:{=AVERAGE(IF(MOD(ROW(C2:C25),2)=1,C2:C25))}

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

試著使用Excel,根據A欄至C欄的表格,要轉換成一個「個數統計表」和「一個總和統計表」。

儲存格F1輸入:=SUMPRODUCT(($A$2:$A$17=$E2)*($B$2:$B$17=F$1))

複製儲存格F1,並在儲存格F1:J5貼上。

儲存格F9輸入:=SUMPRODUCT(($A$2:$A$17=$E9)*($B$2:$B$17=F$1)*$C$2:$C$17)

複製儲存格F9,並在儲存格F9:J13貼上。

 

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

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

Array1, array2, array3, ...   是 2 到 255 個欲求其對應元素乘積之和的陣列。

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

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

有時候接收到的文件中日期格式是沒有「/」或「-」這種分隔符號,例如「20100204」,但是這種資料放到Excel的工作表中,Excel只會把它當為數字來看。如果想要讓Excel認為它是日期格式,可能要做一些變化,例如:「2010/02/04」。

你可能會想要使用[資料剖析]的方式,將A欄的內容分成年、月、日三格,再將其合併成一個日期。以下的方式是以公式來調整。

在儲存格B2中輸入公式:=DATEVALUE(LEFT(A2,4)&"/"&MID(A2,5,2)&"/"&RIGHT(A2,2))

分別在儲存格A2中取出最左邊四碼、中間第五位數字取二碼、最右邊取二碼。

再放到DATEVALUE()中,即可轉變為日期格式。

在B欄中設定數值格式為「yyyy/mm/dd」,即可讓月份和日期都是二碼。

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

當你取得一個部落格文章發佈的記錄表(參考下圖),如何自動算出每日發佈的文章數量呢?

 

假設要設計一個表格,以週為單位,並且標示出每日文章發佈的文章數量。

首先在儲存格D2輸入一個起始日期(例如:2008/09/14),儲存格F2輸入公式:=D2+6。

儲存格D3輸入公式:=D2+7,儲存格F3輸入公式:=F2+7,將D2:F3往下複製。

在儲存格G2輸入公式:=COUNTIF($A$2:$A$504,$D2+COLUMN(A2)-1)。

並將儲存格G2複製到G2:M2,再複製儲存格G2:M2,並往下貼上。

其中儲存格$A$2:$A$504為日期的記錄。

公式中使用儲存格D2當為每週的第一天,所以其他各天的日期則以加上COLUMN(A2)-1來表示。

如此就可輕鬆將一個單純的記錄表轉換為以每週為單位來表示的報表了。

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

在Excel中如果想要知道每一個月的最後一天是那一天,又是星期幾,該如何做呢?

例如:

在儲存格C1中輸入公式:=EOMONTH(DATE(A2,B2,1),0)

在儲存格D1中輸入公式:=C2,然後設定數值格式為星期幾。

(其中C欄的數值格式設定為:yyyy/mm/dd)

EOMONTH 函數

語法 :

EOMONTH(start_date,months)

傳回在 start_date 之前或之後指定月數的這一個月最後一天的序列值。

Start_date:表示開始日期。必須使用 DATE 函數輸入日期,或其他的公式產生的日期。(若使用文字格式輸入日期將會發生問題。)

Months:為 start_date 之前或之後的月數。(正值表示未來日期;負值表示過去日期。如果月份不是整數,則只會取整數。)

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

在Excel中如果想要依序顯示某些字串,可以使用自訂清單的方式,非常方便。如果想要將某個儲存格中的字串,重複且依序的在某些儲存格中顯示,該如何做呢?參考下圖。

例如在儲存格A1中輸入字串,在儲存格B1中輸入公式:=MID($A$1,MOD(ROW()-1,LEN($A$1))+1,1)

將儲存格B1向下複製到其他儲存格即可。

利用MOD(ROW()-1,LEN($A$1))+1,藉由於ROW()來找出某個儲存格要顯示字串中的第幾個字元。

而藉由MID()函數,在字串中的第幾個位置取出1個字元。

這個公式套用在中文的字串也可以。

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

在Excel中如果想要輸入一個日期後,只顯示月或日或年的部分,可以使用自訂數值格式(參考下圖)。

其中:

[DBNum1]顯示:一、二、三、四 …

[DBNum2]顯示:壹、貳、参、肆 …

[DBNum3]顯示:1、2、3、4 …

[DBNum1]顯示:1、2、3、4 …

image01

你也可以自行變化為:

[DBNum1]m"月"d"日"

[DBNum2]m"月"d"日"

[DBNum3]y"年"m"月

[DBNum4]y"年"m"月

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

如果想要在Excel的工作表中顯示某個工作表的名稱,該如何做呢?

例如在Sheet1工作表的儲存格A1中輸入公式:

=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)

結果會得到Sheet2,如果工作表的名稱有所更改,其結果也會跟著變動。

其中CELL()函數會傳回有關儲存格之格式、位置或內容的資訊

語法:CELL(info_type, [reference])

info_type如下:

info_type 傳回
"address" 以文字形式表示 reference 中第一個儲存格的位址。
"col" Reference 中儲存格的欄名。
"color" 如果儲存格設定為會因負數而改變色彩的格式,則傳回 1;否則傳回 0 (零)。
"contents" 參照左上角儲存格的數值;不是公式。
"filename" 以文字形式傳回 reference 所屬文件的檔案名稱 (包含完整的路徑名稱)。如果該文件尚未存檔,則傳回空字串 ("")。
"format" 對應於數值儲存格格式的文字表示形式。各種格式的文字表示列示於以下的表格中。如果儲存格為會因負數而改變色彩,則傳回的文字值的後面會帶有負號 (-)。如果儲存格被設定為將正數或任何數值放在一組括弧中的格式時,則在傳回的文字值的後面加一組 "()"。
"parentheses" 如果儲存格格式設定為將正數或所有數值放在一組括弧中,傳回 1;否則傳回 0。
"prefix" 文字儲存格的「標籤首碼」的文字表示形式。如果該儲存格含有靠左對齊的文字時,傳回單引號 (');如果該儲存格中含有靠右對齊的文字時,傳回雙引號 (");如果該儲存格中含有置中對齊的文字時,傳回脫字符號 (^);如果該儲存格中含有填滿對齊的文字時,傳回反斜線 (\);如果該儲存格含有其他的資料,則傳回空字串 ("")。
"protect" 如果儲存格並未鎖定保護,傳回 0;如果儲存格已鎖定保護,則傳回 1。
"row" 參照位址中儲存格的列號。
"type" 儲存格中資料類型的一個對應文字值。如果該儲存格是空白的,傳回「b」(代表 blank),如果該儲存格含有文字常數,則傳回標籤「l」(代表 label);如果該儲存格中含有其他類別的資料,則傳回「v」(代表 value)。
"width" 儲存格欄寬四捨五入成整數值。每個欄寬單位都等於預設字型大小的一個字元寬度。

 

其中公式:

CELL("filename",Sheet2!A1)會傳回檔案的完整路徑,你必須先該檔案存檔。

得到結果例如:C:\Users\Administrator\Documents\[Book.xlsx]Sheet2

FIND("]",CELL("filename",Sheet2!A1))+1

找出"]”在第幾個位置,+1的目的是指定下一個位置

MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)

指在"]”的下一個位置取256個字,由於一個儲存格最多只能放256個字元,所以保證可以取得工作表的完整名稱。

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

在Excel中如果想要計算出生到今天的實際年齡,有沒有比較快速的方式呢?試試Datedif()函數。

語法:

DATEDIF(start_date,end_date,unit)

unit參數 說明
"Y" 週期中的整年數
"M" 週期中的整月數
"D" 週期中的天數
"MD" start_dateend_date間的天數差。(忽略日期中的月和年)
"YM" start_dateend_date間的月數差。(忽略日期中的日和年)
"YD" start_dateend_date間的天數差。(忽略日期中的年)

 

參考下例:

A為start_date,B為end_date,其結果C2即為兩個日期之間的整年數。

若A為你的出生日期,B為今天的日期(=now()),其結果(41)即為你現在的年齡(實歲,非虛歲)。

如果你想要查詢你出生已經幾天了,可以使用參數"D"。

image01

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

如果想要計算下圖中的總人數,將[合計]置於A欄,似乎有些浪費空間,而且可能會造成排版上的困擾。有什麼方法可以調整呢?

例如:

在儲存格D6中輸入公式:="合計" & SUM(D2:D5) & "人"

使用串接符號 &來串接公式,如果要修改公式時,會比較不方便。

或是使用數值格式設定方式:

在儲存格D6中設定數值格式,自訂格式為:"合計"?"人"

則只要在儲存D6中輸入公式:=SUM(D2:D5),即可以顯示串接文字結果,此處?代表儲存格的運算結果。

而如果你自訂格式時,使用@字元,例如"合計"@"人"

而在儲存格F6中輸入公式:=SUM(F2:F5)

則結果顯示:合計=SUM(F2:F5)人,因為@符號代表儲存格中輸入的內容。

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

在Excel中如果使用RANK函數來計算名次,如果出現相同名次,則後面的名次會被跳過。

例如:儲存格B2的公式為:=RANK(A2,$A$2:$A$15)

則第2名有2個,則沒有第3名,直接會跳到第4名。

如果想要重覆名次不跳過,則可以試試以下的公式:

儲存格C2:=SUMPRODUCT((A$2:A$15>A2)*(1/COUNTIF(A$2:A$15,A$2:A$15)))+1

(可參考先前的文章:http://isvincent.blogspot.com/2010/03/excel.html)

以本例儲存格C2而言:

1/COUNTIF(A$2:A$15,A$2:A$15)

=1/(1;3;1;2;2;1;1;1;1;3;3;1;1;1) (找出重覆的個數)

=(1;0.33;1;0.5;0.5;1;1;1;1;0.33;0.33;1;1;1)

SUMPRODUCT((A$2:A$15>A2)*(1/COUNTIF(A$2:A$15,A$2:A$15)))+1

=SUMPRODUCT((False;True;False;True;True,False;True;False;True;True;True;True;False;False)*(1;0.33;1;0.5;0.5;1;1;1;1;0.33;0.33;1;1;1))+1

=SUMPRODUCT(0;0.33;0;0.5;0.5;0;1;0;1;0.33;0.33;1;0;0))+1

(沒有重覆者為0;重覆者其各數之和分別為1)

=6 (找到前有1~5名,最後加1表示自己是第6名)

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

在儲存格中可能會出現錯誤訊息,所以在做總和(或其他運算)時,也可能會出現錯誤結果。

例如:儲存格H1運算公式為「=SUM(B2:G2)」,其結果為#VALUE!,無法得到運算結果。

如果想要忽略有錯誤訊息的儲存格,得出總和的運算結果,以下兩個方式可以實現:

儲存格H3:{=SUM(IF(NOT(ISERROR(B3:G3)),B3:G3,0))}

使用陣列公式,排除有錯誤訊息的儲存格才列入總和(即沒有錯誤者才算總和)。

儲存格H4:=SUMIF(B4:G4,"<9.9E+307")

這個運算式比較算單,因為根據Excel的規格,其最大的可容許正數:9.99999999999999E+307,所以利用SUMIF公式,合於條件的才列入計算。

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

如果要讓Excel自動求得當月的最大天數,必須藉助Day()函數,先來看看Day()函數的使用。

語法:DAY(serial_number)

以下表為例:

D2儲存格:=DAY(DATE(A2,B2,C2))

日期為2010年2月20日,所以顯示20,即第20天。

D3儲存格:=DAY(DATE(A3,B3,C3))

日期為2010年2月,則顯示前一個月的最後一天,即第31天。

D4儲存格:=DAY(DATE(A4,B4,C4))

日期為2010年3月,則顯示前一個月的最後一天,即第28天。(不同年份,會顯示不同結果。)

D5儲存格:=DAY(DATE(A5,B5+1,))

日期為2010年2月2日,套入公式將月份+1(3月),可以求得前一個月(2月)的天數。

image1  

所以如果要求得當月份的最大天數,則利用NOW()函數取得今天的日期,再套入公式:

=DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,))

 

即可求得本月的最大天數。

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

網路上看到這個計算不重覆的數值個數的解法還不錯,一起來分享一下。

在儲存格A2:A21是20個數列,要計算不重覆的數值個數,輸入公式:

儲存格D2:=SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21))

其中COUNTIF(A2:A21,A2:A21))可以算出在A欄中的數值重覆的個數。

而1/COUNTIF(A2:A21,A2:A21))取重覆個數的倒數,參考C欄。

利用SUMPRODUCT這個函數將全部的數值陣列加總,

其中重覆的字數不管重覆幾個加起來為1。

所以最後的結果會剛好為不重覆的個數。

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

假設取得一個學生成績資料表(共300筆資料),其為學生夜間讀書方式和模擬考名次的記錄表(以下資料為模擬產生)。

如果想要轉換成以下的統計報表,該如何處理呢?

首先,在儲存格F2輸入:=(ROW(F2)-2)*50+1 & "-" & (ROW(F2)-1)*50

(如果想要改變成績間距,只要改變公式中的50即可)

再將儲存格F2複製到F3:F7。

在儲存格G2輸入:=SUMPRODUCT(($C$2:$C$301>=VALUE(LEFT($F2,SEARCH("-",$F2)-1)))*($C$2:$C$301<=VALUE(RIGHT($F2,LEN($F2)-SEARCH("-",$F2))))*($B$2:$B$301=G$1))

再將儲存格G2複製到G2:K7。

 

其中:

SEARCH("-",$F2)-1)) 表示:找尋 -符號在儲存格F2中的第幾個字元

LEFT($F2,SEARCH("-",$F2)-1)) 表示:取出 -符號左邊的字元

VALUE(LEFT($F2,SEARCH("-",$F2)-1))) 表示:將取得的文字轉換為數字

接著:

RIGHT($F2,LEN($F2)-SEARCH("-",$F2))) 表示:取出 -符號右邊的字元

VALUE(RIGHT($F2,LEN($F2)-SEARCH("-",$F2)))) 表示:將取得的文字轉換為數字

最後:

利用SUMPRODUCT函數

=SUMPRODUCT(模考名次>=成績區間)*(模考名次<=成績區間)*(夜間讀書方式=標題)

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

有網友問到如下的表格,若要欄據ref.和color兩欄來查詢q.欄,該如何做呢?

假設A欄和B欄中的結果,不會有兩個以上相同結果,而q.欄又是數字,則最簡單的方式是:

儲存格F4使用陣列公式:{=SUM(IF(A2:A9=F2,IF(B2:B9=F3,C2:C9,)))}

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

國道高速公路局1月26日發布99年春節假期易壅塞路段、時段預測,並提供下表:

資料來源:http://www.freeway.gov.tw/default.aspx

大家要先好功課,免得浪費太多時間在塞車上。高速公路局表示,從2/13(除夕)至2/20(初七),會發布更精細國道路況預報,供用路人作為決定出發時間之參考,以避開易壅塞路段及時段。

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

在二進制的減法中,是以加法來處理減法,所以A-B可以看成是A+(-B)。而負數又是以2的補數來表示,將A+B的2的補數後相加,如果有進位,表示結果為正數,直接將進位捨去,就是答案;如果沒有進位,表示結果為負數,將結果取2的補數,再加上負號,就是答案。

儲存格F1:=RIGHT("00000000"&DEC2BIN(C1),8)

串接"00000000",是因為有些二進制數不滿8位元。

儲存格F2:=RIGHT("00000000"&DEC2BIN(-C2),8)

儲存格F5:=RIGHT("000000000"&DEC2BIN(255-BIN2DEC(F3)),8)

儲存格F7:=RIGHT("00000000"&DEC2BIN(BIN2DEC(--F5)+1),8)

--F5的用意是將文字轉成數字,才能進一步運算。

儲存格H3:=IF(LEFT(F3,1)="1","有進位,表示結果為正數。","")

儲存格H4:=IF(LEFT(F3,1)="1","直接將進位捨去,答案="&BIN2DEC(RIGHT(F3,8)),"")

儲存格H6:=IF(LEFT(F3,1)="0","沒有進位,表示結果為負數。","")

儲存格H7:=IF(LEFT(F3,1)="0","取2的補數加上負號,答案="&-BIN2DEC(--F7),"")

或許這個模擬補數運算的試算表,對於課程上會有些幫助。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼