學不完．教不停．用不盡

在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]顯示：１、２、３、４ …

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

參考下例：

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)

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

建立如下的表格：

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

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