贊助廠商

學不完.教不停.用不盡文章列表

一個老師將學生分成多組輪流上台報告,每個學生都要為各組給一個分數,而自己所在的組別不評分,每組成員的分數是相同的,都是全班其他組所給分數的平均。如何來設計這個表格呢?(參考下圖)

儲存格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函數計算平均。

現在,要利用格式化規則來設定自己組別的儲存格為較深的綠色,以提醒輸入成績時不要輸入。

先選取儲存格D2:I25,設定格式化規則為:

選取「使用公式來決定要格式化哪些儲存格」,輸入公式:「=$C2=COLUMN(A:A)」,格式為較深的綠色。

如果還是怕不小心輸入,則可以使用「資料驗證」方式,將不小心輸入的數值,顯示錯誤訊息(如下圖)。

先選取儲存格D2:I25,設定資料驗證為:

儲存格允許:自訂,公式:=$C2<>COLUMN(A:A)

並設錯誤提醒訊息,如下所示:

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

在 Excel 中取得一個物品領用的數量統計表,現在要利用這個資料表以公式運算方式列出(1)整年領取數為0者,(2)每個月都有被領取者。

因為需要用到一個「輔助」欄位,所以先輸入公式:

儲存格Q2:=COUNTIF(D2:O2,"<>0"),複製儲存格Q2,往下各列貼上。

將A欄有資料的部分定義名稱為「編號」;將B欄有資料的部分定義名稱為「請領物品」;將P欄有資料的部分定義名稱為「小計」;將Q欄有資料的部分定義名稱為「輔助」。

(1) 整年領取數為0者

儲存格S2:{=IFERROR(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),"")}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格S2,往下各列貼上。以下公式做法雷同。

(A) IF(小計=0,編號,FALSE):得到小計為0者的編號陣列。

(B) SMALL((A),ROW(1:1):取得(A)陣列中的最小值(最小的編號)。往下複製時ROW(1:1)=1 → ROW(2:2)=2 → ‥‥,可取得第2小、第3小‥‥的編號

(C) IFERROR((B),""):因為當找不到編號時會傳回錯誤值,所以藉由IFERROR函數,將其顯示為空白。

儲存格T2:{=IFERROR(LOOKUP(SMALL(IF(小計=0,編號,FALSE),ROW(1:1)),編號,請領物品),"")}

原理同上,再將編號利用LOOKUP函數以查表方式找到對應的「請領物品」名稱。

(2)每個月都有被領取者

試著根據(1)的做法,藉助「輔助」欄位,練習每個月都有被領取者。

儲存格V2:{=IFERROR(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),"")}

其先找到輔助欄位為12者(12個月都不為0)的陣列,再取出其編號。

儲存格W2:{=IFERROR(LOOKUP(SMALL(IF(輔助=12,編號,FALSE),ROW(1:1)),編號,請領物品),"")}

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼