在 Excel 的工作表中,如果要核對三個欄位中的數字是否完全相同,該如何做呢?
試著列舉以下數種計算方式來練習:
儲存格D2:=IF(A2=B2,IF(B2=C2,"V","X"),"X")
儲存格D3:=IF((A3=B3)*(B3=C3),"V","X")
儲存格D4:=IF(AND(A4=B4,B4=C4),"V","X")
在 Excel 的工作表中,如果要核對三個欄位中的數字是否完全相同,該如何做呢?
試著列舉以下數種計算方式來練習:
儲存格D2:=IF(A2=B2,IF(B2=C2,"V","X"),"X")
儲存格D3:=IF((A3=B3)*(B3=C3),"V","X")
儲存格D4:=IF(AND(A4=B4,B4=C4),"V","X")
在 Excel 中的有一個資料表(如下圖),若要執行以下的運算,該如何處理?
(1) F欄要計算當A欄大於50 且 B欄大於50時,計算C欄、D欄、E欄的和。
(2) G欄要計算當A欄大於50 或 B欄大於50時,計算C欄、D欄、E欄的和。
透過陣列公式加入AND和OR運算即可完成。
儲存格F2:{=SUM(IF((A2>50)*(B2>50),C2:E2))}
儲存格G2:{=SUM(IF((A2>50)+(B2>50),C2:E2))}
在 Excel 中若要在一個儲存格中顯示文字+數字的結果,則必須藉助 & 符號。例如:
儲存格E2:="共" & SUM(B2:D2) & "人"
會顯示「共1819人」,但是如果要對數字部分做顯示格式的設定該如何做呢?如何做到以下的效果呢?
只要藉助TEXT函數即可,詳細的函數說明請參考微軟網站:
http://office.microsoft.com/zh-tw/excel-help/HP010342952.aspx
當你在 Excel 中取得如下圖的工作表(資料範圍為儲存格A1:M79),分別列出各年級得分的人數,如何將各年級7-10分和1-6分的人數各為多少,分別計算出來?
(表中人數為亂數產生,無特定意義。)
假設要產生如下圖的統計表,分別輸入以下公式:
儲存格P3:{=SUM(IF($A$2:$A$79=O3,IF($B$2:$B$79=P$1,$C$2:$F$79)))}
(意義:=SUM(IF(符合學校名稱,IF(符合年級,7-10分儲存格範圍)))
在 Excel 的工作表中有一個各個分數區間(2~10)的統計表,如果想要以勾選方式求得各等第的人數和比例應該如何操作?使用SUMPRODUCT函數很容易可以做到。
儲存格K3:=SUMPRODUCT((B3:J3="V")*$B$1:$J$1)
儲存格L3:=K3/$K$1
複製儲存格K3:L3到儲存格K3:L7。
其中SUMPRODUCT函數中的參數:
(B3:J3="V")*$B$1:$J$1
在 Excel 的工作表中,數值通常包含整數和小數部分,如何利用數值格式設定來讓數字之間相互對齊呢?
在數值格式設定中可以使用「.、#、0和 ?」 來設定,參考以下說明:
0 |
如果數字的位數少於格式中零的個數,則這個小數預留位置會顯示有效的零。 |
# |
這個小數預留位置的規則和 0 (零) 相同。不過,如果輸入的數字其小數點兩邊的位數少於格式中 # 符號的個數,Excel 並不會額外補上零。 |
在 Excel 中取得一個資料表(如下圖左)(資料範圍:儲存格A2:D141),以樞紐分析方式得到各種年資的各種職務人數分析(如下圖右)。如果想要以年資區間和區別各處室來分析可能不太容易,如何以公式完成這個要求呢?
參考下圖左,年資以每5年一個區間,依不同處室和不同職務來計算符合的人數。這次要以SUMPRODUCT函數來運算。
儲存格N2:=SUMPRODUCT(--($B$2:$B$141=$M2),--($C$2:$C$141=N$1),--($D$2:$D$141>=INT(ROW(3:3)/3)*5-4),--($D$2:$D$141<=INT(ROW(3:3)/3)*5))
將儲存格N2複製到儲存格N2:P19。
公式的意義是:
在 Excel 中取得一個資料表(如下圖左)(資料範圍:儲存格A2:D141),以樞紐分析方式得到各種年資的各種職務人數分析(如下圖右)。如果想要以年資區間來分析可能不太容易,如何以公式完成這個要求呢?
參考下圖左,年資以每5年一個區間,依不同職務來計算符合的人數。
(一)使用COUNTIFS函數
儲存格M2:=COUNTIFS($C$2:$C$141,M$1,$D$2:$D$141,"<=5")
複製儲存格M2到儲存格M2:O2。
在 Excel 中取得一個資料表(如下圖左)(資料範圍:儲存格A2:C141),以樞紐分析方式得到各年級的各種請假時數分析(如下圖右)。如果想要以月份來分析可能不太容易,如何以公式完成這個要求呢?
現在要以公式來建立分析表(如下圖左),依年級、月份和假別分別來分析。你可以使用SUMPRODUCT函數、SUMIFS函數和陣列公式等三種方式來做,以下以SUMPRODUCT為例:
儲存格M3:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$3),--($D$2:$D$141=O$2),$E$2:$E$141)
將儲存格M3複製到儲存格O3:Q8。
儲存格M9:=SUMPRODUCT(--(MONTH($A$2:$A$141)=ROW(1:1)),--($B$2:$B$141=$M$9),--($D$2:$D$141=O$2),$E$2:$E$141)
在 Excel 中取得一個資料表(如下圖左)(資料範圍:儲存格A2:C395),以樞紐分析方式得到各年級的數量分析(如下圖右)。如果你想以公式完成相同動作該如何處理?
你可以透過以下三種方式完成和樞紐分析表相同的工作:
(一)使用SUMIF函式
儲存格F3:=SUMIFS($C$2:$C$395,$A$2:$A$395,$E3,$B$2:$B$395,F$2)
使用SUMIF函式:SUMIFS(加總範圍,學校範圍,指定某一學校,年級範圍,指定某一年級)
(二)使用SUMPRODUCT函式
在 Excel 中執行樞紐分析時,對於產生的樞紐分析表,如果其中有儲存格未填入任何資料,則在計算平均時將會產生錯誤。
例如下圖左(資料範圍:儲存格A1:A395),有些儲存格沒有任何資料,所以:
得到第一個平均為100+200+300+400/4=250(如下圖中)。
但是如果要將空白存格列入平均(如下圖右),則可以輸入公式:
儲存格I2:{=AVERAGE(IF($A$2:$A$395=H2,$C$2:$C$395))}
陣列公式,輸入完成後要按一下 Ctrl+Alt+Enter 鍵。複製儲存格I2,在儲存格I2:I27貼上。
在 Excel 中常會用到各種亂數,RAND 和 RANDBETWEEN 兩個函數可以產生各種亂數值。但是如果想要產生某個範圍內的亂數,則需進一步改造公式。試著練習以下各種公式:
項次 |
公式範例 |
範圍 |
備註 |
1 |
在 Excel 的工作表中有一份如下圖的數據,如果想要計算指定名次內的小計,該如何處理?
(一)計算累計的總和和平均
你可以使用以下三種方式來計算總和:
1.儲存格G2:=SUMIF($C$2:$C$26,"<="&E2*5,$B$2:$B$26)
2.儲存格G2:=SUMPRODUCT(--($C$2:$C$26<=E2*5)*$B$2:$B$26)
公式中的「--」運算,可以將 $C$2:$C$26<=E2*5 的結果 True/False 轉換為 1/0。
在使用 Excel 時,加總運算是最平常的公式,所以 Excel 提供了加總按鈕,可以快速完成。通常你要執行加總運算,可以在儲存格中輸入:=SUM(number1, [number2], …),其中要加入運算的儲存格可以使用選取儲存格方式代入。
如果你選取了如下的表格範圍(連同要顯示加總的儲存格也一併選取)。
或是只選取要顯示小計的儲存格。
上一篇:Excel-產生連續的數列來運算(http://isvincent.blogspot.com/2010/11/excel_18.html)提到連續數列的產生,這次拿它進一步做一些運算。
例如:傳統上要計算1-1/2+1/3-1/4+1/5-1/6 …,根據下圖右的運算,
儲存格B3:=-1*((MOD(ROW(A3),2))*2-1)*(1/A3)
儲存格C3:=SUM($I$2:I3),複製儲存格B3:C3,並往下貼上。可求得累加的結果。
如果運用連續的數列來運算,則可以這樣做:
儲存格F2:
在 Excel 中的基本單位是儲存格,以程式的角度來看,每個儲存格相當於一個記憶體,而每個記憶體可以當成一個變數使用,也可以將儲存格中的內容當為常數使用。所以當你要計算1+2+3+ … +100的運算時,可以在100個儲存格中產1~100的數字,再將這些儲存格作SUM運算,很容易就可以得到答案。
你可以有更簡單的做法。配合陣列運算和ROW、INDIRECT函數,可以產生連續的數列。
例如:
存格陣列公式:{ROW($A$1:INDIRECT("A"&A2))}
若儲存格A2的內容為:10,則
ROW($A$1:INDIRECT("A"&A2))
在 Excel 計算成績的加權平均是一件簡單的事,即每個「科目X學分」的總和,再除以「學分的總和」(例如下圖)。公式如何設定?
(一)學分數以數字表示(如下圖上半部)
儲存格G3:{=SUM(B3:F3*B2:F2)/SUM(B2:F2)}
此為陣列公式,輸入完成要按 Ctrl+Alt+Enter。
如果不使用陣列公式,則改為:
儲存格G3:=SUMPRODUCT(B3:F3,B2:F2)/SUM(B2:F2)
在 Excel 的工作表中如果想要產生每月是同一天的日期數列,如何操作較為方便呢?
方法一:
1. 在儲存格B1中輸入第一個日期,例如:2010/01/05。
2. 在儲存格B2中輸入第一個日期,例如:2010/02/05。
3. 選取儲存格B1:B2。
4. 向下拖曳填滿控制點至想要的終止儲存格(拖曳過程中即可預覽產生的數列)。
如果你在 Excel 中取得一個成績的資料表,而想要計算某百分比內的平均分數,該如何處理?
輸入公式:
儲存格F2:="前"&ROW(1:1)*10&"%"
ROW(1:1)=1,ROW(2:2)=2,…,ROW(9:9)=9。
儲存格G2:=AVERAGEIF($D$2:$D$34,">="&PERCENTILE($D$2:$D$34,1-ROW(1:1)/10))
利用 PERCENTILE 函數從一個範圍裡,找出位於其中第 k 個百分位數的值。