前一篇文章說明查表法的應用:http://isvincent.blogspot.com/2010/05/excel_12.html
如果要將編號輸入改用微調按鈕來取代較為方便,該如何操作呢?
在Excel中如果要使用表單控制項,則必須先顯示這個工具。在Excel選項中的[自訂]項下,選取[開發人員索引標籤],新增[控制項]。
在自訂工具中會新增[控制項],按一下[插入],選取[微調按鈕],然後在工作表上拖曳出適當的大小。
前一篇文章說明查表法的應用:http://isvincent.blogspot.com/2010/05/excel_12.html
如果要將編號輸入改用微調按鈕來取代較為方便,該如何操作呢?
在Excel中如果要使用表單控制項,則必須先顯示這個工具。在Excel選項中的[自訂]項下,選取[開發人員索引標籤],新增[控制項]。
在自訂工具中會新增[控制項],按一下[插入],選取[微調按鈕],然後在工作表上拖曳出適當的大小。
在Excel中有一個基本表如下:
如果要藉由查表方求得個別一個人的資料(如下表),有那些方式可行?
儲存格G2:輸入一個編號,例如:A103
通常使用查表精靈,再稍微修後,可以得到公式:
如果有一個表格,其欄位中有分男、女要分開計算,而男、女是在間隔列中,該如何計算總和及平均呢?
如果利用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,找出奇數列。
你還可以使用陣列方式來計算總和:
試著使用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貼上。
有時候接收到的文件中日期格式是沒有「/」或「-」這種分隔符號,例如「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」,即可讓月份和日期都是二碼。
當你取得一個部落格文章發佈的記錄表(參考下圖),如何自動算出每日發佈的文章數量呢?
假設要設計一個表格,以週為單位,並且標示出每日文章發佈的文章數量。
首先在儲存格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)。
在Excel中如果想要知道每一個月的最後一天是那一天,又是星期幾,該如何做呢?
例如:
在儲存格C1中輸入公式:=EOMONTH(DATE(A2,B2,1),0)
在儲存格D1中輸入公式:=C2,然後設定數值格式為星期幾。
(其中C欄的數值格式設定為:yyyy/mm/dd)
在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個字元。
這個公式套用在中文的字串也可以。
在Excel中如果想要輸入一個日期後,只顯示月或日或年的部分,可以使用自訂數值格式(參考下圖)。
其中:
[DBNum1]顯示:一、二、三、四 …
[DBNum2]顯示:壹、貳、参、肆 …
[DBNum3]顯示:1、2、3、4 …
[DBNum1]顯示:1、2、3、4 …
如果想要在Excel的工作表中顯示某個工作表的名稱,該如何做呢?
例如在Sheet1工作表的儲存格A1中輸入公式:
=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,256)
結果會得到Sheet2,如果工作表的名稱有所更改,其結果也會跟著變動。
其中CELL()函數會傳回有關儲存格之格式、位置或內容的資訊
語法:CELL(info_type, [reference])
在Excel中如果想要計算出生到今天的實際年齡,有沒有比較快速的方式呢?試試Datedif()函數。
語法:
DATEDIF(start_date,end_date,unit)
unit參數 | 說明 |
"Y" | 週期中的整年數 |
"M" | 週期中的整月數 |
"D" | 週期中的天數 |
"MD" | start_date與end_date間的天數差。(忽略日期中的月和年) |
"YM" | start_date與end_date間的月數差。(忽略日期中的日和年) |
"YD" | start_date與end_date間的天數差。(忽略日期中的年) |
參考下例:
A為start_date,B為end_date,其結果C2即為兩個日期之間的整年數。
如果想要計算下圖中的總人數,將[合計]置於A欄,似乎有些浪費空間,而且可能會造成排版上的困擾。有什麼方法可以調整呢?
例如:
在儲存格D6中輸入公式:="合計" & SUM(D2:D5) & "人"
使用串接符號 &來串接公式,如果要修改公式時,會比較不方便。
或是使用數值格式設定方式:
在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
在儲存格中可能會出現錯誤訊息,所以在做總和(或其他運算)時,也可能會出現錯誤結果。
例如:儲存格H1運算公式為「=SUM(B2:G2)」,其結果為#VALUE!,無法得到運算結果。
如果想要忽略有錯誤訊息的儲存格,得出總和的運算結果,以下兩個方式可以實現:
儲存格H3:{=SUM(IF(NOT(ISERROR(B3:G3)),B3:G3,0))}
使用陣列公式,排除有錯誤訊息的儲存格才列入總和(即沒有錯誤者才算總和)。
如果要讓Excel自動求得當月的最大天數,必須藉助Day()函數,先來看看Day()函數的使用。
語法:DAY(serial_number)
以下表為例:
D2儲存格:=DAY(DATE(A2,B2,C2))
日期為2010年2月20日,所以顯示20,即第20天。
D3儲存格:=DAY(DATE(A3,B3,C3))
網路上看到這個計算不重覆的數值個數的解法還不錯,一起來分享一下。
在儲存格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這個函數將全部的數值陣列加總,
假設取得一個學生成績資料表(共300筆資料),其為學生夜間讀書方式和模擬考名次的記錄表(以下資料為模擬產生)。
如果想要轉換成以下的統計報表,該如何處理呢?
首先,在儲存格F2輸入:=(ROW(F2)-2)*50+1 & "-" & (ROW(F2)-1)*50
(如果想要改變成績間距,只要改變公式中的50即可)
在二進制的減法中,是以加法來處理減法,所以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)
如果要建立一個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。
一般使用計算機要計算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)