贊助廠商

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

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

如果要讓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))

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 發表在 痞客邦 留言(13) 人氣()

假設取得一個學生成績資料表(共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))

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的用意是將文字轉成數字,才能進一步運算。

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)} (陣列公式)

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)

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)))

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

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

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

儲存格A3:1

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

儲存格C3:=IMREAL(B3)

儲存格D3:=IMAGINARY(B3)

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

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)

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列的格式),再設定顯示格式。

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

這次利用Excel函數來玩玩特殊的統計圖形。輸入公式:

儲存格A2:=(INT(RAND()*15)+1)/10 (產生0.1~1.5的亂數)

儲存格C2:=(INT(RAND()*15)+1)/10 (產生0.1~1.5的亂數)

儲存格B2:=SIN(A2/180*PI()) (將角度儲存格A2轉為徑度)

儲存格D2:=SIN(C2/180*PI()) (將角度儲存格C2轉為徑度)

儲存格A3:=A2+$A$2 (產生間距:A2的亂數值)(可以自行變化間距的大小)

儲存格C3:=C2+$C$2 (產生間距:C2的亂數值)(可以自行變化間距的大小)

將A3:D3往下複製10000筆。(數值愈大,速度會變慢。)

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

在Excel中,各種運算如果發生錯誤,都會以錯誤代表來表示。參考下表:

錯誤代碼 錯誤原因
##### 此錯誤表示欄的寬度不夠,無法顯示所有內容,或在儲存格中使用了負數日期或時間。
#DIV/0! 當某個數字以零 (0) 做為除數,或除數儲存格未內含值時,會顯示 #DIV/0! 錯誤。
#N/A 此錯誤表示某個函數或公式無法取得某個值。
#NAME? 此錯誤發生於 Microsoft Office Excel 無法辨識公式中的文字。
#NULL! 此錯誤發生於指定兩個不相交的交集區域。交集運算子是參照間的空白字元。
#NUM! 此錯誤表示公式或函數中有無效的數值。
#REF! 此錯誤發生於儲存格參照無效時。
#VALUE! 此錯誤發生於使用錯誤類型的引數或運算元 。

 

對於發生錯誤的儲存格,其左上角處會有綠色的小三角型指示器,當你選取這個儲存格時,會出現錯誤指示器(黃色+驚嘆號)。將滑鼠移至這個指示器上,第一個項目會指出這個錯誤的原因。如果你認為沒有錯誤,可以選取「忽略錯誤」。

如果想要了解這個錯誤發生的細部原因,則可以使用[公式/評估值公式],或是在錯誤指示器中選取[顯示計算步驟]選項,透過評估值逐步的檢查是那裡出錯。

你也可以使用[追蹤前導參照]和[追蹤從屬參照]來找尋錯誤來源。

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

如果在Excel中,想要間隔產生深淺的不同底色,而又不想一個儲存格一個儲存格的填底色,該如何做呢?

(1)先選取將要間隔填色的儲存格。

(2)設定格式化條件,選取[使用公式來決定格式化哪些儲存格]項目

(3)輸入公式:=MOD(ROW(),2)=0,會在偶數列上自動填入不同色彩,造成深淺間隔的不同列。

       


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

一般在版面設定的[工作表]標籤下,就可以設定列印範圍和標題列、欄。

 

當你設定完成後,Excel其實會設定Print_Area(列印範圍)和Print_Titles(標題列、欄),如果想要查看這兩個區域,只要在[名稱]位置點選,即會顯示所設定的範圍。

 

 

如果想要修改範圍,則也可以按一下Ctrl+F3鍵,在[名稱管理員]對話框中修改。記得在[參照到]位置,若修改了範圍,記得要按一下[V]符號。

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

在Office中要插入一些符號字元,則會看到字元代碼。根據這些字元代碼,試著在Excel中顯示這些符號。

image2

首先建立如下格式的資料表,其中各個類別的起紿符號,來自上述表單的字元代碼。

利用HEX2DEC()函數,將16進制改為10進制。

然後,輸入公式:

儲存格B2:=IF(CHAR($A2+COLUMN()-2)="?","",IFERROR(CHAR($A2+COLUMN()-2),""))

往右和往下複製即可。

其中若查到「?」或發生錯誤,則不顯示。

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

政府公佈的BMI值公式及身體體位的判定有一定的標準,參考下表:

一般人會帶入自己的身高和體重來求BMI值,而BMI值的公式為:BMI=體重(kg)/身高(m)的平方

然後再查表得知自己的體位為何。所以,

B3儲存格:=ROUND(B2/(B1/100)^2,1)  (ROUND()函數為取小數點1位)

B4儲存格:=IF(B3<A7,B7,IF(B3<=A8,B8,IF(B3<=A9,B9,IF(B3<=A10,B10,IF(B3<=A11,B11,B12)))))

然而,一般成年人的身高不太會再增/減,所以應該建立一個反推的體重範圍供參考比較好。

C6儲存格:="身高"&B1&"cm的體重範圍"

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼