一個老師將學生分成多組輪流上台報告,每個學生都要為各組給一個分數,而自己所在的組別不評分,每組成員的分數是相同的,都是全班其他組所給分數的平均。如何來設計這個表格呢?(參考下圖)
儲存格J2 :=AVERAGE(OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),))
複製儲存格J2,往下貼在儲存格J2:J25。
COUNT($C$2:$C$25):計算所有人員共有幾列。
OFFSET($D$2,,$C2-1,COUNT($C$2:$C$25),):取得各組的位址,例如第1組為儲存格D2:D25,第2組為E2:E25,…。
再使用AVERAGE函數計算平均。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 中取得一個物品領用的數量統計表,現在要利用這個資料表以公式運算方式列出(1)整年領取數為0者,(2)每個月都有被領取者。
因為需要用到一個「輔助」欄位,所以先輸入公式:
儲存格Q2:=COUNTIF(D2:O2,"<>0"),複製儲存格Q2,往下各列貼上。
將A欄有資料的部分定義名稱為「編號」;將B欄有資料的部分定義名稱為「請領物品」;將P欄有資料的部分定義名稱為「小計」;將Q欄有資料的部分定義名稱為「輔助」。
(1) 整年領取數為0者
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 中取得一串資料(如下圖A欄),因為每筆資料中包含了「"」字元,所以當你使用 Excel 的排序工具時,將會變成2" → 21" → 3" → ‥‥,而非2" → 3" → ‥‥。如何解決這樣的問題呢?
這是因為 Excel 將 2" 視為文字而非數字來排序所造成的問題。
先將儲存格A2:A21定義名稱為「資料」
儲存格C2:{=SMALL(VALUE(SUBSTITUTE(資料,"""","")),ROW(1:1))&""""}
這是陣列公式,輸入完成時要按 Ctrl+Shift6+Enter 鍵。複製儲存格C2,貼至儲存格C2:C21。
SUBSTITUTE(資料,"""",""):將資料中的「"」字元消除。
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 如果要製作一個類似教師甄選的成績計算(如下圖),該如何設計?
因為不同項目有不同的比重,而且比重的總和為100%,設計如下:
儲存格K4:=B4*$B$2+AVERAGE(C4:G4)*$C$2+AVERAGE(H4:J4)*$H$2
儲存格L4:=RANK(K4,$K$4:$K$24)
複製儲存格K4:L4,往下各列貼上。
vincent 發表在 痞客邦 留言(3) 人氣()
在 Excel 中的「資料驗證」功能,可以幫助使用者在輸入資料時可以只接受符合準則的輸入內容。Excel 提供了整數、實數、清單、日期、時間、文字長度和自訂公式等幾種驗證項目。
其實你只要將平時使用在儲存格中的公式,稍加變化即可套用在儲存格的資料驗證上,會有很多的妙用哦!
以下例舉5種應用:
(1) 不接受未來日期
日期設定小於或等於今天的日期(=TODAY())
vincent 發表在 痞客邦 留言(1) 人氣()
在 Excel 中有一串數字(如下圖左),如何以公式篩選出不同位數的數字?操作前先執行名稱的定義:
編號:儲存格A2:26;數字:儲存格B2:B26。
儲存格D2:
{=IFERROR(INDEX(資料,LARGE(IF(LEN(數字)=COLUMN(A:A),編號,FALSE),ROW(1:1))+1,2),"")}
複製儲存格D2,貼至儲存格D2:G21。
原理:
vincent 發表在 痞客邦 留言(0) 人氣()
在 Excel 中,針對各班班級幹部的資料表(如下圖),來練習INDEX、MATCH、OFFSET函數。
如果要產生下圖的查詢結果,該如何處理?其中班級名稱和幹部的名稱要用下拉式清單來挑選,而姓名則是以公式來查詢。
(1) 定義以下三個名稱
班級:儲存格A2:A29;幹部:儲存格B1:H1;資料:儲存格A1:H29。
vincent 發表在 痞客邦 留言(2) 人氣()
在 Excel 中取得學生量測身高和體重(參考下圖)(虛擬數據),如何計算學生的BMI值和判定體位,如果要告訴學生適當的體重該如何計算?
(1) 因為BMI值為「體重(KG)÷身高(M)的平方」,所以:
儲存格G2:=F2/((E2/100)^2)
(2) 根據下圖右側的BMI範圍對照體位表:
儲存格H2:=LOOKUP(G2,$K$2:$L$7,$N$2:$N$7)
(3) 根據體重來求出適當的體重
vincent 發表在 痞客邦 留言(0) 人氣()
這次要練習的查表動作,需要較多的公式,感覺會較難,所以要有耐心和毅力才能有助於學會。
(一)
在 Excel 中有一個全校班級幹部的摘要表(如下圖),如何自動轉換至學生名單中註記幹部名稱(如下下圖)?
儲存格F2:
vincent 發表在 痞客邦 留言(0) 人氣()
最近被問到:如果取得一個一年級選組後的報表,如何針對各類組的男、女生人數和各班的選組人數,製作一個摘要表?(參考下圖)
這兩個工作只要交給SUMPRODUCT函數即可解決:
儲存格I2:=SUMPRODUCT(--($E$2:$E$484=1),--($F$2:$F$484=$H2))
--($E$2:$E$484=1):判斷E欄中是否為「1」(男生)的 True/False 陣列,其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。
--($F$2:$F$484=$H2):判斷F欄中是否為「1」(第1類組)的 True/False 陣列,其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。
SUMPRODUCT函數會將這兩個陣列相乘,再將這些 1/0 的結果加總。(其實是兩者條件皆成立時,相乘結果才會為1,也才會被加總。)
vincent 發表在 痞客邦 留言(0) 人氣()