贊助廠商

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

搜尋本部落格文章資料

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

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

在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) 人氣()

如果要建立一個0~63的十進制對二進制的對照表,而不想要一個數字一個數字的打,該如何產生呢?

依照下列說明,輸入公式:

儲存格A1:F1分別填入32, 16, 8, 4, ,2 ,1  (輔助用途)

觀察這個對照表可以發現,A中的0/1為每隔一個變換一次,B中的0/1為每隔二個變換一次,C中的0/1為每隔四個變換一次,D中的0/1為每隔八個變換一次,E中的0/1為每隔十六個變換一次,F中的0/1為每隔卅十二個變換一次。因此推出公式:

儲存格A3:=IF(ISODD(INT((ROW()-ROW($G$3))/A$1)),1,0)

將儲存格A3複製到A3:F66。

接著將二進制轉換為十進制:

儲存格G3:{=SUM(A3:F3*$A$1:$F$1)} (陣列公式)

將儲存格G3複製到G66。

建立好第一個表後,要根據第一個表轉換出第二個表。第二個表是將A到F中,為1的10進制數字搜集在一起,並依小到大排序排好。

儲存格I3:I34分別填入1, 2, 3, … , 32。  (輔助用途)

儲存格J3:=SMALL(IF(A$3:A$66=1,$G$3:$G$66),$I3)

將儲存格J3複製到J3:O32。

儲存格Q3:{=SMALL(IF($F$3:$F$66=1,$G$3:$G$66),(ROW()-ROW(Q$3))*6+COLUMN()-COLUMN($Q1)+1)} (陣列公式)

複製到Q3:V7,Q8,R8。

儲存格X3:{=SMALL(IF($E$3:$E$66=1,$G$3:$G$66),(ROW()-ROW(Q$3))*6+COLUMN()-COLUMN($X3)+1)} (陣列公式)

複製到X3:AC7,X8,Y8。

儲存格Q11:{=SMALL(IF($D$3:$D$66=1,$G$3:$G$66),(ROW()-ROW(Q$11))*6+COLUMN()-COLUMN($Q11)+1)} (陣列公式)

複製到Q11:V15,Q16,R16。

儲存格X11:{=SMALL(IF($C$3:$C$66=1,$G$3:$G$66),(ROW()-ROW(X$11))*6+COLUMN()-COLUMN($X11)+1)} (陣列公式)

複製到X11:AC15,X16,Y16。

儲存格Q20:{=SMALL(IF($B$3:$B$66=1,$G$3:$G$66),(ROW()-ROW(Q$20))*6+COLUMN()-COLUMN($Q20)+1)} (陣列公式)

複製到Q20:V24,Q25,R25。

儲存格X20:{=SMALL(IF($A$3:$A$66=1,$G$3:$G$66),(ROW()-ROW(X$20))*6+COLUMN()-COLUMN($X20)+1)} (陣列公式)

複製到X20:AC24,X25,Y25。

這個練習是將一個表摘要成另外兩種表,觀念可以應用到更多位元。(註:其中並沒有用到Excel 2007中的十進制/二進制轉換的函數)

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

一般使用計算機要計算10位元數以上的乘法幾乎是不太可能,如果使用電腦程式來運算,也只能得到有效位數有限的近似值。如果用Excel來設計一個10位元數以上的乘法,並且能看到運算過程和標準結果呢?如下圖,這是一個以12位元數乘以12位元數的例子。

儲存格Y3:=MOD($Y$2*Y$1+INT(($Y$2*Z$1+INT($Y$2*AA$1/10))/10),10)

儲存格X4:=MOD($X$2*Y$1+INT(($X$2*Z$1+INT($X$2*AA$1/10))/10),10)

儲存格W5:=MOD($W$2*Y$1+INT(($W$2*Z$1+INT($W$2*AA$1/10))/10),10)

儲存格V6:=MOD($V$2*Y$1+INT(($V$2*Z$1+INT($V$2*AA$1/10))/10),10)

儲存格U7:=MOD($U$2*Y$1+INT(($U$2*Z$1+INT($U$2*AA$1/10))/10),10)

儲存格T8:=MOD($T$2*Y$1+INT(($T$2*Z$1+INT($T$2*AA$1/10))/10),10)

儲存格S9:=MOD($S$2*Y$1+INT(($S$2*Z$1+INT($S$2*AA$1/10))/10),10)

儲存格R10:=MOD($R$2*Y$1+INT(($R$2*Z$1+INT($R$2*AA$1/10))/10),10)

儲存格Q11:=MOD($Q$2*Y$1+INT(($Q$2*Z$1+INT($Q$2*AA$1/10))/10),10)

儲存格P12:=MOD($P$2*Y$1+INT(($P$2*Z$1+INT($P$2*AA$1/10))/10),10)

儲存格O13:=MOD($O$2*Y$1+INT(($O$2*Z$1+INT($O$2*AA$1/10))/10),10)

儲存格N14:=MOD($N$2*Y$1+INT(($N$2*Z$1+INT($N$2*AA$1/10))/10),10)

比較一下這些儲存格,你就會發現其間的規則,並將儲存格往左邊複製。

儲存格Y15:=MOD(SUM(Y3:Y14)+INT((SUM(Z3:Z14)+INT(SUM(AA3:AA14))/10)/10),10)

將儲存格往左邊複製。

有了這個好用的工具,可以得到一些有趣的乘法結果,參考下圖:

111111111 X 111111111 = 12345678987654321

上求的算式,如果使用Excel儲存格相乘,將得到1.23457E+16,即使修正儲存格數值格式,也只能得到12345678987654300,而非精準的答案。但是使用上述的計算方式,可以得到完整的結果。

你可以修改這個Excel試算表,減少位數或增加位數,甚至求得100位數X100位數,也不是難事。

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

在電腦網路中若要設定固定IP,則必須設定子網路遮罩(如下表),作為判斷子網路之用。試著在Excel中寫一個可以說明子網路遮罩運作的試算結果。

建立如下的表格:

儲存格B3:=REPT("0",8-LEN(DEC2BIN(B1)))&DEC2BIN(B1)

將儲存格B3複製到C3:E3。

儲存格B4:

=IF(INT(I3/8)>=4,REPT("0",8),IF(INT(I3/8)>=3,REPT("1",32-I3)&REPT("0",I3-24),REPT("1",8)))

儲存格C4:

=IF(INT(I3/8)>=3,REPT("0",8),IF(INT(I3/8)>=2,REPT("1",24-I3)&REPT("0",I3-16),REPT("1",8)))

儲存格D4:

=IF(INT(I3/8)>=2,REPT("0",8),IF(INT(I3/8)>=1,REPT("1",16-I3)&REPT("0",I3-8),REPT("1",8)))

儲存格E4:

=IF(INT(I3/8)>=1,REPT("0",8),REPT("1",8-I3)&REPT("0",I3))

儲存格B5:

=IF(AND(--MID(B3,1,1),--MID(B4,1,1)),1,0)&IF(AND(--MID(B3,2,1),--MID(B4,2,1)),1,0)&IF(AND(--MID(B3,3,1),--MID(B4,3,1)),1,0)&IF(AND(--MID(B3,4,1),--MID(B4,4,1)),1,0)&IF(AND(--MID(B3,5,1),--MID(B4,5,1)),1,0)&IF(AND(--MID(B3,6,1),--MID(B4,6,1)),1,0)&IF(AND(--MID(B3,7,1),--MID(B4,7,1)),1,0)&IF(AND(--MID(B3,8,1),--MID(B4,8,1)),1,0)

儲存格G3:

=BIN2DEC(B3)&"."&BIN2DEC(C3)&"."&BIN2DEC(D3)&"."&BIN2DEC(E3)&"/"&I3

儲存格G4:

=BIN2DEC(B4)&"."&BIN2DEC(C4)&"."&BIN2DEC(D4)&"."&BIN2DEC(E4)

儲存格G5:

=BIN2DEC(B5)&"."&BIN2DEC(C5)&"."&BIN2DEC(D5)&"."&BIN2DEC(E5)

將儲存格B5複製到C5:E5。

其中用到的公式有:

(1)DEC2BIN()函數:將10進制數轉換為2進制數

(2)LEN()函數:計算字串的長度

(3)REPT()函數:重覆顯示某個字元

(4)INT()函數:取不大於的最大整數

(5)AND()函數:取兩個數的AND邏輯運算

(6)MID()函數:在一個字串中,從中間第n個字取m個字元

其中還用到以&符號來連接字元,而--MID()乃將MID取得的字元轉換為數字,以方便做AND運算。

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

在Excel中,如果輸入一個複數,並將複數各個次方分解實數和虛數,再將實數和虛數繪成統計圖(帶有平滑線的XY分佈圖),將會呈現一個螺旋狀圖形。

先將一個複數輸入於儲存格B1(例如1+0.5j),再輸入公式:

儲存格A3:1

儲存格B3:=IMPOWER($B$1,A3)

儲存格C3:=IMREAL(B3)

儲存格D3:=IMAGINARY(B3)

選取A3:D3,複製至A27:D27,如下圖:

選取C3:D27,指定帶有平滑線的XY分佈圖的統計圖,稍微修改座標軸格式,可得如下的螺旋狀圖形。

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

一般常用的羅馬數字大多是1-10,Excel中提供了ROMAN()函數,來轉換數字為羅馬數字。

例如:儲存格B2:=ROMAN(A2)

看看其他規則:

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

如果要製作tan()函數的圖形,則要先求得tan()函數的結果。輸入公式:

儲存格A4:=B4/180

(其中儲存格A4的格式自訂為:# ??/?0,可以表示成分數)

儲存格C4:=TAN((B4/180)*PI()) (要將角度轉為弳度)

往下複製數百個。

image1

選取A欄和B欄,約數百個,再選取帶有平滑線XY分佈圖的統計圖,即可畫出tan()的圖形。

image2

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

如果想要在Excel中產生某兩個數之間的整數亂數,該如何處理呢?

在Excel 2003中,你要輸入以下的公式:

儲存格A7:=INT(RAND()*($B$4-$A$4+1))+$A$4

其中INT()函數的功用為取一個不大於的最大整數。

如果是在Excel 2007以下,你還有另一個選擇:RANDBETWEEN()函數。

儲存格B7:=RANDBETWEEN($A$4,$B$4)

只要給予頭尾兩個數,即會產生想要的亂數。

註:這兩個公式都可能會產生包含頭尾兩個數的亂數。

 

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

在Excel中如果要顯PI的有效位數,預設只有10個位數(例如儲存格A2),如果想要顯示15位數呢?(Excel中最多的有效位數為15位)

例如,可以設定數值格式為自訂:0.??????????????,結果如儲存格A2。

如果設定更多位元,其將以空白顯示,例如儲存格A3。

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

日前取得一個世界各地區和台灣之間的時差,其時差有正、有負,也有零。依據這個表,該如何顯示現在世界各國的時間呢?

首先,在儲存格B27中輸入公式:=NOW(),以顯示目前的台灣時間(你的電腦必須有正確的日期和時間)。

在儲存格B30中輸入公式:=NOW()+SIGN(B2)*TIME(ABS(TRUNC(B2,0)),MOD(B2,1)*60,0)

然後複製到其他儲存格,就大功告成了。只要按一下F9,即會立即顯示世界各國的時間。

解析一下公式:

MOD(B2,1)*60

利用將時差(儲存格B2)除以1的餘數,可以求得小數點,再乘以60,即可以得秒數(例如:0.5X60=30)

 

ABS(TRUNC(B2,0))

TRUNC:將數字中的小數部分捨去而成為整數。

TRUNC(number,num_digits)

Number:為所要處理的數字。

Num_digits:對數值執行捨去計算時所採用的精確位數。其預設值為 0。

ABS()函數為:傳回數字的絕對值。

ABS(TRUNC(B2,0))乃是取得時差的整數部份(不含正負符號)

 

TIME(ABS(TRUNC(B2,0)),MOD(B2,1)*60,0)

將取得的時、分、秒代入TIME()函數,轉成時間格式。(注意:時分秒的數字不可以為負數)

 

SIGN(B2)*TIME(ABS(TRUNC(B2,0)),MOD(B2,1)*60,0)

將轉換後的時間再乘以原時差的正負符號(SIGN()函數可以取得正負符號)

 

NOW()+SIGN(B2)*TIME(ABS(TRUNC(B2,0)),MOD(B2,1)*60,0)

將現在的時間來加上時差(有正、有負、有零),藉由按F9,重新計算結果,就可以取得目前的世界各國時間了。

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

在一個Table中,如果想要動態顯示垂直資料和水平資料的交點,該如何做呢?例如在一個九九乘法表中,當輸入(或亂數產生)乘數和被乘數(儲存格A1和儲存格C1),可以反白方式指出Table中的運算位置。

最快速的做法是利用格式化條件來處理。

(1)選取B4:J12,設定公式=$A4=$A$1 (設定垂直方向的格式),再設定顯示格式

(2)選取B4:J12,設定公式=B$3=$C$1 (設定水平方向的格式),再設定顯示格式

(3)選取B3:J3,設定公式=B$3=$C$1 (設定第1列的格式),再設定顯示格式。

(4)選取B3:J3,設定公式=$A4=$A$1B (設定第1欄的格式),再設定顯示格式。

試著產生以下兩種條件化格式效果:

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼